Oracle提取现有数据库表空间、角色、用户信息，并生成脚本

Oracle提取现有数据库表空间、角色、用户信息，并生成脚本

set head off
set feedback off
set echo off
set termout off
set linesize 300
spool d:\zly_his\script\createtsp.sql
remark create tablespace ;
Select ' Create TABLESPACE ' || tablespace_name  || '  
    DATAFILE ' || file_name ||'  SIZE ' || to_char(ROUND(bytes/1000000,0)) ||'M REUSE AUTOEXTEND 
    ON NEXT  32M MAXSIZE UNLIMITED ;' from dba_data_files where tablespace_name not in ('SYSTEM','USERS','TEMP');
spool off;
-----
remark create user 
spool d:\zly_his\script\createuser.sql
select ' create user '|| username || ' identified by values ''' ||
 password || ''' default tablespace '|| 
DEFAULT_TABLESPACE || '  TEMPORARY TABLESPACE '
||   TEMPORARY_TABLESPACE    ||';'
from dba_users   where username not in ('SYS','SYSTEM','SCOTT','DBSNMP','CWKLP','OA','OA88');
spool off
----

remark create role;
set head off
set feedback off
set echo off
set termout off
set linesize 1000
spool d:\zly_his\script\createrole.sql
select 'create role ' || role || ' ;' from  dba_roles where role like 'ROLE%';
spool off
set head on
set feedback on
set echo on
set termout on
set linesize 80