--按有表分区的表自动生成表空间处理语句
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,