Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2003
    Posts
    9

    Unanswered: Moving multiple tables from one tablespace to another.

    Hi!

    I have a database where one users tables are in the wrong tablespace and need to move them... not just once but many times (dont ask me why ).

    I have some sql that I spool to another file and then run this file. Looks like this:

    select 'alter table ' || table_name || ' move tablespace the_new_tblspce;' from user_tables;

    It works, but im sure I could do this easy without using a file to spool to (ie do it directly in sql).

    Sorry for the somewhat silly question.

    Thanks in advance!

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    You could do some plsql and use 'execute immediate' to do it for you.

    Also dont forget to rebuild all indexes where status != valid afterwards (user_indexes) in the same way you move the tables.

    Alan

  3. #3
    Join Date
    Mar 2004
    Location
    Sydney
    Posts
    20
    I do something very similar....

    CREATE OR REPLACE PROCEDURE ??USER_NAME.TABLEMOVE_TO_HIST(schema_name in VARCHAR2)
    IS
    cur INTEGER:= DBMS_SQL.OPEN_CURSOR;
    fdbk INTEGER;
    BEGIN
    DECLARE
    CURSOR NEXTINDEX IS
    SELECT 'ALTER TABLE "'||owner||'"."'||table_name||'" MOVE TABLESPACE ??TABLESPACE_NAME' AS COMMAND
    FROM DBA_TABLES
    where owner = schema_name
    AND TABLE_NAME LIKE '%_ARC';
    BEGIN
    DBMS_OUTPUT.PUT_LINE( 'schema_name = ' || schema_name );
    FOR CURRENTINDEX IN NEXTINDEX LOOP
    DBMS_OUTPUT.PUT_LINE( 'COMMAND = ' || CURRENTINDEX.COMMAND );
    DBMS_SQL.PARSE (cur, CURRENTINDEX.COMMAND, DBMS_SQL.NATIVE); -- PARSE IS ALL THAT IS NEEDED FOR DDL
    -- fdbk := DBMS_SQL.EXECUTE (cur); -- NOT NEEDED FOR DDL STATEMENTS
    END LOOP;
    DBMS_SQL.CLOSE_CURSOR (cur);
    END;
    END;
    /

    CREATE OR REPLACE PROCEDURE ??USER_NAME.INDEX_TO_HIST(schema_name in VARCHAR2)
    IS
    cur INTEGER:= DBMS_SQL.OPEN_CURSOR;
    fdbk INTEGER;
    BEGIN
    DECLARE
    CURSOR NEXTINDEX IS
    SELECT 'ALTER INDEX "'||owner||'"."'||index_name||'" REBUILD TABLESPACE ??TABLESPACE_NAME ONLINE NOLOGGING' AS COMMAND
    FROM DBA_INDEXES
    where owner = schema_name
    AND TABLE_NAME LIKE '%_ARC';
    BEGIN
    DBMS_OUTPUT.PUT_LINE( 'schema_name = ' || schema_name );
    FOR CURRENTINDEX IN NEXTINDEX LOOP
    DBMS_OUTPUT.PUT_LINE( 'COMMAND = ' || CURRENTINDEX.COMMAND );
    DBMS_SQL.PARSE (cur, CURRENTINDEX.COMMAND, DBMS_SQL.NATIVE); -- PARSE IS ALL THAT IS NEEDED FOR DDL
    -- fdbk := DBMS_SQL.EXECUTE (cur); -- NOT NEEDED FOR DDL STATEMENTS
    END LOOP;
    DBMS_SQL.CLOSE_CURSOR (cur);
    END;
    END;
    /

  4. #4
    Join Date
    Sep 2003
    Posts
    9
    Aha.. Great! thanks a lot... The pl/sql procedures (with slight modifications) works great.

    I am totaly new to pl/sql so any/all examples i can get is very appreciated.

    I couldnt get execute immediate to do what i wanted so if you have time to post a short example (perhaps using the sql from my first post) to illustrate how to use it I would be very thankful. And thanks for reminding me that the indexes need to be rebuilt.

    Cheers!

Posting Permissions

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