Oracle Tips and Tricks — David Fitzjarrell

August 15, 2008

That Darned "CAT"-alogue

Filed under: General — dfitzjarrell @ 20:42

There have been quite a number of searches for information on many of the exu8 views present in an Oracle data dictionary. Such views belong to the export/import catalog, created with the $ORACLE_HOME/rdbms/admin/catexp.sql script. Inside this script, in the internal documentation, lies descriptions of many of these views. Let’s look at a few which have made the ‘Hit Parade’ in Google searches:

exu8poki

 This view contains the partitioning key columns for partitioned indexes.

exu8jbq

 Contains job queue entries.

exu8rif

 Contains information on referential contraints.

exu8snapl

 Snapshot log information

exu81obj

 This view selects all rows from sys.obj$ that are NOT secondary
 objects as created by Domain Indexes. Secondary objects are not normally
 exported because the domain index's CREATE INDEX at import time will create
 them.
 

I won’t cover any more of these views, as you can easily look in the creation script and find descriptions for almost all of the export/import views.

[If any of these views are found marked INVALID in the data dictionary you can run $ORACLE_HOME/rdbms/admin/utlrp.sql to try to recompile them; should that not fix the issue they can be rebuilt with the $ORACLE_HOME/rdbms/admin/catexp.sql script; it’s probably best to shutdown the database, start it in restricted mode and then run the script to ensure no one is attempting to use these views while recreating them or you can use

ALTER SYSTEM ENABLE RESTRICTED SESSION;

Of course you’ll need to kill all connected sessions to ensure no one is doing any work as using the above statement only affects new connections to the database. Once the views are listed as VALID you can either shutdown the database and restart it or use

ALTER SYSTEM DISABLE RESTRICTED SESSION;

to allow ‘regular’ users to connect.]

Happy reading.

Advertisements

1 Comment »

  1. […] 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 […]

    Pingback by Where, O Where Has That Table Gone? « Oracle Tips and Tricks — July 23, 2012 @ 09:16 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: