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

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

SQL> select 'create tablespace '||tablespace_name||' datafile '||''''||file_name||''''||' size '||bytes/1024/1024||'M;' ct_tbs from dba_data_files;

CT_TBS
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
create tablespace USERS datafile '/ora_data/oradata/orcl/users01.dbf' size 112.5M;
create tablespace UNDOTBS1 datafile '/ora_data/oradata/orcl/undotbs01.dbf' size 80M;
create tablespace SYSAUX datafile '/ora_data/oradata/orcl/sysaux01.dbf' size 540M;
create tablespace SYSTEM datafile '/ora_data/oradata/orcl/system01.dbf' size 730M;
create tablespace LVSKSK_T_JS datafile '/u01/app/oracle/oradata/prod/lvsksk_t_js01.dbf' size 200M;
create tablespace MY_TBS datafile '/u01/app/oracle/product/11.2.0/db_1/dbs/E:APPORACLEORADATADZFPMXMY_TBS01.DBF' size 10M;
create tablespace TEST datafile '/u01/app/oracle/product/11.2.0/db_1/dbs/E:APPORACLEORADATADZFPMXTEST01.DBF' size 10M;
create tablespace TBS datafile '/u01/app/oracle/product/11.2.0/db_1/dbs/E:APPORACLEORADATADZFPMXTBS01.DBF' size 10M;

批量创建用户
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

10 rows selected.