在华为 GaussDB T 的初始安装目录 scripts 下，initview.sql 记录了创建视图的过程，这其中有 135个视图，198个同义词。

收入如下脚本内容，供参考：

CREATE OR REPLACE PUBLIC SYNONYM DV_PL_REFSQLS FOR SYS.DV_PL_REFSQLS
/

CREATE OR REPLACE PUBLIC SYNONYM DV_REPL_STATUS FOR SYS.DV_REPL_STATUS
/

CREATE OR REPLACE PUBLIC SYNONYM DV_GMA_STATS FOR SYS.DV_GMA_STATS
/

CREATE OR REPLACE PUBLIC SYNONYM DV_LIBRARY_CACHE FOR SYS.DV_LIBRARY_CACHE
/

CREATE OR REPLACE PUBLIC SYNONYM DV_UNDO_SEGMENTS FOR SYS.DV_UNDO_SEGMENTS
/

CREATE OR REPLACE PUBLIC SYNONYM DV_TEMP_UNDO_SEGMENT FOR SYS.DV_TEMP_UNDO_SEGMENT
/

CREATE OR REPLACE PUBLIC SYNONYM DV_LONG_SQL FOR SYS.DV_LONG_SQL
/

CREATE OR REPLACE PUBLIC SYNONYM DV_SQL_POOL FOR SYS.DV_SQL_POOL
/

CREATE OR REPLACE PUBLIC SYNONYM DV_OPEN_CURSORS FOR SYS.DV_OPEN_CURSORS
/

CREATE OR REPLACE PUBLIC SYNONYM DV_DC_POOLS FOR SYS.DV_DC_POOLS
/

CREATE OR REPLACE PUBLIC SYNONYM DV_PL_MANAGER FOR SYS.DV_PL_MANAGER
/

CREATE OR REPLACE PUBLIC SYNONYM DV_FREE_SPACE FOR SYS.DV_FREE_SPACE
/

CREATE OR REPLACE PUBLIC SYNONYM DV_HA_SYNC_INFO FOR SYS.DV_HA_SYNC_INFO
/

CREATE OR REPLACE PUBLIC SYNONYM DV_HBA FOR SYS.DV_HBA
/

CREATE OR REPLACE PUBLIC SYNONYM DV_INSTANCE FOR SYS.DV_INSTANCE
/

CREATE OR REPLACE PUBLIC SYNONYM DV_RUNNING_JOBS FOR SYS.DV_RUNNING_JOBS
/

CREATE OR REPLACE PUBLIC SYNONYM DV_LOG_FILES FOR SYS.DV_LOG_FILES
/
CREATE OR REPLACE PUBLIC SYNONYM DV_SESSIONS FOR SYS.DV_SESSIONS
/
CREATE OR REPLACE PUBLIC SYNONYM DV_BUFFER_POOLS FOR SYS.DV_BUFFER_POOLS
/

CREATE OR REPLACE PUBLIC SYNONYM DV_PARAMETERS FOR SYS.DV_PARAMETERS
/

CREATE OR REPLACE PUBLIC SYNONYM DV_USER_PARAMETERS FOR SYS.DV_USER_PARAMETERS
/
GRANT SELECT ON DV_USER_PARAMETERS TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM DV_TEMP_POOLS FOR SYS.DV_TEMP_POOLS
/
CREATE OR REPLACE PUBLIC SYNONYM DV_OBJECT_CACHE FOR DV_OBJECT_CACHE
/
CREATE OR REPLACE PUBLIC SYNONYM DV_LOCKS FOR SYS.DV_LOCKS
/
CREATE OR REPLACE PUBLIC SYNONYM DV_ARCHIVED_LOGS FOR SYS.DV_ARCHIVED_LOGS
/
CREATE OR REPLACE PUBLIC SYNONYM DV_ARCHIVE_GAPS FOR SYS.DV_ARCHIVE_GAPS
/
CREATE OR REPLACE PUBLIC SYNONYM DV_ARCHIVE_THREADS FOR SYS.DV_ARCHIVE_THREADS
/
CREATE OR REPLACE PUBLIC SYNONYM DV_ARCHIVE_DEST_STATUS FOR SYS.DV_ARCHIVE_DEST_STATUS
/
CREATE OR REPLACE PUBLIC SYNONYM DV_DATABASE FOR SYS.DV_DATABASE
/
CREATE OR REPLACE PUBLIC SYNONYM DV_GMA FOR SYS.DV_GMA
/
CREATE OR REPLACE PUBLIC SYNONYM DV_LOCKED_OBJECTS FOR SYS.DV_LOCKED_OBJECTS
/
CREATE OR REPLACE PUBLIC SYNONYM DV_STANDBYS FOR SYS.DV_STANDBYS
/
CREATE OR REPLACE PUBLIC SYNONYM DV_TABLESPACES FOR SYS.DV_TABLESPACES
/
CREATE OR REPLACE PUBLIC SYNONYM DV_SPINLOCKS FOR SYS.DV_SPINLOCKS
/
CREATE OR REPLACE PUBLIC SYNONYM DV_SQLS FOR SYS.DV_SQLS
/
CREATE OR REPLACE PUBLIC SYNONYM DV_SESSION_WAITS FOR SYS.DV_SESSION_WAITS
/
CREATE OR REPLACE PUBLIC SYNONYM DV_SESSION_EVENTS FOR SYS.DV_SESSION_EVENTS
/
CREATE OR REPLACE PUBLIC SYNONYM DV_SYS_EVENTS FOR SYS.DV_SYS_EVENTS
/
CREATE OR REPLACE PUBLIC SYNONYM DV_ME FOR SYS.DV_ME
/
GRANT SELECT ON DV_ME TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM DV_DATA_FILES FOR SYS.DV_DATA_FILES
/
CREATE OR REPLACE PUBLIC SYNONYM DV_SYS_STATS FOR SYS.DV_SYS_STATS
/
CREATE OR REPLACE PUBLIC SYNONYM DV_MEM_STATS FOR SYS.DV_MEM_STATS
/
CREATE OR REPLACE PUBLIC SYNONYM DV_SYSTEM FOR SYS.DV_SYSTEM
/
CREATE OR REPLACE PUBLIC SYNONYM DV_VERSION FOR SYS.DV_VERSION
/
CREATE OR REPLACE PUBLIC SYNONYM DV_TRANSACTIONS FOR SYS.DV_TRANSACTIONS
/
CREATE OR REPLACE PUBLIC SYNONYM DV_ALL_TRANS FOR SYS.DV_ALL_TRANS
/
CREATE OR REPLACE PUBLIC SYNONYM DV_GLOBAL_TRANSACTIONS FOR SYS.DV_GLOBAL_TRANSACTIONS
/
CREATE OR REPLACE PUBLIC SYNONYM DV_RESOURCE_MAP FOR SYS.DV_RESOURCE_MAP
/
CREATE OR REPLACE PUBLIC SYNONYM DV_USER_ASTATUS_MAP FOR SYS.DV_USER_ASTATUS_MAP
/
CREATE OR REPLACE PUBLIC SYNONYM DV_DYNAMIC_VIEWS FOR SYS.DV_DYNAMIC_VIEWS
/
CREATE OR REPLACE PUBLIC SYNONYM DV_DYNAMIC_VIEW_COLS FOR SYS.DV_DYNAMIC_VIEW_COLS
/
CREATE OR REPLACE PUBLIC SYNONYM DV_BACKUP_PROCESSES FOR SYS.DV_BACKUP_PROCESSES
/

CREATE OR REPLACE PUBLIC SYNONYM DV_USER_ADVISORY_LOCKS FOR SYS.DV_USER_ADVISORY_LOCKS
/
CREATE OR REPLACE PUBLIC SYNONYM DV_CONTROL_FILES FOR SYS.DV_CONTROL_FILES
/
CREATE OR REPLACE PUBLIC SYNONYM DV_REACTOR_POOLS FOR SYS.DV_REACTOR_POOLS
/
CREATE OR REPLACE PUBLIC SYNONYM NLS_SESSION_PARAMETERS FOR SYS.NLS_SESSION_PARAMETERS
/
GRANT SELECT ON NLS_SESSION_PARAMETERS TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM DV_SEGMENT_STATS FOR SYS.DV_SEGMENT_STATS
/
CREATE OR REPLACE PUBLIC SYNONYM DV_CONNPOOL_STATS FOR SYS.DV_CONNPOOL_STATS
/
GRANT SELECT ON DV_CONNPOOL_STATS TO PUBLIC
/

CREATE OR REPLACE PUBLIC SYNONYM DV_WAIT_STATS FOR SYS.DV_WAIT_STATS
/
CREATE OR REPLACE PUBLIC SYNONYM DV_LATCHS FOR SYS.DV_LATCHS
/

CREATE OR REPLACE PUBLIC SYNONYM DV_VM_FUNC_STACK FOR SYS.DV_VM_FUNC_STACK
/

CREATE OR REPLACE PUBLIC SYNONYM DV_BUFFER_POOL_STATS FOR SYS.DV_BUFFER_POOL_STATS
/

CREATE OR REPLACE PUBLIC SYNONYM DV_BUFFER_PAGE_STATS FOR SYS.DV_BUFFER_PAGE_STATS
/

CREATE OR REPLACE PUBLIC SYNONYM DV_BUFFER_INDEX_STATS FOR SYS.DV_BUFFER_INDEX_STATS
/

CREATE OR REPLACE PUBLIC SYNONYM DV_XACT_LOCKS FOR SYS.DV_XACT_LOCKS
/

CREATE OR REPLACE PUBLIC SYNONYM DV_EMERG_POOL FOR SYS.DV_EMERG_POOL
/

CREATE OR REPLACE PUBLIC SYNONYM SYS_DUMMY FOR SYS.SYS_DUMMY
/

GRANT SELECT ON SYS_DUMMY TO PUBLIC
/

CREATE OR REPLACE VIEW DB_NODE_INFO
AS
SELECT STATUS, DATABASE_ROLE, DATABASE_CONDITION, CURRENT_SCN FROM sys.DV_DATABASE
/

CREATE OR REPLACE PUBLIC SYNONYM DB_NODE_INFO FOR SYS.DB_NODE_INFO
/

GRANT SELECT ON DB_NODE_INFO TO PUBLIC
/

CREATE OR REPLACE VIEW SYS.ADM_2PC_PENDING
(FORMAT_ID, GLOBAL_TRAN_ID, BRANCH_ID,OWNER)
AS
SELECT FORMAT_ID,GLOBAL_TRAN_ID,BRANCH_ID,OWNER FROM SYS.SYS_PENDING_TRANS
/
CREATE OR REPLACE PUBLIC SYNONYM ADM_2PC_PENDING FOR SYS.ADM_2PC_PENDING
/
GRANT SELECT ON ADM_2PC_PENDING TO PUBLIC
/

CREATE OR REPLACE VIEW ADM_BACKUP_SET
(
BACKUP_TYPE,
STAGE,
STATUS,
INCREMENTAL_LEVEL,
TAG,
SCN,
DEVICE_TYPE,
DIR,
START_TIME,
BASE_TAG
)
AS
SELECT DECODE(TYPE, 1, ‘FULL’, 2, ‘INCR’, 3, ‘LOG’, ‘INVALID’) AS BACKUP_TYPE,
DECODE(STAGE, 1, ‘CTRL’, 2, ‘HEAD’, 3, ‘DATA’, 4, ‘LOG’, ‘INVALID’) AS STAGE,
DECODE(STATUS, 0, ‘SUCCESS’, 1, ‘DOING’, ‘FAILED’) AS STATUS,
INCREMENTAL_LEVEL, TAG, SCN, DECODE(DEVICE_TYPE, 0, ‘DISK’, 1, ‘NBU’, ‘UNKNOW’) AS DEVICE_TYPE,
DIR, START_TIME, BASE_TAG FROM SYS.SYS_BACKUP_SETS
/

CREATE OR REPLACE VIEW MY_SEGMENTS
(
SEGMENT_NAME,
PARTITION_NAME,
SEGMENT_TYPE,
TABLESPACE_NAME,
BYTES,
PAGES,
EXTENTS
)
AS
SELECT T.NAME, ‘’, ‘TABLE’, TS.NAME,
DBMS_DIAGNOSE.DBA_SEGSIZE(0, T.ENTRY), DBMS_DIAGNOSE.DBA_SEGSIZE(1, T.ENTRY), DBMS_DIAGNOSE.DBA_SEGSIZE(2, T.ENTRY)
FROM SYS.DV_ME ME, SYS.SYS_TABLES T, SYS.DV_TABLESPACES TS
WHERE T.USER# = ME.USER_ID AND T.SPACE# = TS.ID
UNION ALL
SELECT I.NAME, ‘’, ‘INDEX’, TS.NAME,
DBMS_DIAGNOSE.DBA_SEGSIZE(0, I.ENTRY), DBMS_DIAGNOSE.DBA_SEGSIZE(1, I.ENTRY), DBMS_DIAGNOSE.DBA_SEGSIZE(2, I.ENTRY)
FROM SYS.DV_ME ME, SYS.SYS_INDEXES I, SYS.DV_TABLESPACES TS
WHERE I.USER# = ME.USER_ID AND I.SPACE# = TS.ID
UNION ALL
SELECT ‘LOB_’ || T.NAME || ‘’ || C.NAME, ‘’, ‘LOB’, TS.NAME,
DBMS_DIAGNOSE.DBA_SEGSIZE(0, L.ENTRY), DBMS_DIAGNOSE.DBA_SEGSIZE(1, L.ENTRY), DBMS_DIAGNOSE.DBA_SEGSIZE(2, L.ENTRY)
FROM SYS.DV_ME ME, SYS.SYS_LOBS L, SYS.SYS_TABLES T, SYS.SYS_COLUMNS C, SYS.DV_TABLESPACES TS
WHERE L.USER# = ME.USER_ID AND L.USER# = T.USER# AND L.TABLE# = T.ID AND
L.USER# = C.USER# AND L.TABLE# = C.TABLE# AND L.COLUMN# = C.ID AND L.SPACE# = TS.ID
UNION ALL
SELECT T.NAME, TP.NAME, ‘TABLE’, TS.NAME,
DBMS_DIAGNOSE.DBA_SEGSIZE(0, TP.ENTRY), DBMS_DIAGNOSE.DBA_SEGSIZE(1, TP.ENTRY), DBMS_DIAGNOSE.DBA_SEGSIZE(2, TP.ENTRY)
FROM SYS.DV_ME ME, SYS.SYS_TABLES T, SYS.SYS_TABLE_PARTS TP, SYS.DV_TABLESPACES TS
WHERE T.PARTITIONED = 1 AND T.USER# = ME.USER_ID AND
T.USER# = TP.USER# AND T.ID = TP.TABLE# AND TP.SPACE# = TS.ID
UNION ALL
SELECT I.NAME, IP.NAME, ‘INDEX’, TS.NAME,
DBMS_DIAGNOSE.DBA_SEGSIZE(0, IP.ENTRY), DBMS_DIAGNOSE.DBA_SEGSIZE(1, IP.ENTRY), DBMS_DIAGNOSE.DBA_SEGSIZE(2, IP.ENTRY)
FROM SYS.DV_ME ME, SYS.SYS_INDEXES I, SYS.SYS_INDEX_PARTS IP, SYS.DV_TABLESPACES TS
WHERE I.PARTITIONED = 1 AND I.USER# = ME.USER_ID AND
I.USER# = IP.USER# AND I.TABLE# = IP.TABLE# AND I.ID = IP.INDEX# AND IP.SPACE# = TS.ID
UNION ALL
SELECT 'LOB’ || T.NAME || ‘’ || C.NAME, 'LOB’ || T.NAME || ‘’ || C.NAME || '’ || LP.PART#,
‘LOB’, TS.NAME, DBMS_DIAGNOSE.DBA_SEGSIZE(0, LP.ENTRY), DBMS_DIAGNOSE.DBA_SEGSIZE(1, LP.ENTRY), DBMS_DIAGNOSE.DBA_SEGSIZE(2, LP.ENTRY)
FROM SYS.DV_ME ME, SYS.SYS_LOBS L, SYS.SYS_TABLES T, SYS.SYS_COLUMNS C, SYS.SYS_LOB_PARTS LP, SYS.DV_TABLESPACES TS
WHERE L.USER# = ME.USER_ID AND L.USER# = T.USER# AND L.TABLE# = T.ID AND T.PARTITIONED = 1 AND
L.USER# = C.USER# AND L.TABLE# = C.TABLE# AND L.COLUMN# = C.ID AND
L.USER# = LP.USER# AND L.TABLE# = LP.TABLE# AND L.COLUMN# = LP.COLUMN# AND LP.SPACE# = TS.ID
/

CREATE OR REPLACE VIEW ADM_SEGMENTS
(
OWNER,
SEGMENT_NAME,
PARTITION_NAME,
SEGMENT_TYPE,
TABLESPACE_NAME,
BYTES,
PAGES,
EXTENTS
)
AS
SELECT U.NAME, T.NAME, ‘’, ‘TABLE’, TS.NAME,
DBMS_DIAGNOSE.DBA_SEGSIZE(0, T.ENTRY), DBMS_DIAGNOSE.DBA_SEGSIZE(1, T.ENTRY), DBMS_DIAGNOSE.DBA_SEGSIZE(2, T.ENTRY)
FROM SYS.SYS_USERS U, SYS.SYS_TABLES T, SYS.DV_TABLESPACES TS
WHERE T.USER# = U.ID AND T.SPACE# = TS.ID
UNION ALL
SELECT U.NAME, I.NAME, ‘’, ‘INDEX’, TS.NAME,
DBMS_DIAGNOSE.DBA_SEGSIZE(0, I.ENTRY), DBMS_DIAGNOSE.DBA_SEGSIZE(1, I.ENTRY), DBMS_DIAGNOSE.DBA_SEGSIZE(2, I.ENTRY)
FROM SYS.SYS_USERS U, SYS.SYS_INDEXES I, SYS.DV_TABLESPACES TS
WHERE I.USER# = U.ID AND I.SPACE# = TS.ID
UNION ALL
SELECT U.NAME, ‘LOB_’ || T.NAME || ‘’ || C.NAME, ‘’, ‘LOB’, TS.NAME,
DBMS_DIAGNOSE.DBA_SEGSIZE(0, L.ENTRY), DBMS_DIAGNOSE.DBA_SEGSIZE(1, L.ENTRY), DBMS_DIAGNOSE.DBA_SEGSIZE(2, L.ENTRY)
FROM SYS.SYS_USERS U, SYS.SYS_LOBS L, SYS.SYS_TABLES T, SYS.SYS_COLUMNS C, SYS.DV_TABLESPACES TS
WHERE L.USER# = U.ID AND L.USER# = T.USER# AND L.TABLE# = T.ID AND
L.USER# = C.USER# AND L.TABLE# = C.TABLE# AND L.COLUMN# = C.ID AND L.SPACE# = TS.ID
UNION ALL
SELECT U.NAME, T.NAME, TP.NAME, ‘TABLE’, TS.NAME,
DBMS_DIAGNOSE.DBA_SEGSIZE(0, TP.ENTRY), DBMS_DIAGNOSE.DBA_SEGSIZE(1, TP.ENTRY), DBMS_DIAGNOSE.DBA_SEGSIZE(2, TP.ENTRY)
FROM SYS.SYS_USERS U, SYS.SYS_TABLES T, SYS.SYS_TABLE_PARTS TP, SYS.DV_TABLESPACES TS
WHERE T.PARTITIONED = 1 AND T.USER# = U.ID AND
T.USER# = TP.USER# AND T.ID = TP.TABLE# AND TP.SPACE# = TS.ID
UNION ALL
SELECT U.NAME, I.NAME, IP.NAME, ‘INDEX’, TS.NAME,
DBMS_DIAGNOSE.DBA_SEGSIZE(0, IP.ENTRY), DBMS_DIAGNOSE.DBA_SEGSIZE(1, IP.ENTRY), DBMS_DIAGNOSE.DBA_SEGSIZE(2, IP.ENTRY)
FROM SYS.SYS_USERS U, SYS.SYS_INDEXES I, SYS.SYS_INDEX_PARTS IP, SYS.DV_TABLESPACES TS
WHERE I.PARTITIONED = 1 AND I.USER# = U.ID AND
I.USER# = IP.USER# AND I.TABLE# = IP.TABLE# AND I.ID = IP.INDEX# AND IP.SPACE# = TS.ID
UNION ALL
SELECT U.NAME, 'LOB’ || T.NAME || ‘’ || C.NAME, 'LOB’ || T.NAME || ‘’ || C.NAME || '’ || LP.PART#,
‘LOB’, TS.NAME, DBMS_DIAGNOSE.DBA_SEGSIZE(0, LP.ENTRY), DBMS_DIAGNOSE.DBA_SEGSIZE(1, LP.ENTRY), DBMS_DIAGNOSE.DBA_SEGSIZE(2, LP.ENTRY)
FROM SYS.SYS_USERS U, SYS.SYS_LOBS L, SYS.SYS_TABLES T, SYS.SYS_COLUMNS C, SYS.SYS_LOB_PARTS LP, SYS.DV_TABLESPACES TS
WHERE L.USER# = U.ID AND L.USER# = T.USER# AND L.TABLE# = T.ID AND T.PARTITIONED = 1 AND
L.USER# = C.USER# AND L.TABLE# = C.TABLE# AND L.COLUMN# = C.ID AND
L.USER# = LP.USER# AND L.TABLE# = LP.TABLE# AND L.COLUMN# = LP.COLUMN# AND LP.SPACE# = TS.ID
/

CREATE OR REPLACE VIEW ADM_TABLES
(
OWNER,
TABLE_NAME,
TABLE_ID,
TABLESPACE_NAME,
COLUMN_COUNT,
INDEX_COUNT,
PARTITIONED,
CREATED_TIME,
LAST_DDL_TIME,
PCT_FREE,
INI_TRANS,
MAX_TRANS,
BYTES,
PAGES,
EXTENTS,
NUM_ROWS,
BLOCKS,
EMPTY_BLOCKS,
AVG_SPACE,
CHAIN_CNT,
AVG_ROW_LEN,
SAMPLE_SIZE,
LAST_ANALYZED,
STATUS,
TEMPORARY,
APPENDONLY,
TABLE_TYPE,
CR_MODE
)
AS
SELECT U.NAME, T.NAME, T.ID,
TS.NAME, T.COLS, T.INDEXES,
DECODE(T.PARTITIONED,1,‘Y’,‘N’),
SCN2DATE(T.ORG_SCN), SCN2DATE(T.CHG_SCN), T.PCTFREE, T.INITRANS, 255,
DECODE(T.PARTITIONED,0,DBMS_DIAGNOSE.DBA_SEGSIZE(0, T.ENTRY),1,DBMS_DIAGNOSE.DBA_PARTITIONED_TABSIZE(0,U.NAME,T.NAME)),
DECODE(T.PARTITIONED,0,DBMS_DIAGNOSE.DBA_SEGSIZE(1, T.ENTRY),1,DBMS_DIAGNOSE.DBA_PARTITIONED_TABSIZE(1,U.NAME,T.NAME)),
DECODE(T.PARTITIONED,0,DBMS_DIAGNOSE.DBA_SEGSIZE(2, T.ENTRY),1,DBMS_DIAGNOSE.DBA_PARTITIONED_TABSIZE(2,U.NAME,T.NAME)),
T.NUM_ROWS, T.BLOCKS, T.EMPTY_BLOCKS, NULL::NUMBER, NULL::NUMBER, T.AVG_ROW_LEN,
T.SAMPLESIZE, T.ANALYZETIME,
‘VALID’,
DECODE(T.TYPE, 0, ‘N’, 1, ‘N’, 2, ‘Y’, 3, ‘Y’, 4, ‘Y’, 5, ‘N’),
DECODE(T.APPENDONLY, 1, ‘Y’, ‘N’),
DECODE(T.TYPE, 0, ‘HEAP’, 1, ‘IOT’, 2, ‘TRANS_TEMP’, 3, ‘SESSION_TEMP’, 4, ‘NOLOGGING’, 5, ‘EXTERNAL’),
DECODE(T.CR_MODE, 0, ‘ROW’, 1, ‘PAGE’, NULL)
FROM SYS.SYS_USERS U, SYS.SYS_TABLES T, SYS.DV_TABLESPACES TS
WHERE T.USER# = U.ID AND T.SPACE# = TS.ID AND T.RECYCLED = 0
/

CREATE OR REPLACE VIEW DB_TABLES
(
OWNER,
TABLE_NAME,
TABLE_ID,
TABLESPACE_NAME,
COLUMN_COUNT,
INDEX_COUNT,
PARTITIONED,
CREATED_TIME,
LAST_DDL_TIME,
PCT_FREE,
INI_TRANS,
MAX_TRANS,
BYTES,
PAGES,
EXTENTS,
NUM_ROWS,
BLOCKS,
EMPTY_BLOCKS,
AVG_SPACE,
CHAIN_CNT,
AVG_ROW_LEN,
SAMPLE_SIZE,
LAST_ANALYZED,
STATUS,
TEMPORARY,
APPENDONLY,
TABLE_TYPE,
CR_MODE
)
AS
SELECT U.NAME, T.NAME, T.ID,
TS.NAME, T.COLS, T.INDEXES,
DECODE(T.PARTITIONED,1,‘Y’,‘N’),
SCN2DATE(T.ORG_SCN), SCN2DATE(T.CHG_SCN), T.PCTFREE, T.INITRANS, 255,
DECODE(T.PARTITIONED,0,DBMS_DIAGNOSE.DBA_SEGSIZE(0, T.ENTRY),1,DBMS_DIAGNOSE.DBA_PARTITIONED_TABSIZE(0,U.NAME,T.NAME)),
DECODE(T.PARTITIONED,0,DBMS_DIAGNOSE.DBA_SEGSIZE(1, T.ENTRY),1,DBMS_DIAGNOSE.DBA_PARTITIONED_TABSIZE(1,U.NAME,T.NAME)),
DECODE(T.PARTITIONED,0,DBMS_DIAGNOSE.DBA_SEGSIZE(2, T.ENTRY),1,DBMS_DIAGNOSE.DBA_PARTITIONED_TABSIZE(2,U.NAME,T.NAME)),
T.NUM_ROWS, T.BLOCKS, T.EMPTY_BLOCKS, NULL::NUMBER, NULL::NUMBER, T.AVG_ROW_LEN,
T.SAMPLESIZE, T.ANALYZETIME,
‘VALID’,
DECODE(T.TYPE, 0, ‘N’, 1, ‘N’, 2, ‘Y’, 3, ‘Y’, 4, ‘Y’, 5, ‘N’),
DECODE(T.APPENDONLY, 1, ‘Y’, ‘N’),
DECODE(T.TYPE, 0, ‘HEAP’, 1, ‘IOT’, 2, ‘TRANS_TEMP’, 3, ‘SESSION_TEMP’, 4, ‘NOLOGGING’, 5, ‘EXTERNAL’),
DECODE(T.CR_MODE, 0, ‘ROW’, 1, ‘PAGE’, NULL)
FROM SYS.SYS_USERS U, SYS.SYS_TABLES T, SYS.DV_TABLESPACES TS
WHERE T.USER# = U.ID AND T.SPACE# = TS.ID AND T.RECYCLED = 0
and (U.NAME = user or U.NAME = ‘SYS’ or user = ‘SYS’ or exists (select 1 from SYS.SYS_USER_ROLES S, SYS.DV_ME V where S.GRANTEE_ID = V.USER_ID and S.GRANTEE_TYPE = 0 and S.GRANTED_ROLE_ID = 0))
/

CREATE OR REPLACE VIEW DB_DBLINK_TABLES
(
OWNER,
TABLE_NAME,
OWNER_ID,
TABLE_ID,
COLUMN_COUNT,
INDEX_COUNT
)
AS
SELECT U.NAME, T.NAME, U.ID, T.ID, T.COLS, T.INDEXES
FROM SYS.SYS_USERS U, SYS.SYS_TABLES T
WHERE T.USER# = U.ID AND T.RECYCLED = 0 and (U.NAME = user or U.NAME = ‘SYS’ or user = ‘SYS’ or exists (select 1 from SYS.SYS_USER_ROLES S, SYS.DV_ME V where S.GRANTEE_ID = V.USER_ID and S.GRANTEE_T
YPE = 0 and S.GRANTED_ROLE_ID = 0))
/

CREATE OR REPLACE VIEW ADM_DBLINK_TABLES
(
OWNER,
TABLE_NAME,
OWNER_ID,
TABLE_ID,
COLUMN_COUNT,
INDEX_COUNT
)
AS
SELECT U.NAME, T.NAME, U.ID, T.ID, T.COLS, T.INDEXES
FROM SYS.SYS_USERS U, SYS.SYS_TABLES T
WHERE T.USER# = U.ID AND T.RECYCLED = 0
/

CREATE OR REPLACE VIEW MY_TABLES
(
TABLE_NAME,
TABLE_ID,
TABLESPACE_NAME,
COLUMN_COUNT,
INDEX_COUNT,
PARTITIONED,
CREATED_TIME,
LAST_DDL_TIME,
PCT_FREE,
INI_TRANS,
MAX_TRANS,
BYTES,
PAGES,
EXTENTS,
NUM_ROWS,
BLOCKS,
EMPTY_BLOCKS,
AVG_SPACE,
CHAIN_CNT,
AVG_ROW_LEN,
SAMPLE_SIZE,
LAST_ANALYZED,
STATUS,
TEMPORARY,
APPENDONLY,
TABLE_TYPE,
CR_MODE
)
AS
SELECT T.NAME, T.ID,
TS.NAME, T.COLS, T.INDEXES,
DECODE(T.PARTITIONED,1,‘Y’,‘N’),
SCN2DATE(T.ORG_SCN), SCN2DATE(T.CHG_SCN), T.PCTFREE, T.INITRANS, 255,
DECODE(T.PARTITIONED,0,DBMS_DIAGNOSE.DBA_SEGSIZE(0, T.ENTRY),1,DBMS_DIAGNOSE.DBA_PARTITIONED_TABSIZE(0,ME.USER_NAME,T.NAME)),
DECODE(T.PARTITIONED,0,DBMS_DIAGNOSE.DBA_SEGSIZE(1, T.ENTRY),1,DBMS_DIAGNOSE.DBA_PARTITIONED_TABSIZE(1,ME.USER_NAME,T.NAME)),
DECODE(T.PARTITIONED,0,DBMS_DIAGNOSE.DBA_SEGSIZE(2, T.ENTRY),1,DBMS_DIAGNOSE.DBA_PARTITIONED_TABSIZE(2,ME.USER_NAME,T.NAME)),
T.NUM_ROWS, T.BLOCKS, T.EMPTY_BLOCKS, NULL::NUMBER, NULL::NUMBER, T.AVG_ROW_LEN,
T.SAMPLESIZE, T.ANALYZETIME,
‘VALID’,
DECODE(T.TYPE, 0, ‘N’, 1, ‘N’, 2, ‘Y’, 3, ‘Y’, 4, ‘Y’, 5, ‘N’),
DECODE(T.APPENDONLY, 1, ‘Y’, ‘N’),
DECODE(T.TYPE, 0, ‘HEAP’, 1, ‘IOT’, 2, ‘TRANS_TEMP’, 3, ‘SESSION_TEMP’, 4, ‘NOLOGGING’, 5, ‘EXTERNAL’),
DECODE(T.CR_MODE, 0, ‘ROW’, 1, ‘PAGE’, NULL)
FROM SYS.DV_ME ME, SYS.SYS_TABLES T, SYS.DV_TABLESPACES TS
WHERE T.USER# = ME.USER_ID AND T.SPACE# =TS.ID AND T.RECYCLED = 0
/

CREATE OR REPLACE VIEW DB_TAB_COLS
(
OWNER,
TABLE_NAME,
COLUMN_NAME,
DATA_TYPE,
DATA_LENGTH,
DATA_PRECISION,
DATA_SCALE,
NULLABLE,
COLUMN_ID,
DATA_DEFAULT,
NUM_DISTINCT,
LOW_VALUE,
HIGH_VALUE,
CHAR_LENGTH,
CHAR_USED,
HISTOGRAM
)
AS
SELECT U.NAME, T.NAME, C.NAME, TYPE_ID2NAME(C.DATATYPE),
C.BYTES, C.PRECISION, C.SCALE, DECODE(C.NULLABLE, 1, ‘Y’, ‘N’), C.ID,
C.DEFAULT_TEXT, C.NUM_DISTINCT, C.LOW_VALUE, C.HIGH_VALUE, 0, NULL, C.HISTOGRAM
FROM SYS.SYS_USERS U, SYS.SYS_COLUMNS C, SYS.SYS_TABLES T
WHERE C.TABLE# = T.ID AND C.USER# = T.USER# AND T.USER# = U.ID AND T.RECYCLED = 0 AND C.FLAGS & 3 = 0
AND TYPE_ID2NAME(C.DATATYPE) NOT IN (‘CHAR’,‘VARCHAR2’,‘NCHAR’,‘NVARCHAR’,‘VARCHAR’)
and (U.NAME = user or U.NAME = ‘SYS’ or user = ‘SYS’ or exists (select 1 from SYS.SYS_USER_ROLES S, SYS.DV_ME V where S.GRANTEE_ID = V.USER_ID and S.GRANTEE_TYPE = 0 and S.GRANTED_ROLE_ID = 0))
UNION ALL
SELECT U.NAME, T.NAME, C.NAME, TYPE_ID2NAME(C.DATATYPE),
C.BYTES, C.PRECISION, C.SCALE, DECODE(C.NULLABLE, 1, ‘Y’, ‘N’), C.ID,
C.DEFAULT_TEXT, C.NUM_DISTINCT, C.LOW_VALUE, C.HIGH_VALUE, C.BYTES, DECODE(C.FLAGS & 32, 32, ‘C’, ‘B’), C.HISTOGRAM
FROM SYS.SYS_USERS U, SYS.SYS_COLUMNS C, SYS.SYS_TABLES T
WHERE C.TABLE# = T.ID AND C.USER# = T.USER# AND T.USER# = U.ID AND T.RECYCLED = 0 AND C.FLAGS & 3 = 0
AND TYPE_ID2NAME(C.DATATYPE) IN (‘CHAR’,‘VARCHAR2’,‘NCHAR’,‘NVARCHAR’,‘VARCHAR’)
and (U.NAME = user or U.NAME = ‘SYS’ or user = ‘SYS’ or exists (select 1 from SYS.SYS_USER_ROLES S, SYS.DV_ME V where S.GRANTEE_ID = V.USER_ID and S.GRANTEE_TYPE = 0 and S.GRANTED_ROLE_ID = 0))
/

CREATE OR REPLACE VIEW ADM_TAB_COLS
(
OWNER,
TABLE_NAME,
COLUMN_NAME,
DATA_TYPE,
DATA_LENGTH,
DATA_PRECISION,
DATA_SCALE,
NULLABLE,
COLUMN_ID,
DATA_DEFAULT,
NUM_DISTINCT,
LOW_VALUE,
HIGH_VALUE,
CHAR_LENGTH,
CHAR_USED,
HISTOGRAM
)
AS
SELECT U.NAME, T.NAME, C.NAME, TYPE_ID2NAME(C.DATATYPE),
C.BYTES, C.PRECISION, C.SCALE, DECODE(C.NULLABLE, 1, ‘Y’, ‘N’), C.ID,
C.DEFAULT_TEXT, C.NUM_DISTINCT, C.LOW_VALUE, C.HIGH_VALUE, 0, NULL, C.HISTOGRAM
FROM SYS.SYS_USERS U, SYS.SYS_COLUMNS C, SYS.SYS_TABLES T
WHERE C.TABLE# = T.ID AND C.USER# = T.USER# AND T.USER# = U.ID AND T.RECYCLED = 0 AND C.FLAGS & 3 = 0
AND TYPE_ID2NAME(C.DATATYPE) NOT IN (‘CHAR’,‘VARCHAR2’,‘NCHAR’,‘NVARCHAR’,‘VARCHAR’)
UNION ALL
SELECT U.NAME, T.NAME, C.NAME, TYPE_ID2NAME(C.DATATYPE),
C.BYTES, C.PRECISION, C.SCALE, DECODE(C.NULLABLE, 1, ‘Y’, ‘N’), C.ID,
C.DEFAULT_TEXT, C.NUM_DISTINCT, C.LOW_VALUE, C.HIGH_VALUE, C.BYTES, DECODE(C.FLAGS & 32, 32, ‘C’, ‘B’), C.HISTOGRAM
FROM SYS.SYS_USERS U, SYS.SYS_COLUMNS C, SYS.SYS_TABLES T
WHERE C.TABLE# = T.ID AND C.USER# = T.USER# AND T.USER# = U.ID AND T.RECYCLED = 0 AND C.FLAGS & 3 = 0
AND TYPE_ID2NAME(C.DATATYPE) IN (‘CHAR’,‘VARCHAR2’,‘NCHAR’,‘NVARCHAR’,‘VARCHAR’)
/

CREATE OR REPLACE VIEW DB_DIST_RULE_COLS
(
DIST_RULE_NAME,
COLUMN_NAME,
DATA_TYPE,
DATA_LENGTH,
DATA_PRECISION,
DATA_SCALE,
NULLABLE,
COLUMN_ID,
DATA_DEFAULT,
NUM_DISTINCT,
LOW_VALUE,
HIGH_VALUE,
CHAR_LENGTH,
CHAR_USED,
HISTOGRAM
)
AS
SELECT R.NAME, C.NAME, TYPE_ID2NAME(C.DATATYPE),
C.BYTES, C.PRECISION, C.SCALE, DECODE(C.NULLABLE, 1, ‘Y’, ‘N’), C.ID,
C.DEFAULT_TEXT, C.NUM_DISTINCT, C.LOW_VALUE, C.HIGH_VALUE, 0, NULL, C.HISTOGRAM
FROM SYS.SYS_DISTRIBUTE_RULES R, SYS.SYS_COLUMNS C
WHERE C.TABLE# = R.ID AND C.FLAGS & 3 = 0
AND TYPE_ID2NAME(C.DATATYPE) NOT IN (‘CHAR’,‘VARCHAR2’,‘NCHAR’,‘NVARCHAR’,‘VARCHAR’)
UNION ALL
SELECT R.NAME, C.NAME, TYPE_ID2NAME(C.DATATYPE),
C.BYTES, C.PRECISION, C.SCALE, DECODE(C.NULLABLE, 1, ‘Y’, ‘N’), C.ID,
C.DEFAULT_TEXT, C.NUM_DISTINCT, C.LOW_VALUE, C.HIGH_VALUE, C.BYTES, DECODE(C.FLAGS & 32, 32, ‘C’, ‘B’), C.HISTOGRAM
FROM SYS.SYS_DISTRIBUTE_RULES R, SYS.SYS_COLUMNS C
WHERE C.TABLE# = R.ID AND C.FLAGS & 3 = 0
AND TYPE_ID2NAME(C.DATATYPE) IN (‘CHAR’,‘VARCHAR2’,‘NCHAR’,‘NVARCHAR’,‘VARCHAR’)
/

CREATE OR REPLACE VIEW MY_PART_STORE
(
NAME,
OBJECT_TYPE,
POSITION,
TABLESPACE_NAME
)
AS
SELECT T.NAME,
‘TABLE’,
P.POSITION#,
TS.NAME
FROM SYS.DV_ME U, SYS.SYS_PART_STORES P, SYS.SYS_TABLES T, SYS.DV_TABLESPACES TS
WHERE P.INDEX# = -1
AND P.USER# = U.USER_ID
AND P.USER# = T.USER# AND P.TABLE# = T.ID
AND P.SPACE# = TS.ID
/

CREATE OR REPLACE VIEW DB_PART_STORE
(
OWNER,
NAME,
OBJECT_TYPE,
POSITION,
TABLESPACE_NAME
)
AS
SELECT U.NAME,
T.NAME,
‘TABLE’,
P.POSITION#,
TS.NAME
FROM SYS.SYS_USERS U, SYS.SYS_PART_STORES P, SYS.SYS_TABLES T, SYS.DV_TABLESPACES TS
WHERE P.INDEX# = -1
AND P.USER# = U.ID
AND P.USER# = T.USER#
AND P.TABLE# = T.ID
AND P.SPACE# = TS.ID
and (U.NAME = user or U.NAME = ‘SYS’ or user = ‘SYS’ or exists (select 1 from SYS.SYS_USER_ROLES S, SYS.DV_ME V where S.GRANTEE_ID = V.USER_ID and S.GRANTEE_TYPE = 0 and S.GRANTED_ROLE_ID = 0))
/

CREATE OR REPLACE VIEW ADM_PART_STORE
(
OWNER,
NAME,
OBJECT_TYPE,
POSITION,
TABLESPACE_NAME
)
AS
SELECT U.NAME,
T.NAME,
‘TABLE’,
P.POSITION#,
TS.NAME
FROM SYS.SYS_USERS U, SYS.SYS_PART_STORES P, SYS.SYS_TABLES T, SYS.DV_TABLESPACES TS
WHERE P.INDEX# = -1
AND P.USER# = U.ID
AND P.USER# = T.USER#
AND P.TABLE# = T.ID
AND P.SPACE# = TS.ID
/

CREATE OR REPLACE VIEW MY_PART_TABLES
(
TABLE_NAME,
PARTITIONING_TYPE,
PARTITION_COUNT,
PARTITIONING_KEY_COUNT,
DEF_TABLESPACE_NAME,
INTERVAL,
STATUS
)
AS
SELECT T.NAME,
DECODE(P.PARTTYPE, 0, ‘INVALID’, 1, ‘RANGE’, 2, ‘LIST’, 3, ‘HASH’),
P.PARTCNT#,
PARTKEYS#,
TS.NAME,
P.INTERVAL,
‘VALID’
FROM SYS.DV_ME U, SYS.SYS_PART_OBJECTS P, SYS.SYS_TABLES T, SYS.DV_TABLESPACES TS
WHERE P.INDEX# = -1
AND P.USER# = U.USER_ID
AND P.USER# = T.USER# AND P.TABLE# = T.ID
AND T.SPACE# = TS.ID
/

CREATE OR REPLACE VIEW DB_PART_TABLES
(
OWNER,
TABLE_NAME,
PARTITIONING_TYPE,
PARTITION_COUNT,
PARTITIONING_KEY_COUNT,
DEF_TABLESPACE_NAME,
INTERVAL,
STATUS
)
AS
SELECT U.NAME,
T.NAME,
DECODE(P.PARTTYPE, 0, ‘INVALID’, 1, ‘RANGE’, 2, ‘LIST’, 3, ‘HASH’),
P.PARTCNT#,
PARTKEYS#,
TS.NAME,
P.INTERVAL,
‘VALID’
FROM SYS.SYS_USERS U, SYS.SYS_PART_OBJECTS P, SYS.SYS_TABLES T, SYS.DV_TABLESPACES TS
WHERE P.INDEX# = -1
AND P.USER# = U.ID
AND P.USER# = T.USER# AND P.TABLE# = T.ID
AND T.SPACE# = TS.ID
and (U.NAME = user or U.NAME = ‘SYS’ or user = ‘SYS’ or exists (select 1 from SYS.SYS_USER_ROLES S, SYS.DV_ME V where S.GRANTEE_ID = V.USER_ID and S.GRANTEE_TYPE = 0 and S.GRANTED_ROLE_ID = 0))
/

CREATE OR REPLACE VIEW ADM_PART_TABLES
(
OWNER,
TABLE_NAME,
PARTITIONING_TYPE,
PARTITION_COUNT,
PARTITIONING_KEY_COUNT,
DEF_TABLESPACE_NAME,
INTERVAL,
STATUS
)
AS
SELECT U.NAME,
T.NAME,
DECODE(P.PARTTYPE, 0, ‘INVALID’, 1, ‘RANGE’, 2, ‘LIST’, 3, ‘HASH’),
P.PARTCNT#,
PARTKEYS#,
TS.NAME,
P.INTERVAL,
‘VALID’
FROM SYS.SYS_USERS U, SYS.SYS_PART_OBJECTS P, SYS.SYS_TABLES T, SYS.DV_TABLESPACES TS
WHERE P.INDEX# = -1
AND P.USER# = U.ID
AND P.USER# = T.USER# AND P.TABLE# = T.ID
AND T.SPACE# = TS.ID
/

CREATE OR REPLACE VIEW DB_PART_KEY_COLUMNS
(
OWNER,
NAME,
OBJECT_TYPE,
COLUMN_NAME,
COLUMN_POSITION
)
AS
SELECT U.NAME,
T.NAME,
‘TABLE’,
C.NAME,
P.POSITION# + 1
FROM SYS.SYS_USERS U, SYS.SYS_PART_COLUMNS P, SYS.SYS_COLUMNS C, SYS.SYS_TABLES T
WHERE P.USER# = U.ID
AND P.TABLE#= T.ID
AND P.USER# = T.USER#
AND P.USER# = C.USER#
AND P.TABLE#= C.TABLE#
AND P.COLUMN# = C.ID
and (U.NAME = user or U.NAME = ‘SYS’ or user = ‘SYS’ or exists (select 1 from SYS.SYS_USER_ROLES S, SYS.DV_ME V where S.GRANTEE_ID = V.USER_ID and S.GRANTEE_TYPE = 0 and S.GRANTED_ROLE_ID = 0))
/

CREATE OR REPLACE VIEW ADM_PART_KEY_COLUMNS
(
OWNER,
NAME,
OBJECT_TYPE,
COLUMN_NAME,
COLUMN_POSITION
)
AS
SELECT U.NAME,
T.NAME,
‘TABLE’,
C.NAME,
P.POSITION# + 1
FROM SYS.SYS_USERS U, SYS.SYS_PART_COLUMNS P, SYS.SYS_COLUMNS C, SYS.SYS_TABLES T
WHERE P.USER# = U.ID
AND P.TABLE#= T.ID
AND P.USER# = T.USER#
AND P.USER# = C.USER#
AND P.TABLE#= C.TABLE#
AND P.COLUMN# = C.ID
/

CREATE OR REPLACE VIEW MY_PART_KEY_COLUMNS
(
NAME,
OBJECT_TYPE,
COLUMN_NAME,
COLUMN_POSITION
)
AS
SELECT T.NAME,
‘TABLE’,
C.NAME,
P.POSITION# + 1
FROM SYS.DV_ME B, SYS.SYS_PART_COLUMNS P, SYS.SYS_COLUMNS C, SYS.SYS_TABLES T
WHERE P.USER# = B.USER_ID
AND P.TABLE#= T.ID
AND P.USER# = T.USER#
AND P.USER# = C.USER#
AND P.TABLE#= C.TABLE#
AND P.COLUMN# = C.ID
/

CREATE OR REPLACE VIEW MY_TAB_COLS
(
TABLE_NAME,
COLUMN_NAME,
DATA_TYPE,
DATA_LENGTH,
DATA_PRECISION,
DATA_SCALE,
NULLABLE,
COLUMN_ID,
DATA_DEFAULT,
NUM_DISTINCT,
LOW_VALUE,
HIGH_VALUE,
CHAR_LENGTH,
CHAR_USED,
HISTOGRAM
)
AS
SELECT T.NAME, C.NAME, TYPE_ID2NAME(C.DATATYPE),
C.BYTES, C.PRECISION, C.SCALE, DECODE(C.NULLABLE, 1, ‘Y’, ‘N’), C.ID,
C.DEFAULT_TEXT, C.NUM_DISTINCT, C.LOW_VALUE, C.HIGH_VALUE, 0, NULL, C.HISTOGRAM
FROM SYS.DV_ME M, SYS.SYS_COLUMNS C, SYS.SYS_TABLES T
WHERE C.TABLE# = T.ID AND C.USER# = T.USER# AND T.USER# = M.USER_ID AND T.RECYCLED = 0 AND C.FLAGS & 3 = 0
AND TYPE_ID2NAME(C.DATATYPE) NOT IN (‘CHAR’,‘VARCHAR2’,‘NCHAR’,‘NVARCHAR’,‘VARCHAR’)
UNION ALL
SELECT T.NAME, C.NAME, TYPE_ID2NAME(C.DATATYPE),
C.BYTES, C.PRECISION, C.SCALE, DECODE(C.NULLABLE, 1, ‘Y’, ‘N’), C.ID,
C.DEFAULT_TEXT, C.NUM_DISTINCT, C.LOW_VALUE, C.HIGH_VALUE, C.BYTES, DECODE(C.FLAGS & 32, 32, ‘C’, ‘B’), C.HISTOGRAM
FROM SYS.DV_ME M, SYS.SYS_COLUMNS C, SYS.SYS_TABLES T
WHERE C.TABLE# = T.ID AND C.USER# = T.USER# AND T.USER# = M.USER_ID AND T.RECYCLED = 0 AND C.FLAGS & 3 = 0
AND TYPE_ID2NAME(C.DATATYPE) IN (‘CHAR’,‘VARCHAR2’,‘NCHAR’,‘NVARCHAR’,‘VARCHAR’)
/

CREATE OR REPLACE VIEW DB_TAB_COLUMNS
(
OWNER,
TABLE_NAME,
COLUMN_NAME,
DATA_TYPE,
DATA_LENGTH,
DATA_PRECISION,
DATA_SCALE,
NULLABLE,
COLUMN_ID,
DATA_DEFAULT,
NUM_DISTINCT,
LOW_VALUE,
HIGH_VALUE,
NUM_NULLS,
NUM_BUCKETS,
CHAR_LENGTH,
CHAR_USED,
SAMPLE_SIZE,
LAST_ANALYZED,
HISTOGRAM,
AUTO_INCREMENT
)
AS
SELECT U.NAME, T.NAME, C.NAME, TYPE_ID2NAME(C.DATATYPE),
C.BYTES, C.PRECISION, C.SCALE, DECODE(C.NULLABLE, 1, ‘Y’, ‘N’), C.ID,
C.DEFAULT_TEXT, C.NUM_DISTINCT, C.LOW_VALUE, C.HIGH_VALUE, HH.NULL_NUM, HH.BUCKET_NUM,
0, NULL::CHAR, T.SAMPLESIZE, T.ANALYZETIME, C.HISTOGRAM,DECODE(C.FLAGS & 8, 0, ‘N’, ‘Y’)
FROM SYS.SYS_USERS U JOIN SYS.SYS_TABLES T ON T.USER# = U.ID
JOIN SYS.SYS_COLUMNS C ON C.USER# = T.USER# AND C.TABLE# = T.ID AND T.RECYCLED = 0 AND C.FLAGS & 3 = 0 AND TYPE_ID2NAME(C.DATATYPE) NOT IN (‘CHAR’,‘VARCHAR2’,‘NCHAR’,‘NVARCHAR’,‘VARCHAR’)
LEFT JOIN SYS.SYS_HISTGRAM_ABSTR HH ON C.USER# = HH.USER# AND C.TABLE# = HH.TAB# AND C.ID = HH.COL# AND (HH.SPARE1 IS NULL OR HH.SPARE1 = 4294967295)
where (U.NAME = user or U.NAME = ‘SYS’ or user = ‘SYS’ or exists (select 1 from SYS.SYS_USER_ROLES S, SYS.DV_ME V where S.GRANTEE_ID = V.USER_ID and S.GRANTEE_TYPE = 0 and S.GRANTED_ROLE_ID = 0))
UNION ALL
SELECT U.NAME, T.NAME, C.NAME, TYPE_ID2NAME(C.DATATYPE),
C.BYTES, C.PRECISION, C.SCALE, DECODE(C.NULLABLE, 1, ‘Y’, ‘N’), C.ID,
C.DEFAULT_TEXT, C.NUM_DISTINCT, C.LOW_VALUE, C.HIGH_VALUE, HH.NULL_NUM, HH.BUCKET_NUM,
C.BYTES, DECODE(C.FLAGS & 32, 32, ‘C’, ‘B’), T.SAMPLESIZE, T.ANALYZETIME, C.HISTOGRAM,DECODE(C.FLAGS & 8, 0, ‘N’, ‘Y’)
FROM SYS.SYS_USERS U JOIN SYS.SYS_TABLES T ON T.USER# = U.ID
JOIN SYS.SYS_COLUMNS C ON C.USER# = T.USER# AND C.TABLE# = T.ID AND T.RECYCLED = 0 AND C.FLAGS & 3 = 0 AND TYPE_ID2NAME(C.DATATYPE) IN (‘CHAR’,‘VARCHAR2’,‘NCHAR’,‘NVARCHAR’,‘VARCHAR’)
LEFT JOIN SYS.SYS_HISTGRAM_ABSTR HH ON C.USER# = HH.USER# AND C.TABLE# = HH.TAB# AND C.ID = HH.COL# AND (HH.SPARE1 IS NULL OR HH.SPARE1 = 4294967295)
where (U.NAME = user or U.NAME = ‘SYS’ or user = ‘SYS’ or exists (select 1 from SYS.SYS_USER_ROLES S, SYS.DV_ME V where S.GRANTEE_ID = V.USER_ID and S.GRANTEE_TYPE = 0 and S.GRANTED_ROLE_ID = 0))
UNION ALL
SELECT U.NAME, V.NAME, C.NAME, TYPE_ID2NAME(C.DATATYPE), C.BYTES, C.PRECISION, C.SCALE, DECODE(C.NULLABLE, 1, ‘Y’, ‘N’), C.ID, NULL,NULL::BINARY_INTEGER,NULL,NULL,
NULL::BINARY_INTEGER,NULL::BINARY_INTEGER,NULL::BINARY_INTEGER,NULL::CHAR,NULL::BINARY_INTEGER,NULL::TIMESTAMP,NULL,NULL::CHAR
FROM SYS.SYS_USERS U, SYS.SYS_VIEWS V, SYS.SYS_VIEW_COLS C
WHERE V.USER# = U.ID AND C.USER# = U.ID AND C.VIEW# = V.ID
and (U.NAME = user or U.NAME = ‘SYS’ or user = ‘SYS’ or exists (select 1 from SYS.SYS_USER_ROLES S, SYS.DV_ME V where S.GRANTEE_ID = V.USER_ID and S.GRANTEE_TYPE = 0 and S.GRANTED_ROLE_ID = 0))
/

CREATE OR REPLACE VIEW ADM_TAB_COLUMNS
(
OWNER,
TABLE_NAME,
COLUMN_NAME,
DATA_TYPE,
DATA_LENGTH,
DATA_PRECISION,
DATA_SCALE,
NULLABLE,
COLUMN_ID,
DATA_DEFAULT,
NUM_DISTINCT,
LOW_VALUE,
HIGH_VALUE,
NUM_NULLS,
NUM_BUCKETS,
CHAR_LENGTH,
CHAR_USED,
SAMPLE_SIZE,
LAST_ANALYZED,
HISTOGRAM,
AUTO_INCREMENT
)
AS
SELECT U.NAME, T.NAME, C.NAME, TYPE_ID2NAME(C.DATATYPE),
C.BYTES, C.PRECISION, C.SCALE, DECODE(C.NULLABLE, 1, ‘Y’, ‘N’), C.ID,
C.DEFAULT_TEXT, C.NUM_DISTINCT, C.LOW_VALUE, C.HIGH_VALUE, HH.NULL_NUM, HH.BUCKET_NUM,
0, NULL::CHAR, T.SAMPLESIZE, T.ANALYZETIME, C.HISTOGRAM,DECODE(C.FLAGS & 8, 0, ‘N’, ‘Y’)
FROM SYS.SYS_USERS U JOIN SYS.SYS_TABLES T ON T.USER# = U.ID
JOIN SYS.SYS_COLUMNS C ON C.USER# = T.USER# AND C.TABLE# = T.ID AND T.RECYCLED = 0 AND C.FLAGS & 3 = 0 AND TYPE_ID2NAME(C.DATATYPE) NOT IN (‘CHAR’,‘VARCHAR2’,‘NCHAR’,‘NVARCHAR’,‘VARCHAR’)
LEFT JOIN SYS.SYS_HISTGRAM_ABSTR HH ON C.USER# = HH.USER# AND C.TABLE# = HH.TAB# AND C.ID = HH.COL# AND (HH.SPARE1 IS NULL OR HH.SPARE1 = 4294967295)
UNION ALL
SELECT U.NAME, T.NAME, C.NAME, TYPE_ID2NAME(C.DATATYPE),
C.BYTES, C.PRECISION, C.SCALE, DECODE(C.NULLABLE, 1, ‘Y’, ‘N’), C.ID,
C.DEFAULT_TEXT, C.NUM_DISTINCT, C.LOW_VALUE, C.HIGH_VALUE, HH.NULL_NUM, HH.BUCKET_NUM,
C.BYTES, DECODE(C.FLAGS & 32, 32, ‘C’, ‘B’), T.SAMPLESIZE, T.ANALYZETIME, C.HISTOGRAM,DECODE(C.FLAGS & 8, 0, ‘N’, ‘Y’)
FROM SYS.SYS_USERS U JOIN SYS.SYS_TABLES T ON T.USER# = U.ID
JOIN SYS.SYS_COLUMNS C ON C.USER# = T.USER# AND C.TABLE# = T.ID AND T.RECYCLED = 0 AND C.FLAGS & 3 = 0 AND TYPE_ID2NAME(C.DATATYPE) IN (‘CHAR’,‘VARCHAR2’,‘NCHAR’,‘NVARCHAR’,‘VARCHAR’)
LEFT JOIN SYS.SYS_HISTGRAM_ABSTR HH ON C.USER# = HH.USER# AND C.TABLE# = HH.TAB# AND C.ID = HH.COL# AND (HH.SPARE1 IS NULL OR HH.SPARE1 = 4294967295)
UNION ALL
SELECT U.NAME, V.NAME, C.NAME, TYPE_ID2NAME(C.DATATYPE), C.BYTES, C.PRECISION, C.SCALE, DECODE(C.NULLABLE, 1, ‘Y’, ‘N’), C.ID, NULL,NULL::BINARY_INTEGER,NULL,NULL,
NULL::BINARY_INTEGER,NULL::BINARY_INTEGER,NULL::BINARY_INTEGER,NULL::CHAR,NULL::BINARY_INTEGER,NULL::TIMESTAMP,NULL,NULL::CHAR
FROM SYS.SYS_USERS U, SYS.SYS_VIEWS V, SYS.SYS_VIEW_COLS C
WHERE V.USER# = U.ID AND C.USER# = U.ID AND C.VIEW# = V.ID
/

CREATE OR REPLACE VIEW ADM_DBLINK_TAB_COLUMNS
(
OWNER,
TABLE_NAME,
COLUMN_NAME,
DATA_TYPE,
DATA_LENGTH,
DATA_PRECISION,
DATA_SCALE,
NULLABLE,
COLUMN_ID
)
AS
SELECT U.NAME, T.NAME, C.NAME, C.DATATYPE,
C.BYTES, C.PRECISION, C.SCALE, C.NULLABLE, C.ID
FROM SYS.SYS_USERS U JOIN SYS.SYS_TABLES T ON T.USER# = U.ID
JOIN SYS.SYS_COLUMNS C ON C.USER# = T.USER# AND C.TABLE# = T.ID AND T.RECYCLED = 0 AND C.FLAGS & 3 = 0 AND TYPE_ID2NAME(C.DATATYPE) NOT IN (‘CHAR’,‘VARCHAR2’,‘NCHAR’,‘NVARCHAR’,‘VARCHAR’)
UNION ALL
SELECT U.NAME, T.NAME, C.NAME, C.DATATYPE,
C.BYTES, C.PRECISION, C.SCALE, C.NULLABLE, C.ID
FROM SYS.SYS_USERS U JOIN SYS.SYS_TABLES T ON T.USER# = U.ID
JOIN SYS.SYS_COLUMNS C ON C.USER# = T.USER# AND C.TABLE# = T.ID AND T.RECYCLED = 0 AND C.FLAGS & 3 = 0 AND TYPE_ID2NAME(C.DATATYPE) IN (‘CHAR’,‘VARCHAR2’,‘NCHAR’,‘NVARCHAR’,‘VARCHAR’)
/

CREATE OR REPLACE VIEW DB_DBLINK_TAB_COLUMNS
(
OWNER,
TABLE_NAME,
COLUMN_NAME,
DATA_TYPE,
DATA_LENGTH,
DATA_PRECISION,
DATA_SCALE,
NULLABLE,
COLUMN_ID
)
AS
SELECT U.NAME, T.NAME, C.NAME, C.DATATYPE,
C.BYTES, C.PRECISION, C.SCALE, C.NULLABLE, C.ID
FROM SYS.SYS_USERS U JOIN SYS.SYS_TABLES T ON T.USER# = U.ID
JOIN SYS.SYS_COLUMNS C ON C.USER# = T.USER# AND C.TABLE# = T.ID AND T.RECYCLED = 0 AND C.FLAGS & 3 = 0 AND TYPE_ID2NAME(C.DATATYPE) NOT IN (‘CHAR’,‘VARCHAR2’,‘NCHAR’,‘NVARCHAR’,‘VARCHAR’)
where (U.NAME = user or U.NAME = ‘SYS’ or user = ‘SYS’ or exists (select 1 from SYS.SYS_USER_ROLES S, SYS.DV_ME V where S.GRANTEE_ID = V.USER_ID and S.GRANTEE_TYPE = 0 and S.GRANTED_ROLE_ID = 0))
UNION ALL
SELECT U.NAME, T.NAME, C.NAME, C.DATATYPE,
C.BYTES, C.PRECISION, C.SCALE, C.NULLABLE, C.ID
FROM SYS.SYS_USERS U JOIN SYS.SYS_TABLES T ON T.USER# = U.ID
JOIN SYS.SYS_COLUMNS C ON C.USER# = T.USER# AND C.TABLE# = T.ID AND T.RECYCLED = 0 AND C.FLAGS & 3 = 0 AND TYPE_ID2NAME(C.DATATYPE) IN (‘CHAR’,‘VARCHAR2’,‘NCHAR’,‘NVARCHAR’,‘VARCHAR’)
where (U.NAME = user or U.NAME = ‘SYS’ or user = ‘SYS’ or exists (select 1 from SYS.SYS_USER_ROLES S, SYS.DV_ME V where S.GRANTEE_ID = V.USER_ID and S.GRANTEE_TYPE = 0 and S.GRANTED_ROLE_ID = 0))
/

