10 Replies Latest reply: Jan 13, 2011 4:27 AM by user9077620 RSS

    Tablespace error

    user9077620
      Hi everyone,

      When the procedure of DWH are running to load data to DB, it gave me this error:

      ORA-01688 : unable to extend table table_name partition partition_name by 8192 in tablespace.

      i am new to the system,so could anyone help me,

      Regards.
      Mustafa
        • 1. Re: Tablespace error
          731759
          Cause : Failed to allocate an extent of the required number of blocks for table segment in the tablespace indicated.

          There is no space to extend. So increase the tablespace size by adding datafile or by resize.

          Thanks
          • 2. Re: Tablespace error
            CKPT
            ORA-01688 : unable to extend table table_name partition partition_name by 8192 in tablespace.
            Tablespace is running out of space.. please check or post compelte error message, at the end of above line you can find tablespace_name

            either resize or add datafile.
            alter database datafile 'location of datafaile' resize <bigger than existing size>
            or
            alter tablespace <tablespace_name> add datafile 'location' size 1000m;
            Error:     ORA 1688
            Text:     unable to extend table %s.%s partition %s by %s in tablespace %s
            ---------------------------------------------------------------------------
            Cause:     Failed to allocate an extent for table segment in tablespace.
            Action:     Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files
                 to the tablespace indicated.



            Thanks
            • 3. Re: Tablespace error
              asifkabirdba
              Tablespace is out of space. Add/ Re-size datafile for this tablespace.


              http://www.morganslibrary.com/reference/tablespaces.html



              Regards
              Asif Kabir
              • 4. Re: Tablespace error
                dba-india
                It is not auto extending your tablespace, it is full now.

                Use one of the following two commands and it will resolve your issue.
                1. Either add datafile to existing tablespace with autoextend on so it will not give you such issue again later,

                Alter tablespace <tablespace_name> add datafile '<fullpath\datafilename>' size <size_in_MBorGB> autoextend ON;

                2. Resize the existing datafile,
                ALTER DATABASE DATAFILE '<issue_tablespace_filename_with_full_path>' RESIZE <size_in_MBorGB>;
                • 5. Re: Tablespace error
                  user9077620
                  Dear Asim.

                  If i resize the data file of this tablespace,could u provide some queries in order to know how can i know if the autoextend is off and is it possible to resize the datafile and it will not fit the size of the tablespace,

                  Appreciate you help
                  Thanks a lot for your assistance.
                  • 6. Re: Tablespace error
                    CKPT
                    do you read all of the posts or only last one?


                    these are also commands
                    alter database datafile 'location of datafaile' resize <bigger than existing size>;
                    or

                    alter tablespace <tablespace_name> add datafile 'location' size 1000m;
                    • 7. Re: Tablespace error
                      731759
                      To find the autoextend status,

                      select tablespace_name,autoextensible from dba_data_files order by tablespace_name;

                      To resize,

                      alter database datafile 'path of that dbf file' resize <value>m;

                      To add datafile,

                      alter tablespace tbsname add datafile 'path to add' size <value>m;

                      Thanks
                      • 8. Re: Tablespace error
                        user9077620
                        Guys.

                        I am trying to find the path of datafile in order to add new one in linux system,i run this query(select file_name from dba_data_files where tablespace_name = 'tablespace_name')

                        and it shows under this path(+DATA/etl_data/etl_data_dbf), so i am trying to get this path in order to add new datafile,and i couldn't find this path in linux system.

                        Please guys you help.

                        Regards.
                        Mustafa
                        • 9. Re: Tablespace error
                          19426
                          and i couldn't find this path in linux system
                          That's no surprise,because '+DATA....' indicates an ASM diskgroup. See the the ASM documentation how to deal with ASM:

                          http://download.oracle.com/docs/cd/E11882_01/server.112/e16102/toc.htm

                          In general the 'ALTER ...' commands remain the same , only the filename specification is different.

                          Werner
                          • 10. Re: Tablespace error
                            user9077620
                            Guys

                            Thanks a lot for your assistance
                            I appreciate your helping so much