How to move tables to a new tablespace

How to move tables to a new tablespace?

>I have a user that used to create the tables
>inside the System TBS.
>I need to move the tables from System to the new
>one.
>We tried making an export, dropping the user,
>creating the user with default TBS the new one,
>reimported all... And the miracle was that before
>the import all the objects were in the new TBS,

that would be a miracle -- how could the objects be in the new TBS before the
import took place? the objects don't exist until you import.

>after the import all the tables and data were on
>System (AGAIN!??!?!).
>The quota were right, the rest also...Now, any
>other idea?? The user has the dba granted....

yes - they have unlimited tablespace so quotas = NO OP. we have to revoke that
for the imp/exp trick to work

>Should it be the problem?? I think so, the
>problem is that my user is using the dba
>privileges to use Oracle and at this time I can't
>revoke them.

Here are two ways to move a users objects from one tablespace to another. the
first uses Oracle8i, release 8.1 features (i don't see any versions so I don't
know whether this applies). the second is the exp/imp trick.

There are 2 methods we can use to do this. One is to use a combination of
"alter table X move tablespace Y" and "alter index X rebuild tablespace Y".
Below is a script called moveall.sql. It uses the user_segments table to
generate all of the needed "alter table move" and "alter index rebuild"
statements to move a table/index into another tablespace preserving the storage
characteristics currently assigned to the object. For example, when we run
moveall.sql in the SCOTT schema, we might see:

------------------ moveall.sql -------------------------------------------
set echo off

column order_col1 noprint
column order_col2 noprint

set heading off
set verify off
set feedback off
set echo off

spool tmp.sql

select decode( segment_type, 'TABLE', segment_name, table_name ) order_col1,
       decode( segment_type, 'TABLE', 1, 2 ) order_col2,
      'alter ' || segment_type || ' ' || segment_name ||
      decode( segment_type, 'TABLE', ' move ', ' rebuild ' ) || chr(10) ||
      ' tablespace &1 ' || chr(10) ||
      ' storage ( initial ' || initial_extent || ' next ' || next_extent ||
chr(10) ||
      ' minextents ' || min_extents || ' maxextents ' || max_extents || chr(10)
||
      ' pctincrease ' || pct_increase || ' freelists ' || freelists || ');'
from user_segments, (select table_name, index_name from user_indexes )
where segment_type in ( 'TABLE', 'INDEX' )
   and segment_name = index_name (+)
order by 1, 2
/

spool off

set heading on
set verify on
set feedback on
set echo on

REM UNCOMMENT TO AUTO RUN the generated commands
REM ELSE edit tmp.sql, modify as needed and run it
REM @tmp
---------------------- eof ----------------------------------------------

scott@ORACLE> @moveall
scott@ORACLE> set echo off

alter TABLE ACCOUNT move
tablespace users
storage ( initial 10240 next 10240
minextents 1 maxextents 121
pctincrease 50 freelists 1);

alter TABLE BONUS move
tablespace users
storage ( initial 10240 next 10240
minextents 1 maxextents 121
pctincrease 50 freelists 1);

alter TABLE DEPT move
tablespace users
storage ( initial 10240 next 10240
minextents 1 maxextents 121
pctincrease 50 freelists 1);

alter INDEX PK_DEPT rebuild
tablespace users
storage ( initial 10240 next 10240
minextents 1 maxextents 121
pctincrease 50 freelists 1);

....

It begins by moving a table and then rebuilding each of the indexes on that
table. Since the indexes on the tables being moved will become unusable after
the table, this script rebuilds them right after moving a table -- before moving
the next table (to reduce downtime).

Running the moveall.sql script is harmless as it is written. It generates the
SQL you need to run and saves the sql into yet another script file "tmp.sql".
You should edit tmp.sql, review it, modify it if you want (eg: if you have a
multi-cpu system, you could modify the index rebuilds to be "parallel N",
"unrecoverable" and add other options to make them go faster on your system),
and then run it.

Another method would be to use EXPort and IMPort. You would

o EXP the user account.
o drop all of the objects this user owns. You can 'select' the drop statements
you need (script attached) in much the same way we 'select' the alter
table/index statements
o revoke UNLIMITED TABLESPACE from the user
o alter the users default tablespace to the target tablespace
o give the user an unlimited quota on this new tablespace and their temporary
tablespace
o IMP this users data.

So, the process to move SCOTT's objects from their current tablespace to a
NEW_TABLESPACE would be:

1) do an export of all of scott's objects. Make sure no one modifies them after
you begin this process. You will lose these changes if they do.

$ exp userid=scott/tiger owner=scott

2) you would drop all of scotts tables. This will get the indexes as well. I
don't suggest dropping the user SCOTT but rather dropping scott's objects.
Dropping scott would cause any system priveleges SCOTT has to disappear and the
import would not restore them. This script can be used to drop someones tables:

--------------------------------------------------------------------
set heading off
set feedback off
set verify off
set echo off

spool tmp.sql
select 'drop table &1..' || table_name || ' cascade constraints;'
from dba_tables
where owner = upper('&1')
/
spool off
@tmp.sql
--------------------------------------------------------------------

3) You would modify the user to *not* have unlimited tablespace (else the IMP
will just put the objects right back into the tablespace they came from) and
then give them unlimited quota's on the new tablespace you want the objects to
go into and on their temporary tablespace (for the sorts the index creates will
do)

alter user SCOTT default tablespace NEW_TABLESPACE
/
revoke unlimited tablespace from SCOTT
/
alter user SCOTT quota unlimited on NEW_TABLESPACE
/
alter user SCOTT quota unlimited on SCOTTS_TEMPORARY_TABLESPACE
/

4) you will IMP the data back in for that user. IMP will rewrite the create
statements to use the users default tablespace when it discovers that it cannot
create the objects in their original tablespace. Please make sure to review the
file imp.log after you do this for any and all errors after you import.

imp userid=scott/tiger full=y ignore=y log=imp.log

5) you can optionally restore 'unlimited tablespace' to this user (or not). If
you do not, this user can only create objects in this new tablespace and temp
(which in itself is not a bad thing)...

As with any operation of this magnitude -- please test these procedures on a
small test account (such as SCOTT) to become familar with them.

A couple of side notes:

o the alter table move/alter index rebuild is more flexible and faster the
exp/imp (and less error prone -- you never actually drop the objects).
Additionally, it would be easy to modify the script to move TABLES to one
tablespace and INDEXES to a different tablespace. The drawback to using this
method is the you cannot move a table with a LONG or LONG RAW. You must exp
that table and imp it into a table. You can do this easily by exporting the
table with the LONG/LONG RAW, dropping that table -- creating an empty version
of this table in the new tablespace and importing just that table.

o if you use the exp/imp, it is upto you to ensure that no modifications happen
to the tables after you begin the export. There are no mechanisms in place to
ensure this -- you must do this (else you will lose changes)

>
>Please let me know.
>
>Thanks a lot in advance.
>
>Regards
>
>Stefano Bracco
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st

Thomas Kyte [email protected]
Oracle Service Industries Reston, VA USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation

Answer 2:

Hi.

Export to blame.
During EXPORT table definitions are exported with original ( SYSTEM in
your case ) tablespace.
You have to create all tables manually in a new tablespace and only
afterward IMPORT data only.

HTH. Michael.

Answer 3:

1. Export layout only
2. Edit file (it's text) and change from system to your new schema
3. Export Data into a different file
4. Run the first file from SQL*Plus to create the table
5. Import the second file
(don't forget to change the default tablespace for the user to the new
tablespace.)

Brad

Hosted by www.Geocities.ws