标签：00 01 数据文件 分区表 表与 SQL table ORDERS name
文档课题：在线将普通表与分区表进行相互转换以及移动数据文件位置.
数据库：oracle 19.3
1、测试数据
C:\Users\Administrator>sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on 星期日 2月 19 10:21:20 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
连接到:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> conn leo/leo@pdb
已连接.
SQL> show user
USER 为 "LEO"
SQL> desc orders
名称 是否为空? 类型
----------------------------------------------------- -------- ------------------------------------
ID NUMBER
NAME VARCHAR2(20)
ORDER_DATE TIMESTAMP(6) WITH LOCAL TIME ZONE
SQL> select count(*) from orders;
COUNT(*)
----------
16000000
SQL> select bytes/1024/1024 from user_segments where segment_name='ORDERS';
BYTES/1024/1024
---------------
575
SQL> select to_char(order_date,'yyyy') year_str,count(*) from orders group by to_char(order_date,'yyyy') order by 1;
YEAR_STR COUNT(*)
-------- ----------
2007 992061
2008 993772
2009 991452
2010 991581
2011 989319
2012 994619
2013 991088
2014 989692
2015 993222
2016 992634
2017 991165
YEAR_STR COUNT(*)
-------- ----------
2018 991529
2019 990773
2020 993974
2021 989582
2022 990245
2023 133292
已选择 17 行.
2、转换为分区表
2.1、开始转换
将普通表在线转换为分区表.
SQL> alter table orders modify partition by range (order_date)
2 (partition p2022 values less than(timestamp '2022-01-01 00:00:00 +00:00'),
3 partition p2023 values less than(timestamp '2023-01-01 00:00:00 +00:00'),
4 partition p2030 values less than(maxvalue)
5 ) online update indexes;
表已更改.
注意：此处数据库版本需为oracle 12.2或更高版本，否则报错ora-14006:invalid partition name.
2.2、验证分区表
SQL> col table_name for a15
SQL> col partition_name for a20
SQL> col high_value for a50
SQL> set line 200
SQL> select table_name,partition_name,high_value from user_tab_partitions where table_name='ORDERS';
TABLE_NAME PARTITION_NAME HIGH_VALUE
--------------- -------------------- --------------------------------------------------
ORDERS P2022 TIMESTAMP' 2022-01-01 00:00:00.000000000+00:00'
ORDERS P2023 TIMESTAMP' 2023-01-01 00:00:00.000000000+00:00'
ORDERS P2030 MAXVALUE
2.3、分区拆分
--将p2022分区拆分为partition p2010,partition p2020分区.
SQL> alter table orders split partition p2022 at(timestamp '2010-01-01 00:00:00 +00:00') into
2 (partition p2010,partition p2020) online;
表已更改.
--查看拆分后的分区情况.
SQL> select table_name,partition_name,high_value from user_tab_partitions where table_name='ORDERS';
TABLE_NAME PARTITION_NAME HIGH_VALUE
--------------- -------------------- --------------------------------------------------
ORDERS P2010 TIMESTAMP' 2010-01-01 00:00:00.000000000+00:00'
ORDERS P2020 TIMESTAMP' 2022-01-01 00:00:00.000000000+00:00'
ORDERS P2023 TIMESTAMP' 2023-01-01 00:00:00.000000000+00:00'
ORDERS P2030 MAXVALUE
SQL> select max(order_date) from orders partition(p2010);
MAX(ORDER_DATE)
---------------------------------------------------------------------------
01-1月 -10 07.58.49.000000 上午
--对p2010分区再次进行拆分.
SQL> alter table orders split partition p2010 at(timestamp '2008-01-01 00:00:00 +00:00') into
2 (partition p2008,partition p2009) online;
表已更改.
SQL> select table_name,partition_name,high_value from user_tab_partitions where table_name='ORDERS';
TABLE_NAME PARTITION_NAME HIGH_VALUE
--------------- -------------------- --------------------------------------------------
ORDERS P2008 TIMESTAMP' 2008-01-01 00:00:00.000000000+00:00'
ORDERS P2009 TIMESTAMP' 2010-01-01 00:00:00.000000000+00:00'
ORDERS P2020 TIMESTAMP' 2022-01-01 00:00:00.000000000+00:00'
ORDERS P2023 TIMESTAMP' 2023-01-01 00:00:00.000000000+00:00'
ORDERS P2030 MAXVALUE
SQL> select max(order_date) from orders partition(p2008);
MAX(ORDER_DATE)
---------------------------------------------------------------------------
01-1月 -08 07.59.37.000000 上午
3、分区合并
--将分区p2009,p2020合并为p2021.
SQL> alter table orders merge partitions p2009,p2020 into partition p2021 online;
表已更改.
已用时间: 00: 02: 29.26
SQL> select table_name,partition_name,high_value from user_tab_partitions where table_name='ORDERS';
TABLE_NAME PARTITION_NAME HIGH_VALUE
--------------- -------------------- --------------------------------------------------
ORDERS P2008 TIMESTAMP' 2008-01-01 00:00:00.000000000+00:00'
ORDERS P2021 TIMESTAMP' 2022-01-01 00:00:00.000000000+00:00'
ORDERS P2023 TIMESTAMP' 2023-01-01 00:00:00.000000000+00:00'
ORDERS P2030 MAXVALUE
已用时间: 00: 00: 00.00
说明：操作的过程中表可以正常访问，索引也能正常维护.
4、修改为普通表
4.1、验证在线重定义可行性
通过dbms_redefinition方式将分区表在线改造成非分区表.
--验证是否可以执行在线重定义.
SQL> conn sys/oracle_4U@pdb as sysdba
已连接.
SQL> grant dba to leo;
授权成功.
已用时间: 00: 00: 00.00
SQL> conn leo/leo@pdb
已连接.
SQL> begin
2 dbms_redefinition.can_redef_table(
3 uname => 'leo',
4 tname => 'orders',
5 options_flag => dbms_redefinition.cons_use_rowid
6 );
7 end;
8 /
PL/SQL 过程已成功完成.
已用时间: 00: 00: 00.00
说明：执行以上存储过程检查是否可以在线重定义，若返回错误则不能在线重定义.
4.2、开始在线重定义
--创建过渡表
SQL> create table orrtable_tmp as select * from orders where 1=2;
表已创建.
已用时间: 00: 00: 00.01
--执行表的在线重定义.
调用以下存储过程，会将目前表的数据转换到过渡表中.
SQL> begin
2 dbms_redefinition.start_redef_table(
3 uname => 'leo',
4 orig_table => 'orders',
5 int_table => 'orrtable_tmp',
6 options_flag => dbms_redefinition.cons_use_rowid
7 );
8 end;
9 /
PL/SQL 过程已成功完成.
已用时间: 00: 04: 17.76
SQL> select table_name,partition_name,high_value from user_tab_partitions where table_name='ORDERS';
TABLE_NAME PARTITION_NAME HIGH_VALUE
--------------- -------------------- --------------------------------------------------
ORDERS P2008 TIMESTAMP' 2008-01-01 00:00:00.000000000+00:00'
ORDERS P2021 TIMESTAMP' 2022-01-01 00:00:00.000000000+00:00'
ORDERS P2023 TIMESTAMP' 2023-01-01 00:00:00.000000000+00:00'
ORDERS P2030 MAXVALUE
已用时间: 00: 00: 00.00
SQL> select table_name,partition_name,high_value from user_tab_partitions where table_name='ORRTALE_TMP';
未选定行
已用时间: 00: 00: 00.03
SQL> select count(*) from orrtable_tmp;
COUNT(*)
----------
16000000
已用时间: 00: 00: 00.23
4.3、解决依赖对象
--自动创建依赖对象，使用copy_table_dependents过程在过渡表上自动创建依赖对象.包括表的权限、触发器、索引、约束，将其复制到orrtable表上去.
SQL> declare
2 num_errors PLS_INTEGER;
3 begin
4 dbms_redefinition.copy_table_dependents(uname => 'leo',
5 orig_table => 'orders',
6 int_table => 'orrtable_tmp',
7 copy_indexes => dbms_redefinition.cons_orig_params,
8 copy_triggers => TRUE,
9 copy_constraints => TRUE,
10 copy_privileges => TRUE,
11 ignore_errors => TRUE,
12 num_errors => num_errors);
13 end;
14 /
PL/SQL 过程已成功完成.
已用时间: 00: 00: 07.56
4.4、处理增量数据
说明：以上处理的是存量数量，因业务一直运行，所以需要将临时表的内容和源表数据进行同步.
--通过以下过程，将增量数据同步到过渡表中.
SQL> begin
2 dbms_redefinition.sync_interim_table(uname => 'leo',
3 orig_table => 'orders',
4 int_table => 'orrtable_tmp');
5 end;
6 /
PL/SQL 过程已成功完成.
已用时间: 00: 00: 00.07
4.5、结束在线重定义
--执行结束在线定义过程.
SQL> begin
2 dbms_redefinition.finish_redef_table(uname => 'leo',
3 orig_table => 'orders',
4 int_table => 'orrtable_tmp');
5 end;
6 /
PL/SQL 过程已成功完成.
已用时间: 00: 00: 01.82
4.6、检查数据
--检查表状态
SQL> select count(*) from orders;
COUNT(*)
----------
16000000
已用时间: 00: 00: 00.26
SQL> select table_name,partition_name,high_value from user_tab_partitions where table_name='ORDERS';
未选定行
已用时间: 00: 00: 00.07
SQL> select table_name,partition_name,high_value from user_tab_partitions where table_name='ORRTALE_TMP';
未选定行
已用时间: 00: 00: 00.03
SQL> select count(*) from orrtable_tmp;
COUNT(*)
----------
16000000
已用时间: 00: 00: 00.22
SQL> select count(*) from orders;
COUNT(*)
----------
16000000
已用时间: 00: 00: 00.22
5、在线迁移数据文件
SQL> col tablespace_name for a15
SQL> select t.table_name,tablespace_name,partitioned,status from user_tables t where table_name='ORDERS';
TABLE_NAME TABLESPACE_NAME PARTIT STATUS
--------------- --------------- ------ ----------------
ORDERS USERS NO VALID
已用时间: 00: 00: 00.00
SQL> col segment_name for a15
SQL> select segment_name,tablespace_name from user_segments where segment_name='ORDERS';
SEGMENT_NAME TABLESPACE_NAME
--------------- ---------------
ORDERS USERS
已用时间: 00: 00: 00.00
SQL> col file_name for a60
SQL> select file_id,tablespace_name,file_name,bytes/1024/1024 from dba_data_files where tablespace_name='USERS'
FILE_ID TABLESPACE_NAME FILE_NAME BYTES/1024/1024
---------- --------------- ------------------------------------------------------------ ---------------
12 USERS F:\APP\ADMINISTRATOR\ORADATA\SIMDB\PDB\USERS01.DBF 5880
已用时间: 00: 00: 00.00
--系统层面新建bak目录.
SQL> alter database move datafile 12 to 'F:\APP\ADMINISTRATOR\ORADATA\SIMDB\BAK\USERS01.DBF';
数据库已更改.
已用时间: 00: 18: 09.02
说明：如果从文件系统移动到asm磁盘，使用语句alter database move datafile 12 to ‘+data’;
参考网址：
https://www.bilibili.com/video/BV1PD4y1T7TT/?spm_id_from=333.337.search-card.all.click&vd_source=8c872e2fd1d99229b38a73ed6718b776
标签：00,
01,
数据文件,
分区表,
表与,
SQL,
table,
ORDERS,
name
From： https://blog.51cto.com/u_12991611/6066675