Which query causes “ORA-01652: unable to extend temp segment by % in tablespace TEMP”


connect / as sysdba

drop table temp_usage_t;
create table temp_usage_t ( msg varchar2(4000) );

create or replace trigger failed_to_extend_temp
after servererror on database
declare
l_sql_text ora_name_list_t;
l_n        number;
begin
if ( is_servererror(1652) )
then
insert into temp_usage_t values ( 'ora_sysevent = ' || ora_sysevent );
insert into temp_usage_t values ( 'ora_login_user = ' || ora_login_user );
insert into temp_usage_t values ( 'ora_server_error = ' || ora_server_error(1) );

l_n := ora_sql_txt( l_sql_text );
for i in 1 .. l_n
loop
insert into temp_usage_t values ( 'l_sql_text(' || i || ') = ' || l_sql_text(i)) ;
end loop;
end if;
end;
/

drop tablespace test_temp including contents and datafiles;
/

create temporary tablespace test_temp
tempfile '/tmp/test_temp.dbf' size 512k reuse
extent management local
uniform size 64k
/

alter user &&user_name temporary tablespace test_temp;

connect &&username

select * from all_objects
order by 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12
/

connect / as sysdba

set echo on termout on


select * from temp_usage_t;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: