备份并恢复用户角色Oracle

Question

是否可以使用脚本备份用户角色（不是整个DB），还是在Oracle 11中有一些备份功能？我想备份用户并以后通过脚本还原它们，应用作反对MIS配置的备份。

dba_usersdba_rolesdba_role_privs角色_tab_privssession_privssession_rolestable_privileges

当前，我可以查询或创建存储大多数用户设置的表的视图。但是，从该来源创建角色并授予在表格上授予，这涉及很多文本转换。查看>存储到文件> CONCAT将它们转换为Create/Grant>在Commandline上执行脚本

是否有另一种方法来导出和导入用户权利和角色以及将其分配给表的方式？或者我可以简单地备份和还原这个问题中提到的表，而无需制动DB？

Answer 1

您可以使用DataPump备份它们：

expdp cyrille/*******@//localhost:1521/orclpdb 
dumpfile=exp_USER_GRANTS.dmp 
logfile=exp_USER_GRANTS.log 
directory=MY_DIRECTORY 
INCLUDE=GRANT 
INCLUDE=OBJECT_GRANT 
INCLUDE=SYSTEM_GRANT 
INCLUDE=ROLE_GRANT 
INCLUDE=USER 
full=y
Export: Release 12.2.0.1.0 - Production on Wed Sep 27 11:35:35 2017
Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "*****"."SYS_EXPORT_FULL_01":  cyrille/********@//localhost:1521/orclpdb dumpfile=exp_USER_GRANTS.dmp logfile=exp_USER_GRANTS.log directory=MY_DIRECTORY INCLUDE=GRANT INCLUDE=OBJECT_GRANT INCLUDE=SYSTEM_GRANT INCLUDE=ROLE_GRANT INCLUDE=USER full=y
Processing object type DATABASE_EXPORT/SYS_USER/USER
Processing object type DATABASE_EXPORT/SCHEMA/USER
Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/DIRECTORY/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/GRANT/PROCOBJ_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/JAVA_CLASS/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/JAVA_RESOURCE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/OPERATOR/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/INDEXTYPE/GRANT/OWNER_GRANT/OBJECT_GRANT
Master table "CYRILLE"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for CYRILLE.SYS_EXPORT_FULL_01 is:
  /u01/exp_USER_GRANTS.dmp

从该转储中您也可以使用SQLFILE参数生成脚本：

impdp cyrille/******@//localhost:1521/orclpdb  dumpfile=exp_USER_GRANTS.dmp  logfile=imp_USER_GRANTS.log  directory=MY_DIRECTORY sqlfile=my_script.sql

这将与脚本生成一个SQL文件。