Performance Monitoring Scripts

=================================================================
==>CONTENTION(SCRIPTS TO CHECK FOR CONTENTION AT VARIOUS LEVELS)
=================================================================

1. Find the objects that are currently locked
-----------------------------------------------------
Description: Anything appear in the result will be a locked object

select o.object_name,l.oracle_username,l.os_user_name,l.session_id
,decode(l.locked_mode,2,'Row-S',3,'Row-X',4,'Share',5,'S/Row-X',6 ,'Exclusive','NULL')
from user_objects o , v$locked_object l
where o.object_id = l.object_id;

2. Monitor System wide wait event
------------------------------
Description: This script will display the waits on the instance since the instance has been created. Good place to look for system bottle necks

col event form A50
col Prev form 999
col Curr form 999
col Tot form 999
select
event,
sum(decode(wait_Time,0,0,1)) "Prev",
sum(decode(wait_Time,0,1,0)) "Curr",
count(*) "Tot"
from
v$session_Wait
group by event
order by 4

3. Rollback Segment Contention
----------------------------
Description: Display the contention in rollback segments

column "Ratio" format 99.99999
select name, waits, gets, waits/gets "Ratio"
from v$rollstat a, v$rollname b
where a.usn = b.usn;


=========================================
==>IO(IO RATE DISPLAY AND TUNING SCRIPTS)
=========================================

1. File I/O
------------
Description: Display I/O by File.

