René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

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:
  • MBRC
  • SREADTIM
  • MREADTIM
  • CPUSPEED - In MHz, obviously not always right
  • SLAVETHR - Throughput
  • ????THR
  • BADSTAT(S)

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$

In dictionary managed tablespaces, free extents are maintained in fet$. See also uet$

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: 
select * from sys.obj$ where owner# = userenv('SCHEMAID')

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$

In dictionary managed tablespaces, extents are maintained in uet$. See also fet$

user$

Contains two types (column type#) of objects: users (type# = 1) and roles (type# = 0).

user_history$

This table is important to store the history of passwords for a user if password related profiles are enabled.