Oracle 
uncommited transactions 
  ..

uncommited transactions

SET LINESIZE 200 PAGESIZE 300
COLUMN sid                    FORMAT 99999           HEADING 'SID'
COLUMN serial_id              FORMAT 99999999        HEADING 'Serial ID'
COLUMN session_status         FORMAT a9              HEADING 'Status' JUSTIFY right
COLUMN oracle_username        FORMAT a14             HEADING 'Oracle User' JUSTIFY right
COLUMN os_username            FORMAT a12             HEADING 'O/S User' JUSTIFY right
COLUMN os_pid                 FORMAT 9999999         HEADING 'O/S PID' JUSTIFY right
COLUMN session_program        FORMAT a18             HEADING 'Session Program' TRUNC
COLUMN session_machine        FORMAT a15             HEADING 'Machine' JUSTIFY right
COLUMN number_of_undo_records FORMAT 999,999,999,999 HEADING "# Undo Records"
COLUMN used_undo_size         FORMAT 999,999,999,999 HEADING "Used Undo Size"
SELECT
 s.sid sid
 , s.serial# serial_id
 , lpad(s.status,9) session_status
 , lpad(s.username,14) oracle_username
 , lpad(s.osuser,12) os_username
 , lpad(p.spid,7) os_pid
 , b.used_urec number_of_undo_records
 , b.used_ublk * d.value used_undo_size
 , s.program session_program
 , lpad(s.machine,15) session_machine
FROM
 v$process p
 , v$session s
 , v$transaction b
 , v$parameter d
WHERE
 b.ses_addr = s.saddr
 AND p.addr = s.paddr
 AND s.audsid <> userenv('SESSIONID')
 AND d.name = 'db_block_size';

uncommited transactions on rac 


set linesize 200 pagesize 300
column session_status         format a9              heading 'Status' JUSTIFY right
column oracle_username        format a14             heading 'Oracle User' JUSTIFY right
column os_username            format a12             heading 'O/S User' JUSTIFY right
column os_pid                 format 9999999         heading 'O/S PID' JUSTIFY right
column session_program        format a18             heading 'Session Program' TRUNC
column session_machine        format a15             heading 'Machine' JUSTIFY right
column number_of_undo_records format 999,999,999,999 heading "# Undo Records"
column used_undo_size         format 999,999,999,999 heading "Used Undo Size"
column kill                   format a15
SELECT distinct
 ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill
 , lpad(s.status,9) session_status
 , lpad(s.username,14) oracle_username
 , lpad(s.osuser,12) os_username
 , lpad(p.spid,7) os_pid
 , b.used_urec number_of_undo_records
 , b.used_ublk * d.value used_undo_size
 , s.program session_program
 , lpad(s.machine,15) session_machine
 , s.sql_id
 , s.PREV_SQL_ID
FROM
   gv$process p
 , gv$session s
 , gv$transaction b
 , gv$parameter d
where 1=1
 and (b.ses_addr = s.saddr and b.inst_id= s.inst_id)
 and (p.addr = s.paddr and p.inst_id= s.inst_id)
 and s.audsid <> userenv('SESSIONID')
 and d.name = 'db_block_size';

Oracle Table detail info1

 

 

 

 

 

 

 

Everything to do with a table

Table info in detail

set linesize 155
set pagesize 60
set echo on feed on arraysize 1 LONG 5000 verify off 
set linesize 140 echo off feed off

 
-- ========================
prompt
prompt
prompt Show the Table Structure
-- Show the Table Structure
  col pos for 999 head "POS"
  col data_type for A15 
  col pct_free format A4 heading "Null"
  select column_name, data_type, data_length, nullable, column_id pos
  from SYS.DBA_TAB_COLUMNS
  where owner = upper('&&owner')
  and table_name = upper('&&table')
  order by column_id;

-- ========================
prompt
prompt
prompt Show Physical Attributes
-- Show Physical Attributes
  col pct_free for 999 heading "%|Free"
  col pct_increase for 999 heading "%|Incr"
  col initial_extent for 999999999 heading "Init|Extent"
  col next_extent for 9999999999999 heading "Next|Extent"
  col max_extents for 9999999999 heading "Max|Ext"
  col avg_row_len for 99999 heading "Avg|Row|Len"
  SELECT PCT_FREE,
  PCT_INCREASE,
  INITIAL_EXTENT,
  NEXT_EXTENT,
  MAX_EXTENTS,
  NUM_ROWS,
  AVG_ROW_LEN
  FROM SYS.DBA_TABLES
  WHERE OWNER = upper('&&owner')
  AND TABLE_NAME = upper('&&table');


-- ==============================
prompt
prompt
prompt Show the actual Maximum Size of a Row
-- Show the actual Maximum Size of a Row
  col MaxRowSize for 999999999999
  select sum(DATA_LENGTH) MaxRowSize 
from sys.dba_tab_columns 
where owner = upper('&&owner') 
and table_name = upper('&&table');
  
-- ========================================================
prompt
prompt
prompt Show the Number of Physical EXTENTS that have been allocated Attributes
-- Show the Number of Physical EXTENTS that have been allocated Attributes

COL SEGMENT_NAME FORMAT A30 HEADING 'Table Name'
COL COUNTER FORMAT 9999999 HEADING 'Number Of Extents Used'
SELECT SEGMENT_NAME, COUNT(*) COUNTER 
FROM SYS.DBA_EXTENTS 
WHERE OWNER = upper('&&owner') 
AND SEGMENT_NAME = upper('&&table') 
GROUP BY SEGMENT_NAME;

COL TABSIZE FORMAT 999999999999 HEADING 'Table Size In Bytes'
--
-- =====================================
prompt
prompt
prompt Show the Physical SIZE IN BYTES of the TABLE
-- Show the Physical SIZE IN BYTES of the TABLE
SELECT SEGMENT_NAME, SUM(BYTES) TABSIZE 
FROM SYS.DBA_EXTENTS WHERE 
OWNER = upper('&&owner') 
AND SEGMENT_NAME = upper('&&table') 
GROUP BY SEGMENT_NAME;

