Oracle表空间和用户的创建和删除-优快云博客

本文档详细介绍了如何使用SQL Plus连接Oracle数据库，创建表空间及用户，并授予必要的权限。此外，还提供了删除用户和表空间的具体步骤。

2019独角兽企业重金招聘Python工程师标准>>>

一、使用sqlplus连接数据库

sqlplus 用户名/密码 as 角色

示例：

sqlplus system/password as sysdba

二、创建表空间

CREATE TABLESPACE ZMB DATAFILE 'D:\software\oracle11g\space\ZMB.DBF' SIZE 2048M AUTOEXTEND ON NEXT 256M MAXSIZE Unlimited;

####三、创建用户并赋予相应的权限

declare
  dh varchar2(10);
Begin
  FOR R IN (select name from v$tablespace where name like 'ZMB' order by name)
    Loop
    dh:=R.NAME;
    Execute Immediate 'CREATE USER ' || dh || '  PROFILE "DEFAULT" '
          ||'IDENTIFIED BY admin123 DEFAULT TABLESPACE ' || dh || ' '
          ||'TEMPORARY TABLESPACE "TEMP" '
          ||'ACCOUNT UNLOCK';  
    Execute Immediate 'GRANT ALTER ANY INDEX TO ' || dh || '';
    Execute Immediate 'GRANT ALTER ANY PROCEDURE TO ' || dh || '';
    Execute Immediate 'GRANT ALTER ANY SEQUENCE TO ' || dh || '';
    Execute Immediate 'GRANT ALTER ANY TABLE TO ' || dh || '';
    Execute Immediate 'GRANT ALTER ANY TRIGGER TO ' || dh || '';
    Execute Immediate 'GRANT CREATE ANY INDEX TO ' || dh || '';
    Execute Immediate 'GRANT CREATE ANY PROCEDURE TO ' || dh || '';
    Execute Immediate 'GRANT CREATE ANY SEQUENCE TO ' || dh || '';
    Execute Immediate 'GRANT CREATE ANY TABLE TO ' || dh || '';
    Execute Immediate 'GRANT CREATE ANY TRIGGER TO ' || dh || '';
    Execute Immediate 'GRANT CREATE ANY VIEW TO ' || dh || '';
    Execute Immediate 'GRANT CREATE PUBLIC SYNONYM TO ' || dh || '';
    Execute Immediate 'GRANT DEBUG ANY PROCEDURE TO ' || dh || '';
    Execute Immediate 'GRANT DELETE ANY TABLE TO ' || dh || '';
    Execute Immediate 'GRANT ALTER SYSTEM TO ' || dh || '';
    Execute Immediate 'GRANT DROP ANY INDEX TO ' || dh || '';
    Execute Immediate 'GRANT DROP ANY PROCEDURE TO ' || dh || '';
    Execute Immediate 'GRANT DROP ANY SEQUENCE TO ' || dh || '';
    Execute Immediate 'GRANT DROP ANY TABLE TO ' || dh || '';
    Execute Immediate 'GRANT DROP PUBLIC SYNONYM TO ' || dh || '';
    Execute Immediate 'GRANT DROP ANY TRIGGER TO ' || dh || '';
    Execute Immediate 'GRANT DROP ANY VIEW TO ' || dh || '';
    Execute Immediate 'GRANT EXECUTE ANY PROCEDURE TO ' || dh || '';
    Execute Immediate 'GRANT INSERT ANY TABLE TO ' || dh || '';
    Execute Immediate 'GRANT SELECT ANY SEQUENCE TO ' || dh || '';
    Execute Immediate 'GRANT SELECT ANY TABLE TO ' || dh || '';
    Execute Immediate 'GRANT SELECT ANY DICTIONARY TO ' || dh || '';
    Execute Immediate 'GRANT UNDER ANY TABLE TO ' || dh || '';
    Execute Immediate 'GRANT GRANT ANY ROLE TO ' || dh || '';
    Execute Immediate 'GRANT GRANT ANY PRIVILEGE TO ' || dh || '';
    Execute Immediate 'GRANT UNDER ANY VIEW TO ' || dh || '';
    Execute Immediate 'GRANT UPDATE ANY TABLE TO ' || dh || '';
    Execute Immediate 'GRANT UNLIMITED TABLESPACE TO ' || dh || '';
    Execute Immediate 'GRANT "CONNECT" TO ' || dh || '';
    Execute Immediate 'GRANT "DBA" TO ' || dh || '';
    Execute Immediate 'alter user ' || dh || ' quota unlimited on ' || dh || '';
    END LOOP;
End;
/

四、删除用户

drop user ZMB cascade;

五、删除表空间

DROP TABLESPACE ZMB INCLUDING CONTENTS AND DATAFILES;

转载于:https://my.oschina.net/zhaokaiju/blog/825185