select
substr(a.file#,1,2) "#",
substr(a.name,1,30) "Name",
a.status,
a.bytes,
b.phyrds,
b.phywrts
from v$datafile a, v$filestat b
where a.file# = b.file#;

2.Monitor IO by Tablespace and Data File
-----------------------------------------
Description: Try to balance the I/O by mixing active and inactive data files / tablespaces on the same drive

column name format a18 heading 'Tablespace' jus cen
column file format a50 heading 'File Name' jus cen
column pbr format 99,999,999,990 heading 'Physical|Blocks|Read' jus cen
column pbw format 99,999,999,990 heading 'Physical |Blocks|Written' jus cen
column pyr format 99,999,999,990 heading 'Physical |Reads' jus cen
column pyw format 99,999,999,990 heading 'Physical|Writes' jus cen

ttitle center 'Disk I/O Activity by file' skip 2
compute sum of pyr on report
compute sum of pyw on report
compute sum of pbr on report
compute sum of pbw on report
break on report
select
df.tablespace_name name,
df.file_name "file",
f.phyrds pyr,
f.phyblkrd pbr,
f.phywrts pyw,
f.phyblkwrt pbw
from v$filestat f, dba_data_files df
where
f.file# = df.file_id
order by df.tablespace_name

3. Filestats Snapshot Script
-----------------------------
Description: This script takes a snapshot of v$filestats at the current time and saves it. It then waits 10 seconds and takes another snapshot and reports on the delta.

col name for a50
set linesize 132
set pages 666
-- drop temporary table
drop table jh$filestats;
create table jh$filestats as
select file#, PHYBLKRD, PHYBLKWRT
from v$filestat;
prompt Waiting......
exec dbms_lock.sleep(10);

prompt NOTE: Only the top 10 files...
select * from (
select df.name, fs.phyblkrd - t.phyblkrd "Reads",
fs.PHYBLKWRT - t.PHYBLKWRT "Writes",
(fs.PHYBLKRD+fs.PHYBLKWRT) - (t.PHYBLKRD+t.PHYBLKWRT) "Total IO"
from v$filestat fs, v$datafile df, jh$filestats t
where df.file# = fs.file#
and t.file# = fs.file#
and (fs.PHYBLKRD+fs.PHYBLKWRT) - (t.PHYBLKRD+t.PHYBLKWRT) > 0
order by "Total IO" desc )
where rownum <= 10
/

=====================================================
==>INDEXING(Oracle Index Analysis and Tuning Scripts)
=====================================================

1.Find the Unindexed Tables and the Columns for Constraints in your database
----------------------------------------------------------------------------
Description: This script will display the table name and column name of the tables that need to be indexed.

COL table_name format A20 head 'TABLE_NAME'
COL constraint_name format A20 head 'CONSTRAINT_NAME'
COL table2 format A20 head 'TABLE_TO_BE_INDEXED'
COL column_name format A20 head 'COLUMN_TO_BE_INDEXED'

SET linesize 100

SELECT t.table_name,c.constraint_name,c.table_name table2
,acc.column_name
FROM all_constraints t, all_constraints c
, all_cons_columns acc
WHERE c.r_constraint_name = t.constraint_name
AND c.table_name =acc.table_name
AND c.constraint_name = acc.constraint_name
AND NOT EXISTS ( SELECT '1' FROM all_ind_columns aid
WHERE aid.table_name = acc.table_name
AND aid.column_name = acc.column_name)
ORDER BY c.table_name;

2. Table Index Report
---------------------
Description: Table to Index Cross Reference Report

ttitle left 'Date: ' format a10 cur_date -
center 'Table/Index Cross Reference for ' format a8 sql.user -
right 'Page:' format 999 sql.pno
set heading on
set pagesize 56
set linesize 100
set newpage 0
column uniqueness format a9 heading 'Unique?'
column index_name format a30 heading 'Index|Name'
column table_name format a24 heading 'Table|Name'
column column_name format a24 heading 'Column|Name'
column table_type format a8 heading 'Index|Type'
break on table_name skip 1 on table_type on index_name on uniqueness
select user_indexes.table_name, user_indexes.index_name,
uniqueness, column_name
from user_ind_columns, user_indexes
where user_ind_columns.index_name = user_indexes.index_name
and user_ind_columns.table_name = user_indexes.table_name
order by user_indexes.table_type, user_indexes.table_name,
user_indexes.index_name, column_position
;
clear breaks
clear columns
ttitle off

3. Foreign Keys without Indexes
-------------------------------
Description: This script lists foreign keys that are missing indexes on the foreign key columns in the child table. If the index is not in place, share lock problems may occur on the parent table.

ttitle 'Foreign Keys with Indexes Missing on Child Table'
SELECT acc.owner||'-> '||acc.constraint_name||'('||acc.column_name
||'['||acc.position||'])'||' ***** Missing Index' "Missing Index"
FROM all_cons_columns acc, all_constraints ac
WHERE ac.constraint_name = acc.constraint_name
AND ac.constraint_type = 'R'
and acc.owner not in ('SYS','SYSTEM')
AND (acc.owner, acc.table_name, acc.column_name, acc.position)
IN
(SELECT acc.owner, acc.table_name, acc.column_name, acc.position
FROM all_cons_columns acc, all_constraints ac
WHERE ac.constraint_name = acc.constraint_name
AND ac.constraint_type = 'R'
MINUS
SELECT table_owner, table_name, column_name, column_position
FROM all_ind_columns)
ORDER BY acc.owner, acc.constraint_name,
acc.column_name, acc.position;
ttitle off

===============================================================================
==>Memory Tuning (Scripts to help you tune SGA, Library Cache, Data Cache etc.)
===============================================================================

1. Buffer Cache Hit Ratio
--------------------------
Description - Calculate buffer cache hit ratio in the database. Make sure it is more than 80 for an oltp environment and 99 is the best value

column "logical_reads" format 99,999,999,999
column "phys_reads" format 999,999,999
column "phy_writes" format 999,999,999
select a.value + b.value "logical_reads",
c.value "phys_reads",
round(100 * ((a.value+b.value)-c.value) /
(a.value+b.value))
"BUFFER HIT RATIO"
from v$sysstat a, v$sysstat b, v$sysstat c
where
a.statistic# = 38
and
b.statistic# = 39
and
c.statistic# = 40;

2. Calculate Dictionary Cache Ratio
-----------------------------------
Description: Increase Shared pool size to reach a 90% hit ratio on Dictionary Cache. Entries for dc_table_grants, d_user_grants, and dc_users should be under 5% each in the MISS RATE % column

select
parameter,
gets,
Getmisses ,
getmisses/(gets+getmisses)*100 "miss ratio",
(1-(sum(getmisses)/ (sum(gets)+sum(getmisses))))*100 "Hit ratio"
from v$rowcache
where gets+getmisses <>0
group by parameter, gets, getmisses ;

3. Calculate Individual Cache Hit Ratios
----------------------------------------
Description: Reduce the Reloads and try to increase the hit ratios to above 85%

ttitle center 'LIBRARY CACHE STATS' skip 2
column namespace format a8 heading 'Library'
column gets format 9,999,990 heading 'GETS'
column gethits format 9,999,990 heading 'GETHITS'
column gethitratio format 99.90 heading 'GET|HIT|RATIO'
column pins format 999,999,990 heading 'PINS'
column pinhits format 999,999,990 heading 'PINHITS'
column pinhitratio format 99.90 heading 'PIN|HIT|RATIO'
column reloads format 999,990 heading 'RELOADS'
compute sum of gets on report
compute sum of gethits on report
compute sum of pins on report
compute sum of pinhits on report
compute sum of reloads on report
break on report
select
namespace,gets,gethits,gethitratio,pins,pinhits,
pinhitratio, reloads
from v$librarycache
where gets+gethits+pins+pinhits>0;

4. Calculate the Library Cache Ratio for the Whole system
---------------------------------------------------------
Description: This should be near 0. If the Ratio is larger than 1% then increase the SHARED_POOL_SIZE.

column libcache format 99.99 heading 'Percentage' jus cen
select sum(pins) "Total Pins", sum(reloads) "Total Reloads",
sum(reloads)/sum(pins) *100 libcache
from v$librarycache

5. Calculating the hit ratio for multiple pools
-----------------------------------------------
Description: To run this script you must get connect as user sys

select name,
1 - ( physical_reads / ( db_block_gets +
consistent_gets)) "HIT RATIO"
from sys.v$buffer_pool_statistics
where db_block_gets + consistent_gets > 0

6. Check Free Shared pool and flush
-----------------------------------
Description: This script check the free space in the shared pool and when the size is small execute an alter system flush shared pool statement.

CREATE OR REPLACE PROCEDURE flush_it ( pct_full IN NUMBER) AS
--
-- Cursor definitions
--
CURSOR get_share IS
SELECT SUM(sharable_mem) FROM
sql_summary;
--
CURSOR get_var IS
SELECT value FROM v$sga WHERE name LIKE 'Var%';
--
CURSOR get_time IS
SELECT sysdate FROM dual;
--
-- Variable definitions
--
todays_date DATE;
mem_ratio NUMBER;
share_mem NUMBER;
variable_mem NUMBER;
cur INTEGER;
sql_com VARCHAR2(60);
row_proc NUMBER;
--
-- Procedure Body
--
BEGIN
OPEN get_share;
OPEN get_var;
FETCH get_share INTO share_mem;
dbms_output.put_line('share_mem: '||to_char(share_mem));
FETCH get_var INTO variable_mem;
dbms_output.put_line('variable_mem: '||to_char(variable_mem));
mem_ratio:=share_mem/variable_mem;
dbms_output.put_line(to_char(mem_ratio));
IF mem_ratio>(pct_full/100) THEN
cur:=dbms_sql.open_cursor;
sql_com:='ALTER SYSTEM FLUSH SHARED_POOL';
dbms_sql.parse(cur,sql_com,dbms_sql.v7);
row_proc:=dbms_sql.execute(cur);
dbms_sql.close_cursor(cur);
OPEN get_time;
FETCH get_time INTO todays_date;
INSERT INTO dba_running_stats VALUES
(
'Flush of Shared Pool',1,35,todays_date,0
);
COMMIT;
END IF;
END;

7. Check Session Level Hit Ratio
--------------------------------
Description: The Hit Ratio should be higher than 90%

select Username,
OSUSER,
Consistent_Gets,
Block_Gets,
Physical_Reads,
100*( Consistent_Gets + Block_Gets - Physical_Reads)/
( Consistent_Gets + Block_Gets ) "Hit Ratio %"
from V$SESSION,V$SESS_IO
where V$SESSION.SID = V$SESS_IO.SID
and ( Consistent_Gets + Block_Gets )>0
and username is not null
order by Username,"Hit Ratio %";


8. Copying the data from one database to another
------------------------------------------------
- If you want to copy the data from one database to another having many tables and constraints,all you need is this script which will first disable all the constraints then delete the prior data and then finaly copy the data from any remote database and finaly it enables all the constraints on the table of that database.......all you need is to have a table having names of all the tables and the corresponding constraints

CREATE OR REPLACE procedure pre_tra
as
cursor cconstraints is select table_name, constraint_name, status from all_constraints where owner='owner name';

c2 cconstraints%rowtype;
mytab varchar2(200);
mytab1 varchar2(200);
mytab3 varchar2(200);
mytab4 varchar2(200);
retrycounter number;
counter number;
cstatus varchar2(20);
enableerrorfound boolean:=true;
--MAX_TRY number:=10000;
begin
begin
open cconstraints;
loop

fetch cconstraints into c2;
exit when cconstraints%notfound;
mytab := c2.table_name;
mytab1 := c2.constraint_name;
mytab3 :='alter table ' || mytab || ' disable constraint ' || mytab1;
execute immediate mytab3;

end loop;
close cconstraints;



open cconstraints;
loop
fetch cconstraints into c2;
exit when cconstraints%notfound;
mytab :=c2.table_name;
execute immediate 'delete ' || mytab;


end loop;
close cconstraints;
end;


open cconstraints;
loop
fetch cconstraints into c2;
exit when cconstraints%notfound;
mytab :=c2.table_name;
mytab4 :='DATABASENAME';
execute immediate 'insert into ' || mytab || ' (select * from '|| mytab || '@' || mytab4 || ')';
DBMS_OUTPUT.PUT_LINE(mytab);
commit;
DBMS_OUTPUT.PUT_LINE(mytab);
end loop;
close cconstraints;


begin
DBMS_OUTPUT.PUT_LINE('Starting enable script.....');

RETRYCOUNTER := 3;

WHILE RETRYCOUNTER > 0
loop
open cconstraints;

loop
fetch cconstraints into c2;
exit when cconstraints%notfound;

DBMS_OUTPUT.PUT_LINE('retrycounter==>'||mytab);
mytab := c2.table_name;
mytab1 := c2.constraint_name;
--cstatus:= c2.status;
--if cstatus = 'DISABLED' then
mytab3 :='alter table ' || mytab || ' enable constraint ' || mytab1;
DBMS_OUTPUT.PUT_LINE('Q' || mytab3);
DBMS_OUTPUT.PUT_LINE(retrycounter);
--enableerrorfound:=false;
BEGIN
execute immediate mytab3;
EXCEPTION
WHEN OTHERS THEN
enableerrorfound:=true;
END;
--end if;



end loop;
RETRYCOUNTER := RETRYCOUNTER - 1;
DBMS_OUTPUT.PUT_LINE(retrycounter);
close cconstraints;
end loop;

DBMS_OUTPUT.PUT_LINE(retrycounter);

IF (enableerrorfound) THEN
DBMS_OUTPUT.PUT_LINE('enableerrorfound==> TRUE');
else
DBMS_OUTPUT.PUT_LINE('enableerrorfound==> FALSE');
end if;
end;

end pre_tra;
/


9. List Session Specific Memory
-------------------------------
Description: List the UGA and PGA used by each session on the server

column name format a25
column total format 999 heading 'Cnt'
column bytes format 9999,999,999 heading 'Total Bytes'
column avg format 99,999,999 heading 'Avg Bytes'
column min format 99,999,999 heading 'Min Bytes'
column max format 9999,999,999 heading 'Max Bytes'
ttitle 'PGA = dedicated server processes - UGA = Client machine process'


compute sum of minmem on report
compute sum of maxmem on report
break on report

select se.sid,n.name,
max(se.value) maxmem
from v$sesstat se,
v$statname n
where n.statistic# = se.statistic#
and n.name in ('session pga memory','session pga memory max',
'session uga memory','session uga memory max')
group by n.name,se.sid
order by 3
/


10. List the size of Stored Procedures and use it to tune Shared Pool
---------------------------------------------------------------------
Description: This script lists the size of stored objects

column num_instances heading "Num" format 999
column type heading "Object Type" format a12
column source_size heading "Source" format 99,999,999
column parsed_size heading "Parsed" format 99,999,999
column code_size heading "Code" format 99,999,999
column error_size heading "Errors" format 999,999
column size_required heading "Total" format 999,999,999
compute sum of size_required on report

select count(name) num_instances
,type
,sum(source_size) source_size
,sum(parsed_size) parsed_size
,sum(code_size) code_size
,sum(error_size) error_size
,sum(source_size)
+sum(parsed_size)
+sum(code_size)
+sum(error_size) size_required
from dba_object_size
group by type
order by 2
/

11. Redo Latch Contention Monitor
---------------------------------
Description: Try to reduce the contention by reducing all the ratios to be less than 1

SET feedback OFF
COLUMN name FORMAT a15
COLUMN gets FORMAT 99999999
COLUMN misses FORMAT 999999
COLUMN immediate_gets FORMAT 99999999 HEADING 'IMM_GETS'
COLUMN immediate_misses FORMAT 99999999 HEADING 'IMM_MISSES'
PROMPT Examining Contention for Redo Log Buffer Latches...
PROMPT ----------------------------------------------------

SELECT name, gets, misses, immediate_gets, immediate_misses,
Decode(gets,0,0,misses/gets*100) ratio1,
Decode(immediate_gets+immediate_misses,0,0,
immediate_misses/(immediate_gets+immediate_misses)*100) ratio2
FROM v$latch WHERE name IN ('redo allocation', 'redo copy');


12. Sorts Monitoring Scripts
----------------------------
Description: Monitor the sorts in memory vs disk. Try to keep the disk/memory ratio to less than .10 by increasing the sort_area_size

SET HEADING OFF
SET FEEDBACK OFF
COLUMN name FORMAT a30
COLUMN value FORMAT 99999990

SELECT name, value FROM v$sysstat
WHERE name IN ('sorts (memory)', 'sorts (disk)');


13. Table Shared Mem Usage Report
---------------------------------
Description: Table Shared Memory Usage Report

set termout off feedback off echo off pagesize 59
set linesize 79 newpage 0 space 1 escape off
col iname noprint new_val instance
col today noprint new_val datetime
select name iname from v$database;
select to_char(sysdate, 'Mon DD, YYYY HH24:MI') today from dual;
btitle ce '======================================================' skip 1 -
le 'table_usage.sql' col 70 'Page: 'FORMAT 999 sql.pno
ttitle center 'Database: ' instance ' - Table Usage Report' -
skip 1 col 62 datetime skip 2
set pagesize 60
col name format A28 heading "TABLE NAME"
set termout off feedback off echo off pagesize 59
set linesize 79 newpage 0 space 1 escape off
col iname noprint new_val instance
col today noprint new_val datetime
select name iname from v$database;
select to_char(sysdate, 'Mon DD, YYYY HH24:MI') today from dual;
btitle ce '======================================================' skip 1 -
le 'table_usage.sql' col 70 'Page: 'FORMAT 999 sql.pno
ttitle center 'Database: ' instance ' - Table Usage Report' -
skip 1 col 62 datetime skip 2
set pagesize 60
col name format A28 heading "TABLE NAME"
col tablespace format A10
col KB format 99,999,999
col sharable_mem format 99,990 heading "SHARE MEM"
col loads format 9,990
col executions format 999,990
col own noprint
break on tablespace
spool table_usage.txt
select c.loads, c.executions, c.owner||'.'||c.name name,
t.tablespace_name tablespace, s.bytes/1024 KB, c.sharable_mem
from dba_tables t,
dba_segments s,
v$db_object_cache c
where c.type = 'TABLE'
and c.executions > 0
and c.name = t.table_name
and c.owner = t.owner
and c.name = s.segment_name
and c.owner = s.owner
order by 4, 1 desc, 2 desc, 3
/
spool off;
exit;


14. TO MEASURE THE BUFFERCACHE HIT RATIO FOR USER WISE
------------------------------------------------------
Description: When all application users are running in the server then we have to see the bufferhit ratio of all application users so that we can measure individuals performance.

select username, (1-(physical_reads/(consistent_gets+block_gets)))*100 "Buffer Cache Hit Ratio"
from v$session se, v$sess_io si
where se.sid = si.sid
and (consistent_gets+block_gets) > 0
and username is not null
order by username
/

15. Transaction Processing Rate
-------------------------------
Description: Computer Transaction Processing rate to get a better idea on sizing memory objects

SELECT SUM(s.value/
(86400*(SYSDATE - TO_DATE(i.VALUE,'J')))) "tps"
FROM V$SYSSTAT s, V$INSTANCE i
WHERE s.NAME in ('user commits','transaction rollbacks')
AND i.KEY = 'STARTUP TIME - JULIAN';

===============================================================================
==>Rollback (Rollback segment tuning and sizing scripts)
===============================================================================

1. Active Transactions in Rollback Segments
-------------------------------------------
Description: This script will display the active user and the rollback segment being used in the database

column rr heading 'RB Segment' format a18
column us heading 'Username' format a15
column os heading 'OS User' format a10
column te heading 'Terminal' format a10

SELECT r.name rr,
nvl(s.username,'no transaction') us,
s.osuser os,
s.terminal te
FROM
v$lock l,
v$session s,
v$rollname r
WHERE
l.sid = s.sid(+) AND
trunc(l.id1/65536) = r.usn AND
l.type = 'TX' AND
l.lmode = 6
ORDER BY r.name
/

2. Rollback Segment Wrap Time Monitoring Script
-----------------------------------------------
Description: This script reports how many hours it has been since the rollback segments wrapped

select n.name,
round(24*(sysdate-to_date(i1.value||' '||i2.value,'j SSSSS')) /
(s.writes/s.rssize),1) "Hours"
from v$instance i1,
v$instance i2,
v$rollname n,
v$rollstat s
where
i1.key = 'STARTUP TIME - JULIAN'
and i2.key = 'STARTUP TIME - SECONDS'
and n.usn = s.usn
and s.status = 'ONLINE'


3. Rollback Statistics
----------------------
Description: Gives lots of usefull easy to read info on how your RBS are performing. Needs 132 char display.

col name for a7
col xacts for 9990 head "Actv|Trans"
col InitExt for 990.00 head "Init|Ext|(Mb)"
col NextExt for 990.00 head "Next|Ext|(Mb)"
col MinExt for 99 head "Min|Ext"
col MaxExt for 999 head "Max|Ext"
col optsize for 9990.00 head "Optimal|Size|(Mb)"
col rssize for 9990.00 head "Curr|Size|(Mb)"
col hwmsize for 9990.00 head "High|Water|Mark|(Mb)"
col wraps for 999 head "W|R|A|P|S"
col extends for 990 head "E|X|T|E|N|D|S"
col shrinks for 990 head "S|H|R|I|N|K|S"
col aveshrink for 990.00 head "AVG|Shrink|(Mb)"
col gets head "Header|Gets"
col waits for 99990 head "Header|Waits"
col writes for 999,999,990 head "Total|Writes|Since|Startup|(Kb)"
col wpg for 9990 head "AVG|Writes|Per|HedGet|(bytes)"
set lines 132 pages 40 feed off
break on report
compute sum of gets on report
compute sum of waits on report
compute avg of aveshrink on report
compute avg of wpg on report

select name,
XACTS,
initial_extent/1048576 InitExt,
next_extent/1048576 NextExt,
min_extents MinExt,
max_extents MaxExt,
optsize/1048576 optsize,
RSSIZE/1048576 rssize,
HWMSIZE/1048576 hwmsize,
wraps,
extends,
shrinks,
aveshrink/1048576 aveshrink,
gets,
waits,
writes/1024 writes,
writes/gets wpg
from v$rollstat,v$rollname,dba_rollback_segs
where v$rollstat.usn=v$rollname.usn
and dba_rollback_segs.segment_id=v$rollname.usn
order by name
/


===========================================
==>SQL (SQL AND APPLICATION TUNING SCRIPTS)
===========================================

1. Who is currently running what SQL
------------------------------------
Description: This script will show you the user's OS name, Username in the database and the SQL Text they are running

SELECT osuser, username, sql_text
from v$session a, v$sqltext b
where a.sql_address =b.address
order by address, piece

2. Auto-Changing SQL Prompt to the user and database connected
---------------------------------------------------------------
Description: When you connect to sqlplus, you see the the following sql prompt SQL>
By using this sql in the glogin.sql, you will see a prompt similar to the following: SAMI@RACDB1>

The following code works on Oracle 8i (8.1.5, 8.1.6, 8.1.7).
You have to insert the following line of code in glogin.sql which is usually found in
$ORACLE_HOME/sqlplus/admin

set termout off
set echo off
define X=NotConnected
define Y=DBNAME

Column Usr New_Value X
Column DBName New_Value Y


Select SYS_CONTEXT('USERENV','SESSION_USER' ) Usr From Dual;

--- The following does not work in 8.1.5 but works in 8.1.6 or above
---Select SYS_CONTEXT('USERENV','DB_NAME') DBNAME From Dual;

--- If you are using 8.1.5, use this .
Select Global_Name DBNAME from Global_Name;

set termout on
set sqlprompt '&X@&Y> '

Please note that this method will work only when you make a new sql plus session because when you make a new sql plus session, then only glogin.sql is executed.


3.Capture application's sql in N tier evironment
------------------------------------------------
Description: In N tier structure it is really hard to track user session specially in a web enviroment, All users connect to database using the same application , so all requests are going to database on behalf of one oracle user. then how can you track actions of one ENDUSER (application user).

In the v$session, there are serval columns you can use to distinguish ENDUSER such as Module, Action and Client_info.

for example, you can use
dbms_application_info.set_client_info('client info')
to set client info accroding to user's ip or loginname in your application, then it is easy to find out what sql statement was execute by this client by using the following script

select s.sid, s.username, s.program, oc.sql_text
from v$session s, v$open_cursor oc
where s.saddr=oc.saddr and s.sid=oc.sid
and s.client_info='client_info';

4. SQL trace and TKPROF in SQLPLUS
-----------------------------------
Description: Purpose: To read raw session trace file with option to format trace file with TKPROF and read it in SQLPLUS Session

#!/bin/sh
#################################################################
#################################################################
## ScriptName:trace ##
## Purpose: To read raw session trace file with option to ##
## format trace file with TKPROF and read it in SQLPLUS Session##
## You must set TRACE_DIR and ORACLE_SID in ##
## this script Put trace in < $HOME/bin > make it executable ##
## and run it from sqlplus session ##
## Example: SQL> ! trace ##
## Note:You Must Run this script from your sqlplus session ##
#################################################################
#################################################################
clear
n1=`ps |grep sqlplus|awk '{print $1}' `
n2=`ps -ef|grep $n1|grep oracle"$ORACLE_SID"|awk '{print $2}'`
TRACE_DIR=/u01/app/oracle/admin/$ORACLE_SID/udump

echo " 1.Do you want to view raw trace file"
echo " 2.Do you want to format trace file and view it"
read choice
if [ "$choice" = "1" ];
then cat $TRACE_DIR/"$ORACLE_SID"_ora_$n2.trc|more
else
if [ "$choice" = "2" ];
then
tkprof $TRACE_DIR/"$ORACLE_SID"_ora_$n2.trc $TRACE_DIR/trace_session_$n2
clear
echo "Your TKPROF formated file name is $TRACE_DIR/trace_session_$n2.prf "
echo " Do you want to read it now y/n "
read ans
if [ "$ans" = "y" ];
then
cat $TRACE_DIR/trace_session_$n2.prf|more
else
if [ "$ans" = "n" ];
then
echo "Happy Hunting Perfomance Problems"
fi
fi
fi
fi


=============================================
==>OTHER SCRIPTS IN THIS PERF/TUNING CATEGORY
=============================================

1. Find Full Table Scans
------------------------
Description: The output of this script will display all sql statements in the shared pool that are doing full table scans.

--run this as sys in SQL worksheet
create table full_sql (sql_text varchar2(1000), executions number);
create or replace procedure p_findfullsql as

v_csr number;
v_rc number;
v_string varchar2(2000);

v_count number;


cursor c1 is select sql_text,executions from v$sqlarea where lower(sql_text) like '%select%';

begin

for x1 in c1 loop

delete from plan_table ;
Begin
v_Csr := DBMS_SQL.OPEN_CURSOR;
v_string := 'explain plan for ' ;
v_string := v_string||x1.sql_text ;
DBMS_SQL.PARSE(v_csr, v_string, DBMS_SQL.V7);
v_rc := DBMS_SQL.EXECUTE(v_csr);
DBMS_SQL.CLOSE_CURSOR(v_csr);
Exception
when others then
null;
End ;

select count(*) into v_count from plan_table where options like '%FULL%' and operation like '%TABLE%' ;
if v_count > 0 then
insert into full_sql(sql_text,executions) values (x1.sql_text, x1.executions) ;
end if;
end loop ;
commit;
end ;
/
execute p_findfullsql ;
select * from full_sql;
drop table full_sql;


2. Identify top 20 Oracle Processes in Unix
-------------------------------------------
Description: Find the top 20 longest running processes in unix. Useful for high CPU bound systems with large number of users. Script also identifies processes without a oracle session.

#!/bin/ksh
#
# Find Highest CPU used Oracle processes and get the Username and SID from oracle
# Only 3 character SIDNAME is displayed - Adjust the script according to your need.
#
date
echo " Top 20 CPU Utilized Session from `hostname`"
echo " ============================================"
echo "O/S Oracle Session Session Serial UNIX Login Ora CPU Time"
echo "ID User ID Status ID No ID MMDD:HHMISS SID Used"
echo "-------- ----------- -------- ------- ------- ------- ----------- --- --------"
ps -ef|grep LOCAL|cut -c1-15,42-79|sort -rn +2 | head -20 | while read LINE
do
SIDNAME=`echo $LINE | awk '{ print $4 }' | cut -c7-14`
CPUTIME=`echo $LINE | awk '{ print $3 }'`
UNIXPID=`echo $LINE | awk '{ print $2 }'`
#echo $SIDNAME $CPUTIME $UNIXPID
export ORACLE_SID=$SIDNAME
SIDNAME=`echo $ORACLE_SID | cut -c4-6`
export ORACLE_HOME=`/dba_script/bin/find_ohome.sh ${ORACLE_SID}`
export SHLIB_PATH=$ORACLE_HOME/lib:/usr/lib
export TMPDIR=/tmp
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export SQLPLUS="$ORACLE_HOME/bin/sqlplus -s / "
$SQLPLUS >> $wlogfile < set pages 0 lines 80 trims on echo off verify off pau off
column pu format a8 heading 'O/S|ID' justify left
column su format a11 heading 'Oracle|User ID' justify left
column stat format a8 heading 'Session|Status' justify left
column ssid format 999999 heading 'Session|ID' justify right
column sser format 999999 heading 'Serial|No' justify right
column spid format 999999 heading 'UNIX|ID' justify right
column ltime format a11 heading 'Login|Time' justify right
select p.username pu,
s.username su,
s.status stat,
s.sid ssid,
s.serial# sser,
lpad(p.spid,7) spid,
to_char(s.logon_time, 'MMDD:HH24MISS') ltime,
'$SIDNAME $CPUTIME'
from v\$process p,
v\$session s
where p.addr=s.paddr
and p.spid=$UNIXPID
union all
select a.username, 'Kill Me', 'NoOracle', a.pid, a.serial#,
lpad(a.spid,7) spid, 'KILL UNIXID', '$SIDNAME $CPUTIME'
from v\$process a
where a.spid = $UNIXPID
and not exists (select 1 from v\$session s
where a.addr=s.paddr);
EOF
done
echo "-------- ----------- -------- ------- ------- ------- ----------- --- --------"
date
#
# End of Script

3. Monitor and Verify Deadlocks and Waiting Sessions
----------------------------------------------------
Description: Monitor and Verify DEAD Locks: Holding and Waiting Sessions

Script: waiters.sql

set lines 80 echo on ver off timing on term on pages 60 feed on head on
spool DEAD_LOCK_WAITERS.LST

col " " for A25
col "Holding Session Info" for A25
col "Waiting Session Info" for A25

select --+ ORDERED
'Session ID' || CHR(10) ||
'Mode Held' || CHR(10) ||
'Lock Type' || CHR(10) ||
'Mode Requested' || CHR(10) ||
'Lock ID 1' || CHR(10) ||
'Lock ID 2' " "
-------------------------------- END of Header
,
HH.session_id || CHR(10) ||
HH.mode_held || CHR(10) ||
HH.lock_type || CHR(10) ||
HH.mode_requested || CHR(10) ||
HH.lock_id1 || CHR(10) ||
HH.lock_id2 "Holding Session Info"
------------------------------ END of Holding Session
,
Ww.session_id || CHR(10) ||
WW.mode_held || CHR(10) ||
Ww.lock_type || CHR(10) ||
Ww.mode_requested || CHR(10) ||
Ww.lock_id1 || CHR(10) ||
Ww.lock_id2 "Waiting Session Info"
------------------------------ END of Waiting Session
from
-----------------------------------------------------
(
select /*+ RULE */ *
from SYS.dba_locks
where blocking_others = 'Blocking' and
mode_held != 'None' and
mode_held != 'Null'
) HH,
-----------------------------------------------------
(
select /*+ RULE */ *
from SYS.dba_locks
where mode_requested != 'None'
) WW
-----------------------------------------------------
where WW.lock_type = HH.lock_type and
WW.lock_id1 = HH.lock_id1 and
WW.lock_id2 = HH.lock_id2
;

spool off

4. SGA Sizing
-------------
Description: An interesting script to size the SGA

set serverout on
DECLARE
l_uplift CONSTANT NUMBER := 0.3;
/* i.e. 30% above calculated */
l_numusers NUMBER DEFAULT 50;
/* Change ths to a predicted number existing database */
l_avg_uga NUMBER;
l_max_uga NUMBER;
l_sum_sql_shmem NUMBER;
l_sum_obj_shmem NUMBER;
l_total_avg NUMBER;
l_total_max NUMBER;
BEGIN
dbms_output.enable(20000);

IF ( l_numusers = 0) THEN
SELECT sessions_highwater
INTO l_numusers
FROM v$license;
dbms_output.put_line('Maximum concurrent users on this database = '||TO_CHAR(l_numusers));
ELSE
dbms_output.put_line('Calculating SGA for = '||TO_CHAR(l_numusers)||' concurrent users');
END IF;
dbms_output.new_line;

SELECT
avg(value)*l_numusers
,max(value)*l_numusers
INTO l_avg_uga, l_max_uga
FROM v$sesstat s, v$statname n
WHERE s.statistic# = n.statistic#
AND n.name = 'session uga memory max';

SELECT sum(sharable_mem) INTO l_sum_sql_shmem FROM v$sqlarea;

SELECT sum(sharable_mem) INTO l_sum_obj_shmem FROM v$db_object_cache;

l_total_avg := l_avg_uga + l_sum_sql_shmem + l_sum_obj_shmem;
l_total_max := l_max_uga + l_sum_sql_shmem + l_sum_obj_shmem;

dbms_output.put_line('Recommended Shared_pool size between :' || TO_CHAR(ROUND(l_total_avg + (l_total_avg * l_uplift), 0) ) ||' and '
|| TO_CHAR(ROUND(l_total_max + (l_total_max * l_uplift), 0) ) ||' bytes');

dbms_output.put_line('Recommended Shared_pool size between :' || TO_CHAR(ROUND((l_total_avg + (l_total_avg * l_uplift)) /(1024*1024), 0) )
||' and '|| TO_CHAR(ROUND((l_total_max + (l_total_max * l_uplift )) /1024*1024) ,0) ) ||' M bytes');

end;
/


5.DB Block Efficiency Script
----------------------------
Description: This script uses the new way of calculating DB_Block Buffer Efficiency.
Logical Read: consistent gets + db block gets
Hit-Ratio: (logical reads - physical reads) / (logical reads)
Our Aim: OLTP >= 95%, DSS/Batch >= 85%
Solution: enlarge block buffers, tune SQL, check appropriateness of indexes

select name, ((consistent_gets + db_block_gets) - physical_reads)/(consistent_gets + db_block_gets) * 100 "Hit Ratio%"
from v$buffer_pool_statistics
where physical_reads > 0;

6. EXPLAIN and get some statistics on your SQL
----------------------------------------------
Description: Haven't you ever thought there should be an easier way to do the EXPLAIN PLAN and TKPROF statistics than to edit your
queries to add the commands (like EXPLAIN PLAN SET...), or to have to find or write a script that automates this? It should
be an automatic part of SQL*Plus. Well, as of SQL*Plus 3.3 it is!! The command is called 'SET AUTOTRACE ON'!

The SET AUTOTRACE Command

sql>SET AUTOTRACE ON

And then run your select statement. Example:

SQL> SET AUTOTRACE ON
SQL> SELECT d.deptno, d.dname, e.empno, e.ename FROM dept d, emp e WHERE d.deptno = e.deptno;

DEPTNO DNAME EMPNO ENAME
---------- -------------- ---------- ----------
10 ACCOUNTING 7839 KING
.
.
30 SALES 7900 JAMES
30 SALES 7521 WARD

14 rows selected.

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 MERGE JOIN
2 1 SORT (JOIN)
3 2 TABLE ACCESS (FULL) OF 'EMP'
4 1 SORT (JOIN)
5 4 TABLE ACCESS (FULL) OF 'DEPT'

Statistics
----------------------------------------------------------
0 recursive calls
4 db block gets
2 consistent gets
0 physical reads
0 redo size
670 bytes sent via SQL*Net to client
376 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
14 rows processed

There are also some other options, for example there is a TRACEONLY option which supresses the SQL output.

Some setup issues:

If you go off and try this on your instance, you may run into some problems. There are a few setup steps that need to be taken to make this work:

-Make sure you have access to PLAN_TABLE. If you don't, create it using utlxplan.sql (It should be in a directory like $ORACLE_HOME/rdbms73/admin/) and make sure you have access to it from the user you are using to tune the SQL.
-You also need to create the PLUSTRACE role, and grant it to the desired users. The script to create this role is in:

$ORACLE_HOME/plus33/Plustrce.sql

It has to be run from SYS in order to have the correct security access. Then grant the role to the desired users or ROLEs.

