RMAN hot backup script

#! /bin/sh

#———————————–
# Available Options For Configurations Below
#———————————–
# ORACLE RELEASE options: [9i,10g]
# BACKUP MEDIA options: [DISK,SBT]
# BACKUP TYPE options:
# For 9i [FULL_UNCOMP]
# For 10g [FULL_UNCOMP,FULL_COMP,INC_MERGE]
# BACKUP_PARALLEL options: [1-16]
#

#———————————–
# Start of RMAN Configurable Section
#———————————–
export ORACLE_RELEASE=10g
export BACKUP_MEDIA=DISK
export BACKUP_TYPE=INC_MERGE
export BACKUP_RETENTION=1
export BACKUP_PARALLEL=4
export DATA_MAXPIECESIZE=5G
export ARCH_MAXPIECESIZE=3G
export P_DBAEMAIL=”abc@abc.com”
export DBAEMAIL=”abc@abc.com”
#export DBAPAGER=”abc@abc.com”
export TIMESTAMP=`date +%T-%m-%d-%Y`
#———————————–
# PATHS
export ORACLE_SID=ABC
export ORACLE_BASE=/oracle
export ORACLE_HOME=/oracle/BID/102_64
export BACKUP_DIR=/dbbackups/BID/backups
export BK_ARCHIVE_DIR=/dbbackups/BID/backups
export BK_CF_DIR=/dbbackups/BID/backups
export BK_SPFILE_DIR=/dbbackups/BID/backups
export LOG_DIR=/dbbackups/BID/logs
export LOG=${LOG_DIR}
LOG=${LOG}/${ORACLE_SID}_${BACKUP_TYPE}_${TIMESTAMP}.log
export TMPLOG=${LOG_DIR}/tmplog.$$

#———————————–
# CATALOG
#export CATALOG_CONN=${ORACLE_SID}/${ORACLE_SID}@catalog

#———————————–
# GENERIC ENVIRONMENTAL
export TMPDIR=/tmp
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib:lib
export LIBPATH=$ORACLE_HOME/lib:/usr/lib:lib
export SHLIB_PATH=$ORACLE_HOME/lib:/usr/lib:lib

#———————————–
# LOCK FILE
export LOCKFILE=${LOG_DIR}/${ORACLE_SID}_rman.lock

#———————————
# End of RMAN Configurable Section
#———————————

echo `date` “Starting $BACKUP_TYPE Backup of $ORACLE_SID to $BACKUP_MEDIA” > $LOG

if [ -f $LOCKFILE ]; then
echo `date` “The script is currently running. Exiting …” >> $LOG
else
echo “DO NOT delete this file. Used for RMAN locking” > $LOCKFILE
#***********************************************************************
if [ $ORACLE_RELEASE = 9i ]; then
#===================================================================
if [ $BACKUP_TYPE = FULL_UNCOMP ]; then

$ORACLE_HOME/bin/rman target / nocatalog log=$TMPLOG <> $LOG
fi
#***********************************************************************
elif [ $ORACLE_RELEASE = 10g ]; then
#===================================================================
if [ $BACKUP_TYPE = FULL_UNCOMP ]; then

