oracle

DBAs often need to know if they should install Oracle 32-bit or 64-bit software on a given server. If the operating system support 64-bit applications, the 64-bit Oracle distribution can be loaded. Otherwise, the 32-bit distribution must be installed. The following Unix commands will tell you whether your OS is running 32-bits or 64-bits:

Solaris - isainfo -v (this command doesn't exist on Solaris 2.6 because it is only 32-bits)

HP-UX - getconf KERNEL_BITS

AIX - bootinfo -K

The most efficient way to remove duplicate rows from a table is by using what we database freaks call 'CTAS'. It is an exotic acronym for the SQL statement 'CREATE TABLE AS'. Imagine table T1 contains duplicate rows and we want to create table T2 as a non-duplicate version of T1. The syntax is as follows:

CREATE TABLE T1 AS SELECT DISTINCT * FROM T2;

In Oracle this will generate lots of temp information so be sure you provide an ample temp tablespace!

You can use Oracle's explain plan to tune your queries by viewing how Oracle is using indexes or dividing the query. You must first build your plan table (in your schema) by issuing the following command:

SQL> @$ORACLE_HOME/rdbms/admin/utlxplan.sql
Table created.

Now you can 'ask' Oracle how it is processing your SQL statements:

SQL> select * from dual;

D
-
X

SQL> explain plan for select * from dual;

Explained.

SQL> delete plan_table;
SQL> explain plan for select /*+ rule */ * from test_for_ep where a = 5;

Explained.

As most of you already know, deleting rows from a table doesn't deallocate space. The only way to shrink the datafiles is by using the ALTER DATAFILE...RESIZE statement, but we must know by how much we can shrink it! Tom Kyte provided this nifty script which will generate the ALTER DATABASE commands to shrink datafiles to their smallest possible size:

column value new_val blksize
select value 
  from v$parameter 
 where name = 'db_block_size'
/

select 'alter database datafile ''' || 
 file_name || ''' resize ' ||
 ceil( (nvl(hwm,1)*&&blksize)/1024/1024 )
 || 'm;' cmd

Whenever a valid SQL statement is processed Oracle has to decide how to retrieve the necessary data. This decision can be made using one of two methods:

* Rule Based Optimizer (RBO) - This method is used if the server has no internal statistics relating to the objects referenced by the statement. This method is no longer favoured by Oracle and will be desupported in future releases.

If you have a very time consuming DDL, such as creation of an index on a very big table, I recommend to enable PARALLEL DDL. This enhances the performance by a big factor. Just put this command before the DDLs:

ALTER SESSION ENABLE PARALLEL DDL;

As a test I have created an index preceded by the above statement. Notice how the DDL is parallelized:

 1   ?     3520 ora8i    156 24   324M 52928K sleep    7:18 23.22 23.18 ora_p000_my_db
 1   ?     3522 ora8i    156 24   324M 52928K sleep    5:53 22.20 22.16 ora_p001_my_db

Use the following UNIX/RMAN script to do a level 0 backup of an Oracle database:

export ORACLE_SID=my_db
rman catalog rman/rman@catalog_db log='backup.log' <<EOF
connect target
run {
allocate channel 'd0' type disk;
backup incremental level 0
filesperset 1
format '/backup/my_db/df_%s_%t_%p.dbf'
database
include current controlfile ;
sql 'alter system archive log current'
;
backup
filesperset 1
format '/backup/my_db/al_%s_%t_%p.dbf'
archivelog all delete input ;
release channel d0;
}
exit
EOF

Sometimes you need to see which Oracle sessions are occupying particular rollback segments. You can use this script:

col name for a10
col username for a20
col status for a10
col sql_text for a40
set linesize 132
select a.name, b.xacts, c.sid, c.serial#, c.username, d.sql_text, e.status
from v$rollname a, v$rollstat b, v$session c, v$sqltext d, v$transaction e
where a.usn = b.usn
and b.usn=e.xidusn
and c.taddr = e.addr
and c.sql_address = d.address
and c.sql_hash_value = d.hash_value
order by a.name, c.sid, d.piece
/

In the past weeks I was working on databases with a slow 'shutdown immediate' process while one in particular just hung in the process. After some research I concluded that for a database that takes more than 30 minutes to 'shutdown immediate', you have to check the CPU and divide the case in two scenarios.

Scenario 1 - No CPU activity:

Last week I bumped across this error on an Oracle 8i instance following a database restart:

Symptoms:
There are lots of messages appearing in alert log of the following form:

SMON: about to recover undo segment %s
SMON: mark undo segment %s as available

When the recovery is going on after a abnormal shutdown.

Cause:
These errors do not indicate rollback segment corruption.

These messages indicate that there is a problem with the
"rollback_segments" parameter in the init.ora.

Fix:
Check that the rollback segment is included in the "rollback_segments"