| Check amount of useable temp space [message #54699] |
Thu, 28 November 2002 10:34  |
Stuart Macpherson
Messages: 6
Registered: November 2002
|
Junior Member |
|
|
We have a tablespace of type temporary that is 60Gb in size.
On ocassions there are major batch jobs that use almost all of this. When these batch jobs finish Oracle marks the extents as re-useable and other users get the temp space they need.
However BMC Patrol alerts that the tablespace is almost full and opertions contact support for resolution.
We have tried adding some space just to prevent alerting but as soon as other queries run they seem to use the new space first before re-using the existing extents so causing the alert to re-appear.
I have two questions :-
1. Is there any way I can use SQL to detect how much useable space is left in temp (not what has been used but is now free)
2. Is it correct Oracle uses the new added space first before reusing the existing segments.
Thanks
|
|
|
|
| Re: Check amount of useable temp space [message #54757 is a reply to message #54699] |
Tue, 03 December 2002 08:39  |
Sanjay Bajracharya
Messages: 279
Registered: October 2001
Location: Florida
|
Senior Member |
|
|
REM *******************************************************************
REM name: show_free.sql
REM author: Sanjay Bajracharya 2000/08/22
REM purpose: Give Alloc, Used and Free space in all tablespaces
REM needs to be run as SYS or SYSTEM
REM input: spool file name
REM *******************************************************************
accept v_filename prompt "Enter Spool File Name (Complete Path): "
REM set termout off
set pages 24
set lines 78
set underline off;
set feedback off;
set heading off;
set echo off;
set serverout on size 90000
set verify off
spool &v_filename
DECLARE
instance varchar2(9);
tbs_name varchar2(30);
avail_space number(15,3);
tot_avail_space number(15,3) := 0;
tot_free_space number(15,3) := 0;
used_space number(15,3);
percent number(15,3);
splat varchar2(2) := '%';
datetime varchar2(27);
cursor c1 is
SELECT tablespace_name, sum(bytes) freespace
FROM sys.dba_free_space
GROUP BY tablespace_name;
cursor c2 is
SELECT sum(bytes) availspace
FROM sys.dba_data_files
WHERE tablespace_name = tbs_name;
BEGIN
SELECT name
INTO instance
FROM v$database;
SELECT to_char(sysdate, 'DD-Mon-YYYY HH:MI:SS PM')
INTO datetime
FROM dual;
dbms_output.put_line('Free Space In Database Instance '||instance);
dbms_output.put_line(datetime);
dbms_output.put_line('--------------------------------------------------------
------------------');
dbms_output.put_line('Tablespace Freespace Allocated
Used Percent');
dbms_output.put_line('Name (MB) (MB)
(MB) Free');
dbms_output.put_line('--------------------------------------------------------
------------------');
for c1rec in c1 loop
tbs_name := c1rec.tablespace_name;
open c2;
fetch c2 into avail_space;
close c2;
tot_avail_space := tot_avail_space + avail_space;
tot_free_space := tot_free_space + c1rec.freespace;
percent := c1rec.freespace / avail_space * 100;
used_space := avail_space - c1rec.freespace;
dbms_output.put_line(rpad(c1rec.tablespace_name,10)||' '||to_char(c1rec.free
space/1024/1024,'999,999,999,990')||' '||to_char(avail_space/1024/1024,'999,999,
999,990')||' '||to_char(used_space/1024/1024,'999,999,999,990')||' '||to_char(pe
rcent,'99999.99')||' '||splat);
end loop;
dbms_output.put_line('========================================================
==================');
used_space := tot_avail_space - tot_free_space;
dbms_output.put_line('TOTAL '||to_char(tot_free_space/1024/1024,'999,999,
999,990')||' '||to_char(tot_avail_space/1024/1024,'999,999,999,990')||' '||to_ch
ar(used_space/1024/1024,'999,999,999,990'));
END;
/
spool off
exit
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
