orcle12c创建表空间、新建用户、授权、连接问题处理

//安装时执行cmd命令

setup.exe -ignorePrereq -J"-Doracle.install.db.validate.supportedOSCheck=false"

//安装oracle之前plsql环境变量要去掉
TNS_ADMIN ----D:\upbest\setup\oracleClient
//全局变量名
SID：ORCL

用户名：HANPP
密码：Hpp2016

用户名：SYS
密码：Hppt2016

用户名：SYSTEM
密码：Hpp2016

-- 通过命令行进入
sqlplus / as sysdba;

-- 查看当前位置
show con_name

-- 切换会话进入pdb模式
alter session set container=pdborcl;

-- 重启服务器时需重新开启数据库
alter database open;

-- 创建表空间
create tablespace HANPP
datafile 'D:\app_oracle\admin\oradata\orcl\HANPP_DATA.DBF'
size 30G
autoextend on
next 10G maxsize 30G
extent management local;
-- 临时表空间
create temporary tablespace HANPP_TEMP
tempfile 'D:\app_oracle\admin\oradata\orcl\HANPP_TEMP.DBF'
size 30G
autoextend on
next 10G maxsize 30G
extent management local;

-- 将表空间给用户
pdb模式下创建
create user HANPP identified by HANPP
default tablespace HANPP
temporary tablespace HANPP_TEMP;


-- 授权
grant connect,resource to HANPP;
grant create table to HANPP;
grant drop any table to HANPP;
grant insert any table to HANPP;
grant update any table to HANPP;
grant create view to HANPP;
grant select any table to HANPP;
grant dba to HANPP;

处理pdb模式下该用户连接不上的问题
在tnsname.ora文件中新加一个SID，指定SID_NAME为PDBORCLPDBORCL

PDBORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = PDBORCL)
    )
  )

在listener.ora中添加信息，指定SID_NAME为PDBORCL

(SID_DESC =
      (SID_NAME = pdborcl)
      (ORACLE_HOME = D:\app_oracle\admin\product\12.1.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:D:\app_oracle\admin\product\12.1.0\dbhome_1\bin\oraclr12.dll")
)

查看所有用户信息
select * from all_users;