Thursday, March 3, 2011

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   :-) 


No comments: