Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2003
    Location
    South Africa
    Posts
    41

    Question Unanswered: Moving Tables to a different Tablespace

    Hi

    Can someone please tell me / show in a script how to move one table thats in the System tablespace to another tablespace and move it with all its Data...

    Also, to take ownership of a certain table

    Thanks

  2. #2
    Join Date
    Jan 2003
    Location
    Vienna, Austria
    Posts
    102

    Re: Moving Tables to a different Tablespace

    hi,

    to move a table just use
    ALTER TABLE <tablename> MOVE TABLESPACE <TS_name>;
    you have to rebuild all indices on this table afterwards.

    You cannot change the owner of a table, I'd suggest 2 ways:
    either grant select-permission on the 'old' table to the 'new' user; log in as 'new' user and run
    CREATE TABLE <name> AS SELECT * FROM 'old'-user.<name>;
    or export the table (using exp) and import it afterwards using FROMUSER= TOUSER=
    ^/\x

  3. #3
    Join Date
    Feb 2003
    Location
    South Africa
    Posts
    41
    Is there a way fo rme to automate the recreate of the indexes on these tables?

    I can get the entire list of tables thats in the wrong tablespaces and spool a script to that does the actual move but then what can I do about the Indexes???

  4. #4
    Join Date
    Jan 2003
    Location
    Vienna, Austria
    Posts
    102
    try in sqlplus
    set head off
    spool ind_recreate.sql
    select 'ALTER INDEX ' || ' owner' || '.' object_name ||
    ' REBUILD ONLINE;'
    from dba_objects
    where object_type = 'INDEX' and
    status = 'INVALID';
    @ind_recreate.sql

    please note, this is only an example. at a simple REBUILD ONLINE some of the storage parameters (e.g. 'parallel') are lost.
    so for a full recreation with equal storage-statements you have to modify this script.
    ^/\x

  5. #5
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    try something like this

    select 'alter index '||owner||'.'||index_name ||' rebuild;' from
    dba_indexes
    where table_name in ( <either a list of tables as a comma seperated list or a subquery against db_tables used to select your list of tables to move> )

    If you are clever you can write a script to do

    select '/* 1*/alter table ....;' from dba_tables...
    union
    select '/* 2*/alter index ....;' from dba_indexes..
    union
    select '/* 3*/analyze table ....;' from dba_tables...
    order by 1

    which will write a script to move the tables, rebuild the indexes and then analyze them all.

    Alan

  6. #6
    Join Date
    Feb 2003
    Location
    South Africa
    Posts
    41
    Thanks for the Info guys, i am busy constructing a decent script to do it.

    If I wanted to include which TableSpace the Index needs to go in then how would I specify it? The TableSpace is P_Indexes...

    Thanks Again

  7. #7
    Join Date
    Mar 2002
    Location
    Ireland
    Posts
    181
    alter index INDEX_NAME rebuild tablespace P_Indexes;

    Rgs,
    Breen.

Posting Permissions

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