测试环境拼接SQL批量导出表空间、用户、权限

日常测试环境,客户时常建立多个测试库，用一段时间，然后重新搭建一套环境，将原测试库的表空间、用户、权限等导入新测试环境，一旦表空间和用户比较多的时候，反复工作量巨大，所以写了一些拼接SQL，方便批量操作

从旧库导出表空间信息，在新库创建

--create new tablespace user from old database  

SELECT 'create tablespace '||
TS.NAME ||' datafile '||''''||
DF.PATH ||''''||' size 128'
FROM
V$TABLESPACE AS TS,
V$DATAFILE AS DF
WHERE

TS.ID = DF.GROUP_ID and TS.NAME not in('MAIN','ROLL','SYSTEM','TEMP');  
-------------------------------------------

create tablespace DMTEST01 datafile '/dm8/dmdata/DAMENG/DMTEST01.DBF' size 128
create tablespace DMTEST datafile '/dm8/dmdata/DAMENG/DMTEST.DBF' size 128
create tablespace TBS01 datafile '/dm8/dmdata/DAMENG/TBS01_01.DBF' size 128
create tablespace TBS02 datafile '/dm8/dmdata/DAMENG/TBS02_01.DBF' size 128
create tablespace TBS03 datafile '/dm8/dmdata/DAMENG/TBS03_01.DBF' size 128

create tablespace TEST datafile '/dm8/dmdata/DAMENG/TEST01.DBF' size 128
-------------------------------------------

从旧库导出用户信息，在新库创建

  --create new database user from old database  

select 'create user ' ||a.USERNAME || 
' identified by '  ||a.USERNAME ||123456 || 
' default tablespace ' ||a.DEFAULT_TABLESPACE||
' default index tablespace ' ||a.DEFAULT_INDEX_TABLESPACE||';'
  from (select * from dba_users WHERE DEFAULT_TABLESPACE not in ('SYSTEM') and USERNAME  not in ('SYSDBA')) a;
  
  -------------------------------------------------
create user DMTEST identified by DMTEST123456 default tablespace TBS01 default index tablespace TBS02;

-------------------------------------------------------------

从旧库导出用户权限信息，在新库创建

--grant new database user  privilege from old database  
 
select 'grant '||'PUBLIC'||
 ',' ||'RESOURCE'||
 ','||'SOI'||
 ','||'SVI'||
 ','|| 'VTI'||
  ' to '  ||a.USERNAME||';'
  from (select * from dba_users WHERE DEFAULT_TABLESPACE not in ('SYSTEM') and USERNAME  not in ('SYSDBA')) a; 
 
 
----------------------------------------------------------
grant PUBLIC,RESOURCE,SOI,SVI,VTI to TEST;
grant PUBLIC,RESOURCE,SOI,SVI,VTI to DMTEST;