This content has been marked as final. Show 5 replies
Welcome to the forums. Please provide the version of your database.
Please paste the output of the following queries (don't forget to enclose the results of queries in CODE tag to make it more readable.
Also write here the INSERT statement you use for the insertion into this table.
select tablespace_name from dba_tablespaces where contents='TEMPORARY'; select tablespace_name,file_name,bytes/1024/1024/1024 from dba_temp_files; select property_value from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';
this could be an issue with your tablespace containing the table where you are inserting the data. check it out and if needed add some more space to it.
I'm using Oracle 10g.
This is the result of the query, displayed subsequently:
Unfortunately I don't have idea which query that cause the error. I have a stored procedure here which triggered by schedule, and it has lots of insert statement. Is there any way to locate which line that raise the error? Sori, another question.
TABLESPACE_NAME TEMP TABLESPACE_NAME, FILE_NAME, BYTES/1024/1024/1024 TEMP, /LOS/los/TEMP1.dbf, 31.9990234375 TEMP, /LOS/los/TEMP2.dbf, 31.9990234375 PROPERTY_VALUE TEMP
You did not answer the question about the INSERT statement which is causing this.
Use following query to find out which SQL and session is generating a lot of temp. (This is not written by me rather i got it from somewhere on internet, curtsy to, who wrote this)
Now, if you don't know when it will happen and all the time you can't monitor it, use following method to run this query every 5 minutes and dump the information in a table from where you can later query to find out who was generating a lot of temp
select a.sid,a.serial#,a.osuser,a.username,a.machine,a.module,c.sql_id,b.sql_text,c.SEGTYPE, c.EXTENTS "MB Taken",sysdate from v$session a, v$sql b, v$tempseg_usage c where a.serial#=c.SESSION_NUM
create table log_temp_usage (sid number, serial number, osuser varchar2(100), dbuser varchar2(30), machine varchar2(100), module varchar2(100), sql_id varchar2(20), sql_text varchar2(4000), segtype varchar2(50), size_taken_in_mb number, log_time date); create or replace procedure log_temp_usage_proc is begin insert into log_temp_usage_salman select a.sid,a.serial#,a.osuser,a.username,a.machine,a.module,c.sql_id,b.sql_text,c.SEGTYPE, c.EXTENTS "MB Taken",sysdate from v$session a, v$sql b, v$tempseg_usage c where a.serial#=c.SESSION_NUM commit; end; / exec dbms_job.submit (:job,what=>'log_temp_usage_proc;',next_date=>sysdate,interval=>'sysdate+1/288') commit;