oracle导出用户、表空间和角色的创建语句

#!/bin/bash
user=`whoami`
if [ $user != oracle ]; then
echo "must be run as oracle!"
exit 1
fi
 
test -d /home/oracle/backup
if [ $? -ne 0 ]; then
mkdir -p /home/oracle/backup
fi
 
sqlplus / as sysdba <<!
set heading off
set feedback off
set echo off
set termout off
set linesize 200
spool /home/oracle/backup/creattbsp.sql
remark create tablespace ;
select 'create TABLESPACE ' || tablespace_name  || '  DATAFILE ' || ''''|| file_name || '''' ||'  SIZE ' || to_char(ROUND(bytes/1024/1024,0)) ||'M REUSE AUTOEXTEND ON NEXT  32M MAXSIZE UNLIMITED ;' from dba_data_files 
where tablespace_name not in ('SYSTEM','USERS','UNDOTBS1','SYSAUX');
 
select 'create TABLESPACE ' || tablespace_name  || '  DATAFILE ' || ''''||file_name||'''' ||'  SIZE ' || to_char(ROUND(bytes/1024/1024,0)) ||'M REUSE AUTOEXTEND ON NEXT  32M MAXSIZE UNLIMITED ;' from dba_temp_files 
where tablespace_name <> 'TEMP';
spool off;
 
remark create user 
spool /home/oracle/backup/createuser.sql
select 'create user '|| username || ' identified by  ''change to your 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;
spool /home/oracle/backup/createrole.sql
select 'create role ' || role || ' ;' from  dba_roles where role like 'ROLE%';
spool off
set heading on
set feedback on
set echo on
set termout on
set linesize 100
>>!

 

©️2020 CSDN 皮肤主题: 书香水墨 设计师:CSDN官方博客 返回首页
实付 29.90元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值