$ORACLE_HOME/bin/rman target / nocatalog log=$TMPLOG <<EOF
sql "alter session set optimizer_mode=RULE";
run
{
CONFIGURE RETENTION POLICY TO REDUNDANCY ${BACKUP_RETENTION};
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '${ORACLE_HOME}/dbs/SNAPSHOT_${ORACLE_SID}_CTL';
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '${BACKUP_DIR}/ctlbkp_${ORACLE_SID}_%F.CTL';
CONFIGURE DEVICE TYPE $BACKUP_MEDIA BACKUP TYPE TO BACKUPSET PARALLELISM ${BACKUP_PARALLEL};
CONFIGURE CHANNEL 1 DEVICE TYPE DISK FORMAT '${BACKUP_DIR}/data_%d_%s_%T_%U_DB' maxpiecesize ${DATA_MAXPIECESIZE};
CONFIGURE CHANNEL 2 DEVICE TYPE DISK FORMAT '${BACKUP_DIR}/data_%d_%s_%T_%U_DB' maxpiecesize ${DATA_MAXPIECESIZE};
CONFIGURE CHANNEL 3 DEVICE TYPE DISK FORMAT '${BACKUP_DIR}/data_%d_%s_%T_%U_DB' maxpiecesize ${DATA_MAXPIECESIZE};
CONFIGURE CHANNEL 4 DEVICE TYPE DISK FORMAT '${BACKUP_DIR}/data_%d_%s_%T_%U_DB' maxpiecesize ${DATA_MAXPIECESIZE};
CONFIGURE CHANNEL 5 DEVICE TYPE DISK FORMAT '${BACKUP_DIR}/data_%d_%s_%T_%U_DB' maxpiecesize ${DATA_MAXPIECESIZE};
CONFIGURE CHANNEL 6 DEVICE TYPE DISK FORMAT '${BACKUP_DIR}/data_%d_%s_%T_%U_DB' maxpiecesize ${DATA_MAXPIECESIZE};
CONFIGURE CHANNEL 7 DEVICE TYPE DISK FORMAT '${BACKUP_DIR}/data_%d_%s_%T_%U_DB' maxpiecesize ${DATA_MAXPIECESIZE};
CONFIGURE CHANNEL 8 DEVICE TYPE DISK FORMAT '${BACKUP_DIR}/data_%d_%s_%T_%U_DB' maxpiecesize ${DATA_MAXPIECESIZE};
CONFIGURE CHANNEL 9 DEVICE TYPE DISK FORMAT '${BACKUP_DIR}/data_%d_%s_%T_%U_DB' maxpiecesize ${DATA_MAXPIECESIZE};
CONFIGURE CHANNEL 10 DEVICE TYPE DISK FORMAT '${BACKUP_DIR}/data_%d_%s_%T_%U_DB' maxpiecesize ${DATA_MAXPIECESIZE};
CONFIGURE CHANNEL 11 DEVICE TYPE DISK FORMAT '${BACKUP_DIR}/data_%d_%s_%T_%U_DB' maxpiecesize ${DATA_MAXPIECESIZE};
CONFIGURE CHANNEL 12 DEVICE TYPE DISK FORMAT '${BACKUP_DIR}/data_%d_%s_%T_%U_DB' maxpiecesize ${DATA_MAXPIECESIZE};
CONFIGURE CHANNEL 13 DEVICE TYPE DISK FORMAT '${BACKUP_DIR}/data_%d_%s_%T_%U_DB' maxpiecesize ${DATA_MAXPIECESIZE};
CONFIGURE CHANNEL 14 DEVICE TYPE DISK FORMAT '${BACKUP_DIR}/data_%d_%s_%T_%U_DB' maxpiecesize ${DATA_MAXPIECESIZE};
CONFIGURE CHANNEL 15 DEVICE TYPE DISK FORMAT '${BACKUP_DIR}/data_%d_%s_%T_%U_DB' maxpiecesize ${DATA_MAXPIECESIZE};
CONFIGURE CHANNEL 16 DEVICE TYPE DISK FORMAT '${BACKUP_DIR}/data_%d_%s_%T_%U_DB' maxpiecesize ${DATA_MAXPIECESIZE};
crosscheck backup;
crosscheck copy;
backup
tag = '${ORACLE_SID}_$BACKUP_TYPE'
database;
crosscheck archivelog all;
allocate channel a1 type disk maxpiecesize ${ARCH_MAXPIECESIZE};
allocate channel a2 type disk maxpiecesize ${ARCH_MAXPIECESIZE};
allocate channel a3 type disk maxpiecesize ${ARCH_MAXPIECESIZE};
allocate channel a4 type disk maxpiecesize ${ARCH_MAXPIECESIZE};
BACKUP ARCHIVELOG ALL format '${BK_ARCHIVE_DIR}/arch_%d_%s_%T_%U_DB' tag '${ORACLE_SID}_ARCH_BKUP' DELETE INPUT;
release channel a1;
release channel a2;
release channel a3;
release channel a4;
BACKUP CURRENT CONTROLFILE FORMAT '${BK_CF_DIR}/cf_%d_%s_%T_%U_DB' tag "${ORACLE_SID}_CTRL_FILE_BKUP";
BACKUP SPFILE FORMAT '${BK_SPFILE_DIR}/spfile_%d_%s_%T_%U_DB' tag "${ORACLE_SID}_SPFILE_BKUP";
sql "create pfile=''${BK_SPFILE_DIR}/pfile_${ORACLE_SID}_${TIMESTAMP}_DB.ora'' from spfile";
crosscheck backup;
delete noprompt obsolete;
delete noprompt expired backup;
report unrecoverable;
}
exit
EOF
#===================================================================
elif [ $BACKUP_TYPE = FULL_COMP ]; then

