取得所有含分区的表对应的表空间
--按有表分区的表自动生成表空间处理语句
SELECT 'PROMPT 删除表空间(' || PARTITION_NAME || ')
DROP TABLESPACE ' || PARTITION_NAME ||
' INCLUDING CONTENTS AND DATAFILES;',
'CREATE TABLESPACE ' || PARTITION_NAME || '
DATAFILE ''E:\ORADATA\GISAP\' || PARTITION_NAME ||
'.DBF'' SIZE 8M
AUTOEXTEND ON NEXT 8M MAXSIZE UNLIMITED
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO;',
'ALTER USER AGGPS QUOTA UNLIMITED ON ' || PARTITION_NAME || ';'
FROM (SELECT DISTINCT T.TABLE_NAME,
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(T.TABLE_NAME, 'T_'),
'_'),
'UNIT'),
'PONSESTATUS'),
'UNI') AS PARTITION_NAME
FROM USER_TAB_PARTITIONS T
WHERE T.TABLE_NAME NOT LIKE '%BIN%'
ORDER BY T.TABLE_NAME)
--取得所有含分区的表对应的表空间
SELECT DISTINCT T.TABLE_NAME,
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(T.TABLE_NAME, 'T_'),
'_'),
'UNIT'),
'PONSESTATUS'),
'UNI') AS PARTITION_NAME
FROM USER_TAB_PARTITIONS T
WHERE T.TABLE_NAME NOT LIKE '%BIN%'
ORDER BY T.TABLE_NAME
--动态处理分区
CREATE OR REPLACE PROCEDURE P_MAINTENANCE_PARTITION_BYWSQ
--
--编制:WSQ
--功能：指定维护分区。即自动删除过时分区，并为分区表新增下一个月的分区 --
--数据处理机制：所有数据保留一年，一年以后的数据将会被删除，所有表每天一个分区
--
IS
L_SQL VARCHAR2(2000);
L_BEGIN_DATE DATE;
L_END_DATE DATE;
L_BEGIN_TIME DATE;
BEGIN
L_BEGIN_DATE := TRUNC(LAST_DAY(SYSDATE)) + 1; --下个月第一天
L_END_DATE := LAST_DAY(L_BEGIN_DATE); --下个月最后一天
FOR V IN (SELECT DISTINCT T.TABLE_NAME,
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(T.TABLE_NAME,
'T_'),
'_'),
'UNIT'),
'PONSESTATUS'),
'UNI') AS PARTITION_NAME
FROM USER_TAB_PARTITIONS T
WHERE T.TABLE_NAME NOT LIKE '%BIN%'
ORDER BY T.TABLE_NAME) LOOP
--对 V.TABLE_NAME 指定表名进行分区维护部分开始--
--为 V.TABLE_NAME 增加下一个月分区,每天一个分区
L_BEGIN_TIME := L_BEGIN_DATE;
LOOP
EXIT WHEN(L_BEGIN_TIME > L_END_DATE);
BEGIN
L_SQL := 'ALTER TABLE ' || V.TABLE_NAME ||
' ADD PARTITION P_TBL_' || V.PARTITION_NAME || '_' ||
TO_CHAR(L_BEGIN_TIME, 'YYYYMMDD') ||
' VALUES LESS THAN (
TO_DATE(' || '''' ||
TO_CHAR(L_BEGIN_TIME, 'YYYY-MM-DD') || '''' ||
&nb
补充：综合编程 , 其他综合 ,
