Where, O Where Has That Table Gone?

It’s frustrating to be told to query a table or a view and, upon doing so, receive:

ERROR at line 1:
ORA-00942: table or view does not exist

Especially when you know the table or view in question is part of the standard Oracle database catalogue. It’s there, so why can’t you see it?

It all boils down to one of two things: privilege or the existence of a synonym.

Without at least a select grant on the table or view that’s ‘missing in action’ (either directly or through a role) you’ll never get to view the contents of the table or view in question. And, without a synonym you’ll not be able to access that table/view simply by name. Granted, some organizations don’t allow access to DBA-related views/tables by non-DBA user accounts, but if your company does allow such access (even in a limited scope) reading further will shed some light on what may be the problem and what can be done to correct it.

Let’s take a plain vanilla user account and see what transpires when we try to access data dictionary views without being granted the necessary role or privilege:

SQL> select instance_name from v$instance;
select instance_name from v$instance
                          *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL>

Of course the view V$INSTANCE exists; any DBA can readily view its contents. So the problem must be privileges, or lack thereof. Let’s rectify that issue by granting SELECT_CATALOG_ROLE to our user and trying again [this is NOT a role to be granted lightly or to everyone on the planet as it grants select access to tables and views not every user should be able to query. To recreate that role the following scripts would be necessary:

CREATE ROLE HS_ADMIN_ROLE NOT IDENTIFIED;
GRANT EXECUTE ON  "SYS"."DBMS_HS" TO HS_ADMIN_ROLE;
GRANT EXECUTE ON  "SYS"."DBMS_HS_EXTPROC" TO HS_ADMIN_ROLE;
GRANT SELECT ON  "SYS"."HS_ALL_CAPS" TO HS_ADMIN_ROLE;
GRANT SELECT ON  "SYS"."HS_ALL_DD" TO HS_ADMIN_ROLE;
GRANT SELECT ON  "SYS"."HS_ALL_INITS" TO HS_ADMIN_ROLE;
GRANT SELECT ON  "SYS"."HS_BASE_CAPS" TO HS_ADMIN_ROLE;
GRANT SELECT ON  "SYS"."HS_BASE_DD" TO HS_ADMIN_ROLE;
GRANT SELECT ON  "SYS"."HS_CLASS_CAPS" TO HS_ADMIN_ROLE;
GRANT SELECT ON  "SYS"."HS_CLASS_DD" TO HS_ADMIN_ROLE;
GRANT SELECT ON  "SYS"."HS_CLASS_INIT" TO HS_ADMIN_ROLE;
GRANT SELECT ON  "SYS"."HS_EXTERNAL_OBJECTS" TO HS_ADMIN_ROLE;
GRANT SELECT ON  "SYS"."HS_EXTERNAL_OBJECT_PRIVILEGES" TO HS_ADMIN_ROLE;
GRANT SELECT ON  "SYS"."HS_EXTERNAL_USER_PRIVILEGES" TO HS_ADMIN_ROLE;
GRANT SELECT ON  "SYS"."HS_FDS_CLASS" TO HS_ADMIN_ROLE;
GRANT SELECT ON  "SYS"."HS_FDS_INST" TO HS_ADMIN_ROLE;
GRANT SELECT ON  "SYS"."HS_INST_CAPS" TO HS_ADMIN_ROLE;
GRANT SELECT ON  "SYS"."HS_INST_DD" TO HS_ADMIN_ROLE;
GRANT SELECT ON  "SYS"."HS_INST_INIT" TO HS_ADMIN_ROLE;

CREATE ROLE SELECT_CATALOG_ROLE NOT IDENTIFIED;
GRANT SELECT ON  "SYS"."CODE_PIECES" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."CODE_SIZE" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_2PC_NEIGHBORS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_2PC_PENDING" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_ALL_TABLES" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_ASSOCIATIONS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_AUDIT_EXISTS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_AUDIT_OBJECT" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_AUDIT_SESSION" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_AUDIT_STATEMENT" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_AUDIT_TRAIL" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_CACHEABLE_OBJECTS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_CACHEABLE_TABLES" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_CATALOG" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_CLUSTERS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_CLUSTER_HASH_EXPRESSIONS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_CLU_COLUMNS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_COLL_TYPES" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_COL_COMMENTS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_COL_PRIVS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_CONSTRAINTS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_CONS_COLUMNS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_CONTEXT" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_DATA_FILES" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_DB_LINKS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_DEPENDENCIES" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_DIMENSIONS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_DIM_ATTRIBUTES" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_DIM_CHILD_OF" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_DIM_HIERARCHIES" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_DIM_JOIN_KEY" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_DIM_LEVELS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_DIM_LEVEL_KEY" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_DIRECTORIES" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_DMT_FREE_SPACE" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_DMT_USED_EXTENTS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_ERRORS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_EXP_FILES" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_EXP_OBJECTS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_EXP_VERSION" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_EXTENTS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_FREE_SPACE" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_FREE_SPACE_COALESCED" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_FREE_SPACE_COALESCED_TMP1" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_FREE_SPACE_COALESCED_TMP2" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_FREE_SPACE_COALESCED_TMP3" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_IAS_CONSTRAINT_EXP" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_IAS_GEN_STMTS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_IAS_GEN_STMTS_EXP" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_IAS_OBJECTS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_IAS_OBJECTS_BASE" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_IAS_OBJECTS_EXP" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_IAS_POSTGEN_STMTS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_IAS_PREGEN_STMTS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_IAS_SITES" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_IAS_TEMPLATES" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_INDEXES" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_INDEXTYPES" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_INDEXTYPE_OPERATORS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_IND_COLUMNS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_IND_EXPRESSIONS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_IND_PARTITIONS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_IND_SUBPARTITIONS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_INTERNAL_TRIGGERS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_JOBS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_JOBS_RUNNING" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_LIBRARIES" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_LMT_FREE_SPACE" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_LMT_USED_EXTENTS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_LOBS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_LOB_PARTITIONS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_LOB_SUBPARTITIONS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_METHOD_PARAMS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_METHOD_RESULTS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_MVIEWS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_MVIEW_AGGREGATES" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_MVIEW_ANALYSIS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_MVIEW_DETAIL_RELATIONS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_MVIEW_JOINS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_MVIEW_KEYS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_NESTED_TABLES" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_OBJECTS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_OBJECT_SIZE" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_OBJECT_TABLES" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_OBJ_AUDIT_OPTS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_OPANCILLARY" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_OPARGUMENTS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_OPBINDINGS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_OPERATORS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_OUTLINES" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_OUTLINE_HINTS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_PARTIAL_DROP_TABS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_PART_COL_STATISTICS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_PART_HISTOGRAMS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_PART_INDEXES" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_PART_KEY_COLUMNS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_PART_LOBS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_PART_TABLES" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_PENDING_TRANSACTIONS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_POLICIES" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_PRIV_AUDIT_OPTS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_PROFILES" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_QUEUES" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_QUEUE_SCHEDULES" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_QUEUE_TABLES" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_RCHILD" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_REFRESH" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_REFRESH_CHILDREN" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_REFS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_REGISTERED_SNAPSHOTS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_REGISTERED_SNAPSHOT_GROUPS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_REPAUDIT_ATTRIBUTE" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_REPAUDIT_COLUMN" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_REPCAT" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_REPCATLOG" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_REPCAT_REFRESH_TEMPLATES" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_REPCAT_TEMPLATE_OBJECTS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_REPCAT_TEMPLATE_PARMS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_REPCAT_TEMPLATE_SITES" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_REPCAT_USER_AUTHORIZATIONS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_REPCAT_USER_PARM_VALUES" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_REPCOLUMN" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_REPCOLUMN_GROUP" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_REPCONFLICT" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_REPDDL" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_REPFLAVORS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_REPFLAVOR_COLUMNS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_REPFLAVOR_OBJECTS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_REPGENERATED" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_REPGENOBJECTS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_REPGROUP" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_REPGROUPED_COLUMN" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_REPGROUP_PRIVILEGES" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_REPKEY_COLUMNS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_REPOBJECT" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_REPPARAMETER_COLUMN" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_REPPRIORITY" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_REPPRIORITY_GROUP" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_REPPROP" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_REPRESOLUTION" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_REPRESOLUTION_METHOD" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_REPRESOLUTION_STATISTICS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_REPRESOL_STATS_CONTROL" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_REPSCHEMA" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_REPSITES" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_RGROUP" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_ROLE;S" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_ROLE;_PRIVS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_ROLLBACK_SEGS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_RSRC_CONSUMER_GROUPS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_RSRC_CONSUMER_GROUP_PRIVS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_RSRC_MANAGER_SYSTEM_PRIVS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_RSRC_PLANS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_RSRC_PLAN_DIRECTIVES" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_RULESETS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_SEGMENTS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_SEQUENCES" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_SNAPSHOTS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_SNAPSHOT_LOGS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_SNAPSHOT_LOG_FILTER_COLS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_SNAPSHOT_REFRESH_TIMES" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_SOURCE" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_STMT_AUDIT_OPTS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_SUBPART_COL_STATISTICS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_SUBPART_HISTOGRAMS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_SUBPART_KEY_COLUMNS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_SUMMARIES" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_SUMMARY_AGGREGATES" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_SUMMARY_DETAIL_TABLES" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_SUMMARY_JOINS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_SUMMARY_KEYS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_SYNONYMS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_SYS_PRIVS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_TABLES" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_TABLESPACES" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_TAB_COLUMNS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_TAB_COL_STATISTICS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_TAB_COMMENTS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_TAB_HISTOGRAMS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_TAB_MODIFICATIONS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_TAB_PARTITIONS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_TAB_PRIVS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_TAB_SUBPARTITIONS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_TEMP_FILES" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_TRIGGERS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_TRIGGER_COLS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_TS_QUOTAS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_TYPES" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_TYPE_ATTRS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_TYPE_METHODS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_UNUSED_COL_TABS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_UPDATABLE_COLUMNS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_USERS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_USTATS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_VARRAYS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DBA_VIEWS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DEFCALL" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DEFCALLDEST" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DEFDEFAULTDEST" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DEFERRCOUNT" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DEFERROR" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DEFLOB" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DEFPROPAGATOR" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DEFSCHEDULE" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DEFTRAN" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."DEFTRANDEST" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."ERROR_SIZE" TO SELECT_CATALOG_ROLE;
--
-- EXP/IMP views
--
-- These views 'power' the traditional EXP/IMP utilities
-- 
-- Will be release-specific which will be indicated
-- in the table name
--
-- EXU8 views found in release 8.0 and apply to that release 
-- and all later releases unless replaced by a later version
--
-- EXU81 found in 8.1.0 and later releases, replacing  
-- the corresponding EXU8 views
--
-- Supplied for backward compatibility in all current releases
-- of Oracle
-- 
-- These views, if found invalid, can be recreated with the
-- $ORACLE_HOME/rdbms/admin/catexp.sql script
-- Please read here for instructions
--
GRANT SELECT ON  "SYS"."EXU816SQV" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU816TCTX" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU816TGR" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU816TGRC" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU816TGRI" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU816TGRIC" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU81CTX" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU81DOI" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU81FIL" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU81INDC" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU81INDI" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU81INDIC" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU81ITY" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU81ITYC" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU81ITYI" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU81IXCP" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU81IXSP" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU81JAVC" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU81JAVI" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU81LBCP" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU81LBP" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU81LBSP" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU81OBJ" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU81OPR" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU81OPRC" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU81OPRI" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU81PLB" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU81PSTC" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU81PSTI" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU81REFC" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU81REFI" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU81RGC" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU81RGS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU81SCM" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU81SLFC" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU81SNAP" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU81SNAPC" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU81SNAPI" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU81SNAPL" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU81SNAPLC" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU81SNAPLI" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU81SPOK" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU81SPOKI" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU81SRT" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU81TAB" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU81TABC" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU81TABI" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU81TABS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU81TBCP" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU81TBS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU81TBSP" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU81TGR" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU81TGRC" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU81TGRI" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU81TGRIC" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU81TNTC" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU81TNTI" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU81TTS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU81TYP" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU81USCI" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8ANAL" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8ASC" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8AUD" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8CCL" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8CCO" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8CGR" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8CLU" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8CLUC" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8CLUI" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8CMT" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8COE" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8COL" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8COLNN" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8COL_TEMP" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8CON" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8COO" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8CSN" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8DEL" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8DELSNAP" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8DELSNAPL" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8DFR" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8DIMC" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8DIMI" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8DIR" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8ERC" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8FIL" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8FPT" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8GRN" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8GRS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8HST" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8ICO" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8IND" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8INDC" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8INDI" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8INDIC" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8INK" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8IOV" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8IXP" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8JBQ" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8LIB" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8LNK" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8LOB" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8NTB" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8NXP" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8OID" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8PDS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8PHS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8PNT" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8POK" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8POKI" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8PRF" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8PRR" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8PST" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8PSTC" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8PSTI" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8PSTIC" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8PVF" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8REF" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8REFC" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8REFI" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8REFIC" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8RFS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8RGC" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8RGS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8RIF" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8RLG" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8ROL" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8RSG" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8SCM" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8SEQ" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8SLFC" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8SLOG" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8SNAP" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8SNAPC" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8SNAPI" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8SNAPL" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8SNAPLC" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8SNAPLI" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8SPR" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8SPRC" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8SPRI" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8SPS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8SPV" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8SRT" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8STO" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8SYN" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8SYNC" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8SYNI" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8TAB" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8TABC" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8TABI" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8TBP" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8TBS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8TGR" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8TGRC" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8TGRI" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8TGRIC" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8TNTC" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8TNTI" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8TNTIC" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8TSL" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8TSN" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8TSQ" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8TYP" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8TYPB" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8TYPT" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8USR" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8VDPT" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8VEW" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8VEWC" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8VEWI" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8VINF" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8VINFC" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8VINFI" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8VNC" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8VNCC" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."EXU8VNCI" TO SELECT_CATALOG_ROLE;
--
-- End of EXP/IMP views
--
GRANT SELECT ON  "SYS"."FILEXT$" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$ACCESS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$AQ" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$AQ1" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$ARCHIVE" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$ARCHIVED_LOG" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$ARCHIVE_DEST" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$ARCHIVE_PROCESSES" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$BACKUP" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$BACKUP_ASYNC_IO" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$BACKUP_CORRUPTION" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$BACKUP_DATAFILE" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$BACKUP_DEVICE" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$BACKUP_PIECE" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$BACKUP_REDOLOG" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$BACKUP_SET" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$BACKUP_SYNC_IO" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$BGPROCESS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$BSP" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$BUFFER_POOL" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$BUFFER_POOL_STATISTICS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$CIRCUIT" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$CLASS_PING" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$COMPATIBILITY" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$COMPATSEG" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$CONTEXT" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$CONTROLFILE" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$CONTROLFILE_RECORD_SECTION" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$COPY_CORRUPTION" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$DATABASE" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$DATAFILE" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$DATAFILE_COPY" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$DATAFILE_HEADER" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$DBFILE" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$DBLINK" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$DB_CACHE_ADVICE" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$DB_OBJECT_CACHE" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$DB_PIPES" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$DELETED_OBJECT" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$DISPATCHER" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$DISPATCHER_RATE" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$DLM_ALL_LOCKS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$DLM_CONVERT_LOCAL" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$DLM_CONVERT_REMOTE" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$DLM_LATCH" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$DLM_LOCKS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$DLM_MISC" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$DLM_RESS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$DLM_TRAFFIC_CONTROLLER" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$ENABLEDPRIVS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$ENQUEUE_LOCK" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$EVENT_NAME" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$EXECUTION" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$FAST_START_SERVERS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$FAST_START_TRANSACTIONS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$FILESTAT" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$FILE_PING" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$FIXED_TABLE" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$FIXED_VIEW_DEFINITION" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$GLOBAL_BLOCKED_LOCKS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$GLOBAL_TRANSACTION" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$HS_AGENT" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$HS_PARAMETER" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$HS_SESSION" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$INDEXED_FIXED_COLUMN" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$INSTANCE" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$INSTANCE_RECOVERY" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$LATCH" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$LATCHHOLDER" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$LATCHNAME" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$LATCH_CHILDREN" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$LATCH_MISSES" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$LATCH_PARENT" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$LIBRARYCACHE" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$LICENSE" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$LOCK" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$LOCKED_OBJECT" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$LOCKS_WITH_COLLISIONS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$LOCK_ELEMENT" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$LOG" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$LOGFILE" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$LOGHIST" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$LOGMNR_CONTENTS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$LOGMNR_DICTIONARY" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$LOGMNR_LOGS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$LOGMNR_PARAMETERS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$LOG_HISTORY" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$MTS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$MYSTAT" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$OBJECT_DEPENDENCY" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$OBSOLETE_PARAMETER" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$OFFLINE_RANGE" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$OPEN_CURSOR" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$PARAMETER" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$PARAMETER2" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$PQ_SLAVE" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$PQ_SYSSTAT" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$PROCESS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$PROXY_ARCHIVEDLOG" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$PROXY_DATAFILE" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$PWFILE_USERS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$PX_PROCESS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$PX_PROCESS_SYSSTAT" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$PX_SESSION" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$PX_SESSTAT" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$QUEUE" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$RECOVERY_FILE_STATUS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$RECOVERY_LOG" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$RECOVERY_PROGRESS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$RECOVERY_STATUS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$RECOVER_FILE" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$REQDIST" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$RESERVED_WORDS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$RESOURCE" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$RESOURCE_LIMIT" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$ROLLSTAT" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$ROWCACHE" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$ROWCACHE_PARENT" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$ROWCACHE_SUBORDINATE" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$SESSION" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$SESSION_CONNECT_INFO" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$SESSION_CURSOR_CACHE" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$SESSION_EVENT" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$SESSION_OBJECT_CACHE" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$SESSION_WAIT" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$SESSTAT" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$SESS_IO" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$SGA" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$SGASTAT" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$SHARED_POOL_RESERVED" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$SHARED_SERVER" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$SORT_SEGMENT" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$SORT_USAGE" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$SQL" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$SQLAREA" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$SQLTEXT" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$SQLTEXT_WITH_NEWLINES" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$SQL_BIND_DATA" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$SQL_BIND_METADATA" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$SQL_CURSOR" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$SQL_SHARED_CURSOR" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$SQL_SHARED_MEMORY" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$STATNAME" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$SUBCACHE" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$SYSSTAT" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$SYSTEM_CURSOR_CACHE" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$SYSTEM_EVENT" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$SYSTEM_PARAMETER" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$SYSTEM_PARAMETER2" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$TABLESPACE" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$TARGETRBA" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$TEMPFILE" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$TEMPSTAT" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$TEMP_EXTENT_MAP" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$TEMP_EXTENT_POOL" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$TEMP_PING" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$TEMP_SPACE_HEADER" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$THREAD" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$TIMER" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$TRANSACTION" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$TRANSACTION_ENQUEUE" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$TYPE_SIZE" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$WAITSTAT" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."GV_$_LOCK" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."IMP8CDT" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."IMP8CON" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."IMP8TTD" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."ORA_KGLR7_OBJECTS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."PARSED_PIECES" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."PARSED_SIZE" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."PLUGGABLE_SET_CHECK" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."PROXY_USERS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."PS1$" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."PSS1$" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."REPCAT$_CDEF" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."REPCAT_GENERATED" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."REPCAT_REPCAT" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."REPCAT_REPCATLOG" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."REPCAT_REPOBJECT" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."REPCAT_REPOBJECT_BASE" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."REPCAT_REPPROP" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."REPCAT_REPSCHEMA" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."SM$AUDIT_CONFIG" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."SM$INTEGRITY_CONS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."SM$TS_AVAIL" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."SM$TS_FREE" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."SM$TS_USED" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."SOURCE_SIZE" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."STRADDLING_RS_OBJECTS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."STRADDLING_TS_OBJECTS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."SYSCATALOG_" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."SYS_DBA_SEGS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."SYS_OBJECTS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."SYS_USER_SEGS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."TP$" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."TRUSTED_SERVERS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."TSP$" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."TS_PITR_CHECK" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."TS_PITR_INFO" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."TS_PITR_OBJECTS_TO_BE_DROPPED" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."TS_PLUG_INFO" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$ACCESS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$AQ" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$AQ1" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$ARCHIVE" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$ARCHIVED_LOG" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$ARCHIVE_DEST" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$ARCHIVE_PROCESSES" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$BACKUP" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$BACKUP_ASYNC_IO" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$BACKUP_CORRUPTION" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$BACKUP_DATAFILE" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$BACKUP_DEVICE" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$BACKUP_PIECE" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$BACKUP_REDOLOG" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$BACKUP_SET" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$BACKUP_SYNC_IO" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$BGPROCESS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$BSP" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$BUFFER_POOL" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$BUFFER_POOL_STATISTICS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$CIRCUIT" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$CLASS_PING" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$COMPATIBILITY" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$COMPATSEG" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$CONTEXT" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$CONTROLFILE" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$CONTROLFILE_RECORD_SECTION" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$COPY_CORRUPTION" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$DATABASE" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$DATAFILE" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$DATAFILE_COPY" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$DATAFILE_HEADER" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$DBFILE" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$DBLINK" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$DB_CACHE_ADVICE" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$DB_OBJECT_CACHE" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$DB_PIPES" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$DELETED_OBJECT" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$DISPATCHER" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$DISPATCHER_RATE" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$DLM_ALL_LOCKS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$DLM_CONVERT_LOCAL" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$DLM_CONVERT_REMOTE" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$DLM_LATCH" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$DLM_LOCKS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$DLM_MISC" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$DLM_RESS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$DLM_TRAFFIC_CONTROLLER" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$ENABLEDPRIVS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$ENQUEUE_LOCK" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$EVENT_NAME" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$EXECUTION" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$FAST_START_SERVERS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$FAST_START_TRANSACTIONS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$FILESTAT" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$FILE_PING" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$FIXED_TABLE" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$FIXED_VIEW_DEFINITION" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$GLOBAL_BLOCKED_LOCKS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$GLOBAL_TRANSACTION" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$HS_AGENT" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$HS_PARAMETER" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$HS_SESSION" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$INDEXED_FIXED_COLUMN" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$INSTANCE" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$INSTANCE_RECOVERY" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$KCCDI" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$KCCFE" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$LATCH" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$LATCHHOLDER" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$LATCHNAME" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$LATCH_CHILDREN" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$LATCH_MISSES" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$LATCH_PARENT" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$LIBRARYCACHE" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$LICENSE" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$LOCK" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$LOCKED_OBJECT" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$LOCKS_WITH_COLLISIONS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$LOCK_ELEMENT" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$LOG" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$LOGFILE" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$LOGHIST" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$LOGMNR_CONTENTS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$LOGMNR_DICTIONARY" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$LOGMNR_LOGS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$LOGMNR_PARAMETERS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$LOG_HISTORY" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$MTS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$MYSTAT" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$OBJECT_DEPENDENCY" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$OBSOLETE_PARAMETER" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$OFFLINE_RANGE" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$OPEN_CURSOR" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$PARAMETER" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$PARAMETER2" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$PQ_SLAVE" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$PQ_SYSSTAT" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$PROCESS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$PROXY_ARCHIVEDLOG" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$PROXY_DATAFILE" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$PWFILE_USERS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$PX_PROCESS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$PX_PROCESS_SYSSTAT" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$PX_SESSION" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$PX_SESSTAT" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$QUEUE" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$RECOVERY_FILE_STATUS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$RECOVERY_LOG" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$RECOVERY_PROGRESS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$RECOVERY_STATUS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$RECOVER_FILE" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$REQDIST" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$RESERVED_WORDS" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$RESOURCE" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$RESOURCE_LIMIT" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$ROLLNAME" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$ROLLSTAT" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$ROWCACHE" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$ROWCACHE_PARENT" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$ROWCACHE_SUBORDINATE" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$SESSION" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$SESSION_CURSOR_CACHE" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$SESSION_EVENT" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$SESSION_OBJECT_CACHE" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$SESSION_WAIT" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$SESSTAT" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$SESS_IO" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$SGA" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$SGASTAT" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$SHARED_POOL_RESERVED" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$SHARED_SERVER" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$SORT_SEGMENT" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$SORT_USAGE" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$SQL" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$SQLAREA" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$SQLTEXT" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$SQLTEXT_WITH_NEWLINES" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$SQL_BIND_DATA" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$SQL_BIND_METADATA" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$SQL_CURSOR" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$SQL_SHARED_CURSOR" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$SQL_SHARED_MEMORY" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$STATNAME" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$SUBCACHE" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$SYSSTAT" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$SYSTEM_CURSOR_CACHE" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$SYSTEM_EVENT" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$SYSTEM_PARAMETER" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$SYSTEM_PARAMETER2" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$TABLESPACE" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$TARGETRBA" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$TEMPFILE" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$TEMPSTAT" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$TEMP_EXTENT_MAP" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$TEMP_EXTENT_POOL" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$TEMP_PING" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$TEMP_SPACE_HEADER" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$THREAD" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$TIMER" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$TRANSACTION" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$TRANSACTION_ENQUEUE" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$TYPE_SIZE" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$WAITSTAT" TO SELECT_CATALOG_ROLE;
GRANT SELECT ON  "SYS"."V_$_LOCK" TO SELECT_CATALOG_ROLE;
GRANT "HS_ADMIN_ROLE;" TO SELECT_CATALOG_ROLE;

It’s used here ONLY to illustrate the issue and the possible solutions]:

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
asdfjkl

