Database Objects
Here are some scripts related to Database Objects :
DATABASE CLUSTER NOTES:
Owner - Owner of the table/cluster
Tablespace - Name of the tablespace containing the cluster
Cluster Name - Name of the cluster
Table Name - Clustered table name
Table Column - Key column in the table
Cluster Column - Key column in the cluster
SQL> select a.OWNER,TABLESPACE_NAME, a.CLUSTER_NAME,
TABLE_NAME,TAB_COLUMN_NAME,CLU_COLUMN_NAME
from dba_clusters a, dba_clu_columns b
where a.CLUSTER_NAME = b.CLUSTER_NAME
order by a.OWNER,TABLESPACE_NAME,a.CLUSTER_NAME,TABLE_NAME ;
DATABASE JOB NOTES :
Log User - USER who was logged in when the job was submitted.
Schema - Default schema used to
parse the job. For example, if the SCHEMA_USER is SCOTT and you submit
the procedure HIRE_EMP as a job, Oracle looks for SCOTT.HIRE_EMP.
Job# - Identifier of job. Neither import/export nor repeated executions change it.
Interval - A date function, evaluated at the start of execution, becomes next NEXT_DATE.
Next Execution - Date/time that this job will next be executed.
Broken - If Y, no attempt is made to run this job. See DBMS_JOBQ.BROKEN (JOB).
What - Body of the anonymous PL/SQL block that this job executes.
SQL> select LOG_USER, SCHEMA_USER schema,JOB job#, INTERVAL,
to_char(NEXT_DATE,'MM/DD/YYYY HH24:MI:SS') next_execution,
BROKEN, substr(WHAT,1,100) what
from dba_jobs order by LOG_USER
DATABASE LINK NOTES:
Owner - Owner of the database link
DBLink - Name of the database link
Username - Name of user to log in as
Host - SQL*Net string for connect
Created - Creation time of the database link
SQL> select OWNER,DB_LINK,USERNAME, HOST,
to_char(CREATED,'MM/DD/YYYY HH24:MI:SS') created
from dba_db_links order by OWNER,DB_LINK ;
DATABASE PROCEDURE/PACKAGE NOTES :
Owner - Owner of the object
Name - Name of the object
Type - Type of object
SQL> select OWNER, NAME, TYPE from dba_source
group by OWNER,NAME,TYPE
order by OWNER,NAME,TYPE ;
DATABASE SEQUENCE NOTES :
Owner - Owner of the sequence
Name - Name of the sequence
MinValue - Minimum value of the sequence
MaxValue - Maximum value of the sequence
Increment - Value by which sequence is incremented
Cycle - Does sequence wrap around on reaching limit?
Order - Are sequence numbers generated in order?
Cache Size - Number of sequence numbers to cache
Last Number - Last sequence number written to disk
SQL> select SEQUENCE_OWNER,SEQUENCE_NAME, MIN_VALUE,
MAX_VALUE,INCREMENT_BY, CYCLE_FLAG, ORDER_FLAG,
CACHE_SIZE, LAST_NUMBER from dba_sequences
where SEQUENCE_OWNER not in ('SYS','SYSTEM')
order by SEQUENCE_OWNER,SEQUENCE_NAME ;
DATABASE SNAPSHOT NOTES :
Owner - Owner of the snapshot
Name - The view used by users and applications for viewing the snapshot
Table Name - Table the snapshot is stored in, has an extra column for the master rowid
Master View - View of the master table, owned by the snapshot owner, used for refreshes
Master Owner - Owner of the master table
Master - Name of the master table of which this snapshot is a copy
Master Link - Database link name to the master site
Can Use Log - If NO, this snapshot is complex and will never use a log
Updatable - If NO, the snapshot is read only
Last Refresh - SYSDATE from the master site at the time of the last refresh
Error - The number of failed automatic refreshes since last successful refresh
Type - The type of refresh (complete, fast, force) for all automatic refreshes
Next Refresh - The date function used to compute next refresh dates
Refresh Group - GROUP All snapshots in a given refresh group get refreshed in the same transaction
SQL> select OWNER,NAME, TABLE_NAME, MASTER_VIEW, MASTER_OWNER,
MASTER, MASTER_LINK, CAN_USE_LOG, UPDATABLE, LAST_REFRESH,
ERROR, TYPE, NEXT, REFRESH_GROUP from dba_snapshots
order by OWNER,NAME ;
DATABASE SYNONYM NOTES :
Owner - Owner of the synonym
Name - Name of the synonym
Table Owner - Owner of the table
Table Name - Name of the table
DB Link - Name of the database link
SQL> select OWNER, SYNONYM_NAME, TABLE_OWNER,
TABLE_NAME,DB_LINK from dba_synonyms
where owner not in ('SYS','SYSTEM','PUBLIC','DBSNMP')
order by OWNER,SYNONYM_NAME ;
DATABASE TRIGGER NOTES :
Table Owner - Owner of the table
Table Name - Name of the table
Trigger Name - Name of the trigger
Trigger Name - When the trigger fires (BEFORE EACH ROW, AFTER EACH ROW, BEFORE STATEMENT, AFTER STATEMENT)
Triggering Event - Statement that fires the trigger (INSERT, UPDATE, DELETE)
Status - Whether the trigger is enabled (ENABLED or DISABLED)
SQL> select TABLE_OWNER, TABLE_NAME, TRIGGER_NAME, TRIGGER_TYPE,
TRIGGERING_EVENT, STATUS from dba_triggers
order by TABLE_NAME, TRIGGER_NAME;
DATABASE VIEW NOTES :
Owner - Owner of the view
View Name - Name of the view
Created - Date/time view was created
Status - Status of the view
SQL> select OWNER, OBJECT_NAME,to_char(CREATED,'MM/DD/YYYY HH24:MI:SS') created,
status from dba_objects where OWNER not in ('SYS','SYSTEM') and OBJECT_TYPE='VIEW' order by OWNER,OBJECT_NAME ;
