#!/bin/ksh

#
# File.........: queries.sh
#
# Author.......: Gary Bradshaw
#
# Date.........: Wed Jul  7 20:26:57 CDT 1999
#
# Description..: This is a self extracting shell script that extracts
#                the query examples contained in this file into individual 
#                files.  These are the examples from the book "Oracle 
#                Performance Tuning Tips & Techniques" by Rich Niemiec.
#                The naming convention of the files this script produces
#                is ${PREFIX}<Chapter#>.<Example#>.${SUFFIX} where you can 
#                change the PREFIX and/or SUFFIX to whatever you wish.
#
# Directions...: 1. Create a directory that will store these files.  
#                2. Change to the directory you just created.
#                3. Copy this script to that directory.
#                4. Make sure that this file has read and execute
#                   permissions (i.e., chmod 555 queries.sh).
#                5. Execute this script.
#
# Usage........: queries.sh
#

PREFIX=ch
SUFFIX=.sql

#-----------#
# Chapter 1 #
#-----------#

printf "Extracting ${PREFIX}01.01${SUFFIX}..."

sed s/^#//g > ${PREFIX}01.01${SUFFIX} << EOF 
#select 
#   name, 
#   value
#from   
#   v\$parameter
#where  
#   name in ('db_block_buffers', 
#            'db_block_size',
#            'shared_pool_size', 
#            'sort_area_size');
EOF

printf "done.\n"
printf "Extracting ${PREFIX}01.02${SUFFIX}..."

sed s/^#//g > ${PREFIX}01.02${SUFFIX} << EOF 
#select 
#  (1 - (sum(decode(name,'physical reads', value, 0)) / 
#       (sum(decode(name,'db block gets', value, 0)) + 
#        sum(decode(name,'consistent gets',value,0))))) * 100 "Hit Ratio"
#from   
#   v\$sysstat;
EOF

printf "done.\n"
printf "Extracting ${PREFIX}01.03${SUFFIX}..."

sed s/^#//g > ${PREFIX}01.03${SUFFIX} << EOF 
#select 
#  (1 - (sum(getmisses) / sum(gets))) * 100 "Hit Ratio"
#from   
#   v\$rowcache;
#
#select 
#   sum(pins) / (sum(pins) + sum(reloads)) * 100 "Hit Ratio"
#from   
#   v\$librarycache;
EOF

printf "done.\n"
printf "Extracting ${PREFIX}01.04${SUFFIX}..."

sed s/^#//g > ${PREFIX}01.04${SUFFIX} << EOF
#col bytes for 999,999,999,999 heading "Free Bytes"
#
#select 
#   to_number(v\$parameter.value) value, 
#   v\$sgastat.bytes,
#   (v\$sgastat.bytes / to_number(v\$parameter.value)) * 100 "Percent Free"
#from   
#   v\$sgastat, 
#   v\$parameter
#where  
#   v\$sgastat.name = 'free memory' and 
#   v\$parameter.name = 'shared_pool_size';
EOF

printf "done.\n"
printf "Extracting ${PREFIX}01.05${SUFFIX}..."

sed s/^#//g > ${PREFIX}01.05${SUFFIX} << EOF
#select 
#   sum(ksmchsiz) Bytes, 
#   ksmchcls Status
#from   
#   x\$ksmsp
#group by 
#   ksmchcls;
EOF

printf "done.\n"
printf "Extracting ${PREFIX}01.06${SUFFIX}..."

sed s/^#//g > ${PREFIX}01.06${SUFFIX} << EOF
#select 
#   a.value "Disk Sorts", 
#   b.value "Memory Sorts",
#   round(100 * (b.value/decode((a.value + b.value),
#      0,1,(a.value+b.value))),2) "Pct Memory Sorts"
#from   
#   v\$sysstat a, 
#   v\$sysstat b
#where  
#   a.name = 'sorts (disk)' and
#   b.name = 'sorts (memory)';
EOF

printf "done.\n"
printf "Extracting ${PREFIX}01.07${SUFFIX}..."

sed s/^#//g > ${PREFIX}01.07${SUFFIX} << EOF
#alter session set sort_area_size=100000000;
#
#select 
#   state, 
#   count(*)
#from   
#   x\$bh
#group by 
#   state;
EOF
 
printf "done.\n"
printf "Extracting ${PREFIX}01.08${SUFFIX}..."

sed s/^#//g > ${PREFIX}01.08${SUFFIX} << EOF
#select 
#   decode(state, 0,'FREE',
#                 1, decode(lrba_seq,0,'AVAILABLE','BEING USED'),
#                 3,'BEING USED', state) "BLOCK STATUS",
#   count(*)
#from   
#   x\$bh
#group by 
#   decode(state, 0,'FREE',
#                 1, decode(lrba_seq,0,'AVAILABLE', 'BEING USED'),
#                 3,'BEING USED', state);
EOF

printf "done.\n"
printf "Extracting ${PREFIX}01.09${SUFFIX}..."

sed s/^#//g > ${PREFIX}01.09${SUFFIX} << EOF
#col PHYRDS format 999,999,999
#col PHYWRTS format 999,999,999
#ttitle "Disk Balancing Report"
#col READTIM format 999,999,999
#col WRITETIM format 999,999,999
#col name format a40
#spool fio1.out
#
#select 
#   name, 
#   phyrds, 
#   phywrts, 
#   readtim, 
#   writetim
#from   
#   v\$filestat a, 
#   v\$dbfile b
#where  
#   a.file# = b.file#
#order by 
#   readtim desc
#/
#spool off
EOF

printf "done.\n"
printf "Extracting ${PREFIX}01.10${SUFFIX}..."

sed s/^#//g > ${PREFIX}01.10${SUFFIX} << EOF
#select 
#   disk_reads, 
#   sql_text
#from   
#   v\$sqlarea
#where  
#   disk_reads > 10000
#order by 
#   disk_reads desc;
EOF

printf "done.\n"
printf "Extracting ${PREFIX}01.11${SUFFIX}..."

sed s/^#//g > ${PREFIX}01.11${SUFFIX} << EOF
#select 
#   buffer_gets, 
#   sql_text
#from   
#   v\$sqlarea
#where  
#   buffer_gets > 200000
#order by 
#   buffer_gets desc;
EOF

printf "done.\n"
printf "Extracting ${PREFIX}01.12${SUFFIX}..."

sed s/^#//g > ${PREFIX}01.12${SUFFIX} << EOF
#break on User_Name on Disk_Reads on Buffer_Gets on Rows_Processed
#
#select 
#   a.user_name, 
#   b.disk_reads, 
#   b.buffer_gets,
#   b.rows_processed, 
#   c.sql_text
#from   
#   v\$open_cursor a, 
#   v\$sqlarea b, 
#   v\$sqltext c
#where  
#   a.user_name = upper('&&User') and
#   a.address = c.address and
#   a.address = b.address
#order by 
#   a.user_name, 
#   a.address, 
#   c.piece;
EOF

#-----------#
# Chapter 2 #
#-----------#

printf "done.\n"
printf "Extracting ${PREFIX}02.01${SUFFIX}..."

sed s/^#//g > ${PREFIX}02.01${SUFFIX} << EOF
#select 
#   table_name, 
#   index_name, 
#   column_name, 
#   column_position
#from   
#   dba_ind_columns
#order by 
#   table_name, 
#   index_name, 
#   column_position;
EOF

#-----------#
# Chapter 3 #
#-----------#

printf "done.\n"
printf "Extracting ${PREFIX}03.01${SUFFIX}..."

sed s/^#//g > ${PREFIX}03.01${SUFFIX} << EOF
#create table dept
#   (deptno         number(2),
#    dept_name      varchar2(30))
#       partition by range(deptno)
#      (partition d1 values less than (10) tablespace dept1,
#       partition d2 values less than (20) tablespace dept2,
#       partition d3 values less than (maxvalue) tablespace dept3);
#
#insert into dept values ( 1, 'ADMIN');
#insert into dept values ( 7, 'MGMT');
#insert into dept values (10, 'MANUF');
#insert into dept values (15, 'ACCT');
#insert into dept values (22, 'SALES');
#
#select * 
#from   dept;
#
#select * 
#from   dept partition (d1);
#
#select * 
#from   dept partition (d2);
#
#select * 
#from   dept partition (d3);
#
#select 
#   table_name, 
#   partitioned 
#from   
#   dba_tables
#where  
#   table_name in ('DEPT','EMP');
#
#select 
#   owner, 
#   table_name, 
#   partition_count 
#from   
#   dba_part_tables
#where  
#   table_name = 'DEPT';
EOF
 
printf "done.\n"
printf "Extracting ${PREFIX}03.02${SUFFIX}..."

sed s/^#//g > ${PREFIX}03.02${SUFFIX} << EOF
#select 
#   segment_name, 
#   partition_name, 
#   segment_type, 
#   tablespace_name
#from   
#   user_segments;
EOF

printf "done.\n"
printf "Extracting ${PREFIX}03.03${SUFFIX}..."

sed s/^#//g > ${PREFIX}03.03${SUFFIX} << EOF
#select 
#   segment_name, 
#   segment_type, 
#   extents, 
#   bytes
#from   
#   dba_segments
#where  
#   extents > 5;
EOF

printf "done.\n"
printf "Extracting ${PREFIX}03.04${SUFFIX}..."

sed s/^#//g > ${PREFIX}03.04${SUFFIX} << EOF
#select 
#   a.name, 
#   b.extents, 
#   b.rssize, 
#   b.xacts, 
#   b.waits, 
#   b.gets,
#   optsize, 
#   status
#from   
#   v\$rollname a, v\$rollstat b
#where  
#   a.usn = b.usn;
EOF

printf "done.\n"
printf "Extracting ${PREFIX}03.05${SUFFIX}..."

sed s/^#//g > ${PREFIX}03.05${SUFFIX} << EOF
#select 
#   a.name, 
#   b.xacts, 
#   c.sid, 
#   c.serial#, 
#   c.username, 
#   d.sql_text
#from   
#   v\$rollname a, 
#   v\$rollstat b, 
#   v\$session c, 
#   v\$sqltext d,
#   v\$transaction e
#where  
#   a.usn = b.usn and
#   b.usn = e.xidusn and
#   c.taddr = e.addr and
#   c.sql_address = d.address and
#   c.sql_hash_value = d.hash_value
#order by 
#   a.name, 
#   c.sid, 
#   d.piece;
EOF

printf "done.\n"
printf "Extracting ${PREFIX}03.06${SUFFIX}..."

sed s/^#//g > ${PREFIX}03.06${SUFFIX} << EOF
#select 
#   name, 
#   value
#from   
#   v\$parameter
#where  
#   name = 'control_files';
EOF

#-----------#
# Chapter 4 #
#-----------#

printf "done.\n"
printf "Extracting ${PREFIX}04.01${SUFFIX}..."

sed s/^#//g > ${PREFIX}04.01${SUFFIX} << EOF
#select 
#   name, 
#   value, 
#   isdefault, 
#   isses_modifiable, 
#   issys_modifiable 
#from   
#   v\$parameter
#where  
#   issys_modifiable <> 'FALSE' or
#   isses_modifiable <> 'FALSE'
#order by 
#   name;
EOF

printf "done.\n"
printf "Extracting ${PREFIX}04.02${SUFFIX}..."

sed s/^#//g > ${PREFIX}04.02${SUFFIX} << EOF
#select 
#   name, 
#   value
#from   
#   v\$sysstat
#where  
#   name in ('db block gets', 
#            'consistent gets',
#            'physical reads');
EOF

printf "done.\n"
printf "Extracting ${PREFIX}04.03${SUFFIX}..."

sed s/^#//g > ${PREFIX}04.03${SUFFIX} << EOF
#column phys format 999,999,999 heading 'Physical Reads'
#column gets format 999,999,999 heading ' DB Block Gets' 
#column con_gets format 999,999,999 heading 'Consistent Gets'
#column hitratio format 9.999 heading ' Hit Ratio '
#
#select 
#   sum(decode(name,'physical reads',value,0))phys, 
#   sum(decode(name,'db block gets',value,0)) gets,
#   sum(decode(name,'consistent gets', value,0)) con_gets,
#  (1 - (sum(decode(name,'physical reads',value,0)) / 
#       (sum(decode(name,'db block gets',value,0)) + 
#        sum(decode(name,'consistent gets',value,0))))) hitratio
#from   
#   v\$sysstat;
EOF 

printf "done.\n"
printf "Extracting ${PREFIX}04.04${SUFFIX}..."

sed s/^#//g > ${PREFIX}04.04${SUFFIX} << EOF
#select 
#   disk_reads, 
#   sql_text
#from   
#   v\$sqlarea
#where  
#   disk_reads > 10000
#order by 
#   disk_reads desc;
#
#select 
#   buffer_gets, 
#   sql_text
#from   
#   v\$sqlarea
#where  
#   buffer_gets > 200000
#order by 
#   buffer_gets desc;
EOF

printf "done.\n"
printf "Extracting ${PREFIX}04.05${SUFFIX}..."

sed s/^#//g > ${PREFIX}04.05${SUFFIX} << EOF
#select 
#   s.sid, 
#   s.username, 
#   s.status, 
#   disk_reads, 
#   buffer_gets, 
#   executions, 
#   loads, 
#   sql_text 
#from   
#   v\$sqlarea q, 
#   v\$session s 
#where  
#   q.address = s.sql_address and
#   s.username = UPPER('&1') 
#order by 
#   sid;
EOF

printf "done.\n"
printf "Extracting ${PREFIX}04.06${SUFFIX}..."

sed s/^#//g > ${PREFIX}04.06${SUFFIX} << EOF
#Break on User_Name On Disk_Reads on Buffer_Gets on Rows_Processed
#
#select 
#   a.user_name, 
#   b.disk_reads, 
#   b.buffer_gets,
#   b.rows_processed, 
#   c.sql_text
#from   
#   v\$open_cursor a, 
#   v\$sqlarea b, 
#   v\$sqltext c
#where  
#   a.user_name = upper('&&User') and
#   a.address = c.address and
#   a.address = b.address
#order by 
#   a.user_name, 
#   a.address, 
#   c.piece;
EOF

printf "done.\n"
printf "Extracting ${PREFIX}04.07${SUFFIX}..."

sed s/^#//g > ${PREFIX}04.07${SUFFIX} << EOF
#select 
#   sum(count) Hits
#from   
#   sys.x\$kcbrbh
#where  
#   indx < 1000;
EOF

printf "done.\n"
printf "Extracting ${PREFIX}04.08${SUFFIX}..."

sed s/^#//g > ${PREFIX}04.08${SUFFIX} << EOF
#select 
#   ((1 - (sum(getmisses) / (sum(gets) + sum(getmisses)))) * 100) "Hit Rate"
#from   
#   v\$rowcache
#where  
#   gets + getmisses <> 0;
EOF

printf "done.\n"
printf "Extracting ${PREFIX}04.09${SUFFIX}..."

sed s/^#//g > ${PREFIX}04.09${SUFFIX} << EOF
#column parameter format a20 heading 'Data Dictionary Area'
#column gets format 999,999,999 heading 'Total|Requests'
#column getmisses format 999,999,999 heading 'Misses' 
#column modifications format 999,999 heading 'Mods'
#column flushes format 999,999 heading 'Flushes'
#column getmiss_ratio format 9.99 heading 'Miss|Ratio'
#ttitle 'Shared Pool Row Cache Usage'
#
#select 
#   parameter, 
#   gets, 
#   getmisses, 
#   modifications, 
#   flushes,
#  (getmisses / decode(gets,0,1,gets)) getmiss_ratio,
#   decode(trunc((getmisses / decode(gets,0,1,gets)),1),.0,' ','*') " "
#from   
#   v\$rowcache
#where  
#   gets + getmisses <> 0
#order by 
#   gets desc; 
EOF

printf "done.\n"
printf "Extracting ${PREFIX}04.10${SUFFIX}..."

sed s/^#//g > ${PREFIX}04.10${SUFFIX} << EOF
#select 
#   sum(pins) "Hits",
#   sum(reloads) "Misses",
# ((sum(reloads) / sum(pins)) * 100)"Reload %"
#from   
#   v\$librarycache;
#
#select 
#   sum(pins) "Hits",
#   sum(reloads) "Misses",
#   sum(pins) / (sum(pins) + sum(reloads)) "Hit Ratio"
#from   
#   v\$librarycache;
EOF

printf "done.\n"
printf "Extracting ${PREFIX}04.11${SUFFIX}..."

sed s/^#//g > ${PREFIX}04.11${SUFFIX} << EOF
#set numwidth 3
#set space 2
#set newpage 0
#set pagesize 58
#set linesize 80
#set tab off
#set echo off
#ttitle 'Shared Pool Library Cache Usage'
#column namespace format a20 heading ' '
#column pins format 999,999,999 heading 'Executions'
#column pinhits format 999,999,999 heading 'Hits' 
#column pinhitratio format 9.99 heading 'Hit|Ratio'
#column reloads format 999,999 heading 'Reloads'
#column reloadratio format .9999 heading 'Reload|Ratio'
#spool cache_lib.lis
#
#select 
#   namespace, 
#   pins, 
#   pinhits, 
#   pinhitratio, 
#   reloads, 
#   reloads / decode(pins,0,1,pins) reloadratio
#from   
#   v\$librarycache;
EOF

printf "done.\n"
printf "Extracting ${PREFIX}04.12${SUFFIX}..."

sed s/^#//g > ${PREFIX}04.12${SUFFIX} << EOF
#col bytes for 999,999,999,999 heading "Free Bytes"
#
#select 
#   to_number(v\$parameter.value) value, 
#   v\$sgastat.bytes,
#  (v\$sgastat.bytes / to_number(v\$parameter.value)) * 100 "Percent Free"
#from   
#   v\$sgastat, 
#   v\$parameter
#where  
#   v\$sgastat.name = 'free memory' and
#   v\$parameter.name = 'shared_pool_size';
EOF

printf "done.\n"
printf "Extracting ${PREFIX}04.13${SUFFIX}..."

sed s/^#//g > ${PREFIX}04.13${SUFFIX} << EOF
#select 
#   sum(ksmchsiz) Bytes, 
#   ksmchcls Status
#from   
#   x\$ksmsp
#group by 
#   ksmchcls;
EOF

printf "done.\n"
printf "Extracting ${PREFIX}04.14${SUFFIX}..."

sed s/^#//g > ${PREFIX}04.14${SUFFIX} << EOF
#select 
#   physical_reads "Disk Reads", 
#   block_gets + consistent_gets "Memory Reads"
#from   
#   v\$buffer_pool
#where  
#   name = 'KEEP';
EOF

printf "done.\n"
printf "Extracting ${PREFIX}04.15${SUFFIX}..."

sed s/^#//g > ${PREFIX}04.15${SUFFIX} << EOF
#select 
#   name, 
#   value, 
#   isdefault, 
#   isses_modifiable, 
#   issys_modifiable 
#from   
#   v\$parameter
#where  
#   name like '%pool%';
EOF

printf "done.\n"
printf "Extracting ${PREFIX}04.16${SUFFIX}..."

sed s/^#//g > ${PREFIX}04.16${SUFFIX} << EOF
#rem This is the showuser.sql query. It does a display of oracle processes 
#spool showuser.out
#
#select 
#   substr(a.username,1,10) USERNAME, 
#   substr(a.osuser,1,8) OSUSER, 
#   substr(b.spid,1,6) SRVPID, 
#   substr(to_char(a.sid),1,3) ID, 
#   substr(a.machine,1,8) HOST, 
#   substr(a.program,1,25) PROGRAM 
#from   
#   v\$process b, 
#   v\$session a 
#where  
#   a.paddr = b.addr 
#order by 
#   a.username; 
#
#spool off
EOF

printf "done.\n"
printf "Extracting ${PREFIX}04.17${SUFFIX}..."

sed s/^#//g > ${PREFIX}04.17${SUFFIX} << EOF
#rem This is the showother.sql query and shows all processes with no session
#spool showuser.out
#
#select 
#   substr(spid,1,6) SRVPID, 
#   username, 
#   program 
#from   
#   v\$process 
#where  
#   BACKGROUND <> 1 and 
#   ADDR not in (select paddr from v\$session); 
#
#spool off
EOF

