下面的脚本为新建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