script to show who is using temp. tablespaces.

From: Brian Motzer <bmotzer_at_spacestar.com>
Date: 1997/07/06
Message-ID: <33C05550.14A8CC73@spacestar.com>

I've been contacting Oracle support about finding a script to display who is using up
the temporary tablespace. They have replied that someone sent them the following
script. They did state that it has not been tested. I have had some luck running
it to show who is using the space but not how much space they are using.

If you have better luck please email me.

• Author: RSANDERS.UK
• Update: KHAILEY.FR - added 64 bit capability and temp tmp seg
• Function: Find and list TEMP segments and their owners
• Note: Totally unsupported and may not work on all platforms/versions
• set serveroutput on
• execute dbms_output.enable(1000000)
• execute find_sort_segment_owners create or replace procedure find_sort_segment_owners as
• ************ BEGIN PORT SPECIFIC VALUES ****************** sga_word_size number := 8 ; /* 8 for Alpha o/w 4 */ byte_swapped boolean := true ; /* True for Sequent and DEC UNIX ow false */ debug boolean := false;
• ************ END PORT SPECIFIC VALUES ******************* size_int number := 4 ; size_kgglk number := 2*sga_word_size ; size_kssob number := 4*sga_word_size; offset_to_temp_table_dba number := 0 ; max_scan_count constant number := 200; /* Max. no of so. to scan */

type temp_seg_number_array is table of number index by binary_integer;

temp_seg_file_no temp_seg_number_array;
temp_seg_block_no temp_seg_number_array;
temp_seg_owner temp_seg_number_array;

no_of_temp_segments number := 0;
sga_base_address number;
sga_base_address_hex varchar2(50);

loop_cnt number;
temp_tmp_flag number;
perm_tmp_flag number;
temp_table_dba number;
temp_table_block_no number;
temp_table_file_no number;
temp_table_nblocks number;
temp_table_nextents number;
temp_table_tbs_num number;
sess_id number;
sess_addr varchar2(50);

sga_addr varchar2(50);

kssob_type_flag varchar2(50);
kssob_own_ptr varchar2(50);
kssob_nxt_link varchar2(50);
kssob_prv_link varchar2(50);

parent_head number;
cursor c1 is select sid,rawtohex(saddr) from v$session ;

function to_decimal (hex_str_in in varchar2) return number as hex_str varchar2(50) :=hex_str_in;
result number;
hex_char number;
begin
if ( sga_word_size = 8 ) then
hex_str := lpad(upper(nvl(ltrim(hex_str,'0'),'0')),16,'0'); result := 0;
for i in 1..16 loop
hex_char := ascii(substr(hex_str,i,1));
if (hex_char - 64 > 0 ) then
hex_char := hex_char - 64 + 9;
else
hex_char := hex_char - 48 ;
end if;
result := result + ( hex_char * power(2, ( 64 - (i*4) ) ) ); end loop;
return (trunc(result));
else
hex_str := lpad(upper(nvl(ltrim(hex_str,'0'),'0')),8,'0'); result := 0;
for i in 1..8 loop
hex_char := ascii(substr(hex_str,i,1));
if (hex_char - 64 > 0 ) then
hex_char := hex_char - 64 + 9;
else
hex_char := hex_char - 48 ;
end if;
result := result + ( hex_char * power(2, ( 32 - (i*4) ) ) ); end loop;
return (trunc(result));
end if;
end;

procedure get_base_address as
hex_address varchar2(40);
begin
select rawtohex(addr)
into hex_address
from x$ksmmem
where indx = 0;
sga_base_address := to_decimal(hex_address); sga_base_address_hex := hex_address;
end;

function get_sga_index(sga_address in varchar2) return number as begin
return trunc((to_decimal(sga_address)-sga_base_address)/sga_word_size );

end;

function get_sga_index_rem (sga_address in varchar2) return number as begin
return (mod((to_decimal(sga_address)-sga_base_address),sga_word_size)); end;

function get_sga_value (hex_address in varchar2,adjustment in number default 0)
return varchar2 as
sga_index number;
local_value varchar2(50);
begin
sga_index := get_sga_index(hex_address) + adjustment; if (debug) then
dbms_output.put_line('. get_sga_value '||hex_address||' '|| to_char(adjustment));
dbms_output.put_line('. get_sga_value get_sga_index '||sga_index); end if;
select rawtohex(ksmmmval) into local_value from x$ksmmem where indx = sga_index;
return local_value;
end;

