How can i identify SQL statements that used up space in temp segment?
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

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

  1. #1
    Join Date
    Jun 2005
    Posts
    17

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

    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!

  2. #2
    Join Date
    Jan 2001
    Posts
    2,828
    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

  3. #3
    Join Date
    Jun 2005
    Posts
    17
    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!

  4. #4
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

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

    regards
    Hrishy

  5. #5
    Join Date
    Jun 2005
    Posts
    17
    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

  6. #6
    Join Date
    Apr 2003
    Posts
    353
    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.

  7. #7
    Join Date
    Jan 2001
    Posts
    2,828
    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

    Code:
    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

  8. #8
    Join Date
    Jun 2005
    Posts
    17
    Thanks hrishy & engiri

  9. #9
    Join Date
    May 2005
    Location
    AZ, USA
    Posts
    131
    Use statspack, snap every 10 minutes between 12:30 and 1:30 am.

  10. #10
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    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!
    "I do not fear computers. I fear the lack of them." Isaac Asimov
    Oracle Scirpts DBA's need

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width