Thursday, January 26, 2012

Oracle Interview Questions and Answers

3) How many types of SQL Statements are there in Oracle
3) There are basically 6 types of SQL statements. They are
a)      Data Definition Language (DDL):
The DDL statements define and maintain objects and drop objects.
b)     Data Manipulation Language (DML):
The DML statements manipulate database data.
c)      Transaction Control Statements:
Manage change by DML
d)     Data Query Language(DQL):
Select Statement
e)      Session Control:
Used to control the properties of current session enabling and disabling roles and changing. e.g.  Alter Statements, Set Role
f)       System Control Statements:
Change Properties of Oracle Instance. e.g.:: Alter System
g)     Embedded SQL:
Incorporate DDL, DML and T.C.S in Programming Language. e.g.:: Using the SQL Statements in languages such as 'C', Open, Fetch, executes and close

4) What is a Transaction in Oracle?
 A transaction is a Logical unit of work that compromises one or more SQL Statements executed by a single User. According to ANSI, a transaction begins with first executable statement and ends when it is explicitly committed or rolled back.

5) Key Words Used in Oracle
 The Key words that are used in Oracle are:
a) Committing: A transaction is said to be committed when the transaction     makes permanent changes resulting from the SQL statements.
b) Rollback:   A transaction that retracts any of the changes resulting from      SQL statements in Transaction.
c) Save Point: For long transactions that contain many SQL statements, intermediate markers or save points are declared. Save points can be used to divide a transaction into smaller points.
d) Rolling Forward: Process of applying redo log during recovery is called rolling forward.
e) Cursor:      A cursor is a handle ( name or a pointer) for the memory associated with a specific stament.  A cursor is basically an area allocated by Oracle for executing the Sql Statement. Oracle uses an implicit cursor statement for Single row query and Uses Explicit cursor for a multi row query.
f) System Global Area (SGA): The SGA is a shared memory region allocated by the Oracle that contains Data and control information for one Oracle Instance. It consists of Database Buffer Cache and Redo log Buffer.
g) Program Global Area (PGA):: The PGA is a memory buffer  that contains data and control information for server process.
h) Database Buffer Cache: Database Buffer of SGA stores the most recently used blocks of database data. The set of database buffers in an instance is called Database Buffer Cache.
i) Redo log Buffer: Redo log Buffer of SGA stores all the redo log entries.
j) Redo Log Files: Redo log files are set of files that protect altered database data in memory that has not been written to Data Files. They are basically used for backup when a database crashes.
k) Process:     A Process is a 'thread of control' or mechanism in Operating System that executes series of steps.

6) What are Procedure, functions and Packages?
 Procedures and functions consist of set of PL/SQL statements that are grouped together as a unit to solve a specific problem or perform set of related tasks.
Procedures do not return values while Functions return one Value
Packages: Packages provide a method of encapsulating and storing related procedures, functions, variables and other Package Contents

7) What are Database Triggers and Stored Procedures
Database Triggers: Database Triggers are Procedures that are automatically executed as a result of insert in, update to, or delete from table.
Database triggers have the values old and new to denote the old value in the table before it is deleted and the new indicated the new value that will be used. DT is useful for implementing complex business rules, which cannot be enforced using the integrity rules. We can have the trigger as before trigger or After Trigger and at Statement or Row level.
e.g.:: operations insert, update ,and delete 3
            Before, after                                  3*2     A total of 6 combinations
            At statement level (once for the trigger) or row level( for every execution )       6 * 2    A total of 12.
Thus a total of 12 combinations are there and the restriction of usage of 12 triggers has been lifted from Oracle 7.3 Onwards.
Stored Procedures: Stored Procedures are Procedures that are stored in Compiled form in the database. The advantage of using the stored procedures is that many users can use the same procedure in compiled and ready to use format.

8) How many Integrity Rules are there and what are they
There are Three Integrity Rules. They are as follows:
a) Entity Integrity Rule: The Entity Integrity Rule enforces that the Primary key cannot be Null
b) Foreign Key Integrity Rule: The FKIR denotes that the relationship between the foreign key and the primary key has to be enforced. When there is data in Child Tables the Master tables cannot be deleted.
c) Business Integrity Rules: The Third Integrity rule is about the complex business processes which cannot be implemented by the above 2 rules.

9) What are the Different Optimization Techniques?
The Various Optimization techniques are
a) Execute Plan: we can see the plan of the query and change it accordingly based on the indexes
b) Optimizer_hint:
Set_item_property ('DeptBlock', OPTIMIZER_HINT,'FIRST_ROWS');
Select /*+ First_Rows */ Deptno, Dname, Loc, Rowid from dept
Where (Deptno > 25)
c) Optimize_Sql:
By setting the Optimize_Sql = No, Oracle Forms assigns a single cursor for all SQL statements. This slow downs the processing because for every time the SQL must be parsed whenever they are executed.
F45run module = my_firstform userid = scott/tiger optimize_sql = No
d) Optimize_Tp:
By setting the Optimize_Tp= No, Oracle Forms assigns separate cursor only for each query SELECT statement. All other SQL statements reuse the cursor.
F45run module = my_firstform userid = scott/tiger optimize_Tp = No

10) How do u implement the If statement in the Select Statement
We can implement the if statement in the select statement by using the Decode statement.
e.g. select   DECODE (EMP_CAT,'1','First','2','Second'Null);
Here the Null is the else statement where null is done.

11) How many types of Exceptions are there
There are 2 types of exceptions. They are
a) System Exceptions
E.g. When no_data_found, When too_many_rows
b) User Defined Exceptions
E.g. My_exception exception
When My_exception then

12) What are the inline and the precompiler directives?
The inline and precompiler directives detect the values directly

13) What are snap shots and views?
Snapshots are mirror or replicas of tables. Views are built using the columns from one or more tables. The Single Table View can be updated but the view with multi table cannot be updated

14) What are the OOPS concepts in Oracle.
Oracle does implement the OOPS concepts. The best example is the Property Classes. We can categorize the properties by setting the visual attributes and then attach the property classes for the objects. OOPS supports the concepts of objects and classes and we can consider the property classes as classes and the items as objects

15) What is the difference between candidate key, unique key and primary key?
Candidate keys are the columns in the table that could be the primary keys and the primary key is the key that has been selected to identify the rows. Unique key is also useful for identifying the distinct rows in the table.

16) What is concurrency?
Concurrency is allowing simultaneous access of same data by different users.  Locks useful for accessing the database are
a) Exclusive: The exclusive lock is useful for locking the row when an insert, update or delete is being done. This lock should not be applied when we do only select from the row.
b) Share lock: We can do the table as Share Lock as many share locks can be put on the same resource.

17) Privileges and Grants
Privileges are the right to execute a particular type of SQL statements.
e.g. Right to Connect, Right to create, Right to resource
Grants are given to the objects so that the object might be accessed accordingly. The grant has to be given by the owner of the object.

18) Table Space, Data Files, Parameter File, Control Files
Table Space: The table space is useful for storing the data in the database. When a database is created two table spaces are created.
a) System Table space: This data file stores all the tables related to the system and DBA tables
b) User Table space: This data file stores all the user related tables
We should have separate table spaces for storing the tables and indexes so that the access is fast.
Data Files: Every Oracle Data Base has one or more physical data files. They store the data for the database. Every data file is associated with only one database. Once the Data file is created the size cannot change. To increase the size of the database to store more data we have to add data file.
Parameter Files: Parameter file is needed to start an instance. A parameter file contains the list of instance configuration parameters e.g.::
db_block_buffers = 500
db_name = ORA7
db_domain = u.s.acme lang
Control Files: Control files record the physical structure of the data files and redo log files. They contain the Db name, name and location of Database, data files, redo log files and time stamp.

19) Physical Storage of the Data
The finest level of granularity of the database is the data blocks.
Data Block: One Data Block correspond to specific number of physical database space
Extent: Extent is the number of specific number of contagious data blocks.
Segments:  Set of Extents allocated for Extents. There are three types of Segments
a) Data Segment: Non Clustered Table has data segment data of every table  is stored in cluster data segment
b) Index Segment: Each Index has index segment that stores data
c) Roll Back Segment: Temporarily store 'undo' information
d) Temp. Segment: It Used in sort order.
                       
20) What are the PCT Free and PCT Used?
PCT Free is used to denote the percentage of the free space that is to be left when creating a table. Similarly PCT Used is used to denote the percentage of the used space that is to be used when creating a table
eg.:: Pctfree 20, Pctused 40

21) What is Row Chaining?
The data of a row in a table may not be able to fit the same data block. Data for row is stored in a chain of data blocks.

22) What is a 2 Phase Commit?
Two Phase commit is used in distributed data base systems. This is useful to maintain the integrity of the database so that all the users see the same values. It contains DML statements or Remote Procedural calls that reference a remote object. There are basically 2 phases in a 2-phase commit.
a) Prepare Phase: Global coordinator asks participants to prepare
b) Commit Phase: Commit all participants to coordinator to Prepared, Read only or abort Reply

23) What is the difference between deleting and truncating of tables?

 Delete

At the simplest level, delete scans the table and removes any rows that match the given criteria in the (optional) where clause. It generates rollback information so that the deletions can be undone should it be necessary. Index entries for the deleted rows are removed from the indexes. You must commit to make the deletions permanent.

 When deleting rows from a table, extents are not deallocated, so if there were 50 extents in the table before the deletion, there will still be 50 after the deletion. In addition the High Water Mark is not moved down, so it remains where it was before the deletion began. This means that any subsequent full table scans may (still) take a long time to complete - because a full table scans always scans up to the HWM. So, by example, if a select count(*) from very_large_table; took 15 minutes to complete before all the rows were deleted, you will find that it still takes about 15 mins after the deletion - because Oracle is still scanning every single block up to the HWM - even though some (or all) of the blocks may have no data in them.

In delete database trigger fires, if any.

Truncate

Truncate, on the other hand, simply moves the high water mark on the table right down to the beginning. It does this very quickly, and does not need to be committed. Once you truncate a table, there is no going back. Indexes are also truncated. There is no facility to be able to specify which rows to 'delete' as you can with the where clause on the delete command.
When a table is truncated, all its extents are deallocated leaving only the extents specified when the table was originally created. So if the table was originally created with minextents 3, there will be 3 extents remaining when the tables is truncated.
If you specify the reuse storage clause, then the extents are not deallocated. This saves time in the recursive SQL department if you intend to reload the table with data from an export for example, and can reduce the time it takes to do the import as there is no need to dynamically allocate any new extents.
Truncate do not fires database trigger.

24) What are mutating tables?
When a table is in state of transition it is said to be mutating. eg :: If a row has been deleted then the table is said to be mutating and no operations can be done on the table except select.

25) What are Codd Rules?
Codd Rules describe the ideal nature of a RDBMS. No RDBMS satisfies all the 12 codd rules and Oracle Satisfies 11 of the 12 rules and is the only RDBMS to satisfy the maximum number of rules.

26) What is Normalisation?
Normalisation is the process of organizing the tables to remove the redundancy. There are mainly 5 Normalisation rules.
a) 1 Normal Form:  A table is said to be in 1st Normal Form when the attributes are atomic
b) 2 Normal Form:  A table is said to be in 2nd Normal Form when all the candidate keys are dependent on the primary key and it is in 1st normal form.
c) 3rd Normal Form: A table is said to be third Normal form when it is not dependent transitively or all the non-columns are mutually independent. And it is to be in 2nd normal form.

27) Can U disable database trigger? How?
Yes. With respect to table
     ALTER TABLE TABLE_NAME
     [DISABLE all_trigger]
28) What is a pseudo column? Name them?
A pseudo column behaves like a table column, but is not actually stored in the table.  You can select from pseudocolumns, but you cannot insert, update, or delete their values.  This section     describes these pseudocolumns:
    * CURRVAL
    * NEXTVAL
    * LEVEL
    * ROWID
    * ROWNUM
    * SYSDATE
    * User

29) How many columns can table have?
The number of columns in a table can range from 1 to 1000.

30) Is space acquired in blocks or extents?
In extents.

31) What is clustered index?
In an indexed cluster, rows are stored together based on their cluster key values. Cannot apply for HASH.

32) What are the data types supported by oracle (INTERNAL)?
Varchar2, Number, Char, MLSLABEL.
39) What are attributes of cursor?
            %FOUND, %NOTFOUND, %ISOPEN, %ROWCOUNT

40) Can you use select in FROM clause of SQL select?
            Yes.

41) Types of Snapshot Refresh
To ensure that a snapshot is consistent with its master table, you need to refresh the
snapshot periodically. Oracle provides the following three methods to refresh snapshots:
a)      Fast refresh uses snapshot logs to update only the rows that have changed since the last refresh.
b)     Complete refresh updates the entire snapshot.
c)      Force refresh performs a fast refresh when possible. When a fast refresh is not possible, force refresh performs a complete refresh.

When it is important for snapshots to be transactional consistent with each other, you can organize them into refresh groups. By refreshing the refresh group, you can ensure that the data in all of the snapshots in the refresh group correspond to the same transactional consistent point in time. A snapshot in a refresh group still can be refreshed individually, but doing so nullifies the benefits of the refresh group because refreshing the snapshot individually does not refresh the other snapshots in the refresh group.






Database:

  1. Explain Database Structure.


            When Oracle server starts Instance gets initiated. Instance is the creation of SGA in server RAM and initiation of the background process. SGA is divided into shared pool, database buffer; redo log buffer and Java pool. Shared pool consists of data dictionary, where the entire table structures, stored procedures, functions, packages, privileges are stored. 
            When query fires, it gets parsed in shared pool area where all the syntaxes are checked with proper positions. Verification of tables, columns and grants will be taken place in data dictionary. Search for the required data will start first in shared pool, otherwise data will be fetch from the data files. 
2. What is the Back ground processes in Oracle and what are they.
2.  This is one of the most frequently asked questions. There are basically 9 Processes but in a general system we need to mention the first five background processes. They do the house keeping activities for the Oracle and are common in any system.
The various background processes in oracle are
a) Data Base Writer (DBWR):  Data Base Writer Writes Modified blocks from Database buffer cache to Data Files. This is required since the data is not written whenever a transaction is committed.
b) Log Writer (LGWR):  Log Writer writes the redo log entries to disk. Redo Log data is generated in redo log buffer of SGA. As transactions commit and log buffer fills, LGWR writes log entries into a online redo log file.
c) System Monitor (SMON): The System Monitor performs instance recovery at instance startup. This is useful for recovery from system failure
d) Process Monitor (PMON): The Process Monitor performs process recovery when user Process fails. PMON Clears and Frees resources that process was using.
e) Check Point (CKPT): At Specified times, all modified database buffers in SGA are written to data files by DBWR at Checkpoints and Updating all data files and control files of database to indicate the most recent checkpoint
f) Achieves (ARCH): The Archiver copies online redo log files to archival storage when they are busy.
g) Recoveror (RECO): The Recoveror is used to resolve the distributed transaction in network
h) Dispatcher (Dnnn): The Dispatcher is useful in Multi Threaded Architecture
i) Lckn: We can have up to 10 lock processes for inter instance locking in parallel SQL.




  1. How to find out duplicate rows?
                               Select *  from my_table t1
                               where  exists (     select 'x' from my_table t2
                                                             where t2.key_value1 = t1.key_value1
                                                             and t2.key_value2 = t1.key_value2
                                                             and t2.rowid      > t1.rowid
                                           );
  1. How to delete duplicate rows with rowid and without rowid?
                               delete from my_table t1
                               where  exists (     select 'x' from my_table t2
                                                             where t2.key_value1 = t1.key_value1
                                                             and t2.key_value2 = t1.key_value2
                                                             and t2.rowid      > t1.rowid
                                           );

  1. What are types of joins? Where the + (Plus) should be given in outer join. Explain with example.  Emp and Dept table.

  1. what is a schema?
It is an logical grouping of database objects based on the user that owns the object.
  1. I want to calculate the percentage EUROSALES against PLANNEDEUROSALES on WEEK basis, only for customers where the PLANNEDEUROSALES IS NOT NULL

    There is one table SDM_CPLAN_INVOICES_W  with the fields
    WEEK, CUSTOMER_NUMBER, EUROSALES_TY, PLANEUROSALES

    SELECT  X.WEEK,
                           count(Y.perc),
   count(X.percentage),
   (count(Y.perc)/count(X.percentage))*100  PERC_CUST_REALIZED
FROM     (SELECT  CPLAN.WEEK WEEK,
       CPLAN.CUSTOMER_NUMBER,
       CPLAN.EUROSALES_TY,
       CPLAN.PLAN_EUROSALES,                          round((CPLAN.EUROSALES_TY/CPLAN.PLAN_EUROSALES)*100) percentage
      FROM   SDM_CPLAN_INVOICES_W CPLAN
      WHERE CPLAN.BRAND_IND = 'AV'
and       cplan.PLAN_EUROSALES != 0
GROUP BY CPLAN.WEEK, CPLAN.CUSTOMER_NUMBER,    CPLAN.EUROSALES_TY , CPLAN.PLAN_EUROSALES  )X ,
    (SELECT  CPLAN.WEEK WEEK,
        CPLAN.CUSTOMER_NUMBER,
        CPLAN.EUROSALES_TY,
CPLAN.PLAN_EUROSALES,        round((CPLAN.EUROSALES_TY/CPLAN.PLAN_EUROSALES)*100) perc
FROM SDM_CPLAN_INVOICES_W CPLAN
WHERE   CPLAN.BRAND_IND = 'AV'
and           cplan.PLAN_EUROSALES != 0
HAVING round((CPLAN.EUROSALES_TY/CPLAN.PLAN_EUROSALES)*100) >= 100
GROUP BY CPLAN.WEEK, CPLAN.CUSTOMER_NUMBER, CPLAN.EUROSALES_TY , CPLAN.PLAN_EUROSALES
  )Y
WHERE X.WEEK = Y.WEEK
GROUP BY X.WEEK