printf "done.\n"
printf "Extracting ${PREFIX}04.18${SUFFIX}..."

sed s/^#//g > ${PREFIX}04.18${SUFFIX} << EOF
#select 
#   s.sid, 
#   s.username, 
#   s.status, 
#   disk_reads, 
#   buffer_gets,
#   executions, 
#   loads, 
#   sql_text
#from   
#   v\$sqlarea q, 
#   v\$session s 
#where  
#   q.address = s.sql_address and
#   s.username = 'CO9962' 
#order by 
#   sid;
EOF

printf "done.\n"
printf "Extracting ${PREFIX}04.19${SUFFIX}..."

sed s/^#//g > ${PREFIX}04.19${SUFFIX} << EOF
#select 
#   a.sid, 
#   a.username, 
#   b.value
#from   
#   v\$session a, 
#   v\$sesstat b, 
#   v\$statname c
#where  
#   a.sid = b.sid and
#   b.statistic# = c.statistic# and
#   c.name = 'session memory';
EOF

printf "done.\n"
printf "Extracting ${PREFIX}04.20${SUFFIX}..."

sed s/^#//g > ${PREFIX}04.20${SUFFIX} << EOF
#select 
#   a.sid, 
#   a.username, 
#   b.sql_text
#from   
#   v\$session a, 
#   v\$sqltext b
#where  
#   a.sql_address = b.address and
#   a.sql_hash_value = b.hash_value
#order by 
#   a.sid, 
#   a.username, 
#   b.piece;
EOF

printf "done.\n"
printf "Extracting ${PREFIX}04.21${SUFFIX}..."

sed s/^#//g > ${PREFIX}04.21${SUFFIX} << EOF
#select 
#   a.sid, 
#   a.username, 
#   b.sql_text
#from   
#   v\$session a, 
#   v\$open_cursor b
#where  
#   a.saddr = b.saddr;
EOF

printf "done.\n"
printf "Extracting ${PREFIX}04.22${SUFFIX}..."

sed s/^#//g > ${PREFIX}04.22${SUFFIX} << EOF
#select 
#   username, 
#   count(*) 
#from   
#   v\$session
#group by 
#   username;
EOF

printf "done.\n"
printf "Extracting ${PREFIX}04.23${SUFFIX}..."

sed s/^#//g > ${PREFIX}04.23${SUFFIX} << EOF
#select 
#   sid, 
#   username, 
#   program, 
#   osuser, 
#   process, 
#   machine, 
#   terminal, 
#   type
#from   
#   v\$session;
EOF

printf "done.\n"
printf "Extracting ${PREFIX}04.24${SUFFIX}..."

sed s/^#//g > ${PREFIX}04.24${SUFFIX} << EOF
#select 
#   a.sid, 
#   a.username, 
#   b.name, 
#   c.value
#from   
#   v\$session a, 
#   v\$statname b, 
#   v\$sesstat c
#where  
#   a.sid = c.sid and
#   b.statistic# = c.statistic# and
#   a.username = upper('&username')
#order by 
#   a.sid, 
#   a.username, 
#   b.name;
EOF

printf "done.\n"
printf "Extracting ${PREFIX}04.25${SUFFIX}..."

sed s/^#//g > ${PREFIX}04.25${SUFFIX} << EOF
#select 
#   a.sid, 
#   a.user_name, 
#   s.sql_text
#from   
#   v\$session a, 
#   v\$sqltext s
#where  
#   a.sql_address = s.address and
#   a.sql_hash_value = s.hash_value
#order by 
#   a.user_name, 
#   a.sid, 
#   s.pieces;
EOF

printf "done.\n"
printf "Extracting ${PREFIX}04.26${SUFFIX}..."

sed s/^#//g > ${PREFIX}04.26${SUFFIX} << EOF
#select 
#   class, 
#   count, 
#   time
#from   
#   v\$waitstat;
#
#select 
#   sid, 
#   event, 
#   wait_time, 
#   state
#from   
#   v\$session_wait;
#
#select 
#   event, 
#   total_waits, 
#   time_waited, 
#   average_wait
#from   
#   v\$system_event;
EOF

printf "done.\n"
printf "Extracting ${PREFIX}04.27${SUFFIX}..."

sed s/^#//g > ${PREFIX}04.27${SUFFIX} << EOF
#select 
#   name "Latch",
#   sum(gets) "WTW Gets",
#   sum(misses) "WTW Misses",
#   sum(immediate_gets) "IMM Gets",
#   sum(immediate_misses) "IMM Misses"
#from   
#   v\$latch
#where  
#   name like 'redo%'
#group by 
#   name;
EOF

printf "done.\n"
printf "Extracting ${PREFIX}04.28${SUFFIX}..."

sed s/^#//g > ${PREFIX}04.28${SUFFIX} << EOF
#select 
#   network,
# ((sum(busy) / (sum(busy) + sum(idle))) * 100) "% Busy Rate"
#from   
#   v\$dispatcher
#group by 
#   network;
EOF

printf "done.\n"
printf "Extracting ${PREFIX}04.29${SUFFIX}..."

sed s/^#//g > ${PREFIX}04.29${SUFFIX} << EOF
#select 
#   network Protocol,
#   decode (sum(totalq), 0, 'No Responses',
#           sum(wait) / sum(totalq) || 'hundredths of a second')
#          "Average Wait Time Per Response"
#from   
#   v\$queue q, 
#   v\$dispatcher d
#where  
#   q.type = 'DISPATCHER' and
#   q.paddr = d.paddr
#group by 
#   network;
EOF

printf "done.\n"
printf "Extracting ${PREFIX}04.30${SUFFIX}..."

sed s/^#//g > ${PREFIX}04.30${SUFFIX} << EOF
#select 
#   decode (sum(totalq), 0, 'Number of Requests',
#           sum(wait) / sum(totalq) || 'hundredths of a second')
#          "Average Wait Time Per Request"
#from   
#   v\$queue
#where  
#   type = 'COMMON';
EOF

printf "done.\n"
printf "Extracting ${PREFIX}04.31${SUFFIX}..."

sed s/^#//g > ${PREFIX}04.31${SUFFIX} << EOF
#select 
#   ksppinm, 
#   ksppivl,
#   ksppidf
#from   
#   x\$ksppi;
EOF

#-----------#
# Chapter 5 #
#-----------#

#*** No scripts for chapter 5 ***#

#-----------#
# Chapter 6 #
#-----------#

printf "done.\n"
printf "Extracting ${PREFIX}06.01${SUFFIX}..."

sed s/^#//g > ${PREFIX}06.01${SUFFIX} << EOF
#select 
#   operation, 
#   options, 
#   object_name, 
#   id, 
#   parent_id, 
#   position
#from   
#   plan_table
#where  
#   statement_id = 'query 1'
#order by 
#   id;
EOF

printf "done.\n"
printf "Extracting ${PREFIX}06.02${SUFFIX}..."

sed s/^#//g > ${PREFIX}06.02${SUFFIX} << EOF
#select 
#   lpad(' ', 2*(level-1)) 
#      || operation   || ' ' 
#      || options     || ' ' 
#      || object_name || ' ' 
#      || decode(id, 0, 'Cost = ' || position) 'Query Plan'
#from   
#   plan_table
#start with 
#   id = 0 and 
#   statement_id = 'query 1'
#connect by 
#   prior id = parent_id and 
#   statement_id = 'query 1';
EOF

printf "done.\n"
printf "Extracting ${PREFIX}06.03${SUFFIX}..."

sed s/^#//g > ${PREFIX}06.03${SUFFIX} << EOF
#delete from plan_table;
#
#explain plan
#set statement_id = 'SQL1' for
#select 
#   to_char(sysdate, 'MM/DD/YY HH:MM AM'), 
#   to_char((trunc((sysdate -4 -1), 'day') +1), 'DD-MON-YY'),
#from   
#   bk, 
#   ee
#where  
#   bk_shift_date >= to_char((trunc((sysdate - 4 - 1),'day') + 1),'DD-MON-YY') and
#   bk_shift_date <= to_char((sysdate - 4), 'DD-MON-YY') and
#   bk_empno = ee_empno(+) and
#   substr(ee_hierarchy_code,1,3) in ('PNA','PNB','PNC','PND','PNE','PNF')
#order by 
#   ee_job_group, 
#   bk_empno, 
#   bk_shift_date 
#/
#select 
#   lpad(' ', 2*(Level-1)) 
#      || Level || '.' 
#      || nvl(position,0) || ' ' 
#      || operation || ' ' 
#      || Options || ' ' 
#      || object_name || ' ' 
#      || object_type || ' ' 
#      || decode(id, 0, statement_id ||' Cost = ' || position) 
#      || other || ' ' 
#      || object_node "Query Plan"
#from   
#   plan_table
#start with 
#   id = 0 and 
#   statement_id = 'SQL1'
#connect by 
#   prior id = parent_id and 
#   statement_id = 'SQL1'
#/
EOF

printf "done.\n"
printf "Extracting ${PREFIX}06.04${SUFFIX}..."

sed s/^#//g > ${PREFIX}06.04${SUFFIX} << EOF
#select 
#   operation, 
#   options, 
#   id, 
#   position , 
#   object_name, 
#   partition_start, 
#   partition_stop
#from   
#   plan_table;
EOF

printf "done.\n"
printf "Extracting ${PREFIX}06.05${SUFFIX}..."

sed s/^#//g > ${PREFIX}06.05${SUFFIX} << EOF
#select 
#   sid, 
#   serial#
#from   
#   v\$session
#where  
#   username = 'SCOTT';
#
#  SID SERIAL#
#    9     190 
#
#1 row selected.
#
#execute dbms_system.set_sql_trace_in_session(9,190,TRUE);
#
#execute DBMS_SESSION.SET_SQL_TRACE (TRUE);
#PL/SQL procedure successfully completed.
#
#select 
#   ksppinm "Parameter Name", 
#   ksppivl "Value",
#   ksppidf "Default"
#from   
#   x\$ksppi
#where  
#   ksppinm like '%trace%';
EOF

