Oracle创建用户及表空间

Oracle12c 创建用户及表空间


-- 创建临时空间,找到D:\app\orcl\oradata\orcl目录，创建local目录
-- drop tablespace local_temp;
create temporary tablespace local_temp
tempfile 'D:\app\orcl\oradata\orcl\local\local_temp.dbf'
size 32m
autoextend on
next 32m maxsize 2048m
extent management local;


-- 创建数据表空间,找到D:\app\orcl\oradata\orcl目录，创建local目录
-- drop tablespace local_data;
create tablespace local_data
logging
datafile 'D:\app\orcl\oradata\orcl\local\local_data.dbf'
size 32m
autoextend on
next 32m maxsize 2048m
extent management local;


-- 创建用户并指定表空间
create user c##wangxin identified by wangxin
profile default
default tablespace local_data
temporary tablespace local_temp
account unlock;
-- 刚开始用户名为wangxin，提示错误ORA-65096：公用用户名或角色名无效
-- 网上查资料，说是取名前缀必须为c##，所以用户名也变成了c#wangxin
/*
ORA-65048:在可插入数据库PDBORCL中处理当前DDL语句时出错
ORA-00959:表空间‘LOCL_DATA’不存在
(注：开始一直提示这个错误，我就纳闷，后来重启下Oracle服务之后，就不会报这个错了)
*/


-- 授权给c##wangxin ，ps:根据需要设置权限。
GRANT CREATE ANY VIEW,DROP ANY VIEW,CONNECT,RESOURCE,CREATE SESSION,DBA TO c##wangxin;
/*GRANT CREATE USER,DROP USER,ALTER USER,CREATE ANY VIEW,DROP ANY VIEW,
EXP_FULL_DATABASE,IMP_FULL_DATABASE,CONNECT,RESOURCE,CREATE SESSION,
select any table,DBA TO c##wangxin;*/


-- Other
-- select * from v$version;
-- select name from v$datafile;
-- select * from v$pdbs;