Handling multiple Tables

We will learn Working with multiple Tables which covers : Joins - Inner Join; Using Table Aliases; Outer Join; Self Join; Non-Equi Join Subqueries - Simple Subqueries; Multilevel Subqueries; Correlated Subqueries; PredicatesSet Operators - Set Operator Examples

Aliases:An alias in oracle is another name used for a column or a table inside SELECT query. They are accessible only inside the query where they are created. Once the query execution is over alias will perish. An alias can be a single word or can be more than one word. If alias used is more than one word it must be enclosed in double quotes

example:current_date or "current date"Student_name or "Student name"Aliases are used in places when ever there is multiple usage of longer table names. example :STUDENT_MASTER_RECORD can be aliased as SMR ACCOUNT_MASTER_TABLE as AMT .
There are 2 types of aliases i) Table alias : It is another name used to refer the table within the query.example :SQL> SELECT ENAME , SAL FROM EMP E WHERE E.DEPTNO = 10;In the above query "E" is an alias for EMP.
ii) column aliasSQL> SELECT SYSDATE "Current Date" FROM DUAL;"Current Date" is an alias.


Working Wih Multiple Tables
To retrieve desired data from more than one table we use join.We will have more than one table name after FROM key word in SELECT statement. A SELECT statement on multiple tables without a proper JOIN condition will lead to a cartesian product.(i.e. No.Of Output rows = No.of rows in table1 X No of rows in table2....)
No of join conditions = No of tables joined - 1
Types Of Joins:

1) EQUI JOIN: The equi join is normally used to join tables with primary key foreign key relation ships.
Example :SQL> SELECT ENAME,JOB,DEPTNO FROM EMP;

ENAME JOB DEPTNO
---------- --------- ----------
SMITH CLERK 20
ALLEN SALESMAN 30
WARD SALESMAN 30
JONES MANAGER 20
MARTIN SALESMAN 30
BLAKE MANAGER 30
CLARK MANAGER 10
SCOTT ANALYST 20
KING PRESIDENT 10
TURNER SALESMAN 30
ADAMS CLERK 20
JAMES CLERK 30
FORD ANALYST 20
MILLER CLERK 10

SQL> SELECT DEPTNO,DNAME,LOC FROM DEPT;

DEPTNO DNAME LOC
---- ----------- -------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

To display ENAME of EMP table ,DNAME of DEPT table we need to join those tables as shown below.

SQL> SELECT E.DEPTNO , ENAME , DNAME FROM EMP E, DEPT DWHERE E.DEPTNO=D.DEPTNO;

DEPTNO ENAME DNAME
-------- ---------- --------------
10 CLARK ACCOUNTING
10 KING ACCOUNTING
10 MILLER ACCOUNTING
20 SMITH RESEARCH
20 ADAMS RESEARCH
20 FORD RESEARCH
20 SCOTT RESEARCH
20 JONES RESEARCH
30 ALLEN SALES
30 BLAKE SALES
30 MARTIN SALES
30 JAMES SALES
30 TURNER SALES
30 WARD SALES

In the above query "E" and "D" are the alias names for the tables EMPand DEPT respectively. As column DEPTNO is there in both the tables, tablename is used along with column name DEPTNO. This is to avoid the ambiguity

2) NON-EQUI JOIN :A join condition where any relation operator other than "=" equal to operator is used.
Consider the following examples Using SALGRADE and EMP tables.

SQL> SELECT * FROM SALGRADE;
GRADE LOSAL HISAL
---------- ---------- ----------
1 700 1200
2 1201 1400
3 1401 2000
4 2001 3000
5 3001 9999

SQL> SELECT ENAME,SAL, GRADE FROM EMP E,SALGRADE SWHERE SAL BETWEEN LOSAL AND HISAL
ENAME SAL GRADE
---------- ---------- ----------
SMITH 800 1
JAMES 950 1
ADAMS 1100 1
WARD 1250 2
MARTIN 1250 2
MILLER 1300 2
TURNER 1500 3
ALLEN 1600 3
CLARK 2450 4
BLAKE 2850 4
JONES 2975 4
SCOTT 3000 4
FORD 3000 4
KING 5000 5

3) OUTER JOIN :In EQUI JOIN rows that does not satisfy specified condition would not be displayed. Example: DEPTNO 40 is not displayed in the previous examplebecause there are no employees in it. If we want to diplay its detail also then we have to use OUTER JOIN.Otherwise OUTER JOIN is imilar to EQUI JOIN except for the difference it uses outer join (+) operator. (A plus within parenthesis) towards the side not having required data. Outer join operator will substitute null values when there are no values available.

SQL> SELECT E.DEPTNO,ENAME,DNAME FROM EMP E , DEPT D 2 WHERE E.DEPTNO (+) = D.DEPTNO;

