Oracle AWR SNAP Detail

Awr snap

Oracle AWR snap

[code]

define BEGIN = "2022-05-12 01:00:00"
define END = "2022-05-12 03:00:00"

set linesize 300 pagesize 300
col BEGIN_INTERVAL_TIME for a27
col END_INTERVAL_TIME for a27
select 
  snap_id,
  instance_number,
  begin_interval_time,
  end_interval_time
from dba_hist_snapshot
where dbid = (select dbid from v$database)
--and begin_interval_time >= to_timestamp('&BEGIN','YYYY-MM-DD HH24:MI:SS') and end_interval_time <= (to_timestamp('&END','YYYY-MM-DD HH24:MI:SS') + 5/24/60)
--and begin_interval_time >= to_timestamp('&BEGIN','YYYY-MM-DD HH24:MI:SS') and end_interval_time <= to_timestamp('&END','YYYY-MM-DD HH24:MI:SS')
and begin_interval_time >sysdate -1
order by snap_id, instance_number;



   SNAP_ID INSTANCE_NUMBER BEGIN_INTERVAL_TIME         END_INTERVAL_TIME
---------- --------------- --------------------------- ---------------------------
     41738               1 12-MAY-22 01.00.03.510 AM   12-MAY-22 02.00.11.856 AM
     41738               2 12-MAY-22 01.00.03.582 AM   12-MAY-22 02.00.11.905 AM
     41739               1 12-MAY-22 02.00.11.856 AM   12-MAY-22 03.00.20.062 AM
     41739               2 12-MAY-22 02.00.11.905 AM   12-MAY-22 03.00.20.100 AM

SQL>






====

SET LINESIZE   145
SET PAGESIZE   9999
SET TRIMSPOOL  ON
SET VERIFY     off

COLUMN instance_name_print  FORMAT a13                   HEADING 'Instance Name'
COLUMN snap_id              FORMAT 9999999               HEADING 'Snap ID'
COLUMN startup_time         FORMAT a21                   HEADING 'Instance Startup Time'
COLUMN begin_interval_time  FORMAT a20                   HEADING 'Begin Interval Time'
COLUMN end_interval_time    FORMAT a20                   HEADING 'End Interval Time'
COLUMN elapsed_time         FORMAT 999,999,999,999.99    HEADING 'Elapsed Time (min)'
COLUMN db_time              FORMAT 999,999,999,999.99    HEADING 'DB Time (min)'
COLUMN pct_db_time          FORMAT 999999999             HEADING '% DB Time'
COLUMN cpu_time             FORMAT 999,999,999.99        HEADING 'CPU Time (min)'

BREAK ON instance_name_print ON startup_time

SPOOL awr_snap.txt

SELECT
    i.instance_name                                                                     instance_name_print
  , s.snap_id                                                                           snap_id
  , TO_CHAR(s.startup_time, 'dd/mm/yyyy HH24:MI:SS')                                    startup_time
  , TO_CHAR(s.begin_interval_time, 'dd/mm/yyyy HH24:MI:SS')                             begin_interval_time
  , TO_CHAR(s.end_interval_time, 'dd/mm/yyyy HH24:MI:SS')                               end_interval_time
  , ROUND(EXTRACT(DAY FROM  s.end_interval_time - s.begin_interval_time) * 1440 +
          EXTRACT(HOUR FROM s.end_interval_time - s.begin_interval_time) * 60 +
          EXTRACT(MINUTE FROM s.end_interval_time - s.begin_interval_time) +
          EXTRACT(SECOND FROM s.end_interval_time - s.begin_interval_time) / 60, 2)     elapsed_time
  , ROUND((e.value - b.value)/1000000/60, 2)                                            db_time
  , ROUND(((((e.value - b.value)/1000000/60) / (EXTRACT(DAY FROM  s.end_interval_time - s.begin_interval_time) * 1440 +
                                                EXTRACT(HOUR FROM s.end_interval_time - s.begin_interval_time) * 60 +
                                                EXTRACT(MINUTE FROM s.end_interval_time - s.begin_interval_time) +
                                                EXTRACT(SECOND FROM s.end_interval_time - s.begin_interval_time) / 60) ) * 100), 2)   pct_db_time
FROM
    dba_hist_snapshot       s
  , gv$instance             i
  , dba_hist_sys_time_model e
  , dba_hist_sys_time_model b
WHERE
      i.instance_number = s.instance_number
  AND e.snap_id         = s.snap_id
  AND b.snap_id         = s.snap_id - 1
  AND e.stat_id         = b.stat_id
  AND e.instance_number = b.instance_number
  AND e.instance_number = s.instance_number
  AND e.stat_name       = 'DB time'
ORDER BY
    i.instance_name
  , s.snap_id;

SPOOL OFF



