例文 ユーザー･表領域作成 - ＠WIKI

set echo on
spool C:\log\drop_user_SCOTT.log
 
 
-- ここからが削除
drop user SCOTT CASCADE;
ALTER TABLESPACE "USER_SCOTT" OFFLINE NORMAL;
drop tablespace user_SCOTT;
 
-- ここからが構築
CREATE TABLESPACE user_SCOTT
 DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SCOTT_tbl.ora'
 SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
ALTER TABLESPACE "user_SCOTT" ONLINE
 
CREATE USER SCOTT IDENTIFIED BY ALPHA DEFAULT TABLESPACE user_SCOTT TEMPORARY TABLESPACE temp;
grant resource ,connect to SCOTT identified by SCOTT ;
 
grant EXP_FULL_DATABASE to SCOTT;
grant IMP_FULL_DATABASE to SCOTT;
 
-- UNLIMITED TABLESPACE権限の取消
REVOKE UNLIMITED TABLESPACE FROM SCOTT;
-- 作成したテーブルスペースのみに権限を付加
ALTER USER SCOTT QUOTA UNLIMITED ON user_SCOTT ;
 
spool off
 
exit