How to Move Table…

来源：互联网发布：施耐德m258编程软件编辑：程序博客网时间：2024/06/06 03:43

如何将表从一个表空间转移到另一个表空间，参考如下文档

How to Move Tables from One Tablespace toAnother. (文档 ID 147356.1) 转到底部

--------------------------------------------------------------------------------

***Checked for relevance on 02-Nov-2011***

=========
Abstract:
=========

This is intended for any DBA who wishes to move tables acrosstablespace.

NOTE:
This article covers also information as was published in previouslyavailable Note 158162.1

There are 2 methods we can use to do this. One isto use a combination of
"alter table X move tablespace Y" and "alter index X rebuildtablespace Y"
-- this works in Oracle8i release 8.1 and upONLY.
The other method is an Export/Import

When deciding which method is best for your situation, pleaseconsider the following:

- the alter table move/alter index rebuildis more flexible and faster than the
    exp/imp (and less error prone -- you never actually drop theobjects).
    Additionally, it would be easy to modify the script to move TABLESto 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 LONGRAW. You
    must exp that table and imp it into a table. Youcan do this easily by
    exporting the table with the LONG/LONG RAW, dropping thattable
   -- creating an empty versionof this table in the new tablespace and
    importing just that table.

- if you use the exp/imp, it is up to you toensure that no modifications
    happen to the tables after you begin theexport.
   There are no mechanisms inplace to ensure this -- you must do this (else
    you will lose changes)

ALTER TABLE MOVE TABLESPACE METHOD:
===================================

We will start with the basic syntax below followed by a scriptthat you can cut and paste:

Syntax
-----------
alter table move storage() tablespace

Example
-------------
SQL> create table ftab(x number)storage(initial 20K next 20K) tablespace users;

SQL> create index iftab on ftab(x)tablespace users;
Index Created

SQL> alter table ftab movestorage(initial 2k next 2k) tablespace trans;
Table altered.

SQL> select table_name,tablespace_namefrom dba_tables where table_name='FTAB';

TABLE_NAME                    TABLESPACE_NAME
------------------------------------------------------------
FTAB                          TRANS

SQL> select index_name,tablespace_name fromdba_indexes where index_name='IFTAB';

INDEX_NAME TABLESPACE_NAME
------------------------------------------------------------
IFTAB USERS

NOTE: When moving a table in this manner, therowid values are changed.
Indexes depend on the rowid information and therefore they willbecome unusable.
You will get an ORA-1502 unless you rebuild the associatedindex(es).

Attached is a script called moveall.sql. The data extracted fromthis must be used with caution.
It begins by moving a table and then rebuilding each of the indexeson that
table. Since the indexes on the tables beingmoved will become unusable after
moving a table, this script rebuilds them right after moving atable -- before moving
the next table (to reduce downtime).

===========
Disclaimer:
===========

This script is provided for educational purposes only. It is NOTsupported by
Oracle World Wide Technical Support. The scripthas been tested and appears
to work as intended. However, you should alwaystest any script before
relying on it.

PROOFREAD THIS SCRIPT PRIOR TO USING IT! Due to differences in theway text
editors, email packages and operating systems handle textformatting (spaces,
tabs and carriage returns), this script may not be in an executablestate when
you first receive it. Check over the script toensure that errors of this
type are corrected.

Warning:
------------
Running the moveall.sql script is harmless as it iswritten. 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 (forexample: 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 onyour system),
and then run it.

=========
Script1:
=========

----------- cut ---------------------- cut -------------- cut--------------

set echo off

column order_col1noprint
column order_col2noprint

   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 RUNthe generated commands
   REM ELSE edit tmp.sql, modifyas needed and run it
   REM @tmp

-----------------------cut here -----------------------

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 intoanother
tablespace preserving the storage characteristics currentlyassigned to the
object. For example, when we run moveall.sql inthe SCOTT schema, we might see:

SQL> @moveall
SQL> set echo off

   alter TABLE ACCOUNTmove
    tablespaceusers
    storage (initial 10240 next 10240
    minextents 1maxextents 121
    pctincrease50 freelists 1);

   alter TABLE BONUSmove
    tablespaceusers
    storage (initial 10240 next 10240
    minextents 1maxextents 121
    pctincrease50 freelists 1);

   alter TABLE DEPTmove
    tablespaceusers
    storage (initial 10240 next 10240
    minextents 1maxextents 121
    pctincrease50 freelists 1);

   alter INDEX PK_DEPTrebuild
    tablespaceusers
    storage (initial 10240 next 10240
    minextents 1maxextents 121
    pctincrease50 freelists 1);