Instance Name  Snap ID Instance Startup Time Begin Interval Time  End Interval Time     Elapsed Time (min)       DB Time (min)  % DB Time
------------- -------- --------------------- -------------------- -------------------- ------------------- ------------------- ----------
orcl              5697 04/11/2011 10:27:02   20/11/2011 01:00:49  20/11/2011 02:00:51                60.03                 .03          0
                  5698                       20/11/2011 02:00:51  20/11/2011 03:00:52                60.03                 .03          0
                  5699                       20/11/2011 03:00:52  20/11/2011 04:00:54                60.03                 .02          0
                  5700                       20/11/2011 04:00:54  20/11/2011 05:00:56                60.03                 .02          0
                  5701                       20/11/2011 05:00:56  20/11/2011 06:00:59                60.06                2.47          4
                  5702                       20/11/2011 06:00:59  20/11/2011 07:00:02                59.05                4.15          7
                  5703                       20/11/2011 07:00:02  20/11/2011 08:00:04                60.04                 .58          1
                  5704                       20/11/2011 08:00:04  20/11/2011 09:00:06                60.03                 .03          0
                  5718                       20/11/2011 22:00:30  20/11/2011 23:00:32                60.03                1.09          2
                  5719                       20/11/2011 23:00:32  21/11/2011 00:00:34                60.03                 .03          0
                  5720                       21/11/2011 00:00:34  21/11/2011 01:00:36                60.04                 .05          0
                  5721                       21/11/2011 01:00:36  21/11/2011 02:00:38                60.03                 .04          0
                  5722                       21/11/2011 02:00:38  21/11/2011 03:00:40                60.03                 .04          0
                  5723                       21/11/2011 03:00:40  21/11/2011 04:00:41                60.03                 .03          0
                  5724                       21/11/2011 04:00:41  21/11/2011 05:00:43                60.03                 .03          0
                  5725                       21/11/2011 05:00:43  21/11/2011 06:00:45                60.03                 .03          0
                  5726                       21/11/2011 06:00:45  21/11/2011 07:00:46                60.03                 .04          0
                  5727                       21/11/2011 07:00:46  21/11/2011 08:00:50                60.06                 .06          0
                  5728                       21/11/2011 08:00:50  21/11/2011 09:00:52                60.03                 .04          0
                  5729                       21/11/2011 09:00:52  21/11/2011 10:00:55                60.04                 .05          0
                  5730                       21/11/2011 10:00:55  21/11/2011 11:00:57                60.04                 .38          1
                  5731                       21/11/2011 11:00:57  21/11/2011 12:00:05                59.13                 .34          1
                  5732                       21/11/2011 12:00:05  21/11/2011 13:00:10                60.09                 .35          1
                  5733                       21/11/2011 13:00:10  21/11/2011 14:00:15                60.08                 .10          0
                  5734                       21/11/2011 14:00:15  21/11/2011 15:00:18                60.05                 .11          0
                  5735                       21/11/2011 15:00:18  21/11/2011 16:00:21                60.05                 .07          0
                  5736                       21/11/2011 16:00:21  21/11/2011 17:00:23                60.04                 .07          0
                  5737                       21/11/2011 17:00:23  21/11/2011 18:00:25                60.03                 .03          0
                  5738                       21/11/2011 18:00:25  21/11/2011 19:00:27                60.03                 .03          0
                  5739                       21/11/2011 19:00:27  21/11/2011 20:00:28                60.03                 .13          0
                  5740                       21/11/2011 20:00:28  21/11/2011 21:00:30                60.03                 .03          0
                  5741                       21/11/2011 21:00:30  21/11/2011 22:00:32                60.03                1.75          3
                  5742                       21/11/2011 22:00:32  21/11/2011 23:00:41                60.15               40.19         67
                  5743                       21/11/2011 23:00:41  22/11/2011 00:00:43                60.03                 .04          0
                  5744                       22/11/2011 00:00:43  22/11/2011 01:00:45                60.04                 .05          0
                  5745                       22/11/2011 01:00:45  22/11/2011 02:00:47                60.03                 .05          0
                  5746                       22/11/2011 02:00:47  22/11/2011 03:00:49                60.03                 .03          0
                  5747                       22/11/2011 03:00:49  22/11/2011 04:00:50                60.03                 .03          0
                  5748                       22/11/2011 04:00:50  22/11/2011 05:00:52                60.03                 .03          0
                  5749                       22/11/2011 05:00:52  22/11/2011 06:00:54                60.03                 .03          0
                  5750                       22/11/2011 06:00:54  22/11/2011 07:00:55                60.03                 .03          0
                  5751                       22/11/2011 07:00:55  22/11/2011 08:00:58                60.05                 .07          0
                  5752                       22/11/2011 08:00:58  22/11/2011 09:00:05                59.12                 .57          1
                  5753                       22/11/2011 09:00:05  22/11/2011 10:00:10                60.08                 .37          1
                  5754                       22/11/2011 10:00:10  22/11/2011 11:00:16                60.09                 .86          1
                  5755                       22/11/2011 11:00:16  22/11/2011 12:00:20                60.06                 .07          0
                  5756                       22/11/2011 12:00:20  22/11/2011 13:00:24                60.08                 .18          0
                  5757                       22/11/2011 13:00:24  22/11/2011 14:00:31                60.11                 .16          0
                  5758                       22/11/2011 14:00:31  22/11/2011 15:00:37                60.11                1.00          2
                  5759                       22/11/2011 15:00:37  22/11/2011 16:00:42                60.08                 .58          1
                  5760                       22/11/2011 16:00:42  22/11/2011 17:00:47                60.09                 .20          0
                  5761                       22/11/2011 17:00:47  22/11/2011 18:00:50                60.06                 .04          0
                  5762                       22/11/2011 18:00:50  22/11/2011 19:00:54                60.06                 .03          0
                  5763                       22/11/2011 19:00:54  22/11/2011 20:00:57                60.06                 .03          0
                  5781                       23/11/2011 13:00:05  23/11/2011 14:00:09                60.07                 .04          0
                  5782                       23/11/2011 14:00:09  23/11/2011 15:00:14                60.08                 .40          1
                  5783                       23/11/2011 15:00:14  23/11/2011 16:00:20                60.09                 .16          0
                  5784                       23/11/2011 16:00:20  23/11/2011 17:00:28                60.14                 .33          1
                  5785                       23/11/2011 17:00:28  23/11/2011 18:00:32                60.06                 .04          0
                  5786                       23/11/2011 18:00:32  23/11/2011 19:00:36                60.07                 .04          0
                  5787                       23/11/2011 19:00:36  23/11/2011 20:00:39                60.06                 .03          0
                  5788                       23/11/2011 20:00:39  23/11/2011 21:00:42                60.06                 .03          0
                  5789                       23/11/2011 21:00:42  23/11/2011 22:00:53                60.17                2.91          5
                  5790                       23/11/2011 22:00:53  23/11/2011 23:00:58                60.10               56.04         93
                  5791                       23/11/2011 23:00:58  24/11/2011 00:00:02                59.07                 .04          0
                  5792                       24/11/2011 00:00:02  24/11/2011 01:00:07                60.08                 .05          0
                  5793                       24/11/2011 01:00:07  24/11/2011 02:00:10                60.06                 .05          0
                  5794                       24/11/2011 02:00:10  24/11/2011 03:00:14                60.06                 .04          0
                  5795                       24/11/2011 03:00:14  24/11/2011 04:00:17                60.05                 .03          0
                  5796                       24/11/2011 04:00:17  24/11/2011 05:00:21                60.06                 .03          0
                  5797                       24/11/2011 05:00:21  24/11/2011 06:00:24                60.06                 .03          0
                  5798                       24/11/2011 06:00:24  24/11/2011 07:00:28                60.06                 .03          0

                  5900                       28/11/2011 12:00:45  28/11/2011 13:00:48                60.06                 .10          0
                  5901                       28/11/2011 13:00:48  28/11/2011 14:00:52                60.06                 .09          0
                  5902                       28/11/2011 14:00:52  28/11/2011 15:00:55                60.06                 .08          0
                  5903                       28/11/2011 15:00:55  28/11/2011 16:00:59                60.07                 .06          0