-- =====================================================
-- GET ALL THE INDEX DETAILS
prompt
prompt
prompt Show all the indexes and their columns for this table
-- Show all the indexes and their columns for this table
  COL OWNER FORMAT A8 heading "Index|Owner"
  COL TABLE_OWNER FORMAT A8 heading "Table|Owner"
  COL INDEX_NAME FORMAT A30 heading "Index Name"
  COL COLUMN_NAME FORMAT A30 heading "Column Name"
  COL COLUMN_POSITION FORMAT 9999 heading "Pos"
  BREAK ON CONSTRAINT_NAME SKIP PAGE
  SELECT IND.OWNER,
  IND.TABLE_OWNER,
  IND.INDEX_NAME,
  IND.UNIQUENESS,
  COL.COLUMN_NAME,
  COL.COLUMN_POSITION
  FROM SYS.DBA_INDEXES IND,
  SYS.DBA_IND_COLUMNS COL
  WHERE IND.TABLE_NAME = upper('&&table')
  AND IND.TABLE_OWNER = upper('&&owner')
  AND IND.TABLE_NAME = COL.TABLE_NAME
  AND IND.OWNER = COL.INDEX_OWNER
  AND IND.TABLE_OWNER = COL.TABLE_OWNER
  AND IND.INDEX_NAME = COL.INDEX_NAME;
 
-- =========================================================

Prompt
Prompt
Prompt Display all the physical details of the Primary and Other Indexes for table
-- Display all the physical details of the Primary and Other Indexes for table
  COL OWNER FOR A8 heading "Index|Owner"
  COL TABLE_OWNER FOR A8 heading "Table|Owner"
  COL INDEX_NAME FOR A30 heading "Index Name"
  COL COLUMN_NAME FOR A30 heading "Column Name"
  COL COLUMN_POSITION FOR 9999 heading "Pos"
  COL PCT_FREE FOR 999 heading "%|Free"
  COL PCT_INCREASE FORMAT 999 heading "%|Incr"
  COL INITIAL_EXTENT FORMAT 999999999 heading "Init|Extent"
  COL NEXT_EXTENT FORMAT 999999999 heading "Next|Extent"
  COL MAX_EXTENTS FORMAT 9999999999 heading "Max|Ext"
SELECT IND.OWNER,IND.TABLE_OWNER,IND.INDEX_NAME,IND.UNIQUENESS,COL.COLUMN_NAME,COL.COLUMN_POSITION,IND.PCT_FREE,
IND.PCT_INCREASE,IND.INITIAL_EXTENT,IND.NEXT_EXTENT,IND.MAX_EXTENTS
FROM DBA_INDEXES IND,DBA_IND_COLUMNS COL
WHERE IND.TABLE_NAME = upper('&&table') 
AND IND.TABLE_OWNER = upper('&&owner') 
AND IND.TABLE_NAME = COL.TABLE_NAME
AND IND.OWNER = COL.INDEX_OWNER 
AND IND.TABLE_OWNER = COL.TABLE_OWNER 
AND IND.INDEX_NAME = COL.INDEX_NAME;
--
-- ====================================================================
-- GET ALL THE CONSTRAINT DETAILS
-- ====================================================================
prompt
prompt
prompt Show the Non-Foreign Keys Constraints on this table
-- Show the Non-Foreign Keys Constraints on this table
  COL OWNER FOR A9 heading "Owner"
  COL constraint_name for A22 heading "Constraint Name"
  COL r_constraint_name for A22 heading "Referenced|Constraint Name"
  COL DELETE_RULE FOR A9 heading "DelRule"
  COL TABLE_NAME FOR A18 heading "Table Name"
  COL COLUMN_NAME FOR A30 heading "Column Name"
  COLUMN POSITION FOR 9999 heading "Pos"
  break on constraint_name skip page
  SELECT COL.OWNER,COL.CONSTRAINT_NAME,COL.COLUMN_NAME,COL.POSITION,
  DECODE (CON.CONSTRAINT_TYPE,'P','primary','R','foreign','U','unique','C','check') "Type"
  FROM DBA_CONS_COLUMNS COL,DBA_CONSTRAINTS CON 
WHERE COL.OWNER = upper('&&owner') 
AND COL.TABLE_NAME = upper('&&table')
AND CONSTRAINT_TYPE <> 'R' 
AND COL.OWNER = CON.OWNER 
AND COL.TABLE_NAME = CON.TABLE_NAME 
AND COL.CONSTRAINT_NAME = CON.CONSTRAINT_NAME
ORDER BY COL.CONSTRAINT_NAME, COL.POSITION;
-- ====================================================================
prompt
prompt
prompt Show the Foreign Keys on this table pointing at other tables Primary key fields for referential integreity purposes
-- Show the Foreign Keys on this table pointing at other tables Primary key fields for referential integreity purposes
  col "Ref Tab" for a22
  col "Ref Const" for a22
  col "Constraint Name" for a22
  SELECT CON.CONSTRAINT_NAME "Constraint Name", CON.R_CONSTRAINT_NAME,
    CON.DELETE_RULE, COL.COLUMN_NAME, COL.POSITION,
  -- CON1.OWNER,
  CON1.TABLE_NAME "Ref Tab",CON1.CONSTRAINT_NAME "Ref Const"
  -- COL1.COLUMN_NAME "Ref Column",
  -- COL1.POSITION
  --FROM DBA_CONS_COLUMNS COL,
  FROM DBA_CONSTRAINTS CON1,DBA_CONS_COLUMNS COL,DBA_CONSTRAINTS CON
  WHERE CON.OWNER = upper('&&owner')
    AND CON.TABLE_NAME = upper('&&table')
    AND CON.CONSTRAINT_TYPE = 'R'
    AND COL.OWNER = CON.OWNER
    AND COL.TABLE_NAME = CON.TABLE_NAME
    AND COL.CONSTRAINT_NAME = CON.CONSTRAINT_NAME
    AND CON1.OWNER = CON.OWNER
    AND CON1.CONSTRAINT_NAME = CON.R_CONSTRAINT_NAME
    AND CON1.CONSTRAINT_TYPE IN ( 'P', 'U' );
  