I get the following results
WEEK     COUNT(Y.PERC)     COUNT(X.PERCENTAGE)     PERC_CUST_REALIZED
1                            32                                32                                100
2                            16                                16                                100
3                            80                                80                                100
4                            48                                48                                100
5                            48                                48                                100
etc.
this is wrong.

The Good results shoulb something like beneath
WEEK     COUNT(Y.PERC)     COUNT(X.PERCENTAGE)     PERC_CUST_REALIZED
1                      2                     16                                                    12.5
2                      1                     16                                                    6.25
3                      5                     16                                                    31.25
4                      3                     16                                                    18.75
5                      3                     16                                                    18.75



SELECT X.WEEK,
SUM(X.realised) num_realised,
   count(*) num_percs,
      SUM(X.realised)/count(*)*100 PERC_CUST_REALIZED
FROM
(SELECT CPLAN.WEEK WEEK, DECODE(SIGN(CPLAN.EUROSALES_TY-CPLAN.PLAN_EUROSALES),-1,0,1) realised
 FROM SDM_CPLAN_INVOICES_W CPLAN
 WHERE CPLAN.BRAND_IND = 'AV' and  cplan.PLAN_EUROSALES != 0
)X
GROUP BY X.WEEK;

  1. If you want to find the top 10 earners in your company then,

    select * from (
    select empno,sal,rownum
    from emp
    order by sal desc
    )
    where rownum < 11

  1. Is it possible to run another query if the results of a first query are null?

    For example, query one is:
    select max(date) from table1 where status = 'B' and account = '123'

    Query 2 is:
    select max(date) from table 2 where curr_status in('a','b','c') and account = '123'

    If the results for query 1 are null, I need the results of query 2, but I don't want to have to test the results of query 1 in my program and then run query 2 if I can get oracle to do the test for me.

Select nvl((select max(date) from table1 where status = 'B' and account = '123'), (select max(date) from table 2 where curr_status in('a','b','c') and account = '123'))
from dual;

Select decode(x, 0, date2, date1)
 from   ( select  max(date) date1,
  count(*) x
   from    table1
   where  status = 'B' and account = '123'
),
(  select  max(date) date2
    from   table 2
    where curr_status in('a','b','c') and account = '123'
);
           
I also have tried this type of statement with thousands of resultant records...not only one.-
using the same example we have:



SELECT table3.field_a,
             nvl(
            (select max(date) from table1 A where A.field2 = 'B' and a.field1 = c.field1),
            (select max(date) from table2 B where curr_status in('a','b','c')                                       and b.field1 = c.field1)
            )
FROM table3 C
WHERE
    c.field1='XXX'
    .
    .
    . ;


  1. I have 2 tables...

    STUDENT
    student_id (pk)          Std_name
    sjones                        Sam Jones
    jdoe                             Jane Doe

    ENROLLMENT
    Student_id (pk)          Course#(pk)             Grade          
    ---------------------------------------------------------------------
sjones                          csc211                     A
sjones                          csc212                     A-
jdoe                              ect555                      B-
jdoe                              is404                        B
jdoe                              is421                        B+

For each student name, list the course number or numbers the student took where the student obtained the lowest grade.

OK, this is what I have so far...
SELECT student.std_name, enrollment.[course#], enrollment.grade
FROM enrollment INNER JOIN student ON enrollment.student_id = student.student_id;

This lists all of the students and the course #'s and grades.  Now, I only want to show the lowest grade and the course # for each student.  How can I sort them so B+ is greater than B, but B- is less than B.





Answer :  you can use the decode function...

select STUDENT_ID, decode(GRADE, 'A+', 10, 'A', 9, 'A-', 8, ... -1) from ENROLLMENT

it basically translates the GRADE into a number value ... like a bunch of if statements... where the first parameter is the value to check on... and the last is the default value if no other value is met...

Other way1

select STUDENT_ID, COURSE#, GRADE from ENROLLMENT E1 where
decode(GRADE, 'A+', 10, 'A', 9, 'A-', 8, 'B+', 7, 'B', 6, 'B-', 5, -1) =
(select min(decode(GRADE, 'A+', 10, 'A', 9, 'A-', 8, 'B+', 7, 'B', 6, 'B-', 5, -1))
from ENROLLMENT E2 where E1.STUDENT_ID=E2.STUDENT_ID);


STUDENT_ID
---------------
COURSE#
---------------
GRADE
---------------
sjones
csc212
A-

jdoe
etc555
B-

Other Way 2

select a.std_name, a.course#, a.grade from enrollment a, (SELECT student.std_name,min(decode(grade, 'A+', 13, 'A', 12, 'A-', 11, 'B+', 10, 'B', 9, 'B-', 8, 'C+', 7, 'C', 6, 'C-',5,'D+', 4, 'D', 3, 'D-', 2, 'F', 1)) min_grade
FROM enrollment INNER JOIN student ON enrollment.student_id= student.std_name
group by student.std_name) b
where a.std_name=b.std_name
AND a.grade=decode(b.min_grade,13, 'A+', 12, 'A', 11, 'A-', 10, 'B+', 9, 'B', 8, 'B-', 7, 'C+', 6, 'C', 5, 'C-',4,'D+', 3, 'D', 2, 'D-', 1, 'F');





  1. I wish to display the value that occurs most frequently. So for record 11 2 appears twice, and 3 only once. Therefore I wish to display 2.

Select id, status
from
(
 select id, status, rank() over (partition by id order by stat_count desc, status) as rnk
 from
 (
   select id, status, count(*) as stat_count
   from my_table
   group by id, status
 )
)
where rnk=1

  1. How many rows of table A in table B:

    select count(*) from (select * from <table a> INTERSECT select * from <table b>);

    How many rows of tablea NOT in table B

    select count(*) from (select * from <table a> MINUS select * from <table b>);

  1. How will you find no of columns in a table
select count(column_name) from all_tab_columns where table_name = 'EVALUATION9CODE';



  1. One question on self join query. How to find out the emp_no doing same job but working in different departments.
Select a.emp_no from emp a ,emp b where a.job = b.job and a.dept_no <> b.dept_no;

  1. Find out the count of three tables in one query. The output should be in the same line.

Select count1, count2 from (select count(*)count1 from tab1), (select count(*)count2 from tab2);

  1. What are cursors? Difference in implicit and explicit cursors. Cursors attributes?

                         Cursors (PL/SQL)
                         =======

Oracle uses work areas to execute SQL statements and store processing
information. A PL/SQL construct called a "cursor" lets you name a work
area and access its stored information. There are two kinds of cursors:
implicit and explicit.

PL/SQL implicitly declares a cursor for all SQL data manipulation
statements, including queries that return only one row. For queries
that return more than one row, you can explicitly declare a cursor
to process the rows individually.


                         Explicit Cursors
                         ----------------

The set of rows returned by a multi-row query is called the "active
set."  Its size is the number of rows that meet your search criteria.
An explicit cursor points to the current row in the active set. This
allows your program to process the rows one at a time.

Multi-row query processing is somewhat like file processing. For
example, a COBOL program opens a file to process records, then closes
the file. Likewise, a PL/SQL program opens a cursor to process rows
returned by a query, then closes the cursor. Just as a file pointer
marks the current position in an open file, a cursor marks the current
position in an active set.

You use three commands to control the cursor: OPEN, FETCH, and CLOSE.
First, you initialize the cursor with the OPEN statement, which
identifies the active set. Then, you use the FETCH statement to
retrieve the first row. You can execute FETCH repeatedly until all
rows have been retrieved. When the last row has been processed, you
release the cursor with the CLOSE statement.

Forward references are not allowed in PL/SQL. So, you must declare a
cursor before referencing it in other statements. You define a cursor
in the declarative part of a PL/SQL block, subprogram, or package by
naming it and specifying a query. In the following example, you declare
a cursor named "c1":

   DECLARE
      CURSOR c1 IS SELECT ename, deptno FROM emp WHERE sal > 2000;
      ...

The cursor name is an undeclared identifier, not the name of a PL/SQL
variable. You cannot assign values to a cursor name or use it in an
expression.

Explicit cursors can take parameters, as the example below shows.
A cursor parameter can appear in a query wherever a constant can appear.

   CURSOR c1 (median IN NUMBER) IS
      SELECT job, ename FROM emp WHERE sal > median;

To declare formal cursor parameters, you use the syntax
   CURSOR cursor_name [(parameter [, parameter, ...])] IS SELECT ...

where "parameter" stands for the following syntax:

   parameter_name [IN] datatype [{:= | DEFAULT} expr]

The formal parameters of a cursor must be IN parameters. Therefore, they
cannot return values to actual parameters.

As the example below shows, you can initialize cursor parameters to default
values. That way, you can pass different numbers of actual parameters to a
cursor, accepting or overriding the default values as you please. Moreover,
you can add new formal parameters without having to change every reference
to the cursor.

   DECLARE
      CURSOR c1
         (low  INTEGER DEFAULT 0,
          high INTEGER DEFAULT 99) IS SELECT ...

The scope of cursor parameters is local to the cursor, meaning that they
can be referenced only within the query used in the cursor declaration.
The values of cursor parameters are used by the associated query when
the cursor is opened.


                         Implicit Cursors
                         ----------------

Oracle implicitly opens a cursor to process each SQL statement not
associated with an explicitly declared cursor. PL/SQL lets you refer
to the most recent implicit cursor as the "SQL" cursor. So, although
you cannot use the OPEN, FETCH, and CLOSE statements to control an
implicit cursor, you can still use cursor attributes to get information
about the most recently executed SQL statement.

The values of cursor attributes always refer to the most recently
executed SQL statement, wherever that statement appears. It might be
in a different scope (for example, in a sub-block). So, if you want to
save an attribute value for later use, assign it to a Boolean variable
immediately. The following example shows how failing to save an
attribute value can result in a logic bug:

   UPDATE parts SET qty = qty - 1 WHERE partno = part_id;
   check_parts;  -- procedure call
   IF SQL%NOTFOUND THEN  -- dangerous!
      ...
   END IF;

In this example, it is dangerous to rely on the IF condition because the
procedure "check_parts" might have changed the value of %NOTFOUND. You
can debug the code as follows:

   UPDATE parts SET qty = qty - 1 WHERE partno = part_id;
   sql_notfound := SQL%NOTFOUND;
   check_parts;
   IF sql_notfound THEN
      ...
   END IF;

Before Oracle opens the SQL cursor, the implicit cursor attributes
yields NULL.

Cursor Attributes


                         %FOUND Attribute (PL/SQL)
                         ================

When appended to the name of a cursor or cursor variable, the %FOUND
attribute returns useful information about the execution of a multi-row
query. When appended to the name of the SQL implicit cursor (SQL),
%FOUND returns useful information about the most recently executed
INSERT, UPDATE, DELETE, or SELECT INTO statement.


                         Explicit Cursors
                         ----------------

%FOUND is the logical opposite of %NOTFOUND. After a cursor or cursor
variable is opened but before the first fetch, %FOUND yields NULL.
Thereafter, it yields TRUE if the last fetch returned a row, or FALSE if
the last fetch failed to return a row.

In the following example, you use %FOUND to select an action:

   LOOP
      FETCH c1 INTO my_ename, my_deptno;
      IF c1%FOUND THEN  -- fetch succeeded
         ...
      ELSE  -- fetch failed, so exit loop
         EXIT;
      END IF;
      ...

      ...
   END LOOP;

If a cursor or cursor variable is not open, referencing it with %FOUND
raises the predefined exception INVALID_CURSOR.


                         Implicit Cursors
                         ----------------

%FOUND is the logical opposite of %NOTFOUND. Until a SQL data
manipulation statement is executed, %FOUND yields NULL. Thereafter,
%FOUND yields TRUE if an INSERT, UPDATE, or DELETE statement affected
one or more rows or a SELECT INTO statement returned one or more rows.
Otherwise, %FOUND yields FALSE. In the following example, you use %FOUND
to insert a row if a deletion succeeds:

   DELETE FROM emp WHERE empno = my_empno;
   IF SQL%FOUND THEN  -- delete succeeded
      INSERT INTO new_emp VALUES (my_empno, my_ename, ...);
      ...
   END IF;

When appended to the name of a cursor or cursor variable, the %NOTFOUND
attribute returns useful information about the execution of a multi-row
query. When appended to the name of the SQL implicit cursor (SQL),
%NOTFOUND returns useful information about the most recently executed
INSERT, UPDATE, DELETE, or SELECT INTO statement.


                         Explicit Cursors
                         ----------------

When a cursor or cursor variable is opened, the rows that satisfy the
associated query are identified and form the active set. Before the
first fetch, %NOTFOUND yields NULL. Rows are fetched from the active set
one at a time. If the last fetch returned a row, %NOTFOUND yields FALSE.
If the last fetch failed to return a row, %NOTFOUND yields TRUE.

In the following example, you use %NOTFOUND to exit a loop when the
FETCH statement fails to return a row:

   LOOP
      FETCH c1 INTO my_ename, my_deptno;
      EXIT WHEN c1%NOTFOUND;
      ...
   END LOOP;

If a cursor or cursor variable is not open, referencing it with
%NOTFOUND raises the predefined exception INVALID_CURSOR.

                         Implicit Cursors
                         ----------------

%NOTFOUND yields TRUE if an INSERT, UPDATE, or DELETE statement affected
no rows or a SELECT INTO statement returned no rows. Otherwise, %NOTFOUND
yields FALSE. In the following example, you use %NOTFOUND to insert a new
row if an update fails:

   UPDATE emp SET sal = sal * 1.05 WHERE empno = my_empno;
   IF SQL%NOTFOUND THEN  -- update failed
      INSERT INTO errors VALUES (...);
   END IF;


                         %ISOPEN Attribute (PL/SQL)
                         =================

When appended to the name of a cursor or cursor variable, the %ISOPEN
attribute returns the status of the cursor or cursor variable.


                         Explicit Cursors
                         ----------------

%ISOPEN yields TRUE if its cursor or cursor variable is open; otherwise,
%ISOPEN yields FALSE. In the following example, you use %ISOPEN to select
an action:

   IF c1%ISOPEN THEN  -- cursor is open
      ...
   ELSE  -- cursor is closed, so open it
      OPEN c1;
   END IF;


                         Implicit Cursors
                         ----------------

Oracle closes the SQL cursor automatically after executing its
associated SQL statement. As a result, %ISOPEN always yields FALSE.

                         %ROWCOUNT Attribute (PL/SQL)
                         ===================

When appended to the name of a cursor or cursor variable, the %ROWCOUNT
attribute returns useful information about the execution of a multi-row
query. When appended to the name of the SQL implicit cursor (SQL),
%ROWCOUNT returns useful information about the most recently executed
INSERT, UPDATE, DELETE, or SELECT INTO statement.


                         Explicit Cursors
                         ----------------

When you open its cursor or cursor variable, %ROWCOUNT is zeroed. Before
the first fetch, %ROWCOUNT yields 0. Thereafter, it yields the number of
rows fetched so far. The number is incremented if the last fetch returned
a row. In the next example, you use %ROWCOUNT to take action if more than
ten rows have been fetched:

   LOOP
      FETCH c1 INTO my_ename, my_deptno;
      IF c1%ROWCOUNT > 10 THEN
         ...
      END IF;
      ...
   END LOOP;

If a cursor or cursor variable is not open, referencing it with
%ROWCOUNT raises the predefined exception INVALID_CURSOR.


                         Implicit Cursors
                         ----------------

%ROWCOUNT yields the number of rows affected by an INSERT, UPDATE, or
DELETE statement or returned by a SELECT INTO statement. %ROWCOUNT
yields 0 if an INSERT, UPDATE, or DELETE statement affected no rows or
a SELECT INTO statement returned no rows. In the following example, you
use %ROWCOUNT to take action if more than ten rows have been deleted:

   DELETE FROM emp WHERE ...
   IF SQL%ROWCOUNT > 10 THEN
      ...
   END IF;

If a SELECT INTO statement returns more than one row, PL/SQL raises the predefined exception TOO_MANY_ROWS and %ROWCOUNT yields 1, not the actual number of rows that satisfy the query.
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
In any DML statement if we want to know how many records are processed then SQL%ROWCOUNT attribute can be used.

In implicit cursor SQL%ROWCOUNT will give the total count of records.
In explicit cursor CUR%ROWCOUNT will give the incremental record count.

In Implicit cursor %ISOPEN is always FALSE.

  1. What are exceptions? Different types of exceptions? Pragma exception in detail.
Three types of Exceptions are there
a)                  System Defined Exception When
b)                 User Defined Exception
c)                  Pragma Exception


                         Predefined Exceptions (PL/SQL)
                         =====================

An internal exception is raised implicitly whenever a PL/SQL program
violates an Oracle rule or exceeds a system-dependent limit. Every
Oracle error has a number, but exceptions must be handled by name.
So, PL/SQL predefines some common Oracle errors as exceptions.
For example, the predefined exception NO_DATA_FOUND is raised if a
SELECT INTO statement returns no rows.

PL/SQL declares predefined exceptions globally in package STANDARD,
which defines the PL/SQL environment. So, you need not declare them
yourself. You can write handlers for predefined exceptions using the
names shown in the table below. Also shown are the corresponding
Oracle error codes.

   Exception                           Error               Raised if ...
   ----------------------------------------------------------------
   CURSOR_ALREADY_OPEN  ORA-06511       you try to OPEN an already open
                                                cursor; you must CLOSE a cursor
                                                                                    before you can reOPEN it
   DUP_VAL_ON_INDEX               ORA-00001   you try to INSERT or UPDATE
                                                                                    duplicate values in a UNIQUE
                                                                                    database column
   INVALID_CURSOR         ORA-01001   you try an illegal cursor
                                                                                    operation such as closing an
                                                                                    unopened cursor
   INVALID_NUMBER       ORA-01722   the conversion of a character
                                                                                    string to a number fails in a  SQL statement
   LOGIN_DENIED                          ORA-01017   you log on to Oracle with an
                                                                                    invalid username/password
