10 Replies Latest reply: Aug 9, 2012 2:43 AM by 415289 RSS

    Issue with tempfile

    954573
      Os info:     Linux
      Oracle Version:     9.2
      Error info:     ORA-1652: unable to extend temp segment by 20 in tablespace TEMP
      Hello ,

      Today myself was looking into a temp issue for one of my client and saw something strange.
      I looked V$TEMP_SPACE_HEADER and v$tempfile for tempfile usage details.It returned me 0 rows.
      I checked dba_tablespace & dba_data_files and i found the tablespace in the list which shouldnt be the case.I checked content column and it showed me as "TEMPORARY".
      I took the DDL for that tablespace and it showed me as it was created by the statement CREATE TEMPORARY.
      Also i found this tablespace TEMP has been made default temp space.checked DATABASE_PROPERTIES & dba_users to find the default value.
      When i checked sort_usage the output showed me TEMP was used by few sessions.
      Even i took a backup of my controlfile to trace and it didnt show a temp tablespace .
      Also as i didnt see the tempfiles in v$tempfile ,i calculated the size of the tablespace usage from dba_data_files and it showed me 100% utilized (24G) ,but v$sort_usage showed only less than 100M used


      My doubts:
      i)why i didnt see any details in v$tempfile and dba_temp_file ,V$TEMP_SPACE_HEADER

      ii)How do i calculate the size in this case.
      iii)Can anyone correct me if am wrong about the concepts.

      Below are my queries againt the Database:

      SELECT * FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

      PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
      --------------- ---------------- -----------------------
      DEFAULT_TEMP_TABLESPACE TEMP ID of default temporary tablespace


      SQL> select dbms_metadata.get_ddl('TABLESPACE','TEMP') from dual;


      CREATE TEMPORARY TABLESPACE "TEMP" TEMPFILE
      '/opt/oracle/temp01a.dbf' SIZE 104857600 REUSE ,
      '/opt/oracle/temp01b.dbf' SIZE 104857600 REUSE ,
      '/opt/oracle/temp01.dbf' SIZE 24956108800 REUSE
      EXTENT MANAGEMENT LOCAL UNIFORM SIZE 131072

      Extend details:

      SEGMENT_NAME||''||SEGMENT_TYPE||''||OWNER||''||EXTENTS||''||MAX_EXTENTS||''||NEXT_EXTENT||''||PCT_INCREASE
      --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      46.2538502 TEMPORARY SYS 153597 2147483645 163840 0


      SQL> select TABLESPACE_NAME,CONTENTS,STATUS from dba_tablespaces where tablespace_name='TEMP';

      TABLESPACE_NAME CONTENTS STATUS
      ------------------------------ --------- ---------
      TEMP TEMPORARY ONLINE


      SQL> select TABLESPACE_NAME, BYTES_USED/1024/1024, BYTES_FREE/1024/1024 from V$TEMP_SPACE_HEADER;

      no rows selected

      SQL> select * from v$tempfile;


      no rows selected

      SQL> SQL> select * from dba_temp_files;

      no rows selected


      Thanks,

      Sree
        • 1. Re: Issue with tempfile
          415289
          SQL> select * from v$tempfile;
          no rows selected
          SQL> SQL> select * from dba_temp_files;
          no rows selected
          Appear temp tablespace is empty ,temp datafile doesn't exist for temp tablespace,was database recently cloned?
          check alert log file regarding any error message on tempfile.

          Error info: ORA-1652: unable to extend temp segment by 20 in tablespace TEMP
          Add temp datafile into TEMP tablespace
          • 2. Re: Issue with tempfile
            933687
            add these tempfiles with given sizes.

            /opt/oracle/temp01a.dbf SIZE 104857600
            /opt/oracle/temp01b.dbf SIZE 104857600
            /opt/oracle/temp01.dbf SIZE 24956108800
            • 3. Re: Issue with tempfile
              Saugat Chatterjee
              Dear Sir

              As i have faced the same situation earlier and i couldn't find any solution so Can you plz tell me what is the relation of cloning the database with the missing tempfile?

              as far as i know about the cloning,it is done by using RMAN DUPLICATE command and during the cloning it sets new name for all the datafiles to the clone server location,so i didnt find any point of missing tempfile during cloning.please correct me if i am wrong
              • 4. Re: Issue with tempfile
                954573
                Thanks Kuljeet Pal Singh for the quick reply.

                But if you check my previous post output,you see there is a tablespace called TEMP,whose content shows "TEMPORARY",which is current set as default temp tablespace to the database .

                So why i dont see them in v$tempfile and why do i see them in dba_data_files.

                I have also pasted the DDL of the tablespace which clearly shows its a temporary one.

                Can you clear my above doubts.

                Regards,

                Sreeraj
                • 5. Re: Issue with tempfile
                  954573
                  Hi saugat,

                  Am not sure by cloning through RMAN DUPLICATE command it creates a temp file by default in 9i version.
                  But in 10g & above,it creates one by default.

                  In that case, you may need to create a temp tablespace if not as suggested by Kuljeet Pal Singh.

                  Regards,

                  Sree
                  • 6. Re: Issue with tempfile
                    Saugat Chatterjee
                    Hi Sreeraj

                    i guess when you open the cloned database then the temp tablespace gets created itself,please correct me if i am wrong
                    • 7. Re: Issue with tempfile
                      Saugat Chatterjee
                      Hi sreeraj

                      if your temporary tablespace is empty then at that scenario you wont be able to see 5the status using v$tempfile
                      • 8. Re: Issue with tempfile
                        anand prakash - oracle
                        Hi,

                        Do you see the files present in /opt/oracle/ location? if so, you can do alter tablespace temp add tempfile with reuse clause and check back the v$tempfile and dba_temp_file.

                        Anand
                        • 9. Re: Issue with tempfile
                          saurabh
                          hi sreeraj,

                          If the database is as cloned database of production then you need to add the tempfile for the temp tablespace. In clonning only temporary tablespace is created its tempfile are not added/created.

                          Edited by: saurabh on Aug 9, 2012 12:03 PM
                          • 10. Re: Issue with tempfile
                            415289
                            Am not sure by cloning through RMAN DUPLICATE command it creates a temp file by default in 9i version.
                            we've to create the tempfile,database alert log will give you the details whether its created or not after clone by rman.
                            But in 10g & above,it creates one by default.
                            yes,its created by default