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 ; 

Enjoy   :-)