Re: Moving my objects to a new tablespace

From: Vladimir M. Zakharychev <bob_at_dpsp-yes.com>
Date: Thu, 4 Apr 2002 13:44:08 +0400
Message-ID: <a8h76i$irv$1@babylon.agtel.net>

For tables use ALTER TABLE <tbl> MOVE TABLESPACE <newdatatbs>, for indexes use ALTER INDEX <idx> REBUILD TABLESPACE <newidxtbs> after you moved tables (since moving a table invalidates all indexes on it), views don't take space and should not be moved. Note that any table having LONG column cannot be moved with alter table move - you will have to either create newtable as select * from oldtable, then drop old table and rename newtable to oldtable, or use export/import. If you are using LONGs, I strongly recommend changing them to LOBs - LONGs are so impaired I don't see why any developer in sane mind would use them... Also note that LOB columns can have their own storage parameters, including tablespace - if you are storing LOBs out of row, you may want to include LOB(lobcolumn) STORE AS (TABLESPACE <newlobtbs>) in your alter table move statement, otherwise LOBs will use table's storage parameters. For example,

ALTER TABLE lobtable MOVE TABLESPACE datatbs LOB(lobcol) STORE AS (TABLESPACE lobtbs DISABLE STORAGE IN ROW CACHE READS)

Simple SQL*Plus script for you that will move all tables and indexes to their new locations:

set serveroutput on
declare

   new_tbs varchar2(50) := 'your new tablespace';    new_idxtbs varchar2(50) := 'your new index tablespace'; begin
 dbms_output.enable(1000000);
 for t in (select table_name from user_tables) loop   begin
   execute immediate 'ALTER TABLE '||t.table_name||' MOVE TABLESPACE '||new_tbs;    dbms_output.put_line('Table '||t.table_name||' moved ok.');   exception
    when others then

     dbms_output.put_line('Failed moving table '||t.table_name);
     dbms_output.put_line(substr(sqlerrm,1,255));

  end;
 end loop;
 for i in (select index_name from user_indexes where index_type != 'DOMAIN') loop   begin
   execute immediate 'ALTER INDEX '||i.index_name||' REBUILD TABLESPACE '||new_idxtbs||      ' NOLOGGING';
   dbms_output.put_line('Index '||i.index_name||' rebuilt ok.');   exception
    when others then

     dbms_output.put_line('Failed rebuilding index '||i.index_name);
     dbms_output.put_line(substr(sqlerrm,1,255));

  end;
 end loop;
end;
/

Note that this script can fail for some tables/indexes (for example, for tables with LONGs), it is very basic and does not take into account IOTs, LOBs, partitioning, domain indexes, etc.

--
Vladimir Zakharychev (bob@dpsp-yes.com)                http://www.dpsp-yes.com
Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
All opinions are mine and do not necessarily go in line with those of my employer.


"sagbodza" <s_agbodza_at_yahoo.co.uk> wrote in message
news:d5173cd6.0204021059.404a2c9d_at_posting.google.com...


> Hi folks,

>

> I am developing an application using Developer 6i and Oracle 8.1.7.

> All my schema objects i.e tables, views, stored procedures, functions

> etc are in the system tablespace. I want to move them to a new

> tablespace but i do not really know the steps to take and what to

> watch out for.

> I have about 100 tables and views now and i am about halfway through.

> Any script to create tablespaces and associated files will be most

> welcome.

>

> Thanks.

>

> Stephen Agbodza.

Received on Thu Apr 04 2002 - 03:44:08 CST