DEPTNO ENAME DNAME
---------- ---------- --------------
10 CLARK ACCOUNTING
10 KING ACCOUNTING
10 MILLER ACCOUNTING
20 SMITH RESEARCH
20 ADAMS RESEARCH
20 FORD RESEARCH
20 SCOTT RESEARCH
20 JONES RESEARCH
30 ALLEN SALES
30 BLAKE SALES
30 MARTIN SALES
30 JAMES SALES
30 TURNER SALES
30 WARD SALESOPERATIONS

SQL> SELECT D.DEPTNO , NVL(ENAME, ' No employee') , DNAME FROM EMP E,DEPT DWHERE D.DEPTNO= E.DEPTNO(+);
DEPTNO NVL(ENAME,'N DNAME
------- ------------ --------------
10 CLARK ACCOUNTING
10 KING ACCOUNTING
10 MILLER ACCOUNTING
20 SMITH RESEARCH
20 ADAMS RESEARCH
20 FORD RESEARCH
20 SCOTT RESEARCH
20 JONES RESEARCH
30 ALLEN SALES
30 BLAKE SALES
30 MARTIN SALES
30 JAMES SALES
30 TURNER SALES
30 WARD SALES
40 No employee OPERATIONS

4) SELF JOIN:When we join a table to itself it is called self join.To join atable itself means that each row of the table is combined with itselfand with every other row of the table. The self join can be seen as join of two copies of the same table.

SQL> SELECT E.ENAME,M.ENAME FROM EMP E,EMP WHERE E.MGR=M.EMPNO;
ENAME ENAME
---------- ----------
SCOTT JONES
FORD JONES
ALLEN BLAKE
WARD BLAKE
JAMES BLAKE
TURNER BLAKE
MARTIN BLAKE
MILLER CLARK
ADAMS SCOTT
JONES KING
CLARK KING
BLAKE KING
SMITH FORD
Above query uses EMP table to display names of the employees and their managers
SQL> SELECT E.ENAME,NVL(M.ENAME,'He is managed by none') FROM EMP E,EMP MWHERE E.MGR=M.EMPNO(+)

ENAME NVL(M.ENAME,'HEISMANA
---------- ---------------------
FORD JONES
SCOTT JONES
JAMES BLAKE
TURNER BLAKE
MARTIN BLAKE
WARD BLAKE
ALLEN BLAKE
MILLER CLARK
ADAMS SCOTT
CLARK KING
BLAKE KING
JONES KING
SMITH FORD
KING He is managed by none

SUBQUERY :A query within another quey. A select statement whose output is substituted in the condition of another select statement .(A query is a statement written for returning specific data). The subquery is executed only once. A subquery is enclosed in parenthesis
Conside the following queries.

1) SELECT DEPTNO FROM EMP WHERE ENAME = 'SMITH';
2) SELECT ENAME FROM EMP WHERE DEPTNO= 20

These two queries can be combined as follows.

SQL> SELECT ENAME FROM EMP WHERE DEPTNO = (SELECT DEPTNO FROM EMP WHERE ENAME = 'SMITH');

The SELECT statement inside parenthesis is called subquery and the one whichuses the values returned by it as main query.

SQL> SELECT ENAME FROM EMPWHERE JOB=( SELECT JOB FROM EMP WHERE ENAME = 'SCOTT');

ENAME
----------
SCOTT
FORD

CORRELATED QUERY:In a correlated subquery the table used in outer query refers to the table used in the inner query. The correlated subquery is executed repeatedly once for each row of the main query table.