$ORACLE_HOME/bin/rman target / nocatalog log=$TMPLOG <<EOF
sql "alter session set optimizer_mode=RULE";
run
{
CONFIGURE RETENTION POLICY TO REDUNDANCY ${BACKUP_RETENTION};
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '${ORACLE_HOME}/dbs/SNAPSHOT_${ORACLE_SID}_CTL';
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '${BACKUP_DIR}/ctlbkp_${ORACLE_SID}_%F.CTL';
CONFIGURE DEVICE TYPE $BACKUP_MEDIA BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM ${BACKUP_PARALLEL};
CONFIGURE CHANNEL 1 DEVICE TYPE DISK FORMAT '${BACKUP_DIR}/data_%d_%s_%T_%U_DB' maxpiecesize ${DATA_MAXPIECESIZE};
CONFIGURE CHANNEL 2 DEVICE TYPE DISK FORMAT '${BACKUP_DIR}/data_%d_%s_%T_%U_DB' maxpiecesize ${DATA_MAXPIECESIZE};
CONFIGURE CHANNEL 3 DEVICE TYPE DISK FORMAT '${BACKUP_DIR}/data_%d_%s_%T_%U_DB' maxpiecesize ${DATA_MAXPIECESIZE};
CONFIGURE CHANNEL 4 DEVICE TYPE DISK FORMAT '${BACKUP_DIR}/data_%d_%s_%T_%U_DB' maxpiecesize ${DATA_MAXPIECESIZE};
CONFIGURE CHANNEL 5 DEVICE TYPE DISK FORMAT '${BACKUP_DIR}/data_%d_%s_%T_%U_DB' maxpiecesize ${DATA_MAXPIECESIZE};
CONFIGURE CHANNEL 6 DEVICE TYPE DISK FORMAT '${BACKUP_DIR}/data_%d_%s_%T_%U_DB' maxpiecesize ${DATA_MAXPIECESIZE};
CONFIGURE CHANNEL 7 DEVICE TYPE DISK FORMAT '${BACKUP_DIR}/data_%d_%s_%T_%U_DB' maxpiecesize ${DATA_MAXPIECESIZE};
CONFIGURE CHANNEL 8 DEVICE TYPE DISK FORMAT '${BACKUP_DIR}/data_%d_%s_%T_%U_DB' maxpiecesize ${DATA_MAXPIECESIZE};
CONFIGURE CHANNEL 9 DEVICE TYPE DISK FORMAT '${BACKUP_DIR}/data_%d_%s_%T_%U_DB' maxpiecesize ${DATA_MAXPIECESIZE};
CONFIGURE CHANNEL 10 DEVICE TYPE DISK FORMAT '${BACKUP_DIR}/data_%d_%s_%T_%U_DB' maxpiecesize ${DATA_MAXPIECESIZE};
CONFIGURE CHANNEL 11 DEVICE TYPE DISK FORMAT '${BACKUP_DIR}/data_%d_%s_%T_%U_DB' maxpiecesize ${DATA_MAXPIECESIZE};
CONFIGURE CHANNEL 12 DEVICE TYPE DISK FORMAT '${BACKUP_DIR}/data_%d_%s_%T_%U_DB' maxpiecesize ${DATA_MAXPIECESIZE};
CONFIGURE CHANNEL 13 DEVICE TYPE DISK FORMAT '${BACKUP_DIR}/data_%d_%s_%T_%U_DB' maxpiecesize ${DATA_MAXPIECESIZE};
CONFIGURE CHANNEL 14 DEVICE TYPE DISK FORMAT '${BACKUP_DIR}/data_%d_%s_%T_%U_DB' maxpiecesize ${DATA_MAXPIECESIZE};
CONFIGURE CHANNEL 15 DEVICE TYPE DISK FORMAT '${BACKUP_DIR}/data_%d_%s_%T_%U_DB' maxpiecesize ${DATA_MAXPIECESIZE};
CONFIGURE CHANNEL 16 DEVICE TYPE DISK FORMAT '${BACKUP_DIR}/data_%d_%s_%T_%U_DB' maxpiecesize ${DATA_MAXPIECESIZE};
crosscheck backup;
crosscheck copy;
backup
tag = '${ORACLE_SID}_$BACKUP_TYPE'
database;
crosscheck archivelog all;
allocate channel a1 type disk maxpiecesize ${ARCH_MAXPIECESIZE};
allocate channel a2 type disk maxpiecesize ${ARCH_MAXPIECESIZE};
allocate channel a3 type disk maxpiecesize ${ARCH_MAXPIECESIZE};
allocate channel a4 type disk maxpiecesize ${ARCH_MAXPIECESIZE};
BACKUP ARCHIVELOG ALL format '${BK_ARCHIVE_DIR}/arch_%d_%s_%T_%U_DB' tag '${ORACLE_SID}_ARCH_BKUP' DELETE INPUT;
release channel a1;
release channel a2;
release channel a3;
release channel a4;
BACKUP CURRENT CONTROLFILE FORMAT '${BK_CF_DIR}/cf_%d_%s_%T_%U_DB' tag "${ORACLE_SID}_CTRL_FILE_BKUP";
BACKUP SPFILE FORMAT '${BK_SPFILE_DIR}/spfile_%d_%s_%T_%U_DB' tag "${ORACLE_SID}_SPFILE_BKUP";
sql "create pfile=''${BK_SPFILE_DIR}/pfile_${ORACLE_SID}_${TIMESTAMP}_DB.ora'' from spfile";
crosscheck backup;
delete noprompt obsolete;
delete noprompt expired backup;
report unrecoverable;
}
exit
EOF
#===================================================================
elif [ $BACKUP_TYPE = INC_MERGE ]; then

