Tuesday, April 15, 2008

Some more dictionary queries

1.Show database datafile Archive status

SQL> column name format a43
SQL> column arc heading 'Backup Status'
SQL> set lines 132 pages 59 feedback off
SQL> start title132 "Database File Backup Status"
SP2-0310: unable to open file "title132.sql"
SQL> ed
Wrote file afiedt.buf

  1  select
  2  name,
  3  a.status,
  4  decode(b.status,'Active','Backup','Normal') arc,
  5  enabled,
  6  bytes,
  7  change#,
  8  time Archive
  9  from
10  sys.v_$datafile a, sys.v_$backup b where
11* a.file#=b.file#
SQL> /

NAME                                        STATUS  Backup ENABLED         BYTES    CHANGE# ARCHIVE
------------------------------------------- ------- ------ ---------- ---------- ---------- --------
/oracle/oracle_home/orcl2_data/orcl2/system SYSTEM  Normal READ WRITE  513802240          0
01.dbf

/oracle/oracle_home/orcl2_data/orcl2/undotb ONLINE  Normal READ WRITE   52428800          0
s01.dbf

/oracle/oracle_home/orcl2_data/orcl2/sysaux ONLINE  Normal READ WRITE  377487360          0
01.dbf

/oracle/oracle_home/orcl2_data/orcl2/users0 ONLINE  Normal READ WRITE  524288000          0
1.dbf

/oracle/oracle_home/orcl2_data/orcl2/exampl ONLINE  Normal READ WRITE  157286400          0
e01.dbf

2.Unless no transaction is going this query will return null..

SQL> select substr(a.os_user_name,1,8) "OS User"
  2  , substr(a.oracle_username,1,8) "DB User"
  3  , substr(b.owner,1,8) "Schema"
  4  , substr(b.object_name,1,20) "Object Name"
  5  , substr(b.object_type,1,10) "Type"
  6  , substr(c.segment_name,1,5) "RBS"
  7  , substr(d.used_urec,1,12) "# of Records"
  8  from v$locked_object a
  9  , dba_objects b
10  , dba_rollback_segs c
11  , v$transaction d
12  , v$session e
13  where a.object_id = b.object_id
14  and a.xidusn = c.segment_id
15  and a.xidusn = d.xidusn
16  and a.xidslot = d.xidslot
17  and d.addr = e.taddr;
SQL> /

OS User  DB User  Schema   Object Name          Type       RBS   # of Records
-------- -------- -------- -------------------- ---------- ----- ------------
         SOE      SOE      PRODUCT_INFORMATION  TABLE      _SYSS 3
         SOE      SOE      ORDERS               TABLE      _SYSS 3
         SOE      SOE      ORDER_ITEMS          TABLE      _SYSS 3
         SOE      SOE      CUSTOMERS            TABLE      _SYSS 3

3.Provides count about the wait times of files

SQL> SELECT file#, name, count, time wait_time,
  2  time/count ratio
  3  FROM x$kcbfwait, v$datafile
  4  WHERE indx + 1 = file#
  5  AND time>0
  6  Order By count DESC;

                                                                  Wait
File# Data File Name                                            Count
------ -------------------------------------------------- ------------
              Time
      Time   Count
---------- -------
     2 /oracle/oracle_home/orcl2_data/orcl2/undotbs01.dbf          342
        15     .04

     3 /oracle/oracle_home/orcl2_data/orcl2/sysaux01.dbf           164
         2     .01

     4 /oracle/oracle_home/orcl2_data/orcl2/users01.dbf             96
       107    1.11

                                                          ------------

sum                                                                602

No comments:

Post a Comment