原始库 创建brucetbs表空间 创建bruce用户 创建brucetb表
SQL> create tablespace brucetbs datafile '/ora/app/oracle/oradata/orcl/brucetbs01.dbf' size 50m autoextend on next 50m maxsize unlimited;
Tablespace created.
SQL> create user bruce identified by bruce default tablespace brucetbs;
User created.
SQL> grant connect,resource to bruce;
Grant succeeded.
SQL> conn bruce/bruce
Connected.
SQL> show user;
USER is "BRUCE"
SQL> create table brucetb(id int,name varchar2(20));
Table created.
SQL> insert into brucetb values(1,'aaaaa');
1 row created.
SQL> insert into brucetb values(2,'bbbbb');
1 row created.
SQL> insert into brucetb values(3,'ccccc');
1 row created.
SQL> commit;
Commit complete.
----
导出整个数据库
expdp system/oracle dumpfile=full.dmp full=y logfile=exp.log
1 导入目标数据库,如果OS没有对应的目录会导入失败
impdp system/oracle dumpfile=full.dmp logfile=imp01.log full=y
2 如果有对应的目录，导出的库用户为bruce默认表空间是brucetbs，目标库中无bruce和brucetbs
结果
impdp会建好用户和表空间
查看导入log 会有brucetb 3条记录
ORA-02354: error in exporting/importing data
ORA-26031: index maintenance error, the load cannot continue
ORA-00001: unique constraint (OE.SYS_IOT_TOP_52386) violated^@
. . imported "BRUCE"."BRUCETB" 5.265 KB 3 rows
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/GRANT/CROSS_SCHEMA/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX
3 如果有对应的目录，导出的库用户为bruce默认表空间是brucetbs，目标库中bruce用户的默认表空间是example表空间
SQL> create user bruce identified by bruce default tablespace example;
User created.
SQL> grant connect,resource to bruce;
Grant succeeded.
结果
SQL> select owner,table_name,tablespace_name from dba_tables where owner='BRUCE';
OWNER TABLE_NAME TABLESPACE_NAME
---------- ------------------------------ ------------------------------
BRUCE BRUCETB BRUCETBS
导入log
ORA-00001: unique constraint (OE.SYS_IOT_TOP_52386) violated^@
. . imported "BRUCE"."BRUCETB" 5.265 KB 3 rows
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/GRANT/CROSS_SCHEMA/OBJECT_GRANT
4 目录相同，导出的库用户为bruce默认表空间是brucetbs，有张表是brcuetb 目标库中也有brucetb这个表
SQL> create tablespace brucetbs datafile '/ora/app/oracle/oradata/orcl/brucetbs01.dbf' size 50m autoextend on next 50m maxsize unlimited;
Tablespace created.
SQL> create user bruce identified by bruce default tablespace brucetbs;
User created.
SQL> grant connect,resource to bruce;
Grant succeeded.
SQL> conn bruce/bruce
Connected.
SQL> create table brucetb(id int,name varchar2(20));
Table created.
SQL> insert into brucetb values(4,'ddddd');
1 row created.
SQL> insert into brucetb values(5,'eeeee');
1 row created.
SQL> commit
2 ;
Commit complete.
然后导入
看导入日志
ORA-31684: Object type TABLESPACE:"BRUCETBS" already exists
Processing object type DATABASE_EXPORT/PROFILE
ORA-31684: Object type PROFILE:"MONITORING_PROFILE" already exists
Processing object type DATABASE_EXPORT/SYS_USER/USER
Processing object type DATABASE_EXPORT/SCHEMA/USER
ORA-31684: Object type USER:"BRUCE" already exists
......
ORA-39151: Table "WK_TEST"."WK$CACHE" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
ORA-39151: Table "BRUCE"."BRUCETB" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
ORA-39151: Table "OUTLN"."OL$HINTS" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
登录查看
SQL> conn bruce/bruce
Connected.
SQL> select * from brucetb;
ID NAME
---------- --------------------
4 ddddd
5 eeeee
没有把数据导入