$ORACLE_HOME/bin/rman target / nocatalog log=$TMPLOG <> $LOG
fi
#***********************************************************************
else
echo `date` “Oracle $ORACLE_RELEASE is not currently supported by this script.” >> $LOG
fi
#***********************************************************************
RC=$?
cat $TMPLOG >> $LOG
rm $LOCKFILE
echo `date` “Lock file removed.” >> $LOG
if [ $RC -ne “0” ]; then
echo `date` “RMAN $BACKUP_TYPE Of $ORACLE_SID To $BACKUP_MEDIA Failed.” >> $LOG
mailx -s “RMAN $BACKUP_TYPE Of $ORACLE_SID To $BACKUP_MEDIA Failed.” \
$DBAEMAIL,$DBAPAGER > $LOG
mailx -s “RMAN $BACKUP_TYPE Of $ORACLE_SID To $BACKUP_MEDIA Completed Successfully.” \
$P_DBAEMAIL < $LOG
find ${BK_SPFILE_DIR} -type f -name 'pfile_${ORACLE_SID}_*.ora' -mtime +${BACKUP_RETENTION} -exec /bin/rm -f {} \;
find ${LOG_DIR} -type f -name '*.log' -mtime +6 -exec /bin/rm -f {} \;
fi
rm $TMPLOG
fi

