Oracle19c 創(chuàng)建表空間遇到的坑
發(fā)布日期：2021-12-24 20:02 | 文章來源：gibhub
#常用的幾個代碼
--查詢臨時表空間 select name from v$tempfile; --查詢表空間 select name from v$datafile; 修改用戶的密碼 alter user 用戶名 identified by 密碼;
昨天部署好oracle19c后，用以前oracle11g的筆記來創(chuàng)建表空間遇到了坑。這里寫一下總結(jié)。
其實之所以遇到坑是因為相比于oracle11g,oracle19c多了一個CDB和PDB的概念（從12C開始出現(xiàn)）。
#確定表空間文件存儲目錄
[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)建用戶
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
此錯誤是因為用戶名稱不符合規(guī)范，Oracle 12C開始引入了CDB與PDB的新特性。sqlplus / as sysdba命令默認登陸的是CDB數(shù)據(jù)庫，而CDB數(shù)據(jù)庫中要求所有新建用戶用戶名必須以c##開頭，否則就會報以上錯誤，在PDB內(nèi)創(chuàng)建用戶則沒有此要求
#修改用戶名后創(chuàng)建用戶
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)建用戶分配表空間時，所分配的表空間必須在PDB和CDB中同時存在，否則會報錯。如果是在PDB與CDB有相同表空間的情況下給CDB用戶分配表空間，則會分配CDB的表空間，給用戶PDB的表空間并不受影響。所以要在PDB內(nèi)創(chuàng)建相同的表空間，然后再回CDB創(chuàng)建用戶
查詢當前數(shù)據(jù)庫名稱 SQL> show con_name CON_NAME ------------------------------ CDB$ROOT 查詢PDB數(shù)據(jù)庫名稱 SQL> select name,open_mode from v$pdbs; NAME -------------------------------------------------------------------------------- OPEN_MODE ------------------------------ PDB$SEED READ ONLY ORCLPDB1 READ WRITE 切換數(shù)據(jù)庫 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)建成功，嘗試用新用戶連接數(shù)據(jù)庫
[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)容請搜索本站以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持本站！
版權(quán)聲明：本站文章來源標注為YINGSOO的內(nèi)容版權(quán)均為本站所有，歡迎引用、轉(zhuǎn)載，請保持原文完整并注明來源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站，禁止在非www.sddonglingsh.com所屬的服務(wù)器上建立鏡像，否則將依法追究法律責(zé)任。本站部分內(nèi)容來源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來，僅供學(xué)習(xí)參考，不代表本站立場，如有內(nèi)容涉嫌侵權(quán)，請聯(lián)系alex-e#qq.com處理。
相關(guān)文章
關(guān)注官方微信