2012年5月18日星期五

How to rebuild a lob index in oracle

  • How to move a lob segment and a lob index to a differrent tablespace.
    =====================================================================
    How to rebuild a LOB Index in oracle
    ===================================
    Normally you cannot move or rebuild a lob index to another tablespace like normal table. so you have to use little difference syntax. this is bellows ....

  • Example:-
    =================
    drop table image ;
    CREATE TABLE image (
    dname VARCHAR2(30),
    sname VARCHAR2(30),
    fname VARCHAR2(30),
    hblob BLOB);

    ALTER TABLE TEST MOVE TABLESPACE SYSTEM; ----ITS OK

    Above command will move the table to new tablespace but will not move the
    CLOB segment and it will still be in original tablespace. This is because LOB
    data is stored outside of the table.

    Check the tablespace of the CLOB column by issuing following sql.

    SELECT index_name, tablespace_name
    FROM user_indexes WHERE table_name = 'IMAGE';


    Alter index SYS_IL0000098512C00004$$ rebuild tablespace SYSTEM;
    ---ITS FAIL WITH BELLOWS ERROR

    ORA-02327: cannot create index on expression with datatype LOB

    ---------ITS OK
    ALTER TABLE image MOVE LOB (hblob)
    STORE AS (TABLESPACE system);

    ----see again
    SELECT index_name, tablespace_name
    FROM user_indexes WHERE table_name = 'IMAGE';

    SELECT * FROM user_lobs;

    [Note : "small" LOBs stored inline (ie in the row itself) are not in a
    seperate LOB SEGMENT at all. That is called STORAGE IN ROW and is the
    default for LOBs of 4000bytes or less.]

    ---for generating a script
    select 'alter table '||table_name||' move tablespace YOUR_TS'||chr(10)||
    'LOB ('||column_name||') store as '||segment_name||chr(10)||
    '(tablespace YOUR_TS);'
    from user_lobs

没有评论:

发表评论