Wednesday, December 26, 2007

How can we find the data dictionary names?

The views of the data dictionary serve as a reference for all database users. Access the data dictionary views with SQL statements. Some views are accessible to all Oracle users, and others are intended for database administrators only.

The data dictionary is always available when the database is open. It resides in the SYSTEM tablespace, which is always online.

The data dictionary consists of sets of views. In many cases, a set consists of three views containing similar information and distinguished from each other by their prefixes:

USER => User's view (what is in the user's schema)

ALL => Expanded user's view (what the user can access)

DBA => Database administrator's view (what is in all users' schemas)

How do we find the data dictionary names?
By using "DICTIONARY" or "DICT" objects

SQL> DESC DICT

Name Null? Type
----------------------------------------------------------------------- -------- ------------------------------------------------
TABLE_NAME VARCHAR2(30)
COMMENTS VARCHAR2(4000)

Example: We would like to check the "SENDXML_COLLECTION" Scheduler job log on userz user. and we don't know anything.

- find the view names of the data dictionary in "DICT" object.
SQL> CONNECT userz@TESTDB
SQL> SELECT * FROM DICT WHERE TABLE_NAME LIKE 'USER_SCH%';

TABLE_NAME COMMENTS
------------------------------ --------------------------------------------------
USER_SCHEDULER_PROGRAMS Scheduler programs owned by the current user
USER_SCHEDULER_JOBS All scheduler jobs in the database
USER_SCHEDULER_PROGRAM_ARGS All arguments of all scheduler programs in the dat
abase
USER_SCHEDULER_JOB_ARGS All arguments with set values of all scheduler job
s in the database
USER_SCHEDULER_JOB_LOG Logged information for all scheduler jobs
USER_SCHEDULER_JOB_RUN_DETAILS The details of a job run
USER_SCHEDULER_SCHEDULES Schedules belonging to the current user
USER_SCHEDULER_CHAINS All scheduler chains owned by the current user
USER_SCHEDULER_CHAIN_RULES All rules from scheduler chains owned by the curre
nt user
USER_SCHEDULER_CHAIN_STEPS All steps of scheduler chains owned by the current
user
USER_SCHEDULER_RUNNING_CHAINS All steps of chains being run by jobs owned by the
current user

- Get view about Scheduler job log, that's "USER_SCHEDULER_JOB_LOG" object.
SQL> DESC USER_SCHEDULER_JOB_LOG

Name Null? Type
----------------------------------------------------------------------------------- -------- --------------------------------------------------------
LOG_ID NOT NULL NUMBER
LOG_DATE TIMESTAMP(6) WITH TIME ZONE
OWNER VARCHAR2(30)
JOB_NAME VARCHAR2(65)
JOB_SUBNAME VARCHAR2(65)
JOB_CLASS VARCHAR2(30)
OPERATION VARCHAR2(30)
STATUS VARCHAR2(30)
USER_NAME VARCHAR2(30)
CLIENT_ID VARCHAR2(64)
GLOBAL_UID VARCHAR2(32)
ADDITIONAL_INFO CLOB

- query the "SENDXML_COLLECTION" Scheduler job log.
SQL> column log_date format a40
SQL> column job_name format a20
SQL> column OPERATION format a5
SQL> column STATUS format a10
SQL> SELECT LOG_DATE, JOB_NAME, OPERATION, STATUS FROM USER_SCHEDULER_JOB_LOG WHERE JOB_NAME='SENDXML_COLLECTION' ORDER BY LOG_DATE DESC;

LOG_DATE JOB_NAME OPERA STATUS
---------------------------------------- -------------------- ----- ----------
26-DEC-07 03.01.58.718421 AM +07:00 SENDXML_COLLECTION RUN SUCCEEDED
25-DEC-07 03.01.40.454928 AM +07:00 SENDXML_COLLECTION RUN SUCCEEDED
24-DEC-07 03.02.00.733701 AM +07:00 SENDXML_COLLECTION RUN SUCCEEDED
23-DEC-07 03.01.29.971802 AM +07:00 SENDXML_COLLECTION RUN SUCCEEDED
22-DEC-07 03.01.49.700460 AM +07:00 SENDXML_COLLECTION RUN SUCCEEDED


If don't know the data dictionary names anymore. Please don't forget "DICT" object, that can help.

No comments: