移动表到另外一个表空间
浏览数：21 /
时间：2015年06月11日
把一个表从一个表空间移动到另外一个表空间有两种方式
一，使用"alter table X move tablespace Y" and "alter index X rebuild tablespace Y"
优点：简单，快速
缺点：不能移动含有LONG or LONG RAW字段的表
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).
需要重建索引
下面是一个批量脚本
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
上面的脚本在要迁移的表的用户下执行，得到该用户下移动的所有move语句和rebuild index语句，
Enter value for 1: Users ---- 这里输入目标表空间
例如，我们在scott用户下运行此脚本
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);
....
二，使用Export/Import
有三种方式
A. On a Per Table Basis B. On a Per User Basis C. From user A to user B
例；
A. On a Per Table Basis ----------------------- 1. Check the tablespaces in use and perform the table level exportSQL> 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=emp2. Drop or rename the table you wish to moveSQL> 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 USERS3. 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:vi打开 删除下面的注释 并且修改原来的表空间为新的表空间- 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 tablespaceSQL> 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_USERS6. Run the script to create the tablesSQL> 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_USERS7. Run the import with IGNORE=Y to populate the new table(s) and create the 导入数据 index(es).$ imp scott/tiger file=emp.dmp ignore=yesB. 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. SQL> CREATE TABLE JUNK (A NUMBER) TABLESPACE <user_b_ts>; * ERROR at line 1: ORA-01536: space quota exceeded for tablespace ?USER_B_TS? 4. Perform the import. $ imp user_b/user_b fromuser=user_a touser=user_b file=user_a.dmp 5. Re-grant the privileges that were revoked in step 2, if required. SQL> conn system/manager SQL> ALTER USER user_b QUOTA 2m ON user_a_ts; SQL> GRANT unlimited tablespace, dba TO user_b
参考：MOS Doc ID 147356.1
郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