NO_DATA_FOUND                 ORA-01403       a SELECT INTO returns no rows, or you refer to
                                                                     an uninitialized row in a PL/SQL table

   NOT_LOGGED_ON                     ORA-01012               your PL/SQL program issues a
                                                                        database call without being logged on to Oracle
   PROGRAM_ERROR                    ORA-06501   PL/SQL has an internal problem such as exiting
                                                                        a function that has no RETURN statement

   ROWTYPE_MISMATCH             ORA-06504   the host cursor variable and PL/SQL cursor
variable involved in an assignment have incompatible return types  
   STORAGE_ERROR          ORA-06500   PL/SQL runs out of memory or memory is
                                                                        corrupted
   TIMEOUT_ON_RESOURCE      ORA-00051   a timeout occurs while Oracle is waiting for a
                                                                        resource                    
   TOO_MANY_ROWS                   ORA-01422   a SELECT INTO returns more than one row
   VALUE_ERROR               ORA-06502   the conversion of a character string to a number
fails in a procedural statement, or an arithmetic, conversion, truncation, or constraint error occurs

   ZERO_DIVIDE                              ORA-01476   you try to divide a number by zero


                         Redeclaring Predefined Exceptions
                         ---------------------------------

Remember, PL/SQL declares predefined exceptions globally in package
STANDARD, so you need not declare them yourself. Redeclaring predefined
exceptions is error-prone because your local declaration overrides the
global declaration. For example, if you declare an exception named
"invalid_number" and then PL/SQL raises the predefined exception
INVALID_NUMBER internally, a handler written for INVALID_NUMBER will
not catch the internal exception. In such cases, you must use dot
notation to specify the predefined exception, as follows:

        ...
   EXCEPTION
      WHEN invalid_number OR STANDARD.INVALID_NUMBER THEN
         -- handle the error
      ...
   END;

                         EXCEPTION_INIT Pragma (PL/SQL)
                         =====================

To handle unnamed internal exceptions, you must use the OTHERS handler
or the pragma EXCEPTION_INIT. A "pragma" is a compiler directive, which
can be thought of as a parenthetical remark to the compiler. Pragmas
(also called "pseudoinstructions") are processed at compile time, not at
run time. They do not affect the meaning of a program; they simply
convey information to the compiler.

The predefined pragma EXCEPTION_INIT tells the PL/SQL compiler to
associate an exception name with an Oracle error number. That allows
you to refer to any internal exception by name and to write a specific
handler for it. You code the pragma EXCEPTION_INIT in the declarative
part of a PL/SQL block, subprogram, or package using the syntax

    PRAGMA EXCEPTION_INIT(exception_name, Oracle_error_number);

where "exception_name" is the name of a previously declared exception.
The pragma must appear somewhere after the exception declaration in the
same declarative part, as shown in the following example:

   DECLARE
      insufficient_privileges EXCEPTION;
      PRAGMA EXCEPTION_INIT(insufficient_privileges, -1031);
         -----------------------------------------------------
         -- Oracle returns error number -1031 if, for example,
         -- you try to UPDATE a table for which you have
only SELECT privileges
   BEGIN
      ...
   EXCEPTION
      WHEN insufficient_privileges THEN
         -- handle the error
      ...
   END;


  1. Hierarchy of exception? (when others should be last)
                         Scope Rules
                         -----------

You cannot declare an exception twice in the same block. You can,
however, declare the same exception in two different blocks.

Exceptions declared in a block are considered local to that block and
global to all its sub-blocks. Because a block can reference only local
or global exceptions, enclosing blocks cannot reference exceptions
declared in a sub-block.

If you redeclare a global exception in a sub-block, the local
declaration prevails. So, the sub-block cannot reference the global
exception unless it was declared in a labeled block, in which case the
following syntax is valid:

   block_label.exception_name

The next example illustrates the scope rules:

   DECLARE
      past_due  EXCEPTION;
      acct_num  NUMBER;
   BEGIN
      ...
      ---------------- beginning of sub-block ----------------
      DECLARE
         past_due  EXCEPTION;  -- this declaration prevails
         acct_num  NUMBER;
      BEGIN
         ...
         IF ... THEN
            RAISE past_due;  -- this is not handled
         END IF;
         ...
      END;
      ------------------- end of sub-block -------------------
   EXCEPTION
      WHEN past_due THEN  -- does not handle RAISEd exception
         ...
   END;

The enclosing block does not handle the RAISEd exception because the
declaration of "past_due" in the sub-block prevails. Though they share
the same name, the two "past_due" exceptions are different, just as
the two "acct_num" variables share the same name but are different
variables. Therefore, the RAISE statement and the WHEN clause refer
to different exceptions. To have the enclosing block handle the RAISEd
exception, you must remove its declaration from the sub-block or define
an OTHERS handler.

  1. In emp table the  fields are empno,sex,deptno. Find out the department no wise total count of employee, count of male, count of female.
                               select dept,  sum(decode(sex,'M',1,0)) MALE,
                                                    sum(decode(sex,'F',1,0)) FEMALE,
                                                    count(decode(sex,'M',1,'F',1)) TOTAL
                               from   my_emp_table
                               group  by dept;

  1. Find out the 3rd max salary in emp table.
               a)            SELECT *
                               FROM   (SELECT * FROM my_table ORDER BY col_name_1 DESC)
                               WHERE  ROWNUM < 10;
               b)             SELECT *      FROM my_table a
                               WHERE 10 >= (SELECT COUNT(DISTINCT maxcol)
                               FROM my_table b
                               WHERE b.maxcol >= a.maxcol)
                               ORDER BY maxcol DESC;
           

  1. Find out the record between 70 and 80.
   a)            SELECT * FROM ( SELECT ENAME,ROWNUM RN FROM EMP WHERE 
                                                ROWNUM < 101 ) 
                   WHERE  RN between 91 and 100 ;
               b)            SELECT rownum, f1 FROM t1
                               GROUP BY rownum, f1 HAVING rownum BETWEEN 2 AND 4;

  1. Update the salary of emp table as: salary between 1000-1999 1.2%sal, 2000- 2999 1.5% otherwise don’t update. In a single update statement.
               a)            select f2,
                                              sum(decode(greatest(f1,59), least(f1,100), 1, 0)) "Range 60-100",
                                              sum(decode(greatest(f1,30), least(f1, 59), 1, 0)) "Range 30-59",
                                              sum(decode(greatest(f1, 0), least(f1, 29), 1, 0)) "Range 00-29"
                               from   my_table
                               group  by f2;
                   b)               select ename "Name", sal "Salary",
                                      decode( trunc(f2/1000, 0), 0, 0.0,
                                                                            1, 0.1,
                                                                               2, 0.2,
                                                                               3, 0.31) "Tax rate"
                                      from   my_table;


  1. What is Public synonym? What’s the use?
                        CREATE SYNONYM command

PURPOSE:
    To create a synonym.  A synonym is an alternative name for a table,
    view, sequence, procedure, stored function, package, snapshot, or
    another synonym.

SYNTAX:
                       
CREATE [PUBLIC] SYNONYM [schema.]synonym
    FOR [schema.]object[@dblink]

where:

PUBLIC
    creates a public synonym.  Public synonyms are accessible to all
    users.  If you omit this option, the synonym is private and is
    accessible only within its schema.

schema
    is the schema to contain the synonym.  If you omit schema, Oracle
    creates the synonym in your own schema.

synonym
    is the name of the synonym to be created.

FOR
    identifies the object for which the synonym is created.  If you do
    not qualify object with schema, Oracle assumes that the object is in
    your own schema.  The object can be of these types:

          * table
          * view
          * sequence
          * stored procedure, function, or package
          * snapshot
          * synonym

    The object cannot be contained in a package.  Note that the object
    need not currently exist and you need not have privileges to access
    the object.

    You can use a complete or partial dblink to create a synonym for an
    object on a remote database where the object is located.  If you
    specify dblink and omit schema, the synonym refers to an object in
    the schema specified by the database link.  Oracle Corporation
    recommends that you specify the schema containing the object in the
    remote database.

    If you omit dblink, Oracle assumes the object is located on the
    local database.

PREREQUISITES:
    To create a private synonym in your own schema, you must have CREATE
    SYNONYM system privilege.

    To create a private synonym in another user's schema, you must have
    CREATE ANY SYNONYM system privilege.  If you are using Trusted
    Oracle in DBMS MAC mode, your DBMS label must dominate the creation
    label of the owner of schema to contain the synonym.

    To create a PUBLIC synonym, you must have CREATE PUBLIC SYNONYM
    system privilege.


  1. How to tune a query? Explain Plan and TKPROF.

Oracle's Explain Plan