CREATE OR REPLACE VIEW MY_TAB_COLUMNS
(
TABLE_NAME,
COLUMN_NAME,
DATA_TYPE,
DATA_LENGTH,
DATA_PRECISION,
DATA_SCALE,
NULLABLE,
COLUMN_ID,
DATA_DEFAULT,
NUM_DISTINCT,
LOW_VALUE,
HIGH_VALUE,
NUM_NULLS,
NUM_BUCKETS,
CHAR_LENGTH,
CHAR_USED,
SAMPLE_SIZE,
LAST_ANALYZED,
HISTOGRAM,
AUTO_INCREMENT
)
AS
SELECT T.NAME, C.NAME, TYPE_ID2NAME(C.DATATYPE),
C.BYTES, C.PRECISION, C.SCALE, DECODE(C.NULLABLE, 1, ‘Y’, ‘N’), C.ID,
C.DEFAULT_TEXT, C.NUM_DISTINCT, C.LOW_VALUE, C.HIGH_VALUE, HH.NULL_NUM, HH.BUCKET_NUM,
0, NULL::CHAR, T.SAMPLESIZE, T.ANALYZETIME, C.HISTOGRAM,DECODE(C.FLAGS & 8, 0, ‘N’, ‘Y’)
FROM SYS.DV_ME M JOIN SYS.SYS_TABLES T ON T.USER# = M.USER_ID AND T.RECYCLED = 0
JOIN SYS.SYS_COLUMNS C ON C.USER# = T.USER# AND C.TABLE# = T.ID AND C.FLAGS & 3 = 0 AND TYPE_ID2NAME(C.DATATYPE) NOT IN (‘CHAR’,‘VARCHAR2’,‘NCHAR’,‘NVARCHAR’,‘VARCHAR’)
LEFT JOIN SYS.SYS_HISTGRAM_ABSTR HH ON C.USER# = HH.USER# AND C.TABLE# = HH.TAB# AND C.ID = HH.COL# AND (HH.SPARE1 IS NULL OR HH.SPARE1 = 4294967295)
UNION ALL
SELECT T.NAME, C.NAME, TYPE_ID2NAME(C.DATATYPE),
C.BYTES, C.PRECISION, C.SCALE, DECODE(C.NULLABLE, 1, ‘Y’, ‘N’), C.ID,
C.DEFAULT_TEXT, C.NUM_DISTINCT, C.LOW_VALUE, C.HIGH_VALUE, HH.NULL_NUM, HH.BUCKET_NUM,
C.BYTES, DECODE(C.FLAGS & 32, 32, ‘C’, ‘B’), T.SAMPLESIZE, T.ANALYZETIME, C.HISTOGRAM,DECODE(C.FLAGS & 8, 0, ‘N’, ‘Y’)
FROM SYS.DV_ME M JOIN SYS.SYS_TABLES T ON T.USER# = M.USER_ID AND T.RECYCLED = 0
JOIN SYS.SYS_COLUMNS C ON C.USER# = T.USER# AND C.TABLE# = T.ID AND C.FLAGS & 3 = 0 AND TYPE_ID2NAME(C.DATATYPE) IN (‘CHAR’,‘VARCHAR2’,‘NCHAR’,‘NVARCHAR’,‘VARCHAR’)
LEFT JOIN SYS.SYS_HISTGRAM_ABSTR HH ON C.USER# = HH.USER# AND C.TABLE# = HH.TAB# AND C.ID = HH.COL# AND (HH.SPARE1 IS NULL OR HH.SPARE1 = 4294967295)
UNION ALL
SELECT V.NAME, C.NAME, TYPE_ID2NAME(C.DATATYPE), C.BYTES, C.PRECISION, C.SCALE, DECODE(C.NULLABLE, 1, ‘Y’, ‘N’), C.ID, NULL,NULL::BINARY_INTEGER,NULL,NULL,
NULL::BINARY_INTEGER,NULL::BINARY_INTEGER,NULL::BINARY_INTEGER,NULL::CHAR,NULL::BINARY_INTEGER,NULL::TIMESTAMP,NULL,NULL::CHAR
FROM SYS.DV_ME ME, SYS.SYS_VIEWS V, SYS.SYS_VIEW_COLS C
WHERE V.USER# = ME.USER_ID AND C.USER# = ME.USER_ID AND C.VIEW# = V.ID
/

CREATE OR REPLACE VIEW DB_VIEWS
(
OWNER,
VIEW_NAME,
VIEW_TYPE,
COLUMN_COUNT,
TEXT,
TEXT_LENGTH,
CREATED_TIME,
LAST_DDL_TIME
)
AS
SELECT U.NAME, V.NAME, ‘NORMAL’, V.COLS, V.TEXT, V.TEXT_LENGTH, SCN2DATE(V.ORG_SCN), SCN2DATE(V.CHG_SCN)
FROM SYS.SYS_USERS U, SYS.SYS_VIEWS V
WHERE V.USER# = U.ID
and (U.NAME = user or U.NAME = ‘SYS’ or user = ‘SYS’ or exists (select 1 from SYS.SYS_USER_ROLES S, SYS.DV_ME V where S.GRANTEE_ID = V.USER_ID and S.GRANTEE_TYPE = 0 and S.GRANTED_ROLE_ID = 0))
UNION ALL
SELECT DV.USER_NAME, DV.NAME, ‘DYNAMIC’, DV.COLUMN_COUNT, NULL, 0, SCN2DATE(0), SCN2DATE(0) FROM SYS.DV_DYNAMIC_VIEWS DV
/

CREATE OR REPLACE VIEW ADM_VIEWS
(
OWNER,
VIEW_NAME,
VIEW_TYPE,
COLUMN_COUNT,
TEXT,
TEXT_LENGTH,
CREATED_TIME,
LAST_DDL_TIME
)
AS
SELECT U.NAME, V.NAME, ‘NORMAL’, V.COLS, V.TEXT, V.TEXT_LENGTH, SCN2DATE(V.ORG_SCN), SCN2DATE(V.CHG_SCN)
FROM SYS.SYS_USERS U, SYS.SYS_VIEWS V
WHERE V.USER# = U.ID
UNION ALL
SELECT DV.USER_NAME, DV.NAME, ‘DYNAMIC’, DV.COLUMN_COUNT, NULL, 0, SCN2DATE(0), SCN2DATE(0) FROM SYS.DV_DYNAMIC_VIEWS DV
/

CREATE OR REPLACE VIEW MY_VIEWS
(
VIEW_NAME,
VIEW_TYPE,
COLUMN_COUNT,
TEXT,
TEXT_LENGTH,
CREATED_TIME,
LAST_DDL_TIME
)
AS
SELECT V.NAME, ‘NORMAL’, V.COLS, V.TEXT, V.TEXT_LENGTH, SCN2DATE(V.ORG_SCN), SCN2DATE(V.CHG_SCN)
FROM SYS.DV_ME ME, SYS.SYS_VIEWS V
WHERE V.USER# = ME.USER_ID
UNION ALL
SELECT DV.NAME, ‘DYNAMIC’, DV.COLUMN_COUNT, NULL, 0, SCN2DATE(0), SCN2DATE(0)
FROM SYS.DV_ME ME, SYS.DV_DYNAMIC_VIEWS DV
WHERE ME.USER_ID = 0
/

CREATE OR REPLACE VIEW DB_VIEW_COLUMNS
(
OWNER,
VIEW_NAME,
COLUMN_ID,
COLUMN_NAME,
DATA_TYPE,
DATA_LENGTH,
DATA_PRECISION,
DATA_SCALE,
NULLABLE
)
AS
SELECT U.NAME, V.NAME, C.ID, C.NAME, TYPE_ID2NAME(C.DATATYPE), C.BYTES, C.PRECISION, C.SCALE, DECODE(C.NULLABLE, 1, ‘Y’, ‘N’)
FROM SYS.SYS_USERS U, SYS.SYS_VIEWS V, SYS.SYS_VIEW_COLS C
WHERE V.USER# = U.ID AND C.USER# = U.ID AND C.VIEW# = V.ID
and (U.NAME = user or U.NAME = ‘SYS’ or user = ‘SYS’ or exists (select 1 from SYS.SYS_USER_ROLES S, SYS.DV_ME V where S.GRANTEE_ID = V.USER_ID and S.GRANTEE_TYPE = 0 and S.GRANTED_ROLE_ID = 0))
UNION ALL
SELECT DC.USER_NAME, DC.VIEW_NAME, DC.COLUMN_ID, DC.COLUMN_NAME, DC.DATA_TYPE, DC.DATA_LENGTH, DC.DATA_PRECISION, DC.DATA_SCALE, ‘Y’
FROM SYS.DV_DYNAMIC_VIEW_COLS DC
/

CREATE OR REPLACE VIEW ADM_VIEW_COLUMNS
(
OWNER,
VIEW_NAME,
COLUMN_ID,
COLUMN_NAME,
DATA_TYPE,
DATA_LENGTH,
DATA_PRECISION,
DATA_SCALE,
NULLABLE
)
AS
SELECT U.NAME, V.NAME, C.ID, C.NAME, TYPE_ID2NAME(C.DATATYPE), C.BYTES, C.PRECISION, C.SCALE, DECODE(C.NULLABLE, 1, ‘Y’, ‘N’)
FROM SYS.SYS_USERS U, SYS.SYS_VIEWS V, SYS.SYS_VIEW_COLS C
WHERE V.USER# = U.ID AND C.USER# = U.ID AND C.VIEW# = V.ID
UNION ALL
SELECT DC.USER_NAME, DC.VIEW_NAME, DC.COLUMN_ID, DC.COLUMN_NAME, DC.DATA_TYPE, DC.DATA_LENGTH, DC.DATA_PRECISION, DC.DATA_SCALE, ‘Y’
FROM SYS.DV_DYNAMIC_VIEW_COLS DC
/

CREATE OR REPLACE VIEW MY_VIEW_COLUMNS
(
VIEW_NAME,
COLUMN_ID,
COLUMN_NAME,
DATA_TYPE,
DATA_LENGTH,
DATA_PRECISION,
DATA_SCALE,
NULLABLE
)
AS
SELECT V.NAME, C.ID, C.NAME, TYPE_ID2NAME(C.DATATYPE), C.BYTES, C.PRECISION, C.SCALE, DECODE(C.NULLABLE, 1, ‘Y’, ‘N’)
FROM SYS.DV_ME ME, SYS.SYS_VIEWS V, SYS.SYS_VIEW_COLS C
WHERE V.USER# = ME.USER_ID AND C.USER# = ME.USER_ID AND C.VIEW# = V.ID
UNION ALL
SELECT DC.VIEW_NAME, DC.COLUMN_ID, DC.COLUMN_NAME, DC.DATA_TYPE, DC.DATA_LENGTH, DC.DATA_PRECISION, DC.DATA_SCALE, ‘Y’
FROM SYS.DV_ME ME, SYS.DV_DYNAMIC_VIEW_COLS DC
WHERE ME.USER_ID = 0
/

CREATE OR REPLACE VIEW DB_INDEXES
(
OWNER,
INDEX_NAME,
INDEX_TYPE,
TABLE_NAME,
TABLESPACE_NAME,
IS_PRIMARY,
IS_UNIQUE,
IS_DUPLICATE,
PARTITIONED,
STATUS,
INI_TRANS,
MAX_TRANS,
PCT_FREE,
COLUMN_COUNT,
COLUMNS,
BYTES,
PAGES,
EXTENTS,
BLEVEL,
LEAF_BLOCKS,
EMPTY_LEAF_BLOCKS,
DISTINCT_KEYS,
AVG_LEAF_BLOCKS_PER_KEY,
AVG_DATA_BLOCKS_PER_KEY,
CLUSTERING_FACTOR,
NUM_ROWS,
SAMPLE_SIZE,
LAST_ANALYZED,
SYS_GENERATE,
CR_MODE
)
AS
SELECT U.NAME, I.NAME, DECODE(I.TYPE, 0, ‘NORMAL’, ‘NORMAL’),
T.NAME, DBMS_DIAGNOSE.DBA_SPACE_NAME(I.SPACE#)::VARCHAR(64), DECODE(I.IS_PRIMARY, 1, ‘Y’, ‘N’), DECODE(I.IS_UNIQUE, 1, ‘Y’, ‘N’), DECODE(I.FLAGS & 16, 0, ‘N’, ‘Y’),
DECODE(I.PARTITIONED, 1, ‘Y’,‘N’), DECODE(I.FLAGS & 4, 0, ‘VALID’, ‘INVALID’), I.INITRANS, 255, I.PCTFREE, I.COLS, DBMS_DIAGNOSE.DBA_LISTCOLS(U.NAME, T.NAME, I.COL_LIST),
DECODE(I.PARTITIONED,0,DBMS_DIAGNOSE.DBA_SEGSIZE(0, I.ENTRY),1,DBMS_DIAGNOSE.DBA_PARTITIONED_INDSIZE(0,U.NAME,T.NAME,I.NAME)),
DECODE(I.PARTITIONED,0,DBMS_DIAGNOSE.DBA_SEGSIZE(1, I.ENTRY),1,DBMS_DIAGNOSE.DBA_PARTITIONED_INDSIZE(1,U.NAME,T.NAME,I.NAME)),
DECODE(I.PARTITIONED,0,DBMS_DIAGNOSE.DBA_SEGSIZE(2, I.ENTRY),1,DBMS_DIAGNOSE.DBA_PARTITIONED_INDSIZE(2,U.NAME,T.NAME,I.NAME)),
I.BLEVEL, I.LEVEL_BLOCKS,I.EMPTY_LEAF_BLOCKS, I.DISTINCT_KEYS, I.AVG_LEAF_BLOCKS_PER_KEY, I.AVG_DATA_BLOCKS_PER_KEY, NULL::NUMBER,
T.NUM_ROWS, T.SAMPLESIZE, T.ANALYZETIME, FLAGS & 1,
DECODE(I.CR_MODE, 0, ‘ROW’, 1, ‘PAGE’, NULL)
FROM SYS.SYS_USERS U, SYS.SYS_INDEXES I, SYS.SYS_TABLES T
WHERE T.USER# = U.ID AND I.USER# = U.ID AND I.TABLE# = T.ID AND T.RECYCLED = 0
and (U.NAME = user or U.NAME = ‘SYS’ or user = ‘SYS’ or exists (select 1 from SYS.SYS_USER_ROLES S, SYS.DV_ME V where S.GRANTEE_ID = V.USER_ID and S.GRANTEE_TYPE = 0 and S.GRANTED_ROLE_ID = 0))
/

CREATE OR REPLACE VIEW ADM_INDEXES
(
OWNER,
INDEX_NAME,
INDEX_TYPE,
TABLE_NAME,
TABLESPACE_NAME,
IS_PRIMARY,
IS_UNIQUE,
IS_DUPLICATE,
PARTITIONED,
STATUS,
INI_TRANS,
MAX_TRANS,
PCT_FREE,
COLUMN_COUNT,
COLUMNS,
BYTES,
PAGES,
EXTENTS,
BLEVEL,
LEAF_BLOCKS,
EMPTY_LEAF_BLOCKS,
DISTINCT_KEYS,
AVG_LEAF_BLOCKS_PER_KEY,
AVG_DATA_BLOCKS_PER_KEY,
CLUSTERING_FACTOR,
NUM_ROWS,
SAMPLE_SIZE,
LAST_ANALYZED,
SYS_GENERATE,
CR_MODE
)
AS
SELECT U.NAME, I.NAME, DECODE(I.TYPE, 0, ‘NORMAL’, ‘NORMAL’),
T.NAME, DBMS_DIAGNOSE.DBA_SPACE_NAME(I.SPACE#)::VARCHAR(64), DECODE(I.IS_PRIMARY, 1, ‘Y’, ‘N’), DECODE(I.IS_UNIQUE, 1, ‘Y’, ‘N’), DECODE(I.FLAGS & 16, 0, ‘N’, ‘Y’),
DECODE(I.PARTITIONED, 1, ‘Y’,‘N’), DECODE(I.FLAGS & 4, 0, ‘VALID’, ‘INVALID’), I.INITRANS, 255, I.PCTFREE, I.COLS, DBMS_DIAGNOSE.DBA_LISTCOLS(U.NAME, T.NAME, I.COL_LIST),
DECODE(I.PARTITIONED,0,DBMS_DIAGNOSE.DBA_SEGSIZE(0, I.ENTRY),1,DBMS_DIAGNOSE.DBA_PARTITIONED_INDSIZE(0,U.NAME,T.NAME,I.NAME)),
DECODE(I.PARTITIONED,0,DBMS_DIAGNOSE.DBA_SEGSIZE(1, I.ENTRY),1,DBMS_DIAGNOSE.DBA_PARTITIONED_INDSIZE(1,U.NAME,T.NAME,I.NAME)),
DECODE(I.PARTITIONED,0,DBMS_DIAGNOSE.DBA_SEGSIZE(2, I.ENTRY),1,DBMS_DIAGNOSE.DBA_PARTITIONED_INDSIZE(2,U.NAME,T.NAME,I.NAME)),
I.BLEVEL, I.LEVEL_BLOCKS,I.EMPTY_LEAF_BLOCKS, I.DISTINCT_KEYS, I.AVG_LEAF_BLOCKS_PER_KEY, I.AVG_DATA_BLOCKS_PER_KEY, NULL::NUMBER,
T.NUM_ROWS, T.SAMPLESIZE, T.ANALYZETIME, FLAGS & 1,
DECODE(I.CR_MODE, 0, ‘ROW’, 1, ‘PAGE’, NULL)
FROM SYS.SYS_USERS U, SYS.SYS_INDEXES I, SYS.SYS_TABLES T
WHERE T.USER# = U.ID AND I.USER# = U.ID AND I.TABLE# = T.ID AND T.RECYCLED = 0
/

CREATE OR REPLACE VIEW MY_INDEXES
(
INDEX_NAME,
INDEX_TYPE,
TABLE_NAME,
TABLESPACE_NAME,
IS_PRIMARY,
IS_UNIQUE,
IS_DUPLICATE,
PARTITIONED,
STATUS,
INI_TRANS,
MAX_TRANS,
PCT_FREE,
COLUMN_COUNT,
COLUMNS,
BYTES,
PAGES,
EXTENTS,
BLEVEL,
LEAF_BLOCKS,
EMPTY_LEAF_BLOCKS,
DISTINCT_KEYS,
AVG_LEAF_BLOCKS_PER_KEY,
AVG_DATA_BLOCKS_PER_KEY,
CLUSTERING_FACTOR,
NUM_ROWS,
SAMPLE_SIZE,
LAST_ANALYZED,
SYS_GENERATE,
CR_MODE
)
AS
SELECT I.NAME, DECODE(I.TYPE, 0, ‘NORMAL’, ‘NORMAL’),
T.NAME, DBMS_DIAGNOSE.DBA_SPACE_NAME(I.SPACE#), DECODE(I.IS_PRIMARY, 1, ‘Y’, ‘N’), DECODE(I.IS_UNIQUE, 1, ‘Y’, ‘N’), DECODE(I.FLAGS & 16, 0, ‘N’, ‘Y’),
DECODE(I.PARTITIONED,1,‘Y’,‘N’), DECODE(I.FLAGS & 4, 0, ‘VALID’, ‘INVALID’), I.INITRANS, 255, I.PCTFREE, I.COLS, DBMS_DIAGNOSE.DBA_LISTCOLS(ME.USER_NAME, T.NAME, I.COL_LIST),
DECODE(I.PARTITIONED,0,DBMS_DIAGNOSE.DBA_SEGSIZE(0, I.ENTRY),1,DBMS_DIAGNOSE.DBA_PARTITIONED_INDSIZE(0,ME.USER_NAME,T.NAME,I.NAME)),
DECODE(I.PARTITIONED,0,DBMS_DIAGNOSE.DBA_SEGSIZE(1, I.ENTRY),1,DBMS_DIAGNOSE.DBA_PARTITIONED_INDSIZE(1,ME.USER_NAME,T.NAME,I.NAME)),
DECODE(I.PARTITIONED,0,DBMS_DIAGNOSE.DBA_SEGSIZE(2, I.ENTRY),1,DBMS_DIAGNOSE.DBA_PARTITIONED_INDSIZE(2,ME.USER_NAME,T.NAME,I.NAME)),
I.BLEVEL, I.LEVEL_BLOCKS,I.EMPTY_LEAF_BLOCKS, I.DISTINCT_KEYS, I.AVG_LEAF_BLOCKS_PER_KEY, I.AVG_DATA_BLOCKS_PER_KEY, NULL::NUMBER,
T.NUM_ROWS, T.SAMPLESIZE, T.ANALYZETIME, FLAGS & 1,
DECODE(I.CR_MODE, 0, ‘ROW’, 1, ‘PAGE’, NULL)
FROM SYS.DV_ME ME, SYS.SYS_INDEXES I, SYS.SYS_TABLES T
WHERE I.USER# = ME.USER_ID AND T.USER# = ME.USER_ID AND I.TABLE# = T.ID AND T.RECYCLED = 0
/

CREATE OR REPLACE VIEW DB_OBJECTS
(
OWNER,
OBJECT_NAME,
SUBOBJECT_NAME,
OBJECT_ID,
OBJECT_TYPE,
CREATED,
LAST_DDL_TIME,
STATUS,
TEMPORARY
)
AS
SELECT U.NAME, T.NAME, NULL AS SUBOBJECT_NAME, T.ID, DECODE(T.RECYCLED, 1, ‘RECYCLED TABLE’, ‘TABLE’),
SCN2DATE(T.ORG_SCN), SCN2DATE(T.CHG_SCN), ‘VALID’, if(T.TYPE = 2 or T.TYPE = 3, ‘Y’, ‘N’)
FROM SYS.SYS_USERS U, SYS.SYS_TABLES T
WHERE T.USER# = U.ID and (U.NAME = user or U.NAME = ‘SYS’ or user = ‘SYS’ or exists (select 1 from SYS.SYS_USER_ROLES S, SYS.DV_ME V where S.GRANTEE_ID = V.USER_ID and S.GRANTEE_TYPE = 0 and S.GRA
NTED_ROLE_ID = 0))
UNION ALL
SELECT U.NAME, T.NAME, P.NAME AS SUBOBJECT_NAME, P.PART#, ‘TABLE PARTITION’,
SCN2DATE(P.ORG_SCN), SCN2DATE(T.CHG_SCN), ‘VALID’, if(T.TYPE = 2 or T.TYPE = 3, ‘Y’, ‘N’)
FROM SYS.SYS_USERS U, SYS.SYS_TABLES T, SYS.SYS_TABLE_PARTS P
WHERE T.USER# = U.ID AND P.USER# = U.ID AND P.TABLE# = T.ID and (U.NAME = user or U.NAME = ‘SYS’ or user = ‘SYS’ or exists (select 1 from SYS.SYS_USER_ROLES S, SYS.DV_ME V where S.GRANTEE_ID = V.U
SER_ID and S.GRANTEE_TYPE = 0 and S.GRANTED_ROLE_ID = 0))
UNION ALL
SELECT U.NAME, I.NAME, NULL AS SUBOBJECT_NAME, I.ID, DECODE(T.RECYCLED, 1, ‘RECYCLED INDEX’, ‘INDEX’),
SCN2DATE(T.ORG_SCN), SCN2DATE(T.CHG_SCN), ‘VALID’, if(T.TYPE = 2 or T.TYPE = 3, ‘Y’, ‘N’)
FROM SYS.SYS_USERS U, SYS.SYS_INDEXES I, SYS.SYS_TABLES T
WHERE I.USER# = U.ID AND T.USER# = U.ID AND I.TABLE# = T.ID and (U.NAME = user or U.NAME = ‘SYS’ or user = ‘SYS’ or exists (select 1 from SYS.SYS_USER_ROLES S, SYS.DV_ME V where S.GRANTEE_ID = V.U
SER_ID and S.GRANTEE_TYPE = 0 and S.GRANTED_ROLE_ID = 0))
UNION ALL
SELECT U.NAME, V.NAME, NULL AS SUBOBJECT_NAME, V.ID, ‘VIEW’,
SCN2DATE(V.ORG_SCN), SCN2DATE(V.CHG_SCN), DECODE(V.FLAGS, 1, ‘VALID’, 0, ‘INVALID’, ‘UNKNOWN’), ‘N’
FROM SYS.SYS_USERS U, SYS.SYS_VIEWS V
WHERE V.USER# = U.ID and (U.NAME = user or U.NAME = ‘SYS’ or user = ‘SYS’ or exists (select 1 from SYS.SYS_USER_ROLES S, SYS.DV_ME V where S.GRANTEE_ID = V.USER_ID and S.GRANTEE_TYPE = 0 and S.GRA
NTED_ROLE_ID = 0))
UNION ALL
SELECT DV.USER_NAME, DV.NAME, NULL AS SUBOBJECT_NAME, DV.ID, ‘DYNAMIC VIEW’,
SCN2DATE(0), SCN2DATE(0), ‘VALID’, ‘N’
FROM SYS.DV_DYNAMIC_VIEWS DV
UNION ALL
SELECT U.NAME, P.NAME, NULL AS SUBOBJECT_NAME, P.OBJ#,
CASE WHEN P.TYPE=‘P’ THEN ‘PROCEDURE’ WHEN P.TYPE=‘T’ THEN ‘TRIGGER’ WHEN P.TYPE=‘F’ THEN ‘FUNCTION’ ELSE P.TYPE END,
SCN2DATE(P.ORG_SCN), SCN2DATE(P.CHG_SCN), DECODE(P.STATUS, 1, ‘VALID’, 0,‘INVALID’,‘UNKNOWN’), ‘N’
FROM SYS.SYS_USERS U, SYS.SYS_PROCS P
WHERE P.USER# = U.ID and (U.NAME = user or U.NAME = ‘SYS’ or user = ‘SYS’ or exists (select 1 from SYS.SYS_USER_ROLES S, SYS.DV_ME V where S.GRANTEE_ID = V.USER_ID and S.GRANTEE_TYPE = 0 and S.GRA
NTED_ROLE_ID = 0))
UNION ALL
SELECT U.NAME, S.NAME, NULL AS SUBOBJECT_NAME, S.ID, ‘SEQUENCE’,
SCN2DATE(S.ORG_SCN), SCN2DATE(S.CHG_SCN), ‘VALID’, ‘N’
FROM SYS.SYS_USERS U, SYS.SYS_SEQUENCES S
WHERE S.UID = U.ID and (U.NAME = user or U.NAME = ‘SYS’ or user = ‘SYS’ or exists (select 1 from SYS.SYS_USER_ROLES R, SYS.DV_ME V where R.GRANTEE_ID = V.USER_ID and R.GRANTEE_TYPE = 0 and R.GRANT
ED_ROLE_ID = 0))
UNION ALL
SELECT U.NAME, Y.SYNONYM_NAME, NULL AS SUBOBJECT_NAME, Y.ID, ‘SYNONYM’,
SCN2DATE(Y.ORG_SCN), SCN2DATE(Y.CHG_SCN), DECODE(Y.FLAGS, 1, ‘VALID’, 0,‘INVALID’,‘UNKNOWN’), ‘N’
FROM SYS.SYS_USERS U, SYS.SYS_SYNONYMS Y
WHERE Y.USER# = U.ID and (U.NAME = user or U.NAME = ‘SYS’ or U.NAME = ‘PUBLIC’ or user = ‘SYS’ or exists (select 1 from SYS.SYS_USER_ROLES S, SYS.DV_ME V where S.GRANTEE_ID = V.USER_ID and S.GRANT
EE_TYPE = 0 and S.GRANTED_ROLE_ID = 0))
/

CREATE OR REPLACE VIEW ADM_OBJECTS
(
OWNER,
OBJECT_NAME,
SUBOBJECT_NAME,
OBJECT_ID,
OBJECT_TYPE,
CREATED,
LAST_DDL_TIME,
STATUS,
TEMPORARY
)
AS
SELECT U.NAME, T.NAME, NULL AS SUBOBJECT_NAME, T.ID, DECODE(T.RECYCLED, 1, ‘RECYCLED TABLE’, ‘TABLE’),
SCN2DATE(T.ORG_SCN), SCN2DATE(T.CHG_SCN), ‘VALID’, if(T.TYPE = 2 or T.TYPE = 3, ‘Y’, ‘N’)
FROM SYS.SYS_USERS U, SYS.SYS_TABLES T
WHERE T.USER# = U.ID
UNION ALL
SELECT U.NAME, T.NAME, P.NAME AS SUBOBJECT_NAME, P.PART#, ‘TABLE PARTITION’,
SCN2DATE(P.ORG_SCN), SCN2DATE(T.CHG_SCN), ‘VALID’, if(T.TYPE = 2 or T.TYPE = 3, ‘Y’, ‘N’)
FROM SYS.SYS_USERS U, SYS.SYS_TABLES T, SYS.SYS_TABLE_PARTS P
WHERE T.USER# = U.ID AND P.USER# = U.ID AND P.TABLE# = T.ID
UNION ALL
SELECT U.NAME, I.NAME, NULL AS SUBOBJECT_NAME, I.ID, DECODE(T.RECYCLED, 1, ‘RECYCLED INDEX’, ‘INDEX’),
SCN2DATE(T.ORG_SCN), SCN2DATE(T.CHG_SCN), ‘VALID’, if(T.TYPE = 2 or T.TYPE = 3, ‘Y’, ‘N’)
FROM SYS.SYS_USERS U, SYS.SYS_INDEXES I, SYS.SYS_TABLES T
WHERE I.USER# = U.ID AND T.USER# = U.ID AND I.TABLE# = T.ID
UNION ALL
SELECT U.NAME, V.NAME, NULL AS SUBOBJECT_NAME, V.ID, ‘VIEW’,
SCN2DATE(V.ORG_SCN), SCN2DATE(V.CHG_SCN), DECODE(V.FLAGS, 1, ‘VALID’, 0, ‘INVALID’, ‘UNKNOWN’), ‘N’
FROM SYS.SYS_USERS U, SYS.SYS_VIEWS V
WHERE V.USER# = U.ID
UNION ALL
SELECT DV.USER_NAME, DV.NAME, NULL AS SUBOBJECT_NAME, DV.ID, ‘DYNAMIC VIEW’,
SCN2DATE(0), SCN2DATE(0), ‘VALID’, ‘N’
FROM SYS.DV_DYNAMIC_VIEWS DV
UNION ALL
SELECT U.NAME, P.NAME, NULL AS SUBOBJECT_NAME, P.OBJ#,
CASE WHEN P.TYPE=‘P’ THEN ‘PROCEDURE’ WHEN P.TYPE=‘T’ THEN ‘TRIGGER’ WHEN P.TYPE=‘F’ THEN ‘FUNCTION’ ELSE P.TYPE END,
SCN2DATE(P.ORG_SCN), SCN2DATE(P.CHG_SCN), DECODE(P.STATUS, 1, ‘VALID’, 0,‘INVALID’,‘UNKNOWN’), ‘N’
FROM SYS.SYS_USERS U, SYS.SYS_PROCS P
WHERE P.USER# = U.ID
UNION ALL
SELECT U.NAME, S.NAME, NULL AS SUBOBJECT_NAME, S.ID, ‘SEQUENCE’,
SCN2DATE(S.ORG_SCN), SCN2DATE(S.CHG_SCN), ‘VALID’, ‘N’
FROM SYS.SYS_USERS U, SYS.SYS_SEQUENCES S
WHERE S.UID = U.ID
UNION ALL
SELECT U.NAME, Y.SYNONYM_NAME, NULL AS SUBOBJECT_NAME, Y.ID, ‘SYNONYM’,
SCN2DATE(Y.ORG_SCN), SCN2DATE(Y.CHG_SCN), DECODE(Y.FLAGS, 1, ‘VALID’, 0,‘INVALID’,‘UNKNOWN’), ‘N’
FROM SYS.SYS_USERS U, SYS.SYS_SYNONYMS Y
WHERE Y.USER# = U.ID
/

