SQL> col FILE_NAME format a30
SQL> select file_name,tablespace_name,online_status,status from dba_data_files ;
/oracle/emerp/bkj1.dbf BKJ1 ONLINE AVAILABLE
SQL> select TABLE_NAME,TABLESPACE_NAME from dba_tables where tablespace_name='BKJ1';

[table=98%,inherit]
[tr=inherit][backcolor=inherit][backcolor=inherit][size=22px]验证是否是自包含

SQL> execute dbms_tts.transport_set_check('bkj1',true);
PL/SQL procedure successfully completed.
SQL> select * from transport_set_violations;
no rows selected
SQL>
[table=98%,inherit]
[tr=inherit][backcolor=inherit][backcolor=inherit][size=22px]将表空间只读状态

SQL> alter tablespace bkj1 read only;
alter tablespace bkj1 read only
*
ERROR at line 1:
ORA-01644: tablespace 'BKJ1' is already read only
[table=98%,inherit]
[tr=inherit][backcolor=inherit][backcolor=inherit][size=22px]导出迁移表空间的目录元数据

[backcolor=inherit][size=22px] [backcolor=inherit] expdp \'/ as sysdba \' [backcolor=inherit][size=22px] dumpfile=pump_dir:bkj1.dmp nologfile=y
[backcolor=inherit][size=22px]transport_tablespaces=bkj1
[oracle@node2 pumpall]$ expdp system/oracle dumpfile=pump_dir:bkj1.dmp nologfile=y
Export: Release 10.2.0.5.0 - 64bit Production on Monday, 23 June, 2014 15:28:04
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** dumpfile=pump_dir:bkj1.dmp nologfile=y
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 3.312 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

[table=98%,inherit]
[tr=inherit][backcolor=inherit][backcolor=inherit][size=22px]将备份文件和迁移表空间的数据文件复制到目标数据库

[oracle@node2 pumpall]$ ll
total 4492
-rw-r----- 1 oracle oinstall 4587520 Jun 23 15:28 bkj1.dmp
[backcolor=inherit][size=22px][oracle@node2 pumpall]$ scp bkj1.dmp 192.168.56.30:/backup/pumpall
oracle@192.168.56.30's password:
bkj1.dmp 100% 4480KB 4.4MB/s 00:00
[oracle@node2 pumpall]$
[oracle@node2 emerp]$ scp bkj1.dbf 192.168.56.30:/oracle/emerp
oracle@192.168.56.30's password:
bkj1.dbf 100% 100MB 9.1MB/s 00:11
[oracle@node2 emerp]$
[table=98%,inherit]
[tr=inherit][backcolor=inherit][backcolor=inherit][size=22px]在目标数据库中导入迁移表空间

[backcolor=inherit][size=22px][oracle@node2 pumpall]$ impdp \'/ as sysdba \' dumpfile=bkj1.dmp transport_datafiles='/oracle/emerp/bkj1.dbf' directory=pump_dir
[backcolor=inherit]Import: Release 10.2.0.5.0 - 64bit Production on Monday, 23 June, 2014 15:51:11
[backcolor=inherit]Copyright (c) 2003, 2007, Oracle. All rights reserved.
[backcolor=inherit]Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
[backcolor=inherit]With the Partitioning, OLAP, Data Mining and Real Application Testing options
[backcolor=inherit]Master table "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
[backcolor=inherit]Starting "SYS"."SYS_IMPORT_TRANSPORTABLE_01": "/******** AS SYSDBA" dumpfile=bkj1.dmp transport_datafiles=/oracle/emerp/bkj1.dbf directory=pump_dir
[backcolor=inherit]Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
[backcolor=inherit]Processing object type TRANSPORTABLE_EXPORT/TABLE
[backcolor=inherit]Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
[backcolor=inherit]Job "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 15:51:13
[backcolor=inherit][oracle@node2 pumpall]$
[table=98%,inherit]
[tr=inherit][backcolor=inherit][backcolor=inherit]改为读写状态

SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
UNDOTBS ONLINE
SYSAUX ONLINE
TEMP ONLINE
USERS ONLINE
BSR ONLINE
UTEMP ONLINE
BIGK ONLINE
UNDO2 ONLINE
BKJ1 READ ONLY
TEMP1 ONLINE
TABLESPACE_NAME STATUS
------------------------------ ---------
TEMP3 ONLINE
RCAT_TBS ONLINE
TS1 ONLINE
14 rows selected.
SQL> alter tablespace bkj1 read write;
Tablespace altered.
SQL> select segment_name from dba_segments where tablespace_name='BKJ1';
SEGMENT_NAME
--------------------------------------------------------------------------------
TEST_EMP
SQL> SELECT * FROM TEST_EMP;
SELECT * FROM TEST_EMP
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> ^[[A " - rest of line ignored.
SQL> 042: unknown command "
SQL>
SQL> select * from scott.test_emp;
EMPNO ENAME JOB MGR HIREDATE SAL
---------- ---------- --------- ---------- ------------------- ----------
COMM DEPTNO
---------- ----------
7369 SMITH CLERK 7902 1980-12-17:00:00:00 801
20
7499 ALLEN SALESMAN 7698 1981-02-20:00:00:00 1601
300 30
7521 WARD SALESMAN 7698 1981-02-22:00:00:00 1251
500 30
EMPNO ENAME JOB MGR HIREDATE SAL
---------- ---------- --------- ---------- ------------------- ----------
COMM DEPTNO
---------- ----------
7566 JONES MANAGER 7839 1981-04-02:00:00:00 2976
20
7654 MARTIN SALESMAN 7698 1981-09-28:00:00:00 1251
1400 30
7698 BLAKE MANAGER 7839 1981-05-01:00:00:00 2851
30
EMPNO ENAME JOB MGR HIREDATE SAL
---------- ---------- --------- ---------- ------------------- ----------
COMM DEPTNO
---------- ----------
7782 CLARK MANAGER 7839 1981-06-09:00:00:00 2451
10
7788 SCOTT ANALYST 7566 1987-04-19:00:00:00 3001
20
7839 KING PRESIDENT 1981-11-17:00:00:00 5001
10
EMPNO ENAME JOB MGR HIREDATE SAL
---------- ---------- --------- ---------- ------------------- ----------
COMM DEPTNO
---------- ----------
7844 TURNER SALESMAN 7698 1981-09-08:00:00:00 1501
0 30
7876 ADAMS CLERK 7788 1987-05-23:00:00:00 1101
20
7900 JAMES CLERK 7698 1981-12-03:00:00:00 951
30
EMPNO ENAME JOB MGR HIREDATE SAL
---------- ---------- --------- ---------- ------------------- ----------
COMM DEPTNO
---------- ----------
7902 FORD ANALYST 7566 1981-12-03:00:00:00 3001
20
7934 MILLER CLERK 7782 1982-01-23:00:00:00 1301
10
14 rows selected.
SQL>
SQL> conn system/oracle
Connected.
SQL> conn scott/tiger
Error accessing PRODUCT_USER_PROFILE
Warning: Product user profile information not loaded!
You may need to run PUPBLD.SQL as SYSTEM
Connected.
SQL> show user
USER is "SCOTT"
SQL> create directory scott_pump_dir as '/backup';
Directory created.
SQL> conn system/oracle
Connected.
SQL> create directory pump_dir as '/backup/pumpall';
Directory created.
SQL> grant read on directory pump_dir to scott;
Grant succeeded.
SQL>
导入

[backcolor=inherit][backcolor=inherit]创建临时表空间
[backcolor=inherit][backcolor=inherit]SQL> create temporary tablespace temp3 tempfile '/oracle/emerp/temp.dbf' size 100M;
[backcolor=inherit][backcolor=inherit]Tablespace created.
[backcolor=inherit][backcolor=inherit]修改默认临时表空间

[backcolor=inherit][backcolor=inherit]SQL> create temporary tablespace temp3 tempfile '/oracle/emerp/temp3.dbf' size 100M;
[backcolor=inherit][backcolor=inherit]Tablespace created.
[backcolor=inherit][backcolor=inherit]SQL> alter tablespace default temporary tablespace temp3;
[backcolor=inherit][backcolor=inherit]alter tablespace default temporary tablespace temp3
[backcolor=inherit][backcolor=inherit] *
[backcolor=inherit][backcolor=inherit]ERROR at line 1:
[backcolor=inherit][backcolor=inherit]ORA-02140: invalid tablespace name
[backcolor=inherit][backcolor=inherit]SQL> conn system/oracle as sysdba

[backcolor=inherit]Connected.
[backcolor=inherit]SQL> alter database default temporary tablespace temp3;
[backcolor=inherit]Database altered.
[backcolor=inherit]SQL> alter database default temporary tablespace temp;
[backcolor=inherit]Database altered.
[table=98%,inherit]
[tr=inherit][backcolor=inherit]zai再次导入

Import: Release 10.2.0.5.0 - 64bit Production on Sunday, 22 June, 2014 14:15:11
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39002: invalid operation
ORA-39061: import mode TRANSPORTABLE conflicts with export mode SCHEMA
[oracle@node2 emerp]$

[oracle@node2 pumpall]$ impdp system/oracle directory=pump_dir dumpfile=bjk1.dmp tablespaces=bjk1 transport_datafiles='/oracle/emerp/bjk1.dbf'
Import: Release 10.2.0.5.0 - 64bit Production on Sunday, 22 June, 2014 16:11:39
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31640: unable to open dump file "/backup/pumpall/bjk1.dmp" for read
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

[oracle@node2 pumpall]$

[oracle@node2 emerp]$ impdp system/oracle directory=pump_dir dumpfile=bkj1.dmp tablespaces=bkj1 transport_datafiles='/oracle/emerp/bkj1.dbf'
Import: Release 10.2.0.5.0 - 64bit Production on Sunday, 22 June, 2014 16:14:08
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-31655: no data or metadata objects selected for job
Master table "SYSTEM"."SYS_IMPORT_TABLESPACE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TABLESPACE_01": system/******** directory=pump_dir dumpfile=bkj1.dmp tablespaces=bkj1 transport_datafiles=/oracle/emerp/bkj1.dbf
Job "SYSTEM"."SYS_IMPORT_TABLESPACE_01" successfully completed at 16:14:09
[oracle@node2 emerp]$

\'/ a

SQL> conn test
Enter password:
Error accessing PRODUCT_USER_PROFILE
Warning: Product user profile information not loaded!
You may need to run PUPBLD.SQL as SYSTEM
Connected.
SQL>

[oracle@node2 emerp]$
impdp system/oracle directory=pump_dir dumpfile=bkj1.dmp transport_datafiles='/oracle/emerp/bkj1.dbf' tablespaces=bkj1

impdp system/oracle directory=pump_dir dumpfile=bkj1.dmp
transport_datafiles='/oracle/emerp/bkj1.dbf'

[oracle@node2 pumpall]$ impdp system/oracle directory=pump_dir dumpfile=bkj1.dmp
Import: Release 10.2.0.5.0 - 64bit Production on Sunday, 22 June, 2014 17:13:41
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** directory=pump_dir dumpfile=bkj1.dmp
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39083: Object type TABLE failed to create with error:
ORA-00959: tablespace 'BKJ1' does not exist
Failing sql is:
CREATE TABLE "SCOTT"."TEST_EMP" ("EMPNO" NUMBER(4,0), "ENAME" VARCHAR2(10 BYTE), "JOB" VARCHAR2(9 BYTE), "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 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 1 error(s) at 17:13:42
[oracle@node2 pumpall]$
[table=98%,inherit]
[tr=inherit][backcolor=inherit]连接用户报错

SQL> conn scott
Enter password:
Error accessing PRODUCT_USER_PROFILE
Warning: Product user profile information not loaded!
You may need to run PUPBLD.SQL as SYSTEM
Connected.
SQL>
SQL> conn system/oracle
Connected.
SQL> @/u01/app/oracle/product/10.0.2/dbhome_1/sqlplus/admin/pupbld.sql
DROP SYNONYM PRODUCT_USER_PROFILE
ERROR at line 1:
ORA-01434: private synonym to be dropped does not exist
DATE_VALUE FROM PRODUCT_USER_PROFILE
ERROR at line 3:
ORA-00980: synonym translation is no longer valid
DROP TABLE PRODUCT_USER_PROFILE
ERROR at line 1:
ORA-00942: table or view does not exist
ALTER TABLE SQLPLUS_PRODUCT_PROFILE ADD (LONG_VALUE LONG)
ERROR at line 1:
ORA-00942: table or view does not exist
Table created.
DROP TABLE PRODUCT_PROFILE
ERROR at line 1:
ORA-00942: table or view does not exist
DROP VIEW PRODUCT_PRIVS
ERROR at line 1:
ORA-00942: table or view does not exist
View created.
Grant succeeded.
Synonym dropped.
Synonym created.
DROP SYNONYM PRODUCT_USER_PROFILE
ERROR at line 1:
ORA-01434: private synonym to be dropped does not exist
Synonym created.
Synonym dropped.
Synonym created.
SQL>
SQL>
SQL> conn scott
Enter password:
Connected.