207 rows selected.

Oracle Date manipulation

 

Oracle add hr  to date
Oracle add Min to date
Oracle add sec to date

1/24=one hour , 1/1440=one minute , 1/86400=one second


1/24/60/60   One second
7/24/60/60   Seven seconds

7/24    7 hours
14/24   14 hours


TRUNC(SYSDATE+1/24,'HH') = 1hr




In 1 day 24 hr



select 24*60 "In 24hr ->>Min " from dual;

In 24hr ->>Min
---------------
           1440



SQL> select 24*60*60 "In 24hr ->>second  " from dual;

In 24hr ->>second
-------------------
              86400



SQL> ALTER SESSION SET NLS_DATE_FORMAT='DD-MM-YYYY HH24:MI:SS';

Session altered.




SQL> SELECT   SYSDATE,SYSDATE+1/24 FROM DUAL;  ---- 1 hr add

SYSDATE             SYSDATE+1/24
------------------- -------------------
28-11-2011 14:09:47 28-11-2011 15:09:47


SQL> SELECT SYSDATE, SYSDATE+1/1440 FROM DUAL;  ---- add one min

SYSDATE             SYSDATE+1/1440
------------------- -------------------
28-11-2011 14:10:47 28-11-2011 14:11:47


SQL> SELECT SYSDATE , SYSDATE+1/86400 FROM DUAL;  ---- add one sec

SYSDATE             SYSDATE+1/86400
------------------- -------------------
28-11-2011 14:47:34 28-11-2011 14:47:35





http://anuj-singh.blogspot.com/2011/02/oracle-date-math-with-anuj.html

Oracle Memory Report

from this site
http://karlarao.wordpress.com/2010/01/31/workload-characterization-using-dba_hist-tables-and-ksar/

