oracle tips & tricks

Invoking and Executing a Script from OEM

2008-04-25T00:59:00.002-07:00

When including script names in OEM you must carefully include the fully-qualified path for the script: (i.e. /u01/app/oracle/scripts/myscript.sql). Placing custom scripts within OEM is a great way to improve reporting. Here is a quick overview of script execution from OEM:

Create a "new event" from the OEM console
Choose "enable unsolicited event" and then "test unsolicited event"
Enter the script name with the full path.

You can also use the oemevent command to invoke a SQL script from OEM:


root> oemevent /u01/app/oracle/scripts/myscript.sql destination alert "Monitored destination message"

Database Link From Oracle to SQL Server

2008-04-24T17:07:00.000-07:00

After you succeed connecting Oracle with SQL Server probably you want to create database link between it. Oracle heterogeneous services allow you to define a database link between Oracle and SQL Server, as well as links to DB2 and other inferior databases.

Here are complete notes in creating a database link between Oracle and SQL Server.

1. Install Oracle ODBC drivers on the server (your local Oracle database) that will access the remote SQL Server database using the database link.
2. Setup the ODBC connection on the local Oracle database using the Windows ODBC Data Source Administrator
3. Test the ODBC drivers to ensure that connectivity is made to the SQL Server database.
4. Ensure that your global_names parameter is set to False.
5. Configure the Oracle Heterogeneous services by creating an initodbc.ora file within the Oracle database.

6. Modify the Listener.ora file.


        SID_NAME is the DSN for the remote database.
        ORACLE_HOME is the actual Oracle home file path.
        PROGRAM tells Oracle to use heterogeneous services.

        SID_LIST_LISTENER =
        (SID_LIST =
        (SID_DESC =
        (SID_NAME=Cas30C) -- Enter the DSN on this line
        (ORACLE_HOME = c:\oracle10gdb) -- Enter your Oracle home on this line
        (PROGRAM = hsodbc) ) )

7. Modify the Tnsnames.ora file. This is the Oracle database installation accessed by the users to connect to the ODBC drivers


        (DESCRIPTION=
        (ADDRESS_LIST=
        (Address=(PROTOCOL=TCP)
        (HOST=
        -- (Server x)
        (PORT=1521))) -- Enter the port on which the server x Oracle installation
        -- is listening
        (CONNECT_DATA=(SID=Cas30c)) - Enter the DSN name
        (HS=OK) -- Enter this value. It tells Oracle to use hetergeneous services
        )

8. Reload the listener on local Oracle database
9. Create a database link on the local Oracle installation that accesses the heterogeneous connection, which, in turn, connect to SQL Server.
10. Run a SQL Server Select statement from the Oracle installation using the database link.

Accessing Oracle Alert Log via SQL with External Tables

2008-04-24T16:57:00.000-07:00

Starting in Oracle9i you can map external flat files to Oracle tables.
Mapping the Oracle alert log is easy and once defined, all you have to do is query it with standard SQL syntax:

create directory BDUMP as '/u01/app/oracle/admin/mysid/bdump';

create table
   alert_log ( msg varchar2(80) )
organization external (
   type oracle_loader
   default directory BDUMP
   access parameters (
      records delimited by newline
   )
   location('alrt_mysid.log')
)
reject limit 1000;

Now we can easily extract important Oracle alert log information without leaving SQL*Plus:

select msg from alert_log where msg like '%ORA-00600%';

ORA-00600: internal error code, arguments: [17034], [2940981512], [0], [], [], [ ], [], []
ORA-00600: internal error code, arguments: [18095], [0xC0000000210D8BF8], [], [], [], [], []
ORA-00600: internal error code, arguments: [4400], [48], [], [], []

Computing date differences

2008-04-22T03:13:00.000-07:00

One of the confounding problems with Oracle DATE datatypes is the computation of elapsed time.

Oracle supports date arithmetic and you can make expressions like "date1 - date2" to get the difference between the two dates. Once you have the date difference, you can use simple techniques to express the difference in days, hours, minutes or seconds.

To get the values for data differences, you must choose you unit of measurement, and this is done with the data format mask:

It might be tempting to use sophisticated conversion functions to convert a data, but we will see that this is not the most elegant solution:

round(to_number(end-date-start_date))– elapsed days



round(to_number(end-date-start_date)*24)– elapsed hours


round(to_number(end-date-start_date)*1440)– elapsed minutes

How are elapsed time data displayed by default?  To find out, we issue a simple SQL*plus query:

SQL> select sysdate-(sysdate-3) from dual;



SYSDATE-(SYSDATE-3)

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

                  3

Here we see that elapsed times are expressed in days. Hence, we can use easy conversion functions to convert this to hours or minutes:

However, when the minutes are not a whole number, we have the problem of trailing decimal places:

select

   (sysdate-(sysdate-3.111))*1440

from

   dual;


(SYSDATE-(SYSDATE-3.111))*1440

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

                    4479.83333

Of course, we can overcome this with the ROUND function, remembering that we must first convert the DATE datatype to a NUMBER:

select

   round(to_number(sysdate-(sysdate-3.111))*1440)

from

   dual;



ROUND(TO_NUMBER(SYSDATE-(SYSDATE-3.111))*1440)

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

                                          4480

Hence, we can use these functions to convert an elapsed time into rounded elapsed minutes, and place the value inside an Oracle table. In this example, we have a logoff system-level trigger that computes the elapsed session time and places it inside a Oracle STATSPACK user_log extension table:

update

   perfstat.stats$user_log

set

   elapsed_minutes =

   round(to_number(logoff_time-logon_time)*1440)

where

   user = user_id

and

   elapsed_minutes is NULL;

Oracle SQL : Coding a matrix of display values

2008-04-22T03:00:00.000-07:00

Oracle has a fascinating non-ANSI feature called an in-line view. The in-line view allows for a query to be placed into the FROM clause of an SQL statement, where you would normally place a table name.

By performing a SELECT in the FROM clause, we can summarize two columns, and display a third value based on two other columns.

In the example below, we take the employee name (ename) and the department number (deptno) and display the salary of the employee by their department.

prompt Display of Salary by Department

 

SELECT 

   *

FROM  (SELECT

         ename,

         sum(decode(deptno,10,sal)) DEPT10,

         sum(decode(deptno,20,sal)) DEPT20,

         sum(decode(deptno,30,sal)) DEPT30,

         sum(decode(deptno,40,sal)) DEPT40

       FROM

         emp

       GROUP BY

         ename)

ORDER BY 1;

 

Display of Salary by Department

 

ENAME          DEPT10     DEPT20     DEPT30     DEPT40

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

ADAMS                       1100

ALLEN                                  1600

BLAKE                                  2850

CLARK            2450

FORD                        3000

JAMES                                   950

JONES                       2975

KING             5000

MARTIN                                 1250

MILLER           1300

SCOTT                       3000

SMITH                        800

TURNER                                 1500

WARD                                   1250

This type of query is very useful for Oracle data warehouse system where you must display information from a FACT table according to the values of two other columns.

Oracle Package Initialization

2008-03-17T23:53:00.000-07:00

Did you notice that Oracle PL/SQL packages can have an initialization section? The initialization part of a package is run only once, the first time you reference the package. Here is an example:

CREATE PACKAGE emp_actions AS
     /* Declare externally callable subprograms. */
     FUNCTION hire_employee (
        ename  VARCHAR2,
        job    VARCHAR2,
        mgr    REAL,
        sal    REAL,
        comm   REAL,
        deptno REAL) RETURN INT;
  END emp_actions;

  CREATE PACKAGE BODY emp_actions AS
     number_hired INT;  -- visible only in this package

     /* Fully define subprograms specified in package. */
     FUNCTION hire_employee (
        ename  VARCHAR2,
        job    VARCHAR2,
        mgr    REAL,
        sal    REAL,
        comm   REAL,
        deptno REAL) RETURN INT IS
        new_empno INT;
     BEGIN
        SELECT empno_seq.NEXTVAL INTO new_empno
        FROM dual;
        INSERT INTO emp VALUES (new_empno, ename, job,
           mgr, SYSDATE, sal, comm, deptno);
        number_hired := number_hired + 1;
        RETURN new_empno;
     END hire_employee;

  BEGIN  -- initialization part starts here
     INSERT INTO emp_audit
     VALUES (SYSDATE, USER, 'EMP_ACTIONS');
     number_hired := 0;
  END emp_actions;

The initialization part of a package is run just once, the first time you reference the package. So, in the last example, only one row is inserted into the database table emp_audit. Likewise, the variable number_hired is initialized only once.

Every time the procedure hire_employee is called, the variable number_hired is updated. However, the count kept by number_hired is session specific. That is, the count reflects the number of new employees processed by one user, not the number processed by all users.

Using rsync to Copy Oracle Home Directories

2008-03-10T23:17:00.000-07:00

If you're using UNIX for your ORACLE database server, it is possible to copy the Oracle Database Software (the Oracle Home directory) from one server to another using rsync.

Here is the example rsync syntax to copy the Oracle Home from one server (svr1) to another server (svr2). This process will ensure that all symbolic links and files owned by root get copied over correctly. Login to the source database server (svr1) as the root user account and run the following:


[root@svr1 ~]# rsync -auvzpogl -e ssh /u01/app/oracle/product/10.2.0/db_1/ svr2:/u01/app/oracle/product/10.2.0/db_1/

Sending E-Mail in PL/SQL

2008-03-10T23:12:00.000-07:00

Here some example code you can use for sending email from within PL/SQL

CREATE OR REPLACE PROCEDURE SEND_MAIL_TCP (
    msg_from     VARCHAR2 := 'sender@testing.com'
  , msg_to       VARCHAR
  , msg_subject  VARCHAR2 := 'E-Mail message from your database'
  , msg_text     VARCHAR2 := ''
)
IS
  c   UTL_TCP.CONNECTION;
  rc  INTEGER;
BEGIN
  c  := UTL_TCP.OPEN_CONNECTION('localhost', 25);       -- open the SMTP port 25 on local machine
  rc := UTL_TCP.WRITE_LINE(c, 'HELO localhost');
  rc := UTL_TCP.WRITE_LINE(c, 'MAIL FROM: '||msg_from);
  rc := UTL_TCP.WRITE_LINE(c, 'RCPT TO: '||msg_to);
  rc := UTL_TCP.WRITE_LINE(c, 'DATA');                  -- Start message body
  rc := UTL_TCP.WRITE_LINE(c, 'Subject: '||msg_subject);
  rc := UTL_TCP.WRITE_LINE(c, '');
  rc := UTL_TCP.WRITE_LINE(c, msg_text);
  rc := UTL_TCP.WRITE_LINE(c, '.');                     -- End of message body
  rc := UTL_TCP.WRITE_LINE(c, 'QUIT');
  UTL_TCP.CLOSE_CONNECTION(c);                          -- Close the connection
EXCEPTION
  WHEN others THEN
    RAISE_APPLICATION_ERROR(-20000, 'Unable to send e-mail message from PL/SQL routine.');
END;

Another Oracle apps DBA Interview Questions (with answer)

2008-02-19T00:49:00.000-08:00

Questions:

What happens if the ICM goes down?
How will you speed up the patching process?
How will you handle an error during patching?
Provide a high-level overview of the cloning process and post-clone manual steps.
Provide an introduction to AutoConfig. How does AutoConfig know which value from the XML file needs to be put in which file?
Can you tell me a few tests you will do to troubleshoot self-service login problems? Which profile options and files will you check?
What could be wrong if you are unable to view concurrent manager log and output files?
How will you change the location of concurrent manager log and output files?
If the user is experiencing performance issues, how will you go about finding the cause?
How will you change the apps password?
Provide the location of the DBC file and explain its significance and how applications know the name of the DBC file.

Answers:

All the other managers will keep working. ICM only takes care of the queue control requests, which means starting up and shutting down other concurrent managers.
- You can merge multiple patches.
- You can create a response file for non-interactive patching.
- You can apply patches with options (nocompiledb, nomaintainmrc, nocompilejsp) and run these once after applying all the patches.
Look at the log of the failed worker, identify and rectify the error and restart the worker using adctrl utility.
Run pre-clone on the source (all tiers), duplicate the DB using RMAN (or restore the DB from a hot or cold backup), copy the file systems and then run post-clone on the target (all tiers).
Manual steps (there can be many more):
- Change all non-site profile option values (RapidClone only changes site-level profile options).
- Modify workflow and concurrent manager tables.
- Change printers.
AutoConfig uses a context file to maintain key configuration files. A context file is an XML file in the $APPL_TOP/admin directory and is the centralized repository.
When you run AutoConfig it reads the XML files and creates all the AutoConfig managed configuration files.
For each configuration file maintained by AutoConfig, there exists a template file which determines which values to pick from the XML file.
- Check guest user/password in the DBC file, profile option guest user/password, the DB.
- Check whether apache/jserv is up.
- Run IsItWorking, FND_WEB.PING, aoljtest, etc.
Most likely the FNDFS listener is down. Look at the value of OUTFILE_NODE_NAME and LOGFILE_NODE_NAME in the FND_CONCURRENT_REQUESTS table. Look at the FND_NODES table. Look at the FNDFS_ entry in tnsnames.ora.
The location of log files is determined by parameter $APPLCSF/$APPLLOG and that of output files by $APPLCSF/$APPLOUT.
- Trace his session (with waits) and use tkprof to analyze the trace file.
- Take a statspack report and analyze it.
- O/s monitoring using top/iostat/sar/vmstat.
- Check for any network bottleneck by using basic tests like ping results.
- Use FNDCPASS to change APPS password.
- Manually modify wdbsvr.app/cgiCMD.dat files.
- Change any DB links pointing from other instances.
- Location: $FND_TOP/secure directory.
- Significance: Points to the DB server amongst other things.
- The application knows the name of the DBC file by using profile option "Applications Database Id."

Five DBA Best Practices

2008-02-19T00:17:00.000-08:00

Multiple Oracle Homes
My favorite best practice is the one about multiple Oracle Homes. Here it how it goes. When applying a patch or a patchset, I recommend against applying to the existing Oracle Home. Instead, I suggest creating a new Oracle Home, and apply the patches there.

I create the first Oracle Home at /app/oracle/db_1, for instance. When a patch comes out, I install the whole Oracle software in a different home -- /app/oracle/db_2 -- and then apply the patch there. During the process of installation and patch application, the database is up and running, as it runs off the home /db_1. When the outage window comes, all I have to do is to shut down Oracle; change Oracle Home to db_2 and bring the database up. If there is a problem, I can reset the Oracle Home back to the old one.

So, here is the conventional approach:

1. Shut down the database
2. Apply patch to the Oracle Home
3. Start the database
4. In case of problems:
5. Shut down the database
6. Roll back the patch
7. Start the database

Steps 2 and 6 could take as much as three hours depending on the amount of patching. The database is down during these times.

In the new approach:

1. Install new Oracle Home
2. Apply the patch to the new Home
3. Shut down the database
4. Change Oracle Home to the new location
5. Start the database
6. In case of problems:
7. Shut down the database
8. Change Oracle Home to the old one
9. Start the database

The database is down only during steps 4 and 8, which takes a couple of minutes at the most, not hours.

So, here are the advantages:

The downtime is significantly reduced, to one 60th of the original time.
The risk is reduced significantly, as there is no rolling back a patch; you just go to the older version.
You can perform a "diff" on these two homes to see what changed. You can see the differences across multiple homes as well.
You can take several databases running on the same server to the new Oracle Home one by one.
You can see the various Oracle Homes and what patch level they are on using the inventory.

The only negative part is the space consumption -- you need space for two Oracle Homes. But considering a typical Oracle Home takes about 4 GB or less, this aspect of the suggestion is trivial.

Set audit trail to DB
Set the audit trail to DB by placing the parameter AUDIT_TRAIL = DB in the initialization parameter file during the database creation. Setting this parameter does not start the auditing, because an explicit AUDIT command must be given on the object. But the parameter must be set to a value other than FALSE (the default) for the command to take effect. Being a non-dynamic parameter, the database must be bounced to change the value of AUDIT_TRAIL. To save the trouble and avoid an outage, always set the value to DB, even if you never intend to audit anything. It does not break anything and you will always be ready to audit when the time comes.

Don't use .log
Don't use .log as the extension of redo logs. Someone may run a script to remove all the log files assuming they are redundant and you will end up losing the online redo logs as well, forcing a database recovery. Instead, name them with extension "redo" or "rdo."

Preview RMAN Restore
Preview RMAN Restore to identify all the various backup pieces that will be used in the recovery process without doing an actual recovery. This eliminates any surprises from missing pieces during an actual recovery process.

Create a new Oracle user for clients running on the same server as the DB
The Oracle Database server software also contains the client piece, which allows the clients to connect to the database on the same server. But as a best practice do not use the same user or the software; use a new one. For instance, if "oracle" is the user to install Oracle software, create a new user called, say, "oraapp" and install the client-only software using that user. The user "oraapp" should not be part of the dba or the oinstall group; so this user can't log on to the database as sysdba. Create a new group called "appgrp" and assign the user oraaap to this group. All the application users on the box should also be part of the appgrp group. This way they can use the sqlplus, sqlldr and other executables on the server, but be able to connect as sysdba.

The common practice is to use the client software in the same user as the database software owner; but starting with 10.2, Oracle has changed the security policy that takes away the global execution permissions from the Oracle Home. So the only option is to let app users be part of the dba group or change the permissions on Oracle Home -- both make the database extremely vulnerable.

dbms_backup_restore Package

2008-02-15T04:42:00.000-08:00

The dbms_backup_restore package is used as a PL/SQL command-line interface for replacing native RMAN commands, and it has very little documentation.

The Oracle docs note how to install and configure the dbms_backup_restore package:

“The DBMS_BACKUP_RESTORE package is an internal package created by the dbmsbkrs.sql and prvtbkrs.plb scripts. This package, along with the target database version of DBMS_RCVMAN, is automatically installed in every Oracle database when the catproc.sql script is run. This package interfaces with the Oracle database server and the operating system to provide the I/O services for backup and restore operations as directed by RMAN.”

The docs also note that “The DBMS_BACKUP_RESTORE package has a PL/SQL procedure to normalize filenames on Windows NT platforms.”

Oracle DBA John Parker gives this example of dbms_backup_restore to recover a controlfile:

declare
devtype varchar2(256);
done boolean;
begin
devtype:=dbms_backup_restore.deviceallocate( type=>'sbt_tape',
params=>'ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=rdcs,OB2BARLIST=ORA_RDCS_WEEKLY)',
ident=>'t1');
dbms_backup_restore.restoresetdatafile;
dbms_backup_restore.restorecontrolfileto('D:\oracle\ora81\dbs\CTL1rdcs.ORA');
dbms_backup_restore.restorebackuppiece(
'ORA_RDCS_WEEKLY.dbf', DONE=>done );
dbms_backup_restore.restoresetdatafile;
dbms_backup_restore.restorecontrolfileto('D:\DBS\RDCS\CTL2RDCS.ORA');
dbms_backup_restore.restorebackuppiece(
'ORA_RDCS_WEEKLY.dbf', DONE=>done );
dbms_backup_restore.devicedeallocate('t1');
end;

Here are some other examples of using dbms_backup_restore:

DECLARE
devtype varchar2(256);
done boolean;
BEGIN
devtype := dbms_backup_restore.DeviceAllocate (type => '',ident => 'FUN');
dbms_backup_restore.RestoreSetDatafile;
dbms_backup_restore.RestoreDatafileTo(dfnumber => 1,toname => 'D:\ORACLE_BASE\datafiles\SYSTEM01.DBF');
dbms_backup_restore.RestoreDatafileTo(dfnumber => 2,toname => 'D:\ORACLE_BASE\datafiles\UNDOTBS.DBF');
--dbms_backup_restore.RestoreDatafileTo(dfnumber => 3,toname => 'D:\ORACLE_BASE\datafiles\MYSPACE.DBF');
dbms_backup_restore.RestoreBackupPiece(done => done,handle => 'D:\ORACLE_BASE\RMAN_BACKUP\MYDB_DF_BCK05H2LLQP_1_1', params => null);
dbms_backup_restore.DeviceDeallocate;
END;
/

--restore archived redolog
DECLARE
devtype varchar2(256);
done boolean;
BEGIN
devtype := dbms_backup_restore.DeviceAllocate (type => '',ident => 'FUN');
dbms_backup_restore.RestoreSetArchivedLog(destination=>'D:\ORACLE_BASE\achive\');
dbms_backup_restore.RestoreArchivedLog(thread=>1,sequence=>1);
dbms_backup_restore.RestoreArchivedLog(thread=>1,sequence=>2);
dbms_backup_restore.RestoreArchivedLog(thread=>1,sequence=>3);
dbms_backup_restore.RestoreBackupPiece(done => done,handle => 'D:\ORACLE_BASE\RMAN_BACKUP\MYDB_LOG_BCK0DH1JGND_1_1', params => null);
dbms_backup_restore.DeviceDeallocate;
END;
/

Creating Oracle Database Link

2008-02-13T16:45:00.000-08:00

Oracle has invested heavily in distributed database technology and the creation of a database link is very straightforward. You specify the database link name, the remote user to connect to, the password for the remote user and the TNS service name for the database link connection:

create public database link
  mylink
connect to
  remote_username
identified by
  mypassword
using 'tns_name';

You can also create a database link to non-Oracle databases, here are steps for a database link to MySQL:

Your first step is having installed the TRANSPARENT GATEWAY (it comes in as of the options when you install Oracle).
You must have a user in the SQL Server.

In the directory \tg4msql\admin look for the file inittg4msql.ora and have the following options:

HS_FDS_CONNECT_INFO="SERVER=name_server;DATABASE=name_db"
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=user_sqlserver
HS_FDS_RECOVERY_PWD=pass_user_sqlserver

Configure the listener and add the following

(SID_DESC =
(PROGRAM = tg4msql)
(SID_NAME = MSQL)
(ORACLE_HOME = C:\oracle1)
)
)

and on tnsnames.ora add the following

MSQL=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = servidor)(PORT = 1521))
)
(CONNECT_DATA =
(SID = MSQL)
)
(HS=OK)
)

where HS set up as a heterogonous service

Create a link to sql server.

create database link msql connect to user_sqlserver identified by password using 'MSQL';

You can now use the database link to a foreign database:
```
select * from table@msql
```

Connect Oracle to SQL Server

2008-02-12T21:55:00.000-08:00

In order to connect to SQL*Server from Oracle, you must do several things:

Install ODBC drivers for the SQL*Server database. The drivers are installed on your laptop or on the server that contains the Oracle code tree.
Setup the ODBC connection on your laptop using the Windows ODBC Data Source Administrator
Test the ODBC drivers to ensure that connectivity is made to the non-Oracle database.
Ensure the Global_names parameter is set to False in the Oracle database.
Configure the Hetergeneous services. This consists of creating an initodbc.ora file within the Oracle
Oracle database installation.
Modify the Listener.ora file on the database installation to connect to the Oracle instance and ODBC drivers.
Modify the Tnsnames.ora file to point to the proper code tree.
Reload the listener
Create a database link on the Oracle installation.
Run a Select statement for the Oracle installation using the database link.

Finding Tables References Inside PL/SQL Store Procedure

2008-02-12T16:06:00.000-08:00

The Oracle data dictionary tracks the object types referenced in PL/SQL with the dba_dependencies view. To track the dependency among packages and tables, try this dictionary query:

select
  referenced_owner,
  referenced_name,
  referenced_type
from
  dba_dependencies
where
  name= 'MY_STORE_PROC'
and
  owner = 'SCOTT'
order by
  referenced_owner, referenced_name, referenced_type;

you can also select the referenced_type, ie. tables

Oracle listener lsnrctl command

2008-02-12T03:56:00.000-08:00

Here are lsnrctl command :

C:\>lsnrctl

LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 12-PEB-2008 18:56:26

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> help
The following operations are available
An asterisk (*) denotes a modifier or extended command:

start               stop                status
services            version             reload
save_config         trace               change_password
quit                exit                set*
show*

LSNRCTL>

The following commands are used to manage the listener:

start – Starts the listener with the name specified, otherwise LISTENER will be used. For Windows systems, the listener can also be started from the Control Panel.
stop – Stops the listener. For Windows systems, the listener can also be stopped from the Control Panel.
status – Provides status information about the listener, including start date, uptime, and trace level.
services – Displays each service available, along with the connection history.
version – Displays the version information of the listener.
reload – Forces a read of the configuration file in order for new settings to take effect without stopping and starting the listener.
save_config – Creates a backup of the existing listener.ora file and saves changes to the current version.
trace – Sets the trace level to one of the following – OFF, USER, ADMIN, or SUPPORT.
spawn – Spawns a program that runs with an alias in the listener.ora file.
dbsnmp_start – Starts the DBSNMP subagent.
dbsnmp_stop – Stops the DBSNMP subagent.
dbsnmp_status – Displays the status of the DBSNMP subagent.
change_password – Sets a new password for the listener.
quit and exit – Exits the utility.
set – Changes the value of any parameter. Everything that can be shown can be set.
show – Displays current parameter settings.

Preventing Oracle DDL Unauthorized Schema Changes

2008-02-11T22:45:00.000-08:00

One of the biggest problems in Oracle are "unexpected" schema changes. In production environments changes are often allowed only during special maintenance times, and changes such as re-analyzing schema statistics and DDL to change table/index structures must be prohibited at other times.

This can be tracked with an Oracle DDL trigger, but the DBA can disable DDL at the object level with a simple script to "disable table lock" syntax:

alter table mbsdba.t_dept disable table lock;

This script could be used to lock all tables within a schema:


connect sys/xxx as sysdba;
spool runme.sql
select "alter table "||owner||"."||table_name||" disable table lock;"
from
dba_tables
where
owner = "mbsdba";

spool off;
@runme.sql

While locked, any attempted schema changes will be rejected

SQL>ALTER TABLE mbsdba.t_dept ADD (NEWCOL NUMBER);

ALTER TABLE mbsdba.t_dept
*

ERROR at line 1:
ORA-00069: cannot acquire lock-- table locks disabled for mbsdba.t_dept

Oracle SQL Sort Merge Join

2008-02-11T17:53:00.000-08:00

The use of a sort merge join in Oracle SQL is quite common, especially in cases where there are missing join predicate against one of the tables or a missing index.
In a sort merge join, Oracle must perform full scans on the target tables, sort the keys and join the rows together.
It's important not to confuse a merge join with a merge join cartesian, which is a special case which is usually avoided.
The Oracle docs note these hints for merge joins:

Sort merge join - Force a sort merge join (use_merge) The use_merge hint forces a sort merge join.
Merge anti join (merge_aj) - Transforms a NOT IN subquery into a merge anti-join.
Merge semi-join (merge_sj) - The merge_sj hint is placed into an EXISTS subquery; This converts the subquery into a special type of merge join between t1 and t2 that preserves the semantics of the subquery. That is, even if there is more than one matching row in t2 for a row in t1, the row in t1 is returned only once.
Turn off sort merge join (_sortmerge_inequality_join_off) - This hidden parameter will disable a sort merge join in cases where the predicate is an inequality.

ORA-00918: column ambiguously defined

2008-02-07T19:17:00.000-08:00

The Oracle docs note this on the ora-00918 error*:

ORA-00918 column ambiguously defined

    Cause: A column name used in a join exists in more than one table and is thus referenced ambiguously. In a join, any column name that occurs in more than one of the tables must be prefixed by its table name when referenced. The column should be referenced as TABLE.COLUMN or TABLE_ALIAS.COLUMN. For example, if tables EMP and DEPT are being joined and both contain the column DEPTNO, then all references to DEPTNO should be prefixed with the table name, as in EMP.DEPTNO or E.DEPTNO.
    
    Action: Prefix references to column names that exist in multiple tables with either the table name or a table alias and a period (.), as in the examples above.

*As a note, the ORA-00918 error does not exist in Oracle 10g, according to the Oracle documentation

When ORA-00918 is thrown, you have a column which has been ambiguously defined. If a column name in a join is referenced ambiguously, it exists in multiple tables.

Column names which occur in multiple tables should be prefixed when it is referenced by its table name.
Columns must be referenced as TABLE.COLUMN or TABLE_ALIAS.COLUM . Oracle documentation which reference ORA-00918 give the following example:
if tables EMP and DEPT are being joined and both contain the column DEPTNO, then all references to DEPTNO should be prefixed with the table name, as in EMP.DEPTNO or E.DEPTNO.

To correct ORA-00918, references should be prefixed to column names existing in multiple tables (either with the table name or table alias and a period)

ORA-00904: STRING: invalid identifier tips

2008-02-07T19:00:00.000-08:00

The Oracle docs note this on the ora-00904 error*:

ORA-00904 string: invalid identifier
    Cause: The column name entered is either missing or invalid.

    Action: Enter a valid column name. A valid column name must begin with a letter, be less than or equal to 30 characters, and consist of only alphanumeric characters and the special characters $, _, and #. If it contains other characters, then it must be enclosed in double quotation marks. It may not be a reserved word.

*According to Oracle documentation, ORA-00904 does not occur in Oracle 10g

When ORA-00904 occurs, you must enter a valid column name as it is either missing or the one entered is invalid.

To avoid ORA-00904, column names cannot be a reserved word, and must contain these four criteria to be valid:

begin with a letter
be less than or equal to twenty characters
consist only of alphanumeric and the special characters ($_#); other characters need double quotation marks around them

Another important factor in correcting ORA-00904 is remembering to run catproc.sql

You can also check your trace file to find the particular error which is causing the ORA-00904 to occur.

ORA-00304: requested instance_number is busy

2008-02-07T18:40:00.000-08:00

The Oracle docs note this on the ora-00304 error:

ORA-00304: requested INSTANCE_NUMBER is busy
 Cause: An instance tried to start by using a value of the initialization parameter INSTANCE_NUMBER that is already in use.
 Action: Either a) specify another INSTANCE_NUMBER, b) shut down the running instance with this number c) wait for instance recovery to complete on the instance with this number.

You will see ORA 00304 if an INSTANCE_NUMBER parameter value is being used at the time of initializing an Oracle instance
To resolve ORA-00304, you may try one of the three options:

Use a different INSTANCE_NUMBER
In the initialization file, modify the initialization of the INSTANCE_NUMBER
Shut down the instance number causing the ORA-00304 error
On the instance with the ORA-00304, allow recovery to conclude

ORA-00984: Column Not Allowed Here

2008-02-04T04:44:00.000-08:00

The Oracle docs note this on the ora-00984 error *

ORA-00984 column not allowed here
    Cause: A column name was used in an expression where it is not permitted, such as in the VALUES clause of an INSERT statement.
    Action: Check the syntax of the statement and use column names only where appropriate.

An ORA-00984 will be thrown if a column name (like in the VALUES clause of an INSERT statement), is used in an expression where it is not permitted. You may have used a column name in an expression where it is not permitted. Typically, ORA-00984 occurs while including a column name in the VALUES clause of an INSERT statement.

To correct ORA-00984, you simply need to view the syntax of the SQL statement and only use column names where they are appropriate.
You may also find it appropriate to include a character value, in the INSERT statement, instead of the column name.

*Note: ORA-00984 error does not exist in Oracle 10g, according to the Oracle documentation.

Getting Table Details with SQL*Plus

2008-01-27T19:22:00.000-08:00

You can use SQL*Plus to provide the following details about a table:

Column Details
PRIMARY KEY
INDEXES
FOREIGN KEYS
CONSTRAINTS
ROWCOUNT
Other Tables That REFER to this Table
PARTITIONED COLUMNS
PARTITIONS
TRIGGERS
DEPENDANTS

Use the following code in SQL*Plus to provide this information:

      SET AUTOTRACE OFF
     SET TIMING OFF
     COLUMN COMMENTS FORMAT A50
     COLUMN column_name FORMAT A35
     COLUMN Data_Type FORMAT A15
     COLUMN DATA_DEFAULT FORMAT A20
     COLUMN "PK Column" FORMAT A35
     COLUMN "FK Column" FORMAT A20

     UNDEF Owner
     ACCEPT Owner PROMPT 'Enter Owner :'

     UNDEF Table_Name
     ACCEPT Table_Name PROMPT 'Enter Table Name :'


     SET HEADING OFF

     PROMPT
     PROMPT Comments for Table &Table_Name.
     SELECT COMMENTS
     FROM ALL_TAB_COMMENTS
     WHERE TABLE_NAME = UPPER('&Table_Name.')
     AND Owner = UPPER('&Owner.') ;

     SET HEADING ON

     PROMPT
     PROMPT Column Details for Table &Table_Name.

     SELECT
     ROWNUM "Sr No", T.COLUMN_NAME , T.Data_Type , T.DATA_LENGTH,
     DECODE(T.Nullable, 'N' , 'NOT NULL' , 'Y', ' ') NULLABLE , T.Data_Default , C.Comments
     FROM
     ALL_TAB_COLS T , All_Col_Comments C
     WHERE
     T.OWNER = C.OWNER
     AND T.TABLE_NAME = C.TABLE_NAME
     AND T.COLUMN_NAME = C.COLUMN_NAME
     AND T.TABLE_NAME = UPPER('&Table_Name.')
     AND T.Owner = UPPER('&Owner.') ;


     PROMPT
     PROMPT PRIMARY KEY for Table &Table_Name.

     select COLUMN_NAME
     FROM ALL_CONS_COLUMNS
     WHERE TABLE_NAME = UPPER('&Table_Name.')
     AND Owner = UPPER('&Owner.')
     AND CONSTRAINT_NAME = ( SELECT CONSTRAINT_NAME
     FROM ALL_CONSTRAINTS
     WHERE TABLE_NAME = UPPER('&Table_Name.')
     AND CONS! TRAINT_T YPE = 'P'
     AND Owner = UPPER('&Owner.')
     )
     ORDER BY POSITION
     /

     PROMPT
     PROMPT INDEXES for Table &Table_Name.

     BREAK ON INDEX_NAME ON UNIQUENESS SKIP 1

     SELECT I.INDEX_NAME , C.COLUMN_NAME , I.UNIQUENESS
     FROM ALL_IND_COLUMNS C , ALL_INDEXES I
     WHERE C.INDEX_NAME = I.INDEX_NAME
     AND C.TABLE_NAME = I.TABLE_NAME
     AND I.TABLE_NAME = UPPER('&Table_Name.')
     AND I.Owner = UPPER('&Owner.')
     AND C.Table_Owner = UPPER('&Owner.')
     AND NOT EXISTS ( SELECT 'X'
     FROM ALL_CONSTRAINTS
     WHERE CONSTRAINT_NAME = I.INDEX_NAME
     AND Owner = UPPER('&Owner.')
     )
     ORDER BY INDEX_NAME , COLUMN_POSITION
     /

     CLEAR BREAKS

     PROMPT
     PROMPT FOREIGN KEYS for Table &Table_Name.

     BREAK ON CONSTRAINT_NAME ON TABLE_NAME ON R_CONSTRAINT_NAME SKIP 1
     COLUMN POSITION NOPRINT

     SELECT UNIQUE A.CONSTRAINT_NAME,
     C.COLUMN_NAME "FK Column" ,
     B.TABLE_NAME || '.' || B.COLUMN_NAME "PK Column",
     A.R_CONSTRAINT_NAME ,
     C.POSITION
     FROM ALL_CONSTRAINTS A, ALL_CONS_COLUMNS B, ALL_CONS_COLUMNS C
     WHERE A.R_CONSTRAINT_NAME=B.CONSTRAINT_NAME
     AND B.OWNER=UPPER('&OWNER')
     AND A.CONSTRAINT_NAME=C.CONSTRAINT_NAME
     AND A.OWNER=C.OWNER
     AND A.OWNER = B.OWNER
     AND A.TABLE_NAME=C.TABLE_NAME
     AND B.POSITION=C.POSITION
     AND A.TABLE_NAME LIKE UPPER('&TABLE_NAME')
     ORDER BY A.CONSTRAINT_NAME, C.POSITION
     /

     COLUMN POSITION NOPRINT
     CLEAR BREAKS

     PROMPT
     PROMPT CONSTRAINTS for Table &Table_Name.

     SELECT CONSTRAINT_NAME , SEARCH_CONDITION
     FROM ALL_CONSTRAINTS
     WHERE TABLE_NAME = UPPER('&Table_Name.')
     AND Owner = UPPER('&Owner.')
     AND CONSTRAINT_TYPE NOT IN ( 'P' , 'R');

     PROMPT
     PROMPT ROWCOUNT for Table &Table_Name.

     SET FEEDBACK OFF
     SET SERVEROUTPUT ON
     DECLARE N NU MBER ;
     V VARCHAR2(100) ;
     BEGIN
     V := 'SELECT COUNT(*) FROM ' || UPPER('&Table_Name.') ;
     EXECUTE IMMEDIATE V INTO N ;
     DBMS_OUTPUT.PUT_LINE (N);
     END;
     /

     SET FEEDBACK ON

     PROMPT
     PROMPT Tables That REFER to Table &Table_Name.

     BREAK ON TABLE_NAME ON CONSTRAINT_NAME skip 1

     SELECT C.TABLE_NAME , C.CONSTRAINT_Name , CC.COLUMN_NAME "FK Column"
     FROM ALL_CONSTRAINTS C
     , All_Cons_colUMNs CC
     WHERE C.Constraint_Name = CC.Constraint_Name
     AND R_CONSTRAINT_NAME = ( SELECT CONSTRAINT_NAME
     FROM ALL_CONSTRAINTS
     WHERE TABLE_NAME = UPPER('&Table_Name.')
     AND CONSTRAINT_TYPE = 'P'
     AND Owner = UPPER('&Owner.')
     )
     AND C.Owner = UPPER('&Owner.')
     /

     CLEAR BREAKS


     PROMPT
     PROMPT PARTITIONED COLUMNS for Table &Table_Name.

     SELECT COLUMN_NAME , COLUMN_POSITION
     FROM All_Part_Key_Columns
     WHERE NAME = UPPER('&Table_Name.')
     AND Owner = UPPER('&Owner.') ;


     PROMPT
     PROMPT PARTITIONS for Table &Table_Name.

     SELECT PARTITION_NAME , NUM_ROWS
     FROM All_Tab_Partitions
     WHERE TABLE_NAME = UPPER('&Table_Name.')
     AND Table_Owner = UPPER('&Owner.') ;


     PROMPT
     PROMPT TRIGGERS for Table &Table_Name.

     SELECT Trigger_Name
     FROM All_Triggers
     WHERE TABLE_NAME = UPPER('&Table_Name.')
     AND Owner = UPPER('&Owner.') ;

     PROMPT
     PROMPT DEPENDANTS for Table &Table_Name.

     BREAK ON TYPE SKIP 1

     SELECT TYPE , NAME
     FROM ALL_DEPENDENCIES
     WHERE REFERENCED_NAME = UPPER('&Table_Name.')
     ORDER BY TYPE ;

     CLEAR BREAKS

     SET TERMOUT OFF
     SET AUTOTRACE ON
     SET TIMING ON
     SET TERMOUT ON

How To : Displaying Column Data Horisontally

2008-01-21T16:25:00.000-08:00

Some time you need to create a result set where the rows need to be columns, or vice versa. This commonly requirement can be done by using pivot (or crosstab) query.

1. A simple pivot query can be done by doing the following
2. Add some kind of count or row number to your query, if necessary for the grouping
3. Then use your (revised) original query as a sub-query
4. Use "decode" to turn rows into columns (ie. a "sparse" matrix).
5. Use "max" to "squash" the multiple rows you moved to columns, into single rows. Don't forget to group by.

(Note: it gets more complicated if you don't know how many columns you'll need).

Here is an example of a pivot query. Say you have the following set of data:

And you would like to make DEPTNO be a column. We have 4 deptno's in EMP, 10,20,30,40.
We can make columns dept_10, dept_20, dept_30, dept_40 that have the values that are
currently in the count column. It would look like this:

Note: don't confuse pivot queries with pivot tables. Pivot tables are a different concept, and have different uses (most typically to fill in missing data).

Trigger on Insert and Delete

2008-01-14T17:14:00.000-08:00

A friend of mine is asking, how to insert into TBL_A, but first you have to insert into TBL_B as master table (TBL_A referencing to TBL_B). We could use trigger on this. Here's two example on creating trigger while insert and delete on a table.

create or replace trigger TBL_A_TRIG_INS
before insert on TBL_A
for each row

begin
insert into TBL_B (ID ,NAME )
values ( :new.ID, :new.NAME );
end;

create or replace trigger TBL_A_TRIG_DEL
after
delete
on TBL_A
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
begin
delete from TBL_B
where ID = :new.ID and NAME = :new.NAME
end;

Oracle Optimizer Approach Hints

2008-01-08T01:35:00.000-08:00

ALL_ROWS
The ALL_ROWS hint explicitly chooses the cost-based approach to optimize a statement block with a goal of best throughput (that is, minimum total resource consumption).

Syntax : /*+ ALL_ROWS */
Example :

conn / as sysdba

set linesize 121
col name format a30
col value format a30

SELECT name, value
FROM gv$parameter
WHERE name LIKE '%optimizer%';

ALTER SYSTEM SET optimizer_mode=RULE SCOPE=MEMORY;

set autotrace traceonly explain

SELECT table_name
FROM dba_tables
WHERE owner = 'SYS'
AND table_name LIKE '%$'
ORDER BY 1;

SELECT /*+ ALL_ROWS */ table_name
FROM dba_tables
WHERE owner = 'SYS'
AND table_name LIKE '%$'
ORDER BY 1;

ALTER SYSTEM SET optimizer_mode=ALL_ROWS SCOPE=MEMORY;

FIRST_ROWS(n)
The FIRST_ROWS hint explicitly chooses the cost-based approach to optimize a statement block with a goal of best response time (minimum resource usage to return first row).

This hint causes the optimizer to make these choices:

If an index scan is available, the optimizer may choose it over a full table scan.
If an index scan is available, the optimizer may choose a nested loops join over a sort-merge join whenever the associated table is the potential inner table of the nested loops.
If an index scan is made available by an ORDER BY clause, the optimizer may choose it to avoid a sort operation.
The optimizer ignores this hint in DELETE and UPDATE statement blocks and in SELECT statement blocks that contain any of the following: UNION, INTERSECT, MINUS, UNION ALL, GROUP BY, FOR UPDATE, aggregating function and the DISTINCT operator.

Syntax : /*+ FIRST_ROWS() */
Example :

set autotrace trace exp
SELECT table_name
FROM dba_tables
WHERE owner = 'SYS'
AND table_name LIKE '%$'
ORDER BY 1;
SELECT /*+ FIRST_ROWS(10) */ table_name
FROM dba_tables
WHERE owner = 'SYS'
AND table_name LIKE '%$'
ORDER BY 1;

RULE
Disables the use of the optimizer. This hint is not supported and should not be used.

Syntax : /*+ RULE */
Example :

set autotrace trace exp

SELECT table_name
FROM dba_tables
WHERE owner = 'SYS'
AND table_name LIKE '%$'
ORDER BY 1;

SELECT /*+ RULE */ table_name
FROM dba_tables
WHERE owner = 'SYS'
AND table_name LIKE '%$'
ORDER BY 1;

Oracle SQL Hints

2008-01-07T23:34:00.000-08:00

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

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. So the following is wrong:

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

better

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

Why using hints

It is a perfect valid question to ask why hints should be used. 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.
It must also be noted, that Oracle allows to lock the statistics when they look ideal which should make the hints meaningless again.

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

Oracle 10g Release 2 Hint List

2008-01-07T17:08:00.000-08:00

Getting a list of Oracle hints for SQL tuning is often difficult. The Oracle hint list is inside the Oracle executable and you can extract the Oracle hint list easily with UNIX commands.

You can use grep and strings to get them directly from the Oracle executable:

strings $ORACLE_HOME/bin/oracle > hints.lst

Here are categorized hint list :
Optimizer Approaches

ALL_ROWS
FIRST_ROWS(n)
RULE

General

APPEND
CACHE
CURSOR_SHARING_EXACT
DRIVING_SITE
DYNAMIC_SAMPLING
MODEL_MIN_ANALYSIS
NOAPPEND
NO_CACHE
NO_PUSH_PRED
NO_PUSH_SUBQ
NO_PX_JOIN_FILTER
NO_XML_QUERY_REWRITE
PUSH_PRED
PUSH_SUBQ
PX_JOIN_FILTER
QB_NAME

Access Method Hints

FULL
INDEX
INDEX_ASC
INDEX_COMBINE
INDEX_DESC
INDEX_FFS
INDEX_JOIN
INDEX_SS
INDEX_SS_ASC
INDEX_SS_DESC
NO_INDEX
NO_INDEX_FFS
NO_INDEX_SS

Cluster Only Access Method Hints

CLUSTER
HASH

Join Order

LEADING
ORDERED

Join Operation

NO_USE_HASH (table1 table2)
NO_USE_MERGE (table1 table2)
NO_USE_NL (table1 table2)
USE_HASH (table1 table2)
USE_MERGE (table1 table2)
USE_NL (table1 table2)
USE_NL_WITH_INDEX

Parallel Execution

PARALLEL
NO_PARALLEL
PQ_DISTRIBUTE
PARALLEL_INDEX
NO_PARALLEL_INDEX

Query Transformation

FACT
NO_EXPAND
NO_FACT
NO_QUERY_TRANSFORMATION
NO_REWRITE
NO_STAR_TRANSFORMATION
NO_UNNEST
REWRITE
STAR_TRANSFORMATION
UNNEST
USE_CONCAT

Others

ANTIJOIN
BITMAP
BUFFER
CARDINALITY
HASH_AJ
INLINE
MATERIALIZE
MERGE
NO_ACCESS
NO_BUFFER
NO_MERGE
NO_MONITORING
NO_PUSH_GSETS
NO_PUSH_JOIN_PRED
NO_QKN_BUFF
NO_SEMIJOIN
OR_EXPAND
PUSH_JOIN_PRED
SEMIJOIN
SEMIJOIN_DRIVER
SWAP_JOIN_INPUTS
USE_ANTI
USE_SEMI

Undocumented Optimizer Hints

BYPASS_RECURSIVE_CHECK
BYPASS_UJVC
CACHE_CB
CACHE_TEMP_TABLE
CIV_GB
COLLECTIONS_GET_REFS
CPU_COSTING
CUBE_GB
DEREF_NO_REWRITE
DML_UPDATE
DOMAIN_INDEX_NO_SORT
DOMAIN_INDEX_SORT
DYNAMIC_SAMPLING_EST_CDN
EXPAND_GSET_TO_UNION
FORCE_SAMPLE_BLOCK
GBY_CONC_ROLLUP
GLOBAL_TABLE_HINTS
HWM_BROKERED
IGNORE_ON_CLAUSE
IGNORE_WHERE_CLAUSE
INDEX_RRS
LIKE_EXPAND
LOCAL_INDEXES
MV_MERGE
NESTED_TABLE_GET_REFS
NESTED_TABLE_SET_REFS
NESTED_TABLE_SET_SETID
NO_ELIMINATE
NO_EXPAND_GSET_TO_UNION
NO_FILTERING
NO_ORDER_ROLLUPS
NO_PRUNE_GSETS
NO_STATS_GSETS
NOCPU_COSTING
OB_NAME
OVERFLOW_NOMOVE
PIV_GB
PIV_SSF
PQ_MAP
PQ_NOMAP
REMOTE_MAPPED
RESTORE_AS_INTERVALS
SAVE_AS_INTERVALS
SCN_ASCENDING
SELECTIVITY
SKIP_EXT_OPTIMIZER
SQLLDR
SYS_DL_CURSOR
SYS_PARALLEL_TXN
SYS_RID_ORDER
TIV_GB
TIV_SSF
USE_TTT_FOR_GSETS

Oracle Optimizer Hints

2008-01-07T16:22:00.001-08:00

Optimizer hints can be used with SQL statements to alter execution plans.Hints let you make decisions usually made by the optimizer. As an application designer, you might know information about your data that the optimizer does not know.

For example, you might know that a certain index is more selective for certain queries. Based on this information, you might be able to choose a more efficient execution plan than the optimizer. In such a case, use hints to force the optimizer to use the optimal execution plan.

You can use hints to specify the following:

The optimization approach for a SQL statement
The goal of the cost-based optimizer for a SQL statement
The access path for a table accessed by the statement
The join order for a join statement
A join operation in a join statement

Note : The use of hints involves extra code that must be managed, checked, and controlled.

Hints provide a mechanism to direct the optimizer to choose a certain query execution plan based on the following criteria:

Join order
Join method
Access path
Parallelization

Hints (except for the RULE hint) invoke the cost-based optimizer (CBO). If you have not gathered statistics, then defaults are used. Hints apply only to the optimization of the statement block in which they appear. A statement block is any one of the following statements or parts of statements:

A simple SELECT, UPDATE, or DELETE statement.
A parent statement or subquery of a complex statement.
A part of a compound query.

For example, a compound query consisting of two component queries combined by the UNION operator has two statement blocks, one for each component query. For this reason, hints in the first component query apply only to its optimization, not to the optimization of the second component query.

You can send hints for a SQL statement to the optimizer by enclosing them in a comment within the statement. A statement block can have only one comment containing hints. This comment can only follow the SELECT, UPDATE, or DELETE keyword.
Exception:
The APPEND hint always follows the INSERT keyword, and the PARALLEL hint can follow the INSERT keyword.

The following syntax shows hints contained in both styles of comments that Oracle supports within a statement block.

{DELETE|INSERT|SELECT|UPDATE} /*+ hint [text] [hint[text]]... */

or

{DELETE|INSERT|SELECT|UPDATE} --+ hint [text] [hint[text]]...

where:

DELETE, INSERT, SELECT, and UPDATE are keywords that begin a statement block. Comments containing hints can appear only after these keywords.
+ causes Oracle to interpret the comment as a list of hints. The plus sign must immediately follow the comment delimiter; no space is permitted.
hint is one of the hints discussed in this section. If the comment contains multiple hints, then each hint must be separated from the others by at least one space.
text is other commenting text that can be interspersed with the hints.

If you specify hints incorrectly, then Oracle ignores them but does not return an error:

Oracle ignores hints if the comment containing them does not follow a DELETE, SELECT, or UPDATE keyword.
Oracle ignores hints containing syntax errors, but considers other correctly specified hints within the same comment.
Oracle ignores combinations of conflicting hints, but considers other hints within the same comment.
Oracle ignores hints in all SQL statements in those environments that use PL/SQL version 1, such as Forms version 3 triggers, Oracle Forms 4.5, and Oracle Reports 2.5. These hints can be passed to the server, but the server ignores them.

The optimizer recognizes hints only when using the cost-based approach. If you include a hint (except the RULE hint) in a statement block, then the optimizer automatically uses the cost-based approach.

Oracle, SQL, PL / SQL Technical Interview Questions

2008-01-03T17:22:00.000-08:00

Listed here are Interview Questions for Database Interviews.
This mainly lists Oracle, SQL, PL SQL frequently asked questions in technical Interviews

1. What is DDL, DML ? How are they different?
2. What are different types of joins in SQL?
3. How do you select unique rows using SQL?
4. What is the difference between DELETE and TRUNCATE ?
5. What is the difference between a "where" clause and a "having" clause?
6. What is the difference between "procedure" and "function"?
7. What is the difference between "translate" and "replace" ?
8. How to remove duplicate records from a table?
9. What is a "trigger"?
10.What is the difference between "translate" and "replace"?
11.What is a VIEW?
12.What is the difference among "dropping a table", "truncating a table"
and "deleting all records" from a table
13.Explain new feature of 9i Database ? Explain new feature of 10g Database ?
14.How to use DECODE function?
15.What is “Group by” clause?
16.What are cursors and what are the situations you will use them?
17.What default packages are provided by Oracle?
18.How do you debug a oracle procedure /function?
19.How many triggers are available?
20.How are procedures executed?

Oracle SQL, PL/SQL Interview Question List

2008-01-03T17:15:00.000-08:00

These are some Oracle SQL, PL/SQL Interview Question List
1. How do you convert a date to a string?
2. What is an aggregate function?
3. What is the dual table?
4. What are cursors? Distinguish between implicit and explict cursors?
5. Explain how cursors are used by Oracle?
6. What is PL/SQL? Describe the block structure of PL/SQL?
7. What is a nested subquery?
8. What are the various types of queries ?
9. Which of the following is not a schema object : Index, table, public synonym, trigger and package ?
10. What is dynamic sql in oracle?
11. What is the difference between a package, procedure and function?
12. What is the difference between delete, drop and truncating a table?
13. How many triggers are supported in Oracle
14. Are you aware of FLASHBACK concept ? What is it?
15. Describe oracle’s logical and physical structure?
16. What is data dictionary ?
17. What is the use of control files ?
18. How would store XML data in table ? What data type would be used for the columns?
19. Difference between post and commit?
20. Difference between commit and rollback?
21. What are savepoints?
22. What is the Difference between a View and Synonym?
23. How would you fetch system date from oracle?
24. What is the difference between primary key, unique key, foreign key?
25. What is the difference between NO DATA FOUND and %NOTFOUND?
26. What is cursor for loop?
27. What are cursor attributes?
28. What will you use in Query : IN or EXISTS? Why?
29. Explain the difference between a data block, an extent and a segment.?
30. What's the difference between logical and physical I/O?
31. What is an anonymous block?
32. What is a PL/SQL collection?
33. How can you tell if an UPDATE updated no rows?
34. How can you tell if a SELECT returned no rows?

The Difference Between %TYPE and %ROWTYPE

2008-01-03T16:59:00.000-08:00

%TYPE and %ROWTYPE are used to define variables in PL/SQL as it is defined within the database. If the datatype or precision of a column changes, the program automically picks up the new definition from the database without having to make any code changes.

The %TYPE and %ROWTYPE constructs provide data independence, reduces maintenance costs, and allows programs to adapt as the database changes to meet new business needs.

%TYPE is used to declare a field with the same type as that of a specified table's column. Example:

DECLARE
v_EmpName emp.ename%TYPE;
BEGIN
SELECT ename INTO v_EmpName FROM emp WHERE ROWNUM = 1;
DBMS_OUTPUT.PUT_LINE('Name = ' || v_EmpName);
END;
/

%ROWTYPE is used to declare a record with the same types as found in the specified database table, view or cursor. Examples:

DECLARE
v_emp emp%ROWTYPE;
BEGIN
v_emp.empno := 10;
v_emp.ename := 'XXXXXXX';
END;
/

DECLARE
v_EmpRecord emp%ROWTYPE;
BEGIN
SELECT * INTO v_EmpRecord FROM emp WHERE ROWNUM = 1;
DBMS_OUTPUT.PUT_LINE('Name = ' || v_EmpRecord.ename);
DBMS_OUTPUT.PUT_LINE('Salary = ' || v_EmpRecord.sal);
END;
/

Oracle DBA Interview Questions

2008-01-03T16:14:00.001-08:00

Here are Oracle DBA Questions and Answers in Interview (don't expect too much on this)

1. Explain database instance ?

A database instance (server) is a set of memory structures and background processes that access a set of database files.
The memory structures are used to store most queried data from database. This helps us to improve database performance by decreasing the amount of I/O performed against data file.
The process can be shared by all users.

2. What is parallel server?
Multiple instances accessing the same database (Only in Multi-CPU environments).

3. What is Schema ?
The set of objects owned by user account is called the schema

4. What is an Index ? How it is implemented in Oracle Database ?
An index is a database structure used by the server to have direct access of a row in a table.An index is automatically created when a unique or primary key constraint clause is specified in create table command

5. What is a clusters? Explain
Group of tables physically stored together because they share common columns and are often used together is called Clusters.

6. What is a cluster key ?
The related columns of the tables are called the cluster key. The cluster key is indexed using a cluster index and its value is stores only once for multiple tables in the cluster.

7. What are the basic element of an oracle Database ?
It consists ofone or more data filesone or more control filestwo or more redo log files
The database containsMultiple users/schemasone or more rollback segmentsone or more tablespacesData dictionary tables
User objects (tables,indexes,views etc)
The server that access the database consists of
SGA (Database buffer, Dictionary Cache Buffers, redo log buffers,Shared SQL pool)
SMON
PMON
LGWR
DBWR
ARCH
CKPT
RECO
Dispatcher
User process with associated PGA

8. What is deadlock ? Explain.
Two processes waiting to update the rows of a table which are locked by the other process then deadlock arises.
In a database environment this will often happen because of not issuing proper row lock commands. Poor design of front-end application may cause this situation and the performance of server will reduce drastically.
These locks will be released automatically when a commit/rollback operation performed or any one of this processes being killed externally.

9. What is SGA ?
The System Global Area in a Oracle database is the area in memory to facilitates the transfer of information between users. It holds the most recently requested structural information about the database.

10. What is Shared SQL pool ?
The data dictionary cache is stored in an area in SGA called the Shared SQL Pool. This will allow sharing of parsed SQL statements among concurrent users.

11. What is meant by Program Global Area (PGA) ?
It is area in memory that is used by a Single Oracle User process.

12. What is a data segment ?
Data segment are the physical areas within a database block in which the data associated with tables and clusters are stored.

13. What are the factors causing the reparsing of SQL statements in SGA ?
Due to insufficient Shared SQL pool size

Oracle 10g UTL_COMPRESS

2008-01-02T19:53:00.000-08:00

Oracle 10g includes many PL/SQL enhancements including: utl_compress
The UTL_COMPRESS package provides an API to allow compression and decompression of binary data (RAW, BLOB and BFILE). It uses the Lempel-Ziv compression algorithm which is equivalent to functionality of the gzip utility. A simple example of it's usage would be:

SET SERVEROUTPUT ON
DECLARE
l_original_blob      BLOB;
l_compressed_blob    BLOB;
l_uncompressed_blob  BLOB;
BEGIN
-- Initialize both BLOBs to something.
l_original_blob     := TO_BLOB(UTL_RAW.CAST_TO_RAW('1234567890123456789012345678901234567890'));
l_compressed_blob   := TO_BLOB('1');
l_uncompressed_blob := TO_BLOB('1');

-- Compress the data.
UTL_COMPRESS.lz_compress (src => l_original_blob,
                          dst => l_compressed_blob);

-- Uncompress the data.
UTL_COMPRESS.lz_uncompress (src => l_compressed_blob,
                            dst => l_uncompressed_blob);

-- Display lengths.
DBMS_OUTPUT.put_line('Original Length    : ' || LENGTH(l_original_blob));
DBMS_OUTPUT.put_line('Compressed Length  : ' || LENGTH(l_compressed_blob));
DBMS_OUTPUT.put_line('Uncompressed Length: ' || LENGTH(l_uncompressed_blob));

-- Free temporary BLOBs.           
DBMS_LOB.FREETEMPORARY(l_original_blob);
DBMS_LOB.FREETEMPORARY(l_compressed_blob);
DBMS_LOB.FREETEMPORARY(l_uncompressed_blob);
END;
/

Oracle DBA : Backup on Tape Drive

2008-01-01T16:02:00.000-08:00

For oracle DBA, here are some cases on Tape Drive Backup:

You want to take backup on tape drive but rman always store the backup on a default location

how can we take backup on tape drive, should we have to install any extra softwares for tape drive or rman handle it itself?

You need thrid party Media managment software like veritas ,omnibackup,tivoli for backing up to tape.If you don't already have Media Management software, you can consider Oracle Secure Backup as a free alternative (assuming you are backing up one server to one tape drive).
how can we set the tape drive as our default location for backup?
how can we take backup on specified location e.g. d:\backup and how can we set this location as default location for backup?
To set the tape drive as the default, see Configuring the Default Device Type for Backups.

If you want the tape drive to be the default backup location, then you'll have to allocate a disk channel to produce backups to d:\backup. You can configure the disk format as CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT 'd:\backups'; and then allocate a disk channel during backup (Manually Overriding Configured Channels).

SQL & PL/SQL : When Your Query Takes Too Long

2007-12-27T16:55:00.000-08:00

When your query takes too long and too slow, first of all you have to investigate the root caused of the problem, you have to know why it is slow

The tools at your disposal are, among more:
1. dbms_profiler
2. explain plan
3. SQL*Trace / tkprof
4. statspack

Use dbms_profiler if you want to know where time is being spent in PL/SQL code.
Statspack is a must if you are a dba and want to know what is going on in your entire database. For a single query or a small process, explain plan and SQL*Trace and tkprof are your tools.

explain plan in SQL*Plus you have to type:

explain plan for [your query];
select * from table(dbms_xplan.display);

When you get error messages or a message complaining about an old version of plan_table, make sure you run the script utlxplan.sql.

The output you get here basically shows you what the cost based optimizer expects. It gives you an idea on why the cost based optimizer chooses an access path.

SQL*Trace/tkprof

For this you have to type in SQL*Plus:
- alter session set sql_trace true;
- run your query
- disconnect (this step is important, because it ensures all cursors get closed, and "row source operation" is generated)
- identify your trace file in the server directory as specified in the parameter user_dump_dest
- on your operating system: "tkprof [trace file] a.txt sys=no sort=prsela exeela fchela"

The file a.txt will now give you valuable information on what has actually happened. No predictions but the truth.

By comparing the output from explain plan with the output from tkprof, you are able to identify the possible problem areas.

So before rushing into possible solutions, always post the output of explain plan and tkprof with your question and don't forget to post them between the tags [pre] and [/pre] for readability.

SQL & PL/QL : Deleting Table with Millions Rows

2007-12-18T15:56:00.000-08:00

Here are the case:
You have to compare 2 tables, both having around million rows, and delete data based on single column.

SQL>DELETE FROM TABLE_A WHERE COLUMN_A NOT IN
(SELECT COLUMN_A FROM TABLE_B)

when you execute the above command, it takes a long time. So what is the best way to achieve it?
Here are some solutions:

1. If the rows percentage to be deleted is large then you can try the following step:
* Create a new table where COLUMN_A NOT IN
(SELECT COLUMN_A FROM TABLE_B)
* TRUNCATE table_A
* Insert in to table_A (select * from new table)
* If you have any constraints then may be it can be disabled.
2. If the query is properly indexed you might get better performance from a correlated subquery instead of NOT IN, something like

DELETE FROM TABLE_A
WHERE NOT EXISTS(
SELECT COLUMN_A
FROM TABLE_B
WHERE b.column_a = a.column_a
)

Lost SYSOPER Password

2007-12-17T23:14:00.000-08:00

Thera are several solutions to this issue, you can use one of the following methods:

Make sure that the database and listener services are started. Then open a command prompt and log in as / as sysdba. Then change the sys password.

c:> sqlplus /nolog
SQL> connect / as sysdba
SQL> alter user sys identified by ;

If you get asked for a password when you log in as / you need to create or recreate the password file using orapw. Check the $ORACLE_HOME/database directory for a file called orapw. That is the password file for the database. Create (or recreate) it using:

c:\> orawpd file=$ORACLE_HOME/database/orapwd password= entries=5

Notice it is from the dos prompt
you may also try this method:
1. login oracle user
2. cd $ORACLE_HOME/network/admin
3. ed(vi) file sqlnet.ora
4. Remark by # at begining of line
  SQLNET.AUTHENTICATION_SERVICES = (NONE)
  => #SQLNET.AUTHENTICATION_SERVICES = (NONE)
5. sqlplus /nolog or (svrmgrl) command
6. connect sys as sysdba, or connect internal
  (not need input the password).

Oracle Data Security Tips

2007-12-17T16:12:00.002-08:00

If you're building a client-server application, you can use these tips:

Build a three tier application, so the client is not connect to the database directly.
Allow only 1 IP Address (as application server) connect to the oracle database (you can use blocking IP tips).
Never use Oracle user ID (specially DBA user) as your Application user ID, instead create a user table with user and encrypted password in it.
Give your Oracle Listener a password, so when someone unauthorized send a stop message to the listener it didn't work out.

Oracle SAVEPOINT

2007-12-12T17:33:00.000-08:00

A SAVEPOINT is a marker within a transaction that allows for a partial rollback. As changes are made in a transaction, we can create SAVEPOINTs to mark different points within the transaction. If we encounter an error, we can rollback to a SAVEPOINT or all the way back to the beginning of the transaction.

SQL> INSERT INTO AUTHOR
2 VALUES ('X123', 'mister',
3 'popo', '62-274-4567',
4 'jl sudirman', 'jakarta',
5 'JKBKJ','10001', '9999');

1 row created.

SQL> savepoint in_author;

Savepoint created.

SQL> INSERT INTO BOOK_AUTHOR VALUES ('X123', 'B130', .20);
1 row created.

SQL> savepoint in_book_author;

Savepoint created.

SQL> INSERT INTO BOOK
2 VALUES ('B130', 'P002', 'how to make love',
3 'miscellaneous', 9.95, 1000, 15, 0, '',
4 to_date ('02-20-2007','MM-DD-YYYY'));
1 row created.

SQL> rollback to in_author;

Rollback complete.

In the example above, I inserted a row into the AUTHOR table and created a SAVEPOINT called in_author. Next, I inserted a row into the book_author table and created another SAVEPOINT called in_book_author. Finally, I inserted a row in the BOOK table. I then issued a ROLLBACK to in_author.

Row locks are NOT released by SETTING a savepoint. Row locks are release by one of three events - commit, rollback, or rollback to savepoint. My argument is that Oracle does not handle the latter well.

If transaction A updates row 1, sets a savepoint, and updates row 2 (but does not as yet commit). Then transaction B wishes to update row 2.
Transaction B will correctly block on the commit or rollback of transaction A.
However, if transaction A does a rollback to savepoint, it will continue to have hold a lock on row 1 (but not row 2).
In fact a third transaction can now update row 2 (as it's not locked by transaction A).
However, our poor transaction B, is still waiting (incorrectly) for transaction A to commit or rollback.

The problem is that Oracle provides no way of waiting on a row - you can only wait on a transaction - and sometimes transactions (through the use of rollback to savepoint) release rows WITHOUT committing or aborting.

Imagine what could happen when by issuing a savepoint the lock of an standing transaction would be released, and another transaction would change 'my' row, and then I do a full rollback.

Repair UNDO Log Corruption

2007-12-12T15:54:00.000-08:00

In rare cases (usually DBA error) the Oracle UNDO tablespace can become corrupted.

This manifests with this error: ORA-00376: file xx cannot be read at this time

In cases of UNDO log corruption, you must:

• Change the undo_management parameter from “AUTO” to “MANUAL”
• Create a new UNDO tablespace
• Drop the old UNDO tablespace

Dropping the corrupt UNDO tablespace can be tricky and you may get the message:

ORA-00376: file string cannot be read at this time

To drop a corrupt UNDO tablespace:

Identify the bad segment:

select
segment_name,
status
from
dba_rollback_segs
where
tablespace_name='undotbs_corrupt'
and
status = ‘NEEDS RECOVERY’;

SEGMENT_NAME STATUS
----------------------------------------------
_SYSSMU22$ NEEDS RECOVERY
Bounce the instance with the hidden parameter “_offline_rollback_segments”, specifying the bad segment name:
_OFFLINE_ROLLBACK_SEGMENTS=_SYSSMU22$
Bounce database, nuke the corrupt segment and tablespace:

SQL> drop rollback segment "_SYSSMU22$";
Rollback segment dropped.

SQL > drop tablespace undotbs including contents and datafiles;
Tablespace dropped.

Remarks in Script with Ttitle and Btitle

2007-11-18T16:57:00.001-08:00

The TTITLE (top title) and BTITLE (bottom title) commands have this syntax:

TTITLE option text OFF/ON

where option refers to the placement of the title:

1. COLUMN n (start in some column, n)
2. SKIP m (skip m blank lines)
3. TAB x (tab x positions)
4. LEFT/CENTER/RIGHT (default is LEFT)

The same holds for BTITLE.

In addition, page numbers may be added with the extension:

option text format 999 sql.pno

SQL*Plus

2007-11-18T16:55:00.000-08:00

SQL*Plus is a tool for interfacing with the oracle database
You can use it to

1. Execute SQL and PL/SQL in the database
2. Update data in your database
3. Perform queries on your data
4. Format the result sets of those queries into reports
5. Create, edit, retrieve, and execute SQL scripts
6. Administer the database

Get IP Address and Hostname

2007-09-19T20:51:00.001-07:00

There are legitimate times when you want your procedural code to gather the current host name or IP address and Oracle has several ways to do this:
The utl_inaddr procedure:
SQL> select utl_inaddr.get_host_address('www.detik.com')
2 hostname from dual;

HOSTNAME
-----------------
203.190.241.41

The sys_context procedure:
SQL> select SYS_CONTEXT('USERENV', 'IP_ADDRESS', 15) ipaddr from dual;

IPADDR
------------------
172.16.1.78

Retrieving Only the Nth Row From a Table

2007-09-18T18:28:00.000-07:00

How are we suppose to retrieve only the Nth row from a table?

SELECT * FROM table
WHERE rowid = (
SELECT rowid FROM table
WHERE rownum <= N
MINUS
SELECT rowid FROM table
WHERE rownum < N
);

Analytic Features - Grouping Sets

2007-09-16T17:36:00.000-07:00

Grouping Sets - Instead of a UNION ALL statement (that would require multiple table scans), define a grouping set - the new syntax will result in only a single pass over the base table.
Grouping Sets are specified in the GROUP BY clause
Syntax:

SELECT group_function(column1), column2, group_function(column3)...
FROM table_list
[WHERE conditions]
GROUP BY GROUPING SETS (group_by_list)

SELECT group_function(column1), column2, group_function(column3)...
FROM table_list
[WHERE conditions]
GROUP BY CUBE (group_by_list)

SELECT group_function(column1), column2, group_function(column3)...
FROM table_list
[WHERE conditions]
GROUP BY ROLLUP (group_by_list)

Examples:
Instead of this UNION query...

SELECT
manager_id, null hire_date, count(*)
FROM
employees
GROUP BY manager_id, 2
UNION ALL
SELECT
null, hire_date, count(*)
FROM
employees
GROUP BY 1, hire_date

The above rewritten as a Grouping Set...

SELECT
manager_id, hire_date, count(*)
FROM
employees
GROUP BY GROUPING SETS (manager_id, hire_date);

The GROUPING SET clause allows you to specify the EXACT groups.

CUBE
Where a large number of groupings are needed then the CUBE and ROLLUP statements extend this idea by calculating multiple groupings in a single statement.

e.g. GROUP BY CUBE (hire_date, manager_id, product) will produce 2^3 =8 groupings
1) hire_date, manager_id, product
2) hire_date, manager_id
3) hire_date, product
4) manager_id, product
5) hire_date
6) manager_id
7) product
8) Grand Total

GROUP BY CUBE always calculates ALL the combinations - which may be far more than needed.

ROLLUP
e.g. GROUP BY ROLLUP (hire_date, manager_id, product) will produce 4 groupings
1) hire_date, manager_id, product
2) hire_date, manager_id
3) hire_date,
4) Grand Total

GROUP BY ROLLUP calculates all combinations for the first column listed in the ROLLUP clause.

This can be further tuned by using parenthesis to remove some of the combinations

e.g. GROUP BY ROLLUP (hire_date, (manager_id, product)) will produce
1) hire_date, manager_id, product
2) hire_date
3) Grand Total

Grouping function
CUBE and ROLLUP will generate NULLs for each dimension at the subtotal levels.
The Grouping() function can be used to identify these rows, which can be very useful when performing additional calculations such as Ranking within a group.

The values returned by grouping() are:
0 for NULL data values
1 for NULL indicating a dimension subtotal

The results of Grouping() can be passed into a decode() e.g.
SELECT .. PARTITION BY GROUPING(column1) ..
SELECT .. PARTITION BY DECODE(GROUPING(column1), 1, ‘My SubTotal’, column1)) …

Combining (concatenating) Groupings
The CUBE and ROLLUP clauses can be combined as part of a standard GROUP BY clause
e.g. GROUP BY manager_id, ROLLUP (hire_date, product)

Notes
Grouping sets are typically 80 - 90% more efficient at producing sub-totals than equivalent SQL code.

ROLLUP/CUBE can be used with all aggregate functions (MAX, MIN, AVG, etc.)

A HAVING clause will apply to all the data returned.

Oracle CASE SQL

2007-09-16T17:25:00.000-07:00

Oracle SQL allows you to add "Boolean logic" and branching using the decode and CASE clauses. The case statement is a more flexible extension of the Decode statement. In its simplest form the Oracle CASE function is used to return a value when a match is found:

SELECT last_name, commission_pct,
(CASE commission_pct
WHEN 0.1 THEN ‘Low’
WHEN 0.15 THEN ‘Average’
WHEN 0.2 THEN ‘High’
ELSE ‘N/A’
END ) Commission
FROM employees ORDER BY last_name;

A more complex version is the Searched CASE expression where a comparison expression is used to find a match:

SELECT last_name, job_id, salary,
(CASE
WHEN job_id LIKE 'SA_MAN' AND salary < 12000 THEN '10%'
WHEN job_id LIKE 'SA_MAN' AND salary >= 12000 THEN '15%'
WHEN job_id LIKE 'IT_PROG' AND salary < 9000 THEN '8%'
WHEN job_id LIKE 'IT_PROG' AND salary >= 9000 THEN '12%'
ELSE 'NOT APPLICABLE'
END ) Raise
FROM employees;

Character Functions

2007-09-16T17:00:00.000-07:00

Character Functions accept character input.
The input may come from a column in a table or from any expression.
Character Functions List

ASCII(x) returns the ASCII value of the character x.
CHR(x) returns the character with the ASCII value of x.
CONCAT(x, y) concatenates y to x and return the appended string.
INITCAP(x) converts the initial letter of each word in x to uppercase and returns that string.
INSTR(x, find_string [, start] [, occurrence]) searches for find_string in x and returns the position at which find_string occurs.
INSTRB(x) returns the location of a string within another string, but returns the value in bytes for a single-byte character system.
LENGTH(x) returns the number of characters in x.
LENGTHB(x) returns the length of a character string in bytes, except that the return value is in bytes for single-byte character sets.
LOWER(x) converts the letters in x to lowercase and returns that string.
LPAD(x, width [, pad_string]) pads x with spaces to left, to bring the total length of the string up to width characters.
LTRIM(x [, trim_string]) trims characters from the left of x.
NANVL(x, value) returns value if x matches the NaN special value (not a number), otherwise x is returned.
NLS_INITCAP(x) Same as the INITCAP function except that it can use a different sort method as specified by NLSSORT.
NLS_LOWER(x) Same as the LOWER function except that it can use a different sort method as specified by NLSSORT.
NLS_UPPER(x) Same as the UPPER function except that it can use a different sort method as specified by NLSSORT.
NLSSORT(x) Changes the method of sorting the characters. Must be specified before any NLS function; otherwise, the default sort will be used.
NVL(x, value) returns value if x is null; otherwise, x is returned.
NVL2(x, value1, value2) returns value1 if x is not null; if x is null, value2 is returned.
REPLACE(x, search_string, replace_string) searches x for search_string and replaces it with replace_string.
RPAD(x, width [, pad_string]) pads x to the right.
RTRIM(x [, trim_string]) trims x from the right.
SOUNDEX(x) returns a string containing the phonetic representation of x.
SUBSTR(x, start [, length]) returns a substring of x that begins at the position specified by start. An optional length for the substring may be supplied.
SUBSTRB(x) Same as SUBSTR except the parameters are expressed in bytes instead of characters to handle single-byte character systems.
TRIM([trim_char FROM) x) trims characters from the left and right of x.
UPPER(x) converts the letters in x to uppercase and returns that string.

Bigfile Tablespace

2007-09-10T21:20:00.000-07:00

A bigfile tablespace (BFT) is a tablespace containing a single file that can have a very large size.

Bigfile Tablespace Overview
The traditional tablespace is referred to as a smallfile tablespace (SFT). A smallfile tablespace contains multiple, relatively small files. The bigfile tablespace has the following characteristics:

An Oracle database can contain both bigfile and smallfile tablespaces.
System default is to create the traditional smallfile tablespace.
The SYSTEM and SYSAUX tablespaces are always created using the system default type.
Bigfile tablespaces are supported only for locally managed tablespaces with automatic segment-space management.

There are two exceptions when bigfile tablespace segments are manually managed:

Locally managed undo tablespace
Temporary tablespace

Bigfile tablespaces are intended to be used with Automated Storage Management (ASM) (see Chapter 1) or other logical volume managers that support RAID.

However, you can also use it without ASM.

Bigfile Tablespace Benefits
Bigfile tablespace has the following benefits:

It simplifies large database tablespace management by reducing the number of datafiles needed.
It simplifies datafile management with Oracle-managed files and Automated Storage Management (ASM) by eliminating the need for adding new datafiles and dealing with multiple files.
It allows you to create a bigfile tablespace of up to eight exabytes (eight million terabytes) in size, and significantly increase the storage capacity of an Oracle database.
It follows the concept that a tablespace and a datafile are logically equivalent.

Maximum Database Size

The BFT extended the maximum size of tablespace and database. Let’s take a look at the two formulas that calculate the maximum size of data file and database.

The maximum data file size is calculated by:

Maximum datafile size = db_block_size * maximum number of blocks

The maximum amount of data in an Oracle database is calculated by:

Maximum database size = maximum datafile size * maximum number of datafile

The maximum number of datafiles in Oracle9i and Oracle 10g Database is 65,536. However, the maximum number of blocks in a data file increase from 4,194,304 (4 million) blocks to 4,294,967,296 (4 billion) blocks.

The maximum amount of data for a 32K block size database is eight petabytes (8,192 Terabytes) in Oracle9i.
The maximum amount of data for a 32K block size database is eight exabytes (8,388,608 Terabytes) in Oracle 10g.
As you can see, with the new BFT addressing scheme, Oracle 10g can contain astronomical amounts of data within a single database.

Oracle Listener security

2007-09-09T21:05:00.000-07:00

The first thing to do is to put a password on your listener. By default the listener comes with no password, and anyone can change listener settings through the lsnrctl tool.
To password protect your listener, perform the following as your Oracle user:

$ lsnrctl
LSNRCTL> change_password
Old password:
New password:
Reenter new password:

If you have done all of this correctly, you will see the following:

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=)))
Password changed for
The command completed successfully

Just as a note, if the listener you are protecting does not have the default name of LISTENER, you must do set cur before issuing the change_password command.

At this point, save the configuration of the listener to the file system. If you are on 10g, it will save with no problems:

LSNRCTL> save_config
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=)))
Saved configuration parameters.
Listener Parameter File /listener.ora
Old Parameter File /listener.bak
The command completed successfully

And you have a password protected listener.

However, this does not happen on 9i. If you perform a save_config, you will see the following:

LSNRCTL> save_config
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=)))
TNS-01169: The listener has not recognized the password

The reason is this: in Oracle 10g, operating system authentication for the listener has been implemented. The listener checks to make sure you are part of the privileged dba group, and if so it will grant you access to change the password, save configuration, stop the listener, etc. In 9i, we must do the following at this point:

LSNRCTL> set password
Password:
The command completed successfully

At this point, you can now perform a save_config.

So what is the result of this? In 9i, you will now require a password whenever you wish to stop the listener or any other "destructive" listener actions. In 10g, if you are not logged into the operating system with a privileged account, you will have to enter a password as well. A typical listener stop may look like this:

$ lsnrctl
LSNRCTL> stop
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=)))
TNS-01169: The listener has not recognized the password
LSNRCTL> set password
Password:
The command completed successfully
LSNRCTL> stop
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=)))
The command completed successfully

You are now protected against unauthorized shutdowns of your listener. This protects you from a whole range of possible security breaches. Remember that "set password" is how you enter your password for authentication; change_password is how it is changed.

Cursors

2007-09-09T19:45:00.000-07:00

Implicit cursors
Whenever a SQL statement is issued the Database server opens an area of memory in which the command is parsed and executed. This area is called a cursor. Microsoft tends to refer to cursors as datasets throughout much of their databse product documentation..

When the executable part of a PL/SQL block issues a SQL command, PL/SQL creates an implicit cursor which has the identifier SQL. PL/SQL manages this cursor for you.

PL/SQL provides some attributes which allow you to evaluate what happened when the implicit cursor was last used. You can use these attributes in PL/SQL statements like functions but you cannot use then within SQL statements.
The SQL cursor attributes are :

%ROWCOUNT:The number of rows processed by a SQL statement.
%FOUND: TRUE if at least one row was processed.
%NOTFOUND: TRUE if no rows were processed.
%ISOPEN:TRUE if cursor is open or FALSE if cursor has not been opened or has been closed. Only used with explicit cursors

Example:
DECLARE
ROW_DEL_NO NUMBER;
BEGIN
DELETE * FROM JD11.SECTION;
ROW_DEL_NO := SQL%ROWCOUNT;
END;

Explicit cursors
SELECT statements that occur within PL/SQL blocks are known as embedded, they must return one row and may only return one row. To get around this you can define a SELECT statement as a cursor (an area of memory), run the query and then manipulate the returned rows within the cursor. Cursors are controlled via four command statements.
They are :

DECLARE:Defines the name and structure of the cursor together with the SELECT statement that will populate the cursor with data. The query is validated but not executed.
OPEN: Executes the query that populates the cursor with rows.
FETCH:Loads the row addressed by the cursor pointer into variables and moves the cursor pointer on to the next row ready for the next fetch.
CLOSE:Releases the data within the cursor and closes it. The cursor can be reopened to refresh its data.

Cursors are defined within a DECLARE section of a PL/SQL block. For example:
DECLARE
CURSOR MYCUR IS SELECT ISBN, COST FROM JD11.BOOK;

The cursor is defined by the CURSOR keyword followed by the cursor identifier (MYCUR in this case) and then the SELECT statement used to populate it, the SELECT statement can be any legal query.

Cursors are opened with the OPEN statement, this populates the cursor with data.
DECLARE
CURSOR MYCUR IS SELECT ISBN, COST FROM JD11.BOOK;
BEGIN
OPEN MYCUR;
END;

To access the rows of data within the cursor we use the FETCH statement.

DECLARE
CURSOR MYCUR IS SELECT ISBN, COST FROM JD11.BOOK;
THISISBN NUMBER(10);
THISCOST NUMBER(10,2);
BEGIN
OPEN MYCUR;
FETCH MYCUR INTO THISISBN, THISCOST;
END;

The FETCH statement reads the column values for the current cursor row and puts them into the specified variables. The cursor pointer is updated to point at the next row. If the cursor has no more rows the variables will be set to null on the first FETCH attempt, subsequent FETCH attempts will raise an exception.

The CLOSE statement releases the cursor and any rows within it, you can open the cursor again to refresh the data in it.

DECLARE
CURSOR MYCUR IS SELECT ISBN, COST FROM JD11.BOOK;
THISISBN NUMBER(10);
THISCOST NUMBER(10,2);
BEGIN
OPEN MYCUR;
FETCH MYCUR INTO THISISBN, THISCOST;
CLOSE MYCUR;
END;

To process all the rows within a cursor we simply need to place the FETCH statement in a loop and check the cursor NOTFOUND attribute to see if we successfully fetched a row or not.

DECLARE
CURSOR MYCUR IS SELECT ISBN, COST FROM JD11.BOOK;
THISISBN NUMBER(10);
THISCOST NUMBER(10,2);
BEGIN
OPEN MYCUR;
LOOP
FETCH MYCUR INTO THISISBN, THISCOST;
EXIT WHEN MYCUR%NOTFOUND;
END LOOP;
CLOSE MYCUR;
END;

PL/SQL records may be based on a cursor. This is very convenient for processing rows from the active data set. An example follows :

DECLARE
CURSOR MYCUR IS SELECT ISBN, COST FROM JD11.BOOK;
PARTBOOK MYCUR%ROWTYPE;
BEGIN
OPEN MYCUR;
LOOP
FETCH MYCUR INTO PARTBOOK;
EXIT WHEN MYCUR%NOTFOUND;
IF PARTBOOK.ISBN = 21 THEN
PARTBOOK.COST = 19.10;
END IF;
END LOOP;
CLOSE MYCUR;
END;

You can use the WHERE CURRENT OF clause to execute DML commands against the current row of a cursor, this makes it easier to update rows. An example is below :

DECLARE
CURSOR MYCUR IS SELECT ISBN, COST FROM JD11.BOOK;
PARTBOOK MYCUR%ROWTYPE;
BEGIN
OPEN MYCUR;
LOOP
FETCH MYCUR INTO PARTBOOK;
EXIT WHEN MYCUR%NOTFOUND;
IF PARTBOOK.ISBN = 21 THEN
DELETE FROM JD11.BOOK WHERE CURRENT OF MYCUR;
END IF;
END LOOP;
CLOSE MYCUR;
END;

Note that I didn’t need to explicitly specify the row that I want deleted, PL/SQL supplies the required row identifier from the current record in the cursor ensuring that only the correct row is deleted.

It’s possible to vary the returned result set by using parameters, parameters allow you to specify the query selection criteria when you open the cursor.

DECLARE
CURSOR MYCUR (PARAM1 NUMBER) IS SELECT ISBN, COST FROM JD11.BOOK WHERE ISBN = PARAM1;
PARTBOOK MYCUR%ROWTYPE;
BEGIN
OPEN MYCUR(21);
FETCH MYCUR INTO PARTBOOK;
CLOSE MYCUR;
OPEN MYCUR(101);
FETCH MYCUR INTO PARTBOOK;
CLOSE MYCUR;
END;

Blocking Oracle access by IP Address

2007-09-06T01:58:00.000-07:00

Now comes the fun part: keeping people out of your database! While IP-based blocking is not as suitable as a full firewall, you are able to block clients based on their IP address or hostname.

The secret lies in the SQLNET.ORA file. This file can be found in your $ORACLE_HOME/network/admin directory along with your tnsnames.ora and listener.ora. Open it up and insert the following line:

tcp.validnode_checking = yes

This turns on the hostname/IP checking for your listeners. After this, you can supply lists of nodes to enable/disable, as such:

tcp.invited_nodes = (hostname1, hostname2)
tcp.excluded_nodes = (172.16.1.3)

Note that if you only specify invited nodes, all others will be excluded, so there is really no reason to do both. The same goes for excluded nodes: exclude a list of clients, invite all others.

Even though this will not protect you against advanced attacks (IP and hostname are easy to spoof) it still serves as a deterrent against hacking attempts.

Here are some rules for entering invited/excluded nodes:

You cannot use wildcards in your specifications.
You must put all invited nodes in one line; likewise for excluded nodes.
You should always enter localhost as an invited node.

Once you have set up your rules and enabled valid node checking, you must restart your listeners to reap the benefits.Here is an example:

PayrollDB is a database server, accessed by Payroll
BankingDB is a database server, accessed by Banking
BApp1, BApp2, andBApp3 are application servers using the BankingDB
PApp1, PApp2, and PApp3 are application servers using the PayrollDB

The sqlnet.ora on PayrollDB would look like this:

tcp.validnode_checking = yes
tcp.invited_nodes = (localhost, PayrollDB, PApp1, PApp2, PApp3)

The sqlnet.ora on SalesDB would look like this:

tcp.validnode_checking = yes
tcp.invited_nodes = (localhost, BankingDB, BApp1, BApp2, BApp3)

Once this has been done, restart the listener.

$ lsnrctl

LSNRCTL> set password
Password:

The command completed successfully

LSNRCTL> stop
The command completed successfully

LSNRCTL> start

Now PApp1, PApp2, and PApp3 can access PayrollDB but not BankingDB; the same goes for the Banking application servers' access to PayrollDB.

PL/SQL Records

2007-09-06T00:17:00.000-07:00

A PL/SQL record is a variable that contains a collection of separate fields. Each field is individually addressable. You can reference the field names in both assignments and expressions. The fields within a record may have different datatypes and sizes, like the columns of a database table. Records are a convenient way of storing a complete fetched row from a database table.

Use the %ROWTYPE attribute to declare a record based upon a collection of database columns from a table or view. The fields within the record take their names and datatypes from the columns of the table or view.

Declare the record in the DECLARE section along with any other required variables and constants. An example follows :
DECLARE
REC1 JD11.BOOK%ROWTYPE;
REC4 JD11.BOOK%ROWTYPE;

The above declaration sets the object REC1 to be a record object holding fields that match the columns in the BOOK table. It doesn't hold any values until it is populated.

Assign values into a PL/SQL record by naming the record after the INTO keyword of a SELECT statement. The INTO keyword defines the name specification for the storage area(s) of queried value(s).
BEGIN
SELECT * FROM JD11.BOOK INTO REC1 WHERE ISBN = 21;
END;

You can assign all the record values from one record to another provided that the record definitions are the same.

BEGIN
REC4 := REC1;
END;

Reference the field values within a PL/SQL record like this :

BEGIN
REC4 := REC1;
IF REC4.COST > 0 THEN
REC4.SECTION_ID := 10;
ELSE
REC4.SECTION_ID := 7;
END IF;
END;

Discovering PL/SQL Errors

2007-09-06T00:11:00.000-07:00

PL/SQL does not always tell you about compilation errors. Instead, it gives you a cryptic message such as "procedure created with compilation errors". If you don't see what is wrong immediately, try issuing the command

show errors procedure ;

Alternatively, you can type, SHO ERR (short for SHOW ERRORS) to see the most recent compilation error.

Note that the location of the error given as part of the error message is not always accurate!

Control Flow in PL/SQL

2007-09-06T00:02:00.000-07:00

PL/SQL allows you to branch and create loops in a fairly familiar way.
An IF statement looks like:

IF THEN ELSE END IF;

The ELSE part is optional. If you want a multiway branch, use:

IF THEN ...

ELSIF THEN ...
... ...
ELSIF THEN ...

ELSE ...

END IF;

Loops are created with the following:

LOOP
/* A list of statements. */
END LOOP;

At least one of the statements in should be an EXIT statement of the form

EXIT WHEN ;

The loop breaks if is true.
Some other useful loop-forming statements are:

EXIT by itself is an unconditional loop break. Use it inside a conditional if you like.
A WHILE loop can be formed with
WHILE LOOP

END LOOP;
A simple FOR loop can be formed with:
FOR IN .. LOOP END LOOP; Here, can be any variable; it is local to the for-loop and need not be declared. Also, and are constants.

Variables and Types

2007-09-05T23:43:00.001-07:00

Information is transmitted between a PL/SQL program and the database through variables. Every variable has a specific type associated with it. That type can be :

One of the types used by SQL for database columns
A generic type used in PL/SQL such as NUMBER
Declared to be the same as the type of some database column

The most commonly used generic type is NUMBER. Variables of type NUMBER can hold either an integer or a real number. The most commonly used character string type is VARCHAR(n), where n is the maximum length of the string in bytes. This length is required, and there is no default. For example, we might declare:

DECLARE
price NUMBER;
myBeer VARCHAR(20);

Note that PL/SQL allows BOOLEAN variables, even though Oracle does not support BOOLEAN as a type for database columns.
Types in PL/SQL can be tricky. In many cases, a PL/SQL variable will be used to manipulate data stored in a existing relation. In this case, it is essential that the variable have the same type as the relation column. If there is any type mismatch, variable assignments and comparisons may not work the way you expect. To be safe, instead of hard coding the type of a variable, you should use the %TYPE operator. For example:

DECLARE
myBeer Beers.name%TYPE;

gives PL/SQL variable myBeer whatever type was declared for the name column in relation Beers.
A variable may also have a type that is a record with several fields. The simplest way to declare such a variable is to use %ROWTYPE on a relation name. The result is a record type in which the fields have the same names and types as the attributes of the relation. For instance:

DECLARE
beerTuple Beers%ROWTYPE;

makes variable beerTuple be a record with fields name and manufacture, assuming that the relation has the schema Beers(name, manufacture).
The initial value of any variable, regardless of its type, is NULL. We can assign values to variables, using the ":=" operator. The assignment can occur either immediately after the type of the variable is declared, or anywhere in the executable portion of the program. An example:

DECLARE
a NUMBER := 3;
BEGIN
a := a + 1;
END;
.

run;

This program has no effect when run, because there are no changes to the database.

Scope of Block Objects

2007-09-05T23:38:00.000-07:00

The scope of an object defines where it is visible, it is the area of the program logic that can legally refer to a given object.

A variable or object's (cursor, constant Etc.) scope is determined by the block that it is declared in. It is only available until the block it is defined in ends execution. Remember that block variables are defined under the DECLARE keyword.

For nested blocks an object defined in a parent block is available within all its child (nested blocks). The reverse is not true, objects defined in a child block are not visible to the parent.

If a nested block defines an object with the same name as an object in its parent block then only the local object is visible. Unlike Java there is no method provided to get at the parent object in this circumstance.

Basic PL/SQL Block Structure

2007-09-05T23:11:00.000-07:00

PL/SQL stands for Procedural Language/SQL. PL/SQL extends SQL by adding constructs found in procedural languages, resulting in a structural language that is more powerful than SQL. The basic unit in PL/SQL is a block. All PL/SQL programs are made up of blocks, which can be nested within each other. Typically, each block performs a logical action in he program. A block has the following structure:

DECLARE

Definition of any variables or objects that are used within the declared block.

BEGIN

Statements that make up the block.

EXCEPTION

All exception handlers.

END;

example:
DECLARE
TEMP_COST NUMBER(10,2);
BEGIN
SELECT COST FROM JD11.BOOK INTO TEMP_COST WHERE ISBN = 21;
IF TEMP_COST > 0 THEN
UPDATE JD11.BOOK SET COST = (TEMP_COST*1.175) WHERE ISBN = 21;
ELSE
UPDATE JD11.BOOK SET COST = 21.32 WHERE ISBN = 21;
END IF;
COMMIT;
EXCEPTION
WHEN NO_DATA_FOUND THEN
INSERT INTO JD11.ERRORS (CODE, MESSAGE) VALUES(99, 'ISBN 21 NOT FOUND');
END;

As you can see there are several elements in the example that haven't been covered in SQL , these elements are the PL/SQL extensions. They include:
- Variables and Constants
- SQL Support
- Composite Datatypes
- Flow Control
- Built In Functions
- Cursor Handling
- Exception Handling
- Code Storage

The rules of block structure are :

Every unit of PL/SQL must constitute a block. As a minimum there must be the delimiting words BEGIN and END around the executable statements.
SELECT statements within PL/SQL blocks are embedded SQL (an ANSI category). As such they must return one row only. SELECT statements that return no rows or more than one row will generate an error. If you want to deal with groups of rows you must place the returned data into a cursor. The INTO clause is mandatory for SELECT statements within PL/SQL blocks (which are not within a cursor definition), you must store the returned values from a SELECT.
If PL/SQL variables or objects are defined for use in a block then you must also have a DECLARE section.
If you include an EXCEPTION section the statements within it are only processed if the condition to which they refer occurs. Block execution is terminated after an exception handling routine is executed.
PL/SQL blocks may be nested, nesting can occur wherever an executable statement could be placed (including the EXCEPTION section).

Oracle DBA : Poor Oracle Performance

2007-09-05T22:41:00.000-07:00

For an Oracle DBA, here are some reasons that was consider as the most common root-causes of poor Oracle performance:

Bad Design – The number one offender to poor performance is over-normalization of Oracle tables, excessive (unused indexes) and 15-way table joins for what should be a simple fetch.
Bad disk I/O configuration – Inappropriate use of RAID5, disk channel bottlenecks and poor disk striping.
Object contention – Failing to set ASSM, freelists or freelist_groups for DML-active tables and indexes can cause very slow DML performance.
Non-reentrant SQL – All SQL should use host variables/cursor_sharing=force to make SQL reusable within the library cache.
Excessive nested loop joins – In 64-bit Oracle systems we have gigabytes available for RAM sorts and hash joins. Failing to set pga_aggregate_target to allow the CBO to choose hash joins can result in very slow SQL performance.
Poor server optimization – Setting the server kernel parameters and I/O configuration (e.g. direct I/O) has a profound impact on Oracle performance.
Poor Optimizer Statistics – Prior to Oracle 10g (automatic statistics), a common cause of poor SQL performance was missing/stale CBO statistics and missing histograms.
Under-allocated RAM regions – Not allocating enough RAM for shared_pool_size, pga_aggregate_target and db_cache_size can cause an I/O-bound database.
Un-set initialization parameters – Many of the initialization parameters are made to be set by the DBA (db_file_multiblock_read_count, optimizer_index_caching) and failing to set these parameters properly results in poorly optimized execution plans.
Human Misfeasance – The DBA’s failure to monitor their database (STATSPACK/AWR), set-up exception reporting alerts (OEM) and adjusting their instance to match changing workloads is a major cause of poor performance.

Oracle temporary tablespace groups (10g)

2007-09-05T17:58:00.000-07:00

A temporary tablespace group consists of only temporary tablespace, and has the following properties:

It contains one or more temporary tablespaces.
It contains only temporary tablespace.
It is not explicitly created. It is created implicitly when the first temporary tablespace is assigned to it, and is deleted when the last temporary tablespace is removed from the group.

Temporary tablespace group has the following benefits:

It allows multiple default temporary tablespaces to be specified at the database level.
It allows the user to use multiple temporary tablespaces in different sessions at the same time.
It allows a single SQL operation to use multiple temporary tablespaces for sorting.

Using a tablespace with a temporary tablespace group will result in the following select statement. However, using a tablespace without a temporary tablespace group will not return the select statement below.
select
tablespace_name, group_name
from
DBA_TABLESPACE_GROUPS;
TABLESPACE_NAME GROUP_NAME
--------------- -----------
TEMP01 TEMPGROUP_A
TEMP02 TEMPGROUP_A
TEMP03 TEMPGROUP_B

Example:
- Create a temporary tablespace and implicitly add it to a temporary tablespace group.

CREATE TEMPORARY TABLESPACE temp01
TEMPFILE ‘/u02/oradata/temp01.dbs’ SIZE 500M
TABLESPACE GROUP tempgroup_a;

- Create a temporary tablespace without assigning it to a temporary tablespace group.

The following two statements are exactly equivalent:

CREATE TEMPORARY TABLESPACE temp04
TEMPFILE ‘/u02/oradata/temp04.dbs’ SIZE 200M
TABLESPACE GROUP ‘’;

CREATE TEMPORARY TABLESPACE temp04
TEMPFILE ‘/u02/oradata/temp04.dbs’ SIZE 200M;

- Remove a temporary tablespace from a temporary tablespace group.

This statement will remove temporary tablespace temp04 from its original temporary tablespace group:

ALTER TABLESPACE temp04 TABLESPACE GROUP ‘‘;
- Add a temporary tablespace to a temporary tablespace group.

ALTER TABLESPACE temp03 TABLESPACE GROUP tempgroup_b;

- Assign a user to a default temporary tablespace group.

ALTER USER USER TEMPORARY TABLESPACE tempgroup_A;

In this case, user 'USER' will have multiple default temporary tablespaces (see Figure). A single SQL operation by 'USER' can use more than one temporary tablespace for sorting.

Killing Oracle Session

2007-09-05T17:08:00.000-07:00

One of the most common mistakes are DBA's killing the wrong session. When killing a session, carefully review the session, process and SQL associated with the offensive task. Before killing the session, the DBA may decide to view the SQL being executed by the session.
Killing session with Oracle can be a challenge and different procedures exist within Windows and UNIX. Here are the steps that I use to kill an Oracle session:
1. Gather session information from Oracle
2. Kill the session at the OS-level
3. Kill the session within Oracle using the "alter system kill session" command:

UNIX - I always locate the Server PID (SPID) from v$process and issue the UNIX kill -9 command.
The Windows command to kill this session would be as follows:
C:\oracle9i\bin>orakill ORCL92 768

In this example, the windows thread corresponding to the Oracle session can be killed in the operating system without ever logging into the database.
Here are the Oracle scripts example to gather information before killing a session:

session.sql script

rem session.sql - displays all connected sessions
set echo off;
set termout on;
set linesize 80;
set pagesize 60;
set newpage 0;

select
rpad(c.name||':',11)||rpad(' current logons='||
(to_number(b.sessions_current)),20)||'cumulative logons='||
rpad(substr(a.value,1,10),10)||'highwater mark='||
b.sessions_highwater Information
from
v$sysstat a,
v$license b,
v$database c
where
a.name = 'logons cumulative'
;

ttitle "dbname Database|UNIX/Oracle Sessions";

set heading off;
select 'Sessions on database '||substr(name,1,8) from v$database;
set heading on;

select
substr(a.spid,1,9) pid,
substr(b.sid,1,5) sid,
substr(b.serial#,1,5) ser#,
substr(b.machine,1,6) box,
substr(b.username,1,10) username,
-- b.server,
substr(b.osuser,1,8) os_user,
substr(b.program,1,30) program
from
v$session b,
v$process a
where
b.paddr = a.addr
and type='USER'
order by spid;
ttitle off;
set heading off;
select 'To kill, enter SQLPLUS> ALTER SYSTEM KILL SESSION',
''''||'SID, SER#'||''''||';' from dual;
spool off;

Oracle ORA-03113: end-of-file on communication channel

2007-09-05T02:46:00.000-07:00

The ORA-03113: end-of-file on communication channel error is a generic error. The ORA-03113 error when connecting suggests that the connection was established, but lost later, like a timeout.
There are many possible causes for the ORA-03113 error.
For example, ORA-3113 could be signaled for any of these scenarios:
- Server machine crashed
- Your server process was killed at O/S level
- Network problems
- Oracle internal errors / aborts on the server
- Client incorrectly handling multiple connections
- Etc

It goes on to note that the ORA-03113 End-Of-File error often has additional messages:
It is common for this error to be accompanied by other errors such as:
- ORA-1041 internal error. hostdef extension doesn't exist
- ORA-3114 not connected to ORACLE
- ORA-1012 not logged on

To find more details, check for a trace file in your USER_DUMP_DEST and BACKGROUND_DMP_DEST directory.

ORA-00054: resource busy and acquire with NOWAIT specified

2007-09-03T17:53:00.000-07:00

When You get an ORA-00054, it's usually impossible to do the DDL unless You spend hours inconveniencing end-users by nuking their sessions.

ORA-00054: resource busy and acquire with NOWAIT specified
Cause: Resource interested is busy.
Action: Retry if necessary.

You have choices for avoiding the ORA-00054 error:

Re-run the change late at night when the database is idle.
Do all DDL during a maintenance window with all end-users locked-out.
Kill the sessions that are preventing the exclusive lock.

Oracle has several views for showing lock status, some of which show the username:

DBA_BLOCKERS – Shows non-waiting sessions holding locks being waited-on
DBA_DDL_LOCKS – Shows all DDL locks held or being requested
DBA_DML_LOCKS - Shows all DML locks held or being requested
DBA_LOCK_INTERNAL – Displays 1 row for every lock or latch held or being requested with the username of who is holding the lock
DBA_LOCKS - Shows all locks or latches held or being requested
DBA_WAITERS - Shows all sessions waiting on, but not holding waited for locks

The DBA_LOCK_INTERNAL view used to show locks for a specific user, and you can specify the query in the form:

SELECT
NVL(b.username,'SYS') username,
session_id,lock_type,mode_held,
mode_requested,lock_id1,lock_id2
FROM
sys.dba_lock_internal a,
sys.v_$session b
where . . .

You can also query v$access and v$locked_object to see specific locks:

select s.sid, s.serial#, p.spid
from
v$session s,
v$process p
where
s.paddr = p.addr
and
s.sid in (select SESSION_ID from v$locked_object);

You can simply kill the session if you're sure:
ALTER SYSTEM KILL SESSION ('sid,serial#');

ORA-12154: TNS: could not resolve service name

2007-09-03T17:30:00.000-07:00

The ORA-21254 is a very common error, always related to your local copy of the tnsnames.ora file, your listener configuration and your database name aliases.

Make sure that your listener is listening for the same service name that you are using.
Your service name might have an alias, so check for global (world) entries and local entries. Check $ORACLE_HOME/network/admin/tnsnames.ora.
Check your global_name setting with this SQL:
SQL>SELECT * FROM GLOBAL_NAME;

The query value should match your init.ora parms for db_name and db_domain, You can change the global_name with an alter database commend, as follows:

SQL>alter database rename global_name to xxx;

Methods for Moving Datafiles

2007-09-02T23:49:00.001-07:00

Depending on the type of tablespace, the database administrator can move datafiles using one of the following two methods :

The ALTER TABLESPACE Command
Precondition :
- Tablespace must be offline
- Target datafiles must be exist

Example of usage:
ALTER TABLESPACE userdata RENAME
DATAFILE '/u01/oradata/userdata01.dbf'
TO '/u02/oradata/userdata01.dbf';

Step to rename a datafile:
- Take the tablespace offline
- Use an OS command to move or copy the files
- Execute the ALTER TABLESPACE RENAME DATAFILES command
- Bring the tablespace online
- Use an OS command to delete the old file if necessary

The ALTER DATABASE Command
The ALTER DATABASE command can be used to move any type of datafile
Precondition:
- Database must be mounted
- Target datafile must exist

Example of usage:
ALTER DATABASE RENAME
FILE '/u01/oradata/system01.dbf'
TO '/u02/oradata/system01.dbf';

Because of the SYSTEM tablespace cannot be taken offline, you must use this method to move datafiles in the SYSTEM tablespace.

Use the following steps to rename files in tablespaces that cannot be taken offline:
- Shut down the database.
- Use an OS Command to move the files.
- Mount the database.
- Execute the ALTER DATABASE RENAME FILE command.
- Open the database

Delete Without Rollback ?

2007-09-02T22:55:00.000-07:00

A few people asking me, is there a a way to delete a table record 'permanently' without using the rollback segments? In some cases if the table have 'so many records' and if there are no more space in the rollback segment, deleting records would be fail.
Indeed there is a way ....
Using the "TRUNCATE" command will bypass the rollback segments when
deleting records. For example, "TRUNCATE TABLE Table_Name;" will
delete all records.
Note that no triggers that may exist get fired. Also, do not do this to a
table that is a master snapshot.

Locally vs. Dictionary Managed Tablespaces

2007-09-02T22:02:00.000-07:00

By declaring a tablespace as DICTIONARY managed, you are specifying that extent management for segments in this tablespace will be managed using the following dictionary tables:

sys.fet$	Every extent for a particular segment is represented by a row in the `sys.fet$` dictionary table. `sys.fet$` is actually clustered in the `C_TS#` cluster.
sys.uet$	Data dictionary view used to store used extents. `sys.uet$` is actually clustered in the `C_FILE#_BLOCK#` cluster.

Oracle updates the above tables in the data dictionary whenever an extent is allocated, or freed for reuse.

To Create Dictionary Managed Tablespaces you can use the following example:

CREATE TABLESPACE users
DATAFILE '/u10/app/oradata/TESTDB/user01.dbf' SIZE 50M
EXTENT MANAGEMENT DICTIONARY
DEFAULT STORAGE (
INITIAL 64K
NEXT 64K
MINEXTENTS 2
MAXEXTENTS 121
PCTINCREASE 0);

A Locally Managed Tablespace is a tablespace that manages its own extents by maintaining a bitmap in each datafile to keep track of the free or used status of blocks in that datafile. Each bit in the bitmap corresponds to a block or a group of blocks. When the extents are allocated or freed for reuse, Oracle simply changes the bitmap values to show the new status of the blocks. These changes do not generate rollback information because they do not update tables in the data dictionary (except for tablespace quota information). Some of the benefits of using a locally managed tablespace are:

Reduced recursive space management.
Reduced contention on data dictionary tables.
No rollback generated.
No coalescing required.

The following statement creates a locally managed tablespace named USERS, where AUTOALLOCATE causes Oracle to automatically manage extent size.

CREATE TABLESPACE users
DATAFILE '/u10/app/oradata/TESTDB/user01.dbf' SIZE 50M
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

Alternatively, this tablespace could be created specifying the UNIFORM clause. In this example, a 512K extent size is specified. Each 512K extent (which is equivalent to 64 Oracle blocks of 8K) is represented by a bit in the bitmap for this file.

CREATE TABLESPACE users
DATAFILE '/u10/app/oradata/TESTDB/user01.dbf' SIZE 50M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K;

Advantages in using Locally Managed Tablespaces Over Dictionary Managed Tablespaces :

Because locally managed tablespaces do not record free space in data dictionary, it reduces contention on these tables.
Local management of extents automatically tracks adjacent free space, eliminating the need to coalesce free extents.
Avoids recursive space management operations, which can occur in dictionary-managed tablespaces if consuming or releasing space in an extent results in another operation that consumes or releases space in a rollback segment or data dictionary table.
Sizes of extents that are managed locally can be determined automatically by the system. Alternatively, all extents can have the same size in a locally managed tablespace.
Changes to the extent bitmaps do not generate rollback information because they do not update tables in the data dictionary (except for special cases such as tablespace quota information).
Reduced fragmentation .

Oracle Database Shutdown Stages

2007-09-02T21:32:00.000-07:00

What happens during the different Oracle shutdown options?

Database Shutdown options:

NORMAL - No new connections, waits for all users to disconnect, closes and dismounts database before instance shutdown, no instance recovery required upon next db startup.
IMMEDIATE - Rolls back all current uncommitted transactions and disconnects users, closes and dismounts database before instance shutdown, no instance recovery required upon next db startup.
TRANSACTIONAL - No new connections, users disconnected when their current transaction is completed, when all "current transactions are complete shutdown immediate is executed.
ABORT - All transactions are terminated, no transactions are rolled back, instance terminated without closing the data files, next startup will require instance recovery.

Resizing Temporary Tablespace

2007-09-02T20:39:00.005-07:00

Sometimes, in many database configuration, the DBA will choose to allow their temporary tablespace to AUTOEXTEND. A runaway query or sort can easily chew up valuable space on the disk as the tempfiles(s) extends to accommodate the request for space.
If the increase in size of the temporary tablespace (the tempfiles) gets exceedingly large because of a particular anomaly, the DBA will often want to resize the temporary tablespace to a more reasonable size in order to reclaim that extra space.
The obvious action would be to resize the tempfiles using the following statement:

SQL> alter database tempfile '/u02/oradata/TESTDB/temp01.dbf' resize 250M;
alter database tempfile '/u02/oradata/TESTDB/temp01.dbf' resize 250M
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value

The best practice are:
- create another temporary tablespace
- make the new temporary tablespace as the default temporary tablespace
or if you want to use the old tablespace name
- drop the old temporary tablespace, and recreate it, set it as the default temporary tablespace
- drop the other temporary tablespace

Example:
SQL> DROP TABLESPACE temp;
drop tablespace temp
*
ERROR at line 1:
ORA-12906: cannot drop default temporary tablespace

SQL> CREATE TEMPORARY TABLESPACE temp2
2 TEMPFILE '/u02/oradata/TESTDB/temp2_01.dbf' SIZE 5M REUSE
3 AUTOEXTEND ON NEXT 1M MAXSIZE unlimited
4 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

Tablespace created.

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

Database altered.

SQL> DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

SQL> CREATE TEMPORARY TABLESPACE temp
2 TEMPFILE '/u02/oradata/TESTDB/temp01.dbf' SIZE 500M REUSE
3 AUTOEXTEND ON NEXT 100M MAXSIZE unlimited
4 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

Tablespace created.

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

Database altered.

SQL> DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

NOTE:
On some platforms , it is possible for the tempfile to be deleted from DBA_TEMP_FILES but not from the hard drive of the server.

If this occurs, simply delete the file using regular O/S commands.

Creating Tablespace

2007-09-02T19:24:00.001-07:00

Types of Tablespace
1. SYSTEM Tablespace
- Created with the database
- Contains the data dictionary
- Contains the SYSTEM undo segment
2. Non-SYSTEM Tablespace
- Separate segments
- Eases space administration
- Controls amount of space allocated to a user

Create a tablespace with the CREATE TABLESPACE command:

CREATE TABLESPACE tablespace
[DATAFILES clause]
[MINIMUM EXTENT integer [K|M] ]
[BLOCKSIZE integer [K] ]
[LOGGING | NOLOGGING]
[DEFAULT storage_clause]
[ONLINE | OFFLINE ]
[PERMANENT | TEMPORARY]
[extent_management_clause]
[segment_management_clause]

Example:

CREATE TABLESPACE userdb
DATAFILE '/u01/oradata/userdb01.dbf' SIZE 100M
AUTOEXTEND ON NEXT 5M MAXSIZE 200M;

For locally managed tablespace:

CREATE TABLESPACE userdb
DATAFILE '/u01/oradata/userdb01.dbf' SIZE 100M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;

Creating Database

2007-09-02T18:10:00.000-07:00

To create a database, use the following SQL command :

CREATE DATABASE [database]
 [CONTROLFILE REUSE]
 [LOGFILE [GROUP integer] filespec
 [MAXLOGFILES integer]
 [MAXLOGMEMBERS integer]
 [MAXLOGHISTORY integer]
 [MAXDATAFILES integer]
 [MAXINSTACES integer]
 [ARCHIVELOG |NOARCHIVELOG]
 [CHARACTER SET charset]
 [NATIONAL CHARACTER SET charset]
[DATAFILES filespec [autoextend_clause]
filespec :== 'filename' [SIZE integer] [K|M] [REUSE]
 autoextend_clause :==
 [AUTOEXTEND {OFF | ON [NEXT integer [K|M] ] [MAXSIZE {UNLIMMITED | integer [K|M] } } ]
[DEFAULT TEMPORARY TABLESPACE tablespace filespec [temp_tablespace_extend_clause]
temp_tablespace_extend_clause :==
EXTENT MANAGEMENT LOCAL UNIFORM [SIZE integer] [K|M] ]

[UNDO TABLESPACE tablespace DATAFILE filespec [autoextend_clause] ]
[SET TIME_ZONE [time_zone_region] ]

Example:
CREATE DATABASE userdb
 LOGFILE
  GROUP 1 ('/$HOME/ORADATA/u01/redo1.log') SIZE 100M,
  GROUP 2 ('/$HOME/ORADATA/u02/redo1.log') SIZE 100M,
   GROUP 3 ('/$HOME/ORADATA/u03/redo1.log') SIZE 100M
 MAXLOGFILES 5
 MAXLOGMEMBERS 5
 MAXLOGHISTORY 1
 MAXDATAFILES 100
 MAXINSTANCES 1
 DATAFILE '/$HOME/ORADATA/u01/system01.dbf' SIZE 300M
 UNDO TABLESPACE undotbs
 DATAFILE '/$HOME/ORADATA/u02/undotbs01.dbf' SIZE 300M
  AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
 DEFAULT TEMPORARY TABLESPACE temp
 CHARACTER SET US7ASCII
 NATIONAL CHARACTER SET AL16UTF16
 SET TIME_ZONE = 'America/New_York'

The Dual Table

2007-09-02T17:17:00.001-07:00

Dual Table is a table that contais a single row.
The dual table has one VARCHAR2 column named dummy.
Dual contains a single row with the value X.

Oracle has created this since it makes some calculations more convenient.

SQL> describe dual;
Name  Type        Nullable Default Comments 
----- ----------- -------- ------- -------- 
DUMMY VARCHAR2(1) Y

SQL> select * from dual;

DUMMY
-----
X

You can use it for math :
SQL> Select (202*44)/4 from dual;

(202*44)/4
----------
  2222

You can use it to increment sequences : 
SQL> select seq_no_tx.nextval from dual;

 NEXTVAL
----------
    1402

Privacy Policy

2007-09-01T20:56:00.000-07:00

If you require any more information or have any questions about our privacy policy, please feel free to contact us by email at sprpopo@yahoo.com.

At oracletipstricks.blogspot.com, the privacy of our visitors is of extreme importance to us. This privacy policy document outlines the types of personal information is received and collected by oracletipstricks.blogspot.com and how it is used.

Log Files
Like many other Web sites, oracletipstricks.blogspot.com makes use of log files. The information inside the log files includes internet protocol ( IP ) addresses, type of browser, Internet Service Provider ( ISP ), date/time stamp, referring/exit pages, and number of clicks to analyze trends, administer the site, track user’s movement around the site, and gather demographic information. IP addresses, and other such information are not linked to any information that is personally identifiable.

Cookies and Web Beacons
oracletipstricks.blogspot.com does use cookies to store information about visitors preferences, record user-specific information on which pages the user access or visit, customize Web page content based on visitors browser type or other information that the visitor sends via their browser.

Some of our advertising partners may use cookies and web beacons on our site. Our advertising partners include Google Adsense, Kontera.

Oracle Tips & Tricks utilizes certain services from Google for ad serving and web traffic analysis. Please review Google's privacy policy for more information on how Google AdSense and Google Analytics stores your personal information. These Google servies may place and read cookies on your browser, or use web beacons to collect information, in the course of ads being served on the Oracle Tips & Tricks website. Additionally, Google uses the DART cookie to enables it to serve ads to based on your visits to Oracle Tips & Tricks and other sites on the Internet. You may opt out of the use of the DART cookie by visiting the Google ad and content network privacy policy.

These third-party ad servers or ad networks use technology to the advertisements and links that appear on oracletipstricks.blogspot.com send directly to your browsers. They automatically receive your IP address when this occurs. Other technologies ( such as cookies, JavaScript, or Web Beacons ) may also be used by the third-party ad networks to measure the effectiveness of their advertisements and / or to personalize the advertising content that you see.

oracletipstricks.blogspot.com has no access to or control over these cookies that are used by third-party advertisers.

You should consult the respective privacy policies of these third-party ad servers for more detailed information on their practices as well as for instructions about how to opt-out of certain practices. oracletipstricks.blogspot.com's privacy policy does not apply to, and we cannot control the activities of, such other advertisers or web sites.

If you wish to disable cookies, you may do so through your individual browser options. More detailed information about cookie management with specific web browsers can be found at the browsers' respective websites.

Oracle Ebook Download

2007-09-01T16:58:00.000-07:00