2 Replies Latest reply: Oct 20, 2005 8:04 AM by The Human Fly RSS

    How to reorg a database

    461399
      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
          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
            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