SQL>

Since a public synonym exists in every Oracle database for the V$INSTANCE view all that was necessary was to grant select privileges on either the V$INSTANCE view alone or grant the user the SELECT_CATALOG_ROLE (as we did here). Voila! Instant access!

But, what if our user is granted the necessary access and STILL gets the obnoxious ORA-00942 error? Let’s look at a relatively obscure SYS view that SELECT_CATALOG_ROLE provides access to:

SQL> select * from ku$_index_view;
select * from ku$_index_view
              *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL>

Gee, we can’t find that view by name, at least. We know SYS owns it, so let’s see if using a qualified name makes a difference:

SQL> select * from sys.ku$_index_view;

[oodles and scads of data returned]

SQL>

It does, so, darn, it must be a missing synonym … let’s fix that problem:

SQL> create synonym ku$_index_view for sys.ku$_index_view;

Synonym created.

SQL>SQL> select * from ku$_index_view;

[oodles and scads of data returned]

SQL>

So, we see that it could be one of three issues causing an ORA-00942 error:

* The OBVIOUS reason: no table or view by that name exists in the database (but you know otherwise which brought you here)

* Less obvious: you have no select privileges on that table/view

* Even less obvious: a synonym for that table/view doesn’t exist presuming you do have privileges on the table/view

You can discover if you do have privileges on a table not in your schema by querying the ALL_TABLES view; a similar query can be run against ALL_VIEWS to see the same information on views not owned by you. If the table or view is listed, but access by name fails, you need a synonym created.

