oracle创建/删除表空间、创建/删除用户并赋予权限

时间：2021-07-13 09:10:08

创建表空间

分开执行如下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 ""

  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;

相关文章