oracle创建/删除表空间、创建/删除用户并赋予权限
2023-09-11 14:21:26 时间
创建表空间
分开执行如下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;
相关文章
- Oracle数据库提权(dba权限执行系统命令)
- 忘记oracle的sys用户密码如何修改以及Oracle 11g 默认用户名和密码
- 【oracle】静默安装 oracle 11gr2
- 【Oracle】sqlplus 遇到共享库权限不足
- 【Oracle】修改oracle监听端口
- 《oracle每天一练》Oracle冷备份与数据恢复
- Oracle中INSTR、SUBSTR和NVL的用法
- 使用Navicat for Oracle新建表空间、用户及权限赋予
- 【Oracle 集群】ORACLE DATABASE 11G RAC 知识图文详细教程之缓存融合技术和主要后台进程(四)
- Oracle 集群】ORACLE DATABASE 11G RAC 知识图文详细教程之ORACLE集群概念和原理(二)
- ORACLE权限中with admin option 、with grant option的用法
- oracle返回多结果集
- 为什么用 PostgreSQL 绑定变量 没有 Oracle pin S 等待问题
- Oracle中添加虚拟列(emp是oracle自带表)
- Oracle连接和用户、权限-12c
- oracle安装过程中先决条件检查失败的解决办法
- Oracle 复制随意表一行的SQL语句(測试Ok)
- 设备Oracle当误差:环境不符合要求》》解决方法
- 【SQL开发实战技巧】系列(四十四):Oracle12C常用新特性☞你知道吗Oracle新增了类似Mysql的自增列(Identity Columns)功能
- Oracle中含有recover 状态的数据文件环境中,做异机恢复
- 第二章: Oracle 约束