....

IMPORT/EXPORT METHOD:
=====================

Another method would be to use EXPort andIMPort. This is a basic outline of the standardmethod:
Please see the examples below for more detailed instructions.

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

EXAMPLES:
This section discusses three ways you can move tables from onetablespace to
another tablespace using Export/Import.

A. On a Per Table Basis
B. On a Per User Basis
C. From user A to user B

These steps require a SQL*Plus account with DBA privileges toset-up the appropriate
user privileges.

A. Moving Tables on a Per Table Basis
-------------------------------------

The following steps will move tables on a per tablebasis:

1. Check the tablespaces in use and perform the table levelexport.

2. If you have enough space in the database, rename thetable(s) and drop the
indexes. After the table hasbeen reimported successfully then drop the
.

   If you do not have enoughspace in the database to rename the table(s) drop it.
   If it is necessary to drop thetable, then it would be wise to perform a backup
   of the database prior totaking this step.

   It would wise to checkthe dump file before performing the drop to ensure that
   the file can be read. Toperform a check of the dump file use the following
   syntax

imp username/passwordfile=expdat.dmp show=yes log=imp.log

This will read the dumpfile and give you some confidence that it can be used
to re-build the tables. Noobjects are imported/created at this point.

3. Run import with INDEXFILE= to get the create table andcreate
index statements.

4. Edit the resulting file, and set the tablespace clause toindicate the new
tablespace. Delete the createindex statements.

5. Grant quota on the new tablespace.

6. Run the edited create script to create the table(s).

7. Run import with IGNORE=Y to populate the new table(s) andcreate the index(es).

B. Moving Tables on a Per User Basis
------------------------------------

The following steps will move tables on a per userbasis:

1. Perform a user level or full database export.

2. Drop or rename the table(s) you are moving.

   It may wise to check thedump file before performing the drop to ensure that
   the file can be read. Toperform a check of the dump file use the following
   syntax

imp username/passwordfile=expdat.dmp show=yes log=imp.log

This will read the dumpfile and give you some confidence that it can be used
to re-build the tables. Noobjects are imported/created at this point.

3. For the user with the tables that are being moved, performthe following:

(a) set their defaulttablespace to the desired tablespace

ALTER USER DEFAULT TABLESPACE ;

(b) remove quota on theold tablespace

ALTER USER QUOTA 0 ON ;

ALTER USER QUOTA ON ;

   (d) If the user has theRESOURCE role, revoke UNLIMITED
      TABLESPACE from that user. Also ensure that the user does not
      have DBA role.

SELECT GRANTED_ROLE FROM DBA_ROLE_PRIVS
WHERE GRANTEE='';

REVOKE UNLIMITED TABLESPACE FROM ;

REVOKE DBA from ;

This is done so that the import does not bring the objectsback
into the ?old? tablespace.

4. Test to make sure that the user can no longer create objects inthe 'old'
tablespace. Create a table and specify the old tablespace:

CREATE TABLE JUNK (A NUMBER) TABLESPACE ;

If you receive anORA-01950 "no privileges on tablespace '%s'", then you
have succeeded in removing thecreate object privilege for that user in the specifiedtablespace.

5. Perform a user level import of this user.

6. Regrant the privileges that were revoked in step 3, ifrequired.

C. MovingTables from userA to userB
---------------------------------------

IMPORT will always import tables into a tablespace that hasthe same name as
the original tablespace (in the original database where the EXPORTwas done
from), regardless of what is userB's default tablespace.

This assumes the destination database has a tablespace withthe same name as
the original tablespace from which userA's tables wereEXPORTed.

Consider the following:

An Export has been done in database A of userA's tables, whichare in tablespace
USER_A_TS. You are attempting to import intodatabase B into userB's schema
which is in tablespace USER_B_TS.

You do the IMPORT with FROMUSER=USERA TOUSER=USERB but userA'stables still are
being put in tablespace USER_A_TS and not in userB's defaulttablespace of
USER_B_TS.

The following steps will move tables from userA tablespaceUSER_A_TS to userB
tablespace USER_B_TS:

1. Perform a user level export for user_a.

2. For userB, check tablespace quotas on tablespaces USER_A_TSand USER_B_TS.

(a) Remove userB's quotas from USER_A_TS (which isuserA's
original tablespace).

First, note any originalquotas if any:

