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]$ ls
ORCLCDB
[oracle@localhost oradata]$ cd ORCLCDB/
[oracle@localhost ORCLCDB]$ ls
[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 sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Feb 21 13:38:42 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 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
logging
datafile '/opt/oracle/oradata/ORCLCDB/anytxn_v2_dev/anytxn_v2_dev_data.dbf'
size 100M
autoextend on
next 100M maxsize 30480M
autoallocate
extent 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 inumbrella歌词翻译
pluggable database ORCLPDB1
粤语经典ORA-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$SEED
READ ONLY
ORCLPDB1
景德镇景点READ 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 MANAGEM Tablespace created.
SQL> create tablespace ANYTXN_V2_DEV_DATA
logging
datafile '/opt/oracle/oradata/ORCLCDB/ORCLPDB1/anytxn_v2_dev/anytxn_v2_dev_data.dbf'
size 100M
潘婷洗发水好吗autoextend on
next 100M maxsize 30480M
autoallocate
extent 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 2020
Version 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 - Production
Version 19.3.0.0.0
到此这篇关于Oracle19c 创建表空间的⽂章就介绍到这了，更多相关Oracle19c 创建表空间内容请搜索以前的⽂章或继续浏览下⾯的相关⽂章希望⼤家以后多多⽀持！
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论