CREATE OR REPLACE VIEW MY_OBJECTS
(
OBJECT_NAME,
SUBOBJECT_NAME,
OBJECT_ID,
OBJECT_TYPE,
CREATED,
LAST_DDL_TIME,
STATUS,
TEMPORARY
)
AS
SELECT T.NAME, NULL AS SUBOBJECT_NAME, T.ID, DECODE(T.RECYCLED, 1, ‘RECYCLED TABLE’, ‘TABLE’),
SCN2DATE(T.ORG_SCN), SCN2DATE(T.CHG_SCN), ‘VALID’, if(T.TYPE = 2 or T.TYPE = 3, ‘Y’, ‘N’)
FROM SYS.DV_ME ME, SYS.SYS_TABLES T
WHERE T.USER# = ME.USER_ID
UNION ALL
SELECT T.NAME, P.NAME AS SUBOBJECT_NAME, P.PART#, ‘TABLE PARTITION’,
SCN2DATE(P.ORG_SCN), SCN2DATE(T.CHG_SCN), ‘VALID’, if(T.TYPE = 2 or T.TYPE = 3, ‘Y’, ‘N’)
FROM SYS.DV_ME ME, SYS.SYS_TABLES T, SYS.SYS_TABLE_PARTS P
WHERE T.USER# = ME.USER_ID AND P.USER# = ME.USER_ID AND P.TABLE# = T.ID
UNION ALL
SELECT I.NAME, NULL AS SUBOBJECT_NAME, I.ID, DECODE(T.RECYCLED, 1, ‘RECYCLED INDEX’, ‘INDEX’),
SCN2DATE(T.ORG_SCN), SCN2DATE(T.CHG_SCN), ‘VALID’, if(T.TYPE = 2 or T.TYPE = 3, ‘Y’, ‘N’)
FROM SYS.DV_ME ME, SYS.SYS_INDEXES I, SYS.SYS_TABLES T
WHERE I.USER# = ME.USER_ID AND T.USER# = I.USER# AND I.TABLE# = T.ID
UNION ALL
SELECT V.NAME, NULL AS SUBOBJECT_NAME, V.ID, ‘VIEW’,
SCN2DATE(V.ORG_SCN), SCN2DATE(V.CHG_SCN), DECODE(V.FLAGS, 1, ‘VALID’, 0, ‘INVALID’, ‘UNKNOWN’), ‘N’
FROM SYS.DV_ME ME, SYS.SYS_VIEWS V
WHERE V.USER# = ME.USER_ID
UNION ALL
SELECT DV.NAME, NULL AS SUBOBJECT_NAME, DV.ID, ‘DYNAMIC VIEW’,
SCN2DATE(0), SCN2DATE(0), ‘VALID’, ‘N’
FROM SYS.DV_DYNAMIC_VIEWS DV
UNION ALL
SELECT P.NAME, NULL AS SUBOBJECT_NAME, P.OBJ#,
CASE WHEN P.TYPE=‘P’ THEN ‘PROCEDURE’ WHEN P.TYPE=‘T’ THEN ‘TRIGGER’ WHEN P.TYPE=‘F’ THEN ‘FUNCTION’ ELSE P.TYPE END,
SCN2DATE(P.ORG_SCN), SCN2DATE(P.CHG_SCN), DECODE(P.STATUS, 1, ‘VALID’, 0,‘INVALID’,‘UNKNOWN’), ‘N’
FROM SYS.DV_ME ME, SYS.SYS_PROCS P
WHERE P.USER# = ME.USER_ID
UNION ALL
SELECT S.NAME, NULL AS SUBOBJECT_NAME, S.ID, ‘SEQUENCE’,
SCN2DATE(S.ORG_SCN), SCN2DATE(S.CHG_SCN), ‘VALID’, ‘N’
FROM SYS.DV_ME ME, SYS.SYS_SEQUENCES S
WHERE S.UID = ME.USER_ID
UNION ALL
SELECT Y.SYNONYM_NAME, NULL AS SUBOBJECT_NAME, Y.ID, ‘SYNONYM’,
SCN2DATE(Y.ORG_SCN), SCN2DATE(Y.CHG_SCN), DECODE(Y.FLAGS, 1, ‘VALID’, 0,‘INVALID’,‘UNKNOWN’), ‘N’
FROM SYS.DV_ME ME, SYS.SYS_SYNONYMS Y
WHERE Y.USER# = ME.USER_ID
/

CREATE OR REPLACE VIEW ADM_TABLESPACES
(
TABLESPACE_NAME,
PAGE_SIZE,
EXTENT_PAGES,
DATAFILE_COUNT,
TOTAL_SIZE,
USED_SIZE,
STATUS,
IN_MEMORY,
CONTENTS,
LOGGING,
BIGFILE
)
AS
SELECT TS.NAME, DBMS_DIAGNOSE.DBA_SPCSIZE(TS.ID, ‘PAGE’), TS.EXTENT_SIZE, TS.FILE_COUNT,
DBMS_DIAGNOSE.DBA_SPCSIZE(TS.ID, ‘TOTAL’), DBMS_DIAGNOSE.DBA_SPCSIZE(TS.ID, ‘USED’), TS.STATUS, TS.IN_MEMORY,
DECODE(TS.TEMPORARY, ‘TRUE’, ‘TEMPORARY’, ‘PERMANENT’),
DECODE(TS.TEMPORARY, ‘TRUE’, ‘N’, ‘Y’), ‘N’
FROM SYS.DV_TABLESPACES TS
/

CREATE OR REPLACE VIEW ADM_FREE_SPACE
(
TABLESPACE_NAME,
FILE_ID,
BLOCK_ID,
BYTES,
BLOCKS,
RELATIVE_FNO
)
AS
SELECT * FROM SYS.DV_FREE_SPACE
/

CREATE OR REPLACE VIEW MY_FREE_SPACE
(
TABLESPACE_NAME,
FILE_ID,
BLOCK_ID,
BYTES,
BLOCKS,
RELATIVE_FNO
)
AS
SELECT
TABLESPACE_NAME,
FILE_ID,
BLOCK_ID,
BYTES,
BLOCKS,
RELATIVE_FNO
FROM SYS.DV_FREE_SPACE
/

CREATE OR REPLACE VIEW MY_SYNONYMS
(
SYNONYM_NAME,
TABLE_OWNER,
TABLE_NAME
)
AS
SELECT S.SYNONYM_NAME, S.TABLE_OWNER, S.TABLE_NAME
FROM SYS.DV_ME ME INNER JOIN SYS.SYS_SYNONYMS S ON ME.USER_ID = S.USER#

/
COMMENT ON COLUMN SYS.MY_SYNONYMS.SYNONYM_NAME IS ‘Name of the synonym’
/
COMMENT ON COLUMN SYS.MY_SYNONYMS.TABLE_OWNER IS ‘Owner of the object referenced by the synonym’
/
COMMENT ON COLUMN SYS.MY_SYNONYMS.TABLE_NAME IS ‘Name of the object referenced by the synonym’
/

CREATE OR REPLACE VIEW SYS.ADM_SYNONYMS
(
OWNER,
SYNONYM_NAME,
TABLE_OWNER,
TABLE_NAME
)
AS
SELECT U.NAME, S.SYNONYM_NAME, S.TABLE_OWNER, S.TABLE_NAME
FROM SYS.SYS_USERS U INNER JOIN SYS.SYS_SYNONYMS S ON U.ID = S.USER#
/

CREATE OR REPLACE VIEW SYS.DB_SYNONYMS
(
OWNER,
SYNONYM_NAME,
TABLE_OWNER,
TABLE_NAME
)
AS
SELECT U.NAME, S.SYNONYM_NAME, S.TABLE_OWNER, S.TABLE_NAME
FROM SYS.SYS_USERS U INNER JOIN SYS.SYS_SYNONYMS S ON U.ID = S.USER#
where U.NAME = user or U.NAME = ‘SYS’ or U.NAME = ‘PUBLIC’ or user = ‘SYS’
or exists (select 1 from SYS.SYS_USER_ROLES R, SYS.DV_ME V where R.GRANTEE_ID = V.USER_ID and R.GRANTEE_TYPE = 0 and R.GRANTED_ROLE_ID = 0)
/

COMMENT ON COLUMN SYS.ADM_SYNONYMS.OWNER IS ‘Username of the owner of the synonym’
/
COMMENT ON COLUMN SYS.ADM_SYNONYMS.SYNONYM_NAME IS ‘Name of the synonym’
/
COMMENT ON COLUMN SYS.ADM_SYNONYMS.TABLE_OWNER IS ‘Owner of the object referenced by the synonym’
/
COMMENT ON COLUMN SYS.ADM_SYNONYMS.TABLE_NAME IS ‘Name of the object referenced by the synonym’
/

CREATE OR REPLACE VIEW MY_TAB_COMMENTS
(
TABLE_NAME,
TABLE_TYPE,
COMMENTS
)
AS
SELECT T.NAME, ‘TABLE’, C.TEXT
FROM SYS.SYS_TABLES T INNER JOIN SYS.DV_ME ME ON ME.USER_ID = T.USER# AND T.RECYCLED = 0 INNER JOIN SYS.SYS_COMMENTS C ON T.USER# = C.USER# AND T.ID = C.TABLE# AND C.COLUMN# IS NULL
UNION ALL
SELECT V.NAME, ‘VIEW’, C.TEXT
FROM SYS.SYS_VIEWS V INNER JOIN SYS.DV_ME ME ON ME.USER_ID = V.USER# INNER JOIN SYS.SYS_COMMENTS C ON V.USER# = C.USER# AND V.ID = C.TABLE# AND C.COLUMN# IS NULL
/
COMMENT ON COLUMN SYS.MY_TAB_COMMENTS.TABLE_NAME IS ‘Name of the object’
/
COMMENT ON COLUMN SYS.MY_TAB_COMMENTS.TABLE_TYPE IS ‘Type of the object: “TABLE” or “VIEW”’
/
COMMENT ON COLUMN SYS.MY_TAB_COMMENTS.COMMENTS IS ‘Comment on the object’
/

CREATE OR REPLACE VIEW ADM_TAB_COMMENTS
(
OWNER,
TABLE_NAME,
TABLE_TYPE,
COMMENTS
)
AS
SELECT U.NAME, T.NAME, ‘TABLE’, C.TEXT
FROM SYS.SYS_TABLES T INNER JOIN SYS.SYS_USERS U ON U.ID = T.USER# AND T.RECYCLED = 0 INNER JOIN SYS.SYS_COMMENTS C ON T.USER# = C.USER# AND T.ID = C.TABLE# AND C.COLUMN# IS NULL
UNION ALL
SELECT U.NAME, V.NAME, ‘VIEW’, C.TEXT
FROM SYS.SYS_VIEWS V INNER JOIN SYS.SYS_USERS U ON U.ID = V.USER# INNER JOIN SYS.SYS_COMMENTS C ON V.USER# = C.USER# AND V.ID = C.TABLE# AND C.COLUMN# IS NULL
/
COMMENT ON COLUMN SYS.ADM_TAB_COMMENTS.OWNER IS ‘Owner of the object’
/
COMMENT ON COLUMN SYS.ADM_TAB_COMMENTS.TABLE_NAME IS ‘Name of the object’
/
COMMENT ON COLUMN SYS.ADM_TAB_COMMENTS.TABLE_TYPE IS ‘Type of the object’
/
COMMENT ON COLUMN SYS.ADM_TAB_COMMENTS.COMMENTS IS ‘Comment on the object’
/

CREATE OR REPLACE VIEW DB_TAB_COMMENTS
(
OWNER,
TABLE_NAME,
TABLE_TYPE,
COMMENTS
)
AS
SELECT U.NAME, T.NAME, ‘TABLE’, C.TEXT
FROM SYS.SYS_TABLES T INNER JOIN SYS.SYS_USERS U ON U.ID = T.USER# AND T.RECYCLED = 0 INNER JOIN SYS.SYS_COMMENTS C ON T.USER# = C.USER# AND T.ID = C.TABLE# AND C.COLUMN# IS NULL
where (U.NAME = user or U.NAME = ‘SYS’ or user = ‘SYS’ or exists (select 1 from SYS.SYS_USER_ROLES S, SYS.DV_ME V where S.GRANTEE_ID = V.USER_ID and S.GRANTEE_TYPE = 0 and S.GRANTED_ROLE_ID = 0))
UNION ALL
SELECT U.NAME, V.NAME, ‘VIEW’, C.TEXT
FROM SYS.SYS_VIEWS V INNER JOIN SYS.SYS_USERS U ON U.ID = V.USER# INNER JOIN SYS.SYS_COMMENTS C ON V.USER# = C.USER# AND V.ID = C.TABLE# AND C.COLUMN# IS NULL
where (U.NAME = user or U.NAME = ‘SYS’ or user = ‘SYS’ or exists (select 1 from SYS.SYS_USER_ROLES S, SYS.DV_ME V where S.GRANTEE_ID = V.USER_ID and S.GRANTEE_TYPE = 0 and S.GRANTED_ROLE_ID = 0))
/
COMMENT ON COLUMN SYS.DB_TAB_COMMENTS.OWNER IS ‘Owner of the object’
/
COMMENT ON COLUMN SYS.DB_TAB_COMMENTS.TABLE_NAME IS ‘Name of the object’
/
COMMENT ON COLUMN SYS.DB_TAB_COMMENTS.TABLE_TYPE IS ‘Type of the object’
/
COMMENT ON COLUMN SYS.DB_TAB_COMMENTS.COMMENTS IS ‘Comment on the object’
/

CREATE OR REPLACE VIEW MY_COL_COMMENTS
(
TABLE_NAME,
COLUMN_NAME,
COMMENTS
)
AS
SELECT T.NAME, C.NAME, CO.TEXT
FROM SYS.DV_ME ME, SYS.SYS_TABLES T, SYS.SYS_COLUMNS C, SYS.SYS_COMMENTS CO WHERE ME.USER_ID = T.USER# AND T.RECYCLED = 0 AND T.USER# = C.USER# AND T.ID = C.TABLE# AND C.USER# = CO.USER# AND C.TAB
LE# = CO.TABLE# AND C.ID = CO.COLUMN#
UNION ALL
SELECT V.NAME, V_C.NAME, CO.TEXT
FROM SYS.DV_ME ME, SYS.SYS_VIEWS V, SYS.SYS_VIEW_COLS V_C, SYS.SYS_COMMENTS CO WHERE ME.USER_ID = V.USER# AND V.USER# = V_C.USER# AND V.ID = V_C.VIEW# AND V_C.USER# = CO.USER# AND V_C.VIEW# = CO.TA
BLE# AND V_C.ID = CO.COLUMN#
/
COMMENT ON COLUMN SYS.MY_COL_COMMENTS.TABLE_NAME IS ‘Name of the object’
/
COMMENT ON COLUMN SYS.MY_COL_COMMENTS.COLUMN_NAME IS ‘Name of the column’
/
COMMENT ON COLUMN SYS.MY_COL_COMMENTS.COMMENTS IS ‘Comment on the object’
/

CREATE OR REPLACE VIEW ADM_COL_COMMENTS
(
OWNER,
TABLE_NAME,
COLUMN_NAME,
COMMENTS
)
AS
SELECT U.NAME, T.NAME, C.NAME, CO.TEXT
FROM SYS.SYS_USERS U, SYS.SYS_TABLES T, SYS.SYS_COLUMNS C, SYS.SYS_COMMENTS CO WHERE U.ID = T.USER# AND T.RECYCLED = 0 AND T.USER# = C.USER# AND T.ID = C.TABLE# AND C.USER# = CO.USER# AND C.TABLE# =
CO.TABLE# AND C.ID = CO.COLUMN#
UNION ALL
SELECT U.NAME, V.NAME, V_C.NAME, CO.TEXT
FROM SYS.SYS_USERS U, SYS.SYS_VIEWS V, SYS.SYS_VIEW_COLS V_C, SYS.SYS_COMMENTS CO WHERE U.ID = V.USER# AND V.USER# = V_C.USER# AND V.ID = V_C.VIEW# AND V_C.USER# = CO.USER# AND V_C.VIEW# = CO.TABLE

AND V_C.ID = CO.COLUMN#

/

CREATE OR REPLACE VIEW DB_COL_COMMENTS
(
OWNER,
TABLE_NAME,
COLUMN_NAME,
COMMENTS
)
AS
SELECT U.NAME, T.NAME, C.NAME, CO.TEXT
FROM SYS.SYS_USERS U, SYS.SYS_TABLES T, SYS.SYS_COLUMNS C, SYS.SYS_COMMENTS CO WHERE U.ID = T.USER# AND T.RECYCLED = 0 AND T.USER# = C.USER# AND T.ID = C.TABLE# AND C.USER# = CO.USER# AND C.TABLE# =
CO.TABLE# AND C.ID = CO.COLUMN#
and (U.NAME = user or U.NAME = ‘SYS’ or user = ‘SYS’ or exists (select 1 from SYS.SYS_USER_ROLES S, SYS.DV_ME V where S.GRANTEE_ID = V.USER_ID and S.GRANTEE_TYPE = 0 and S.GRANTED_ROLE_ID = 0))
UNION ALL
SELECT U.NAME, V.NAME, V_C.NAME, CO.TEXT
FROM SYS.SYS_USERS U, SYS.SYS_VIEWS V, SYS.SYS_VIEW_COLS V_C, SYS.SYS_COMMENTS CO WHERE U.ID = V.USER# AND V.USER# = V_C.USER# AND V.ID = V_C.VIEW# AND V_C.USER# = CO.USER# AND V_C.VIEW# = CO.TABLE

AND V_C.ID = CO.COLUMN#

and (U.NAME = user or U.NAME = 'SYS' or user = 'SYS' or exists (select 1 from SYS.SYS_USER_ROLES S, SYS.DV_ME V where S.GRANTEE_ID = V.USER_ID and S.GRANTEE_TYPE = 0 and S.GRANTED_ROLE_ID = 0))

/

CREATE OR REPLACE VIEW DB_USERS
(
USER_ID,
USERNAME,
CREATED,
CRYPTOPERIOD
)
AS
SELECT U.ID, U.NAME, U.CTIME, decode(U.ASTATUS,
1 ,NUMTODSINTERVAL(0, ‘DAY’),
5 ,NUMTODSINTERVAL(0, ‘DAY’),
9 ,NUMTODSINTERVAL(0, ‘DAY’),
U.PTIME + NUMTODSINTERVAL(P.THRESHOLD / 86400.0, ‘DAY’) - CURRENT_TIMESTAMP() )
FROM SYS.SYS_USERS U JOIN SYS.SYS_PROFILE P ON U.PROFILE# = P.PROFILE# AND P.RESOURCE# = 1
/

CREATE OR REPLACE VIEW MY_SOURCE
(NAME, TYPE, LINE, TEXT)
AS
SELECT
P.NAME, DECODE(P.TYPE, ‘T’, ‘TRIGGER’, ‘P’, ‘PROCEDURE’, ‘F’, ‘FUNCTION’,‘UNDEFINED’)::VARCHAR(12),
NULL::NUMBER, P.SOURCE
FROM SYS.DV_ME ME, SYS.SYS_PROCS P
WHERE P.USER# = ME.USER_ID
/

CREATE OR REPLACE VIEW ADM_SOURCE
(OWNER, NAME, TYPE, LINE, TEXT)
AS
SELECT
U.NAME, P.NAME, DECODE(P.TYPE, ‘T’, ‘TRIGGER’, ‘P’, ‘PROCEDURE’, ‘F’, ‘FUNCTION’,‘UNDEFINED’),
NULL::NUMBER, P.SOURCE
FROM SYS.SYS_USERS U, SYS.SYS_PROCS P
WHERE P.USER# = U.ID
/

CREATE OR REPLACE VIEW DB_SOURCE
(OWNER, NAME, TYPE, LINE, TEXT)
AS
SELECT
U.NAME, P.NAME, DECODE(P.TYPE, ‘T’, ‘TRIGGER’, ‘P’, ‘PROCEDURE’, ‘F’, ‘FUNCTION’,‘UNDEFINED’),
NULL::NUMBER, P.SOURCE
FROM SYS.SYS_USERS U, SYS.SYS_PROCS P
WHERE P.USER# = U.ID and
(U.NAME = user or user = ‘SYS’ or exists (select 1 from SYS.SYS_USER_ROLES S, SYS.DV_ME V where S.GRANTEE_ID = V.USER_ID and S.GRANTEE_TYPE = 0 and S.GRANTED_ROLE_ID = 0))
/