Whenever you read or write data in Oracle, you do so by issuing an SQL Statement. One of Oracle's task when it receives such a statement is to build a statement execution plan. An execution plan defines how Oracle finds or writes the data. For example, an important decision that Oracle has to take is if it uses indexes or not. And if there are more indexes, which of these is used. All this is contained in an execution plan.
If one wants to explore such an execution plan, Oracle provides the SQL Statement EXPLAIN PLAN to determine this.
The general syntax of EXPLAIN PLAN is EXPLAIN PLAN [SET STATEMENT_ID=<id>; [INTO <table>] FOR <here goes the sql statement>. If you do an EXPLAIN PLAN, Oracle will analyze the statment and fill a special table with the Execution plan for that statement. You can indicate which table has to be filled with the INTO <table> part of the EXPLAIN PLAN command. If you omit the INTO <table> clause, Oracle fills a default table: PLAN_TABLE.

The Plan Table

The plan table is the table that Oracle fills when you have it explain an execution plan for an SQL Statement. You must make sure such a plan table exists. Oracle ships with the script UTLXPLAN.SQL which creates this table, named PLAN_TABLE (which is the default name used by EXPLAIN PLAN). If you like, however, you can choose any other name for the plan table, as long as you have been granted insert on it and it has all the fields as here.

The fields (attributes) within the plan table

Arguably, the most important fields within the plan table are operation, option, object_name, id, and parent_id. The pair operation and object_name define what operation would be done on (or with) object_name. If an operation has an id which other operations have as parent_id, it means the other operations feed their result to the parent.
Possible values for operation are: DELETE STATEMENT, INSERT STATEMENT, SELECT STATEMENT, UPDATE STATEMENT, AND-EQUAL, CONNECT BY, CONCATENATION, COUNT, DOMAIN INDEX, FILTER, FIRST ROW, FOR UPDATE, HASH JOIN, INDEX, INLIST ITERATOR, INTERSECTION, MERGE JOIN, MINUS, NESTED LOOPS, PARTITION, REMOTE, SEQUENCE, SORT, TABLE ACCESS, UNION and VIEW.
Option tells more about how an operation would be done. For example, the operation TABLE ACCESS can have the options: FULL or BY ROWID or many others. Full in this case means, that the entire table is accessed (takes a long time if table is huge) whereas BY ROWID means, Oracle knows where (from which block) the rows are to be retrieved, which makes the time to access the table shorter.

dbms_xplan

As if 9i, dbms_xplan can be used to format the plan table.

Operations

TABLE ACCESS

The following table is used to demonstrate EXPLAIN PLAN:
CREATE TABLE test_for_ep (a number, b varchar2(100));
Now, let's explain the plan for selecting everything on that table:
delete plan_table;
explain plan for select /*+ rule */ * from test_for_ep where a = 5;

Displaying the execution plan

In order to view the explained plan, we have to query the plan table:
select 
  substr (lpad(' ', level-1) || operation || ' (' || options || ')',1,30 ) "Operation", 
  object_name                                                              "Object"
from 
  plan_table 
start with id = 0 
connect by prior id=parent_id;
 
Here's the output of the explain plan:
SELECT STATEMENT ()
 TABLE ACCESS (FULL)           TEST_FOR_EP
First, take a look at the indention: TABLE ACCESS is indented right. In an explain plan output, the more indented an operation is, the earlier it is executed. And the result of this operation (or operations, if more than one have are equally indented AND have the same parent) is then feeded to the parent operation. In this case, TABLE ACCESS is made first, and its result feeded to SELECT STATEMENT (which is not an actual operation). Note the FULL in paranthesis in TABLE ACCESS: this means that the entire table is accessed.
Btw, sql*plus automatically explains the plan for you if autotrace is enabled.
Now, let's create an index on that table:
create index test_for_ep_ix on test_for_ep (a);
And do the same select statement again:
delete plan_table;
explain plan for select /*+ rule */ * from test_for_ep where a = 5;
The plan is now:
SELECT STATEMENT ()
 TABLE ACCESS (BY INDEX ROWID) TEST_FOR_EP
  INDEX (RANGE SCAN)           TEST_FOR_EP_IX
Obviously, the index (TEST_FOR_EP_IX) is used first (most indented) then used for a TABLE ACCESS, second most indented, then the result is returned. The table access is not done by a FULL table scan but rather by using the data's rowid.

INDEX

In the last example, Oracle employed an INDEX (RANGE SCAN). The RANGE SCAN basically means, that the index was used, but that it can return more than one row. Now, we create a unique index to see how this alters the explain plan:
create table test_for_ep (a number, b varchar2(100), constraint uq_tp unique(a));
 
delete plan_table;
explain plan for select /*+ rule */ * from test_for_ep where a = 5;
The explained plan is:
SELECT STATEMENT ()
 TABLE ACCESS (BY INDEX ROWID) TEST_FOR_EP
  INDEX (UNIQUE SCAN)          UQ_TP
INDEX (UNIQUE SCAN) means, that this index is used, and it sort of guarantees that this index returnes exactly one rowid. What happens, if we query the field not for equality but for greater than (a>5)?
explain plan for select /*+ rule */ * from test_for_ep where a > 5;
Here, we see that the index is used, but for a RANGE SCAN:
SELECT STATEMENT ()
 TABLE ACCESS (BY INDEX ROWID) TEST_FOR_EP
  INDEX (RANGE SCAN)           UQ_TP
If we only query fields of a table that are already in an index, Oracle doesn't have to read the data blocks because it can get the relevant data from the index:
create table test_for_ep (a number, b varchar2(100), constraint uq_tp unique(a));
 
delete plan_table;
explain plan for select /*+ rule */ a from test_for_ep where a > 5 and a < 50;
Here's the execution plan. No table access anymore!
SELECT STATEMENT ()
 INDEX (RANGE SCAN)            UQ_TP

MERGE JOIN

Usually, you want to select data across multiple table which is referred to as to join the tables. MERGE JOIN is just one possible method of joining the data (the others are HASH JOIN, NESTED LOOPS and CLUSTER JOIN). A Sort Merge Join basically sorts all relevant rows in the first table by the join key (ba), and also sorts the relevant rows in the second table by the join key (aa), and then merges these sorted rows. Take an example! At a garage sale you can buy 400 books. The deal is to take all or none. You take all. Now, you have to find the books that you already have at home. How would you go about it? Probably, you'd do a merge join: first, you sort your books by the primary key (author, title), then you sort the 400 books by their primary key (auther, title). Now, you start at the top of both piles. If the value of the left piles primary key is higher, then you take a book from the right pile and vice versa. When both values are equal, then you have found a dublicate. To demonstrate a MERGE JOIN, two tables need to be created:
create table test_for_ep_a (aa number, ab varchar2(100));
create table test_for_ep_b (ba number, bb varchar2(100));
Note, there are no indexes on both of the tables. Now, we join the tables on aa and ba:
explain plan for 
  select /*+ rule */ a.aa from test_for_ep_a a, test_for_ep_b b where 
  a.aa=b.ba and a.aa > 5;
As there are no indexes, both tables must be TABLE ACCESSed (FULL). After these accesses, their results are sorted.
SELECT STATEMENT ()
 MERGE JOIN ()
  SORT (JOIN)
   TABLE ACCESS (FULL)         TEST_FOR_EP_B
  SORT (JOIN)
   TABLE ACCESS (FULL)         TEST_FOR_EP_A
The behaviour of MERGE JOINS is influenced by the init.ora parameters sort_area_size and db_file_mutliblock_read_count.
Note MERGE JOINs can only be used for equi joins, as is demonstrated in NESTED LOOPS

NESTED LOOPS

For each relevant row in the first table (driving table), find all matching rows in the other table (probed table).
create table test_for_ep_a (aa number, ab varchar2(100));
create table test_for_ep_b (ba number, bb varchar2(100));
explain plan for 
  select /*+ rule */ a.aa from test_for_ep_a a, test_for_ep_b b where 
  a.aa > b.ba and a.aa > 5;
Note, there is no equi join to join test_for_ep_a and test_for_ep_b, (a.aa > b.ba)
SELECT STATEMENT ()
 NESTED LOOPS ()
  TABLE ACCESS (FULL)          TEST_FOR_EP_B
  TABLE ACCESS (FULL)          TEST_FOR_EP_A
Now, we put an index on TEST_FOR_EP_B and see how that influences our nested loop:
create table test_for_ep_a (aa number, ab varchar2(100));
create table test_for_ep_b (ba number, bb varchar2(100), constraint uq_ba unique(ba));
 
delete plan_table;
explain plan for 
  select /*+ rule */ a.aa from test_for_ep_a a, test_for_ep_b b where 
  a.aa > b.ba; 
The plan is:
SELECT STATEMENT ()
 NESTED LOOPS ()
  TABLE ACCESS (FULL)          TEST_FOR_EP_A
  INDEX (RANGE SCAN)           UQ_BA
Interpreted, this means: TEST_FOR_EP_A is fully accessed and for each row, TEST_FOR_EP_B (or more accurately, its index UQ_BA) is probed. Thinking about it, this makes sense, doing the costly TABLE ACCESS once and use the index for each row. Then again, thinking about it, if TEST_FOR_EP_A is very small nad TEST_FOR_EP_B is large, this doesn't make sense anymore. This is when the Cost Based Optimizer comes into play.

Sorts

Aggregate Sorts

Whenever a result set must be sorted, the operation is sort. If this sort is used to return a single row (for example max or min) the options is AGGREGATE. Consider the following example:
create table t_ep (
  w date,
  v number,
  x varchar2(40)
);
 
 
delete plan_table;
explain plan for select /*+ rule */ max(w) from t_ep where v=4;
SELECT STATEMENT ()
 SORT (AGGREGATE)
  TABLE ACCESS (FULL)          T_EP
Now: creating an index:
alter table t_ep add constraint uq_t_ep unique(v);
delete plan_table;
explain plan for select /*+ rule */ max(w) from t_ep where v=4;
SELECT STATEMENT ()
 SORT (AGGREGATE)
  TABLE ACCESS (BY INDEX ROWID) T_EP
   INDEX (UNIQUE SCAN)          UQ_T_EP


    EXPLAIN PLAN command

PURPOSE:
    To determine the execution plan Oracle follows to execute a
    specified SQL statement.  This command inserts a row describing each
    step of the execution plan into a specified table.  If you are using
    cost-based optimization, this command also determines the cost of
    executing the statement.

SYNTAX:
EXPLAIN PLAN
    [SET STATEMENT ID = 'text']
    [INTO [schema.]table[@dblink]]
    FOR statement

where:

SET
    specifies the value of the STATEMENT_ID column for the rows of the
    execution plan in the output table.  If you omit this clause, the
    STATEMENT_ID value defaults to null.

INTO
    specifies the schema, name, and database containing the output
    table.  This table must exist before you use the EXPLAIN PLAN
    command.  If you omit schema, Oracle assumes the table is in your
    own schema.

    The dblink can be a complete or partial name of a database link to a
    remote Oracle7 database where the output table is located.  You can
    only specify a remote output table if you are using Oracle with the
    distributed option.  If you omit dblink, Oracle assumes the table is
    on your local database.
           
    If you omit the INTO clause altogether, Oracle assumes an output
    table           named PLAN_TABLE in your own schema on your local database.

FOR
    specifies a SELECT, INSERT, UPDATE, or DELETE statement for which
    the execution plan is generated.

PREREQUISITES:
    To issue an EXPLAIN PLAN statement, you must have the privileges
    necessary to insert rows into an existing output table that you
    specify to hold the execution plan.  For information on these
    privileges, see the INSERT command.
           
    You must also have the privileges necessary to execute the SQL
    statement for which you are find the execution plan.  If the SQL
    statement accesses a view, you must have privileges to access any
    tables and views on which the view is based.  If the view is based
    on another view that is based on a table, you must have privileges
    to access both the other view and its underlying table.


    To examine the execution plan produced by an EXPLAIN PLAN statement,
    you must have the privileges necessary to query the output table.
    For more information on these privileges, see the SELECT command.

    If you are using Trusted Oracle in DBMS MAC mode, your DBMS label
    must dominate the output table's creation label or you must satisfy
    one of these criteria:

    * If the output table's creation label is higher than your DBMS
      label, you must have READUP and WRITEUP system privileges.
    * If the output table's creation label and your DBMS label are
      noncomparable, you must have READUP, WRITEUP, and WRITEDOWN system
      privileges.
           

            EXPLAIN PLAN Usage

When an SQL statement is passed to the server the Cost Based Optimizer (CBO) uses database statistics to create an execution plan which it uses to navigate through the data. Once you've highlighted a problem query the first thing you should do is EXPLAIN the statement to check the execution plan that the CBO has created. This will often reveal that the query is not using the relevant indexes, or indexes to support the query are missing. Interpretation of the execution plan is beyond the scope of this article.

Plan Table

The explain plan process stores data in the PLAN_TABLE. This table can be located in the current schema or a shared schema and is created using in SQL*Plus as follows:
SQL> @%ORACLE_HOME%\rdbms\admin\utlxplan.sql
SQL> GRANT select, insert, update, delete ON plan_table TO public;
SQL> CREATE PUBLIC SYNONYM plan_table FOR .plan_table;

AUTOTRACE - The Easy Option?

Switching on the AUTOTRACE parameter in SQL*Plus causes an explain to be performed on every query.
SQL> SET AUTOTRACE ON
SQL> SELECT * FROM dual;
 
D
-
X
 
1 row selected.
 
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF 'DUAL'
 
Statistics
----------------------------------------------------------
          0  recursive calls
          4  db block gets
          1  consistent gets
          0  physical reads
          0  redo size
        363  bytes sent via SQL*Net to client
        429  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
 
SQL>
This is a relatively easy way to get the execution plan but there is an issue. In order to get the execution plan the statement must be run to completion. If the query is particularly inefficient and/or returns many rows, this may take a considerable time.

EXPLAIN PLAN

The EXPLAIN PLAN method doesn't require the query to be run, greatly reducing the time it takes to get an execution plan for long-running queries compared to AUTOTRACE. First the query must be explained:
SQL> EXPLAIN PLAN FOR
  2  SELECT * FROM dual;
 
Explained.
 
SQL>
Then the execution plan displayed:
SQL> @%ORACLE_HOME%\rdbms\admin\utlxpls.sql
 
Plan Table
----------------------------------------------------------------------------------------------------
| Operation                 |  Name    |  Rows | Bytes|  Cost  | Pstart| Pstop  |
-----------------------------------------------------------------------------------------------------
| SELECT STATEMENT   |                    |         |           |           |                    |            |
|  TABLE ACCESS FULL  |DUAL      |           |           |           |                    |            |
-----------------------------------------------------------------------------------------------------
 
5 rows selected.
 
SQL>
For parallel queries use the utlxplp.sql script instead of utlxpls.sql.

Statement ID

If multiple people are accessing the same plan table, or you would like to keep a history of the execution plans you should use the STATEMENT_ID. This associates a user specified ID with each plan which can be used when retrieving the data.
SQL> EXPLAIN PLAN STATEMENT_ID = 'TIM' FOR
  2  SELECT * FROM dual;
 
Explained.
 
SQL>

By default the Oracle scripts to not accept a statement_id parameter. You can easily modify the scripts or you can use the script listed under DBA Scripts on this site.

            Oracle's TKPROF

Enabling SQL TRACE

To be able to use TKPROF, you must first enable sql trace. This can be done for either the instance or the session. If you want to change it for the entire instance, set sql_trace=true into the init.ora file and restart the instance. However, usually, you'll want to turn on sql trace for a particular session only. This can be done like so:
or, from another session with a
sys.dbms_system.set_sql_trace_in_session(session's id,serial number)

Timed Statistics

You can have Oracle include timing information into the sql trace files as well. Either set the timed_statistics parameter to truo or or issue an
or

Finding the SQL Trace file

The trace file will be written into the directory pointed to by the parameter user_dump_dest. You can query for the value with select value from v$parameter where name = 'user_dump_dest'.
See find the trace file to see where the tk prof file goes.

Maximum size of the trace file

The init parameter max_dump_file_size allows to set the maximum size of the trace file. Valid values for this parameter are: unlimited, a number followed by a K or a M or a number. If the value is a number only, it indicates how many OS Blocks the file can grow to. K or M set the file's maximum size in kilo or mega bytes.

Using tkprof

Follow the following links:

The meaning of count, cpu and elapsed in tkprof

Be sure to also take a look at Basics of tkprof.
In order to demonstrate the meaning of count, cpu and elapsed in tkprof, three different and simple SQL statements are executed. One of these SQL Statements is executed three times, one is executed twice and one is executed once. The only thing these statements actually do is call dbms_lock.sleep.
set feedback off
 
select spid from v,v where audsid= sys_context('userenv','sessionid') and addr=paddr;
 
alter session set sql_trace=true; 
 
begin /* three times */
  dbms_lock.sleep(1);
end;
/
 
begin /* once */
  dbms_lock.sleep(1);
end;
/
 
begin /* three times */
  dbms_lock.sleep(1);
end;
/
 
begin /* twice */ 
  dbms_lock.sleep(2);
end;
/
begin /* twice */ 
  dbms_lock.sleep(2);
end;
/
 
begin /* three times */
  dbms_lock.sleep(1);
end;
/
alter session set sql_trace=false;
If these statements are run and the trace file is tkprof'ed, it produces the following output:
begin /* three times */
  dbms_lock.sleep(1);
end;
 
call       count      cpu   elapsed    disk    query  current      rows
-------     ------    -------- ---------- ---------- ---------- ----------  ----------
Parse        3        0.01       0.01          0          0          0           0
Execute    3        0.00       3.10          0          0          0           3
Fetch        0        0.00       0.00          0          0          0           0
-------    ------    -------- ---------- ---------- ---------- ----------  ----------
total          6        0.01       3.11          0          0          0           3
 
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 206 
 
--------------------
 
begin /* twice */
  dbms_lock.sleep(2);
end;
 
call      count     cpu   elapsed      disk    query  current      rows
-------     ------  --------   ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.01          0          0          0           0
Execute    2      0.00       4.09          0          0          0           2
Fetch        0      0.00       0.00          0          0          0           0
-------    ------   --------   ---------- ---------- ---------- ----------  ----------
total          4      0.00       4.10          0          0          0           2
 
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 206
 
--------------------
 
 
begin /* once */
  dbms_lock.sleep(1);
end;
 
call      count     cpu   elapsed     disk     query  current      rows
-------     ------  --------   ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.01          0          0          0           0
Execute    1      0.00       1.03          0          0          0           1
Fetch        0      0.00       0.00          0          0          0           0
-------    ------  -------- ---------- ---------- ---------- ----------  ----------
total          2      0.01       1.04          0          0          0           1
 
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 206
 

Query and current in tkprof

Be sure to also take a look at Basics of tkprof.

Setting up a demonstration environment

A table is created to demonstrate the use of TKPROF. This PL/SQL Script will take care of this.
Ok, now let's give it a try:
alter session set sql_trace=true;
alter session set timed_statistics=true;
select id, ob from test_for_tk_1 where nm='elit magna wisi nulla praesent possim';
When the statement returns, we go to the directory pointed to by user_dump_dest and do a:
tkprof ora01676.trc perf
Then, we open the file perf.prf which was created by tkprof and search for the statement:
select id, ob 
from
 test_for_tk_1 where nm='elit magna wisi nulla praesent possim'
 
 
call      count      cpu   elapsed      disk       query     current      rows
-------    ------    --------  ----------   ----------    ----------   ----------  ----------
Parse        1      0.00       0.00          0              0             1           0
Execute    1      0.00       0.00          0              0              0           0
Fetch        2      2.49      30.56      37355      75405         4           1
-------    ------    --------  ----------    ----------    ----------  ----------  ----------
total          4      2.49      30.56      37355      75405          5           1
 
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 18  
 
Rows     Row Source Operation
-------  ---------------------------------------------------
      1  TABLE ACCESS FULL TEST_FOR_TK_1
How is this table to be interpreted? First, the statement was parsed and executed once.

query

Query: also known as consistent gets
The TKPROF output includes in his header the following explanation:
query = number of buffers gotten for consistent read
query means: blocks gotten in consistent mode. That could possibly entail reading the rollback segment to reconstruct the data.
Most often, query gets

current

current: also known as db block gets.
The TKPROF output includes in his header the following explanation:
current = number of buffers gotten in current mode (usually for update)
current mode means: the blocks are read as they are (either in the datafile or still in the buffer cache). Current gets might entail waiting for some data.



  1. A column varchar having numbers and characters mixed. How will get only numbers from it. For e.g. 123AB4CD to 1234.
Select replace(translate(‘123AB4CD’,’ABCD’,’A’),’A’) from dual;
  1. How will get the reverse of Ename column in employee. Suppose you have ename as vidhya output should be ayhdiv. Without using reverse funtion.

  1. Can a package have only specification without body?
Yes. But you can’t have Body without Specification.
  1. Can a package body be compiled separately?
Yes.
  1.  What is the difference between EXIST and IN?
Exist gives the value as True and False depends upon the query gives records or not.
You can give multiple value in the predicate ‘IN’ which will be true for the every value given as in the quotes.
 
  1. Diiference between rownum and rowid?  Why only < is allowed in rownum?
Rowid stores the physical address of the records as a hexadecimal number.
Rownum is psuedo column getting the number while query is executing.

  1. Find out employee number and names from emp having salary greater than Jones salary.
                  Use of subquery.

  1. How to convert an amount in number into amount in words?

select to_char(to_date('123','J'),'JSP') from dual;

  1. What are purity levels in functions?

1.15 Calling PL/SQL Functions in SQL

Stored functions can be called from SQL statements in a manner similar to built-in functions like DECODE, NVL, or RTRIM. This is a powerful technique for incorporating business rules into SQL in a simple and elegant way. Unfortunately, there are a number of caveats and restrictions.
The most notable caveat is that stored functions executed from SQL are not guaranteed to follow the read consistency model of the database. Unless the SQL statement and any stored functions in that statement are in the same read-consistent transaction (even if they are read-only), each execution of the stored function will look at a different time-consistent set of data. To avoid this potential problem, you need to ensure read consistency programmatically by issuing the SET TRANSACTION READ ONLY or SET TRANSACTION SERIALIZABLE statement before executing your SQL statement containing the stored function. A COMMIT or ROLLBACK then needs to follow the SQL statement to end this read-consistent transaction.

1.15.1 Syntax for Calling Stored Functions in SQL

The syntax for calling a stored function from SQL is the same as referencing it from PL/SQL:
   [schema_name.][pkg_name.]func_name[@db_link]
                   [parm_list]
schema_name is optional and refers to the user/owner of the function or package. pkg_name is optional and refers to the package containing the called function. func_name is mandatory and is the function name. db_link is optional and refers to the database link name to the remote database containing the function. parm_list is optional, as are the parameters passed to the function.
The following are example calls to the GetTimestamp function in the time_pkg example seen earlier in the Section 1.14.1, "Overview of Package Structure " section:
-- Capture system events.
   INSERT INTO v_sys_event (timestamp ,event ,qty_waits)
   SELECT time_pkg.GetTimestamp ,event ,total_waits
   FROM v$system_event
 
-- Capture system statistics.
INSERT INTO v_sys_stat (timestamp,stat#,value)
   SELECT time_pkg.GetTimestamp ,statistic# ,value
   FROM v$sysstat;

1.15.2 Requirements and Restrictions on Stored Functions in SQL

There are a number of requirements for calling stored functions in SQL:
·         The function must be a single-row function -- not one that operates on a column or group function.
·         All parameters must be IN; no IN OUT or OUT parameters are allowed.
·         The datatypes of the function's parameters and RETURN must be compatible with RDBMS datatypes. You cannot have arguments or RETURN types like BOOLEAN, programmer-defined record, index-by table, etc.
·         The parameters passed to the function must use positional notation; named notation is not supported.
·         Functions defined in packages must have a RESTRICT_REFERENCES pragma in the specification (Oracle8.0 and earlier).
·         The function must be stored in the database, not a local program, Developer/2000 PL/SQL library, or Form.

1.15.3 Calling Packaged Functions in SQL

Prior to Oracle8i Release 8.1, it was necessary to assert the purity level of a packaged procedure or function when using it directly or indirectly in a SQL statement. Beginning with Oracle8i Release 8.1, the PL/SQL runtime engine determines a program's purity level automatically if no assertion exists.
The RESTRICT_REFERENCES pragma asserts a purity level. The syntax for the RESTRICT_REFERENCES pragma is:
PRAGMA RESTRICT_REFERENCES (program_name | 
   DEFAULT, purity_level);
The keyword DEFAULT applies to all methods of an object type or all programs in a package. There can be from one to five purity levels, in any order, in a comma-delimited list. The purity level describes to what extent the program or method is free of side effects. Side effects are listed in the following table with the purity levels they address.





Purity Level
Description
Restriction
WNDS
Write No Database State
Executes no INSERT, UPDATE, or DELETE statements.
RNDS
Read No Database State
Executes no SELECT statements.
WNPS
Write No Package State
Does not modify any package variables.
RNPS
Read No Package State
Does not read any package variables.
TRUST (Oracle8i)

Does not enforce the restrictions declared but allows the compiler to trust they are true.
The purity level requirements for packaged functions are different depending on where in the SQL statement the stored functions are used:
·         To be called from SQL, all stored functions must assert WNDS.
·         All functions not used in a SELECT, VALUES, or SET clause must assert WNPS.
·         To be executed remotely, the function must assert WNPS and RNPS.
·         To be executed in parallel, the function must assert all four purity levels or, in Oracle8i, use PARALLEL_ENABLED in the declaration.
·         These functions must not call any other program that does not also assert the minimum purity level.
·         If a package has an initialization section, it too must assert purity in Oracle7.
·         If a function is overloaded, each overloading must assert its own purity level, and the levels don't have to be the same. To do this, place the pragma immediately after each overloaded declaration.
Many of the built-in packages, including DBMS_OUTPUT, DBMS_PIPE, and DBMS_SQL, do not assert WNPS or RNPS, so their use in SQL stored functions is necessarily limited.

 

1.15.3.1 Column/function name precedence

If your function has the same name as a table column in your SELECT statement and the function has no parameter, then the column takes precedence over the function. To force the RDBMS to resolve the name to your function, prepend the schema name to it:
CREATE TABLE emp(new_sal NUMBER ...);
CREATE FUNCTION new_sal RETURN NUMBER IS ...;
 
SELECT new_sal FROM emp;      -- Resolves to column.
SELECT scott.new_sal FROM emp;-- Resolves to 
                                 function.


  1. What are the minimum arguments required in Decode?
Three.
  1. A column has a constraint of value not exceeding 100. If try to insert value greater than 100 when will the constraint fire at insert statement or at commit.
By default the constraints are checked immediately after the insert (before commit) but you can make it to check after all the inserts and at the time of commit.

  1. A column varchar having combination of number like 123ABC, 234XYZ. Want to have the max number.

Use of substring and to_number
  1. Explain Materialized view in detail

A materialized view is a database object that contains the results of a query. They are local copies of data located remotely, or are used to create summary tables based on aggregations of a table's data. Materialized views, which store data based on remote tables are also, know as snapshots.
A materialized view can query tables, views, and other materialized views. Collectively these are called master tables (a replication term) or detail tables (a data warehouse term).
For replication purposes, materialized views allow you to maintain copies of remote data on your local node. These copies are read-only. If you want to update the local copies, you have to use the Advanced Replication feature. You can select data from a materialized view as you would from a table or view.
For data warehousing purposes, the materialized views commonly created are aggregate views, single-table aggregate views, and join views.
In this article, we shall see how to create a Materialized View and discuss Refresh Option of the view.
In replication environments, the materialized views commonly created are primary key, rowid, and subquery materialized views.
Primary Key Materialized Views
The following statement creates the primary-key materialized view on the table emp located on a remote database.
SQL>     CREATE MATERIALIZED VIEW mv_emp_pk
               REFRESH FAST START WITH SYSDATE 
               NEXT  SYSDATE + 1/48
               WITH PRIMARY KEY 
               AS SELECT * FROM emp@remote_db;
 
Materialized view created.
Note: When you create a materialized view using the FAST option you will need to create a view log on the master tables(s) as shown below:

SQL> CREATE MATERIALIZED VIEW LOG ON emp;
Materialized view log created.
Rowid Materialized Views
The following statement creates the rowid-materialized view on table emp located on a remote database:

SQL>     CREATE MATERIALIZED VIEW mv_emp_rowid 
               REFRESH WITH ROWID 
               AS SELECT * FROM emp@remote_db; 
 
Materialized view log created.
Subquery Materialized Views
The following statement creates a subquery-materialized view based on the emp and dept tables located on the remote database:
SQL> CREATE MATERIALIZED VIEW  mv_empdept
AS SELECT * FROM emp@remote_db e
WHERE EXISTS
     (SELECT * FROM dept@remote_db d
     WHERE e.dept_no = d.dept_no)

REFRESH CLAUSE

[refresh [fast|complete|force]
               [on demand | commit]
               [start with date] [next date]
               [with {primary key|rowid}]]
The refresh option specifies:
  1. The refresh method used by Oracle to refresh data in materialized view
  2. Whether the view is primary key based or row-id based
  3. The time and interval at which the view is to be refreshed
Refresh Method - FAST Clause
The FAST refreshes use the materialized view logs (as seen above) to send the rows that have changed from master tables to the materialized view.
You should create a materialized view log for the master tables if you specify the REFRESH FAST clause.
SQL> CREATE MATERIALIZED VIEW LOG ON emp;
 
Materialized view log created.
Materialized views are not eligible for fast refresh if the defined subquery contains an analytic function.
Refresh Method - COMPLETE Clause
The complete refresh re-creates the entire materialized view. If you request a complete refresh, Oracle performs a complete refresh even if a fast refresh is possible.
Refresh Method - FORCE Clause
When you specify a FORCE clause, Oracle will perform a fast refresh if one is possible or a complete refresh otherwise. If you do not specify a refresh method (FAST, COMPLETE, or FORCE), FORCE is the default.
PRIMARY KEY and ROWID Clause
WITH PRIMARY KEY is used to create a primary key materialized view i.e. the materialized view is based on the primary key of the master table instead of ROWID (for ROWID clause). PRIMARY KEY is the default option. To use the PRIMARY KEY clause you should have defined PRIMARY KEY on the master table or else you should use ROWID based materialized views.
Primary key materialized views allow materialized view master tables to be reorganized without affecting the eligibility of the materialized view for fast refresh.
Rowid materialized views should have a single master table and cannot contain any of the following:
  • Distinct or aggregate functions
  • GROUP BY Subqueries , Joins & Set operations

Timing the refresh

The START WITH clause tells the database when to perform the first replication from the master table to the local base table. It should evaluate to a future point in time. The NEXT clause specifies the interval between refreshes
SQL>     CREATE MATERIALIZED VIEW mv_emp_pk
               REFRESH FAST 
               START WITH SYSDATE 
               NEXT  SYSDATE + 2
               WITH PRIMARY KEY 
               AS SELECT * FROM emp@remote_db;
 
Materialized view created.
In the above example, the first copy of the materialized view is made at SYSDATE and the interval at which the refresh has to be performed is every two days.

Summary

Materialized Views thus offer us flexibility of basing a view on Primary key or ROWID, specifying refresh methods and specifying time of automatic refreshes. Materialized Views can be refresh on commit or on request on base table using package for that M. View. (DBMS_MVIEW)

32.  How does one escape special characters when building SQL queries?

The LIKE keyword allows for string searches. The '_' wild card character is used to match exactly one character, '%' is used to match zero or more occurrences of any characters. These characters can be escaped in SQL. Example:
       a) SELECT name FROM emp WHERE id LIKE '%\_%' ESCAPE '\';
Use two quotes for every one displayed. Example:
       b)  SELECT 'Franks''s Oracle site' FROM DUAL;
       c)  SELECT 'A ''quoted'' word.' FROM DUAL;
       d)  SELECT 'A ''''double quoted'''' word.' FROM DUAL;  

