Oracle数据库创建表空间及用户授权

分为四步

第1步：创建临时表空间

create temporary tablespace test_temp
tempfile 'E:\app\Administrator\oradata\emis\datafile\test_temp.dbf'
size 50m
autoextend on
next 50m maxsize 2048m
extent management local;

linux服务器上操作

create temporary tablespace test_temp tempfile '/u01/app/oracle/oradata/EMIS/datafile/test_temp.dbf' size 50m autoextend on next 50m maxsize 2048m extent management local;

第2步：创建数据表空间

create tablespace test_data
logging
datafile 'E:\app\Administrator\oradata\emis\datafile\test_data.dbf'
size 50m
autoextend on
next 50m maxsize 2048m
extent management local;

linux服务器上操作

create tablespace test logging datafile '/u01/app/oracle/oradata/EMIS/datafile/test_data.dbf' size 50m autoextend on next 50m maxsize 2048m extent management local;

第3步：创建用户并指定表空间
CREATE USER 用户名
IDENTIFIED BY 密码
DEFAULT TABLESPACE 表空间(默认USERS)
TEMPORARY TABLESPACE 临时表空间(默认TEMP)

create user C##test identified by 123456
default tablespace test_data
temporary tablespace test_temp;

linux服务器上操作

create user test identified by 123456 default tablespace test temporary tablespace test_temp;

如果建的是CDB容器数据库，则用户必须加C##，可以用dbca重新建一个库，然后不要选择CDB 就会和以前的版本一样没有这个限制，CDB不适合新手或者初学者

第4步：给用户授予权限

grant connect,resource,dba,create session,create table,create view to C##test;

移除授权

revoke connect from test

删除表空间

DROP TABLESPACE tablespace_name INCLUDING CONTENTS AND DATAFILES;

解锁用户

ALTER USER username ACCOUNT UNLOCK;

修改用户密码

ALTER USER EMIS_FRAME IDENTIFIED BY EMIS_FRAME;
ALTER USER system identified by xxzx#emis#73;

查看已经创建好的表空间：

select default_tablespace, temporary_tablespace, d.username from dba_users d;

Linux上正式创建表空间和用户

create temporary tablespace EMIS_TEST_TEMP tempfile '/u01/app/oracle/oradata/EMIS/datafile/EMIS_TEST_TEMP.dbf' size 50m autoextend on next 50m maxsize 2048m extent management local;

create tablespace EMIS_TEST logging datafile '/u01/app/oracle/oradata/EMIS/datafile/EMIS_TEST_DATA.dbf' size 50m autoextend on next 50m maxsize 2048m extent management local;

create user EMIS_TEST identified by EMIS_TEST default tablespace EMIS_TEST temporary tablespace EMIS_TEST_TEMP;

grant connect,resource,dba,create session,create table,create view to EMIS_TEST;