总结：
dexp 导出owner 时会将owner对应的多个schema 数据全部导出，在导出owner时只导出对象信息，用户的权限和创建语句不会导出
dexp 导出schema 时只导出schema 自己下的对象
dimp 导入owner 时会将owner 下对应的所有schema数据全部导入，在导入owner之前需要先穿件owner
dimp 导入schema 是会把备份schema下的所有数据全部导入，同时自动与原来的owner进行授权关联。在导入schema前需要先创建owner
1.创建用户和sceham
创建用户的同时会创建同名schema
create user test identified by test_123456;
grant dba to test;
查看模式和表的对应关系
select a.NAME schename, a.id, a.PID, b.NAME username
from SYSOBJECTS a, SYSOBJECTS b
where a."TYPE$" = 'SCH' and a.PID = b.id
and b.name='TEST';2 3 4
---sysobjects 视图会记录对象信息，包括schema和user，table 等，是一个杂糅的视图
LINEID SCHENAME ID PID USERNAME
---------- -------- ----------- ----------- --------
1 TEST 150995951 50331756 TEST
创建一个schema test_yk并与用户test 关联
SQL> create SCHEMA test_yk AUTHORIZATION test;
2 / -- 此处必须加 “/” 否则执行不了
executed successfully
再次查看，一个用户对应了2个schema
LINEID SCHENAME ID PID USERNAME ID
---------- -------- ----------- ----------- -------- -----------
1 TEST 150995951 50331756 TEST 50331756
2 TEST_YK 150995952 50331756 TEST 50331756
used time: 1.159(ms). Execute id is 2017.
2.在test 和test_yk schema 下创建不同的表
SQL> create table test_yk.test_yk as select * from sysobjects;
create table test.test as select * from dba_users;executed successfully
used time: 80.443(ms). Execute id is 2018.
SQL>
executed successfully
used time: 10.928(ms). Execute id is 2019.
SQL> select count(*) from test.test;
LINEID COUNT(*)
---------- --------------------
1 8
used time: 0.492(ms). Execute id is 2020.
SQL> select count(*) from test_yk.test_yk;
LINEID COUNT(*)
---------- --------------------
1 808
used time: 0.473(ms). Execute id is 2021.
分别按schema,和owner 导出数据
1. dexp sysdba/password file=/dm8/backup/test2.dmp log=/dm8/backup/test_schema.log schemas=test2;
dexp sysdba/password file=/dm8/backup/test.dmp log=/dm8/backup/test.log schemas=test
[WARNING]FILE "/dm8/backup/test.log" has already existed
whether to overwrite(y/n, 1/0):1
exporting NO. 1 SCHEMA : TEST
start export schema[TEST].....
----- export total 0 SEQUENCE -----
----- export total 0 VIEW -----
----- export total 0 TRIGGER -----
----- export total 0 COMMENT VIEW -----
----- export total 0 COMMENT COL -----
----- export total 0 PROCEDURE -----
----- export total 0 SYNONYM -----
----- export total 0 DBLINK -----
----- export total 0 TRIGGER -----
----- export total 0 PACKAGE -----
----- export total 0 PKG_BODY -----
----- export total 0 OBJECT of NO REFER OTHER CLASS -----
----- export total 0 OBJECT of REFER OTHER CLASS -----
----- export total 0 JCLASS -----
----- export total 0 CLASS_BODY -----
----- export total 0 DOMAIN -----
the privilege of the object at the export mode...
---- [2022-04-09 13:39:20]export table:TEST -----
the privilege of the object at the export mode...
table :TEST export terminate, total export 8 rows
schema[TEST] export terminate.....
successfully exported NO.1 SCHEMA : TEST
export total 1 SCHEMA
all the export process spent total 0.065 s
terminate export success without warning
重标红的部分可以看到，这里只导出了TEST schema 下的TEST 表
按导出test_yk 模式
dexp sysdba/password file=/dm8/backup/test_yk.dmp log=/dm8/backup/test_yk.log schemas=test_yk
[dmdba@10-65-62-233 ~]$ dexp sysdba/password file=/dm8/backup/test_yk.dmp log=/dm8/backup/test_yk.log schemas=test_yk
dexp V8
exporting NO. 1 SCHEMA : TEST_YK
start export schema[TEST_YK].....
----- export total 0 SEQUENCE -----
----- export total 0 VIEW -----
----- export total 0 TRIGGER -----
----- export total 0 COMMENT VIEW -----
----- export total 0 COMMENT COL -----
----- export total 0 PROCEDURE -----
----- export total 0 SYNONYM -----
----- export total 0 DBLINK -----
----- export total 0 TRIGGER -----
----- export total 0 PACKAGE -----
----- export total 0 PKG_BODY -----
----- export total 0 OBJECT of NO REFER OTHER CLASS -----
----- export total 0 OBJECT of REFER OTHER CLASS -----
----- export total 0 JCLASS -----
----- export total 0 CLASS_BODY -----
----- export total 0 DOMAIN -----
the privilege of the object at the export mode...
---- [2022-04-09 13:41:01]export table:TEST_YK -----
the privilege of the object at the export mode...
table :TEST_YK export terminate, total export 808 rows
schema[TEST_YK] export terminate.....
successfully exported NO.1 SCHEMA : TEST_YK
export total 1 SCHEMA
all the export process spent total 0.075 s
terminate export success without warning
从日志中可以看到，只导出了test_yk 下的test_yk 表
按owner 导出test 用户
dexp sysdba/password file=/dm8/backup/test_owner.dmp log=/dm8/backup/test_owner.log owner=test
dexp V8
exporting NO. 1 SCHEMA : TEST_YK
start export schema[TEST_YK].....
----- export total 0 SEQUENCE -----
----- export total 0 VIEW -----
----- export total 0 TRIGGER -----
----- export total 0 COMMENT VIEW -----
----- export total 0 COMMENT COL -----
----- export total 0 PROCEDURE -----
----- export total 0 SYNONYM -----
----- export total 0 DBLINK -----
----- export total 0 TRIGGER -----
----- export total 0 PACKAGE -----
----- export total 0 PKG_BODY -----
----- export total 0 OBJECT of NO REFER OTHER CLASS -----
----- export total 0 OBJECT of REFER OTHER CLASS -----
----- export total 0 JCLASS -----
----- export total 0 CLASS_BODY -----
----- export total 0 DOMAIN -----
the privilege of the object at the export mode...
---- [2022-04-09 13:42:02]export table:TEST_YK -----
the privilege of the object at the export mode...
table :TEST_YK export terminate, total export 808 rows
schema[TEST_YK] export terminate.....
successfully exported NO.1 SCHEMA : TEST_YK
exporting NO. 2 SCHEMA : TEST
start export schema[TEST].....
----- export total 0 SEQUENCE -----
----- export total 0 VIEW -----
----- export total 0 TRIGGER -----
----- export total 0 COMMENT VIEW -----
----- export total 0 COMMENT COL -----
----- export total 0 PROCEDURE -----
----- export total 0 SYNONYM -----
----- export total 0 DBLINK -----
----- export total 0 TRIGGER -----
----- export total 0 PACKAGE -----
----- export total 0 PKG_BODY -----
----- export total 0 OBJECT of NO REFER OTHER CLASS -----
----- export total 0 OBJECT of REFER OTHER CLASS -----
----- export total 0 JCLASS -----
----- export total 0 CLASS_BODY -----
----- export total 0 DOMAIN -----
the privilege of the object at the export mode...
---- [2022-04-09 13:42:02]export table:TEST -----
the privilege of the object at the export mode...
table :TEST export terminate, total export 8 rows
schema[TEST] export terminate.....
successfully exported NO.2 SCHEMA : TEST
export total 2 SCHEMA
all the export process spent total 0.107 s
terminate export success without warning
从日志中可以发现
按owner 导出，owner 对应的多个schema的数据全部被导出来了，按schema导出，只能到出本schema 下的数据。从日志中可以发现，dexp 不会导出owner的创建语句和权限
3.删除owner 和 schema的区别，drop schema 只会drop 掉本schema下的数据，drop owner 会将owner 拥有的全部schema数据全部dorp掉
drop user if EXISTS test CASCADE;
drop user 会把用户下对应的所有模式全部drop 掉
SQL> drop schema test_yk cascade;
executed successfully
used time: 29.867(ms). Execute id is 2022.
SQL> drop schema test_yk;
drop schema test_yk;
[-5001]:Error in line: 1
Not empty schema [TEST_YK].
used time: 0.376(ms). Execute id is 0.
SQL> drop schema test_yk cascade;
executed successfully
used time: 29.867(ms). Execute id is 2022.
SQL>
SQL> select count(*) from test.test;
LINEID COUNT(*)
---------- --------------------
1 8
used time: 0.269(ms). Execute id is 2023.
SQL> select count(*) from test.test_yk;
select count(*) from test.test_yk;
[-2106]:Error in line: 1
Invalid table or view name [TEST_YK].
used time: 0.240(ms). Execute id is 0.
只有一个test 用户和schema
SQL> select a.NAME schename, a.id, a.PID, b.NAME username,b.id
from SYSOBJECTS a, SYSOBJECTS b
where a."TYPE$" = 'SCH' and a.PID = b.id
and b.name='TEST';2 3 4
LINEID SCHENAME ID PID USERNAME ID
---------- -------- ----------- ----------- -------- -----------
1 TEST 150995951 50331756 TEST 50331756
used time: 0.480(ms). Execute id is 2024.
再次创建schema test_yk
create SCHEMA test_yk AUTHORIZATION test;
create table test_yk.test_yk as select * from dba_objects;
select a.NAME schename, a.id, a.PID, b.NAME username,b.id
from SYSOBJECTS a, SYSOBJECTS b
where a."TYPE$" = 'SCH' and a.PID = b.id
and b.name='TEST';
SQL> select a.NAME schename, a.id, a.PID, b.NAME username,b.id
from SYSOBJECTS a, SYSOBJECTS b
where a."TYPE$" = 'SCH' and a.PID = b.id
and b.name='TEST';
2 3 4
LINEID SCHENAME ID PID USERNAME ID
---------- -------- ----------- ----------- -------- -----------
1 TEST 150995951 50331756 TEST 50331756
2 TEST_YK 150995953 50331756 TEST 50331756
此处删除owner，owner对应的两个schema 一并被删除了
drop test cascade;
SQL> drop user test cascade;
executed successfully
used time: 49.917(ms). Execute id is 2029.
SQL> select count(*) from test_yk.test_yk;
select count(*) from test_yk.test_yk;
[-2103]:Error in line: 1
Invalid schema name [TEST_YK].
used time: 0.273(ms). Execute id is 0.
SQL> select count(*) from test.test;
select count(*) from test.test;
[-2103]:Error in line: 1
Invalid schema name [TEST].
used time: 0.245(ms). Execute id is 0.
SQL> select name,id,SCHID,TYPE$ ,pid from sysobjects where name='TEST';
no rows
used time: 0.332(ms). Execute id is 2036.
4.导入操作
按owner 导入，所有的schema数据均被导入，导入之前需要把用户创建好
SQL> create user test identified by test_123456;
executed successfully
used time: 18.838(ms). Execute id is 2400.
SQL> grant dba to test;
executed successfully
used time: 15.169(ms). Execute id is 2401.
dimp userid=sysdba/password file=/dm8/backup/full.dmp
log=/dm8/backup/impfull.log remap_schema=DMHR:HRTEST
SQL> select count(*) from test_yk.test_yk;
LINEID COUNT(*)
---------- --------------------
1 808
used time: 0.709(ms). Execute id is 2800.
SQL> select count(*) from test.test;
LINEID COUNT(*)
---------- --------------------
1 8
used time: 0.480(ms). Execute id is 2801.
SQL>
[dmdba@10-65-62-233 backup]$ dimp userid=sysdba/dameng123 file=/dm8/backup/test_owner.dmp log=/dm8/backup/impowner.log owner=test
dimp V8
[WARNING]FILE "/dm8/backup/impowner.log" has already existed
whether to overwrite(y/n, 1/0):1
local code: PG_UTF8, dump file code: PG_GB18030
start importing schema[TEST_YK]...
start importing the NECESSARY GLOBAL objects of the schema...
finish importing the NECESSARY GLOBAL objects of the schema...
----- [2022-04-09 15:25:37]import table:TEST_YK -----
create table TEST_YK
import table TEST_YK , has coped with 808 rows
start importing the GLOBAL objects of the schema...
finish importing the GLOBAL objects of the schema...
import schema[TEST_YK] finish...
start importing schema[TEST]...
start importing the NECESSARY GLOBAL objects of the schema...
finish importing the NECESSARY GLOBAL objects of the schema...
----- [2022-04-09 15:25:37]import table:TEST -----
create table TEST
import table TEST , has coped with 8 rows
start importing the GLOBAL objects of the schema...
finish importing the GLOBAL objects of the schema...
import schema[TEST] finish...
all the import process spent total 0.081 s
terminate import success without warning
清理数据库环境
drop schema test_yk cascade;
SQL> drop schema test_yk cascade;
executed successfully
used time: 28.750(ms). Execute id is 2802.
SQL> drop schema test cascade;
drop schema test cascade;
[-2506]:Error in line: 1
Try to drop default schema of user [TEST].
used time: 0.349(ms). Execute id is 0. ---- 用户默认的
按schema 导入
[dmdba@10-65-62-233 backup]$ dimp userid=sysdba/dameng123 file=/dm8/backup/test_yk.dmp log=/dm8/backup/imptest_yk.log schemas=test_yk
dimp V8
local code: PG_UTF8, dump file code: PG_GB18030
start importing schema[TEST_YK]...
start importing the NECESSARY GLOBAL objects of the schema...
finish importing the NECESSARY GLOBAL objects of the schema...
----- [2022-04-09 15:34:57]import table:TEST_YK -----
create table TEST_YK
import table TEST_YK , has coped with 808 rows
start importing the GLOBAL objects of the schema...
finish importing the GLOBAL objects of the schema...
import schema[TEST_YK] finish...
all the import process spent total 0.081 s
terminate import success without warning
LINEID SCHENAME ID PID USERNAME ID
---------- -------- ----------- ----------- -------- -----------
1 TEST 150995954 50331757 TEST 50331757
2 TEST_YK 150995956 50331757 TEST 50331757
used time: 0.683(ms). Execute id is 3100.
SQL> select count(*) from test_yk.test_yk;
LINEID COUNT(*)
---------- --------------------
1 808
used time: 0.720(ms). Execute id is 3101.
按schema 导入后，test_yk schema 与test 用户的依赖关系自动关联。
5845
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
