How to Move Table…

最新推荐文章于 2022-05-28 23:16:11 发布

babymouse1212

最新推荐文章于 2022-05-28 23:16:11 发布

阅读量576

点赞数

分类专栏：经验收集

本文链接：https://blog.csdn.net/babymouse1212/article/details/71079101

版权

经验收集专栏收录该内容

84 篇文章 0 订阅

订阅专栏

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

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

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

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

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

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

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

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"
-- this works in Oracle8i release 8.1 and up ONLY.
The other method is an Export/Import

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

- the alter table move/alter index rebuild is more flexible and faster than 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.

- if you use the exp/imp, it is up to 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)

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

We will start with the basic syntax below followed by a script that 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 move storage(initial 2k next 2k) tablespace trans;
Table altered.

SQL> select table_name,tablespace_name from dba_tables where table_name='FTAB';

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

SQL> select index_name,tablespace_name from dba_indexes where index_name='IFTAB';

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

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

Attached is a script called moveall.sql. The data extracted from this must be used with caution.
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
moving a table, this script rebuilds them right after moving a table -- before moving
the next table (to reduce downtime).

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

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

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

Warning:
------------
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 (for example: 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.

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

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

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

----------------------- 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 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:

SQL> @moveall
SQL> 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);

....

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

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

- EXP the user account.
- 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
- revoke UNLIMITED TABLESPACE from the user
- alter the users default tablespace to the target tablespace
- give the user an unlimited quota on this new tablespace and their temporary
tablespace
- IMP this users data.

EXAMPLES:
This section discusses three ways you can move tables from one tablespace 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 to set-up the appropriate
user privileges.

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

The following steps will move tables on a per table basis:

1. Check the tablespaces in use and perform the table level export.

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

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

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

imp username/password file=expdat.dmp show=yes log=imp.log

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

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

4. Edit the resulting file, and set the tablespace clause to indicate the new
tablespace. Delete the create index 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) and create the index(es).

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

The following steps will move tables on a per user basis:

1. Perform a user level or full database export.

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

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

imp username/password file=expdat.dmp show=yes log=imp.log

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

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

(a) set their default tablespace to the desired tablespace

ALTER USER DEFAULT TABLESPACE ;

(b) remove quota on the old tablespace

ALTER USER QUOTA 0 ON ;

ALTER USER QUOTA ON ;

(d) If the user has the RESOURCE 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 objects back
into the ?old? tablespace.

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

CREATE TABLE JUNK (A NUMBER) TABLESPACE ;

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

5. Perform a user level import of this user.

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

C. Moving Tables from userA to userB
---------------------------------------

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

This assumes the destination database has a tablespace with the same name as
the original tablespace from which userA's tables were EXPORTed.

Consider the following:

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

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

The following steps will move tables from userA tablespace USER_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_TS and USER_B_TS.

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

First, note any original quotas if any:

SELECT * FROM DBA_TS_QUOTAS where username = '';

Now remove them:

ALTER USER QUOTA 0 ON TABLESPACE ;

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

SELECT GRANTED_ROLE FROM DBA_ROLE_PRIVS WHERE GRANTEE='';

REVOKE UNLIMITED TABLESPACE from ;

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

CREATE TABLE JUNK (A NUMBER) TABLESPACE ;

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

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

CREATE TABLE JUNK (A NUMBER) TABLESPACE ;

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

ALTER USER QUOTA ON ;

4. Perform the import.

5. Set userB's quotas back if needed:

ALTER USER QUOTA nn ON TABLESPACE ;

Regrant the privileges that were revoked in step 2, if required.

EXAMPLES

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

1. Check the tablespaces in use and perform the table level export

SQL> CONN scott/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 FROM user_indexes
WHERE table_name = 'EMP';

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

exp scott/tiger file=emp.dmp rows=yes tables=emp

2. Drop or rename the table you wish to move

SQL> CONN scott/tiger

SQL> RENAME emp to old_emp;

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

no rows selected

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

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

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

$ imp scott/tiger file=emp.dmp indexfile=emp.sql

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

- Remove ?REM ? from the CREATE and ALTER TABLE statements
- Remove the CONNECT and CREATE INDEX statements
- Replace the tablespace names with the new name (?NEW_USERS?)

After the edit, the file should 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 INITRANS 1 MAXTRANS 255 LOGGING STORAGE(INITIAL
131072 NEXT 65536 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 50
FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "NEW_USERS" ;

ALTER TABLE "SCOTT"."EMP" ADD CONSTRAINT "PK_EMP" PRIMARY KEY
("EMPNO") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 131072 NEXT 65536 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 50 FREELISTS 1 FREELIST 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> CONN system/manager

SQL> ALTER USER scott QUOTA 2m ON new_users;

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

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

6. Run the script to create the tables

SQL> CONN scott/tiger

SQL> @emp.sql

SQL> SELECT table_name, tablespace_name FROM user_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) and create the
index(es).

$ imp scott/tiger file=emp.dmp ignore=yes

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

1. Perform a user level or full database export

$ exp scott/tiger file=scott.dmp log=scott.log

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

SQL> CONN scott/tiger

SQL> RENAME emp TO old_emp;

SQL> RENAME dept TO old_dept;

3. Grant quota on the new tablespace

SQL> CONN system/manager

SQL> ALTER USER scott DEFAULT TABLESPACE new_users;

SQL> ALTER USER scott QUOTA 0 ON users;

SQL> ALTER USER scott QUOTA 2m ON new_users;

SQL> REVOKE unlimited tablespace FROM scott;

SQL> REVOKE dba FROM scott;

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

SQL> CONN scott/tiger

SQL> CREATE TABLE test (a varchar2(10)) tablespace users;
*
ERROR at line 1:
ORA-01536: space quota exceeded for tablespace 'USERS'

5. Perform the import with IGNORE=YES

$ imp scott/tiger file=scott.dmp log=imp_scott.log ignore=yes

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

SQL> CONN system/manager

SQL> GRANT dba, resource, unlimited tablespace TO scott;

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

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

1. Perform a user level export for user_a

$ exp user_a/user_a file=user_a.dmp

2. For userB check tablespace quotas on tablespaces USER_A_TS and USER_B_TS
and then amend accordingly

SQL> SELECT tablespace_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_b QUOTA 0 on user_a_ts;

SQL> REVOKE unlimited tablespace FROM user_b;

SQL> REVOKE dba FROM user_b;

SQL> ALTER USER user_b QUOTA 2m ON user_b_ts;

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

SQL> CONN user_b/user_b

SQL> CREATE TABLE test (a varchar2(10)) TABLESPACE user_a_ts;
create table test (a varchar2(10)) tablespace user_a_ts
*
ERROR at line 1:
ORA-01536: space quota exceeded for tablespace 'USER_A_TS'

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