What is Data Dictionary?

The  Data Dictionary   is  a  repository   of  database  metadata (data  about  data), about  all the  information  inside  the database. This  repository  is owned  by  "sys",  and  is  stored principally  in  the "system"  tablespace, though  some  components  are stored in the "sysaux"  tablespace (in Oracle 10g and 11g). The  Oracle  user  "SYS"  stores  all base tables and user-accessible views of the data dictionary. No Oracle user should ever alter (update,delete or insert)  any  rows  or  schema  objects  contained  in  the  SYS  schema, because such activity can compromise data integrity. 

A data dictionary contains the following contents  :

• The definitions of all schema objects in the database (tables, views, indexes, clusters, synonyms, sequences, procedures, functions, packages, triggers, and so on)
• How much space has been allocated for, and is currently used by, the schema objects
• Default values for columns
• Integrity constraint information
• The names of Oracle users
• Privileges and roles each user has been granted
• Auditing information, such as who has accessed or updated various schema objects
• Other general database information.

The data dictionary consists of the following :

1.) Base Table : The  underlying  tables  that  store  information about the associated database. Only 

Oracle  should write to and read these  tables. Users  rarely access them directly because they  are  normalized, and most of the data is stored in a cryptic format.

2.) User-Accessible Views :  The  views that summarize and  display  the  information  stored  in  the base tables  of  the  data dictionary. These  views  decode  the  base table data into  useful information, such as user or table names, using joins and where clauses to simplify the information. Most users are given access to the views rather than the base tables.

The  user-accessible views  come in two primary forms :

1.) The static performance views : The static views are dba_xx , all_xx  , user_xx  views  (eg. dba_users, user_tables, all_tables). These views are used to manage database structures.

2.) The v$dynamic performance views : The dynamics views are v$xx views (eg v$process).These views are used to monitor real time database statistics.

Some  of  these tables  are inside  of  the Oracle  kernel, so  we  would  never  work  directly  with them unless  we  are  working  for Oracle support  or  performing  a  disaster  recovery scenario . But instead we can  access  to  the  views  in  order  to know  the “information about the information”.  For example,  a possible  usage  of  this  data  dictionary  would  be  to  know all  the tables  owned  by  a  single user, or  the list  or  relationships between  all  the  tables  of  the  database.

The  main view of the data dictionary is the view DICT (or DICTIONARY):  
SQL> desc dict
Name                           Null?                Type
---------------               --------         -----------------
TABLE_NAME                                   VARCHAR2(30)
COMMENTS                                      VARCHAR2(4000)

Through  the  DICT view, we can access  to all the data dictionary views that could provide us the information that  we  need. For  example, if  we are looking  for  information  related  to  link , but we  don’t know  where  to  look  for, then query the DICT view:

SQL> select  table_name  from  dict  where  table_name  like   '%DATAPUMP%'  ; 
TABLE_NAME
----------------------------
DBA_DATAPUMP_JOBS

DBA_DATAPUMP_SESSIONS
USER_DATAPUMP_JOBS
GV$DATAPUMP_JOB
GV$DATAPUMP_SESSION
V$DATAPUMP_JOB
V$DATAPUMP_SESSION
7 rows selected.


Now, we  have just to query  one of  these  views  to  find  the  data  we are  looking for  .GV$ views  are very  useful  when we  are working  with  RAC,  and V$  views  are instance  related. Remember  that  the data  dictionary  provides  critical  information  of  the database, and  it should  be  restricted  to users. However,  if  a  user  really  needs  to query  the  data dictionary,  we  can  grant  the  following  privileges .

SQL> grant select_catalog_role to username ;

As  a DBA, we  can see why the data dictionary is so important. Since we can’t possibly remember everything  about our database (like the names of all the tables and columns) Oracle remembers this for us. All  we need is to learn how to find that information .

Enjoy     :-) 

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

Remote_login_passwordfile Parameter and it's use

Remote_login_passwordfile   specifies whether Oracle checks for a password file . When we grant "sysdba" or  "sysoper" privileges to a user, that  user's name  and  privilege  information  are  added to the password file. A user's name remains in the password file only as long as that user has at least one of these two privileges. If we revoke both of these privileges, Oracle Database removes the user from the password file .

We have two options for Super User "SYS" password authentication .
1.) Operating System authentication

2.) Password file authentication

