oracle建立/刪除表空間、建立/刪除使用者並賦予許可權

我是冰霜發表於2017-07-19
Oracle

建立表空間

分開執行如下sql語句

--建立臨時表空間
CREATE SMALLFILE 
    TEMPORARY TABLESPACE "TEMP11" TEMPFILE 
    'E:\app\MD\oradata\oanet\TEMP11.ora' SIZE 200M    （“E:\app\MD\oradata\oanet”是資料庫例項oanet的實際路徑）
    EXTENT MANAGEMENT LOCAL UNIFORM SIZE 5M;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE "TEMP11"

--建立表空間1
CREATE SMALLFILE 
    TABLESPACE "WORKFLOW01" 
    LOGGING 
    DATAFILE 
    'E:\app\MD\oradata\oanet\WORKFLOW01.ora' SIZE 
    2048M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT  AUTO
    
--建立表空間2
CREATE SMALLFILE 
    TABLESPACE "WORKFLOW02" 
    LOGGING 
    DATAFILE 
    'E:\app\MD\oradata\oanet\WORKFLOW02.ora' SIZE 
    1024M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT  AUTO
   
--自動擴充套件表空間大小   
ALTER DATABASE DATAFILE 'E:\app\MD\oradata\oanet\WORKFLOW01.ora' AUTOEXTEND ON NEXT 200M MAXSIZE 20180M; 
ALTER DATABASE DATAFILE 'E:\app\MD\oradata\oanet\WORKFLOW02.ora' AUTOEXTEND ON NEXT 200M MAXSIZE 2048M;

建立使用者

--建立使用者
-- Create the user 
create user yx_base
  identified by "11"
  default tablespace WORKFLOW01
  temporary tablespace TEMP11
  profile DEFAULT;


-- Grant/Revoke role privileges 授予使用者connect 和 dba許可權
grant connect to yx_base with admin option;
grant dba to yx_base with admin option;
-- Grant/Revoke system privileges 授予使用者增刪改查等許可權
grant alter any table to yx_base with admin option;
grant create any table to yx_base with admin option;
grant delete any table to yx_base with admin option;
grant drop any table to yx_base with admin option;
grant insert any table to yx_base with admin option;
grant select any table to yx_base with admin option;
grant update any table to yx_base with admin option;
grant unlimited tablespace to yx_base with admin option;

 刪除使用者和表空間

drop user gd_base cascade;

drop tablespace WORKFLOW01 including contents and datafiles;

drop tablespace WORKFLOW02 including contents and datafiles;

drop tablespace WORKFLOWTEMP including contents and datafiles;

--ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp
--DROP TABLESPACE TEMP11 INCLUDING CONTENTS AND DATAFILES;

 

相關文章