Oracle Rac建立資料表空間及使用者

標籤：name   xtend   datafile   exce   constrain   data   content   str   contents   

1. 建立資料表空間：

BEGINDECLAREcnt 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;/BEGINDECLAREcnt 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. 建立使用者：

BEGINDECLAREcnt 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;

　　

Oracle Rac建立資料表空間及使用者