取得所有含分区的表对应的表空间

--按有表分区的表自动生成表空间处理语句
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') || '''' ||
                        ',''YYYY-MM-DD'')) TABLESPACE ' || V.PARTITION_NAME || '';
        L_BEGIN_TIME := L_BEGIN_TIME + 1;
        EXECUTE IMMEDIATE L_SQL;
      EXCEPTION
        WHEN OTHERS THEN
          INSERT INTO T_SQL_DEBUG_LOG
            (SQL_DEBUG_LOG_ID, SQL_DEBUG_LOG, REMARKS, STAMP)
          VALUES
            (SNT_SQL_DEBUG_LOG.NEXTVAL,
             L_SQL,
             'ADD PARTITION P_TBL_' || V.PARTITION_NAME || '分区错误',
             SYSDATE);
          COMMIT;
          L_BEGIN_TIME := L_BEGIN_TIME + 1;
      END;
    END LOOP;
    --清理过时数据，此处设置保留十二个月
    FOR VV IN (SELECT TABLE_NAME, PARTITION_NAME
                 FROM USER_TAB_PARTITIONS
                WHERE TO_DATE(SUBSTR(PARTITION_NAME, -8), 'YYYY-MM-DD') <
                      ADD_MONTHS(TRUNC(SYSDATE, 'MONTH'), -12)
                  AND TABLE_NAME = V.TABLE_NAME) LOOP
      BEGIN
        --就算要删除的分区不在，程序还是可以继续执行
        L_SQL := 'ALTER TABLE  ' || V.TABLE_NAME || ' DROP PARTITION ' ||
                 VV.PARTITION_NAME;
        EXECUTE IMMEDIATE L_SQL;
      EXCEPTION
        WHEN OTHERS THEN
          BEGIN
            INSERT INTO T_SQL_DEBUG_LOG
              (SQL_DEBUG_LOG_ID, SQL_DEBUG_LOG, REMARKS, STAMP)
            VALUES
              (SNT_SQL_DEBUG_LOG.NEXTVAL,
               L_SQL,
               '删除 ' || VV.PARTITION_NAME || '分区错误',
               SYSDATE);
            COMMIT;
            NULL;
          END;
      END;
    END LOOP;
    --对 V.TABLE_NAME 进行分区维护部分结束--
  END LOOP;
END P_MAINTENANCE_PARTITION_BYWSQ;