[ORACLE] 통계정보 저장용 프로시져 및 스케줄링

-- 테이블 생성(ADMINUSR.STATTAB :  통계정보가 백업될 파티션테이블)

CREATE TABLE ADMINUSR.STATTAB
(
    STATID  VARCHAR2(30),
    TYPE    CHAR(1),
    VERSION NUMBER,
    FLAGS   NUMBER,
    C1      VARCHAR2(30),
    C2      VARCHAR2(30),
    C3      VARCHAR2(30),
    C4      VARCHAR2(30),
    C5      VARCHAR2(30),
    N1      NUMBER,
    N2      NUMBER,
    N3      NUMBER,
    N4      NUMBER,
    N5      NUMBER,
    N6      NUMBER,
    N7      NUMBER,
    N8      NUMBER,
    N9      NUMBER,
    N10     NUMBER,
    N11     NUMBER,
    N12     NUMBER,
    D1      DATE,
    R1      RAW(32),
    R2      RAW(32),
    CH1     VARCHAR2(1000)
)
TABLESPACE WORKD
NOLOGGING
PARTITION BY LIST (STATID)
(
  PARTITION D20221227 VALUES ('SCOTT20221227', 'TESTUSER20221227')
    TABLESPACE WORKD,
  PARTITION D20221228 VALUES ('SCOTT20221228', 'TESTUSER20221228')
    TABLESPACE WORKD,
  PARTITION D20221229 VALUES ('SCOTT20221229', 'TESTUSER20221229')
    TABLESPACE WORKD,
  PARTITION D20221230 VALUES ('SCOTT20221230', 'TESTUSER20221230')
    TABLESPACE WORKD,
  PARTITION D20221231 VALUES ('SCOTT20221231', 'TESTUSER20221231')
    TABLESPACE WORKD,
    PARTITION D20230101 VALUES ('SCOTT20230101', 'TESTUSER20230101')
    TABLESPACE WORKD
);


CREATE INDEX ADMINUSR.STATTAB
ON ADMINUSR.STATTAB
(
    STATID,
    TYPE,
    C5,
    C1,
    C2,
   C3,
    C4,
    VERSION
)
LOCAL
TABLESPACE WORKD
NOLOGGING;


--통계정보수집 권한 부여
grant ANALYZE ANY to "ADMINUSR";

--프로시져 생성(SCOTT, TESTUSER  는 테스트했던 스키마명이며 통계정보 수집을 원하는 스키마 명으로 프로시져 수정 필요 )

  PROCEDURE CREATE_SNAP_GATHER IS
    vStmt VARCHAR2(300);
    vSeq NUMBER := 0;
    vSnapshotKeepDate NUMBER := 5;
    vSnapshotToDayCHAR VARCHAR2(8) := TO_CHAR(SYSDATE,'YYYYMMDD');
    vDropPartition VARCHAR2(100) := NULL;
  BEGIN
    /* 스키마 통계 정보 EXPORT - STATID => [OWNER]||[YYYYMMDD] */
    EXECUTE IMMEDIATE 'ALTER TABLE SCOTT.STATTAB DROP PARTITION D'||TO_CHAR(SYSDATE-vSnapshotKeepDate,'YYYYMMDD');
    EXECUTE IMMEDIATE 'ALTER TABLE SCOTT.STATTAB ADD PARTITION D'||vSnapshotToDayCHAR||' VALUES('||
    '''SCOTT'||vSnapshotToDayCHAR||''','||
    '''TESTUSER'||vSnapshotToDayCHAR||''''||
    ') NOLOGGING';
    DBMS_STATS.EXPORT_SCHEMA_STATS(ownname=>'SCOTT',stattab=>'STATTAB',statid=>'SCOTT'||TO_CHAR(SYSDATE,'YYYYMMDD'),statown=>'ADMINUSR');
    DBMS_STATS.EXPORT_SCHEMA_STATS(ownname=>'TESTUSER',stattab=>'STATTAB',statid=>'TESTUSER'||TO_CHAR(SYSDATE,'YYYYMMDD'),statown=>'ADMINUSR');
    COMMIT;


        

        EXCEPTION
        WHEN NO_DATA_FOUND THEN
          NULL;
      END;
  END CREATE_SNAP_GATHER;



--스케줄 등록

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name                                          =>       'DAILY_CREATE_SNAP_GATHER',
job_type                                =>       'STORED_PROCEDURE',
job_action                              =>       'ADMINUSR.CREATE_SNAP_GATHER',
number_of_arguments     =>  0,
start_date                              =>       sysdate,
repeat_interval              =>       'FREQ=DAILY;BYHOUR=6;BYMINUTE=30;BYSECOND=0;',
end_date                                          =>       NULL,
job_class                     =>  'DEFAULT_JOB_CLASS',
enabled                                 =>  TRUE,
auto_drop                              =>  FALSE,
comments                              =>  'Create STATTAB of CALL Schemas's gather Daily');
END;
/






-- 백업한 통계정보 복구방법(SCOTT 스키마가 소유하고있는 TESTTABLE 테이블를 STATTAB이라는 통계정보 테이블에 저장되어있는 통계정보를 ADMINUSR계정으 통하여 복구) 
Exec DBMS_STATS.IMPORT_TABLE_STATS(ownname => 'SCOTT',Tabname => 'TESTTABLE', Stattab =>'STATTAB',Statid => 'SCOTT20230102', Statown => 'ADMINUSR');


-- 백업한 통계정보 복구방법(스키마)
Exec DBMS_STATS.IMPORT_TABLE_STATS(ownname => 'SCOTT', Stattab =>'STATTAB',Statid => 'SCOTT20230102', Statown => 'ADMINUSR');


-- 현재 통계정보 삭제
exec  dbms_stats.delete_table_stats( ownname => 'SCOTT', tabname => 'TESTTABLE' ) ;