7. See How Far Sorts Have Progressed
------------------------------------
Description: This script will display the estimated work and time remaining for long operations (i.e. over 10 minutes).

select sid,
message || '(' || time_remaining || ')' "Long Ops"
from v$session_longops
where time_remaining > 600;

8. Types of Contention a Database Instance is (or is not) Experiencing
----------------------------------------------------------------------
Description: This is possibly one of the most useful tuning scripts available. The V$SYSTEM_EVENT dynamic performance view is the highest-level view of the "Session Wait Interface". Information in this view is cumulative since the database instance was started, and one can get a very good idea of what types of contention a database instance is (or is not) experiencing by monitoring this view.

/**********************************************************************
* File: systemevent.sql
* Type: SQL*Plus script
* Description:
* This is possibly one of the most useful tuning scripts available.
*
* The V$SYSTEM_EVENT dynamic performance view is the highest-level
* view of the "Session Wait Interface". Information in this view
* is cumulative since the database instance was started, and one
* can get a very good idea of what types of contention a database
* instance is (or is not) experiencing by monitoring this view.
*
* NOTE: this script sorts output by the TIME_WAITED column in the
* V$SYSTEM_EVENT view. If the Oracle initialization parameter
* TIMED_STATISTICS is not set to TRUE, then the TIME_WAITED
* column will not be populated.
*
* Please *disregard* the advice of people who insist that turning
* off TIMED_STATISTICS is somehow a performance boost. Whatever
* performance overhead that might be incurred is more than
* compensated for by the incredible tuning information that
* results...
*
*********************************************************************/
set echo off feedback off timing off pause off verify off
set pagesize 100 linesize 500 trimspool on trimout on
col event format a26 truncate heading "Event Name"
col total_waits format 999,990.00 heading "Total|Waits|(in 1000s)"
col total_timeouts format 999,990.00 heading "Total|Timeouts|(in 1000s)"
col time_waited format 999,990.00 heading "Time|Waited|(in Hours)"
col pct_significant format 90.00 heading "% of|Concern"
col average_wait format 990.00 heading "Avg|Wait|(Secs)"

