Subscriber Count


Subscribe to Posts by Email


Recover datafile that accidentally deleted

Thanks to Martin’s blog, which show us the data file recovery when it accidentally dropped. (apologies i lost the original link)

***** DO IT ON YOUR OWN RISK *****

Created tablespace and users and some tables.

SQL> create tablespace test datafile '/oradata/TESTTest01.dbf' size 100m; Tablespace created. SQL> create user test identified by test default […]

RAC: Timezone differences in CRS and Database logs

Hello All,

We have got an issue about time zone differences in alert logs , crs logs with system timestamp and with system timestamp.

System date time [oracle@TESTRAC1]/ # date Tue Apr 24 04:10:11 CEST 2013

But database Alert log shows,

Thread 1 advanced to log sequence 235 (LGWR switch) Current log# 11 […]

Time to read few good posts from other blogs

Dear Readers,

We have few good posts in Oracle community this week and here are they, and very interesting though

Jonathan Lewis on Systimestamp puzzles:-


Oracle optimizer blog comes up with method_opt parameter significance in dbms_stats

How does the METHOD_OPT parameter work-

Randolf Geist on


Arup Nanda […]

Identifying clustername in Oracle CRS

To identify the clustername in Oracle CRS use

$CRS_HOME/bin/cemutlo –n


Use OCRDUMP which will create a text file called OCRDUMPFILE open that file and look for this entry

#ocrdump -stdout -keyname SYSTEM | grep -A 1 clustername | grep ORATEXT | awk ‘{print $3}’


In the script $ORA_CRS_HOME/install/rootconfig:

ORA_CRS_HOME=/oracle/product/CRS CRS_ORACLE_OWNER = Oracle […]

High cpu usage with library cache locks

We have got a call from team asking about a high cpu usage, and here is our hunting down

load averages: 97.5, 98.8, 100.0; up 11+22:24:01 01:14:52 410 processes: 276 sleeping, 118 running, 3 zombie, 13 on cpu CPU states: 0.0% idle, 89.6% user, 10.4% kernel, 0.0% iowait, 0.0% swap Memory: 32G phys mem, 17G […]

Using log miner effectively

I have got a request from the team asking to get the DML statements that ran against a table for a particular period of time

For this, I have three options 1) If auditing is enabled, get the statements from the audit trail, but the problem will be no bind variable replacement and also the […]

First & Test post on new site

Hello All,

This is the first post and test post for this new site, if you are encountering any issues with site please drop me an email with issue, will fix it.

For those who enrolled post to email subscription, they should receive this post via email, if not can you please spare sometime […]

SQL Tuning: Handling nulls in indexes and get them used


Thanks to my colleague, I have just buyed the following from my Friend Naga Satish from his email and sharing here.

"Edited Explain plan" —————————————————————————————— | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | —————————————————————————————— | 0 | SELECT STATEMENT | | 3626K| 390M| | 1217K (1)| 04:03:28 […]

SQL Plan Management:- How many days the sql plan baselines or management data can be retained?

If you want to understand more about SQL Plan Management, read this post before reading further,

Debate- SPA vs. SPM vs. ACS, SPM vs. sql_profiles etc

As you see in the figure, Each and every sql baselines has multiple plans associated with it and stored in sql plan management base. Some of the statements […]