Oracle19c 创建表空间遇到的坑

#常用的几个代码

--查询临时表空间select name From v$tempfile;
    --查询表空间select name from v$datafile;
    修改用户的密码alter user 用户名 identified by 密码;
    

昨天部署好oracle19c后，用以前oracle11g的笔记来创建表空间遇到了坑。这里写一下总结。

其实之所以遇到坑是因为相比于oracle11g,oracle19c多了一个CDB和PDB的概念（从12C开始出现）。

#确定表空间文件存储目录

[oracle@localhost ~]$ su - oracle[oracle@localhost ~]$ cd /opt/oracle/oradata/[oracle@localhost oradata]$ lsORCLCDB[oracle@localhost oradata]$ cd ORCLCDB/[oracle@localhost ORCLCDB]$ lscontrol01.ctl  control02.ctl  ORCLPDB1  pdbseed  redo01.LOG  redo02.log  redo03.log  Sysaux01.dbf  system01.dbf  temp01.dbf  undotbs01.dbf  users01.dbf[oracle@localhost ORCLCDB]$ mkdir anytxn_v2_dev[oracle@localhost ORCLCDB]$ cd anytxn_v2_dev/[oracle@localhost anytxn_v2_dev]$ pwd/opt/oracle/oradata/ORCLCDB/anytxn_v2_dev

#创建表空间文件

[oracle@localhost anytxn_v2_dev]$  SQLplus / as sysdbaSQL*Plus: Release 19.0.0.0.0 - PRoduction on Fri Feb 21 13:38:42 2020Version 19.3.0.0.0Copyright (c) 1982, 2019, Oracle.  All rights reserved.Connected to:Oracle Database 19c Enterprise EdITion Release 19.0.0.0.0 - ProductionVersion 19.3.0.0.0 SQL>
     CREATE TEMPORARY TABLESPACE  ANYTXN_DEV_DATA_TEMP TEMPFILE '/opt/oracle/oradata/ORCLCDB/anytxn_v2_dev/anytxn_v2_dev_temp.dbf' SIZE 32M AUTOEXTEND ON NEXT 32M MAXSIZE 20480M EXTENT MANAGEMENT LOCAL;
     Tablespace created. SQL>
     create tablespace ANYTXN_V2_DEV_DATA       loggingdatafile  '/opt/oracle/oradata/ORCLCDB/anytxn_v2_dev/anytxn_v2_dev_data.dbf'size 100Mautoextend onnext 100M maxsize 30480Mautoallocateextent management local segment space management auto;
      2    3    4    5    6    7    8    9   Tablespace created.

#创建用户

SQL>
     create user anytxn_v2_dev  identified by "jrx12345" default tablespace ANYTXN_V2_DEV_DATA  temporary tablespace ANYTXN_DEV_DATA_TEMP profile DEFAULT;
    create user anytxn_v2_dev  identified by "jrx12345" default tablespace ANYTXN_V2_DEV_DATA  temporary tablespace ANYTXN_DEV_DATA_TEMP profile DEFAULT            *ERROR at line 1:ORA-65096: invalid common user or role name

此错误是因为用户名称不符合规范，Oracle 12C开始引入了CDB与PDB的新特性。sqlplus / as sysdba命令默认登陆的是CDB数据库，而CDB数据库中要求所有新建用户用户名必须以c##开头，否则就会报以上错误，在PDB内创建用户则没有此要求

#修改用户名后创建用户

SQL>
     create user c##anytxn_v2_dev  identified by "jrx12345" default tablespace ANYTXN_V2_DEV_DATA  temporary tablespace ANYTXN_DEV_DATA_TEMP profile DEFAULT;
    create user c##anytxn_v2_dev  identified by "jrx12345" default tablespace ANYTXN_V2_DEV_DATA  temporary tablespace ANYTXN_DEV_DATA_TEMP profile DEFAULT*ERROR at line 1:ORA-65048: error encountered when processing the current DDL statement inpluggable database ORCLPDB1ORA-00959: tablespace 'ANYTXN_V2_DEV_DATA' does not exist

原因是在CDB内创建用户分配表空间时，所分配的表空间必须在PDB和CDB中同时存在，否则会报错。如果是在PDB与CDB有相同表空间的情况下给CDB用户分配表空间，则会分配CDB的表空间，给用户PDB的表空间并不受影响。所以要在PDB内创建相同的表空间，然后再回CDB创建用户

查询当前数据库名称SQL>
     show con_name CON_NAME------------------------------CDB$ROOT查询PDB数据库名称SQL>
     select name,oPEn_mode from v$pdbs;
     NAME--------------------------------------------------------------------------------OPEN_MODE------------------------------PDB$SEEDREAD ONLY ORCLPDB1READ WRITE切换数据库SQL>
     alter session set container=ORCLPDB1;
     Session altered. SQL>
     CREATE TEMPORARY TABLESPACE  ANYTXN_DEV_DATA_TEMP TEMPFILE '/opt/oracle/oradata/ORCLCDB/ORCLPDB1/anytxn_v2_dev/anytxn_v2_dev_temp.dbf' SIZE 32M AUTOEXTEND ON NEXT 32M MAXSIZE 20480M EXTENT MANAGEMENT LOCAL;
     Tablespace created. SQL>
     create tablespace ANYTXN_V2_DEV_DATA       loggingdatafile  '/opt/oracle/oradata/ORCLCDB/ORCLPDB1/anytxn_v2_dev/anytxn_v2_dev_data.dbf'size 100Mautoextend onnext 100M maxsize 30480Mautoallocateextent management local segment space management auto;
      2    3    4    5    6    7    8    9   Tablespace created. SQL>
     alter session set container=CDB$ROOT;
     Session altered. SQL>
     create user c##anytxn_v2_dev  identified by "jrx12345" default tablespace ANYTXN_V2_DEV_DATA  temporary tablespace ANYTXN_DEV_DATA_TEMP profile DEFAULT;
     User created. SQL>
     GRANT CONNECT,RESOURCE TO c##anytxn_v2_dev;
     Grant succeeded.

如上所示，创建成功，尝试用新用户连接数据库

[oracle@localhost anytxn_v2_dev]$  sqlplus c##anytxn_v2_dev/jrx12345  SQL*Plus: Release 19.0.0.0.0 - Production on Fri Feb 21 20:46:04 2020Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle.  All rights reserved. Last Successful login time: Fri Feb 21 2020 15:33:39 +08:00 Connected to:Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - ProductionVersion 19.3.0.0.0

到此这篇关于Oracle19c 创建表空间的文章就介绍到这了,更多相关Oracle19c 创建表空间内容请搜索以前的文章或继续浏览下面的相关文章希望大家以后多多支持！

• Oracle表空间与权限的深入讲解
• oracle表空间扩容详情
• Oracle如何设置表空间数据文件大小
• Oracle表空间设置和管理浅析
• Oracle表空间管理和用户管理介绍

声明：本文内容由网友自发贡献，本站不承担相应法律责任。对本内容有异议或投诉，请联系2913721942#qq.com核实处理，我们将尽快回复您，谢谢合作！