col instance new_value V_INSTANCE noprint
select lower(replace(t.instance,chr(0),
)) instance
from sys.v_$thread t,
sys.v_$parameter p
where p.name = 'thread'
and t.thread# = to_number(decode(p.value,'0','1',p.value));

col total_time_waited new_value V_TOTAL_TIME_WAITED noprint
select sum(time_waited) total_time_waited
from sys.v_$system_event
where event not in ('SQL*Net message from client',
'rdbms ipc message',
'slave wait',
'pmon timer',
'smon timer',
'rdbms ipc reply',
'SQL*Net message to client',
'SQL*Net break/reset to client',
'inactive session',
'Null event')
/

select event,
(total_waits / 1000) total_waits,
(total_timeouts / 1000) total_timeouts,
(time_waited / 360000) time_waited,
decode(event,
'SQL*Net message from client', 0,
'rdbms ipc message', 0,
'slave wait', 0,
'pmon timer', 0,
'smon timer', 0,
'rdbms ipc reply', 0,
'SQL*Net message to client', 0,
'SQL*Net break/reset to client', 0,
'inactive session', 0,
'Null event', 0,
(time_waited / &&V_TOTAL_TIME_WAITED)*100) pct_significant,
(average_wait / 100) average_wait
from sys.v_$system_event
where (time_waited/360000) >= 0.01
order by pct_significant desc, time_waited desc