function get_temp_table_state_object(state_name in varchar2) return number as

local_state_object number;
hex_str varchar2(50);
state_object_address varchar2(50);
state_object_offset number;
sga_structure boolean;
sga_col_count number;
c1 integer;
rc integer;
sql_stmt varchar2(255);
begin
select count(*) into sga_col_count from x$kqfta a, x$kqfco b where a.kqftanam = 'X$KSMFSV' and a.indx = b.KQFCOTAB and b.KQFCONAM = 'KSMFSADR';
if ( sga_col_count = 1 ) then
sga_structure := false;
else
sga_structure := true;
end if;
if ( sga_structure ) then
c1 := dbms_sql.open_cursor;
sql_stmt := 'select KSMFSOFF from x$ksmfsv where KSMFSNAM like '''||state_name||''''; dbms_sql.parse(c1,sql_stmt,dbms_sql.native); dbms_sql.define_column(c1,1,state_object_offset); rc := dbms_sql.execute(c1);
rc := dbms_sql.fetch_rows(c1);
dbms_sql.column_value(c1,1,state_object_offset); dbms_sql.close_cursor(c1);
local_state_object:=to_decimal(
get_sga_value(sga_base_address_hex,
state_object_offset/sga_word_size));
else
c1 := dbms_sql.open_cursor;
sql_stmt := 'select rawtohex(KSMFSADR) from x$ksmfsv where KSMFSNAM like '''||state_name||'''' ; dbms_sql.parse(c1,sql_stmt,dbms_sql.native); dbms_sql.define_column(c1,1,state_object_address,50); rc := dbms_sql.execute(c1);
rc := dbms_sql.fetch_rows(c1);

dbms_sql.column_value(c1,1,state_object_address);
dbms_sql.close_cursor(c1);
dbms_output.put_line('state_object_address '||

state_object_address);
if ( sga_word_size = 8 ) then
hex_str := get_sga_value(state_object_address); dbms_output.put_line('. temp flag value at address '||hex_str); hex_str := lpad(upper(nvl(ltrim(hex_str,'0'),'0')),16,'0'); if ( get_sga_index_rem(state_object_address) != 0 ) then hex_str := substr(hex_str,1,8);
else
hex_str := substr(hex_str,9,16);
end if;
dbms_output.put_line('. substr temp flag value at address '||hex_str); local_state_object := to_decimal(hex_str); else
local_state_object:=to_decimal(get_sga_value(state_object_address)); end if;
end if;
dbms_output.put_line('. temp_table_state_object '||local_state_object); return(local_state_object);
end;

function state_object_initialised(flags in varchar2) return boolean is
begin
if ( sga_word_size = 8 and byte_swapped ) then if ( to_decimal(substr(flags,14,1)) = 1 ) then return true;
else
return false;
end if;
else
if ( byte_swapped ) then
if ( to_decimal(substr(flags,6,1)) = 1 ) then return true;
else
return false;
end if;
else
if ( to_decimal(substr(flags,4,1)) = 1 ) then return true;
else
return false;

end if;
end if;
end if;

end;

function match_flag(state_flag in number,flags in out varchar2) return boolean
is
begin
if ( byte_swapped ) then
if ( sga_word_size = 8 ) then
flags:=lpad(upper(nvl(ltrim(flags,'0'),'0')),16,'0'); if (debug) then
dbms_output.put_line('. '||
to_decimal(substr(flags,15,2))||
'?='||state_flag);
end if;
if ( to_decimal(substr(flags,15,2)) = state_flag ) then return true;
else
return false;
end if;
else
if ( to_decimal(substr(flags,7,2)) = state_flag ) then return true;
else
return false;
end if;
end if;
else
if ( to_decimal(substr(flags,1,2)) = state_flag ) then return true;
else
return false;
end if;
end if;
end;

procedure build_active_temp_segments as
cursor c1 is select file#,block# from seg$ where type=3; local_file_no number;
local_block_no number;
begin
open c1;
no_of_temp_segments := 0 ;
loop
fetch c1 into local_file_no,local_block_no; exit when c1%notfound;
no_of_temp_segments := no_of_temp_segments + 1;

temp_seg_file_no(no_of_temp_segments) := local_file_no ;
temp_seg_block_no(no_of_temp_segments) := local_block_no;
temp_seg_owner(no_of_temp_segments) := (-1);

end loop;
close c1;
end;

function valid_temp_seg_dba (t_file_no number, t_block_no number, sess_id number ) return boolean
is
begin
for i in 1..no_of_temp_segments loop
if ( temp_seg_file_no(i) = t_file_no and temp_seg_block_no(i)= t_block_no ) then
temp_seg_owner(i) := sess_id ;
return true;
end if;
end loop;
return false;
end;

begin
get_base_address;

dbms_output.put_line('SGA base '||sga_base_address);
temp_tmp_flag:=get_temp_table_state_object('%ktstud%');
perm_tmp_flag:=get_temp_table_state_object('%ktatlt%');
dbms_output.put_line('temp tmp state '||temp_tmp_flag);
dbms_output.put_line('perm tmp state '||perm_tmp_flag);

build_active_temp_segments;
open c1;
loop
fetch c1 into sess_id,sess_addr ;
exit when c1%notfound;
dbms_output.put_line('********** sess_id,sess_addr '|| to_char(sess_id)||' '||sess_addr||' ***********'); kssob_type_flag := get_sga_value(sess_addr); if (debug) then
dbms_output.put_line('. kssob_type_flag '||kssob_type_flag); end if;
parent_head := to_decimal(sess_addr) + size_kssob; kssob_nxt_link := get_sga_value(sess_addr,size_kssob/sga_word_size); if (debug) then
dbms_output.put_line('. kssob_nxt_link '||kssob_nxt_link); end if;
sga_addr := parent_head ;
loop_cnt := 0;
if ( state_object_initialised(kssob_type_flag) ) then loop
exit when to_decimal(kssob_nxt_link) = parent_head; exit when loop_cnt > max_scan_count ;
loop_cnt := loop_cnt + 1;
sga_addr := kssob_nxt_link;
kssob_type_flag := get_sga_value(sga_addr, - (size_kgglk/sga_word_size) );
kssob_nxt_link := get_sga_value(sga_addr,0); if (debug) then
dbms_output.put_line('. type flag '||kssob_type_flag); end if;
if ( state_object_initialised(kssob_type_flag)) then if (match_flag(perm_tmp_flag,kssob_type_flag)) then temp_table_dba := to_decimal( get_sga_value(sga_addr, ( size_kgglk ) / sga_word_size
+ offset_to_temp_table_dba ));
temp_table_file_no
:=dbms_utility.data_block_address_file(temp_table_dba); temp_table_block_no

:=dbms_utility.data_block_address_block(temp_table_dba); if (valid_temp_seg_dba(temp_table_file_no,temp_table_block_no, sess_id) ) then
sys.dbms_output.put_line(

'Session '||rpad(to_char(sess_id),6,' ')||
' DBA '||rpad(temp_table_dba,10,' ')||
' File No '||rpad(temp_table_file_no,6,' ')||' Block No '||

rpad(temp_table_block_no,10,' '));
end if;
end if;
if (match_flag(temp_tmp_flag,kssob_type_flag) ) then temp_table_tbs_num:=to_decimal( get_sga_value(sga_addr, ( size_kgglk ) / sga_word_size
+ (size_int) ));
temp_table_nextents:=to_decimal( get_sga_value(sga_addr, ( size_kgglk ) / sga_word_size
+ (3*size_int) ));
temp_table_nblocks:=to_decimal( get_sga_value(sga_addr, ( size_kgglk ) / sga_word_size
+ (4*size_int) ));
sys.dbms_output.put_line(

'Session '||rpad(to_char(sess_id),6,' ')||
' TS id # :'||temp_table_tbs_num||
' Blocks used :'||temp_table_nblocks||
' Exetents used :'||temp_table_nextents);

end if;
end if;
end loop;
end if;
end loop;
close c1;
for i in 1..no_of_temp_segments loop
if (temp_seg_owner(i) = (-1) ) then
dbms_output.put_line('Unresolved Segment file:'||temp_seg_file_no(i)|| ' block: '||temp_seg_block_no(i)||' owner: '||temp_seg_owner(i)); end if;
end loop;
end;
/ Received on Sun Jul 06 1997 - 00:00:00 CDT