orabase

Dense Rank -Vs- Rank -Vs- Row_Number

2011-09-19T11:30:00.000-07:00

Rank => Rank with Gaps (Eg: 1,2,2,4,5)

Dense Rank => Rank without Gaps (Eg: 1,2,2,3,4)

Row_Number => Ordering of rows; no consideration of equal ranks (Eg: 1,2,3,4,5)

Rank over (ORDER BY clause)

Dense_rank over (ORDER BY clause)

Row_number over (ORDER BY clause)

ORDER BY clause can also be replaced by "PARTITION BY x ORDER BY y"

For details see the example here

http://www.adp-gmbh.ch/ora/sql/analytical/dense_rank_vs_rank_vs_row_number.html

Analytic Function: Lag

2009-01-27T14:33:00.000-08:00

Lag: Analytic function to retrieve a value from the previous row.

Usage:


Lag([column]) OVER (PARTITION BY [partition_column] ORDER BY [ordering_column])

column - column to be pulled in from the previous row
ordering_column - column by which a partition will be ordered
partition_column - column to be used to partition data. On first thought it might seem that this column can as well be included in the ordering_column list. On careful inspection one may notice that doing so can result in incorrect data. Suppose the requirement is not just to pull data from any previous row; rather only rows belonging to the same class as the current row; in such a case including the partition_column under ordering_column will produce an incorrect result.

Eg: See below example: Fetching transaction amounts for customers over a two months: Here the data needs to be partitioned by the customer and then the previous row may be pulled in.


CREATE TABLE tmp828cust(month date, custno number, cust_name varchar2(50))
CREATE TABLE tmp828transact(month date, transactno number, custno number, amount number)

Required output:
CUSTNO CUST_NAME  TOTAL-MONTH1  TOTAL-MONTH2
    1        A         5000          5500
    2        B         1000           500
    3        C         2000          1800

Inefficient Solution


SELECT t1.custno,
       t1.cust_name,
       t1.tot,
       t2.tot
  FROM
(
SELECT c.custno,
       c.cust_name,
       Sum(t.amount) tot
  FROM tmp828cust c,
       tmp828transact t
 WHERE c.month = t.month
   AND c.custno = t.custno
   AND t.MONTH = '01-JAN-2008'
 GROUP BY c.custno, c.cust_name
)t1,
(
SELECT c.custno,
       c.cust_name,
       Sum(t.amount) tot
  FROM tmp828cust c,
       tmp828transact t
 WHERE c.month = t.month
   AND c.custno = t.custno
   AND t.MONTH = '01-FEB-2008'
 GROUP BY c.custno, c.cust_name
)t2
WHERE t1.custno = t2.custno

Better Solution


SELECT *
  FROM (SELECT custno,
               cust_name,
               month,
               amt,
               Lag(amt) OVER (PARTITION BY custno ORDER BY month) last_amt
         FROM (SELECT t.month,
                      c.custno,
                      c.cust_name,
                      Sum(t.amount) amt
                 FROM tmp828cust c,
                      tmp828transact t
                WHERE c.month = t.month
                  AND c.custno = t.custno
                GROUP BY t.month,
                         c.custno,
                         c.cust_name
              )
        )
 WHERE last_amt IS NOT NULL

Notice the usage of Lag function here
Lag(amt) OVER (PARTITION BY custno ORDER BY month) : Get the amt column from the previous row, rows should be Partitioned by custno and Ordered by month

References:
1. http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1396477600346947904
2. http://www.adp-gmbh.ch/ora/sql/analytical/lag.html

Oracle AQ

2007-10-23T16:49:00.000-07:00

Here's a good article on Oracle Advanced Queues (AQ). The article talks about the basics of a queue and goes on to explain how this is implemented in Oracle's AQ. There's also a basic explanation about Oracle's JMS interface to AQ.

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

Result_Cache in Oracle 11g

2007-08-23T21:39:00.000-07:00

One interesting feature in Oracle 11g database is result caching. To reduce disk I/O, oracle already caches data blocks. With result caching, this is taken one step ahead - results of a query are cached in memory so that a repeat evaluation is not required. So if you have a common SQL being executed in your application, Oracle 11g does the caching for you. And this caching does not compromise on data integrity - whenever a DML operation is performed on a dependant table, the result cache is invalidated.

As Tom says, Result_Cache is more like a Just-In-Time Materialized View - an MV which does not require DBA intervention

For examples usages of Result_Cache click here.

Why are COMMIT times generally flat?

2007-07-02T12:26:00.000-07:00

Contrary to intuitive reasoning, COMMIT is an operation which is not dependant on the size of the transaction (a more or less flat CommitTime-Vs-Volume chart). The reason behind this is that even before the COMMIT is issued, most of the volume dependant operations have been completed (Modifying data blocks in SGA, undo block generation, redo information generation etc). When a COMMIT is issued, the following operations are performed
1. Generate an SCN for the transaction
2. Write remaining buffered redo log entries to disk and record the SCN (lengthiest operation).
3. Release any locks that were acquired as part of the transaction.

Hence COMMIT time is not affected by the size of the transaction. Now thats why wise men say - Never issue COMMITs in a loop (of course, unless each iteration is required to be an atomic transactions in itself!).

Join Types - NestedLoop, HashJoin, SortMerge

2007-06-01T21:53:00.000-07:00

Nested Loop Join
============
Outer Loop : Row source 1 is scanned (outer /driving table)
Inner Loop : Each row returned drives a lookup in row source 2 (inner)
Joining rows are then returned
Cost: Read driving table and access on inner table.
Performance is very dependent on index on inner table

Optimizer uses nested loop when we are joining tables containing small number of rows with an efficient driving condition. It is important to have an index on column of inner join table as this table is probed every time for a new value from outer table.

Hash Join
=======
In theory - the most efficient joint method.
Build Phase: The smaller row source is used to build a hash table and a bitmap
Probe Phase: The second row source is hashed and checked against the hash table
The bitmap is used as a quick lookup to check if rows are in the hash table.
It requires single pass for each row source , and more efficient than sorting and merging
Optimizer uses has join while joining big tables or big fraction of small tables.

SortMerge Join
===========
Sort Phase : Rows from source 1 are sorted; Rows from source 2 are then sorted by the same sort key
Merge Phase : Sorted rows from both sides are then merged
Cost : sorting, reading tables, I/O for temporary segments
Optmizer choose SM Join in following cases
a) When the join condition is an inequality condition (like <, <=, >=). This is because hash join cannot be used for inequality conditions and if the data set is large, nested loop is definitely not an option.
b) If sorting is anyways required due to some other attribute (other than join) like “order by”, optimizer prefers sort merge join over hash join as it is cheaper.

More Info:
www.oraclenotes.com/Articles/Optimizer%20and%20CBO.ppt
http://oracle-online-help.blogspot.com/2007/03/nested-loops-hash-join-and-sort-merge.html

LSNRCTL

2007-03-30T23:35:00.000-07:00

LSNRCTL is a utility to manage Oracle's listener processes.

What does the listener do?
The Oracle listener process is required for database applications to access the database through SQL*Net or Net8.
* listens for requests on a specific port
* spawns or requests a database process/thread
* redirects or passes the connection to the process/thread

Config File for the Listener -> listener.ora

What does listener.org contain?
The listener.ora file resides on the server and defines
a. network listener address
b. SID for the database for which it listens
c. optional parameters for tracing and logging

Partitioned tables/indexes

2007-02-01T00:55:00.000-08:00

Partitioned tables/indexes are useful in VLDBs (Very Large DataBase); partitioned tables/indexes are divided into a number of pieces, or partitions, which have the same logical attributes. For example, all partitions in a table share the same column and constraint definitions, and all partitions in an index share the same index options. Each partition is stored in a separate segment and can have different physical attributes (such as PCTFREE, PCTUSED, INITRANS, MAXTRANS, TABLESPACE, and STORAGE).

Although you are not required to keep each table or index partition in a separate tablespace, it is to your advantage to do so. Storing partitions in separate tablespaces can:

* reduce the possibility of data corruption in multiple partitions
* make it possible to back up and recover each partition independently
* make it possible to control the mapping of partitions to disk drives (important for balancing I/O load)

There are several partitioning methods offered by Oracle:

* Range partitioning
* Hash partitioning
* List partitioning
* Composite range-hash partitioning
* Composite range-list partitioning

1. When creating range partitions, you must specify:

* Partitioning method: range
* Partitioning column(s)
* Partition descriptions identifying partition bounds

The example below creates a table of four partitions, one for each quarter's sales. The columns sale_year, sale_month, and sale_day are the partitioning columns, while their values constitute a specific row's partitioning key. The VALUES LESS THAN clause determines the partition bound: rows with partitioning key values that compare less than the ordered list of values specified by the clause are stored in the partition. Each partition is given a name (sales_q1, sales_q2, ...), and each partition is contained in a separate tablespace (tsa, tsb, ...).


CREATE TABLE sales
   ( invoice_no NUMBER,
     sale_year  INT NOT NULL,
     sale_month INT NOT NULL,
     sale_day   INT NOT NULL )
 PARTITION BY RANGE (sale_year, sale_month, sale_day)
   ( PARTITION sales_q1 VALUES LESS THAN (1999, 04, 01)
       TABLESPACE tsa,
     PARTITION sales_q2 VALUES LESS THAN (1999, 07, 01)
       TABLESPACE tsb,
     PARTITION sales_q3 VALUES LESS THAN (1999, 10, 01)
       TABLESPACE tsc,
     PARTITION sales_q4 VALUES LESS THAN (2000, 01, 01)
       TABLESPACE tsd );

A row with sale_year=1999, sale_month=8, and sale_day=1 has a partitioning key of (1999, 8, 1) and would be stored in partition sales_q3.

2. To create hash partitions you specify the following:

* Partitioning method: hash
* Partitioning columns(s)
* Number of partitions or individual partition descriptions

The following example creates a hash-partitioned table. The partitioning column is id, four partitions are created and assigned system generated names, and they are placed in four named tablespaces (gear1, gear2, ...).


CREATE TABLE scubagear
    (id NUMBER,
     name VARCHAR2 (60))
  PARTITION BY HASH (id)
  PARTITIONS 4
  STORE IN (gear1, gear2, gear3, gear4);

3. When creating list partitions, you must specify:

* Partitioning method: list
* Partitioning column
* Partition descriptions, each specifying a list of literal values (a value list), which are the discrete values of the partitioning column that qualify a row to be included in the partition

The following example creates a list-partitioned table. It creates table q1_sales_by_region which is partitioned by regions consisting of groups of states.


CREATE TABLE q1_sales_by_region
     (deptno number,
      deptname varchar2(20),
      quarterly_sales number(10, 2),
      state varchar2(2))
  PARTITION BY LIST (state)
     (PARTITION q1_northwest VALUES ('OR', 'WA'),
      PARTITION q1_southwest VALUES ('AZ', 'UT', 'NM'),
      PARTITION q1_northeast VALUES  ('NY', 'VM', 'NJ'),
      PARTITION q1_southeast VALUES ('FL', 'GA'),
      PARTITION q1_northcentral VALUES ('SD', 'WI'),
      PARTITION q1_southcentral VALUES ('OK', 'TX'));

Source: http://www.pitt.edu/AFShome/h/o/hoffman/public/html/oradoc/server.804/a58397/ch11.htm

The ROW_NUMBER() Function

2007-01-26T05:44:00.000-08:00

ROW_NUMBER( ) is an Analytic Function which can be used to assign a running serial number to a 'partition' of records.

ROWNUM gives a running count for the entire result set. ROW_NUMBER() on the other hand assigns a running count for partitioned sets within the entire result set.
Eg: Suppose you need to have a partitioned numbering which will assign running serial number to employees in each department.


SELECT empno,
     deptno,
     hiredate,
     ROW_NUMBER( ) OVER (PARTITION BY deptno ORDER BY hiredate NULLS LAST) SRLNO
FROM emp
WHERE deptno IN (10, 20)
ORDER BY deptno, SRLNO;

EMPNO  DEPTNO HIREDATE       SRLNO
------ ------- --------- ----------
7782      10 09-JUN-81          1
7839      10 17-NOV-81          2
7934      10 23-JAN-82          3
7369      20 17-DEC-80          1
7566      20 02-APR-81          2
7902      20 03-DEC-81          3
7788      20 09-DEC-82          4
7876      20 12-JAN-83          5

8 rows selected.

With Excerpts from: http://orafaq.com/node/55

Cursors - Vs - Ref Cursors

2007-01-03T01:31:00.000-08:00

Here are couple of differences between Cursors and Ref Cursors

1. A "normal" plsql cursor is static in defintion. Ref cursors may be dynamically opened or opened based on logic.


Declare
  type rc is ref cursor;
 
  cursor c is select * from dual;

  l_cursor rc;
begin
  if ( to_char(sysdate,'dd') = 30 ) then
    open l_cursor for 'select * from emp';
  elsif ( to_char(sysdate,'dd') = 29 ) then
    open l_cursor for select * from dept;
  else
    open l_cursor for select * from dual;
  end if;
  open c;
end;
/

Given that block of code -- you see perhaps the most "salient" difference -- no
matter how many times you run that block -- cursor C will always be select *
from dual. The ref cursor can be anything.

2. Another difference is a ref cursor can be returned to a client. a plsql "cursor
cursor" cannot be returned to a client. Also, a ref cursor can be passed from subroutine to subroutine -- a cursor cannot be.

3. Another difference is a cursor can be global -- a ref cursor cannot (you cannot
define them OUTSIDE of a procedure / function)

4. Static sql (not using a ref cursor) is much more
efficient then using ref cursors and that use of ref cursors should be limited
to
- returning result sets to clients
- when there is NO other efficient/effective means of achieving the goal

that is, you want to use static SQL (with implicit cursors really) first and use
a ref cursor only when you absolutely have to.

Source: http://asktom.oracle.com/pls/ask/f?p=4950:8:15205724112663115383::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:14188501024541

Audit Trails in Oracle

2006-11-15T18:59:00.000-08:00

Audit trails allow the DBA to dig-up information regarding
* database access
* changes to the database structure
* failed log-on attempts
* attempts to access the database at unusual hours
* users sharing database accounts
* multiple access attempts for different users from the same terminal
* Insert/Update/Delete on tables

1. Audit is turned on for writing to the database by adding the following line to the init.ora file. A symbolic link to it can usually be found in $ORACLE_HOME/dbs


audit_trail = db

The database now needs to be restarted.

2. A simple check will show that audit is indeed now turned on.


SQL> select name,value from v$parameter
 2  where name like 'audit%';

NAME                           VALUE
------------------------------ ------------------------------
audit_trail                    DB
audit_file_dest                ?/rdbms/audit

SQL>

3. No audit actions are captured yet until audit actions are defined; that is, except for privileged access to the database, starting and stopping of the database, and structural changes such as adding a datafile. These are logged to operating system files in $ORACLE_HOME/rdbms/audit unless audit_file_dest is redefined in the init.ora file.

4. To turn on audit for the access attempts to the database:


SQL> audit create session;

Audit succeeded.

SQL>

5. To find out what objects are being audited, query the view dba_obj_audit_opts.

For a user to define audit statements, the privilege "AUDIT SYSTEM" needs to have been granted first. The users that have this privilege can be checked as follows:


SQL> select *
 2  from dba_sys_privs
 3  where privilege like '%AUDIT%';

GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
CTXSYS                         AUDIT ANY                                NO
CTXSYS                         AUDIT SYSTEM                             NO
DBA                            AUDIT ANY                                YES
DBA                            AUDIT SYSTEM                             YES

Source: http://www.securityfocus.com/infocus/1689

When ROWNUM value is actually assigned

2006-10-30T16:09:00.000-08:00

Tom Kyte explains this very well in a recent article on OTN. Excerpts below...

1. ROWNUM value is assigned to a row after it passes the predicate phase of the query but before the query does any sorting or aggregation.
2. A ROWNUM value is incremented only after it is assigned, which is why the following query will never return a row:


select * 
  from t 
 where ROWNUM > 1;

Because ROWNUM > 1 is not true for the first row, ROWNUM does not advance to 2. Hence, no ROWNUM value ever gets to be greater than 1.

For a detailed article on ROWNUM check out http://www.oracle.com/technology/oramag/oracle/06-sep/o56asktom.html?rssid=rss_otn_articles

Statspack

2006-10-02T23:43:00.000-07:00

Statspack is an oracle utility which allows a Datavase Administrator to compare how the DB was at two different points of time. Statspack does this by capturing two snapshots of the DB at two different points in time. When statspack is installed, it creates a PERFSTAT user which will own all statspack related database objects.

To install statspack, run SPCREATE.sql
SQL> @?/rdbms/admin/spcreate

To take a snapshot using statspack
SQL> CONNECT perfstat/my_perfstat_passwordSQL> EXECUTE statspack.snap;

To automate the gathering of statistics

# Use DBMS_JOB procedure to schedule snapshots.

# Use an operating system utility, such as cron on UNIX. On Windows platforms, you can use the at utility on Windows NT or System Tools > Scheduled Tasks on Windows 2000.

Creating a Statspack Report Without Prompts (UNIX)


SQL>  connect perfstat/my_perfstat_password
SQL>  define begin_snap=1
SQL>  define end_snap=2
SQL>  define report_name=batch_run
SQL>  @?/rdbms/admin/spreport

With Excerpts from : http://download-east.oracle.com/docs/cd/B10501_01/server.920/a96533/statspac.htm#34837

Oracle Wait Interface

2006-09-28T00:37:00.000-07:00

A Good Article which explains Oracle's wait interface

http://www.oracle.com/technology/pub/articles/schumacher_10gwait.html

Removing large listener.log files

2006-08-26T04:31:00.000-07:00

Attempts to remove or rename the listener.log file while the TNS listener process is running throw errors, since the listener process would be holding a lock on it.

Two solutions are
1. Stop the TNS listener process, rename (or remove) the file, then restart the TNS listener process.
2. A better solution which does not require the listener process to be stopped.


C:\> lsnrctl set log_status off

Now rename/remove the log file

C:\> lsnrctl set log_status on

With excerpts from http://www.idevelopment.info/data/Oracle/DBA_tips/Networking/NET_3.shtml

Soundex Function

2006-08-20T05:40:00.000-07:00

Given a String, the Soundex function returns a character string which is its Phonetic Representation. This is useful for performing "Sounds Like" comparisons.(See the example below)

The Soundex algorithm is as follows:


    * Retain the first letter of the string and remove all other occurrences of the following letters: a, e, h, i, o, u, w, y
    * Assign numbers to the remaining letters (after the first) as
      follows:
      b, f, p, v = 1
      c, g, j, k, q, s, x, z = 2
      d, t = 3
      l = 4
      m, n = 5
      r = 6
    * If two or more letters with the same number were adjacent in the original name (before step 1), or adjacent except for any intervening h and w, then omit all but the first.
    * Return the first four bytes padded with 0.

The soundex function uses only the first 5 consonants to determine the NUMERIC portion of the return value, except if the first letter of string1 is a vowel. The soundex function is not case-sensitive. ie. both uppercase and lowercase characters will generate the same soundex return value.



Syntax:
SOUNDEX()

Example:
CREATE TABLE test123
 (name VARCHAR2(15));

INSERT INTO test123 VALUES ('Smith');
INSERT INTO test123 VALUES ('Smyth');
INSERT INTO test123 VALUES ('Smythe');
INSERT INTO test123 VALUES ('Smither');
INSERT INTO test123 VALUES ('Smidt');
INSERT INTO test123 VALUES ('Smick');
INSERT INTO test123 VALUES ('Smiff');
COMMIT;

SELECT * FROM test123;

SELECT SOUNDEX(NAME), NAME
FROM test123
WHERE SOUNDEX(name) = SOUNDEX('SMITH');

Source:
http://www.psoug.org/reference/string_func.html
http://www.techonthenet.com/oracle/functions/soundex.php

Loopback Adapters for Oracle Installation in DHCP Networks

2006-08-20T04:10:00.000-07:00

Dynamic Host Configuration Protocol (DHCP) assigns dynamic IP addresses on a network. Dynamic addressing allows a computer to have a different IP address each time it connects to the network. In some cases, the IP address can change while the computer is still connected. You can have a mixture of static and dynamic IP addressing in a DHCP system.

In a DHCP setup, the software tracks IP addresses, which simplifies network administration. This lets you add a new computer to the network without having to manually assign that computer a unique IP address. However, before installing Oracle Database onto a computer that uses the DHCP protocol, you need to install a loopback adapter to assign a local IP address to that computer.