printf "done.\n"
printf "Extracting ${PREFIX}06.06${SUFFIX}..."

sed s/^#//g > ${PREFIX}06.06${SUFFIX} << EOF
#alter session set events ='4031 trace name errorstack';
#
#alter session set events='4031 trace name errorstack level 4';
EOF

#-----------#
# Chapter 7 #
#-----------#

printf "done.\n"
printf "Extracting ${PREFIX}07.01${SUFFIX}..."

sed s/^#//g > ${PREFIX}07.01${SUFFIX} << EOF
#select /*+ FULL(table) */ column1,...
#
#select /*+ FULL(table) CACHE(table)*/ column1,...
#
#select /*+ CHOOSE */ column1, ...
#
#select /*+ RULE */ column1, ...
#
#select /*+ FIRST_ROWS */ column1, ...
#
#select /*+ ALL_ROWS */ column1, ...
#
#select /*+ INDEX (table index1, index2...) */ column1, ...
#
#select /*+ INDEX_ASC (table index1, index2...) */ column1, ...
#
#select /*+ INDEX_DESC (table index1, index2...) */ column1, ...
#
#select /*+ INDEX_FFS (table index) */ column1, ...
#
#select /*+ ORDERED */ column1, ...
#
#select /*+ ROWID (table) */ column1, ...
#
#select /*+ STAR */ column1, ...
#
#select /*+ ORDERED USE_NL(table) INDEX(table concat_idx) */ column1, ...
#
#select /*+ DRIVING_SITE (table) */ column1, ...
#
#select /*+ USE_MERGE (table) */ column1, ...
#
#select /*+ USE_NL (table index1, index2...) */ column1, ...
#
#select /*+ PUSH_SUBQ */ column1, ...
#
#select /*+ PARALLEL (table) */ column1, ...
EOF

#-----------#
# Chapter 8 #
#-----------#

printf "done.\n"
printf "Extracting ${PREFIX}08.01${SUFFIX}..."

sed s/^#//g > ${PREFIX}08.01${SUFFIX} << EOF
#select 
#   b.username username, 
#   a.disk_reads reads, 
#   a.executions exec, 
#   a.disk_reads / decode(a.executions, 0, 1,a.executions) rds_exec_ratio, 
#   a.command_type, 
#   a.sql_text Statement
#from   
#   v\$sqlarea a, dba_users b
#where  
#   a.parsing_user_id = b.user_id and
#   a.disk_reads > 100000
#order by 
#   a.disk_reads desc;
EOF

#-----------#
# Chapter 9 #
#-----------#

#*** No scripts for chapter 9 ***#

#------------#
# Chapter 10 #
#------------#

printf "done.\n"
printf "Extracting ${PREFIX}10.01${SUFFIX}..."

sed s/^#//g > ${PREFIX}10.01${SUFFIX} << EOF
#select 
#   substr(sql_text, 1, 20) sqltext, 
#   count(*)
#from   
#   v\$sqlarea
#group by 
#   substr(sql_text, 1, 20)
#having 
#   count(*) > 10;
#
#select 
#   upper(substr(sql_text, 1, 20)) sqltext, 
#   count(*)
#from   
#   v\$sqlarea
#group by 
#   upper(substr(sql_text, 1, 20))
#having 
#   count(*) > 10;
EOF

printf "done.\n"
printf "Extracting ${PREFIX}10.02${SUFFIX}..."

sed s/^#//g > ${PREFIX}10.02${SUFFIX} << EOF
#select 
#   name, 
#   sharable_mem
#from   
#   v\$db_object_cache
#where  
#   sharable_mem > 100000 and
#   type in ('PACKAGE','PACKAGE BODY','FUNCTION','PROCEDURE') and
#   kept = 'NO';
EOF

printf "done.\n"
printf "Extracting ${PREFIX}10.03${SUFFIX}..."

sed s/^#//g > ${PREFIX}10.03${SUFFIX} << EOF
#select 
#   to_char(sharable_mem / 1000 ,'999999') sz, 
#   decode(kept_versions,0,' ',rpad('yes(' || 
#      to_char(kept_versions) || ')' ,6)) keeped, 
#   rawtohex(address) || ',' || 
#      to_char(hash_value) name, 
#   substr(sql_text,1,354) extra, 
#   1 iscursor
#from 
#   v\$sqlarea
#where 
#   sharable_mem > &min_ksize * 1000
#union
#select 
#   to_char(sharable_mem / 1000 ,'999999') sz, 
#   decode(kept,'yes', 'yes ','') keeped, 
#   owner || '.' || name || 
#      lpad(' ',29 - (length(owner) + length(name))) 
#      || '(' || type || ')' name, 
#   null extra, 
#   0 iscursor
#from 
#   v\$db_object_cache v 
#where 
#   sharable_mem > &min_ksize * 1000 
#order by 
#   1 desc;
EOF

printf "done.\n"
printf "Extracting ${PREFIX}10.04${SUFFIX}..."

sed s/^#//g > ${PREFIX}10.04${SUFFIX} << EOF
#select 
#   ksmchsiz, 
#   ksmchcom 
#from   
#   x\$ksmsp 
#where  
#   ksmchsiz > 10000 and
#   ksmchcom like '%PL/SQL%';
EOF

printf "done.\n"
printf "Extracting ${PREFIX}10.05${SUFFIX}..."

sed s/^#//g > ${PREFIX}10.05${SUFFIX} << EOF
#col "Owner" format a12
#col "Object" format a20
#col "OType" format a12
#col "Change DTE" format a20
#
#select 
#   substr(owner,1,12) "Owner", 
#   substr(object_name,1,20) "Object", 
#   object_type "OType", 
#   to_char(last_ddl_time, 'DD-MON-YYYY HH24:MI:SS') "Change Date"
#from   
#   dba_objects
#where  
#   status <> 'VALID'
#order by 
#   1, 2;
EOF

printf "done.\n"
printf "Extracting ${PREFIX}10.06${SUFFIX}..."

sed s/^#//g > ${PREFIX}10.06${SUFFIX} << EOF
#column object_name format a20
#column last_ddl_time heading 'last ddl time'
#
#select 
#   object_type, 
#   object_name, 
#   status, 
#   created, 
#   last_ddl_time
#from   
#   user_objects
#where  
#   object_type in ('PROCEDURE','FUNCTION','PACKAGE','PACKAGE BODY','TRIGGER');
EOF

printf "done.\n"
printf "Extracting ${PREFIX}10.07${SUFFIX}..."

sed s/^#//g > ${PREFIX}10.07${SUFFIX} << EOF
#col "Owner/Table" format a30
#col "Trigger Name" format a20
#col "Event" format a12
#
#select 
#   substr(owner,12) "Owner", 
#   trigger_name "Trigger Name", 
#   trigger_type "Type", 
#   triggering_event "Event", 
#   table_owner || '.' || table_name "Owner/Table"
#from   
#   dba_triggers
#where  
#   status <> 'ENABLED'
#order by 
#   owner, 
#   trigger_name;
EOF

printf "done.\n"
printf "Extracting ${PREFIX}10.08${SUFFIX}..."

sed s/^#//g > ${PREFIX}10.08${SUFFIX} << EOF
#column trigger_name format a15
#column trigger_type format a15
#column triggering_event format a15
#column table_name format a15
#column trigger_body format a25
#
#select 
#   trigger_name, 
#   trigger_type, 
#   triggering_event, 
#   table_name, 
#   status, 
#   trigger_body
#from   
#   user_triggers;
EOF

printf "done.\n"
printf "Extracting ${PREFIX}10.09${SUFFIX}..."

sed s/^#//g > ${PREFIX}10.09${SUFFIX} << EOF
#declare
#
#   type ref_dat_array is table of varchar2(10) 
#      index by binary_integer;
#
#   ref_dat     ref_dat_array;
#   new_code    varchar2(10);
#
#   cursor c1 is
#      select *
#      from   ref_table;
#
#   cursor cmain is
#      select *
#      from   incoming_data;
#begin
#
#   -- First, load the reference array
#   -- with data from the reference table.
#
#   for c1_rec in c1 loop
#      ref_dat(c1_rec.ref_num) := c1_rec.ref_string;
#   end loop;
#
#   -- Open a cursor to the incoming data.
#
#   for in_data in cmain loop
#
#      begin
#
#         -- Calculate the reference string from the
#         -- reference data.
#
#         new_code := ref_dat(in_data.coded_value);
#
#         -- processing logic...
#         -- Commit each record as it is processed.
#
#         commit;
#
#         exception
#            when NO_DATA_FOUND then
#               -- Appropriate steps...
#            when OTHERS then
#               -- Appropriate steps...
#      end;
#
#   end loop;
#
#end;
EOF

printf "done.\n"
printf "Extracting ${PREFIX}10.10${SUFFIX}..."

sed s/^#//g > ${PREFIX}10.10${SUFFIX} << EOF
#column name format a20
#column referenced_owner format a15 heading R_OWNER
#column referenced_name format a15 heading R_NAME
#column referenced_type format a12 heading R_TYPE
#
#select 
#   name, 
#   type, 
#   referenced_owner, 
#   referenced_name,
#   referenced_type
#from   
#   user_dependencies
#order by 
#   type, 
#   name;
EOF

#------------#
# Chapter 11 #
#------------#

printf "done.\n"
printf "Extracting ${PREFIX}11.01${SUFFIX}..."

sed s/^#//g > ${PREFIX}11.01${SUFFIX} << EOF
#select 
#   statistic, 
#   value 
#from   
#   v\$pq_sysstat;
#
#select 
#   statistic, 
#   last_query, 
#   session_total 
#from   
#   v\$pq_sesstat;
#
#select * 
#from   v\$pq_tqstat;
EOF

#------------#
# Chapter 12 #
#------------#

printf "done.\n"
printf "Extracting ${PREFIX}12.01${SUFFIX}..."

sed s/^#//g > ${PREFIX}12.01${SUFFIX} << EOF
#alter user (user) password expire;
#
#alter user (user) identified by (password);
#
#alter user (user) account lock;
#
#alter user (user) account unlock;
EOF