-- ================================================================
prompt 
prompt 
prompt Show the Foreign Keys pointing at this table via the recursive call to the constraints table
-- Show the Foreign Keys pointing at this table via the recursive call to the constraints table
  col "Constraint Name" for a35
  col TABLE_NAME for A25 heading "Table Name"
  SELECT CON1.OWNER||'.'||CON1.TABLE_NAME||'-'||CON1.CONSTRAINT_NAME "Constraint Name",CON1.DELETE_RULE,CON1.STATUS, 
  CON.TABLE_NAME,CON.CONSTRAINT_NAME,COL.POSITION,COL.COLUMN_NAME 
  FROM DBA_CONSTRAINTS CON,DBA_CONS_COLUMNS COL,DBA_CONSTRAINTS CON1 
WHERE CON.OWNER = upper('&&owner')
    AND CON.TABLE_NAME = upper('&&table')
    AND ((CON.CONSTRAINT_TYPE = 'P') OR (CON.CONSTRAINT_TYPE = 'U'))
    AND COL.TABLE_NAME = CON1.TABLE_NAME
    AND COL.CONSTRAINT_NAME = CON1.CONSTRAINT_NAME
    AND CON1.OWNER = CON.OWNER
    AND CON1.R_CONSTRAINT_NAME = CON.CONSTRAINT_NAME
    AND CON1.CONSTRAINT_TYPE = 'R'
  GROUP BY CON1.OWNER,CON1.TABLE_NAME,CON1.CONSTRAINT_NAME,CON1.DELETE_RULE,
    CON1.STATUS,CON.TABLE_NAME,CON.CONSTRAINT_NAME,COL.POSITION,COL.COLUMN_NAME;
-- ==========================================================
prompt
prompt
prompt Show all the check Constraints
-- Show all the check Constraints
  SET HEADING OFF
  col search_condition for a38
  select 'alter table '||TABLE_NAME||' add constraint '||constraint_name||' check (',SEARCH_CONDITION,'); '
  from dba_constraints WHERE OWNER = upper('&&owner') 
  AND TABLE_NAME = upper('&&table')
 AND CONSTRAINT_TYPE = 'C';
-- ==========================================================
prompt
prompt
prompt Show all the Triggers that have been created on this table

-- Show all the Triggers that have been created on this table
-- add query to extract Trigger Body etcc WHEN CLAUSE here.

SET ARRAYSIZE 1
SET LONG 6000000
select owner,'create or replace trigger ',trigger_name,description,trigger_body,'/'
from dba_triggers 
where owner = upper('&&owner') 
and table_name = upper('&&table');
-- ========================================================
prompt
prompt
prompt Show all the GRANTS made on this table and it's columns.
-- Show all the GRANTS made on this table and it's columns.
-- ========================================================
-- Table 1st
-- =========

select 'GRANT ',privilege,' ON ',TABLE_NAME,' TO ',GRANTEE,';' 
from DBA_TAB_PRIVS 
where OWNER = upper('&&owner')
AND TABLE_NAME = upper('&&table');

-- Columns 2nd
-- ===========
SELECT 'GRANT ',PRIVILEGE,' ( ',COLUMN_NAME,' ) ',' ON ',TABLE_NAME,' TO ',GRANTEE,';'
 FROM DBA_COL_PRIVS
 WHERE OWNER = upper('&&owner') 
AND TABLE_NAME = upper('&&table');
SET HEADING ON 



====

@table_info1

SQL> set linesize 155
SQL> set pagesize 60
SQL> set echo on feed on arraysize 1 LONG 5000 verify off
SQL> set linesize 140 echo off feed off


Show the Table Structure

EMPNO                          NUMBER                   22 N    1
ENAME                          VARCHAR2                 10 Y    2
JOB                            VARCHAR2                  9 Y    3
MGR                            NUMBER                   22 Y    4
HIREDATE                       DATE                      7 Y    5
SAL                            NUMBER                   22 Y    6
COMM                           NUMBER                   22 Y    7
DEPTNO                         NUMBER                   22 Y    8
Elapsed: 00:00:00.00


Show Physical Attributes

  10           65536        1048576  2147483645         14     38
Elapsed: 00:00:00.00


Show the actual Maximum Size of a Row

          136
Elapsed: 00:00:00.00


Show the Number of Physical EXTENTS that have been allocated Attributes

EMP                                                 1
Elapsed: 00:00:00.04


Show the Physical SIZE IN BYTES of the TABLE

EMP                                          65536
Elapsed: 00:00:00.04


Show all the indexes and their columns for this table

SCOTT    SCOTT    SYS_C0022543                   UNIQUE    EMPNO                              1
Elapsed: 00:00:00.00


Display all the physical details of the Primary and Other Indexes for table

SCOTT    SCOTT    SYS_C0022543                   UNIQUE    EMPNO                              1   10           65536    1048576  2147483645
Elapsed: 00:00:00.00


Show the Non-Foreign Keys Constraints on this table

SCOTT     SYS_C0022543           EMPNO                              1 primary
Elapsed: 00:00:00.79


Show the Foreign Keys on this table pointing at other tables Primary key fields for referential integreity purposes
Elapsed: 00:00:00.01


Show the Foreign Keys pointing at this table via the recursive call to the constraints table
Elapsed: 00:00:03.74


Show all the check Constraints
Elapsed: 00:00:00.30


Show all the Triggers that have been created on this table
Elapsed: 00:00:00.30


Show all the GRANTS made on this table and it's columns.
Elapsed: 00:00:00.03
Elapsed: 00:00:00.00

Oracle Sort Report

Oracle sort report

Oracle temp space report

prompt
prompt Report Current Sort Activity.
prompt

set linesize 150
set verify off
set pagesize 50

prompt SQL work area (current)
col sid for 99990
col operation_type for a20
col wsize  for 999,990     heading 'Current|W.Size(k)'
col esize  for 999,990     heading 'Expected|W.Size(k)'
col amsize for 999,999,990 heading 'Current|Mem.(k)'
col mmsize for 999,999,990 heading 'Maximum|Mem.(k)'
col tsize  for 999,999,990 heading 'Tmp. Seg.|Size (k)'
col passes for 999,990     heading 'Passes'
compute sum of wsize  on report
compute sum of wsize  on report
compute sum of esize  on report
compute sum of amsize on report
compute sum of mmsize on report
compute sum of tsize  on report
break on report
select SID
     , OPERATION_TYPE
     , WORK_AREA_SIZE/1024   as wsize
     , EXPECTED_SIZE/1024    as esize
     , ACTUAL_MEM_USED/1024  as amsize
     , MAX_MEM_USED/1024     as mmsize
     , TEMPSEG_SIZE/1024     as tsize
     , NUMBER_PASSES         as passes
