1. 创建表空间：
BEGIN
DECLARE
cnt integer := 0;
BEGIN
SELECT 1 INTO cnt FROM dual WHERE exists(SELECT * FROM user_tablespaces WHERE tablespace_name = UPPER(TRIM('TEST')));
IF cnt != 0 THEN
DECLARE
s VARCHAR2(500);
BEGIN
s := 'DROP TABLESPACE TEST INCLUDING CONTENTS CASCADE CONSTRAINTS';
DBMS_OUTPUT.PUT_LINE(s);
EXECUTE IMMEDIATE s;
END;
END IF;
exception
WHEN no_data_found THEN
DBMS_OUTPUT.PUT_LINE(cnt);
END;
END;
/ BEGIN
DECLARE
cnt integer := 0;
BEGIN
SELECT 1 INTO cnt FROM dual WHERE exists(SELECT * FROM user_tablespaces WHERE tablespace_name = UPPER(TRIM('TEST_TEMP')));
IF cnt != 0 THEN
DECLARE
s VARCHAR2(500);
BEGIN
s := 'DROP TABLESPACE TEST_TEMP INCLUDING CONTENTS CASCADE CONSTRAINTS';
DBMS_OUTPUT.PUT_LINE(s);
EXECUTE IMMEDIATE s;
END;
END IF;
exception
WHEN no_data_found THEN
DBMS_OUTPUT.PUT_LINE(cnt);
END;
END;
/ create tablespace TEST
datafile '/u01/app/oracle/product/12.1.0/db_1/test.dbf' size 500M REUSE
AUTOEXTEND ON NEXT 100M
ONLINE PERMANENT; CREATE TEMPORARY
TABLESPACE TEST_TEMP TEMPFILE
'/u01/app/oracle/product/12.1.0/db_1/test_temp.dbf' SIZE 500M REUSE
AUTOEXTEND ON NEXT 100M ;
2. 创建用户：
BEGIN
DECLARE
cnt integer := 0;
BEGIN
SELECT 1 INTO cnt FROM dual WHERE exists(SELECT * FROM ALL_USERS WHERE USERNAME = UPPER(TRIM('test')));
IF cnt != 0 THEN
DECLARE
s VARCHAR2(500);
BEGIN
s := 'DROP USER test CASCADE';
DBMS_OUTPUT.PUT_LINE(s);
EXECUTE IMMEDIATE s;
END;
END IF;
exception
WHEN no_data_found THEN
DBMS_OUTPUT.PUT_LINE(cnt);
END;
END;
/ CREATE USER test PROFILE DEFAULT
IDENTIFIED BY TEST DEFAULT TABLESPACE TEST
TEMPORARY TABLESPACE TEST_TEMP
ACCOUNT UNLOCK; GRANT CONNECT TO test;
GRANT RESOURCE TO test;
GRANT UNLIMITED TABLESPACE TO test;
GRANT ALTER ANY CLUSTER TO test;
GRANT ALTER ANY DIMENSION TO test;
GRANT ALTER ANY INDEX TO test;
GRANT ALTER ANY TABLE TO test;
GRANT ALTER ANY PROCEDURE TO test;
GRANT CREATE ANY INDEX TO test;
GRANT CREATE ANY PROCEDURE TO test;
GRANT CREATE ANY TABLE TO test;
GRANT DROP ANY INDEX TO test;
GRANT DROP ANY PROCEDURE TO test;
GRANT DROP ANY TABLE TO test;
GRANT EXECUTE ANY PROCEDURE TO test;