#------------#
# Chapter 13 #
#------------#

printf "done.\n"
printf "Extracting ${PREFIX}13.01${SUFFIX}..."

sed s/^#//g > ${PREFIX}13.01${SUFFIX} << EOF
#select 
#   name, 
#   value, 
#   isdefault, 
#   isses_modifiable, 
#   issys_modifiable 
#from   
#   v\$parameter
#where  
#   issys_modifiable <> 'FALSE' or
#   isses_modifiable <> 'FALSE'
#order by 
#   name;
EOF

#------------#
# Chapter 14 #
#------------#

printf "done.\n"
printf "Extracting ${PREFIX}14.01${SUFFIX}..."

sed s/^#//g > ${PREFIX}14.01${SUFFIX} << EOF
#REM ORACLE version 7
#
#select 
#   name 
#from   
#   v\$fixed_table
#where  
#   name like 'V%'
#order by 
#   name;
#
#REM ORACLE version 8
#
#select 
#   name 
#from   
#   v\$fixed_table
#where  
#   name like 'GV%'
#order by 
#   name;
EOF

printf "done.\n"
printf "Extracting ${PREFIX}14.02${SUFFIX}..."

sed s/^#//g > ${PREFIX}14.02${SUFFIX} << EOF
#select 
#  'View Name: ' || view_name, 
#   substr(view_definition, 1, (instr(view_definition, 'from')-1)) def1, 
#   substr(view_definition,(instr(view_definition,'from'))) def2
#from   
#   v\$fixed_view_definition
#order by 
#   view_name;
#
#select distinct 
#   s.ksusenum,
#   o.kglnaown,
#   o.kglnaobj,
#   o.kglobtyp 
#from   
#   x\$ksuse s,
#   x\$kglob o,
#   x\$kgldp d, 
#   x\$kgllk l 
#where  
#   l.kgllkuse=s.addr and
#   l.kgllkhdl=d.kglhdadr and
#   o.kglhdadr=d.kglrfhdl
#/
#
#select 
#   le.indx + 1,
#   le.lethr,
#   le.leseq, 
#   decode(bitand(le.leflg,8),0,'NO','YES'), 
#   to_number(le.lelos) 
#from   
#   x\$kccle le,
#   x\$kccdi di 
#where  
#   bitand(di.diflg,1)!= 0 and 
#   le.ledup != 0 and
#   bitand(le.leflg,1) = 0 and 
#   (to_number(le.lelos) <= to_number(di.difas) or bitand(le.leflg,8) = 0)
#/
EOF

printf "done.\n"
printf "Extracting ${PREFIX}14.03${SUFFIX}..."

sed s/^#//g > ${PREFIX}14.03${SUFFIX} << EOF
#select 
#  'View Name: ' || view_name, 
#   substr(view_definition,1, (instr (view _definition,'from')-1)) def1,
#   substr(view_definition, (instr(view _definition,'from'))) def2
#from    
#   v\$fixed_view_definition
#order by 
#   view_name;
#
#select distinct 
#   s.inst_id, 
#   s.ksusenum, 
#   o.kglnaown, 
#   o.kglnaobj, 
#   decode(o.kglobtyp, 
#          0, 'CURSOR', 
#          1, 'INDEX', 
#          2, 'TABLE', 
#          3, 'CLUSTER', 
#          4, 'VIEW', 
#          5, 'SYNONYM', 
#          6, 'SEQUENCE', 
#          7, 'PROCEDURE', 
#          8, 'FUNCTION', 
#          9, 'PACKAGE', 
#         10,'NON-EXISTENT', 
#         11,'PACKAGE BODY', 
#         12,'TRIGGER', 
#         13,'CLASS', 
#         14,'SET', 
#         15,'OBJECT', 
#         16,'USER', 
#         17,'DBLINK', 
#            'INVALID TYPE') 
#from   
#   x\$ksuse s,
#   x\$kglob o,
#   x\$kgldp d,
#   x\$kgllk l 
#where  
#   l.kgllkuse=s.addr and
#   l.kgllkhdl=d.kglhdadr and
#   l.kglnahsh=d.kglnahsh and
#   o.kglnahsh=d.kglrfhsh and
#   o.kglhdadr=d.kglrfhdl
#/
#
#select 
#   inst_id, 
#   ksiminum, 
#   rpad(ksimstr,60) 
#from   
#   x\$ksimsi
#/
EOF

printf "done.\n"
printf "Extracting ${PREFIX}14.04${SUFFIX}..."

sed s/^#//g > ${PREFIX}14.04${SUFFIX} << EOF
#select 
#   table_name, 
#   column_name, 
#   index_number
#from   
#   v\$indexed_fixed_column
#order by 
#   table_name, 
#   column_name;
EOF

printf "done.\n"
printf "Extracting ${PREFIX}14.05${SUFFIX}..."

sed s/^#//g > ${PREFIX}14.05${SUFFIX} << EOF
#select *
#from   v\$database;
#
#select * 
#from   v\$license;
#
#select * 
#from   v\$option;
#
#select * 
#from   v\$nls_parameter;
#
#select *
#from   v\$sga;
#
#select *
#from   v\$sgastat;
EOF

printf "done.\n"
printf "Extracting ${PREFIX}14.06${SUFFIX}..."

sed s/^#//g > ${PREFIX}14.06${SUFFIX} << EOF
#select 
#   name, 
#   value, 
#   isdefault, 
#   isses_modifiable, 
#   issys_modifiable
#from   
#   v\$parameter 
#order by 
#   name;
EOF

printf "done.\n"
printf "Extracting ${PREFIX}14.07${SUFFIX}..."

sed s/^#//g > ${PREFIX}14.07${SUFFIX} << EOF
#select 
#   1 - (sum(decode(name,'physical reads',value,0))/ 
#       (sum(decode(name,'db block gets',value,0)) + 
#       (sum(decode(name,'consistent gets',value,0))))) 
#       "Read Hit Ratio" 
#from   
#   v\$sysstat;
EOF

printf "done.\n"
printf "Extracting ${PREFIX}14.08${SUFFIX}..."

sed s/^#//g > ${PREFIX}14.08${SUFFIX} << EOF
#select 
#   sum(gets), 
#   sum(getmisses),
#  (1 - (sum(getmisses) / (sum(gets) + sum(getmisses)))) * 100 HitRate
#from   
#   v\$rowcache;
EOF

printf "done.\n"
printf "Extracting ${PREFIX}14.09${SUFFIX}..."

sed s/^#//g > ${PREFIX}14.09${SUFFIX} << EOF
#select 
#   sum(pins) "Executions", 
#   sum(pinhits) "Hits", 
# ((sum(pinhits) / sum(pins)) * 100) "PinHitRatio", 
#   sum(reloads) "Misses", 
# ((sum(pins) / (sum(pins) + sum(reloads))) * 100) "RelHitRatio"
#from   
#   v\$librarycache;
EOF

printf "done.\n"
printf "Extracting ${PREFIX}14.10${SUFFIX}..."

sed s/^#//g > ${PREFIX}14.10${SUFFIX} << EOF
#select 
#   name, 
#   sharable_mem 
#from   
#   v\$db_object_cache 
#where  
#   sharable_mem > 100000 and
#   type in ('PACKAGE','PACKAGE BODY','FUNCTION','PROCEDURE') and
#   kept = 'NO';
EOF

printf "done.\n"
printf "Extracting ${PREFIX}14.11${SUFFIX}..."

sed s/^#//g > ${PREFIX}14.11${SUFFIX} << EOF
#select 
#   b.username username, 
#   a.disk_reads reads, 
#   a.executions exec, 
#   a.disk_reads / decode(a.executions, 0, 1, a.executions) rds_exec_ratio,
#   a.command_type, 
#   a.sql_text Statement
#from 
#   v\$sqlarea a, 
#   dba_users b
#where 
#   a.parsing_user_id = b.user_id and
#   a.disk_reads > 100000
#order by 
#   a.disk_reads desc;
EOF

printf "done.\n"
printf "Extracting ${PREFIX}14.12${SUFFIX}..."

sed s/^#//g > ${PREFIX}14.12${SUFFIX} << EOF
#select 
#   a.sid, 
#   a.username, 
#   s.sql_text
#from 
#   v\$session a, 
#   v\$sqltext s
#where 
#   a.sql_address = s.address and
#   a.sql_hash_value = s.hash_value
#order by 
#   a.username, 
#   a.sid, 
#   s.piece;
EOF

printf "done.\n"
printf "Extracting ${PREFIX}14.13${SUFFIX}..."

sed s/^#//g > ${PREFIX}14.13${SUFFIX} << EOF
#select 
#   a.username, 
#   b.block_gets, 
#   b.consistent_gets, 
#   b.physical_reads, 
#   b.block_changes, 
#   b.consistent_changes
#from 
#   v\$session a, 
#   v\$sess_io b
#where 
#   a.sid = b.sid
#order by 
#   a.username;
EOF

printf "done.\n"
printf "Extracting ${PREFIX}14.14${SUFFIX}..."

sed s/^#//g > ${PREFIX}14.14${SUFFIX} << EOF
#select 
#   a.sid, 
#   a.username, 
#   b.owner, 
#   b.object, 
#   b.type
#from 
#   v\$session a, 
#   v\$access b
#where 
#   a.sid = b.sid;
EOF

printf "done.\n"
printf "Extracting ${PREFIX}14.15${SUFFIX}..."

sed s/^#//g > ${PREFIX}14.15${SUFFIX} << EOF
#
#select 
#   a.sid, 
#   a.username, 
#   c.name, 
#   b.value
#from 
#   v\$session a, 
#   v\$sesstat b, 
#   v\$statname c
#where 
#   a.sid = b.sid and
#   b.statistic# = c.statistic# and
#   b.value != 0
#order by 
#   name;
EOF

printf "done.\n"
printf "Extracting ${PREFIX}14.16${SUFFIX}..."

