Display Trace File's Name and Location using ORADEBUG

There are few ways to determine the trace file name and its location.

ORADEBUG is one of them.  See below for example.

SQL> oradebug setmypid

Statement processed.

SQL> alter database backup controlfile to trace;
Database altered.

SQL> oradebug tracefile_name
C:\APP\MOHIDEEN\diag\rdbms\zdb11g\zdb11g\trace\zdb11g_ora_8856.trc

How to Drop Oracle Database.

RMAN is not only for backup and recovery . RMAN can be used to drop the database ( data files and log files ,...) .

Needless to say , this is VERY destructive operation . Please be mindful of this . 

For RMAN to drop the database  , the database needs to be mounted exclusively in restricted mode.

C:\Users\oracle>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Wed Mar 2 15:20:17 2011
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount EXCLUSIVE RESTRICT;
ORACLE instance started.

Total System Global Area 1.3696E+10 bytes
Fixed Size                  2188768 bytes
Variable Size            6878661152 bytes
Database Buffers         6777995264 bytes
Redo Buffers               37044224 bytes
Database mounted.

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

C:\Users\oracle>rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Wed Mar 2 15:20:50 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
connected to target database: DUP2 (DBID=2709345826, not open)

RMAN> drop database ;
database name is "DUP2" and DBID is 2709345826

Do you really want to drop the database (enter YES or NO)? yes
database dropped

RMAN>

Real Time Statistics of a SQL statement in Oracle

To get real time statistics ( well , close to ) of a SQL statement , we can use the view v$sql_monitor.
This view is available only in Oracle 11g .

Here is an example .


SQL> SELECT *

2 FROM
3 (SELECT sid ,
4 sql_id ,
5 status ,
6 cpu_time ,
7 buffer_gets ,
8 disk_reads ,
9 SQL_PLAN_HASH_VALUE
10 FROM v$sql_monitor
11 order by cpu_time desc
12 )
13 WHERE rownum <= 5 ;


SID    SQL_ID STATUS CPU_TIME BUFFER_GETS DISK_READS SQL_PLAN_HASH_VALUE
---------- ------------- ------------------- ---------- ----------- ---------- -------------------
139    5zruc4v6y32f9    DONE   184656250   2877026   77594 0
154    401xxgh4h55g3    DONE    94843750   3258120    7137 0
152    401xxgh4h55g3    DONE    92531250   3254389    7550 0
36     401xxgh4h55g3    DONE    92109375   3239036    8009 0
141    401xxgh4h55g3    DONE    21687500    988400    3190 0

5 rows selected.

You can see the documentation of this view at
http://download.oracle.com/docs/cd/B28359_01/server.111/b28320/dynviews_3048.htm