[ORACLE] 스키마 정보

--테이블 스키마 정보

SELECT TABLE_NAME

     , TABLESPACE_NAME

     , PCT_FREE

     , PCT_USED

     , INI_TRANS

     , MAX_TRANS

     , INITIAL_EXTENT

     , MIN_EXTENTS

FROM ALL_TABLES@DSACMA2 ASIS

WHERE OWNER = 'SCOOT'  AND TABLE_NAME = 'TIGER'  

;

 

--테이블 컬럼 정보

 SELECT A.TABLE_NAME

     , C.COMMENTS

     , B.COLUMN_NAME

     , DECODE(D.COLUMN_NAME, NULL, '', 'PK') AS PK_YN

     , B.DATA_TYPE

     , B.DATA_LENGTH

     , B.COLUMN_ID

  FROM ALL_TABLES A

     , ALL_TAB_COLUMNS B

     , ALL_TAB_COMMENTS C

     , ALL_IND_COLUMNS D

 WHERE A.TABLE_NAME = B.TABLE_NAME

   AND A.OWNER = 'INST1'

   AND A.TABLE_NAME = 'TSMFAAE08'

   AND A.TABLE_NAME = C.TABLE_NAME

   AND B.TABLE_NAME = D.TABLE_NAME(+)

   AND B.COLUMN_NAME = D.COLUMN_NAME(+)

 ORDER BY A.TABLE_NAME, B.COLUMN_ID;

;

--GRANT 일괄 생성

SELECT 'GRANT ' || PRIVILEGE || ' ON ' || OWNER || '.' || TABLE_NAME || '_EDW' || ' TO ' || GRANTEE || ';'

FROM DBA_TAB_PRIVS@DSACMA2

WHERE GRANTOR='SCOOT'

AND   TABLE_NAME = 'TABLE'

ORDER BY GRANTEE ASC, PRIVILEGE ASC