In sweet memories of my ever loving brother "kutty thambi " ARUN KUMAR

Sunday, November 15, 2009

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.

regards,
rajeshkumar

source and reference:
http://www.adp-gmbh.ch/ora/misc/sys_tables.html

No comments:

 
Share/Bookmark