define instance_num="1"
define start_date = "2011_1126_0009"
define end_date = "2011_1127_1900"
define start_hour = "9"
define end_hour = "19"
define date_format = "YYYY_MMDD_HH24MI"
set pagesize 1000
col dur format 999990.00 heading "Snap|Dur|(m)"
col id format 99999 heading "Snap|ID"
col tm format a15 heading "Snap|Start|Time"
col inst format 90 heading "i|n|s|t|#"
col cpu format 90 heading "C|P|U"
col cap format 9999990.00 heading "***|Total|CPU|Time|(s)"
col dbt format 999999990.00 heading "DB|Time"
col dbc format 99990.00 heading "DB|CPU"
col bgc format 99990.00 heading "Bg|CPU"
col rman format 9990.00 heading "RMAN|CPU"
col aas format 90.0 heading "A|A|S"
col totora format 9999990.00 heading "***|Total|Oracle|CPU|(s)"
col busy format 9999990.00 heading "Busy|Time"
col load format 990.00 heading "OS|Load"
col totos format 9999990.00 heading "***|Total|OS|CPU|(s)"
col mem format 999990.00 heading "Physical|Memory|(mb)"
col iors format 9990.000 heading "IOPs|r"
col iows format 9990.000 heading "IOPs|w"
col ioredo format 9990.000 heading "IOPs|redo"
col iormbs format 9990.000 heading "IO r|(mb)/s"
col iowmbs format 9990.000 heading "IO w|(mb)/s"
col redosizesec format 999990.000 heading "Redo|(mb)/s"
col logons format 99990 heading "Sess"
col logone format 990 heading "Sess|End"
col exsraw format 99990.000 heading "Exec|raw|delta"
col exs format 999990.000 heading "Exec|/s"
col oracpupct format 990 heading "Oracle|CPU|%"
col rmancpupct format 990 heading "RMAN|CPU|%"
col oscpupct format 990 heading "OS|CPU|%"
col oscpuusr format 990 heading "U|S|R|%"
col oscpusys format 990 heading "S|Y|S|%"
col oscpuio format 990 heading "I|O|%"
WITH sub_snap
AS (SELECT dbid,
instance_number,
snap_id,
fin end_interval_time,
Round(Extract(DAY FROM fin - debut) * 1440 + Extract(HOUR FROM fin - debut) * 60 + Extract(MINUTE FROM fin - debut) + Extract(SECOND FROM fin - debut) / 60, 2) dur
FROM (SELECT dbid,
instance_number,
snap_id,
end_interval_time fin,
( Lag(end_interval_time, 1) over (PARTITION BY dbid, instance_number ORDER BY snap_id) ) debut
FROM dba_hist_snapshot
WHERE instance_number = &instance_num
AND begin_interval_time >= To_date('&start_date', '&date_format')
-- AND end_interval_time = 7
AND To_number(To_char(end_interval_time, 'HH24')) <= 19
AND To_number(To_char(begin_interval_time, 'D', 'NLS_DATE_LANGUAGE=AMERICAN')) < 6)),
sub_sys_time_model
AS (SELECT dbid,
snap_id,
instance_number,
SUM(db_time) db_time,
SUM(db_cpu) db_cpu,
SUM(bg_cpu) bg_cpu,
SUM(rm_cpu) rm_cpu
FROM (SELECT dbid,
snap_id,
instance_number,
CASE
WHEN stat_name = 'DB time' THEN CASE
WHEN begin_interval_time = startup_time THEN VALUE
ELSE VALUE - Lag(VALUE, 1) over(PARTITION BY stat_id, dbid, instance_number, startup_time ORDER BY snap_id)
END
ELSE NULL
END db_time,
CASE
WHEN stat_name = 'DB CPU' THEN CASE
WHEN begin_interval_time = startup_time THEN VALUE
ELSE VALUE - Lag(VALUE, 1) over(PARTITION BY stat_id, dbid, instance_number, startup_time ORDER BY snap_id)
END
ELSE NULL
END db_cpu,
CASE
WHEN stat_name = 'background cpu time' THEN CASE
WHEN begin_interval_time = startup_time THEN VALUE
ELSE VALUE - Lag(VALUE, 1) over(PARTITION BY stat_id, dbid, instance_number, startup_time ORDER BY snap_id)
END
ELSE NULL
END bg_cpu,
CASE
WHEN stat_name = 'RMAN cpu time (backup/restore)' THEN CASE
WHEN begin_interval_time = startup_time THEN VALUE
ELSE VALUE - Lag(VALUE, 1) over(PARTITION BY stat_id, dbid, instance_number, startup_time ORDER BY snap_id)
END
ELSE NULL
END rm_cpu
FROM dba_hist_snapshot
natural join dba_hist_sys_time_model
WHERE stat_name IN ( 'DB time', 'DB CPU', 'background cpu time', 'RMAN cpu time (backup/restore)' ))
GROUP BY dbid,
snap_id,
instance_number),
sub_sys_stat
AS (SELECT dbid,
snap_id,
instance_number,
MAX(logons_current) logons_current,
SUM(execute_count) execute_count,
SUM(physical_reads) physical_reads,
SUM(physical_writes) physical_writes,
SUM(redo_writes) redo_writes,
SUM(redo_size) redo_size,
SUM(physical_read_io_requests) physical_read_io_requests,
SUM(physical_write_io_requests) physical_write_io_requests
FROM (SELECT dbid,
snap_id,
instance_number,
CASE
WHEN stat_name = 'logons current' THEN VALUE
ELSE NULL
END logons_current,
CASE
WHEN stat_name = 'execute count' THEN CASE
WHEN begin_interval_time = startup_time THEN VALUE
ELSE VALUE - Lag(VALUE, 1) over(PARTITION BY stat_id, dbid, instance_number, startup_time ORDER BY snap_id)
END
ELSE NULL
END execute_count,
CASE
WHEN stat_name = 'physical reads' THEN CASE
WHEN begin_interval_time = startup_time THEN VALUE
ELSE VALUE - Lag(VALUE, 1) over(PARTITION BY stat_id, dbid, instance_number, startup_time ORDER BY snap_id)
END
ELSE NULL
END physical_reads,
CASE
WHEN stat_name = 'physical writes' THEN CASE
WHEN begin_interval_time = startup_time THEN VALUE
ELSE VALUE - Lag(VALUE, 1) over(PARTITION BY stat_id, dbid, instance_number, startup_time ORDER BY snap_id)
END
ELSE NULL
END physical_writes,
CASE
WHEN stat_name = 'redo writes' THEN CASE
WHEN begin_interval_time = startup_time THEN VALUE
ELSE VALUE - Lag(VALUE, 1) over(PARTITION BY stat_id, dbid, instance_number, startup_time ORDER BY snap_id)
END
ELSE NULL
END redo_writes,
CASE
WHEN stat_name = 'redo size' THEN CASE
WHEN begin_interval_time = startup_time THEN VALUE
ELSE VALUE - Lag(VALUE, 1) over(PARTITION BY stat_id, dbid, instance_number, startup_time ORDER BY snap_id)
END
ELSE NULL
END redo_size,
CASE
WHEN stat_name = 'physical read IO requests' THEN CASE
WHEN begin_interval_time = startup_time THEN VALUE
ELSE VALUE - Lag(VALUE, 1) over(PARTITION BY stat_id, dbid, instance_number, startup_time ORDER BY snap_id)
END
ELSE NULL
END physical_read_io_requests,
CASE
WHEN stat_name = 'physical write IO requests' THEN CASE
WHEN begin_interval_time = startup_time THEN VALUE
ELSE VALUE - Lag(VALUE, 1) over(PARTITION BY stat_id, dbid, instance_number, startup_time ORDER BY snap_id)
END
ELSE NULL
END physical_write_io_requests
FROM dba_hist_snapshot
natural join dba_hist_sysstat
WHERE stat_name IN ( 'logons current', 'execute count', 'physical reads', 'physical writes', 'redo writes', 'redo size', 'physical read IO requests', 'physical write IO requests' ))
GROUP BY dbid,
snap_id,
instance_number),
sub_osstat
AS (SELECT dbid,
snap_id,
instance_number,
MAX(load) load,
SUM(busy_time) busy_time,
SUM(user_time) user_time,
SUM(sys_time) sys_time,
SUM(io_wait_time) io_wait_time,
MAX(cpu) cpu,
MAX(phy_mem) phy_mem,
SUM(avg_sys_time),
SUM(avg_busy_time),
SUM(avg_iowait_time),
SUM(avg_idle_time),
SUM(avg_user_time)
FROM (SELECT e.snap_id,
e.dbid,
e.instance_number,
CASE
WHEN e.stat_name = 'LOAD' THEN e.VALUE
ELSE 0
END load,
CASE
WHEN e.stat_name = 'NUM_CPUS' THEN e.VALUE
ELSE 0
END cpu,
CASE
WHEN e.stat_name = 'AVG_BUSY_TIME' THEN CASE
WHEN s.begin_interval_time = s.startup_time THEN e.VALUE
ELSE e.VALUE - Lag(e.VALUE) over(PARTITION BY e.stat_name, e.dbid, e.instance_number, s.startup_time ORDER BY e.snap_id)
END
ELSE 0
END avg_busy_time,
CASE
WHEN e.stat_name = 'AVG_SYS_TIME' THEN CASE
WHEN s.begin_interval_time = s.startup_time THEN e.VALUE
ELSE e.VALUE - Lag(e.VALUE) over(PARTITION BY e.stat_name, e.dbid, e.instance_number, s.startup_time ORDER BY e.snap_id)
END
ELSE 0
END avg_sys_time,
CASE
WHEN e.stat_name = 'AVG_USER_TIME' THEN CASE
WHEN s.begin_interval_time = s.startup_time THEN e.VALUE
ELSE e.VALUE - Lag(e.VALUE) over(PARTITION BY e.stat_name, e.dbid, e.instance_number, s.startup_time ORDER BY e.snap_id)
END
ELSE 0
END avg_user_time,
CASE
WHEN e.stat_name = 'AVG_IOWAIT_TIME' THEN CASE
WHEN s.begin_interval_time = s.startup_time THEN e.VALUE
ELSE e.VALUE - Lag(e.VALUE) over(PARTITION BY e.stat_name, e.dbid, e.instance_number, s.startup_time ORDER BY e.snap_id)
END
ELSE 0
END avg_iowait_time,
CASE
WHEN e.stat_name = 'AVG_IDLE_TIME' THEN CASE
WHEN s.begin_interval_time = s.startup_time THEN e.VALUE
ELSE e.VALUE - Lag(e.VALUE) over(PARTITION BY e.stat_name, e.dbid, e.instance_number, s.startup_time ORDER BY e.snap_id)
END
ELSE 0
END avg_idle_time,
CASE
WHEN e.stat_name = 'BUSY_TIME' THEN CASE
WHEN s.begin_interval_time = s.startup_time THEN e.VALUE
ELSE e.VALUE - Lag(e.VALUE) over(PARTITION BY e.stat_name, e.dbid, e.instance_number, s.startup_time ORDER BY e.snap_id)
END
ELSE 0
END busy_time,
CASE
WHEN e.stat_name = 'USER_TIME' THEN CASE
WHEN s.begin_interval_time = s.startup_time THEN e.VALUE
ELSE e.VALUE - Lag(e.VALUE) over(PARTITION BY e.stat_name, e.dbid, e.instance_number, s.startup_time ORDER BY e.snap_id)
END
ELSE 0
END user_time,
CASE
WHEN e.stat_name = 'SYSR_TIME' THEN CASE
WHEN s.begin_interval_time = s.startup_time THEN e.VALUE
ELSE e.VALUE - Lag(e.VALUE) over(PARTITION BY e.stat_name, e.dbid, e.instance_number, s.startup_time ORDER BY e.snap_id)
END
ELSE 0
END sys_time,
CASE
WHEN e.stat_name = 'IOWAIT_TIME' THEN CASE
WHEN s.begin_interval_time = s.startup_time THEN e.VALUE
ELSE e.VALUE - Lag(e.VALUE) over(PARTITION BY e.stat_name, e.dbid, e.instance_number, s.startup_time ORDER BY e.snap_id)
END
ELSE 0
END io_wait_time,
CASE
WHEN e.stat_name = 'PHYSICAL_MEMORY_BYTES' THEN e.VALUE
ELSE 0
END phy_mem
FROM dba_hist_osstat e,
dba_hist_snapshot s
WHERE s.snap_id = e.snap_id
AND e.instance_number = s.instance_number
AND e.dbid = s.dbid
AND e.stat_name IN ( 'LOAD', 'AVG_BUSY_TIME', 'AVG_IOWAIT_TIME', 'AVG_USER_TIME',
'AVG_SYS_TIME', 'AVG_IDLE_TIME', 'NUM_CPUS', 'BUSY_TIME',
'PHYSICAL_MEMORY_BYTES', 'USER_TIME', 'SYS_TIME', 'IOWAIT_TIME' ))
GROUP BY dbid,
snap_id,
instance_number)
SELECT snap_id id,
To_char(end_interval_time, 'YYYYMMDD HH24MI') tm,
instance_number inst,
dur,
cpu,
( dur * cpu * 60 ) cap,
( db_time / 1000000 ) dbt,
( db_cpu / 1000000 ) dbc,
( bg_cpu / 1000000 ) bgc,
( rm_cpu / 1000000 ) rman,
( db_time / 1000000 / 60 ) / dur aas,
Round(bg_cpu / 1000000 + db_cpu / 1000000, 2) totora,
Round(load, 2) AS load,
( busy_time ) / 100 AS totos,
( phy_mem / 1024 / 1024 ) AS mem,
physical_reads / ( dur * 60 ) AS iors,
physical_writes / ( dur * 60 ) AS iows,
redo_writes / ( dur * 60 ) AS ioredo,
physical_read_io_requests / ( dur * 60 ) AS iormbs,
- physical_write_IO_requests/(dur*60) as IOWmbs,
- redo_size/(dur*60) as redosizesec,
- logons_current as logons,
- execute_count as exs,
( ( Round(( ( db_cpu ) / 1000000 ) + ( ( bg_cpu ) / 1000000 ), 2) ) / ( dur * 60 * cpu ) ) * 100 AS oracpupct,
( ( Round(Decode(rm_cpu, NULL, 'null',rm_cpu / 1000000), 2) ) / ( dur * 60 * cpu ) ) * 100 AS rmancpupct,
( ( ( busy_time ) / 100 ) / ( dur * 60 * cpu ) ) * 100 AS oscpupct,
( ( user_time / 100 ) / ( dur * 60 * cpu ) ) * 100 AS oscpuusr,
( ( ( sys_time ) / 100 ) / ( dur * 60 * cpu ) ) * 100 AS oscpusys,
( ( ( io_wait_time ) / 100 ) / ( dur * cpu * 60 ) ) * 100 AS oscpuio
FROM sub_snap
natural join sub_sys_time_model
natural join sub_sys_stat
natural join sub_osstat
ORDER BY id ASC
/