Advertisements

tablespace fragmentation

select
b.file_name, b.tablespace_name,
nvl(round(sqrt(max(a.blocks)/
sum(a.blocks))*(100/sqrt(sqrt(count(a.blocks)) )),2),0)
fragmentation_index,
decode(c.inc,null,’no’,’yes’) autoextend,
count (*) free_chunks,
decode (
round ((max (a.bytes) / 1024000), 2),
null, 0,
round ((max (a.bytes) / 1024000), 2)) largest_chunk
from
sys.dba_free_space a,
sys.dba_data_files b,
sys.filext$ c
 where
b.tablespace_name = a.tablespace_name (+) and
c.file# (+)= a.file_id and
b.file_id = a.file_id (+)
 group
by b.file_name,
decode(c.inc,null,’no’,’yes’),
b.tablespace_name
 order
by 5 desc, 1;

SGA /PGA size per session

SET LINESIZE 145
SET PAGESIZE 9999

COLUMN sid FORMAT 999 HEADING ‘SID’
COLUMN oracle_username FORMAT a12 HEADING ‘Oracle User’ JUSTIFY right
COLUMN os_username FORMAT a9 HEADING ‘O/S User’ JUSTIFY right
COLUMN session_program FORMAT a18 HEADING ‘Session Program’ TRUNC
COLUMN session_machine FORMAT a8 HEADING ‘Machine’ JUSTIFY right TRUNC
COLUMN session_pga_memory FORMAT 9,999,999,999 HEADING ‘PGA Memory’
COLUMN session_pga_memory_max FORMAT 9,999,999,999 HEADING ‘PGA Memory Max’
COLUMN session_uga_memory FORMAT 9,999,999,999 HEADING ‘UGA Memory’
COLUMN session_uga_memory_max FORMAT 9,999,999,999 HEADING ‘UGA Memory MAX’

SELECT
s.sid sid
, lpad(s.username,12) oracle_username
, lpad(s.osuser,9) os_username
, s.program session_program
, lpad(s.machine,8) session_machine
, (select ss.value from v$sesstat ss, v$statname sn
where ss.sid = s.sid and
sn.statistic# = ss.statistic# and
sn.name = ‘session pga memory’) session_pga_memory
, (select ss.value from v$sesstat ss, v$statname sn
where ss.sid = s.sid and
sn.statistic# = ss.statistic# and
sn.name = ‘session pga memory max’) session_pga_memory_max
, (select ss.value from v$sesstat ss, v$statname sn
where ss.sid = s.sid and
sn.statistic# = ss.statistic# and
sn.name = ‘session uga memory’) session_uga_memory
, (select ss.value from v$sesstat ss, v$statname sn
where ss.sid = s.sid and
sn.statistic# = ss.statistic# and
sn.name = ‘session uga memory max’) session_uga_memory_max
FROM
v$session s
ORDER BY session_pga_memory DESC
/

long running query

–Show queries in progress.
col opname for a25 trunc
col username for a15 trunc
col target for a20
col sid for 999999
col serial# for 999999
col %DONE for a8
select b.username,a.sid,b.opname,b.target,round(b.SOFAR*100 / b.TOTALWORK,0) || ‘%’ as “%DONE”,
b.TIME_REMAINING,to_char(b.start_time,’YYYY/MM/DD HH24:MI:SS’) START_TIME
from V$SESSION_LONGOPS b,V$SESSION a where a.sid=b.sid and TIME_REMAINING 0 order by b.SOFAR/b.TOTALWORK;

–variation on sql above
col opname for a25
col progress for a15
col progress for a8
select a.sid,b.username,b.opname,round(b.SOFAR*100 / b.TOTALWORK,0) || ‘%’ as progress,
b.TIME_REMAINING,b.target from V$SESSION_LONGOPS b,V$SESSION a where a.sid=b.sid and TIME_REMAINING 0 order by 5;