33. How does one get the time difference between two date columns?

Look at this example query:
               select floor(((date1-date2)*24*60*60)/3600)
                              || ' HOURS ' ||
                                              floor((((date1-date2)*24*60*60) -
                                              floor(((date1-date2)*24*60*60)/3600)*3600)/60)
                                              || ' MINUTES ' ||
                                              round((((date1-date2)*24*60*60) -
                                              floor(((date1-date2)*24*60*60)/3600)*3600 -
                                              (floor((((date1-date2)*24*60*60) -
                                              floor(((date1-date2)*24*60*60)/3600)*3600)/60)*60)))
                                              || ' SECS ' time_difference
                               from   ...
If you don't want to go through the floor and ceiling math, try this method (contributed by Erik Wile):
        select to_char(to_date('00:00:00','HH24:MI:SS') +
                    (date1 - date2), 'HH24:MI:SS') time_difference
        from ...
Note that this query only uses the time portion of the date and ignores the date itself. It will thus never return a value bigger than 23:59:59.

34.  How does one add a day/hour/minute/second to a date value?

The SYSDATE pseudo-column shows the current system date and time. Adding 1 to SYSDATE will advance the date by 1 day. Use fractions to add hours, minutes or seconds to the date. Look at these examples:
        SQL> select sysdate, sysdate+1/24, sysdate +1/1440, sysdate + 1/86400 from dual;
                               
        SYSDATE                    SYSDATE+1/24            SYSDATE+1/1440        SYSDATE+1/86400
        --------------------             --------------------              --------------------              --------------------
        03-Jul-2002 08:32:12   03-Jul-2002 09:32:12    03-Jul-2002 08:33:12    03-Jul-2002 08:32:13
 
The following format is frequently used with Oracle Replication:
       select sysdate NOW, sysdate+30/(24*60*60) NOW_PLUS_30_SECS from dual;
 
        NOW                           NOW_PLUS_30_SECS
        --------------------             --------------------
        03-JUL-2002 16:47:23  03-JUL-2002 16:47:53

35. Can one retrieve only the Nth row from a table?

Rupak Mohan provided this solution to select the Nth row from a table:
               SELECT * FROM t1 a
               WHERE  n = (SELECT COUNT(rowid)
                               FROM t1 b
                               WHERE a.rowid >= b.rowid);
Shaik Khaleel provided this solution:
                SELECT * FROM (
 SELECT ENAME,ROWNUM RN FROM EMP WHERE ROWNUM < 101 )
                WHERE  RN = 100;
Note: In this first query we select one more than the required row number, then we select the required one. Its far better than using a MINUS operation.
Ravi Pachalla provided these solutions:
               SELECT f1 FROM t1
               WHERE  rowid = (
                               SELECT rowid FROM t1
                               WHERE  rownum <= 10
                               MINUS
                               SELECT rowid FROM t1
                               WHERE  rownum < 10);
               SELECT rownum,empno FROM scott.emp a
               GROUP BY rownum,empno HAVING rownum = 4;
               
Alternatively...
        SELECT * FROM emp WHERE rownum=1 AND rowid NOT IN
           (SELECT rowid FROM emp WHERE rownum < 10);
Please note, there is no explicit row order in a relational database. However, this query is quite fun and may even help in the odd situation.

36. How does one select EVERY Nth row from a table?

One can easily select all even, odd, or Nth rows from a table using SQL queries like this:
Method 1: Using a subquery
        SELECT *
        FROM   emp
        WHERE  (ROWID,0) IN ( SELECT ROWID, MOD(ROWNUM,4)
                                              FROM   emp);
 
Method 2: Use dynamic views (available from Oracle7.2):
        SELECT *
        FROM   ( SELECT rownum rn, empno, ename FROM emp ) temp
        WHERE  MOD(temp.ROWNUM,4) = 0;
 
Method 3: Using GROUP BY and HAVING - provided by Ravi Pachalla
               SELECT rownum, f1
               FROM t1
               GROUP BY rownum, f1 HAVING MOD(rownum,n) = 0 OR rownum = 2-n
Please note, there is no explicit row order in a relational database. However, these queries are quite fun and may even help in the odd situation.

37.  How does one select the TOP N rows from a table?

Form Oracle8i one can have an inner-query with an ORDER BY clause. Look at this example:
                       SELECT *  FROM   (SELECT * FROM my_table ORDER BY col_name_1 DESC)
                                                      WHERE  ROWNUM < 10;
Use this workaround with prior releases:
                       SELECT *  FROM my_table a
                        WHERE 10 >= (    SELECT COUNT(DISTINCT maxcol)   FROM my_table b
                                                      WHERE b.maxcol >= a.maxcol)
                        ORDER BY maxcol DESC;

38.    How does one code a tree-structured query?

Tree-structured queries are definitely non-relational (enough to kill Codd and make him roll in his grave). Also, this feature is not often found in other database offerings.The SCOTT/TIGER database schema contains a table EMP with a self-referencing relation (EMPNO and MGR columns). This table is perfect for tesing and demonstrating tree-structured queries as the MGR column contains the employee number of the "current" employee's boss.

The LEVEL pseudo-column is an indication of how deep in the tree one is. Oracle can handle queries with a depth of up to 255 levels. Look at this example:
               select  LEVEL, EMPNO, ENAME, MGR
               from  EMP
               connect by prior EMPNO = MGR
               start with MGR is NULL;
One can produce an indented report by using the level number to substring or lpad() a series of spaces, and concatenate that to the string. Look at this example:
               select lpad(' ', LEVEL * 2) || ENAME ........
One uses the "start with" clause to specify the start of the tree. More than one record can match the starting condition. One disadvantage of having a "connect by prior" clause is that you cannot perform a join to other tables. The "connect by prior" clause is rarely implemented in the other database offerings. Trying to do this programmatically is difficult as one has to do the top level query first, then, for each of the records open a cursor to look for child nodes.
One way of working around this is to use PL/SQL, open the driving cursor with the "connect by prior" statement, and the select matching records from other tables on a row-by-row basis, inserting the results into a temporary table for later retrieval.


39.   How does one code a matrix report in SQL?

Look at this example query with sample output:
                       SELECT  * FROM  (SELECT job,
                                                                                  sum(decode(deptno,10,sal)) DEPT10,
                                                                    sum(decode(deptno,20,sal)) DEPT20,
                                                                  sum(decode(deptno,30,sal)) DEPT30,
                                                                  sum(decode(deptno,40,sal)) DEPT40
                        FROM scott.emp
                        GROUP BY job)
                         ORDER BY 1;
 
        JOB                DEPT10 DEPT20 DEPT30 DEPT40
        ---------           ----------   ----------   ----------   ----------
        ANALYST                                  6000
        CLERK                        1300                       1900                       950
        MANAGER       2450                 2975                       2850
        PRESIDENT     5000
        SALESMAN                               5600

40.   How does one implement IF-THEN-ELSE in a select statement?

The Oracle decode function acts like a procedural statement inside an SQL statement to return different values or columns based on the values of other columns in the select statement.
Some examples:
        select decode(sex, 'M', 'Male', 'F', 'Female', 'Unknown')  from   employees;
 
        select a, b, decode( abs(a-b), a-b, 'a > b',
                                                      0,   'a = b',
                                                                     'a < b') from  tableX;
 
        select decode( GREATEST(A,B), A, 'A is greater OR EQUAL than B', 'B is greater than A')...
                               
        select decode( GREATEST(A,B), 
                                 A, decode(A, B, 'A NOT GREATER THAN B', 'A GREATER THAN B'), 
                                                'A NOT GREATER THAN B')...
Note: The decode function is not ANSI SQL and is rarely implemented in other RDBMS offerings. It is one of the good things about Oracle, but use it sparingly if portability is required.
From Oracle 8i one can also use CASE statements in SQL. Look at this example:
        SELECT ename, CASE WHEN sal>1000 THEN 'Over paid' ELSE 'Under paid' END
        FROM   emp;

41.   How can one dump/ examine the exact content of a database column?

        SELECT DUMP(col1)
        FROM tab1
        WHERE cond1 = val1;
 
        DUMP(COL1)
        -------------------------------------
        Typ=96 Len=4: 65,66,67,32
For this example the type is 96, indicating CHAR, and the last byte in the column is 32, which is the ASCII code for a space. This tells us that this column is blank-padded.

42.   Workaround for snapshots on tables with LONG columns

You can use the SQL*Plus COPY command instead of snapshots if you need to copy LONG and LONG RAW variables from one location to another. Eg:
               COPY TO SCOTT/TIGER@REMOTE     -
               CREATE IMAGE_TABLE USING       -
                      SELECT IMAGE_NO, IMAGE  -
                      FROM   IMAGES;
Note: If you run Oracle8, convert your LONGs to LOBs, as it can be replicated.

43.   Describe Create Table syntax
   CREATE TABLE command
PURPOSE:
   To create a table, the basic structure to hold user data, specifying  this information:
    * column definitions
    * integrity constraints
    * the table's tablespace
    * storage characteristics
    * an optional cluster
    * data from an arbitrary query

SYNTAX:
CREATE TABLE [schema.]table
     ( { column datatype [DEFAULT expr] [column_constraint] ...
       | table_constraint}
    [, { column datatype [DEFAULT expr] [column_constraint] ...
       | table_constraint} ]...)
    [ [PCTFREE  integer] [PCTUSED  integer]
      [INITRANS integer] [MAXTRANS integer]
      [TABLESPACE tablespace]
      [STORAGE storage_clause]
      [ RECOVERABLE | UNRECOVERABLE ]
    [  PARALLEL ( [ DEGREE { integer | DEFAULT } ]
                  [ INSTANCES { integer | DEFAULT } ]
                )
     | NOPARALLEL ]
    [  CACHE | NOCACHE  ]
    | [CLUSTER cluster (column [, column]...)] ]
    [ ENABLE   enable_clause
    | DISABLE disable_clause ] ...
    [AS subquery]

where:

schema
    is the schema to contain the table.  If you omit schema, Oracle
    creates the table in your own schema.

table
    is the name of the table to be created.

column
    specifies the name of a column of the table.  The number of columns
    in a table can range from 1 to 254.

datatype
    is the datatype of a column.

DEFAULT
    specifies a value to be assigned to the column if a subsequent
    INSERT statement omits a value for the column.  The datatype of the
    expression must match the datatype of the column.  A DEFAULT
    expression cannot contain references to other columns, the
    pseudocolumns CURRVAL, NEXTVAL, LEVEL, and ROWNUM, or date constants
    that are not fully specified.

column_constraint
    defines an integrity constraint as part of the column definition.

table_constraint
    defines an integrity constraint as part of the table definition.

PCTFREE
    specifies the percentage of space in each of the table's data blocks
    reserved for future updates to the table's rows.  The value of
    PCTFREE must be a positive integer from 1 to 99.  A value of 0
    allows the entire block to be filled by inserts of new rows.  The
    default value is 10.  This value reserves 10% of each block for
    updates to existing rows and allows inserts of new rows to fill a
    maximum of 90% of each block.

    PCTFREE has the same function in the commands that create and alter
    clusters, indexes, snapshots, and snapshot logs.  The combination of
    PCTFREE and PCTUSED determines whether inserted rows will go into
    existing data blocks or into new blocks.

PCTUSED
    specifies the minimum percentage of used space that Oracle maintains
    for each data block of the table.  A block becomes a candidate for
    row insertion when its used space falls below PCTUSED.  PCTUSED is
    specified as a positive integer from 1 to 99 and defaults to 40.

    PCTUSED has the same function in the commands that create and alter
    clusters, snapshots, and snapshot logs.

    The sum of PCTFREE and PCTUSED must be less than 100.  You can use
    PCTFREE and PCTUSED together use space within a table more
    efficiently.
INITRANS
    specifies the initial number of transaction entries allocated within
    each data block allocated to the table.  This value can range from 1
    to 255 and defaults to 1.  In general, you should not change the
    INITRANS value from its default.

    Each transaction that updates a block requires a transaction entry
    in the block.  The size of a transaction entry depends on your
    operating system.

    This parameter ensures that a minimum number of concurrent
    transactions can update the block and helps avoid the overhead of
    dynamically allocating a transaction entry.

    The INITRANS parameter serves the same purpose in clusters, indexes,
    snapshots, and snapshot logs as in tables.  The minimum and default
    INITRANS value for a cluster or index is 2, rather than 1.

MAXTRANS
    specifies the maximum number of concurrent transactions that can
    update a data block allocated to the table.  This limit does not
    apply to queries.  This value can range from 1 to 255 and the
    default is a function of the data block size.  You should not change
    the MAXTRANS value from its default.

    If the number concurrent transactions updating a block exceeds the
    INITRANS value, Oracle dynamically allocates transaction entries in
    the block until either the MAXTRANS value is exceeded or the block
    has no more free space.
    The MAXTRANS parameter serves the same purpose in clusters,
    snapshots, and snapshot logs as in tables.

TABLESPACE
    specifies the tablespace in which Oracle creates the table.  If you
    omit this option, then Oracle creates the table in the default
    tablespace of the owner of the schema containing the table.