Loopback adapter approach is recommended particularly for laptops (presumably used only for learning purposes!) which connect and disconnect from the real, corporate network. The loopback adapter means that Oracle will function regardless of whether the laptop is connected to the network or not: it gives Oracle a static, always-there, point of reference independent of what shenannigans the real NIC gets up to.

To install a loopback adapter on Windows XP:

1. From the Start menu, select Control Panel.
2. Double-click Add Hardware to start the Add Hardware wizard.
3. On the Welcome screen, click Next.
4. On the Is the hardware connected? screen, select Yes, I have already connected the hardware, and click Next.
5. On the The following hardware is already installed on your computer screen, select Add a new hardware device, and click Next.
6. On the The wizard can help you install other hardware screen, select Install the hardware that I manually select from a list, and click Next.
7. From the list, select the type of hardware you are installing screen, select Network adapters, and click Next.
8. On the Select Network Adapter screen, make the following selections:
* Manufacturer: select Microsoft.
* Network Adapter: select Microsoft Loopback Adapter.
9. Click Next.
10. On the The wizard is ready to install your hardware screen, click Next.
11. On the Completing the Add Hardware Wizard screen, click Finish.
12. If you are using Windows 2003, restart your computer.
13. Right-click My Network Places on the desktop and choose Properties. This displays the Network Connections control panel.
14. Right-click the connection that was just created. This is usually named "Local Area Connection 2". Choose Properties.
15. On the General tab, select Internet Protocol (TCP/IP), and click Properties.
16. In the Properties dialog, do the following:
1. IP Address: Enter a non-routable IP for the loopback adapter. Oracle recommends the following non-routable addresses:
* 192.168.x.x (x is any value between 1 and 255)
* 10.10.10.10
2. Subnet mask: Enter 255.255.255.0.
3. Leave all other fields empty.
4. Click OK.
17. Click OK.
18. Click OK in the Local Area Connection 2 Properties dialog.
19. Restart the computer.
20. Add a line to the C:\windows\system32\drivers\etc\hosts file with the following format, after the localhost line:

IP_address hostname.domainname hostname

where:
* IP_address is the non-routable IP address you entered in step 16.
* hostname is the name of the computer.
* domainname is the name of the domain.

For example:

10.10.10.10 mycomputer.mydomain.com mycomputer

21. Check the network configuration:
1. Open System Properties, and select the Computer Name tab. In Full computer name, make sure you see the hostname and the domain name.
2. Click Change. In Computer name, you should see the hostname, and in Full computer name, you should see the hostname and domain name.
3. Click More. In Primary DNS suffix of this computer, you should see the domain name.

Source:
http://www.dizwell.com/prod/node/71
http://download-west.oracle.com/docs/html/B10130_02/reqs.htm#BABDJJFF

Oracle PFile and SPFile

2006-08-07T12:24:00.000-07:00

In Oracle Databases through 8i, parameters controling memory, processor usage, control file locations and other key parameters are kept in a pfile (short for parameter file).

The pfile is a static, plain text files which can be altered using a text editor, but it is only read at database startup. Any changes to the pfile will not be read until the database is restarted and any changes to a running database will not be written to the pfile.

Due to these limitations, in 9i Oracle introduced the spfile (server parameter file). The spfile cannot be edited by the DBA; instead it is updated by using ALTER SYSTEM commands from within Oracle. This allows parameter changes to be persistent across database restarts, but can leave you in a pinch if you need to change a parameter to get a database started but you need the database running to change the parameter.

A 9i (or later) database can have either a pfile or an spfile, or even both, but how can you tell which you have? If you have both, which one is being used? How do you go from one to the other? How do you get out of the chicken-and-the-egg quandary of a database that will not start up without you changing a parameter that’s in that file you can’t update unless the database is up?

Note: This information is based on an Oracle 9i installation on Solaris. Your mileage may vary. I have also chosen to ignore issues of RAC installation. In my example I have used ORADB as my SID.

Am I using a pfile or an spfile?

The first thing to check is if you have a pfile or spfile. They can be specified at startup or found in the default location. The default path for the pfile is $ORACLE_HOME/dbs/init$ORACLE_SID.ora and the default for the spfile is $ORACLE_HOME/dbs/spfile$ORACLE_SID.ora.

If both a pfile and an spfile exist in their default location and the database is started without a pfile='/path/to/init.ora' then the spfile will be used.

Assuming your database is running you can also check the spfile parameter. Either the command SHOW PARAMETER spfile or SELECT value FROM v$parameter WHERE name='spfile'; will return the path to the spfile if you are using one. If the value of spfile is blank you are not using an spfile.

The path to the spfile will often be represented in the database by ?/dbs/spfile@.ora. This may seem cryptic, but Oracle translates ? to $ORACLE_HOME and @ to $ORACLE_SID so this string translates to the default location of the spfile for this database.

How can I create an spfile from a pfile?

As long as your pfile is in the default locations and you want your spfile in the default location, you can easily create an spfile with the command CREATE SPFILE FROM PFILE;.

If you need to be more specific about the locations you can add paths to the create command like this:


CREATE SPFILE='/u01/app/oracle/product/9.2/dbs/spfileORADB.ora'
FROM PFILE=’/u01/app/oracle/product/9.2/dbs/initORADB.ora’;

These commands should work even when the database is not running! This is important when you want to change a database to use an spfile before you start it.

How can I create a pfile from an spfile?

The commands for creating a pfile are almost identical to those for creating a spfile except you reverse the order of spfile and pfile:

If your pfile is in the default location and you want your spfile created there as well run CREATE SPFILE FROM PFILE;.

If you have, or want them in custom locations specify the paths like this:


CREATE PFILE='/u01/app/oracle/product/9.2/dbs/initORADB.ora'
FROM SPFILE=’/u01/app/oracle/product/9.2/dbs/spfileORADB.ora’;

Again, this can be done without the database running. This is useful when the database fails to start due to a parameter set in the spfile. This is also a good step to integrate into your backup procedures.

How can I see what’s in my spfile

To view the settings in the spfile we have two options: First, we can use the command above to create a pfile from the spfile. This is simple, and fairly fast, but unnecessary if the database is running.

The better way, if the database is running, is to select the parameter you want to view from the oracle view v$spparameter with a command like this:


SELECT value FROM v$spparameter WHERE name='processes';

If you try to view the spfile with a text editor it may seem like it is plain text, but beware! The spfile will not behave correctly (if it works at all) if it has been edited by a text editor.

How can I update values in my spfile?

The values in spfile are updated with the ALTER SYSTEM command, but to update the spfile we add an additional parameter of SCOPE.


ALTER SYSTEM SET processes=50 SCOPE=spfile;

This command would update the parameter processes in the spfile. Since this parameter can only be set at startup, we say SCOPE=spfile and the change will be reflected when the database is restarted. Other options for SCOPE are memory which only changes the parameter until the database is restarted, and both which changes the instance immediately and will remain in effect after the database is restarted.

How can I update values in my spfile when my database won’t start?

So your database won’t startup because of a problem in your spfile. You can’t edit it with a text editor and you can’t use ALTER SYSTEM because your database is not running. It sounds like a problem, but really isn’t. Here’s what you do:

Connect up to your database as sysdba. You should get the message Connected to an idle instance

Run the command CREATE pfile FROM spfile; specifying the location as above if necessary. You should now have a fresh version of the spfile.

Edit the pfile to update the parameter you need to update.

Run the command CREATE spfile FROM pfile; to move the changes you have just made back into the spfile.

Startup the database normally. It should read the changed spfile and start up correctly. You can optionally delete the pfile if you are done.

Source: www.lifeaftercoffee.com/2005/09/29/oracle-pfile-and-spfile-for-parameters

Suspending and Resuming a Database

2006-08-06T13:13:00.000-07:00

The ALTER SYSTEM SUSPEND statement suspends a database by halting all input and output (I/O) to datafiles (file header and file data) and control files, thus allowing a database to be backed up without I/O interference. When the database is suspended all preexisting I/O operations are allowed to complete and any new database accesses are placed in a queued state.

The suspend command suspends the database, and is not specific to an instance. Therefore, in an Oracle Real Application Clusters environment, if the suspend command is entered on one system, then internal locking mechanisms will propagate the halt request across instances, thereby quiescing all active instances in a given cluster. However, do not start a new instance while you suspend another instance, since the new instance will not be suspended.

Use the ALTER SYSTEM RESUME statement to resume normal database operations. You can specify the SUSPEND and RESUME from different instances. For example, if instances 1, 2, and 3 are running, and you issue an ALTER SYSTEM SUSPEND statement from instance 1, then you can issue a RESUME from instance 1, 2, or 3 with the same effect.

The suspend/resume feature is useful in systems that allow you to mirror a disk or file and then split the mirror, providing an alternative backup and restore solution. If you use a system that is unable to split a mirrored disk from an existing database while writes are occurring, then you can use the suspend/resume feature to facilitate the split.

The suspend/resume feature is not a suitable substitute for normal shutdown operations, however, since copies of a suspended database can contain uncommitted updates.

Caution:
Do not use the ALTER SYSTEM SUSPEND statement as a substitute for placing a tablespace in hot backup mode. Precede any database suspend operation by an ALTER TABLESPACE BEGIN BACKUP statement.

The following statements illustrate ALTER SYSTEM SUSPEND/RESUME usage. The V$INSTANCE view is queried to confirm database status.


SQL> ALTER SYSTEM SUSPEND;
System altered
SQL> SELECT DATABASE_STATUS FROM V$INSTANCE;
DATABASE_STATUS
---------
SUSPENDED

SQL> ALTER SYSTEM RESUME;
System altered
SQL> SELECT DATABASE_STATUS FROM V$INSTANCE;
DATABASE_STATUS
---------
ACTIVE

Source: http://www.utexas.edu/its/unix/reference/oracledocs/v92/B10501_01/server.920/a96521/start.htm#9497

Quiescing a Database

2006-08-06T13:05:00.000-07:00

There are times when there is a need to put a database into a state where only DBA transactions, queries, fetches, or PL/SQL statements are allowed. This is called a quiesced state, in the sense that there are no ongoing non-DBA transactions, queries, fetches, or PL/SQL statements in the system. This quiesced state allows you or other administrators to perform actions that cannot safely be done otherwise. These actions are categorized as follows:

* Actions that can fail if concurrent user transactions access the same object. For example, changing the schema of a database table or adding a column to an existing table where a no-wait lock is required.
* Actions whose undesirable intermediate effect can be seen by concurrent user transactions. For example, a multistep procedure for reorganizing a table where the table is first exported, then dropped, and finally imported. A concurrent user who attempted to access the table after it was dropped, but before import, would see disturbing results.

Without the ability to quiesce the database, you would be required to shut down the database and reopen it in restricted mode. This is a serious restriction, especially for systems requiring 24 x 7 availability. Quiescing a database is much less of a restriction because it eliminates the disruption to users and downtime associated with shutting down and restarting the database.
Note:

For this release of Oracle9i, in the quiesce database context a DBA is defined as user SYS or SYSTEM. Other users, including those with the DBA role are not allowed to issue the ALTER SYSTEM QUIESCE DATABASE statement or proceed after the database is quiesced.
Placing a Database into a Quiesced State

To place a database into a quiesced state, issue the following statement:


ALTER SYSTEM QUIESCE RESTRICTED;

Any non-DBA active sessions will proceed until they become inactive. An active session is defined as a session that is currently inside of a transaction, a query, a fetch, or a PL/SQL statement; or a session that is currently holding any shared resources (for example, enqueues). No inactive sessions are allowed to become active. If a user, for example, issues a SQL query in an attempt to force an inactive session to become active, the query will appear to be hung. When the database is later unquiesced, the session is resumed, and the blocked action (for example, the previously mentioned SQL query) will be processed.

Once all non-DBA sessions become inactive, the ALTER SYSTEM QUIESCE RESTRICTED statement finishes, and the database is considered as in a quiesced state. In an Oracle Real Application Clusters environment, this statement affects all instances, not just the one that issues the statement.

The database remains in the quiesced state even if the session that issued the statement exits. A DBA must log in to the database to issue the statement that specifically unquiesces the database.

While in the quiesced state, you cannot use file system copy to backup the database's datafiles as cold backups, even if you do a checkpoint on every instance. The reason for this is that in the quiesced state the file headers of online datafiles continue to look like they are being accessed. They do not look the same as if a clean shutdown were done. Similarly, to perform a hot backup of the datafiles of any online tablespace while the database is in a quiesced state, you are still required to first place the tablespace into backup mode using the ALTER TABLESPACE... BEGIN BACKUP statement.

Restoring the System to Normal Operation

The following statement restores the database to normal operation:


ALTER SYSTEM UNQUIESCE;

All non-DBA activity is allowed to proceed. In an Oracle Real Application Clusters environment, this statement is not required to be issued from the same session, or even the same instance, as that which imposed the quiesce state. If the session issuing the ALTER SYSTEM UNQUIESCE statement should terminate abnormally, the Oracle database server ensures that the unquiesce operation finishes.

Viewing the Quiesce State of an Instance

The V$INSTANCE view can be queried to see the current state of an instance. It contains a column named ACTIVE_STATE, whose values are shown in the following table:


ACTIVE_STATE |  Description
-------------+------------------------------------
NORMAL       |  Normal unquiesced state
QUIESCING    |  Being quiesced, but there are still
             |  active non-DBA sessions running
QUIESCED     |  Quiesced, no active non-DBA sessions
             |  are active or allowed

Source: http://www.utexas.edu/its/unix/reference/oracledocs/v92/B10501_01/server.920/a96521/start.htm#9497

Startup and Shutdown Options

2006-08-06T12:56:00.000-07:00

StartUp
Starting up the instance with the NOMOUNT option
Starting the instance with the MOUNT option
Starting up the instance with the FORCE option
Starting the instance with the OPEN option

ShutDown
Shutting down the database with the TRANSACTIONAL option
Shutting down the database with the IMMEDIATE option
Shutting down the database with the NORMAL option
Shutting down the database with the ABORT option

Options
Using the IMMEDIATE option
Using the NOMOUNT option
Using the NORMAL option
Using the MOUNT option
Using the TRANSACTIONAL option
Using the OPEN option
Using the FORCE option
Using the READ ONLY mode
Using the ABORT option
Using the BACKGROUND_DUMP_DEST parameter

Commands
SHUTDOWN IMMEDIATE
SHUTDOWN NORMAL
SHUTDOWN TRANSACTIONAL
SHUTDOWN ABORT
STARTUP OPEN READ ONLY FILE=init.ora
STARTUP NOMOUNT PFILE=init.ora
STARTUP MOUNT PFILE=init.ora
STARTUP OPEN PFILE=init.ora
STARTUP FORCE PFILE=init.ora
ALTER DATABASE MOUNT;
ALTER DATABASE OPEN;
SHUTDOWN IMMEDIATE

Exercise

connect system/manager@school as sysdba
startup
SET linesize 1000 pagesize 55
COL name FORMAT a50
col parameter format a40
col username format a10

pause
--Start
CLEAR SCR
-- In this exercise you will learn how to START and SHUTDOWN
-- the ORACLE database.
-- Connect to SQL*Plus as the system/manager user.
pause
CONNECT system/manager@school AS SYSDBA
pause


CLEAR SCR
-- Shutdown the database with the IMMEDIATE option and then
-- start the instance with the NOMOUNT option.

-- The IMMEDIATE option means not to wait for a user
-- to log off and roll back uncommitted transactions, then
-- shut down the instance and close the database.

-- The NOMOUNT option starts the instance without mounting
-- the database. It means that all of the memory structure
-- and background processes are in place, but no database is
-- attached to the instance.
pause
-- >>>>>>>>>>>>>>>>>>>>>> Shutdown
SHUTDOWN IMMEDIATE
CONNECT system/manager@school AS SYSDBA

-- >>>>>>>>>>>>>>>>>>>>>> Startup
STARTUP NOMOUNT PFILE=%ORACLE_BASE%\admin\school\pfile\init.ora
pause


CLEAR SCR
-- Now, mount the database and then open it.

-- Don't forget to check your alert file. The alert log file
-- stores information that is extremely useful in order to know
-- the health of the database. It records the starting and
-- stopping of the databases, creation of new redo log files,
-- datafiles, tablespaces, and most importantly, the Oracle
-- system error messages. It is located in the BACKGROUND_DUMP_DEST
-- parameter.

pause


ALTER DATABASE MOUNT;

ALTER DATABASE OPEN;

pause


CLEAR SCR
-- Shutdown the database with the NORMAL option and then
-- start the instance with the MOUNT option.

-- The NORMAL option will wait for users to log out and then, it
-- will close the database and shutdown the instance.

-- The MOUNT option, starts the instance, reads the control file,
-- and attaches the database, but does not open it.
pause
-- >>>>>>>>>>>>>>>>>>>>>> Shutdown
SHUTDOWN NORMAL
CONNECT system/manager@school AS SYSDBA

-- >>>>>>>>>>>>>>>>>>>>>> Startup
STARTUP MOUNT PFILE=%ORACLE_BASE%\admin\school\pfile\init.ora
pause


CLEAR SCR
-- Now, open the database.

-- We must open the database since in the STARTUP command,
-- we used the MOUNT option.

-- Notice that the MOUNT option starts the instance, reads
-- the control file, and attaches the database, but does not
-- open it.

pause


ALTER DATABASE OPEN;

pause


CLEAR SCR
-- Shutdown the database with the TRANSACTIONAL option and then
-- start the instance and open the database.

-- The TRANSACTIONAL option tells oracle not to wait for a user to
-- log off, but wait for the client to end the transaction that is
-- in progress, then shut down the instance and close the database.

-- The OPEN option starts the instance, reads the control file,
-- attaches the database, and then opens it. Notice that the OPEN
-- option is a default option.
-- You do not need to use the OPEN option, since it is the default
-- option.
pause
-- >>>>>>>>>>>>>>>>>>>>>> Shutdown
SHUTDOWN TRANSACTIONAL
CONNECT system/manager@school AS SYSDBA

-- >>>>>>>>>>>>>>>>>>>>>> Startup
STARTUP OPEN PFILE=%ORACLE_BASE%\admin\school\pfile\init.ora
pause


CLEAR SCR
-- Use the FORCE option to shutdown and then startup the database.
-- This should be your last resort when you cannot shutdown
-- your database.

-- Make sure that you have already patiently waited for the
-- database to be shutdown.

pause

-- >>>>>>>>>>>>>>>>>>>>>> Shutdown and Startup
STARTUP FORCE PFILE=%ORACLE_BASE%\admin\school\pfile\init.ora

pause


CLEAR SCR
-- You can also open your database on the READ ONLY mode.

-- In the READ ONLY mode, you cannot insert, update, or
-- delete any records.

-- Nor are you allowed to create, alter, or drop any tables.

-- Also, you can't change the structure of the database by adding
-- tablespaces or datafiles.

pause

SHUTDOWN IMMEDIATE
CONNECT system/manager@school AS SYSDBA

-- >>>>>>>>>>>>>>>>>>>>>> Startup
STARTUP OPEN READ ONLY PFILE=%ORACLE_BASE%\admin\school\pfile\init.ora

pause


CLEAR SCR
-- Now, let's connect to SQL*Plus as myuser.

pause
CONNECT myuser/schooling@school
pause

CLEAR SCR
-- Query the department table
pause
SELECT * FROM dept
/
pause

CLEAR SCR
-- Insert a record into the department table.
pause
INSERT INTO dept VALUES (50, 'EDUCATION','VIRGINIA')
/
-- Notice that you are not able to insert any record.
pause


CLEAR SCR
-- Shutdown the database with the ABORT option.
-- The ABORT option tells Oracle not to wait for a user and do not
-- roll back for any transaction and shutdown the instance.

pause

CONNECT system/manager@school AS sysdba

-- >>>>>>>>>>>>>>>>>>>>>> Shutdown
SHUTDOWN ABORT
pause

Source: http://www.iselfschooling.com/mcfd1cd1/iscddbh02plm.htm

Oracle Startup and Shutdown

2006-08-06T12:52:00.000-07:00

The best way to understand how all the diffrent files work together is to examine the Oracle startup proccess.

When Oracle starts an instance it reads the spfile or pfile to determine the initialization paramters. It uses these paramters to allocate the SGA and create background proccesses. All this is done without associating a database to the instance! At this point the instance is started but not mounted, or as some say "Started in no mount mode". They say that because you can reach this state by using the SQL*Plus command "startup nomount".


SQL> startup nomount;
ORACLE instance started.

Total System Global Area  184549376 bytes
Fixed Size                  1300928 bytes
Variable Size             157820480 bytes
Database Buffers           25165824 bytes
Redo Buffers                 262144 bytes
SQL> quit
# ps -ef | grep -i ora_
 oracle   720     1  0 14:14:20 ?        0:00 ora_reco_test
 oracle   710     1  0 14:14:19 ?        0:00 ora_mman_test
 oracle   708     1  0 14:14:19 ?        0:00 ora_pmon_test
 oracle   712     1  0 14:14:19 ?        0:00 ora_dbw0_test
 oracle   718     1  0 14:14:19 ?        0:00 ora_smon_test
 oracle   714     1  0 14:14:19 ?        0:00 ora_lgwr_test
 oracle   716     1  0 14:14:19 ?        0:00 ora_ckpt_test
 oracle   726     1  0 14:14:20 ?        0:00 ora_s000_test
 oracle   724     1  0 14:14:20 ?        0:00 ora_d000_test
 oracle   722     1  0 14:14:20 ?        0:00 ora_cjq0_test
#

When a database is mounted, the datafiles are actually associated is the instance. It's somewhat akin to loading the bullets into a gun; the gun is mearly a vehicle to utilize bullets and without them it's utterly useless. As a fun test, you can rename the datafile directory (where the control and datafiles are) and startup the instance without mounting. You won't get an error, you won't get a complaint... because Oracle isn't interested in anything but the parameter files at this point. Even though the pfile specifies the location of the control file, it hasn't tried to open the control files yet so it won't complain!

This last revolation is extremely important. Why? You'll notice that alot of the documenation, particularly reguarding recovery will tell you to connect to an instance even though it's in need of major recovery. At first glance, in some cases, you'll scratch your head trying to figure out why they expect you to start an instance of a database thats been destroyed. Well, now you know.

Moving along to the second phase of database startup, the database is mounted. In this step we associate the control, data, redo, and other database related files to the running instance. If you are missing files this is where you'll get your error. If you started your instance using nomount you can't use the startup command again, but you can use alter database statements to change the state of your instance.

Lets quickly look at what happens when you mount your database with the instance already running but with the all the data and control files missing (renamed data directory):


SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-00205: error in identifying controlfile, check alert log for more info
SQL> quit
# tail /u01/app/oracle/admin/test/bdump/alert_test.log
alter database mount
Wed Oct 13 14:28:12 2004
ORA-00202: controlfile: '/u02/oradata/test/control01.ctl'
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3
Wed Oct 13 14:28:12 2004
Controlfile identified with block size 0
Wed Oct 13 14:28:12 2004
ORA-205 signalled during: alter database mount...
#

Notice that it complains about the first controlfile and not the datafiles. Thats because the parameter file has a listing of all the controlfiles and the controlfile is responsable for storing information about all the other datafiles and resources used by the database. If the controlfile can't be read the database doesn't know what else exists! This is why you should be careful to keep good backups of your controlfiles using plain ol' system backups. This is also why Oracle maintains multiple copies (typically 3) of the control file for safety.

Lets put all the datafiles back and try mounting the database again.


# mv test.HOLD/ test
# sqlplus sys/passwd as sysdba
SQL*Plus: Release 10.1.0.2.0 - Production on Wed Oct 13 14:36:09 2004
Copyright (c) 1982, 2004, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> alter database mount;
Database altered.

Looking at the proccesses on the system, you'll notice that after mounting the database no change has occured to the proccesses.

Once an instance is started using a pfile and the mount proccess has used the controlfile(s) to associate the datafiles with the instance we need to open the database. Untill a database is opened it is not accessable. It's equivilent to starting a system in single-user mode. Some ammount of interaction with the databse is avalible at this stage but it's limited to fixed tables and views. The fixed tables and views are those in the data dictionary (Oracle's internal configuration tables).

But here's the confusing part, the normal data dictionary tables (ALL_USERS, for example) will give you an error, but most of the V$ tables, which are supposed to be the dynamic tables, work fine! What exactly "fixed" is supposed to mean in the case I dunno.


SQL> select * from all_users;
select * from all_users
             *
ERROR at line 1:
ORA-01219: database not open: queries allowed on fixed
tables/views only
SQL> select status from v$instance;

STATUS
------------
MOUNTED
SQL>

To open the database for normal access, we can alter the database again.

SQL> alter database open;
Database altered.

The shutdown proccess is the simply opposite of the startup.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

Source: http://cuddletech.com/articles/oracle/node31.html

Oracle Managed Files (OMF)

2006-08-06T12:13:00.000-07:00

OMF simplifies the creation of databases as Oracle does all OS operations and file naming. It has several advantages including:

* Automatic cleanup of the filesystem when database objects are dropped.
* Standardized naming of database files.
* Increased portability since file specifications are not needed.
* Simplified creation of test systems on differing operating systems.
* No unused files wasting disk space.

OMF Parameters

1. DB_CREATE_FILE_DEST
Defines the location of the default file system directory where Oracle creates datafiles or tempfiles when no file specification is given in the creation operation. Also used as the default file system directory for online redo log and control files if DB_CREATE_ONLINE_LOG_DEST_n is not specified.

2. DB_CREATE_ONLINE_LOG_DEST_n
Defines the location of the default file system directory for online redo log files and control file creation when no file specification is given in the creation operation. You can use this initialization parameter multiple times, where n specifies a multiplexed copy of the online redo log or control file. You can specify up to five multiplexed copies.

The location of database files is defined using the DB_CREATE_FILE_DEST parameter. If it is defined on its own all files are placed in the same location. All files will be created with unique names, 100 MB in size, with properties AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED. If the DB_CREATE_ONLINE_LOG_DEST_n parameter is defined alternate locations and levels of multiplexing can be defined for Logfiles. These parameters are dymanic and can be changed using the ALTER SYSTEM statement:


  ALTER SYSTEM SET DB_CREATE_FILE_DEST='C:\Oracle\Oradata\TSH1';

Files typically have a default size of 100M and are named using the following formats


File Type  Format
Controlfiles  ora_%u.ctl
Redo Log Files  ora_%g_%u.log
Datafiles  ora_%t_%u.dbf
Temporary Datafiles  ora_%t_%u.tmp

u% is a unique 8 digit code
g% is the logfile group number
%t is the tablespace name

Do not rename an Oracle-managed file. Oracle identifies an Oracle-managed file based on its name. If you rename the file, Oracle is no longer able to recognize it as an Oracle-managed file and will not manage the file accordingly.

Managing Controlfiles Using OMF
During database creation the controlfile names are not specified. Instead, a controlfile is created for each DB_CREATE_ONLINE_LOG_DEST_n specified in the init.ora file. Once the database creation is complete the CONTROL_FILES parameter can be set in the init.ora file using the generated names shown in the V$CONTROLFILE view.

Managing Redo Log Files Using OMF
When using OMF for redo logs the DB_CREATE_ONLINE_LOG_DEST_n parameters in the init.ora file decide on the locations and numbers of logfile members. For exmple:


  DB_CREATE_ONLINE_LOG_DEST_1 = c:\Oracle\Oradata\TSH1
  DB_CREATE_ONLINE_LOG_DEST_2 = d:\Oracle\Oradata\TSH1

The above parameters mean two members will be created for the logfile group in the specified locations when the ALTER DATABASE ADD LOGFILE; statement is issued. Oracle will name the file and increment the group number if they are not specified.

The ALTER DATABASE DROP LOGFILE GROUP 3; statement will remove the group and it members from the database and delete the files at operating system level.

Managing Tablespaces Using OMF
As shown previously the DB_CREATE_FILE_DEST parameter in the init.ora file specifies the location of the datafiles for OMF tablespaces. Since the file location is specified and Oracle will name the file, new tablespaces can be created using the following statement:

CREATE TABLESPACE tsh_data;

The resultant datafiles will have a default size of 100M and AUTOEXTEND UNLIMITED. For a specific size file use:

CREATE TABLESPACE tsh_data DATAFILE SIZE 150M;

To add a datafile to a tablespace use:

ALTER TABLESPACE tsh_data ADD DATAFILE;

If a tablespace is dropped, Oracle will remove the OS files also. For tablespaces not using the OMF feature this cleanup can be performed by issuing the statement:

DROP TABLESPACE tsh_data INCLUDING CONTENTS AND DATAFILES;

Default Temporary Tablespace
In previous releases, if you forgot to assign a temporary tablespace to a user the SYSTEM tablespace was used. This can cause contention and is considered bad practice. To prevent this 9i gives you the ability to assign a default temporary tablespace. If a temporary tablespace is not explicitly assigned the user is assigned to this tablespace.

A default temporary tablespace can be created during database creation or assigned afterwards:


  CREATE DATABASE TSH1
  ....
  DEFAULT TEMPORARY TABLESPACE dts1
  TEMPFILE 'c:\Oracle\Oradata\dts_1.f' SIZE 20M
  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

  -- or

  ALTER DATABASE DEFAULT TEMPORARY TABLESPACE dts2;

A default temporary tablespace cannot be taken offline until a new default temporary tablespace is brought online.

Source
http://www.lc.leidenuniv.nl/awcourse/oracle/server.920/a96521/omf.htm
http://www.oracle-base.com/articles/9i/OracleManagedFiles.php
Also See
http://orafaq.com/node/6

Soft and Hard Parses

2006-08-03T01:49:00.000-07:00

SQL statement is submitted to oracle => Oracle checks if statement has already been processed and if the resultant parse information still exists in the library cache.

If Yes => Parse Activity need not be repeated (this is a soft parse)
If No => Generate Parse information from scratch (this is a hard parse)

NOTE: "ALTER system FLUSH shared_pool" will force all subsequent SQL statements to hard parse the next time that they are run.

Examples of soft and hard parse
-------------------------------


   1. ALTER SESSION SET sql_trace=true;
   2. SELECT * FROM dual WHERE 1=1; -- Hard Parse
   3. SELECT /* Some Comment */ * FROM dual WHERE 1=1; -- Hard Parse
   4. SELECT * FROM dual WHERE 1=1; -- Soft Parse
   5. ALTER system FLUSH shared_pool;
   6. SELECT * FROM dual WHERE 1=1; -- Hard Parse

NOTE: Note that Step 3 is different from Steps 2,4 and 6. Even though the only difference is a comment (/* Some Comment */) that makes no difference to the data set returned or to the chosen access path, the statement is still treated as a different statement by Oracle.

These steps generate SQL_TRACE output as follows:


Step 1: ALTER SESSION SET sql_trace=true;
Sets up the tracing.


Step 2: select * from dual where 1=1

PARSING IN CURSOR #1 len=28 dep=0 uid=65 oct=3 lid=65 tim=1127532894840234 hv=1584415464 ad='583962ac'
select * from dual where 1=1
END OF STMT
PARSE #1:c=0,e=3962,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1127532894840222
EXEC #1:c=0,e=52,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1127532894840843
FETCH #1:c=0,e=265,p=0,cr=3,cu=0,mis=0,r=1,dep=0,og=1,tim=1127532894841178
FETCH #1:c=0,e=3,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=1127532894882236
STAT #1 id=1 cnt=1 pid=0 pos=1 obj=195 op='TABLE ACCESS FULL DUAL (cr=3 pr=0 pw=0 time=254 us)'

'PARSE #1' - indicates that there has been a parse call for this statement
'mis=1'    - indicates that this is a hard parse


Step 3: select /* Some Comment */ * from dual where 1=1;

PARSING IN CURSOR #2 len=47 dep=0 uid=65 oct=3 lid=65 tim=1127532904993772 hv=3208276547 ad='568f76bc'
select /* Some Comment */ * from dual where 1=1
END OF STMT
PARSE #2:c=0,e=1490,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1127532904993761
EXEC #2:c=0,e=39,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1127532904993919
FETCH #2:c=0,e=93,p=0,cr=3,cu=0,mis=0,r=1,dep=0,og=1,tim=1127532904994083
FETCH #2:c=0,e=3,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=1127532905034634
STAT #2 id=1 cnt=1 pid=0 pos=1 obj=195 op='TABLE ACCESS FULL DUAL (cr=3 pr=0 pw=0 time=82 us)'

'hard' parse (mis=1) since this SQL has not been parsed before. (although only a comment was added)


Step 4: select * from dual where 1=1;


PARSING IN CURSOR #1 len=28 dep=0 uid=65 oct=3 lid=65 tim=1127532927402693 hv=1584415464 ad='583962ac'
select * from dual where 1=1
END OF STMT
PARSE #1:c=0,e=112,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1127532927402682
EXEC #1:c=0,e=42,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1127532927402838
FETCH #1:c=0,e=75,p=0,cr=3,cu=0,mis=0,r=1,dep=0,og=1,tim=1127532927402983
FETCH #1:c=0,e=3,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=1127532927443401
STAT #1 id=1 cnt=1 pid=0 pos=1 obj=195 op='TABLE ACCESS FULL DUAL (cr=3 pr=0 pw=0 time=63 us)'

Since this select is a repeat of Step 2 it has already been parsed and, in this case, the parse information is still in the library cache, the query is found in the shared pool and a soft parse occurs. The parse line shows this as mis=0.


Step 5: ALTER system FLUSH shared_pool;

This step 'removes' all the SQL in the shared pool


Step 6: select * from dual where 1=1;

PARSING IN CURSOR #2 len=28 dep=0 uid=65 oct=3 lid=65 tim=1127533057421354 hv=1584415464 ad='583962ac'
select * from dual where 1=1
END OF STMT
PARSE #2:c=10000,e=3360,p=0,cr=4,cu=0,mis=1,r=0,dep=0,og=1,tim=1127533057421344
EXEC #2:c=0,e=30,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1127533057421494
FETCH #2:c=0,e=177,p=0,cr=3,cu=0,mis=0,r=1,dep=0,og=1,tim=1127533057421744
FETCH #2:c=0,e=4,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=1127533057462246
STAT #2 id=1 cnt=1 pid=0 pos=1 obj=195 op='TABLE ACCESS FULL DUAL (cr=3 pr=0 pw=0 time=163 us)'

(The trace output above follows large amounts of recursive SQL that has also had to be reparsed due to flushing ALL SQL from the shared pool).
The query in this step is the same as the query in Step 2 and Step 4 Step 2 was a hard parse whereas Step 4 was soft parsed. This time a hard parse is required since the SQL has been flushed from the shared pool and so the parse line shows 'mis=1'.

TKProf Output
=============

Steps 2, 4 and 6:


select *
from
 dual where 1=1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        3      0.00       0.00          0          0          0           0
Execute      3      0.00       0.00          0          0          0           0
Fetch        6      0.00       0.00          0          9          0           3
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       12      0.00       0.00          0          9          0           3

Misses in library cache during parse: 2
Optimizer mode: ALL_ROWS
Parsing user id: 65

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  TABLE ACCESS FULL DUAL (cr=3 pr=0 pw=0 time=254 us)

Observation : The Parse count above shows 3 parse calls.
This consists of two Hard Parses (Step 2 and Step 6) and a single soft parse (Step 4).
'Misses in library cache during parse: 2' records where the SQL was not found (i.e. a library cache miss) in the library cache and indicates that 2 of the parses were hard parses.
Step 4 found an existing entry in the library cache for this statement and therefore, only a soft parse was necessary.

Step 3:


select /* Some Comment */ *
from
 dual where 1=1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          3          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0          3          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 65

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  TABLE ACCESS FULL DUAL (cr=3 pr=0 pw=0 time=82 us)

Observation : In this case the Query shows a hard parse as the parse count is 1 and the 'Misses in library cache during parse: 1'.

Source: After reading Note 34433.1, attitudenine was trying out what was said there and thats what produced the above output.

Check for which objects are being locked and by whom

2006-08-02T07:36:00.000-07:00



select  nvl(S.USERNAME,'Internal') username,
        nvl(S.TERMINAL,'None') terminal,
        L.SID||','||S.SERIAL# Kill,
        U1.NAME||'.'||substr(T1.NAME,1,20) tab,
        decode(L.LMODE,1,'No Lock',
                2,'Row Share',
                3,'Row Exclusive',
                4,'Share',
                5,'Share Row Exclusive',
                6,'Exclusive',null) lmode,
        decode(L.REQUEST,1,'No Lock',
                2,'Row Share',
                3,'Row Exclusive',
                4,'Share',
                5,'Share Row Exclusive',
                6,'Exclusive',null) request
from    V$LOCK L,
        V$SESSION S,
        SYS.USER$ U1,
        SYS.OBJ$ T1
where   L.SID = S.SID
and     T1.OBJ# = decode(L.ID2,0,L.ID1,L.ID2)
and     U1.USER# = T1.OWNER#
and     S.TYPE != 'BACKGROUND'
order by 1,2,5

Sys_Context Function

2006-07-22T07:30:00.000-07:00

The sys_context function can be used to retrieve information about the Oracle environment.

The syntax for the sys_context function is:


      sys_context( namespace, parameter, [ length ] )

* namespace is an Oracle namespace that has already been created. If the namespace of 'USERENV' is used, attributes describing the current Oracle session can be returned.
* parameter is a valid attribute that has been set using the DBMS_SESSION.set_context procedure.
* length is optional. It is the length of the return value in bytes. If this parameter is omitted or if an invalid entry is provided, the sys_context function will default to 256 bytes.

USERENV is an Oracle provided namespace that describes the current session. Sample queries using parameters from the 'USERENV' namespace are listed below


SELECT Sys_Context('USERENV', 'DB_DOMAIN') FROM dual
-- us.acme.com
SELECT Sys_Context('USERENV', 'DB_NAME')   FROM dual
-- orcl
SELECT Sys_Context('USERENV', 'HOST') FROM dual    
-- pc-mydb
SELECT Sys_Context('USERENV', 'INSTANCE') FROM dual
-- 1
SELECT Sys_Context('USERENV', 'INSTANCE_NAME') FROM dual
-- orcl
SELECT Sys_Context('USERENV', 'IP_ADDRESS') FROM dual
-- 15x.xx.245.138
SELECT Sys_Context('USERENV', 'TERMINAL') FROM dual 
-- pc-mydb
SELECT Sys_Context('USERENV', 'SERVER_HOST') FROM dual
-- ap601gns
SELECT Sys_Context('USERENV', 'NLS_DATE_FORMAT') FROM dual
-- DD-MON-RR

With Excerpts from:
http://www.psoug.org/reference/sys_context.html
http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10759/functions150.htm
http://www.techonthenet.com/oracle/functions/sys_context.php

Userenv Function

2006-07-22T05:56:00.000-07:00

In Oracle/PLSQL, the userenv function can be used to retrieve information about the current Oracle session. Although this function still exists in Oracle for backwards compatibility, it is recommended that you use the sys_context function instead.

The syntax for the userenv function is:


       userenv( parameter )

parameter is the value to return from the current Oracle session. The possible values are:


CLIENT_INFO   Returns user session information stored using 
                          the DBMS_APPLICATION_INFO package
ENTRYID       Available auditing entry identifier
INSTANCE      The identifier number of the current instance
ISDBA         Returns TRUE if the user has DBA privileges. 
                          Otherwise, it will return FALSE.
LANG          The ISO abbreviation for the language
LANGUAGE      The language, territory, and character of 
                      the session in the following format:
                          Language_Territory.Characterset
SESSIONID     The identifier of the auditing session
TERMINAL      The OS identifier of the current session

Sample Values from my PC


SELECT UserEnv('CLIENT_INFO') FROM dual -- Returns NULL
SELECT UserEnv('ENTRYID') FROM dual     -- 0
SELECT UserEnv('INSTANCE') FROM dual    -- 1
SELECT UserEnv('ISDBA') FROM dual       -- FALSE
SELECT UserEnv('LANG') FROM dual        -- US
SELECT UserEnv('LANGUAGE') FROM dual    -- AMERICAN_AMERICA.UTF8
SELECT UserEnv('SESSIONID') FROM dual   -- 7297876
SELECT UserEnv('TERMINAL') FROM dual    -- pc-mydb

Source: http://www.techonthenet.com/oracle/functions/userenv.php

Undo Management - Manual and Automatic

2006-07-22T04:04:00.000-07:00

Oracle Database keeps records of actions of transactiion, before they are committed and Oracle needs this information to rollback or Undo the Changes to the database. These records in Oracle are called Rollback or Undo Records. These records are used to Rollback transactions when a rollback statement is issued or during recovery of a database or to provide a read consistent view of data.

Until Oracle 8i and also in 9i, Oracle uses Rollback Segments to manage the Undo Data. Starting with Oracle 9i, DBA's are provided with a new feature referred to as "Undo Tablespace" which allows the dba to exert more control on how long Undo information is retained and also eliminates the complexity of managing Rollback Segments in certain environments.

Starting with 9i, the rollback segment way is referred to as Manual Undo Management Mode and the new Undo Tablespaces method as the Automatic Undo Management Mode. Although both rollback Segments and Undo Tablespaces are supported in Oracle 9i, both modes cannot be used. System Rollback segment exists in both the modes.

Init.ora Parameters for Automatic Undo Management

UNDO_MANAGEMENT : This parameter sets the mode in which oracle manages the Undo Information.
The default value for this parameter is MANUAL so that all your old init.ora files can be used without any changes. To set the database in an automated mode, set this value to AUTO. ( UNDO_MANAGEMENT = AUTO)

UNDO_TABLESPACE : This parameter defines the tablespaces that are to be used as Undo Tablespaces. If no value is specified oracle grabs the first available Undo Tablespace or if there are none present, Oracle will use the system rollback segment to startup. This value is dynamic and can be changed online ( UNDO_TABLESPACE = undo_tbs1 )

UNDO_RETENTION : This value specifies the amount of time, Undo is kept in the tablespace. This applies to both committed and uncommitted transactions since the introduction of FlashBack Query feature in Oracle needs this information to create a read consistent copy of the data in the past. Default value is 900 Secs ( UNDO_RETENTION = 500)

UNDO_SUPRESS_ERRORS : This is a good thing to know about in case your code has the alter transaction commands that perform manual undo management operations. Set this to true to suppress the errors generated when manual management SQL operations are issued in an automated management mode.

Creating and Managing Undo Tablespaces :

Undo tablespaces use syntax that is similar to regular tablespaces except that they use the keyword UNDO. These tablespaces can be created during the database creation time or can be added to an existing database using the create UNDO Tablespace command

Create DATABASE mydb controlfile ...........
UNDO Tablespace undo_tbs0 datafile '/vol1/data/mydb/undotbs0.dbf' ...

Create UNDO Tablespace undo_tbs1
datafile '/vol1/data/uday/undotbs1.dbf' size 25m autoextend on;

All operations like Renaming a data file, Adding a datafile, Online /Offline Swith or Start Backup / End Backup Switch can be made using the regular alter tablespace command. All other operations are managed by Oracle in the automated management mode.

Monitoring :

v$UNDOSTAT : This view contains statistics for monitoring the effects of transaction execution on Undo Space in the current instance. These are available for space usage, transaction concurrency and length of query operations. This view contains information that spans over a 24 hour period and each row in this view contains data for a 10 minute interval specified by the BEGIN_TIME and END_TIME.

Source: http://www.dbasupport.com/oracle/ora9i/undo.shtml

Query to find locked objects

2006-07-15T09:16:00.000-07:00

Run the below query to check for locked objects


SELECT DISTINCT session_id as "Session Id",
       a.os_user_name as "Os user",
       b.object_name "Object Name",
       decode(a.locked_mode,
              2 ,'ROW SHARE MODE',
              3 ,'ROW EXCLUSIVE MODE',
              4 ,'SHARE MODE',
              5 ,'SHARE ROW EXCLUSIVE MODE',
              6 ,'EXCLUSIVE','UNKNOWN')as "Lock Mode"
from   v$locked_object a,
       all_objects b
WHERE  a.object_id = b.object_id

Eg: The 'SELECT ... FOR UPDATE' statement below will lock the EMP table.


SQL> SELECT *
     FROM   emp
     WHERE  ename = 'xyz'
     FOR UPDATE;

After locking EMP table, run the lock-checker query


SQL> /

Session Id  Os user  Object Name  Lock Mode
----------  -------  -----------  ------------------
       681  myuser           EMP  ROW EXCLUSIVE MODE

Reading an Explain Plan

2006-07-15T07:40:00.000-07:00

We'll take a look at a query plan resulting from a query against the SCOTT/TIGER tables (note, I add primary keys to the EMP and DEPT tables - hence, they are indexed):


scott@ORA920> delete from plan_table;
7 rows deleted.

scott@ORA920> explain plan for
2  select ename, dname, grade
3    from emp, dept, salgrade
4   where emp.deptno = dept.deptno
5     and emp.sal between salgrade.losal and salgrade.hisal
6  /
Explained.

scott@ORA920> @?/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------
| Id  | Operation                    |Name     |Rows|Bytes|Cost |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |    |     |     |
|   1 |  NESTED LOOPS                |         |    |     |     |
|   2 |   NESTED LOOPS               |         |    |     |     |
|   3 |    TABLE ACCESS FULL         | SALGRADE|    |     |     |
|*  4 |    TABLE ACCESS FULL         | EMP     |    |     |     |
|   5 |   TABLE ACCESS BY INDEX ROWID| DEPT    |    |     |     |
|*  6 |    INDEX UNIQUE SCAN         | DEPT_PK |    |     |     |
-----------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - filter("EMP"."SAL"<="SALGRADE"."HISAL" AND            "EMP"."SAL">="SALGRADE"."LOSAL")
6 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")

Note: rule based optimization

21 rows selected.

Now - what happens first? How does that plan actually get evaluated? First I'll show you the psuedo code for how the plan is evaluated and then we'll discuss how I arrived at this conclusion:


For salgrade in (select * from salgrade)
Loop
For emp in ( select * from emp )
Loop
 If ( emp.sal between salgrade.losal and salgrade.hisal )
 Then
     Select * into dept_rec
       From dept
      Where dept.deptno = emp.deptno;

     OUTPUT RECORD with fields from salgrade,emp,dept
 End if;
End loop;
End loop;

The way I read the plan was to turn it into a graph of sorts - an evaluation tree. In order to do that, we need to understand something about access paths.

For detailed information on the access paths available to Oracle, please see the Oracle Performance and Tuning Guide.

In order to build the tree - we can start at the top, with step 1. That will be our "root node" in the tree. Next, we need to find the things that "feed" this root node - that will be steps 2 and 5 - as you can see - 2 and 5 are at the same level of indentation - they "feed" into step 1. Further, we can see that steps 3 and 4 feed step 2 and that step 6 feeds step 5.


Putting that together iteratively - we would draw:

                               1
                              /                              2   5
                            / \                              3   4   6

And then just read the tree. In order to get 1 we need 2 and 5 - 2 is "first". In order to get 2, we need 3 and 4. 3 is "first". That is how I arrived at the psuedo code for:


For salgrade in (select * from salgrade)
Loop
For emp in ( select * from emp )
Loop

Full scan SALGRADE is step 3, full scan EMP is step 4 and step 2 is a nested loop - which is roughly equivalent to two "for loops". Once we get step 2 going like that - we can look at step 5. Step 5 runs step 6 first - step 6 is the index scan step. We are taking the output of step 2 here and using that to "feed" this part of the query plan. So, the output from step 2 is used to perform an index scan - that index scan output is used to TABLE ACCESS BY ROWID the DEPT table and that result is the output of step 1 - our result set.

Now, to make this "interesting", we will run an equivalent query - but we'll mix up the order of the tables in the from clause this time. Since I am using the rule based optimizer - this will affect the generated query plan (and is just one reason why you DON'T want to use the rule based optimizer!). We'll use the same logic to build its query plan tree and evaluate how it processed the query:


scott@ORA920> delete from plan_table;
7 rows deleted.

scott@ORA920> explain plan for
2  select ename, dname, grade
3    from salgrade, dept, emp
4   where emp.deptno = dept.deptno
5     and emp.sal between salgrade.losal and salgrade.hisal
6  /
Explained.

scott@ORA920> @?/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------
| Id  | Operation                     |  Name       | Rows  | Bytes
-------------------------------------------------------------------
|   0 | SELECT STATEMENT              |             |       |
|   1 |  NESTED LOOPS                 |             |       |
|   2 |   NESTED LOOPS                |             |       |
|   3 |    TABLE ACCESS FULL          | EMP         |       | 
|   4 |    TABLE ACCESS BY INDEX ROWID| DEPT        |       | 
|*  5 |     INDEX UNIQUE SCAN         | DEPT_PK     |       | 
|*  6 |   TABLE ACCESS FULL           | SALGRADE    |       | 
-------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
6 - filter("EMP"."SAL"<="SALGRADE"."HISAL" AND            "EMP"."SAL">="SALGRADE"."LOSAL")
Note: rule based optimization

21 rows selected.

Here we see that steps 2 and 6 feed 1, steps 3 and 4 feed 2, and step 5 feeds 4.


Drawing the tree:
                               1
                              /                              2   6
                            / \  
                           3   4
                                                                 5

So, the psuedo code logic here is - starting with steps 3 and 4:


For emp in ( select * from emp )
Loop
-- using the index
Select * from dept where dept.deptno = emp.deptno

For salgrade in (select * from salgrade )
Loop
 If ( emp.sal between salgrade.losal and salgrade.hisal )
 Then
    OUTPUT RECORD;
 End if;
End loop
End loop;

And that is it - if you draw the graphical tree like that and then read it bottom up, left to right, you'll get a good understanding of the "flow" of the data.

Source: A wonderful article by Tom Kyte at
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:231814117467

Tablespace- Disk usage check

2006-07-15T05:12:00.000-07:00

Query to check free space in each tablespace

The following query will show how much free space is left in each tablespace. A space check would be the first step in diagnosing and rectifying errors like
'ORA-01654: unable to extend index scott.my_idx by 128 in tablespace '

SELECT df.tablespace_name TABLESPACE,
       df.total_space TOTAL_SPACE,
       fs.free_space FREE_SPACE, 
       df.total_space_mb TOTAL_SPACE_MB,
      (df.total_space_mb - fs.free_space_mb) USED_SPACE_MB,
       fs.free_space_mb FREE_SPACE_MB,
       ROUND(100 * (fs.free_space / df.total_space),2) PCT_FREE
FROM (SELECT tablespace_name, 
             SUM(bytes) TOTAL_SPACE,
             ROUND(SUM(bytes) / 1048576) TOTAL_SPACE_MB
      FROM   dba_data_files
      GROUP BY tablespace_name) df, 
     (SELECT tablespace_name, 
             SUM(bytes) FREE_SPACE,
             ROUND(SUM(bytes) / 1048576) FREE_SPACE_MB
      FROM   dba_free_space
      GROUP BY tablespace_name) fs
WHERE df.tablespace_name = fs.tablespace_name (+)
ORDER BY PCT_FREE asc;

If you find that a Tablespace is lacking in freeSpace, you might want to add extra space to it by issuing the following command.

ALTER TABLESPACE TABLESPACENAME
ADD DATAFILE '/slot01/oracle/mydbdata/tablespacenamexx.dbf' SIZE 500 M

With Excerpts from: http://www.psoug.org/reference/tablespaces.html

Unlock an Oracle Account

2006-07-15T04:41:00.000-07:00

To unlock an oracle account do the following

$ sqlplus / as sysdba
SQL> alter user account unlock;

To check if the account has been unlocked
SQL> select USERNAME,ACCOUNT_STATUS,LOCK_DATE from dba_users where USERNAME like 'userName';

Peformance Tuning: Part-3

2006-07-15T04:25:00.000-07:00

In this part, we will look at the importance of Stored Procedures and Sub-Queries

6.Stored Procedures

A stored procedure is a module that is stored in the Oracle database and is written in either PL/SQL (Oracle's proprietory procedural language) or Java. The term procedure is really a misnomer as the "stored procedure" can be a function (ie. it returns a value), a procedure (may have input and/or output parameters), or a package (may contain one or more procedures/functions).

The main performance advantages arise from the fact that there is no need to send data back and forth across the network whilst it is being processed and from the already mentioned fact that stored procedures are tightly coupled with the database and are stored in pre-compiled form which means that the commands are processed much more efficiently than would be the case if an external 3gl was used. The other benefit derives from the fact that the stored procedures will be run on the server hosting the database which is generally a much more powerful computer than the clients.

Stored procedures are ideal when there is a complex piece of business logic that needs to be performed and which involves a lot of database access and if this logic is required in many different places, then even better.

The biggest benefit in the use of stored procedures comes from the use of packages, because whenever any public element of the package is referenced, the whole package is loaded into memory and (assuming it is not aged out) remains in memory for future use. Also if other elements in the package are needed the overhead of calling them is very small, because they are in the same memory area. This means that you can and should group together related program units so that they can share data structures when needed and call each other with minimal overhead.

Having extolled the virtues of stored procedures, you should be aware that pl/sql is not always the best tool, sometimes it is quicker to do things in pure SQL rather than use pl/sql. The other pitfall of using pl/sql is that involves a context switch from SQL which may add a considerable overhead. This is most likley to be a problem when embedding pl/sql function calls in SQL statements. Also the code inside pl/sql functions called from SQL statements will be ignored by EXPLAIN PLAN. This means that a judgement will have to be made on whether the advantages of using stored procedures will outweigh the disadvantages.

7. Sub Queries

Sub-queries are queries embedded in another sql statement, often a query. They allow us to answer multi-part questions and are often interchangeable with a join.

Usage
* in the WHERE, HAVING and START WITH clauses to define the limiting set of rows for SELECT, UPDATE and DELETE statements;
* in the FROM clause of a SELECT statement instead of a table name;
* in INSERT, UPDATE and DELETE statements instead of a table name;
* to define the set of rows to be created in the target table of a CREATE TABLE AS or INSERT INTO statement;
* to define the set of rows to be included by a view or a snapshot in a CREATE VIEW or CREATE SNAPSHOT statement;
* to provide the new values for the specified columns in an UPDATE statement.

There are two types of sub queries: correlated and non-correlated

* Non Correlated Sub-Queries
Non-correlated sub queries are executed once for the whole statement, correlated sub queries are executed once per row in the parent query

Eg:
SELECT name FROM emp WHERE dept_id IN
(SELECT dept_id FROM dept WHERE budget > 20,000)

The most common use of sub queries is in the WHERE clause of queries to define the limiting condition (i.e. what value(s) rows must have to be of interest), as in the above example.

* Correlated Sub-Queries

Correlated sub queries also answer multi-part questions, but are most often used to check for existence or absence of matching records in the parent table and the related table in the sub query. A correlated sub query refers to a column from a table in the parent query.

SELECT *
FROM scott.dept
WHERE NOT EXISTS (SELECT 1 FROM scott.emp WHERE emp.deptno = dept.deptno)

The main performance advantages arise when using sub queries to perform efficient existence checks ie. when you don't actually need to know the value of one or more columns in a row in a table you just need to know whether or not there is a matching row. The EXISTS and NOT EXISTS operators are used for this purpose.

Very often, significant performance improvement can be achieved by modifying queries as below
* IN-based sub-queries - with EXISTS-based sub-queries
________ WHERE {someCol} IN ( ... ) ==> WHERE EXISTS ( ... )
* 'NOT IN'-based sub-queries - with 'NOT EXISTS'-based sub-queries
________ WHERE {someCol} NOT IN ( ... ) ==> WHERE NOT EXISTS ( ... )

With Excerpts from:
http://www.smart-soft.co.uk/Oracle/oracle-performance-tuning-part7.htm
http://www.smart-soft.co.uk/Oracle/oracle-performance-tuning-part6.htm

Peformance Tuning: Part-2

2006-07-14T23:58:00.000-07:00

In the previous post, we saw how FTS and indexes can be helpful. Here we will see the impact of Joins, Views and De-Normalization.

3. Joins

There are two ways of maximising performance, the first is to design your Oracle database to minmise the number of joins required and to minimise the number of tables in the joins. This requires knowledge of the data structure and the processes performed on the data. If de-normalisation is performed, the performance of queries may be enhanced at the expense of updates and overall flexibility in the system (thereby increasing maintenance costs).

When using the rule-based optimiser in Oracle, the query can only be tuned by physically changing the order of the tables lsited in the from clause. All other things being equal (eg indexes available), the RBO will use the table listed last in the from clause as the driving table (ie. the table queried first, and the results from which are used to match with the other tables). For the CBO, this is not applicable since it would automatically pick the best table to start with(the smaller table or the table which would return least number of rows).

The best way to optimise a join is to ensure that each join involves only a few tables. If you have a large number of queries requiring 4 or 5 or more tables to be joined then it suggests that your database design should be revisited to minimise the number of joins required. If you have an RBO, make sure that the smallest result set is created first (either use hints or rearrange the order of the tables in the join clause).

Also ensure that the join columns are indexed and that the query does not inadvertently prevent the use of indexes by using functions such as "upper" or making it part of an expression eg.
"WHERE {indexed_field} + 1 = {expression}". In Oracle 8i and above, you can have function-based indexes but these have to be specifically created for each function. You must also be aware that the creation of new indexes may affect the performance of unrelated queries and that de-normalising tables is a trade off between faster queries and slower updates (and reduced flexibility).

4. Views

Views are useful for providing
(a) horizontal or vertical subsets of data from a table (possibly for security reasons)
(b) for hiding the complexity of a query
(c) for ensuring that exactly the same SQL is used throughout your application
(d) to retrieve supplementary information about an entity from a related table.

* A horizontal subset of a table is simply a subset of the rows in the table - i.e. a restriction is added to the where clause to limit the rows returned by the query.
* A vertical subset is just a subset of the columns in the table.

Suppose you create a view to hide the details of few rather complicated queries involving the join of several tables. Now, if you have a query which requires an intersection of these two views, you need to check the explain plan so that the execution path is satisfactory.

A view, by definition is merely a pre-written SQL query, therefore by using the view you are using the pre-defined query, more importantly though, you are using exactly the same query. This means that the query has to be parsed and the execution path determined only once - the first time it is used. Subsequent uses of the query will avoid the overhead of parsing the query and determining the execution plan. Generally this is a good thing. There is a caveat however - if the data is highly skewed and the cost-based optimiser is being used, this may not be desirable as the initially chosen execution plan may not be right for all elements of the data (assuming that the query uses a bind variable and not a constant, of course).

5. De-Normalization

"Normalize until it hurts, denormalize until it works"
-Jason Couchman, author of Oracle8 Certfied Professional DBA Certification Exam Guide

Normalization is part of the analysis phase not the design phase. There is a very common misconception among developers that an Oracle database should be normalised to third normal form or above. A fully normalised database will have severe performance problems as Normalisation increases the complexity of the database and increases the number of tables that have to be joined to provide the required information.

For a large multi-user application an Oracle database in 3NF or above will quickly grind to a halt as your Oracle database struggles to join all the required tables, particularly if any of the tables are quite large.

The space requirements also will be higher as each table will require its own indexes and each update transaction may have to write to several tables instead of just the one.

The information requirements tell us what pieces of data are needed together (eg. customer name, address, telephone number, account transaction history, etc.) and how often, the update requirements tell us which pieces of data are changed, and again how often. With this information a pattern should emerge showing which pieces of data are often required together and whether or not they are updated together. This will then indicate which entities should be combined to form one table.

There is of course a trade-off between
* speed of access and
* maintenance/storage requirements caused by duplication of data.

Technique #1 Eliminate joins
Eg: If an employee and his department name are accessed very often try having the department name column within the EMP table.
Technique #2 Create summary tables
This can be achieved either by creating extra tables, or using materialised views in Oracle 8i and above, or by creating extra columns in existing tables.
Eg: Create summary tables to track sales by product, by sales person, by department, by month, by quarter, by year to date and by region.
Technique #3 Include detail info with master
Eg: If a customer database was required to store customers' past and present addresses, this would usually be normalised to create a seperate customer address table. However, most of the time, previous addresses would not be of much interest so the current address could be stored redundantly in the customer table.

Source:
http://www.smart-soft.co.uk/Oracle/oracle-performance-tuning-part3.htm
http://www.smart-soft.co.uk/Oracle/oracle-tuning-part4-vw-use.htm

Peformance Tuning: Part-1

2006-07-11T23:02:00.000-07:00

The following are the "weapons" available in your "arsenal" for performance tuning

1. Full table scans
2. Indexes
3. Joins
4. Views
5. De-normalization
6. Stored Procedures
7. Sub-queries

1. Full table scans

This might seem counter-intuitive, surely all queries on a table should be index driven ? Actually, no, they shouldn't. If the table is very large or very small using an index may increase the amount of i/o that is required to access the data and as i/o is the slowest operation, this will cause a degradation in performance.
1.1 Small Tables - Because the table is small and because Oracle reads multiple database blocks in one read operation the whole table is scanned in just one read, so however efficient the index, by using it we will be performing unnecessary i/o. The exception to this of course is when the table has been created as an index-only table (available since Oracle 8) which means that the whole table is stored in a B-tree structure
1.2 Large Tables - For Large Tables, the situation when a full table scan is very likely to perform better than an index scan and table lookup is when you are retrieving 10% or more of the data in the table. Of course if you only want to retrieve one row in the table, then you would want to use an index.

2. Indexes - B-Tree(Balanced Tree) and Bitmap

2.1 B-Tree or Balanced-Tree Indexes (not necessarily Binary Tree, because they may be 'n'-ary trees) are most appropriate when retrieving a small amount of data from a very large table. However, by using Indexes, the performance of updates will be impaired because every time a row is inserted into, deleted from or updated (if the indexed column(s) is/are updated) in the database, the indexes have to be adjusted accordingly.

Therefore having lots of indexes is ideal when the database is mostly read only, but if there are a high proportion of inserts and deletes and only a few read operations, then you would find that indexes would degrade rather than improve performance. You also need to ensure that the particular column(s) you are proposing to index are used frequently as the limiting conditions for a query, if not you will waste a lot of space and degrade database performance.

Another factor to consider with b-tree indexes, is how specific they are. The ideal index is one that refers to only one row in the table, as a primary key does by definition. This reduces considerably the amount of i/o required to retrieve the information, but if the index value relates to many rows because it is not selective, then a lot more data will be retrieved resulting in unnecessary i/o.

As mentioned before the ideal index will refer to just one row in the table and the same rule applies to concatenated indexes. With concatenated indexes, therefore, the leading column should be the most selective because this will have the biggest impact on the biggest number of queries, assuming that the same column is used most frequently in the limiting conditions of queries.

2.2 Bitmap Indexes

If your application is designed to query on non-selective columns, then you should consider the use of bitmap indexes, but only when the data is infrequently updated as bitmap indexes add a considerable overhead to update operations: each new value of the indexed column will require a whole new bitmap to be created and deletes and updates of the indexed column will also require the bitmaps to be adjusted, as there is one bitmap for each distinct value in the column.

Another disadvantage of bit-map indexes in a transaction processing environment is that each update would lock a large number of rows, degrading throughput even more. Bitmaps are valuable in indexing frequently queried, non selective columns in large tables where little or no updating is done, for example in a data warehouse.

Source:
http://www.smart-soft.co.uk/Oracle/oracle-performance-tuning-part1.htm
http://www.smart-soft.co.uk/Oracle/oracle-performance-tuning-part2.htm

PLSQL Collections and Records

2006-07-11T07:09:00.000-07:00

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

Script to Kill all sessions which are holding EXCLUSIVE Locks

2006-07-11T05:26:00.000-07:00

To remove Exclusive locks, the following crude method can be employed.
Before running this make sure that someone is not genuinely holding a lock ;-)

DECLARE
--Cursor to fetch all sessions which are holding Exclusive Locks
CURSOR c IS
SELECT LOCKs.session_id sessionid, ses.serial# serialid
FROM v$session ses, sys.dba_lock locks
WHERE locks.mode_held = 'Exclusive'
AND ses.sid = locks.session_id;

c_row c%ROWTYPE;

l_sql VARCHAR2(100);

BEGIN

OPEN c;

LOOP
FETCH c INTO c_row;
EXIT WHEN c%NOTFOUND;
l_sql := 'alter system kill session '''||c_row.sessionid||','||c_row.serialid||'''';
EXECUTE IMMEDIATE l_sql;
END LOOP;

CLOSE c;
END;

Why Oracle Database doesn't have a boolean datatype

2006-07-10T22:49:00.000-07:00

Two reasons I could find -

1. Since a column defined as below would serve the same purpose
--> flag char(1) NOT NULL check (flag in ( 'Y', 'N' ))

2. ANSI SQL doesn't require a BOOLEAN datatype.

However PLSQL does support Boolean variables.

Find a good discussion on this topic at:
http://asktom.oracle.com/pls/ask/f?p=4950:8:2109566621053828525::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:6263249199595

High Water Mark (HWM)

2006-07-09T05:02:00.000-07:00

HWM is a term used with table segments. If a table is envisioned as a series of blocks laid one after the other in a line from left to right, the HWM would be the rightmost block that ever contained data.

As shown in the figure, HWM starts at the first block for a newly created table. As data is inserted into the table, the HWM rises. If certain records were to be deleted, the HWM remains unchanged. This remains so until the object is rebuilt, truncated or the segment shrunk. DELETE operation does not reset the HWM whereas TRUNCATE will reset the HWM.

HWM is relevant because - for a Full Table Scan, Oracle scans all blocks under the HWM, even if they contain no data. Hence HWM is a parameter which might affect performance.

The MERGE Statement

2006-07-09T02:54:00.000-07:00

The MERGE statement (aka "UPSERT") released in Oracle 9i is possibly one of the most useful ETL-enabling technologies built into the Oracle kernel. The MERGE statement enables us to either UPDATE or INSERT a row into a target table in one statement. You simply tell Oracle your rules for determining whether a target row should UPDATEd or INSERTed from the source, and Oracle does the rest.

Prior to 9i, the alternative in SQL was to perform two individual DML statements (one UPDATE and one INSERT, each with opposing predicates). The alternative in PL/SQL was - either try to INSERT a row and if it failed with a DUP_VAL_ON_INDEX exception, then UPDATE the row instead, or try to UPDATE a row, only INSERTing in the event of a SQL%NOTFOUND.

SQL>
SQL> CREATE TABLE emp_source
2 AS
3 SELECT * FROM emp;

Table created.

SQL>
SQL> SELECT COUNT(*) FROM emp_source;

COUNT(*)
----------
14

SQL>
SQL> CREATE TABLE emp_target
2 AS
3 SELECT * FROM emp WHERE ROWNUM <= 8;

Table created.

SQL>
SQL> SELECT COUNT(*) FROM emp_target;

COUNT(*)
----------
8

SQL>
SQL> BEGIN
2 MERGE INTO emp_target et
3 USING ( SELECT * FROM emp_source ) es
4 ON ( et.empno = es.empno )
5 WHEN MATCHED THEN
6 UPDATE
7 SET et.ename = es.ename
8 , et.sal = es.sal
9 , et.mgr = es.mgr
10 , et.deptno = es.deptno
11 WHEN NOT MATCHED THEN
12 INSERT
13 ( et.empno, et.ename, et.sal, et.mgr, et.deptno )
14 VALUES
15 ( es.empno, es.ename, es.sal, es.mgr, es.deptno );
16
17 DBMS_OUTPUT.PUT_LINE(TO_CHAR(SQL%ROWCOUNT) || ' rows merged.');
18 END;
19 /
14 rows merged.

PL/SQL procedure successfully completed.

Source: http://www.quest-pipelines.com/newsletter-v4/0903_D.htm

How a Deadlock happens

2006-07-09T02:40:00.000-07:00

Activities from Session-1 => Blue
Activities from Session-2 => Pink

SQL> CREATE TABLE dl_1(num NUMBER)
SQL> CREATE TABLE dl_2(num NUMBER)

SQL> INSERT INTO dl_1 VALUES(1)
SQL> INSERT INTO dl_2 VALUES(2)

SQL> COMMIT

SQL> UPDATE dl_1 SET num = 10 WHERE num=1

SQL> UPDATE dl_2 SET num = 20 WHERE num=2

SQL> UPDATE dl_2 SET num = 200 WHERE num=2
-- The above UPDATE from Session-1 will wait till Session-2 has released its lock over this row

SQL> UPDATE dl_1 SET num = 100 WHERE num=1
UPDATE dl_1 SET num = 100 WHERE num=1
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
--Here an UPDATE was issued from Session-2. This UPDATE caused Session-2 to wait on Session-1, while Session-1 was already waiting for Session-2. Hence it resulted in a deadlock.

When a deadlock is detected, Oracle choses one of the two sessions as a "Victim" and the statement which caused the deadlock is rolled back.

Locking - Optimistic and Pessimistic

2006-07-09T01:34:00.000-07:00

Locks are mechanisms to regulate concurrent access to a shared resource(like a row in a table, a stored procedure)
Without proper locking, the classic problem of "Lost Updates" might happen.

Eg. for "Lost Updates"
1. In Session-1, User-1 finds that for EmpName=KING, Salary=10000
2. In Session-2, User-2 finds that for EmpName=KING, Salary=10000
3. In Session-1, User-1 updates the Salary for KING as 20000 and issues a commit
4. In Session-2, User-2 updates the Salary for KING as 30000 and issues a commit

Here the lost update has happened in Step-3; The change made by User-1 is lost, even without User-2 knowing that such an update has happened. To solve this issue, we can use 2 types of locking - Pessimistic and Optimistic

** Pessimistic Locking
In pessimistic locking, a lock is established as soon as the user indicates his intention of performing an update.

Doing a SELECT ... FOR UPDATE will cause the row to get locked. Unless a COMMIT is issued from the session where a SELECT...FOR UPDATE was done, the row cannot be updated from another session.

Session-1
------------

SQL> SELECT * FROM scott.emp
1 WHERE EMPNO = 7369
2 FOR UPDATE NOWAIT

Session-2
------------

UPDATE scott.emp SET sal = 1601 WHERE empno=7369 -- will hang
UPDATE scott.emp SET sal = 1601 WHERE empno=7499 -- will not hang

SQL> SELECT * FROM scott.emp
1 WHERE EMPNO = 7369
2 FOR UPDATE NOWAIT;

SELECT * FROM scott.emp
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified

** Optimistic Locking
In Optimistic Locking, locking is deferred up to the point right before the update is performed. Here an optimistic assumption is made that the data would not be updated by another user - hence we wait until the last moment to find out if we are right. The disadvantage of optimistic locking is that if another user has updated the same data, the current user may have to requery the data and start over with his update.

Ways of avoiding Lost Updates using optimistic locking

1. Remembering data that was initially queried up and using it in future updates to see if anything has changed in between

SQL> UPDATE scott.emp
1 SET sal =801
2 WHERE empno=7369
3 AND sal = 800 -- Here the application checks if the old value has been updated in between.

2. Adding a timestamp column to the table so that UPDATE statements can use this for Versioning
CREATE TABLE timestamp_versioning
(deptno NUMBER(2),
dname VARCHAR2(14),
loc VARCHAR2(13),
last_mod TIMESTAMP WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL,
CONSTRAINT timestamp_versioning_dept_pk PRIMARY KEY(deptno)
)

Now every row inserted will bear a timestamp and that can be user to track changes in the record.

3. Using a checksum/Hash computed using one of the following
-- OWA_OPT_LOCK.CHECKSUM
-- DBMS_OBFUSCATION_TOOLKIT.MD5
-- DBMS_CRYPTO.HASH (Uses SHA-1 - Secure Hash Algorithm)

4. Using ORA_ROWSCN (in Oracle 10g)

Why Oracle has separate DBWn and LGWR processes

2006-07-08T08:37:00.000-07:00

LGWR does sequential writes - so it takes lesser time to do its job. It needs to keep a record of what operations were peformed on the data and hence doesn't have a requirement to post the data to the actual data blocks of interest.
However DBWn needs to write data to appropriate data blocks - which means it need to first find where to write and then do the writing job.

Since a sequential write takes lesser time, it is advantageous to have separate LGWR and DBWn processes. The user gets better performance because LGWR does it faster sequential job while DBWn does its slow job in the background.

Processes in Oracle

2006-07-08T08:31:00.000-07:00

Processes in Oracle can be broadly classified into
1. Server Processes
2. Background Processes
3. Slave Processes
______________________________________________________________

1. Server Processes

** Dedicated Server : One to One mapping between connection to the DB and a server process.
-- This is more appropriate in a non-OLTP environment where there could be long-running transactions. With Shared Server mode, these long running transactions could block other transactions.

** Shared Server : Many sessions share a pool of server processes; A connection in this case is made to a Dispatcher - not to a dedicated server process.
-- When using Shared Server configuration, care needs to be taken that sessions do not monopolize the shared server (For example a command like DBMS_LOCK.SLEEP(20) would monopolize the shared server for 20 seconds)
-- Shared Server configuration is useful for OLTP systems characterized by short, frequent transactions.
-- If J2EE connection pooling is already being used, using Shared Server configuration can be a performance inhibitor(pooling feature connecting to yet another pooling feature)
-- Helps reduce the number of OS processes(Unix) / threads(Win).
______________________________________________________________

2. Background Processes

Background processes perform maintenance tasks needed to keep the database running.

** PMON : Process Monitor - Responsible for cleaning up abnormally terminated connections.
** SMON : System Monitor - System Level jobs of "garbage collection" - Cleans up temporary space, Coalesces free space
** RECO : Recovers transaction left in PreparedState because of crash/loss of connection during a 2-Phase-Commit(2PC)
** DBWn : Responsible for writing dirty blocks to disk from the buffer cache
** LGWR : Responsible for flushing the contents of RedoLogBuffer(in SGA) to disk.
_________ This is done for
_________ (a) every 3 seconds
_________ (b) whenever a commit is issued.
_________ (c) when redo log biffer is one third full or contains 1MB of buffered data.
** CKPT : Assists LGWR in the CheckPointing process by updating file headers of data files
** ARCn : Copies Online Redo Log file written by LGWR to another location(better if it is on a different machine) when LGWR fills it up.
_________ Online Redo Logs written by LGWR are used to fix data files in the event of a power failure
_________ Archived Redo Logs written by ARCn are used to fix data files in the event of a hard disk failure

To check background processes
-----------------------------
SELECT paddr, name, description
FROM v$bgprocess
ORDER BY paddr DESC

54668DE8 QMN0 AQ Time Manager Process 0
54668A2C CJQ0 Job Queue Coordinator
54668670 RECO distributed recovery
546682B4 SMON System Monitor Process
54667EF8 CKPT checkpoint
54667B3C LGWR Redo etc.
54667780 DBW0 db writer process 0
546673C4 PMON process cleanup
00 ----- DIAG diagnosibility process
00 ----- FMON File Mapping Monitor Process
00 ----- DBW1 db writer process 1
00 ----- ARC1 Archival Process 1
00 ----- ARC0 Archival Process 0
00 ----- MRP0 Managed Standby Recovery
00 ----- LCK0 Lock Process 0
______________________________________________________________

3. Slave Processes

** I/O Slaves : Used to emulate asynchronous I/O for systems/devices that do not support it.
Eg: For tape devices which do not support async I/O

** Parallel Query Slaves : Helps to break down query plan into multiple pieces which can be executed by multiple processors. Useful when multiple processors are available at disposal and a query needs to be executed over a large table.

Two-Phase Commit Mechanism

2006-07-08T08:02:00.000-07:00

Unlike a transaction on a local database, a distributed transaction involves altering data on multiple databases. Consequently, distributed transaction processing is more complicated, because Oracle must coordinate the committing or rolling back of the changes in a transaction as a self-contained unit. In other words, the entire transaction commits, or the entire transaction rolls back.

Oracle ensures the integrity of data in a distributed transaction using the two-phase commit mechanism. In the prepare phase, the initiating node in the transaction asks the other participating nodes to promise to commit or roll back the transaction. During the commit phase, the initiating node asks all participating nodes to commit the transaction. If this outcome is not possible, then all nodes are asked to roll back.

All participating nodes in a distributed transaction should perform the same action: they should either all commit or all perform a rollback of the transaction. Oracle automatically controls and monitors the commit or rollback of a distributed transaction and maintains the integrity of the global database (the collection of databases participating in the transaction) using the two-phase commit mechanism. This mechanism is completely transparent, requiring no programming on the part of the user or application developer.

The commit mechanism has the following distinct phases, which Oracle performs automatically whenever a user commits a distributed transaction:

1. Prepare phase
The initiating node, called the global coordinator, asks participating nodes other than the commit point site to promise to commit or roll back the transaction, even if there is a failure. If any node cannot prepare, the transaction is rolled back.

2. Commit phase
If all participants respond to the coordinator that they are prepared, then the coordinator asks the commit point site to commit. After it commits, the coordinator asks all other nodes to commit the transaction.

3. Forget phase
The global coordinator forgets about the transaction.

Source: http://www.utexas.edu/its/unix/reference/oracledocs/v92/B10501_01/server.920/a96521/ds_txns.htmhttp://www.utexas.edu/its/unix/reference/oracledocs/v92/B10501_01/server.920/a96521/ds_txns.htm

Trivia on AUTOTRACE

2006-07-08T06:41:00.000-07:00

When AUTOTRACE is enabled, following actions are performed during DML operations
1. A new session is created using the current connection(if secondary session does not already exist)
2. The new session will query V$SESSTAT view to remember initial statistics values for the session where DML is done.
3. DML is run from original session
4. Upon completion of DML, secondary session will query V$SESSTAT once again to find the difference in statistics

The reason behind having a secondary session doing the monitoring job is that, if it were do accomplish the same from the primary session, the statistics reported would include the cost of monitoring as well.

To verify this, query the v$session before and after setting AUTOTRACE ON.

SQL> SELECT username UNAME, sid, serial# SLNO, server, paddr, status
2 FROM v$session
3 WHERE username = 'uName';
4 /

UNAME SID SLNO SERVER __ PADDR __ STATUS
----- --- ---- --------- -------- ------
uName 153 3196 DEDICATED AE4CF614 ACTIVE

SQL> SET AUTOTRACE ON STATISTICS
SQL> SELECT username UNAME, sid, serial# SLNO, server, paddr, status
2 FROM v$session
3 WHERE username = 'uName';
4 /

UNAME SID SLNO SERVER __ PADDR __ STATUS
----- --- ---- --------- -------- --------
uName 151 1511 DEDICATED AE4CF614 INACTIVE
uName 153 3196 DEDICATED AE4CF614 ACTIVE

Statistics
----------------------------------------
...

Difference between a Connection and a Session

2006-07-08T05:58:00.000-07:00

Connection: Physical path from client to Oracle instance.
Options are
1. client --connected to-- dispatcher
2. client --connected to-- dedicated server
3. client --connected to-- Oracle Connection Manager(CMAN)

Session: Logical entity in the instance. Collection of data structures represents a unique sessions. "SQL statements are execeuted", "transactions are committed" and "stored procedures are run" in a session.

A connection may have zero or more sessions.
A session may have have zero or one connection.(zero in cases where OracleNet connection pooling is employed and the session is idle)

Summary of Storage Hierarchies in Oracle

2006-07-05T18:59:00.000-07:00

1. A database is made up of one or more tablespaces

2. A tablespace is made up of one or more datafiles.
These files might be OS managed files in a file system, raw partitions, ASM managed database files or a file on a clustered file system. A tablespace contains segments

3. A segment (TABLE, INDEX,...) is made up of one or more extents. A segment exists in a tablespace, but may have data in multiple data files within that tablespace.

4. An extent is a logically contiguous set of blocks on a disk. An extent exists within a single data file within a tablespace.

5. A block is the smallest unit of allocation in the database. It is the smallest unit of I/O used by a database.

Index Organized Tables

2006-07-03T22:47:00.000-07:00

In an Index Organized Table(IOT), data is stored and sorted by primary key(s).

A table that is accessed exclusively by its Primary Key is a good candidate for IOT.

Eg: Consider the following table
CREATE TABLE heap_org_table_test
(
empname VARCHAR2(50),
empid NUMBER,
deptid NUMBER
PRIMARY KEY(empname, empid, deptid)
)

Here the table consists solely of columns from the Primary Key. The storage overhead in this case would be 100% since the size of the table and the Primary Key Index would be comparable. In such a case it is better to use an IOT.

Apart from the storage benefit, a key aspect about IOT is that it helps reduce the number of Physical I/O and Buffer-Gets. This is because in an IOT, related data is co-located. Hence the database doesn't have to go over multiple blocks to find what it needs.

Differences between Heap Organized and Index Organized Tables
Heap Organized Tables
- disk usage for data + index (assuming the table has an index)
- no guarantee about order of rows - unless an 'order by' is used with a SELECT
Index Organized Tables
- disk usage for data alone
- data is stored in indexed order

An interesting point with IOT is that although it is called a 'Table', Oracle internally uses an INDEX segment to store it. Data for an index-organized table is stored in B-tree index leaves in a sorted order based on the table's primary key so that changes to that data, such as adding, updating, or deleting rows, require an update to the index only. The following query confirms that an index segment is being used.

SELECT dbms_metadata.get_ddl('TABLE', '')
FROM dual

The metadata would indicate that the underlying segment is an index. For example the output of the above query would have a PCTFREE parameter but not a PCTUSED. This is because in an index, data must go where it belongs - unlike in a randomly-organized Table (Heap Organized) where the database has the option of storing the same data on any block - hence PCTUSED would not be relevant with IOT.

Example for IOT creation

CREATE TABLE t
(x INT PRIMARY KEY
y VARCHAR2(25)
z DATE
)
ORGANIZATION INDEX

Data Files

2006-07-03T13:15:00.000-07:00

Any real DataBase has atleast 3 Data Files for the following purposes
1. SYSTEM data
2. SYSAUX data
3. USER data

File System Mechanisms

1. 'Cooked' OS Based file system
Files managed via the OS. Viewble by doing an ls/dir on unix/windows
Typically these are buffered by the OS. ie. the OS will cache info as you read/write.

2. Raw partitions
Raw disk storage available to Oracle as one large chunk. Not part of the filesystem
Not buffered like OS Files(which is good for a DB)

3. ASM - Automatic Storage Management
Database specific FileSystem - feature of 10g

4. Clustered FileSystem
Brings benefits of 'Cooked' OS FileSystem to Clustered files.

File Types in an Oracle Database

2006-07-03T11:47:00.000-07:00

1. Data Files
---> Hold tables, indexes and other segments
2. Temp Files
---> For disk based sorts and temporary storage
3. Control Files
---> Contains info about where dataFiles, tempFiles and redoLogFiles are + Metadata about their state
4. Redo log Files
---> Contains transaction logs
5. Password Files
---> For authenticating users performing admin tasks
6. Parameter Files
---> Where to find Control Files; How bug memory structures should be etc
7. Trace Files
---> Diagnostic files generated in error conditions
8. Alert Files
---> Contains info about "Expected" events
9. Change Tracking Files (10g)
---> Facilitates incremental backup of Oracle data
10. Flashback Log Files (10g)
---> Stores "before images" of database blocks to facilitate 'FLASHBACK DATABASE' command

Dedicated and Shared Server Connections

2006-07-02T14:23:00.000-07:00

Blue Lines - Dedicated Connection
Flow
1. Client initiates request
2. Listener does fork/exec to create new process/thread for dedicated server
3. Client establishes connection to Dedicated server
4. Dedicated server directly interacts with SGA

Pink Lines - Shared Connection
Flow
1. Client initates request
2. Listener replies back with Dispatcher Port number
3. Client connects to Dispatcher
4. Dispatcher places Client-Request on Request queue
5. Shared Server finds the request -processes it - and places response on Response Queue
6. Dispatcher monitors Response Queue - finds the response - and sends it to Client

Difference between a Database and an Instance

2006-07-02T11:17:00.000-07:00

Database: A colection of physical OS Files.
Instance : A set of Oracle background processes + shared memory area.

A database may be mounted and opened by many instances (as in RAC)
An instance will have at most one database associated with it.

Oracle V$views related to a database
1. v$datafile
2. v$logfile
3. v$controlfile

Hash Joins

2006-07-01T04:58:00.000-07:00

A Hash Join is a join which utilizes Hash functions and Hashtables for performing efficient joins.
Consider 2 tables - EMP and DEPT
EMP - 10000 records
DEPT - 500 records

If these 2 tables are being joined on the basis of a common column, say deptno (which appears in both EMP and DEPT), there could be 2 ways of performing the join
1. Through Nested Loops - In a nested loop, the execution path turns out to be somewhat like 2 nested FOR loops.
In other words, for each record in EMP, a suitable record in DEPT needs to be found - if deptno is an indexed column, an INDEX SCAN on DEPT would be performed for each record in EMP.
2. Through the use of Hash Joins - For performing a Hash join, Oracle constructs a Hash over the smaller table (here DEPT). This Hash would contain
information in the format {HashValue -> dept details}

For obtaining the Hashvalue, a Hash Function is used - this is a one-way function (many-to-one mapping from domain to range). To perform the query, Oracle would initially construct an in-memory Hash(if possible an in-memory Hash is constructed, or else it may use the TEMP tablespace); then within the loop over EMP table, this Hashtable would be used to efficiently find out the associated DEPT info.
NOTE: It is possible that multiple values of deptno map to the same Hash value. In this case, once the dept information is found using the Hashtable, Oracle will determine the appropriate row using the deptno.(and that doesn't cost a lot, since with a proper Hashing Function, we dont expect all deptno values to be Hashed to the same value)

Approach #2 (Hashing) is efficient when the number of rows is high in the tables being joined. For lesser number of rows, performing an INDEX SCAN would be efficient since that would avoid the cost of creating the Hash structure.

For an excellent Viewlet explaining Hash joins, visit
http://asktom.oracle.com/~tkyte/hj/hj.html
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:65506569258230

TNSNAMES.ORA, LISTENER.ORA and SQLNET.ORA

2006-07-01T03:31:00.000-07:00

In its most basic form, Oracle uses three files (listener.ora, tnsnames.ora & sqlnet.ora) for network configuration.

Listener.ora
The listerner.ora file contains server side network configuration parameters. It can be found in the ORACLE_HOME/network/admin directory on the server. Here is an example of a listener.ora file from Windows 2000:

# LISTENER.ORA Network Configuration File: C:\Oracle\817\network\admin\listener.ora
# Generated by Oracle configuration tools.

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = 1521))
)
)
(DESCRIPTION =
(PROTOCOL_STACK =
(PRESENTATION = GIOP)
(SESSION = RAW)
)
(ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = 2481))
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = C:\Oracle\817)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = ORCL.WORLD)
(ORACLE_HOME = C:\Oracle\817)
(SID_NAME = ORCL)
)
)

After the listener.ora file is amended the listener should be restarted or reloaded to allow the new configuation to take effect:

C:> lsnrctl stop
C:> lsnrctl start

C:> lsnrctl reload

Tnsnames.ora
The tnsnames.ora file contains client side network configuration parameters. It can be found in the ORACLE_HOME/network/admin or ORACLE_HOME/net80/admin directory on the client. This file will also be present on the server if client style connections are used on the server itself. Basically the listener.ora file is used on a server, and it helps make the connections between client requests and the database instance(s) on the server. The tnsnames.ora file is used on the client, and it points to a particular entry in a listener.ora file on a server.

Here is an example of a tnsnames.ora file from Windows 2000:

# TNSNAMES.ORA Network Configuration File: C:\Oracle\817\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

ORCL.WORLD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ORCL.WORLD)
)
)

INST1_HTTP.WORLD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = SHARED)
(SERVICE_NAME = MODOSE)
(PRESENTATION = http://admin)
)
)

EXTPROC_CONNECTION_DATA.WORLD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)

Sqlnet.ora
The sqlnet.ora file contains client side network configuration parameters. It can be found in the ORACLE_HOME/network/admin or ORACLE_HOME/net80/admin directory on the client. This file will also be present on the server if client style connections are used on the server itself. Here is an example of an sqlnet.ora file from Windows 2000:

# SQLNET.ORA Network Configuration File: C:\Oracle\817\network\admin\sqlnet.ora
# Generated by Oracle configuration tools.

SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)
NAMES.DEFAULT_DOMAIN = WORLD

Source: http://www.oracle-base.com/articles/misc/OracleNetworkConfiguration.php

CUBE

2006-06-29T02:03:00.000-07:00

CUBE applies the aggregation function to all possible combinations.
ie. if you have N fields in the CUBE clause, you would have 2^N levels of aggregation.

Below, we have the same data(as in the ROLLUP example) after applying a CUBE

ROLLUP based Query
------------------
select country, state, mon, sum(sales)
from sales_data
group by rollup (country, state, mon)

CUBE based query
----------------
select country, state, mon, sum(sales)
from sales_data
group by cube (country, state, mon)

CUBE Result Set
---------------

CN | ST | MON | SALES
---+----+-----+------
-- | -- | --- |416000
-- | -- | FEB |216000
-- | -- | JAN |200000
-- | AP | --- | 61000
-- | AP | FEB | 31000
-- | AP | JAN | 30000
-- | CA | --- |151000
-- | CA | FEB | 81000
-- | CA | JAN | 70000
-- | ND | --- | 82000
-- | ND | FEB | 42000
-- | ND | JAN | 40000
-- | NJ | --- |122000
-- | NJ | FEB | 62000
-- | NJ | JAN | 60000
IN | -- | --- |143000
IN | -- | FEB | 73000
IN | -- | JAN | 70000
IN | AP | --- | 61000
IN | AP | FEB | 31000
IN | AP | JAN | 30000
IN | ND | --- | 82000
IN | ND | FEB | 42000
IN | ND | JAN | 40000
US | -- | --- |273000
US | -- | FEB |143000
US | -- | JAN |130000
US | CA | --- |151000
US | CA | FEB | 81000
US | CA | JAN | 70000
US | NJ | --- |122000
US | NJ | FEB | 62000
US | NJ | JAN | 60000

ROLLUP

2006-06-29T00:08:00.000-07:00

ROLLUP enables a SELECT statement to calculate multiple levels of subtotals across a specified group of dimensions. The ROLLUP extension is highly efficient, adding minimal overhead to a query.

ROLLUP creates subtotals at n+1 levels, where n is the number of grouping columns. For instance, if a query specifies ROLLUP on grouping columns of time, region, and department (n=3), the result set will include rows at four aggregation levels.

This queries below perform the same task - only that the second one uses ROLLUP

Query - A
-----------
select s, a, b, c from (
select sum(n) s, a, b, c from test_groupby group by a, b, c
union
select sum(n) s, a, b, null from test_groupby group by a, b, null
union
select sum(n) s, a, null, null from test_groupby group by a, null, null
union
select sum(n), null, null, null from test_groupby
)
order by a, b, c;

Query - B
-----------
select sum(n), a, b, c from test_groupby group by rollup (a, b, c);

What ROLLUP does

Data in SALES table
---------------------
CN | ST | MON | SALES
---+----+-----+------
US | CA | JAN | 70000
US | CA | FEB | 81000
US | NJ | JAN | 60000
US | NJ | FEB | 62000
IN | AP | JAN | 30000
IN | AP | FEB | 31000
IN | ND | JAN | 40000
IN | ND | FEB | 42000

After ROLLUP action

CN | ST | MON | SALES
---+----+-----+------
US | CA | JAN | 70000
US | CA | FEB | 81000
US | CA | --- |151000 <- US, CA all MON
US | NJ | JAN | 60000
US | NJ | FEB | 62000
US | NJ | --- |122000 <- US, NJ all MON
US | -- | --- |273000 <- US all STATES and MON
IN | AP | JAN | 30000
IN | AP | FEB | 31000
IN | AP | --- | 61000 <- IN, AP all MON
IN | ND | JAN | 40000
IN | ND | FEB | 42000
IN | ND | --- | 82000 <- IN, ND all MON
IN | -- | --- |143000 <- IN all STATES and MON
-- | -- | --- |417000 <- Combined - all COUNTRIES, STATES and MON

With Excerpts from:
http://www.adp-gmbh.ch/ora/sql/group_by_rollup.html

Why Bind Parameters improve performance

2006-06-28T01:59:00.000-07:00

When a query is fired, the database tries to parse it and obtain an execution plan. This operation is quite costly and overall performance of your application can be improved if the number of parsing operations is reduced. Bind parameters become useful in such a scenario. When a bind parameter appears in a query, the query-format remains unchanged although the actual bind value may change. Hence reparsing can be avoided with the usage of bind.

To illustrate, consider the following 4 queries

1. select * from employees
where id = 1

2. select * from employees
where id = 2

3. select * from employees
where id = :1 -- With Bind = 3

4. select * from employees
where id = :1 -- With Bind = 4

Run these 4 queries from SQL*Plus and then run the below query.

select name, sharable_mem, executions
from v$db_object_cache
where name like 'select * from employees where %'

Results
-------
select * from employees where id = 1 7480 1
select * from employees where id = 1 1446 0
select * from employees where id = :1 7480 2
select * from employees where id = :1 1447 0
select * from employees where id = 2 7480 1
select * from employees where id = 2 1446 0

From the result set, it can been seen that the Query which had binds has only one entry in v$db_object_cache (with executions=2) - whereas for the ones without binds, there are multiple entries.

DUAL table in Oracle

2006-06-27T22:09:00.001-07:00

1. Dual is a table which is created by oracle along with the data dictionary. It consists of exactly one column whose name is dummy and one record. The value of that record is X.

SQL> desc dual
Name Null? Type
----- ----- -----------
DUMMY _____ VARCHAR2(1)

SQL> select * from dual;

D
-
X

2. The owner of dual is SYS but dual can be accessed by every user. As dual contains exactly one row (unless someone fiddled with it), it is guaranteed to return exactly one row in select statements if a constant expression is selected against dual, such as in - select sysdate from dual;

Although it is possible to delete the one record, or insert additional records, one really should not do that!.

3. DUAL is owned by SYS. SYS owns the data dictionary, therefore DUAL is part of the data dictionary. You are not to
modify the data dictionary via SQL ever -- wierd things can and will happen. We can make many strange things happen
in Oracle by updating the data dictionary. It is neither recommend, supported nor a very good idea.

4. What is the dual table, what is its purpose.

Dual is just a convienence table. It isn't mandatory to use DUAL, you can use any other table with a similar structure(1 column and 1 row). The advantage to dual is that the optimizer understands dual is a special one row, one column table -- when you use it in queries, it uses this knowledge when developing the plan.

5. Why does it contain only one column with datatype varchar2, why not number .

Truly, why no. Why not a date you would ask then. The column, its name, its datatype and even its value are NOT relevant. DUAL exists solely as a means to have a 1 row table we can reliably select from. Thats all.

6. Does it contain one row by default.

Yes, when we build the database, we build dual and put a single row in it.

SVRMGR> select * from dual;
D
-
X
1 row selected.

7 Trivia

SVRMGR> alter database close;
Statement processed.

SVRMGR> select * from dual;
ADDR INDX INST_ID D
-------- ---------- ---------- -
01680288 0 1 X
1 row selected.

closing the database makes this special dual table come into play (there for RMAN to have a dual table to select from even when the database isn't fully up)

8. Recreating a DUAL table which was dropped by SYS user

step 1: revoke all privileges except for CREATE SESSION from this friend.
step 2: connect as sysdba
step 3:

create table dual
(dummy varchar2(1))
storage (initial 1)
/
insert into dual values('X')
/
create public synonym dual for dual
/
grant select on dual to public with grant option
/

Source:
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:1562813956388
http://www.adp-gmbh.ch/ora/misc/dual.html

Queries for obtaining Shared Pool Information

2006-06-27T21:45:00.001-07:00

http://vsbabu.org/oracle/sect13.html

Related Tables :
1. v$shared_pool_reserved
2. v$db_object_cache
3. v$librarycache

Cache-Hits and Cache-Misses

2006-06-27T13:47:00.000-07:00

1. Oracle maintains its own buffer cache inside the system global area (SGA) for each instance. A properly sized buffer cache can usually yield a cache hit ratio over 90%, meaning that nine requests out of ten are satisfied without going to disk.

If a buffer cache is too small, the cache hit ratio will be small and more physical disk I/O will result. If a buffer cache is too big, then parts of the buffer cache will be under-utilized and memory resources will be wasted.

Checking The Cache Hit Ratio

1. Oracle maintains statistics of buffer cache hits and misses. The following query will show you the overall buffer cache hit ratio for the entire instance since it was started:

SELECT (P1.value + P2.value - P3.value) / (P1.value + P2.value)
FROM v$sysstat P1, v$sysstat P2, v$sysstat P3
WHERE P1.name = 'db block gets'
AND P2.name = 'consistent gets'
AND P3.name = 'physical reads'

2. The db_block_buffers parameter in the parameter file determines the size of the buffer cache for the instance. The size of the buffer cache (in bytes) is equal to the value of the db_block_buffers parameter multiplied by the data block size.

3. When Oracle performs a full table scan of a large table, the blocks are read into the buffer cache but placed at the least recently used end of the LRU list. This causes the blocks to be aged out quickly, and prevents one large full table scan from wiping out the entire buffer cache.

SELECT A.file_name, B.phyrds, B.phyblkrd
FROM SYS.dba_data_files A, v$filestat B
WHERE B.file# = A.file_id
ORDER BY A.file_id

4. Spotting I/O Intensive SQL Statements: The v$sqlarea dynamic performance view contains one row for each SQL statement currently in the shared SQL area of the SGA for the instance. v$sqlarea shows the first 1000 bytes of each SQL statement, along with various statistics. Following is a query you can use:

SELECT executions, buffer_gets, disk_reads,
first_load_time, sql_text
FROM v$sqlarea
ORDER BY disk_reads

5. No matter what the hit ratio is, if we are not using all of the buffers that have been allocated, there is no advantage in allocating more. In fact, this could slow us down by forcing more swapping at the OS level.

So we can just check if there are free buffers:

select count(1) from v$bh where status='free';

COUNT(1)
----------
984

Source: http://www.dbspecialists.com/presentations/buffercache.html

Shared Pool

2006-06-27T13:08:00.000-07:00

1. The shared pool portion of the SGA contains the library cache, the dictionary cache, buffers for parallel execution messages, and control structures.

2. The total size of the shared pool is determined by the initialization parameter SHARED_POOL_SIZE. The default value of this parameter is 8MB on 32-bit platforms and 64MB on 64-bit platforms. Increasing the value of this parameter increases the amount of memory reserved for the shared pool.

3. Library Cache: The library cache includes the shared SQL areas, private SQL areas (in the case of a shared server configuration), PL/SQL procedures and packages, and control structures such as locks and library cache handles. Shared SQL areas are accessible to all users, so the library cache is contained in the shared pool within the SGA.

4. Shared SQL Areas and Private SQL Areas: Oracle represents each SQL statement it runs with a shared SQL area and a private SQL area. Oracle recognizes when two users are executing the same SQL statement and reuses the shared SQL area for those users. However, each user must have a separate copy of the statement’s private SQL area. A shared SQL area contains the parse tree and execution plan for a given SQL statement. Oracle saves memory by using one shared SQL area for SQL statements run multiple times, which often happens when many users run the same application.

5. PL/SQL Program Units and the Shared Pool: Oracle processes PL/SQL program units (procedures, functions, packages, anonymous blocks, and database triggers) much the same way it processes individual SQL statements. Oracle allocates a shared area to hold the parsed, compiled form of a program unit. Oracle allocates a private area to hold values specific to the session that runs the program unit, including local, global, and package variables (also known as package instantiation) and buffers for executing SQL. If more than one user runs the same program unit, then a single, shared area is used by all users, while each user maintains a separate copy of his or her private SQL area, holding values specific to his or her session.

6. Individual SQL statements contained within a PL/SQL program unit are processed as
described in the previous sections. Despite their origins within a PL/SQL program
unit, these SQL statements use a shared area to hold their parsed representations and a
private area for each session that runs the statement.

7. Dictionary Cache: The data dictionary is a collection of database tables and views containing reference information about the database, its structures, and its users. Oracle accesses the data dictionary frequently during SQL statement parsing. This access is essential to the continuing operation of Oracle. The data dictionary is accessed so often by Oracle that two special locations in memory are designated to hold dictionary data. One area is called the data dictionary cache, also known as the row cache because it holds data as rows instead of buffers (which hold entire blocks of data). The other area in memory to hold dictionary data is the library cache. All Oracle user processes share these two caches for access to data dictionary information.

Database Buffer Cache

2006-06-27T13:07:00.000-07:00

1. The database buffer cache is the portion of the SGA that holds copies of data blocks read from datafiles. All user processes concurrently connected to the instance, share access to the database buffer cache.

2. Organization of the Database Buffer Cache: The buffers in the cache are organized in two lists: the write list and the least recently used (LRU) list. The write list holds dirty buffers, which contain data that has been modified but has not yet been written to disk. The LRU list holds free buffers, pinned buffers, and dirty buffers that have not yet been moved to the write list. Free buffers do not contain any useful data and are available for use. Pinned buffers are currently being accessed.

3. When an Oracle process accesses a buffer, the process moves the buffer to the most recently used (MRU) end of the LRU list. As more buffers are continually moved to the MRU end of the LRU list, dirty buffers age toward the LRU end of the LRU list.

4. The first time an Oracle user process requires a particular piece of data, it searches for the data in the database buffer cache. If the process finds the data already in the cache (a cache hit), it can read the data directly from memory. If the process cannot find the data in the cache (a cache miss), it must copy the data block from a datafile on disk into a buffer in the cache before accessing the data. Accessing data through a cache hit is faster than data access through a cache miss.

5. Before reading a data block into the cache, the process must first find a free buffer. The process searches the LRU list, starting at the least recently used end of the list. The process searches either until it finds a free buffer or until it has searched the threshold limit of buffers. If the user process finds a dirty buffer as it searches the LRU list, it moves that buffer to the write list and continues to search. When the process finds a free buffer, it reads the data block from disk into the buffer and moves the buffer to the MRU end of the LRU list.

6. If an Oracle user process searches the threshold limit of buffers without finding a free buffer, the process stops searching the LRU list and signals the DBW0 background process to write some of the dirty buffers to disk.

SGA - System Global Area

2006-06-27T11:38:00.000-07:00

A system global area (SGA) is a group of shared memory structures that contain data
and control information for one Oracle database instance. If multiple users are
concurrently connected to the same instance, then the data in the instance’s SGA is
shared among the users. Consequently, the SGA is sometimes called the shared global
area.
An SGA and Oracle processes constitute an Oracle instance. Oracle automatically
allocates memory for an SGA when you start an instance, and the operating system
reclaims the memory when you shut down the instance. Each instance has its own
SGA.
The SGA contains the following data structures:
1. Database buffer cache
2. Redo log buffer
3. Shared pool
4. Java pool
5. Large pool (optional)
6. Streams pool
7. Data dictionary cache
Part of the SGA contains general information about the state of the database and the
instance, which the background processes need to access; this is called the fixed SGA.
No user data is stored here. The SGA also includes information communicated
between processes, such as locking information.

Source: Oracle Database Concepts

Oracle SQL Hints

2006-06-26T11:31:00.000-07:00

1. Oracle comes with an optimizer that promises to optimize a query's execution plan. When this optimizer is really doing a good job, no hints should be required at all. Sometimes, however, the characteristics of the data in the database are changing rapidly, so that the optimizer (or more accuratly, its statistics) are out of date. In this case, a hint could help.

2. Syntax

/*+ hint */
/*+ hint(argument) */
/*+ hint(argument-1 argument-2) */

3. All hints except /*+ rule */ cause the CBO to be used. Therefore, it is good practise to analyze the underlying tables if hints are used (or the query is fully hinted. There should be no schema names in hints. Hints must use aliases if alias names are used for table names.

select /*+ index(emp_alias ix_emp) */ ... from scott.emp emp_alias

4. Hints can be categorized as follows:

* Hints for Optimization Approaches and Goals,
* Hints for Access Paths, Hints for Query Transformations,
* Hints for Join Orders,
* Hints for Join Operations,
* Hints for Parallel Execution,
* Additional Hints

5. Hints for Optimization Approaches and Goals

5.1 ALL_ROWS
One of the hints that 'invokes' the Cost based optimizer
ALL_ROWS is usually used for batch processing or data warehousing systems.
5.2 FIRST_ROWS
One of the hints that 'invokes' the Cost based optimizer
FIRST_ROWS is usually used for OLTP systems.
5.3 CHOOSE
One of the hints that 'invokes' the Cost based optimizer
This hint lets the server choose (between ALL_ROWS and FIRST_ROWS, based on statistics gathered.
5.4 RULE
The RULE hint should be considered deprecated as it is dropped from Oracle9i2.

Source: http://www.adp-gmbh.ch/ora/sql/hints.html

Escape characters in SQL

2006-06-26T11:23:00.000-07:00

1. Escape quotes
Use two quotes for every one displayed. Examples:

SQL> SELECT 'Frank''s Oracle site' AS text FROM DUAL;

TEXT
--------------------
Franks's Oracle site

SQL> SELECT 'A ''quoted'' word.' AS text FROM DUAL;

TEXT
----------------
A 'quoted' word.

SQL> SELECT 'A ''''double quoted'''' word.' AS text FROM DUAL;

TEXT
-------------------------
A ''double quoted'' word.

2. Escape wildcard characters

The LIKE keyword allows for string searches. The '_' wild card character is used to match exactly one character, while '%' is used to match zero or more occurrences of any characters. These characters can be escaped in SQL. Examples:

SELECT name FROM emp
WHERE id LIKE '%/_%' ESCAPE '/';

SELECT name FROM emp
WHERE id LIKE '%\%%' ESCAPE '\';

3. Escape SQL*Plus special characters

When using SQL*Plus, the DEFINE setting can be changed to allow &'s (ampersands) to be used in text:

SET DEFINE ~
SELECT 'Laurel & Hardy' FROM dual;

Source: http://www.orafaq.com/faq/how_does_one_escape_special_characters_when_writing_sql_queries

Difference between IN and EXISTS clause

2006-06-26T10:15:00.000-07:00

The two are processed very very differently.

1. Select * from T1 where x in ( select y from T2 )

is typically processed as:

select *
from t1, ( select distinct y from t2 ) t2
where t1.x = t2.y;

The subquery is evaluated, distinct'ed, indexed (or hashed or sorted) and then
joined to the original table -- typically.

2. As opposed to

select * from t1 where exists ( select null from t2 where y = x )

That is processed more like:

for x in ( select * from t1 )
loop
if ( exists ( select null from t2 where y = x.x )
then
OUTPUT THE RECORD
end if
end loop

It always results in a full scan of T1 whereas the first query can make use of
an index on T1(x).

3. So, when is where exists appropriate and in appropriate?

Lets say the result of the subquery ( select y from T2 )

is "huge" and takes a long time. But the table T1 is relatively small and
executing ( select null from t2 where y = x.x ) is very very fast (nice index on
t2(y)). Then the exists will be faster as the time to full scan T1 and do the
index probe into T2 could be less then the time to simply full scan T2 to build
the subquery we need to distinct on.

Lets say the result of the subquery is small -- then IN is typicaly more
appropriate.

If both the subquery and the outer table are huge -- either might work as well
as the other -- depends on the indexes and other factors.

Source: A wonderful thread at http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:953229842074

When does Oracle choose not to use an index

2006-06-26T10:08:00.000-07:00

This problem normally only arises when the query plan is being generated by the Cost Based Optimizer (CBO). The usual cause is because the CBO calculates that executing a Full Table Scan would be faster than accessing the table via the index. Fundamental things that can be checked are:

1. USER_TAB_COLUMNS.NUM_DISTINCT - This column defines the number of distinct values the column holds.

2. USER_TABLES.NUM_ROWS - If NUM_DISTINCT = NUM_ROWS then using an index would be preferable to doing a FULL TABLE SCAN. As the NUM_DISTINCT decreases, the cost of using an index increase thereby making the index less desirable.

3. USER_INDEXES.CLUSTERING_FACTOR - This defines how ordered the rows are in the index. If CLUSTERING_FACTOR approaches the number of blocks in the table, the rows are ordered. If it approaches the number of rows in the table, the rows are randomly ordered. In such a case, it is unlikely that index entries in the same leaf block will point to rows in the same data blocks.

4. Decrease the INIT.ORA parameter DB_FILE_MULTIBLOCK_READ_COUNT - A higher value will make the cost of a FULL TABLE SCAN cheaper.

Remember that you MUST supply the leading column of an index, for the index to be used (unless you use a FAST FULL SCAN or SKIP SCANNING).

There are many other factors that affect the cost, but sometimes the above can help to show why an index is not being used by the CBO. If from checking the above you still feel that the query should be using an index, try specifying an index hint. Obtain an explain plan of the query either using TKPROF with TIMED_STATISTICS, so that one can see the CPU utilization, or with AUTOTRACE to see the statistics. Compare this to the explain plan when not using an index.

Prevent Oracle from using an Index

2006-06-26T10:06:00.000-07:00

In certain cases, one may want to disable the use of a specific, or all indexes for a given query. Here are some examples:

1. Adding an expression to the indexed column

SQL>select count(*) from t where empno+0=1000;

COUNT(*)
----------
1

Execution Plan
--------------------------------------------- ----- --------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=3)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=1 Bytes=3)

2. Specifying the FULL hint to force full table scan

SQL>select /*+ FULL(t) */ * from t where empno=1000;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO GRADE
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ----------
1000 Victor DBA 7839 20-MAY-03 11000 0 10 JUNIOR

Execution Plan
--------------------------------------------- ----- --------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=41)
1 0 TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=1 Bytes=41)

3. Specifying NO_INDEX hint

SQL>select /*+ NO_INDEX(T) */ count(*) from t where empno=1000;

COUNT(*)
----------
1

Execution Plan
--------------------------------------------- ----- --------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=3)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=1 Bytes=3)

4. Using a function over the indexed column

SQL>select count(*) from t where to_number(empno)=1000;

COUNT(*)
----------
1

Execution Plan
--------------------------------------------- ----- --------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=3)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=1 Bytes=3)

Source: http://www.orafaq.com/faq/how_does_one_prevent_oracle_from_using_an_index

Oracle: Case Statement

2006-06-26T10:00:00.000-07:00

In Oracle 9i, you can use the case statement within an SQL statement. It has the functionality of an IF-THEN-ELSE statement.

The syntax for the case statement is:

CASE expression
WHEN condition_1 THEN result_1
WHEN condition_2 THEN result_2
...
WHEN condition_n THEN result_n
ELSE result END

expression is the value that you are comparing to the list of conditions. (ie: condition_1, condition_2, ... condition_n)

condition_1 to condition_n must all be the same datatype. Conditions are evaluated in the order listed. Once a condition is found to be true, the case statement will return the result and not evaluate the conditions any further.

result_1 to result_n must all be the same datatype. This is the value returned once a condition is found to be true.

Note:

If no condition is found to be true, then the case statement will return the value in the ELSE clause.

If the ELSE clause is omitted and no condition is found to be true, then the case statement will return NULL.

You can have up to 255 comparisons in a case statement. Each WHEN ... THEN clause is considered 2 comparisons.

For Example:

You could use the case statement in an SQL statement as follows:

select table_name,
CASE owner
WHEN 'SYS' THEN 'The owner is SYS'
WHEN 'SYSTEM' THEN 'The owner is SYSTEM'
ELSE 'The owner is another value' END
from all_tables;

The above case statement is equivalent to the following IF-THEN-ELSE statement:

IF owner = 'SYS' THEN
result := 'The owner is SYS';

ELSIF owner = 'SYSTEM' THEN
result := 'The owner is SYSTEM'';

ELSE
result := 'The owner is another value';

END IF;

The case statement will compare each owner value, one by one.

One thing to note is that the ELSE clause within the case statement is optional. You could have omitted it. Let's take a look at the SQL statement above with the ELSE clause omitted.

Your SQL statement would look as follows:

select table_name,
CASE owner
WHEN 'SYS' THEN 'The owner is SYS'
WHEN 'SYSTEM' THEN 'The owner is SYSTEM' END
from all_tables;

With the ELSE clause omitted, if no condition was found to be true, the case statement would return NULL.

Database Clusters

2006-06-26T09:40:00.000-07:00

1. Clusters are groups of one or more tables physically stored together because they share common columns and are often used together. Because related rows are physically stored together, disk access time improves.

2. Like indexes, clusters do not affect application design. Whether a table is part of a cluster is transparent to users and to applications. Data stored in a clustered table is accessed by SQL in the same way as data stored in a nonclustered table.

Eg: the employees and departments table share the department_id column. When you cluster the employees and departments tables, Oracle physically stores all rows for each department from both the employees and departments tables in the same data blocks.

3. Because clusters store related rows of different tables together in the same data blocks, properly used clusters offers these benefits:
* Disk I/O is reduced for joins of clustered tables.
* Access time improves for joins of clustered tables.
* In a cluster, a cluster key value is the value of the cluster key columns for a particular row. Each cluster key value is stored only once each in the cluster and the cluster index, no matter how many rows of different tables contain the value. Therefore, less storage is required to store related table and index data in a cluster than is necessary in nonclustered table format.

For example, in the diagram notice how each cluster key (each department_id) is stored just once for many rows that contain the same value in both the employees and departments tables

"WHERE CURRENT OF" in a Cursor

2006-06-26T09:20:00.000-07:00

If you plan on updating or deleting records that have been referenced by a Select For Update statement, you can use the Where Current Of statement.

The syntax for the Where Current Of statement is either:

UPDATE table_name
SET set_clause
WHERE CURRENT OF cursor_name;

OR

DELETE FROM table_name
WHERE CURRENT OF cursor_name;

The Where Current Of statement allows you to update or delete the record that was last fetched by the cursor.

1) DECLARE
/* Output variables to hold the result of the query: */
2) a T1.e%TYPE;
3) b T1.f%TYPE;
/* Cursor declaration: */
4) CURSOR T1Cursor IS
5) SELECT e, f
6) FROM T1
7) WHERE e <>
8) FOR UPDATE;
9) BEGIN
10) OPEN T1Cursor;
11) LOOP
/* Retrieve each row of the result of the above query into PL/SQL variables: */
12) FETCH T1Cursor INTO a, b;
/* If there are no more rows to fetch, exit the loop: */
13) EXIT WHEN T1Cursor%NOTFOUND;
/* Delete the current tuple: */
14) DELETE FROM T1 WHERE CURRENT OF T1Cursor;
/* Insert the reverse tuple: */
15) INSERT INTO T1 VALUES(b, a);
16) END LOOP;
/* Free cursor used by the query. */
17) CLOSE T1Cursor;
18) END;

Source:
http://www.techonthenet.com/oracle/cursors/current_of.php
http://www-db.stanford.edu/~ullman/fcdb/oracle/or-plsql.html

Data Concurrency - Preventable Phenomena and Isolation Levels

2006-06-26T06:25:00.000-07:00

Three preventable phenomena in Concurrent Transactions are:

* Dirty reads: A transaction reads data that has been written by another transaction that has not been committed yet.
* Nonrepeatable (fuzzy) reads: A transaction rereads data it has previously read and finds that another committed transaction has modified or deleted the data.
* Phantom reads: A transaction re-executes a query returning a set of rows that satisfies a search condition and finds that another committed transaction has inserted additional rows that satisfy the condition.

SQL92 Isolation Levels:

-----------------------
Isol.Lvl D N P
-----------------------
RD_UNCOM Y Y Y
RD_COMMT N Y Y
RPT_READ N N Y
SERLISBL N N N
-----------------------
RD_UNCOM - Read uncommitted
RD_COMMT - Read committed
RPT_READ - Repeatable read
SERLISBL - Serializable
D - Dirty Read
N - Nonrepeatable Read
P - Phantom Read

Oracle offers the read committed and serializable isolation levels, as well as a read-only mode that is not part of SQL92. Read committed is the default.

Cursor Variables - REF CURSOR

2006-06-26T06:14:00.000-07:00

1. Cursor variables are like pointers to result sets. You use them when you want to perform a query in one subprogram, and process the results in a different subprogram (possibly one written in a different language).

2. A cursor variable has datatype REF CURSOR, and you might see them referred to informally as REF CURSORs.

3. Unlike an explicit cursor, which always refers to the same query work area, a cursor variable can refer to different work areas. You cannot use a cursor variable where a cursor is expected, or vice versa.

4. You use cursor variables to pass query result sets between PL/SQL stored subprograms and various clients. PL/SQL and its clients share a pointer to the query work area in which the result set is stored. For example, an OCI client, Oracle Forms application, and Oracle database server can all refer to the same work area.

5. A query work area remains accessible as long as any cursor variable points to it, as you pass the value of a cursor variable from one scope to another. For example, if you pass a host cursor variable to a PL/SQL block embedded in a Pro*C program, the work area to which the cursor variable points remains accessible after the block completes.

6. To create cursor variables, you define a REF CURSOR type, then declare cursor variables of that type. You can define REF CURSOR types in any PL/SQL block, subprogram, or package. In the following example, you declare a REF CURSOR type that represents a result set from the DEPARTMENTS table:

DECLARE
TYPE DeptCurTyp IS REF CURSOR RETURN departments%ROWTYPE;

7. REF CURSOR types can be strong (with a return type) or weak (with no return type).

7.1 Strong REF CURSOR types are less error prone because the PL/SQL compiler lets you associate a strongly typed cursor variable only with queries that return the right set of columns.

7.2 Weak REF CURSOR types are more flexible because the compiler lets you associate a weakly typed cursor variable with any query. Because there is no type checking with a weak REF CURSOR, all such types are interchangeable. Instead of creating a new type, you can use the predefined type SYS_REFCURSOR.

8. Once you define a REF CURSOR type, you can declare cursor variables of that type in any PL/SQL block or subprogram.

DECLARE
TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE; -- strong
TYPE GenericCurTyp IS REF CURSOR; -- weak
cursor1 EmpCurTyp;
cursor2 GenericCurTyp;
my_cursor SYS_REFCURSOR; -- didn't need to declare a new type above

Basic Cursor Operations

2006-06-26T05:49:00.000-07:00

Opening a Cursor

Opening the cursor executes the query and identifies the result set, which consists of all rows that meet the query search criteria. For cursors declared using the FOR UPDATE clause, the OPEN statement also locks those rows.

Fetching with a Cursor

Unless you use the BULK COLLECT clause (discussed in the next section), the FETCH statement retrieves the rows in the result set one at a time. Each fetch retrieves the current row and advances the cursor to the next row in the result set.

DECLARE
my_sal employees.salary%TYPE;
my_job employees.job_id%TYPE;
factor INTEGER := 2;
CURSOR c1 IS
SELECT factor*salary FROM employees WHERE job_id = my_job;
BEGIN
OPEN c1; -- here factor equals 2
LOOP
FETCH c1 INTO my_sal;
EXIT WHEN c1%NOTFOUND;
factor := factor + 1; -- does not affect FETCH
END LOOP;
CLOSE x1;
END;
/

Fetching Bulk Data with Cursors

DECLARE
TYPE NumTab IS TABLE OF employees.employee_id%TYPE;
TYPE NameTab IS TABLE OF employees.last_name%TYPE;
nums NumTab;
names NameTab;
CURSOR c1 IS
SELECT employee_id, last_name
FROM employees
WHERE job_id = 'ST_CLERK';
BEGIN
OPEN c1;
FETCH c1 BULK COLLECT INTO nums, names;
-- Here is where you iterate through the elements in the NUMS and
-- NAMES collections.
NULL;
CLOSE c1;
END;
/

Closing a Cursor

The CLOSE statement disables the cursor, and the result set becomes undefined. Once a cursor is closed, you can reopen it, which runs the query again with the latest values of any cursor parameters and variables referenced in the WHERE clause. Any other operation on a closed cursor raises the predefined exception INVALID_CURSOR.

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

%ROWTYPE - PLSQL Records

2006-06-26T05:37:00.000-07:00

DECLARE
   emp_rec emp%ROWTYPE;
BEGIN
  emp_rec.eno := 1500;
  emp_rec.ename := 'Steven Hill';
  emp_rec.sal := '40000';
-- A %ROWTYPE value can fill in all the row fields.
   INSERT INTO emp VALUES emp_rec;

-- The fields of a %ROWTYPE can completely replace the table columns.
   UPDATE emp SET ROW = emp_rec WHERE eno = 100;
END;
/

Implicit Cursor Attributes

2006-06-26T05:21:00.000-07:00

Implicit cursor attributes return information about the execution of an INSERT, UPDATE, DELETE, or SELECT INTO statement. The values of the cursor attributes always refer to the most recently executed SQL statement. Before Oracle opens the SQL cursor, the implicit cursor attributes yield NULL.

Format: SQL%x

1. %FOUND Attribute: Has a DML Statement Changed Rows?
%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

2. %ISOPEN Attribute: Always FALSE for Implicit Cursors
Oracle closes the SQL cursor automatically after executing its associated SQL statement. As a result, %ISOPEN always yields FALSE.

3. %NOTFOUND Attribute: Has a DML Statement Failed to Change Rows?
%NOTFOUND is the logical opposite of %FOUND

4. %ROWCOUNT Attribute: How Many Rows Affected So Far?
%ROWCOUNT yields the number of rows affected by an INSERT, UPDATE, or DELETE statement, or returned by a SELECT INTO statement.

Exception-1: 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.

Exception-2: The %NOTFOUND attribute is not useful in combination with the SELECT INTO statement:
* If a SELECT INTO statement fails to return a row, PL/SQL raises the predefined exception NO_DATA_FOUND immediately, interrupting the flow of control before you can check %NOTFOUND.
* A SELECT INTO statement that calls a SQL aggregate function always returns a value or a null. After such a statement, the %NOTFOUND attribute is always FALSE, so checking it is unnecessary.

5. %BULK_ROWCOUNT Attribute: Counting Rows Affected by FORALL

CREATE TABLE emp2 AS SELECT * FROM employees;
DECLARE
TYPE NumList IS TABLE OF NUMBER;
depts NumList := NumList(30, 50, 60);
BEGIN
FORALL j IN depts.FIRST..depts.LAST
DELETE FROM emp2 WHERE department_id = depts(j);
-- How many rows were affected by each DELETE statement?
FOR i IN depts.FIRST..depts.LAST
LOOP
dbms_output.put_line('Iteration #' || i || ' deleted ' ||
SQL%BULK_ROWCOUNT(i) || ' rows.');
END LOOP;
END;
/
DROP TABLE emp2;

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

Basic Query Patterns with Examples

2006-06-26T04:26:00.000-07:00

http://dev.fyicenter.com/faq/oracle/oracle_sql_select_query_statements.html

First/Last records in a table – in insertion order

2006-06-26T04:09:00.000-07:00

Since there is no inherent way for you to figure out the order the rows were put into a table, this one is a little trickier. The only way you can positively indicate the order the rows were inserted into the table is if you explicitly mark each record. Sometimes this may be a “DATE_INSERTED” field or it may be some other field that indicates order. In my example, I have created a BEFORE INSERT trigger to populate the Primary Key (x) with a value from a sequence:

create trigger xyz_bi
before insert
on xyz
for each row
declare
pkval number;

begin
select xyz_id.nextval into pkval from dual;

:new.x := pkval;
end;

Therefore, I know my rows were inserted in the order according to X. I can then use my PK value to find out the last 10 rows inserted by:

SQL> select * from (
2 select * from xyz
3 order by x desc
4 )
5 where rownum <>
6 /

X Y Z
---------- ---------- -------------------
156 30 09/15/2005 14:48:42
155 29 09/24/2005 14:48:42
154 28 09/23/2005 14:48:42
153 27 09/22/2005 14:48:42
152 26 09/21/2005 14:48:42
151 25 09/20/2005 14:48:42
150 24 09/19/2005 14:48:42
149 23 09/18/2005 14:48:42
148 22 09/17/2005 14:48:42
147 21 09/16/2005 14:48:42

10 rows selected.

Source: http://marist89.blogspot.com/2005/09/top-n.html

Top-N Queries using RANK() function

2006-06-26T03:58:00.000-07:00

1. For a top-N query you can use two ranking functions: RANK and DENSE_RANK. Both allow you to rank items in a group—for example, finding the top-five employees by salary, which is exactly what we need to achieve.

The difference between RANK() and DENSE_RANK() is that RANK() leaves gaps in the ranking sequence when there are ties. In our case, Scott and Ford tie for second place with a $3,000 salary; Jones' $2,975 salary brings him in third place using DENSE_RANK() but only fourth place using RANK():

SELECT Empno, Ename, Job, Mgr, Sal,
RANK() OVER
(ORDER BY SAL Desc NULLS LAST) AS Rank,
DENSE_RANK() OVER
(ORDER BY SAL Desc NULLS LAST) AS Drank
FROM Emp
ORDER BY SAL Desc NULLS LAST;

2. The NULLS FIRST | NULLS LAST clause determines the position of rows with NULL values in the ordered query.

If the sequence is in descending order, then NULLS LAST implies that NULL values are smaller than non-NULL ones and rows with NULLs will appear at the bottom of the list. If the NULLS FIRST | NULLS LAST clause is omitted, then NULL values are considered larger than any other values and their ordering position depends on the ASC | DESC arguments.

If the ordering sequence is ascending (ASC), then rows with NULLs will appear last; if the sequence is descending (DESC), then rows with NULLs will appear first. NULLs are considered equal to other NULLs and, therefore, the order in which rows with NULLs are presented is nondeterministic.

3. Using RANK() to Obtain a Top-N Query
To obtain a top-N query, use RANK() in a subquery and then apply a filter condition outside the subquery:

SELECT Empno, Ename, Job, Mgr, Hiredate, Sal
FROM
(SELECT Empno, Ename, Job, Mgr, Hiredate, Sal,
RANK() OVER
(ORDER BY SAL Desc NULLS LAST) AS Emp_Rank
FROM Emp
ORDER BY SAL Desc NULLS LAST)
WHERE Emp_Rank <>

4. Ranking functions can be used to operate within groups, too—that is, the rank value gets reset whenever the group changes. This is achieved with a PARTION BY subclause. Here is the syntax to retrieve the top employee by salary per manager group:

SELECT Empno, Ename, Job, Mgr, Hiredate, Sal
FROM
(SELECT Empno, Ename, Job, Mgr, Hiredate, Sal,
RANK() OVER
(PARTITION BY MGR ORDER BY MGR, SAL DESC NULLS LAST) AS Emp_Rank
FROM Emp
ORDER BY MGR, SAL DESC NULLS LAST)
WHERE Emp_Rank = 1;

Source:
http://www.devx.com/gethelpon/10MinuteSolution/16608/0/page/4
http://www.devx.com/gethelpon/10MinuteSolution/16608/0/page/5

PseudoColumns in Oracle

2006-06-26T03:43:00.000-07:00

Pseudocolumns

A pseudocolumn 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.

1. CURRVAL and NEXTVAL

schema.sequence.CURRVAL: returns the current value of a sequence
schema.sequence.NEXTVAL: increments the sequence and returns the next value

(Over a DB Link >> schema.sequence.CURRVAL@dblink and schema.sequence.NEXTVAL@dblink)

2. LEVEL
For each row returned by a hierarchical query, the LEVEL pseudocolumn returns 1 for a root row, 2 for a child of a root, and so on.

SELECT v.employee_id, v.last_name, v.lev
FROM
(SELECT employee_id, last_name, LEVEL lev
FROM employees v
START WITH employee_id = 100
CONNECT BY PRIOR employee_id = manager_id) v
WHERE (v.employee_id, v.lev) IN
(SELECT employee_id, 2 FROM employees);

3. ROWID
For each row in the database, the ROWID pseudocolumn returns a row's address. Rowid values contain information necessary to locate a row.
# They are the fastest way to access a single row.
# They can show you how a table's rows are stored.
# They are unique identifiers for rows in a table.
You should not use ROWID as a table's primary key. If you delete and reinsert a row with the Import and Export utilities, for example, then its rowid may change. If you delete a row, then Oracle may reassign its rowid to a new row inserted later.

4. ROWNUM
For each row returned by a query, the ROWNUM pseudocolumn returns a number indicating the order in which Oracle selects the row from a table or set of joined rows. The first row selected has a ROWNUM of 1, the second has 2, and so on. You can use ROWNUM to limit the number of rows returned by a query, as in this example:

SELECT * FROM employees WHERE ROWNUM <>

If you embed the ORDER BY clause in a subquery and place the ROWNUM condition in the top-level query, then you can force the ROWNUM condition to be applied after the ordering of the rows. For example, the following query returns the 10 smallest employee numbers. This is sometimes referred to as a "top-N query":

SELECT * FROM
(SELECT * FROM employees ORDER BY employee_id)
WHERE ROWNUM <>

5. XMLDATA
Oracle stores XMLType data either in LOB or object-relational columns, based on XMLSchema information and how you specify the storage clause. The XMLDATA pseudocolumn lets you access the underlying LOB or object relational column to specify additional storage clause parameters, constraints, indexes, and so forth.

Source: http://www.utexas.edu/its/unix/reference/oracledocs/v92/B10501_01/server.920/a96540/sql_elements6a.htm

Details on Functions

2006-06-26T03:23:00.000-07:00

1. A function is a subprogram that can take parameters and return a single value.

2. Inside a function, an IN parameter acts like a constant; you cannot assign it a value. An OUT parameter acts like a local variable; you can change its value and reference the value in any way. An IN OUT parameter acts like an initialized variable; you can assign it a value, which can be assigned to another variable.

3. Avoid using the OUT and IN OUT modes with functions. The purpose of a function is to take zero or more parameters and return a single value. Functions should be free from side effects, which change the values of variables not local to the subprogram.

4. NOCOPY
A compiler hint (not directive) that allows the PL/SQL compiler to pass OUT and IN OUT parameters by reference instead of by value (the default). The function can run faster, because it does not have to make temporary copies of these parameters, but the results can be different if the function ends with an unhandled exception.

5. PARALLEL_ENABLE
Declares that a stored function can be used safely in the slave sessions of parallel DML evaluations. The state of a main (logon) session is never shared with slave sessions. Each slave session has its own state, which is initialized when the session begins. The function result should not depend on the state of session (static) variables. Otherwise, results might vary across sessions.

Source: www.stanford.edu/dept/itss/docs/oracle/10g/appdev.101/b10807/13_elems022.htm

OCI -Vs- Precompiler

2006-06-26T03:03:00.000-07:00

1. Oracle Call Interface
The Oracle Call Interface (OCI) is a set of low-level APIs (Application Programming Interface Calls) used to interact with Oracle databases. It allows one to use operations like logon, execute, parse, fetch, etc. OCI programs are normally written in C or C++, although they can be written in almost any programing language.

Unlike with the Oracle Precompilers (like Pro*C and Pro*COBOL), OCI programs are not precompiled

1.1 OCI is superior to Pro*C in the following ways:

* Performance is much better with OCI
* Reduced code size
* Direct access to built-in functions (No intermediate files or substitutions).
* Piecewise Operation on LONG fields (All LONG field problems are solved)
* In Pro*C one cannot dynamically allocate memory to be used as bind variables
* You cannot control the Pro*C precompiler to provide better and more compilable C-code.

1.2 Common problems with OCI:

* OCI code is difficult to write and to maintain

2. Precompiler

A precompiler is a tool that allows programmers to embed SQL statements in high-level source programs like C, C++, COBOL, etc. The precompiler accepts the source program as input, translates the embedded SQL statements into standard Oracle runtime library calls, and generates a modified source program that one can compile, link, and execute in the usual way. Examples are the Pro*C Precompiler for C, Pro*Cobol for Cobol, SQLJ for Java, etc.

Sources:
http://www.orafaq.com/faqoci.htm
http://www.orafaq.com/faq/precompilers

Autonomous Transactions

2006-06-26T02:58:00.000-07:00

Autonomous Transaction is a feature of oracle 8i which maintains the state of its transactions and save it , to affect with the commit or rollback of the surrounding transactions.

Here is the simple example to understand this :-

SQL :> declare
2 Procedure InsertInTest_Table_B
3 is
4 BEGIN
5 INSERT into Test_Table_B(x) values (1);
6 Commit;
7 END ;

8 BEGIN
9 INSERT INTO Test_Table_A(x) values (123);
10 InsertInTest_Table_B;
11 Rollback;
12 END;
13 /

PL/SQL procedure successfully completed.

SQL :> Select * from Test_Table_A;

X
----------
123

SQL :> Select * from Test_Table_B;

X
----------
1

Notice in above pl/sql COMMIT at line no 6 , commits the transaction at line-no 5 and line-no 9. The Rollback at line-no 11 actually did nothing. Commit/ROLLBACK at nested transactions will commit/rollback all other DML transaction before that. PRAGMA AUTONOMOUS_TRANSACTION override this behavior.

Let us the see the following example with PRAGMA AUTONOMOUS_TRANSACTION.

SQL :> declare
2 Procedure InsertInTest_Table_B
3 is
4 PRAGMA AUTONOMOUS_TRANSACTION;
5 BEGIN
6 INSERT into Test_Table_B(x) values (1);
7 Commit;
8 END ;

9 BEGIN
10 INSERT INTO Test_Table_A(x) values (123);
11 InsertInTest_Table_B;
12 Rollback;
13 END;
14 /

PL/SQL procedure successfully completed.

SQL :> Select * from Test_Table_A;
no rows selected

SQL :> Select * from Test_Table_B;
X
----------
1

With PRAGMA AUTONOMOUS_TRANSACTION , the transaction state maintained independently . Commit/Rollback of nested transaction will no effect the other transaction. It is advisable to increase the value of TRANSACTIONS parameter in the INIT parameter file to allow for the extra concurrent transaction .

Source: http://www.samoratech.com/PLSQL/TIPautonomousTrans.htm

UTL_FILE

2006-06-26T02:53:00.000-07:00

1. The Oracle supplied package UTL_FILE can be used to read and write files that are located on the server. It cannot be used to access files locally, that is on the computer where the client is running.

2. The following two procedures show how to use utl_file to write to and read from a file using PL/SQL. It doesn't do very much let alone something useful, but it can be extended.
In order to use it, make sure the utl_file_dir paramter is set:

3. select value from v$parameter where name = 'utl_file_dir';

The value returned is actually the path that you must use in the arguments to utl_file_test_read and utl_file_test_write.

4. utl_file_test_write writes two lines into the file specified with the parameters path and filename.

create or replace procedure utl_file_test_write (
path in varchar2,
filename in varchar2,
firstline in varchar2,
secondline in varchar2)
is
output_file utl_file.file_type;
begin
output_file := utl_file.fopen (path,filename, 'W');

utl_file.put_line (output_file, firstline);
utl_file.put_line (output_file, secondline);
utl_file.fclose(output_file);

--exception
-- when others then null;
end;
/

5. utl_file_test_read reads two lines from the file specified with the parameters path and filename and prints them using dbms_output.

create or replace procedure utl_file_test_read (
path in varchar2,
filename in varchar2)
is
input_file utl_file.file_type;
input_buffer varchar2(4000);
begin
input_file := utl_file.fopen (path,filename, 'R');

utl_file.get_line (input_file, input_buffer);
dbms_output.put_line(input_buffer);
utl_file.get_line (input_file, input_buffer);
dbms_output.put_line(input_buffer);
utl_file.fclose(input_file);

--exception
-- when others then null;
end;
/

6. Creating and writing to a file:

begin
utl_file_test_write (
'/tmp',
'utl_file_test',
'first line',
'second line'
);
end;
/

7. Now, reading from the file:

set serveroutput on size 1000000

begin
utl_file_test_read('/tmp','utl_file_test');
end;
/

Also check in your utl_file_dir that the file was created.

Source: http://www.adp-gmbh.ch/ora/plsql/utl_file.html

Built-in functions/operators for manipulating strings

2006-06-26T02:43:00.000-07:00

The most useful ones are LENGTH, SUBSTR, INSTR, and ||:

* LENGTH(str) returns the length of str in characters.
* SUBSTR(str,m,n) returns a portion of str, beginning at character m, n characters long. If n is omitted, all characters to the end of str will be returned.
* INSTR(str1,str2,n,m) searches str1 beginning with its n-th character for the m-th occurrence of str2 and returns the position of the character in str1 that is the first character of this occurrence.
* str1 || str2 returns the concatenation of str1 and str2.

The example below shows how to convert a string name of the format 'last, first' into the format 'first last':

SUBSTR(name, INSTR(name,',',1,1)+2)
|| ' '
|| SUBSTR(name, 1, INSTR(name,',',1,1)-1)

For case-insensitive comparisons, first convert both strings to all upper case using Oracle's built-in function upper() (or all lower case using lower()).

Source: http://www-db.stanford.edu/%7Eullman/fcdb/oracle/or-faq.html#string

Object-Relational Features of Oracle

2006-06-26T02:39:00.000-07:00

1. Defining Types

CREATE TYPE PointType AS OBJECT (
x NUMBER,
y NUMBER
);

An object type can be used like any other type in further declarations of object-types or table-types. For instance, we might define a line type by:

CREATE TYPE LineType AS OBJECT (
end1 PointType,
end2 PointType
);

CREATE TABLE Lines (
lineID NUMBER,
line LineType
);

2. Constructing Object Values

INSERT INTO Lines
VALUES(27, LineType(
PointType(0.0, 0.0),
PointType(3.0, 4.0)
)
);

3. A type declaration can also include methods that are defined on values of that type. The method is declared by MEMBER FUNCTION or MEMBER PROCEDURE in the CREATE TYPE statement, and the code for the function itself (the definition of the method) is in a separate CREATE TYPE BODY statement.

Methods have available a special tuple variable SELF, which refers to the ``current'' tuple. If SELF is used in the definition of the method, then the context must be such that a particular tuple is referred to.

CREATE TYPE LineType AS OBJECT (
end1 PointType,
end2 PointType,
MEMBER FUNCTION length(scale IN NUMBER) RETURN NUMBER,
PRAGMA RESTRICT_REFERENCES(length, WNDS)
);

Note the ``pragma'' that says the length method will not modify the database (WNDS = write no database state). This clause is necessary if we are to use length in queries.

CREATE TYPE BODY LineType AS
MEMBER FUNCTION length(scale NUMBER) RETURN NUMBER IS
BEGIN
RETURN scale *
SQRT((SELF.end1.x-SELF.end2.x)*(SELF.end1.x-SELF.end2.x) +
(SELF.end1.y-SELF.end2.y)*(SELF.end1.y-SELF.end2.y)
);
END;
END;
/

Notice that the mode of the argument is not given here.

4. Queries to Relations That Involve User-Defined Types

SELECT lineID, ll.line.length(2.0)
FROM Lines ll;

* Note that in order to access fields of an object, we have to start with an alias of a relation name. While lineID, being a top-level attribute of relation LInes, can be referred to normally, in order to get into the attribute line, we need to give relation Lines an alias (we chose ll) and use it to start all paths to the desired subobjects.

* Dropping the ``ll.'' or replacing it by ``Lines.'' doesn't work.

* Notice also the use of a method in a query. Since line is an attribute of type LineType, one can apply to it the methods of that type, using the dot notation shown.

5. Nested Tables
A more powerful use of object types in Oracle is the fact that the type of a column can be a table-type. That is, the value of an attribute in one tuple can be an entire relation.
CREATE TABLE Polygons (
name VARCHAR2(20),
points PolygonType)
NESTED TABLE points STORE AS PointsTable;

Source: http://www-db.stanford.edu/%7Eullman/fcdb/oracle/or-objects.html

Pro*C

2006-06-26T02:16:00.000-07:00

Embedded SQL is a method of combining the computing power of a high-level language like C/C++ and the database manipulation capabilities of SQL. It allows you to execute any SQL statement from an application program. Oracle's embedded SQL environment is called Pro*C.

A Pro*C program is compiled in two steps.
1. First, the Pro*C precompiler recognizes the SQL statements embedded in the program, and replaces them with appropriate calls to the functions in the SQL runtime library. The output is pure C/C++ code with all the pure C/C++ portions intact.
2. Then, a regular C/C++ compiler is used to compile the code and produces the executable.

Source: http://www-db.stanford.edu/~ullman/fcdb/oracle/or-proc.html

Oracle Constraints and Triggers

2006-06-26T02:01:00.000-07:00

1. Constraints are declaractions of conditions about the database that must remain true. These include attributed-based, tuple-based, key, and referential integrity constraints.

2. Triggers are a special PL/SQL construct similar to procedures. However, a procedure is executed explicitly from another block via a procedure call, while a trigger is executed implicitly whenever the triggering event happens. The triggering event is either a INSERT, DELETE, or UPDATE command. The timing can be either BEFORE or AFTER. The trigger can be either row-level or statement-level, where the former fires once for each row affected by the triggering statement and the latter fires once for the whole statement.

3. Deferring Constraint Checking

4. To view a list of all defined triggers, use:
select trigger_name from user_triggers;

5. drop trigger ;

6. alter trigger {disable|enable};

7. Aborting Triggers with Error

create table Person (age int);

CREATE TRIGGER PersonCheckAge
AFTER INSERT OR UPDATE OF age ON Person
FOR EACH ROW
BEGIN
IF (:new.age <>
RAISE_APPLICATION_ERROR(-20000, 'no negative age allowed');
END IF;
END;
.

8. Mutating Table Errors
Sometimes you may find that Oracle reports a "mutating table error" when your trigger executes. This happens when the trigger is querying or modifying a "mutating table", which is either the table whose modification activated the trigger, or a table that might need to be updated because of a foreign key constraint with a CASCADE policy. To avoid mutating table errors:

* A row-level trigger must not query or modify a mutating table. (Of course, NEW and OLD still can be accessed by the trigger.)
* A statement-level trigger must not query or modify a mutating table if the trigger is fired as the result of a CASCADE delete.

Source : http://www-db.stanford.edu/~ullman/fcdb/oracle/or-triggers.html