普通表转化成分区表一例(在线重定义分区表)

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;

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值