Friday, February 8, 2013

Drop database Using RMAN command.

Run below command in RMAN prompt.  You must be connected to a target database and database is mount exclusive and not open. You need to start RESTRICT mode.

RMAN> connect target /

connected to target database: TEST (DBID=233344476, not open)

RMAN> SQL 'ALTER SYSTEM ENABLE RESTRICTED SESSION';

using target database control file instead of recovery catalog
sql statement: ALTER SYSTEM ENABLE RESTRICTED SESSION

RMAN> DROP DATABASE INCLUDING BACKUPS;

database name is "TEST" and DBID is 233344476

Do you really want to drop all backups and the database (enter YES or NO)? YES

allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=421 device type=DISK
specification does not match any backup in the repository

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=421 device type=DISK
specification does not match any datafile copy in the repository
specification does not match any control file copy in the repository
specification does not match any control file copy in the repository
specification does not match any archived log in the repository

database name is "TEST" and DBID is 233344476
database dropped


Wednesday, February 6, 2013

Consuming most undo your database


Below Query find out SQL has run for the longest time in your database.

select  s.sql_text
 from  gv$sql s, gv$undostat u 
where u.maxqueryid=s.sql_id


Base on gv$transaction and gv$session find out most undo used in your database.

 
select   s.sid, s.username, t.used_urec, t.used_ublk 
 from   gv$session s, gv$transaction t
where  s.saddr = t.ses_addr  order by t.used_ublk desc;

Below query  which session is currently  using the most undo  in your database.

 

select  s.sid, t.name, s.value
 from  gv$sesstat s, gv$statname t 
where  s.statistic# = t.statistic#
    and  t.name = 'undo change vector size' order by s.value desc;

 
When you run above query base on output your find out which SID for the session  consuming the most undo.

 
If you want find out active transaction, you can user gv$transaction view.

 
select  sql.sql_text sql_text, t.USED_UREC Records,
           t.USED_UBLK Blocks, (t.USED_UBLK*8192/1024) KBytes
  from  gv$transaction t,  gv$session s,  gv$sql sql
where  t.addr = s.taddr 
    and s.sql_id = sql.sql_id 
    and s.username ='&USERNAME'


Base on above query output, number of undo records used(USED_REC) and undo blocks consumed  by a transaction (USED_UBLK).
 

Above query is based on RAC  Database. If you database is not in RAC, please user V$ views.