from v$sql_workarea_active
order by sid
/

column max_space   format a11         heading 'Tablespace|Name'
column max_tot_mb  format 999,990     heading 'Max Total|Mbytes'
column max_used_mb format 999,990     heading 'Max Single|Use Mbytes'
column max_sort_mb format 999,990     heading 'Max Single|Sort Mbytes'
column max_file_mb format 999,990     heading 'Tablespace|Size (Mb)'
column ftype                          heading 'File|Type'
column extent_size format 99,999,999  heading 'Ext. Size|(Bytes)'

-- get the database blk size
column blk_size new_value _blk_size
set termout off
select to_number (value) as blk_size from  v$parameter
where  upper(name) = 'DB_BLOCK_SIZE';
set termout on

prompt sort segments:
select s.tablespace_Name                          as max_space
     , ftype                                      as ftype
     , (s.extent_size*&_blk_size)                 as extent_size
     , (file_bytes)/(1024*1024)                   as max_file_mb
     , (s.max_blocks*&_blk_size)/(1024*1024)      as max_tot_mb
     , (s.max_used_blocks*&_blk_size)/(1024*1024) as max_used_mb
     , (s.max_sort_blocks*&_blk_size)/(1024*1024) as max_sort_mb
from  v$sort_segment s
,(select tablespace_name
       , sum (bytes) as file_bytes
       , 'Data' as ftype
  from   dba_data_files
  group by tablespace_name
  union
  select tablespace_name
       , sum (bytes) as file_bytes
       , 'Temp' as ftype
  from   dba_temp_files
  group by tablespace_name
) f
where f.tablespace_name = s.tablespace_name
order by 1
/

compute sum of srt_mb  on srt_space
compute sum of srt_ext on srt_space
break on srt_space skip page

column srt_space           format a11     heading 'TSpace'
column srt_sid_serial      format A15     heading 'Sid serial'
column srt_osuser          format a25     heading 'OS User / Schema'
column srt_program         format a20     heading 'Program'
column srt_event           format a30     heading 'Wait Event'
column srt_mb              format 999,990 heading 'Mbytes'
column srt_ext             format 99,990  heading 'Extents'

prompt sort segment usage:
select u.tablespace                      as srt_space
     , s.sid||','||serial#               as srt_sid_serial
     , s.osuser||'/'||s.schemaname       as srt_osuser
     , substr (s.program,1,20)           as srt_program
     , u.segtype
     , u.extents                         as srt_ext
     , (u.blocks*&_blk_size)/(1024*1024) as srt_mb
     , w.event                           as srt_event
     , s.sql_id
from v$sort_usage u, v$session s, v$session_wait w
where u.session_addr = s.saddr (+)
and   s.sid = w.sid (+)
order by s.osuser, s.schemaname, s.program, s.sid, u.extents
/

clear breaks
clear computes

====

SQL> @dba_sort

Report Current Sort Activity.

SQL work area (current)

                              Current  Expected      Current      Maximum    Tmp. Seg.
   SID OPERATION_TYPE       W.Size(k) W.Size(k)      Mem.(k)      Mem.(k)     Size (k)   Passes
------ -------------------- --------- --------- ------------ ------------ ------------ --------
                            --------- --------- ------------ ------------ ------------
sum

no rows selected

sort segments:

Tablespace  File   Ext. Size Tablespace Max Total Max Single  Max Single
Name        Type     (Bytes)  Size (Mb)    Mbytes Use Mbytes Sort Mbytes
----------- ---- ----------- ---------- --------- ---------- -----------
TEMP        Temp   1,048,576        326       325         11           2

1 row selected.

sort segment usage:

TSpace      Sid serial      OS User / Schema          Program              SEGTYPE   Extents   Mbytes Wait Event                     SQL_ID
----------- --------------- ------------------------- -------------------- --------- ------- -------- ------------------------------ -------------
TEMP        43,7069         oracle/SYS                sqlplus@apt-amd-02 ( LOB_DATA        1        1 SQL*Net message from client
            47,12494        oracle/SYS                sqlplus@apt-amd-02 ( DATA            1        1 SQL*Net message from client
***********                                                                          ------- --------
sum                                                                                        2        2

2 rows selected.

Oracle Top SQL

Oracle Top Sql



col sql_text       format a65        heading 'SQL Text'
col sharable_mem   format 999999     heading 'SHARED|MEMORY'
col persistent_mem format 999999     heading 'PERSIST|MEMORY'
col runtime_mem    format 999999     heading 'RUNTIME|MEMORY'
col loads          format 9999       heading 'LOADS'
col invalidations  format 9999       heading 'INVALID'
col parse_calls    format 999999     heading 'PARSE|CALLS'
col executions     format 999999     heading 'EXECUTE'
col log_phy        format 9999       heading 'LOG/|PHY'
col disk_reads     format 9999999    heading 'DISK|READS'
col phy_exe        format 999999     heading 'PHY/|EXE'
col buffer_gets    format 999999999  heading 'BUFFER|GETS'
col log_exe        format 9999999    heading 'LOG/|EXE'
col sorts          format 9999       heading 'S'
col rows_processed format 99999999   heading 'ROWS|PROCESSED'
col rows_exe       format 9999999    heading 'ROWS/|EXE'




select
       loads,
       optimizer_mode,
       rows_processed,
       sorts,
       parse_calls,
       executions,
       disk_reads,
       buffer_gets,
       sql_id,
       sql_text
  from gv$sqlarea
 where parse_calls >= 0
    and executions >= 2
    and rows_processed >= 5
    and disk_reads >= 100
    and buffer_gets >= 1000
 order by buffer_gets asc;




                      ROWS         PARSE             DISK     BUFFER
LOADS OPTIMIZER_ PROCESSED     S   CALLS EXECUTE    READS       GETS SQL_ID        SQL Text
----- ---------- --------- ----- ------- ------- -------- ---------- ------------- -----------------------------------------------------------------
    9 ALL_ROWS         119     0       2      26      427       1023 9wygvu6cx2npy DELETE FROM WRI$_ADV_MESSAGE_GROUPS A WHERE A.TASK_ID = :B2 AND (
                                                                                   :B1 IS NULL OR :B1 = A.EXEC_NAME)

   12 CHOOSE           592     0     232     592      458       1851 g3wrkmxkxzhf2 select cols,audit$,textlength,intcols,property,flags,rowid from v
                                                                                   iew$ where obj#=:1

    8 CHOOSE           702     0     702     702      330       2163 b1wc53ddd6h3p select audit$,options from procedure$ where obj#=:1
    2 ALL_ROWS           9     0       9       9      132       2850 2nszajb0qbyvp DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;  b
                                                                                   roken BOOLEAN := FALSE; BEGIN wwv_flow_mail.push_queue(wwv_flow_p
                                                                                   latform.get_preference('SMTP_HOST_ADDRESS'),wwv_flow_platform.get
                                                                                   _preference('SMTP_HOST_PORT')); :mydate := next_date; IF broken T
                                                                                   HEN :b := 1; ELSE :b := 0; END IF; END;

    7 CHOOSE           688     0    1245    1245      696       4762 c6awqs517jpj0 select /*+ index(idl_char$ i_idl_char1) +*/ piece#,length,piece f
                                                                                   rom idl_char$ where obj#=:1 and part=:2 and version=:3 order by p
                                                                                   iece#

    7 ALL_ROWS         396    16       2       2      801       4866 g5m0bnvyy37b1 select sql_id, plan_hash_value, bucket_id,        begin_snap, end
                                                                                   _snap from   (select   dbid, sql_id, bucket_id, plan_hash_value,
                                                                                            begin_snap, end_snap, cpu_plus_io from     (select  dbid
                                                                                   , sql_id, bucket_id, plan_hash_value,                   begin_sna
                                                                                   p, end_snap, cpu_plus_io,                   row_number() over (pa
                                                                                   rtition by bucket_id                                      order b
                                                                                   y     cpu_plus_io desc)                     as within_bucket_rnk
                                                                                             from    (select   dbid, sql_id, bucket_id,
                                                                                                   max(plan_hash_value) keep (dense_rank last
                                                                                                           order by cpu_plus_io) plan_hash_value,
                                                                                                             max(begin_snap) keep (dense_rank last
                                                                                                                order by cpu_plus_io) begin_snap,
                                                                                                             max(end_snap) keep (dense_rank last
                                                                                                              order by cpu_plus_io) end_snap,
                                                                                                         max(cpu_plus_io) cpu_plus_io
                                                                                        from     (select dbi

    4 ALL_ROWS          60     0      60      60      177       5679 ga6ja2d04ycbm DECLARE job BINARY_INTEGER := :job;  next_date TIMESTAMP WITH TIM
                                                                                   E ZONE := :mydate;  broken BOOLEAN := FALSE;  job_name VARCHAR2(3
                                                                                   0) := :job_name;  job_subname VARCHAR2(30) := :job_subname;  job_
                                                                                   owner VARCHAR2(30) := :job_owner;  job_start TIMESTAMP WITH TIME
                                                                                   ZONE := :job_start;  job_scheduled_start TIMESTAMP WITH TIME ZONE
                                                                                    := :job_scheduled_start;  window_start TIMESTAMP WITH TIME ZONE
                                                                                   := :window_start;  window_end TIMESTAMP WITH TIME ZONE := :window
                                                                                   _end;  chain_id VARCHAR2(14) :=  :chainid;  credential_owner varc
                                                                                   har2(30) := :credown;  credential_name  varchar2(30) := :crednam;
                                                                                     destination_owner varchar2(30) := :destown;  destination_name v
                                                                                   archar2(30) := :destnam;  job_dest_id varchar2(14) := :jdestid;
                                                                                   BEGIN  begin dbms_rlmgr_dr.execschdactions('RLM$SCHDNEGACTION');
                                                                                   end;  :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0;
                                                                                    END IF; END;

    4 CHOOSE          2881     0     278     278      122       6316 bgjhtnqhr5u9h select procedure#,entrypoint# from procedureplsql$ where obj#=:1
                                                                                   order by procedure#

   10 CHOOSE          2469     0      19      19      115      10348 2mp99nzd9u1qp delete from histgrm$ where obj# = :1
    1 CHOOSE          2304     0    1677    1677     2519      10429 39m4sx9k63ba2 select /*+ index(idl_ub2$ i_idl_ub21) +*/ piece#,length,piece fro
                                                                                   m idl_ub2$ where obj#=:1 and part=:2 and version=:3 order by piec
                                                                                   e#

    1 CHOOSE          2406     0    1677    1677     1292      10660 ga9j9xk5cy9s0 select /*+ index(idl_sb4$ i_idl_sb41) +*/ piece#,length,piece fro
                                                                                   m idl_sb4$ where obj#=:1 and part=:2 and version=:3 order by piec
                                                                                   e#

    5 CHOOSE          5238     0     254     254      350      11126 dcstr36r0vz0d select procedure#,procedurename,properties,itypeobj# from procedu
                                                                                   reinfo$ where obj#=:1 order by procedurename desc, overload# desc

    6 CHOOSE          3404  1162    1162    1162      508      11389 3ktacv9r56b51 select owner#,name,namespace,remoteowner,linkname,p_timestamp,p_o
                                                                                   bj#, nvl(property,0),subname,type#,d_attrs from dependency$ d, ob
                                                                                   j$ o where d_obj#=:1 and p_obj#=obj#(+) order by order#

    1 CHOOSE           549     0     549     549      234      13172 9ctt1scmwbmbg begin dbsnmp.bsln_internal.maintain_thresholds; end;
   18 RULE           67066  4333     142    4333      461      13213 db78fxqxwxt7r select /*+ rule */ bucket, endpoint, col#, epvalue from histgrm$
                                                                                   where obj#=:1 and intcol#=:2 and row#=:3 order by bucket

   12 CHOOSE          2605  6831    2257    6831      296      15610 2q93zsrvbdw48 select grantee#,privilege#,nvl(col#,0),max(mod(nvl(option$,0),2))
                                                                                   from objauth$ where obj#=:1 group by grantee#,privilege#,nvl(col#
                                                                                   ,0) order by grantee#

    1 CHOOSE          3985     0    1677    1677     4666      16132 cvn54b7yz0s8u select /*+ index(idl_ub1$ i_idl_ub11) +*/ piece#,length,piece fro
                                                                                   m idl_ub1$ where obj#=:1 and part=:2 and version=:3 order by piec
                                                                                   e#

    2 CHOOSE          8083     0    3800    3800     1414      23798 8swypbbr0m372 select order#,columns,types from access$ where d_obj#=:1
    5 CHOOSE            12     0   14612   14612      144      29294 20vv6ttajyjzq delete from access$ where d_obj#=:1
    4 ALL_ROWS        1593    58      58      58      882      30606 gnux0zb3sxduk SELECT TIMEPOINT, DELTA_SPACE_USAGE, DELTA_SPACE_ALLOC, TOTAL_SPA
                                                                                   CE_USAGE, TOTAL_SPACE_ALLOC, INSTANCE_NUMBER, OBJN FROM TABLE(DBM
                                                                                   S_SPACE.OBJECT_GROWTH_TREND_SWRF(:B1 , :B2 , :B3 , :B4 )) ORDER B
                                                                                   Y TIMEPOINT

    3 FIRST_ROWS       364   728       2     364      109      33379 424h0nf7bhqzd  SELECT sqlset_row(sql_id, force_matching_signature,           sq
                                                                                   l_text, object_list, bind_data, parsing_schema_name,           mo
                                                                                   dule, action, elapsed_time, cpu_time, buffer_gets,           disk
                                                                                   _reads, direct_writes, rows_processed, fetches,           executi
                                                                                   ons, end_of_fetch_count, optimizer_cost,           optimizer_env,
                                                                                    priority, command_type, first_load_time,           stat_period,
                                                                                   active_stat_period, other, plan_hash_value,           sql_plan, b
                                                                                   ind_list) FROM (  SELECT /*+ first_rows(1) */ sql_id, force_match
                                                                                   ing_signature, sql_text, cast(NULL as SQL_OBJECTS) object_list, b
                                                                                   ind_data, parsing_schema_name, module, action, elapsed_time, cpu_
                                                                                   time, buffer_gets, disk_reads, direct_writes,rows_processed, fetc
                                                                                   hes, executions, end_of_fetch_count, optimizer_cost, optimizer_en
                                                                                   v,NULL priority, command_type, NULL first_load_time, null stat_pe
                                                                                   riod, null active_stat_period, xmlelement(
                                                                                       "other_attrs",                            xmlelement("parsing
                                                                                   _user_id",

    3 CHOOSE         17848     0   17848   17848      567      35777 grwydz59pu6mc select text from view$ where rowid=:1
    7 CHOOSE            28     0   14462   14462      157      43742 1gfaj4z5hn1kf delete from dependency$ where d_obj#=:1
    4 CHOOSE         24529     0     254    5238      649      64917 32hbap2vtmf53 select position#,sequence#,level#,argument,type#,charsetid,charse
                                                                                   tform,properties,nvl(length, 0), nvl(precision#, 0),nvl(scale, 0)
                                                                                   ,nvl(radix, 0), type_owner,type_name,type_subname,type_linkname,p
                                                                                   ls_type from argument$ where obj#=:1 and procedure#=:2 order by s
                                                                                   equence# desc

    9 RULE           25858     0     641   28937      430      84837 96g93hntrzjtr select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, time
                                                                                   stamp#, sample_size, minimum, maximum, distcnt, lowval, hival, de
                                                                                   nsity, col#, spare1, spare2, avgcln from hist_head$ where obj#=:1
                                                                                    and intcol#=:2

    4 ALL_ROWS        2088     0      58      58   123112     157671 8szmwam7fysa3 insert into wri$_adv_objspace_trend_data select timepoint,  space
                                                                                   _usage, space_alloc, quality from  table(dbms_space.object_growth
                                                                                   _trend(:1, :2, :3, :4, NULL, NULL,  NULL, 'FALSE', :5, 'FALSE'))

   11 ALL_ROWS         130    10       2       2      126     160747 2tr12b1b8uj71 MERGE /*+ dynamic_sampling(ST 4) dynamic_sampling_est_cdn(ST) */
                                                                                   INTO STATS_TARGET$ ST USING (SELECT STALENESS, OSIZE, OBJ#, TYPE#
                                                                                   , CASE WHEN STALENESS > LOG(0.01, NVL(LOC_STALE_PCT, :B1 )/100) T
                                                                                   HEN 128 ELSE 0 END + AFLAGS AFLAGS, STATUS, SID, SERIAL#, PART#,
                                                                                   BO# FROM ( SELECT /*+ no_expand dynamic_sampling(4) dynamic_sampl
                                                                                   ing_est_cdn */ DECODE(BITAND(T.FLAGS,16), 16, ROUND( LOG(0.01, NV
                                                                                   L( LEAST( 100, GREATEST( 0.01, (DECODE(BITAND(M.FLAGS, 1), 1, GRE
                                                                                   ATEST(T.ROWCNT, M.INSERTS), LEAST((M.INSERTS + M.DELETES + M.UPDA
                                                                                   TES), GREATEST(T.ROWCNT, (T.ROWCNT + M.INSERTS - M.DELETES)))) /
                                                                                   (T.ROWCNT + 0.01)))), 0.01)), 1), -100.0) STALENESS, CASE WHEN T.
                                                                                   FILE# = 0 THEN DBMS_STATS_INTERNAL.GET_TABLE_BLOCK_COUNT(U.NAME,
                                                                                   O.NAME, NULL, NULL, 'TRUE') WHEN S.TYPE# = 5 THEN DBMS_STATS_INTE
                                                                                   RNAL.SEGMENT_NUMBER_BLOCKS(T.TS#, T.FILE#, T.BLOCK#, S.TYPE#, S.C
                                                                                   ACHEHINT, NVL(S.SPARE1,0), O.DATAOBJ#, S.BLOCKS, 'TRUE') ELSE NUL
                                                                                   L END * NVL(TS.BLOCKSIZE, :B6 ) OSIZE, O.OBJ# OBJ#, O.TYPE# TYPE#
                                                                                   , 32 AFLAGS, 0 STATUS, :B

   13 CHOOSE        103829     0    1933   38518      120     284702 5n1fs4m2n2y0r select pos#,intcol#,col#,spare1,bo#,spare2,spare3 from icol$ wher
                                                                                   e obj#=:1

    1 CHOOSE            24     0      24      24    50024   35848714 59v4zh1ac3v2a DECLARE job BINARY_INTEGER := :job;  next_date TIMESTAMP WITH TIM
                                                                                   E ZONE := :mydate;  broken BOOLEAN := FALSE;  job_name VARCHAR2(3
                                                                                   0) := :job_name;  job_subname VARCHAR2(30) := :job_subname;  job_
                                                                                   owner VARCHAR2(30) := :job_owner;  job_start TIMESTAMP WITH TIME
                                                                                   ZONE := :job_start;  job_scheduled_start TIMESTAMP WITH TIME ZONE
                                                                                    := :job_scheduled_start;  window_start TIMESTAMP WITH TIME ZONE
                                                                                   := :window_start;  window_end TIMESTAMP WITH TIME ZONE := :window
                                                                                   _end;  chain_id VARCHAR2(14) :=  :chainid;  credential_owner varc
                                                                                   har2(30) := :credown;  credential_name  varchar2(30) := :crednam;
                                                                                     destination_owner varchar2(30) := :destown;  destination_name v
                                                                                   archar2(30) := :destnam;  job_dest_id varchar2(14) := :jdestid;
                                                                                   BEGIN  DECLARE          ename VARCHAR2(30);        BEGIN
                                                                                    ename := dbms_sqltune.execute_tuning_task(                     '
                                                                                   SYS_AUTO_SQL_TUNING_TASK');        END;  :mydate := next_date; IF
                                                                                    broken THEN :b := 1; ELSE :b := 0; END IF; END;


29 rows selected.

Oracle Role detail Info

roles - Roles, Privileges, assigned users

set serveroutput on feedback off verify off pages 0

spool /tmp/roleinfo.lst

declare
     wrole varchar2 (30) := '&amp1';
     
     /*  Users */
     
     cursor crole is select role
     from dba_roles where
     role like upper(wrole);
     
     /* Roles granted */
     
     cursor crg (r in varchar2) is
     select granted_role, admin_option, default_role
     from dba_role_privs where
     grantee = upper(r)
     order by granted_role;
     
     /* System privileges granted */
     
     cursor csg (r in varchar2) is
     select privilege, admin_option
     from dba_sys_privs where
     grantee = upper(r)
     order by privilege;
     
     /* Object privileges granted */
     
     cursor cog (r in varchar2) is
     select (owner ||'.'|| table_name) object, privilege
     from dba_tab_privs where
     grantee = upper(r)
     order by owner, table_name;
     
     /* Column privileges granted */
     
     cursor ccg (r in varchar2) is
     select (owner ||'.'|| table_name ||'.'|| column_name) wcolumn, privilege
     from dba_col_privs 
     where  grantee = upper(r)
     order by  owner, table_name, column_name;
     
     /* Users / roles granted this role */
     
     cursor cug (r in varchar2) is 
     select grantee, admin_option, default_role
     from dba_role_privs where
     granted_role = upper(r)
     order by grantee;
     wcount number := 0;
     
     wdate varchar2 (25) := to_char(sysdate,'Mon DD, YYYY  HH:MI AM');
     w5space char(5) := '.    ';
     wdum1 varchar2 (255);
     wdum2 varchar2 (255);
     wdum3 varchar2 (255);
     wdum4 varchar2 (255);
     wdum5 varchar2 (255);
     wdum6 varchar2 (255);
     wdum7 varchar2 (255);
  begin
    dbms_output.enable(100000);
    for rrole in crole loop
      dbms_output.put_line('********** ROLE INFORMATION **********                ' || wdate);
      dbms_output.put_line('*--------------------------------------------------------------------------*');
      wcount := wcount + 1;
      dbms_output.put_line('Role Name : ' || rrole.role);
      dbms_output.put_line(w5space);
      open crg (rrole.role);
      fetch crg into wdum1, wdum2, wdum3;
      if crg%notfound then
         dbms_output.put_line('********** ' || rrole.role || ' - NO OTHER ROLES GRANTED  *********');
         close crg;
      else
         close crg;
         dbms_output.put_line('********** ' || rrole.role || ' - OTHER ROLES GRANTED  *********');
         dbms_output.put_line(w5space || 'Role name                                         Admin     Default');
         dbms_output.put_line(w5space || '*---------------------------------------------------------------------*');
         for rrg  in crg (rrole.role) loop
          dbms_output.put_line(w5space || rpad(rrg.granted_role,50) || rpad(rrg.admin_option,10) || rpad(rrg.default_role,10));
         end loop;
         dbms_output.put_line(w5space);
      end if;
      dbms_output.put_line(w5space);
      open csg (rrole.role);
      fetch csg into wdum1, wdum2;
      if csg%notfound then
         dbms_output.put_line('********** ' || rrole.role || ' - NO SYSTEM PRIVILEGES GRANTED  *********');
         close csg;
      else
         close csg;
         dbms_output.put_line('********** ' || rrole.role || ' - SYSTEM PRIVILEGES GRANTED  *********');

         dbms_output.put_line(w5space || 'System Privilege                                  Admin');
         dbms_output.put_line(w5space || '*---------------------------------------------------------------------*');
         for rsg  in csg (rrole.role) loop
          dbms_output.put_line(w5space || rpad(rsg.privilege,50) || rpad(rsg.admin_option,10));
         end loop;
         dbms_output.put_line(w5space);
      end if;
      dbms_output.put_line(w5space);
      open cog (rrole.role);
      fetch cog into wdum1, wdum2;
      if cog%notfound then
         dbms_output.put_line('********** ' || rrole.role || ' - NO OBJECT PRIVILEGES GRANTED  *********');
         close cog;
      else
         close cog;
         dbms_output.put_line('********** ' || rrole.role || ' - OBJECT PRIVILEGES GRANTED  *********');

         dbms_output.put_line(w5space || 'Object Name                             Privilege');
         dbms_output.put_line(w5space || '*---------------------------------------------------------------------*');
         for rog  in cog (rrole.role) loop
          dbms_output.put_line(w5space || rpad(rog.object,40) || rpad(rog.privilege,30));
         end loop;
         dbms_output.put_line(w5space);
      end if;
      dbms_output.put_line(w5space);
      open ccg (rrole.role);
      fetch ccg into wdum1, wdum2;
      if ccg%notfound then
         dbms_output.put_line('********** ' || rrole.role || ' - NO COLUMN PRIVILEGES GRANTED  *********');
         close ccg;
      else
         close ccg;
         dbms_output.put_line('********** ' || rrole.role || ' - COLUMN PRIVILEGES GRANTED  *********');

         dbms_output.put_line(w5space || 'Column Name                                       Privilege');
         dbms_output.put_line(w5space || '*---------------------------------------------------------------------*');
         for rcg  in ccg (rrole.role) loop
         dbms_output.put_line(w5space || rpad(rcg.wcolumn,50) || rpad(rcg.privilege,20));
         end loop;
         dbms_output.put_line(w5space);
      end if;
      dbms_output.put_line(w5space);
      open cug (rrole.role);
      fetch cug into wdum1, wdum2, wdum3;
      if cug%notfound then
         dbms_output.put_line('********** ' || rrole.role || ' - NO USERS/ROLES ASSIGNED  *********');
         close cug;
      else
         close cug;
         dbms_output.put_line('********** ' || rrole.role || ' - USERS/ROLES ASSIGNED  *********');
         dbms_output.put_line(w5space || 'User / Role Name                                  Admin     Default');
         dbms_output.put_line(w5space || '*---------------------------------------------------------------------*');
         for rug  in cug (rrole.role) loop
          dbms_output.put_line(w5space || rpad(rug.grantee,50) || rpad(rug.admin_option,10) || rpad(rug.default_role,10));
         end loop;
         dbms_output.put_line(w5space);
      end if;
      dbms_output.put_line('*--------------------------------------------------------------------------*');
    end loop;
    if wcount =0 then
      dbms_output.put_line('******************************************************');
      dbms_output.put_line('*                                                    *');
      dbms_output.put_line('* Plese Verify Input Parameters... No Matches Found! *');
      dbms_output.put_line('*                                                    *');
      dbms_output.put_line('******************************************************');
    end if;
  end;
/
set serveroutput off feedback on verify on pages 999
spool off
prompt
prompt Output saved at /tmp/roleinfo.lst






SQL> @role10
Enter value for amp1: CONNECT
********** ROLE INFORMATION **********                Nov 27, 2011  07:24 AM
*--------------------------------------------------------------------------*
Role Name : CONNECT
.
********** CONNECT - NO OTHER ROLES GRANTED  *********
.
********** CONNECT - SYSTEM PRIVILEGES GRANTED  *********
.    System Privilege                                  Admin
.    *---------------------------------------------------------------------*
.    CREATE SESSION                                    NO
.
.
********** CONNECT - NO OBJECT PRIVILEGES GRANTED  *********
.
********** CONNECT - NO COLUMN PRIVILEGES GRANTED  *********
.
********** CONNECT - USERS/ROLES ASSIGNED  *********
.    User / Role Name                                  Admin     Default
.    *---------------------------------------------------------------------*
.    ABC                                               NO        YES
.    ANUJ                                              NO        YES
.    ANUJREP                                           NO        YES
.    ANUJTEST                                          NO        YES
.    APEX_030200                                       YES       YES
.    APEX_040000                                       NO        YES
.    GGATE                                             NO        YES
.    IX                                                NO        YES
.    MDDATA                                            NO        YES
.    MDSYS                                             NO        YES
.    OWBSYS                                            YES       YES
.    PM                                                NO        YES
.    SCOTT                                             NO        YES
.    SPATIAL_CSW_ADMIN_USR                             NO        YES
.    SPATIAL_WFS_ADMIN_USR                             NO        YES
.    SYS                                               YES       YES
.    TEST_USER                                         YES       YES
.    VIHAAN                                            NO        YES
.    WMSYS                                             NO        YES
.    XYZ                                               NO        YES
.
*--------------------------------------------------------------------------*

Output saved at /tmp/roleinfo.lst


SQL>  @role10
Enter value for amp1: RESOURCE
********** ROLE INFORMATION **********                Nov 27, 2011  07:25 AM
*--------------------------------------------------------------------------*
Role Name : RESOURCE
.
********** RESOURCE - NO OTHER ROLES GRANTED  *********
.
********** RESOURCE - SYSTEM PRIVILEGES GRANTED  *********
.    System Privilege                                  Admin
.    *---------------------------------------------------------------------*
.    CREATE CLUSTER                                    NO
.    CREATE INDEXTYPE                                  NO
.    CREATE OPERATOR                                   NO
.    CREATE PROCEDURE                                  NO
.    CREATE SEQUENCE                                   NO
.    CREATE TABLE                                      NO
.    CREATE TRIGGER                                    NO
.    CREATE TYPE                                       NO
.
.
********** RESOURCE - NO OBJECT PRIVILEGES GRANTED  *********
.
********** RESOURCE - NO COLUMN PRIVILEGES GRANTED  *********
.
********** RESOURCE - USERS/ROLES ASSIGNED  *********
.    User / Role Name                                  Admin     Default
.    *---------------------------------------------------------------------*
.    ABC                                               NO        YES
.    ANUJ                                              NO        YES
.    ANUJREP                                           NO        YES
.    ANUJTEST                                          NO        YES
.    APEX_030200                                       YES       YES
.    APEX_040000                                       YES       YES
.    BI                                                NO        YES
.    CTXSYS                                            NO        YES
.    EXFSYS                                            NO        YES
.    GGATE                                             NO        YES
.    HR                                                NO        YES
.    IX                                                NO        YES
.    LOGSTDBY_ADMINISTRATOR                            NO        YES
.    MDDATA                                            NO        YES
.    MDSYS                                             NO        YES
.    OE                                                NO        YES
.    OLAPSYS                                           NO        YES
.    OUTLN                                             NO        YES
.    PM                                                NO        YES
.    SCOTT                                             NO        YES
.    SH                                                NO        YES
.    SPATIAL_CSW_ADMIN_USR                             NO        YES
.    SPATIAL_WFS_ADMIN_USR                             NO        YES
.    SYS                                               YES       YES
.    TEST_USER                                         YES       YES
.    VIHAAN                                            NO        YES
.    WMSYS                                             NO        YES
.    XDB                                               NO        YES
.    XYZ                                               NO        YES
.
*--------------------------------------------------------------------------*

Output saved at /tmp/roleinfo.lst