1 2 Previous Next 15 Replies Latest reply: Jan 18, 2011 2:33 AM by 618702 RSS

    Extent management of Tablespaces.

    VJ4
      Hello,
      Oracle Database 11g
      RHEL 5

      I got a error in alert log file that TEMP tablespace was not able to grow , when i checked the status of temp tablespace it had 768 MB of free space. So i started to focus in different angel.

      I figured out that the index when used is not getting the proper extent in TEMP tablespace and few other tablespaces in my DB. So i wanted to extend the extent management in those tablespaces .... but i got the following error ..
      SQL> alter tablespace TEMP default storage (maxextents unlimited);
      alter tablespace TEMP default storage (maxextents unlimited)
      *
      ERROR at line 1:
      ORA-03217: invalid option for alter of TEMPORARY TABLESPACE
      
      SQL> alter tablespace TBSP1 default storage (maxextents unlimited);
      alter tablespace TBSP1  default storage (maxextents unlimited)
      *
      ERROR at line 1:
      ORA-25143: default storage clause is not compatible with allocation policy
      I checked on net and I found that the tablespace with Uniform allocation type cannot be changed.
      select t.tablespace_name,t.allocation_type from dba_tablespaces t
      where tablespace_name in ('TEMP03','TBSP1');
      
      1     TBSP1      SYSTEM
      2     TEMP03     UNIFORM
      As TEMP tablespace is UNIFORM , can i change it to something different so that i can set the extent management. What about TBSP1 tablespace ?? what is the problem with this query.

      The major question here is " How should i be able to manage the extent management for these tablespace " ???

      Thanks in Advance .....
        • 2. Re: Extent management of Tablespaces.
          VJ4
          I have already read that thread ... but that dint sorted out my issue.

          Can anybody tell me some better ideas on this situation. ???
          • 3. Re: Extent management of Tablespaces.
            Chinar
            I got a error in alert log file that TEMP tablespace was not able to grow
            can you post there exactly which error you got in alert.log?
            • 4. Re: Extent management of Tablespaces.
              VJ4
              ORA-1652: unable to extend temp segment by 64 in tablespace                 TEMP 
              Wed Jan 12 06:17:01 2011
              ORA-1652: unable to extend temp segment by 64 in tablespace                 TEMP 
              Wed Jan 12 06:17:01 2011
              ORA-1652: unable to extend temp segment by 64 in tablespace                 TEMP 
              Wed Jan 12 08:43:16 2011
              ORA-1652: unable to extend temp segment by 64 in tablespace                 TEMP 
              Wed Jan 12 08:43:16 2011
              ORA-1652: unable to extend temp segment by 64 in tablespace                 TEMP 
              Wed Jan 12 11:08:55 2011
              ORA-1652: unable to extend temp segment by 64 in tablespace                 TEMP 
              Wed Jan 12 11:08:55 2011
              ORA-1652: unable to extend temp segment by 64 in tablespace                 TEMP 
              Wed Jan 12 13:35:10 2011
              ORA-1652: unable to extend temp segment by 64 in tablespace                 TEMP 
              Wed Jan 12 16:01:43 2011
              ORA-1652: unable to extend temp segment by 64 in tablespace                 TEMP 
              Wed Jan 12 16:01:43 2011
              ORA-1652: unable to extend temp segment by 64 in tablespace                 TEMP 
              But i am sure this is not the space related problem in TEMP tablespace , as i have checked that TEMP tablespace has sufficient free space ...

              Thanks for your reply ..
              • 5. Re: Extent management of Tablespaces.
                Chinar
                Post there
                select AUTOEXTENSIBLE,file_name from dba_temp_files
                where TABLESPACE_NAME='TEMP'
                • 6. Re: Extent management of Tablespaces.
                  VJ4
                  select AUTOEXTENSIBLE,file_name from dba_temp_files
                  where TABLESPACE_NAME='TEMP03'
                  
                  YES     /u01/app/oracle/oradata/rdbbk/TEMP03.DBF
                  This was about TEMP tablespace ... if you have some solution please let me know ... also i have this same issue in other tablespace also ... you can see the detail exaplin scenario above ...
                  • 7. Re: Extent management of Tablespaces.
                    Chinar
                    You have to refer below note
                    *ORA-1652 Error Troubleshooting [ID 793380.1]*
                    • 8. Re: Extent management of Tablespaces.
                      VJ4
                      You mean to say the " metalink" .. well i dont have access to metalink ..

                      Can you please clearly specify what solutions should i apply ??
                      • 9. Re: Extent management of Tablespaces.
                        knowledgespring
                        The simplest solution for this problem is to create another temporary tablespace

                        CREATE TEMPORARY TABLESPACE temp ...

                        ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp; --> change to new temp.. tab.space.

                        and drop earlier temporary tablespace after some time i.e. after switching to the new temporary tablespace.

                        Let us know if further help required.
                        • 10. Re: Extent management of Tablespaces.
                          Aman....
                          The simplest thing would be to drop the temp tablespace and recreate it and let it grow on the fly. If you are having the same issue with other tablespaces as well, it simply means that you are either running out of spaces in them or they have space but that's not usable and you need to defragment them.

                          How did you come to the conclusion that the temp has space? What's the output from v$tempseg_usage/V$sort_usage?

                          Aman....

                          PS: Please don't make your question in bold, it's not required and doesn't add any emphasis to the question either.

                          Edited by: Aman.... on Jan 13, 2011 9:36 PM
                          • 11. Re: Extent management of Tablespaces.
                            wiZ
                            An interposed question: what is v$temp_segment? Did you mean v$tempseg_usage? afaik its the same as v$sort_usage, isnt it ?

                            Best regards
                            • 12. Re: Extent management of Tablespaces.
                              Aman....
                              Yes, I stand corrected. You are correct , thanks.

                              Aman....
                              • 13. Re: Extent management of Tablespaces.
                                618702
                                Dear DBA4,

                                According to the documentation of 11g you simply can not change the TEMP tablespace's extent allocation from UNIFORM to AUTOMATIC. Of course by default the behaviour for the extent allocation (if you do not define it explicitly) is automatic. Can you please explain why do you want to deal with the maxextents parameter of temporary and system tablespace?

                                I believe the explanation is quite helpful;

                                +"+
                                +ORA-25143: default storage clause is not compatible with allocation policy+
                                +Cause: default storage clause was specified for a tablespace with AUTOALLOCATE or UNIFORM policy+
                                +Action: Omit the storage clause+
                                +"+

                                autoallocate is the alternative name for the extent allocation automatic. So with that message i can say that both automatic and uniform tablespaces' can not have the default storage clause. On the other hand a dictionary managed tablespace can have. Please read the following documentation;

                                +"+
                                *+DEFAULT storage_clause+*

                                +DEFAULT storage_clause lets you specify the new default storage parameters for objects subsequently created in the tablespace. For a dictionary-managed temporary table, Oracle Database considers only the NEXT parameter of the storage_clause.+

                                +Refer to the storage_clause for more information.+

                                +Restriction on Default Tablespace Storage+
                                +You cannot specify this clause for a locally managed tablespace.+
                                +"+

                                Since you are using 11g version by default those tablespaces are created as locally managed tablespaces.

                                http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/statements_3002.htm#SQLRF01002

                                Hope That Helps.

                                Ogan

                                Edited by: Ogan Ozdogan on 13.Oca.2011 23:12

                                Also the Oracle can change the extent allocation size according to the inserts into the particular table. It may start with small extent allocations in the segment and than grows automatically if needed. Allocating an extent from the system takes time and the Oracle tries to decrease that performance impact by allocating bigger extents.

                                Ogan
                                • 14. Re: Extent management of Tablespaces.
                                  VJ4
                                  Thanks to all the people for their replies.

                                  I know the TEMP tablespace recreation process. I was just trying to figure out that if its possible to manage any solution with the current tablespace as we have only 1 or 2 GB free space in our Disk to create a new tablespace. Well regarding the TEMP tablespace i will manage it but what about the Other tablespaces which contains user data and which can't be dropped ???

                                  Next i will fire the below command and expect to get some free space.
                                  ALTER TABLESPACE TABLESPACE_NAME COALESCE ;
                                  One more question : By default when we create tablespace in ORACLE 10g and higher version it always gets created as Locally Managed tablespaces. ( Am i right or wrong ? please let me know ) What if i need to create a tablespace with dictionary managed or TEMP tablespace with Uniform extent ? how should i write the query in Oracle 11g ??? I am doing this so as in future if i face this situation again , i would be able to fire this query ..
                                  SQL> alter tablespace TEMP default storage (maxextents unlimited);
                                  
                                  SQL> alter tablespace TBSP1 default storage (maxextents unlimited);
                                  The major part is how can i create a tablespace on which i can fire the above queries ....

                                  Again thanks for all your replies ... I will come back with the latest update.
                                  1 2 Previous Next