–show long operations.
column c4 heading “%age Completed” format 99,99.99
column TotalWork format a15
column ELAPSED_SECONDS format 99999
select username,sofar,totalwork||units “TotalWork”,(sofar/totalwork)*100 c4,to_char(start_time,’mm-dd-yyyy hh24:mi’) “Start_Time”,
elapsed_seconds,sql_address from V$SESSION_LONGOPS where sofar/totalwork < 1 order by elapsed_seconds;

–show long operations. it shows jobs that have finished too
set linesize 150
col username for a20
col target for a35
col opname for a25 trunc
col sid for 999999
col serial# for 999999
col sofar for 9999999
select a.username,b.sid,b.serial#,b.opname,b.target,b.sofar,b.totalwork,to_char(b.start_time,'HH24:MM:SS') START_TIME,
to_char(SYSDATE,'HH24:MM:SS') CURR_TIME from V$SESSION_LONGOPS b, V$SESSION a where a.sid=b.sid order by 8,9;

log size and logs generated per day

–show current redo logs, number allowed, their size, average switches per hour, switches per hour by day. See showRedo.sql
prompt
prompt >>> Total allowed redo log files <<>> Current redo logs files <<>> If greater than 4 per hour, increase size of logs << sysdate -10
group by to_char(first_time,’DDMonYY’) order by to_date(to_char(first_time,’DDMonYY’),’DDMonYY’);

Full table scan Sql statements

select
sql_text,
table_owner,
table_name,
table_type,
size_kb,
–statement_count,
–reference_count,
executions–,
–executions * reference_count total_scans
from
(select
c.sql_text sql_text,
a.object_owner table_owner,
a.object_name table_name,
b.segment_type table_type,
b.bytes / 1024 size_kb,
sum(c.executions) executions–,
–count(distinct a.hash_value) statement_count,
–count(*) reference_count
from
sys.v_$sql_plan a,
dba_segments b,
sys.v_$sql c
where
a.object_owner (+) = b.owner
and
a.object_name (+) = b.segment_name
and
b.segment_type in (‘TABLE’, ‘TABLE_PARTITION’)
and
a.operation like ‘%TABLE%’
and
a.options = ‘FULL’
and
a.hash_value = c.hash_value
and
b.bytes / 1024 > 1024
and
a.object_owner in (‘&&v_AppOwner’)
group by
c.sql_text,
a.object_owner,
a.object_name,
a.operation,
b.bytes / 1024,
b.segment_type
order by
5 desc,
2,
3);

Estimate Shared pool size

SAMPLE OUTPUT Go to Script
——————————————————————————–

Class Total Size Max Size Description
——– ———- ———- ———————————————————-
R-free 246440 19612 Free memory in the reserved pool
R-freea 552964 19612 Freeable chunk in the reserved pool
R-perm 379920 72264 Permanent chunk in the reserved pool
free 140476 3700 Free chunk of shared memory
freeabl 13772032 124152 Freeable, but not flushable, chunk of shared memory
perm 18750480 4063044 Permanently allocated, nonfreeable chunk of shared memory
recr 3906100 8776 Allocated, fluashable (recreatable) chunk of shared memory
Total 37748412 4063044 Total Size of memory

8 rows selected.

Elapsed: 00:00:00.18

BUCKET_NO CHUNK_SIZE CHUNKS FRBL_KB FREE_KB AVAIL_KB SML_BYTE LRG_BYTE
——— —————— ——- ——- ——- ——– ——— ———
0 16 to 76 bytes 799 12 24 35
2 80 to 136 bytes 1643 141 14 154
3 144 to 268 bytes 529 85 25 110
4 272 to 524 bytes 1067 380 9 389
5 528 to 1036 bytes 2977 1746 32 1779
6 1 to 2 Kb 1324 1924 27 1951
7 2 to 4 Kb 1256 3679 6 3685
8 4 to 7 Kb 1175 4826 0 4826
9 9 to 10 Kb 2 19 0 19
10 19 to 20 Kb 17 328 0 328
11 42 to 42 Kb 2 86 0 86
12 104 to 121 Kb 2 225 0 225
99 reserved pool 88 0 241 781 20 19612

