René Nyffenegger's collection of things on the web | |
René Nyffenegger on Oracle - Most wanted - Feedback
- Follow @renenyffenegger
|
SYS owned tables [Base tables of the Oracle dictionary] | ||
These tables are the base tables in the data dictionary. Their content is exposed for easier access through SQL through
the static dictionary views.
aud$
See auditing and
audit (sql).
aux_stats$
pname can take several values, the most usefull beeing:
col$col_usage$
This table allows to monitor the usage of predicates on columns in select statements.
It is updated (if
_column_tracking_level is set to 1) at intervalls by smon, so it might be a little out of date.
Also, dbms_stats will make use of that info when deciding if it needs to create a histogram on a column.
select o.name, c.name, u.equality_preds, u.equijoin_preds, u.nonequijoin_preds, u.range_preds, u.like_preds, u.null_preds from sys.col_usage$ u join sys.obj$ o on u.obj# = o.obj# join sys.col$ c on u.obj# = c.obj# and u.intcol# = c.col#; con$
This table keeps track of constraints. dba_constraints derive from
this table.
dbms_lock_allocated
This table stores a row for each lock allocated with dbms_lock.allocate_unique.
fet$mlog$
See also refreshing a materialized view.
obj$
Lists all objects created in a database.
The following statement lists all objects created for the current loged on user:
prop$rgroup$session_privs
This view lists the selecting user's privileges.
See also this page.
slog$
See also refreshing a materialized view.
snap$
See also refreshing a materialized view.
smon_scn_time
smon_scn_time is a table that is filled by SMON
every 5 minutes with a timestamp and the current
SCN. However, it only counts 1440 records (=5 days). This makes it possible to roughly find an SCN
for a point in time in the last 5 days.
This table is not documented, so only look at it out of curiosity. uet$user$user_history$
This table is important to store the history of passwords for a user if password related profiles are
enabled.
|