============

 

 @mem1
old  13: WHERE instance_number = &instance_num
new  13: WHERE instance_number = 1
old  14: AND begin_interval_time >= To_date('&start_date', '&date_format')
new  14: AND begin_interval_time >= To_date('2011_1126_0009', 'YYYY_MMDD_HH24MI')

                         i                        ***                                                          ***                 ***
                         n                      Total                                                        Total               Total
       Snap              s       Snap   C         CPU                                                A      Oracle                  OS   Physical
  Snap Start             t        Dur   P        Time            DB        DB        Bg     RMAN     A         CPU      OS         CPU     Memory      IOPs      IOPs      IOPs      IO r      IO w
    ID Time              #        (m)   U         (s)          Time       CPU       CPU      CPU     S         (s)    Load         (s)       (mb)         r         w      redo    (mb)/s    (mb)/s
------ --------------- --- ---------- --- ----------- ------------- --------- --------- -------- ----- ----------- ------- ----------- ---------- --------- --------- --------- --------- ---------
                                                   U    S
                               Oracle RMAN   OS    S    Y    I
       Redo               Exec    CPU  CPU  CPU    R    S    O
     (mb)/s   Sess          /s      %    %    %    %    %    %
----------- ------ ----------- ------ ---- ---- ---- ---- ----
  5864 20111127 0100     1              1                      2.13      1.99     20.20     0.00             22.19    0.00      276.09    2008.28
               -33  -17736.000

  5865 20111127 0200     1      60.03   1     3601.80          1.81      1.88     32.90     0.00   0.0       34.79    0.00      253.48    2008.28     0.017     0.213     0.042     0.016    -0.156
   -476.717    -31   -3444.000      1    0    7    5    0    1

  5866 20111127 0300     1      60.03   1     3601.80          1.89      2.08      5.75     0.00   0.0        7.82    0.11      251.37    2008.28     0.015     0.205     0.041     0.015    -0.150
   -405.729    -31   -3048.000      0    0    7    5    0    1

  5867 20111127 0400     1      60.03   1     3601.80          1.79      1.93      5.69     0.00   0.0        7.61    0.00      250.89    2008.28     0.002     0.200     0.039     0.002    -0.142
   -373.793    -31   -2878.000      0    0    7    5    0    1

  5868 20111127 0500     1      60.03   1     3601.80          1.65      1.84      5.61     0.00   0.0        7.44    0.00      251.16    2008.28     0.001     0.182     0.040     0.001    -0.134
   -382.797    -31   -2803.000      0    0    7    5    0    1

  5869 20111127 0600     1      60.03   1     3601.80        114.08     31.84      6.16     0.00   0.0       38.01    2.07      281.67    2008.28     1.533     0.575     0.095     1.299    -0.271
  -2229.629    -35  -45405.000      1    0    8    6    0    1

  5870 20111127 0700     1      60.03   1     3601.80       2922.71   2370.90      7.13     0.00   0.8     2378.03    0.32     1435.30    2008.28     6.731     1.596     0.176     2.565    -0.871
  -8896.886    -32  -40023.000     66    0   40   38    0    1

  5871 20111127 0800     1      60.03   1     3601.80          4.31      2.81      8.11     0.00   0.0       10.92    0.31      262.49    2008.28     0.187     0.385     0.044     0.178    -0.272
  -2397.647    -33   -7563.000      0    0    7    5    0    2

  5872 20111127 0900     1      60.04   1     3602.40          2.18      1.78      5.87     0.00   0.0        7.64    0.03      255.82    2008.28     0.023     0.231     0.041     0.023    -0.168
   -592.602    -33   -3721.000      0    0    7    5    0    2

  5873 20111127 1000     1      60.03   1     3601.80         40.03     36.49      6.03     0.00   0.0       42.53    0.05      278.79    2008.28     0.033     0.201     0.041     0.032    -0.152
   -532.957    -33   -4260.000      1    0    8    6    0    1

  5874 20111127 1100     1      60.03   1     3601.80        197.02     37.29      6.80     0.00   0.1       44.09    0.00      262.95    2008.28     2.206     0.676     0.085     1.624    -0.439
  -3947.046    -33  -47552.000      1    0    7    5    0    2

  5875 20111127 1200     1      60.03   1     3601.80          1.61      1.53      5.90     0.00   0.0        7.42    0.06      247.03    2008.28     0.023     0.234     0.041     0.022    -0.174
   -568.728    -33   -3356.000      0    0    7    5    0    1

  5876 20111127 1300     1      60.03   1     3601.80          1.72      1.50      6.07     0.00   0.0        7.56    0.15      246.49    2008.28     0.630     0.203     0.041     0.630    -0.149
   -438.576    -32   -3237.000      0    0    7    5    0    1

  5877 20111127 1400     1      60.03   1     3601.80          1.64      1.54      5.81     0.00   0.0        7.35    0.09      243.98    2008.28     0.020     0.193     0.040     0.020    -0.144
   -411.757    -30   -2893.000      0    0    7    5    0    1

  5878 20111127 1500     1      60.03   1     3601.80        134.56     50.66      6.04     0.00   0.0       56.71    0.35      271.03    2008.28     1.312     0.376     0.063     1.217    -0.265
  -1219.445    -30  -39622.000      2    0    8    6    0    2

  5879 20111127 1600     1      60.03   1     3601.80          1.62      1.52      5.81     0.00   0.0        7.33    0.12      246.28    2008.28     0.009     0.205     0.041     0.009    -0.156
   -404.427    -30   -3032.000      0    0    7    5    0    1

  5880 20111127 1700     1      60.03   1     3601.80          1.57      1.52      5.93     0.00   0.0        7.44    0.21      246.90    2008.28     0.009     0.189     0.040     0.009    -0.139
   -429.958    -30   -2939.000      0    0    7    5    0    1

  5881 20111127 1800     1      60.03   1     3601.80          1.60      1.51      5.71     0.00   0.0        7.22    0.21      249.48    2008.28     0.005     0.194     0.040     0.005    -0.142
   -396.104    -30   -2872.000      0    0    7    5    0    1

  5882 20111127 1900     1      59.03   1     3541.80         40.16     24.87      6.24     0.00   0.0       31.11    0.18      261.76    2008.28     1.195     0.385     0.070     1.190    -0.262
  -1435.691    -33  -40145.000      1    0    7    5    0    2

  5887 20111128 0000     1     300.15   1    18009.00          1.44      1.48      6.01     0.00   0.0        7.50    0.01      246.41    2008.28     0.003     0.039     0.008     0.002    -0.030
    -82.805    -32   -3106.000      0    0    1    1    0    0

  5888 20111128 0100     1      60.04   1     3602.40          3.31      1.63     20.80     0.00   0.0       22.43    0.09      263.24    2008.28     3.520     2.446     0.151     2.175    -1.980
 -15881.552    -33  -17739.000      1    0    7    5    0    4

  5889 20111128 0200     1      60.03   1     3601.80          1.47      1.46     32.32     0.00   0.0       33.78    0.28      247.52    2008.28     0.024     0.222     0.042     0.023    -0.162
   -467.332    -31   -3594.000      1    0    7    5    0    1

  5890 20111128 0300     1      60.03   1     3601.80          1.49      1.56      5.68     0.00   0.0        7.25    0.07      242.56    2008.28     0.007     0.206     0.040     0.007    -0.148
   -403.055    -31   -2946.000      0    0    7    5    0    1

  5891 20111128 0400     1      60.03   1     3601.80          1.35      1.46      5.56     0.00   0.0        7.02    0.00      250.56    2008.28     0.003     0.195     0.039     0.003    -0.139
   -393.477    -31   -2820.000      0    0    7    5    0    1

  5892 20111128 0500     1      60.03   1     3601.80          1.50      1.49      5.64     0.00   0.0        7.14    0.02      251.27    2008.28     0.002     0.189     0.041     0.002    -0.140
   -377.975    -31   -2801.000      0    0    7    5    0    1

  5893 20111128 0600     1      60.03   1     3601.80          1.40      1.46      5.55     0.00   0.0        7.02    0.13      242.30    2008.28     0.002     0.186     0.039     0.002    -0.138
   -410.680    -31   -2802.000      0    0    7    5    0    1

  5894 20111128 0700     1      60.03   1     3601.80          1.50      1.52      5.54     0.00   0.0        7.06    0.27      242.49    2008.28     0.002     0.191     0.041     0.002    -0.148
   -394.106    -31   -2851.000      0    0    7    5    0    1

  5895 20111128 0800     1      60.04   1     3602.40          1.56      1.58      5.70     0.00   0.0        7.28    0.14      242.87    2008.28     0.005     0.185     0.041     0.005    -0.141
   -406.256    -32   -2813.000      0    0    7    5    0    1

  5896 20111128 0900     1      60.03   1     3601.80          1.36      1.39      5.56     0.00   0.0        6.95    0.05      242.45    2008.28     0.002     0.184     0.039     0.002    -0.136
   -409.270    -31   -2783.000      0    0    7    5    0    1

  5897 20111128 1000     1      60.03   1     3601.80          2.34      1.51      5.76     0.00   0.0        7.27    0.30      332.63    2008.28     0.003     0.188     0.039     0.003    -0.146
   -391.966    -31   -2797.000      0    0    9    7    0    3

  5898 20111128 1100     1      60.13   1     3607.80        107.21      9.39      6.31     0.00   0.0       15.70    0.30     1137.09    2008.28     1.731     0.196     0.053     1.730    -0.151
   -556.871    -31   -3629.000      0    0   32   27    0   10


