Forgot your password?    
+ Reply to Thread
Results 1 to 3 of 3

Thread: Re-org of Database Objects

  1. #1
    Oracle DBA
    Join Date
    Dec 2011

    Re-org of Database Objects

    We will discuss the following segment_types

    1) Table
    - Normal
    - Long Datatye column

    2) Table Partition
    3) Table Subpartition
    4) Index
    5) Index Partition
    6) Index Subpartition
    7) Lobsegment/LobIndex

    1) Table

    - Normal

    Alter table <owner.table_name> move tablespace <tablespace_name>

    - Long Datatye column

    While moving the tables with column of long datatype, we get the following error

    ERROR at line 1:
    ORA-00997: illegal use of LONG datatype

    These tables cannot be moved using normal move command.
    We need to export, drop and import the tables.

    2) Table Partiton

    ALTER TABLE <table_name>
    MOVE PARTITION <partition_name>
    TABLESPACE <tablespace_name>;

    Dba_tab_partitions -- to get the partition name

    3) Table Subpartition

    ALTER TABLE <table_name>
    MOVE SUBPARTITION <subpartition_name>
    TABLESPACE <tablespace_name>;

    Dba_tab_subpartitions – to get the subpartition name

    4) Index

    Alter index <index-name> rebuild tablespace <new-tablespace>;

    1) Index Partition

    Alter index <index-name> rebuild partition <partition_name>
    tablespace <new-tablespace>;

    dba_ind_partitions -- to get the partition name

    2) Index Subpartition

    Alter index <index-name> rebuild subpartition <subpartition_name>
    tablespace <new-tablespace>;

    Dba_ind_subpartitions – to get the subpartition name

    3) Lobsegment/LobIndex

    alter table <your_table> move lob (<lob_column>)
    store as (tablespace <tablespace_lob>);

    To find the table_name and lob_column

    select OWNER,TABLE_NAME,COLUMN_NAME,SEGMENT_NAME,TABLESPA CE_NAME from dba_lobs where SEGMENT_NAME like ‘lobsegment_name’

    SYS_LOB0000195332C00032$$ LOBSEGMENT

    ------------------------------ ------------------------------
    ------------------------------ ------------------------------ ------------------------------
    SYS_LOB0000195332C00032$$ SYS_IL0000195332C00032$$ APPS_TS_QUEUES

    So from above output , following will be the command to move lobsegment

    alter table IEO.IEO_ICSM_QUEUE_TBL_1 move lob (USER_PROP) store as (tablespace APPS_TS_QUEUES_NEW);

    Note that , once the LOBSEGMENT is moved ,corresponding LOBINDEX will
    Also move. You don’t need to run the command seperately for LOBINDEX.

    Here is the dynamic query which will give the command to move LOBSEGMENT/INDEX

    select 'alter table ' ||owner || '.' || table_name||' move lob ('||column_name||') store as (tablespace APPS_TS_QUEUES_NEW);'
    from dba_lobs where SEGMENT_NAME in ( select segment_name from dba_segments where tablespace_name like ‘<tablespace_name>’ and segment_type like '%LOB%');
    Last edited by Hemant; 01-10-2012 at 06:47 PM.

  2. #2
    Oracle DBA
    Join Date
    Feb 2012
    Hi Hemant sir,

    I am facing following error in Internal Concurrent Manager log

    Internal Concurrent Manager found node PRODDB to be down. Adding it to the list of unavailable nodes.

  3. #3
    Expert Oracle Administrator
    Join Date
    Oct 2011
    New Delhi, India
    Hi Sanjay ,

    Your question is not raled to reorg , Its related to ICM . so please post the thread in the right category.

    I have opended a thread for your query :



+ Reply to Thread

Similar Threads

  1. Script to compile invalid objects in DB
    By Amit in forum Database Scripts
    Replies: 0
    Last Post: 11-03-2011, 01:03 PM
  2. Replies: 0
    Last Post: 11-02-2011, 07:29 PM
  3. Script to Assess Fragmented objects for Reorg
    By Amit in forum Database Scripts
    Replies: 0
    Last Post: 11-02-2011, 03:25 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

DBA Lounge (P) Ltd. deals in Oracle Technologies on Consulting, Resourcing, Corporate Training

Online and corporate training available on Oracle Database 11g, Oracle 11g Real Application Cluster (RAC), Oracle Applications 11i/R12, Oracle Fusion Middleware 11g, Oracle Identity Management-OIM, Oracle Internet Directory 11g-OID, Oracle Business Intelligence Enterprise Edition-OBIEE, Oracle Golden Gate, Oracle Access Management-OAM, Oracle Internet Directory-ODS, Oracle Identity Analytics Architecture-OIA Statistics