普通表转化成分区表一例（在线重定义分区表）_alter session force parallel query parallel degree-CSDN博客

本文链接：https://blog.csdn.net/hzhvv/article/details/51735874

DBA用户：
GRANT EXECUTE ON DBMS_REDEFINITION TO newsduser;

NEWSDUSER用户:
1、创建中间表，分区键为logdat，2015年前为一个分区，2015年起按月分区，分区先创建到2015年12月。
CREATE TABLE POSTXNJNLHIS_part (
LOGNO CHAR(14) ,
LOGDAT CHAR(8) ,
TTXNCD CHAR(10) ,
TXNTYP CHAR(1) ,
TXNCD CHAR(10) ,
SYSDAT CHAR(8) ,
TXNTM CHAR(6) ,
TRMTYP CHAR(1) ,
TXNSTS CHAR(1) ,
PROCOD CHAR(6) ,
CSEQNO CHAR(6) ,
MERCID CHAR(15) ,
TERMID CHAR(8) ,
BATNO CHAR(6) ,
SREFNO CHAR(12) ,
AGTORG CHAR(13) ,
RTRSVR CHAR(8) ,
RTRCOD CHAR(10) ,
TTXNDT CHAR(8) ,
TTXNTM CHAR(6) ,
TSEQNO CHAR(20) ,
THDCHK CHAR(1) ,
TMERCID CHAR(15) ,
TTERMID CHAR(8) ,
TBATNO CHAR(6) ,
AUTCOD CHAR(6) ,
TOPRID CHAR(6) ,
CPSCOD CHAR(2) ,
TSREFNO CHAR(12) ,
ACTNO CHAR(21) ,
CRDNO CHAR(30) ,
CRDSQN CHAR(3) ,
CRDFLG CHAR(2) ,
EXPDAT CHAR(4) ,
ISSINO CHAR(11) ,
HISINO CHAR(11) ,
CRDNO1 CHAR(30) ,
TXNAMT CHAR(15) ,
REFAMT CHAR(15) ,
FEE CHAR(15) ,
ORGNO CHAR(6) ,
CCYCOD CHAR(3) ,
INMOD CHAR(3) ,
IDNO CHAR(18) ,
ACQCOD CHAR(11) ,
FORCOD CHAR(11) ,
ACTTYP CHAR(2) ,
OLOGNO CHAR(14) ,
MERTYP CHAR(4) ,
INSADR VARCHAR2(100 CHAR),
POSCND CHAR(2) ,
RSVDAT CHAR(30) ,
ORNDAT CHAR(42) ,
OPROCOD CHAR(6) ,
OTXNDT CHAR(4) ,
OTXNTM CHAR(6) ,
OSREFNO CHAR(12) ,
OBATNO CHAR(6) ,
EMVFLG CHAR(1) ,
ICDAT CHAR(510) ,
ICLEN CHAR(3) ,
HACQCD CHAR(11) ,
HFORCD CHAR(11) ,
CPSRSV CHAR(100) ,
ACQRSV CHAR(100) ,
ENDFLG CHAR(1) ,
TMSTIM CHAR(10) ,
HSTDAT CHAR(8) ,
CPSDAT CHAR(8) ,
TXNRSV1 CHAR(20) ,
TXNRSV2 CHAR(20) ,
TXNRSV3 CHAR(20) ,
OCSEQNO CHAR(6) ,
ROTFLG CHAR(1) ,
BRAID CHAR(8) ,
DAYSTLFLG CHAR(1) ,
STLFLG CHAR(1) ,
SHAFEE CHAR(15) ,
SHRABLEAMT CHAR(15) ,
COSTFEE CHAR(15) ,
ORGCOST CHAR(15) ,
SERCOST CHAR(15) ,
CHAMERCID CHAR(15) ,
CHATERMID CHAR(8) ,
ISSNAM CHAR(100) ,
ISFALLBACK CHAR(100)
) PARTITION BY RANGE(logdat)
(
PARTITION P_BEFORE_201501 VALUES LESS THAN ('20150101'),
PARTITION P_201501 VALUES LESS THAN ('20150201'),
PARTITION P_201502 VALUES LESS THAN ('20150301'),
PARTITION P_201503 VALUES LESS THAN ('20150401'),
PARTITION P_201504 VALUES LESS THAN ('20150501'),
PARTITION P_201505 VALUES LESS THAN ('20150601'),
PARTITION P_201506 VALUES LESS THAN ('20150701'),
PARTITION P_201507 VALUES LESS THAN ('20150801'),
PARTITION P_201508 VALUES LESS THAN ('20150901'),
PARTITION P_201509 VALUES LESS THAN ('20151001'),
PARTITION P_201510 VALUES LESS THAN ('20151101'),
PARTITION P_201511 VALUES LESS THAN ('20151201'),
PARTITION P_201512 VALUES LESS THAN ('20160101'),
PARTITION P_max VALUES LESS THAN (MAXVALUE)
);

2、判断表是否能够被重新定义成分区表
SQL> exec DBMS_REDEFINITION.CAN_REDEF_TABLE('newsduser','postxnjnlhis');

DBA用户查看当前redo量，在分区表定义过程中会产生redo，可作为对比参照
select c.sid,b.NAME,a.VALUE from v$sesstat a ,v$statname b ,(select SID from v$mystat where rownum<=1) c where a.SID=c.sid and a.STATISTIC#=b.STATISTIC# and b.name like '%redo%' and a.VALUE<>0 ;

