Oracle's Coracle

How to install and use statspack utility

2009-05-03T06:00:00.000-07:00

STEP 1: Create required tablespaces for statspack utility.

Statspack utility requires two tablespaces to run:

(a) PERFSTAT tablespace

(b) TEMP tablespace

Command to get list of available tablespaces:

SQL> SELECT *

2> FROM DBA_TABLESPACES;

Other "less preferred" commands to get tablespaces list:

SELECT * FROM DBA_DATA_FILES;

SELECT * FROM DBA_TEMP_FILES;

SELECT * FROM v$datafile;

SELECT * FROM v$tempfile;

Command to create PERFSTAT tablespace (login as User:SYS to execute):

SQL> CREATE TABLESPACE PERFSTAT

2 DATAFILE 'd:\oracle\oradata\oracle\perf01.dbf' SIZE 100M

3 /

or,

SQL> CREATE TABLESPACE PERFSTAT

2 DATAFILE 'd:\oracle\oradata\oracle\perfstat.dbf' SIZE 100M AUTOEXTEND OFF

3 EXTENT MANAGEMENT LOCAL AUTOALLOCATE

4 LOGGING

5 ONLINE

6 SEGMENT SPACE MANAGEMENT AUTO

7 /

STEP 2: Locate and Execute script spcreate.sql as user:SYS

Standard Location of script: $ORACLE_HOME/RDBMS/admin/spcreate.sql

Example: D:\oracle\ora92\rdbms\admin\spcreate.sql

While being executed, script will ask following questions:

a) Password for newly created user perfstat

b) Tablespace name

c) Temporary tablespace name

STEP 3: Login as user:perfstat now and generate diagnosis data by executing "statspack.snap" twice

Details:

Execute the following command twice with a few minutes of interval in between(lets say at 9 am and then at 9:10 am)

SQL> exec statspack.snap;

STEP 4: Note down SNAP_IDs of the snapshots created above

In our case, since we have just generated statspack package, IDs will be 1 and 2.

You can confirm snapshot IDs using following command:

SQL> SELECT SNAP_ID, SNAP_TIME

2> FROM STATS$SNAPSHOT;

3> /

STEP 5: Generate statspack report by executing "spreport.sql"

STANDARD location of script to run report : $ORACLE_HOME/RDBMS/admin/spreport.sql

Example: D:\oracle\ora92\rdbms\admin\spreport.sql

While executing, script will ask you to specify

a) Name of the new report being created.

b) Two snapshot IDs to compare

STEP 6:

