/*分为四步 */
/*第1步：创建临时表空间 */
create temporary tablespace test_temptempfile 'E:\app\Administrator\oradata\emis\datafile\test_temp.dbf'size 50mautoextend onnext 50m maxsize 2048mextent 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_dataloggingdatafile 'E:\app\Administrator\oradata\emis\datafile\test_data.dbf'size 50mautoextend onnext 50m maxsize 2048mextent 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 123456default tablespace test_datatemporary 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;
如果觉得《Oracle数据库创建表空间及用户授权》对你有帮助，请点赞、收藏，并留下你的观点哦！