sed s/^#//g > ${PREFIX}14.16${SUFFIX} << EOF
#select 
#   b.username, 
#   b.serial#, 
#   d.id1, 
#   a.sql_text
#from 
#   v\$session b, 
#   v\$lock d, 
#   v\$sqltext a
#where 
#   b.lockwait = d.kaddr and
#   a.address = b.sql_address and
#   a.hash_value = b.sql_hash_value;
EOF

printf "done.\n"
printf "Extracting ${PREFIX}14.17${SUFFIX}..."

sed s/^#//g > ${PREFIX}14.17${SUFFIX} << EOF
#select 
#   a.serial#, 
#   a.sid, 
#   a.username, 
#   b.id1, 
#   c.sql_text
#from 
#   v\$session a, 
#   v\$lock b, 
#   v\$sqltext c
#where 
#   b.id1 in (select distinct 
#                e.id1
#             from 
#                v\$session d, 
#                v\$lock e
#             where 
#                d.lockwait = e.kaddr) and
#   a.sid = b.sid
#   c.hash_value = a.sql_hash_value
#   b.request = 0;
#
#select 
#   username, 
#   count(*)
#from 
#   v\$session
#group by 
#   username;
EOF

printf "done.\n"
printf "Extracting ${PREFIX}14.18${SUFFIX}..."

sed s/^#//g > ${PREFIX}14.18${SUFFIX} << EOF
#column c0 heading "Profile";
#column c1 heading "Resource_Name";
#column c2 heading "Limit";
#
#select 
#   substr(profile,1,10) Profile, 
#   substr(resource_name,1,30) Resource_Name,
#   substr(limit,1,10) Limit
#from 
#   dba_profiles
#group by 
#   substr(profile,1,10), 
#   substr(resource_name,1,30), 
#   substr(limit,1,10);
EOF

printf "done.\n"
printf "Extracting ${PREFIX}14.19${SUFFIX}..."

sed s/^#//g > ${PREFIX}14.19${SUFFIX} << EOF
#select 
#   a.file#, 
#   a.name, 
#   a.status, 
#   a.bytes, 
#   b.phyrds, 
#   b.phywrts 
#from 
#   v\$datafile a, 
#   v\$filestat b 
#where 
#   a.file# = b.file#;
EOF

printf "done.\n"
printf "Extracting ${PREFIX}14.20${SUFFIX}..."

sed s/^#//g > ${PREFIX}14.20${SUFFIX} << EOF
#Set TrimSpool On
#Set Line 142
#Set Pages 57
#Set NewPage 0
#Set FeedBack Off
#Set Verify Off
#Set Term Off
#TTitle Off
#BTitle Off
#Clear Breaks
#Break On Tablespace_Name
#Column TableSpace_Name For A12 Head "Tablespace"
#Column Name For A45 Head "File Name"
#Column Total For 999,999,990 Head "Total"
#Column Phyrds For 999,999,990 Head "Physical|Reads "
#Column Phywrts For 999,999,990 Head "Physical| Writes "
#Column Phyblkrd For 999,999,990 Head "Physical |Block Reads"
#Column Phyblkwrt For 999,999,990 Head "Physical |Block Writes"
#Column Avg_Rd_Time For 90.9999999 Head "Average |Read Time|Per Block"
#Column Avg_Wrt_Time For 90.9999999 Head "Average |Write Time|Per Block"
#Column Instance New_Value _Instance NoPrint
#Column Today New_Value _Date NoPrint
#
#select 
#   Global_Name Instance, 
#   To_Char(SysDate, 'FXDay DD, YYYY HH:MI') Today
#from 
#   Global_Name;
#
#TTitle On
#TTitle Left 'Date Run: ' _Date Skip 1-
#Center 'Data File I/O' Skip 1 -
#Center 'Instance Name: ' _Instance Skip 1
#
#select 
#   C.TableSpace_Name, 
#   B.Name, 
#   A.Phyblkrd + 
#A.Phyblkwrt Total, 
#   A.Phyrds, 
#   A.Phywrts, 
#A.Phyblkrd, 
#   A.Phyblkwrt
#from 
#   V\$FileStat A, 
#   V\$DataFile B, 
#   Sys.DBA_Data_Files C
#where 
#   B.File# = A.File# and
#   B.File# = C.File_Id
#order by 
#   TableSpace_Name, 
#   A.File#
#/
EOF

printf "done.\n"
printf "Extracting ${PREFIX}14.21${SUFFIX}..."

sed s/^#//g > ${PREFIX}14.21${SUFFIX} << EOF
#Column TableSpace_Name For A12 Head "Tablespace"
#Column Total For 9,999,999,990 Head "Total"
#Column Phyrds For 9,999,999,990 Head "Physical|Reads "
#Column Phywrts For 9,999,999,990 Head "Physical| Writes "
#Column Phyblkrd For 9,999,999,990 Head "Physical |Block Reads"
#Column Phyblkwrt For 9,999,999,990 Head "Physical |Block Writes"
#Column Avg_Rd_Time For 9,999,990.9999 Head "Average |Read Time|Per Block"
#Column Avg_Wrt_Time For 9,999,990.9999 Head "Average |Write Time|Per Block"
#Clear Breaks
#Break on Disk Skip 1
#Compute Sum Of Total On Disk
#Compute Sum Of Phyrds On Disk
#Compute Sum Of Phywrts On Disk
#Compute Sum Of Phyblkrd On Disk
#Compute Sum Of Phyblkwrt On Disk
#TTitle Left 'Date Run: ' _Date Skip 1-
#Center 'Disk I/O' Skip 1 -
#Center 'Instance Name: ' _Instance Skip 2
#
#select 
#   SubStr(B.Name, 1, 13) Disk, 
#   C.TableSpace_Name, 
#   A.Phyblkrd + A.Phyblkwrt Total, 
#   A.Phyrds, 
#   A.Phywrts, 
#   A.Phyblkrd, 
#   A.Phyblkwrt, 
# ((A.ReadTim / Decode(A.Phyrds,0,1,A.Phyblkrd))/100) Avg_Rd_Time, 
# ((A.WriteTim / Decode(A.PhyWrts,0,1,A.PhyblkWrt)) / 100) Avg_Wrt_Time 
#from 
#   V\$FileStat A, 
#   V\$DataFile B, 
#   Sys.DBA_Data_Files C
#where 
#   B.File# = A.File# and
#   B.File# = C.File_Id
#order by 
#   Disk,
#   C.Tablespace_Name, 
#   A.File#
#/
EOF

printf "done.\n"
printf "Extracting ${PREFIX}14.22${SUFFIX}..."

sed s/^#//g > ${PREFIX}14.22${SUFFIX} << EOF
#Set FeedBack On
#Set Verify On
#Set Term On
#TTitle Off
#BTitle Off
#
#select 
#   a.name, 
#   b.xacts, 
#   c.sid, 
#   c.serial#, 
#   c.username, 
#   d.sql_text 
#from 
#   v\$rollname a, 
#   v\$rollstat b, 
#   v\$session c, 
#   v\$sqltext d,
#   v\$transaction e 
#where 
#   a.usn = b.usn and
#   b.usn = e.xidusn and
#   c.taddr = e.addr and
#   c.sql_address = d.address and 
#   c.sql_hash_value = d.hash_value 
#order by 
#   a.name, 
#   c.sid, 
#   d.piece;
EOF

printf "done.\n"
printf "Extracting ${PREFIX}14.23${SUFFIX}..."

sed s/^#//g > ${PREFIX}14.23${SUFFIX} << EOF
#select 
#   a.name, 
#   b.extents, 
#   b.rssize, 
#   b.xacts, 
#   b.waits, 
#   b.gets, 
#   optsize, 
#   status
#from 
#   v\$rollname a, 
#   v\$rollstat b;
EOF

printf "done.\n"
printf "Extracting ${PREFIX}14.24${SUFFIX}..."

sed s/^#//g > ${PREFIX}14.24${SUFFIX} << EOF
#select 
#   A.Class, 
#   Count, 
#   Sum(Value) Con_Get, 
# ((Count / Sum(Value)) * 100) pct
#from 
#   V\$WaitStat A, 
#   V\$SysStat B
#where 
#   Name In ('db block gets', 'consistent gets')
#group by 
#   A.Class, 
#   Count 
EOF

printf "done.\n"
printf "Extracting ${PREFIX}14.25${SUFFIX}..."

sed s/^#//g > ${PREFIX}14.25${SUFFIX} << EOF
#Set TrimSpool On
#Set NewPage 0
#Set Pages 57
#Set Line 132
#Set FeedBack Off
#Set Verify Off
#Set Term Off
#TTitle Off
#BTitle Off
#Clear Breaks
#Column Event For A40 Heading "Wait Event"
#Column Total_Waits For 999,999,990 Head Total Number| Of Waits "
#Column Total_Timeouts For 999,999,990 Head "Total Number|Of TimeOuts"
#Column Tot_Time For 999,999,990 Head "Total Time|Waited "
#Column Avg_Time For 99,990.999 Head "Average Time|Per Wait "
#Column Instance New_Value _Instance NoPrint
#Column Today New_Value _Date NoPrint
#
#select 
#   Global_Name Instance, 
#   To_Char(SysDate,'FXDay DD, YYYY HH:MI') Today
#from 
#   Global_Name;
#
#select 
#   event, 
#   total_waits, 
#   total_timeouts, 
#  (time_waited / 100) tot_time, 
#  (average_wait / 100) Avg_time
#from 
#   v\$system_event
#order by 
#   total_waits desc
#/
EOF

printf "done.\n"
printf "Extracting ${PREFIX}14.26${SUFFIX}..."

sed s/^#//g > ${PREFIX}14.26${SUFFIX} << EOF
#Set TrimSpool On
#Set Line 132
#Set Pages 57
#Set NewPage 0
#Set FeedBack Off
#Set Verify Off
#Set Term Off
#TTitle Off
#BTitle Off
#
#select 
#   Global_Name Instance, 
#   To_Char(SysDate, 'FXDay DD, YYYY HH:MI') Today
#from 
#   Global_Name;
#
#select 
#   ((A.Count / (B.Value + C.Value)) * 100) Pct
#from 
#   V\$WaitStat A, 
#   V\$SysStat B, 
#   V\$SysStat C
#where 
#   A.Class = 'free list' and
#   B.Statistic# = (select Statistic#
#                   from   V\$StatName
#                   where Name = 'db block gets') and
#   C.Statistic# = (select Statistic#
#                   from   V\$StatName
#                   where  Name = 'consistent gets')
#/
EOF

