DBA Scripts - Very Useful - 15. Database Objects

Here are some scripts related to Database Objects .

Clusters

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

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

Jobs

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.

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

Links

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

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

Procs/Pkgs

DATABASE PROCEDURE/PACKAGE NOTES:

  Owner - Owner of the object

  Name - Name of the object

  Type - Type of object

select       OWNER,

               NAME,

               TYPE

from        dba_source

group       by OWNER,NAME,TYPE

order        by OWNER,NAME,TYPE

Sequences

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

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

Snapshots

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

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

Synonyms

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

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

Triggers

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)

select       TABLE_OWNER,

               TABLE_NAME,

               TRIGGER_NAME,

               TRIGGER_TYPE,

               TRIGGERING_EVENT,

               STATUS

from        dba_triggers

order        by TABLE_NAME, TRIGGER_NAME

Views

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

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