SET SERVEROUTPUT ON;
DECLARE
--SAVE DATA_FILE
folder varchar2(100) := '/data/oracle/oradata/orcl/';
--SET USERNAME
username varchar2(20) := 'test';
--SET PASSWROD
password varchar2(32) := 'test1234';
--Prefix FOR TABLESPACE,Best default
tablePre varchar(20);
dbf_file varchar(150) := folder || tablePre;
V_NUM number;
BEGIN
username := upper(username);
tablePre := username;
EXECUTE IMMEDIATE 'SELECT COUNT(0) FROM DBA_USERS WHERE USERNAME = ''' || username || '''' INTO V_NUM;
if V_NUM > 0 then
EXECUTE IMMEDIATE 'DROP USER '|| username || ' CASCADE';
end if;
V_NUM := 0;
EXECUTE IMMEDIATE 'SELECT COUNT(0) FROM DBA_TABLESPACES WHERE TABLESPACE_NAME=''' || username || '_DATA''' INTO V_NUM ;
if V_NUM > 0 then
EXECUTE IMMEDIATE 'DROP TABLESPACE ' || username || '_TEMP INCLUDING CONTENTS AND DATAFILES';
EXECUTE IMMEDIATE 'DROP TABLESPACE ' || username || '_DATA INCLUDING CONTENTS AND DATAFILES';
end if;
EXECUTE IMMEDIATE 'CREATE TEMPORARY TABLESPACE ' || username || '_TEMP TEMPFILE ''' || dbf_file || '_TEMP.dbf'' SIZE 32M AUTOEXTEND ON NEXT 32M MAXSIZE 2048M EXTENT MANAGEMENT LOCAL';
EXECUTE IMMEDIATE 'CREATE TABLESPACE ' || tablePre || '_DATA LOGGING DATAFILE ''' || dbf_file || '_DATA.dbf'' SIZE 32M AUTOEXTEND ON NEXT 32M MAXSIZE 2048M EXTENT MANAGEMENT LOCAL';
EXECUTE IMMEDIATE 'CREATE USER ' || username || ' IDENTIFIED BY ' || password || ' DEFAULT TABLESPACE ' || tablePre || '_DATA TEMPORARY TABLESPACE ' || tablePre || '_TEMP';
EXECUTE IMMEDIATE 'GRANT CREATE ANY TABLE, CREATE ANY VIEW ,CREATE ANY INDEX, CREATE ANY PROCEDURE, CREATE SESSION,ALTER ANY TABLE, ALTER ANY PROCEDURE, DROP ANY TABLE, DROP ANY VIEW, DROP ANY INDEX, DROP ANY PROCEDURE, SELECT ANY TABLE, INSERT ANY TABLE, UPDATE ANY TABLE, DELETE ANY TABLE, RESOURCE TO ' || username;
EXECUTE IMMEDIATE 'ALTER profile DEFAULT limit password_life_time unlimited';
END;
/
surpassLiang
发布了69 篇原创文章 · 获赞 24 · 访问量 9万+
私信
关注