CREATE OR REPLACE VIEW ADM_PROFILES
(
PROFILE,
RESOURCE_NAME,
RESOURCE_TYPE,
THRESHOLD
)
AS
SELECT P.NAME, M.NAME, DECODE(M.TYPE#, 0, ‘KERNEL’, 1, ‘PASSWORD’),
DECODE(P.THRESHOLD, 0, ‘UNLIMITED’, DECODE(P.RESOURCE#, 1, CAST(P.THRESHOLD/86400 AS NUMBER(16,6)), 2, CAST(P.THRESHOLD/86400 AS NUMBER(16,6)), 4, CAST(P.THRESHOLD/86400 AS NUMBER(16,6)), 5, CAST
(P.THRESHOLD/86400 AS NUMBER(16,6)), P.THRESHOLD))
FROM SYS.SYS_PROFILE P, SYS.DV_RESOURCE_MAP M WHERE P.RESOURCE# = M.RESOURCE# ORDER BY P.NAME, M.TYPE#
/

CREATE OR REPLACE VIEW SYS.DB_USER_SYS_PRIVS
(
USERNAME,
PRIVILEGE,
ADMIN_OPTION
)
AS
SELECT U.NAME,
DECODE(P.PRIVILEGE,
1, ‘ALTER ANY INDEX’,
2, ‘ALTER ANY MATERIALIZED VIEW’,
3, ‘ALTER ANY PROCEDURE’,
4, ‘ALTER ANY ROLE’,
5, ‘ALTER ANY SEQUENCE’,
6, ‘ALTER ANY TABLE’,
7, ‘ALTER ANY TRIGGER’,
8, ‘ALTER DATABASE’,
9, ‘ALTER PROFILE’,
10, ‘ALTER SESSION’,
11, ‘ALTER SYSTEM’,
12, ‘ALTER TABLESPACE’,
13, ‘ALTER USER’,
14, ‘CREATE ANY INDEX’,
15, ‘CREATE ANY MATERIALIZED VIEW’,
16, ‘CREATE ANY PROCEDURE’,
17, ‘CREATE ANY SEQUENCE’,
18, ‘CREATE ANY SYNONYM’,
19, ‘CREATE ANY TABLE’,
20, ‘CREATE ANY TRIGGER’,
21, ‘CREATE ANY VIEW’,
22, ‘CREATE DATABASE’,
23, ‘CREATE MATERIALIZED VIEW’,
24, ‘CREATE NODE’,
25, ‘CREATE PROCEDURE’,
26, ‘CREATE PROFILE’,
27, ‘CREATE PUBLIC SYNONYM’,
28, ‘CREATE ROLE’,
29, ‘CREATE SEQUENCE’,
30, ‘CREATE SESSION’,
31, ‘CREATE SYNONYM’,
32, ‘CREATE TABLE’,
33, ‘CREATE TABLESPACE’,
34, ‘CREATE TRIGGER’,
35, ‘CREATE USER’,
36, ‘CREATE VIEW’,
37, ‘CREATE DISTRIBUTE RULE’,
38, ‘DROP ANY INDEX’,
39, ‘DROP ANY MATERIALIZED VIEW’,
40, ‘DROP ANY PROCEDURE’,
41, ‘DROP ANY ROLE’,
42, ‘DROP ANY SEQUENCE’,
43, ‘DROP ANY SYNONYM’,
44, ‘DROP ANY TABLE’,
45, ‘DROP ANY TRIGGER’,
46, ‘DROP ANY VIEW’,
47, ‘DROP PROFILE’,
48, ‘DROP PUBLIC SYNONYM’,
49, ‘DROP TABLESPACE’,
50, ‘DROP USER’,
51, ‘FLASHBACK ANY TABLE’,
52, ‘FLASHBACK ARCHIVE ADMINISTER’,
53, ‘GLOBAL QUERY REWRITE’,
54, ‘GRANT ANY OBJECT PRIVILEGE’,
55, ‘GRANT ANY PRIVILEGE’,
56, ‘GRANT ANY ROLE’,
57, ‘LOCK ANY TABLE’,
58, ‘MANAGE TABLESPACE’,
59, ‘ON COMMIT REFRESH’,
60, ‘PURGE DBA_RECYCLEBIN’,
61, ‘READ ANY TABLE’,
62, ‘SELECT ANY SEQUENCE’,
63, ‘SELECT ANY TABLE’,
64, ‘UNLIMITED TABLESPACE’,
65, ‘UNDER ANY VIEW’,
66, ‘COMMENT ANY TABLE’,
67, ‘UPDATE ANY TABLE’,
68, ‘INSERT ANY TABLE’,
69, ‘DELETE ANY TABLE’,
70, ‘EXECUTE ANY PROCEDURE’,
71, ‘SYSBACKUP’,
72, ‘SYSDBA’,
73, ‘SYSOPER’,
74, ‘ANALYZE ANY’,
75, ‘DROP NODE’,
76, ‘ALTER NODE’,
‘UNKOWN’),
DECODE(P.ADMIN_OPTION, 0, ‘NO’, ‘YES’)
FROM SYS.SYS_USERS U INNER JOIN SYS.SYS_PRIVS P ON U.ID = P.GRANTEE_ID AND P.GRANTEE_TYPE = 0
/

CREATE OR REPLACE VIEW SYS.ROLE_SYS_PRIVS
(
ROLE,
PRIVILEGE,
ADMIN_OPTION
)
AS
SELECT R.NAME,
DECODE(P.PRIVILEGE,
1, ‘ALTER ANY INDEX’,
2, ‘ALTER ANY MATERIALIZED VIEW’,
3, ‘ALTER ANY PROCEDURE’,
4, ‘ALTER ANY ROLE’,
5, ‘ALTER ANY SEQUENCE’,
6, ‘ALTER ANY TABLE’,
7, ‘ALTER ANY TRIGGER’,
8, ‘ALTER DATABASE’,
9, ‘ALTER PROFILE’,
10, ‘ALTER SESSION’,
11, ‘ALTER SYSTEM’,
12, ‘ALTER TABLESPACE’,
13, ‘ALTER USER’,
14, ‘CREATE ANY INDEX’,
15, ‘CREATE ANY MATERIALIZED VIEW’,
16, ‘CREATE ANY PROCEDURE’,
17, ‘CREATE ANY SEQUENCE’,
18, ‘CREATE ANY SYNONYM’,
19, ‘CREATE ANY TABLE’,
20, ‘CREATE ANY TRIGGER’,
21, ‘CREATE ANY VIEW’,
22, ‘CREATE DATABASE’,
23, ‘CREATE MATERIALIZED VIEW’,
24, ‘CREATE NODE’,
25, ‘CREATE PROCEDURE’,
26, ‘CREATE PROFILE’,
27, ‘CREATE PUBLIC SYNONYM’,
28, ‘CREATE ROLE’,
29, ‘CREATE SEQUENCE’,
30, ‘CREATE SESSION’,
31, ‘CREATE SYNONYM’,
32, ‘CREATE TABLE’,
33, ‘CREATE TABLESPACE’,
34, ‘CREATE TRIGGER’,
35, ‘CREATE USER’,
36, ‘CREATE VIEW’,
37, ‘CREATE DISTRIBUTE RULE’,
38, ‘DROP ANY INDEX’,
39, ‘DROP ANY MATERIALIZED VIEW’,
40, ‘DROP ANY PROCEDURE’,
41, ‘DROP ANY ROLE’,
42, ‘DROP ANY SEQUENCE’,
43, ‘DROP ANY SYNONYM’,
44, ‘DROP ANY TABLE’,
45, ‘DROP ANY TRIGGER’,
46, ‘DROP ANY VIEW’,
47, ‘DROP PROFILE’,
48, ‘DROP PUBLIC SYNONYM’,
49, ‘DROP TABLESPACE’,
50, ‘DROP USER’,
51, ‘FLASHBACK ANY TABLE’,
52, ‘FLASHBACK ARCHIVE ADMINISTER’,
53, ‘GLOBAL QUERY REWRITE’,
54, ‘GRANT ANY OBJECT PRIVILEGE’,
55, ‘GRANT ANY PRIVILEGE’,
56, ‘GRANT ANY ROLE’,
57, ‘LOCK ANY TABLE’,
58, ‘MANAGE TABLESPACE’,
59, ‘ON COMMIT REFRESH’,
60, ‘PURGE DBA_RECYCLEBIN’,
61, ‘READ ANY TABLE’,
62, ‘SELECT ANY SEQUENCE’,
63, ‘SELECT ANY TABLE’,
64, ‘UNLIMITED TABLESPACE’,
65, ‘UNDER ANY VIEW’,
66, ‘COMMENT ANY TABLE’,
67, ‘UPDATE ANY TABLE’,
68, ‘INSERT ANY TABLE’,
69, ‘DELETE ANY TABLE’,
70, ‘EXECUTE ANY PROCEDURE’,
71, ‘SYSBACKUP’,
72, ‘SYSDBA’,
73, ‘SYSOPER’,
74, ‘ANALYZE ANY’,
75, ‘DROP NODE’,
76, ‘ALTER NODE’,
‘UNKOWN’),
DECODE(P.ADMIN_OPTION, 0, ‘NO’, ‘YES’)
FROM SYS.SYS_ROLES R INNER JOIN SYS.SYS_PRIVS P ON R.ID = P.GRANTEE_ID AND P.GRANTEE_TYPE = 1 where user = ‘SYS’ or exists (select * from SYS.SYS_USER_ROLES U, SYS.DV_ME V where U.GRANTEE_ID = V.USE
R_ID and U.GRANTEE_TYPE = 0 and (U.GRANTED_ROLE_ID = 0 or U.GRANTED_ROLE_ID = R.ID))
/

CREATE OR REPLACE VIEW SYS.ADM_SYS_PRIVS
(
GRANTEE,
PRIVILEGE,
ADMIN_OPTION
)
AS
SELECT * FROM SYS.DB_USER_SYS_PRIVS
UNION ALL
SELECT R.NAME,
DECODE(P.PRIVILEGE,
1, ‘ALTER ANY INDEX’,
2, ‘ALTER ANY MATERIALIZED VIEW’,
3, ‘ALTER ANY PROCEDURE’,
4, ‘ALTER ANY ROLE’,
5, ‘ALTER ANY SEQUENCE’,
6, ‘ALTER ANY TABLE’,
7, ‘ALTER ANY TRIGGER’,
8, ‘ALTER DATABASE’,
9, ‘ALTER PROFILE’,
10, ‘ALTER SESSION’,
11, ‘ALTER SYSTEM’,
12, ‘ALTER TABLESPACE’,
13, ‘ALTER USER’,
14, ‘CREATE ANY INDEX’,
15, ‘CREATE ANY MATERIALIZED VIEW’,
16, ‘CREATE ANY PROCEDURE’,
17, ‘CREATE ANY SEQUENCE’,
18, ‘CREATE ANY SYNONYM’,
19, ‘CREATE ANY TABLE’,
20, ‘CREATE ANY TRIGGER’,
21, ‘CREATE ANY VIEW’,
22, ‘CREATE DATABASE’,
23, ‘CREATE MATERIALIZED VIEW’,
24, ‘CREATE NODE’,
25, ‘CREATE PROCEDURE’,
26, ‘CREATE PROFILE’,
27, ‘CREATE PUBLIC SYNONYM’,
28, ‘CREATE ROLE’,
29, ‘CREATE SEQUENCE’,
30, ‘CREATE SESSION’,
31, ‘CREATE SYNONYM’,
32, ‘CREATE TABLE’,
33, ‘CREATE TABLESPACE’,
34, ‘CREATE TRIGGER’,
35, ‘CREATE USER’,
36, ‘CREATE VIEW’,
37, ‘CREATE DISTRIBUTE RULE’,
38, ‘DROP ANY INDEX’,
39, ‘DROP ANY MATERIALIZED VIEW’,
40, ‘DROP ANY PROCEDURE’,
41, ‘DROP ANY ROLE’,
42, ‘DROP ANY SEQUENCE’,
43, ‘DROP ANY SYNONYM’,
44, ‘DROP ANY TABLE’,
45, ‘DROP ANY TRIGGER’,
46, ‘DROP ANY VIEW’,
47, ‘DROP PROFILE’,
48, ‘DROP PUBLIC SYNONYM’,
49, ‘DROP TABLESPACE’,
50, ‘DROP USER’,
51, ‘FLASHBACK ANY TABLE’,
52, ‘FLASHBACK ARCHIVE ADMINISTER’,
53, ‘GLOBAL QUERY REWRITE’,
54, ‘GRANT ANY OBJECT PRIVILEGE’,
55, ‘GRANT ANY PRIVILEGE’,
56, ‘GRANT ANY ROLE’,
57, ‘LOCK ANY TABLE’,
58, ‘MANAGE TABLESPACE’,
59, ‘ON COMMIT REFRESH’,
60, ‘PURGE DBA_RECYCLEBIN’,
61, ‘READ ANY TABLE’,
62, ‘SELECT ANY SEQUENCE’,
63, ‘SELECT ANY TABLE’,
64, ‘UNLIMITED TABLESPACE’,
65, ‘UNDER ANY VIEW’,
66, ‘COMMENT ANY TABLE’,
67, ‘UPDATE ANY TABLE’,
68, ‘INSERT ANY TABLE’,
69, ‘DELETE ANY TABLE’,
70, ‘EXECUTE ANY PROCEDURE’,
71, ‘SYSBACKUP’,
72, ‘SYSDBA’,
73, ‘SYSOPER’,
74, ‘ANALYZE ANY’,
75, ‘DROP NODE’,
76, ‘ALTER NODE’,
‘UNKOWN’),
DECODE(P.ADMIN_OPTION, 0, ‘NO’, ‘YES’)
FROM SYS.SYS_ROLES R INNER JOIN SYS.SYS_PRIVS P ON R.ID = P.GRANTEE_ID AND P.GRANTEE_TYPE = 1
/

CREATE OR REPLACE VIEW SYS.MY_SYS_PRIVS
(
USERNAME,
PRIVILEGE,
ADMIN_OPTION
)
AS
SELECT M.USER_NAME,
DECODE(P.PRIVILEGE,
1, ‘ALTER ANY INDEX’,
2, ‘ALTER ANY MATERIALIZED VIEW’,
3, ‘ALTER ANY PROCEDURE’,
4, ‘ALTER ANY ROLE’,
5, ‘ALTER ANY SEQUENCE’,
6, ‘ALTER ANY TABLE’,
7, ‘ALTER ANY TRIGGER’,
8, ‘ALTER DATABASE’,
9, ‘ALTER PROFILE’,
10, ‘ALTER SESSION’,
11, ‘ALTER SYSTEM’,
12, ‘ALTER TABLESPACE’,
13, ‘ALTER USER’,
14, ‘CREATE ANY INDEX’,
15, ‘CREATE ANY MATERIALIZED VIEW’,
16, ‘CREATE ANY PROCEDURE’,
17, ‘CREATE ANY SEQUENCE’,
18, ‘CREATE ANY SYNONYM’,
19, ‘CREATE ANY TABLE’,
20, ‘CREATE ANY TRIGGER’,
21, ‘CREATE ANY VIEW’,
22, ‘CREATE DATABASE’,
23, ‘CREATE MATERIALIZED VIEW’,
24, ‘CREATE NODE’,
25, ‘CREATE PROCEDURE’,
26, ‘CREATE PROFILE’,
27, ‘CREATE PUBLIC SYNONYM’,
28, ‘CREATE ROLE’,
29, ‘CREATE SEQUENCE’,
30, ‘CREATE SESSION’,
31, ‘CREATE SYNONYM’,
32, ‘CREATE TABLE’,
33, ‘CREATE TABLESPACE’,
34, ‘CREATE TRIGGER’,
35, ‘CREATE USER’,
36, ‘CREATE VIEW’,
37, ‘CREATE DISTRIBUTE RULE’,
38, ‘DROP ANY INDEX’,
39, ‘DROP ANY MATERIALIZED VIEW’,
40, ‘DROP ANY PROCEDURE’,
41, ‘DROP ANY ROLE’,
42, ‘DROP ANY SEQUENCE’,
43, ‘DROP ANY SYNONYM’,
44, ‘DROP ANY TABLE’,
45, ‘DROP ANY TRIGGER’,
46, ‘DROP ANY VIEW’,
47, ‘DROP PROFILE’,
48, ‘DROP PUBLIC SYNONYM’,
49, ‘DROP TABLESPACE’,
50, ‘DROP USER’,
51, ‘FLASHBACK ANY TABLE’,
52, ‘FLASHBACK ARCHIVE ADMINISTER’,
53, ‘GLOBAL QUERY REWRITE’,
54, ‘GRANT ANY OBJECT PRIVILEGE’,
55, ‘GRANT ANY PRIVILEGE’,
56, ‘GRANT ANY ROLE’,
57, ‘LOCK ANY TABLE’,
58, ‘MANAGE TABLESPACE’,
59, ‘ON COMMIT REFRESH’,
60, ‘PURGE DBA_RECYCLEBIN’,
61, ‘READ ANY TABLE’,
62, ‘SELECT ANY SEQUENCE’,
63, ‘SELECT ANY TABLE’,
64, ‘UNLIMITED TABLESPACE’,
65, ‘UNDER ANY VIEW’,
66, ‘COMMENT ANY TABLE’,
67, ‘UPDATE ANY TABLE’,
68, ‘INSERT ANY TABLE’,
69, ‘DELETE ANY TABLE’,
70, ‘EXECUTE ANY PROCEDURE’,
71, ‘SYSBACKUP’,
72, ‘SYSDBA’,
73, ‘SYSOPER’,
74, ‘ANALYZE ANY’,
75, ‘DROP NODE’,
76, ‘ALTER NODE’,
‘UNKOWN’),
DECODE(P.ADMIN_OPTION, 0, ‘NO’, ‘YES’)
FROM SYS.DV_ME M INNER JOIN SYS.SYS_PRIVS P ON M.USER_ID = P.GRANTEE_ID AND P.GRANTEE_TYPE = 0
/

CREATE OR REPLACE VIEW SYS.ADM_ROLE_PRIVS
(
GRANTEE,
GRANTED_ROLE,
ADMIN_OPTION
)
AS
SELECT U.NAME, R.NAME, DECODE(P.ADMIN_OPTION, 0, ‘NO’, ‘YES’)
FROM SYS.SYS_USERS U, SYS.SYS_ROLES R, SYS.SYS_USER_ROLES P
WHERE P.GRANTEE_TYPE = 0 AND U.ID = P.GRANTEE_ID AND R.ID = P.GRANTED_ROLE_ID
UNION ALL
SELECT R1.NAME, R2.NAME, DECODE(P.ADMIN_OPTION, 0, ‘NO’, ‘YES’)
FROM SYS.SYS_ROLES R1, SYS.SYS_ROLES R2, SYS.SYS_USER_ROLES P
WHERE P.GRANTEE_TYPE = 1 AND R1.ID = P.GRANTEE_ID AND R2.ID = P.GRANTED_ROLE_ID
/

CREATE OR REPLACE VIEW SYS.MY_ROLE_PRIVS
(
USERNAME,
GRANTED_ROLE,
ADMIN_OPTION
)
AS
SELECT M.USER_NAME, R.NAME, DECODE(P.ADMIN_OPTION, 0, ‘NO’, ‘YES’)
FROM SYS.DV_ME M, SYS.SYS_ROLES R, SYS.SYS_USER_ROLES P
WHERE P.GRANTEE_TYPE = 0 AND M.USER_ID = P.GRANTEE_ID AND R.ID = P.GRANTED_ROLE_ID
/

CREATE OR REPLACE VIEW SYS.ADM_TAB_PRIVS
(
GRANTEE,
OWNER,
OBJECT_NAME,
OBJECT_TYPE,
PRIVILEGE,
GRANTABLE
)
AS
SELECT U1.NAME, U2.NAME, P.OBJECT_NAME,
DECODE(P.OBJECT_TYPE, 0, ‘TABLE’, 1, ‘VIEW’, 2, ‘SEQUENCE’, 3, ‘PROCEDURE’, ‘UNKNOWN’),
DECODE(P.PRIVILEGE, 0, ‘ALTER’, 1, ‘DELETE’, 2, ‘EXECUTE’, 3, ‘INDEX’, 4, ‘INSERT’, 5, ‘READ’, 6, ‘REFERENCES’, 7, ‘SELECT’, 8, ‘UPDATE’, ‘UNKNOWN’),
DECODE(P.GRANTABLE, 0, ‘NO’, 1, ‘YES’, ‘-’)
FROM SYS.SYS_USERS U1, SYS.SYS_USERS U2, SYS.SYS_OBJECT_PRIVS P
WHERE P.GRANTEE_TYPE = 0 AND U1.ID = P.GRANTEE AND P.OBJECT_OWNER = U2.ID
UNION ALL
SELECT R.NAME, U.NAME, P.OBJECT_NAME,
DECODE(P.OBJECT_TYPE, 0, ‘TABLE’, 1, ‘VIEW’, 2, ‘SEQUENCE’, 3, ‘PROCEDURE’, ‘UNKNOWN’),
DECODE(P.PRIVILEGE, 0, ‘ALTER’, 1, ‘DELETE’, 2, ‘EXECUTE’, 3, ‘INDEX’, 4, ‘INSERT’, 5, ‘READ’, 6, ‘REFERENCES’, 7, ‘SELECT’, 8, ‘UPDATE’, ‘UNKNOWN’),
DECODE(P.GRANTABLE, 0, ‘NO’, 1, ‘YES’, ‘-’)
FROM SYS.SYS_ROLES R, SYS.SYS_USERS U, SYS.SYS_OBJECT_PRIVS P
WHERE P.GRANTEE_TYPE = 1 AND R.ID = P.GRANTEE AND P.OBJECT_OWNER = U.ID
/

CREATE OR REPLACE VIEW SYS.MY_TAB_PRIVS
(
GRANTEE,
OWNER,
OBJECT_NAME,
OBJECT_TYPE,
PRIVILEGE,
GRANTABLE
)
AS
SELECT M.USER_NAME, U.NAME, P.OBJECT_NAME,
DECODE(P.OBJECT_TYPE, 0, ‘TABLE’, 1, ‘VIEW’, 2, ‘SEQUENCE’, 3, ‘PROCEDURE’, ‘UNKNOWN’),
DECODE(P.PRIVILEGE, 0, ‘ALTER’, 1, ‘DELETE’, 2, ‘EXECUTE’, 3, ‘INDEX’, 4, ‘INSERT’, 5, ‘READ’, 6, ‘REFERENCES’, 7, ‘SELECT’, 8, ‘UPDATE’, ‘UNKNOWN’),
DECODE(P.GRANTABLE, 0, ‘NO’, 1, ‘YES’, ‘-’)
FROM SYS.DV_ME M, SYS.SYS_USERS U, SYS.SYS_OBJECT_PRIVS P
WHERE P.GRANTEE_TYPE = 0 AND M.USER_ID = P.GRANTEE AND P.OBJECT_OWNER = U.ID
/

CREATE OR REPLACE VIEW ADM_SEQUENCES
(
SEQUENCE_OWNER,
SEQUENCE_NAME,
MIN_VALUE,
MAX_VALUE,
INCREMENT_BY,
CYCLE_FLAG,
ORDER_FLAG,
CACHE_SIZE,
LAST_NUMBER
)
AS
SELECT U.NAME, SEQ.NAME, SEQ.MINVAL, SEQ.MAXVAL, SEQ.STEP, SEQ.CYCLE_FLAG, SEQ.ORDER_FLAG, SEQ.CACHESIZE, SEQ.LAST_NUMBER
FROM SYS.SYS_USERS U, SYS.SYS_SEQUENCES SEQ
WHERE SEQ.UID = U.ID
/

CREATE OR REPLACE VIEW DB_SEQUENCES
(
SEQUENCE_OWNER,
SEQUENCE_NAME,
MIN_VALUE,
MAX_VALUE,
INCREMENT_BY,
CYCLE_FLAG,
ORDER_FLAG,
CACHE_SIZE,
LAST_NUMBER
)
AS
SELECT U.NAME, SEQ.NAME, SEQ.MINVAL, SEQ.MAXVAL, SEQ.STEP, SEQ.CYCLE_FLAG, SEQ.ORDER_FLAG, SEQ.CACHESIZE, SEQ.LAST_NUMBER
FROM SYS.SYS_USERS U, SYS.SYS_SEQUENCES SEQ
WHERE SEQ.UID = U.ID
and (U.NAME = user or U.NAME = ‘SYS’ or user = ‘SYS’ or exists (select 1 from SYS.SYS_USER_ROLES S, SYS.DV_ME V where S.GRANTEE_ID = V.USER_ID and S.GRANTEE_TYPE = 0 and S.GRANTED_ROLE_ID = 0))
/

CREATE OR REPLACE VIEW MY_SEQUENCES
(
SEQUENCE_NAME,
MIN_VALUE,
MAX_VALUE,
INCREMENT_BY,
CYCLE_FLAG,
ORDER_FLAG,
CACHE_SIZE,
LAST_NUMBER
)
AS
SELECT SEQ.NAME, SEQ.MINVAL, SEQ.MAXVAL, SEQ.STEP, SEQ.CYCLE_FLAG, SEQ.ORDER_FLAG, SEQ.CACHESIZE, SEQ.LAST_NUMBER
FROM SYS.DV_ME M, SYS.SYS_SEQUENCES SEQ
WHERE SEQ.UID = M.USER_ID
/

CREATE OR REPLACE VIEW SYS.DB_IND_COLUMNS
(
INDEX_OWNER,
INDEX_NAME,
TABLE_OWNER,
TABLE_NAME,
COLUMN_NAME,
COLUMN_POSITION,
COLUMN_LENGTH,
ID,
DEFAULT_TEXT
)
AS
SELECT
U.NAME,
I.NAME,
U.NAME,
T.NAME,
C.NAME,
DBMS_DIAGNOSE.DBA_IND_POS(I.COL_LIST::VARCHAR(128), C.ID::VARCHAR(128))::INTEGER,
C.BYTES,
C.ID,
C.DEFAULT_TEXT
FROM SYS.SYS_USERS U, SYS.SYS_INDEXES I, SYS.SYS_COLUMNS C, SYS.SYS_TABLES T
WHERE
I.USER# = U.ID
AND T.USER# = U.ID
AND C.USER# = U.ID
AND C.TABLE# = I.TABLE#
AND T.ID = I.TABLE#
AND INSTR(’,’ || I.COL_LIST ||’,’, ‘,’ || C.ID ||’,’) > 0
AND T.RECYCLED = 0
and (U.NAME = user or U.NAME = ‘SYS’ or user = ‘SYS’ or exists (select 1 from SYS.SYS_USER_ROLES S, SYS.DV_ME V where S.GRANTEE_ID = V.USER_ID and S.GRANTEE_TYPE = 0 and S.GRANTED_ROLE_ID = 0))
/

CREATE OR REPLACE VIEW SYS.ADM_IND_COLUMNS
(
INDEX_OWNER,
INDEX_NAME,
TABLE_OWNER,
TABLE_NAME,
COLUMN_NAME,
COLUMN_POSITION,
COLUMN_LENGTH
)
AS
SELECT
U.NAME,
I.NAME,
U.NAME,
T.NAME,
C.NAME,
DBMS_DIAGNOSE.DBA_IND_POS(I.COL_LIST::VARCHAR(128), C.ID::VARCHAR(128))::INTEGER,
C.BYTES
FROM SYS.SYS_USERS U, SYS.SYS_INDEXES I, SYS.SYS_COLUMNS C, SYS.SYS_TABLES T
WHERE
I.USER# = U.ID
AND T.USER# = U.ID
AND C.USER# = U.ID
AND C.TABLE# = I.TABLE#
AND T.ID = I.TABLE#
AND INSTR(’,’ || I.COL_LIST ||’,’, ‘,’ || C.ID ||’,’) > 0
AND T.RECYCLED = 0
/

CREATE OR REPLACE VIEW SYS.MY_IND_COLUMNS
(
INDEX_NAME,
TABLE_NAME,
COLUMN_NAME,
COLUMN_POSITION,
COLUMN_LENGTH
)
AS
SELECT I.NAME,
T.NAME,
C.NAME,
DBMS_DIAGNOSE.DBA_IND_POS(I.COL_LIST::VARCHAR(128), C.ID::VARCHAR(128))::INTEGER,
C.BYTES
FROM SYS.DV_ME ME, SYS.SYS_INDEXES I, SYS.SYS_COLUMNS C, SYS.SYS_TABLES T
WHERE
I.USER# = ME.USER_ID
AND T.USER# = ME.USER_ID
AND C.USER# = ME.USER_ID
AND I.TABLE# = T.ID
AND C.TABLE# = T.ID
AND INSTR(’,’ || I.COL_LIST || ‘,’, ‘,’ || C.ID || ‘,’) > 0
AND T.RECYCLED = 0
/

CREATE OR REPLACE VIEW SYS.ADM_DATA_FILES
(
FILE_NAME,
FILE_ID,
TABLESPACE_NAME,
BYTES,
BLOCKS,
STATUS,
RELATIVE_FNO,
AUTOEXTENSIBLE,
MAXBYTES,
MAXBLOCKS,
INCREMENT_BY,
USER_BYTES,
USER_BLOCKS,
ONLINE_STATUS
)
AS
SELECT D.FILE_NAME, D.ID, T.NAME, D.BYTES, (D.BYTES / (RTRIM(P.VALUE, ‘Kk’)::BINARY_INTEGER * 1024))::BINARY_BIGINT, ‘VALID’, D.ID, DECODE(D.AUTO_EXTEND, ‘TRUE’, ‘YES’, ‘NO’),
D.MAX_SIZE::BINARY_BIGINT,
(D.MAX_SIZE / (RTRIM(P.VALUE, ‘Kk’)::BINARY_INTEGER * 1024))::BINARY_BIGINT,
D.AUTO_EXTEND_SIZE,
(D.BYTES - 20 * (D.BYTES / (RTRIM(P.VALUE, ‘Kk’)::BINARY_INTEGER * 1024)))::BINARY_BIGINT,
((D.BYTES - 20 * (D.BYTES / (RTRIM(P.VALUE, ‘Kk’)::BINARY_INTEGER * 1024))) / (RTRIM(P.VALUE, ‘Kk’)::BINARY_INTEGER * 1024))::BINARY_BIGINT,
D.STATUS
FROM SYS.DV_DATA_FILES D, SYS.DV_TABLESPACES T, SYS.DV_PARAMETERS P
WHERE D.TABLESPACE_ID = T.ID AND P.NAME = ‘PAGE_SIZE’
/

CREATE OR REPLACE VIEW SYS.ADM_TAB_MODIFICATIONS
(
TABLE_OWNER,
TABLE_NAME,
PARTITION_NAME,
SUBPARTITION_NAME,
INSERTS,
UPDATES,
DELETES,
TIMESTAMP,
DROP_SEGMENTS
) AS
SELECT U.NAME, T.NAME, null::VARCHAR(64), null::VARCHAR(64), MO.INSERTS, MO.UPDATES, MO.DELETES, MO.MODIFY_TIME, MO.DROP_SEGMENTS
FROM SYS.SYS_USERS U JOIN SYS.SYS_TABLES T ON U.ID = T.USER#
JOIN SYS.SYS_DML_STATS MO ON T.USER# = MO.USER# AND T.ID = MO.TABLE# WHERE MO.PARTED = 0 OR (MO.PARTED = 1 AND MO.PART#=-1)
UNION ALL
SELECT U.NAME, T.NAME, TP.NAME, null::VARCHAR(64), MO.INSERTS, MO.UPDATES, MO.DELETES, MO.MODIFY_TIME, MO.DROP_SEGMENTS
FROM SYS.SYS_USERS U JOIN SYS.SYS_TABLES T ON U.ID = T.USER#
JOIN SYS.SYS_TABLE_PARTS TP ON T.USER# = TP.USER# AND T.ID = TP.TABLE#
JOIN SYS.SYS_DML_STATS MO ON T.USER# = MO.USER# AND T.ID = MO.TABLE# AND MO.PART# = TP.PART#
WHERE MO.PARTED = 1 AND MO.PART# <> -1
/

CREATE OR REPLACE VIEW SYS.DB_TAB_MODIFICATIONS
(
TABLE_OWNER,
TABLE_NAME,
PARTITION_NAME,
SUBPARTITION_NAME,
INSERTS,
UPDATES,
DELETES,
TIMESTAMP,
DROP_SEGMENTS
) AS
SELECT U.NAME, T.NAME, null::VARCHAR(64), null::VARCHAR(64), MO.INSERTS, MO.UPDATES, MO.DELETES, MO.MODIFY_TIME, MO.DROP_SEGMENTS
FROM SYS.SYS_USERS U JOIN SYS.SYS_TABLES T ON U.ID = T.USER#
JOIN SYS.SYS_DML_STATS MO ON T.USER# = MO.USER# AND T.ID = MO.TABLE# WHERE MO.PARTED = 0 OR (MO.PARTED = 1 AND MO.PART#=-1)
UNION ALL
SELECT U.NAME, T.NAME, TP.NAME, null::VARCHAR(64), MO.INSERTS, MO.UPDATES, MO.DELETES, MO.MODIFY_TIME, MO.DROP_SEGMENTS
FROM SYS.SYS_USERS U JOIN SYS.SYS_TABLES T ON U.ID = T.USER#
JOIN SYS.SYS_TABLE_PARTS TP ON T.USER# = TP.USER# AND T.ID = TP.TABLE#
JOIN SYS.SYS_DML_STATS MO ON T.USER# = MO.USER# AND T.ID = MO.TABLE# AND MO.PART# = TP.PART#
WHERE MO.PARTED = 1 AND MO.PART# <> -1
/
CREATE OR REPLACE VIEW SYS.MY_TAB_MODIFICATIONS
(
TABLE_NAME,
PARTITION_NAME,
SUBPARTITION_NAME,
INSERTS,
UPDATES,
DELETES,
TIMESTAMP,
DROP_SEGMENTS
) AS
SELECT T.NAME, null::VARCHAR(64), null::VARCHAR(64), MO.INSERTS, MO.UPDATES, MO.DELETES, MO.MODIFY_TIME, MO.DROP_SEGMENTS
FROM SYS.DV_ME M JOIN SYS.SYS_TABLES T ON M.USER_ID = T.USER#
JOIN SYS.SYS_DML_STATS MO ON T.USER# = MO.USER# AND T.ID = MO.TABLE# WHERE MO.PARTED = 0 OR (MO.PARTED = 1 AND MO.PART#=-1)
UNION ALL
SELECT T.NAME, TP.NAME, null::VARCHAR(64), MO.INSERTS, MO.UPDATES, MO.DELETES, MO.MODIFY_TIME, MO.DROP_SEGMENTS
FROM SYS.DV_ME M JOIN SYS.SYS_TABLES T ON M.USER_ID = T.USER#
JOIN SYS.SYS_TABLE_PARTS TP ON T.USER# = TP.USER# AND T.ID = TP.TABLE#
JOIN SYS.SYS_DML_STATS MO ON T.USER# = MO.USER# AND T.ID = MO.TABLE# AND MO.PART# = TP.PART#
WHERE MO.PARTED = 1 AND MO.PART# <> -1
/

CREATE OR REPLACE PUBLIC SYNONYM DB_TAB_MODIFICATIONS FOR SYS.DB_TAB_MODIFICATIONS
/
CREATE OR REPLACE PUBLIC SYNONYM MY_TAB_MODIFICATIONS FOR SYS.MY_TAB_MODIFICATIONS
/
GRANT SELECT ON MY_TAB_MODIFICATIONS TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM ADM_TAB_MODIFICATIONS FOR SYS.ADM_TAB_MODIFICATIONS
/

CREATE OR REPLACE VIEW MY_TAB_PARTITIONS AS
SELECT
T.NAME AS TABLE_NAME,
‘NO’::VARCHAR2(3) AS COMPOSITE,
TP.NAME AS PARTITION_NAME,
0::NUMBER AS SUBPARTITION_COUNT,
TP.HIBOUNDVAL AS HIGH_VALUE,
HIBOUNDLEN::NUMBER AS HIGH_VALUE_LENGTH,
ROW_NUMBER() OVER (PARTITION BY T.NAME ORDER BY TP.PART#) AS PARTITION_POSITION,
TS.NAME AS TABLESPACE_NAME,
(CASE WHEN TP.PART# < 1073741824 THEN ‘N’ ELSE ‘Y’ END) AS INTERVAL,
TP.PCTFREE AS PCT_FREE,
NULL::NUMBER AS PCT_USED,
TP.INITRANS AS INI_TRANS,
NULL::NUMBER AS MAX_TRANS,
NULL::NUMBER AS INITIAL_EXTENT,
NULL::NUMBER AS NEXT_EXTENT,
NULL::NUMBER AS MIN_EXTENT,
NULL::NUMBER AS MAX_EXTENT,
NULL::NUMBER AS MAX_SIZE,
NULL::NUMBER AS PCT_INCREASE,
NULL::NUMBER AS FREELISTS,
NULL::NUMBER AS FREELIST_GROUPS,
NULL::VARCHAR2(7) AS LOGGING,
NULL::VARCHAR2(8) AS COMPRESSION,
NULL::VARCHAR2(18) AS COMPRESS_FOR,
TP.ROWCNT AS NUM_ROWS,
TP.BLKCNT AS BLOCKS,
TP.EMPCNT AS EMPTY_BLOCKS,
NULL::NUMBER AS AVG_SPACE,
NULL::NUMBER AS CHAIN_CNT,
TP.AVGRLN AS AVG_ROW_LEN,
TP.SAMPLESIZE AS SAMPLE_SIZE,
TP.ANALYZETIME AS LAST_ANALYZED,
NULL::VARCHAR2(7) AS BUFFER_POOL,
NULL::VARCHAR2(3) AS GLOBAL_STATS,
NULL::VARCHAR2(3) AS USER_STATS
FROM SYS.DV_ME M, SYS.SYS_TABLE_PARTS TP, SYS.SYS_TABLES T, SYS.DV_TABLESPACES TS
WHERE TP.TABLE#=T.ID AND TP.USER# = T.USER# AND TP.SPACE#=TS.ID AND TP.USER# = M.USER_ID
/

CREATE OR REPLACE VIEW ADM_TAB_PARTITIONS AS
SELECT
U.NAME AS TABLE_OWNER,
T.NAME AS TABLE_NAME,
‘NO’::VARCHAR2(3) AS COMPOSITE,
TP.NAME AS PARTITION_NAME,
0::NUMBER AS SUBPARTITION_COUNT,
TP.HIBOUNDVAL AS HIGH_VALUE,
NULL::NUMBER AS HIGH_VALUE_LENGTH,
ROW_NUMBER() OVER (PARTITION BY U.NAME, T.NAME ORDER BY TP.PART#) AS PARTITION_POSITION,
TS.NAME AS TABLESPACE_NAME,
(CASE WHEN TP.PART# < 1073741824 THEN ‘N’ ELSE ‘Y’ END) AS INTERVAL,
TP.PCTFREE AS PCT_FREE,
NULL::NUMBER AS PCT_USED,
TP.INITRANS AS INI_TRANS,
NULL::NUMBER AS MAX_TRANS,
NULL::NUMBER AS INITIAL_EXTENT,
NULL::NUMBER AS NEXT_EXTENT,
NULL::NUMBER AS MIN_EXTENT,
NULL::NUMBER AS MAX_EXTENT,
NULL::NUMBER AS MAX_SIZE,
NULL::NUMBER AS PCT_INCREASE,
NULL::NUMBER AS FREELISTS,
NULL::NUMBER AS FREELIST_GROUPS,
NULL::VARCHAR2(7) AS LOGGING,
NULL::VARCHAR2(8) AS COMPRESSION,
NULL::VARCHAR2(18) AS COMPRESS_FOR,
TP.ROWCNT AS NUM_ROWS,
TP.BLKCNT AS BLOCKS,
TP.EMPCNT AS EMPTY_BLOCKS,
NULL::NUMBER AS AVG_SPACE,
NULL::NUMBER AS CHAIN_CNT,
TP.AVGRLN AS AVG_ROW_LEN,
TP.SAMPLESIZE AS SAMPLE_SIZE,
TP.ANALYZETIME AS LAST_ANALYZED,
NULL::VARCHAR2(7) AS BUFFER_POOL,
NULL::VARCHAR2(3) AS GLOBAL_STATS,
NULL::VARCHAR2(3) AS USER_STATS
FROM SYS.SYS_USERS U, SYS.SYS_TABLE_PARTS TP, SYS.SYS_TABLES T, SYS.DV_TABLESPACES TS
WHERE TP.USER# = U.ID AND TP.USER# = T.USER# AND TP.TABLE#=T.ID AND TP.SPACE#=TS.ID
/

CREATE OR REPLACE VIEW DB_TAB_PARTITIONS AS
SELECT
U.NAME AS TABLE_OWNER,
T.NAME AS TABLE_NAME,
‘NO’::VARCHAR2(3) AS COMPOSITE,
TP.NAME AS PARTITION_NAME,
0::NUMBER AS SUBPARTITION_COUNT,
TP.HIBOUNDVAL AS HIGH_VALUE,
NULL::NUMBER AS HIGH_VALUE_LENGTH,
ROW_NUMBER() OVER (PARTITION BY U.NAME, T.NAME ORDER BY TP.PART#) AS PARTITION_POSITION,
TS.NAME AS TABLESPACE_NAME,
(CASE WHEN TP.PART# < 1073741824 THEN ‘N’ ELSE ‘Y’ END) AS INTERVAL,
TP.PCTFREE AS PCT_FREE,
NULL::NUMBER AS PCT_USED,
TP.INITRANS AS INI_TRANS,
NULL::NUMBER AS MAX_TRANS,
NULL::NUMBER AS INITIAL_EXTENT,
NULL::NUMBER AS NEXT_EXTENT,
NULL::NUMBER AS MIN_EXTENT,
NULL::NUMBER AS MAX_EXTENT,
NULL::NUMBER AS MAX_SIZE,
NULL::NUMBER AS PCT_INCREASE,
NULL::NUMBER AS FREELISTS,
NULL::NUMBER AS FREELIST_GROUPS,
NULL::VARCHAR2(7) AS LOGGING,
NULL::VARCHAR2(8) AS COMPRESSION,
NULL::VARCHAR2(18) AS COMPRESS_FOR,
TP.ROWCNT AS NUM_ROWS,
TP.BLKCNT AS BLOCKS,
TP.EMPCNT AS EMPTY_BLOCKS,
NULL::NUMBER AS AVG_SPACE,
NULL::NUMBER AS CHAIN_CNT,
TP.AVGRLN AS AVG_ROW_LEN,
TP.SAMPLESIZE AS SAMPLE_SIZE,
TP.ANALYZETIME AS LAST_ANALYZED,
NULL::VARCHAR2(7) AS BUFFER_POOL,
NULL::VARCHAR2(3) AS GLOBAL_STATS,
NULL::VARCHAR2(3) AS USER_STATS
FROM SYS.SYS_USERS U, SYS.SYS_TABLE_PARTS TP, SYS.SYS_TABLES T, SYS.DV_TABLESPACES TS
WHERE TP.USER# = U.ID AND TP.USER# = T.USER# AND TP.TABLE#=T.ID AND TP.SPACE#=TS.ID
and (U.NAME = user or U.NAME = ‘SYS’ or user = ‘SYS’ or exists (select 1 from SYS.SYS_USER_ROLES S, SYS.DV_ME V where S.GRANTEE_ID = V.USER_ID and S.GRANTEE_TYPE = 0 and S.GRANTED_ROLE_ID = 0))
/

CREATE OR REPLACE VIEW MY_PROCEDURES
(
OBJECT_NAME,
PROCEDURE_NAME,
OBJECT_ID,
SUBPROGRAM_ID,
OVERLOAD,
OBJECT_TYPE,
AGGREGATE,
PIPELINED,
SOURCE,
STATUS
)
AS
SELECT P.NAME, NULL::VARCHAR(128), P.OBJ#, 1, NULL::VARCHAR2(40), DECODE(P.TYPE, ‘T’, ‘TRIGGER’, ‘F’, ‘FUNCTION’, ‘PROCEDURE’)::VARCHAR2(19),
DECODE(P.AGGREGATE, 0, ‘NO’, ‘YES’)::VARCHAR2(3), DECODE(P.PIPELINED, 0, ‘NO’, ‘YES’)::VARCHAR2(3), P.SOURCE, DECODE(P.STATUS, 0, ‘INVALID’, 1, ‘VALID’, ‘UNKOWN’)::VARCHAR2(7)
FROM SYS.DV_ME ME, SYS.SYS_PROCS P WHERE P.USER# = ME.USER_ID
/

CREATE OR REPLACE VIEW ADM_PROCEDURES
(
OWNER,
OBJECT_NAME,
PROCEDURE_NAME,
OBJECT_ID,
SUBPROGRAM_ID,
OVERLOAD,
OBJECT_TYPE,
AGGREGATE,
PIPELINED,
SOURCE,
STATUS
)
AS
SELECT U.NAME, P.NAME, NULL::VARCHAR(128), P.OBJ#, 1, NULL::VARCHAR2(40), DECODE(P.TYPE, ‘T’, ‘TRIGGER’, ‘F’, ‘FUNCTION’, ‘PROCEDURE’)::VARCHAR2(19),
DECODE(P.AGGREGATE, 0, ‘NO’, ‘YES’)::VARCHAR2(3), DECODE(P.PIPELINED, 0, ‘NO’, ‘YES’)::VARCHAR2(3), P.SOURCE, DECODE(P.STATUS, 0, ‘INVALID’, 1, ‘VALID’, ‘UNKOWN’)::VARCHAR2(7)
FROM SYS.SYS_USERS U, SYS.SYS_PROCS P WHERE P.USER# = U.ID
/

CREATE OR REPLACE VIEW DB_PROCEDURES
(
OWNER,
OBJECT_NAME,
PROCEDURE_NAME,
OBJECT_ID,
SUBPROGRAM_ID,
OVERLOAD,
OBJECT_TYPE,
AGGREGATE,
PIPELINED,
SOURCE,
STATUS
)
AS
SELECT U.NAME, P.NAME, NULL::VARCHAR(128), P.OBJ#, 1, NULL::VARCHAR2(40), DECODE(P.TYPE, ‘T’, ‘TRIGGER’, ‘F’, ‘FUNCTION’, ‘PROCEDURE’)::VARCHAR2(19),
DECODE(P.AGGREGATE, 0, ‘NO’, ‘YES’)::VARCHAR2(3), DECODE(P.PIPELINED, 0, ‘NO’, ‘YES’)::VARCHAR2(3), P.SOURCE, DECODE(P.STATUS, 0, ‘INVALID’, 1, ‘VALID’, ‘UNKOWN’)::VARCHAR2(7)
FROM SYS.SYS_USERS U, SYS.SYS_PROCS P WHERE P.USER# = U.ID
and (U.NAME = user or user = ‘SYS’ or exists (select 1 from SYS.SYS_USER_ROLES S, SYS.DV_ME V where S.GRANTEE_ID = V.USER_ID and S.GRANTEE_TYPE = 0 and S.GRANTED_ROLE_ID = 0))
/

CREATE OR REPLACE VIEW SYS.ADM_USERS
(
USERNAME,
USER_ID,
ACCOUNT_STATUS,
LOCK_DATE,
EXPIRY_DATE,
DEFAULT_TABLESPACE,
TEMPORARY_TABLESPACE,
CREATED,
PROFILE,
INITIAL_RSRC_CONSUMER_GROUP,
AUTHENTICATION_TYPE
)
AS
SELECT DISTINCT U.NAME, U.ID,
DECODE(U.ASTATUS,
0, ‘OPEN’,
1, ‘EXPIRED’,
2, ‘EXPIRED(GRACE)’,
4, ‘LOCKED(TIMED)’,
5, ‘EXPIRED & LOCKED(TIMED)’,
6, ‘EXPIRED(GRACE) & LOCKED(TIMED)’,
8, ‘LOCKED’,
9, ‘EXPIRED & LOCKED’,
10, ‘EXPIRED(GRACE) & LOCKED’, ‘UNKNOWN’),
U.LTIME, U.EXPTIME, S1.NAME, S2.NAME, U.CTIME, P.NAME, DECODE(U.ID, 0, ‘SYS_GROUP’, ‘DEFAULT_CONSUMER_GROUP’), ‘PASSWORD’
FROM SYS.SYS_USERS U, SYS.SYS_PROFILE P, SYS.DV_TABLESPACES S1, SYS.DV_TABLESPACES S2
WHERE U.DATA_SPACE# = S1.ID AND U.TEMP_SPACE# = S2.ID AND U.PROFILE# = P.PROFILE#
/

CREATE OR REPLACE VIEW SYS.MY_USERS
(
USERNAME,
USER_ID,
ACCOUNT_STATUS,
LOCK_DATE,
EXPIRY_DATE,
DEFAULT_TABLESPACE,
TEMPORARY_TABLESPACE,
CREATED,
INITIAL_RSRC_CONSUMER_GROUP
)
AS
SELECT U.NAME, U.ID,
DECODE(U.ASTATUS,
0, ‘OPEN’,
1, ‘EXPIRED’,
2, ‘EXPIRED(GRACE)’,
4, ‘LOCKED(TIMED)’,
5, ‘EXPIRED & LOCKED(TIMED)’,
6, ‘EXPIRED(GRACE) & LOCKED(TIMED)’,
8, ‘LOCKED’,
9, ‘EXPIRED & LOCKED’,
10, ‘EXPIRED(GRACE) & LOCKED’, ‘UNKNOWN’),
U.LTIME, U.EXPTIME, S1.NAME, S2.NAME, U.CTIME, DECODE(U.ID, 0, ‘SYS_GROUP’, ‘DEFAULT_CONSUMER_GROUP’)
FROM SYS.DV_ME M, SYS.SYS_USERS U, SYS.DV_TABLESPACES S1, SYS.DV_TABLESPACES S2
WHERE U.DATA_SPACE# = S1.ID AND U.TEMP_SPACE# = S2.ID AND U.ID = M.USER_ID
/

CREATE OR REPLACE VIEW SYS.ADM_CONSTRAINTS
(
OWNER,
CONSTRAINT_NAME,
CONSTRAINT_TYPE,
TABLE_NAME,
SEARCH_CONDITION,
R_OWNER,
R_TABLE_NAME,
R_CONSTRAINT_NAME,
DELETE_RULE,
STATUS,
DEFERRABLE,
DEFERRED,
VALIDATED,
BAD,
RELY,
INDEX_OWNER,
INDEX_NAME,
INVALID,
VIEW_RELATED,
CONS_COLS,
REF_COLS,
SYS_GENERATE,
IS_DUPLICATE
)
AS
SELECT U1.NAME, C.CONS_NAME, ‘R’, T.NAME, C.COND_TEXT, U2.NAME, T3.NAME, I.NAME,
DECODE(C.REFACT, 1, ‘DELETE CASCADE’, 2, ‘SET NULL’, ‘NOT ALLOWED’) DELETE_RULE, DECODE(C.FLAGS & 2, 2, ‘ENABLED’, ‘DISABLED’), ‘NOT DEFERRABLE’, ‘IMMEDIATE’, DECODE(C.FLAGS & 4, 4, ‘VALIDATED’, ’
NOT VALIDATED’), ‘’, ‘’, ‘’, ‘’, ‘FALSE’, ‘’,
DBMS_DIAGNOSE.DBA_LISTCOLS(U1.NAME, T.NAME, C.COL_LIST) CONS_COLS, DBMS_DIAGNOSE.DBA_LISTCOLS(U3.NAME, T3.NAME, I.COL_LIST) REF_COLS, DECODE(C.FLAGS & 8, 8, ‘Y’, ‘N’) SYS_GENERATE, DECODE(C.FLAGS
& 1024, 0, ‘N’, ‘Y’) IS_DUPLICATE
FROM SYS.SYS_USERS U1, SYS.SYS_USERS U2, SYS.SYS_USERS U3, SYS.SYS_CONSTRAINT_DEFS C, SYS.SYS_TABLES T, SYS.SYS_INDEXES I, SYS.SYS_TABLES T3
WHERE C.CONS_TYPE = 2 AND U1.ID = C.USER# AND (C.USER# = T.USER# AND T.ID = C.TABLE#) AND C.REF_USER# = I.USER# AND C.REF_TABLE# = I.TABLE# AND C.REF_CONS = I.ID AND C.REF_USER# = U2.ID AND T.RECYCL
ED = 0
AND I.USER# = U3.ID AND I.TABLE# = T3.ID AND I.USER# = T3.USER#
UNION ALL
SELECT U1.NAME, C.CONS_NAME, DECODE(C.CONS_TYPE, 0, ‘P’, 1, ‘U’, ‘’), T.NAME, C.COND_TEXT, ‘’, ‘’, ‘’,
‘’, DECODE(C.FLAGS & 2, 2, ‘ENABLED’, ‘DISABLED’), ‘NOT DEFERRABLE’, ‘IMMEDIATE’, DECODE(C.FLAGS & 4, 4, ‘VALIDATED’, ‘NOT VALIDATED’), ‘’, ‘’, U1.NAME, I.NAME, ‘FALSE’, ‘’,
DBMS_DIAGNOSE.DBA_LISTCOLS(U1.NAME, T.NAME, C.COL_LIST) CONS_COLS, ‘’ REF_COLS, DECODE(C.FLAGS & 8, 8, ‘Y’, ‘N’) SYS_GENERATE, DECODE(C.FLAGS & 1024, 0, ‘N’, ‘Y’) IS_DUPLICATE
FROM SYS.SYS_USERS U1, SYS.SYS_CONSTRAINT_DEFS C, SYS.SYS_TABLES T, SYS.SYS_INDEXES I
WHERE (C.CONS_TYPE = 0 OR C.CONS_TYPE = 1) AND U1.ID = C.USER# AND (C.USER# = T.USER# AND T.ID = C.TABLE#)
AND C.COL_LIST = I.COL_LIST AND C.USER# = I.USER# AND C.TABLE# = I.TABLE# AND T.RECYCLED = 0
UNION ALL
SELECT U.NAME, C.CONS_NAME, ‘C’, T.NAME, C.COND_TEXT, ‘’, ‘’, ‘’,
‘’, DECODE(C.FLAGS & 2, 2, ‘ENABLED’, ‘DISABLED’), ‘NOT DEFERRABLE’, ‘IMMEDIATE’, DECODE(C.FLAGS & 4, 4, ‘VALIDATED’, ‘NOT VALIDATED’), ‘’, ‘’, ‘’, ‘’, ‘FALSE’, ‘’, DBMS_DIAGNOSE.DBA_LISTCOLS(U.NA
ME, T.NAME, C.COL_LIST) CONS_COLS, ‘’ REF_COLS, DECODE(C.FLAGS & 8, 8, ‘Y’, ‘N’) SYS_GENERATE, DECODE(C.FLAGS & 1024, 0, ‘N’, ‘Y’) IS_DUPLICATE
FROM SYS.SYS_USERS U, SYS.SYS_CONSTRAINT_DEFS C, SYS.SYS_TABLES T
WHERE C.CONS_TYPE = 3 AND U.ID = C.USER# AND (C.USER# = T.USER# AND T.ID = C.TABLE# AND T.RECYCLED = 0)
/

CREATE OR REPLACE VIEW DB_CONSTRAINTS
(
OWNER,
CONSTRAINT_NAME,
CONSTRAINT_TYPE,
TABLE_NAME,
SEARCH_CONDITION,
R_OWNER,
R_TABLE_NAME,
R_CONSTRAINT_NAME,
DELETE_RULE,
STATUS,
DEFERRABLE,
DEFERRED,
VALIDATED,
BAD,
RELY,
INDEX_OWNER,
INDEX_NAME,
INVALID,
VIEW_RELATED,
CONS_COLS,
REF_COLS,
SYS_GENERATE,
IS_DUPLICATE
)
AS
SELECT U1.NAME, C.CONS_NAME, ‘R’, T.NAME, C.COND_TEXT, U2.NAME, T3.NAME, I.NAME,
DECODE(C.REFACT, 1, ‘DELETE CASCADE’, 2, ‘SET NULL’, ‘NOT ALLOWED’) DELETE_RULE, DECODE(C.FLAGS & 2, 2, ‘ENABLED’, ‘DISABLED’), ‘NOT DEFERRABLE’, ‘IMMEDIATE’, DECODE(C.FLAGS & 4, 4, ‘VALIDATED’, ’
NOT VALIDATED’), ‘’, ‘’, ‘’, ‘’, ‘FALSE’, ‘’,
DBMS_DIAGNOSE.DBA_LISTCOLS(U1.NAME, T.NAME, C.COL_LIST) CONS_COLS, DBMS_DIAGNOSE.DBA_LISTCOLS(U3.NAME, T3.NAME, I.COL_LIST) REF_COLS, DECODE(C.FLAGS & 8, 8, ‘Y’, ‘N’) SYS_GENERATE, DECODE(C.FLAGS
& 1024, 0, ‘N’, ‘Y’) IS_DUPLICATE
FROM SYS.SYS_USERS U1, SYS.SYS_USERS U2, SYS.SYS_CONSTRAINT_DEFS C, SYS.SYS_TABLES T, SYS.SYS_INDEXES I, SYS.SYS_USERS U3, SYS.SYS_TABLES T3
WHERE C.USER# = U1.ID AND T.USER# = U1.ID AND T.ID = C.TABLE# AND C.REF_USER# = I.USER# AND C.REF_USER# = U2.ID AND C.REF_TABLE# = I.TABLE# AND C.REF_CONS = I.ID AND T.RECYCLED = 0
AND I.USER# = U3.ID AND T3.USER# = U3.ID AND I.TABLE# = T3.ID AND C.CONS_TYPE = 2
AND (U1.NAME = user or U1.NAME = ‘SYS’ or user = ‘SYS’ or exists (select 1 from SYS.SYS_USER_ROLES S, SYS.DV_ME V where S.GRANTEE_ID = V.USER_ID and S.GRANTEE_TYPE = 0 and S.GRANTED_ROLE_ID = 0))
UNION ALL
SELECT U1.NAME, C.CONS_NAME, DECODE(C.CONS_TYPE, 0, ‘P’, 1, ‘U’, ‘’), T.NAME, C.COND_TEXT, ‘’, ‘’, ‘’,
‘’, DECODE(C.FLAGS & 2, 2, ‘ENABLED’, ‘DISABLED’), ‘NOT DEFERRABLE’, ‘IMMEDIATE’, DECODE(C.FLAGS & 4, 4, ‘VALIDATED’, ‘NOT VALIDATED’), ‘’, ‘’, U1.NAME, I.NAME, ‘FALSE’, ‘’,
DBMS_DIAGNOSE.DBA_LISTCOLS(U1.NAME, T.NAME, C.COL_LIST) CONS_COLS, ‘’ REF_COLS, DECODE(C.FLAGS & 8, 8, ‘Y’, ‘N’) SYS_GENERATE, DECODE(C.FLAGS & 1024, 0, ‘N’, ‘Y’) IS_DUPLICATE
FROM SYS.SYS_USERS U1, SYS.SYS_CONSTRAINT_DEFS C, SYS.SYS_TABLES T, SYS.SYS_INDEXES I
WHERE C.USER# = U1.ID AND T.USER# = U1.ID AND T.ID = C.TABLE#
AND C.COL_LIST = I.COL_LIST AND C.USER# = I.USER# AND C.TABLE# = I.TABLE# AND T.RECYCLED = 0 AND (C.CONS_TYPE = 0 OR C.CONS_TYPE = 1)
AND (U1.NAME = user or U1.NAME = ‘SYS’ or user = ‘SYS’ or exists (select 1 from SYS.SYS_USER_ROLES S, SYS.DV_ME V where S.GRANTEE_ID = V.USER_ID and S.GRANTEE_TYPE = 0 and S.GRANTED_ROLE_ID = 0))
UNION ALL
SELECT U.NAME, C.CONS_NAME, ‘C’, T.NAME, C.COND_TEXT, ‘’, ‘’, ‘’,
‘’, DECODE(C.FLAGS & 2, 2, ‘ENABLED’, ‘DISABLED’), ‘NOT DEFERRABLE’, ‘IMMEDIATE’, DECODE(C.FLAGS & 4, 4, ‘VALIDATED’, ‘NOT VALIDATED’), ‘’, ‘’, ‘’, ‘’, ‘FALSE’, ‘’, DBMS_DIAGNOSE.DBA_LISTCOLS(U.NA
ME, T.NAME, C.COL_LIST) CONS_COLS, ‘’ REF_COLS, DECODE(C.FLAGS & 8, 8, ‘Y’, ‘N’) SYS_GENERATE, DECODE(C.FLAGS & 1024, 0, ‘N’, ‘Y’) IS_DUPLICATE
FROM SYS.SYS_USERS U, SYS.SYS_CONSTRAINT_DEFS C, SYS.SYS_TABLES T
WHERE C.USER# = U.ID AND T.USER# = U.ID AND T.ID = C.TABLE# AND T.RECYCLED = 0 AND C.CONS_TYPE = 3
AND (U.NAME = user or U.NAME = ‘SYS’ or user = ‘SYS’ or exists (select 1 from SYS.SYS_USER_ROLES S, SYS.DV_ME V where S.GRANTEE_ID = V.USER_ID and S.GRANTEE_TYPE = 0 and S.GRANTED_ROLE_ID = 0))
/

CREATE OR REPLACE VIEW MY_CONSTRAINTS
(
OWNER,
CONSTRAINT_NAME,
CONSTRAINT_TYPE,
TABLE_NAME,
SEARCH_CONDITION,
R_OWNER,
R_TABLE_NAME,
R_CONSTRAINT_NAME,
DELETE_RULE,
STATUS,
DEFERRABLE,
DEFERRED,
VALIDATED,
BAD,
RELY,
INDEX_OWNER,
INDEX_NAME,
INVALID,
VIEW_RELATED,
CONS_COLS,
REF_COLS,
SYS_GENERATE,
IS_DUPLICATE
)
AS
SELECT M.USER_NAME, C.CONS_NAME, ‘R’, T.NAME, C.COND_TEXT, U.NAME, T2.NAME, I.NAME,
DECODE(C.REFACT, 1, ‘DELETE CASCADE’, 2, ‘SET NULL’, ‘NOT ALLOWED’) DELETE_RULE, DECODE(C.FLAGS & 2, 2, ‘ENABLED’, ‘DISABLED’), ‘NOT DEFERRABLE’, ‘IMMEDIATE’, DECODE(C.FLAGS & 4, 4, ‘VALIDATED’, ’
NOT VALIDATED’), ‘’, ‘’, ‘’, ‘’, ‘FALSE’, ‘’,
DBMS_DIAGNOSE.DBA_LISTCOLS(M.USER_NAME, T.NAME, C.COL_LIST) CONS_COLS, DBMS_DIAGNOSE.DBA_LISTCOLS(U2.NAME, T2.NAME, I.COL_LIST) REF_COLS, DECODE(C.FLAGS & 8, 8, ‘Y’, ‘N’) SYS_GENERATE, DECODE(C.FL
AGS & 1024, 0, ‘N’, ‘Y’) IS_DUPLICATE
FROM SYS.DV_ME M, SYS.SYS_USERS U, SYS.SYS_CONSTRAINT_DEFS C, SYS.SYS_TABLES T, SYS.SYS_INDEXES I, SYS.SYS_USERS U2, SYS.SYS_TABLES T2
WHERE C.USER# = M.USER_ID AND T.USER# = M.USER_ID AND T.ID = C.TABLE# AND T.RECYCLED = 0 AND C.REF_USER# = I.USER# AND C.REF_TABLE# = I.TABLE# AND C.REF_CONS = I.ID AND C.REF_USER# = U.ID
AND I.USER# = U2.ID AND I.TABLE# = T2.ID AND I.USER# = T2.USER# AND C.CONS_TYPE = 2
UNION ALL
SELECT M.USER_NAME, C.CONS_NAME, DECODE(C.CONS_TYPE, 0, ‘P’, 1, ‘U’, ‘’), T.NAME, C.COND_TEXT, ‘’, ‘’, ‘’,
‘’, DECODE(C.FLAGS & 2, 2, ‘ENABLED’, ‘DISABLED’), ‘NOT DEFERRABLE’, ‘IMMEDIATE’, DECODE(C.FLAGS & 4, 4, ‘VALIDATED’, ‘NOT VALIDATED’), ‘’, ‘’, M.USER_NAME, I.NAME, ‘FALSE’, ‘’,
DBMS_DIAGNOSE.DBA_LISTCOLS(M.USER_NAME, T.NAME, C.COL_LIST) CONS_COLS, ‘’ REF_COLS, DECODE(C.FLAGS & 8, 8, ‘Y’, ‘N’) SYS_GENERATE, DECODE(C.FLAGS & 1024, 0, ‘N’, ‘Y’) IS_DUPLICATE
FROM SYS.DV_ME M, SYS.SYS_CONSTRAINT_DEFS C, SYS.SYS_TABLES T, SYS.SYS_INDEXES I
WHERE C.USER# = M.USER_ID AND T.USER# = M.USER_ID AND T.ID = C.TABLE#
AND C.COL_LIST = I.COL_LIST AND C.USER# = I.USER# AND C.TABLE# = I.TABLE# AND T.RECYCLED = 0 AND (C.CONS_TYPE = 0 OR C.CONS_TYPE = 1)
UNION ALL
SELECT M.USER_NAME, C.CONS_NAME, ‘C’, T.NAME, C.COND_TEXT, ‘’, ‘’, ‘’,
‘’, DECODE(C.FLAGS & 2, 2, ‘ENABLED’, ‘DISABLED’), ‘NOT DEFERRABLE’, ‘IMMEDIATE’, DECODE(C.FLAGS & 4, 4, ‘VALIDATED’, ‘NOT VALIDATED’), ‘’, ‘’, ‘’, ‘’, ‘FALSE’, ‘’, DBMS_DIAGNOSE.DBA_LISTCOLS(M.US
ER_NAME, T.NAME, C.COL_LIST) CONS_COLS,’’ REF_COLS, DECODE(C.FLAGS & 8, 8, ‘Y’, ‘N’) SYS_GENERATE, DECODE(C.FLAGS & 1024, 0, ‘N’, ‘Y’) IS_DUPLICATE
FROM SYS.DV_ME M, SYS.SYS_CONSTRAINT_DEFS C, SYS.SYS_TABLES T
WHERE C.USER# = M.USER_ID AND T.USER# = M.USER_ID AND T.ID = C.TABLE# AND T.RECYCLED = 0 AND C.CONS_TYPE = 3
/

CREATE OR REPLACE VIEW MY_CONS_COLUMNS
(
OWNER,
CONSTRAINT_NAME,
TABLE_NAME,
COLUMN_NAME,
POSITION
)
AS
SELECT M.USER_NAME, CON.CONS_NAME, T.NAME, C.NAME, C.ID
FROM SYS.DV_ME M, SYS.SYS_TABLES T, SYS.SYS_COLUMNS C, SYS.SYS_CONSTRAINT_DEFS CON
WHERE CON.USER# = M.USER_ID AND CON.USER# = C.USER# AND CON.USER# = T.USER# AND CON.TABLE# = C.TABLE# AND CON.TABLE# = T.ID AND CON.TABLE# = C.TABLE# AND T.RECYCLED = 0 AND LOCATE(CONCAT(’,’,C.ID,’
,’), CONCAT(’,’,CON.COL_LIST,’,’)) > 0
/

CREATE OR REPLACE VIEW DB_TRIGGERS
(
OWNER,
TRIGGER_NAME,
TABLE_OWNER,
TABLE_NAME,
SOURCE,
STATUS
)
AS
SELECT U.NAME, P.NAME, P.TRIG_TABLE_USER, P.TRIG_TABLE, P.SOURCE, P.TRIG_STATUS FROM SYS.SYS_USERS U, SYS.SYS_PROCS P
WHERE P.TYPE = ‘T’ AND P.USER# = U.ID
and (U.NAME = user or U.NAME = ‘SYS’ or user = ‘SYS’ or exists (select 1 from SYS.SYS_USER_ROLES S, SYS.DV_ME V where S.GRANTEE_ID = V.USER_ID and S.GRANTEE_TYPE = 0 and S.GRANTED_ROLE_ID = 0)
)
/

CREATE OR REPLACE VIEW ADM_TRIGGERS
(
OWNER,
TRIGGER_NAME,
TABLE_OWNER,
TABLE_NAME,
SOURCE,
STATUS
)
AS
SELECT U.NAME, P.NAME, P.TRIG_TABLE_USER, P.TRIG_TABLE, P.SOURCE, P.TRIG_STATUS FROM SYS.SYS_USERS U, SYS.SYS_PROCS P WHERE P.TYPE = ‘T’ AND P.USER# = U.ID
/

CREATE OR REPLACE VIEW MY_TRIGGERS
(
TRIGGER_NAME,
TABLE_OWNER,
TABLE_NAME,
SOURCE,
STATUS
)
AS
SELECT P.NAME, P.TRIG_TABLE_USER, P.TRIG_TABLE, P.SOURCE, P.TRIG_STATUS FROM SYS.DV_ME M, SYS.SYS_PROCS P WHERE P.TYPE = ‘T’ AND P.USER# = M.USER_ID
/

CREATE OR REPLACE VIEW ADM_ROLES
(
ROLE,
PASSWORD_REQUIRED,
AUTHENTICATION_TYPE
)
AS
SELECT R.NAME, ‘NO’, ‘PASSWORD’ FROM SYS.SYS_ROLES R
/

CREATE OR REPLACE VIEW ADM_ARGUMENTS
(
OWNER,
OBJECT_NAME,
ARGUMENT_NAME,
POSITION,
SEQUENCE,
DATA_LEVEL,
DATA_TYPE,
DEFAULTED,
IN_OUT,
DATA_LENGTH,
DATA_PRECISION,
DATA_SCALE
)
AS
SELECT U.NAME, A.OBJECT_NAME, A.ARGUMENT_NAME, A.POSITION,A.SEQUENCE,A.DATA_LEVEL,
TYPE_ID2NAME(A.DATA_TYPE),
DECODE(A.DEFAULTED, 0,‘N’, 1,‘Y’),
DECODE(A.IN_OUT, 2,‘OUT’,3,‘IN OUT’,‘IN’),
A.DATA_LENGTH,
A.DATA_PRECISION,
A.DATA_SCALE FROM SYS.SYS_USERS U, SYS.SYS_PROC_ARGS A WHERE U.ID = A.USER#
/

CREATE OR REPLACE VIEW DB_ARGUMENTS
(
OWNER,
OBJECT_NAME,
ARGUMENT_NAME,
POSITION,
SEQUENCE,
DATA_LEVEL,
DATA_TYPE,
DEFAULTED,
IN_OUT,
DATA_LENGTH,
DATA_PRECISION,
DATA_SCALE
)
AS
SELECT U.NAME, A.OBJECT_NAME, A.ARGUMENT_NAME, A.POSITION,A.SEQUENCE,A.DATA_LEVEL,
TYPE_ID2NAME(A.DATA_TYPE),
DECODE(A.DEFAULTED, 0,‘N’, 1,‘Y’),
DECODE(A.IN_OUT, 2,‘OUT’,3,‘IN OUT’,‘IN’),
A.DATA_LENGTH,
A.DATA_PRECISION,
A.DATA_SCALE FROM SYS.SYS_USERS U, SYS.SYS_PROC_ARGS A WHERE U.ID = A.USER#
and (U.NAME = user or U.NAME = ‘SYS’ or user = ‘SYS’ or exists (select 1 from SYS.DV_ME V, SYS.SYS_USER_ROLES R where R.GRANTEE_ID = V.USER_ID and R.GRANTEE_TYPE = 0 and R.GRANTED_ROLE_ID
= 0))
/

CREATE OR REPLACE VIEW MY_ARGUMENTS
(
OBJECT_NAME,
ARGUMENT_NAME,
POSITION,
SEQUENCE,
DATA_LEVEL,
DATA_TYPE,
DEFAULTED,
IN_OUT,
DATA_LENGTH,
DATA_PRECISION,
DATA_SCALE
)
AS
SELECT A.OBJECT_NAME, A.ARGUMENT_NAME, A.POSITION,A.SEQUENCE,A.DATA_LEVEL,
TYPE_ID2NAME(A.DATA_TYPE),
DECODE(A.DEFAULTED, 0,‘N’, 1,‘Y’),
DECODE(A.IN_OUT, 2,‘OUT’,3,‘IN OUT’,‘IN’),
A.DATA_LENGTH,
A.DATA_PRECISION,
A.DATA_SCALE FROM SYS.DV_ME M, SYS.SYS_PROC_ARGS A WHERE M.USER_ID = A.USER#
/

CREATE OR REPLACE VIEW DB_TAB_STATISTICS
(
OWNER,
TABLE_NAME,
PARTITION_NAME,
PARTITION_POSITION,
OBJECT_TYPE,
NUM_ROWS,
BLOCKS,
EMPTY_BLOCKS,
AVG_SPACE,
CHAIN_CNT,
AVG_ROW_LEN,
AVG_SPACE_FREELIST_BLOCKS,
NUM_FREELIST_BLOCKS,
AVG_CACHED_BLOCKS,
AVG_CACHE_HIT_RATIO,
SAMPLE_SIZE,
LAST_ANALYZED
)
AS
SELECT U.NAME, T.NAME, NULL::VARCHAR(64), NULL::NUMBER,
‘TABLE’, T.NUM_ROWS, T.BLOCKS, T.EMPTY_BLOCKS,
NULL::NUMBER, NULL::NUMBER, T.AVG_ROW_LEN,
NULL::NUMBER, NULL::NUMBER, NULL::NUMBER, NULL::NUMBER, T.SAMPLESIZE, T.ANALYZETIME
FROM SYS.SYS_USERS U JOIN SYS.SYS_TABLES T ON T.USER# = U.ID
where (U.NAME = user or U.NAME = ‘SYS’ or user = ‘SYS’ or exists (select 1 from SYS.SYS_USER_ROLES S, SYS.DV_ME V where S.GRANTEE_ID = V.USER_ID and S.GRANTEE_TYPE = 0 and S.GRANTED_ROLE_ID = 0))
UNION ALL
SELECT U.NAME, T.NAME, TP.NAME, ROW_NUMBER() OVER (PARTITION BY TP.USER#, TP.TABLE# ORDER BY TP.PART#),
‘PARTITION’, TP.ROWCNT, TP.BLKCNT, TP.EMPCNT,
NULL::NUMBER, NULL::NUMBER, TP.AVGRLN,
NULL::NUMBER, NULL::NUMBER, NULL::NUMBER, NULL::NUMBER, TP.SAMPLESIZE, TP.ANALYZETIME
FROM SYS.SYS_USERS U JOIN SYS.SYS_TABLES T ON T.USER# = U.ID
JOIN SYS.SYS_TABLE_PARTS TP ON T.USER# = TP.USER# AND T.ID = TP.TABLE#
where (U.NAME = user or U.NAME = ‘SYS’ or user = ‘SYS’ or exists (select 1 from SYS.SYS_USER_ROLES S, SYS.DV_ME V where S.GRANTEE_ID = V.USER_ID and S.GRANTEE_TYPE = 0 and S.GRANTED_ROLE_ID = 0))
/

CREATE OR REPLACE VIEW ADM_TAB_STATISTICS
(
OWNER,
TABLE_NAME,
PARTITION_NAME,
PARTITION_POSITION,
OBJECT_TYPE,
NUM_ROWS,
BLOCKS,
EMPTY_BLOCKS,
AVG_SPACE,
CHAIN_CNT,
AVG_ROW_LEN,
AVG_SPACE_FREELIST_BLOCKS,
NUM_FREELIST_BLOCKS,
AVG_CACHED_BLOCKS,
AVG_CACHE_HIT_RATIO,
SAMPLE_SIZE,
LAST_ANALYZED
)
AS
SELECT * FROM SYS.DB_TAB_STATISTICS
/

CREATE OR REPLACE VIEW MY_TAB_STATISTICS
(
TABLE_NAME,
PARTITION_NAME,
PARTITION_POSITION,
OBJECT_TYPE,
NUM_ROWS,
BLOCKS,
EMPTY_BLOCKS,
AVG_SPACE,
CHAIN_CNT,
AVG_ROW_LEN,
AVG_SPACE_FREELIST_BLOCKS,
NUM_FREELIST_BLOCKS,
AVG_CACHED_BLOCKS,
AVG_CACHE_HIT_RATIO,
SAMPLE_SIZE,
LAST_ANALYZED
)
AS
SELECT T.NAME, NULL::VARCHAR(64), NULL::NUMBER,
‘TABLE’, T.NUM_ROWS, T.BLOCKS, T.EMPTY_BLOCKS,
NULL::NUMBER, NULL::NUMBER, T.AVG_ROW_LEN,
NULL::NUMBER, NULL::NUMBER, NULL::NUMBER, NULL::NUMBER, T.SAMPLESIZE, T.ANALYZETIME
FROM SYS.DV_ME M JOIN SYS.SYS_TABLES T ON T.USER# = M.USER_ID
UNION ALL
SELECT T.NAME, TP.NAME, ROW_NUMBER() OVER (PARTITION BY TP.USER#, TP.TABLE# ORDER BY TP.PART#),
‘PARTITION’, TP.ROWCNT, TP.BLKCNT, TP.EMPCNT,
NULL::NUMBER, NULL::NUMBER, TP.AVGRLN,
NULL::NUMBER, NULL::NUMBER, NULL::NUMBER, NULL::NUMBER, TP.SAMPLESIZE, TP.ANALYZETIME
FROM SYS.DV_ME M JOIN SYS.SYS_TABLES T ON T.USER# = M.USER_ID
JOIN SYS.SYS_TABLE_PARTS TP ON T.USER# = TP.USER# AND T.ID = TP.TABLE#
/

CREATE OR REPLACE PUBLIC SYNONYM DB_TAB_STATISTICS FOR SYS.DB_TAB_STATISTICS
/
GRANT SELECT ON DB_TAB_STATISTICS TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM ADM_TAB_STATISTICS FOR SYS.ADM_TAB_STATISTICS
/
CREATE OR REPLACE PUBLIC SYNONYM MY_TAB_STATISTICS FOR SYS.MY_TAB_STATISTICS
/
GRANT SELECT ON MY_TAB_STATISTICS TO PUBLIC
/

CREATE OR REPLACE VIEW DB_IND_PARTITIONS
(
INDEX_OWNER,
INDEX_NAME,
COMPOSITE,
PARTITION_NAME,
PARTITION_POSITION,
STATUS,
PCT_FREE,
INI_TRANS,
MAX_TRANS,
BLEVEL,
LEAF_BLOCKS,
DISTINCT_KEYS,
AVG_LEAF_BLOCKS_PER_KEY,
AVG_DATA_BLOCKS_PER_KEY,
CLUSTERING_FACTOR,
NUM_ROWS,
SAMPLE_SIZE,
LAST_ANALYZED
)
AS
SELECT U.NAME, I.NAME, ‘NO’, TP.NAME,
ROW_NUMBER() OVER (PARTITION BY IP.USER#, IP.TABLE#, IP.INDEX# ORDER BY TP.PART#),
‘USABLE’, TP.PCTFREE, TP.INITRANS, 255,
IP.BLEVEL, IP.LEVEL_BLOCKS, IP.DISTKEY, IP.LBLKKEY, IP.DBLKKEY, NULL::NUMBER,
TP.ROWCNT, TP.SAMPLESIZE, TP.ANALYZETIME
FROM SYS.SYS_USERS U JOIN SYS.SYS_TABLE_PARTS TP ON TP.USER# = U.ID
JOIN SYS.SYS_INDEX_PARTS IP ON TP.USER# = IP.USER# AND TP.TABLE# = IP.TABLE# AND TP.PART#=IP.PART#
JOIN SYS.SYS_INDEXES I ON IP.USER# = I.USER# AND IP.TABLE# = I.TABLE# AND IP.INDEX# = I.ID
and (U.NAME = user or U.NAME = ‘SYS’ or user = ‘SYS’ or exists (select 1 from SYS.SYS_USER_ROLES S, SYS.DV_ME V where S.GRANTEE_ID = V.USER_ID and S.GRANTEE_TYPE = 0 and S.GRANTED_ROLE_ID = 0))
/

CREATE OR REPLACE VIEW ADM_IND_PARTITIONS
(
INDEX_OWNER,
INDEX_NAME,
COMPOSITE,
PARTITION_NAME,
PARTITION_POSITION,
STATUS,
PCT_FREE,
INI_TRANS,
MAX_TRANS,
BLEVEL,
LEAF_BLOCKS,
DISTINCT_KEYS,
AVG_LEAF_BLOCKS_PER_KEY,
AVG_DATA_BLOCKS_PER_KEY,
CLUSTERING_FACTOR,
NUM_ROWS,
SAMPLE_SIZE,
LAST_ANALYZED
)
AS
SELECT U.NAME, I.NAME, ‘NO’, TP.NAME,
ROW_NUMBER() OVER (PARTITION BY IP.USER#, IP.TABLE#, IP.INDEX# ORDER BY TP.PART#),
‘USABLE’, TP.PCTFREE, TP.INITRANS, 255,
IP.BLEVEL, IP.LEVEL_BLOCKS, IP.DISTKEY, IP.LBLKKEY, IP.DBLKKEY, NULL::NUMBER,
TP.ROWCNT, TP.SAMPLESIZE, TP.ANALYZETIME
FROM SYS.SYS_USERS U JOIN SYS.SYS_TABLE_PARTS TP ON TP.USER# = U.ID
JOIN SYS.SYS_INDEX_PARTS IP ON TP.USER# = IP.USER# AND TP.TABLE# = IP.TABLE# AND TP.PART#=IP.PART#
JOIN SYS.SYS_INDEXES I ON IP.USER# = I.USER# AND IP.TABLE# = I.TABLE# AND IP.INDEX# = I.ID
/

CREATE OR REPLACE VIEW MY_IND_PARTITIONS
(
INDEX_NAME,
COMPOSITE,
PARTITION_NAME,
PARTITION_POSITION,
STATUS,
PCT_FREE,
INI_TRANS,
MAX_TRANS,
BLEVEL,
LEAF_BLOCKS,
DISTINCT_KEYS,
AVG_LEAF_BLOCKS_PER_KEY,
AVG_DATA_BLOCKS_PER_KEY,
CLUSTERING_FACTOR,
NUM_ROWS,
SAMPLE_SIZE,
LAST_ANALYZED
)
AS
SELECT I.NAME, ‘NO’, TP.NAME,
ROW_NUMBER() OVER (PARTITION BY IP.USER#, IP.TABLE#, IP.INDEX# ORDER BY TP.PART#),
‘USABLE’, TP.PCTFREE, TP.INITRANS, 255,
IP.BLEVEL, IP.LEVEL_BLOCKS, IP.DISTKEY, IP.LBLKKEY, IP.DBLKKEY, NULL::NUMBER,
TP.ROWCNT, TP.SAMPLESIZE, TP.ANALYZETIME
FROM SYS.DV_ME M JOIN SYS.SYS_TABLE_PARTS TP ON TP.USER# = M.USER_ID
JOIN SYS.SYS_INDEX_PARTS IP ON TP.USER# = IP.USER# AND TP.TABLE# = IP.TABLE# AND TP.PART#=IP.PART#
JOIN SYS.SYS_INDEXES I ON IP.USER# = I.USER# AND IP.TABLE# = I.TABLE# AND IP.INDEX# = I.ID
/

CREATE OR REPLACE PUBLIC SYNONYM DB_IND_PARTITIONS FOR SYS.DB_IND_PARTITIONS
/
GRANT SELECT ON DB_IND_PARTITIONS TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM ADM_IND_PARTITIONS FOR SYS.ADM_IND_PARTITIONS
/
CREATE OR REPLACE PUBLIC SYNONYM MY_IND_PARTITIONS FOR SYS.MY_IND_PARTITIONS
/
GRANT SELECT ON MY_IND_PARTITIONS TO PUBLIC
/

CREATE OR REPLACE VIEW DB_IND_STATISTICS
(
OWNER,
INDEX_NAME,
TABLE_OWNER,
TABLE_NAME,
PARTITION_NAME,
PARTITION_POSITION,
OBJECT_TYPE,
BLEVEL,
LEAF_BLOCKS,
DISTINCT_KEYS,
AVG_LEAF_BLOCKS_PER_KEY,
AVG_DATA_BLOCKS_PER_KEY,
CLUSTERING_FACTOR,
NUM_ROWS,
SAMPLE_SIZE,
LAST_ANALYZED
)
AS
SELECT U.NAME, I.NAME, U.NAME, T.NAME, NULL::VARCHAR(64), NULL::NUMBER,
‘INDEX’, I.BLEVEL, I.LEVEL_BLOCKS, I.DISTINCT_KEYS, I.AVG_LEAF_BLOCKS_PER_KEY, I.AVG_DATA_BLOCKS_PER_KEY,
NULL::NUMBER, T.NUM_ROWS, T.SAMPLESIZE, T.ANALYZETIME
FROM SYS.SYS_USERS U JOIN SYS.SYS_TABLES T ON T.USER# = U.ID
JOIN SYS.SYS_INDEXES I ON T.USER# = I.USER# AND T.ID = I.TABLE#
UNION ALL
SELECT U.NAME, I.NAME, U.NAME, T.NAME, TP.NAME,
ROW_NUMBER() OVER (PARTITION BY TP.USER#, TP.TABLE# ORDER BY TP.PART#),
‘PARTITION’, IP.BLEVEL, IP.LEVEL_BLOCKS, IP.DISTKEY, IP.LBLKKEY, IP.DBLKKEY,
NULL::NUMBER, TP.ROWCNT, TP.SAMPLESIZE, TP.ANALYZETIME
FROM SYS.SYS_USERS U JOIN SYS.SYS_TABLES T ON T.USER# = U.ID
JOIN SYS.SYS_INDEXES I ON T.USER# = I.USER# AND T.ID = I.TABLE#
JOIN SYS.SYS_TABLE_PARTS TP ON T.USER# = TP.USER# AND T.ID = TP.TABLE#
JOIN SYS.SYS_INDEX_PARTS IP ON I.USER# = IP.USER# AND I.TABLE# = IP.TABLE# AND I.ID = IP.INDEX# AND TP.PART# = IP.PART#
/

CREATE OR REPLACE VIEW ADM_IND_STATISTICS
(
OWNER,
INDEX_NAME,
TABLE_OWNER,
TABLE_NAME,
PARTITION_NAME,
PARTITION_POSITION,
OBJECT_TYPE,
BLEVEL,
LEAF_BLOCKS,
DISTINCT_KEYS,
AVG_LEAF_BLOCKS_PER_KEY,
AVG_DATA_BLOCKS_PER_KEY,
CLUSTERING_FACTOR,
NUM_ROWS,
SAMPLE_SIZE,
LAST_ANALYZED
)
AS
SELECT U.NAME, I.NAME, U.NAME, T.NAME, NULL::VARCHAR(64), NULL::NUMBER,
‘INDEX’, I.BLEVEL, I.LEVEL_BLOCKS, I.DISTINCT_KEYS, I.AVG_LEAF_BLOCKS_PER_KEY, I.AVG_DATA_BLOCKS_PER_KEY,
NULL::NUMBER, T.NUM_ROWS, T.SAMPLESIZE, T.ANALYZETIME
FROM SYS.SYS_USERS U JOIN SYS.SYS_TABLES T ON T.USER# = U.ID
JOIN SYS.SYS_INDEXES I ON T.USER# = I.USER# AND T.ID = I.TABLE#
UNION ALL
SELECT U.NAME, I.NAME, U.NAME, T.NAME, TP.NAME,
ROW_NUMBER() OVER (PARTITION BY TP.USER#, TP.TABLE# ORDER BY TP.PART#),
‘PARTITION’, IP.BLEVEL, IP.LEVEL_BLOCKS, IP.DISTKEY, IP.LBLKKEY, IP.DBLKKEY,
NULL::NUMBER, TP.ROWCNT, TP.SAMPLESIZE, TP.ANALYZETIME
FROM SYS.SYS_USERS U JOIN SYS.SYS_TABLES T ON T.USER# = U.ID
JOIN SYS.SYS_INDEXES I ON T.USER# = I.USER# AND T.ID = I.TABLE#
JOIN SYS.SYS_TABLE_PARTS TP ON T.USER# = TP.USER# AND T.ID = TP.TABLE#
JOIN SYS.SYS_INDEX_PARTS IP ON I.USER# = IP.USER# AND I.TABLE# = IP.TABLE# AND I.ID = IP.INDEX# AND TP.PART# = IP.PART#
/

CREATE OR REPLACE VIEW MY_IND_STATISTICS
(
INDEX_NAME,
TABLE_OWNER,
TABLE_NAME,
PARTITION_NAME,
PARTITION_POSITION,
OBJECT_TYPE,
BLEVEL,
LEAF_BLOCKS,
DISTINCT_KEYS,
AVG_LEAF_BLOCKS_PER_KEY,
AVG_DATA_BLOCKS_PER_KEY,
CLUSTERING_FACTOR,
NUM_ROWS,
SAMPLE_SIZE,
LAST_ANALYZED
)
AS
SELECT I.NAME, M.USER_NAME, T.NAME, NULL::VARCHAR(64), NULL::NUMBER,
‘INDEX’, I.BLEVEL, I.LEVEL_BLOCKS, I.DISTINCT_KEYS, I.AVG_LEAF_BLOCKS_PER_KEY, I.AVG_DATA_BLOCKS_PER_KEY,
NULL::NUMBER, T.NUM_ROWS, T.SAMPLESIZE, T.ANALYZETIME
FROM SYS.DV_ME M JOIN SYS.SYS_TABLES T ON T.USER# = M.USER_ID
JOIN SYS.SYS_INDEXES I ON T.USER# = I.USER# AND T.ID = I.TABLE#
UNION ALL
SELECT I.NAME, M.USER_NAME, T.NAME, TP.NAME,
ROW_NUMBER() OVER (PARTITION BY TP.USER#, TP.TABLE# ORDER BY TP.PART#),
‘PARTITION’, IP.BLEVEL, IP.LEVEL_BLOCKS, IP.DISTKEY, IP.LBLKKEY, IP.DBLKKEY,
NULL::NUMBER, TP.ROWCNT, TP.SAMPLESIZE, TP.ANALYZETIME
FROM SYS.DV_ME M JOIN SYS.SYS_TABLES T ON T.USER# = M.USER_ID
JOIN SYS.SYS_INDEXES I ON T.USER# = I.USER# AND T.ID = I.TABLE#
JOIN SYS.SYS_TABLE_PARTS TP ON T.USER# = TP.USER# AND T.ID = TP.TABLE#
JOIN SYS.SYS_INDEX_PARTS IP ON I.USER# = IP.USER# AND I.TABLE# = IP.TABLE# AND I.ID = IP.INDEX# AND TP.PART# = IP.PART#
/

CREATE OR REPLACE PUBLIC SYNONYM DB_IND_STATISTICS FOR SYS.DB_IND_STATISTICS
/
CREATE OR REPLACE PUBLIC SYNONYM ADM_IND_STATISTICS FOR SYS.ADM_IND_STATISTICS
/
CREATE OR REPLACE PUBLIC SYNONYM MY_IND_STATISTICS FOR SYS.MY_IND_STATISTICS
/
GRANT SELECT ON MY_IND_STATISTICS TO PUBLIC
/

CREATE OR REPLACE VIEW DB_TAB_COL_STATISTICS
(
OWNER,
TABLE_NAME,
COLUMN_NAME,
NUM_DISTINCT,
LOW_VALUE,
HIGH_VALUE,
DENSITY,
NUM_NULLS,
NUM_BUCKETS,
SAMPLE_SIZE,
LAST_ANALYZED,
AVG_COL_LEN,
HISTOGRAM
)
AS
SELECT U.NAME, T.NAME, C.NAME, C.NUM_DISTINCT, C.LOW_VALUE, C.HIGH_VALUE,
HH.DENSITY, HH.NULL_NUM, HH.BUCKET_NUM, T.SAMPLESIZE, T.ANALYZETIME, NULL::NUMBER, C.HISTOGRAM
FROM SYS.SYS_USERS U JOIN SYS.SYS_TABLES T ON T.USER# = U.ID
JOIN SYS.SYS_COLUMNS C ON T.ID = C.TABLE# AND T.USER# = C.USER#
LEFT JOIN SYS.SYS_HISTGRAM_ABSTR HH ON C.USER# = HH.USER# AND C.TABLE# = HH.TAB# AND C.ID = HH.COL# AND (HH.SPARE1 IS NULL OR HH.SPARE1 = 4294967295)
where (U.NAME = user or U.NAME = ‘SYS’ or user = ‘SYS’ or exists (select 1 from SYS.SYS_USER_ROLES S, SYS.DV_ME V where S.GRANTEE_ID = V.USER_ID and S.GRANTEE_TYPE = 0 and S.GRANTED_ROLE_ID = 0))
/

CREATE OR REPLACE VIEW ADM_TAB_COL_STATISTICS
(
OWNER,
TABLE_NAME,
COLUMN_NAME,
NUM_DISTINCT,
LOW_VALUE,
HIGH_VALUE,
DENSITY,
NUM_NULLS,
NUM_BUCKETS,
SAMPLE_SIZE,
LAST_ANALYZED,
AVG_COL_LEN,
HISTOGRAM
)
AS
SELECT U.NAME, T.NAME, C.NAME, C.NUM_DISTINCT, C.LOW_VALUE, C.HIGH_VALUE,
HH.DENSITY, HH.NULL_NUM, HH.BUCKET_NUM, T.SAMPLESIZE, T.ANALYZETIME, NULL::NUMBER, C.HISTOGRAM
FROM SYS.SYS_USERS U JOIN SYS.SYS_TABLES T ON T.USER# = U.ID
JOIN SYS.SYS_COLUMNS C ON T.ID = C.TABLE# AND T.USER# = C.USER#
LEFT JOIN SYS.SYS_HISTGRAM_ABSTR HH ON C.USER# = HH.USER# AND C.TABLE# = HH.TAB# AND C.ID = HH.COL# AND (HH.SPARE1 IS NULL OR HH.SPARE1 = 4294967295)
/

CREATE OR REPLACE VIEW MY_TAB_COL_STATISTICS
(
TABLE_NAME,
COLUMN_NAME,
NUM_DISTINCT,
LOW_VALUE,
HIGH_VALUE,
DENSITY,
NUM_NULLS,
NUM_BUCKETS,
SAMPLE_SIZE,
LAST_ANALYZED,
AVG_COL_LEN,
HISTOGRAM
)
AS
SELECT T.NAME, C.NAME, C.NUM_DISTINCT, C.LOW_VALUE, C.HIGH_VALUE,
HH.DENSITY, HH.NULL_NUM, HH.BUCKET_NUM, T.SAMPLESIZE, T.ANALYZETIME, NULL::NUMBER, C.HISTOGRAM
FROM SYS.DV_ME M JOIN SYS.SYS_TABLES T ON T.USER# = M.USER_ID
JOIN SYS.SYS_COLUMNS C ON T.ID = C.TABLE# AND T.USER# = C.USER#
LEFT JOIN SYS.SYS_HISTGRAM_ABSTR HH ON C.USER# = HH.USER# AND C.TABLE# = HH.TAB# AND C.ID = HH.COL# AND (HH.SPARE1 IS NULL OR HH.SPARE1 = 4294967295)
/

CREATE OR REPLACE PUBLIC SYNONYM DB_TAB_COL_STATISTICS FOR SYS.DB_TAB_COL_STATISTICS
/
GRANT SELECT ON DB_TAB_COL_STATISTICS TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM ADM_TAB_COL_STATISTICS FOR SYS.ADM_TAB_COL_STATISTICS
/
CREATE OR REPLACE PUBLIC SYNONYM MY_TAB_COL_STATISTICS FOR SYS.MY_TAB_COL_STATISTICS
/
GRANT SELECT ON MY_TAB_COL_STATISTICS TO PUBLIC
/

CREATE OR REPLACE VIEW DB_PART_COL_STATISTICS
(
OWNER,
TABLE_NAME,
PARTITION_NAME,
COLUMN_NAME,
NUM_DISTINCT,
LOW_VALUE,
HIGH_VALUE,
DENSITY,
NUM_NULLS,
NUM_BUCKETS,
SAMPLE_SIZE,
LAST_ANALYZED,
AVG_COL_LEN,
HISTOGRAM
)
AS
SELECT U.NAME, T.NAME, TP.NAME, C.NAME, HH.DIST_NUM, HH.MINVALUE, HH.MAXVALUE,
HH.DENSITY, HH.NULL_NUM, HH.BUCKET_NUM, TP.SAMPLESIZE, TP.ANALYZETIME, NULL::NUMBER, C.HISTOGRAM
FROM SYS.SYS_USERS U JOIN SYS.SYS_TABLES T ON T.USER# = U.ID
JOIN SYS.SYS_TABLE_PARTS TP ON T.USER# = TP.USER# AND T.ID = TP.TABLE#
JOIN SYS.SYS_COLUMNS C ON TP.USER# = C.USER# AND TP.TABLE# = C.TABLE#
LEFT JOIN SYS.SYS_HISTGRAM_ABSTR HH ON C.USER# = HH.USER# AND C.TABLE# = HH.TAB# AND C.ID = HH.COL# AND HH.SPARE1 = TP.PART#
where (U.NAME = user or U.NAME = ‘SYS’ or user = ‘SYS’ or exists (select 1 from SYS.SYS_USER_ROLES S, SYS.DV_ME V where S.GRANTEE_ID = V.USER_ID and S.GRANTEE_TYPE = 0 and S.GRANTED_ROLE_ID = 0))
/

CREATE OR REPLACE VIEW ADM_PART_COL_STATISTICS
(
OWNER,
TABLE_NAME,
PARTITION_NAME,
COLUMN_NAME,
NUM_DISTINCT,
LOW_VALUE,
HIGH_VALUE,
DENSITY,
NUM_NULLS,
NUM_BUCKETS,
SAMPLE_SIZE,
LAST_ANALYZED,
AVG_COL_LEN,
HISTOGRAM
)
AS
SELECT U.NAME, T.NAME, TP.NAME, C.NAME, HH.DIST_NUM, HH.MINVALUE, HH.MAXVALUE,
HH.DENSITY, HH.NULL_NUM, HH.BUCKET_NUM, TP.SAMPLESIZE, TP.ANALYZETIME, NULL::NUMBER, C.HISTOGRAM
FROM SYS.SYS_USERS U JOIN SYS.SYS_TABLES T ON T.USER# = U.ID
JOIN SYS.SYS_TABLE_PARTS TP ON T.USER# = TP.USER# AND T.ID = TP.TABLE#
JOIN SYS.SYS_COLUMNS C ON TP.USER# = C.USER# AND TP.TABLE# = C.TABLE#
LEFT JOIN SYS.SYS_HISTGRAM_ABSTR HH ON C.USER# = HH.USER# AND C.TABLE# = HH.TAB# AND C.ID = HH.COL# AND HH.SPARE1 = TP.PART#
/

CREATE OR REPLACE VIEW MY_PART_COL_STATISTICS
(
TABLE_NAME,
PARTITION_NAME,
COLUMN_NAME,
NUM_DISTINCT,
LOW_VALUE,
HIGH_VALUE,
DENSITY,
NUM_NULLS,
NUM_BUCKETS,
SAMPLE_SIZE,
LAST_ANALYZED,
AVG_COL_LEN,
HISTOGRAM
)
AS
SELECT T.NAME, TP.NAME, C.NAME, HH.DIST_NUM, HH.MINVALUE, HH.MAXVALUE,
HH.DENSITY, HH.NULL_NUM, HH.BUCKET_NUM, TP.SAMPLESIZE, TP.ANALYZETIME, NULL::NUMBER, C.HISTOGRAM
FROM SYS.DV_ME M JOIN SYS.SYS_TABLES T ON T.USER# = M.USER_ID
JOIN SYS.SYS_TABLE_PARTS TP ON T.USER# = TP.USER# AND T.ID = TP.TABLE#
JOIN SYS.SYS_COLUMNS C ON TP.USER# = C.USER# AND TP.TABLE# = C.TABLE#
LEFT JOIN SYS.SYS_HISTGRAM_ABSTR HH ON C.USER# = HH.USER# AND C.TABLE# = HH.TAB# AND C.ID = HH.COL# AND HH.SPARE1 = TP.PART#
/

CREATE OR REPLACE PUBLIC SYNONYM DB_PART_COL_STATISTICS FOR SYS.DB_PART_COL_STATISTICS
/
GRANT SELECT ON DB_PART_COL_STATISTICS TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM ADM_PART_COL_STATISTICS FOR SYS.ADM_PART_COL_STATISTICS
/
CREATE OR REPLACE PUBLIC SYNONYM MY_PART_COL_STATISTICS FOR SYS.MY_PART_COL_STATISTICS
/
GRANT SELECT ON MY_PART_COL_STATISTICS TO PUBLIC
/

CREATE OR REPLACE VIEW DB_HISTOGRAMS
(
OWNER,
TABLE_NAME,
COLUMN_NAME,
ENDPOINT_NUMBER,
ENDPOINT_VALUE,
ENDPOINT_ACTUAL_VALUE
)
AS
SELECT U.NAME, T.NAME, C.NAME, ROW_NUMBER() OVER (PARTITION BY T.NAME, C.NAME ORDER BY H.BUCKET), H.ENDPOINT, H.BUCKET
FROM SYS.SYS_USERS U JOIN SYS.SYS_TABLES T ON T.USER# = U.ID
JOIN SYS.SYS_COLUMNS C ON T.USER# = C.USER# AND T.ID = C.TABLE#
JOIN SYS.SYS_HISTGRAM H ON C.USER# = H.USER# AND C.TABLE# = H.TABLE# AND C.ID = H.COL# AND (H.PART# IS NULL OR H.PART# = -1)
where (U.NAME = user or U.NAME = ‘SYS’ or user = ‘SYS’ or exists (select 1 from SYS.SYS_USER_ROLES S, SYS.DV_ME V where S.GRANTEE_ID = V.USER_ID and S.GRANTEE_TYPE = 0 and S.GRANTED_ROLE_ID = 0))
/

CREATE OR REPLACE VIEW ADM_HISTOGRAMS
(
OWNER,
TABLE_NAME,
COLUMN_NAME,
ENDPOINT_NUMBER,
ENDPOINT_VALUE,
ENDPOINT_ACTUAL_VALUE
)
AS
SELECT U.NAME, T.NAME, C.NAME, ROW_NUMBER() OVER (PARTITION BY T.NAME, C.NAME ORDER BY H.BUCKET), H.ENDPOINT, H.BUCKET
FROM SYS.SYS_USERS U JOIN SYS.SYS_TABLES T ON T.USER# = U.ID
JOIN SYS.SYS_COLUMNS C ON T.USER# = C.USER# AND T.ID = C.TABLE#
JOIN SYS.SYS_HISTGRAM H ON C.USER# = H.USER# AND C.TABLE# = H.TABLE# AND C.ID = H.COL# AND (H.PART# IS NULL OR H.PART# = -1)
/

CREATE OR REPLACE VIEW MY_HISTOGRAMS
(
TABLE_NAME,
COLUMN_NAME,
ENDPOINT_NUMBER,
ENDPOINT_VALUE,
ENDPOINT_ACTUAL_VALUE
)
AS
SELECT T.NAME, C.NAME, ROW_NUMBER() OVER (PARTITION BY T.NAME, C.NAME ORDER BY H.BUCKET), H.ENDPOINT, H.BUCKET
FROM SYS.DV_ME M JOIN SYS.SYS_TABLES T ON T.USER# = M.USER_ID
JOIN SYS.SYS_COLUMNS C ON T.USER# = C.USER# AND T.ID = C.TABLE#
JOIN SYS.SYS_HISTGRAM H ON C.USER# = H.USER# AND C.TABLE# = H.TABLE# AND C.ID = H.COL# AND (H.PART# IS NULL OR H.PART# = -1)
/

CREATE OR REPLACE VIEW DB_DB_LINKS
(
OWNER,
DB_LINK,
USERNAME,
HOST,
CREATED
)
AS
SELECT U.NAME, L.NAME, L.USERID, L.HOST, L.CTIME FROM SYS.SYS_LINKS L, SYS.SYS_USERS U WHERE L.OWNER# = U.ID
/

CREATE OR REPLACE VIEW SYS.DB_JOBS AS
SELECT JOB, LOWNER LOG_USER, POWNER PRIV_USER, COWNER SCHEMA_USER,
LAST_DATE, SUBSTR(TO_CHAR(LAST_DATE,‘HH24:MI:SS’),1,8) LAST_SEC,
THIS_DATE, SUBSTR(TO_CHAR(THIS_DATE,‘HH24:MI:SS’),1,8) THIS_SEC,
NEXT_DATE, SUBSTR(TO_CHAR(NEXT_DATE,‘HH24:MI:SS’),1,8) NEXT_SEC,
DECODE(MOD(BROKEN,2),1,‘Y’,0,‘N’,’?’) BROKEN,
INTERVAL# INTERVAL_TIME, FAILURES,
WHAT, CREATE_DATE
FROM SYS.SYS_JOBS J
/

CREATE OR REPLACE VIEW SYS.ADM_JOBS AS
SELECT JOB, LOWNER LOG_USER, POWNER PRIV_USER, COWNER SCHEMA_USER,
LAST_DATE, SUBSTR(TO_CHAR(LAST_DATE,‘HH24:MI:SS’),1,8) LAST_SEC,
THIS_DATE, SUBSTR(TO_CHAR(THIS_DATE,‘HH24:MI:SS’),1,8) THIS_SEC,
NEXT_DATE, SUBSTR(TO_CHAR(NEXT_DATE,‘HH24:MI:SS’),1,8) NEXT_SEC,
DECODE(MOD(BROKEN,2),1,‘Y’,0,‘N’,’?’) BROKEN,
INTERVAL# INTERVAL_TIME, FAILURES,
WHAT, CREATE_DATE
FROM SYS.SYS_JOBS J
/

CREATE OR REPLACE VIEW SYS.MY_JOBS AS
SELECT
J.JOB,J.LOG_USER,J.PRIV_USER,J.SCHEMA_USER,J.LAST_DATE,J.LAST_SEC,
J.THIS_DATE,J.THIS_SEC,J.NEXT_DATE,J.NEXT_SEC,J.BROKEN,J.INTERVAL_TIME, FAILURES,
J.WHAT,CREATE_DATE
FROM ADM_JOBS J, SYS.DV_ME M WHERE
J.LOG_USER = M.USER_NAME
/

CREATE OR REPLACE VIEW SYS.ADM_JOBS_RUNNING AS
SELECT V.JOBNO JOB, V.SESSION_ID SID, V.SERIAL_ID SERIAL#, J.FAILURES,
J.LAST_DATE, SUBSTR(TO_CHAR(J.LAST_DATE,‘HH24:MI:SS’),1,8) LAST_SEC,
J.THIS_DATE, SUBSTR(TO_CHAR(J.THIS_DATE,‘HH24:MI:SS’),1,8) THIS_SEC
FROM SYS.DV_RUNNING_JOBS V LEFT JOIN SYS.SYS_JOBS J ON V.JOBNO = J.JOB
/

CREATE OR REPLACE PUBLIC SYNONYM DB_HISTOGRAMS FOR SYS.DB_HISTOGRAMS
/
GRANT SELECT ON DB_HISTOGRAMS TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM ADM_HISTOGRAMS FOR SYS.ADM_HISTOGRAMS
/
CREATE OR REPLACE PUBLIC SYNONYM MY_HISTOGRAMS FOR SYS.MY_HISTOGRAMS
/
GRANT SELECT ON MY_HISTOGRAMS TO PUBLIC
/

CREATE OR REPLACE VIEW SYS.DB_DEPENDENCIES
(OWNER, NAME, TYPE, REFERENCED_OWNER, REFERENCED_NAME, REFERENCED_TYPE)
AS
SELECT DBMS_DIAGNOSE.DBA_USER_NAME(D_OWNER#)::VARCHAR(64), D.D_NAME,
DECODE(D.D_TYPE#, 0, ‘TABLE’, 1, ‘VIEW’, 2, ‘SEQUENCE’, 3, ‘PROCEDURE’,
7, ‘SYNONYM’, 8, ‘FUNCTION’, 9, ‘TRIGGER’, 10, ‘INDEX’,
11, ‘DYNAMIC VIEW’, 15, ‘SYNONYM’,‘UNDEFINED’),
DBMS_DIAGNOSE.DBA_USER_NAME(P_OWNER#)::VARCHAR(64), D.P_NAME,
DECODE(D.P_TYPE#, 0, ‘TABLE’, 1, ‘VIEW’, 2, ‘SEQUENCE’, 3, ‘PROCEDURE’,
7, ‘SYNONYM’, 8, ‘FUNCTION’, 9, ‘TRIGGER’, 10, ‘INDEX’,
11, ‘DYNAMIC VIEW’, 15, ‘SYNONYM’ ,‘UNDEFINED’)
FROM SYS.SYS_DEPENDENCIES D where DBMS_DIAGNOSE.DBA_USER_NAME(D_OWNER#)::VARCHAR(64) = user or DBMS_DIAGNOSE.DBA_USER_NAME(D_OWNER#)::VARCHAR(64) = ‘SYS’ or user = ‘SYS’
or exists (select 1 from SYS.SYS_USER_ROLES S, SYS.DV_ME V where S.GRANTEE_ID = V.USER_ID and S.GRANTEE_TYPE = 0 and S.GRANTED_ROLE_ID = 0)
/

CREATE OR REPLACE VIEW SYS.DB_VIEW_DEPENDENCIES
(OWNER, NAME, REFERENCED_OWNER, REFERENCED_NAME, REFERENCED_LEVEL)
AS
SELECT
DBMS_DIAGNOSE.DBA_USER_NAME(D_OWNER#)::VARCHAR(64) as OWNER,
D.D_NAME,
DBMS_DIAGNOSE.DBA_USER_NAME(P_OWNER#)::VARCHAR(64) as REFERENCED_OWNER,
D.P_NAME,
LEVEL REFERENCED_LEVEL
FROM
SYS.SYS_DEPENDENCIES D
START WITH
D.D_TYPE# = 1 and D.P_TYPE# = 1 and (DBMS_DIAGNOSE.DBA_USER_NAME(D_OWNER#)::VARCHAR(64) = user or DBMS_DIAGNOSE.DBA_USER_NAME(D_OWNER#)::VARCHAR(64) = ‘SYS’ or user = ‘SYS’
or exists (select 1 from SYS.SYS_USER_ROLES S, SYS.DV_ME V where S.GRANTEE_ID = V.USER_ID and S.GRANTEE_TYPE = 0 and S.GRANTED_ROLE_ID = 0))
CONNECT BY
prior D.P_NAME = D.D_NAME and D.P_TYPE# = 1 and D.D_TYPE# = 1
/

CREATE OR REPLACE VIEW SYS.ADM_DEPENDENCIES
(OWNER, NAME, TYPE, REFERENCED_OWNER, REFERENCED_NAME, REFERENCED_TYPE)
AS
SELECT DBMS_DIAGNOSE.DBA_USER_NAME(D_OWNER#)::VARCHAR(64) as OWNER, D.D_NAME,
DECODE(D.D_TYPE#, 0, ‘TABLE’, 1, ‘VIEW’, 2, ‘SEQUENCE’, 3, ‘PROCEDURE’,
7, ‘SYNONYM’, 8, ‘FUNCTION’, 9, ‘TRIGGER’, 10, ‘INDEX’,
11, ‘DYNAMIC VIEW’, 15, ‘SYNONYM’ ,‘UNDEFINED’),
DBMS_DIAGNOSE.DBA_USER_NAME(P_OWNER#)::VARCHAR(64), D.P_NAME,
DECODE(D.P_TYPE#, 0, ‘TABLE’, 1, ‘VIEW’, 2, ‘SEQUENCE’, 3, ‘PROCEDURE’,
7, ‘SYNONYM’, 8, ‘FUNCTION’, 9, ‘TRIGGER’, 10, ‘INDEX’,
11, ‘DYNAMIC VIEW’, 15, ‘SYNONYM’ ,‘UNDEFINED’)
FROM SYS.SYS_DEPENDENCIES D
/

CREATE OR REPLACE VIEW SYS.MY_DEPENDENCIES
(OWNER, NAME, TYPE, REFERENCED_OWNER, REFERENCED_NAME, REFERENCED_TYPE)
AS
SELECT DBMS_DIAGNOSE.DBA_USER_NAME(D.D_OWNER#)::VARCHAR(64), D.D_NAME,
DECODE(D.D_TYPE#, 0, ‘TABLE’, 1, ‘VIEW’, 2, ‘SEQUENCE’, 3, ‘PROCEDURE’,
7, ‘SYNONYM’, 8, ‘FUNCTION’, 9, ‘TRIGGER’, 10, ‘INDEX’,
11, ‘DYNAMIC VIEW’, 15, ‘SYNONYM’ ,‘UNDEFINED’),
DBMS_DIAGNOSE.DBA_USER_NAME(D.P_OWNER#)::VARCHAR(64), D.P_NAME,
DECODE(D.P_TYPE#, 0, ‘TABLE’, 1, ‘VIEW’, 2, ‘SEQUENCE’, 3, ‘PROCEDURE’,
7, ‘SYNONYM’, 8, ‘FUNCTION’, 9, ‘TRIGGER’, 10, ‘INDEX’,
11, ‘DYNAMIC VIEW’, 15, ‘SYNONYM’ ,‘UNDEFINED’)
FROM SYS.SYS_DEPENDENCIES D, SYS.DV_ME M
WHERE D.D_OWNER# = M.USER_ID
/

CREATE OR REPLACE VIEW DB_TAB_DISTRIBUTE
(
OWNER,
TABLE_NAME,
DIST_INFO
)
AS
SELECT U.NAME, T.NAME, get_distribute_str(S.USER#, S.TABLE#)
FROM SYS.SYS_USERS U, SYS.SYS_TABLES T, SYS.SYS_DISTRIBUTE_STRATEGIES S
WHERE U.ID = T.USER# AND U.ID = S.USER# AND T.ID = S.TABLE# AND T.RECYCLED = 0
and (U.NAME = user or U.NAME = ‘SYS’ or user = ‘SYS’ or exists (select 1 from SYS.SYS_USER_ROLES R, SYS.DV_ME V where R.GRANTEE_ID = V.USER_ID and R.GRANTEE_TYPE = 0 and R.GRANTED_ROLE_ID = 0))
/

CREATE OR REPLACE VIEW ADM_TAB_DISTRIBUTE
(
OWNER,
TABLE_NAME,
DIST_INFO
)
AS
SELECT U.NAME, T.NAME, get_distribute_str(S.USER#, S.TABLE#)
FROM SYS.SYS_USERS U, SYS.SYS_TABLES T, SYS.SYS_DISTRIBUTE_STRATEGIES S
WHERE U.ID = T.USER# AND U.ID = S.USER# AND T.ID = S.TABLE# AND T.RECYCLED = 0
/

CREATE OR REPLACE VIEW MY_TAB_DISTRIBUTE
(
OWNER,
TABLE_NAME,
DIST_INFO
)
AS
SELECT U.USER_NAME, T.NAME, get_distribute_str(S.USER#, S.TABLE#)
FROM SYS.DV_ME U, SYS.SYS_TABLES T, SYS.SYS_DISTRIBUTE_STRATEGIES S
WHERE U.USER_ID = T.USER# AND U.USER_ID = S.USER# AND T.ID = S.TABLE# AND T.RECYCLED = 0
/

CREATE OR REPLACE VIEW DB_DISTRIBUTE_RULES
(
ID,
NAME,
DIST_INFO
)
AS
SELECT R.ID, R.NAME, get_distribute_str(R.UID, R.ID)
FROM SYS.SYS_DISTRIBUTE_RULES R
/

CREATE OR REPLACE VIEW MY_SQL_MAPS
(
SRC_SQL,
DEST_SQL
)
AS
SELECT S.SRC_TEXT, S.DST_TEXT
FROM SYS.DV_ME ME, SYS.SYS_SQL_MAPS S
WHERE S.USER# = ME.USER_ID
/

CREATE OR REPLACE PUBLIC SYNONYM MY_SEGMENTS FOR SYS.MY_SEGMENTS
/
GRANT SELECT ON MY_SEGMENTS TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM ADM_SEGMENTS FOR SYS.ADM_SEGMENTS
/
CREATE OR REPLACE PUBLIC SYNONYM DB_TABLES FOR SYS.DB_TABLES
/
CREATE OR REPLACE PUBLIC SYNONYM ADM_TABLES FOR SYS.ADM_TABLES
/
GRANT SELECT ON DB_TABLES TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM DB_DBLINK_TABLES FOR SYS.DB_DBLINK_TABLES
/
GRANT SELECT ON DB_DBLINK_TABLES TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM ADM_DBLINK_TABLES FOR SYS.ADM_DBLINK_TABLES
/
CREATE OR REPLACE PUBLIC SYNONYM MY_TABLES FOR SYS.MY_TABLES
/
GRANT SELECT ON MY_TABLES TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM DB_TAB_COLS FOR SYS.DB_TAB_COLS
/
GRANT SELECT ON DB_TAB_COLS TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM DB_DIST_RULE_COLS FOR SYS.DB_DIST_RULE_COLS
/
GRANT SELECT ON DB_DIST_RULE_COLS TO PUBLIC
/

CREATE OR REPLACE PUBLIC SYNONYM ADM_PART_TABLES FOR SYS.ADM_PART_TABLES
/
CREATE OR REPLACE PUBLIC SYNONYM DB_PART_TABLES FOR SYS.DB_PART_TABLES
/
GRANT SELECT ON DB_PART_TABLES TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM MY_PART_TABLES FOR SYS.MY_PART_TABLES
/
GRANT SELECT ON MY_PART_TABLES TO PUBLIC
/

CREATE OR REPLACE PUBLIC SYNONYM ADM_PART_STORE FOR SYS.ADM_PART_STORE
/
CREATE OR REPLACE PUBLIC SYNONYM DB_PART_STORE FOR SYS.DB_PART_STORE
/
GRANT SELECT ON DB_PART_STORE TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM MY_PART_STORE FOR SYS.MY_PART_STORE
/
GRANT SELECT ON MY_PART_STORE TO PUBLIC
/

CREATE OR REPLACE PUBLIC SYNONYM ADM_PART_KEY_COLUMNS FOR SYS.ADM_PART_KEY_COLUMNS
/
CREATE OR REPLACE PUBLIC SYNONYM DB_PART_KEY_COLUMNS FOR SYS.DB_PART_KEY_COLUMNS
/
GRANT SELECT ON DB_PART_KEY_COLUMNS TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM MY_PART_KEY_COLUMNS FOR SYS.MY_PART_KEY_COLUMNS
/
GRANT SELECT ON MY_PART_KEY_COLUMNS TO PUBLIC
/

CREATE OR REPLACE PUBLIC SYNONYM MY_TAB_COLS FOR SYS.MY_TAB_COLS
/
GRANT SELECT ON MY_TAB_COLS TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM DB_TAB_COLUMNS FOR SYS.DB_TAB_COLUMNS
/
GRANT SELECT ON DB_TAB_COLUMNS TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM ADM_DBLINK_TAB_COLUMNS FOR SYS.ADM_DBLINK_TAB_COLUMNS
/
CREATE OR REPLACE PUBLIC SYNONYM DB_DBLINK_TAB_COLUMNS FOR SYS.DB_DBLINK_TAB_COLUMNS
/
GRANT SELECT ON DB_DBLINK_TAB_COLUMNS TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM MY_TAB_COLUMNS FOR SYS.MY_TAB_COLUMNS
/
GRANT SELECT ON MY_TAB_COLUMNS TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM ADM_TAB_COLUMNS FOR SYS.ADM_TAB_COLUMNS
/
CREATE OR REPLACE PUBLIC SYNONYM DB_VIEWS FOR SYS.DB_VIEWS
/
GRANT SELECT ON DB_VIEWS TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM ADM_VIEWS FOR SYS.ADM_VIEWS
/
CREATE OR REPLACE PUBLIC SYNONYM MY_VIEWS FOR SYS.MY_VIEWS
/
GRANT SELECT ON MY_VIEWS TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM DB_VIEW_COLUMNS FOR SYS.DB_VIEW_COLUMNS
/
GRANT SELECT ON DB_VIEW_COLUMNS TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM MY_VIEW_COLUMNS FOR SYS.MY_VIEW_COLUMNS
/
GRANT SELECT ON MY_VIEW_COLUMNS TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM DB_INDEXES FOR SYS.DB_INDEXES
/
GRANT SELECT ON DB_INDEXES TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM ADM_INDEXES FOR SYS.ADM_INDEXES
/
CREATE OR REPLACE PUBLIC SYNONYM MY_INDEXES FOR SYS.MY_INDEXES
/
GRANT SELECT ON MY_INDEXES TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM DB_OBJECTS FOR SYS.DB_OBJECTS
/
GRANT SELECT ON DB_OBJECTS TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM MY_OBJECTS FOR SYS.MY_OBJECTS
/
GRANT SELECT ON MY_OBJECTS TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM ADM_TABLESPACES FOR SYS.ADM_TABLESPACES
/
CREATE OR REPLACE PUBLIC SYNONYM MY_SYNONYMS FOR SYS.MY_SYNONYMS
/
GRANT SELECT ON MY_SYNONYMS TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM ADM_SYNONYMS FOR SYS.ADM_SYNONYMS
/
CREATE OR REPLACE PUBLIC SYNONYM DB_SYNONYMS FOR SYS.DB_SYNONYMS
/
GRANT SELECT ON DB_SYNONYMS TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM MY_TAB_COMMENTS FOR SYS.MY_TAB_COMMENTS
/
CREATE OR REPLACE PUBLIC SYNONYM DB_TAB_COMMENTS FOR SYS.DB_TAB_COMMENTS
/
GRANT SELECT ON MY_TAB_COMMENTS TO PUBLIC
/
GRANT SELECT ON DB_TAB_COMMENTS TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM ADM_TAB_COMMENTS FOR SYS.ADM_TAB_COMMENTS
/
CREATE OR REPLACE PUBLIC SYNONYM MY_COL_COMMENTS FOR SYS.MY_COL_COMMENTS
/
CREATE OR REPLACE PUBLIC SYNONYM DB_COL_COMMENTS FOR SYS.DB_COL_COMMENTS
/
GRANT SELECT ON MY_COL_COMMENTS TO PUBLIC
/
GRANT SELECT ON DB_COL_COMMENTS TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM ADM_COL_COMMENTS FOR SYS.ADM_COL_COMMENTS
/
CREATE OR REPLACE PUBLIC SYNONYM DB_USERS FOR SYS.DB_USERS
/
CREATE OR REPLACE PUBLIC SYNONYM ADM_PROFILES FOR SYS.ADM_PROFILES
/
CREATE OR REPLACE PUBLIC SYNONYM DB_USER_SYS_PRIVS FOR SYS.DB_USER_SYS_PRIVS
/
CREATE OR REPLACE PUBLIC SYNONYM ROLE_SYS_PRIVS FOR SYS.ROLE_SYS_PRIVS
/
GRANT SELECT ON ROLE_SYS_PRIVS TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM ADM_SYS_PRIVS FOR SYS.ADM_SYS_PRIVS
/
CREATE OR REPLACE PUBLIC SYNONYM MY_SYS_PRIVS FOR SYS.MY_SYS_PRIVS
/
GRANT SELECT ON MY_SYS_PRIVS TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM ADM_ROLE_PRIVS FOR SYS.ADM_ROLE_PRIVS
/
CREATE OR REPLACE PUBLIC SYNONYM MY_ROLE_PRIVS FOR SYS.MY_ROLE_PRIVS
/
GRANT SELECT ON MY_ROLE_PRIVS TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM ADM_TAB_PRIVS FOR SYS.ADM_TAB_PRIVS
/
CREATE OR REPLACE PUBLIC SYNONYM MY_TAB_PRIVS FOR SYS.MY_TAB_PRIVS
/
GRANT SELECT ON MY_TAB_PRIVS TO PUBLIC
/

CREATE OR REPLACE PUBLIC SYNONYM DB_SEQUENCES FOR SYS.DB_SEQUENCES
/
GRANT SELECT ON DB_SEQUENCES TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM MY_SEQUENCES FOR SYS.MY_SEQUENCES
/
GRANT SELECT ON MY_SEQUENCES TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM ADM_TAB_PARTITIONS FOR SYS.ADM_TAB_PARTITIONS
/
CREATE OR REPLACE PUBLIC SYNONYM DB_TAB_PARTITIONS FOR SYS.DB_TAB_PARTITIONS
/
GRANT SELECT ON DB_TAB_PARTITIONS TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM MY_TAB_PARTITIONS FOR SYS.MY_TAB_PARTITIONS
/
GRANT SELECT ON MY_TAB_PARTITIONS TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM ADM_IND_COLUMNS FOR SYS.ADM_IND_COLUMNS
/
CREATE OR REPLACE PUBLIC SYNONYM DB_IND_COLUMNS FOR SYS.DB_IND_COLUMNS
/
CREATE OR REPLACE PUBLIC SYNONYM MY_IND_COLUMNS FOR SYS.MY_IND_COLUMNS
/
GRANT SELECT ON DB_IND_COLUMNS TO PUBLIC
/
GRANT SELECT ON MY_IND_COLUMNS TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM MY_USERS FOR SYS.MY_USERS
/
GRANT SELECT ON MY_USERS TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM MY_CONSTRAINTS FOR SYS.MY_CONSTRAINTS
/
GRANT SELECT ON MY_CONSTRAINTS TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM DB_CONSTRAINTS FOR SYS.DB_CONSTRAINTS
/
GRANT SELECT ON DB_CONSTRAINTS TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM ADM_CONSTRAINTS FOR SYS.ADM_CONSTRAINTS
/
CREATE OR REPLACE PUBLIC SYNONYM ADM_SOURCE FOR SYS.ADM_SOURCE
/
CREATE OR REPLACE PUBLIC SYNONYM MY_SOURCE FOR SYS.MY_SOURCE
/
CREATE OR REPLACE PUBLIC SYNONYM DB_SOURCE FOR SYS.DB_SOURCE
/
GRANT SELECT ON MY_SOURCE TO PUBLIC
/
GRANT SELECT ON DB_SOURCE TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM ADM_PROCEDURES FOR SYS.ADM_PROCEDURES
/
CREATE OR REPLACE PUBLIC SYNONYM DB_PROCEDURES FOR SYS.DB_PROCEDURES
/
GRANT SELECT ON DB_PROCEDURES TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM ADM_TRIGGERS FOR SYS.ADM_TRIGGERS
/
CREATE OR REPLACE PUBLIC SYNONYM MY_TRIGGERS FOR SYS.MY_TRIGGERS
/
GRANT SELECT ON MY_TRIGGERS TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM MY_CONS_COLUMNS FOR SYS.MY_CONS_COLUMNS
/
GRANT SELECT ON MY_CONS_COLUMNS TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM DB_TRIGGERS FOR SYS.DB_TRIGGERS
/
GRANT SELECT ON DB_TRIGGERS TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM ADM_BACKUP_SET FOR SYS.ADM_BACKUP_SET
/
CREATE OR REPLACE PUBLIC SYNONYM ADM_OBJECTS FOR SYS.ADM_OBJECTS
/
CREATE OR REPLACE PUBLIC SYNONYM ADM_ROLES FOR SYS.ADM_ROLES
/
CREATE OR REPLACE PUBLIC SYNONYM ADM_DATA_FILES FOR SYS.ADM_DATA_FILES
/
CREATE OR REPLACE PUBLIC SYNONYM ADM_USERS FOR SYS.ADM_USERS
/
CREATE OR REPLACE PUBLIC SYNONYM ADM_ARGUMENTS FOR SYS.ADM_ARGUMENTS
/
CREATE OR REPLACE PUBLIC SYNONYM MY_ARGUMENTS FOR SYS.MY_ARGUMENTS
/
GRANT SELECT ON MY_ARGUMENTS TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM DB_ARGUMENTS FOR SYS.DB_ARGUMENTS
/
GRANT SELECT ON DB_ARGUMENTS TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM ADM_FREE_SPACE FOR SYS.ADM_FREE_SPACE
/
CREATE OR REPLACE PUBLIC SYNONYM MY_FREE_SPACE FOR SYS.MY_FREE_SPACE
/
GRANT SELECT ON MY_FREE_SPACE TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM DB_DEPENDENCIES FOR SYS.DB_DEPENDENCIES
/
GRANT SELECT ON DB_DEPENDENCIES TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM DB_VIEW_DEPENDENCIES FOR SYS.DB_VIEW_DEPENDENCIES
/
GRANT SELECT ON DB_VIEW_DEPENDENCIES TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM ADM_DEPENDENCIES FOR SYS.ADM_DEPENDENCIES
/
CREATE OR REPLACE PUBLIC SYNONYM MY_DEPENDENCIES FOR SYS.MY_DEPENDENCIES
/
GRANT SELECT ON MY_DEPENDENCIES TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM MY_PROCEDURES FOR SYS.MY_PROCEDURES
/
GRANT SELECT ON MY_PROCEDURES TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM DB_TAB_DISTRIBUTE FOR SYS.DB_TAB_DISTRIBUTE
/
GRANT SELECT ON DB_TAB_DISTRIBUTE TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM ADM_TAB_DISTRIBUTE FOR SYS.ADM_TAB_DISTRIBUTE
/
CREATE OR REPLACE PUBLIC SYNONYM MY_TAB_DISTRIBUTE FOR SYS.MY_TAB_DISTRIBUTE
/
GRANT SELECT ON MY_TAB_DISTRIBUTE TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM DB_DISTRIBUTE_RULES FOR SYS.DB_DISTRIBUTE_RULES
/
GRANT SELECT ON DB_DISTRIBUTE_RULES TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM ADM_JOBS FOR SYS.ADM_JOBS
/
CREATE OR REPLACE PUBLIC SYNONYM DB_JOBS FOR SYS.DB_JOBS
/
CREATE OR REPLACE PUBLIC SYNONYM MY_JOBS FOR SYS.MY_JOBS
/
CREATE OR REPLACE PUBLIC SYNONYM ADM_JOBS_RUNNING FOR SYS.ADM_JOBS_RUNNING
/
CREATE OR REPLACE PUBLIC SYNONYM ADM_PROCEDURES FOR SYS.ADM_PROCEDURES
/
CREATE OR REPLACE PUBLIC SYNONYM ADM_SEQUENCES FOR SYS.ADM_SEQUENCES
/
CREATE OR REPLACE PUBLIC SYNONYM ADM_SOURCE FOR SYS.ADM_SOURCE
/
CREATE OR REPLACE PUBLIC SYNONYM ADM_TAB_COLS FOR SYS.ADM_TAB_COLS
/
CREATE OR REPLACE PUBLIC SYNONYM ADM_VIEW_COLUMNS FOR SYS.ADM_VIEW_COLUMNS
/
GRANT SELECT ON MY_JOBS TO PUBLIC
/
GRANT EXECUTE ON SYS.DBMS_OUTPUT TO PUBLIC
/
GRANT EXECUTE ON SYS.DBMS_LOB TO PUBLIC
/
GRANT EXECUTE ON SYS.DBMS_RAFT TO PUBLIC
/
GRANT EXECUTE ON SYS.DBMS_RANDOM TO PUBLIC
/
GRANT EXECUTE ON SYS.DBMS_SQL TO PUBLIC
/
GRANT EXECUTE ON SYS.DBMS_STANDARD TO PUBLIC
/
GRANT EXECUTE ON SYS.DBMS_STATS TO PUBLIC
/
GRANT EXECUTE ON SYS.DBMS_UTILITY TO PUBLIC
/
GRANT EXECUTE ON SYS.DBMS_JOB TO DBA
/
CREATE OR REPLACE PUBLIC SYNONYM MY_SQL_MAPS FOR SYS.MY_SQL_MAPS
/
GRANT SELECT ON MY_SQL_MAPS TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM SYS_DUMMY FOR SYS.SYS_DUMMY
/
GRANT EXECUTE ON SYS.DBMS_DEBUG TO PUBLIC;
/