spool sysevent_&&V_INSTANCE
/
spool off


9. Wait Sessions
----------------
Description: The Script lists the Session ID,Event Name,Time Waited on the Database. This Script is useful in identifying the resource intensive Queries. Can Be used on oracle 8i & 9i Databases.

select se.sid,s.username,Se.Event,se.time_waited
from v$session s, v$session_event se
where s.username is not null
and s.username not in ('SYS','SYSTEM')
and se.sid=s.sid
and s.status='ACTIVE'
and se.event not like '%SQL*Net%';

10. Health Check
---------------
Description: Look at the statistics of your Database

REM Database Health monitoring script.
REM
REM Segments Max extents & Current extent comparison
REM
set line 180
set pagesize 10000
set feedback off
prompt
col Time format a50 heading "System Time"
select to_char(sysdate,'DD-MON-YYYY:HH24:MI:SS') Time from dual;
prompt
prompt
prompt
prompt**---------------Objects Reaching Max extents-----------------------**
prompt
col segment_name format a40 heading "Object Name"
col max_extents format 9999999999 heading "Max Extents"
col curr_extents format 99999999999 heading "Curent Extents"
select a.segment_name,a.max_extents,b.curr_extents from dba_segments a,(select
segment_name,max(extent_id) curr_extents from dba_extents group by segment_name) b where a.segment_name = b.segment_name
and (a.max_extents - b.curr_extents) <= 10;
prompt
prompt**---------------User Session Information----------------------------**
prompt
col sid format 9999 heading "SID"
col serial# format 9999999 heading "SERIAL NO"
col logon_time format 9999999 heading "Login Time"
col username format a12
col osuser format a24
col program format a38
select s.sid,s.serial#,p.spid,s.username,s.osuser,s.program,to_char(s.LOGON_TIME,'DD-MON-YY:HH24:MI:SS') "Log on Time",round((s.LAST_CALL_ET/(60*60*24)),2)"Wait in Days" from v$session s,v$process p where s.paddr = p.addr and s.username is not null order by 8 desc;
prompt
prompt**---------------File Information-------------------------------------**
prompt
col file_name format a55 heading "File Name"
col SizeInMB format 99999999 heading "Total Size (MB)"
col MAXSIZE format 99999999 heading "Maximum Size (MB)"
select file_name,BYTES/(1024*1024) SizeInMB,AUTOEXTENSIBLE,MAXBYTES/(1024*1024) MAXSIZE from dba_data_files;
prompt
prompt**---------------Tablespace Information-------------------------------**
prompt
col tablespace_name format a25 heading "Tablespace Name"
col logging format a10
col status format a12
col extent_management format a30 heading "Local/Dict"
select tablespace_name,status,contents,decode(logging,LOGGING,'YES','NO') Logging,extent_management from dba_tablespaces;
SELECT Total.name "Tablespace Name",
Free_space, (total_space-Free_space) Used_space, total_space
FROM
(select tablespace_name, sum(bytes/1024/1024) Free_Space
from sys.dba_free_space
group by tablespace_name
) Free,
(select b.name, sum(bytes/1024/1024) TOTAL_SPACE
from sys.v_$datafile a, sys.v_$tablespace B
where a.ts# = b.ts#
group by b.name
) Total
WHERE Free.Tablespace_name = Total.name;
prompt
prompt**---------------Tablespace Fragmentation Status----------------**
prompt
col TABLESPACE_NAME format a25 heading "Tablespace Name"
select TABLESPACE_NAME Name,TOTAL_EXTENTS "Total Extents",EXTENTS_COALESCED,decode(PERCENT_EXTENTS_COALESCED,100,'NO','YES') "Frag" from
dba_free_space_coalesced;
prompt
prompt**---------------Latch Contention-------------------------------**
prompt
col name format a40 heading "Latch Name"
SELECT name, gets, misses,
round((gets-misses)/decode(gets,0,1,gets),3) hit_ratio,
sleeps,
round(sleeps/decode(misses,0,1,misses),3) "sleeps/misses"
from v$latch
where gets != 0
order by name;