Query to diplay name of highest salary taker.
SQL> SELECT EMPNO, ENAME FROM EMP AWHERE 1 > ( SELECT COUNT(*) FROM EMP BWHERE A.SAL <> SELECT EMPNO, ENAME,SAL FROM EMP AWHERE 1 > ( SELECT COUNT(*) FROM EMP BWHERE A.SAL > B.SAL)

EMPNO ENAME SAL
---------- ---------- ----------
7369 SMITH 800

SET OPERATORS They combine results from two or more queries into one result.Data typeof all selected colums must be of same type.

UNION: It returns rows of first query plus rows of second query but avoids duplicates.(UNION ALL will give duplicates also).

To display designations in department 10 and 20
SQL> SELECT JOB FROM EMP WHERE DEPTNO=10UNION SELECT JOB FROM EMP WHERE DEPTNO=20;

JOB
---------
ANALYSTCLERKMANAGERPRESIDENT

To display designations in department 10,20 and 30
SQL> SELECT JOB FROM EMP WHERE DEPTNO=10UNIONSELECT JOB FROM EMP WHERE DEPTNO=20UNIONSELECT JOB FROM EMP WHERE DEPTNO=30;

JOB
---------
ANALYSTCLERKMANAGERPRESIDENTSALESMAN

Similar output can be produced by writing
SQL> SELECT DISTINCT JOB FROM EMP;

JOB
---------
ANALYSTCLERKMANAGERPRESIDENTSALESMAN

INTERSECT: It returns rows that are common to all queries.
To diplay the designations that are common in DEPTNO 10 and 20 write.
SQL> SELECT JOB FROM EMP WHERE DEPTNO=10INTERSECTSELECT JOB FROM EMP WHERE DEPTNO=20;

JOB
---------
CLERKMANAGER

MINUS : It returns rows unique to the first query.To diplay designations unique in DEPTNO 10 we can write.
SQL> SELECT JOB FROM EMP WHERE DEPTNO=10MINUS SELECT JOB FROM EMP WHERE DEPTNO=20;
JOB---------PRESIDENT
SQL> SELECT JOB FROM EMP WHERE DEPTNO=10MINUSSELECT JOB FROM EMP WHERE DEPTNO=20MINUSSELECT JOB FROM EMP WHERE DEPTNO=30;
JOB---------PRESIDENT

Overview of Common Schema Objects

A schema is a collection of Database objects. A schema is owned by a database user and has the same name as that user. Schema objects are the logical structures that directly refer to the database's data. Schema objects include structures like tables, views, and indexes. (There is no relationship between a tablespace and a schema. Objects in the same schema can be in different tablespaces, and a tablespace can hold objects from different schemas.)
Some of the most common schema objects are defined in the following section.
Tables
Tables are the basic unit of data storage in an Oracle database. Database tables hold all user-accessible data. Each table has columns and rows. Columns in a table is the different types of information that the table will contain and all instances of such data is stored in rows.
Indexes
Indexes are optional structures associated with tables. Indexes can be created to increase the performance of data retrieval. Just as the index in a book helps you quickly locate specific information, an Oracle index provides an access path to table data.
When processing a request, Oracle can use some or all of the available indexes to locate the requested rows efficiently. Indexes are useful when applications frequently query a table for a range of rows (for example, all employees with a salary greater than 1000 dollars) or a specific row. Indexes are created on one or more columns of a table. After it is created, an index is automatically maintained and used by Oracle. Changes to table data (such as adding new rows, updating rows, or deleting rows) are automatically incorporated into all relevant indexes with complete transparency to the users.
Views
Views are customized presentations of data in one or more tables or other views. A view can also be considered a stored query. Views do not actually contain data. Rather, they derive their data from the tables on which they are based, referred to as the base tables of the views.
Like tables, views can be queried, updated, inserted into, and deleted from, with some restrictions. All operations performed on a view actually affect the base tables of the view.
Views provide an additional level of table security by restricting access to a predetermined set of rows and columns of a table. They also hide data complexity and store complex queries.

Clusters
Clusters are groups of one or more tables physically stored together because they share common columns and are often used together. Because related rows are physically stored together, disk access time improves.
Like indexes, clusters do not affect application design. Whether a table is part of a cluster is transparent to users and to applications. Data stored in a clustered table is accessed by SQL in the same way as data stored in a nonclustered table.
Synonyms
A synonym is an alias for any table, view, materialized view, sequence, procedure, function, package, type, Java class schema object, user-defined object type, or another synonym. Because a synonym is simply an alias, it requires no storage other than its definition in the data dictionary.

Oracle Architecture

User Process - A user process connects with a server process toestablish a session.

Server Process : serves the requests of the user process.

Oracle can be said to be composed of 2 major components :

1. Instance =SGA ( System Global Area) + Background Processes

2. Database - All the Files ( C - D - R) - This is the physical Database

C = Control File , D= Datafile , R= Redo log file

SGA (System Global Area)
------------------------------------------------------------
Major Components :

SGA = Database Buffer Cache + Shared Pool + Redo Buffer

Shared Pool = Library Cache + Data Dictionary Cache

Shared Pool

Library Cache


The compiled version of the statement (Parse Tree)The Execution Plan - tells how to run the statement, determined by theoptimizer.

Data dictionary cache: Used during the Parse phase to resolve objectnames and validate access privileges.

Table and column definitionsUser names, Passwords and Privileges

-Database Buffer cache: Holds the most recently used data.The Least Recently Used (LRU) algorithm is used to age out blocks ofdata in the Buffer Cache. Each buffer in the buffer cache is equal tothe size of the specified data block size.

DB_BLOCK_BUFFERS -parameter sets the number of buffers available.

DB_BLOCKSIZE - parameter sets the size of the data block

-Redo Log Buffer: Registers changes made to the database via theinstance. Size is defined by LOG_BUFFER in the parameter file.

Stores records of changes: Block that changed, the new value, andlocation of the change.The buffer is reused after the redo entries are recorded in the redo logfiles.

PGA (Program Global Area)Used by one Process only and contains the following
Session Information Sort AreaCursor StateStack SpaceDBWR (Database Writer)Writes changed data to the database file when one of the followingconditions are met:Dirty Buffers reach a threshold valueNo free blocks are availableA timeout occurs.

A DBWR checkpoint takes placeLGWR (Log Writer)Writes changes registered in the redo log buffer to the database filewhen one of the following conditions are met:

When the redo log buffer is 1/3 full

A timeout occurs (about every 3 seconds)Before DBWR writes modified blocks to the data files
A transaction COMMITS

SMON (System Monitor)Checks for consistency and initiates recovery.

PMON (Process Monitor)Cleans up resources after a process fails. Watches after the serverprocess

CKPT (Checkpoint Process)Updates the database status information after changes are permanentlywritten to the database file from the buffer cache.

Database Files
-Data files
-Redo log files
-Control files
-Parameter file
-Password file
-Archived redo log files

Query Processing
Parse: checks syntax
Execute:
Fetch: returns data to userDML
StatementsRequires Parse and Execute

Server process reads the data and rollback blocks from the data files.

Blocks that are read then are placed in the Buffer Cache.

Server process locks the data and objects.Server Process records the changes to the rollback and data (new) in theredo log buffer.The Server Process then records the rollback block "old" and updates thedata block "new" in the database buffer cache. Both are marked "dirtybuffers."

What Happens when you login to Oracle Database:

I log in to oracle using Scott? What happens , how oracle serves my request?

Ans:How the Oracle Database processes operate—by decoupling the user process from the server process. If you don't remember it completely,When a user connects to an Oracle database, say with SQL*Plus, Oracle creates a new process to service this user's program.
This new process is called the Oracle server process, which differs from the user's process (sqlplus, sqlplus.exe, TOAD.exe, or whatever it else it may be). This server process interacts with the memory structures such as the System Global Area (SGA) and reads from the data files; if the data is not found in the data block buffers in the SGA, and so on. Under no circumstances is the user's process (sqlplus) allowed to directly interact with the Oracle database. As there are two processes (the user process and the server process) working in tandem to get the work done, this is sometimes known as two-task architecture.If a user process does something potentially disruptive, such as violating the memory management in the host machine, the Oracle database itself is not affected and the damage is limited to the user's process.
Note: that the above applies to Oracle connections in a dedicated server environment. In a multi-threaded server environment, this model is a little different in the sense that a single server process can service more than one user process. It's still two-task, but instead of a 1:1 relation between the server and user processes, it's 1:many.

What are Host based Connections and What are Network-based Connections?

Ans:Host Based Connection:

Suppose the user logs into the database using SQL*Plus.
oracle@myserver::/scripts [practicedb] $ sqlplus bnfdba
SQL*Plus: Release 10.2.0.2.0 - Production on Fri Oct 19 17:44:56 2007
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.

Enter password:

Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit ProductionWith the Partitioning, Real Application Clusters, OLAP and Data Mining options

USER is "BNFDBA"

Instance
----------
PracticeDB

PracticeDB > select sid from v$mystat where rownum=1;

SID
----------
3917

PracticeDB > select spid from v$session s, v$process p
2 where s.sid = (select sid from v$mystat where rownum <2) addr =" s.paddr;"> !ps -eafgrep 10442

bnfdba 17151 17149 1 18:32:58 pts/7 0:00 grep 10442
bnfdba 10442 10212 0 18:27:07 ? 0:00 oraclepracticedb (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

This, of course, assumes that no other SQL*Plus sessions have been running on the server. 
The first one you've seen before (the process for the SQL*Plus session). The second one—process ID 10442—is the server process that Oracle creates for the user. Note the Parent Process ID of the process; it's 10212, which is the process ID of the SQL*Plus session. 
The process name is "oraclepracticedb (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))", which tells you several things. First, the presence of the clause 
LOCAL=YES indicates that this process started due to another process that is running locally, on the same server as the database itself. It also shows PROTOCOL=beq, which means that the connection was made through a bequeath connection. You can also find the information about the server process from the dynamic views.

select spid
from v$session s,v$process p
where s.sid = (select sid from v$mystat where rownum <2) addr =" s.paddr;" practicedb =" (DESCRIPTION" address_list =" (ADDRESS" protocol =" TCP)(HOST" port =" 1521))" connect_data =" (SERVER" service_name =" PRACTICEDB)" onclick="return top.js.OpenExtLink(window,event,this)" href="mailto:oracle@myserver::/scripts" target="_blank">oracle@myserver::/scripts [practicedb] $ sqlplusbnfdba@practicedb.bnfd.com

SQL*Plus: Release 10.2.0.2.0 - Production on Fri Oct 19 17:44:56 2007

Copyright (c) 1982, 2005, Oracle. All Rights Reserved.

Enter password:

Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit ProductionWith the Partitioning, Real Application Clusters, OLAP and Data Mining options

USER is "BNFDBA"

Instance
----------
PracticeDB

PracticeDB > select sid from v$mystat where rownum=1;

SID
----------
3939

After this, if you search for this process:

It shows:

PracticeDB > @vusersid
Enter value for sid: 3939
Press return to continue
Fri Oct 19 page 1 
User Session Statistics

CLIENT SERVER OS ORACLE 
PID PID SID SERIAL USERNAME USERNAME MODULE
---------- ------- ----------- ----- --------- -------- ----- - --------------------------------26917 27292 3939 34941 oracle BNFDBA SQL*Plus

This, of course, assumes that no other SQL*Plus sessions have been running on the server.
Note the process id (26917). Now if you search that process ID Searching for this on the server:

PracticeDB > !ps -eafgrep 26917 
oracle 383 26917 1 17:47:22 pts/7 0:00 grep 26917
oracle 26917 29489 1 17:44:55 pts/7 0:00 sqlplus bnfdba@practicedb.bnfd.com <---This is the Client PID 29489 PracticeDB > !ps -eafgrep 27292

oracle 27292 1 0 17:45:03 ? 0:00 oraclepracticedb (LOCAL=NO) <-----This is the Server PID 27292 oracle 286 26917 1 17:47:11 pts/7 0:00 grep 27292 The parent process is "1". But why isn't it 29489?

Ans:To understand the answer, you have to understand how different Oracle server processes are created. In the first case, when the user did not use a TNS connect string, theconnection was routed directly to the database without going to the listener first. The database created a user process and then handed the control of the process to the 
process owner, a process known as bequeathing—hence the term bequeath process, which showed up in the process name. In the second case, when the user was still on the same server but connected through the listener, the listener created the process for the user—which is known as forking. Similarly, if the user process were running on a different machine (such as a laptop), the connection would have to be made to the listener and the listener would have
created the process. The process was created by a remote server, hence the process name contains the clause LOCAL=NO. Even if the SQL*Plus session was running on the same server, the fact that it was a non-bequeath connection made it a non-LOCAL one.

Note: Depending on the OS, you may not see the parent ID of the server process the same way you see the SQL*Plus session in bequeath connections. In some cases, even through the connection is bequeath, the parent ID will show as "1". Therefore, don't rely on the parent ID to determine what type of server process it is; use the process name instead.

DBA Responsibilities:

As a DBA, my responsibilities are :

We have production systems and non-production (Development, Testing, QA ) systems.

1. Health Check - of database servers. I use monitoring scripts for database health checkup.
2.Code Migration - With time the enhanced facilities and technical upgradation of modules are done through code migrations from development instances to Production systems.
3.Address daily/Weekly Space requests.-Monitor and add spaces as when required.
4. Manage Redo - Monitor Archive log files and troubleshoot if programs eror out due to it.
5.Manage Printers

* Review and design database structures to support business requirements including logical and physical database modeling;
* Install, configure and maintain database instances, creates various database objects, creates users with appropriate roles and levels of security.
* Recommend and implements database security policies that are consistent with security guidelines;
* Recommends and implements database backup and recovery strategies that meet recovery and availability requirements;
* Mentor DBA team members and developers, and delegates appropriately;
* Provide DBA support and technical expertise for multiple applications in development, test, QA and production environments;
* Troubleshoots and documents database problems and resolutions;
* Lead database projects and manages resources required for successful completion of the project and
* Support all database platforms.
* Strong experience in all aspects of database administration is required.

Caution!!! DisClaimer From The Owner of Blog

The views expressed are my own and not necessarily those of my company or Oracle and its affiliates. Also the views and opinions expressed by visitors to this blog are theirs and do not necessarily reflect mine. I’ve been using Oracle since 1998. This blog opines on issues to share my investigations and experiences about these technologies.Before using any script or contents from this site in your production systems , please test it in a development environment

Welcome To Our Big N Fat DBA Launching

Hi Folks,

The very reason of creating this blog is to have a platform to get in touch with you guys, share the aspects that I have experienced and learned on the job. I will share some of what I have learned in my last 6 years with those of you who are new to the role, or considering it.This needs to be an interactive platform as promised by you buddies!!! Its my pleasure to bring you my team of professionals assisting me in bringing the unique state-of-the art DBA Training program to you. In this journey what you will be looking for :

1.Session Road Map
2.Session Summary
3.Things You did not Get/Feed Back/ Comments...
4.My Questions to You...
5.Posts by Sessions


You are going to be the next Big N Fat DBA.... Here We are........
[Some questions I was asked today in the Big N Fat DBA seminar... ]

How many of you want to be Oracle DBAs ?
All Raised their Hands ..... I counted... it was 86.Thats nice ...

From the center Jane had a question... How to become a DBA ?

BNFD: This is a valid question. Though this can be a tough field to get into. Since DBAs ought to be very very responsible for the Organization's most valuable assets " Their Data". So corporates are definitely careful in choosing the chaps for this position. However, on the other side of the coin, there is a certain demand curve that seems to outstrip the experience supply. That being the case, there is some opportunity for the DBA juniors.

Typically, even the DBA juniors will already have some IT experience, either as a developer or as a system administrator. So, for the truly new beis, finding such a position is a place to get your foot in the door. If you are already at a company that uses Oracle, then it's time to start looking for opportunity within your company. For example, if you know your company is going to have a big Oracle-related development project gearing up, it's time to put your hat in the ring to work on it.

Educating yourself is always a good start too. User events, sessions at Big N FAt DBA for beginning DBAs, and reading will all help you as you search for that Oracle DBA job.

Question: What is the minimum background needed before companies will even consider someone for a DBA role?

BNFD: For a new resource, if you don't have two years of previous DBA experience, then it's going to be difficult. Typically, a minimum of five years of IT experience, in addition, is going to be preferred as well. For the newer DBA, OCP certification is a must if you want to be able to sell yourself in spite of lack of experience. Years ago, I knew of at least two DBA contractors who were hired with no Oracle work experience, little overall IT experience, who graduated with an IT degree and got their OCPs. I don't think you will see that in this day and age.

Something else to consider is that many companies already have experienced DBAs on staff, so they may be looking for a junior type, at a lower salary, to do things like basic monitoring and so on. Often these companies will try to hire from within too. This is the kind of opportunity that a DBA want-to-be needs to be on the look out for. While the short-term salary benefits might not be great, the experience you will gain will eventually give you an opportunity for the salaries that experienced DBAs can demand.

Question:Is certification worthwhile for new DBAs?

BNFD: I think its truly necessary.This gives a direction to DBA thought process!!! I do not think that its just a memorization exercise.How ever it provides the basic orientation about the subjects of Oracle in its entirety. Do and have it.

Question:What's the best way to learn about administering Oracle databases?

BNFD: The best ways to learn how to administer Oracle databases are:

1.Find a Guru who knows Oracle and who knows how to mentor. I had a great Guru when I first started working with Oracle Technology and it makes all the difference in the world.
Training is important and helpful.To start with I can suggest you sit down with Oracle and use if for a while (create a few tables, a few users, and learn basic SQL) before you actually go for the training.

2.Read, read, read. Load up on books.

3. Join Oracle related forums and news groups and read them daily.

4.Forums like http://www.dba-oracle.com/, http://www.quest-pipelines.com/, Oracle-L and LazyDBA all will provide insight and education on a daily basis.

5.User group meetings, like Big N Fat DBA - America, IOUG-A, are invaluable to the learning process. It is in these environments that you can learn from a large number of people, on a large number of topics. In addition, you have the University sessions, such as the one I'm doing for the beginning DBA, that can be most helpful in learning about Oracle.

6.Finally, don't believe everything you hear or read, in fact, don't believe anything you hear. Oracle is about few absolutes. Every system is different, and there are many different ways of doing things. Do not build yourself a box of ideas that can never change, because that does not make for the best of DBA's. There are some best practices, and standards, that are somewhat absolute, but for the most part, it's all fluid and situation-dependent.

7.You may choose to learn with us.We have expertise of giving Big N Fat DBA's to the world of DBAs. After creating 56 DBAs in 2 years we are commited to craft your career with our special and needful inputs to build you taking decisions for the benefits of company.

Question:Is it possible to learn on-the-job DBA tasks without being on-the-job?

BNFD: It is possible to learn on-the-job DBA tasks, but the real question is can you retain what you have learned without having the opportunity to practice it? The job is key because a lot of what the DBA does is on-his-feet thinking and responses to out-of-the-ordinary problems. There are numerous technologies and varying complexities. This is hard experience to acquire in a strictly learning environment.

Question:What are the first few questions new DBAs have when sitting down with Oracle for the first time?
BNFD: That depends on if they have any experience with databases at all. If not, then the first question is, where do I start? Things like using SQL, and SQL*Plus are of prime interest because until you can actually talk to the database, there isn't much to do.

If it's a developer who may have done SQL coding, and had some SQL*Plus experience, then the questions are more of the order of, "How do I do the task I've been asked to do?". Then it's a discussion of how to create a user, a table, a discussion on grants and privileges, and some base information to get them started.

When it's an experienced DBA, the first questions generally are something like, "What's the Oracle account password, what's the SYS password, and where is the bathroom and the Coke machine?". That's the difference between beginners and experienced DBAs. Experienced DBA's have time to go to the bathroom and the Coke machine.

Question:What are the most common mistakes beginners make?

BNFD: Believing that there is a single answer to a given question. While there are standards that one should always use, more often than not, the answer to a given question is, it depends. Another common mistake is thinking you know everything after a while. Just when you think you know everything is the exact moment when you actually should realize that you don't know anything. Always rethink your way of looking at things, and check your reality against the thinking of other professionals in the field. Always ask yourself if you are being closed-minded about things. A DBA is paid to think, in my opinion, and that requires an open mind.

Question:What should the top five priorities be for new DBAs on the job?

BNFD: Be on fire. You have to want to learn everything, do everything, consume everything. So you got the DBA job, now is not the time to rest in your new chair and enjoy your success. List out your goals and what you want to learn. A five year plan is a great idea. Listen! Ask Questions! Be involved! Don't just sit back waiting for the create table requests. When it comes to theory, don't believe anything you hear or read until you have tried it yourself. Database rule number one, in my opinion, is that no rule of thumb applies all the time. If you are solely responsible for a database, make darned sure, before you leave your job on day one, that your database can be recovered. Nothing else matters if you can't get that database back. Document everything

Introducing Oracle for beginners

What is Oracle?
Its a RDBMS.Relational Database Management System.
Oracle - It is a Greek word meaning God- Who fulfils all the Dreams.
Oracle Inc is the world's largest supplier of relational databaseproducts (notably Oracle9i), which are used most of the Fortune 500companies and of course by all the leading E-business and Internetsites.
Oracle's relational database was the world's first to support theStructured Query Language (SQL), now an industry standard.Founder of Oracle Inc: CEO Lawrence J. Ellison and a few associatesformed Oracle in 1977.
What is a RDBMS?
A relational database is a collection of data items organized as a setof formally-described tables from which data can be accessed orreassembled in many different ways without having to reorganize thedatabase tables. The relational database was invented by E. F. Codd at IBM in 1970.The standard user and application program interface to a relationaldatabase is the structured query language (SQL). SQL statements are usedboth for interactive queries for information from a relational databaseand for gathering data for reports.
What Are Codd's Rules?
Dr. E. F. Codd's 12 rules for defining a fully relational database.
Note that based on these rules there is no fully relational databasemanagement system available today. In particular, rules 6, 9, 10, 11 and12 are difficult to satisfy.

• Foundation Rule:A relational database management system must manage its stored datausing only its relational capabilities.
• Information Rule All information in the database should be represented in one and onlyone way - as values in a table.
• Guaranteed Access Rule Each and every datum (atomic value) is guaranteed to be logicallyaccessible by resorting to a combination of table name, primary keyvalue and column name.
• Systematic Treatment of Null Values Null values (distinct from empty character string or a string of blankcharacters and distinct from zero or any other number) are supported inthe fully relational DBMS for representing missing information in asystematic way, independent of data type.
• Dynamic On-line Catalog Based on the Relational Model The database description is represented at the logical level in the sameway as ordinary data, so authorized users can apply the same relationallanguage to its interrogation as they apply to regular data.
• Comprehensive Data Sublanguage Rule A relational system may support several languages and various modes ofterminal use. However, there must be at least one language whosestatements are expressible, per some well-defined syntax, as characterstrings and whose ability to support all of the following iscomprehensible:data definitionview definitiondata manipulation (interactive and by program)integrity constraintsauthorizationtransaction boundaries (begin, commit, and rollback).
• View Updating Rule All views that are theoretically updateable are also updateable by the system.
• High-level Insert, Update, and Delete The capability of handling a base relation or a derived relation as asingle operand applies nor only to the retrieval of data but also to theinsertion, update, and deletion of data.
• Physical Data Independence Application programs and terminal activities remain logically unimpairedwhenever any changes are made in either storage representation or accessmethods.
• Integrity Independence Integrity constraints specific to a particular relational database mustbe definable in the relational data sublanguage and storable in thecatalog, not in the application programs.
• Distribution Independence The data manipulation sublanguage of a relational DBMS must enableapplication programs and terminal activities to remain logicallyunimpaired whether and whenever data are physically centralized ordistributed.
• Nonsubversion Rule If a relational system has or supports a low-level(single-record-at-a-time) language, that low-level language cannot beused to subvert or bypass the integrity rules or constraints expressedin the higher-level (multiple-records-at-a-time) relational language.

Client/Desktop/Presentation Tier in Apps

In Oracle Applications 11i , Each user logs on to the oracle Applications through the E-Business Suie Home Page on a Desktop client web browser.The E-Business Suite Home Page provides a single point of access to HTML-based applications, Form-based applications and Business Intelleigence applications.
Q. Does OA registers preferences( Languages)?
Ans: O, Yes, OA retains preferences as you navigate through the system.If you have registered "German" as your preferred language, this preference carries over whether you access Forms-based or HTML-based applications.
DESKTOP TIER:
The forms Client Applet:
- Is a general-purpose presentaion applet.
- Supports all OA Form based products.
- The Forms client applet contain all Java classes required to run the presentation layer of OA forms.
Oracle JInitiator
The forms Client Applet must run with in a JVM on the desktop client.The Oracle JInitiator component allows use of the Oracle JVM on web clients, instead of browser's own JVM.
Q. What will happen if the Jinitiator is not installed in a M/C running Oracle application?
Ans: Traditionally Jinitiator was run as a part of standard Applications sign-on process but with the move to a mainly a HTML-based environment , Jinitiator is only invoked when a user chooses to access functions that require it( e.g. Forms).
If the Jinitiator is not installed , the Web browser will prompt the user to download the required installation executables.
Note:
The forms Client Applet and the used JAR files are downloaded from the web server at the beginning of the client's first session.Less commonly used JAR files are downloaded as needed.More importantly all downloaded JAR files are cached locally on the client, ready for future sessions. This eliminates network traffic that would be involved in downloading them.
Q.How do I check that the JAR files are downloaded at the beginning of the client's session?
Ans:Selecting "Show Console" on the "Basic" Jiitiator will allow you to observe downloading of the JAR files , to confirm that they are being downloaded when they should be.
VVIP: All updates to the JAR files are installed on the application tier and downloaded to the client automatically via the caching mechanism.

Some important FND Tables

Here is the list of few important tables.
Concurrent Manager
FND_CONCURRENT_QUEUES
FND_CONCURRENT_PROGRAMS
FND_CONCURRENT_REQUESTS
FND_CONCURRENT_PROCESSES
FND_CONCURRENT_QUEUE_SIZE

FND
FND_APPL_TOPS
FND_LOGINS
FND_USER
FND_DM_NODES
FND_TNS_ALIASES
FND_NODES
FND_RESPONSIBILITY
FND_DATABASES
FND_UNSUCCESSFUL_LOGINS
FND_LANGUAGES
FND_APPLICATION
FND_PROFILE_OPTION_VALUES

AD / Patches
AD_APPLIED_PATCHES
AD_PATCH_DRIVERS
AD_BUGS AD_INSTALL_PROCESSES
AD_SESSIONS
AD_APPL_TOPS

Oracle 8i & 9i DBA certification

The Oracle Certification Program continues to be the most popular certification among all database vendors.Oracle has completely revamped the DBA Certification track with the release of the Oralce9i database. To best understand the modifications that the DBA track has undergone, it's best to first review the Oracle8i DBA track.
To become an Oracle8i Certified Professional (OCP) DBA, the certification candidate needed to pass five exams:
Introduction to Oracle: SQL and PL/SQL
Oracle8i: Architecture and Administration
Oracle8i: Backup and Recovery
Oracle8i: Performance Tuning
Oracle8i: Network Administration
With Oracle9i, the DBA Certification track is now divided into 2 primary tiers, as well as a third Master-level tier:
Oracle9i Certified Associate (OCA)
Oracle9i Certified Professional (OCP)
Oracle9i Certified Master (OCM)

Oracle9i Certified Associate (OCA) :

Oracle’s 9i OCA DBA (Database Administrator) certification is for “apprentice skill level” IT professionals who have have a foundation of knowledge that will allow them to act as a junior team member working with database administrators or application developers. Associate (OCA) requires passing two exams:
Introduction to Oracle9i: SQL
Oracle9i Database: Fundamentals I

Oracle9i Certified Professional (OCP):
The OCP tier was designed for mid to senior-level DBAs with one or more years of focused Oracle DBA experience. The knowledge and skills obtained from completing this tier of the certification program prepare the DBA to provide the full assortment of DBA-related tasks...from Database Design and Development to production tasks such as Performance Tuning and Backup & Recovery.
The Oracle9i Certified Professional (OCP) requires passing the two exams in the OCA Tier, plus two additional exams:
Oracle9i Database: Fundamentals II
Oracle9i Database: Performance Tuning

Oracle9i Certified Master (OCM):
There is a third tier to the Oracle9i DBA Certification Track, the OCM. This track is targeted at very senior level DBAs and Oracle consultants.The OCM designation first requires that you obtain your Oracle9i OCP credential. Once this is secured, Oracle requires that you take two advanced Oracle courses from Oracle University. These are Instructor-led courses

Oracle9i: Program with PL/SQL
Oracle9i: Advanced PL/SQL
Oracle9i: SQL Tuning Workshop
Oracle9i: Database: Real Application Clusters
Oracle9i: Database: Data Guard Administration
Oracle9i: Database: Implement Partitioning
Oracle9i: Data Warehouse Administration
Oracle Net Services: Advanced Administration
Oracle9i: Advanced Replication
Oracle9i: Enterprise Manager
Once you've completed the course requirements, you can then register for and take the "practicum" exam. The practicum is a two-day, hands-on performance exam that requires the candidate to perform a number of tasks using Oracle9i on the Linux platform. Some of the skills that need to be proven include: database configuration, replication, partitioning, parallel operations, diagnostics, troubleshooting, performance tuning, and backup and recovery...all with "real-world" scenarios.