Hopefully this discussion has provided the tools necessary to investigate the situation when this error arises, and can provide information to supply to the DBA so he/she can either:

Tell you why such access isn’t allowed for non-DBA users, or

Allow he/she to grant the necessary privileges and create the necessary synonyms so you can have access.

Not every user is destined to access every table or view in an Oracle database, but when one non-DBA user can access a catalog table/view that you cannot you can determine what is missing and inform your DBA or manager (you really want to follow the chain of command with such issues). Oh, and the same problems can plague access to application tables/views, so you may need to contact your application support person to obtain access (presuming, again, that no sensitive information is contained in those tables/views).

Suffice it to say this can be a ‘hot button’ in some organizations, so follow the chain of command when making such access requests, no matter what table or view you feel you need privileges on. There may be valid reasons why you aren’t allowed to see that particular data.

Now, get back to work. 😀

Locks (No bagels)

Our intrepid blogger of a few posts back claims the following:

“1. How do you know What are the objects have locks??

Solution:

select * from v$lock where block<>0;

if block=0 then no locks if block=1 then there is locks”

Unfortunately the V$LOCK view contains no object information whatsoever. Also, simply because BLOCK is 0 does not mean there are no locks present, simply that none of the existing locks are blocking anyone. Executing the query supplied against a 10.2.0.3 database provides the following output:

