扩展表分区用

--以system用户
--创建通用表空间
CREATE TABLESPACE SRPT_DAT_ALL DATAFILE
  '/datafs8/srpt1008/data/SRPT_DAT_ALL.dbf' SIZE 3072M AUTOEXTEND ON NEXT 1024M MAXSIZE UNLIMITED
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K
BLOCKSIZE 16K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

CREATE TABLESPACE SRPT_IND_ALL DATAFILE
  '/datafs8/srpt1008/data/SRPT_IND_ALL.dbf' SIZE 1024M AUTOEXTEND ON NEXT 1024M MAXSIZE UNLIMITED
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K
BLOCKSIZE 16K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

--以system用户
--生成改表的语句（每次更新完版本，进行作业调度前都要执行，看看是否有新增表,删除类似语句：ALTER INDEX SRPT.BIN$WtYIt7QUCFLgRAAUT34uhA==$0 rebuild partition PART_999912 TABLESPACE SRPT_IND_ALL;）
SELECT DISTINCT 'ALTER TABLE '
|| TABlE_OWNER||'.'||TABLE_NAME ||' ADD PARTITION PART_999912  values less than (''999912'') tablespace '
||'SRPT_DAT_ALL'
||';'
FROM DBA_TAB_PARTITIONS
WHERE( PARTITION_NAME LIKE '%200799' or PARTITION_NAME LIKE '%200899' or PARTITION_NAME LIKE '%200999' or PARTITION_NAME LIKE '%201099')
AND TABLE_NAME NOT IN ( SELECT DISTINCT TABLE_NAME
FROM DBA_TAB_PARTITIONS
WHERE PARTITION_NAME LIKE '%999912')
and TABLE_NAME NOT LIKE 'BIN$%';

--生成改索引的语句（每次更新完版本，进行作业调度前都要执行，看看是否有新增表）
SELECT DISTINCT 'ALTER INDEX '
|| INDEX_OWNER||'.'||INDEX_NAME ||' rebuild partition PART_999912 TABLESPACE SRPT_IND_ALL;'
FROM DBA_IND_PARTITIONS
WHERE ( PARTITION_NAME LIKE '%200799' or PARTITION_NAME LIKE '%200899' or PARTITION_NAME LIKE '%200999' or PARTITION_NAME LIKE '%201099')
AND INDEX_NAME NOT IN ( SELECT DISTINCT INDEX_NAME
FROM DBA_IND_PARTITIONS
WHERE PARTITION_NAME LIKE '%999912')
AND INDEX_NAME NOT LIKE 'BIN$%';