-- RECOMPILE 
------------------------------------------------------------------------------------------------
SELECT  O.STATUS
,       O.OBJECT_TYPE
,       O.OBJECT_NAME
,       'ALTER ' || O.OBJECT_TYPE || ' ' || OWNER || '.' || O.OBJECT_NAME || ' COMPILE;' AS RECOMPILE_SCRIPT
,       O.*
FROM ALL_OBJECTS O
WHERE O.OWNER IN (SYS_CONTEXT('USERENV', 'SESSION_USER'))
AND O.OBJECT_TYPE IN ('VIEW', 'PROCEDURE', 'FUNCTION', 'TRIGGER')
AND O.STATUS = 'INVALID';



-- INDEX REBUILD
------------------------------------------------------------------------------------------------
SELECT  T.STATUS
,       T.INDEX_TYPE
,       T.TABLE_NAME
,       T.INDEX_NAME
,       T.UNIQUENESS
,       T.LEAF_BLOCKS
,       T.DISTINCT_KEYS
,       T.AVG_LEAF_BLOCKS_PER_KEY
,       T.AVG_DATA_BLOCKS_PER_KEY
,       T.CLUSTERING_FACTOR
,       T.NUM_ROWS
,       T.LAST_ANALYZED
,       'ALTER INDEX ' || T.INDEX_NAME || ' REBUILD TABLESPACE ' || T.TABLESPACE_NAME || ';' AS REBUILD_SCRIPT
FROM ALL_INDEXES T
WHERE T.OWNER = SYS_CONTEXT('USERENV', 'SESSION_USER');



-- 뷰
------------------------------------------------------------------------------------------------
ELECT  'CREATE OR REPLACE FORCE VIEW ' || T.OWNER || '.' || T.VIEW_NAME || CHR(10) || ' AS' || CHR(10) AS SCRIPT
,       T.TEXT
,       ';'
FROM ALL_VIEWS T
WHERE T.OWNER = SYS_CONTEXT('USERENV', 'SESSION_USER');





-- 프로시저, 함수, 트리거
------------------------------------------------------------------------------------------------
SELECT  T.NAME
,       T.TYPE
,       T.LINE
,       DECODE(T.LINE, 1, '/' || CHR(10) || 'CREATE OR REPLACE ') || REPLACE(T.TEXT, CHR(10), '') AS TEXT
FROM ALL_SOURCE T
WHERE T.OWNER = SYS_CONTEXT('USERENV', 'SESSION_USER')
AND T.TYPE IN ('PROCEDURE', 'FUNCTION', 'TRIGGER', 'PACKAGE', 'PACKAGE BODY')
ORDER BY T.NAME, T.TYPE, T.LINE;




-- 권한
------------------------------------------------------------------------------------------------
SELECT  T.*
,       'GRANT ' || T.PRIVILEGE || ' ON ' || T.OWNER || '.' || T.TABLE_NAME || ' TO ' || T.GRANTEE || ';' AS SCRIPT
FROM USER_TAB_PRIVS T
WHERE T.OWNER = SYS_CONTEXT('USERENV', 'SESSION_USER');




-- COLUMN
------------------------------------------------------------------------------------------------
SELECT  'ALTER TABLE ' || T.OWNER || '.' || T.TABLE_NAME || ' ADD ' || T.COLUMN_NAME || ' ' || T.DATA_TYPE || '(' || T.CHAR_LENGTH || DECODE(T.CHAR_USED, 'B', ' BYTE', 'C', ' CHAR') || ');'       AS ADD_SCRIPT
,       'ALTER TABLE ' || T.OWNER || '.' || T.TABLE_NAME || ' MODIFY ' || T.COLUMN_NAME || ' ' || T.DATA_TYPE || '(' || T.CHAR_LENGTH || DECODE(T.CHAR_USED, 'B', ' BYTE', 'C', ' CHAR') || ');'    AS MODIFY_SCRIPT
,       'ALTER TABLE ' || T.OWNER || '.' || T.TABLE_NAME || ' DROP COLUMN ' || T.COLUMN_NAME || ';'                                                                                                 AS DROP_SCRIPT
,       'ALTER TABLE ' || T.OWNER || '.' || T.TABLE_NAME || ' RENAME COLUMN ' || T.COLUMN_NAME || ' TO ' || T.COLUMN_NAME  || ';'                                                                   AS RENAME_SCRIPT
,       'COMMENT ON COLUMN ' || T.OWNER || '.' || T.TABLE_NAME || '.' || T.COLUMN_NAME || ' IS ' || CHR(39) || C.COMMENTS || CHR(39) || ';'                                                         AS COMMENT_SCRIPT
FROM ALL_TAB_COLUMNS T
JOIN ALL_COL_COMMENTS C ON  C.OWNER = T.OWNER
                        AND C.TABLE_NAME = T.TABLE_NAME
                        AND C.COLUMN_NAME = T.COLUMN_NAME
WHERE T.OWNER = SYS_CONTEXT('USERENV', 'SESSION_USER')
ORDER BY T.TABLE_NAME, T.COLUMN_ID;



-- SEQUENCE
-------------------------------------------------------------------------------------------------
SELECT  T.SEQUENCE_NAME
,       'CREATE SEQUENCE ' || T.SEQUENCE_OWNER || '.' || T.SEQUENCE_NAME || ' INCREMENT BY 1 START WITH 1 NOMAXVALUE NOMINVALUE NOCYCLE NOORDER;'                                                                                                   AS CREATE_SCRIPT
,       'CREATE SEQUENCE ' || T.SEQUENCE_OWNER || '.' || T.SEQUENCE_NAME || ' MINVALUE ' || T.MIN_VALUE || ' MAXVALUE ' || T.MAX_VALUE || ' INCREMENT BY ' || T.INCREMENT_BY || ' START WITH ' || TO_CHAR(T.LAST_NUMBER + 1) || ' NOCYCLE NOORDER;' AS MODIFY_SCRIPT 
,       'DROP SEQUENCE '   || T.SEQUENCE_OWNER || '.' || T.SEQUENCE_NAME || ';'                                                                                                                                                                     AS DROP_SCRIPT
FROM ALL_SEQUENCES T
WHERE T.SEQUENCE_OWNER = SYS_CONTEXT('USERENV', 'SESSION_USER');