SQL> select * From v$lock where block <> 0;

no rows selected

SQL>

which, according to the ‘information’ supplied says no locks exist in the database. Nothing could be further from the truth; querying V$LOCK again, absent the supplied WHERE clause, reveals:

SQL> select * From v$lock;

ADDR             KADDR                   SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
070000003B8AF020 070000003B8AF040        165 XR          4          0          1          0     130060          0
070000003B8AF0B8 070000003B8AF0D8        165 CF          0          0          2          0     130060          0
070000003B8AF1E8 070000003B8AF208        165 RS         25          1          2          0     130055          0
070000003B8AF318 070000003B8AF338        166 RT          1          0          6          0     130055          0
070000003B8AF4E0 070000003B8AF500        164 TS          3          1          3          0     130049          0
070000003B8AF6A8 070000003B8AF6C8        167 MR         20          0          4          0     130052          0
070000003B8AF740 070000003B8AF760        167 MR          1          0          4          0     130052          0
070000003B8AF7D8 070000003B8AF7F8        167 MR          2          0          4          0     130052          0
070000003B8AF870 070000003B8AF890        167 MR          3          0          4          0     130052          0
070000003B8AF908 070000003B8AF928        167 MR          4          0          4          0     130052          0
070000003B8AF9B8 070000003B8AF9D8        167 MR          5          0          4          0     130052          0
070000003B8AFA50 070000003B8AFA70        167 MR          6          0          4          0     130052          0
070000003B8AFAE8 070000003B8AFB08        167 MR          7          0          4          0     130052          0
070000003B8AFB80 070000003B8AFBA0        167 MR          8          0          4          0     130052          0
070000003B8AFC18 070000003B8AFC38        167 MR          9          0          4          0     130052          0
070000003B8AFCB0 070000003B8AFCD0        167 MR         10          0          4          0     130052          0
070000003B8AFD48 070000003B8AFD68        167 MR         11          0          4          0     130052          0
070000003B8AFDE0 070000003B8AFE00        167 MR         12          0          4          0     130052          0
070000003B8AFE78 070000003B8AFE98        167 MR         13          0          4          0     130052          0
070000003B8AFF10 070000003B8AFF30        167 MR         14          0          4          0     130052          0
070000003B8AFFA8 070000003B8AFFC8        167 MR         15          0          4          0     130052          0
070000003B8B0040 070000003B8B0060        167 MR         16          0          4          0     130052          0
070000003B8B00D8 070000003B8B00F8        167 MR         17          0          4          0     130052          0
070000003B8B0170 070000003B8B0190        167 MR         18          0          4          0     130052          0
070000003B8B0208 070000003B8B0228        167 MR         19          0          4          0     130052          0
070000003B8B02A0 070000003B8B02C0        167 MR         21          0          4          0     130052          0
070000003B8B0350 070000003B8B0370        167 MR         22          0          4          0     130052          0
070000003B8B03E8 070000003B8B0408        167 MR         23          0          4          0     130052          0
070000003B8B0480 070000003B8B04A0        167 MR         24          0          4          0     130052          0
070000003B8B0518 070000003B8B0538        167 MR         25          0          4          0     130052          0
070000003B8B05B0 070000003B8B05D0        167 MR         26          0          4          0     130052          0
070000003B8B0648 070000003B8B0668        167 MR         27          0          4          0     130052          0
070000003B8B06E0 070000003B8B0700        167 MR         28          0          4          0     130052          0
070000003B8B0778 070000003B8B0798        167 MR         29          0          4          0     130052          0
070000003B8B0810 070000003B8B0830        167 MR        201          0          4          0     130052          0

