This discussion is archived
10 Replies Latest reply: Feb 6, 2013 1:53 AM by 984556 RSS

how to rebuild indexes in e-business suite

user10721329 Newbie
Currently Being Moderated
Hi

How to rebuild all the indexes in e-business suite
  • 1. Re: how to rebuild indexes in e-business suite
    EBSDBA Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Employee ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Employee ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Employee ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Employee ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Solved the problem, Hussein thanks. I increased by 6GB and no more errors for unable to extend tablespace.

    thanks

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points