Oracle DB Admin Important Queries

DB Lock status

select OS_USER_NAME os_user,
 PROCESS os_pid,
 ORACLE_USERNAME oracle_user,
 l.SID oracle_id,
 decode(TYPE,
  'MR', 'Media Recovery',
  'RT', 'Redo Thread',
  'UN', 'User Name',
  'TX', 'Transaction',
  'TM', 'DML',
  'UL', 'PL/SQL User Lock',
  'DX', 'Distributed Xaction',
  'CF', 'Control File',
  'IS', 'Instance State',
  'FS', 'File Set',
  'IR', 'Instance Recovery',
  'ST', 'Disk Space Transaction',
  'TS', 'Temp Segment',
  'IV', 'Library Cache Invalidation',
  'LS', 'Log Start or Switch',
  'RW', 'Row Wait',
  'SQ', 'Sequence Number',
  'TE', 'Extend Table',
  'TT', 'Temp Table', type) lock_type,
 decode(LMODE,
  0, 'None',
  1, 'Null',
  2, 'Row-S (SS)',
  3, 'Row-X (SX)',
  4, 'Share',
  5, 'S/Row-X (SSX)',
  6, 'Exclusive', lmode) lock_held,
 decode(REQUEST,
  0, 'None',
  1, 'Null',
  2, 'Row-S (SS)',
  3, 'Row-X (SX)',
  4, 'Share',
  5, 'S/Row-X (SSX)',
  6, 'Exclusive', request) lock_requested,
 decode(BLOCK,
  0, 'Not Blocking',
  1, 'Blocking',
  2, 'Global', block) status,
 OWNER,
 OBJECT_NAME
from v$locked_object lo,
 dba_objects do,
 v$lock l
where  lo.OBJECT_ID = do.OBJECT_ID
AND     l.SID = lo.SESSION_ID

Rows per block 

SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300
COLUMN "Rows per Block" FORMAT 9999
 
SELECT
   count(*) as "Rows per Block"
FROM
   &&tabname
WHERE
   dbms_rowid.rowid_block_number(rowid) =
     (
     SELECT
        min(dbms_rowid.rowid_block_number(rowid))
     FROM &&tabname
     )

Execution plan

create table PLAN_TABLE (
        statement_id       varchar2(30),
        plan_id            number,
        timestamp          date,
        remarks            varchar2(4000),
        operation          varchar2(30),
        options            varchar2(255),
        object_node        varchar2(128),
        object_owner       varchar2(30),
        object_name        varchar2(30),
        object_alias       varchar2(65),
        object_instance    numeric,
        object_type        varchar2(30),
        optimizer          varchar2(255),
        search_columns     number,
        id                 numeric,
        parent_id          numeric,
        depth              numeric,
        position           numeric,
        cost               numeric,
        cardinality        numeric,
        bytes              numeric,
        other_tag          varchar2(255),
        partition_start    varchar2(255),
        partition_stop     varchar2(255),
        partition_id       numeric,
        other              long,
        distribution       varchar2(30),
        cpu_cost           numeric,
        io_cost            numeric,
        temp_space         numeric,
        access_predicates  varchar2(4000),
        filter_predicates  varchar2(4000),
        projection         varchar2(4000),
        time               numeric,
        qblock_name        varchar2(30)
);
commit




EXPLAIN PLAN
SET STATEMENT_ID= 'MY_FIRST_EXP'
FOR
<SQL QUERY for which execution plan needs to be generated>

SELECT * 
FROM   TABLE(DBMS_XPLAN.DISPLAY);

Active Sessions 

SET LINESIZE 500
SET PAGESIZE 1000

COLUMN username FORMAT A15
COLUMN machine FORMAT A25
COLUMN logon_time FORMAT A20

SELECT NVL(s.username, '(oracle)') AS username,
       s.osuser,
       s.sid,
       s.serial#,
       p.spid,
       s.lockwait,
       s.status,
       s.module,
       s.machine,
       s.program,
       TO_CHAR(s.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time,
       s.last_call_et AS last_call_et_secs
FROM   v$session s,
       v$process p
WHERE  s.paddr  = p.addr
AND    s.status = 'ACTIVE'
ORDER BY s.username, s.osuser;

SET PAGESIZE 14

Index Usage 

SET VERIFY OFF
SET LINESIZE 200

SELECT table_name,
       index_name,
       used,
       start_monitoring,
       end_monitoring
FROM   v$object_usage
WHERE  table_name = UPPER('&1')
AND    index_name = DECODE(UPPER('&2'), 'ALL', index_name, UPPER('&2'));

Tablespaces 

SET LINESIZE 200

SELECT tablespace_name,
       block_size,
       extent_management,
       allocation_type,
       segment_space_management,
       status
FROM   dba_tablespaces
ORDER BY tablespace_name;

Top Sessions 

SET LINESIZE 500
SET PAGESIZE 1000
SET VERIFY OFF

COLUMN username FORMAT A15
COLUMN machine FORMAT A25
COLUMN logon_time FORMAT A20

SELECT NVL(a.username, '(oracle)') AS username,
       a.osuser,
       a.sid,
       a.serial#,
       c.value AS &1,
       a.lockwait,
       a.status,
       a.module,
       a.machine,
       a.program,
       TO_CHAR(a.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time
FROM   v$session a,
       v$sesstat c,
       v$statname d
WHERE  a.sid        = c.sid
AND    c.statistic# = d.statistic#
AND    d.name       = DECODE(UPPER('&1'), 'READS', 'session logical reads',
                                          'EXECS', 'execute count',
                                          'CPU',   'CPU used by this session',
                                                   'CPU used by this session')
ORDER BY c.value DESC;

SET PAGESIZE 14

Top Sqls 

SET LINESIZE 500
SET PAGESIZE 1000
SET VERIFY OFF

SELECT *
FROM   (SELECT Substr(a.sql_text,1,50) sql_text,
               Trunc(a.disk_reads/Decode(a.executions,0,1,a.executions)) reads_per_execution, 
               a.buffer_gets, 
               a.disk_reads, 
               a.executions, 
               a.sorts,
               a.address
        FROM   v$sqlarea a
        ORDER BY 2 DESC)
WHERE  rownum <= &&1;

SET PAGESIZE 14


Author :
Labels: ,