35 rows selected.

SQL>

Gee, there are 35 locks existing in the database; of course none of which are blocking anyone (30 of the locks are media recovery locks* with the remaining 5 locks identified as follows: CF — Control file enqueue lock, RS — row shared lock, RT — Redo thread enqueue, TS — Temporary segment enqueue lock, XR — Forced logging enqueue due to checkpoints, I suspect). Which doesn’t mean they don’t exist, just that no one is waiting on one or more of them to be released so that the transaction in waiting can complete. [The MR locks are associated with the data files and temp file associated with the database in question, one per file.]

* – Media recovery locks are held for one of two reasons: to prevent anyone from bringing a tablespace online which is under recovery or to prevent someone from attempting recovery on an online tablespace. Thus they are always present in a database as a protection mechanism.

Of course this was not enough misinformation to provide the user community so this poor soul offers this nugget of wisdom:

“2. How do you know locked tables? and how do you remove locks?

Solution:

1. select a.object_id, a.session_id, substr(b.object_name, 1, 40) from v$locked_object a, dba_objects b where a.object_id = b.object_id order by b.object_name ;

2. select sid, serial#, command, taddr from v$session where sid= <sid>;

3. alter system kill session ”<sid,serial#>;”

My, what wonderful and considerate advice: just kill whatever session is blocking someone else, whether or not that session has completed its task and without any notification to the soon-to-be affected user. To act in such an irrational and arbitrary manner is wrong, to say the least. Yes, there MAY be situations where such action is warranted but, invariably, such action follows user complaints and/or physical problems not resolvable in any other way. A power outage (no matter how small) may render users unable to re-connect as the prior sessions still exist in the database. [This would usually be an application issue, although if resource profiles are in use this could generate an Oracle error.] Such is one valid case for simply killing sessions to free resources; remember, though, that a complaint was lodged regarding the inability to perform necessary work and that notice was given regarding said act. Another might be that an application, due to network or client resource problems, appeared to ‘hang’ while processing a transaction and the end-user simply clicked on the red ‘X’ in the upper right hand corner of the window, killing the local process but leaving the database session still in existence. Killing the offending session in such a scenario would be a valid course of action, again preceded by notification from the user community. But, to kill sessions simply because they lock an object or objects (for even the smallest length of time) that YOU want to access is irresponsible.

The Internet is an interesting place, full of wit, wisdom, humour and, unfortunately, plenty of mis-information. Myths abound, and half-truths become gospel truths simply because they are found there. Such could be the fate of these examples of ‘a little knowledge can be a dangerous thing.’ And, truly, the latter of the two examples is a dangerous piece of ‘advice’, based solely upon the author’s ill-conceived notion of locks and locking in an Oracle database. Of course, if your goal is to madden the user community at large and make it virtually impossible to perform any meaningful work then, by all means, follow his recommendation. Of course if you do then don’t be surprised when a ‘pink slip’ from HR lands upon your desk, as those who blindly follow such instructions invariably find themselves standing in the unemployment line.

To "b" or not to "b"

With the plethora of database-centric applications available today, and with the performance problems they can generate it can be a worthy effort to determine which vendor-created indexes are and are not being used. (This is especially helpful if you’re working closely with the application vendor to improve their product.) Of course one way to do this is to set event 10046 at level 8 or 12 and let the trace files fly so they can be analyzed later for which indexes are being used by the application queries. And that could be a long and tedious process. One would think there is a better way to accomplish this.

There is.

Oh, I suppose you’d like to know this better way … it’s really rather simple:

Let Oracle do the work for you.

So let’s see how we tell Oracle to do this task for us so our job is much easier.

Oracle has provided a mechanism (since at least Oracle 8.1.6) to monitor an index for usage using

alter index  monitoring usage;

The results of that monitoring are found in the V$OBJECT_USAGE view,in a column, strangely enough, named USED. This isn’t a long, boring thesis on how, when, where, who and why the index in question was used, only that it either is or is not used. The ‘window’ spans the time period starting with the execution of the above-listed command and ends when the following is issued:

alter index  nomonitoring usage;

The data remains in the V$OBJECT_USAGE view until another monitoring ‘window’ is started.

So, let’s see an example of how this works. We’ll use the EMP table from the SCOTT/TIGER demonstration schema:

SQL>
SQL> --
SQL> -- Create an index on the EMPNO column
SQL> -- of the EMP table
SQL> --
SQL> create index emp_eno_idx
  2  on emp(empno);

Index created.

SQL>
SQL> --
SQL> -- Let's monitor the index to see if
SQL> -- it's being used
SQL> --
SQL> alter index emp_eno_idx monitoring usage;

Index altered.

SQL>
SQL> --
SQL> -- Now, let's run some queries
SQL> --
SQL> -- First, let's get everything from the
SQL> -- EMP table
SQL> --
SQL> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 09-DEC-82       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.

SQL>
SQL> --
SQL> -- Obviously the index hasn't yet been
SQL> -- used
SQL> --
SQL> select index_name, table_name, used from v$object_usage;

INDEX_NAME                     TABLE_NAME                     USE
------------------------------ ------------------------------ ---
EMP_ENO_IDX                    EMP                            NO

1 row selected.

