使用分隔符 创建表空间、用户

使用 oracle数据库提供的 拼字符 ‘||’ 批量创建 表空间

SQL> select 'create tablespace '||tablespace_name||' datafile '||''''||file_name||''''||' size '||bytes/1024/1024||'M'||' autoextend '||decode(autoextensible,'YES','ON')||';' from dba_data_files;
CT_TBS
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
create tablespace USERS datafile '/u01/app/oracle/oradata/nafu3/users01.dbf' size 1142.5M autoextend ON;
create tablespace UNDOTBS1 datafile '/u01/app/oracle/oradata/nafu3/undotbs01.dbf' size 75M autoextend ON;
create tablespace SYSAUX datafile '/u01/app/oracle/oradata/nafu3/sysaux01.dbf' size 510M autoextend ON;
create tablespace SYSTEM datafile '/u01/app/oracle/oradata/nafu3/system01.dbf' size 680M autoextend ON;

批量创建用户
SQL> select ' create user '||username||' identified by xxx '||' default tablespace '||default_tablespace ct_user from dba_users where account_status='OPEN' order by created desc;

CT_USER
----------------------------------------------------------------------------------------------------------------
 create user DEV123 identified by xxx  default tablespace USERS
 create user DEV identified by xxx  default tablespace USERS
 create user TEST123 identified by xxx  default tablespace USERS
 create user TEST identified by xxx  default tablespace USERS
 create user SCOTT identified by xxx  default tablespace USERS
 create user SYSMAN identified by xxx  default tablespace SYSAUX
 create user DBSNMP identified by xxx  default tablespace SYSAUX
 create user ORACLE_OCM identified by xxx  default tablespace USERS
 create user SYS identified by xxx  default tablespace SYSTEM
 create user SYSTEM identified by xxx  default tablespace SYSTEM