13 rows selected.

SCRIPT
——————————————————————————–

set timing on

— select count(*) “Total entries”,
— sum(ksmchsiz) “Total Size”,
— min(ksmchsiz) “Min Size”,
— max(ksmchsiz) “Max Size”,
— avg(ksmchsiz) “Avg Size”,
— stddev(ksmchsiz) “Stand. Dev.”
— from sys.x$ksmsp;

REM — *** Shared pool summary display
select nvl(ksmchcls,’Total’) “Class”,
sum(ksmchsiz) “Total Size”,
max(ksmchsiz) “Max Size”,
decode(ksmchcls,
‘recr’, ‘Allocated, fluashable (recreatable) chunk of shared memory’,
‘freeabl’, ‘Freeable, but not flushable, chunk of shared memory’,
‘free’, ‘Free chunk of shared memory’,
‘perm’, ‘Permanently allocated, nonfreeable chunk of shared memory’,
‘R-free’, ‘Free memory in the reserved pool’,
‘R-freea’, ‘Freeable chunk in the reserved pool’,
‘R-recr’, ‘Recreatable chunk in the reserved pool’,
‘R-perm’, ‘Permanent chunk in the reserved pool’,
”,’Total Size of memory’) “Description”
from SYS.X$KSMSP
group by rollup(ksmchcls);

REM — *** Shared pool freelist display
set pages 50000
COLUMN lrg_byte FORMAT 99999999
COLUMN sml_byte FORMAT 99999999
COLUMN avail_kb FORMAT 999999
COLUMN free_kb FORMAT 999999
COLUMN FRBL_KB FORMAT 999999
COLUMN chunks FORMAT 999999
COLUMN chunk_size FORMAT a18
COLUMN bucket_no FORMAT 999
select /*+ RULE */
BUCKET bucket_no,
CASE
— when 1/log(min(ksmchsiz),2) < 10
when min(ksmchsiz) < 1024
then min(ksmchsiz)||' to '||max(ksmchsiz)||' bytes'
— when 1/log(min(ksmchsiz),2) < 20
when min(ksmchsiz) < (1024*1024)
then trunc(min(ksmchsiz)/1024)||' to '||trunc(max(ksmchsiz)/1024)||' Kb'
else trunc(min(ksmchsiz)/1024/1024)||' to '||trunc(max(ksmchsiz)/1024/1024)||' Mb'
end chunk_size,
count(*) chunks,
sum(decode(ksmchcls,'freeabl',ksmchsiz,0))/1024 frbl_kb,
sum(decode(ksmchcls,'free',ksmchsiz,0))/1024 free_kb,
sum(ksmchsiz/1024) avail_kb,
null sml_byte,
null lrg_byte
from (
select ksmchsiz,
ksmchcls,
decode(sign(ksmchsiz-80),-1,0,trunc(1/log(greatest(ksmchsiz-15,2),2))-4) "BUCKET"
from SYS.X$KSMSP
where ksmchcls like 'free%'
)
group by BUCKET
union
select
99 bucket_no,
'reserved pool',
count(*) chunks,
nvl(sum(decode(ksmchcls,'R-freeabl',ksmchsiz,0)),0)/1024 frbl_kb,
nvl(sum(decode(ksmchcls,'R-free',ksmchsiz,0)),0)/1024 free_kb,
nvl(sum(ksmchsiz/1024),0) avail_kb,
nvl(min(ksmchsiz),0) sml_byte,
nvl(max(ksmchsiz),0) lrg_byte
from SYS.X$KSMSP
where ksmchcls like 'R-free%'
order by 1;