10 Replies Latest reply: Feb 6, 2013 3:53 AM by 984556 RSS

    how to rebuild indexes in e-business suite

    user10721329
      Hi

      How to rebuild all the indexes in e-business suite
        • 1. Re: how to rebuild indexes in e-business suite
          EBSDBA
          Hi,

          Please see

          bde_rebuild.sql - Validates and rebuilds indexes occupying more space than needed [ID 182699.1]

          Thank
          • 2. Re: how to rebuild indexes in e-business suite
            984556
            Hi guys,

            Would this work for the below problem as well ?


            ORA-1654: unable to extend index APPLSYS.SYS_IOT_TOP_245802 by 311075 in tablespace APPLSYSD

            Even though there is enough space in tablespace
            select
            fs.tablespace_name "Tablespace",
            (df.totalspace - fs.freespace) "Used MB",
            fs.freespace "Free MB",
            df.totalspace "Total MB",
            round(100 * (fs.freespace / df.totalspace)) "Pct. Free"
            from
            (select
            tablespace_name,
            round(sum(bytes) / 1048576) TotalSpace
            from
            dba_data_files
            group by
            tablespace_name
            ) df,
            (select
            tablespace_name,
            round(sum(bytes) / 1048576) FreeSpace
            from
            dba_free_space
            group by
            tablespace_name
            ) fs
            where
            df.tablespace_name = fs.tablespace_name;
            Tablespace Used MB Free MB Total MB Pct. Free
            ----------
            ----------

            APPLSYSD 15332 5168 20500 25
            APPLSYSX 3302 3798 7100 53
            SQL> select max(bytes) as bytes from dba_extents;
            BYTES
            1698897920
            SQL>
            select index_name, table_name, max_extents from dba_indexes where
            index_name in('SYS_IOT_TOP_245802', 'SYS_IOT_TOP_245802');
            SQL> 2

            INDEX_NAME TABLE_NAME MAX_EXTENTS
            ------------------------------
            SYS_IOT_TOP_245802 AQ$_WF_CONTROL_H 505
            SQL> select table_name,table_owner from dba_indexes where index_name like 'SYS_IOT_TOP_245802';

            TABLE_NAME TABLE_OWNER
            ------------------------------
            AQ$_WF_CONTROL_H APPLSYS

            SQL> select QUEUE_TABLE,OWNER from dba_queue_tables where QUEUE_TABLE like '%WF_CONTROL%';

            QUEUE_TABLE OWNER
            ------------------------------
            WF_CONTROL APPLSYS

            Since this IOT = Index Organized Table , what is needed to resolve the error ORA-1654: unable to extend index APPLSYS.SYS_IOT_TOP_245802 by 311075 in tablespace APPLSYSD

            Appreciate your feedback
            • 3. Re: how to rebuild indexes in e-business suite
              Hussein Sawwan-Oracle
              ORA-1654: unable to extend index APPLSYS.SYS_IOT_TOP_245802 by 311075 in tablespace APPLSYSD
              You need to add more space.

              OERR: ORA 1654 unable to extend index <name.name> by <num> for tablespace <nam [ID 19049.1]
              Overview Of ORA-01654: Unable To Extend Index %s.%s By %s In Tablespace %s [ID 146595.1]

              https://forums.oracle.com/forums/search.jspa?threadID=&q=ORA-01654&objID=c3&dateRange=all&userID=&numResults=15&rankBy=10001

              Thanks,
              Hussein
              • 4. Re: how to rebuild indexes in e-business suite
                984556
                Error: ORA 1654
                Text: unable to extend index <schema>.<name> by <blocks> in tablespace <name>"
                -------------------------------------------------------------------
                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.

                How can I find out how much space is needed ?

                because I did add more space

                ALTER DATABASE DATAFILE '/proddata/applsysd06.dbf'
                RESIZE 2048M
                Mon Feb 4 23:31:07 2013
                Completed: ALTER DATABASE DATAFILE '/proddata/applsysd
                Mon Feb 4 23:31:13 2013
                ORA-1654: unable to extend index APPLSYS.SYS_IOT_TOP_245802 by 311075 in tablespace APPLSYSD
                ORA-1654: unable to extend index APPLSYS.SYS_IOT_TOP_245802 by 311075 in tablespace APPLSYSD

                Still getting the same error
                • 5. Re: how to rebuild indexes in e-business suite
                  Hussein Sawwan-Oracle
                  From the error, you need to add more space.

                  What was the size of the TS before you increase it to 2048M?

                  Thanks,
                  Hussein
                  • 6. Re: how to rebuild indexes in e-business suite
                    984556
                    Before increasing it was


                    Tablespace Used MB Free MB Total MB Pct. Free
                    ------------------------------ ---------- ---------- ---------- ----------

                    APPLSYSD 15332 5168 *20500* 25

                    APPS_UNDOTS1 18 3982 4000 100


                    After increasing:

                    Tablespace Used MB Free MB Total MB Pct. Free
                    ------------------------------ ---------- ---------- ---------- ----------
                    APPLSYSD 15335 7213 *22548* 32
                    APPLSYSX 3305 3795 7100 53

                    is this not enough ? from 25% free to 32% free and still getting same error.

                    This is resulting in sudden error page in oracle application

                    Error Page


                    You have encountered an unexpected error. Please contact the System Administrator for assistance.
                    • 7. Re: how to rebuild indexes in e-business suite
                      Hussein Sawwan-Oracle
                      Looks like it is not enough.

                      Can you add a new datafile to this TS with a size of (1-2 GB) and see if this helps?

                      Please also check the database log file for any other error messages.

                      Thanks,
                      Hussein
                      • 8. Re: how to rebuild indexes in e-business suite
                        984556
                        As you requested, I have added another datafile and still the same error

                        Alert.Log

                        ORA-1654: unable to extend index APPLSYS.SYS_IOT_TOP_245802 by 311075 in tablespace APPLSYSD
                        Tue Feb 5 00:20:36 2013
                        alter tablespace APPLSYSD add datafile '/oracle/proddb/applsysd07.dbf' size 1024M
                        autoextend on
                        next 100M
                        maxsize unlimited


                        Tue Feb 5 00:21:03 2013
                        Completed: alter tablespace APPLSYSD add datafile '/oracle/pr
                        Tue Feb 5 00:22:17 2013
                        Shutting down instance: further logons disabled
                        Tue Feb 5 00:22:22 2013
                        Completed checkpoint up to RBA [0xd4.2.10], SCN: 0x0000.c8817136
                        Tue Feb 5 00:22:24 2013
                        Shutting down instance (immediate)
                        License high water mark = 69
                        Tue Feb 5 00:23:27 2013
                        ALTER DATABASE CLOSE NORMAL
                        Tue Feb 5 00:23:28 2013
                        SMON: disabling tx recovery
                        SMON: disabling cache recovery
                        Tue Feb 5 00:23:30 2013
                        Shutting down archive processes
                        Archiving is disabled
                        Archive process shutdown avoided: 0 active
                        Thread 1 closed at log sequence 212
                        Successful close of redo thread 1.
                        Tue Feb 5 00:23:39 2013
                        Completed: ALTER DATABASE CLOSE NORMAL
                        Tue Feb 5 00:23:39 2013
                        ALTER DATABASE DISMOUNT
                        Completed: ALTER DATABASE DISMOUNT
                        ARCH: Archiving is disabled
                        Shutting down archive processes
                        Archiving is disabled
                        Archive process shutdown avoided: 0 active
                        ARCH: Archiving is disabled
                        Shutting down archive processes
                        Archiving is disabled
                        Archive process shutdown avoided: 0 active
                        Tue Feb 5 00:24:26 2013
                        Starting ORACLE instance (normal)
                        LICENSE_MAX_SESSION = 0
                        LICENSE_SESSIONS_WARNING = 0
                        SCN scheme 3
                        Using log_archive_dest parameter default value
                        LICENSE_MAX_USERS = 0
                        SYS auditing is disabled
                        Starting up ORACLE RDBMS Version: 9.2.0.3.0.
                        System parameters with non-default values:
                        tracefiles_public = TRUE
                        processes = 800
                        sessions = 885
                        timed_statistics = TRUE
                        shared_pool_size = 301989888
                        shared_pool_reserved_size= 30000000
                        sharedpool_reserved_min_alloc= 4100
                        java_pool_size = 67108864
                        enqueue_resources = 32000
                        nls_language = american
                        nls_territory = america
                        nls_sort = binary
                        nls_date_format = DD-MON-RR
                        nls_numeric_characters = .,
                        nls_comp = binary
                        nls_length_semantics = BYTE
                        control_files = /oracle/proddata/cntrl01.dbf, /oracle/data/cntrl02.dbf, /oracle/proddata/cntrl03.dbf
                        db_block_checksum = TRUE
                        db_block_size = 8192
                        db_cache_size = 167772160
                        compatible = 9.2.0
                        log_buffer = 10485760
                        log_checkpoint_interval = 100000
                        log_checkpoint_timeout = 1200
                        db_files = 512
                        db_file_multiblock_read_count= 8
                        log_checkpoints_to_alert = TRUE
                        dml_locks = 10000
                        row_locking = always
                        undo_management = AUTO
                        undo_tablespace = APPS_UNDOTS1
                        undo_suppress_errors = FALSE
                        undo_retention = 1800
                        db_block_checking = FALSE
                        max_enabled_roles = 100
                        O7_DICTIONARY_ACCESSIBILITY= TRUE
                        session_cached_cursors = 200
                        utl_file_dir = /usr/tmp, /oracle/proddb/9.2.0/appsutil/outbound/PROD_test6
                        job_queue_processes = 2
                        systemtrig_enabled = TRUE
                        cursor_sharing = EXACT
                        parallel_min_servers = 0
                        parallel_max_servers = 8
                        background_dump_dest = /oracle/proddb/9.2.0/admin/PROD_test6/bdump
                        user_dump_dest = /oracle/proddb/9.2.0/admin/PROD_test6/udump
                        max_dump_file_size = 20480
                        core_dump_dest = /oracle/proddb/9.2.0/admin/PROD_test6/cdump
                        optimizer_features_enable= 9.2.0
                        db_name = PROD
                        open_cursors = 600
                        ifile = /oracle/proddb/9.2.0/dbs/PROD_test6_ifile.ora
                        sql_trace = FALSE
                        sortelimination_cost_ratio= 5
                        btree_bitmap_plans = FALSE
                        fastfull_scan_enabled = FALSE
                        optimizer_max_permutations= 2000
                        query_rewrite_enabled = true
                        indexjoin_enabled = FALSE
                        sqlexecprogression_cost= 2147483647
                        likewith_bind_as_equality= TRUE
                        pga_aggregate_target = 1073741824
                        workarea_size_policy = AUTO
                        aq_tm_processes = 1
                        olap_page_pool_size = 4194304
                        PMON started with pid=2
                        DBW0 started with pid=3
                        LGWR started with pid=4
                        CKPT started with pid=5
                        SMON started with pid=6
                        RECO started with pid=7
                        CJQ0 started with pid=8
                        QMN0 started with pid=9
                        Tue Feb 5 00:24:27 2013
                        ALTER DATABASE MOUNT
                        Tue Feb 5 00:24:31 2013
                        Successful mount of redo thread 1, with mount id 218369979.
                        Tue Feb 5 00:24:31 2013
                        Database mounted in Exclusive Mode.
                        Completed: ALTER DATABASE MOUNT
                        Tue Feb 5 00:24:31 2013
                        ALTER DATABASE OPEN
                        test6:/>

                        ALTER DATABASE OPEN
                        Tue Feb 5 00:24:44 2013
                        Thread 1 opened at log sequence 212
                        Current log# 1 seq# 212 mem# 0: /oracle/proddata/log01a.dbf
                        Current log# 1 seq# 212 mem# 1: /oracle/proddata/log01b.dbf
                        Successful open of redo thread 1.
                        Tue Feb 5 00:24:44 2013
                        MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
                        Tue Feb 5 00:24:44 2013
                        SMON: enabling cache recovery
                        Tue Feb 5 00:24:44 2013
                        Undo Segment 1 Onlined
                        Undo Segment 2 Onlined
                        Undo Segment 3 Onlined
                        Undo Segment 4 Onlined
                        Undo Segment 5 Onlined
                        Undo Segment 6 Onlined
                        Undo Segment 7 Onlined
                        Undo Segment 8 Onlined
                        Undo Segment 9 Onlined
                        Undo Segment 10 Onlined
                        Successfully onlined Undo Tablespace 371.
                        Tue Feb 5 00:24:45 2013
                        SMON: enabling tx recovery
                        Tue Feb 5 00:24:45 2013
                        Database Characterset is AR8ISO8859P6
                        Tue Feb 5 00:24:57 2013
                        replication_dependency_tracking turned off (no async multimaster replication found)
                        Completed: ALTER DATABASE OPEN
                        Tue Feb 5 00:27:58 2013
                        ORA-1654: unable to extend index APPLSYS.SYS_IOT_TOP_245802 by 311075 in tablespace APPLSYSD
                        ORA-1654: unable to extend index APPLSYS.SYS_IOT_TOP_245802 by 311075 in tablespace APPLSYSD
                        ORA-1654: unable to extend index APPLSYS.SYS_IOT_TOP_245802 by 311075 in tablespace APPLSYSD
                        ORA-1654: unable to extend index APPLSYS.SYS_IOT_TOP_245802 by 311075 in tablespace APPLSYSD



                        in Oracle Application Clicking on any page gives me:

                        Error Page


                        You have encountered an unexpected error. Please contact the System Administrator for assistance.
                        • 9. Re: how to rebuild indexes in e-business suite
                          Hussein Sawwan-Oracle
                          Please add more space as mentioned above.

                          It is normal to get such errors from the application if you have similar errors in the database log file (especially when it is related to APPLSYSD TS).

                          Thanks,
                          Hussein
                          • 10. Re: how to rebuild indexes in e-business suite
                            984556
                            Solved the problem, Hussein thanks. I increased by 6GB and no more errors for unable to extend tablespace.

                            thanks