This discussion is archived
2 Replies Latest reply: Oct 20, 2005 6:04 AM by The Human Fly RSS

How to reorg a database

461399 Newbie
Currently Being Moderated
Hi,

I am using Oracle 9.2.0. I have a big database with about 50 tables in 10 tablespaces.
Most of the tables have indexes. Also, there are referential integrities between the
tables in different tablespaces. Recently, I purged a lot of out-dated data.
How can I reorganize the database to free the storage spaces?

thanks & regards,
Mike
  • 1. Re: How to reorg a database
    SomeoneElse Guru
    Currently Being Moderated
    Will your tables eventually fill up with new data? If so, why bother to "reorg"?
  • 2. Re: How to reorg a database
    The Human Fly Oracle ACE Director
    Currently Being Moderated
    follwoing would help you.

    Either your can exp and import for use the following.

    DECLARE
    MSG VARCHAR2(250);
    ERR VARCHAR2(200);
    BEGIN
    FOR I IN (SELECT TABLE_NAME, TABLESPACE_NAME FROM user_tables order by table_name)
    LOOP
    MSG := 'ALTER TABLE ' ||I.TABLE_NAME || ' MOVE TABLESPACE '||I.TABLESPACE_NAME;
    EXECUTE IMMEDIATE MSG;
    FOR J IN (SELECT INDEx_NAME,TABLESPACE_NAME FROM USER_INDEXES
    WHERE TABLE_NAME = I.TABLE_NAME)
    LOOP
    MSG := 'ALTER INDEX ' ||J.INDEX_NAME || ' REBUILD TABLESPACE '||J.TABLESPACE_NAME;
    BEGIN
         EXECUTE IMMEDIATE MSG;
    EXCEPTION
         WHEN OTHERS THEN NULL;
    END;
    END LOOP;
    END LOOP;
    EXCEPTION
    WHEN OTHERS THEN
         ERR := SUBSTR(SQLERRM,1,200);
         DBMS_OUTPUT.PUT_LINE(ERR);
    END;
    /

    Connect to the required schema to which re-org is required and run this pl/sql script, and make sure that there are no invalid indexes left.

    select distinct status from user_indexes.

    If there are any, rebuild them without fail.

    Jaffar