3、DBA用户执行分区交换
SQL> show user
USER is "SYS"
SQL> set timing on
SQL> exec DBMS_REDEFINITION.START_REDEF_TABLE('newsduser','POSTXNJNLHIS','POSTXNJNLHIS_part');

PL/SQL procedure successfully completed.

Elapsed: 00:00:49.31
SQL> exec DBMS_REDEFINITION.FINISH_REDEF_TABLE('newsduser','POSTXNJNLHIS','POSTXNJNLHIS_part');

######## 如需取消，则运行 exec DBMS_REDEFINITION.ABORT_REDEF_TABLE('newsduser', 'POSTXNJNLHIS','POSTXNJNLHIS_part');

对于大型表的在线重定义可以通过启用并行以提高性能(此步骤可选)
ALTER SESSION FORCE PARALLEL DML PARALLEL degree-of-parallelism;
ALTER SESSION FORCE PARALLEL QUERY PARALLEL degree-of-parallelism;

4、newsduser查看分区情况
SQL> select table_name,partitioning_type,partition_count from user_part_tables;
SQL> select partition_name,high_value,tablespace_name from user_tab_partitions where table_name='&1' order by partition_position;

5、创建分区表索引
SQL> create index IDX_PART_POSTXNJNLHIS_LOGDAT on POSTXNJNLHIS(LOGDAT) tablespace PISINDX local;
SQL> select table_name,index_name,partitioning_type,partition_count,locality from user_part_indexes;

TABLE_NAME INDEX_NAME PARTITI PARTITION_COUNT LOCALI
------------------------------ ------------------------------ ------- --------------- ------
POSTXNJNLHIS IDX_PART_POSTXNJNLHIS_LOGDAT RANGE 14 LOCAL

SQL> select index_name,partition_name,status from user_ind_partitions;

INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ --------
IDX_PART_POSTXNJNLHIS_SYSDAT P_201503 USABLE
IDX_PART_POSTXNJNLHIS_SYSDAT P_201504 USABLE
IDX_PART_POSTXNJNLHIS_SYSDAT P_201505 USABLE
IDX_PART_POSTXNJNLHIS_SYSDAT P_201501 USABLE
IDX_PART_POSTXNJNLHIS_SYSDAT P_201502 USABLE
IDX_PART_POSTXNJNLHIS_SYSDAT P_BEFORE_201501 USABLE
IDX_PART_POSTXNJNLHIS_SYSDAT P_201506 USABLE
IDX_PART_POSTXNJNLHIS_SYSDAT P_201507 USABLE
IDX_PART_POSTXNJNLHIS_SYSDAT P_201508 USABLE
IDX_PART_POSTXNJNLHIS_SYSDAT P_201509 USABLE
IDX_PART_POSTXNJNLHIS_SYSDAT P_201510 USABLE

INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ --------
IDX_PART_POSTXNJNLHIS_SYSDAT P_201511 USABLE
IDX_PART_POSTXNJNLHIS_SYSDAT P_201512 USABLE
IDX_PART_POSTXNJNLHIS_SYSDAT P_MAX USABLE

6、删除中间表
SQL> TRUNCATE TABLE POSTXNJNLHIS_part;
SQL> DROP TABLE POSTXNJNLHIS_part PURGE;

7、分区测试
SQL> select * from POSTXNJNLHIS where logdat>='20150303' and logdat<='20150404';

Execution Plan
----------------------------------------------------------
Plan hash value: 1931887777

-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2500 | 4833K| 47 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE ITERATOR | | 2500 | 4833K| 47 (0)| 00:00:01 | 4 | 5 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| POSTXNJNLHIS | 2500 | 4833K| 47 (0)| 00:00:01 | 4 | 5 |
|* 3 | INDEX RANGE SCAN | IDX_PART_POSTXNJNLHIS_LOGDAT | 2500 | | 5 (0)| 00:00:01 | 4 | 5 |
-----------------------------------------------------------------------------------------------------------------------------------
通过分区索引，只扫描4、5分区

注意：关于约束，因为是使用CTAS方式，故只有显示的NOT NULL能够被带到新表，而隐式的NOT NULL如主键是不会被带到新表。此外索引、check、分区、默认值也不会被带到新表。

以下转载一条查询分区表和分区键的SQL：

SELECT p.table_name AS 表名,
decode(p.partitioning_key_count, 1, '主分区') AS 分区类型,
p.partitioning_type AS 分区类型,
p.column_name AS 分区键,
decode(nvl(q.subpartitioning_key_count, 0),
0,
'无子分区',
1,
'子分区') AS 有无子分区,
q.subpartitioning_type AS 子分区类型,
q.column_name AS 子分区键
FROM (SELECT a.table_name,
a.partitioning_type,
b.column_name,
a.partitioning_key_count
FROM user_part_tables a, user_part_key_columns b
WHERE a.table_name = b.NAME
AND b.object_type = 'TABLE') p,
(SELECT a.table_name,
a.subpartitioning_type,
b.column_name,
a.subpartitioning_key_count
FROM user_part_tables a, user_subpart_key_columns b
WHERE a.table_name = b.NAME
AND a.subpartitioning_key_count <> 0
AND b.object_type = 'TABLE') q
WHERE p.table_name = q.table_name(+)
ORDER BY 5, 4, 1;