1 2 Previous Next 16 Replies Latest reply: Feb 4, 2013 9:57 AM by 984556 RSS

    ORA-1654 <-> Unable to extend index

    Toolman21
      We have an oracle apps server and we started experiencing ORA-1654 errors. After digging through log files we found an error: ORA-1654: unable to extend index JTF.JTF_IH_ACTIVITIES_N6 by 16 in tablespace APPS_TS_TX_IDX.

      From what I can figure we need to add a datafile to our system. We currently have 4 datafiles that are 4Gb each. Is this the correct solution? If so what is the safest way to do this. Can I copy the 4 DB files to an alternate location and restore that way if the adding file doesn't work or what is the best practice for this type of action? Thanks
        • 1. Re: ORA-1654 <-> Unable to extend index
          sb92075
          01654, 00000, "unable to extend index %s.%s by %s in tablespace %s"
          // *Cause:  Failed to allocate an extent of the required number of blocks for
          //          an index segment in the tablespace indicated.
          // *Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more
          //          files to the tablespace indicated.
          just add another datafile
          • 2. Re: ORA-1654 <-> Unable to extend index
            729338
            Hi,

            This error simply tells you that it needed certain contigeous extents to be allocated on the tablespace but could not find as the tablespace may be full. Soyou need to either add a data file or resize any existing data file.

            Regards
            • 3. Re: ORA-1654 <-> Unable to extend index
              591186
              Add data file.
              • 4. Re: ORA-1654 <-> Unable to extend index
                Anand...
                Hi.
                what is the best practice for this type of action?
                yes you need to add a datafile or increase the size of the existing datafiles belonging to the tablespace APPS_TS_TX_IDX.


                Anand
                • 5. Re: ORA-1654 <-> Unable to extend index
                  Robert Geier
                  Do you know why the files are only 4GB ?

                  Your options are to add a new file, resize an existing file, or turn on autoextend for an existing file (if there is space available on disk)
                  • 6. Re: ORA-1654 <-> Unable to extend index
                    Roy
                    Hi ToolMan ,

                    unable to extend index JTF.JTF_IH_ACTIVITIES_N6 by 16 in tablespace APPS_TS_TX_IDX.

                    It means that index has need to grow in space and tablespace APPS_TS_TX_IDX is full .
                    You need to add datafille , This is regular daily dba work nothing to worry about.
                    Action plan :
                    1.First verify where are APPS_TS_TX_IDX datafiles --> select file_name from dba_data_files where tablesapce_name='APPS_TS_TX_IDX';
                    2.Verify you have enough space in the filsystem .
                    3. Add datafile --> alter tablespace APPS_TS_TX_IDX add datafile '/path to datafile' size XXXM;

                    Edited by: user11176183 on Dec 22, 2009 7:40 AM
                    • 7. Re: ORA-1654 <-> Unable to extend index
                      Toolman21
                      There are currently 4 entries for that tablespace, pointing to all 4 db files. The entry for db file #4 is auto extensible, if you subtract user_bytes from max_bytes you get 1310672 which is about 1024kb. so I assume this means it is full? The other three table references are auto extensible = N and max_bytes, max_blocks, increment_by are all 0.

                      Since this file seems to be full, and assuming that the 4Gb per file limit was set up for a particular reason. If I wanted to create another file for this tablespace I just 'alter tablespace APPS_TS_TX_IDX add datafile '/path to datafile' size XXXM;' and that is it despite the 4 entries for this tablespace?

                      Do I need to backup data in order to perform this? It is a production system and data loss is not an option.

                      Do I need to shut down all services (Apps, portal, etc) before I add the datafile?

                      Thanks for your help!
                      • 8. Re: ORA-1654 <-> Unable to extend index
                        Anand...
                        >
                        Do I need to backup data in order to perform this? It is a production system and data loss is not an option.

                        Do I need to shut down all services (Apps, portal, etc) before I add the datafile?>

                        NO.Just add the datafile using alter tablespace <tbs_name> add datafile '/location/datafile/name' size XXXXM;


                        Anand
                        • 9. Re: ORA-1654 <-> Unable to extend index
                          Toolman21
                          I added the file and nothing crashed yet, so I think everything will be well.

                          Thanks everyone for the help and answering what seems like stupid questions. I inherited the system and playing catch up.
                          • 10. Re: ORA-1654 <-> Unable to extend index
                            Anand...
                            I added the file and nothing crashed yet, so I think everything will be well.
                            LOL :D ...everything will be well..don't worry :)


                            Regards,
                            Anand
                            • 11. Re: ORA-1654 <-> Unable to extend index
                              Toolman21
                              Actually one more question, just to verify things are as they need to be.

                              The new datafile was added, showed up in the file system, etc.

                              However when looking at the dba_data_files the #4 file still has Auto_extensible='Y' and has max_bytes, max_blocks, increment_by with values.

                              The newly added #5 file is auto-extensible=N and 0 for max_bytes, max_blocks and increment_by. Do I need to do something to change this?

                              Thanks again
                              • 12. Re: ORA-1654 <-> Unable to extend index
                                Anand...
                                Hi..

                                What is size of the datafile taht you have added.Many a times, a 2Gb datafile is added and put in autoextend on mode with maxsize of 4gb.To do that you can use

                                alter tablespace <tbs_name> add datafile '/datafile/ocation/name' size 2048M
                                autoextend on
                                next 100M
                                maxsize 4096M;

                                If you add datafile as shown above you will bet the max_bytes,increment_by etc columns populated.Once that is limit (4GB) is reached you will get the ORA-1654 error.You can also add new datafile of 4Gb directly without any autoextend on.If you have OS level space crunch you can add datafile of 4gb you can create a 2Gb datafile with autoextend on and later clear some space for the datafile to autoextend.


                                Anand
                                • 13. Re: ORA-1654 <-> Unable to extend index
                                  Toolman21
                                  I added a file that was 4GB. Just wanted to make sure that the #4 file being set to auto_extend and the #5 not would not create an issue.

                                  Thanks again for your help!
                                  • 14. Re: ORA-1654 <-> Unable to extend index
                                    Anand...
                                    Just wanted to make sure that the #4 file being set to auto_extend and the #5 not would not create an issue.
                                    No it won't create an issue.You can autoextend it.

                                    Anand
                                    1 2 Previous Next