How can i identify SQL statements that used up space in temp segment?

[Harry_Potter]

Im getting the error "ORA-1652: unable to extend temp segment". So i need to identify what causing the problem. I have done "alter system set events '1652 trace name errorstack level 1' , but ORACLE8 doesn't support this option. Any suggestions? Thanks!

[hrishy]

Hi

oerr ora 1652
01652, 00000, "unable to extend temp segment by %s in tablespace %s"
// *Cause: Failed to allocate an extent for temp segment in tablespace.
// *Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more
// files to the tablespace indicated.

join tables v$sort_usage v$seesion v$sql to find out who is using the sort space.

regards
Hrishy

[Harry_Potter]

Hi Hrishy,

I have tried querying v$sort_usage but there's no row in it. Is there any way to log the SQL statement aside from querying v$sort_usage?

Thanks!

[hrishy]

Hi

What sql's are the usrs running ? are they trying to create any indexes ?

regards
Hrishy

[Harry_Potter]

Hi,
Actually, the error occurs every 1am so i dont know what the usrs are doing in the DB. I have checked all the crons but i dont see any SQL statements. I have tried the alter system set events but oracle8 doesnt support this option. Hope you can help me.

Thanks a lot

[engiri]

Look at your alert file. If the tablespace is an permenant tablespace then the index creation space requirement is the issue at the tablespace where index is getting created. If the temp segment name is there in the alert then also the probable cause is the temp tablespace space issue due to index creation.

Increase both of them with auto extend and maxsize and see tomorrow with last ddl time whether any indexes created. Or simple ask your users if accessible, whether they received any errors. If you have helpdesk for your complany ask them whether any user errors are reported at the time the error occured.

[hrishy]

Hi

Look at your alert log and see at approximately what time this is happening.Try to find out if there are any dbms_jobs that are running at thsi time.

schedule the following sql's to run and see that you spool it to a file or something at the time mentioned in the alert log

select	s.sid || ',' || s.serial# sid,
	s.username,
	u.tablespace,
	substr(a.sql_text, 1, (instr(a.sql_text, ' ')-1)) sql_text,
	u.blocks/128 mb,
	((u.blocks/128)/(sum(f.blocks)/128))*100 pct
from	v$sort_usage	u,
	v$session	s,
	v$sqlarea	a,
	dba_data_files	f
where	s.saddr = u.session_addr
and	a.address (+) = s.sql_address
and	a.hash_value (+) = s.sql_hash_value
and	f.tablespace_name = u.tablespace
group by
	s.sid || ',' || s.serial#,
	s.username,
	substr(a.sql_text, 1, (instr(a.sql_text, ' ')-1)),
	u.tablespace,
	u.blocks/128

Please check if the script works as desired in oracle8.I dont have acesses to a oracle 8 database right now.

regards
Hrishy

[Harry_Potter]

Thanks hrishy & engiri

[dbtoo]

Use statspack, snap every 10 minutes between 12:30 and 1:30 am.

[KenEwald]

Regardless of where it's running, you know when it's running and that it's heavy.

No offense, but you're probably on salary.

Get up at 12:50pm, pour a cup of your favorite beverage, remote in, look for the top sql, nail it...

Or you could run statspack, but think of the case you would have if you had to get up in the middle of the night.

Good luck.

Make yourself a dang quesidilla!