printf "done.\n"
printf "Extracting ${PREFIX}14.27${SUFFIX}..."

sed s/^#//g > ${PREFIX}14.27${SUFFIX} << EOF
#Column Total_Waits Format 999,999,999,990 Heading "Buffer Busy Waits"
#Column DB_Get Format 999,999,999,990 Heading "DB Block Gets"
#Column Con_Get Format 999,999,999,990 Heading "Consistent Gets"
#Column Busy_Rate Format 990.999 Heading "Busy Rate"
#TTitle Left 'Date Run: ' _Date Skip 1-
#Center 'Buffer Busy Waits Rate' Skip 1 - 
#Center 'If >5% review V\$WaitStat' Skip 1 - 
#Center 'Instance Name: ' _Instance Skip 2
#
#select 
#   Total_Waits, 
#   B.Value DB_Get, 
#   C.Value Con_Get, 
# ((A.Total_Waits / (B.Value + C.Value)) * 100) Busy
#from 
#   V\$System_Event A, 
#   V\$SysStat B, 
#   V\$SysStat C
#where 
#   A.Event = 'buffer busy waits' and
#   B.Statistic# = (select Statistic#
#                   from   V\$StatName
#                   where Name = 'db block gets') and
#   C.Statistic# = (select Statistic#
#                   from   V\$StatName
#                   where  Name = 'consistent gets')
#/
EOF

#------------#
# Chapter 15 #
#------------#

printf "done.\n"
printf "Extracting ${PREFIX}15.01${SUFFIX}..."

sed s/^#//g > ${PREFIX}15.01${SUFFIX} << EOF
#select 
#   kqfvinam name
#from 
#   x\$kqfvi
#order by 
#   kqfvinam;
EOF

printf "done.\n"
printf "Extracting ${PREFIX}15.02${SUFFIX}..."

sed s/^#//g > ${PREFIX}15.02${SUFFIX} << EOF
#select * 
#from 
#   v\$fixed_view_definition
#where 
#   view_name = 'GV\$FIXED_TABLE';
EOF

printf "done.\n"
printf "Extracting ${PREFIX}15.03${SUFFIX}..."

sed s/^#//g > ${PREFIX}15.03${SUFFIX} << EOF
#select 
#   name
#from 
#   v\$fixed_table
#where 
#   name like 'X%'
#order by 
#   name;
EOF

printf "done.\n"
printf "Extracting ${PREFIX}15.04${SUFFIX}..."

sed s/^#//g > ${PREFIX}15.04${SUFFIX} << EOF
#select *
#from   x\$kqfdt;
EOF

printf "done.\n"
printf "Extracting ${PREFIX}15.05${SUFFIX}..."

sed s/^#//g > ${PREFIX}15.05${SUFFIX} << EOF
#select 
#   table_name, 
#   column_name, 
#   index_number
#from 
#   v\$indexed_fixed_column
#order by 
#   table_name;
#
#select 
#   * 
#from   
#   v\$fixed_view_definition
#where  
#   view_name = 'GV\$INDEXED_FIXED_COLUMN';
EOF

printf "done.\n"
printf "Extracting ${PREFIX}15.06${SUFFIX}..."

sed s/^#//g > ${PREFIX}15.06${SUFFIX} << EOF
#select 
#   /*+ index p(p.indx) */ 
#   p.ksbdppro, 
#   p.ksbdpnam, 
#   d.ksbdddsc,
#   p.ksbdperr
#from 
#   x\$ksbdp p, 
#   x\$ksbdd d
#where 
#   p.indx = d.indx and
#   p.indx = 1;
EOF

printf "done.\n"
printf "Extracting ${PREFIX}15.07${SUFFIX}..."

sed s/^#//g > ${PREFIX}15.07${SUFFIX} << EOF
#select 
#   /*+ ordered */ 
#   p.ksbdppro, 
#   p.ksbdpnam, 
#   d.ksbdddsc,
#   p.ksbdperr
#from 
#   x\$ksbdp p, 
#   x\$ksbdd d
#where 
#   p.indx = d.indx and
#   p.indx = 1;
#
#select 
#   /*+ ordered */ 
#   p.ksbdppro, 
#   p.ksbdpnam, 
#   d.ksbdddsc,
#   p.ksbdperr
#from 
#   x\$ksbdd d, 
#   x\$ksbdp p
#where 
#   p.indx = d.indx and
#   p.indx = 1;
EOF

printf "done.\n"
printf "Extracting ${PREFIX}15.08${SUFFIX}..."

sed s/^#//g > ${PREFIX}15.08${SUFFIX} << EOF
#select 
#   dbablk, 
#   state, 
#   lrba_seq 
#from 
#   x\$bh
#where 
#   lrba_seq <> 0 or
#   state = 3;
EOF

printf "done.\n"
printf "Extracting ${PREFIX}15.09${SUFFIX}..."

sed s/^#//g > ${PREFIX}15.09${SUFFIX} << EOF
#select 
#   sum(ksmchsiz) Bytes, 
#   ksmchcls Status
#from 
#   x\$ksmsp
#group by 
#   ksmchcls
EOF

printf "done.\n"
printf "Extracting ${PREFIX}15.10${SUFFIX}..."

sed s/^#//g > ${PREFIX}15.10${SUFFIX} << EOF
#ORACLE Version 7
#
#select 
#   ksppinm, 
#   ksppivl, 
#   ksppidf
#from 
#   x\$ksppi
#where 
#   substr(ksppinm,1,1) = '_';
#
#ORACLE Version 8
#
#select 
#   a.ksppinm, 
#   b.ksppstvl, 
#   b.ksppstdf
#from 
#   x\$ksppi a, 
#   x\$ksppcv b
#where 
#   a.indx = b.indx and
#   substr(ksppinm,1,1) = '_'
#order by 
#   a.ksppinm;
EOF

printf "done.\n"
printf "Extracting ${PREFIX}15.11${SUFFIX}..."

sed s/^#//g > ${PREFIX}15.11${SUFFIX} << EOF
#select 
#   a.ksppinm, 
#   b.ksppstvl
#from 
#   x\$ksppi a, 
#   x\$ksppcv b
#where 
#   a.indx = b.indx and
#   ksppinm = '_init_sql_file';
EOF

printf "done.\n"
printf "Extracting ${PREFIX}15.12${SUFFIX}..."

sed s/^#//g > ${PREFIX}15.12${SUFFIX} << EOF
#select 
#   ksppinm, 
#   ksppivl,
#   ksppidf
#from 
#   x\$ksppi
#where 
#   ksppinm like '%_trace%';
EOF

printf "done.\n"
printf "Extracting ${PREFIX}15.13${SUFFIX}..."

sed s/^#//g > ${PREFIX}15.13${SUFFIX} << EOF
#select 
#   kvisval, 
#   kvisdsc
#from 
#   x\$kvis;
EOF

#------------#
# Chapter 16 #
#------------#

printf "done.\n"
printf "Extracting ${PREFIX}16.01${SUFFIX}..."

sed s/^#//g > ${PREFIX}16.01${SUFFIX} << EOF
#select 
#   namespace library, 
#   gets,
#   round(decode(gethits,0,1,gethits)/decode(gets,0,1,gets),3) gethitratio,
#   pins, 
#   round(decode(pinhits,0,1,pinhits)/decode(pins,0,1,pins),3) pinhitratio, 
#   reloads, 
#   invalidations
#from 
#   stats\$lib;
EOF

printf "done.\n"
printf "Extracting ${PREFIX}16.02${SUFFIX}..."

sed s/^#//g > ${PREFIX}16.02${SUFFIX} << EOF
#select 
#   n1.name "Statistic", 
#   n1.change "Total",
#   round(n1.change/trans.change,2) "Per Transaction",
#   round(n1.change/logs.change,2) "Per Logon",
#   round(n1.change/(to_number(to_char(end_time, 'J'))*60*60*24 -
#      to_number(to_char(start_time, 'J'))*60*60*24 +
#      to_number(to_char(end_time, 'SSSSS')) -
#      to_number(to_char(start_time, 'SSSSS'))), 2) "Per Second"
#from 
#   stats\$stats n1, 
#   stats\$stats trans, 
#   stats\$stats logs, 
#   stats\$dates
#where 
#   trans.name='user commits' and
#   logs.name='logons cumulative' and
#   n1.change != 0
#order by 
#   n1.name;
EOF

printf "done.\n"
printf "Extracting ${PREFIX}16.03${SUFFIX}..."

sed s/^#//g > ${PREFIX}16.03${SUFFIX} << EOF
#select 
#   queue.change / writes.change "Average Write Queue Length"
#from 
#   stats\$stats queue, 
#   stats\$stats writes
#where 
#   queue.name = 'summed dirty queue length' and
#   writes.name = 'write requests';
EOF

printf "done.\n"
printf "Extracting ${PREFIX}16.04${SUFFIX}..."

sed s/^#//g > ${PREFIX}16.04${SUFFIX} << EOF
#select 
#   n1.event "Event Name", 
#   n1.event_count "Count", 
#   n1.time_waited "Total Time",
#   round(n1.time_waited/n1.event_count, 2) "Avg Time"
#from 
#   stats\$event n1
#where 
#   n1.event_count > 0
#order by 
#   n1.time_waited desc;
EOF

printf "done.\n"
printf "Extracting ${PREFIX}16.05${SUFFIX}..."

sed s/^#//g > ${PREFIX}16.05${SUFFIX} << EOF
#select 
#   n1.event "Event Name", 
#   n1.event_count "Count", 
#   n1.time_waited "Total Time",
#   round(n1.time_waited/n1.event_count, 2) "Avg Time"
#from 
#   stats\$bck_event n1
#where 
#   n1.event_count > 0
#order by 
#   n1.time_waited desc;
EOF

printf "done.\n"
printf "Extracting ${PREFIX}16.06${SUFFIX}..."

sed s/^#//g > ${PREFIX}16.06${SUFFIX} << EOF
#select 
#   name latch_name, 
#   gets, 
#   misses,
#   round((gets-misses)/decode(gets,0,1,gets),3) hit_ratio,
#   sleeps, 
#   round(sleeps/decode(misses,0,1,misses),3) "SLEEPS/MISS"
#from 
#   stats\$latches
#where 
#   gets != 0
#order by 
#   name;
EOF

printf "done.\n"
printf "Extracting ${PREFIX}16.07${SUFFIX}..."

sed s/^#//g > ${PREFIX}16.07${SUFFIX} << EOF
#select 
#   name latch_name, 
#   immed_gets nowait_gets,
#   immed_miss nowait_misses, 
#   round((immed_gets / immed_gets + immed_miss), 3) nowait_hit_ratio
#from 
#   stats\$latches
#where 
#   immed_gets + immed_miss != 0
#order by 
#   name;
EOF

printf "done.\n"
printf "Extracting ${PREFIX}16.08${SUFFIX}..."

sed s/^#//g > ${PREFIX}16.08${SUFFIX} << EOF
#select 
#   *
#from 
#   stats\$waitstat
#where 
#   count != 0
#order by 
#   count desc;
EOF

printf "done.\n"
printf "Extracting ${PREFIX}16.09${SUFFIX}..."

sed s/^#//g > ${PREFIX}16.09${SUFFIX} << EOF
#select 
#   * 
#from 
#   stats\$roll;
EOF

printf "done.\n"
printf "Extracting ${PREFIX}16.10${SUFFIX}..."

sed s/^#//g > ${PREFIX}16.10${SUFFIX} << EOF
#select 
#   name, 
#   value 
#from 
#   v\$parameter 
#where 
#   isdefault = 'FALSE'
#order by 
#   name;
EOF

printf "done.\n"
printf "Extracting ${PREFIX}16.11${SUFFIX}..."

sed s/^#//g > ${PREFIX}16.11${SUFFIX} << EOF
#select 
#   *
#from 
#   stats\$dc
#where 
#   get_reqs != 0 or
#   scan_reqs != 0 or
#   mod_reqs != 0;
EOF

printf "done.\n"
printf "Extracting ${PREFIX}16.12${SUFFIX}..."

sed s/^#//g > ${PREFIX}16.12${SUFFIX} << EOF
#select 
#   table_space || ' ' table_space,
#   sum(phys_reads) reads, 
#   sum(phys_blks_rd) blks_read,
#   sum(phys_rd_time) read_time, 
#   sum(phys_writes) writes,
#   sum(phys_blks_wr) blks_wrt, 
#   sum(phys_wrt_tim) write_time,
#   sum(megabytes_size) megabytes
#from 
#   stats\$files
#group by 
#   table_space
#order by table_space;
#
#select 
#   table_space, 
#   file_name, 
#   phys_reads reads, 
#   phys_blks_rd blks_read, 
#   phys_rd_time read_time, 
#   phys_writes writes, 
#   phys_blks_wr blks_wrt,
#   phys_wrt_tim write_time, 
#   megabytes_size megabytes
#from 
#   stats\$files 
#order by 
#   table_space, 
#   file_name;
EOF

#------------#
# Chapter 17 #
#------------#

printf "done.\n"
printf "Extracting ${PREFIX}17.01${SUFFIX}..."

sed s/^#//g > ${PREFIX}17.01${SUFFIX} << EOF
#select 
#  (1-(sum(decode(name, 'physical reads',value,0)) / 
#     (sum(decode(name, 'db block gets',value,0)) + 
#      sum(decode(name, 'consistent gets',value,0))))) * 100 "Hit Ratio",
#   decode(sign((1 -
      (sum(decode(name,'physical reads',value,0)) / 
#     (sum(decode(name,'db block gets',value,0)) + 
#      sum(decode(name,'consistent gets',value,0)))))*100-98,1,30
#   decode(sign((1 -
      (sum(decode(name,'physical reads',value,0)) / 
#     (sum(decode(name,'db block gets',value,0)) + 
#      sum(decode(name,'consistent gets',value,0)))))*100-95,1,20
#   decode(sign((1 -
      (sum(decode(name, 'physical reads',value,0)) / 
#     (sum(decode(name, 'db block gets',value,0)) + 
#      sum(decode(name, 'consistent gets',value,0)))))*100-90,1,10,0))) "Score"
#from 
#   v\$sysstat;
EOF

printf "done.\n"
printf "Extracting ${PREFIX}17.02${SUFFIX}..."

sed s/^#//g > ${PREFIX}17.02${SUFFIX} << EOF
#set serverout on
#
#declare
#
#   cursor c1 is 
#      select buffer_gets
#      from   v\$sqlarea
#      order by buffer_gets DESC;
#
#   cursor c2 is 
#      select sum(buffer_gets)
#      from   v\$sqlarea;
#
#   sumof10     number:=0;
#   mybg        number;
#   mytotbg     number;
#
#begin
#
#   dbms_output.put_line('Percent');
#   dbms_output.put_line('-------');
#
#   open c1;
#
#   for i in 1..10 loop
#      fetch c1 into mybg;
#      sumof10 := sumof10 + mybg;
#   end loop;
#
#   close c1;
#
#   open c2;
#   fetch c2 into mytotbg;
#   close c2;
#
#   dbms_output.put_line(sumof10/mytotbg*100);
#
#end;
#/
EOF

printf "done.\n"
printf "Extracting ${PREFIX}17.03${SUFFIX}..."

sed s/^#//g > ${PREFIX}17.03${SUFFIX} << EOF
#set serverout on size 1000000
#
#declare 
#
#   top25      number;
#   text1      varchar2(4000);
#   x          number;
#   len1       number;
#
#   cursor c1 is
#      select disk_reads, 
#             substr(sql_text,1,4000)
#      from   v\$sqlarea
#      order by disk_reads desc;
#
#begin
#
#   dbms_output.put_line('Reads'||' '||'Text');
#   dbms_output.put_line('----------'||' '||'----------------------');
#
#   open c1;
#
#   for i in 1..25 loop
#
#      fetch c1 into top25, text1;
#      dbms_output.put_line(rpad(to_char(top25),9)||' '||substr(text1,1,66));
#      len1 := length(text1);
#      x := 66;
#
#      while len1 > x-1 loop
#         dbms_output.put_line('" '||substr(text1,x,66));
#         x := x + 66; 
#      end loop;
#
#   end loop;
#
#end;
#/
EOF

printf "done.\n"
printf "Extracting ${PREFIX}17.04${SUFFIX}..."

sed s/^#//g > ${PREFIX}17.04${SUFFIX} << EOF
#set serverout on;
#
#declare
#
#   cursor c1 is 
#      select disk_reads
#      from   v\$sqlarea
#      order by disk_reads DESC;
#
#   cursor c2 is 
#      select sum(disk_reads)
#      from   v\$sqlarea;
#
#   Sumof10    number:=0;
#   mydr       number;
#   mytotdr    number;
#
#begin
#
#   dbms_output.put_line('Percent');
#   dbms_output.put_line('-------');
#
#   open c1;
#
#   for i IN 1..10 loop
#      fetch c1 INTO mydr;
#      sumof10 := sumof10 + mydr;
#   end loop;
#
#   close c1;
#
#   open c2;
#   fetch c2 into mytotdr;
#   close c2;
#
#   dbms_output.put_line(sumof10 / mytotdr * 100);
#
#end;
#/
EOF

printf "done.\n"
printf "Extracting ${PREFIX}17.05${SUFFIX}..."

sed s/^#//g > ${PREFIX}17.05${SUFFIX} << EOF
#select 
#   segment_name, 
#   file_name
#from 
#   dba_data_files, 
#   dba_rollback_segs
#where 
#   dba_data_files.file_id = dba_rollback_segs.file_id;
EOF

printf "done.\n"
printf "Extracting ${PREFIX}17.06${SUFFIX}..."

sed s/^#//g > ${PREFIX}17.06${SUFFIX} << EOF
#select 
#   username, 
#   file_name
#from 
#   dba_data_files, 
#   dba_users
#where 
#   dba_data_files.tablespace_name = dba_users.temporary_tablespace;
EOF

printf "done.\n"
printf "Extracting ${PREFIX}17.07${SUFFIX}..."

sed s/^#//g > ${PREFIX}17.07${SUFFIX} << EOF
#select 
#   segment_name, 
#   extents
#from 
#   dba_segments
#where 
#   extents > 5
#order by 
#   extents desc;
#
#select 
#   segment_name, 
#   extents
#from 
#   dba_segments
#where 
#   extents > 30
#order by 
#   extents desc;
EOF

#------------#
# Chapter 18 #
#------------#

printf "done.\n"
printf "Extracting ${PREFIX}18.01${SUFFIX}..."

sed s/^#//g > ${PREFIX}18.01${SUFFIX} << EOF
#col username format a15
#col osuser format a10
#col program format a20 
#set verify off
#
#select 
#   a.username, 
#   a.osuser, 
#   a.program, 
#   spid, 
#   sid, 
#   a.serial#
#from 
#   v\$session a, 
#   v\$process b
#where 
#   a.paddr = b.addr and
#   spid = '&pid';
#
#rem this is ps_sql.sql
#
#set verify off
#column username format a15
#column sql_text format a60
#undefine sid
#undefine serial#
#accept sid prompt 'sid: '
#accept serial prompt 'serial#: '
#
#select 'SQL Currently Executing: ' 
#from   dual;
#
#select 
#   b.username, 
#   a.sql_text 
#from 
#   v\$sql a, 
#   v\$session b
#where 
#   b.sql_address = a.address and
#   b.sql_hash_value = a.hash_value and
#   b.sid = &sid and
#   b.serial# = '&serial';
#
#select 'Open Cursors:' 
#from   dual;
#
#select 
#   b.username, 
#   a.sql_text 
#from   
#   v\$open_cursor a, 
#   v\$session b
#where  
#   b.sql_address = a.address and
#   b.sql_hash_value = a.hash_value and
#   b.sid = &sid and
#   b.serial# = '&serial';
EOF

printf "done.\n"

exit 0