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;
/