Oracle19c 创建表空间遇到的坑(2)-木庄网络博客

#修改用户名后创建用户

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 in
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 MANAGEMENT LOCAL;
 
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 创建表空间内容请搜索

标签：Oracle

返回前面的内容