CREATE OR REPLACE VIEW SYS.V$PL_MANAGER
(
USER#,
USER_NAME,
NAME,
TYPE,
BUCKET_ID,
LIST_POS,
UNUSED,
TRIG_USER,
TRIG_TABLE,
REF_COUNT,
ENTITY,
PAGES,
PACKAGE_NAME
)
AS SELECT * FROM SYS.DV_PL_MANAGER
/
CREATE OR REPLACE VIEW SYS.V$PL_REFSQLS
(
USER#,
USER_NAME,
NAME,
ENTITY,
SQL_ID
)
AS SELECT * FROM SYS.DV_PL_REFSQLS
/
CREATE OR REPLACE VIEW SYS.V$REPL_STATUS
(
DATABASE_ROLE,
DATABASE_CONDITION,
SWITCHOVER_STATUS
)
AS SELECT * FROM SYS.DV_REPL_STATUS
/
CREATE OR REPLACE VIEW SYS.V$SGASTAT
(
AREA,
POOL,
NAME,
VALUE
)
AS SELECT * FROM SYS.DV_GMA_STATS
/
CREATE OR REPLACE VIEW SYS.V$SQLPOOL
(
SQL_ID,
SQL_TYPE,
UID,
REF_COUNT,
VALID,
CLEANED,
IS_FREE,
MCTX_PAGE_COUNT,
MCTX_PAGE_FRIST,
MCTX_PAGE_LAST,
CURRENT_PAGE_ID,
MCTX_PAGES,
LARGE_PAGE,
FIRST_OPTMZ_VARS,
FIRST_OPTMZ_BUFF,
LAST_LOAD_TIME,
LAST_ACTIVE_TIME
)
AS SELECT * FROM SYS.DV_SQL_POOL
/
CREATE OR REPLACE VIEW SYS.V$LIBRARYCACHE
(
NAMESPACE,
GETS,
GETHITS,
PINS,
PINHITS,
RELOADS,
INVLIDATIONS
)
AS SELECT * FROM SYS.DV_LIBRARY_CACHE
/
CREATE OR REPLACE VIEW SYS.V$SYSSTAT
(
STATISTIC#,
NAME,
CLASS,
VALUE
)
AS SELECT * FROM SYS.DV_SYS_STATS
/
CREATE OR REPLACE VIEW SYS.V$SYSTEM
(
ID,
NAME,
VALUE,
COMMENTS,
ACCUMULATIVE
)
AS SELECT * FROM SYS.DV_SYSTEM
/
CREATE OR REPLACE VIEW SYS.V$SYSTEM_EVENT
(
EVENT#,
EVENT,
P1,
WAIT_CLASS,
TOTAL_WAITS,
TIME_WAITED,
TIME_WAITED_MIRCO,
AVERAGE_WAIT,
AVERAGE_WAIT_MIRCO
)
AS SELECT * FROM SYS.DV_SYS_EVENTS
/
CREATE OR REPLACE VIEW SYS.V$TABLESPACE
(
ID,
NAME,
TEMPORARY,
IN_MEMORY,
AUTO_PURGE,
EXTENT_SIZE,
SEGMENT_COUNT,
FILE_COUNT,
STATUS,
AUTO_OFFLINE,
EXTENT_MANAGEMENT,
EXTENT_ALLOCATION,
ENCRYPT,
PUNCHED_SIZE
)
AS SELECT * FROM SYS.DV_TABLESPACES
/
CREATE OR REPLACE VIEW SYS.V$TEMP_POOL
(
ID,
TOTAL_VIRTUAL,
FREE_VIRTUAL,
PAGE_SIZE,
TOTAL_PAGES,
FREE_PAGES,
PAGE_HWM,
FREE_LIST,
CLOSED_LIST,
DISK_EXTENTS,
SWAP_COUNT,
FREE_EXTENTS,
MAX_SWAP_COUNT
)
AS
SELECT * FROM SYS.DV_TEMP_POOLS
/
CREATE OR REPLACE VIEW SYS.V$TRANSACTION
(
SEG_ID,
SLOT,
XNUM,
SCN,
SID,
STATUS,
UNDO_COUNT,
UNDO_FIRST,
UNDO_LAST,
BEGIN_TIME,
TXN_PAGEID,
RMID,
REMAINED,
EXEC_TIME
)
AS
SELECT * FROM SYS.DV_TRANSACTIONS
/
CREATE OR REPLACE VIEW SYS.V$UNDO_SEGMENT
(
ID,
SEG_ENTRY,
SEG_STATUS,
TXN_PAGES,
TXN_FREE_ITEM_CNT,
TXN_FIRST,
TXN_LAST,
UNDO_PAGES,
UNDO_FIRST,
UNDO_LAST,
FIRST_TIME,
LAST_TIME,
RETENTION_TIME,
OW_SCN,
BEGIN_TIME,
TXN_CNT,
REUSE_XP_PAGES,
REU_UNXP_PAGES,
USE_SPACE_PAGES,
STEAL_XP_PAGES,
STEAL_UNXP_PAGES,
STEALED_XP_PAGES,
STEALED_UNXP_PAGES,
BUF_BUSY_WAITS
)
AS
SELECT * FROM SYS.DV_UNDO_SEGMENTS
/
CREATE OR REPLACE VIEW SYS.V$UNDO_STAT
(
BEGIN_TIME,
END_TIME,
TOTAL_UNDO_PAGES,
REU_XP_PAGES,
REU_UNXP_PAGES,
USE_SPACE_PAGES,
STEAL_XP_PAGES,
STEAL_UNXP_PAGES,
TXN_CNT,
LONGEST_SQL_TIME,
BUF_BUSY_WAITS,
BUSY_WAITS_SEG,
BUSY_SEG_PAGES,
RETENTION_TIME
)
AS
SELECT * FROM SYS.DV_UNDO_STATS
/
CREATE OR REPLACE VIEW SYS.V$INDEX_COALESCE
(
USER_NAME,
TABLE_NAME,
INDEX_NAME,
INDEX_PART_NAME,
INDEX_SUBPART_NAME,
NEED_RECYCLE,
GARBAGE_RATIO,
GARBAGE_SIZE,
EMPTY_RATIO,
EMPTY_SIZE,
FIRST_CHILD_EMPTY_SIZE,
RECYCLE_STAT,
SEGMENT_SIZE,
RECYCLED_SIZE,
RECYCLED_REUSABLE,
FIRST_RECYCLE_SCN,
LAST_RECYCLE_SCN,
OW_DEL_SCN,
OW_RECYCLE_SCN,
DELETE_SIZE,
INSERT_SIZE,
ALLOC_PAGES,
SEGMENT_SCN,
BTREE_LEVEL
)
AS
SELECT * FROM SYS.DV_INDEX_COALESCE
/
CREATE OR REPLACE VIEW SYS.V$INDEX_RECYCLE
(
UID,
TABLE_ID,
INDEX_ID,
PART_ORG_SCN,
XID,
SCN,
IS_TX_ACTIVE,
MIN_SCN,
CUR_SCN
)
AS
SELECT * FROM SYS.DV_INDEX_RECYCLE
/
CREATE OR REPLACE VIEW SYS.V$INDEX_REBUILD
(
UID,
TABLE_ID,
ALTER_INDEX_TYPE,
INDEX_NAME,
INDEX_PART_NAME,
STATE,
SCN
)
AS
SELECT * FROM SYS.DV_INDEX_REBUILD
/
CREATE OR REPLACE VIEW SYS.V$VERSION
(
VERSION
)
AS
SELECT * FROM SYS.DV_VERSION
/
CREATE OR REPLACE VIEW SYS.V$USER_ASTATUS_MAP
(
STATUS#,
STATUS
)
AS
SELECT * FROM SYS.DV_USER_ASTATUS_MAP
/
CREATE OR REPLACE VIEW SYS.V$TEMP_UNDO_SEGMENT
(
ID,
SEG_ENTRY,
TXN_PAGES,
UNDO_PAGES,
UNDO_FIRST,
UNDO_LAST,
FIRST_TIME,
LAST_TIME,
RETENTION_TIME,
OW_SCN
)
AS SELECT * FROM SYS.DV_TEMP_UNDO_SEGMENT
/
CREATE OR REPLACE VIEW SYS.V$REACTOR_POOL
(
REACTOR_ID,
EPOLL_FD,
REACTOR_STATUS,
SESSION_COUNT,
KILLEVENT_R_POS,
KILLEVENT_W_POS,
ACTIVE_AGENT_COUNT,
BLANK_AGENT_COUNT,
IDLE_AGENT_COUNT,
OPTIMIZED_AGENT_COUNT,
MAX_AGENT_COUNT,
MODE,
DEDICATED_AGENT_COUNT,
FREE_DEDICATED_AGENT_COUNT,
EMERG_SESSION_COUNT,
BUSY_SCHEDULING_COUNT,
PRIV_ACTIVE_AGENT_COUNT,
PRIV_BLANK_AGENT_COUNT,
PRIV_IDLE_AGENT_COUNT,
PRIV_OPTIMIZED_AGENT_COUNT,
PRIV_MAX_AGENT_COUNT
)
AS SELECT * FROM SYS.DV_REACTOR_POOLS
/
CREATE OR REPLACE VIEW SYS.V$RESOURCE_MAP
(
RESOURCE#,
TYPE#,
NAME
)
AS SELECT * FROM SYS.DV_RESOURCE_MAP
/
CREATE OR REPLACE VIEW SYS.V$SEGMENT_STATISTICS
(
OWNER,
OBJECT_NAME,
SUBOBJECT_NAME,
TS#,
OBJECT_TYPE,
STATISTIC_NAME,
STATISTIC#,
VALUE
)
AS SELECT * FROM SYS.DV_SEGMENT_STATS
/
CREATE OR REPLACE VIEW SYS.V$SESSION
(
SID,
SPID,
SERIAL#,
USER#,
USERNAME,
CURR_SCHEMA,
PIPE_TYPE,
CLIENT_IP,
CLIENT_PORT,
CLIENT_UDS_PATH,
SERVER_IP,
SERVER_PORT,
SERVER_UDS_PATH,
SERVER_MODE,
OSUSER,
MACHINE,
PROGRAM,
AUTO_COMMIT,
CLIENT_VERSION,
TYPE,
LOGON_TIME,
STATUS,
LOCK_WAIT,
WAIT_SID,
EXECUTIONS,
SIMPLE_QUERIES,
DISK_READS,
BUFFER_GETS,
CR_GETS,
CURRENT_SQL,
SQL_EXEC_START,
SQL_ID,
ATOMIC_OPERS,
REDO_BYTES,
COMMITS,
NOWAIT_COMMITS,
XA_COMMITS,
ROLLBACKS,
XA_ROLLBACKS,
LOCAL_TXN_TIMES,
XA_TXN_TIMES,
PARSES,
HARD_PARSES,
EVENT#,
EVENT,
SORTS,
PROCESSED_ROWS,
IO_WAIT_TIME,
CON_WAIT_TIME,
CPU_TIME,
ELAPSED_TIME,
ISOLEVEL,
MODULE,
VMP_PAGES,
LARGE_VMP_PAGES,
RES_CONTROL_GROUP,
RES_IO_WAIT_TIME,
RES_QUEUE_TIME,
PRIV_FLAG,
QUERY_SCN,
STMT_COUNT,
MIN_SCN,
PREV_SQL_ID,
DCS_BUFFER_GETS,
DCS_BUFFER_SENDS,
DCS_CR_GETS,
DCS_CR_SENDS
)
AS
SELECT * FROM SYS.DV_SESSIONS
/
CREATE OR REPLACE VIEW SYS.V$SESSION_EX
(
SID,
SQL_ID,
EVENT#,
EVENT,
CONN_NODE
)
AS
SELECT * FROM SYS.DV_SESSIONS_EX
/
CREATE OR REPLACE VIEW SYS.V$LOCKED_OBJECT
(
SESSION_ID,
XIDUSN,
XIDSLOT,
XIDSQN,
USER_NAME,
OBJECT_ID,
OBJECT_NAME,
CLIENT_OS_NAME,
CLIENT_PROGREM,
LMODE
)
AS
SELECT * FROM SYS.DV_LOCKED_OBJECTS
/
CREATE OR REPLACE VIEW SYS.V$LOGFILE
(
INSTANCE,
ID,
STATUS,
TYPE,
FILE_NAME,
BYTES,
WRITE_POS,
FREE_SIZE,
RESET_ID,
ASN,
BLOCK_SIZE,
CURRENT_POINT,
ARCH_POS
)
AS
SELECT * FROM SYS.DV_LOG_FILES
/
CREATE OR REPLACE VIEW SYS.V$MANAGED_STANDBY
(
PROCESS,
STATUS,
RESETLOG_ID,
THREAD#,
SEQUENCE#,
FLUSH_POINT,
PRIMARY_CURR_POINT,
REPLAY_POINT
)
AS
SELECT * FROM SYS.DV_STANDBYS
/
CREATE OR REPLACE VIEW SYS.V$ME
(
SID,
USER_NAME,
USER_ID,
CURR_SCHEMA,
SPID,
OS_PROG,
OS_HOST,
OS_USER,
CLIENT_IP,
CLIENT_PORT
)
AS
SELECT * FROM SYS.DV_ME
/
CREATE OR REPLACE VIEW SYS.V$OPEN_CURSOR
(
SESSION_ID,
STMT_ID,
USER_NAME,
SQL_TEXT,
SQL_TYPE,
SQL_ID,
STATUS,
CURSOR_TYPE,
VM_OPEN_PAGES,
VM_CLOSE_PAGES,
VM_MAX_OPEN_PAGES,
VM_ALLOC_PAGES,
VM_FREE_PAGES,
VM_SWAPIN_PAGES,
VM_SWAPOUT_PAGES,
QUERY_SCN,
LAST_SQL_ACTIVE_TIME,
ELAPSED_TIME,
DISK_READS,
IO_WAIT_TIME,
BUFFER_GETS,
CR_GETS,
CON_WAIT_TIME,
CPU_TIME
)
AS
SELECT * FROM SYS.DV_OPEN_CURSORS
/
CREATE OR REPLACE VIEW SYS.V$PARAMETER
(
NAME,
VALUE,
RUNTIME_VALUE,
DEFAULT_VALUE,
ISDEFAULT,
MODIFIABLE,
DESCRIPTION,
RANGE,
DATATYPE,
EFFECTIVE
)
AS
SELECT * FROM SYS.DV_PARAMETERS
/
CREATE OR REPLACE VIEW SYS.V$FREE_SPACE
(
TABLESPACE_NAME,
FILE_ID,
BLOCK_ID,
BYTES,
BLOCKS,
RELATIVE_FNO
)
AS
SELECT * FROM SYS.DV_FREE_SPACE
/
CREATE OR REPLACE VIEW SYS.V$HA_SYNC_INFO
(
THREAD#,
STATUS,
LOCAL_HOST,
ROLE_VALID,
NET_MODE,
PEER_HOST,
PEER_PORT,
LOCAL_SEND_POINT,
PEER_FLUSH_POINT,
PEER_CONTFLUSH_POINT,
PEER_BUILDING,
LOCAL_LFN,
LOCAL_LSN,
PEER_LFN,
PEER_LSN,
FLUSH_LAG,
REPLAY_LAG,
BUILD_TYPE,
BUILD_PROGRESS,
BUILD_STAGE,
BUILD_SYNCED_STAGE_SIZE,
BUILD_TOTAL_STAGE_SIZE,
BUILD_TIME
)
AS
SELECT * FROM SYS.DV_HA_SYNC_INFO
/
CREATE OR REPLACE VIEW SYS.V$HBA
(
TYPE,
USER_NAME,
ADDRESS
)
AS
SELECT * FROM SYS.DV_HBA
/
CREATE OR REPLACE VIEW SYS.V$INSTANCE
(
INSTANCE_ID,
INSTANCE_NAME,
STATUS,
KERNEL_SCN,
SHUTDOWN_PHASE,
STARTUP_TIME,
HOST_NAME,
PLATFORM_NAME,
CONNECT_STATUS
)
AS
SELECT * FROM SYS.DV_INSTANCE
/
CREATE OR REPLACE VIEW SYS.V$JOBS_RUNNING
(
JOBNO,
SESSION_ID,
SERIAL_ID
)
AS
SELECT * FROM SYS.DV_RUNNING_JOBS
/
CREATE OR REPLACE VIEW SYS.V$LATCH
(
ID,
NAME,
GETS,
MISSES,
SPIN_GETS,
WAIT_TIME
)
AS
SELECT * FROM SYS.DV_LATCHS
/
CREATE OR REPLACE VIEW SYS.V$LOCK
(
SID,
TYPE,
ID1,
ID2,
LMODE,
BLOCK,
RMID
)
AS
SELECT * FROM SYS.DV_LOCKS
/
CREATE OR REPLACE VIEW V$BUF_INFO
(
IDX,
FILE_ID,
PAGE_ID,
OWNER_ID,
OWNER_LOCK,
CONVERTING_INST,
CONVERTING_CUR_LOCK,
CONVERTING_REQ_LOCK,
CONVERTQ_LEN,
EDP_MAP,
CONVERTING_REQ_SID,
CONVERTING_REQ_RSN,
PART_ID,
READONLY_COPIES,
LAST_EDP,
IN_RECOVERY,
REFORM_PROMOTE,
LSN,
RECOVERY_SKIP,
RECYCLING,
MASTER_ID,
)
AS
SELECT * FROM SYS.DV_DRC_BUF_INFO
/
CREATE OR REPLACE VIEW V$LOCAL_LOCK_INFO
(
IDX,
DRID_TYPE,
DRID_UID,
DRID_ID,
DRID_IDX,
DRID_PART,
DRID_SUBPART,
IS_OWNER,
IS_LOCKED,
COUNT,
LATCH_SHARE_COUNT,
LATCH_STAT,
LATCH_SID,
IS_RELEASING,
LOCK_MODE
)
AS
SELECT * FROM SYS.DV_DRC_LOCAL_LOCK_INFO
/
CREATE OR REPLACE VIEW V$RES_RATIO
(
DRC_RESOURCE,
USED,
TOTAL,
RATIO
)
AS
SELECT * FROM SYS.DV_DRC_RES_RATIO
/
CREATE OR REPLACE VIEW V$GLOBAL_RES
(
DRC_RESOURCE,
BUCKET_NUM,
POOL_RECYCLE_POS,
POOL_FREE_LIST,
BUCKETS_COUNT,
BUCKETS_FIRST
)
AS
SELECT * FROM SYS.DV_DRC_GLOBAL_RES
/
CREATE OR REPLACE VIEW V$RES_MAP
(
DRC_RESOURCE,
BUCKET_NUM,
POOL_RECYCLE_POS,
POOL_FREE_LIST,
BUCKETS_COUNT,
BUCKETS_FIRST
)
AS
SELECT * FROM SYS.DV_DRC_RES_MAP
/
CREATE OR REPLACE VIEW V$BUF_CTRL_INFO
(
POOL_ID,
FILE_ID,
PAGE_ID,
LATCH_SHARE_COUNT,
LATCH_SID,
LATCH_STAT,
LATCH_XSID,
LOAD_STATUS,
IS_READONLY,
IS_DIRTY,
IS_REMOTE_DIRTY,
IS_MARKED,
IN_OLD,
IN_CKPT,
LOCK_MODE,
IS_EDP,
REF_NUM,
LASTEST_LFN,
EDP_SCN,
EDP_MAP,
NEED_FLUSH,
BEEN_LOADED,
IN_RECOVERY,
LAST_CKPT_TIME,
IS_RESIDENT,
IS_PINNED
)
AS
SELECT * FROM SYS.DV_BUF_CTRL_INFO
/
CREATE OR REPLACE VIEW SYS.V$BUFFER_POOL_STATISTICS
(
ID,
NAME,
SET_MSIZE,
CNUM_REPL,
CNUM_WRITE,
CNUM_FREE,
CNUM_PINNED,
CNUM_RO,
OLD_LEN,
STATS_LEN,
RECYCLED,
WRITE_LEN,
RECYCLE_GROUP,
COLD_DIRTY_GROUP,
TOTAL_GROUP,
LOCAL_MASTER,
REMOTE_MASTER
)
AS
SELECT * FROM SYS.DV_BUFFER_POOL_STATS
/
CREATE OR REPLACE VIEW SYS.V$CONTROLFILE
(
STATUS,
NAME,
IS_RECOVERY_DEST_FILE,
BLOCK_SIZE,
FILE_SIZE_BLKS
)
AS
SELECT * FROM SYS.DV_CONTROL_FILES
/
CREATE OR REPLACE VIEW SYS.V$DATABASE
(
DBID,
NAME,
STATUS,
OPEN_STATUS,
OPEN_COUNT,
INIT_TIME,
CURRENT_SCN,
RCY_POINT,
LRP_POINT,
CKPT_ID,
LSN,
LFN,
LOG_COUNT,
LOG_FIRST,
LOG_LAST,
LOG_FREE_SIZE,
LOG_MODE,
SPACE_COUNT,
DEVICE_COUNT,
DW_START,
DW_END,
PROTECTION_MODE,
DATABASE_ROLE,
DATABASE_CONDITION,
SWITCHOVER_STATUS,
FAILOVER_STATUS,
ARCHIVELOG_CHANGE,
LREP_POINT,
LREP_MODE,
OPEN_INCONSISTENCY,
CHARACTER_SET,
COMMIT_SCN,
NEED_REPAIR_REASON,
READONLY_REASON,
BIN_SYS_VERSION,
DATA_SYS_VERSION,
RESETLOG,
MIN_SCN,
ARCHIVELOG_SIZE,
DDL_EXEC_STATUS
)
AS
SELECT * FROM SYS.DV_DATABASE
/
CREATE OR REPLACE VIEW SYS.V$DATAFILE
(
ID,
TABLESPACE_ID,
STATUS,
TYPE,
FILE_NAME,
BYTES,
AUTO_EXTEND,
AUTO_EXTEND_SIZE,
MAX_SIZE,
HIGH_WATER_MARK,
ALLOC_SIZE,
COMPRESSION,
PUNCHED
)
AS
SELECT * FROM SYS.DV_DATA_FILES
/
CREATE OR REPLACE VIEW SYS.V$DB_OBJECT_CACHE
(
OWNER,
NAME,
NAMESPACE,
TYPE,
LOADS,
LOCKS,
HASH_VALUE,
LOCK_MODE,
STATUS
)
AS
SELECT * FROM SYS.DV_OBJECT_CACHE
/
CREATE OR REPLACE VIEW SYS.V$DC_POOL
(
POOL_OPT_COUNT,
POOL_PAGE_COUNT,
POOL_FREE_PAGE_COUNT,
LRU_COUNT,
LRU_PAGE_COUNT,
LRU_LOCKED_COUNT,
LRU_LOCKED_PAGE_COUNT,
LRU_RECYCLABLE_COUNT,
LRU_RECYCLABLE_PAGE_COUNT
)
AS
SELECT * FROM SYS.DV_DC_POOLS
/
CREATE OR REPLACE VIEW SYS.V$DYNAMIC_VIEW
(
USER_NAME,
NAME,
ID,
COLUMN_COUNT
)
AS
SELECT * FROM SYS.DV_DYNAMIC_VIEWS
/
CREATE OR REPLACE VIEW SYS.V$DYNAMIC_VIEW_COLUMN
(
USER_NAME,
VIEW_NAME,
COLUMN_ID,
COLUMN_NAME,
DATA_TYPE,
DATA_LENGTH,
DATA_PRECISION,
DATA_SCALE
)
AS
SELECT * FROM SYS.DV_DYNAMIC_VIEW_COLS
/
CREATE OR REPLACE VIEW SYS.V$ALL_TRANSACTION
(
SEG_ID,
SLOT,
XNUM,
SCN,
SID,
STATUS,
UNDO_COUNT,
UNDO_FIRST,
UNDO_LAST,
TXN_PAGEID,
RMID,
REMAINED
)
AS
SELECT * FROM SYS.DV_ALL_TRANS
/
CREATE OR REPLACE VIEW SYS.V$GLOBAL_TRANSACTIONS
(
GLOBAL_TRAN_ID,
FORMAT_ID,
BRANCH_ID,
LOCAL_TRAN_ID,
STATUS,
SID,
RMID
)
AS
SELECT * FROM SYS.DV_GLOBAL_TRANSACTIONS
/
CREATE OR REPLACE VIEW SYS.V$ARCHIVED_LOG
(
RECID,
STAMP,
NAME,
DEST_ID,
THREAD#,
SEQUENCE#,
RESETLOGS_CHANGE#,
RESETLOGS_TIME,
RESETLOGS_ID,
FIRST_CHANGE#,
FIRST_TIME,
NEXT_CHANGE#,
NEXT_TIME,
BLOCKS,
BLOCK_SIZE,
CREATOR,
REGISTRAR,
STANDBY_DEST,
ARCHIVED,
APPLIED,
DELETED,
STATUS,
COMPLETION_TIME,
DICTIONARY_BEGIN,
DICTIONARY_END,
END_OF_REDO,
BACKUP_COUNT,
ARCHIVAL_THREAD#,
ACTIVATION#,
IS_RECOVERY_DEST_FILE,
COMPRESSED,
FAL,
END_OF_REDO_TYPE,
BACKED_BY_VSS,
CON_ID,
REAL_SIZE,
FIRST_LSN,
LAST_LSN
)
AS
SELECT * FROM SYS.DV_ARCHIVED_LOGS
/
CREATE OR REPLACE VIEW SYS.V$ARCHIVE_DEST_STATUS
(
DEST_ID,
DEST_NAME,
STATUS,
TYPE,
DATABASE_MODE,
PROTECTION_MODE,
DESTINATION,
DB_UNIQUE_NAME,
SYNCHRONIZATION_STATUS,
SYNCHRONIZED
)
AS
SELECT * FROM SYS.DV_ARCHIVE_DEST_STATUS
/
CREATE OR REPLACE VIEW SYS.V$ARCHIVE_GAP
(
THREAD#,
LOW_SEQUENCE#,
HIGH_SEQUENCE#
)
AS
SELECT * FROM SYS.DV_ARCHIVE_GAPS
/
CREATE OR REPLACE VIEW SYS.V$ARCHIVE_PROCESSES
(
PROCESS,
STATUS,
LOG_SEQUENCE,
STATE,
ROLES,
CON_ID
)
AS
SELECT * FROM SYS.DV_ARCHIVE_THREADS
/
CREATE OR REPLACE VIEW SYS.V$BACKUP_PROCESS
(
TYPE,
PROGRESS,
STAGE,
STATUS,
ERR_NO,
ERR_MSG,
TOTAL_PROC,
FREE_PROC
)
AS
SELECT * FROM SYS.DV_BACKUP_PROCESSES
/
CREATE OR REPLACE VIEW SYS.V$BUFFER_POOL
(
ID,
NAME,
PAGE_SIZE,
CURRENT_SIZE,
BUFFERS,
FREE
)
AS
SELECT * FROM SYS.DV_BUFFER_POOLS
/
CREATE OR REPLACE VIEW SYS.V$USER_PARAMETER
(
NAME,
VALUE,
RUNTIME_VALUE,
DEFAULT_VALUE,
ISDEFAULT,
MODIFIABLE,
DESCRIPTION,
RANGE,
DATATYPE,
EFFECTIVE
)
AS
SELECT * FROM SYS.DV_USER_PARAMETERS
/
CREATE OR REPLACE VIEW SYS.V$SESSION_EVENT
(
SID,
EVENT#,
EVENT,
P1,
WAIT_CLASS,
TOTAL_WAITS,
TIME_WAITED,
TIME_WAITED_MIRCO,
AVERAGE_WAIT,
AVERAGE_WAIT_MIRCO,
TENANT_ID
)
AS
SELECT * FROM SYS.DV_SESSION_EVENTS S WHERE DBE_DIAGNOSE.TENANT_CHECK(0, S.TENANT_ID)
/
CREATE OR REPLACE VIEW SYS.V$SESSION_WAIT
(
SID,
EVENT#,
EVENT,
P1,
WAIT_CLASS,
STATE,
WAIT_BEGIN_TIME,
WAIT_TIME_MIRCO,
SECONDS_IN_WAIT,
TENANT_ID
)
AS
SELECT * FROM SYS.DV_SESSION_WAITS S WHERE DBE_DIAGNOSE.TENANT_CHECK(0, S.TENANT_ID)
/
CREATE OR REPLACE VIEW SYS.V$SGA
(
NAME,
VALUE
)
AS
SELECT * FROM SYS.DV_GMA
/
CREATE OR REPLACE VIEW SYS.V$SPINLOCK
(
SID,
TYPE,
SPINS,
SLEEPS,
FAILS
)
AS
SELECT * FROM SYS.DV_SPINLOCKS
/
CREATE OR REPLACE VIEW SYS.V$DLSLOCK
(
IDX,
DRID_TYPE,
DRID_UID,
DRID_ID,
DRID_IDX,
DRID_PART,
DRID_SUBPART,
MODE,
PART_ID,
GRANTED_MAP,
CONVERTQ_LEN,
CONVERTING
)
AS
SELECT * FROM SYS.DV_DLSLOCKS
/
CREATE OR REPLACE VIEW SYS.V$SQLAREA
(
SQL_TEXT,
SQL_ID,
EXECUTIONS,
DISK_READS,
BUFFER_GETS,
CR_GETS,
SORTS,
PARSE_TIME,
PARSE_CALLS,
PROCESSED_ROWS,
PARSING_USER_ID,
PARSING_USER_NAME,
MODULE,
IO_WAIT_TIME,
CON_WAIT_TIME,
CPU_TIME,
ELAPSED_TIME,
LAST_LOAD_TIME,
PROGRAM_ID,
PROGRAM_LINE#,
LAST_ACTIVE_TIME,
REF_COUNT,
IS_FREE,
CLEANED,
PAGES,
VALID,
SHARABLE_MEM,
VM_OPEN_PAGES,
VM_CLOSE_PAGES,
VM_SWAPIN_PAGES,
VM_FREE_PAGES,
NETWORK_TIME,
PDOWN_SQL_ID,
VM_ALLOC_PAGES,
VM_MAX_OPEN_PAGES,
VM_SWAPOUT_PAGES,
DCS_BUFFER_GETS,
DCS_CR_GETS,
DCS_NET_TIME
)
AS
SELECT * FROM SYS.DV_SQLS
/
CREATE OR REPLACE VIEW SYS.V$USER_ADVISORY_LOCKS
(
SID,
LOCK_NAME,
LOCK_TIMES,
LOCK_SCN
)
AS
SELECT * FROM SYS.DV_USER_ADVISORY_LOCKS
/
CREATE OR REPLACE VIEW SYS.V$WAITSTAT
(
CLASS,
COUNT,
TIME
)
AS
SELECT * FROM SYS.DV_WAIT_STATS
/
CREATE OR REPLACE VIEW SYS.V$VM_FUNC_STACK
(
FUNC_STACK,
REF_COUNT
)
AS
SELECT * FROM SYS.DV_VM_FUNC_STACK
/
CREATE OR REPLACE VIEW SYS.V$XACT_LOCK
(
SID,
SERIAL#,
LOCK_NAME,
LOCK_TIMES
)
AS SELECT * FROM SYS.DV_XACT_LOCKS
/
CREATE OR REPLACE PUBLIC SYNONYM V$PL_REFSQLS FOR SYS.V$PL_REFSQLS
/
CREATE OR REPLACE PUBLIC SYNONYM V$REPL_STATUS FOR SYS.V$REPL_STATUS
/
CREATE OR REPLACE PUBLIC SYNONYM V$SGASTAT FOR SYS.V$SGASTAT
/
CREATE OR REPLACE PUBLIC SYNONYM V$SQLPOOL FOR SYS.V$SQLPOOL
/
CREATE OR REPLACE PUBLIC SYNONYM V$LIBRARYCACHE FOR SYS.V$LIBRARYCACHE
/
CREATE OR REPLACE PUBLIC SYNONYM V$UNDO_SEGMENT FOR SYS.V$UNDO_SEGMENT
/
CREATE OR REPLACE PUBLIC SYNONYM V$TEMP_UNDO_SEGMENT FOR SYS.V$TEMP_UNDO_SEGMENT
/
CREATE OR REPLACE PUBLIC SYNONYM V$OPEN_CURSOR FOR SYS.V$OPEN_CURSOR
/
CREATE OR REPLACE PUBLIC SYNONYM V$DC_POOL FOR SYS.V$DC_POOL
/
CREATE OR REPLACE PUBLIC SYNONYM V$PL_MANAGER FOR SYS.V$PL_MANAGER
/
CREATE OR REPLACE PUBLIC SYNONYM V$FREE_SPACE FOR SYS.V$FREE_SPACE
/
CREATE OR REPLACE PUBLIC SYNONYM V$HA_SYNC_INFO FOR SYS.V$HA_SYNC_INFO
/
CREATE OR REPLACE PUBLIC SYNONYM V$HBA FOR SYS.V$HBA
/
CREATE OR REPLACE PUBLIC SYNONYM V$INSTANCE FOR SYS.V$INSTANCE
/
CREATE OR REPLACE PUBLIC SYNONYM V$JOBS_RUNNING FOR SYS.V$JOBS_RUNNING
/
CREATE OR REPLACE PUBLIC SYNONYM V$LOGFILE FOR SYS.V$LOGFILE
/
CREATE OR REPLACE PUBLIC SYNONYM V$SESSION FOR SYS.V$SESSION
/
CREATE OR REPLACE PUBLIC SYNONYM V$BUFFER_POOL FOR SYS.V$BUFFER_POOL
/
CREATE OR REPLACE PUBLIC SYNONYM V$PARAMETER FOR SYS.V$PARAMETER
/
CREATE OR REPLACE PUBLIC SYNONYM V$USER_PARAMETER FOR SYS.V$USER_PARAMETER
/
GRANT SELECT ON V$USER_PARAMETER TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM V$TEMP_POOL FOR SYS.V$TEMP_POOL
/
CREATE OR REPLACE PUBLIC SYNONYM V$DB_OBJECT_CACHE FOR SYS.V$DB_OBJECT_CACHE
/
CREATE OR REPLACE PUBLIC SYNONYM V$LOCK FOR SYS.V$LOCK
/
CREATE OR REPLACE PUBLIC SYNONYM V$ARCHIVED_LOG FOR SYS.V$ARCHIVED_LOG
/
CREATE OR REPLACE PUBLIC SYNONYM V$ARCHIVE_GAP FOR SYS.V$ARCHIVE_GAP
/
CREATE OR REPLACE PUBLIC SYNONYM V$ARCHIVE_PROCESSES FOR SYS.V$ARCHIVE_PROCESSES
/
CREATE OR REPLACE PUBLIC SYNONYM V$ARCHIVE_DEST_STATUS FOR SYS.V$ARCHIVE_DEST_STATUS
/
CREATE OR REPLACE PUBLIC SYNONYM V$DATABASE FOR SYS.V$DATABASE
/
CREATE OR REPLACE PUBLIC SYNONYM V$SGA FOR SYS.V$SGA
/
CREATE OR REPLACE PUBLIC SYNONYM V$LOCKED_OBJECT FOR SYS.V$LOCKED_OBJECT
/
CREATE OR REPLACE PUBLIC SYNONYM V$MANAGED_STANDBY FOR SYS.V$MANAGED_STANDBY
/
CREATE OR REPLACE PUBLIC SYNONYM V$TABLESPACE FOR SYS.V$TABLESPACE
/
CREATE OR REPLACE PUBLIC SYNONYM V$SPINLOCK FOR SYS.V$SPINLOCK
/
CREATE OR REPLACE PUBLIC SYNONYM V$SQLAREA FOR SYS.V$SQLAREA
/
CREATE OR REPLACE PUBLIC SYNONYM V$SESSION_WAIT FOR SYS.V$SESSION_WAIT
/
CREATE OR REPLACE PUBLIC SYNONYM V$SESSION_EVENT FOR SYS.V$SESSION_EVENT
/
CREATE OR REPLACE PUBLIC SYNONYM V$SYSTEM_EVENT FOR SYS.V$SYSTEM_EVENT
/
CREATE OR REPLACE PUBLIC SYNONYM V$ME FOR SYS.V$ME
/
GRANT SELECT ON V$ME TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM V$DATAFILE FOR SYS.V$DATAFILE
/
CREATE OR REPLACE PUBLIC SYNONYM V$SYSSTAT FOR SYS.V$SYSSTAT
/
CREATE OR REPLACE PUBLIC SYNONYM V$SYSTEM FOR SYS.V$SYSTEM
/
CREATE OR REPLACE PUBLIC SYNONYM V$VERSION FOR SYS.V$VERSION
/
CREATE OR REPLACE PUBLIC SYNONYM V$TRANSACTION FOR SYS.V$TRANSACTION
/
CREATE OR REPLACE PUBLIC SYNONYM V$ALL_TRANSACTION FOR SYS.V$ALL_TRANSACTION
/
CREATE OR REPLACE PUBLIC SYNONYM V$GLOBAL_TRANSACTIONS FOR SYS.V$GLOBAL_TRANSACTIONS
/
CREATE OR REPLACE PUBLIC SYNONYM V$RESOURCE_MAP FOR SYS.V$RESOURCE_MAP
/
CREATE OR REPLACE PUBLIC SYNONYM V$USER_ASTATUS_MAP FOR SYS.V$USER_ASTATUS_MAP
/
CREATE OR REPLACE PUBLIC SYNONYM V$DYNAMIC_VIEW FOR SYS.V$DYNAMIC_VIEW
/
CREATE OR REPLACE PUBLIC SYNONYM V$DYNAMIC_VIEW_COLUMN FOR SYS.V$DYNAMIC_VIEW_COLUMN
/
CREATE OR REPLACE PUBLIC SYNONYM V$BACKUP_PROCESS FOR SYS.V$BACKUP_PROCESS
/
CREATE OR REPLACE PUBLIC SYNONYM V$USER_ADVISORY_LOCKS FOR SYS.V$USER_ADVISORY_LOCKS
/
CREATE OR REPLACE PUBLIC SYNONYM V$CONTROLFILE FOR SYS.V$CONTROLFILE
/
CREATE OR REPLACE PUBLIC SYNONYM V$REACTOR_POOL FOR SYS.V$REACTOR_POOL
/
CREATE OR REPLACE PUBLIC SYNONYM V$SEGMENT_STATISTICS FOR SYS.V$SEGMENT_STATISTICS
/
CREATE OR REPLACE PUBLIC SYNONYM V$WAITSTAT FOR SYS.V$WAITSTAT
/
CREATE OR REPLACE PUBLIC SYNONYM V$LATCH FOR SYS.V$LATCH
/
CREATE OR REPLACE PUBLIC SYNONYM V$VM_FUNC_STACK FOR SYS.V$VM_FUNC_STACK
/
CREATE OR REPLACE PUBLIC SYNONYM V$BUFFER_POOL_STATISTICS FOR SYS.V$BUFFER_POOL_STATISTICS
/
CREATE OR REPLACE PUBLIC SYNONYM V$XACT_LOCK FOR SYS.V$XACT_LOCK
/
CREATE OR REPLACE PUBLIC SYNONYM V$SESSION_EX FOR SYS.V$SESSION_EX
/
CREATE OR REPLACE PUBLIC SYNONYM DUAL FOR SYS.SYS_DUMMY
/
CREATE OR REPLACE SYNONYM DUAL FOR SYS.SYS_DUMMY
/
CREATE OR REPLACE PUBLIC SYNONYM V$RES_RATIO FOR SYS.V$RES_RATIO
/
CREATE OR REPLACE PUBLIC SYNONYM V$GLOBAL_RES FOR SYS.V$GLOBAL_RES
/
CREATE OR REPLACE PUBLIC SYNONYM V$RES_MAP FOR SYS.V$RES_MAP
/
CREATE OR REPLACE PUBLIC SYNONYM V$BUF_INFO FOR SYS.V$BUF_INFO
/
GRANT SELECT ON DUAL TO PUBLIC
/
CREATE OR REPLACE VIEW DBA_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', 'UNKNOWN') AS DEVICE_TYPE,
DIR, START_TIME, BASE_TAG FROM SYS.SYS_BACKUP_SETS
/
CREATE OR REPLACE VIEW USER_SEGMENTS
(
SEGMENT_NAME,
PARTITION_NAME,
SEGMENT_TYPE,
TABLESPACE_NAME,
BYTES,
PAGES,
EXTENTS
)
AS
SELECT T.NAME, '', 'TABLE', TS.NAME,
DBE_DIAGNOSE.DBA_SEGSIZE(0, T.ENTRY), DBE_DIAGNOSE.DBA_SEGSIZE(1, T.ENTRY), DBE_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,
DBE_DIAGNOSE.DBA_SEGSIZE(0, I.ENTRY), DBE_DIAGNOSE.DBA_SEGSIZE(1, I.ENTRY), DBE_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,
DBE_DIAGNOSE.DBA_SEGSIZE(0, L.ENTRY), DBE_DIAGNOSE.DBA_SEGSIZE(1, L.ENTRY), DBE_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,
DBE_DIAGNOSE.DBA_SEGSIZE(0, TP.ENTRY), DBE_DIAGNOSE.DBA_SEGSIZE(1, TP.ENTRY), DBE_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,
DBE_DIAGNOSE.DBA_SEGSIZE(0, IP.ENTRY), DBE_DIAGNOSE.DBA_SEGSIZE(1, IP.ENTRY), DBE_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, DBE_DIAGNOSE.DBA_SEGSIZE(0, LP.ENTRY), DBE_DIAGNOSE.DBA_SEGSIZE(1, LP.ENTRY), DBE_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 DBA_SEGMENTS
(
OWNER,
SEGMENT_NAME,
PARTITION_NAME,
SEGMENT_TYPE,
TABLESPACE_NAME,
BYTES,
PAGES,
EXTENTS
)
AS
SELECT U.NAME, T.NAME, '', 'TABLE', TS.NAME,
DBE_DIAGNOSE.DBA_SEGSIZE(0, T.ENTRY), DBE_DIAGNOSE.DBA_SEGSIZE(1, T.ENTRY), DBE_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 AND DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_ID)
UNION ALL
SELECT U.NAME, I.NAME, '', 'INDEX', TS.NAME,
DBE_DIAGNOSE.DBA_SEGSIZE(0, I.ENTRY), DBE_DIAGNOSE.DBA_SEGSIZE(1, I.ENTRY), DBE_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 AND DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_ID)
UNION ALL
SELECT U.NAME, 'LOB_' || T.NAME || '_' || C.NAME, '', 'LOB', TS.NAME,
DBE_DIAGNOSE.DBA_SEGSIZE(0, L.ENTRY), DBE_DIAGNOSE.DBA_SEGSIZE(1, L.ENTRY), DBE_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 AND DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_ID)
UNION ALL
SELECT U.NAME, T.NAME, TP.NAME, 'TABLE', TS.NAME,
DBE_DIAGNOSE.DBA_SEGSIZE(0, TP.ENTRY), DBE_DIAGNOSE.DBA_SEGSIZE(1, TP.ENTRY), DBE_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 AND DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_ID)
UNION ALL
SELECT U.NAME, I.NAME, IP.NAME, 'INDEX', TS.NAME,
DBE_DIAGNOSE.DBA_SEGSIZE(0, IP.ENTRY), DBE_DIAGNOSE.DBA_SEGSIZE(1, IP.ENTRY), DBE_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 AND DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_ID)
UNION ALL
SELECT U.NAME, 'LOB_' || T.NAME || '_' || C.NAME, 'LOB_' || T.NAME || '_' || C.NAME || '_' || LP.PART#,
'LOB', TS.NAME, DBE_DIAGNOSE.DBA_SEGSIZE(0, LP.ENTRY), DBE_DIAGNOSE.DBA_SEGSIZE(1, LP.ENTRY), DBE_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 AND DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_ID)
/
CREATE OR REPLACE VIEW DBA_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,
ROW_FORMAT,
NOLOGGING_INSERT
)
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,DBE_DIAGNOSE.DBA_SEGSIZE(0, T.ENTRY),1,DBE_DIAGNOSE.DBA_PARTITIONED_TABSIZE(0,U.NAME,'"'||T.NAME||'"')),
DECODE(T.PARTITIONED,0,DBE_DIAGNOSE.DBA_SEGSIZE(1, T.ENTRY),1,DBE_DIAGNOSE.DBA_PARTITIONED_TABSIZE(1,U.NAME,'"'||T.NAME||'"')),
DECODE(T.PARTITIONED,0,DBE_DIAGNOSE.DBA_SEGSIZE(2, T.ENTRY),1,DBE_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),
DECODE(T.FLAG & 1, 1, 'CSF', 'ASF'),
DECODE(T.FLAG & 4, 4, 'YES', 'NO')
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 DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_ID)
/
CREATE OR REPLACE VIEW ALL_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,
ROW_FORMAT,
NOLOGGING_INSERT
)
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,DBE_DIAGNOSE.DBA_SEGSIZE(0, T.ENTRY),1,DBE_DIAGNOSE.DBA_PARTITIONED_TABSIZE(0,U.NAME,'"'||T.NAME||'"')),
DECODE(T.PARTITIONED,0,DBE_DIAGNOSE.DBA_SEGSIZE(1, T.ENTRY),1,DBE_DIAGNOSE.DBA_PARTITIONED_TABSIZE(1,U.NAME,'"'||T.NAME||'"')),
DECODE(T.PARTITIONED,0,DBE_DIAGNOSE.DBA_SEGSIZE(2, T.ENTRY),1,DBE_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),
DECODE(T.FLAG & 1, 1, 'CSF', 'ASF'),
DECODE(T.FLAG & 4, 4, 'YES', 'NO')
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
(user = 'SYS' or
U.NAME = user or
DBE_DIAGNOSE.has_obj_privs(user, U.NAME, '"'||T.NAME||'"', 'TABLE')) AND DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_ID)
/
CREATE OR REPLACE VIEW ALL_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
(user = 'SYS' or
U.NAME = user or
DBE_DIAGNOSE.has_obj_privs(user, U.NAME, '"'||T.NAME||'"', 'TABLE')) AND DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_ID)
/
CREATE OR REPLACE VIEW DBA_REDACTION_POLICIES
(
USER_NAME,
OBJECT_NAME,
COL_NAME,
RULE_NAME,
TYPE_NAME,
PARAM
)
AS
SELECT U.NAME, T.NAME, C.NAME, D.RULE_NAME, D.TYPE_NAME, D.PARAM
FROM SYS.SYS_DDM D, SYS.SYS_USERS U, SYS.SYS_TABLES T, SYS.SYS_COLUMNS C
WHERE U.ID = D.USER# AND T.ID = D.TABLE# AND C.TABLE# = D.TABLE# AND C.USER# = D.USER# AND C.ID = D.COLUMN# AND T.USER# = U.ID AND DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_ID)
/
CREATE OR REPLACE VIEW USER_REDACTION_POLICIES
(
USER_NAME,
OBJECT_NAME,
COL_NAME,
RULE_NAME,
TYPE_NAME,
PARAM
)
AS
SELECT U.NAME, T.NAME, C.NAME, D.RULE_NAME, D.TYPE_NAME, D.PARAM
FROM SYS.SYS_DDM D, SYS.SYS_USERS U, SYS.SYS_TABLES T, SYS.SYS_COLUMNS C, SYS.DV_ME ME
WHERE U.ID = D.USER# AND T.ID = D.TABLE# AND C.TABLE# = D.TABLE# AND C.USER# = D.USER# AND C.ID = D.COLUMN# AND T.USER# = U.ID AND D.USER# = ME.USER_ID
/
CREATE OR REPLACE VIEW DBA_POLICIES
(
OBJECT_OWNER,
OBJECT_NAME,
POLICY_NAME,
PF_OWNER,
FUNCTION,
SEL,
INS,
UPD,
DEL,
CHK_OPTION,
ENABLE,
LONG_PREDICATE
)
AS
SELECT U.NAME, P.OBJ_NAME, P.PNAME, P.PF_SCHEMA, P.PF_NAME,
DECODE(bitand(P.STMT_TYPE, 1), 0, 'NO', 1, 'YES'),
DECODE(bitand(P.STMT_TYPE, 2), 0, 'NO', 2, 'YES'),
DECODE(bitand(P.STMT_TYPE, 4), 0, 'NO', 4, 'YES'),
DECODE(bitand(P.STMT_TYPE, 8), 0, 'NO', 8, 'YES'),
DECODE(P.CHK_OPTION, 0, 'NO', 1, 'YES'),
DECODE(P.ENABLE, 0, 'NO', 1, 'YES'),
DECODE(P.LONG_PREDICATE, 0, 'NO', 1, 'YES')
FROM SYS.SYS_POLICIES P, SYS.SYS_USERS U
WHERE U.ID = P.OBJ_SCHEMA_ID AND DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_ID)
/
CREATE OR REPLACE VIEW USER_POLICIES
(
OBJECT_OWNER,
OBJECT_NAME,
POLICY_NAME,
PF_OWNER,
FUNCTION,
SEL,
INS,
UPD,
DEL,
CHK_OPTION,
ENABLE,
LONG_PREDICATE
)
AS
SELECT U.NAME, P.OBJ_NAME, P.PNAME, P.PF_SCHEMA, P.PF_NAME,
DECODE(bitand(P.STMT_TYPE, 1), 0, 'NO', 1, 'YES'),
DECODE(bitand(P.STMT_TYPE, 2), 0, 'NO', 2, 'YES'),
DECODE(bitand(P.STMT_TYPE, 4), 0, 'NO', 4, 'YES'),
DECODE(bitand(P.STMT_TYPE, 8), 0, 'NO', 8, 'YES'),
DECODE(P.CHK_OPTION, 0, 'NO', 1, 'YES'),
DECODE(P.ENABLE, 0, 'NO', 1, 'YES'),
DECODE(P.LONG_PREDICATE, 0, 'NO', 1, 'YES')
FROM SYS.SYS_POLICIES P, SYS.SYS_USERS U, SYS.DV_ME ME
WHERE U.ID = P.OBJ_SCHEMA_ID AND P.OBJ_SCHEMA_ID = ME.USER_ID
/
CREATE OR REPLACE VIEW DBA_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 DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_ID)
/
CREATE OR REPLACE VIEW USER_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,
ROW_FORMAT,
NOLOGGING_INSERT
)
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,DBE_DIAGNOSE.DBA_SEGSIZE(0, T.ENTRY),1,DBE_DIAGNOSE.DBA_PARTITIONED_TABSIZE(0,ME.USER_NAME,'"'||T.NAME||'"')),
DECODE(T.PARTITIONED,0,DBE_DIAGNOSE.DBA_SEGSIZE(1, T.ENTRY),1,DBE_DIAGNOSE.DBA_PARTITIONED_TABSIZE(1,ME.USER_NAME,'"'||T.NAME||'"')),
DECODE(T.PARTITIONED,0,DBE_DIAGNOSE.DBA_SEGSIZE(2, T.ENTRY),1,DBE_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),
DECODE(T.FLAG & 1, 1, 'CSF', 'ASF'),
DECODE(T.FLAG & 4, 4, 'YES', 'NO')
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 ALL_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) || DECODE(C.FLAGS & 1024, 1024, '[]', ''),
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
user = 'SYS' or
DBE_DIAGNOSE.has_obj_privs(user, U.NAME, '"'||T.NAME||'"', 'TABLE'))
AND DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_ID)
UNION ALL
SELECT U.NAME, T.NAME, C.NAME, TYPE_ID2NAME(C.DATATYPE) || DECODE(C.FLAGS & 1024, 1024, '[]', ''),
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
user = 'SYS' or
DBE_DIAGNOSE.has_obj_privs(user, U.NAME, '"'||T.NAME||'"', 'TABLE'))
AND DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_ID)
/
CREATE OR REPLACE VIEW DBA_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) || DECODE(C.FLAGS & 1024, 1024, '[]', ''),
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 DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_ID)
UNION ALL
SELECT U.NAME, T.NAME, C.NAME, TYPE_ID2NAME(C.DATATYPE) || DECODE(C.FLAGS & 1024, 1024, '[]', ''),
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 DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_ID)
/
CREATE OR REPLACE VIEW USER_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 ALL_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 T.RECYCLED = 0
AND (U.NAME = user or
user = 'SYS' or
DBE_DIAGNOSE.has_obj_privs(user, U.NAME, '"'||T.NAME||'"', 'TABLE'))
AND DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_ID)
/
CREATE OR REPLACE VIEW DBA_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 DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_ID)
/
CREATE OR REPLACE VIEW USER_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 ALL_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 T.RECYCLED = 0
AND (U.NAME = user or
user = 'SYS' or
DBE_DIAGNOSE.has_obj_privs(user, U.NAME, '"'||T.NAME||'"', 'TABLE'))
AND DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_ID)
/
CREATE OR REPLACE VIEW DBA_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 DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_ID)
/
CREATE OR REPLACE VIEW ALL_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 T.RECYCLED = 0
AND (U.NAME = user or
user = 'SYS' or
DBE_DIAGNOSE.has_obj_privs(user, U.NAME, '"'||T.NAME||'"', 'TABLE'))
AND DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_ID)
/
CREATE OR REPLACE VIEW DBA_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 DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_ID)
/
CREATE OR REPLACE VIEW USER_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 USER_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) || DECODE(C.FLAGS & 1024, 1024, '[]', ''),
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) || DECODE(C.FLAGS & 1024, 1024, '[]', ''),
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 USER_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) || DECODE(C.FLAGS & 1024, 1024, '[]', ''),
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, HH.SPARE3, HH.ANALYZE_TIME, 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) || DECODE(C.FLAGS & 1024, 1024, '[]', ''),
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'), HH.SPARE3, HH.ANALYZE_TIME, 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) || DECODE(C.FLAGS & 1024, 1024, '[]', ''), 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,0,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 AND TYPE_ID2NAME(C.DATATYPE) NOT IN ('CHAR','VARCHAR2','NCHAR','NVARCHAR','VARCHAR')
UNION ALL
SELECT V.NAME, C.NAME, TYPE_ID2NAME(C.DATATYPE) || DECODE(C.FLAGS & 1024, 1024, '[]', ''), 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,C.BYTES,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 AND TYPE_ID2NAME(C.DATATYPE) IN ('CHAR','VARCHAR2','NCHAR','NVARCHAR','VARCHAR')
/
CREATE OR REPLACE VIEW ALL_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) || DECODE(C.FLAGS & 1024, 1024, '[]', ''),
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, HH.SPARE3, HH.ANALYZE_TIME, C.HISTOGRAM,DECODE(C.FLAGS & 8, 0, 'N', 'Y')
FROM SYS.SYS_USERS U JOIN SYS.SYS_TABLES T ON T.USER# = U.ID AND DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_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
user = 'SYS' or
DBE_DIAGNOSE.has_obj_privs(user, U.NAME, '"'||T.NAME||'"', 'TABLE'))
UNION ALL
SELECT U.NAME, T.NAME, C.NAME, TYPE_ID2NAME(C.DATATYPE) || DECODE(C.FLAGS & 1024, 1024, '[]', ''),
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'), HH.SPARE3, HH.ANALYZE_TIME, C.HISTOGRAM,DECODE(C.FLAGS & 8, 0, 'N', 'Y')
FROM SYS.SYS_USERS U JOIN SYS.SYS_TABLES T ON T.USER# = U.ID AND DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_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
user = 'SYS' or
DBE_DIAGNOSE.has_obj_privs(user, U.NAME, '"'||T.NAME||'"', 'TABLE'))
UNION ALL
SELECT U.NAME, V.NAME, C.NAME, TYPE_ID2NAME(C.DATATYPE) || DECODE(C.FLAGS & 1024, 1024, '[]', ''), 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,0, 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 TYPE_ID2NAME(C.DATATYPE) NOT IN ('CHAR','VARCHAR2','NCHAR','NVARCHAR','VARCHAR')
and (U.NAME = user or
user = 'SYS' or
DBE_DIAGNOSE.has_obj_privs(user, U.NAME, '"'||V.NAME||'"', 'VIEW'))
AND DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_ID)
UNION ALL
SELECT U.NAME, V.NAME, C.NAME, TYPE_ID2NAME(C.DATATYPE) || DECODE(C.FLAGS & 1024, 1024, '[]', ''), 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,C.BYTES, DECODE(C.FLAGS & 32, 32, 'C', 'B'),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 TYPE_ID2NAME(C.DATATYPE) IN ('CHAR','VARCHAR2','NCHAR','NVARCHAR','VARCHAR')
and (U.NAME = user or
user = 'SYS' or
DBE_DIAGNOSE.has_obj_privs(user, U.NAME, '"'||V.NAME||'"', 'VIEW'))
AND DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_ID)
/
CREATE OR REPLACE VIEW DBA_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) || DECODE(C.FLAGS & 1024, 1024, '[]', ''),
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, HH.SPARE3, HH.ANALYZE_TIME, C.HISTOGRAM,DECODE(C.FLAGS & 8, 0, 'N', 'Y')
FROM SYS.SYS_USERS U JOIN SYS.SYS_TABLES T ON T.USER# = U.ID AND DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_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) || DECODE(C.FLAGS & 1024, 1024, '[]', ''),
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'), HH.SPARE3, HH.ANALYZE_TIME, C.HISTOGRAM,DECODE(C.FLAGS & 8, 0, 'N', 'Y')
FROM SYS.SYS_USERS U JOIN SYS.SYS_TABLES T ON T.USER# = U.ID AND DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_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) || DECODE(C.FLAGS & 1024, 1024, '[]', ''), 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,0, 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 TYPE_ID2NAME(C.DATATYPE) NOT IN ('CHAR','VARCHAR2','NCHAR','NVARCHAR','VARCHAR') AND DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_ID)
UNION ALL
SELECT U.NAME, V.NAME, C.NAME, TYPE_ID2NAME(C.DATATYPE) || DECODE(C.FLAGS & 1024, 1024, '[]', ''), 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,C.BYTES, DECODE(C.FLAGS & 32, 32, 'C', 'B'),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 TYPE_ID2NAME(C.DATATYPE) IN ('CHAR','VARCHAR2','NCHAR','NVARCHAR','VARCHAR') AND DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_ID)
/
CREATE OR REPLACE VIEW DBA_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 AND DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_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 AND DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_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 ALL_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 AND DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_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
user = 'SYS' or
DBE_DIAGNOSE.has_obj_privs(user, U.NAME, '"'||T.NAME||'"', 'TABLE')
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 AND DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_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
user = 'SYS' or
DBE_DIAGNOSE.has_obj_privs(user, U.NAME, '"'||T.NAME||'"', 'TABLE')
/
CREATE OR REPLACE VIEW ALL_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
user = 'SYS' or
DBE_DIAGNOSE.has_obj_privs(user, U.NAME, '"'||V.NAME||'"', 'VIEW'))
AND DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_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
WHERE DV.USER_NAME = user or
user = 'SYS' or
DBE_DIAGNOSE.has_obj_privs(user, 'SYS', '"'||DV.NAME||'"', 'VIEW')
/
CREATE OR REPLACE VIEW DBA_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 DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_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 USER_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 ALL_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) || DECODE(C.FLAGS & 1024, 1024, '[]', ''), 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
user = 'SYS' or
DBE_DIAGNOSE.has_obj_privs(user, U.NAME, '"'||V.NAME||'"', 'VIEW'))
AND DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_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
where DC.USER_NAME = user or
user = 'SYS' or
DBE_DIAGNOSE.has_obj_privs(user, 'SYS', '"'||DC.VIEW_NAME||'"', 'VIEW')
/
CREATE OR REPLACE VIEW DBA_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) || DECODE(C.FLAGS & 1024, 1024, '[]', ''), 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 DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_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 USER_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) || DECODE(C.FLAGS & 1024, 1024, '[]', ''), 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 ALL_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,
IS_REVERSED
)
AS
SELECT U.NAME, I.NAME, DECODE(I.TYPE, 0, 'NORMAL', 'NORMAL'),
T.NAME, DBE_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, DBE_DIAGNOSE.DBA_LISTCOLS(U.NAME, '"'||T.NAME||'"', I.COL_LIST),
DECODE(I.PARTITIONED,0,DBE_DIAGNOSE.DBA_SEGSIZE(0, I.ENTRY),1,DBE_DIAGNOSE.DBA_PARTITIONED_INDSIZE(0,U.NAME,'"'||T.NAME||'"','"'||I.NAME||'"')),
DECODE(I.PARTITIONED,0,DBE_DIAGNOSE.DBA_SEGSIZE(1, I.ENTRY),1,DBE_DIAGNOSE.DBA_PARTITIONED_INDSIZE(1,U.NAME,'"'||T.NAME||'"','"'||I.NAME||'"')),
DECODE(I.PARTITIONED,0,DBE_DIAGNOSE.DBA_SEGSIZE(2, I.ENTRY),1,DBE_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, I.CLUFAC,
T.NUM_ROWS, I.SAMPLESIZE, I.ANALYZETIME, FLAGS & 1,
DECODE(I.CR_MODE, 0, 'ROW', 1, 'PAGE', NULL),
DECODE(I.FLAGS & 64, 0, 'N', 'Y')
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
user = 'SYS' or
DBE_DIAGNOSE.has_obj_privs(user, U.NAME, '"'||T.NAME||'"', 'TABLE'))
AND DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_ID)
/
CREATE OR REPLACE VIEW DBA_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,
IS_REVERSED
)
AS
SELECT U.NAME, I.NAME, DECODE(I.TYPE, 0, 'NORMAL', 'NORMAL'),
T.NAME, DBE_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, DBE_DIAGNOSE.DBA_LISTCOLS(U.NAME, '"'||T.NAME||'"', I.COL_LIST),
DECODE(I.PARTITIONED,0,DBE_DIAGNOSE.DBA_SEGSIZE(0, I.ENTRY),1,DBE_DIAGNOSE.DBA_PARTITIONED_INDSIZE(0,U.NAME,'"'||T.NAME||'"','"'||I.NAME||'"')),
DECODE(I.PARTITIONED,0,DBE_DIAGNOSE.DBA_SEGSIZE(1, I.ENTRY),1,DBE_DIAGNOSE.DBA_PARTITIONED_INDSIZE(1,U.NAME,'"'||T.NAME||'"','"'||I.NAME||'"')),
DECODE(I.PARTITIONED,0,DBE_DIAGNOSE.DBA_SEGSIZE(2, I.ENTRY),1,DBE_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, I.CLUFAC,
T.NUM_ROWS, I.SAMPLESIZE, I.ANALYZETIME, FLAGS & 1,
DECODE(I.CR_MODE, 0, 'ROW', 1, 'PAGE', NULL),
DECODE(I.FLAGS & 64, 0, 'N', 'Y')
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 DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_ID)
/
CREATE OR REPLACE VIEW USER_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,
IS_REVERSED
)
AS
SELECT I.NAME, DECODE(I.TYPE, 0, 'NORMAL', 'NORMAL'),
T.NAME, DBE_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, DBE_DIAGNOSE.DBA_LISTCOLS(ME.USER_NAME, '"'||T.NAME||'"', I.COL_LIST),
DECODE(I.PARTITIONED,0,DBE_DIAGNOSE.DBA_SEGSIZE(0, I.ENTRY),1,DBE_DIAGNOSE.DBA_PARTITIONED_INDSIZE(0,ME.USER_NAME,'"'||T.NAME||'"','"'||I.NAME||'"')),
DECODE(I.PARTITIONED,0,DBE_DIAGNOSE.DBA_SEGSIZE(1, I.ENTRY),1,DBE_DIAGNOSE.DBA_PARTITIONED_INDSIZE(1,ME.USER_NAME,'"'||T.NAME||'"','"'||I.NAME||'"')),
DECODE(I.PARTITIONED,0,DBE_DIAGNOSE.DBA_SEGSIZE(2, I.ENTRY),1,DBE_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, I.CLUFAC,
T.NUM_ROWS, I.SAMPLESIZE, I.ANALYZETIME, FLAGS & 1,
DECODE(I.CR_MODE, 0, 'ROW', 1, 'PAGE', NULL),
DECODE(I.FLAGS & 64, 0, 'N', 'Y')
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 ALL_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.RECYCLED = 0 AND
T.USER# = U.ID and
(U.NAME = user or
user = 'SYS' or
DBE_DIAGNOSE.has_obj_privs(user, U.NAME, '"'||T.NAME||'"', 'TABLE')) AND DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_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.RECYCLED = 0 AND
T.USER# = U.ID AND
P.USER# = U.ID AND
P.TABLE# = T.ID and
(U.NAME = user or
user = 'SYS' or
DBE_DIAGNOSE.has_obj_privs(user, U.NAME, '"'||T.NAME||'"', 'TABLE')) AND DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_ID)
UNION ALL
SELECT U.NAME, I.NAME, NULL AS SUBOBJECT_NAME, I.ID, DECODE(T.RECYCLED, 1, 'RECYCLED INDEX', 'INDEX'),
SCN2DATE(I.SEQUENCE#), SCN2DATE(I.SEQUENCE#), DECODE(I.FLAGS & 4, 0, 'VALID', 'INVALID'), if(T.TYPE = 2 or T.TYPE = 3, 'Y', 'N')
FROM SYS.SYS_USERS U, SYS.SYS_INDEXES I, SYS.SYS_TABLES T
WHERE T.RECYCLED = 0 AND
I.USER# = U.ID AND
T.USER# = U.ID AND
I.TABLE# = T.ID AND
(U.NAME = user or
user = 'SYS' or
DBE_DIAGNOSE.has_obj_privs(user, U.NAME, '"'||T.NAME||'"', 'TABLE')) AND DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_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 and
(U.NAME = user or
user = 'SYS' or
DBE_DIAGNOSE.has_obj_privs(user, U.NAME, '"'||V.NAME||'"', 'VIEW')) AND DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_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
WHERE DV.USER_NAME = user or
user = 'SYS' or
DBE_DIAGNOSE.has_obj_privs(user, 'SYS', '"'||DV.NAME||'"', 'VIEW')
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' WHEN P.TYPE='S' THEN 'PACKAGE SPEC' WHEN P.TYPE='B' THEN 'PACKAGE BODY' WHEN P.TYPE='Y' THEN 'TYPE SPEC' WHEN P.TYPE='O' THEN 'TYPE BODY' 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
user = 'SYS' or
DBE_DIAGNOSE.has_obj_privs(user, U.NAME, '"'||P.NAME||'"', P.TYPE)) AND DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_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 and
(U.NAME = user or
user = 'SYS' or
DBE_DIAGNOSE.has_obj_privs(user, U.NAME, '"'||S.NAME||'"', 'SEQUENCE') ) AND DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_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 and
Y.TYPE = 0 and
(U.NAME = user or
U.NAME = 'PUBLIC' or
user = 'SYS' or
DBE_DIAGNOSE.has_obj_privs(user, Y.TABLE_OWNER, '"'||Y.TABLE_NAME||'"', 'TABLE')) AND DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_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 and
Y.TYPE = 1 and
(U.NAME = user or
U.NAME = 'PUBLIC' or
user = 'SYS' or
DBE_DIAGNOSE.has_obj_privs(user, Y.TABLE_OWNER, '"'||Y.TABLE_NAME||'"', 'VIEW')) AND DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_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 and
Y.TYPE = 8 and
(U.NAME = user or
U.NAME = 'PUBLIC' or
user = 'SYS' or
DBE_DIAGNOSE.has_obj_privs(user, Y.TABLE_OWNER, '"'||Y.SYNONYM_NAME||'"', 'FUNCTION')) AND DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_ID)
UNION ALL
SELECT U.NAME, D.DIRECTORY_NAME, NULL AS SUBOBJECT_NAME, NULL AS OBJECT_ID, 'DIRECTORY', NULL AS CREATED, NULL AS LAST_DDL_TIME, NULL AS STATUS, NULL AS TEMPORARY
FROM SYS.SYS_USERS U, SYS.SYS_DIRECTORIES D
WHERE U.ID = D.USER# and
(U.NAME = user or
user = 'SYS' or
DBE_DIAGNOSE.has_obj_privs(user, U.NAME, '"'||D.DIRECTORY_NAME||'"', 'DIRECTORY')) AND DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_ID)
UNION ALL
SELECT U.NAME, L.NAME, NULL AS SUBOBJECT_NAME, NULL AS OBJECT_ID, 'LIBRARY', SCN2DATE(L.ORG_SCN), SCN2DATE(L.CHG_SCN), NULL AS STATUS, 'N'
FROM SYS.SYS_USERS U, SYS.SYS_LIBRARIES L
WHERE U.ID = L.USER# and
(U.NAME = user or
user = 'SYS' or
DBE_DIAGNOSE.has_obj_privs(user, U.NAME, '"'||L.NAME||'"', 'LIBRARY') ) AND DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_ID)
/
CREATE OR REPLACE VIEW DBA_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 DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_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 AND DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_ID)
UNION ALL
SELECT U.NAME, I.NAME, NULL AS SUBOBJECT_NAME, I.ID, DECODE(T.RECYCLED, 1, 'RECYCLED INDEX', 'INDEX'),
SCN2DATE(I.SEQUENCE#), SCN2DATE(I.SEQUENCE#), DECODE(I.FLAGS & 4, 0, 'VALID', 'INVALID'), 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 DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_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 AND DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_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' WHEN P.TYPE='S' THEN 'PACKAGE SPEC' WHEN P.TYPE='B' THEN 'PACKAGE BODY' WHEN P.TYPE='Y' THEN 'TYPE SPEC' WHEN P.TYPE='O' THEN 'TYPE BODY' 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 DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_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 AND DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_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 AND DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_ID)
UNION ALL
SELECT U.NAME, D.DIRECTORY_NAME, NULL AS SUBOBJECT_NAME, NULL AS OBJECT_ID, 'DIRECTORY', NULL AS CREATED, NULL AS LAST_DDL_TIME, NULL AS STATUS, NULL AS TEMPORARY FROM SYS.SYS_USERS U, SYS.SYS_DIRECTORIES D WHERE U.ID = D.USER# AND DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_ID)
UNION ALL
SELECT U.NAME, L.NAME, NULL AS SUBOBJECT_NAME, NULL AS OBJECT_ID, 'LIBRARY', SCN2DATE(L.ORG_SCN), SCN2DATE(L.CHG_SCN), NULL AS STATUS, 'N' FROM SYS.SYS_USERS U, SYS.SYS_LIBRARIES L WHERE U.ID = L.USER# AND DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_ID)
/
CREATE OR REPLACE VIEW USER_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(I.SEQUENCE#), SCN2DATE(I.SEQUENCE#), DECODE(I.FLAGS & 4, 0, 'VALID', 'INVALID'), 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 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' WHEN P.TYPE='S' THEN 'PACKAGE SPEC' WHEN P.TYPE='B' THEN 'PACKAGE BODY' WHEN P.TYPE='Y' THEN 'TYPE SPEC' WHEN P.TYPE='O' THEN 'TYPE BODY' 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
UNION ALL
SELECT D.DIRECTORY_NAME, NULL AS SUBOBJECT_NAME, NULL AS OBJECT_ID, 'DIRECTORY', NULL AS CREATED, NULL AS LAST_DDL_TIME, NULL AS STATUS, NULL AS TEMPORARY FROM SYS.DV_ME ME, SYS.SYS_DIRECTORIES D WHERE ME.USER_ID = D.USER#
UNION ALL
SELECT L.NAME, NULL AS SUBOBJECT_NAME, NULL AS OBJECT_ID, 'LIBRARY', SCN2DATE(L.ORG_SCN), SCN2DATE(L.CHG_SCN), NULL AS STATUS, 'N' FROM SYS.DV_ME ME, SYS.SYS_LIBRARIES L WHERE ME.USER_ID = L.USER#
/
CREATE OR REPLACE VIEW DBA_TABLESPACES
(
TABLESPACE_NAME,
PAGE_SIZE,
EXTENT_PAGES,
DATAFILE_COUNT,
TOTAL_SIZE,
USED_SIZE,
STATUS,
IN_MEMORY,
CONTENTS,
LOGGING,
BIGFILE,
ENCRYPT
)
AS
SELECT TS.NAME, DBE_DIAGNOSE.DBA_SPCSIZE(TS.ID, 'PAGE'), TS.EXTENT_SIZE, TS.FILE_COUNT,
DBE_DIAGNOSE.DBA_SPCSIZE(TS.ID, 'TOTAL'), DBE_DIAGNOSE.DBA_SPCSIZE(TS.ID, 'USED'), TS.STATUS, TS.IN_MEMORY,
DECODE(TS.TEMPORARY, 'TRUE', 'TEMPORARY', 'PERMANENT'),
DECODE(TS.TEMPORARY, 'TRUE', 'N', 'Y'), 'N',
DECODE(TS.ENCRYPT, 'TRUE', 'Y', 'N')
FROM SYS.DV_TABLESPACES TS
/
CREATE OR REPLACE VIEW DBA_FREE_SPACE
(
TABLESPACE_NAME,
FILE_ID,
BLOCK_ID,
BYTES,
BLOCKS,
RELATIVE_FNO
)
AS
SELECT * FROM SYS.DV_FREE_SPACE
/
CREATE OR REPLACE VIEW USER_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 USER_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.USER_SYNONYMS.SYNONYM_NAME IS 'Name of the synonym'
/
COMMENT ON COLUMN SYS.USER_SYNONYMS.TABLE_OWNER IS 'Owner of the object referenced by the synonym'
/
COMMENT ON COLUMN SYS.USER_SYNONYMS.TABLE_NAME IS 'Name of the object referenced by the synonym'
/
CREATE OR REPLACE VIEW SYS.DBA_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# AND DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_ID)
/
CREATE OR REPLACE VIEW SYS.ALL_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# AND DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_ID)
where S.TYPE = 0 and
(U.NAME = user or
user = 'SYS' or
U.NAME = 'PUBLIC' or
DBE_DIAGNOSE.has_obj_privs(user, S.TABLE_OWNER, '"'||S.TABLE_NAME||'"', 'TABLE'))
UNION ALL
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# AND DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_ID)
where S.TYPE = 1 and
(U.NAME = user or
user = 'SYS' or
U.NAME = 'PUBLIC' or
DBE_DIAGNOSE.has_obj_privs(user, S.TABLE_OWNER, '"'||S.TABLE_NAME||'"', 'VIEW'))
UNION ALL
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# AND DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_ID)
where S.TYPE = 8 and
(U.NAME = user or
user = 'SYS' or
U.NAME = 'PUBLIC' or
DBE_DIAGNOSE.has_obj_privs(user, S.TABLE_OWNER, '"'||S.SYNONYM_NAME||'"', 'FUNCTION'))
/
COMMENT ON COLUMN SYS.ALL_SYNONYMS.OWNER IS 'Username of the owner of the synonym'
/
COMMENT ON COLUMN SYS.ALL_SYNONYMS.SYNONYM_NAME IS 'Name of the synonym'
/
COMMENT ON COLUMN SYS.ALL_SYNONYMS.TABLE_OWNER IS 'Owner of the object referenced by the synonym'
/
COMMENT ON COLUMN SYS.ALL_SYNONYMS.TABLE_NAME IS 'Name of the object referenced by the synonym'
/
CREATE OR REPLACE VIEW USER_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.USER_TAB_COMMENTS.TABLE_NAME IS 'Name of the object'
/
COMMENT ON COLUMN SYS.USER_TAB_COMMENTS.TABLE_TYPE IS 'Type of the object: "TABLE" or "VIEW"'
/
COMMENT ON COLUMN SYS.USER_TAB_COMMENTS.COMMENTS IS 'Comment on the object'
/
CREATE OR REPLACE VIEW DBA_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 AND DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_ID) 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# AND DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_ID) 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.DBA_TAB_COMMENTS.OWNER IS 'Owner of the object'
/
COMMENT ON COLUMN SYS.DBA_TAB_COMMENTS.TABLE_NAME IS 'Name of the object'
/
COMMENT ON COLUMN SYS.DBA_TAB_COMMENTS.TABLE_TYPE IS 'Type of the object'
/
COMMENT ON COLUMN SYS.DBA_TAB_COMMENTS.COMMENTS IS 'Comment on the object'
/
CREATE OR REPLACE VIEW ALL_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 AND DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_ID) 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
user = 'SYS' or
DBE_DIAGNOSE.has_obj_privs(user, U.NAME, '"'||T.NAME||'"', 'TABLE'))
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# AND DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_ID) 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
user = 'SYS' or
DBE_DIAGNOSE.has_obj_privs(user, U.NAME, '"'||V.NAME||'"', 'VIEW'))
/
COMMENT ON COLUMN SYS.ALL_TAB_COMMENTS.OWNER IS 'Owner of the object'
/
COMMENT ON COLUMN SYS.ALL_TAB_COMMENTS.TABLE_NAME IS 'Name of the object'
/
COMMENT ON COLUMN SYS.ALL_TAB_COMMENTS.TABLE_TYPE IS 'Type of the object'
/
COMMENT ON COLUMN SYS.ALL_TAB_COMMENTS.COMMENTS IS 'Comment on the object'
/
CREATE OR REPLACE VIEW USER_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.TABLE# = 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.TABLE# AND V_C.ID = CO.COLUMN#
/
COMMENT ON COLUMN SYS.USER_COL_COMMENTS.TABLE_NAME IS 'Name of the object'
/
COMMENT ON COLUMN SYS.USER_COL_COMMENTS.COLUMN_NAME IS 'Name of the column'
/
COMMENT ON COLUMN SYS.USER_COL_COMMENTS.COMMENTS IS 'Comment on the object'
/
CREATE OR REPLACE VIEW DBA_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 DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_ID)
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 DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_ID)
/
CREATE OR REPLACE VIEW ALL_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
user = 'SYS' or
DBE_DIAGNOSE.has_obj_privs(user, U.NAME, '"'||T.NAME||'"', 'TABLE'))
AND DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_ID)
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
user = 'SYS' or
DBE_DIAGNOSE.has_obj_privs(user, U.NAME, '"'||V.NAME||'"', 'VIEW'))
AND DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_ID)
/
CREATE OR REPLACE VIEW ALL_USERS
(
USER_ID,
USERNAME,
CREATED,
CRYPTOPERIOD
)
AS
SELECT U.ID, U.NAME, U.CTIME, decode(U.ASTATUS,
1 ,cast(NUMTODSINTERVAL(0, 'DAY') as varchar(64)),
5 ,cast(NUMTODSINTERVAL(0, 'DAY') as varchar(64)),
9 ,cast(NUMTODSINTERVAL(0, 'DAY') as varchar(64)),
17,cast(NUMTODSINTERVAL(0, 'DAY') as varchar(64)),
21,cast(NUMTODSINTERVAL(0, 'DAY') as varchar(64)),
25,cast(NUMTODSINTERVAL(0, 'DAY') as varchar(64)),
cast(DECODE(P.THRESHOLD, 0, 'UNLIMITED', U.PTIME + NUMTODSINTERVAL(P.THRESHOLD / 86400.0, 'DAY') - CURRENT_TIMESTAMP()) as varchar(64)))
FROM SYS.SYS_USERS U JOIN SYS.SYS_PROFILE P ON U.PROFILE# = P.PROFILE# AND P.RESOURCE# = 1 AND DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_ID)
/
CREATE OR REPLACE VIEW USER_SOURCE
(NAME, TYPE, LINE, TEXT)
AS
SELECT
P.NAME, DECODE(P.TYPE, 'T', 'TRIGGER', 'P', 'PROCEDURE', 'F', 'FUNCTION', 'S', 'PACKAGE SPEC', 'B', 'PACKAGE BODY', 'Y', 'TYPE SPEC', 'O', 'TYPE BODY', 'UNDEFINED'),
NULL::NUMBER, P.SOURCE
FROM SYS.DV_ME ME, SYS.SYS_PROCS P
WHERE P.USER# = ME.USER_ID
/
CREATE OR REPLACE VIEW DBA_SOURCE
(OWNER, NAME, TYPE, LINE, TEXT)
AS
SELECT
U.NAME, P.NAME, DECODE(P.TYPE, 'T', 'TRIGGER', 'P', 'PROCEDURE', 'F', 'FUNCTION','S', 'PACKAGE SPEC', 'B', 'PACKAGE BODY', 'Y', 'TYPE SPEC', 'O', 'TYPE BODY', 'UNDEFINED'),
NULL::NUMBER, P.SOURCE
FROM SYS.SYS_USERS U, SYS.SYS_PROCS P
WHERE P.USER# = U.ID AND DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_ID)
/
CREATE OR REPLACE VIEW ALL_SOURCE
(OWNER, NAME, TYPE, LINE, TEXT)
AS
SELECT
U.NAME, P.NAME, DECODE(P.TYPE, 'T', 'TRIGGER', 'P', 'PROCEDURE', 'F', 'FUNCTION','S', 'PACKAGE SPEC', 'B', 'PACKAGE BODY', 'Y', 'TYPE SPEC', 'O', 'TYPE BODY', '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
DBE_DIAGNOSE.has_obj_privs(user, U.NAME, '"'||P.NAME||'"', P.TYPE)) AND DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_ID)
/
CREATE OR REPLACE VIEW DBA_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.ALL_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',
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',
78, 'CREATE ANY DIRECTORY',
79, 'DROP ANY DIRECTORY',
81, 'CREATE ANY SQL MAP',
82, 'DROP ANY SQL MAP',
83, 'CREATE ANY TYPE',
84, 'CREATE TYPE',
85, 'DROP ANY TYPE',
86, 'EXECUTE ANY TYPE',
87, 'CREATE CTRLFILE',
88, 'CREATE LIBRARY',
89, 'EXEMPT REDACTION POLICY',
90, 'CREATE ANY LIBRARY',
91, 'DROP ANY LIBRARY',
92, 'EXECUTE ANY LIBRARY',
93, 'EXEMPT ACCESS POLICY',
94, 'INHERIT ANY PRIVILEGES',
95, 'CREATE TENANT',
96, 'ALTER TENANT',
97, 'DROP TENANT',
98, 'SELECT ANY DICTIONARY',
99, 'FORCE ANY TRANSACTION',
100, 'CREATE DATABASE LINK',
101, 'ALTER DATABASE LINK',
102, 'DROP DATABASE LINK',
103, 'USE ANY TABLESPACE',
'UNKNOWN'),
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 AND DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_ID)
/
CREATE OR REPLACE VIEW SYS.DBA_SYS_PRIVS
(
GRANTEE,
PRIVILEGE,
ADMIN_OPTION
)
AS
SELECT * FROM SYS.ALL_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',
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',
78, 'CREATE ANY DIRECTORY',
79, 'DROP ANY DIRECTORY',
81, 'CREATE ANY SQL MAP',
82, 'DROP ANY SQL MAP',
83, 'CREATE ANY TYPE',
84, 'CREATE TYPE',
85, 'DROP ANY TYPE',
86, 'EXECUTE ANY TYPE',
87, 'CREATE CTRLFILE',
88, 'CREATE LIBRARY',
89, 'EXEMPT REDACTION POLICY',
90, 'CREATE ANY LIBRARY',
91, 'DROP ANY LIBRARY',
92, 'EXECUTE ANY LIBRARY',
93, 'EXEMPT ACCESS POLICY',
94, 'INHERIT ANY PRIVILEGES',
95, 'CREATE TENANT',
96, 'ALTER TENANT',
97, 'DROP TENANT',
98, 'SELECT ANY DICTIONARY',
99, 'FORCE ANY TRANSACTION',
100, 'CREATE DATABASE LINK',
101, 'ALTER DATABASE LINK',
102, 'DROP DATABASE LINK',
103, 'USE ANY TABLESPACE',
'UNKNOWN'),
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.USER_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',
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',
78, 'CREATE ANY DIRECTORY',
79, 'DROP ANY DIRECTORY',
81, 'CREATE ANY SQL MAP',
82, 'DROP ANY SQL MAP',
83, 'CREATE ANY TYPE',
84, 'CREATE TYPE',
85, 'DROP ANY TYPE',
86, 'EXECUTE ANY TYPE',
87, 'CREATE CTRLFILE',
88, 'CREATE LIBRARY',
89, 'EXEMPT REDACTION POLICY',
90, 'CREATE ANY LIBRARY',
91, 'DROP ANY LIBRARY',
92, 'EXECUTE ANY LIBRARY',
93, 'EXEMPT ACCESS POLICY',
94, 'INHERIT ANY PRIVILEGES',
95, 'CREATE TENANT',
96, 'ALTER TENANT',
97, 'DROP TENANT',
98, 'SELECT ANY DICTIONARY',
99, 'FORCE ANY TRANSACTION',
100, 'CREATE DATABASE LINK',
101, 'ALTER DATABASE LINK',
102, 'DROP DATABASE LINK',
103, 'USE ANY TABLESPACE',
'UNKNOWN'),
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.DBA_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 AND DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_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.USER_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.DBA_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', 18, 'DIRECTORY', 21, 'LIBRARY', 25, 'USER', 'UNKNOWN'),
DECODE(P.PRIVILEGE, 0, 'ALTER', 1, 'DELETE', 2, 'EXECUTE', 3, 'INDEX', 4, 'INSERT', 5, 'READ', 6, 'REFERENCES', 7, 'SELECT', 8, 'UPDATE', 9, 'READ ON DIRECTORY', 10, 'WRITE ON DIRECTORY', 11, 'EXECUTE ON DIRECTORY', 12, 'INHERIT PRIVILEGES', '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 AND DBE_DIAGNOSE.TENANT_CHECK(0, U1.TENANT_ID)
UNION ALL
SELECT R.NAME, U.NAME, P.OBJECT_NAME,
DECODE(P.OBJECT_TYPE, 0, 'TABLE', 1, 'VIEW', 2, 'SEQUENCE', 3, 'PROCEDURE', 18, 'DIRECTORY', 21, 'LIBRARY', 25, 'USER', 'UNKNOWN'),
DECODE(P.PRIVILEGE, 0, 'ALTER', 1, 'DELETE', 2, 'EXECUTE', 3, 'INDEX', 4, 'INSERT', 5, 'READ', 6, 'REFERENCES', 7, 'SELECT', 8, 'UPDATE', 9, 'READ ON DIRECTORY', 10, 'WRITE ON DIRECTORY', 11, 'EXECUTE ON DIRECTORY', 12, 'INHERIT PRIVILEGES', '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 AND DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_ID)
UNION ALL
SELECT U1.NAME, 'SYS', U2.NAME, 'USER',
DECODE(P.PRIVILEGE, 0, 'INHERIT PRIVILEGES', 'UNKNOWN'),
DECODE(P.OPTION, 0, 'NO', 1, 'YES', '-')
FROM SYS.SYS_USERS U1, SYS.SYS_USERS U2, SYS.SYS_USER_PRIVS P
WHERE U1.ID = P.GRANTEE AND U2.ID = P.UID
/
CREATE OR REPLACE VIEW SYS.USER_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', 18, 'DIRECTORY', 21, 'LIBRARY', 25, 'USER', 'UNKNOWN'),
DECODE(P.PRIVILEGE, 0, 'ALTER', 1, 'DELETE', 2, 'EXECUTE', 3, 'INDEX', 4, 'INSERT', 5, 'READ', 6, 'REFERENCES', 7, 'SELECT', 8, 'UPDATE', 9, 'READ ON DIRECTORY', 10, 'WRITE ON DIRECTORY', 11, 'EXECUTE ON DIRECTORY', 12, 'INHERIT PRIVILEGES', '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 AND DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_ID)
UNION ALL
SELECT M.USER_NAME, 'SYS', U2.NAME, 'USER',
DECODE(P.PRIVILEGE, 0, 'INHERIT PRIVILEGES', 'UNKNOWN'),
DECODE(P.OPTION, 0, 'NO', 1, 'YES', '-')
FROM SYS.DV_ME M, SYS.SYS_USERS U2, SYS.SYS_USER_PRIVS P
WHERE M.USER_ID = P.GRANTEE AND U2.ID = P.UID
/
CREATE OR REPLACE VIEW DBA_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 DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_ID)
/
CREATE OR REPLACE VIEW ALL_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
user = 'SYS' or
DBE_DIAGNOSE.has_obj_privs(user, U.NAME, '"'||SEQ.NAME||'"', 'SEQUENCE')) AND DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_ID)
/
CREATE OR REPLACE VIEW USER_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.ALL_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,
DBE_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 DBE_DIAGNOSE.DBA_IND_POS(I.COL_LIST::VARCHAR(128), C.ID::VARCHAR(128))::INTEGER > 0
AND T.RECYCLED = 0
AND (U.NAME = user or
user = 'SYS' or
DBE_DIAGNOSE.has_obj_privs(user, U.NAME, '"'||T.NAME||'"', 'TABLE'))
AND DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_ID)
/
CREATE OR REPLACE VIEW SYS.DBA_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,
DBE_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 DBE_DIAGNOSE.DBA_IND_POS(I.COL_LIST::VARCHAR(128), C.ID::VARCHAR(128))::INTEGER > 0
AND T.RECYCLED = 0
AND DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_ID)
/
CREATE OR REPLACE VIEW SYS.USER_IND_COLUMNS
(
INDEX_NAME,
TABLE_NAME,
COLUMN_NAME,
COLUMN_POSITION,
COLUMN_LENGTH
)
AS
SELECT I.NAME,
T.NAME,
C.NAME,
DBE_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 DBE_DIAGNOSE.DBA_IND_POS(I.COL_LIST::VARCHAR(128), C.ID::VARCHAR(128))::INTEGER > 0
AND T.RECYCLED = 0
/
CREATE OR REPLACE VIEW SYS.DBA_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.DBA_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# AND DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_ID)
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# AND DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_ID)
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.ALL_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# AND DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_ID)
JOIN SYS.SYS_DML_STATS MO ON T.USER# = MO.USER# AND T.ID = MO.TABLE# WHERE T.RECYCLED = 0 AND (MO.PARTED = 0 OR (MO.PARTED = 1 AND MO.PART#=-1)) AND
(U.NAME = user or
user = 'SYS' or
DBE_DIAGNOSE.has_obj_privs(user, U.NAME, '"'||T.NAME||'"', 'TABLE'))
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# AND DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_ID)
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 T.RECYCLED = 0 AND MO.PARTED = 1 AND MO.PART# <> -1 AND
(U.NAME = user or
user = 'SYS' or
DBE_DIAGNOSE.has_obj_privs(user, U.NAME, '"'||T.NAME||'"', 'TABLE'))
/
CREATE OR REPLACE VIEW SYS.USER_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 ALL_TAB_MODIFICATIONS FOR SYS.ALL_TAB_MODIFICATIONS
/
CREATE OR REPLACE PUBLIC SYNONYM USER_TAB_MODIFICATIONS FOR SYS.USER_TAB_MODIFICATIONS
/
GRANT SELECT ON USER_TAB_MODIFICATIONS TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM DBA_TAB_MODIFICATIONS FOR SYS.DBA_TAB_MODIFICATIONS
/
CREATE OR REPLACE VIEW USER_TAB_PARTITIONS AS
SELECT
T.NAME AS TABLE_NAME,
DECODE(TP.FLAGS & 0x4, 0, 'NO', 'YES')::VARCHAR2(3) AS COMPOSITE,
TP.NAME AS PARTITION_NAME,
DECODE(TP.FLAGS & 0x4, 0, 0, TP.SUBPARTCNT)::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,
DECODE(TP.FLAGS & 8, 8, 'CSF', 'ASF') AS ROW_FORMAT,
DECODE(TP.FLAGS & 16, 16, 'YES', 'NO') AS NOLOGGING_INSERT
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 DBA_TAB_PARTITIONS AS
SELECT
U.NAME AS TABLE_OWNER,
T.NAME AS TABLE_NAME,
DECODE(TP.FLAGS & 0x4, 0, 'NO', 'YES')::VARCHAR2(3) AS COMPOSITE,
TP.NAME AS PARTITION_NAME,
DECODE(TP.FLAGS & 0x4, 0, 0, TP.SUBPARTCNT)::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,
DECODE(TP.FLAGS & 8, 8, 'CSF', 'ASF') AS ROW_FORMAT,
DECODE(TP.FLAGS & 16, 16, 'YES', 'NO') AS NOLOGGING_INSERT
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 DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_ID)
/
CREATE OR REPLACE VIEW ALL_TAB_PARTITIONS AS
SELECT
U.NAME AS TABLE_OWNER,
T.NAME AS TABLE_NAME,
DECODE(TP.FLAGS & 0x4, 0, 'NO', 'YES')::VARCHAR2(3) AS COMPOSITE,
TP.NAME AS PARTITION_NAME,
DECODE(TP.FLAGS & 0x4, 0, 0, TP.SUBPARTCNT)::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,
DECODE(TP.FLAGS & 8, 8, 'CSF', 'ASF') AS ROW_FORMAT,
DECODE(TP.FLAGS & 16, 16, 'YES', 'NO') AS NOLOGGING_INSERT
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
user = 'SYS' or
DBE_DIAGNOSE.has_obj_privs(user, U.NAME, '"'||T.NAME||'"', 'TABLE'))
/
CREATE OR REPLACE VIEW USER_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', 'P', 'PROCEDURE', 'S', 'PACKAGE SPEC', 'B', 'PACKAGE BODY', 'Y', 'TYPE SPEC', 'O', 'TYPE BODY', 'UNKNOWN'),
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', 'UNKNOWN')::VARCHAR2(7)
FROM SYS.DV_ME ME, SYS.SYS_PROCS P WHERE P.USER# = ME.USER_ID
/
CREATE OR REPLACE VIEW DBA_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', 'P', 'PROCEDURE', 'S', 'PACKAGE SPEC', 'B', 'PACKAGE BODY', 'Y', 'TYPE SPEC', 'O', 'TYPE BODY', 'UNKNOWN'),
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', 'UNKNOWN')::VARCHAR2(7)
FROM SYS.SYS_USERS U, SYS.SYS_PROCS P WHERE P.USER# = U.ID AND DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_ID)
/
CREATE OR REPLACE VIEW ALL_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', 'P', 'PROCEDURE', 'S', 'PACKAGE SPEC', 'B', 'PACKAGE BODY', 'Y', 'TYPE SPEC', 'O', 'TYPE BODY', 'UNKNOWN'),
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', 'UNKNOWN')::VARCHAR2(7)
FROM SYS.SYS_USERS U, SYS.SYS_PROCS P
WHERE P.USER# = U.ID and
(U.NAME = user or
user = 'SYS' or
DBE_DIAGNOSE.has_obj_privs(user, U.NAME, '"'||P.NAME||'"', P.TYPE)) AND DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_ID)
/
CREATE OR REPLACE VIEW SYS.DBA_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',
16, 'PERMANENT',
17, 'PERMANENT & EXPIRED',
18, 'PERMANENT & EXPIRED(GRACE)',
20, 'PERMANENT & LOCKED(TIMED)',
21, 'PERMANENT & EXPIRED & LOCKED(TIMED)',
22, 'PERMANENT & EXPIRED(GRACE) & LOCKED(TIMED)',
24, 'PERMANENT & LOCKED',
25, 'PERMANENT & EXPIRED & LOCKED',
26, 'PERMANENT & 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# AND DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_ID)
/
CREATE OR REPLACE VIEW SYS.USER_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',
16, 'PERMANENT',
17, 'PERMANENT & EXPIRED',
18, 'PERMANENT & EXPIRED(GRACE)',
20, 'PERMANENT & LOCKED(TIMED)',
21, 'PERMANENT & EXPIRED & LOCKED(TIMED)',
22, 'PERMANENT & EXPIRED(GRACE) & LOCKED(TIMED)',
24, 'PERMANENT & LOCKED',
25, 'PERMANENT & EXPIRED & LOCKED',
26, 'PERMANENT & 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 DBA_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', '',
DBE_DIAGNOSE.DBA_LISTCOLS(U1.NAME, '"'||T.NAME||'"', C.COL_LIST) CONS_COLS, DBE_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.RECYCLED = 0
AND I.USER# = U3.ID AND I.TABLE# = T3.ID AND I.USER# = T3.USER# AND DBE_DIAGNOSE.TENANT_CHECK(0, U1.TENANT_ID)
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', '',
DBE_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 AND DBE_DIAGNOSE.TENANT_CHECK(0, U1.TENANT_ID)
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', '', DBE_DIAGNOSE.DBA_LISTCOLS(U.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 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) AND DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_ID)
/
CREATE OR REPLACE VIEW ALL_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', '',
DBE_DIAGNOSE.DBA_LISTCOLS(U1.NAME, '"'||T.NAME||'"', C.COL_LIST) CONS_COLS, DBE_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
user = 'SYS' or
DBE_DIAGNOSE.has_obj_privs(user, U1.NAME, '"'||T.NAME||'"', 'TABLE')) AND DBE_DIAGNOSE.TENANT_CHECK(0, U1.TENANT_ID)
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', '',
DBE_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
user = 'SYS' or
DBE_DIAGNOSE.has_obj_privs(user, U1.NAME, '"'||T.NAME||'"', 'TABLE')) AND DBE_DIAGNOSE.TENANT_CHECK(0, U1.TENANT_ID)
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', '', DBE_DIAGNOSE.DBA_LISTCOLS(U.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 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
user = 'SYS' or
DBE_DIAGNOSE.has_obj_privs(user, U.NAME, '"'||T.NAME||'"', 'TABLE')) AND DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_ID)
/
CREATE OR REPLACE VIEW USER_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', '',
DBE_DIAGNOSE.DBA_LISTCOLS(M.USER_NAME, '"'||T.NAME||'"', C.COL_LIST) CONS_COLS, DBE_DIAGNOSE.DBA_LISTCOLS(U2.NAME, '"'||T2.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.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
AND DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_ID)
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', '',
DBE_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', '', DBE_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
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 USER_CONS_COLUMNS
(
OWNER,
CONSTRAINT_NAME,
TABLE_NAME,
COLUMN_NAME,
POSITION
)
AS
SELECT M.USER_NAME, CON.CONS_NAME, T.NAME, C.NAME, CEIL(LOCATE(C.ID, ','||CON.COL_LIST)/2)
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 ALL_CONS_COLUMNS
(
OWNER,
CONSTRAINT_NAME,
TABLE_NAME,
COLUMN_NAME,
POSITION
)
AS
SELECT U.NAME, CON.CONS_NAME, T.NAME, C.NAME, CEIL(LOCATE(C.ID, ','||CON.COL_LIST)/2)
FROM SYS.SYS_USERS U, SYS.SYS_TABLES T, SYS.SYS_COLUMNS C, SYS.SYS_CONSTRAINT_DEFS CON
WHERE CON.USER# = U.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 and
(U.NAME = user or
user = 'SYS' or
DBE_DIAGNOSE.has_obj_privs(user, U.NAME, '"'||T.NAME||'"', 'TABLE')) AND DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_ID)
/
CREATE OR REPLACE VIEW ALL_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
user = 'SYS' or
DBE_DIAGNOSE.has_obj_privs(user, U.NAME, '"'||P.NAME||'"', P.TYPE))
AND DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_ID)
/
CREATE OR REPLACE VIEW DBA_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 DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_ID)
/
CREATE OR REPLACE VIEW USER_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 DBA_ROLES
(
ROLE,
PASSWORD_REQUIRED,
AUTHENTICATION_TYPE
)
AS
SELECT R.NAME, 'NO', 'PASSWORD' FROM SYS.SYS_ROLES R
/
CREATE OR REPLACE VIEW DBA_ARGUMENTS
(
OWNER,
OBJECT_NAME,
PACKAGE_NAME,
OVERLOAD,
SUBPROGRAM_ID,
ARGUMENT_NAME,
POSITION,
SEQUENCE,
DATA_LEVEL,
DATA_TYPE,
DEFAULTED,
DEFAULT_VALUE,
IN_OUT,
DATA_LENGTH,
DATA_PRECISION,
DATA_SCALE
)
AS
SELECT U.NAME, A.OBJECT_NAME, A.PACKAGE, DECODE(A.OVERLOAD, 0, NULL, TO_CHAR(A.OVERLOAD)),PROC_SEQ,
A.ARGUMENT_NAME, A.POSITION,A.SEQUENCE,A.DATA_LEVEL,
TYPE_ID2NAME(A.DATA_TYPE),
DECODE(A.DEFAULTED, 0,'N', 1,'Y'),
A.DEFAULT_VALUE,
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 DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_ID)
/
CREATE OR REPLACE VIEW ALL_ARGUMENTS
(
OWNER,
OBJECT_NAME,
PACKAGE_NAME,
OVERLOAD,
SUBPROGRAM_ID,
ARGUMENT_NAME,
POSITION,
SEQUENCE,
DATA_LEVEL,
DATA_TYPE,
DEFAULTED,
DEFAULT_VALUE,
IN_OUT,
DATA_LENGTH,
DATA_PRECISION,
DATA_SCALE
)
AS
SELECT U.NAME, A.OBJECT_NAME, A.PACKAGE, DECODE(A.OVERLOAD, 0, NULL, TO_CHAR(A.OVERLOAD)),PROC_SEQ,
A.ARGUMENT_NAME, A.POSITION,A.SEQUENCE,A.DATA_LEVEL,
TYPE_ID2NAME(A.DATA_TYPE),
DECODE(A.DEFAULTED, 0,'N', 1,'Y'),
A.DEFAULT_VALUE,
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, SYS.SYS_PROCS P
WHERE U.ID = A.USER# and
P.NAME = A.OBJECT_NAME and
(P.TYPE <> 'S' and P.TYPE <> 'B') and
(U.NAME = user or
user = 'SYS' or
DBE_DIAGNOSE.has_obj_privs(user, U.NAME, '"'||P.NAME||'"', P.TYPE)) AND DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_ID)
UNION ALL
SELECT U.NAME, A.OBJECT_NAME, A.PACKAGE, DECODE(A.OVERLOAD, 0, NULL, TO_CHAR(A.OVERLOAD)),PROC_SEQ,
A.ARGUMENT_NAME, A.POSITION,A.SEQUENCE,A.DATA_LEVEL,
TYPE_ID2NAME(A.DATA_TYPE),
DECODE(A.DEFAULTED, 0,'N', 1,'Y'),
A.DEFAULT_VALUE,
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, SYS.SYS_PROCS P
WHERE U.ID = A.USER# and
P.NAME = A.PACKAGE and
P.USER# = A.USER# and
P.TYPE = 'S' and
(U.NAME = user or
user = 'SYS' or
DBE_DIAGNOSE.has_obj_privs(user, U.NAME, '"'||P.NAME||'"', 'PACKAGE')) AND DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_ID)
/
CREATE OR REPLACE VIEW USER_ARGUMENTS
(
OBJECT_NAME,
PACKAGE_NAME,
OVERLOAD,
SUBPROGRAM_ID,
ARGUMENT_NAME,
POSITION,
SEQUENCE,
DATA_LEVEL,
DATA_TYPE,
DEFAULTED,
DEFAULT_VALUE,
IN_OUT,
DATA_LENGTH,
DATA_PRECISION,
DATA_SCALE
)
AS
SELECT A.OBJECT_NAME, A.PACKAGE, DECODE(A.OVERLOAD, 0, NULL, TO_CHAR(A.OVERLOAD)),PROC_SEQ,
A.ARGUMENT_NAME, A.POSITION,A.SEQUENCE,A.DATA_LEVEL,
TYPE_ID2NAME(A.DATA_TYPE),
DECODE(A.DEFAULTED, 0,'N', 1,'Y'),
A.DEFAULT_VALUE,
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 ALL_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 AND DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_ID)
where T.RECYCLED = 0 AND
(U.NAME = user or
user = 'SYS' or
DBE_DIAGNOSE.has_obj_privs(user, U.NAME, '"'||T.NAME||'"', 'TABLE'))
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 AND DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_ID)
JOIN SYS.SYS_TABLE_PARTS TP ON T.USER# = TP.USER# AND T.ID = TP.TABLE#
where T.RECYCLED = 0 AND
(U.NAME = user or
user = 'SYS' or
DBE_DIAGNOSE.has_obj_privs(user, U.NAME, '"'||T.NAME||'"', 'TABLE'))
/
CREATE OR REPLACE VIEW DBA_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.ALL_TAB_STATISTICS
/
CREATE OR REPLACE VIEW USER_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 ALL_TAB_STATISTICS FOR SYS.ALL_TAB_STATISTICS
/
GRANT SELECT ON ALL_TAB_STATISTICS TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM DBA_TAB_STATISTICS FOR SYS.DBA_TAB_STATISTICS
/
CREATE OR REPLACE PUBLIC SYNONYM USER_TAB_STATISTICS FOR SYS.USER_TAB_STATISTICS
/
GRANT SELECT ON USER_TAB_STATISTICS TO PUBLIC
/
CREATE OR REPLACE VIEW ALL_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#),
DECODE(IP.FLAGS & 4, 0, 'VALID', 'INVALID'), IP.PCTFREE, IP.INITRANS, 255,
IP.BLEVEL, IP.LEVEL_BLOCKS, IP.DISTKEY, IP.LBLKKEY, IP.DBLKKEY, IP.CLUFAC,
TP.ROWCNT, IP.SAMPLESIZE, IP.ANALYZETIME
FROM SYS.SYS_USERS U
JOIN SYS.SYS_TABLE_PARTS TP ON TP.USER# = U.ID AND DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_ID)
JOIN SYS.SYS_TABLES T ON T.ID = TP.TABLE# AND T.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
where T.RECYCLED = 0 AND
(U.NAME = user or
user = 'SYS' or
DBE_DIAGNOSE.has_obj_privs(user, U.NAME, '"'||T.NAME||'"', 'TABLE'))
/
CREATE OR REPLACE VIEW DBA_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#),
DECODE(IP.FLAGS & 4, 0, 'VALID', 'INVALID'), IP.PCTFREE, IP.INITRANS, 255,
IP.BLEVEL, IP.LEVEL_BLOCKS, IP.DISTKEY, IP.LBLKKEY, IP.DBLKKEY, IP.CLUFAC,
TP.ROWCNT, IP.SAMPLESIZE, IP.ANALYZETIME
FROM SYS.SYS_USERS U JOIN SYS.SYS_TABLE_PARTS TP ON TP.USER# = U.ID AND DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_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 USER_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#),
DECODE(IP.FLAGS & 4, 0, 'VALID', 'INVALID'), IP.PCTFREE, IP.INITRANS, 255,
IP.BLEVEL, IP.LEVEL_BLOCKS, IP.DISTKEY, IP.LBLKKEY, IP.DBLKKEY, IP.CLUFAC,
TP.ROWCNT, IP.SAMPLESIZE, IP.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 ALL_IND_PARTITIONS FOR SYS.ALL_IND_PARTITIONS
/
GRANT SELECT ON ALL_IND_PARTITIONS TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM DBA_IND_PARTITIONS FOR SYS.DBA_IND_PARTITIONS
/
CREATE OR REPLACE PUBLIC SYNONYM USER_IND_PARTITIONS FOR SYS.USER_IND_PARTITIONS
/
GRANT SELECT ON USER_IND_PARTITIONS TO PUBLIC
/
CREATE OR REPLACE VIEW ALL_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,
I.CLUFAC, T.NUM_ROWS, I.SAMPLESIZE, I.ANALYZETIME
FROM SYS.SYS_USERS U JOIN SYS.SYS_TABLES T ON T.USER# = U.ID AND DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_ID)
JOIN SYS.SYS_INDEXES I ON T.USER# = I.USER# AND T.ID = I.TABLE#
where T.RECYCLED = 0 AND
(U.NAME = user or
user = 'SYS' or
DBE_DIAGNOSE.has_obj_privs(user, U.NAME, '"'||T.NAME||'"', '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,
IP.CLUFAC, TP.ROWCNT, IP.SAMPLESIZE, IP.ANALYZETIME
FROM SYS.SYS_USERS U JOIN SYS.SYS_TABLES T ON T.USER# = U.ID AND DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_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#
where T.RECYCLED = 0 AND
(U.NAME = user or
user = 'SYS' or
DBE_DIAGNOSE.has_obj_privs(user, U.NAME, '"'||T.NAME||'"', 'TABLE'))
/
CREATE OR REPLACE VIEW DBA_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,
I.CLUFAC, T.NUM_ROWS, I.SAMPLESIZE, I.ANALYZETIME
FROM SYS.SYS_USERS U JOIN SYS.SYS_TABLES T ON T.USER# = U.ID AND DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_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,
IP.CLUFAC, TP.ROWCNT, IP.SAMPLESIZE, IP.ANALYZETIME
FROM SYS.SYS_USERS U JOIN SYS.SYS_TABLES T ON T.USER# = U.ID AND DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_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 USER_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,
I.CLUFAC, T.NUM_ROWS, I.SAMPLESIZE, I.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,
IP.CLUFAC, TP.ROWCNT, IP.SAMPLESIZE, IP.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 ALL_IND_STATISTICS FOR SYS.ALL_IND_STATISTICS
/
CREATE OR REPLACE PUBLIC SYNONYM DBA_IND_STATISTICS FOR SYS.DBA_IND_STATISTICS
/
CREATE OR REPLACE PUBLIC SYNONYM USER_IND_STATISTICS FOR SYS.USER_IND_STATISTICS
/
GRANT SELECT ON USER_IND_STATISTICS TO PUBLIC
/
CREATE OR REPLACE VIEW ALL_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, HH.SPARE3, HH.ANALYZE_TIME, NULL::NUMBER, C.HISTOGRAM
FROM SYS.SYS_USERS U JOIN SYS.SYS_TABLES T ON T.USER# = U.ID AND DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_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 T.RECYCLED = 0 AND
(U.NAME = user or
user = 'SYS' or
DBE_DIAGNOSE.has_obj_privs(user, U.NAME, '"'||T.NAME||'"', 'TABLE'))
/
CREATE OR REPLACE VIEW DBA_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 AND DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_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 USER_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, HH.SPARE3, HH.ANALYZE_TIME, 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 ALL_TAB_COL_STATISTICS FOR SYS.ALL_TAB_COL_STATISTICS
/
GRANT SELECT ON ALL_TAB_COL_STATISTICS TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM DBA_TAB_COL_STATISTICS FOR SYS.DBA_TAB_COL_STATISTICS
/
CREATE OR REPLACE PUBLIC SYNONYM USER_TAB_COL_STATISTICS FOR SYS.USER_TAB_COL_STATISTICS
/
GRANT SELECT ON USER_TAB_COL_STATISTICS TO PUBLIC
/
CREATE OR REPLACE VIEW ALL_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, HH.SPARE3, HH.ANALYZE_TIME, NULL::NUMBER, C.HISTOGRAM
FROM SYS.SYS_USERS U JOIN SYS.SYS_TABLES T ON T.USER# = U.ID AND DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_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 T.RECYCLED = 0 AND
(U.NAME = user or
user = 'SYS' or
DBE_DIAGNOSE.has_obj_privs(user, U.NAME, '"'||T.NAME||'"', 'TABLE'))
/
CREATE OR REPLACE VIEW DBA_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, HH.SPARE3, HH.ANALYZE_TIME, NULL::NUMBER, C.HISTOGRAM
FROM SYS.SYS_USERS U JOIN SYS.SYS_TABLES T ON T.USER# = U.ID AND DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_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 USER_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, HH.SPARE3, HH.ANALYZE_TIME, 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 ALL_PART_COL_STATISTICS FOR SYS.ALL_PART_COL_STATISTICS
/
GRANT SELECT ON ALL_PART_COL_STATISTICS TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM DBA_PART_COL_STATISTICS FOR SYS.DBA_PART_COL_STATISTICS
/
CREATE OR REPLACE PUBLIC SYNONYM USER_PART_COL_STATISTICS FOR SYS.USER_PART_COL_STATISTICS
/
GRANT SELECT ON USER_PART_COL_STATISTICS TO PUBLIC
/
CREATE OR REPLACE VIEW ALL_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.ENDPOINT), H.ENDPOINT, H.BUCKET
FROM SYS.SYS_USERS U JOIN SYS.SYS_TABLES T ON T.USER# = U.ID AND DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_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 T.RECYCLED = 0 AND
(U.NAME = user OR
user = 'SYS' OR
DBE_DIAGNOSE.has_obj_privs(user, U.NAME, '"'||T.NAME||'"', 'TABLE'))
/
CREATE OR REPLACE VIEW DBA_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.ENDPOINT), H.ENDPOINT, H.BUCKET
FROM SYS.SYS_USERS U JOIN SYS.SYS_TABLES T ON T.USER# = U.ID AND DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_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 USER_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.ENDPOINT), 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 ALL_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 AND DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_ID)
/
CREATE OR REPLACE VIEW SYS.ALL_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.DBA_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.USER_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 DBA_JOBS J, SYS.DV_ME M WHERE
J.LOG_USER = M.USER_NAME
/
CREATE OR REPLACE VIEW SYS.DBA_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 ALL_HISTOGRAMS FOR SYS.ALL_HISTOGRAMS
/
GRANT SELECT ON ALL_HISTOGRAMS TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM DBA_HISTOGRAMS FOR SYS.DBA_HISTOGRAMS
/
CREATE OR REPLACE PUBLIC SYNONYM USER_HISTOGRAMS FOR SYS.USER_HISTOGRAMS
/
GRANT SELECT ON USER_HISTOGRAMS TO PUBLIC
/
CREATE OR REPLACE VIEW SYS.ALL_DEPENDENCIES
(OWNER, NAME, TYPE, REFERENCED_OWNER, REFERENCED_NAME, REFERENCED_TYPE)
AS
SELECT DBE_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', 16, 'PACKAGE', 17 , 'PACKAGE BODY', 'UNDEFINED'),
DBE_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', 16, 'PACKAGE', 17 , 'PACKAGE BODY', 'UNDEFINED')
FROM SYS.SYS_DEPENDENCIES D where DBE_DIAGNOSE.DBA_USER_NAME(D_OWNER#)::VARCHAR(64) = 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.DBA_DEPENDENCIES
(OWNER, NAME, TYPE, REFERENCED_OWNER, REFERENCED_NAME, REFERENCED_TYPE)
AS
SELECT DBE_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' ,16, 'PACKAGE', 17 , 'PACKAGE BODY', 'UNDEFINED'),
DBE_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' ,16, 'PACKAGE', 17 , 'PACKAGE BODY', 'UNDEFINED')
FROM SYS.SYS_DEPENDENCIES D
/
CREATE OR REPLACE VIEW SYS.USER_DEPENDENCIES
(OWNER, NAME, TYPE, REFERENCED_OWNER, REFERENCED_NAME, REFERENCED_TYPE)
AS
SELECT DBE_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' ,16, 'PACKAGE', 17 , 'PACKAGE BODY', 'UNDEFINED'),
DBE_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' ,16, 'PACKAGE', 17 , 'PACKAGE BODY', 'UNDEFINED')
FROM SYS.SYS_DEPENDENCIES D, SYS.DV_ME M
WHERE D.D_OWNER# = M.USER_ID
/
CREATE OR REPLACE VIEW SYS.ALL_DIRECTORIES
(
USER#,
DIRECTORY_NAME,
DIRECTORY_PATH
)
AS
SELECT D.* FROM SYS.SYS_USERS U, SYS.SYS_DIRECTORIES D
WHERE U.ID = D.USER# and
(U.NAME = user or
user = 'SYS' or
DBE_DIAGNOSE.has_obj_privs(user, U.NAME, '"'||D.DIRECTORY_NAME||'"', 'DIRECTORY')) AND DBE_DIAGNOSE.TENANT_CHECK(0, U.TENANT_ID)
/
CREATE OR REPLACE PUBLIC SYNONYM ALL_DIRECTORIES FOR SYS.ALL_DIRECTORIES
/
GRANT SELECT ON ALL_DIRECTORIES TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM USER_SEGMENTS FOR SYS.USER_SEGMENTS
/
GRANT SELECT ON USER_SEGMENTS TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM DBA_SEGMENTS FOR SYS.DBA_SEGMENTS
/
CREATE OR REPLACE PUBLIC SYNONYM ALL_TABLES FOR SYS.ALL_TABLES
/
CREATE OR REPLACE PUBLIC SYNONYM DBA_TABLES FOR SYS.DBA_TABLES
/
GRANT SELECT ON ALL_TABLES TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM ALL_DBLINK_TABLES FOR SYS.ALL_DBLINK_TABLES
/
GRANT SELECT ON ALL_DBLINK_TABLES TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM DBA_DBLINK_TABLES FOR SYS.DBA_DBLINK_TABLES
/
CREATE OR REPLACE PUBLIC SYNONYM USER_TABLES FOR SYS.USER_TABLES
/
GRANT SELECT ON USER_TABLES TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM ALL_TAB_COLS FOR SYS.ALL_TAB_COLS
/
GRANT SELECT ON ALL_TAB_COLS TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM DBA_PART_TABLES FOR SYS.DBA_PART_TABLES
/
CREATE OR REPLACE PUBLIC SYNONYM ALL_PART_TABLES FOR SYS.ALL_PART_TABLES
/
GRANT SELECT ON ALL_PART_TABLES TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM USER_PART_TABLES FOR SYS.USER_PART_TABLES
/
GRANT SELECT ON USER_PART_TABLES TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM DBA_PART_STORE FOR SYS.DBA_PART_STORE
/
CREATE OR REPLACE PUBLIC SYNONYM ALL_PART_STORE FOR SYS.ALL_PART_STORE
/
GRANT SELECT ON ALL_PART_STORE TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM USER_PART_STORE FOR SYS.USER_PART_STORE
/
GRANT SELECT ON USER_PART_STORE TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM DBA_PART_KEY_COLUMNS FOR SYS.DBA_PART_KEY_COLUMNS
/
CREATE OR REPLACE PUBLIC SYNONYM ALL_PART_KEY_COLUMNS FOR SYS.ALL_PART_KEY_COLUMNS
/
GRANT SELECT ON ALL_PART_KEY_COLUMNS TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM USER_PART_KEY_COLUMNS FOR SYS.USER_PART_KEY_COLUMNS
/
GRANT SELECT ON USER_PART_KEY_COLUMNS TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM USER_TAB_COLS FOR SYS.USER_TAB_COLS
/
GRANT SELECT ON USER_TAB_COLS TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM ALL_TAB_COLUMNS FOR SYS.ALL_TAB_COLUMNS
/
GRANT SELECT ON ALL_TAB_COLUMNS TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM DBA_DBLINK_TAB_COLUMNS FOR SYS.DBA_DBLINK_TAB_COLUMNS
/
CREATE OR REPLACE PUBLIC SYNONYM ALL_DBLINK_TAB_COLUMNS FOR SYS.ALL_DBLINK_TAB_COLUMNS
/
GRANT SELECT ON ALL_DBLINK_TAB_COLUMNS TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM USER_TAB_COLUMNS FOR SYS.USER_TAB_COLUMNS
/
GRANT SELECT ON USER_TAB_COLUMNS TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM DBA_TAB_COLUMNS FOR SYS.DBA_TAB_COLUMNS
/
CREATE OR REPLACE PUBLIC SYNONYM ALL_VIEWS FOR SYS.ALL_VIEWS
/
GRANT SELECT ON ALL_VIEWS TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM DBA_VIEWS FOR SYS.DBA_VIEWS
/
CREATE OR REPLACE PUBLIC SYNONYM USER_VIEWS FOR SYS.USER_VIEWS
/
GRANT SELECT ON USER_VIEWS TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM ALL_VIEW_COLUMNS FOR SYS.ALL_VIEW_COLUMNS
/
GRANT SELECT ON ALL_VIEW_COLUMNS TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM USER_VIEW_COLUMNS FOR SYS.USER_VIEW_COLUMNS
/
GRANT SELECT ON USER_VIEW_COLUMNS TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM ALL_INDEXES FOR SYS.ALL_INDEXES
/
GRANT SELECT ON ALL_INDEXES TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM DBA_INDEXES FOR SYS.DBA_INDEXES
/
CREATE OR REPLACE PUBLIC SYNONYM USER_INDEXES FOR SYS.USER_INDEXES
/
GRANT SELECT ON USER_INDEXES TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM ALL_OBJECTS FOR SYS.ALL_OBJECTS
/
GRANT SELECT ON ALL_OBJECTS TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM USER_OBJECTS FOR SYS.USER_OBJECTS
/
GRANT SELECT ON USER_OBJECTS TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM DBA_TABLESPACES FOR SYS.DBA_TABLESPACES
/
CREATE OR REPLACE PUBLIC SYNONYM USER_SYNONYMS FOR SYS.USER_SYNONYMS
/
GRANT SELECT ON USER_SYNONYMS TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM DBA_SYNONYMS FOR SYS.DBA_SYNONYMS
/
CREATE OR REPLACE PUBLIC SYNONYM ALL_SYNONYMS FOR SYS.ALL_SYNONYMS
/
GRANT SELECT ON ALL_SYNONYMS TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM USER_TAB_COMMENTS FOR SYS.USER_TAB_COMMENTS
/
CREATE OR REPLACE PUBLIC SYNONYM ALL_TAB_COMMENTS FOR SYS.ALL_TAB_COMMENTS
/
GRANT SELECT ON USER_TAB_COMMENTS TO PUBLIC
/
GRANT SELECT ON ALL_TAB_COMMENTS TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM DBA_TAB_COMMENTS FOR SYS.DBA_TAB_COMMENTS
/
CREATE OR REPLACE PUBLIC SYNONYM USER_COL_COMMENTS FOR SYS.USER_COL_COMMENTS
/
CREATE OR REPLACE PUBLIC SYNONYM ALL_COL_COMMENTS FOR SYS.ALL_COL_COMMENTS
/
GRANT SELECT ON USER_COL_COMMENTS TO PUBLIC
/
GRANT SELECT ON ALL_COL_COMMENTS TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM DBA_COL_COMMENTS FOR SYS.DBA_COL_COMMENTS
/
CREATE OR REPLACE PUBLIC SYNONYM ALL_USERS FOR SYS.ALL_USERS
/
CREATE OR REPLACE PUBLIC SYNONYM DBA_PROFILES FOR SYS.DBA_PROFILES
/
CREATE OR REPLACE PUBLIC SYNONYM ALL_USER_SYS_PRIVS FOR SYS.ALL_USER_SYS_PRIVS
/
CREATE OR REPLACE PUBLIC SYNONYM DBA_SYS_PRIVS FOR SYS.DBA_SYS_PRIVS
/
CREATE OR REPLACE PUBLIC SYNONYM USER_SYS_PRIVS FOR SYS.USER_SYS_PRIVS
/
GRANT SELECT ON USER_SYS_PRIVS TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM DBA_ROLE_PRIVS FOR SYS.DBA_ROLE_PRIVS
/
CREATE OR REPLACE PUBLIC SYNONYM USER_ROLE_PRIVS FOR SYS.USER_ROLE_PRIVS
/
GRANT SELECT ON USER_ROLE_PRIVS TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM DBA_TAB_PRIVS FOR SYS.DBA_TAB_PRIVS
/
CREATE OR REPLACE PUBLIC SYNONYM USER_TAB_PRIVS FOR SYS.USER_TAB_PRIVS
/
GRANT SELECT ON USER_TAB_PRIVS TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM ALL_SEQUENCES FOR SYS.ALL_SEQUENCES
/
GRANT SELECT ON ALL_SEQUENCES TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM USER_SEQUENCES FOR SYS.USER_SEQUENCES
/
GRANT SELECT ON USER_SEQUENCES TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM DBA_TAB_PARTITIONS FOR SYS.DBA_TAB_PARTITIONS
/
CREATE OR REPLACE PUBLIC SYNONYM ALL_TAB_PARTITIONS FOR SYS.ALL_TAB_PARTITIONS
/
GRANT SELECT ON ALL_TAB_PARTITIONS TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM USER_TAB_PARTITIONS FOR SYS.USER_TAB_PARTITIONS
/
GRANT SELECT ON USER_TAB_PARTITIONS TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM DBA_IND_COLUMNS FOR SYS.DBA_IND_COLUMNS
/
CREATE OR REPLACE PUBLIC SYNONYM ALL_IND_COLUMNS FOR SYS.ALL_IND_COLUMNS
/
CREATE OR REPLACE PUBLIC SYNONYM USER_IND_COLUMNS FOR SYS.USER_IND_COLUMNS
/
GRANT SELECT ON ALL_IND_COLUMNS TO PUBLIC
/
GRANT SELECT ON USER_IND_COLUMNS TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM USER_USERS FOR SYS.USER_USERS
/
GRANT SELECT ON USER_USERS TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM USER_CONSTRAINTS FOR SYS.USER_CONSTRAINTS
/
GRANT SELECT ON USER_CONSTRAINTS TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM ALL_CONSTRAINTS FOR SYS.ALL_CONSTRAINTS
/
GRANT SELECT ON ALL_CONSTRAINTS TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM DBA_CONSTRAINTS FOR SYS.DBA_CONSTRAINTS
/
CREATE OR REPLACE PUBLIC SYNONYM DBA_SOURCE FOR SYS.DBA_SOURCE
/
CREATE OR REPLACE PUBLIC SYNONYM USER_SOURCE FOR SYS.USER_SOURCE
/
CREATE OR REPLACE PUBLIC SYNONYM ALL_SOURCE FOR SYS.ALL_SOURCE
/
GRANT SELECT ON USER_SOURCE TO PUBLIC
/
GRANT SELECT ON ALL_SOURCE TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM DBA_PROCEDURES FOR SYS.DBA_PROCEDURES
/
CREATE OR REPLACE PUBLIC SYNONYM ALL_PROCEDURES FOR SYS.ALL_PROCEDURES
/
GRANT SELECT ON ALL_PROCEDURES TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM DBA_TRIGGERS FOR SYS.DBA_TRIGGERS
/
CREATE OR REPLACE PUBLIC SYNONYM USER_TRIGGERS FOR SYS.USER_TRIGGERS
/
GRANT SELECT ON USER_TRIGGERS TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM USER_CONS_COLUMNS FOR SYS.USER_CONS_COLUMNS
/
GRANT SELECT ON USER_CONS_COLUMNS TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM ALL_TRIGGERS FOR SYS.ALL_TRIGGERS
/
GRANT SELECT ON ALL_TRIGGERS TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM DBA_BACKUP_SET FOR SYS.DBA_BACKUP_SET
/
CREATE OR REPLACE PUBLIC SYNONYM DBA_OBJECTS FOR SYS.DBA_OBJECTS
/
CREATE OR REPLACE PUBLIC SYNONYM DBA_ROLES FOR SYS.DBA_ROLES
/
CREATE OR REPLACE PUBLIC SYNONYM DBA_DATA_FILES FOR SYS.DBA_DATA_FILES
/
CREATE OR REPLACE PUBLIC SYNONYM DBA_USERS FOR SYS.DBA_USERS
/
CREATE OR REPLACE PUBLIC SYNONYM DBA_ARGUMENTS FOR SYS.DBA_ARGUMENTS
/
CREATE OR REPLACE PUBLIC SYNONYM USER_ARGUMENTS FOR SYS.USER_ARGUMENTS
/
GRANT SELECT ON USER_ARGUMENTS TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM ALL_ARGUMENTS FOR SYS.ALL_ARGUMENTS
/
GRANT SELECT ON ALL_ARGUMENTS TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM DBA_FREE_SPACE FOR SYS.DBA_FREE_SPACE
/
CREATE OR REPLACE PUBLIC SYNONYM USER_FREE_SPACE FOR SYS.USER_FREE_SPACE
/
GRANT SELECT ON USER_FREE_SPACE TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM ALL_DEPENDENCIES FOR SYS.ALL_DEPENDENCIES
/
GRANT SELECT ON ALL_DEPENDENCIES TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM DBA_DEPENDENCIES FOR SYS.DBA_DEPENDENCIES
/
CREATE OR REPLACE PUBLIC SYNONYM USER_DEPENDENCIES FOR SYS.USER_DEPENDENCIES
/
GRANT SELECT ON USER_DEPENDENCIES TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM USER_PROCEDURES FOR SYS.USER_PROCEDURES
/
GRANT SELECT ON USER_PROCEDURES TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM DBA_JOBS FOR SYS.DBA_JOBS
/
CREATE OR REPLACE PUBLIC SYNONYM ALL_JOBS FOR SYS.ALL_JOBS
/
CREATE OR REPLACE PUBLIC SYNONYM USER_JOBS FOR SYS.USER_JOBS
/
CREATE OR REPLACE PUBLIC SYNONYM DBA_JOBS_RUNNING FOR SYS.DBA_JOBS_RUNNING
/
CREATE OR REPLACE PUBLIC SYNONYM DBA_PROCEDURES FOR SYS.DBA_PROCEDURES
/
CREATE OR REPLACE PUBLIC SYNONYM DBA_SEQUENCES FOR SYS.DBA_SEQUENCES
/
CREATE OR REPLACE PUBLIC SYNONYM DBA_SOURCE FOR SYS.DBA_SOURCE
/
CREATE OR REPLACE PUBLIC SYNONYM DBA_TAB_COLS FOR SYS.DBA_TAB_COLS
/
CREATE OR REPLACE PUBLIC SYNONYM DBA_VIEW_COLUMNS FOR SYS.DBA_VIEW_COLUMNS
/
CREATE OR REPLACE PUBLIC SYNONYM WSR$SQL_LIST_PLAN FOR SYS.WSR_SQL_LIST_PLAN
/
CREATE OR REPLACE PUBLIC SYNONYM WSR$SQL_LIST FOR SYS.WSR_SQL_LIST
/
CREATE OR REPLACE PUBLIC SYNONYM WSR$_WAITSTAT FOR SYS.WSR_WAITSTAT
/
CREATE OR REPLACE PUBLIC SYNONYM WSR$_SEGMENT FOR SYS.WSR_SEGMENT
/
CREATE OR REPLACE PUBLIC SYNONYM WSR$_SLOWSQL FOR SYS.WSR_SLOWSQL
/
CREATE OR REPLACE PUBLIC SYNONYM WSR$_LIBRARYCACHE FOR SYS.WSR_LIBRARYCACHE
/
CREATE OR REPLACE PUBLIC SYNONYM WSR$_LATCH FOR SYS.WSR_LATCH
/
CREATE OR REPLACE PUBLIC SYNONYM WSR$_DBA_SEGMENTS FOR SYS.WSR_DBA_SEGMENTS
/
CREATE OR REPLACE PUBLIC SYNONYM WRM$_WR_CONTROL FOR SYS.WSR_CONTROL
/
CREATE OR REPLACE PUBLIC SYNONYM WRM$_SNAPSHOT FOR SYS.WSR_SNAPSHOT
/
CREATE OR REPLACE PUBLIC SYNONYM WRH$_SYSTEM_EVENT FOR SYS.WSR_SYSTEM_EVENT
/
CREATE OR REPLACE PUBLIC SYNONYM WRH$_SYSTEM FOR SYS.WSR_SYSTEM
/
CREATE OR REPLACE PUBLIC SYNONYM WRH$_SYSSTAT FOR SYS.WSR_SYS_STAT
/
CREATE OR REPLACE PUBLIC SYNONYM WRH$_SQLAREA FOR SYS.WSR_SQLAREA
/
CREATE OR REPLACE PUBLIC SYNONYM WRH$_PARAMETER FOR SYS.WSR_PARAMETER
/
CREATE OR REPLACE PUBLIC SYNONYM DBA_HIST_WR_CONTROL FOR SYS.ADM_HIST_WR_CONTROL
/
CREATE OR REPLACE PUBLIC SYNONYM DBA_HIST_SNAPSHOT FOR SYS.ADM_HIST_SNAPSHOT
/
CREATE OR REPLACE PUBLIC SYNONYM DBA_HIST_SYSSTAT FOR SYS.ADM_HIST_SYSSTAT
/
CREATE OR REPLACE PUBLIC SYNONYM DBA_HIST_SYSTEM FOR SYS.ADM_HIST_SYSTEM
/
CREATE OR REPLACE PUBLIC SYNONYM DBA_HIST_SYSTEM_EVENT FOR SYS.ADM_HIST_SYSTEM_EVENT
/
CREATE OR REPLACE PUBLIC SYNONYM DBA_HIST_SQLAREA FOR SYS.ADM_HIST_SQLAREA
/
CREATE OR REPLACE PUBLIC SYNONYM DBA_HIST_PARAMETER FOR SYS.ADM_HIST_PARAMETER
/
CREATE OR REPLACE PUBLIC SYNONYM DBA_HIST_WAITSTAT FOR SYS.ADM_HIST_WAITSTAT
/
CREATE OR REPLACE PUBLIC SYNONYM DBA_HIST_LATCH FOR SYS.ADM_HIST_LATCH
/
CREATE OR REPLACE PUBLIC SYNONYM DBA_HIST_LIBRARYCACHE FOR SYS.ADM_HIST_LIBRARYCACHE
/
CREATE OR REPLACE PUBLIC SYNONYM DBA_HIST_SEGMENT FOR SYS.ADM_HIST_SEGMENT
/
CREATE OR REPLACE PUBLIC SYNONYM DBA_HIST_DBASEGMENTS FOR SYS.ADM_HIST_DBASEGMENTS
/
CREATE OR REPLACE PUBLIC SYNONYM DBA_HIST_SLOWSQL FOR SYS.ADM_HIST_SLOWSQL
/
GRANT SELECT ON USER_JOBS TO PUBLIC
/
CREATE OR REPLACE SYNONYM SYS.BACKUP_SET$ FOR SYS.SYS_BACKUP_SETS
/
CREATE OR REPLACE SYNONYM SYS.COLUMN$ FOR SYS.SYS_COLUMNS
/
CREATE OR REPLACE SYNONYM SYS.COMMENT$ FOR SYS.SYS_COMMENTS
/
CREATE OR REPLACE SYNONYM SYS.CONSDEF$ FOR SYS.SYS_CONSTRAINT_DEFS
/
CREATE OR REPLACE SYNONYM SYS.DATA_NODES$ FOR SYS.SYS_DATA_NODES
/
CREATE OR REPLACE SYNONYM SYS.DBA_EXP$TBL_ORDER FOR SYS.EXP_TAB_ORDERS
/
CREATE OR REPLACE SYNONYM SYS.DBA_EXP$TBL_RELATIONS FOR SYS.EXP_TAB_RELATIONS
/
CREATE OR REPLACE SYNONYM SYS.DEPENDENCY$ FOR SYS.SYS_DEPENDENCIES
/
CREATE OR REPLACE SYNONYM SYS.DUAL FOR SYS.SYS_DUMMY
/
CREATE OR REPLACE SYNONYM SYS.EXTERNAL$ FOR SYS.SYS_EXTERNAL_TABLES
/
CREATE OR REPLACE SYNONYM SYS.GARBAGE_SEGMENT$ FOR SYS.SYS_GARBAGE_SEGMENTS
/
CREATE OR REPLACE SYNONYM SYS.HIST_HEAD$ FOR SYS.SYS_HISTGRAM_ABSTR
/
CREATE OR REPLACE SYNONYM SYS.HISTGRAM$ FOR SYS.SYS_HISTGRAM
/
CREATE OR REPLACE SYNONYM SYS.INDEX$ FOR SYS.SYS_INDEXES
/
CREATE OR REPLACE SYNONYM SYS.INDEXPART$ FOR SYS.SYS_INDEX_PARTS
/
CREATE OR REPLACE SYNONYM SYS.JOB$ FOR SYS.SYS_JOBS
/
CREATE OR REPLACE SYNONYM SYS.LINK$ FOR SYS.SYS_LINKS
/
CREATE OR REPLACE SYNONYM SYS.LOB$ FOR SYS.SYS_LOBS
/
CREATE OR REPLACE SYNONYM SYS.LOBPART$ FOR SYS.SYS_LOB_PARTS
/
CREATE OR REPLACE SYNONYM SYS.LOGIC_REP$ FOR SYS.SYS_LOGIC_REPL
/
CREATE OR REPLACE SYNONYM SYS.MON_MODS_ALL$ FOR SYS.SYS_DML_STATS
/
CREATE OR REPLACE SYNONYM SYS.OBJECT_PRIVS$ FOR SYS.SYS_OBJECT_PRIVS
/
CREATE OR REPLACE SYNONYM SYS.PARTCOLUMN$ FOR SYS.SYS_PART_COLUMNS
/
CREATE OR REPLACE SYNONYM SYS.PARTOBJECT$ FOR SYS.SYS_PART_OBJECTS
/
CREATE OR REPLACE SYNONYM SYS.PARTSTORE$ FOR SYS.SYS_PART_STORES
/
CREATE OR REPLACE SYNONYM SYS.PENDING_TRANS$ FOR SYS.SYS_PENDING_TRANS
/
CREATE OR REPLACE SYNONYM SYS.PROC$ FOR SYS.SYS_PROCS
/
CREATE OR REPLACE SYNONYM SYS.PROC_ARGS$ FOR SYS.SYS_PROC_ARGS
/
CREATE OR REPLACE SYNONYM SYS.PROFILE$ FOR SYS.SYS_PROFILE
/
CREATE OR REPLACE SYNONYM SYS.RECYCLEBIN$ FOR SYS.SYS_RECYCLEBIN
/
CREATE OR REPLACE SYNONYM SYS.ROLES$ FOR SYS.SYS_ROLES
/
CREATE OR REPLACE SYNONYM SYS.SEQUENCE$ FOR SYS.SYS_SEQUENCES
/
CREATE OR REPLACE SYNONYM SYS.SHADOW_INDEX$ FOR SYS.SYS_SHADOW_INDEXES
/
CREATE OR REPLACE SYNONYM SYS.SHADOW_INDEXPART$ FOR SYS.SYS_SHADOW_INDEX_PARTS
/
CREATE OR REPLACE SYNONYM SYS.SYNONYM$ FOR SYS.SYS_SYNONYMS
/
CREATE OR REPLACE SYNONYM SYS.SYS_PRIVS$ FOR SYS.SYS_PRIVS
/
CREATE OR REPLACE SYNONYM SYS.TABLE$ FOR SYS.SYS_TABLES
/
CREATE OR REPLACE SYNONYM SYS.TABLEPART$ FOR SYS.SYS_TABLE_PARTS
/
CREATE OR REPLACE SYNONYM SYS.TMP_SEG_STAT$ FOR SYS.SYS_TMP_SEG_STATS
/
CREATE OR REPLACE SYNONYM SYS.USER$ FOR SYS.SYS_USERS
/
CREATE OR REPLACE SYNONYM SYS.USER_HISTORY$ FOR SYS.SYS_USER_HISTORY
/
CREATE OR REPLACE SYNONYM SYS.USER_ROLES$ FOR SYS.SYS_USER_ROLES
/
CREATE OR REPLACE SYNONYM SYS.VIEW$ FOR SYS.SYS_VIEWS
/
CREATE OR REPLACE SYNONYM SYS.VIEWCOL$ FOR SYS.SYS_VIEW_COLS
/
CREATE OR REPLACE SYNONYM SYS.SQL_MAP$ FOR SYS.SYS_SQL_MAPS
/
CREATE OR REPLACE SYNONYM SYS.AUD$ FOR SYS.SYS_AUDIT
/
CREATE OR REPLACE PUBLIC SYNONYM ALL_CONS_COLUMNS FOR SYS.ALL_CONS_COLUMNS
/
GRANT SELECT ON ALL_CONS_COLUMNS TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM DBA_RSRC_PLANS FOR SYS.ADM_RSRC_PLANS
/
CREATE OR REPLACE PUBLIC SYNONYM DBA_RSRC_CONSUMER_GROUPS FOR SYS.ADM_RSRC_CONTROL_GROUPS
/
CREATE OR REPLACE PUBLIC SYNONYM DBA_RSRC_GROUP_MAPPINGS FOR SYS.ADM_RSRC_GROUP_MAPPINGS
/
CREATE OR REPLACE PUBLIC SYNONYM DBA_RSRC_PLAN_DIRECTIVES FOR SYS.ADM_RSRC_PLAN_RULES
/
CREATE OR REPLACE PUBLIC SYNONYM V$RSRC_CONSUMER_GROUP FOR SYS.DV_RSRC_CONTROL_GROUP
/
CREATE OR REPLACE SYNONYM SYS.DIRECTORY$ FOR SYS.SYS_DIRECTORIES
/
CREATE OR REPLACE PUBLIC SYNONYM USER_POLICIES FOR SYS.USER_POLICIES
/
GRANT SELECT ON USER_POLICIES TO PUBLIC
/
CREATE OR REPLACE PUBLIC SYNONYM DBA_POLICIES FOR SYS.DBA_POLICIES
/
CREATE OR REPLACE PUBLIC SYNONYM DBA_REDACTION_POLICIES FOR SYS.DBA_REDACTION_POLICIES
/
CREATE OR REPLACE PUBLIC SYNONYM USER_REDACTION_POLICIES FOR SYS.USER_REDACTION_POLICIES
/
COMMIT
/