STORAGE
    specifies the storage characteristics for the table.  This clause
    has performance ramifications for large tables.  Storage should be
    allocated to minimize dynamic allocation of additional space.

RECOVERABLE
       specifies that the index will be re-created as part of a database
       recovery. This is the default in ARCHIVELOG mode. You cannot specify
       RECOVERABLE when using NOARCHIVELOG mode.

UNRECOVERABLE
       specifies that the index will not be re-created during recovery. For
       more information on recoverable and unrecoverable objects, see
       Oracle7 Server Administrator's Guide.

PARALLEL
    DEGREE specifies the number of query server processes that can scan
    the table in parallel.  Either specify a positive integer or DEFAULT
    which signifies to use the initialization parameter

    INSTANCES specifies the minimum number of instances that need to be
    of a Parallel Server.  A positive integer specifies the number of
    caches.

NOPARALLEL
    specifies that queries on this table are not performed in parallel
    by default.  A hint in the query still causes the query to be
    performed in parallel.

CACHE
    specifies that blocks of this table are placed on the most recently
    is performed.
    This option is useful for small lookup tables.

NOCACHE
    specifies that blocks of the table in the buffer cache follow the
    standard LRU algorithm when a full table scan is performed.

CLUSTER
    specifies that the table is to be part of the cluster.  The columns
    listed in this clause are the table columns that correspond to the
    cluster's columns.  Generally, the cluster columns of a table are
    the column or columns that comprise its primary key or a portion of
    its primary key.

    Specify one column from the table for each column in the cluster
    key.  The columns are matched by position, not by name.  Since a
    clustered table uses the cluster's space allocation, do not use the
    PCTFREE, PCTUSED, INITRANS, or MAXTRANS parameters, the TABLESPACE
    option, or the STORAGE clause in conjunction with the CLUSTER
    option.

ENABLE
    enables an integrity constraint.

DISABLE
    disables an integrity constraint.

    Constraints specified in the ENABLE and DISABLE clauses of a CREATE
    TABLE statement must be defined in the statement.  You can also
    enable and disable constraints with the ENABLE and DISABLE keywords
    of the CONSTRAINT clause.  If you define a constraint but do not
    explicitly enable or disable it, Oracle enables it by default.

    You cannot use the ENABLE and DISABLE clauses in a CREATE TABLE
    statement to enable and disable triggers.

AS subquery
    inserts the rows returned by the subquery into the table upon its
    creation.

    If you include this clause, the column definitions can only specify
    column names, default values, and integrity constraints, not
    datatypes.  Oracle derives column datatypes and lengths from the
    subquery.  Oracle also automatically defines NOT NULL constraints on
    columns in the new table if they existed on the corresponding
    columns of the selected table and the subquery does not modify the
    column value with a SQL function or operator.  A CREATE TABLE
    statement cannot contain both the AS clause and a referential
    integrity constraint definition.

    The number of columns must equal the number of expressions in the
    subquery.  If all expressions in the subquery are columns, you can
    omit the columns from the table definition entirely.  In this case,
    the names of the columns of table are the same as the columns in the
    subquery.

PREREQUISITES:
    To create a table in your own schema, you must have CREATE TABLE
    system privilege.  To create a table in another user's schema, you
    must have CREATE ANY TABLE system privilege.  Also, the owner of the
    schema to contain the table must have either space quota on the
    tablespace to contain the table or UNLIMITED TABLESPACE system
    privilege.

SEE:
    ALTER TABLE, CONSTRAINT, CREATE CLUSTER, CREATE INDEX, CREATE
    TABLESPACE, DISABLE, DROP TABLE, ENABLE, STORAGE


    CREATE TABLESPACE command


PURPOSE:
    To create a tablespace.  A tablespace is an allocation of space in
    the database that can contain objects.

SYNTAX:

CREATE TABLESPACE tablespace
    DATAFILE filespec
        [ AUTOEXTEND { OFF
                     | ON [NEXT integer [K|M]]
                          [MAXSIZE [UNLIMITED | integer [K|M]] } ]
        [, filespec ...]
    [DEFAULT STORAGE storage_clause]
    [ONLINE | OFFLINE]
 
where:
 
tablespace
    is the name of the tablespace to be created.
 
DATAFILE
    specifies the data file or files to comprise the tablespace.
 
    AUTOEXTEND
       enables or disables the autoextending of the size of the datafile in
       the tablespace.
 
       OFF disable autoextend if it is turned on. NEXT and MAXSIZE are set
           to zero. Values for NEXT and MAXSIZE must be respecified in
           further ALTER TABLESPACE AUTOEXTEND commands.
 
        ON enable autoextend.
 
      NEXT the size in bytes of the next increment of disk space to be
           automatically allocated to the datafile when more extents are
           required. You can also use K or M to specify this size in
           in kilobytes or megabytes. The default is one data block.
 
    MAXSIZE maximum disk space allowed for automatic extension of the
           datafile.
 
    UNLIMITED set no limit on allocating disk space to the datafile.
 
DEFAULT STORAGE
    specifies the default storage parameters for all objects created in
    the tablespace.
 
ONLINE
    makes the tablespace available immediately after creation to users
    who have been granted access to the tablespace.
 
OFFLINE
    makes the tablespace unavailable after immediately after creation.
 
    If you omit both the ONLINE and OFFLINE options, Oracle creates the
    tablespace online by default.  The data dictionary view
    DBA_TABLESPACES indicates whether each tablespace is online or
    offline.
 
PREREQUISITES:
    You must have CREATE TABLESPACE system privilege.  Also, the SYSTEM
    tablespace must contain at least two rollback segments including the
    SYSTEM rollback segment.
 
44.  Explain Snapshots
 
    CREATE SNAPSHOT command
 
PURPOSE:
    To create a snapshot.  A snapshot is a table that contains the
    results of a query of one or more tables or views, often located on
    a remote database.
 
SYNTAX:
 
CREATE SNAPSHOT [schema.]snapshot
    [ [PCTFREE  integer] [PCTUSED  integer]
      [INITRANS integer] [MAXTRANS integer]
      [TABLESPACE tablespace]
      [STORAGE storage_clause]
    [ USING INDEX [  PCTFREE integer | TABLESPACE tablespace
                    | INITTRANS integer | MAXTRANS integer
                    | STORAGE storage_clause ] ...
    | [CLUSTER cluster (column [, column]...)] ]
    [ REFRESH [FAST | COMPLETE | FORCE] [START WITH date] [NEXT date]]
    AS subquery
 
where:
 
schema
    is the schema to contain the snapshot.  If you omit schema, Oracle
    creates the snapshot in your schema.
snapshot
    is the name of the snapshot to be created.
 
    Oracle chooses names for the table, views, and index used to
    maintain the snapshot by prefixing the snapshot name.  To limit
    these names to 30 bytes and allow them to contain the entire
    snapshot name, Oracle Corporation recommends that you limit your
    snapshot names to 23 bytes.
 
PCTFREE
PCTUSED
INITRANS
MAXTRANS
    establishes values for these parameters for the internal table
    Oracle uses to maintain the snapshot's data.
 
TABLESPACE
    specifies the tablespace in which the snapshot is to be created.  If
    you omit this option, Oracle creates the snapshot in the default
    tablespace of the owner of the snapshot's schema.
 
STORAGE
    establishes storage characteristics for the table Oracle uses to
    maintain the snapshot's data.
 
USING INDEX
    specifies the storage characteristics for the index on a simple
    snapshot.  If the USING INDEX clause not specified, the index is
    create with the same tablespace and storage parameters as the
    snapshot.
 
CLUSTER
    creates the snapshot as part of the specified cluster.  Since a
    clustered snapshot uses the cluster's space allocation, do not use
    the PCTFREE, PCTUSED, INITRANS, or MAXTRANS parameters, the
    TABLESPACE option, or the STORAGE clause in conjunction with the
    CLUSTER option.
 
REFRESH
    specifies how and when Oracle automatically refreshes the snapshot:
            FAST
                   specifies a fast refresh, or a refresh using only the
                   updated data stored in the snapshot log associated
                   with the master table.
            COMPLETE
                   specifies a complete refresh, or a refresh that re-
                   executes the snapshot's query.
            FORCE
                   specifies a fast refresh if one is possible or
                   complete refresh if a fast refresh is not possible.
                   Oracle decides whether a fast refresh is possible at
                   refresh time.
                   If you omit the FAST, COMPLETE, and FORCE options,
                   Oracle uses FORCE by default.
            START WITH
                   specifies a date expression for the first automatic
                   refresh time.
            NEXT
                   specifies a date expression for calculating the
                   interval between automatic refreshes.
 
    Both the START WITH and NEXT values must evaluate to a time in the
    future.  If you omit the START WITH value, Oracle determines the
    first automatic refresh time by evaluating the NEXT expression when
    you create the snapshot.  If you specify a START WITH value but omit
    the NEXT value, Oracle refreshes the snapshot only once.  If you
    omit both the START WITH and NEXT values or if you omit the REFRESH
    clause entirely, Oracle does not automatically refresh the snapshot.
 
AS subquery
    specifies the snapshot query.  When you create the snapshot, Oracle
    executes this query and places the results in the snapshot.  The
    select list can contain up to 253 expressions.  A snapshot query is
    subject to the same restrictions as a view query.
 
PREREQUISITES:
    To create a snapshot in your own schema, you must have CREATE
    SNAPSHOT system privilege.  To create a snapshot in another user's
    schema, you must have CREATE ANY SNAPSHOT system privilege.
 
    Before a snapshot can be created, the user SYS must run the SQL
    script DBMSSNAP.SQL on both the database to contain the snapshot and
    the database(s) containing the tables and views of the snapshot's
    query.  This script creates the package SNAPSHOT which contains both
    public and private stored procedures used for refreshing the
    snapshot and purging the snapshot log.  The exact name and location
    of this script may vary depending on your operating system.
    When you create a snapshot, Oracle creates a table, two views, and
    an index in the schema of the snapshot.  Oracle uses these objects
    to maintain the snapshot's data.  You must have the privileges
    necessary to create these objects.  For information on these
    privileges, see the CREATE TABLE, CREATE VIEW, and CREATE INDEX
    commands.
 
    The owner of the schema containing the snapshot must have either
    space quota on the tablespace to contain the snapshot or UNLIMITED
    TABLESPACE system privilege.  Also, both you (the creator) and the
    owner must also have the privileges necessary to issue the
    snapshot's query.
 
    To create a snapshot, you must be using Oracle with the procedural
    option.  To create a snapshot on a remote table or view, you must
    also be using the distributed option.
 
SEE:
    ALTER SNAPSHOT, CREATE SNAPSHOT LOG, DROP SNAPSHOT
    CREATE SNAPSHOT LOG command
 
PURPOSE:
    To create a snapshot log.  A snapshot log is a table associated with
    the master table of a snapshot.  Oracle stores changes to the master
    table's data in the snapshot log and then uses the snapshot log to
    refresh the master table's snapshots.
 
SYNTAX:
CREATE SNAPSHOT LOG ON [schema.]table
    [PCTFREE  integer]    [PCTUSED  integer]
    [INITRANS integer]    [MAXTRANS integer]
    [TABLESPACE tablespace]
    [STORAGE storage_clause]
 
where:
 
schema
    is the schema containing the snapshot log's master table.  If you
    omit schema, Oracle assumes the master table is contained in your
    own schema. Oracle creates the snapshot log in the schema of its
    master table.  You cannot create a snapshot log for a table in the
    schema of the user SYS.
 
table
    is the name of the master table for which the snapshot log is to be
    created.  You cannot create a snapshot log for a view.
 
    Oracle chooses names for the table and trigger used to maintain the
    snapshot log by prefixing the master table name.  To limit these
    names to 30 bytes and allow them to contain the entire master table
    name, Oracle Corporation recommends that you limit master table
    names to 24 bytes.
 
PCTFREE
PCTUSED
INITRANS
MAXTRANS
    establishes values for these parameters for the snapshot log.
 
TABLESPACE
    specifies the tablespace in which the snapshot log is to be created.
    If you omit this option, Oracle creates the snapshot log in the
    default tablespace the owner of the snapshot log's schema.
 
STORAGE
    establishes storage characteristics for the snapshot log.
 
PREREQUISITES:
    You must have the privileges necessary to create a table in the
    schema of the master table.  For information on these privileges,
    see the CREATE TABLE command.
 
    Before a snapshot log can be created, the user SYS must run the SQL
    script DBMSSNAP.SQL on the database containing the master table.
    This script creates the package SNAPSHOT which contains both public
    and private stored procedures used for refreshing the snapshot and
    urging the snapshot log.  The exact name and location of this script
    may vary depending on your operating system.
 
    You must also have the privileges to create a trigger on the master
    table.  For information on these privileges, see the CREATE TRIGGER
    command.
 
 
    If you are using Trusted Oracle in DBMS MAC mode, your DBMS label
    must dominate the label of the tablespace in which the snapshot log
    is to be stored.
 
45.  What are the Different types Parameters you can pass in Procedure and Function?
 
            CREATE PROCEDURE command

PURPOSE:
                        To create a stand-alone stored procedure.  A procedure  is a group
                        of PL/SQL statements that you can call by name.

SYNTAX:

CREATE [OR REPLACE] PROCEDURE [schema.]procedure
                          [ (argument [IN | OUT | IN OUT] datatype
                         [, argument [IN | OUT | IN OUT] datatype] ...)]
                        {IS | AS} pl/sql_subprogram_body

where:
                       
OR REPLACE
    recreates the procedure if it already exists.  You can use this
    option to change the definition of an existing procedure without
    dropping, recreating, and regranting object privileges previously
    granted on it.  If you redefine a procedure, Oracle recompiles it.
    For information on recompiling procedures, see the ALTER PROCEDURE
    command.

    Users who had previously been granted privileges on a redefined
    procedure can still access the procedure without being regranted the
    privileges.

    CREATE FUNCTION command

PURPOSE:
    To create a stand-alone stored function.  A stored function is a set
    of PL/SQL statements you can call by name.  Stored functions are
    very similar to procedures, except that a function returns a value
    to the environment in which it is called.

SYNTAX:

CREATE [OR REPLACE] FUNCTION [schema.]function
    [ (argument [IN] datatype
    [, argument [IN] datatype] ...)]
    RETURN datatype
    {IS | AS} pl/sql_subprogram_body

where:

OR REPLACE
    recreates the function if it already exists.  You can use this
    option to change the definition of an existing function without
    dropping, recreating, and regranting object privileges previously
    granted on the function.  If you redefine a function, Oracle
    recompiles it.  For information on recompiling functions, see the
    ALTER FUNCTION command.

    Users who had previously been granted privileges on a redefined
    function can still access the function without being regranted the
    privileges.

schema
    is the schema to contain the function.  If you omit schema, Oracle
    creates the function in your current schema.

function
    is the name of the function to be created.

argument
    is the name of an argument to the function.  If the function does
    not accept arguments, you can omit the parentheses following the
    function name.

IN
    specifies that you must supply a value for the argument when calling
    the function.  This is always true for function arguments, so this
    keyword is entirely optional.

    A procedure, rather than a stored function, can accept arguments for
    which the procedure passes a value back to the calling environment
    after execution.

datatype
    is the datatype of an argument.  An argument can have any datatype
    supported by PL/SQL.

    The datatype cannot specify a length, precision, or scale.  Oracle
    derives the length, precision, or scale of an argument from the
    environment from which the function is called.


RETURN datatype
    specifies the datatype of the function's return value.  Because
    every function must return a value, this clause is required.  The
    return value can have any datatype supported by PL/SQL.

    The datatype cannot specify a length, precision, or scale.  Oracle
    derives the length, precision, or scale of the return value from the
    environment from which the function is called.

pl/sql_subprogram_body
    is the definition of the function.  Function definitions are written
    in PL/SQL.
    To embed a CREATE FUNCTION statement inside an Oracle Precompiler
    program, you must terminate the statement with the keyword END-EXEC
    followed by the embedded SQL statement terminator for the specific
    language.

PREREQUISITES:
    Before a stored function can be created, the user SYS must run the
    SQL script DBMSSTDX.SQL.  The exact name and location of this script
    may vary depending on your operating system.

    To create a function in your own schema, you must have CREATE
    PROCEDURE system privilege.  To create a function in another user's
    schema, you must have CREATE ANY PROCEDURE system privilege.

    If you are using Trusted Oracle in DBMS MAC mode, you can create a
    function in another user's schema if your DBMS label dominates the
    creation label of the other user.

46.  Explain Packages

    CREATE PACKAGE command

PURPOSE:
    To create the specification for a stored package.  A package is an
    encapsulated collection of related procedures, functions, and other
    program objects stored together in the database.  The specification
    declares these objects.

SYNTAX:

CREATE [OR REPLACE] PACKAGE [schema.]package
    {IS | AS} pl/sql_package_spec

where:

OR REPLACE
    recreates the package specification if it already exists.  You can
    use this option to change the specification of an existing package
    without dropping, recreating, and regranting object privileges
    previously granted on the package.  If you change a package
    specification, Oracle recompiles it.  For information on recompiling
    package specifications, see the ALTER PROCEDURE command.

    Users who had previously been granted privileges on a redefined
    package can still access the package without being regranted the
    privileges.

Schema
    is the schema to contain the package.  If you omit schema, Oracle
    creates the package in your own schema.

package
    is the name of the package to be created.

pl/sql_package_spec
    is the package specification.  The package specification can declare
    program objects.  Package specifications are written in PL/SQL.

    To embed a CREATE PACKAGE statement inside an Oracle Precompiler
    program, you must terminate the statement with the keyword END-EXEC
    followed by the embedded SQL statement terminator for the specific
    language.

PREREQUISITES:
    Before a package can be created, the user SYS must run the SQL
    script DBMSSTDX.SQL.  The exact name and location of this script may
    vary depending on your operating system.

    To create a package in your own schema, you must have CREATE
    PROCEDURE system privilege.  To create a package in another user's
    schema, you must have CREATE ANY PROCEDURE system privilege.

    If you are using Trusted Oracle in DBMS MAC mode, you can only
    create a package in another user's schema if your DBMS label
    dominates the creation label of the other user.
    To create a package, you must be using Oracle with the procedural
    option.

SEE:
    ALTER PACKAGE, CREATE FUNCTION, CREATE PACKAGE BODY, CREATE
    PROCEDURE, DROP PACKAGE
    CREATE PACKAGE BODY command

PURPOSE:
    To create the body of a stored package.  A package is an
    encapsulated collection of related procedures, stored functions, and
    other program objects stored together in the database.  The body
    defines these objects.

SYNTAX:

CREATE [OR REPLACE] PACKAGE BODY [schema.]package
    {IS | AS} pl/sql_package_body

where:

OR REPLACE
    recreates the package body if it already exists.  You can use this
    option to change the body of an existing package without dropping,
    recreating, and regranting object privileges previously granted on
    it.  If you change a package body, Oracle recompiles it.  For
    information on recompiling package bodies, see the ALTER PACKAGE
    BODY command.

    Users who had previously been granted privileges on a redefined
    package can still access the package without being regranted the
    privileges.

schema
    is the schema to contain the package.  If you omit schema, Oracle
    creates the package in your current schema.

package
    is the name of the package to be created.

pl/sql_package_body
    is the package body.  The package body can declare and define
    program objects.  Package bodies are written in PL/SQL.

    To embed a CREATE PACKAGE BODY statement inside an Oracle
    Precompiler program, you must terminate the statement with the
    keyword END-EXEC followed by the embedded SQL statement terminator
    for the specific language.

PREREQUISITES:
    Before a package can be created, the user SYS must run the SQL
    script DBMSSTDX.SQL.  The exact name and location of this script may
    vary depending on your operating system.

    To create a package in your own schema, you must have CREATE
    PROCEDURE system privilege.  To create a package in another user's
    schema, you must have CREATE ANY PROCEDURE system privilege.

    If you are using Trusted Oracle in DBMS MAC mode, you can only
    create a package in another user's schema if your DBMS label
    dominates the creation label of the other user.

    To create a package, you must be using Oracle with the procedural
    option.

Summary:
           There are two types of functions/Procedure/Variable  in packages
                       a)  Public Function:-   Functions define at package specification is called as Public Function.
                       b)  Private Function:-  Functions define at package Body is called as Private Function.