The report thus created can be opened in any text editor and viewed (In our example, since the two SNAP_IDs were for snapshots taken at 9 am and 9 :15 am, the report will show compare performance data generated at 9 am with that generated at 9:15 am.

-Param

=====================

Want to know more? Visit:

http://www.akadia.com/services/ora_statspack_survival_guide.html

http://ist.uwaterloo.ca/~baumbach/ORACLEnotes/ST_statspack.html

http://www.troygeek.com/articles/Statspack101-Installing/

http://www.coderanch.com/t/79947/Oracle-OAS/why-datafile-TEMP-tablespace-not

Daily Diary with Random thoughts!

2009-02-12T04:28:00.000-08:00

Everyday I visit so many web pages and come accross so much information pages that I end up thinking of starting my on version of "digg". I am dying to have a method of recording it all forever and save it for rainy days.
But you know, this lethargic nature of mine will kill me.

At last I am trying to start from today: writing(documenting) whatever I find everyday and treat this part of blog as my "daily-diary" - a place where I can write whatever I want to and record it for future dig!
So here's my first attempt.

1. Tried to brush up my knowldege of flashback version queris in Oracle 10G a bit today.
Fav link is again "Arup Nanda's top 20 features for DBAs"
http://www.oracle.com/technology/pub/articles/10gdba/week1_10gdba.html

A summary for quick reference in future:
TOPIC: FLASHBACK VERSIONS QUERY
Parameters:
undo_management = AUTO
undo_tablespace = UNDOTBS1
undo_retention = 900
Example:
select num, name, type, value from v$parameter where upper(name) like '%UNDO%'
Pseudocolumns used:
versions_starttime, versions_endtime, versions_xid, versions_operation, versions_startscn, versions_endscn
where conditions allowed:
versions between timestamp minvalue and maxvalue
versions between scn minvalue and maxvalue
Tables available:
FLASHBACK_TRANSACTION_QUERY
(Let's see if I get time to try these things practically at home tonight)

2. Also visited arup's own blog on blogspot and read his blog on "effective communication" here:
http://arup.blogspot.com/2008/10/about-clear-communications.html

3. Stumbled upon a great website today:
http://www.oracle-developer.net/
Visited an article related to bulk load(collections) and liked it:
Topic: "Introduction to bulk pl/sql enhancements in 9i"
http://www.oracle-developer.net/display.php?id=201
(explains with example how to use cast() and multiset() functions)

4. Somebody asked me about CAMEO today and I was like, dumbstruck! She linked "cameo" with "oracle database knowledge", "restructuring of existing computing environment rerquired due to merger of a newly acquired organisation in existing setup" and what not!

Google says: CAMEO means "Consolidated Access Management and Enterprise Optimization"
Will I ever get time to (1) google on such topics (2) and understand a bit of them the same day?
I don't think so.

INDEX BY TABLES (Collections)

2008-11-13T05:14:00.000-08:00

INDEX BY TABLE:

Here is a Program which uses all collection functions available to index by tables. Such programs come very handy when you have very less time to revise something without even going through the documention:
create or replace procedure proc1
as
type name_type is table of varchar2(20) index by binary_integer;
names name_type;
i number;
begin
-- Index by table values can be used as part of select query
select ename into names(1) from emp where empno = 7654;
dbms_output.put_line(names(1));

-- Index by table values can be populated using direct assignments
names(2) := 'Param';
names(3) := 'jeet';
names(4) := 'singh';

dbms_output.put_line('names.count: 'names.count); --function:count
dbms_output.put_line('names.first:'names.first); --function:first
dbms_output.put_line('names.last:'names.last); --function:last

dbms_output.put_line('Displaying values using for loop...');
for i in names.first..names.last --functions:first,last
loop
dbms_output.put_line('names('i'): 'names(i));
end loop;

dbms_output.put_line('Displaying values using while, ascending order..');
i:= names.first;
while i <= names.last
loop
dbms_output.put_line('names('i'): 'names(i));
i := names.next(i); --function:next
end loop;
dbms_output.new_line;
dbms_output.put_line('Deleting element #2...');
names.delete(2); --function:delete
if names.exists(2)
then
dbms_output.put_line('Element #2 exists'); --function:exists
else
dbms_output.put_line('Element #2 does not exist');
end if;
dbms_output.put_line('Displaying values using while loop, descending order...');
i := names.last;
while i >= names.first
loop
dbms_output.put_line('names('i'): 'names(i));
i := names.prior(i); --function:prior
end loop;
dbms_output.new_line;
exception
when others then
dbms_output.put_line('Exception occurred, sqlcode:'sqlcode', sqlerrm:'sqlerrm);
end proc1;

=====================
Summary of Collection functions used above:
count : returns count of elements in a collection (here, index by tables).
exists: returns TRUE or FaLSE on the basis of whether the specified element exists in a collection
first : retrurns index of first element in a collection
last : return index of last element in a collection
prior : returns index of previous element in a collection
next : returns index of next element in a collection
delete : deletes specified element in a collection
Collection functions not available to index-by-tables:
limit: returns maximum number of elementrs for a collcection
extend : adds elements to a collection

NOTE:
"Index by table" is the oldest collection type introduced in Oracle 7.
"Index by tables" save values in the form of key-value (index-value) pairs.
SYNTAX:
Type is TABLE of index by BINARY_INTEGER;
EXAMPLE:
Type name_list_type is TABLE OF varchar2(20) index by BINARY_INEGER;
can only be of type binary_integer.
There is virtually no limit of storing values in an index-by-table. The limit is restricted by storage limit of binary_integer type which is -2GB to +2GB (which is treated as virtually unlimited).
Elements in an index by table need not be in order, but they can be mapped to a C++ or java array ( for that element need to be in order)
Limitations:
Index by table cannot be used as a table column type.

How good you understand PL/SQL Loops?

2008-11-11T04:34:00.001-08:00

Let's see how good you know about Pl/SQL loops.
Here, I'll show you a code and you need to guess the expected output. Simple?
Be Honest and dont scroll down the question to see answer beforehand!
=============================
Q1. In the following code, what will be the output of line marked as "(Q)" below.
declare
begin
for i in 1..5
loop
dbms_output.put_line('i: 'i);
end loop;
dbms_output.put_line('After loop, i: 'i); -- (Q)
end;

ANSWER: The above code will show an error !
dbms_output.put_line('After loop, i: 'i); -- (Q)
*
ERROR at line 8:
ORA-06550: line 8, column 44:
PLS-00201: identifier 'I' must be declared
ORA-06550: line 8, column 3:
PL/SQL: Statement ignored

EXPLAINATION: There is no variable 'i' defined in the above code block. Variable i used by for-loop here is actually an 'on the fly' variable which is local to the loop. It has no existance outsiode the for loop. Hence the error.

=============================
Q2. In the following code, what will be the output of line marked as "(Q)" below.
declare
i number:= 0;
begin
for i in 1..5
loop
dbms_output.put_line('i: 'i);
end loop;
dbms_output.put_line('After loop, i: 'i); -- (Q)
end;

ANSWER: Output is "After loop, i: 0"

EXPLAINATION: As explained above i used by for loop is an "on the fly" variable whih loses its existance once the loop ends. The line marked as '(Q)' is printing value of another variable 'i' which is declared in the declare section.

=============================
Q3. In the following code, what will be the output of line marked as "(Q)" below.
declare
begin
for i in 1..5
loop
dbms_output.put_line('i: 'i);
i := i + 1;
end loop;
end;

ANSWER: The above code will show an error !
i := i + 1;
*
ERROR at line 7:
ORA-06550: line 7, column 5:
PLS-00363: expression 'I' cannot be used as an assignment target
ORA-06550: line 7, column 5:
PL/SQL: Statement ignored

EXPLAINATION: Variable 'i' is a loop variable here and hence its value cannot be modifed by us.
=============================
Q4. In the following code, what will be the output of lines marked as "(Q1)" and "Q2" below.
declare
i number:= 0;
n_start number := 1;
n_stop number := 5;
begin
for i in n_start..n_stop
loop
n_stop := 3;
dbms_output.put_line('i: 'i); -- (Q1)
end loop;
dbms_output.put_line('n_stop: 'n_stop); -- (Q2)
end;

ANSWER: Line marked as (Q1) will print all values from 1 to 5. Line marked with (Q2) will print "3".

EXPLAINATION: Loop is supposed to run from "p_start" value to "p_stop" value. So apparantly, if p_stop value changes from 5 to 3, loop should print values from 1 to 3 only. But in reality this doesnot happen. Once the loop starts running, run-range is freezed! So no matter you change n_stop while the loop is running, this willnot affect the loop range.

Object Types in Oracle

2008-11-10T13:49:00.000-08:00

A step by step guide to "Usage of objects in Oracle"

========================================

This article answers the following questions:

1. When was ORDBMS introduced?

2. How do we create objects in Oracle?

3. How to use Objects as oracle table column type?

4. How and where to use object constructors?

5. How to query from a table containing object types?

========================================

A little background first...

With Oracle 8i, oracle introduces the concept of object relational DBMS or ORDBMS. Starting 8i, Oracle allows you to incorporate object oriented concepts in a DBMS too.

Terms like object, constructor, attribute etc are now part of a oracle's vocabulary too.

Let us practically learn how to create objects in Oracle and how to use them as part of an oracle table.

We start with the basic "create table" command here. Execute the following:

SQL> create table OLD_EMP

2 (

3 id number(2),

4 f_name varchar2(20),

5 m_name varchar2(20),

6 l_name varchar2(20),

7 add_line1 varchar2(20),

8 add_line2 varchar2(20),

9 city varchar2(20),

10 state varchar2(20),

11 pincode varchar2(10),

12 homephone varchar(20),

13 cellphone varchar2(20)

14 );

Table created.

Now that we have created table OLD_EMP, let us put a row in it:

SQL> insert into old_emp

2 values ( 19,

3 'Paramjeet', 'Singh', 'Sasan',

4 '1001, CASTLE', 'BAVDHAN', 'PUNE', 'MAHARASHTRA', '411021',

5 '020-112233', '+91 985 XX5 XXX5'

6 );

1 row created.

So this is the basic method of creating a table in Oracle. Correct. But I want you to revisit the table definition

create table OLD_EMP

(

id number(2),

f_name varchar2(20),

m_name varchar2(20),

l_name varchar2(20),

add_line1 varchar2(20),

add_line2 varchar2(20),

city varchar2(20),

state varchar2(20),

pincode varchar2(10),

homephone varchar(20),

cellphone varchar2(20)

);

As you can see above (shown in different colors), we can easily divide the above data in such a way that related data can be kept together separately as independent objects). Correct?

Ok so lets' do that. We will now create three separet object types to save the the above data in three groups. Use following commands to create required objects:

SQL> create or replace type full_name_type as OBJECT

2 (

3 f_name varchar2(20),

4 m_name varchar2(20),

5 l_name varchar2(20)

6 );

7 /

Type created.

SQL> create or replace type address_type as OBJECT

2 (

3 add_line1 varchar2(20),

4 add_line2 varchar2(20),

5 city varchar2(20),

6 state varchar2(20),

7 pincode varchar2(10)

8 );

9 /

Type created.

SQL> create or replace type contact_type as OBJECT

2 (

3 homephone varchar(20),

4 cellphone varchar2(20)

5 );

6 /

Type created.

Now that we've created all three objects required, we can create an object relational table "NEW_EMP" as shown below:

SQL> create table new_emp

2 (

3 id number(2),

4 name full_name_type,

5 address address_type,

6 contact_no contact_type

7 );

Table created.

Looks so simple and clear now. Isn't it?

Q. But how do we insert values in this new table now?

Old style of inserting values will not work for such a table because it has only four publicly visible columns now.

So now we need to insert values using constructors. Execute the following command:

SQL> insert into new_emp

2 values ( 19,

3 full_name_type('Paramjeet', 'Singh', 'Sasan'),

4 address_type('1001, CASTLE', 'BAVDHAN', 'PUNE', 'MAHARASHTRA', '411021'),

5 contact_type('020-112233', '+91 985 XX5 XXX5')

6 );

1 row created.

Notice the way constuctors are used (shown in bold) here to insert values in the objects which are based on new objects types (or, datatypes) introduced in table:NEW_EMP.

Now, let us try selecting from both the tables to see how differntly data is inserted and selected.

SQL> select * from old_emp;

ID F_NAME M_NAME L_NAME

---------- -------------------- -------------------- --------------------

ADD_LINE1 ADD_LINE2 CITY

-------------------- -------------------- --------------------

STATE PINCODE HOMEPHONE CELLPHONE

-------------------- ---------- -------------------- --------------------

19 Paramjeet Singh Sasan

1001, CASTLE BAVDHAN PUNE

MAHARASHTRA 411021 020-112233 +91 985 XX5 XXX5

SQL> select * from new_emp;

----------

NAME(F_NAME, M_NAME, L_NAME)

--------------------------------------------------------------------------------

ADDRESS(ADD_LINE1, ADD_LINE2, CITY, STATE, PINCODE)

--------------------------------------------------------------------------------

CONTACT_NO(HOMEPHONE, CELLPHONE)

--------------------------------------------------------------------------------

FULL_NAME_TYPE('Paramjeet', 'Singh', 'Sasan')

ADDRESS_TYPE('1001, CASTLE', 'BAVDHAN', 'PUNE', 'MAHARASHTRA', '411021')

CONTACT_TYPE('020-112233', '+91 985 XX5 XXX5')

As we can see, in case of table:NEW_EMP, the whole address of employee "paramjeet" is treated as a single object in table NEW_EMP;

Q. What if we want to select on the basis of an object's attribute (like, on the basis of name.f_name or address.city)?

The above task can easily be performed on OLD_EMP using a simple "select.. where" query. Let's see...

SQL> select f_name from old_emp where l_name = 'Sasan';

F_NAME

--------------------

Paramjeet

Q. But will the same query work for NEW_EMP too?

Let's see that too...

SQL> select f_name from new_emp where l_name = 'Sasan';

select f_name from new_emp where l_name = 'Sasan'

ERROR at line 1:

ORA-00904: "L_NAME": invalid identifier

OOPs, the query is not working the age old way. Do you know why ? Because, l_name is an attribute of object name, it is hidden and not visible to outside World. So we should use "object_name.attribute_name" style to access that, the way we do in Object oriented languages like C++ and java.

Try This:

SQL> select name.f_name from new_emp where name.l_name = 'Sasan';

select name.f_name from new_emp where name.l_name = 'Sasan'

ERROR at line 1:

ORA-00904: "NAME"."L_NAME": invalid identifier

OOPS, this is not working either! Why ? Now an oracle restriction comes in: In such cases we have to use table alias name to access object's attribute value.

So the style to try now is "table_alias.object_name.attribute_name".

SQL> select e.name.f_name from new_emp e where e.name.l_name = 'Sasan';

NAME.F_NAME

--------------------

Paramjeet

=======================================================

Interested in learning more about Object type in oracle? Visit the following link:

http://www.stanford.edu/dept/itss/docs/oracle/10g/appdev.101/b10807/10_objs.htm

Cheers!

-Param

Difference of one second between sysdate and current_date results

2008-11-10T03:20:00.000-08:00

The following issue has been troubling me since yestarday morning.

Issue: I expect sydate result to match exactly with current_date result, but 50% of the times I get a difference of one second between the two results and I am not able to figure out why.

Following are the time zone settings I have:
SQL> select dbtimezone, sessiontimezone from dual;
DBTIME SESSIONTIMEZONE
------ ---------------
+00:00 +05:30
My Database server machine is set to Indian time zone +5:30

Though sysdate is not timzone aware but current_date is, I dont think this should stop the two functions from displaying me the same date and time.
When I try to fire the same following command a multiple times quickly (so as to fire it 2-3 times within a second), bot the commands donot print the same time always:

SQL> select to_char(sysdate, 'dd-mon-yyyy hh:mi:ss'), to_char(current_date, 'dd-mon-yyyy hh:mi:ss') from dual;

SQL> /
TO_CHAR(SYSDATE,'DD- TO_CHAR(CURRENT_DATE
-------------------- --------------------
10-nov-2008 03:45:45 10-nov-2008 03:45:45

SQL> /
TO_CHAR(SYSDATE,'DD- TO_CHAR(CURRENT_DATE
-------------------- --------------------
10-nov-2008 03:45:45 10-nov-2008 03:45:46

SQL> /
TO_CHAR(SYSDATE,'DD- TO_CHAR(CURRENT_DATE
-------------------- --------------------
10-nov-2008 03:45:46 10-nov-2008 03:45:46

NOTE:
(1) Just to reaffirm my query I have tried my query on three different database servers, but everywhere I am faced the same issue.
(2) Timestamp commands do not have any issues, They give me accuracy in seconds correct to 6 decimal places, which is expected.
SQL> select systimestamp, current_timestamp from dual;
SYSTIMESTAMP CURRENT_TIMESTAMP

----------------------------------- -----------------------------------

10-NOV-08 03.49.55.358227 PM +05:30 10-NOV-08 03.49.55.358257 PM +05:30

Searched google for possible answer but to no avail.

Now thinking of asking Tom.....

Are triggers trustworthy?

2008-11-05T06:42:00.000-08:00

Why do DBAs suggest avoiding trigger usage in production Databases?

Somebody asked me this question recently and forced me to reconsider my regular "short" answer I always give. Below is the list of advantages and disadvantages of using triggers.

Advantages:

1. Triggers can be used to apply complex table constraints automatically, which we cannot apply using check constraints.
For example, check constraints cannot refer to other tables, but triggers can do that.

2. Triggers are event based so unlike procedures and functions they can be more productive in many cases where you want to automate a validation/verification process.

3. Database Triggers are executed whenver table data gets modified and they can evaluate both "before" and "after" state of data undergoing modification, and take decision effectively.

4. "Instead of" triggers are used to make complex (readonly) views MODIFIEABLE. We have to make architectural changes if we dont want to use "instead of" triggers.

5. Triggers can be used to implement cascading modificaton effect, a more complex form which may not be possible using cascade constraints (like "on cascade delete" etc). For example, If table A undergoes a specific data change, a few tables need to be automatically updated based on listed where conditions. Oracle procides "Cascade constraint" can only take care of tables linked with foreign key constraints.

Disadvantages:

1. Triggers are fired whenever Data undergoes modification. This can slow down the data modification speed if trigger is fired for every row data change again n again.

2. In cases where mass insertions are going on and the table has an "on insert" trigger, insertions will be pathetically slow.

3. Though Oracle provides us the facility of disabling triggers temporarily to counter act above two problems, this can result in too much of tracking (user has to keep track of what triggers are currently enabled/disabled) and possible human errors.

4. Since Oracle allows multiple triggers on the same table and on the same invocation level too, this can create confusion while understanding the code. For example, it is very difficult to understand the process flow if the table has multiple triggers written on it for various conditions. In such a case developer has to always keep a complex diagram depicting prcess flow with numberless arrows drawn here n there.

5. Sometimes multiple triggers create an infinite, uncontrolled cascading effect.
For example: When Table-A is updated Trigger-A is fired. Body of Trigger-A contains a statement to update Table-B. Table-B also has a simialr trigger-B containing code to modify
table-C. Now, similar is the case with Table-C too, it also has Trigger-C.
Such a case will result in cascaded call of multiple triggers which is difficult to understand and handle. The condition becomes worse when you find Trigger-C actulally containing code to modify Table-A leading to an INFINITE LOOP!

6. Oracle cannot guarantee order of invocation of two triggers defined on the same level with different functionalities.

For example:
Trigger A says: If join_date is > sysdate, donot allow this.
Trigger B says: If Join date is NULL, replace it with sysdate +1

Now if both the triggers are on the same level of invocation (like, both are defined as "before insert on same table"), Oracle cannot guarantee which of the two will be fired first. This leads to inconsistent results everytime!

The following statement proves this:

SQL> insert into EMP(empno, ename) values (1,'PARAMJEET SINGH');

Case 1: If trigger A fires before trigger B
Result: Trigger A says nothing as join_date is NULL. But now trigger B fires and puts join_date as sysdate+1 in this new row
successfully!

Case 2: If trigger B fires before trigger A
Result: Trigger B puts join_date as sysdate+1 in this new row successfully. But then Trigger A fires and throws error making the previous insertion UNSUCCESSFUL!

UPDATES:

Jan 15, 2009:

Find a related article today and thought of sharing with you all:

"The Trouble with Triggers" by Tom Kyte

link: http://www.oracle.com/technology/oramag/oracle/08-sep/o58asktom.html

============END OF THE DOCUMENT=================

Oracle Interview Questions (Part 2)

2008-11-04T15:52:00.000-08:00

More Questions.....

25. Whats are the datatypes available in Oracle to store currency? Which datatype should I use to store currency with 6 decimal places of accuracy?

26. Why do we use views? List some real life problems where views can provide solutions. What are the advantages and disadvantages of views.

27. My organisation has offices accross the globe. What date type should I use to manage all timezones?

28. How many types of indexes are present in Oracle? What are clustered indexes?

29. What is XParse? How do we use XML in Oracle?

30. What are triggers? Why should/shouldnt we use triggers in a production database (i.e. list disadvantages of using triggerts)?

31. Differentiate between stored functions and stored procedures? What datatype(s) are not allowed to be passed as parameters in an Oracle function/procedure?

32. How to change table rows into columns and columns into rows using oracle commands?

Explain Plan (Part 1)

2008-11-04T03:01:00.000-08:00

Hi Friends,
Here comes my attempt to gather all important information at one place for future reference. I'll try to keep it as simple as possible.

Q. What is "Explain plan"?
Every SQL statement has to go through three phases:
(1) Parsing of SQL statement
(2) Creation of execution plan
(3) Actual execution of SQL statement
Explain Plan" helps us understand the second phase. It is a tool to view execution plan for a SQL. It is not a universal RDBMS command. It is an Oracle specific command.

Q. How to execute "explain Plan" command?
The syntax is fairly simple! You just need to type in "explain plan for" followed by the SQL statement you want to see execution plan for.

Typical Syntax:
SQL> Explain plan for

Try this command:
SQL> explain plan for select * from EMP;
Explained

The above command simply generates the execution plan and save it in a table called "plan_table" by default. If successful, it prints a message "Explained" (and not the actual execution plan!).

Q. How to view execution plan:
After creating execution plan, try the following command to view it:

SQL> select * from table(dbms_xplan.display);

Here,
-> table() is an oracle provided function
-> dbms_xplan.display() is a packaged function used to view the execution plan generated. As seen above, we have not passed any parameters into this function here, so it will show us the latest plan generated.

COMMON ERRORS WE FACE SOMETIMES:

Error# (A): plan_table does not exist (ORA-02402: PLAN_TABLE not found)
Solution:
Create plan_table using oracle provided script "utlxplan.sql". You can find it here in Windows: \rdbms\admin\utlxplan.sql (windows specific)

Example:
SQL> @D:\oracle\ora92\rdbms\admin\utlxplan.sql
Table created.

If you are not able to locate it, execute below script (which is Oracle9i Release 9.2.0.1.0 specific):
create table PLAN_TABLE (
statement_id varchar2(30),
timestamp date,
remarks varchar2(80),
operation varchar2(30),
options varchar2(255),
object_node varchar2(128),
object_owner varchar2(30),
object_name varchar2(30),
object_instance numeric,
object_type varchar2(30),
optimizer varchar2(255),
search_columns number,
id numeric,
parent_id numeric,
position numeric,
cost numeric,
cardinality numeric,
bytes numeric,
other_tag varchar2(255),
partition_start varchar2(255),
partition_stop varchar2(255),
partition_id numeric,
other long,
distribution varchar2(30),
cpu_cost numeric,
io_cost numeric,
temp_space numeric,
access_predicates varchar2(4000),
filter_predicates varchar2(4000));

Error # (B): Old version of plan_table exists (In this case, plan of course gets created with no errors, but shows a warning msg when you try to view it using dbms_xplan.display)
Solution: Drop old version plan_table and recreate it as shown above.

Error # (C): Error: cannot fetch last explain plan
Solution: This happens when you try to view execution plan without even creating it. An important point to note here is that plan_table gets emptied as soon as the user session ends. So if you generate an execution plan and then your session somehow ends, you simply lose the plan generated.

More to come on this topic.....

Oracle Interview Questions (Part 1)

2008-10-23T23:30:00.000-07:00

1. Explain Oracle architecture.

2. Explain the dIfference between "where...in()" clause and "where exists()" clause. Which one is faster and why? If the column used in "where...in()" clause has an index on it, Which one of the two will be faster?

3. Explain "bulk collect"? Suppose we have a table emp with over 1 million rows and we want to update all records by
increasing salaries by 2%. Can we use bulk collect in such a case ? What issues can we face? Will I face any memory issues? If Yes, how to deal with them?

4. Explain "Pragma autonomous transaction". Suppose there are three update queries inside a block with "pragma autonomous transaction" declaration and 1st query fails while execution, what will happen?

5. Differentiate between "direct path load" and "conventional path load" (context: SQL Loader)? Which of the two is set by default? What happens when SQL_Loader execution fails in between? Does it result in partial load of data into table?

6. What is default commit setting during SQL Loader execution: Autocommit ON or OFF?

7. Whenever we execute a DML query, it creates redo logs. Can we disable this logging somehow? What are the implications of disabling this logging?

8. What is flashback query ? How do we use it?

9. What is recycle bin in Oracle? How do we use it? How is it related with DDL commands? Can I get my deleted tables back using recycle bin even after firing a few DML and DDL commands after deletion (of tables)?

10. Can I get history of recently executed DML and DDL commands using recycle bin ? Or, undo some of these commands ?

11. Explain "append clause"(context: SQL Loader). Is it faster than using "insert" clause? If yes, Why? If "append" clause is faster, why dont we use this clause always?

12. While using cost based optimizer, if I change the order of tables specifed in a join query, does the execution plan
change? Does a join query always use nested loop join (as shown by explain plan command) ?

13. Explain bitmap indexes? How does it work? Does it also use rowids to search rows like binary indexes do?

14. List drawkacks of using triggers and indexes (if any)? Explain performance implications behind using these?

15. What is the difference between "before" and "after" triggers? Is there something which can be done only by one of them and not by the other? Anyone of these two can be used for validation of data or rollbacking a transaction.Then, why do we need two types in Oracle ? (For examples, all sybase triggers are only "after" triggers)

16. Explain meaning of transaction?

17. Users A and B are trying to update same rows of a table at the same time. How does oracle deal with such a situation? Can both updates be made successful?

18. User:A is updating 10 million rows in a table which is taking a lot of time. User:B is also trying to update some 10 rows in the same table. Both the updates have 5 rows in common. Now, there can be a case where user B's task has to wait for User A's task to complete which will obviously create unneccessary wait time for User B. Can we avoid this somehow? Can "nowait" option help in such a case?

19. What are the implications of using functions in a query? How does function usage affect query performance?

20. What are function based indexes? What is the difference between user defined functions and oracle provided functions (mainly, performance related)?

21. "Explain plan" command normally shows query execution plan in the form of nested loops with indented lines. What is the order of reading and understanding the plan shown? Is it "from middle to top" (i.e. from the innermost nested loop to the top of the loop upwards)? Or, is it "from bottom of the shown plan to the top"? Do we use the same method always ?

22. How do we evaluate query-cost using "explain plan"? What do you mean by "CPU cost" and "query cost" in general?

23. Does "commit" command ensures changes are written in Datafiles? What is the relation between commit, SCN and checkpoints?How often does checkpoint happen? Can we force a checkpoint?

24. How to do exception handling while using bulk collect? What happens when while saving 10000 rows you encounter an issue at the 500th row?

===============================================

How to derive rownum from rowid ...

2008-10-21T14:02:00.000-07:00

Here is how to derive a rownum value from a rowid value.....
Function:rowid_row_number() of package:dbms_rowid can help us do that:

Execute the following query to see that:
SQL> select rowid, dbms_rowid.rowid_row_number(rowid), rownum from EMP;

ROWID DBMS_ROWID.ROWID_ROW_NUMBER(RO ROWNUM
AAAHW7AABAAAMUiAAA 0 1
AAAHW7AABAAAMUiAAB 1 2
AAAHW7AABAAAMUiAAC 2 3
AAAHW7AABAAAMUiAAD 3 4
AAAHW7AABAAAMUiAAE 4 5

Hence,
dbms_rowid.rowid_row_number(rowid) +1 = rownum

ROWID, UROWID and ROWNUM in Oracle

2008-10-12T13:48:00.000-07:00

A simple explaination of ROWID, UROWID and ROWNUM (Oracle)...

ROWID:
Rowid is a pseudocolumn provide by Oracle. Oracle generates and assigns a unique binary number (a rowid) with every row inserted. Rowid is not an actual column but it behaves like one. It can be printed using a simple query like:

SQL> select rowid, empno, ename, job from emp;
ROWID EMPNO ENAME JOB
------------------ ----- ---------- ---------
AAAHW7AABAAAMUiAAA 7369 SMITH CLERK
AAAHW7AABAAAMUiAAB 7499 ALLEN SALESMAN
AAAHW7AABAAAMUiAAC 7521 WARD SALESMAN
AAAHW7AABAAAMUiAAD 7566 JONES MANAGER
AAAHW7AABAAAMUiAAE 7654 MARTIN SALESMAN
AAAHW7AABAAAMUiAAF 7698 BLAKE MANAGER

Benefits of using ROWIDs
Searching records using rowid is the fastest way to access rows. This means, it's even faster than using primary
key (Internally, primary key also uses rowid to search a record).

Restricted Vs Extended ROWIDs:
Before Oracle version 8, Rowid had "restricted" format. We now call it "restricted" because it was not able to address many rows on disk. Oracle Version 8 improved the way rowids were generated-n-saved and this format is known as "extended format". Now rowids can address virtually limitless rows on disk.

UROWID
U" stands for Universal here.
ROWIDs can only save physical ROW IDs. There are certain cases where we require to save "logical ROW IDs" too. For example, IOTs (Index Organized Tables) require "logical row IDs" which keep changing. Similarly Foreign tables (for example, DB2 tables accessed through a gateway) also require "logical ROW IDs". UROWIDs can save both "physical" as well as "logical" ROW IDs.

ROWNUM
Rownum is a also a pseudocolumn attached with every row in an oracle table. We can use it to execute Top-N queries in oracle tables.
For example, below query can be used to print only 5 rows from table "EMP":
SQL> select * from EMP where rownum <=5;

UROWID Related Oracle package : DBMS_ROWID

================================================
Suggested Readings:

Following link has exhaustive information about DBMS_ROWID package functions: http://www.psoug.org/reference/dbms_rowid.html
To learn more about restricted and extended ROWIDs read: Book: Oracle 1Z0-031 Exam Guide (By Jason Couchman & Sudheer Marisetti, Oracle Press) -> Chapter 5 (Managing Database objects) -> Topic: "Extended and Restricted ROWIDs".