31 rows selected.

 

 

 

 

Oracle Primary Key - Foreign Key Report

---

 

 

Primary Key - Foreign Key Report
pk fk Report On user




UNDEF ENTER_OWNER_NAME
COLUMN A_OWNER            FORMAT A15      HEADING 'Primary|Table|Owner'
COLUMN B_OWNER            FORMAT A15      HEADING 'For|Table|Owner'
COLUMN TABLE_NAME         FORMAT A15      HEADING 'Table Name'
COLUMN COLUMN_NAME        FORMAT A15      HEADING 'Column Name'
REM
SET LINES 132 pages 66 feedback off verify off
TTITLE "Primary Key - Foreign Key Report"
REM
WITH reference_view AS
     (SELECT a.owner, a.table_name, a.constraint_name, a.constraint_type,
             a.r_owner, a.r_constraint_name, b.column_name
        FROM dba_constraints a, dba_cons_columns b
       WHERE a.owner LIKE UPPER ('&&ENTER_OWNER_NAME')
         AND a.owner = b.owner
         AND a.constraint_name = b.constraint_name
         AND constraint_type = 'R'),
     constraint_view AS
     (SELECT a.owner a_owner, a.table_name, a.column_name, b.owner b_owner,
             b.constraint_name
        FROM dba_cons_columns a, dba_constraints b
       WHERE a.owner = b.owner
         AND a.constraint_name = b.constraint_name
         AND b.constraint_type = 'P'
         AND a.owner LIKE UPPER ('&&ENTER_OWNER_NAME'))
SELECT CV.a_owner a_owner, CV.table_name, rv.column_name, rv.owner b_owner,
       rv.table_name, rv.column_name
  FROM reference_view rv, constraint_view CV
 WHERE rv.r_constraint_name = CV.constraint_name AND rv.r_owner = CV.b_owner;




Primary Key - Foreign Key Report

Primary                                         For
Table                                           Table
Owner           Table Name      Column Name     Owner           Table Name      Column Name
--------------- --------------- --------------- --------------- --------------- ---------------
SCOTT           ORDERS          ORDER_ID        SCOTT           ORDER_LINES     ORDER_ID

 

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.