--11g使用（创建用户）
select a.username,a.password_versions,a.account_status,a.lock_date,a.expiry_date,a.created,b.password,b.spare4,
case when a.password_versions in ('10G ') then
'create user '||username||' identified by values '||''''||b.password||''''||' default tablespace '||default_tablespace||' temporary tablespace '||temporary_tablespace||';'
when a.password_versions in ('10G 11G ','10G 11G 12C ') then
'create user '||b.name||' identified by values '||''''||b.spare4||';'||b.password||''' default tablespace '||a.default_tablespace||' temporary tablespace '||a.temporary_tablespace||';'
else '' end as "create user"
from dba_users a,user$ b
where a.username=b.name
and a.user_id=b.user#;
--（创建角色）
select 'create role '||role||';' from dba_roles;
--角色权限
select 'grant '||GRANTED_ROLE||' to '||grantee||';' from dba_role_privs where
grantee in(select username from dba_users where trunc(created) <> to_date('2010/4/20','yyyy/mm/dd'));
select 'grant '||GRANTED_ROLE||' to '||grantee||';' from dba_role_privs where
grantee in(select username from dba_users where username not in
(select name
from system.logstdby$skip_support
where action=0) );
SQL> select 'grant '||GRANTED_ROLE||' to '||grantee||';' from dba_role_privs where grantee in(select username from dba_users where default_tablespace in(' TBS1 ' , ' TBS2 '));
--系统权限
select 'grant '||PRIVILEGE||' to '||grantee||';' from DBA_SYS_PRIVS where grantee in (select username from dba_users where username not in
(select name
from system.logstdby$skip_support
where action=0) );
--对象权限
select 'grant '||privilege||' on '||owner||'.'||table_name||' to '||grantee||';' from dba_tab_privs where owner='XXX' ;
目标库根据上述查询结果进行创建。
2）通过oracle提供的包
dbms_metadata.get_ddl
具体操作百度上面很多，方法也是一样；
3）通过expdp和impdp
该方法，主要是通过从原库中抽取自己想要的元数据信息，然后转化成dmp文件内容；
再利用impdp方式将dump中的内容转成文本方式，供用户编辑调整使用
源库：
create directory dump_xtts as '/backup/';
expdp \' sys/his as sysdba \' DIRECTORY=dump_xtts LOGFILE=user_exp.log dumpfile=user_exp.dmp INCLUDE=USER,ROLE,ROLE_GRANT,PROFILE full=y
目标库：
[oracle@ol7 backup]$ scp
oracle@192.168.9.179:/backup/user_exp.* .
create directory dump_xtts as '/backup/';
impdp \' / as sysdba \' DIRECTORY=dump_xtts LOGFILE=user_exp.log dumpfile=user_exp.dmp INCLUDE=USER,ROLE,ROLE_GRANT,PROFILE sqlfile=text.sql
注意：上面这个就是将dump中的文件转储到/backup/下的text.sql中，将该sql拿出来就是提取的元数据脚本了；