为了把用户按表空间归类,需要把用户所有的当前数据转移到另一个表空间里,那么可以使用impdp的remap_tablespace参数。下面就这一内容进行实验。
还是以test_user为例。
先查看用户的表空间属性:
21:56:08 SQL> select username,default_tablespace,temporary_tablespace from dba_users
21:56:41 2 where username='TEST_USER';
USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
------------------------------ ------------------------------ ------------------------------
TEST_USER USERS TEMP
21:56:48 SQL>
用户当前的对象及所在位置:
test_user$orcl@localhost.localdomain SQL> select object_name,object_type,status from user_objects;
OBJECT_NAME
------------------------------------------------------------------------------------------------------------------------------------------------------
OBJECT_TYPE STATUS
--------------------------------------------------------- ---------------------
PK_TEST
INDEX VALID
TEST_T
TABLE VALID
TEST_VW
VIEW VALID
test_user$orcl@localhost.localdomain SQL> select segment_name,segment_type,tablespace_name from user_extents;
SEGMENT_NAME
------------------------------------------------------------------------------------------------------------------------------------------------------
SEGMENT_TYPE TABLESPACE_NAME
------------------------------------------------------ ------------------------------------------------------------------------------------------
TEST_T
TABLE USERS
PK_TEST
INDEX USERS
整理后,希望用户test_user的对象位于test_tbsp表空间中。
当前情况和目标已经确定,下面开始实现。
首先,以schemas方式导出用户test_user的全部数据:
[oracle@localhost log]$ expdp system/orcl directory=data_pump_dir dumpfile=test_user.dmp schemas=test_user
Export: Release 10.2.0.1.0 - Production on 星期日, 14 11月, 2010 22:08:46
Copyright (c) 2003, 2005, Oracle. All rights reserved.
。。。
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/u01/app/oracle/product/10.2.0/db_1/rdbms/log/test_user.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 22:09:20
[oracle@localhost log]$
说明:使用system导出是为了导出test_user schema中的全部内容,包括用户创建和权限授予部分,使用用户本身导出则不包含这些内容。
其次,drop用户及其全部对象:
21:56:48 SQL> drop user test_user;
drop user test_user
*
ERROR at line 1:
ORA-01922: CASCADE must be specified to drop 'TEST_USER'
22:13:46 SQL> drop user test_user cascade;
User dropped.
22:14:02 SQL>
最后,以remap_tablespace方式导入test_user的全部内容:
[oracle@localhost log]$ ls
export.log test_user.dmp
[oracle@localhost log]$ impdp system/orcl directory=data_pump_dir dumpfile=test_user.dmp remap_tablespace=users:test_tbsp
Import: Release 10.2.0.1.0 - Production on 星期日, 14 11月, 2010 22:15:53
Copyright (c) 2003, 2005, Oracle. All rights reserved.
。。。
Job "SYSTEM"."SYS_IMPORT_FULL_02" successfully completed at 22:16:04
[oracle@localhost log]$
查看导入的结果:
22:17:19 SQL> select username,default_tablespace,temporary_tablespace from dba_users
22:17:24 2 where username='TEST_USER';
USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
------------------------------ ------------------------------ ------------------------------
TEST_USER TEST_TBSP TEMP
22:17:30 SQL>
注意:经过导入后,用户test_user的缺省表空间被改成了remap_tablespace的目的表空间,有点出乎意料。
[oracle@localhost ~]$ rlwrap sqlplus test_user/test_user
SQL*Plus: Release 10.2.0.1.0 - Production on 星期日 11月 14 22:20:17 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
test_user$orcl@localhost.localdomain SQL> select object_name,object_type,status from user_objects;
OBJECT_NAME
------------------------------------------------------------------------------------------------------------------------------------------------------
OBJECT_TYPE STATUS
--------------------------------------------------------- ---------------------
TEST_T
TABLE VALID
PK_TEST
INDEX VALID
TEST_VW
VIEW VALID
test_user$orcl@localhost.localdomain SQL> select segment_name,segment_type,tablespace_name from user_extents;
SEGMENT_NAME
------------------------------------------------------------------------------------------------------------------------------------------------------
SEGMENT_TYPE TABLESPACE_NAME
------------------------------------------------------ ------------------------------------------------------------------------------------------
TEST_T
TABLE TEST_TBSP
PK_TEST
INDEX TEST_TBSP
test_user$orcl@localhost.localdomain SQL>
说明:导入以后,用户test_user还可以使用原来的密码登陆。用户的原有对象全部恢复,并且表,索引都存放到了remap_tablespace的目的表空间中。
还是以test_user为例。
先查看用户的表空间属性:
21:56:08 SQL> select username,default_tablespace,temporary_tablespace from dba_users
21:56:41 2 where username='TEST_USER';
USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
------------------------------ ------------------------------ ------------------------------
TEST_USER USERS TEMP
21:56:48 SQL>
用户当前的对象及所在位置:
test_user$orcl@localhost.localdomain SQL> select object_name,object_type,status from user_objects;
OBJECT_NAME
------------------------------------------------------------------------------------------------------------------------------------------------------
OBJECT_TYPE STATUS
--------------------------------------------------------- ---------------------
PK_TEST
INDEX VALID
TEST_T
TABLE VALID
TEST_VW
VIEW VALID
test_user$orcl@localhost.localdomain SQL> select segment_name,segment_type,tablespace_name from user_extents;
SEGMENT_NAME
------------------------------------------------------------------------------------------------------------------------------------------------------
SEGMENT_TYPE TABLESPACE_NAME
------------------------------------------------------ ------------------------------------------------------------------------------------------
TEST_T
TABLE USERS
PK_TEST
INDEX USERS
整理后,希望用户test_user的对象位于test_tbsp表空间中。
当前情况和目标已经确定,下面开始实现。
首先,以schemas方式导出用户test_user的全部数据:
[oracle@localhost log]$ expdp system/orcl directory=data_pump_dir dumpfile=test_user.dmp schemas=test_user
Export: Release 10.2.0.1.0 - Production on 星期日, 14 11月, 2010 22:08:46
Copyright (c) 2003, 2005, Oracle. All rights reserved.
。。。
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/u01/app/oracle/product/10.2.0/db_1/rdbms/log/test_user.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 22:09:20
[oracle@localhost log]$
说明:使用system导出是为了导出test_user schema中的全部内容,包括用户创建和权限授予部分,使用用户本身导出则不包含这些内容。
其次,drop用户及其全部对象:
21:56:48 SQL> drop user test_user;
drop user test_user
*
ERROR at line 1:
ORA-01922: CASCADE must be specified to drop 'TEST_USER'
22:13:46 SQL> drop user test_user cascade;
User dropped.
22:14:02 SQL>
最后,以remap_tablespace方式导入test_user的全部内容:
[oracle@localhost log]$ ls
export.log test_user.dmp
[oracle@localhost log]$ impdp system/orcl directory=data_pump_dir dumpfile=test_user.dmp remap_tablespace=users:test_tbsp
Import: Release 10.2.0.1.0 - Production on 星期日, 14 11月, 2010 22:15:53
Copyright (c) 2003, 2005, Oracle. All rights reserved.
。。。
Job "SYSTEM"."SYS_IMPORT_FULL_02" successfully completed at 22:16:04
[oracle@localhost log]$
查看导入的结果:
22:17:19 SQL> select username,default_tablespace,temporary_tablespace from dba_users
22:17:24 2 where username='TEST_USER';
USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
------------------------------ ------------------------------ ------------------------------
TEST_USER TEST_TBSP TEMP
22:17:30 SQL>
注意:经过导入后,用户test_user的缺省表空间被改成了remap_tablespace的目的表空间,有点出乎意料。
[oracle@localhost ~]$ rlwrap sqlplus test_user/test_user
SQL*Plus: Release 10.2.0.1.0 - Production on 星期日 11月 14 22:20:17 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
test_user$orcl@localhost.localdomain SQL> select object_name,object_type,status from user_objects;
OBJECT_NAME
------------------------------------------------------------------------------------------------------------------------------------------------------
OBJECT_TYPE STATUS
--------------------------------------------------------- ---------------------
TEST_T
TABLE VALID
PK_TEST
INDEX VALID
TEST_VW
VIEW VALID
test_user$orcl@localhost.localdomain SQL> select segment_name,segment_type,tablespace_name from user_extents;
SEGMENT_NAME
------------------------------------------------------------------------------------------------------------------------------------------------------
SEGMENT_TYPE TABLESPACE_NAME
------------------------------------------------------ ------------------------------------------------------------------------------------------
TEST_T
TABLE TEST_TBSP
PK_TEST
INDEX TEST_TBSP
test_user$orcl@localhost.localdomain SQL>
说明:导入以后,用户test_user还可以使用原来的密码登陆。用户的原有对象全部恢复,并且表,索引都存放到了remap_tablespace的目的表空间中。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/24131851/viewspace-678080/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/24131851/viewspace-678080/