下面的脚本为新建2014年一年的分区。可根据不同的需求修改下。
sql:
select 'alter table ' || b.table_name || ' add partition PA_' || TO_CHAR(a - 1, 'YYYYMM') || ' values less than (TO_DATE( ''' || TO_CHAR(a, 'YYYY-MM-DD HH24:MI:SS') || ''',''YYYY-MM-DD HH24:MI:SS'');' from (select add_months(trunc(DATE '2014-01-01', 'month'), level) a from dual connect by level < 13), (select owner || '.' || table_name table_name from dba_tables where table_name in ('T_WCP_FRZ_CREDENTIAL', 'T_WCP_ACCT_QUERY_CREDENTIAL')) b union all select 'alter table ' || c.owner||'.'||c.table_name || ' add partition PA_MAX values less than (MAXVALUE);' from dual,(select owner, table_name from dba_tables where table_name in ('T_WCP_FRZ_CREDENTIAL','T_WCP_ACCT_QUERY_CREDENTIAL')) c order by 1
执行的结果:
alter table EXCH.T_WCP_ACCT_QUERY_CREDENTIAL add partition PA_201401 values less than (TO_DATE( '2014-02-01 00:00:00','YYYY-MM-DD HH24:MI:SS'); alter table EXCH.T_WCP_ACCT_QUERY_CREDENTIAL add partition PA_201402 values less than (TO_DATE( '2014-03-01 00:00:00','YYYY-MM-DD HH24:MI:SS'); alter table EXCH.T_WCP_ACCT_QUERY_CREDENTIAL add partition PA_201403 values less than (TO_DATE( '2014-04-01 00:00:00','YYYY-MM-DD HH24:MI:SS'); alter table EXCH.T_WCP_ACCT_QUERY_CREDENTIAL add partition PA_201404 values less than (TO_DATE( '2014-05-01 00:00:00','YYYY-MM-DD HH24:MI:SS'); alter table EXCH.T_WCP_ACCT_QUERY_CREDENTIAL add partition PA_201405 values less than (TO_DATE( '2014-06-01 00:00:00','YYYY-MM-DD HH24:MI:SS'); alter table EXCH.T_WCP_ACCT_QUERY_CREDENTIAL add partition PA_201406 values less than (TO_DATE( '2014-07-01 00:00:00','YYYY-MM-DD HH24:MI:SS'); alter table EXCH.T_WCP_ACCT_QUERY_CREDENTIAL add partition PA_201407 values less than (TO_DATE( '2014-08-01 00:00:00','YYYY-MM-DD HH24:MI:SS'); alter table EXCH.T_WCP_ACCT_QUERY_CREDENTIAL add partition PA_201408 values less than (TO_DATE( '2014-09-01 00:00:00','YYYY-MM-DD HH24:MI:SS'); alter table EXCH.T_WCP_ACCT_QUERY_CREDENTIAL add partition PA_201409 values less than (TO_DATE( '2014-10-01 00:00:00','YYYY-MM-DD HH24:MI:SS'); alter table EXCH.T_WCP_ACCT_QUERY_CREDENTIAL add partition PA_201410 values less than (TO_DATE( '2014-11-01 00:00:00','YYYY-MM-DD HH24:MI:SS'); alter table EXCH.T_WCP_ACCT_QUERY_CREDENTIAL add partition PA_201411 values less than (TO_DATE( '2014-12-01 00:00:00','YYYY-MM-DD HH24:MI:SS'); alter table EXCH.T_WCP_ACCT_QUERY_CREDENTIAL add partition PA_201412 values less than (TO_DATE( '2015-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS'); alter table EXCH.T_WCP_ACCT_QUERY_CREDENTIAL add partition PA_MAX values less than (MAXVALUE); alter table EXCH.T_WCP_FRZ_CREDENTIAL add partition PA_201401 values less than (TO_DATE( '2014-02-01 00:00:00','YYYY-MM-DD HH24:MI:SS'); alter table EXCH.T_WCP_FRZ_CREDENTIAL add partition PA_201402 values less than (TO_DATE( '2014-03-01 00:00:00','YYYY-MM-DD HH24:MI:SS'); alter table EXCH.T_WCP_FRZ_CREDENTIAL add partition PA_201403 values less than (TO_DATE( '2014-04-01 00:00:00','YYYY-MM-DD HH24:MI:SS'); alter table EXCH.T_WCP_FRZ_CREDENTIAL add partition PA_201404 values less than (TO_DATE( '2014-05-01 00:00:00','YYYY-MM-DD HH24:MI:SS'); alter table EXCH.T_WCP_FRZ_CREDENTIAL add partition PA_201405 values less than (TO_DATE( '2014-06-01 00:00:00','YYYY-MM-DD HH24:MI:SS'); alter table EXCH.T_WCP_FRZ_CREDENTIAL add partition PA_201406 values less than (TO_DATE( '2014-07-01 00:00:00','YYYY-MM-DD HH24:MI:SS'); alter table EXCH.T_WCP_FRZ_CREDENTIAL add partition PA_201407 values less than (TO_DATE( '2014-08-01 00:00:00','YYYY-MM-DD HH24:MI:SS'); alter table EXCH.T_WCP_FRZ_CREDENTIAL add partition PA_201408 values less than (TO_DATE( '2014-09-01 00:00:00','YYYY-MM-DD HH24:MI:SS'); alter table EXCH.T_WCP_FRZ_CREDENTIAL add partition PA_201409 values less than (TO_DATE( '2014-10-01 00:00:00','YYYY-MM-DD HH24:MI:SS'); alter table EXCH.T_WCP_FRZ_CREDENTIAL add partition PA_201410 values less than (TO_DATE( '2014-11-01 00:00:00','YYYY-MM-DD HH24:MI:SS'); alter table EXCH.T_WCP_FRZ_CREDENTIAL add partition PA_201411 values less than (TO_DATE( '2014-12-01 00:00:00','YYYY-MM-DD HH24:MI:SS'); alter table EXCH.T_WCP_FRZ_CREDENTIAL add partition PA_201412 values less than (TO_DATE( '2015-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS'); alter table EXCH.T_WCP_FRZ_CREDENTIAL add partition PA_MAX values less than (MAXVALUE);
附件一个查找数据库中range分区的分区表(我们系统基本是时间的range分区,查找出来的表还需仔细核对,防止出现误执行):
select OWNER, TABLE_NAME from dba_part_tables c where c.partitioning_type = 'RANGE' and table_name not like 'BIN$%' and owner not in ('SYS', 'SYSTEM', 'DBSNMP', 'SYSMAN', 'OUTLN', 'MDSYS', 'ORDSYS', 'EXFSYS', 'DMSYS', 'WMSYS', 'CTXSYS', 'ANONYMOUS', 'XDB', 'ORDPLUGINS', 'OLAPSYS', 'PUBLIC') order by 1