SELECT * FROMDBA_TS_QUOTAS where username = '';

Now remove them:

ALTERUSER QUOTA 0 ON TABLESPACE ;

(b) If the user has theRESOURCE role, revoke UNLIMITED TABLESPACE
from that user.

SELECT GRANTED_ROLE FROMDBA_ROLE_PRIVS WHERE GRANTEE='';

REVOKE UNLIMITEDTABLESPACE from ;

3. Test to make sure that the user can no longer createobjects in the 'old'
tablespace. Create a table and specify the old tablespace:

CREATE TABLE JUNK (ANUMBER) TABLESPACE ;

   If you receive anORA-01950 no privileges on tablespace '%s', then you have
   succeeded in removing thecreate object privilege for that user in the
   specified tablespace.

Check to see that userBcan create table(s) in the new tablespace USER_B_TS.

CREATE TABLE JUNK (ANUMBER) TABLESPACE ;

If you receive ORA-01536:space quota exceeded for tablespace USER_B_TS,
then:

ALTERUSER QUOTA ON ;

4. Perform the import.

5. Set userB's quotas back if needed:

ALTERUSER QUOTA nn ON TABLESPACE ;

Regrant the privilegesthat were revoked in step 2, if required.

EXAMPLES

A. On a Per Table Basis
-----------------------

1. Check the tablespaces in use and perform the table levelexport

   SQL> CONNscott/tiger

   SQL> SELECT table_name,tablespace_name FROM user_tables
       WHERE table_name = 'EMP';

  TABLE_NAME                    TABLESPACE_NAME
   ------------------------------------------------------------
  EMP                           USERS

   SQL> SELECT index_name,tablespace_name FROMuser_indexes
       WHERE table_name ='EMP';

  INDEX_NAME                    TABLESPACE_NAME
   ------------------------------------------------------------
  PK_EMP                        USERS

   exp scott/tiger file=emp.dmprows=yestables=emp

2. Drop or rename the table you wish to move

SQL> CONNscott/tiger

   SQL> RENAME emp toold_emp;

   SQL> SELECT index_name,tablespace_name FROMuser_indexes
       WHERE table_name ='EMP';

       no rowsselected

   SQL> SELECT index_name,tablespace_name FROMuser_indexes
       WHERE table_name = 'OLD_EMP';

  TABLE_NAME                    TABLESPACE_NAME
   ------------------------------------------------------------
  OLD_EMP                       USERS

3. Run import with INDEXFILE= to get a file with the createtable
and index statements.

$ imp scott/tigerfile=emp.dmpindexfile=emp.sql

4. Using an editor (like ?vi?) to make the following changes:

   - Remove?REM ? from the CREATE and ALTER TABLEstatements
   - Remove theCONNECT and CREATE INDEX statements
   - Replace thetablespace names with the new name (?NEW_USERS?)