Function / Procedure Overloading:
                       A Function Name can be use within a same package with different signature.

47.  Oracle Database Objects.
Table, Index, Views, Sequences, Stored Procedure, Functions, Packages, Snapshot, Synonyms, Types, Clusters, Role, Database Links, Jobs, Triggers, Partition.

48.   Explain Indexes.
Types Of Indexes.
a)      B-Tree Index: - Its Oracle default Index type.
A traditional B-Tree (balanced tree) index stores the key values and pointers in an inverted tree structure. The key to good B-Tree index performance is to build the index on columns having a lot of different values. Oracle describes this as "good selectivity" Oracle is able to quickly bypass rows that do not meet the search criteria when searching through indexes built on columns having a high degree of selectivity.


b)     Bitmap Index: - 
Conversely, bitmapped indexes perform better when the selectivity of an index is poor. The fewer different values a bitmapped index contains, the better it will perform. Bitmap indexes, in certain situations, can provide impressive performance benefits. Bitmapped indexes are most appropriate for complex and ad-hoc queries that contain lengthy WHERE clauses on columns that have a limited number of different values (poor selectivity).  
c)      Reverse Key: -








d)     Function based Index: -
One of the largest problems with indexes is that the indexes are often suppressed by developers.  Developers using the UPPER function can suppress an index on a column for a given query.  In Oracle8i, there is now a way to combat this problem.  Function-based indexes allow you to create an index based on a function or expression. The value of the function or expression is specified by the person creating the index and is stored in the index. Function-based indexes can involve multiple columns, arithmetic expressions or may be a PL/SQL function or C callout.  The following example shows an example of a function based index.       
e)      #key Index: -









f)       Cluster Index: -












49.  Is there a limit to the number of indexes that we can have on a table?
If most of the columns are selected often from a table is it advisable to define indexes on all the columns Besides what other performance issues can be created by indexes other than the overhead in insert operations.

      No limit.
The number of columns is not so important. Analyze the query and the where clauses there to decide how menu indexes you need.

You must be careful when writing queries or you may unintentional suppress (turn off) an index that you may have intended on using.  Any function that modifies the column name in a WHERE clause will suppress the corresponding index.  In Oracle8I, there are function-based indexes that allow indexes to be built on functions like UPPER, SUBSTR and DECODE    Many common functions that are used to suppress a standard index are listed below
·     NOT / IS NULL / !=  or <>
·     Comparing a number field to a character field
·     Any modification to the Indexed Column Name
·     (TO_CHAR, TO_DATE, +0,   || '',  SUBSTR, DECODE...)

Suppression Example; despite the intended hint to use the index, the SUBSTR function will suppress the index on the CUST_NO column below:
select      /*+ index(customer custidx) */  CUST_NO, ZIP_CODE
from        CUSTOMER
where       SUBSTR(CUST_NO,1,4) = '2502';
Execution Time - 280 seconds

The SUBSTR function was re-written with a LIKE instead and part of the index is used and the performance is substantially increased:
select      CUST_NO, ZIP_CODE
from        CUSTOMER
where       CUST_NO LIKE '2502%';
Execution Time - 3 seconds

Tip: Prior to Oracle 8.1, if a column is modified in anyway in the WHERE clause, the index on the column will not be used (it will be internally suppressed).


Comparing wrong datatypes
If you compare the wrong datatypes, your index may be suppressed internally.  This is because Oracle will re-write the query so that the comparison is correct.  This problem is at times difficult to track down.
Comparing Characters to Numbers:
where char_data  = 123
could be rewritten to:
where To_Number(char_data) = 123

Comparing Numbers to Characters:
where num_data = ‘123’
could be rewritten lik:e
where To_Char(num_data) = ‘123’


Tip: Comparing mismatched datatypes could cause an internal index suppression that is difficult to track down.  Oracle will often place a function on the column that fixes the mismatch, but suppresses the index.




Here are the limits on indexes:

# of indexes per table - unlimited
# of columns per index - 32 (30 if bitmapped index)

Total size of all indexed columns cannot be more than 40% of database block size.  So, the limit could be less than 32 columns if you have large columns you are indexing.
 
If you're going to index all the columns, you may want to consider an INDEX ORGANIZED TABLE - this is a normal table except that the table itself does not physically exist - index(es) are created to hold all the data for the table. This is done with the ORGANIZATION INDEX clause on the CREATE TABLE statement. It requires a Primary Key.      

50.  What is TNS Stands for?
Transparent Network Substrate

51.  Explain Constraints.

CONSTRAINT clause

PURPOSE:
    To define an integrity constraint.  An integrity constraint is a
    rule that restricts the values for one or more columns in a table.

SYNTAX:

Column constraint:

[CONSTRAINT constraint]
{ [NOT] NULL
| {UNIQUE | PRIMARY KEY}
|  REFERENCES [schema.]table [(column)]
        [ON DELETE CASCADE]
|  CHECK (condition) }
{ [ USING INDEX [PCTFREE integer]
                [INITRANS integer] [MAXTRANS integer]
                [TABLESPACE tablespace]
                [STORAGE storage_clause]
                [PARALLEL [integer] | NOPARALLEL] ]
  [ EXCEPTIONS INTO [schema.]table
| DISABLE }

Table constraint:

[CONSTRAINT constraint]
{ {UNIQUE | PRIMARY KEY} (column [,column] ...)
|  FOREIGN KEY (column [,column] ...)
         REFERENCES [schema.]table [(column [,column] ...)]
        [ON DELETE CASCADE]
| CHECK (condition) }
{ [ USING INDEX [PCTFREE integer]
                [INITRANS integer] [MAXTRANS integer]
                [TABLESPACE tablespace]
                [STORAGE storage_clause]
                [PARALLEL [integer] | NOPARALLEL] ]
  [ EXCEPTIONS INTO [schema.]table[@dblink]
| DISABLE }

where:

CONSTRAINT
    identifies the integrity constraint by the name constraint.  Oracle
    stores this name in the data dictionary along with the definition of
    the integrity constraint.  If you omit this identifier, Oracle
    generates a name with this form:
            SYS_Cn
    where
            n
                   is an integer that makes the name unique
                   within the database.

    For the names and definitions of integrity constraints, query the
    data dictionary.

NULL
    specifies that a column can contain null values.

NOT NULL
    specifies that a column cannot contain null values.

    If you do not specify NULL or NOT NULL in a column definition, NULL
    is the default.

UNIQUE
    designates a column or combination of columns as a unique key.

PRIMARY KEY
    designates a column or combination of columns as the table's primary
    key.

FOREIGN KEY
    designates a column or combination of columns as the foreign key in
    a referential integrity constraint.

REFERENCES
    identifies the primary or unique key that is referenced by a foreign
    key in a referential integrity constraint.

ON DELETE CASCADE
    specifies that Oracle maintains referential integrity by
    automatically removing dependent foreign key values if you remove a
    referenced primary or unique key value.
CHECK
    specifies a condition that each row in the table must satisfy.

USING INDEX
    specifies parameters for the index Oracle uses to enforce a UNIQUE
    or PRIMARY KEY constraint.  The name of the index is the same as the
    name of the constraint.  You can choose the values of the INITRANS,
    MAXTRANS, TABLESPACE, STORAGE, and PCTFREE parameters for the index.
    For information on these parameters, see the CREATE TABLE command.

    Only use this clause when enabling UNIQUE and PRIMARY KEY
    constraints.

  PARALLEL
     specifies the number of processes that create the index in parallel.
     You can only specify positive integer values greater than 1.  If you
     do not specify an integer, the degree of parallelism is based on
     the parallelism specified in the table's definition.

  NOPARALLEL
     specifies that the index should not be created in parallel.

EXCEPTIONS INTO
    identifies a table into which Oracle places information about rows
    that violate an enabled integrity constraint.  This table must exist
    before you use this option.  If you omit schema, Oracle assumes the
    exception table is in your own schema.  The exception table must be
    on your local database.

DISABLE
    disables the integrity constraint.  If an integrity constraint is
    disabled, Oracle does not enforce it.

    If you do not specify this option, Oracle automatically enables the
    integrity constraint.

    You can also enable and disable integrity constraints with the
    ENABLE and DISABLE clauses of the CREATE TABLE and ALTER TABLE
    commands.

PREREQUISITES:
    CONSTRAINT clauses can appear in either CREATE TABLE or ALTER TABLE
    commands.  To define an integrity constraint, you must have the
    privileges necessary to issue one of these commands.  See the CREATE
    TABLE and ALTER TABLE commands.

    Defining a constraint may also require additional privileges or
    preconditions that depend on the type of constraint.


52.  PL/SQL Tables


  PL/SQL Tables (PL/SQL)
                                                =============

Objects of type TABLE are called "PL/SQL tables," which are modelled
on (but not the same as) database tables. PL/SQL tables use a primary
key to give you array-like access to rows. The size of a PL/SQL table
is unconstrained. That is, the number of rows in a PL/SQL table can
increase dynamically.

PL/SQL tables can have one column and a primary key, neither of which
can be named. The column can have any scalar datatype, but the primary
key must have datatype BINARY_INTEGER.


                         Declaring PL/SQL Tables
                         -----------------------

PL/SQL tables must be declared in two steps. First, you define a TABLE
type, then declare PL/SQL tables of that type. You can declare TABLE
types in the declarative part of any block, subprogram, or package
using the syntax

   TYPE type_name IS TABLE OF
      { column_type | variable%TYPE | table.column%TYPE } [NOT NULL]
      INDEX BY BINARY_INTEGER;

where "type_name" is a type specifier used in subsequent declarations
of PL/SQL tables and "column_type" is any scalar datatype such as CHAR,
DATE, or NUMBER. You can use the %TYPE attribute to specify a column
datatype.

In this example, you declare a TABLE type called "EnameTabTyp":

   DECLARE
      TYPE EnameTabTyp IS TABLE OF CHAR(10)
         INDEX BY BINARY_INTEGER;

You could have used %TYPE to provide the column datatype, as follows:

   DECLARE
      TYPE EnameTabTyp IS TABLE OF emp.ename%TYPE
         INDEX BY BINARY_INTEGER;

Once you define type "EnameTabTyp," you can declare PL/SQL tables of
that type, as follows:

   ename_tab  EnameTabTyp;

The identifier "ename_tab" represents an entire PL/SQL table.

Like scalar variables, PL/SQL tables can be declared as the formal
parameters of procedures and functions. Some packaged examples follow:

   PACKAGE emp_actions IS
      TYPE EnameTabTyp IS TABLE OF emp.ename%TYPE
      INDEX BY BINARY_INTEGER;
      TYPE SalTabTyp IS TABLE OF emp.sal%TYPE
         INDEX BY BINARY_INTEGER;
      ename_tab EnameTabTyp;
      sal_tab   SalTabTyp;
      ...
      PROCEDURE hire_batch
         (ename_tab EnameTabTyp,
          sal_tab   SalTabTyp,
          ...);
      PROCEDURE log_names
         (ename_tab EnameTabTyp,
          num       BINARY_INTEGER);
      ...
   END emp_actions;


                         Referencing PL/SQL Tables
                         -------------------------

To reference rows in a PL/SQL table, you specify a primary key value
using the array-like syntax

   plsql_table_name(primary_key_value)

where "primary_key_value" has datatype BINARY_INTEGER. For example,
you reference the third row in PL/SQL table "ename_tab" as follows:

   ename_tab(3) ...

You can assign the value of a PL/SQL expression to a specific row using
the following syntax:

   plsql_table_name(primary_key_value) := plsql_expression;

In the next example, you assign the sum of variables "salary" and
"increase" to the fifth row in PL/SQL table "sal_tab":

   sal_tab(5) := salary + increase;

Until a row is assigned a value, it does not exist. If you try to
reference an uninitialized row, PL/SQL raises the predefined exception
NO_DATA_FOUND. Consider the following example:

   DECLARE
      TYPE JobTabTyp IS TABLE OF CHAR(14)
         INDEX BY BINARY_INTEGER;
      job_tab JobTabTyp;
   BEGIN
      job_tab(1) := 'CLERK';
      IF job_tab(2) = 'CLERK' THEN  -- raises NO_DATA_FOUND
         ...
      END IF;
      ...
   EXCEPTION
WHEN NO_DATA_FOUND THEN
         -- here because job_tab(2) does not exist
         ...
   END;


                         Inserting/Fetching Rows
                         -----------------------

You must use a loop to INSERT values from a PL/SQL table into a database
column. Likewise, you must use a loop to FETCH values from a database
column into a PL/SQL table. For example, given the declarations

   DECLARE
      TYPE EmpnoTabTyp IS TABLE OF NUMBER(4)
         INDEX BY BINARY_INTEGER;
      TYPE EnameTabTyp IS TABLE OF CHAR(10)
         INDEX BY BINARY_INTEGER;
      ...
      empno_tab EmpnoTabTyp;
      ename_tab EnameTabTyp;

you might use the following procedure to INSERT values from the PL/SQL
tables into the "emp" database table:

   PROCEDURE insert_emp_data
      (rows      BINARY_INTEGER,
       empno_tab EmpnoTabTyp,
       ename_tab EnameTabTyp,
...) IS
   BEGIN
      FOR i IN 1..rows LOOP
         INSERT INTO emp (empno, ename, ...)
            VALUES (empno_tab(i), ename_tab(i), ...);
      END LOOP;
   END;

Conversely, you might use the next procedure to FETCH all rows from the
database table into PL/SQL tables "empno_tab" and "ename_tab":

   PROCEDURE fetch_emp_data
      (rows      OUT BINARY_INTEGER,
       empno_tab OUT EmpnoTabTyp,
       ename_tab OUT EnameTabTyp,
       ...) IS
   BEGIN
      rows := 0;
      FOR emprec IN (SELECT * FROM emp) LOOP
         rows := rows + 1;
         empno_tab(rows) := emprec.empno;
         ename_tab(rows) := emprec.ename;
         ...
      END LOOP;
   END;
However, you cannot reference PL/SQL tables in the INTO clause. For
example, the following SELECT statement is illegal:

   PROCEDURE fetch_emp_data
      (rows      OUT BINARY_INTEGER,
       empno_tab OUT EmpnoTabTyp,
       ename_tab OUT EnameTabTyp,
       ...) IS
   BEGIN
      SELECT empno, ename
         INTO empno_tab, ename_tab  -- illegal
         FROM emp;
      ...
   END;


                         Deleting Rows
                         -------------

The DELETE statement cannot specify PL/SQL tables, so you cannot
delete individual rows from a PL/SQL table. Assigning NULL to a row
as follows does not work because the row remains:

   sal_tab(3) := NULL;

However, you can use a simple workaround to delete an entire PL/SQL
table. Simply assign NULL to it, as shown in the following example:
DECLARE
      TYPE NumTabTyp IS TABLE OF NUMBER
         INDEX BY BINARY_INTEGER;
      sal_tab NumTabTyp;
   BEGIN
      /* Load salary table. */
      FOR i IN 1..50 LOOP
         sal_tab(i) := i;
      END LOOP;
      ...
      sal_tab := NULL;  -- deletes sal_tab table
      IF sal_tab(3) ...  -- raises NO_DATA_FOUND


53. Explain Cursors in PL/SQL

1.9 Cursors in PL/SQL

Every SQL statement executed by the RDBMS has a private SQL area that contains information about the SQL statement and the set of data returned. In PL/SQL, a cursor is a name assigned to a specific private SQL area for a specific SQL statement. There can be either static cursors, whose SQL statement is determined at compile time, or dynamic cursors, whose SQL statement is determined at runtime. Static cursors are covered in greater detail in this section. Dynamic cursors in PL/SQL are implemented via the built-in package DBMS_SQL. See the book Oracle Built-in Packages and the corresponding Oracle PL/SQL Built-ins Pocket Reference, both from O'Reilly & Associates, for full coverage on DBMS_SQL and the other built-in packages.

1.9.1 Explicit Cursors

Explicit cursors are SELECT statements that are DECLAREd explicitly in the declaration section of the current block or in a package specification. Use OPEN, FETCH, and CLOSE in the execution or exception sections of your programs.

1.9.1.1 Declaring explicit cursors

To use an explicit cursor, you must first declare it in the declaration section of a block or package. There are three types of explicit cursor declarations:
·         A cursor without parameters, such as:
      CURSOR company_cur 
         IS
         SELECT company_id FROM company;
·         A cursor that accepts arguments through a parameter list:
      CURSOR company_cur (id_in IN NUMBER) IS
      SELECT name FROM company
      WHERE  company_id = id_in;
·         A cursor header that contains a RETURN clause in place of the SELECT statement:
      CURSOR company_cur (id_in IN NUMBER) 
      RETURN company%ROWTYPE IS
      SELECT * FROM company;
This technique can be used in packages to hide the implementation of the cursor in the package body. See the Section 1.14" section for more information.

1.9.1.2 Opening explicit cursors

To open a cursor, use the following syntax:
OPEN cursor_name [(argument [,argument ...])];
where cursor_name is the name of the cursor as declared in the declaration section. The arguments are required if the definition of the cursor contains a parameter list.
You must open an explicit cursor before you can fetch rows from that cursor. When the cursor is opened, the processing includes the PARSE, BIND, OPEN, and EXECUTE statements. This OPEN processing includes: determining an execution plan, associating host variables and cursor parameters with the placeholders in the SQL statement, determining the result set, and, finally, setting the current row pointer to the first row in the result set.
When using a cursor FOR loop, the OPEN is implicit in the FOR statement. If you try to open a cursor that is already open, PL/SQL will raise an "ORA-06511: PL/SQL: cursor already open" exception.

1.9.1.3 Fetching from explicit cursors

The FETCH statement places the contents of the current row into local variables. To retrieve all rows in a result set, each row needs to be fetched. The syntax for a FETCH statement is:
FETCH cursor_name INTO record_or_variable_list;
where cursor_name is the name of the cursor as declared and opened.

1.9.1.4 Closing explicit cursors

The syntax of the CLOSE statement is:
CLOSE cursor_name;
where cursor_name is the name of the cursor declared and opened.
After all rows have been fetched, a cursor needs to be closed. Closing a cursor releases the private SQL area used by the cursor, freeing the memory used by that cursor.
If you declare a cursor in a local anonymous, procedure, or function block, that cursor will automatically close when the block terminates. Package-based cursors must be closed explicitly, or they stay open for the duration of your session. Closing a cursor that is not open raises an INVALID CURSOR exception.

1.9.1.5 Explicit cursor attributes

There are four attributes associated with cursors: ISOPEN, FOUND, NOTFOUND, and ROWCOUNT. These attributes can be accessed with the % delimiter to obtain information about the state of the cursor. The syntax for a cursor attribute is:
cursor_name%attribute
where cursor_name is the name of the explicit cursor.
The behaviors of the explicit cursor attributes are described in the following table.
Attribute
Description
%ISOPEN
TRUE if cursor is open.
FALSE if cursor is not open.
%FOUND
INVALID_CURSOR is raised if cursor has not been OPENed.
NULL before the first fetch.
TRUE if record was fetched successfully.
FALSE if no row was returned.
INVALID_CURSOR if cursor has been CLOSEd.
%NOTFOUND
INVALID_CURSOR is raised if cursor has not been OPENed.
NULL before the first fetch.
FALSE if record was fetched successfully.
TRUE if no row was returned.
INVALID_CURSOR if cursor has been CLOSEd.
%ROWCOUNT
INVALID_CURSOR is raised if cursor has not been OPENed.
The number of rows fetched from the cursor.
INVALID_CURSOR if cursor has been CLOSEd.
Frequently a cursor attribute is checked as part of a WHILE loop that fetches rows from a cursor:
DECLARE
   caller_rec caller_pkg.caller_cur%ROWTYPE;
BEGIN
   OPEN caller_pkg.caller_cur;
   LOOP
      FETCH caller_pkg.caller_cur into caller_rec;
      EXIT WHEN caller_pkg.caller_cur%NOTFOUND
               OR 
               caller_pkg.caller_cur%ROWCOUNT > 10;
 
      UPDATE call
         SET caller_id = caller_rec.caller_id
         WHERE call_timestamp < SYSDATE;
   END LOOP;
   CLOSE caller_pkg.caller_cur;
END;

1.9.2 Implicit Cursors

Whenever a SQL statement is directly in the execution or exception section of a PL/SQL block, you are working with implicit cursors. These statements include INSERT, UPDATE, DELETE, and SELECT INTO statements. Unlike explicit cursors, implicit cursors do not need to be declared, OPENed, FETCHed, or CLOSEd.
SELECT statements handle the %FOUND and %NOTFOUND attributes differently from explicit cursors. When an implicit SELECT statement does not return any rows, PL/SQL immediately raises the NO_DATA_FOUND exception and control passes to the exception section. When an implicit SELECT returns more than one row, PL/SQL immediately raises the TOO_MANY_ROWS exception and control passes to the exception section.
Implicit cursor attributes are referenced via the SQL cursor. For example:
BEGIN
   UPDATE activity SET last_accessed := SYSDATE
   WHERE UID = user_id;
 
   IF SQL%NOTFOUND THEN
      INSERT INTO activity_log (uid,last_accessed)
      VALUES (user_id,SYSDATE);
   END IF
END;
SQL Attributes
Description
%ISOPEN
Always FALSE since the cursor is opened implicitly and closed immediately after the statement is executed.
%FOUND
NULL before the statement.
TRUE if one or more rows were inserted, updated, or deleted or if only one row was selected.
FALSE if no row was selected, updated, inserted, or deleted.
%NOTFOUND
NULL before the statement.
TRUE if no row was selected, updated, inserted, or deleted.
FALSE if one or more rows were inserted, updated, or deleted.
%ROWCOUNT
The number of rows affected by the cursor.
%BULK_ROWCOUNT (Oracle8i)
A pseudo index-by table containing the numbers of rows affected by the statements executed in bulk bind operations. See the "Bulk Binds (Oracle8i)" section for more information on %BULK_ROWCOUNT.
Use the RETURNING clause in INSERT, UPDATE, and DELETE statements to obtain data modified by the associated DML statement. This clause allows you to avoid an additional SELECT statement to query the results of the DML statement. For example:
BEGIN
   UPDATE activity SET last_accessed := SYSDATE
   WHERE UID = user_id
   RETURNING last_accessed, cost_center 
   INTO timestamp, chargeback_acct;

1.9.2.1 The SELECT FOR UPDATE clause

By default, the Oracle RDBMS locks rows as they are changed. To lock all rows in a result set, use the FOR UPDATE clause in your SELECT statement when you OPEN the cursor, instead of when you change the data. Using the FOR UPDATE clause does not require you to actually make changes to the data; it only locks the rows when opening the cursor. These locks are released on the next COMMIT or ROLLBACK. As always, these row locks do not affect other SELECT statements unless they, too, are FOR UPDATE. The FOR UPDATE clause is appended to the end of the SELECT statement and has the following syntax:
SELECT ...
  FROM ...
   FOR UPDATE [OF column_reference] [NOWAIT];
where column_reference is a comma-delimited list of columns that appear in the SELECT clause. The NOWAIT keyword tells the RDBMS to not wait for other blocking locks to be released. The default is to wait forever.
In the following example, only columns from the inventory (pet) table are referenced FOR UPDATE, so no rows in the dog_breeds (dog) table are locked when hounds_in_stock_cur is opened:
DECLARE
   CURSOR hounds_in_stock_cur IS
      SELECT pet.stock_no, pet.breeder, dog.size
        FROM dog_breeds dog ,inventory pet
       WHERE dog.breed = pet.breed
         AND dog.class = 'HOUND'
         FOR UPDATE OF pet.stock_no, pet.breeder;
BEGIN

1.9.2.2 The WHERE CURRENT OF clause

UPDATE and DELETE statements can use a WHERE CURRENT OF clause if they reference a cursor declared FOR UPDATE. This syntax indicates that the UPDATE or DELETE should modify the current row identified by the FOR UPDATE cursor. The syntax is:
[UPDATE | DELETE ] ...
   WHERE CURRENT OF cursor_name;
By using WHERE CURRENT OF, you do not have to repeat the WHERE clause in the SELECT statement. For example:
DECLARE
   CURSOR wip_cur IS
      SELECT acct_no, enter_date FROM wip
       WHERE enter_date < SYSDATE -7
         FOR UPDATE;
BEGIN
   FOR wip_rec IN wip_cur
   LOOP
      INSERT INTO acct_log (acct_no, order_date)
         VALUES (wip_rec.acct_no, wip_rec.enter_
            date);
      DELETE FROM wip 
         WHERE CURRENT OF wip_cur;
   END LOOP;
END; 

1.9.3 Cursor Variables

A cursor variable is a data structure that points to a cursor object, which in turn points to the cursor's result set. You can use cursor variables to more easily retrieve rows in a result set from client and server programs. You can also use cursor variables to hide minor variations in queries.
The syntax for a REF_CURSOR type is:
TYPE ref_cursor_name IS REF CURSOR 
   [RETURN record_type];
If you do not include a RETURN clause, then you are declaring a weak REF CURSOR. Cursor variables declared from weak REF CURSORs can be associated with any query at runtime. A REF CURSOR declaration with a RETURN clause defines a "strong" REF CURSOR. A cursor variable based on a strong REF CURSOR can be associated with queries whose result sets match the number and datatype of the record structure after the RETURN at runtime.
To use cursor variables, you must first create a REF_CURSOR type, then declare a cursor variable based on that type.
The following example shows the use of both weak and strong REF CURSORs:
DECLARE
   -- Create a cursor type based on the companies   
      table.
   TYPE company_curtype IS REF CURSOR 
      RETURN companies%ROWTYPE;
 
   -- Create the variable based on the REF CURSOR.
   company_cur company_curtype;
 
   -- And now the weak, general approach.
   TYPE any_curtype IS REF CURSOR;
   generic_curvar any_curtype;
The syntax to OPEN a cursor variable is:
OPEN cursor_name FOR select_statement;
FETCH and CLOSE a cursor variable using the same syntax as for explicit cursors. There are a number of restrictions on cursor variables:
·         Cursor variables cannot be declared in a package since they do not have a persistent state.
·         You cannot use the FOR UPDATE clause with cursor variables.
·         You cannot assign NULLs to a cursor variable nor use comparison operators to test for equality, inequality, or nullity.
·         Neither database columns nor collections can store cursor variables.
·         You cannot use RPCs to pass cursor variables from one server to another.
·         Cursor variables cannot be used with the dynamic SQL built-in package DBMS_SQL.
54. What is a cluster table?

            A cluster is a schema object that contains one or more tables that all have one or more columns in common. Rows of one or more tables that share the same value in these common columns are physically stored together within the database.
 
Generally, you should only cluster tables that are frequently joined on the cluster key columns in SQL statements. Clustering multiple tables improves the performance of joins, but it is likely to reduce the performance of full table scans, INSERT statements, and UPDATE statements that modify cluster key values. Before clustering, consider its
 
Cluster Keys
The columns defined by the CREATE CLUSTER command make up the cluster key. These cluster columns must correspond in both datatype and size to columns in each of the clustered tables, although they need not correspond in name.
 
Types of Clusters
A cluster can be either an indexed cluster or a hash cluster.
 
Indexed Clusters
In an indexed cluster, Oracle stores together rows having the same cluster key value. Each distinct cluster key value is stored only once in each data block, regardless of the number of tables and rows in which it occurs. This saves disk space and improves performance for many operations.
You may want to use indexed clusters in the following cases:
·        Your queries retrieve rows over a range of cluster key values.
·        Your clustered tables may grow unpredictably.
After you create an indexed cluster, you must create an index on the cluster key before you can issue any data manipulation language (DML) statements against a table in the cluster. This index is called the cluster index.
A cluster index provides quick access to rows within a cluster based on the cluster key. If you issue a SQL statement that searches for a row in the cluster based on its cluster key value, Oracle searches the cluster index for the cluster key value and then locates the row in the cluster based on its ROWID.
 
Hash Clusters
In a hash cluster, Oracle stores together rows that have the same hash key value. The hash value for a row is the value returned by the cluster's hash function. When you create a hash cluster, you can either specify a hash function or use the Oracle internal hash function. Hash values are not actually stored in the cluster, although cluster key values are stored for every row in the cluster.
You may want to use hash clusters in the following cases:
·        Your queries retrieve rows based on equality conditions involving all cluster key columns.
·        Your clustered tables are static or you can determine the maximum number of rows and the maximum amount of space required by the cluster when you create the cluster.
The hash function provides access to rows in the table based on the cluster key value. If you issue a SQL statement that locates a row in the cluster based on its cluster key value, Oracle applies the hash function to the given cluster key value and uses the resulting hash value to locate the matching rows. Because multiple cluster key values can map to the same hash value, Oracle must also check the row's cluster key value. This process often results in less I/O than the process for the indexed cluster, because the index search is not required.
Oracle's internal hash function returns values ranging from 0 to the value of HASHKEYS - 1. If you specify a column with the HASH IS clause, the column values need not fall into this range. Oracle divides the column value by the HASHKEYS value and uses the remainder as the hash value. The hash value for null is HASHKEYS - 1. Oracle also rounds the HASHKEYS value up to the nearest prime number to obtain the actual number of hash values. This rounding reduces the likelihood of hash collisions, or multiple cluster key values having the same hash value.
You cannot create a cluster index for a hash cluster, and you need not create an index on a hash cluster key.
If you cannot fit all rows for one hash value into a data block, do not use hash clusters. Performance is very poor in this circumstance because an insert or update of a row in a hash cluster with a size exceeding the data block size fills the block and performs row chaining to contain the rest of the row.
 
Cluster Size
Oracle uses the value of the SIZE parameter to determine the space reserved for rows corresponding to one cluster key value or one hash value. This space then determines the maximum number of cluster or hash values stored in a data block. If the SIZE value is not a divisor of the data block size, Oracle uses the next largest divisor. If the SIZE value is larger than the data block size, Oracle uses the operating system block size, reserving at least one data block per cluster or hash value.
Oracle also considers the length of the cluster key when determining how much space to reserve for the rows having a cluster key value. Larger cluster keys require larger sizes. To see the actual size, query the KEY_SIZE column of the USER_CLUSTERS data dictionary view. This does not apply to hash clusters because hash values are not actually stored in the cluster.
Although the maximum number of cluster and hash key values per data block is fixed on a per `-cluster basis, Oracle does not reserve an equal amount of space for each cluster or hash key value. Varying this space stores data more efficiently, because the data stored per cluster or hash key value is rarely fixed.
A SIZE value smaller than the space needed by the average cluster or hash key value may require the data for one cluster key or hash key value to occupy multiple data blocks. A SIZE value much larger results in wasted space.
When you create a hash cluster, Oracle immediately allocates space for the cluster based on the values of the SIZE and HASHKEYS parameters.
 
Adding Tables to a Cluster
You can add tables to an existing cluster by issuing a CREATE TABLE statement with the CLUSTER clause. A cluster can contain as many as 32 tables, although the performance gains of clustering are often lost in clusters of more than four or five tables.
 
All tables in the cluster have the cluster's storage characteristics as specified by the PCTUSED, PCTFREE, INITRANS, MAXTRANS, TABLESPACE, and STORAGE parameters.
 
Example I
The following statement creates an indexed cluster named PERSONNEL with the cluster key column DEPARTMENT_NUMBER, a cluster size of 512 bytes, and storage parameter values:
CREATE CLUSTER personnel
    ( department_number  NUMBER(2) )
    SIZE 512
    STORAGE (INITIAL 100K NEXT 50K PCTINCREASE 10);
 
The following statements add the EMP and DEPT tables to the cluster:
CREATE TABLE emp
    (empno     NUMBER        PRIMARY KEY,
     ename     VARCHAR2(10)  NOT NULL
                             CHECK (ename = UPPER(ename)),
     job       VARCHAR2(9),
     mgr       NUMBER        REFERENCES scott.emp(empno),
     hiredate  DATE          CHECK (hiredate >= SYSDATE),
     sal       NUMBER(10,2)  CHECK (sal > 500),
     comm      NUMBER(9,0)   DEFAULT NULL,
     deptno   NUMBER(2)      NOT NULL )
     CLUSTER personnel (deptno);
 
CREATE TABLE dept
    (deptno  NUMBER(2),
     dname   VARCHAR2(9),
     loc     VARCHAR2(9))
     CLUSTER personnel (deptno);
 
The following statement creates the cluster index on the cluster key of PERSONNEL:
CREATE INDEX idx_personnel ON CLUSTER personnel;
 
After creating the cluster index, you can insert rows into either the EMP or DEPT tables.
 
Example II
The following statement creates a hash cluster named PERSONNEL with the cluster key column DEPARTMENT_NUMBER, a maximum of 503 hash key values, each of size 512 bytes, and storage parameter values:
CREATE CLUSTER personnel
( department_number  NUMBER )
SIZE 512  HASHKEYS 500
    STORAGE (INITIAL 100K  NEXT 50K  PCTINCREASE 10);
Because the above statement omits the HASH IS clause, Oracle uses the internal hash function for the cluster.
 
Example III
The following statement creates a hash cluster named PERSONNEL with the cluster key made up of the columns HOME_AREA_CODE and HOME_PREFIX, and uses a SQL expression containing these columns for the hash function:
CREATE CLUSTER personnel
    ( home_area_code  NUMBER,
    home_prefix     NUMBER )
    HASHKEYS 20
    HASH IS MOD(home_area_code + home_prefix, 101);

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect