oracle tips & tricks

Oracle Tips & Tricks

Thursday, 27 December 2007
SQL & PL/SQL : When Your Query Takes Too Long
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.


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.

Labels: , , ,

posted by popo @ 16:55  
Tuesday, 18 December 2007
SQL & PL/QL : Deleting Table with Millions Rows
Here are the case:
You have to compare 2 tables, both having around million rows, and delete data based on single column.


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
* 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

WHERE b.column_a = a.column_a

Labels: , ,

posted by popo @ 15:56  
Monday, 17 December 2007
Lost SYSOPER Password
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
    5. sqlplus /nolog or (svrmgrl) command
    6. connect sys as sysdba, or connect internal
      (not need input the password).


posted by popo @ 23:14   0 comments
Oracle Data Security Tips
If you're building a client-server application, you can use these tips:
  1. Build a three tier application, so the client is not connect to the database directly.
  2. Allow only 1 IP Address (as application server) connect to the oracle database (you can use blocking IP tips).
  3. 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.
  4. Give your Oracle Listener a password, so when someone unauthorized send a stop message to the listener it didn't work out.

Labels: ,

posted by popo @ 16:12   0 comments
Wednesday, 12 December 2007
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.

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.

1 row created.

SQL> savepoint in_book_author;

Savepoint created.

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.

  1. 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.
  2. Transaction B will correctly block on the commit or rollback of transaction A.
  3. However, if transaction A does a rollback to savepoint, it will continue to have hold a lock on row 1 (but not row 2).
  4. In fact a third transaction can now update row 2 (as it's not locked by transaction A).
  5. 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.


posted by popo @ 17:33  
Repair UNDO Log Corruption
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:
  1. Identify the bad segment:

    status = ‘NEEDS RECOVERY’;


  2. Bounce the instance with the hidden parameter “_offline_rollback_segments”, specifying the bad segment name:
  3. 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.


posted by popo @ 15:54  

Previous Post
Template by
Free Blogger templates