After the edit, the fileshould look similar to:

   CREATE TABLE"SCOTT"."EMP" ("EMPNO" NUMBER(4, 0), "ENAME"
   VARCHAR2(10), "JOB"VARCHAR2(9), "MGR" NUMBER(4, 0), "HIREDATE" DATE,
   "SAL" NUMBER(7, 2), "COMM"NUMBER(7, 2), "DEPTNO" NUMBER(2, 0))
   PCTFREE 10 PCTUSED 40 INITRANS1 MAXTRANS 255 LOGGING STORAGE(INITIAL
   131072 NEXT 65536 MINEXTENTS 1MAXEXTENTS 2147483645 PCTINCREASE 50
   FREELISTS 1 FREELIST GROUPS 1BUFFER_POOL DEFAULT) TABLESPACE "NEW_USERS" ;

   ALTER TABLE "SCOTT"."EMP"ADD CONSTRAINT "PK_EMP" PRIMARY KEY
   ("EMPNO") USING INDEX PCTFREE10 INITRANS 2 MAXTRANS 255
   STORAGE(INITIAL 131072 NEXT65536 MINEXTENTS 1 MAXEXTENTS 2147483645
   PCTINCREASE 50 FREELISTS 1FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
   TABLESPACE "NEW_USERS" ENABLE;

ALTER TABLE "SCOTT"."EMP"ADD CONSTRAINT "FK_DEPTNO" FOREIGN KEY
("DEPTNO") REFERENCES "DEPT"("DEPTNO") ENABLE NOVALIDATE ;

ALTER TABLE "SCOTT"."EMP"ENABLE CONSTRAINT "PK_EMP" ;

ALTER TABLE "SCOTT"."EMP"ENABLE CONSTRAINT "FK_DEPTNO" ;

5. Grant quota on the new tablespace

   SQL> CONNsystem/manager

   SQL> ALTER USER scott QUOTA2m ONnew_users;

   If the user has no quota, thenthe create will fail with

   CREATE TABLE"SCOTT"."EMP" ("EMPNO" NUMBER(4, 0), "ENAME"
  *
   ERROR at line1:
   ORA-01536: space quotaexceeded for tablespace 'NEW_USERS

6. Run the script to create the tables

SQL> CONNscott/tiger

   SQL>@emp.sql

   SQL> SELECT table_name,tablespace_name FROMuser_tables
       WHERE table_name ='EMP';

  TABLE_NAME                    TABLESPACE_NAME
   ------------------------------------------------------------
  EMP                           NEW_USERS

7. Run the import with IGNORE=Y to populate the new table(s) andcreate the
index(es).

$ imp scott/tigerfile=emp.dmpignore=yes

B. On a Per User Basis
----------------------

1. Perform a user level or full database export

$ exp scott/tigerfile=scott.dmplog=scott.log

2. Drop or rename the table(s) you are moving

   SQL> CONNscott/tiger

   SQL> RENAME emp TOold_emp;

   SQL> RENAME dept TOold_dept;

3. Grant quota on the new tablespace

SQL> CONNsystem/manager

   SQL> ALTER USER scottDEFAULT TABLESPACEnew_users;

   SQL> ALTER USER scott QUOTA0 ONusers;

   SQL> ALTER USER scott QUOTA2m ONnew_users;

   SQL> REVOKE unlimitedtablespace FROMscott;

   SQL> REVOKE dba FROMscott;

4. Test to make sure that the user can no longer createobjects in the old?
   tablespace. Create a table andspecify the old tablespace.

   SQL> CONNscott/tiger

   SQL> CREATE TABLE test (avarchar2(10)) tablespaceusers;
       *
   ERROR at line1:
   ORA-01536: space quotaexceeded for tablespace'USERS'

5. Perform the import with IGNORE=YES

$ imp scott/tigerfile=scott.dmp log=imp_scott.logignore=yes

6. Re-grant the privileges that were revoked in step 3, ifrequired.

   SQL> CONNsystem/manager

   SQL> GRANT dba, resource,unlimited tablespace TOscott;

C. From user A to user B
------------------------

The following steps will move tables from userA tablespaceUSER_A_TS
to userB tablespace USER_B_TS:

1. Perform a user level export for user_a

   $ exp user_a/user_afile=user_a.dmp

2. For userB check tablespace quotas on tablespaces USER_A_TS andUSER_B_TS
   and then amendaccordingly

   SQL> SELECTtablespace_name, max_blocks FROM dba_ts_quotas
       WHERE username ='USER_B';

  TABLESPACE_NAME               MAX_BLOCKS
   ----------------------------------------
  USER_B_TS                            256
  USER_A_TS                            256

   SQL> ALTER USER user_bQUOTA 0 onuser_a_ts;

   SQL> REVOKE unlimitedtablespace FROMuser_b;

   SQL> REVOKE dba FROMuser_b;

   SQL> ALTER USER user_bQUOTA 2m ON user_b_ts;

3. Test to make sure that the user can no longer create objects inthe ?old?
   tablespace. Create a table andspecify the old tablespace.

   SQL> CONNuser_b/user_b

   SQL> CREATE TABLE test (avarchar2(10)) TABLESPACE user_a_ts;
   create table test (avarchar2(10)) tablespaceuser_a_ts
  *
   ERROR at line1:
   ORA-01536: space quotaexceeded for tablespace'USER_A_TS'

   Check to see that userB cancreate table(s) in the new tablespace, USER_B_TS.

   SQL> CREATE TABLE JUNK (ANUMBER) TABLESPACE ;
       *
       ERROR at line 1:
       ORA-01536: space quota exceeded for tablespace ?USER_B_TS?

4. Perform the import.

$ imp user_b/user_bfromuser=user_a touser=user_b file=user_a.dmp

5. Re-grant the privileges that were revoked in step 2, ifrequired.

SQL> connsystem/manager

SQL> ALTER USER user_bQUOTA 2m ONuser_a_ts;

SQL> GRANT unlimitedtablespace, dba TO user_b

0 0

How&nbsp;to&nbsp;Move&nbsp;Table…

How to Move Table…