_____________________________________________________________________________________ INDEX ORGANIZED TABLES ====================== select owner, table_name from dba_tables where iot_type is not null and owner not in ('DBSNMP','SYS') order by 1, 2; _____________________________________________________________________________________ HOW TO CHECK IF ACTIVE DATA GUARD IS ALREADY ENABLED ==================================================== select 'Using Active Data Guard' ADG from v$managed_standby m, v$database d where m.process like 'MRP%' and d.open_mode='READ ONLY'; _____________________________________________________________________________________ DATATYPE INFO OF A COLUMN ========================= col data_type for a32 select data_type, char_length, char_used, data_precision, data_scale from dba_tab_columns where owner = 'OWNER_NAME' and table_name = 'TABLE_NAME' and column_name = 'COLUMN_NAME'; _____________________________________________________________________________________ WHAT IS THE CURRENT BUFFER CACHE SIZE? ====================================== select sum(cnum_set * blk_size) bc from x$kcbwds; _____________________________________________________________________________________ WHICH VIEW(S) REFERENCE A PARTICULAR TABLE? =========================================== select * from dba_dependencies where type = 'VIEW' and referenced_type = 'TABLE' and referenced_name = ''; _____________________________________________________________________________________ DATABASE ENVIRONMENT ==================== select SYS_CONTEXT ('USERENV','ACTION') "ACTION", SYS_CONTEXT ('USERENV','AUDITED_CURSORID') "AUDITED_CURSORID", SYS_CONTEXT ('USERENV','AUTHENTICATED_IDENTITY') "AUTHENTICATED_IDENTITY", SYS_CONTEXT ('USERENV','AUTHENTICATION_DATA') "AUTHENTICATION_DATA", SYS_CONTEXT ('USERENV','AUTHENTICATION_METHOD') "AUTHENTICATION_METHOD", SYS_CONTEXT ('USERENV','BG_JOB_ID') "BG_JOB_ID", SYS_CONTEXT ('USERENV','CDB_NAME') "CDB_NAME", SYS_CONTEXT ('USERENV','CLIENT_IDENTIFIER') "CLIENT_IDENTIFIER", SYS_CONTEXT ('USERENV','CLIENT_INFO') "CLIENT_INFO", SYS_CONTEXT ('USERENV','CLIENT_PROGRAM_NAME') "CLIENT_PROGRAM_NAME", SYS_CONTEXT ('USERENV','CON_ID') "CON_ID", SYS_CONTEXT ('USERENV','CON_NAME') "CON_NAME", SYS_CONTEXT ('USERENV','CURRENT_BIND') "CURRENT_BIND", SYS_CONTEXT ('USERENV','CURRENT_EDITION_ID') "CURRENT_EDITION_ID", SYS_CONTEXT ('USERENV','CURRENT_EDITION_NAME') "CURRENT_EDITION_NAME", SYS_CONTEXT ('USERENV','CURRENT_SCHEMA') "CURRENT_SCHEMA", SYS_CONTEXT ('USERENV','CURRENT_SCHEMAID') "CURRENT_SCHEMAID", SYS_CONTEXT ('USERENV','CURRENT_SQL') "CURRENT_SQL", SYS_CONTEXT ('USERENV','CURRENT_SQLn') "CURRENT_SQLn", SYS_CONTEXT ('USERENV','CURRENT_SQL_LENGTH') "CURRENT_SQL_LENGTH", SYS_CONTEXT ('USERENV','CURRENT_USER') "CURRENT_USER", SYS_CONTEXT ('USERENV','CURRENT_USERID') "CURRENT_USERID", SYS_CONTEXT ('USERENV','DATABASE_ROLE') "DATABASE_ROLE", SYS_CONTEXT ('USERENV','DB_DOMAIN') "DB_DOMAIN", SYS_CONTEXT ('USERENV','DB_NAME') "DB_NAME", SYS_CONTEXT ('USERENV','DB_SUPPLEMENTAL_LOG_LEVEL') "DB_SUPPLEMENTAL_LOG_LEVEL", SYS_CONTEXT ('USERENV','DB_UNIQUE_NAME') "DB_UNIQUE_NAME", SYS_CONTEXT ('USERENV','DBLINK_INFO') "DBLINK_INFO", SYS_CONTEXT ('USERENV','ENTRYID') "ENTRYID", SYS_CONTEXT ('USERENV','ENTERPRISE_IDENTITY') "ENTERPRISE_IDENTITY", SYS_CONTEXT ('USERENV','FG_JOB_ID') "FG_JOB_ID", SYS_CONTEXT ('USERENV','GLOBAL_CONTEXT_MEMORY') "GLOBAL_CONTEXT_MEMORY", SYS_CONTEXT ('USERENV','GLOBAL_UID') "GLOBAL_UID", SYS_CONTEXT ('USERENV','HOST') "HOST", SYS_CONTEXT ('USERENV','IDENTIFICATION_TYPE') "IDENTIFICATION_TYPE", SYS_CONTEXT ('USERENV','INSTANCE') "INSTANCE", SYS_CONTEXT ('USERENV','INSTANCE_NAME') "INSTANCE_NAME", SYS_CONTEXT ('USERENV','IP_ADDRESS') "IP_ADDRESS", SYS_CONTEXT ('USERENV','IS_APPLY_SERVER') "IS_APPLY_SERVER", SYS_CONTEXT ('USERENV','IS_DG_ROLLING_UPGRADE') "IS_DG_ROLLING_UPGRADE", SYS_CONTEXT ('USERENV','ISDBA') "ISDBA", SYS_CONTEXT ('USERENV','LANG') "LANG", SYS_CONTEXT ('USERENV','LANGUAGE') "LANGUAGE", SYS_CONTEXT ('USERENV','MODULE') "MODULE", SYS_CONTEXT ('USERENV','NETWORK_PROTOCOL') "NETWORK_PROTOCOL", SYS_CONTEXT ('USERENV','NLS_CALENDAR') "NLS_CALENDAR", SYS_CONTEXT ('USERENV','NLS_CURRENCY') "NLS_CURRENCY", SYS_CONTEXT ('USERENV','NLS_DATE_FORMAT') "NLS_DATE_FORMAT", SYS_CONTEXT ('USERENV','NLS_DATE_LANGUAGE') "NLS_DATE_LANGUAGE", SYS_CONTEXT ('USERENV','NLS_SORT') "NLS_SORT", SYS_CONTEXT ('USERENV','NLS_TERRITORY') "NLS_TERRITORY", SYS_CONTEXT ('USERENV','ORACLE_HOME') "ORACLE_HOME", SYS_CONTEXT ('USERENV','OS_USER') "OS_USER", SYS_CONTEXT ('USERENV','PLATFORM_SLASH') "PLATFORM_SLASH", SYS_CONTEXT ('USERENV','POLICY_INVOKER') "POLICY_INVOKER", SYS_CONTEXT ('USERENV','PROXY_ENTERPRISE_IDENTITY') "PROXY_ENTERPRISE_IDENTITY", SYS_CONTEXT ('USERENV','PROXY_USER') "PROXY_USER", SYS_CONTEXT ('USERENV','PROXY_USERID') "PROXY_USERID", SYS_CONTEXT ('USERENV','SCHEDULER_JOB') "SCHEDULER_JOB", SYS_CONTEXT ('USERENV','SERVER_HOST') "SERVER_HOST", SYS_CONTEXT ('USERENV','SERVICE_NAME') "SERVICE_NAME", SYS_CONTEXT ('USERENV','SESSION_EDITION_ID') "SESSION_EDITION_ID", SYS_CONTEXT ('USERENV','SESSION_EDITION_NAME') "SESSION_EDITION_NAME", SYS_CONTEXT ('USERENV','SESSION_USER') "SESSION_USER", SYS_CONTEXT ('USERENV','SESSION_USERID') "SESSION_USERID", SYS_CONTEXT ('USERENV','SESSIONID') "SESSIONID", SYS_CONTEXT ('USERENV','SID') "SID", SYS_CONTEXT ('USERENV','STATEMENTID') "STATEMENTID", SYS_CONTEXT ('USERENV','TERMINAL') "TERMINAL", SYS_CONTEXT ('USERENV','UNIFIED_AUDIT_SESSIONID') "UNIFIED_AUDIT_SESSIONID" FROM DUAL;