Password file authentication : When we use password file authentication then we have to create password file for  "SYS"  user or  the  user  having  "sysdba"  privileges . We can create the password file as below : 

C:\>orapwd  file=$ORACLE_HOME\database\orapwd.ora  password=sys_password  entries=n force=y

Note:

=> Entries= n  specifies the "maximum number of distinct DBAs and OPERs (opt)" can login into database .

=> Force=y    specifies if the password file exists then it overwrites the existing password file .

 There are three value for remote_login_passwordfile parameters : 

1.) EXCLUSIVE:  This is default value. The password file can be used by only one database and the password file can contain names other than sys.We can use password file for authentication and connect to remote machine with sys user.Only an EXCLUSIVE file can contain the names of users other than SYS. Using an "exclusive" password file lets us allow to grant sysdba and sysoper system privileges to individual users and have them connect as themselves.

2.) NONE: If this parameter set "NONE" means passwordfile doesn't exists. That is, no privileged connections are allowed over non-secure connections. If remote_login_password is set to EXCLUSIVE or SHARED and the password file is missing, this is equivalent to setting  remote_login_password  to NONE.

3.) SHARED: A SHARED password file can be used by multiple databases running on the same server. However, only user recognized by a SHARED password file is sys .We cannot add users to a SHARED password file. All users needing  sysdba  or sysoper system privileges must connect using the same name, SYS, and password. This option is useful if we have a single DBA administering multiple databases.A shared password file cannot be modified. This means that we cannot add users to a shared password file. Any attempt to do so or to change the password of SYS or other users with the sysdba or sysoper privileges generates an error.

All users needing  sysdba or sysoper  system privileges must be added to the password file when remote_login_passwordfile is set to  exclusive . After all users are added, we can change remote_login_passwordfile  to shared, and then share the file.This option is useful if we are administering multiple databases or a RAC database. If  remote_login_passwordfile is set to  exclusive  or shared and the password file is missing, this is equivalent to setting  remote_login_passwordfile  to None .

If we ever receive the file full error i.e, ORA-1996  while granting  "sysdba"  or  "sysoper" system privileges to a user, we must create a larger password file and regrant the privileges to the users. The below steps will replace the current passwordfile with new larger password file .

1.)  Identify the users who have sysdba or  sysoper  privileges by querying the  v$pwfile_users views .
2.)  Delete the existing password file.
3.)  Creating a new password file using the ORAPWD utility and ensure that the ENTRIES parameter is set to a number larger then the previous password file .
4.) Connect with "sys" and grant the priveleges to user as quired from v$pwfile_users view .

Enjoy     :-) 

A Good Time To Be an Oracle DBA

I have come across a very good article by  Paul Sorensen , Director of Oracle Certification. Here it is  ... 

I ’ve seen a few articles lately that remind us that - in spite of the current economic climate - it is a good time to be a Database Administrator, both now and in the future.

In a CNN article  the author lists Database Administrator (DBA) as one of the Top Ten Promising jobs with solid job growth outlook over the next several years. Additionally, a US-oriented salary survey indicates encouragingly high salaries for DBAs and Senior DBAs. Most importantly what these articles both indicate is that the DBA job role indeed continues to be a critical, high paying role with solid future prospects.

Now as much as ever, DBAs of all skill levels should look to shore up their skill sets and resumes. Certification can be a relatively inexpensive (although not easy) way to enhance your credibility and prospects while adding to your skills and knowledge.

As you evaluate your current situation, remember to consider certification as a part of your career-planning strategy – whether you need to upgrade, have never certified before, or are looking to specialize with an Expert designation.

Paul Sorensen,
Director, Oracle Certification

Enjoy     J J J

Grow That DBA Career

Today I came across a very good article  i.e, "Grow That DBA Career"  written and published by Brian Peasland.  This article covers the  frequent asked question by freshers or by those who want to make their in DBA field .  This article covers the below points  . 

• Should I Become a DBA?
• How do I get that first DBA job?
• I just got my first job! now what?
• How do I go from a beginner DBA to an intermediate DBA?
• How do I go from an intermediate to a senior DBA?

Enjoy reading below link  .

http://www.peasland.net/Grow%20That%20DBA%20Career.zip
http://liroc.itpub.net/post/1068/268481

Hope , this may help you .  

Enjoy  :-)