SQL>
SQL> --
SQL> -- So let's run a qualified query and
SQL> -- see if things change
SQL> --
SQL> -- Since the DEPTNO column isn't indexed
SQL> -- the monitored index still shouldn't be
SQL> -- used
SQL> --
SQL> select * from emp where deptno = 30;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30

6 rows selected.

SQL>
SQL> --
SQL> -- And we see it isn't
SQL> --
SQL> select index_name, table_name, used from v$object_usage;

INDEX_NAME                     TABLE_NAME                     USE
------------------------------ ------------------------------ ---
EMP_ENO_IDX                    EMP                            NO

1 row selected.

SQL>
SQL> --
SQL> -- Yet another qualified query, this time
SQL> -- using the indexed column
SQL> --
SQL>  select * from emp where empno > 7000;

     EMPNO ENAME      JOB	       MGR HIREDATE	    SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK	      7902 17-DEC-80	    800 	 0	   20
      7499 ALLEN      SALESMAN	      7698 20-FEB-81	   1600        300	   30
      7521 WARD       SALESMAN	      7698 22-FEB-81	   1250        500	   30
      7566 JONES      MANAGER	      7839 02-APR-81	   2975 	 0	   20
      7654 MARTIN     SALESMAN	      7698 28-SEP-81	   1250       1400	   30
      7698 BLAKE      MANAGER	      7839 01-MAY-81	   2850 	 0	   30
      7782 CLARK      MANAGER	      7839 09-JUN-81	   2450 	 0	   10
      7788 SCOTT      ANALYST	      7566 19-APR-87	   3000 	 0	   20
      7839 KING       PRESIDENT 	   17-NOV-81	   5000 	 0	   10
      7844 TURNER     SALESMAN	      7698 08-SEP-81	   1500 	 0	   30
      7876 ADAMS      CLERK	      7788 23-MAY-87	   1100 	 0	   20

     EMPNO ENAME      JOB	       MGR HIREDATE	    SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7900 JAMES      CLERK	      7698 03-DEC-81	    950 	 0	   30
      7902 FORD       ANALYST	      7566 03-DEC-81	   3000 	 0	   20
      7934 MILLER     CLERK	      7782 23-JAN-82	   1300 	 0	   10

14 rows selected.

SQL> -- We see the index is now being used, or at
SQL> -- least it was for that last query
SQL> --
SQL> select index_name, table_name, used from v$object_usage;

INDEX_NAME                     TABLE_NAME                     USE
------------------------------ ------------------------------ ---
EMP_ENO_IDX                    EMP                            YES

1 row selected.

SQL>
SQL> --
SQL> -- We'll try another query using that column
SQL> --
SQL> -- Let's set autotrace on to see if the index
SQL> -- is being used in this example
SQL> --
SQL> set autotrace on
SQL> select * From emp where empno is null;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 3712041407

---------------------------------------------------------------------------
 Id   Operation           Name  Rows   Bytes  Cost (%CPU) Time 
---------------------------------------------------------------------------
   0  SELECT STATEMENT              1     87      0   (0)      
*  1   FILTER                                                  
   2    TABLE ACCESS FULL EMP      14   1218      3   (0) 00:00:01
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(NULL IS NOT NULL)

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
          4  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
          0  redo size
        353  bytes sent via SQL*Net to client
        239  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

SQL> set autotrace off
SQL>
SQL> --
SQL> -- Since the index has been marked as used
SQL> -- it remains in the USED state for this
SQL> -- monitoring window even though the last
SQL> -- query didn't use the index at all
SQL> --
SQL> select index_name, table_name, used from v$object_usage;

INDEX_NAME                     TABLE_NAME                     USE
------------------------------ ------------------------------ ---
EMP_ENO_IDX                    EMP                            YES

1 row selected.

SQL>
SQL> --
SQL> -- Turn off the usage monitoring
SQL> --
SQL> alter index emp_eno_idx nomonitoring usage;

Index altered.

SQL>
SQL> --
SQL> -- And the last generated data remains
SQL> --
SQL> select index_name, table_name, used from v$object_usage;

INDEX_NAME                     TABLE_NAME                     USE
------------------------------ ------------------------------ ---
EMP_ENO_IDX                    EMP                            YES

1 row selected.

SQL> 

Letting Oracle monitor index usage is much easier than traipsing through piles of event 10046 trace files looking for index scans. I’m happy they’ve provided such a tool. But, you may run across an index which is used but won’t be marked as such in V$OBJECT_USAGE. How can this be? Oracle can use the statistics from the index in determining the best query plan, and when those statistics are gone (as when the index has been dropped) performance can suffer; the optimizer generates a decision tree when each query is hard parsed, and missing index statistics may direct the optimizer down a path it might not have taken when the statistics existed. Oracle, in one respect,is correct in that the index in question hasn’t been read however it did use the statistics to perform path elimination. So, before heading straight for the ‘drop index’ command it would be prudent to verify the index in question really isn’t being used in any way — this is why we have test systems, correct? Dropping the index on a test database then verifying that no performance degradation occurs is, in my mind, a good idea. If, after the tests indicate an index may truly be unused, performance problems arise because that index is missing it can be recreated to restore the application to its original lustre.

Some suggest that simply setting an index to UNUSABLE would provide the same conditions as dropping it, but disabling an index in that fashion doesn’t remove the statistics generated on that index and if a query or process is using those statistics but is not actually accessing the index the same conditions don’t exist and one could be led into a false sense of security that the index in question is truly unused. Yes, actual access to the index is not allowed but since the index wasn’t being read to begin with (only the statistics were used by the CBO for cost calculations) I can’t accept that the same run-time conditions exist. Eventually the statistics will be outdated and no longer will be used but it could take a week, a month or longer for this to occur (depending upon system activity). For those DBAs in a hurry (and, face it, sometimes management IS in a hurry for results) setting an index to UNUSABLE may not be a valid course of action to discover whether it’s actually used or not.

There is a way in Oracle 11.2 and later releases: set the index to INVISIBLE. This will prevent the optimizer from using the index and the associated statistics without dropping it:

SQL> create index emp_empno_idx
  2  on emp(empno)
  3  invisible;

Index created.

SQL> 
SQL> set autotrace on
SQL> 
SQL> select
  2  	     *
  3  from
  4  	     emp
  5  where
  6  	     empno < 7400;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                               
---------- ---------- --------- ---------- --------- ---------- ---------- ----------                                               
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20                                               


Execution Plan
----------------------------------------------------------                                                                          
Plan hash value: 3956160932                                                                                                         
                                                                                                                                    
--------------------------------------------------------------------------                                                          
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |                                                          
--------------------------------------------------------------------------                                                          
|   0 | SELECT STATEMENT  |      |     1 |    87 |     3   (0)| 00:00:01 |                                                          
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    87 |     3   (0)| 00:00:01 |                                                          
--------------------------------------------------------------------------                                                          
                                                                                                                                    
Predicate Information (identified by operation id):                                                                                 
---------------------------------------------------                                                                                 
                                                                                                                                    
   1 - filter("EMPNO" 
SQL> alter session set optimizer_use_invisible_indexes=true;

Session altered.

SQL> set autotrace off
SQL> select LOADED_VERSIONS,executions,INVALIDATIONS,ROWS_PROCESSED from v$sql where sql_id='axr7gct6p1g3y';

LOADED_VERSIONS EXECUTIONS INVALIDATIONS ROWS_PROCESSED                                                                             
--------------- ---------- ------------- --------------                                                                             
              1          1             2              1                                                                             
              1          1             1              1                                                                             

SQL> set autotrace on
SQL> select
  2  	     *
  3  from
  4  	     emp
  5  where
  6  	     empno < 7400;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                               
---------- ---------- --------- ---------- --------- ---------- ---------- ----------                                               
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20                                               


Execution Plan
----------------------------------------------------------                                                                          
Plan hash value: 576302360                                                                                                          
                                                                                                                                    
---------------------------------------------------------------------------------------------                                       
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |                                       
---------------------------------------------------------------------------------------------                                       
|   0 | SELECT STATEMENT            |               |     1 |    87 |     2   (0)| 00:00:01 |                                       
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP           |     1 |    87 |     2   (0)| 00:00:01 |                                       
|*  2 |   INDEX RANGE SCAN          | EMP_EMPNO_IDX |     1 |       |     1   (0)| 00:00:01 |                                       
---------------------------------------------------------------------------------------------                                       
                                                                                                                                    
Predicate Information (identified by operation id):                                                                                 
---------------------------------------------------                                                                                 
                                                                                                                                    
   2 - access("EMPNO" 
SQL> alter session set optimizer_use_invisible_indexes=false;

Session altered.

SQL> set autotrace off
SQL> select LOADED_VERSIONS,EXECUTIONS,INVALIDATIONS,ROWS_PROCESSED from v$sql where sql_id='axr7gct6p1g3y';

LOADED_VERSIONS EXECUTIONS INVALIDATIONS ROWS_PROCESSED                                                                             
--------------- ---------- ------------- --------------                                                                             
              1          1             2              1                                                                             
              1          1             1              1                                                                             

SQL> set autotrace on 
SQL> select
  2  	     *
  3  from
  4  	     emp
  5  where
  6  	     empno < 7400;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                               
---------- ---------- --------- ---------- --------- ---------- ---------- ----------                                               
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20                                               


Execution Plan
----------------------------------------------------------                                                                          
Plan hash value: 3956160932                                                                                                         
                                                                                                                                    
--------------------------------------------------------------------------                                                          
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |                                                          
--------------------------------------------------------------------------                                                          
|   0 | SELECT STATEMENT  |      |     1 |    87 |     3   (0)| 00:00:01 |                                                          
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    87 |     3   (0)| 00:00:01 |                                                          
--------------------------------------------------------------------------                                                          
                                                                                                                                    
Predicate Information (identified by operation id):                                                                                 
---------------------------------------------------                                                                                 
                                                                                                                                    
   1 - filter("EMPNO" 
SQL> alter index emp_empno_idx visible;

Index altered.

SQL> set autotrace off
SQL> select LOADED_VERSIONS,EXECUTIONS,INVALIDATIONS,ROWS_PROCESSED from v$sql where sql_id='axr7gct6p1g3y';

LOADED_VERSIONS EXECUTIONS INVALIDATIONS ROWS_PROCESSED                                                                             
--------------- ---------- ------------- --------------                                                                             
              1          2             3              2                                                                             
              1          1             2              1                                                                             


SQL> set autotrace on
SQL> select
  2  	     *
  3  from
  4  	     emp
  5  where
  6  	     empno < 7400;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                               
---------- ---------- --------- ---------- --------- ---------- ---------- ----------                                               
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20                                               


Execution Plan
----------------------------------------------------------                                                                          
Plan hash value: 576302360                                                                                                          
                                                                                                                                    
---------------------------------------------------------------------------------------------                                       
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |                                       
---------------------------------------------------------------------------------------------                                       
|   0 | SELECT STATEMENT            |               |     1 |    87 |     2   (0)| 00:00:01 |                                       
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP           |     1 |    87 |     2   (0)| 00:00:01 |                                       
|*  2 |   INDEX RANGE SCAN          | EMP_EMPNO_IDX |     1 |       |     1   (0)| 00:00:01 |                                       
---------------------------------------------------------------------------------------------                                       
                                                                                                                                    
Predicate Information (identified by operation id):                                                                                 
---------------------------------------------------                                                                                 
                                                                                                                                    
   2 - access("EMPNO" 

Note the change in LOADED_VERSIONS and INVALIDATIONS when the index is made visible or invisible. There is also a setting, optimizer_use_invisible_indexes, which can be set at the session level. It’s FALSE by default; setting it to TRUE will show the same results as the example shown above without making the index visible to every user who has access the objects in the affected schema, thus making the effect local to the current session. By setting the index to INVISIBLE you can assess whether that index is truly used or not, regardless of what Oracle’s index monitoring tells you. You will then be better prepared to assess whether the index can be safely dropped.

Of course database administration cannot be ruled by rash acts, and relying upon a sole source of information (such as V$OBJECT_USAGE) can result in problems down the line. So, careful attention to detail is necessary, especially when managing the existence (or not) of an index [or table, or view, or …]. I like to follow a few simple rules:

1 — Test, test, test.
2 — Keep testing.
3 — Never do anything you can’t undo.

Keeping to that methodology usually ensures I’m not in trouble later. And, it keeps the end-users happier.

I like happy end-users.

A Tale Of Two Indexes

A fairly common question recently appeared in an Oracle-related newsgroup; the question was titled ‘index didn’t work’ and was presented thus:

“a table TEST,includes two columns:c_1(number not null),c_2(varchar2(255));10 rows inserted;create independent index on each column,then i query:select c_1 from TEST,explain plan report:index full scan,that’s right.next,i query:select c_2 from TEST,explain plan report:table access full.why?”

Okay, that’s a bit difficult to read so let’s re-word it a bit:

A table, TEST, contains two columns, defined as follows:

c_1 number not null
c_2 varchar2(255)

10 rows are inserted into this table and single-column indexes are created. Querying the data with the following statement:

select c_1 from test;

produces an index full scan path. Changing the query to:

select c_2 from test;

produces a full table scan path. Both columns have an index; why isn’t the index for the second column used for the above query?

So why isn’t that index used? It all comes down to … [ominous musical interlude here] … NULLs.

You read correctly, NULLs.

In an Oracle database entirely NULL keys are not indexed in conventional index structures (b-tree indexes); I consider a function-based index a special case of this which can index NULL keys provided the function used ‘translates’ NULLs to a non-null value. Bitmap indexes, on the other hand, do index NULLs by default; unfortunately for the questioner the indexes created aren’t bitmap indexes. So, why are NULLs at fault here? Let’s look at the table definition again and see.

SQL> desc test
 Name                                      Null?    Type
 ----------------------------------------- -------- ---------------------------
 C_1                                       NOT NULL NUMBER
 C_2                                                VARCHAR2(255)

SQL>

Column c_1 is declared not null, thus there can never be a NULL value in that field, THUS an index keyed on that column is guaranteed to contain all values of that column. Oracle can, and will, use only an index created against not null columns to return query results if that index satisfies the select list as all values will be present; the associated table will never be accessed in such cases. Contrast that to column c_2, which is nullable. It doesn’t matter that the column doesn’t contain any NULL values when the index is created, the fact remains that the column CAN contain NULLs. This, then, prevents Oracle from using the index to retrieve the data from that column since it’s possible to miss any NULL values stored there. The only way to ensure Oracle returns all values from c_2 is to perform a full table scan.

Let’s look at an example of this in action:

SQL> create table test1(
  2        c_1 number not null,
  3        c_2 varchar2(255)
  4  );

Table created.

SQL>
SQL> begin
  2        for i in 1..10 loop
  3         insert into test1
  4         values (i, 'Test '¦¦i);
  5        end loop;
  6
  7        commit;
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL>
SQL> create index test1_c_1
  2  on test1(c_1);

Index created.

SQL>
SQL> create index test1_c_2
  2  on test1(c_2);

Index created.

SQL>
SQL> set autotrace on
SQL>
SQL> select c_1 from test1;

       C_1
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10

10 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2941126162

------------------------------------------------------------------------------
 Id   Operation         Name       Rows   Bytes  Cost (%CPU) Time
------------------------------------------------------------------------------
   0  SELECT STATEMENT                10    130      1   (0) 00:00:01
   1   INDEX FULL SCAN  TEST1_C_1     10    130      1   (0) 00:00:01
------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
          5  recursive calls
          0  db block gets
         11  consistent gets
          0  physical reads
          0  redo size
        284  bytes sent via SQL*Net to client
        246  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         10  rows processed

SQL>
SQL> select c_2 from test1;

C_2
--------------------------------------------------------------------------------
Test 1
Test 2
Test 3
Test 4
Test 5
Test 6
Test 7
Test 8
Test 9
Test 10

10 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3852271815

---------------------------------------------------------------------------
 Id   Operation          Name   Rows   Bytes  Cost (%CPU) Time
---------------------------------------------------------------------------
   0  SELECT STATEMENT             10   1290      3   (0) 00:00:01
   1   TABLE ACCESS FULL TEST1     10   1290      3   (0) 00:00:01
---------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
          4  recursive calls
          0  db block gets
         17  consistent gets
          0  physical reads
          0  redo size
        344  bytes sent via SQL*Net to client
        246  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         10  rows processed

SQL>

Notice that even though no NULL values occur in c_2 the path is a full table scan; this is due, as explained earlier, to the column definition at table creation, allowing NULL values to be stored. Notice, too, that the query

select c_1 from test1;

accesses only the index on that column; at no time is the table ‘touched’ to return any data. There is no need, since all data values for that column are present in that index.

Let’s see if we can get an index full scan of the index for c_2; we’ll change the query and see what the optimizer does:

SQL> select c_2 from test1 where c_2 is not null;

C_2
------------------------------------------------------------------------------
Test 1
Test 10
Test 2
Test 3
Test 4
Test 5
Test 6
Test 7
Test 8
Test 9

10 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 950550661

------------------------------------------------------------------------------
 Id   Operation         Name       Rows   Bytes  Cost (%CPU) Time
------------------------------------------------------------------------------
   0  SELECT STATEMENT                10     80      1   (0) 00:00:01
*  1   INDEX FULL SCAN  TEST1_C_2     10     80      1   (0) 00:00:01
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("C_2" IS NOT NULL)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        343  bytes sent via SQL*Net to client
        246  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         10  rows processed

SQL>

Since Oracle need not return any NULLs stored in c_2, and since the index contains only non-NULL key values, Oracle can perform an index full scan to return the desired data. Of course in this case there are no NULLs stored in the table, making this possibly a bit confusing. We’ll now store a few NULL values in that column and run the queries again:

SQL> begin
  2     for i in 11..20 loop
  3             if mod(i,2) = 0 then
  4                     insert into test1 values (i, 'Test '¦¦i);
  5             else
  6                     insert into test1 values(i, null);
  7             end if;
  8     end loop;
  9  end;
 10  /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> select c_2 from test1;

C_2
--------------------------------------------------------------------------------

Test 12

Test 14

Test 16

Test 18

Test 20
Test 1

C_2
--------------------------------------------------------------------------------
Test 2
Test 3
Test 4
Test 5
Test 6
Test 7
Test 8
Test 9
Test 10

20 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3852271815

---------------------------------------------------------------------------
 Id   Operation          Name   Rows   Bytes  Cost (%CPU) Time
---------------------------------------------------------------------------
   0  SELECT STATEMENT             10     80      3   (0) 00:00:01
   1   TABLE ACCESS FULL TEST1     10     80      3   (0) 00:00:01
---------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          9  consistent gets
          4  physical reads
          0  redo size
        474  bytes sent via SQL*Net to client
        253  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         20  rows processed

SQL> select c_2 from test1 where c_2 is not null;

C_2
--------------------------------------------------------------------------------
Test 1
Test 10
Test 12
Test 14
Test 16
Test 18
Test 2
Test 20
Test 3
Test 4
Test 5

C_2
--------------------------------------------------------------------------------
Test 6
Test 7
Test 8
Test 9

15 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 950550661

------------------------------------------------------------------------------
 Id   Operation         Name       Rows   Bytes  Cost (%CPU) Time
------------------------------------------------------------------------------
   0  SELECT STATEMENT                10     80      1   (0) 00:00:01
*  1   INDEX FULL SCAN  TEST1_C_2     10     80      1   (0) 00:00:01
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("C_2" IS NOT NULL)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        408  bytes sent via SQL*Net to client
        246  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         15  rows processed

SQL>

The original query

select c_2 from test1;

returns all values for the column, including the NULLs, requiring the table to be scanned. The second query, with the ‘is not null’ qualifier, returns 5 fewer rows but doesn’t touch the table at all, instead relying solely on the index to produce the desired result set.

Even

select count(*) from test1;

will use the index on the NOT NULL column, thus avoiding any table access:

SQL> select count(*) from test1;

  COUNT(*)
----------
        10


Execution Plan
----------------------------------------------------------
Plan hash value: 2402158148

----------------------------------------------------------------------
 Id   Operation         Name       Rows   Cost (%CPU) Time    
----------------------------------------------------------------------
   0  SELECT STATEMENT                 1      1   (0) 00:00:01
   1   SORT AGGREGATE                  1                      
   2    INDEX FULL SCAN TEST1_C_1     10      1   (0) 00:00:01
----------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
          4  recursive calls
          0  db block gets
         11  consistent gets
          0  physical reads
          0  redo size
        225  bytes sent via SQL*Net to client
        246  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>

Since, as explained before, the index on the NOT NULL column will list every value in that column it will also provide the count for every row in that table.

Let’s make the index on c_2 contain all of the values of c_2, NULL or not, by including a non-null component (in this case a 0):

SQL> drop index test1_c_2;

Index dropped.

SQL> 
SQL> create index test1_c_2
  2  on test1(c_2,0);

Index created.

SQL>

Now the plan accesses the index without the need for the “where c_2 is not null” condition:

SQL> select c_2 from test1;

C_2
-------------------------------------------------------------------------
Test 1
Test 10
Test 12
Test 14
Test 16
Test 18
Test 2
Test 20
Test 3
Test 4
Test 5
Test 6
Test 7
Test 8
Test 9






20 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2529630288

------------------------------------------------------------------------------
| Id  | Operation        | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |           |    20 |  2580 |     1   (0)| 00:00:01 |
|   1 |  INDEX FULL SCAN | TEST1_C_2 |    20 |  2580 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
         48  recursive calls
          0  db block gets
         20  consistent gets
          0  physical reads
          0  redo size
        766  bytes sent via SQL*Net to client
        427  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         20  rows processed

SQL> 

Since the index key was not null (there is always a 0 in each index entry) the index could contain NULL column values for c_2. So Oracle can index NULL values in a b-tree index as long as the entire index key is not null.

So, the optimizer is doing the ‘right thing’, really. It just may be confusing when situations like this arise. Just remember that entirely NULL keys, in a traditional b-tree index, aren’t included and, for a key composed of nullable columns, Oracle must scan the entire table to return all of the possible values. It isn’t a problem, it’s just ‘good business’.