DAILY 테이블 사이즈 기록 프로시져

CREATE OR REPLACE PROCEDURE SP_DAILY_TBL_SIZE
/*
-- 2019/10/11
--  일별 테이블 별 사이즈 증가   
  CREATE TABLE TB_TBL_DAILY_SIZE
(
  OWNER       VARCHAR2(30 BYTE)                 NOT NULL,
  TABLE_NAME  VARCHAR2(100 BYTE)                NOT NULL,
  TABLE_SIZE  VARCHAR2(10),
  OBJECT_TYPE VARCHAR2(10),
  CREATED     DATE                              DEFAULT SYSDATE               NOT NULL
)
;


COMMENT ON TABLE TB_TBL_DAILY_SIZE IS '테이블 일일 사이즈';
COMMENT ON COLUMN TB_TBL_DAILY_SIZE.OWNER IS 'OWNER';
COMMENT ON COLUMN TB_TBL_DAILY_SIZE.TABLE_NAME IS '테이블명';
COMMENT ON COLUMN TB_TBL_DAILY_SIZE.TABLE_SIZE IS '테이블 사이즈(GB)';
COMMENT ON COLUMN TB_TBL_DAILY_SIZE.OBJECT_TYPE IS '타입';
COMMENT ON COLUMN TB_TBL_DAILY_SIZE.CREATED IS '생성일자';
*/
(   
    p_exec              in number default 1     -- 0 :SQL만 출력 , 1:실행
) authid current_user
IS
    V_OWNER         VARCHAR2(40);
    V_TABLE_NAME    VARCHAR2(40);
    V_TABLE_SIZE     NUMBER(22);

            
    V_SQL      VARCHAR2(1000);    
    V_MSG       long;        
BEGIN


    DBMS_OUTPUT.ENABLE;   

        -- 블록 에러 발생시에도 계속 실행토록 
        BEGIN  
            INSERT INTO TB_TBL_DAILY_SIZE(OWNER,TABLE_NAME,OBJECT_TYPE,TABLE_SIZE)              
            SELECT  A.OWNER
                  , A.SEGMENT_NAME
                  , A.SEGMENT_TYPE      
                  , ROUND(SUM(A.BYTES)/1024/1024) "SIZE_MB"      
              FROM DBA_SEGMENTS A
                 , DBA_TABLES B
             WHERE A.SEGMENT_NAME = B.TABLE_NAME
               AND A.SEGMENT_TYPE IN ('LOB','INDEX','TABLE','TABLE PARTITION')
               AND A.OWNER = B.OWNER
               AND A.OWNER IN ('SCOTT','AMEX_SCOTT','WEB','TIGER','AMEX_TIGER','TIGERHT')
            --   AND EXISTS (SELECT 1
            --                 FROM MIG_TABLES C
            --                WHERE C.TABLE_NAME = B.TABLE_NAME
            --                  AND C.OWNER      = B.OWNER
            --                  AND C.SYSTEM_CODE = 'LC1'
            --                  AND C.USE_YN = 'Y'
            --              )     
             GROUP BY  A.OWNER
                    , A.SEGMENT_NAME
                    , A.SEGMENT_TYPE;            
  
            COMMIT;
        EXCEPTION
            WHEN OTHERS THEN
                V_MSG := 'ERROR : ['|| to_char(SQLCODE) ||']'|| substr(SQLERRM,1,500);
                ROLLBACK;        
        END;       

EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('ERR MESSAGE : ' || SQLERRM);

END;
/
DAILY 테이블 사이즈 기록 프로시져

DB CAFE
