新增兩個USER，創建表，賦與權限。實驗看看哪個復原方式可以達到我要的效果。
CREATE USER HR3
IDENTIFIED BY VALUES 'S:78DE8200DFBDE7C8A683934A5AF43E2FE8614C357C0BBA8DBC7C7898D824;9F1BFB4526C62B14'
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT
ACCOUNT UNLOCK;
-- 3 Roles for HR3
GRANT CONNECT TO HR3;
GRANT RESOURCE TO HR3;
GRANT SELECT_CATALOG_ROLE TO HR3;
ALTER USER HR3 DEFAULT ROLE ALL;
-- 5 System Privileges for HR3
GRANT ALTER SYSTEM TO HR3;
GRANT CREATE DATABASE LINK TO HR3;
GRANT CREATE MATERIALIZED VIEW TO HR3;
GRANT ON COMMIT REFRESH TO HR3;
GRANT UNLIMITED TABLESPACE TO HR3;
CREATE USER HR4
IDENTIFIED BY VALUES 'S:78DE8200DFBDE7C8A683934A5AF43E2FE8614C357C0BBA8DBC7C7898D824;9F1BFB4526C62B14'
DEFAULT TABLESPACE TEST_TBS
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT
ACCOUNT UNLOCK;
-- 3 Roles for HR4
GRANT CONNECT TO HR4;
GRANT RESOURCE TO HR4;
GRANT SELECT_CATALOG_ROLE TO HR4;
ALTER USER HR4 DEFAULT ROLE ALL;
-- 5 System Privileges for HR4
GRANT ALTER SYSTEM TO HR4;
GRANT CREATE DATABASE LINK TO HR4;
GRANT CREATE MATERIALIZED VIEW TO HR4;
GRANT ON COMMIT REFRESH TO HR4;
GRANT UNLIMITED TABLESPACE TO HR4;
SQL> create table hr3.EMPLOYEES_TEST as select * from HR1.EMPLOYEES_TEST;
Table created.
SQL> grant select on hr3.EMPLOYEES_TEST to hr4;
Grant succeeded.
[oracle@testdb89 dpdump]$ expdp DIRECTORY=DATA_PUMP_DIR SCHEMAS=HR3 CONTENT=METADATA_ONLY EXCLUDE=statistics dumpfile=hr3_20201130.dmp
Export: Release 11.2.0.4.0 - Production on Mon Nov 30 12:42:39 2020
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** DIRECTORY=DATA_PUMP_DIR SCHEMAS=HR3 CONTENT=METADATA_ONLY EXCLUDE=statistics dumpfile=hr3_20201130.dmp
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
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
>>> DBMS_AW_EXP: SYS.AW$EXPRESS: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWMD: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWCREATE: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWCREATE10G: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWXML: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWREPORT: OLAP not enabled
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
>>> DBMS_AW_EXP: SYS.AW$EXPRESS: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWMD: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWCREATE: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWCREATE10G: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWXML: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWREPORT: OLAP not enabled
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/u01/app/oracle/admin/DB11G/dpdump/hr3_20201130.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Mon Nov 30 12:42:55 2020 elapsed 0 00:00:10
[oracle@testdb89 dpdump]$ expdp DIRECTORY=DATA_PUMP_DIR SCHEMAS=HR4 CONTENT=METADATA_ONLY EXCLUDE=statistics dumpfile=hr4_20201130.dmp
Export: Release 11.2.0.4.0 - Production on Mon Nov 30 12:44:47 2020
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** DIRECTORY=DATA_PUMP_DIR SCHEMAS=HR4 CONTENT=METADATA_ONLY EXCLUDE=statistics dumpfile=hr4_20201130.dmp
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
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
>>> DBMS_AW_EXP: SYS.AW$EXPRESS: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWMD: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWCREATE: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWCREATE10G: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWXML: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWREPORT: OLAP not enabled
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/u01/app/oracle/admin/DB11G/dpdump/hr4_20201130.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Mon Nov 30 12:45:02 2020 elapsed 0 00:00:07
[oracle@testdb89 dpdump]$ expdp DIRECTORY=DATA_PUMP_DIR SCHEMAS=HR3,HR4 CONTENT=METADATA_ONLY EXCLUDE=statistics dumpfile=hr34_20201130.dmp
Export: Release 11.2.0.4.0 - Production on Mon Nov 30 12:45:17 2020
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** DIRECTORY=DATA_PUMP_DIR SCHEMAS=HR3,HR4 CONTENT=METADATA_ONLY EXCLUDE=statistics dumpfile=hr34_20201130.dmp
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
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
>>> DBMS_AW_EXP: SYS.AW$EXPRESS: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWMD: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWCREATE: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWCREATE10G: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWXML: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWREPORT: OLAP not enabled
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
>>> DBMS_AW_EXP: SYS.AW$EXPRESS: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWMD: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWCREATE: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWCREATE10G: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWXML: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWREPORT: OLAP not enabled
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/u01/app/oracle/admin/DB11G/dpdump/hr34_20201130.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Mon Nov 30 12:45:30 2020 elapsed 0 00:00:07
[oracle@testdb89 dpdump]$ expdp include=user SCHEMAS=HR4 DIRECTORY=DATA_PUMP_DIR dumpfile=userhr4_20201130.dmp
Export: Release 11.2.0.4.0 - Production on Mon Nov 30 13:36:29 2020
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** include=user SCHEMAS=HR4 DIRECTORY=DATA_PUMP_DIR dumpfile=userhr4_20201130.dmp
Estimate in progress using BLOCKS method...
Total estimation using BLOCKS method: 0 KB
Processing object type SCHEMA_EXPORT/USER
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/u01/app/oracle/admin/DB11G/dpdump/userhr4_20201130.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Mon Nov 30 13:36:41 2020 elapsed 0 00:00:04
SQL> DROP USER HR4 CASCADE;
[oracle@testdb89 dpdump]$ impdp DIRECTORY=DATA_PUMP_DIR DUMPFILE=userhr4_20201130.dmp
Import: Release 11.2.0.4.0 - Production on Mon Nov 30 13:37:24 2020
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** DIRECTORY=DATA_PUMP_DIR DUMPFILE=userhr4_20201130.dmp
Processing object type SCHEMA_EXPORT/USER
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Mon Nov 30 13:38:40 2020 elapsed 0 00:00:02
用expdp include=user 的export file 做impdp，只會create user。沒有相關system, object, role 等權限。
沒有權限，連登入資料庫都會失敗。
[oracle@testdb89 dpdump]$ sqlplus hr4/hr
SQL*Plus: Release 11.2.0.4.0 Production on Mon Nov 30 13:41:44 2020
Copyright (c) 1982, 2013, Oracle. All rights reserved.
ERROR:
ORA-01045: user HR4 lacks CREATE SESSION privilege; logon denied
Enter user-name:
[oracle@testdb89 dpdump]$ sqlplus / as sysdba
SQL> DROP USER HR4 CASCADE;
[oracle@testdb89 dpdump]$ impdp DIRECTORY=DATA_PUMP_DIR DUMPFILE=hr4_20201130.dmp
缺 -- 1 Object Privilege for HR4
GRANT SELECT ON HR3.EMPLOYEES_TEST TO HR4;
[oracle@testdb89 dpdump]$ sqlplus / as sysdba
SQL> DROP USER HR4 CASCADE;
[oracle@testdb89 dpdump]$ impdp DIRECTORY=DATA_PUMP_DIR DUMPFILE=hr34_20201130.dmp
Import: Release 11.2.0.4.0 - Production on Mon Nov 30 12:47:47 2020
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** DIRECTORY=DATA_PUMP_DIR DUMPFILE=hr34_20201130.dmp
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"HR3" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
ORA-39151: Table "HR3"."EMPLOYEES_TEST" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 2 error(s) at Mon Nov 30 12:47:56 2020 elapsed 0 00:00:02
缺 -- 1 Object Privilege for HR4
GRANT SELECT ON HR3.EMPLOYEES_TEST TO HR4;
因為 ORA-39151: Table "HR3"."EMPLOYEES_TEST" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
[oracle@testdb89 dpdump]$ sqlplus / as sysdba
SQL> DROP USER HR3 CASCADE;
SQL> DROP USER HR4 CASCADE;
[oracle@testdb89 dpdump]$ impdp DIRECTORY=DATA_PUMP_DIR DUMPFILE=hr34_20201130.dmp
Import: Release 11.2.0.4.0 - Production on Mon Nov 30 12:49:58 2020
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** DIRECTORY=DATA_PUMP_DIR DUMPFILE=hr34_20201130.dmp
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
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Mon Nov 30 12:50:09 2020 elapsed 0 00:00:02
將HR3, HR4 都完整drop，才會恢復的完整。 HR4 有 GRANT SELECT ON HR3.EMPLOYEES_TEST TO HR4;
SQL> set long 64000
SQL> select dbms_metadata.get_ddl('USER','HR3') from dual;
SQL> select dbms_metadata.get_ddl('USER','HR4') from dual;
DBMS_METADATA.GET_DDL('USER','HR4')
--------------------------------------------------------------------------------
CREATE USER "HR4" IDENTIFIED BY VALUES 'S:78DE8200DFBDE7C8A683934A5AF43E2FE86
14C357C0BBA8DBC7C7898D824;9F1BFB4526C62B14'
DEFAULT TABLESPACE "TEST_TBS"
TEMPORARY TABLESPACE "TEMP"
如果是想在開發機，設定和正式機相同的密碼。
ALTER USER "HR4" IDENTIFIED BY VALUES 'S:78DE8200DFBDE7C8A683934A5AF43E2FE86
14C357C0BBA8DBC7C7898D824;9F1BFB4526C62B14'
沒有留言:
張貼留言