7 Replies Latest reply: Feb 13, 2013 1:12 PM by 989272 RSS

    ORA-01659: unable to allocate MINEXTENTS beyond 13 in tablespace TBSP1

    846231
      Hi,

      I am getting crazy on how to solve this error. :(
      I still have 4Gb space in my TBSP1, what does "13" in the keyword "beyond" stands for? is it in bytes or blocks? which parameter can I see this number 13? Is it in tablespace/datafiles storage params or is it in create table parameter?


      Thanks
        • 1. Re: ORA-01659: unable to allocate MINEXTENTS beyond 13 in tablespace TBSP1
          Chinar
          ORA-01659: unable to allocate MINEXTENTS beyond string in tablespace string
          Cause: Failed to find sufficient contiguous space to allocate MINEXTENTS for the segment being created.
          Action: Use ALTER TABLESPACE ADD DATAFILE to add additional space to the tablespace or retry with smaller value for MINEXTENTS, NEXT or PCTINCREASE
          • 2. Re: ORA-01659: unable to allocate MINEXTENTS beyond 13 in tablespace TBSP1
            Helios-GunesEROL
            Hi;

            What is Db version?

            please see:
            OERR: ORA 1659 unable to allocate MINEXTENTS beyond in tablespace
            TROUBLESHOOTING GUIDE (TSG) - UNABLE TO EXTEND Errors [ID 1025288.6]
            Extent Sizes for Sort, Direct Load and Parallel Operations (PCTAS & PDML) [ID 50592.1]

            If this db belong peoplesoft please see:
            [OLM] 8.9 Broadcast triggers are failing in Scheduled Job report [ID 636286.1]

            Regard
            Helios
            • 3. Re: ORA-01659: unable to allocate MINEXTENTS beyond 13 in tablespace TBSP1
              rajeysh
              $ oerr ora 1659
              01659, 00000, "unable to allocate MINEXTENTS beyond %s in tablespace %s"
              // *Cause:  Failed to find sufficient contiguous space to allocate MINEXTENTS
              //          for the segment being created.
              // *Action: Use ALTER TABLESPACE ADD DATAFILE to add additional space to the
              //          tablespace or retry with smaller value for MINEXTENTS, NEXT or
              //          PCTINCREASE 
              refer:-
              http://www.dbasupport.com/forums/archive/index.php/t-22847.html
              • 4. Re: ORA-01659: unable to allocate MINEXTENTS beyond 13 in tablespace TBSP1
                846231
                Hi,

                Oracle 11g

                The tablespace has still big freespace. :(

                What is the number 13 stands for?
                • 5. Re: ORA-01659: unable to allocate MINEXTENTS beyond 13 in tablespace TBSP1
                  Helios-GunesEROL
                  Hi;

                  Pelase check below this 13 number can be change as 2 9 etc..

                  MINEXTENTS set the number of minimum extent for any segment that to be created.

                  MAXEXTENTS set the number of maximum number of extent for any segment that to be allocated.

                  PCTINCREASE: it take place when two extent are already allocated.it work for third or subsequent extent.
                  third extent size=last extent size+ pctincrease of last extent size.

                  Please see:
                  Using DBA_FREE_SPACE [ID 121259.1]

                  Regard
                  Helios
                  • 6. Re: ORA-01659: unable to allocate MINEXTENTS beyond 13 in tablespace TBSP1
                    jeffWong
                    I've also just encountered, and solved this. In my case the TS was unable to extend enough to accommodate the new table.


                    create table fstmpxxSAVER (fstmp number) pctfree 99 tablespace xxSAVER storage (initial 2m next 2m minextents 64)
                    *
                    ERROR at line 1:
                    ORA-01659: unable to allocate MINEXTENTS beyond 6 in tablespace xxSAVER

                    -- increased datafile max size to be bigger

                    SQL> l
                    1* create table fstmpxxSAVER (fstmp number) pctfree 99 tablespace xxSAVER storage (initial 2m next 2m minextents 64)
                    SQL> /

                    Table created.


                    HTH
                    • 7. Re: ORA-01659: unable to allocate MINEXTENTS beyond 13 in tablespace TBSP1
                      989272
                      Possible solutions:

                      **On the command prompt**

                      1.Make sure data file is not filled up and auto extend is on.
                      2.Try to add a data file or alter existing data file with auto extend capability and max extents unlimited.

                      alter database datafile '/..../fiilename.dbf' autoextend on maxsize unlimited;

                      3.Try de-fragmentation of tablespace that caused the problem.

                      alter tablespace TBSP1 coalesce;

                      **If you have TOAD, here is the best possible solution**
                      1. Go to database icon, click schema browser, set your schema as SYSTEM or SYS, then look for tablespaces tab below.
                      2. Click on the tablespaces tab, look for the tablespace that caused the problem,click the tablespace.
                      3. Click Basic Info tab, beneath that find extent management.
                      4. Click on auto allocate extent sizes.
                      5. Click OK button.

                      That`s it Problem is resolved.

                      -Regards
                      Rah