快捷導(dǎo)航

Oracle19c 創(chuàng)建表空間遇到的坑

更新時(shí)間：2021年10月19日 14:25:39 作者：W~C停用

昨天部署好oracle19c后，用以前oracle11g的筆記來(lái)創(chuàng)建表空間遇到了坑，今天特此把解決過(guò)程分享到腳本之家平臺(tái)，感興趣的朋友一起看看吧

#常用的幾個(gè)代碼

--查詢(xún)臨時(shí)表空間
select name from v$tempfile;

--查詢(xún)表空間
select name from v$datafile;
修改用戶(hù)的密碼
alter user 用戶(hù)名 identified by 密碼;

昨天部署好oracle19c后，用以前oracle11g的筆記來(lái)創(chuàng)建表空間遇到了坑。這里寫(xiě)一下總結(jié)。

其實(shí)之所以遇到坑是因?yàn)橄啾扔趏racle11g,oracle19c多了一個(gè)CDB和PDB的概念（從12C開(kāi)始出現(xiàn)）。

#確定表空間文件存儲(chǔ)目錄

[oracle@localhost ~]$ su - oracle
[oracle@localhost ~]$ cd /opt/oracle/oradata/
[oracle@localhost oradata]$ ls
ORCLCDB
[oracle@localhost oradata]$ cd ORCLCDB/
[oracle@localhost ORCLCDB]$ ls
control01.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

#創(chuàng)建表空間文件

[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.

#創(chuàng)建用戶(hù)

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

此錯(cuò)誤是因?yàn)橛脩?hù)名稱(chēng)不符合規(guī)范，Oracle 12C開(kāi)始引入了CDB與PDB的新特性。sqlplus / as sysdba命令默認(rèn)登陸的是CDB數(shù)據(jù)庫(kù)，而CDB數(shù)據(jù)庫(kù)中要求所有新建用戶(hù)用戶(hù)名必須以c##開(kāi)頭，否則就會(huì)報(bào)以上錯(cuò)誤，在PDB內(nèi)創(chuàng)建用戶(hù)則沒(méi)有此要求

#修改用戶(hù)名后創(chuàng)建用戶(hù)

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內(nèi)創(chuàng)建用戶(hù)分配表空間時(shí)，所分配的表空間必須在PDB和CDB中同時(shí)存在，否則會(huì)報(bào)錯(cuò)。如果是在PDB與CDB有相同表空間的情況下給CDB用戶(hù)分配表空間，則會(huì)分配CDB的表空間，給用戶(hù)PDB的表空間并不受影響。所以要在PDB內(nèi)創(chuàng)建相同的表空間，然后再回CDB創(chuàng)建用戶(hù)

查詢(xún)當(dāng)前數(shù)據(jù)庫(kù)名稱(chēng)
SQL> show con_name
 
CON_NAME
------------------------------
CDB$ROOT
查詢(xún)PDB數(shù)據(jù)庫(kù)名稱(chēng)
SQL> select name,open_mode from v$pdbs;
 
NAME
--------------------------------------------------------------------------------
OPEN_MODE
------------------------------
PDB$SEED
READ ONLY
 
ORCLPDB1
READ WRITE
切換數(shù)據(jù)庫(kù)
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.

如上所示，創(chuàng)建成功，嘗試用新用戶(hù)連接數(shù)據(jù)庫(kù)

[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

到此這篇關(guān)于Oracle19c 創(chuàng)建表空間的文章就介紹到這了,更多相關(guān)Oracle19c 創(chuàng)建表空間內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: