Oracle DBA Tips

Tip#28 Thin (JDBC) Vs Thick (OCI) Driver

2010-08-09T12:03:00.000+02:00

JDBC Thin Vs OCI (Thick)

Loads of info available on what it means and which one to use etc etc. I just put two of the links which says it all,

Horse's Mouth : Oracle Document Link

Generally the Thin driver is the best choice. In most cases it is as fast or faster than the OCI driver (from 10.1.0), has almost exactly the same set of features, and is easier to administer.

In a few cases the OCI driver has slightly better performance. The OCI driver supports a few Oracle features better than the Thin driver.
The Thin driver is easier to administer since it does not require installation of the OCI C libraries.
The Thin driver will work on any machine that has a suitable Java VM, whereas with the OCI driver you must install the proper OCI C libraries for each machine.

We recommend using the Thin driver unless you must have one or more of the OCI only features, or until it is clear that the small performance gain provided by the OCI driver is worth the extra effort.

Thin Vs Thick Performance Test : Test Link

Final Word :

"The Thin driver clearly outperforms the OCI driver for every type of operation except executions of CallableStatement objects. On a Unix platform, my experience has been that the CallableStatement numbers are tilted even more in favor of the OCI driver. Nonetheless, you can feel completely comfortable using the Thin driver in almost any setting. The Thin driver has been well-tuned by Oracle's JDBC development team to perform better than its OCI counterpart."

Tip#27 Listener Log File Too Big

2010-08-04T14:20:00.001+02:00

Over a period it is quite often you will see that listener.log in ORACLE_HOME/network/log has grown to X GB in size.So how do we manage it ?

Do not have to stop the listener in order to delete the log files, just do the following

$> lsnrctl

LSNRCTL> set current_listener listener_name 
LSNRCTL> show log_file
LSNRCTL> set log_file xxx.log
LSNRCTL> show log_file
LSNRCTL> exit

$> remove or backup the old listener.log

Tip#26 OMS generates lot of core.xxx files

2009-10-01T13:22:00.004+02:00

Recently we had disk full issue on our Grid Control OMS server, firstly we thought it must be just few backup copies which are lying around. After cleaning up the disk of unwanted backups got some breathing space (atleat thats what we thought!) but soon found out it is full again in like an hour.

So digging further found out that OMS was generating HUGE amount of logs (like core.xxx) and at very brisk speed too. Metalink Doc ID 419999.1 says it is due to "the access_log for the http server of the OMS is over 2Gb in size and this is causing the http server to core dump." and to fix it we need to stop all OMS process, remove the log file and startup the OMS process. So basically,

/opmn/bin/opmnctl stopall

rm /Apache/Apache/logs/access_log

/opmn/bin/opmnctl startall

After doing that all seems to be back to normal. To avoid the same in future, as suggested in the metalink, Consider rotating the Apache logs on a regular basis as part of the routine maintenance of the system. This can be done while the OMS is down during monthly maintenance tasks.

Tip#25 Fix for ORA-38029 : object statistics are locked

2009-07-15T17:34:00.003+02:00

Recently while analyzing tables in one of our test database, we got ORA-38029: object statistics are locked. It turns out that in 10gR2, when you import (imp or impdp) table without data i.e. structure only, oracle will lock the table statistics. (ref : metalink doc id 433240.1)

You can see list of all locked tables in a schema by running following query:

select table_name, stattype_locked from dba_tab_statistics where owner = 'MBS' and stattype_locked is not null;

To generate unlock statement for all tables in the schema you can use following,

select 'exec DBMS_STATS.UNLOCK_TABLE_STATS ('''|| owner ||''','''|| table_name ||''');' from dba_tab_statistics where owner = 'MBS' and stattype_locked is not null;

OR for each table individually the following,

exec DBMS_STATS.UNLOCK_TABLE_STATS('owner','table name');

Tip#24 Fix for ORA-12545 on RAC

2009-06-17T12:23:00.002+02:00

We had a case recently with a 10gR2 RAC, Some clients getting ORA-12545 errors i.e. ORA-12545 Connect failed because target host or object does not exist. TNS and Listener entries were all fine and TNSPING was also working so it had to be something else.

On digging further and googling a bit it turned out that root cause was that the client was being redirected to the server hostname instead of virtual addresses. Due to that the client had to resolve the server hostname and in some cases it could not ( e.g. no entry in their DNS & client Host file), hence the error. Here is the link where it is nicely explained Link .

Hence two thing needed to be fixed,

1) Client should have been pointed(redirected) to Virtual address and not real host names
2) Client should be able to resolve virtual address

Point 1 can be fixed by setting up LOCAL_LISTENER parameter on all RAC nodes. Here is how,

Add entry in TNSnames.ora on each node e.g. on Node 1

LISTENER_NODE1 =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT =1521))
)

set local listner for each node,

Alter system set LOCAL_LISTENER= 'LISTENER_NODE1' scope=both sid='SID1';

Repeat above steps on each node with respective host & tns name.

Point 2 can be fixed by either add DNS entry for the host names or if not possible, add entry in Client's host file e.g. on Linux add entry in /etc/hosts or on Windows add entry in windows\system32\drivers\etc\hosts.

Tip#23 2>&1 and Argument list too long

2009-06-03T16:52:00.004+02:00

What does 2>&1 mean?

Sometimes I call my SQL script via shell script which generally have this kind of statement in it,

$SCRIPTDIR/run_me.sh 2>&1 > $LOGDIR/log_me.log

What above statement does for me is, it runs run_me.sh script from my script folder and logs the detail in files called log_me.log in my log folder. Over here what I am interested in is what is 2>&1. Well firstly let me define three data streams in linux i.e. STDIN, STDOUT, and STDERR,

STDIN : Standard input usually comes from the keyboard or from another program

STDOUT : The program usually prints to standard output

STDERR : The program sometimes prints to standard error

In Linux/Unix, The built-in numberings for them are 0, 1, and 2, in that order.

The command above is redirecting standard error into standard output (you have to put an & in front of the destination when you do this) and redirecting standard output into my log file, which is a place I want to dump anything my scripts writes out.

So effectively, all output from this command should be logged into my log file. So in case of any issue I can always look at the log to find out the trouble area.

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

Sometimes there will be cases where a directory is filled with lots of files e.g. dump directory with lots of trace file. In such case when I tried following,

/bin/rm *.trc

I got following error message:

bash: /bin/rm: Argument list too long

Solution:

find . -name "*.trc"| xargs /bin/rm.

Depending on the number of files, after a while all files will be erased.

Tip#22 Fix for ORA-01882

2009-06-03T12:44:00.001+02:00

ORA-01882: timezone region %s not found

Recently we got this error while running via TOAD

select * from dba_scheduler_jobs;

The error message itself turns out not very informative.

01882, 00000, "timezone region %s not found"
// *Cause: The specified region name was not found.
// *Action: Please contact Oracle Customer Support.

In short, the error is because there are 7 timezone region IDs changed from version 3 and above. If you have old Timezone data from Version 2 that using one of these IDs the error raises.

In our case, server had time zone files were already upgraded so running same query on the DB server was working fine. So to fix it we had fix it on the client side,

1) Download patch 5731535 for a 10.2.0.X client ( 10.2.0.1 to 10.2.0.3)

2) Copy the 2 .dat files located at Client_patch\5731535\files\oracore\zoneinfo and the readme.txt file into the %ORACLE_HOME%\oracore\zoneinfo directory on your oracle client.

3) restart the TOAD

Ref : Metalink Doc ID: 414590.1 and 417893.1

Tip#21 DB Link Name Suffix

2009-04-03T18:47:00.002+02:00

Recently came across installations where all DB link were having suffix US.ORACLE.COM or REGRESS.RDBMS.DEV.US.ORACLE.COM and was bit annoying for users so here is how we went forward to fix it.

As per documentation , whenever we create DB link, the db_domain is automatically appended to it. So following SQL should help determines naming of all DB links,

select name,value from v_$parameter where name IN ('db_name', 'db_domain');

db_name = MYDB
db_domain = NULL

So in our case db_domain was NULL then where does US.ORACLE.COM or REGRESS.RDBMS.DEV.US.ORACLE.COM come form, lets check the global name, in case you dont know, GLOBAL_NAME = db_name.db_domain

select global_name from global_name;

global_name = MYDB.US.ORACLE.COM

hmm so global name does show me domain name now, how do I fix it?

Option 1 :

Rename global name with desired domain name e.g. MYWORLD

alter database rename global_name to MYDB.MYWORLD;

Please note with above alter statement, you MUST specify db_domain else it wont work.

Option 2 :

In case you dont wont any domain, Update global name to db_name without db_domain

UPDATE GLOBAL_NAME SET GLOBAL_NAME = 'MYDB';
commit;

Now cross check the changes,

select global_name from global_name ;

global_name = MYDB

Now you can go and create DB link as any user and it will be as expected i.e. with desired suffix or without.

While we are on DB link topic, please note global_name <> global_names i.e.

Global Name is global name of the DB determined by db_name.db_domain

Global Names is a parameter which determines whether DB link should always be named AFTER the global name of the database it connects to. You can set parameter value to be TRUE or FALSE (default) . If you have a replication environment then it is in general a good thing to set it to true.

Tip#20 Session Marked for Kill Forever

2009-03-23T12:55:00.002+01:00

I had a situation recently where our junior DBA accidentally killed an MV refresh job session and session killed was 'marked for kill' forever i.e. it wont release the resource (in this case lock) hence any subsequent refresh were also failing.

Lock holding session can be determined by,

select SID from v$lock where ID1 = (select object_id from dba_objects where object_name = *Object name* and OWNER = *Owner*)

Also any subsequent try to kill/disconnect session using alter session will result in ORA-00031: session marked for kill. So only way to release resources is to kill the process at OS level and let the PMON do the cleanup.

Now since sessoin is already killed and we are not yet using 11g (which gives you OS PID after killing as well), I tried getting OS PID using following SQL (Also suggest to read Metalink Doc ID 1020720.102) i.e.

SQL> SELECT spid FROM v$process WHERE NOT EXISTS ( SELECT 1 FROM v$session WHERE paddr = addr) AND spid IS NOT NULL;

unfortunately this didnt help as PID returned was not JOB process, you can check on Linux using,

% ps -ef | grep spid

The process I was expecting to be something like ora_j00x_sid but didnt found any so search for OS PID was still on.

Since we still have an entry in v$session with 'KILLED' status, we decided to get a 'Logon time' and see if we can match that with OS PID for same time and with command format 'ora_j00x_sid'. And fortunately we had hit the bulls eye we had only one OS PID with same time and expected command so we could just easily nailed it. So we killed the process at OS level

% kill spid

and after few moments we could see the lock was released!

Tip#19 Oracle Date Insight

2009-03-11T13:02:00.003+01:00

Note : Partial text from Metalink Note:69028.1

Oracle DATE values are always stored in 7 bytes, excluding the length byte, within a datafile. These bytes store the century, year, month, day, hour, minute, and second details respectively. The following is the definition of Oracle's internal DATE storage structure:

BYTE Meaning
---- -------
1 Century -- stored in excess-100 notation
2 Year -- " "
3 Month -- stored in 0 base notation
4 Day -- " "
5 Hour -- stored in excess-1 notation
6 Minute -- " "
7 Second -- " "

Note that the century and year components are stored in 'excess 100 format', which means that 100 must be deducted from the byte's value. If a negative number results, then we've got a BC date at which point we take the absolute number. Also, to avoid ever having a zero byte, 1 is added to the hour, minute and second bytes. Therefore, 1 must be detected to get the correct value.

For example, take the following date:
11-MAR-2009 13:08:00

we would expect this date to be stored internally as follows:
120,109,3,11,14,9,1

Let's confirm this,

SQL> create table test1 as select sysdate sd from dual;

SQL> select to_char(sd, 'DD-MON-YYYY HH24:MI:SS'), dump(sd) from test1;

Result:

TO_CHAR(SD,'DD-MON-YYYYHH24:MI:SS') : 11-MAR-2009 13:08:00

DUMP(SD) : Typ=12 Len=7: 120,109,3,11,14,9,1

Let's try using the DUMP() function to do the same thing with TO_DATE now. Issue the following statement:

SQL> SELECT dump(to_date('11-MAR-2009 13:08:00', 'DD-MON-YYYY HH24:MI:SS'))
FROM dual;

Result: Typ=13 Len=8: 217,7,3,11,13,8,0,0

Note the different "Typ=" values to understand why we are seeing these results. The datatype returned is 13 and not 12, the external DATE datatype. This occurs because we rely on the TO_DATE function! External datatype 13 is an internal c-structure whose length varies depending on how the c-compiler represents the structure. Note that the "Len=" value is 8 and not 7. Type 13 is not a part of the published 3GL interfaces for Oracle and is used for date calculations mainly within PL/SQL operations.

Note that the same result can be seen when DUMPing the value SYSDATE.

Using deductive logic, we can derive the following storage format for type 13 data:

Byte 1 - Base 256 year modifier
2 - Base 256 year
3 - Month
4 - Day
5 - Hours
6 - Minutes
7 - Seconds
8 - Unused

For AD dates, the year and base 256 modifier are stored in base 0 notation and we must add the modifier to the year to obtain the true year. For BC dates, the year and base 256 modifier are stored in excess-255 notation. We must subtract the modifier from the year to obtain the true year.

For our year 2009, we could read this to be, Byte 1 + Byte 2 * 256. In other words, 217 + 7 * 256 = 2009.

Oracle is capable of handling dates from 01-JAN-4712 BC 00:00:00 TO 31-DEC-9999 AD 23:59:59 AD OR in terms of Julian Day: 1 through Julian Day: 5373484

The Julian Day number is a count of days elapsed since Greenwich mean noon on 1 January 4712 B.C. in the Julian proleptic calendar. The Julian Date is the Julian Day number followed by the fraction of the day elapsed since the preceding noon.

Tip#18 Rebuilding A Table

2009-02-16T15:52:00.005+01:00

The DBA may have to rebuild a table after maintenance e.g. after a huge delete would like to lower the High Water Mark (HWM). There are several options to do it,

Option 1: Export and Import the table
Option 2: CTAS - Create table as Select
Option 3: Alter table MOVE (> 8.1.6)
Optoin 4: Alter table SHRINK (> 10g )

First two option are fairly straight forward so I wont go in the details here.

ALTER TABLE MOVE :

This one is my preferred option compare to the first two (if not already on 10g or above) and would like to point out few facts.

The ALTER TABLE MOVE command was implemented in 8.1.6. The command will allow tables to be moved from one tablespace to another, reorganise a table, provide the ability to modify of the INITIAL parameter. Also note that we don't lose grants (unlike CTAS), above all it is easy and fast.

Although it has a drawback, please note that when you use it, all the indices on the table become invalid, have to do an ALTER INDEX REBUILD.

e.g. I have a large table MBS with 50 million rows and now due to some process change I need to delete 25 million rows. After such huge delete obviously I would like to lower the HWM to avoid scanning those empty blocks below HWM.

Since I dont want to change the tablespace or initial parametere I would simply execute this,

Alter table MBS move; --tablespace test_tbs pctfree 20 pctused 40;

Since indices on the table will become invalid, I will also need to do this,

SELECT index_name FROM user_indexes WHERE table_name = 'MBS';

Once you know the indices used by the table just rebuild each of them as shown below,

Alter index pk_mbs rebuild; -- unrecoverable; --to avoid generating redo

ALTER TABLE SHRINK :

If you are on 10g or above, both of the above mentioned steps (table move and index rebuild) are done using single statement using SHRINK option.

alter table MBS enable row movement;
alter table MBS shrink space cascade;
alter table MBS disable row movement;

Just for comparison of Alter table options,

1.) deallocate unused : claims back unused space above HWM and doesnt lowers the HWM.
2.) move : claims back unused space below HWM and lowers the HWM
3.) shrink : claims back unused space below and above HWM and lowers the HWM

This blog was intended to just give you an overview, for more details on both the command please refer to the documentation. Also as usual always do backup before and after the changes.

Tip#17 How to find out 32/64 Bit

2008-07-30T15:22:00.005+02:00

Find out whether OS is 32 bit or 64 bit

Linux : getconf LONG_BIT

Solaris : /usr/bin/isainfo -kv

AIX : getconf -a | grep KERN

HPUX : /usr/bin/getconf KERNEL_BITS

Windows : Start>All Programs>accessories> System Tools>System Information> System summary

Find out whether Oracle Software is 32 bit or 64 bit.

Method # 1
cd $ORACLE_HOME/bin/
file oracle

Method # 2
sqlplus / as sysdba (Look for "Connected to:")

Method # 3
If the two directories $ORACLE_HOME/lib32 and $ORACLE_HOME/lib are existing then it is 64 bit else 32 bit

Tip#16 Fix Migrated/Chained rows

2008-07-07T15:24:00.005+02:00

What do they mean?

Row chaining is what happens when a row cannot fit into a single Oracle block ever, because it is just too big. Oracle therefore has to break it up into several smaller row pieces, store each piece in a separate block, and link (or “chain”) from one block to another so that the entire set of pieces can be retrieved when you query the table for the row.

Row migration is what happens when a row grows in size and can no longer fit into its original Oracle block: Oracle will move it (or ‘migrate’ it) into a completely new block into which it can fit.

How to Find them?

Assuming you have latest stats for the tables, run following SQL to find out if you have any tables with high percentage of chained or migrated rows in a table.

SELECT owner, table_name, num_rows, chain_cnt,
round((chain_cnt * 100 / num_rows),2) pct, pct_free, pct_used
FROM dba_tables WHERE chain_cnt > 0
AND owner NOT IN ('SYS', 'SYSTEM')
ORDER BY 5 desc ;

If you have any tables with large number of chained/migrated rows then follow the below steps to fix it,

How do I fix them?

1. connect to the database as the owner of the table having chained rows run utlchain.sql script which is in your ORACLE_HOME/rdbms/admin. It should create a table called 'chained_rows'.

2. Analyze the table suppose that the table having the issue is called: MBS

SQL>analyze table MBS list chained rows into chained_rows;

3. The above statement will load the chained row information into the table created in step 1

SQL>select * from chained_rows;

4. To fix the rows, put them in a temp table and remove it from original table and copy them from temp table to original table

create table my_tmp AS select * from mbs where rowid IN (select head_rowid from chained_rows where table_name = 'MBS');

DELETE FROM MBS WHERE rowid IN (select head_rowid from chained_rows where table_name = 'MBS');

INSERT INTO MBS SELECT * FROM my_tmp;

COMMIT;

Any Suggestion to avoid it?

Increase PCTFREE to avoid the future problem for those tables.

Tip#15 DBFile Sequential and Scattered Reads

2008-07-03T14:16:00.002+02:00

Both "db file sequential read" and "db file scattered read" events signify time waited for I/O read requests to complete. Most people confuse these events with each other as they think of how data is read from disk. Instead they should think of how data is read into the SGA buffer cache.

db file sequential read:

A sequential read operation reads data into contiguous memory (usually a single-block read with p3=1, but can be multiple blocks). Single block I/Os are usually the result of using indexes. This event is also used for rebuilding the controlfile and reading datafile headers (P2=1). In general, this event is indicative of disk contention on index reads.

db file scattered read:

Similar to db file sequential reads, except that the session is reading multiple data blocks and scatters them into different discontinuous buffers in the SGA. This statistic is NORMALLY indicating disk contention on full table scans. Rarely, data from full table scans could be fitted into a contiguous buffer area, these waits would then show up as sequential reads instead of scattered reads.

The following query shows average wait time for sequential versus scattered reads:

select a.average_wait "SEQ READ", b.average_wait "SCAT READ"
from sys.v_$system_event a, sys.v_$system_event b
where a.event = 'db file sequential read'
and b.event = 'db file scattered read';

Tip#14 Resumable Space Allocation

2008-06-30T16:56:00.002+02:00

In the event of space allocation failures, rather than returning the error to the user and stopping the operation, the transaction can be temporarily suspended and corrective action taken. After the error condition is corrected, the suspended operation automatically resumes. This feature is called resumable space allocation. The affected statements are called resumable statements.

When the execution of a resumable statement is suspended, the system issues a Resumable Session Suspended alert. A suspended operation is automatically aborted if the error condition is not fixed within the time-out period. By default, the time-out period is two hours. you can enable resumable space allocation either at the system level, by setting the RESUMABLE_TIMEOUT initialization parameter to a nonzero value, or at the session level, by issuing the ALTER SESSION ENABLE RESUMABLE statement. A resumable statement can be suspended and resumed multiple times during execution.

Which statements are resumable?

• DML statements, including INSERT INTO ... SELECT from external tables, are resumable.
• DDL statements, including CREATE TABLE ... AS SELECT, are resumable.
• SELECT statements that run out of temporary space are also candidates for resumable execution.

A resumable statement can be suspended under any of these conditions:

• Space quota exceeded (e.g. The user has exceeded his or her assigned space quota in the tablespace),
• Maximum extents reached (e.g. The number of extents in a table or index equals the number of maximum extents defined on the object.)
• Out of space (e.g. The operation cannot acquire any more extents for an index in a tablespace.)

Tip#13 Big Vs Little Endian

2008-05-28T16:49:00.009+02:00

Little Endian means that the low-order byte of the number is stored in memory at the lowest address, and the high-order byte at the highest address. (The little end comes first.) For example, a 4 byte LongInt

Byte3 Byte2 Byte1 Byte0

will be arranged in memory as follows:
Base Address+0 Byte0
Base Address+1 Byte1
Base Address+2 Byte2
Base Address+3 Byte3

Linux, Windows use "Little Endian" byte order.

Big Endian means that the high-order byte of the number is stored in memory at the lowest address, and the low-order byte at the highest address. (The big end comes first.) Our LongInt, would then be stored as:

Base Address+0 Byte3
Base Address+1 Byte2
Base Address+2 Byte1
Base Address+3 Byte0

Solaris, HPUX, Apple Mac use "Big Endian" byte order.

In Oracle 10g, following SQL should tell you which operating systems follow which byte order,

select * from v$transportable_platform order by platform_id;

To transport tablespaces between OS with same byte orders (endianness), we don’t need conversion in other cases we do which can be done using RMAN. E.g. I need to transport a Tablespace from Linux (Little Endian) to Solaris (Big Endian)

RMAN> convert tablespace XXX to
platform ‘Solaris[tm] OE (64-bit)' 
db_file_name_convert '/app/oracle/oradata/mbs’,
'/app/oracle/rman_bkups';

Now this file can be copied over to the target Solaris system, and the rest of the steps are easy.

Tip#12 STATUS column of dba_undo_extents

2008-04-28T13:31:00.004+02:00

I am sure you might have seen loads of information regarding how to resize/drop/recreate UNDO tablspace but point to note during dropping (ex-default/previous) UNDO tablespace is to check a view dba_undo_extents to see if any undo statments is still used by any session or needed for flashback. Following SQL should tell you if you can go ahead and drop the ex-default/previous UNDO tablespace or not (assuming undo tablespace I want to drop is UNDOTBS2),

SELECT tablespace_name, status, COUNT (*)
FROM SYS.dba_undo_extents
WHERE tablespace_name = 'UNDOTBS2'
GROUP BY tablespace_name, status

What is of our interest is status column of dba_undo_extents, 3 possible values - ACTIVE, EXPIRED, UNEXPIRED. What are the meanings ?

ACTIVE means that this undo segment contains active transactions

EXPIRED means that this segment is not rqeuired at all after considering Undo retention period

UNEXPIRED means that this segment does not contain any active transactions but it contains transactions which are still required for Flashback option (after considering Undo retention period).

So if you have all extents EXPIRED then go ahead and drop the tablespace, as usual make sure that you have done proper backup before and after.

Tip#11 SQL*Plus - Ignore Blank Lines in the Script

2008-04-09T17:50:00.005+02:00

Recently I came across a situation where during our database deploy one of our View script was exiting in SQLplus due to a blank line inside the script. To fix it, we had to set sqlblanllines setting in sqlplus to ignore the blank lines and that did the trick for us.

SQL> create or replace view xx
2 as
3 select
4 sysdate as x
5
SQL> from
SP2-0042: unknown command "from" - rest of line ignored.
SQL> dual
SP2-0042: unknown command "dual" - rest of line ignored.
SQL> set sqlblanklines on
SQL> create or replace view xx
2 as
3 select
4 sysdate as x
5
6 from
7 dual
8 ;

View created.

SQL>

Tip#10 Dump to text file using UTL_FILE

2008-04-04T16:49:00.001+02:00

Dump the data from table/sql to text file (CSV or PIPE delimated etc) on the server using UTL_FILE

Note :
A) Specify the accessible directories for the UTL_FILE functions in the initialization file using the UTL_FILE_DIR parameter. e.g. : UTL_FILE_DIR = /app/oracle/my_dir
FYI, UTL_FILE_DIR = * means turn off directory access checking, and it makes any directory accessible to the UTL_FILE (NOT RECOMMANDED).

EDIT: if you are using > 9i , you can use DIRECTORY instead of UTL_FILE_DIR.

B) Since we have data with special character, we open file with FOPEN_NCHAR to make sure we don’t loose special characters in the text dump.

C) I have just tested it on Redhat Linux - Oracle 9.2.0.6

This is the Function which I use (adapted to my requirements but thanks to Tom Kyte to get me started)

CREATE OR REPLACE FUNCTION dump_to_file (
p_query IN VARCHAR2,
p_separator IN VARCHAR2 DEFAULT '|',
p_dir IN VARCHAR2,
p_filename IN VARCHAR2
)
RETURN NUMBER
IS
l_filehandle UTL_FILE.file_type;
l_thecursor INTEGER DEFAULT DBMS_SQL.open_cursor;
l_columnvalue VARCHAR2 (32767);
l_col_hdr VARCHAR2 (32767);
l_status INTEGER;
l_colcnt NUMBER DEFAULT 0;
l_separator VARCHAR2 (10) DEFAULT '';
l_cnt NUMBER DEFAULT 0;
col_cnt PLS_INTEGER;
rec_tab DBMS_SQL.desc_tab;
col_num NUMBER;
BEGIN
-- open a writable file in the UTL_FILE_DIR directory
l_filehandle := UTL_FILE.fopen_nchar (p_dir, p_filename, 'w', 32767);

-- parse the sql we got
DBMS_SQL.parse (l_thecursor, p_query, DBMS_SQL.native);

-- execute the sql cursor
l_status := DBMS_SQL.EXECUTE (l_thecursor);

-- get the column information l_colcnt OUT param to get us number of columns
-- and rec_tab is plsql table with all column related information like name, length,schema, precision, scale etc
DBMS_SQL.describe_columns (l_thecursor, l_colcnt, rec_tab);

-- we need to print the column names as the first line
l_separator := '';
l_col_hdr := '';

FOR i IN 1 .. l_colcnt
LOOP
-- we define which columns we need in the fetch
DBMS_SQL.define_column (l_thecursor, i, l_columnvalue, 4000);
-- print the column name in the file
l_col_hdr := l_col_hdr || l_separator || rec_tab (i).col_name;
l_separator := p_separator;
END LOOP;

UTL_FILE.put_nchar (l_filehandle, l_col_hdr);
UTL_FILE.new_line (l_filehandle);

-- lets print the data now
LOOP
EXIT WHEN (DBMS_SQL.fetch_rows (l_thecursor) <= 0); l_separator := ''; FOR i IN 1 .. l_colcnt LOOP DBMS_SQL.COLUMN_VALUE (l_thecursor, i, l_columnvalue); UTL_FILE.put_nchar (l_filehandle, l_separator || l_columnvalue); l_separator := p_separator; END LOOP; UTL_FILE.new_line (l_filehandle); l_cnt := l_cnt + 1; END LOOP; DBMS_SQL.close_cursor (l_thecursor); UTL_FILE.fclose (l_filehandle); RETURN l_cnt; EXCEPTION WHEN UTL_FILE.invalid_operation THEN UTL_FILE.fclose (l_filehandle); raise_application_error (-20061, 'Dump To File Error: Invalid Operation'); WHEN UTL_FILE.invalid_filehandle THEN UTL_FILE.fclose (l_filehandle); raise_application_error (-20062, 'Dump To File Error: Invalid File Handle'); WHEN UTL_FILE.write_error THEN UTL_FILE.fclose (l_filehandle); raise_application_error (-20063, 'Dump To File Error: Write Error'); WHEN OTHERS THEN UTL_FILE.fclose (l_filehandle); RAISE; END dump_to_file;

How to Use above function ?

To generate pipe (‘ | ’) delimated file called emp.txt in /app/oracle/my_dir for my query, I execute something like following

DECLARE
l_rows NUMBER := 0;
l_dir VARCHAR2 (100) := '/app/oracle/my_dir';
BEGIN
l_rows := dump_to_file ('select * FROM scott.emp ', '|', l_dir, 'emp.txt');
DBMS_OUTPUT.put_line ('Number of rows dumped to emp.txt : ' || l_rows);
END;

EDIT:
if you want to use DIRECTORY instead of UTL_FILE_DIR, just create one like following,

create or replace directory my_dir as '/app/oracle/my_dir';

then use above code with 'my_dir' instead of l_dir.

Tip#9 Some OS utilities (Unix)

2008-04-01T17:17:00.000+02:00

NOHUP :

nohup (no hangups) is a UNIX command that allows you to execute a command in the background while you are logged into UNIX. The background process will continue to run until completion, even if you log off. For long running processes, this is a nice way to execute them. In my case, it allows me to log into a client server remotely, execute a process interactively with nohup, and then disconnect. Another benefit of nohup is that by default, a log of all activity is recorded in the current directory in the file nohup.out . e.g.

nohup sqlplus user/pwd @sqlscript &

Note : & at the end is for running the process in the background

SKILL:

Sometime I have a situations where I find that a process is consuming a lot of CPU and memory, but I don't want to kill it and I just wished if I could just 'pause' the process and that's where SKILL really helps me. e.g Process ID 1234 is taking too much resources and I need to somehow get other 'important' things done I will execute following command,

skill -STOP 1234

Once I have finished other 'important' things done I can free the process with following command

skill -CONT 1234

The nice feature is you can pass not just PID but also User, Terminal ID OR Command which makes it more powerful then I initially thought. e.g. You can 'pause' all RMAN commands on DB server with

skill -STOP rman

SNICE:

The command snice is similar to skill but Instead of stopping a process it makes its priority a lower one. e.g. for the same heavy process 1234 I could decrease the priority by using something like this,

snice +4 -p 1234

Note: +4 means we are increasing the Nice Value and effectively the higher the number, the lower the priority.

This utility is quite useful in reducing priorities.

Tip#8 Cursor best practice

2007-09-26T13:37:00.002+02:00

Cursor best practice

Oracle provides three possible way to loop through the cursor rows and these are,

loop with explicit cursor i.e. OPEN, FETCH one row at a time and CLOSE

Using cursor FOR-LOOP

Bulk collect and then loop through the collection.

Which of the above methods is most efficient for a cursor?

Without a doubt, you should use BULK COLLECT whenever possible to query information from your tables. It will be significantly faster than either a cursor FOR loop or loop with explicit cursor. However, a BULK COLLECT of many rows can consume a large amount of memory. But you can balance memory utilization against performance improvements by using the LIMIT clause with BULK COLLECT. Here's an example:

DECLARE
CURSOR allrows_cur
IS
SELECT *
FROM order_header_scratch;

TYPE order_header_scratch_aat IS TABLE OF order_header_scratch%ROWTYPE
INDEX BY BINARY_INTEGER;

l_order_header_scratch order_header_scratch_aat;
l_row PLS_INTEGER;
BEGIN
OPEN allrows_cur;

LOOP
FETCH allrows_cur
BULK COLLECT INTO l_order_header_scratch LIMIT 100;

-- Remember: BULK COLLECT will NOT raise NO_DATA_FOUND if no rows
-- are queried. Instead, check the contents of the collection to
-- see if you have anything left to process.
EXIT WHEN l_order_header_scratch.COUNT = 0;

-- Process the data, if any.
l_row := l_order_header_scratch.FIRST;

WHILE (l_row IS NOT NULL)
LOOP
-- Some processing ....
process_order_header_scratch (l_order_header_scratch (l_row));

-- get the next row
l_row := l_order_header_scratch.NEXT (l_row);
END LOOP;
END LOOP;

-- Clean up when done: close the cursor and delete everything
-- in the collection.
CLOSE allrows_cur;

l_order_header_scratch.DELETE;
END;

EDIT : I just happen to read an interesting article regarding cursor for-loop and would certainly suggest to read it. LINK .

Also I forgot to mention why bulk collect is faster compare to other, the biggest advantage of BULK COLLECT is: BULKING! obviously :)

In the cursor Oracle must refresh the binds every loop with the new data. Also the Insert is executed each time! With BULK COLLECT Oracle can put all the values into a collection at once. No need to refresh something again and again hence no soft parses, reading of the new values of the binds…

Tip#7 SQL*Plus Command Line History

2007-09-17T14:25:00.000+02:00

Thanks to Howard Rogers( Link ) , I got a very useful utility to have command line history for SQL*Plus on Linux. This small utility makes life so much easier for me especially as most of the time I do work on Linux platform. All you have to do is

1. Download a rpm (Link)

2. As root user, Install it -> rpm -ivh rlwrap-0.18-1.i386.rpm

3. Create alias for SQL*Plus in the user profile (Bash -> .bash_profile , Ksh -> .profile )
alias sqlplus='rlwrap sqlplus'

This will make sure that every time you login and run sql*Plus, "rlwrap" captures all SQL*Plus commands which you can just recall with up/down arrow key. Other nice feature is search capability of this utility, you can press Ctrl+R and type in key word to search for in your historical SQLs.

I really find this utility very helpful, hope it helps you too!!!

Tip#6 Compile Invalid Objects

2007-09-11T13:07:00.000+02:00

The Oracle database will invalidate objects if a dependent object is changed. In development environment it is sometimes pain as code is deployed very often and objects like packages, procedures and functions keep changing and need to compile all dependent objects.

Earlier most of the time we use a script to recompile invalid objects like PL/SQL packages and package bodies which may need to run it more than once for dependencies. But then as the number of objects grows so as dependencies and hence the number of times the compile script has to run.

So to avoid executing multiple times the compile script, we created following stored procedure which compiles in hierarchical order to take care of dependencies. Since we had a inter schema dependencies as well and wanted each schema owner to compile there own objects we pass a parameter which is a schema owner who will be compiling their own invalid objects in a schema. As of now inter schema dependencies is not table driven but I will implement it soon.

Please note to have generic solution, it needs to be created in a schema with DBA rights e.g. in Dev I created in SYS and then grant execute access to the schema owners.

CREATE OR REPLACE PROCEDURE Compile_Invalid_Objects
(In_Owner IN VARCHAR2)
IS
CURSOR obj_Cur IS
SELECT 'ALTER '
||DECODE(a.Object_Type,'PACKAGE BODY','PACKAGE',
'TYPE BODY','TYPE',
'SYNONYM',DECODE(a.Owner,'PUBLIC','PUBLIC SYNONYM',
'SYNONYM'),
a.Object_Type)
||' '
||DECODE(a.Owner,'PUBLIC',NULL,
a.Owner
||'.')
||a.Object_Name
||DECODE(a.Object_Type,'JAVA CLASS',' RESOLVE',
' COMPILE')
||DECODE(a.Object_Type,'PACKAGE BODY',' BODY',
'TYPE BODY','BODY') Text
FROM sys.dba_Objects a,
(SELECT MAX(LEVEL) dLevel,
Object_Id
FROM sys.Public_Dependency
START WITH Object_Id IN (SELECT Object_Id
FROM sys.dba_Objects
WHERE Status = 'INVALID'
AND Owner = Upper(In_Owner))
CONNECT BY Object_Id = PRIOR Referenced_Object_Id
GROUP BY Object_Id) b
WHERE a.Object_Id = b.Object_Id (+)
AND a.Status = 'INVALID'
AND a.Owner = Upper(In_Owner)
ORDER BY b.dLevel DESC,
a.Object_Name ASC;
BEGIN
FOR obj_rec IN obj_Cur LOOP
BEGIN
EXECUTE IMMEDIATE obj_rec.Text;
EXCEPTION
WHEN OTHERS THEN
util.Error_Handler('compile_invalid_objects'); -- Log any error
END;
END LOOP;

EXCEPTION
WHEN OTHERS THEN
util.Error_Handler('compile_invalid_objects');
END Compile_Invalid_Objects
/*-----------------------------------------------------------------------------

NAME: compile_invalid_objects
DESC: Compile all invalid objects for the given USER
usage : exec compile_invalid_objects ('SCOTT');
-----------------------------------------------------------------------------*/
;
/

Grant access to SCOTT user,

grant execute on sys.compile_invalid_objects to SCOTT;

I also create a public synonym for the SP,

CREATE PUBLIC SYNONYM COMPILE_INVALID_OBJECTS FOR SYS.COMPILE_INVALID_OBJECTS;

Tip#5 DDL Auditing

2007-08-24T14:40:00.000+02:00

As a DBA , I sometimes get complains that this object is missing or changed on our development or UAT system. So in order to find out WHO is the culprit I need to setup the DDL auditing. This is how I generally do this,

STEP 1 :
Please note that you need to give the user (which you are going to use) the following grant,

GRANT ADMINISTER DATABASE TRIGGER TO username;

STEP 2 :
Create Table to log the changes, something like following

CREATE TABLE DDL_AUDIT
(
AUD_ID NUMBER,
USERNAME VARCHAR2(50 BYTE),
HOSTNAME VARCHAR2(50 BYTE),
DDL_DATE DATE,
OBJ_TYPE VARCHAR2(50 BYTE),
OBJ_OWNER VARCHAR2(30 BYTE),
OBJ_NAME VARCHAR2(30 BYTE),
DDL_EVENT VARCHAR2(50 BYTE)
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
NOMONITORING
/

STEP 3 :
Create sequence for the PK of the table,

CREATE SEQUENCE DDL_AUDIT_SEQ
START WITH 1
MAXVALUE 99999999999
MINVALUE 0
NOCYCLE
NOCACHE
NOORDER
/

STEP 4 :
Create a DDL auditing database trigger,

CREATE OR REPLACE TRIGGER ddl_audit_trigger
AFTER DDL ON DATABASE
BEGIN
INSERT INTO ddl_audit
(aud_id, username,
hostname, ddl_date, obj_type, obj_owner,
obj_name, ddl_event
)
VALUES (ddl_audit_seq.NEXTVAL, SYS_CONTEXT ('USERENV', 'OS_USER'),
REPLACE (SYS_CONTEXT ('USERENV', 'HOST'), 'your_domain\', ''), SYSDATE,
ora_dict_obj_type, ora_dict_obj_owner, ora_dict_obj_name, ora_sysevent
);
EXCEPTION
WHEN OTHERS
THEN
-- log in the error table or ignore it
NULL; -- ignore it
END;
/

Tip#4 Dump, Lock & Nth Max/Min

2007-08-15T16:05:00.000+02:00

DUMP Function :
Sometimes if you need to handle special characters or like in my case various European characters then it is quite useful to know the Hexadecimal or decimal value of the character. You can use the DUMP sql function for the same as shown below,

dump( expression, [return_format], [start_position], [length] )

=> expression is the expression to analyze.

=> return_format is optional. It determines the format of the return value. This parameter can be any of the following values:

Value Explanation
----- -------------
8....... octal notation
10..... decimal notation
16 ..... hexadecimal notation
17 ..... single characters
1008... octal notation with the character set name
1010...decimal notation with the character set name
1016... hexadecimal notation with the character set name
1017... single characters with the character set name

=> start_position and length are optional parameters. They determines which portion of the internal representation to display. If these parameters are omitted, the dump function will display the entire internal representation in decimal notation.

e.g.
select dump('öäüß',1016) hex_value from dual;

Hex_Value
-------------
Typ=96 Len=8 CharacterSet=UTF8: c3,b6,c3,a4,c3,bc,c3,9f

Lock any table explicitly :

LOCK table MV_REPORT_E2E_LOGIC IN EXCLUSIVE MODE NOWAIT;

commit or rollback to release the lock.

Find Nth Maximum value of a column in a table :

SELECT *
FROM MY_TAB t1
WHERE &N = (SELECT count(DISTINCT(t2.col1))
FROM MY_TAB t2 WHERE t1.col1<=t2.col1)

e.g. N=1 will return first max or N=2 will return second max.

Find Nth Minimum value of a column in a table :

SELECT *
FROM MY_TAB t1
WHERE &N = (SELECT count(DISTINCT(t2.col1))
FROM MY_TAB t2 WHERE t1.col1 >=t2.col1)

e.g. N=1 will return first min or N=2 will return second min.