select name,immediate_gets,immediate_misses,(immediate_gets)/(immediate_gets+immediate_misses) Hit_Ratio
from v$latch where immediate_gets != 0;
prompt
prompt**---------------Shared Pool Statistics-------------------------------**
prompt
col namespace format a22
select namespace,gets,gethits,round(gethitratio,2) gethitratio,pins,pinhits,round(pinhitratio,2)
pinhitratio,reloads,invalidations from v$librarycache;
prompt
prompt**---------------Roll back segment Statistics-------------------------------**
prompt
col segment_name format a30 heading "Segment Name"
col status format a15
select segment_name,a.status,initial_extent/(1024) "Initial KB",next_extent/(1024)"NextKB",max_extents,min_extents,optsize/102
"Opt Size KB",curext "Current Extents" from dba_rollback_segs a,v$rollstat b where a.segment_id = b.usn;
prompt
prompt**---------------Top 20 Events and System Statistics-------------------------------**
prompt
col event format a40 heading "Event Name"
col Stat format a40 heading "Stat Name"
select * from ( select name "Stat",value from v$sysstat order by value desc ) where rownum <= 20 ;
select * from ( select event,total_waits from v$system_event order by total_waits desc ) where rownum <=
20;
prompt
prompt**---------------Buffer Cache statistics-------------------------------**
prompt
select (1-(a.value/(b.value+c.value))) *100 "Buffer Cache Hit ratio" from v$sysstat a,v$sysstat b,v$sysstat c where a.name
= 'physical reads' and b.name = 'consistent gets' and c.name = 'db block gets';
col name format a20 heading "Buffer Pool Name"
select name,free_buffer_wait,write_complete_wait,buffer_busy_wait,db_block_gets,consistent_gets,physical_reads,physical_writes
from v$buffer_pool_statistics;
prompt
prompt
prompt
prompt**---------------File I/O statistics-------------------------------**
prompt
col file# format 99 heading "File No"
select file#,PHYRDS "Physical Reads",PHYWRTS "Physical Writes",READTIM "Read Time",WRITETIM "Write Time",AVGIOTIM "Avg Time" from v$filestat;
set feedback on

10.Handy oracle freespace scripts
--------------------------------------
There are several views in the data dictionary that display database free space. We have to start by defining free space:
Is "free space" the empty space in the tablespace?
Is "free space the available space on the data files and filesystems?
There are several salient DBA views for displaying free space in Oracle:
dba_free_space
dba_data_files
dba_tablespaces
Here is a simple script to display Oracle free space within the data file space. Note the in-line view where dba_data_files is joined into dba_free_space:
SELECT
a.tablespace_name,
a.file_name,
a.bytes allocated_bytes,
b.free_bytes
FROM
dba_data_files a,
(SELECT file_id, SUM(bytes) free_bytes
FROM dba_free_space b GROUP BY file_id) b
WHERE
a.file_id=b.file_id
ORDER BY
a.tablespace_name;

This shows the allocated and free bytes within the data files, but it DOES not show available free space on the OS filesystem:
TABLESPACE_NAME FILE_NAME ALLOCATED_BYTES FREE_BYTES
-------------------- ------------------------- --------------- --------------
TBS_LOCALS /u01/app/oradata/devdb/de 20,971,520 20,774,912
vdb/devdb_tbs_locals_01.d
bf
Note that there are many other ways to create tablespace reports. This method create an intermediate view to doisplay free space:
Rem free_space.sql
rem run this script first, to create the free_space view;
drOP VIEW SYS.FREE_SPACE;
CREATE VIEW SYS.FREE_SPACE AS
SELECT
TABLESPACE_NAME TABLESPACE,
FILE_ID,
COUNT(*) PIECES,
SUM(BYTES) FREE_BYTES,
SUM(BLOCKS) FREE_BLOCKS,
MAX(BYTES) LARGEST_BYTES,
MAX(BLOCKS) LARGEST_BLKS
FROM
SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME, FILE_ID;
This is the next script:
rem tsfree.sql - Shows all free space within tablespaces.
Prompt be sure that you have run free_space.sql prior to this script
clear breaks;
clear computes;
set verify off;
set pagesize 66;
set linesize 79;
set newpage 0;
column temp_col new_value spool_file noprint;
column today new_value datevar noprint;
column TABLESPACE_NAME FORMAT A15 HEADING 'Tablespace';
COLUMN PIECES FORMAT 9,999 HEADING 'Tablespace|Pieces';
COLUMN FILE_MBYTES FORMAT 99,999 HEADING 'Tablespace|Mbytes';
cOLUMN FREE_MBYTES FORMAT 99,999 HEADING 'Free|Mbytes';
COLUMN CONTIGUOUS_FREE_MBYTES FORMAT 99,999 HEADING 'Contiguous|Free|Mbytes';
COLUMN PCT_FREE FORMAT 999 HEADING 'Percent|FREE';
COLUMN PCT_CONTIGUOUS_FREE FORMAT 999 HEADING 'Percent|FREE|Contiguous';
ttitle left datevar right sql.pno -
center ' Instance Data File Storage' SKIP 1 -
center ' in ORACLE Megabytes (1048576 bytes)' -
skip skip;
BREAK ON REPORT
COMPUTE SUM OF FILE_MBYTES ON REPORT
select to_char(sysdate,'mm/dd/yy') today,
TABLESPACE_NAME,
PIECES,
(D.BYTES/1048576) FILE_MBYTES,
(F.FREE_BYTES/1048576) FREE_MBYTES,
((F.FREE_BLOCKS / D.BLOCKS) * 100) PCT_FREE,
(F.LARGEST_BYTES/1048576) CONTIGUOUS_FREE_MBYTES,
((F.LARGEST_BLKS / D.BLOCKS) * 100) PCT_CONTIGUOUS_FREE
from SYS.DBA_DATA_FILES D, SYS.FREE_SPACE F
where D.STATUS = 'AVAILABLE' AND
D.FILE_ID= F.FILE_ID AND
D.TABLESPACE_NAME = F.TABLESPACE
order by TABLESPACE_NAME;
Here is the report from this script.
Tablespace Pieces Mbytes Mbytes FREE Mbytes Contiguous
--------------- ---------- ---------- ------- ------- ---------- ----------
MASTER1_DETAILS 1 18 2 10 2 10
MASTER1_DETAILS 1 20 20 100 20 100
MASTER2_DETAILS 1 2 1 65 1 65
MASTER3_DETAILS 1 5 5 95 5 95
MASTER4_DETAILS 2 3 1 36 1 35
RBS_ONE 11 490 380 78 280 57
RBS_TWO 11 490 379 77 279 57
SYSTEM 17 60 45 76 45 75
TEMP 1 650 650 100 650 100
TOOLS 2 15 9 61 8 55
USERS 41 100 31 31 4 4
----------
13,255
This report is useful for finding the largest sized chunk of free space within a tablespace