Oracle數(shù)據(jù)庫創(chuàng)建本地用戶、授予權(quán)限、創(chuàng)建表并插入數(shù)據(jù)實例代碼
一. 用戶的種類
?在 Oracle 多租戶架構(gòu)中(從 Oracle 12c 開始),用戶分為
- 普通用戶(
Common User
)- 用戶的名稱必須以
C##
開頭,在CDB
中創(chuàng)建 - 適用于管理員用戶,用戶管理
CDB
下的多個PDB
- 用戶的名稱必須以
- 本地用戶(
Local User
)- 用戶的名稱無需以
C##
開頭,在PDB
中創(chuàng)建 - 更適合應(yīng)用開發(fā)和租戶管理
- 我們平時開發(fā)時用的多是本地用戶
- 用戶的名稱無需以
?數(shù)據(jù)庫剛被安裝后,并沒有本地用戶,我們需要通過system
用戶登錄Oracle之后,創(chuàng)建本地用戶。
二. 切換session為PDB
?SHOW CON_NAME;
:顯示當(dāng)前會話所連接的容器名稱
- 在包含 CDB(容器數(shù)據(jù)庫)和 PDB(可插拔數(shù)據(jù)庫)的環(huán)境中,顯示會話所連接的容器的名稱。
- 容器可以是根容器(
CDB$ROOT
)、種子數(shù)據(jù)庫(PDB$SEED
)或某個具體的 PDB。
apluser@ubuntu24-01:~$ sqlplus system/oracle@192.168.118.137/XE SQL*Plus: Release 21.0.0.0.0 - Production on Wed Jan 1 09:00:28 2025 Version 21.16.0.0.0 Copyright (c) 1982, 2022, Oracle. All rights reserved. Last Successful login time: Tue Dec 31 2024 23:15:56 +09:00 Connected to: Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production Version 21.3.0.0.0 SQL> SQL> SHOW CON_NAME; CON_NAME ------------------------------ CDB$ROOT SQL>
?查看數(shù)據(jù)庫中所有的PDB
SELECT PDB_ID, PDB_NAME, STATUS FROM DBA_PDBS;
SELECT NAME FROM V$PDBS;
-- 方式1 SQL> SELECT PDB_ID, PDB_NAME, STATUS FROM DBA_PDBS; PDB_ID PDB_NAME STATUS ------- ---------- ---------- 3 XEPDB1 NORMAL 2 PDB$SEED NORMAL -- 方式2 SQL> SELECT NAME FROM V$PDBS; NAME -------------- PDB$SEED XEPDB1
?切換當(dāng)前用戶的session
為PDB
ALTER SESSION SET CONTAINER = XEPDB1;
- ??我們只有在
PDB
的session
中創(chuàng)建的才是PDB
的用戶,如果不切換session
的話,創(chuàng)建的是CDB
的用戶。??
-- 切換session到根容器 SQL> ALTER SESSION SET CONTAINER = CDB$ROOT; Session altered. -- 切換session到PDB SQL> ALTER SESSION SET CONTAINER = XEPDB1; Session altered. -- 查看當(dāng)前容器名稱 SQL> SHOW CON_NAME; CON_NAME ------------------------------ XEPDB1 SQL>
三. 創(chuàng)建用戶并授予權(quán)限
?創(chuàng)建用戶并指定密碼
SQL> CREATE USER db_user IDENTIFIED BY oracle; User created.
?為創(chuàng)建的用戶賦予權(quán)限
-- 授予用戶登錄的權(quán)限 GRANT CREATE SESSION TO db_user; -- 授予用戶創(chuàng)建 表,視圖,存儲過程,序列對象的權(quán)限 GRANT CREATE TABLE, CREATE VIEW, CREATE PROCEDURE, CREATE SEQUENCE TO db_user;
?查看創(chuàng)建好的PDB用戶
COMMON = 'NO'
:濾出PDB的本地用戶COMMON = 'YES'
:濾出CDB的普通用戶
SQL> SELECT USER_ID, USERNAME, ACCOUNT_STATUS, COMMON, TO_CHAR(LAST_LOGIN, 'YYYY/MM/DD HH24:MI:SS') LAST_LOGIN FROM DBA_USERS WHERE COMMON = 'NO'; USER_ID USERNAME ACCOUNT_STATUS COM LAST_LOGIN -------- ---------- ---------------- ---- ------------------- 108 PDBADMIN OPEN NO 110 DB_USER OPEN NO
四. 創(chuàng)建表空間
默認表空間
:用戶在不指定表空間的情況下創(chuàng)建的對象(如表)會存儲到默認表空間。臨時表空間
:用戶在執(zhí)行排序操作(如 ORDER BY 或 GROUP BY)時會使用臨時表空間。SIZE 100M AUTOEXTEND ON
:表空間大小為100M,當(dāng)空間不足時,會自動增加
-- 創(chuàng)建默認表空間并指定表空間文件 CREATE TABLESPACE DB_STUDY_LOCAL_01 DATAFILE 'C:/app/FengYeHong/product/21c/custom_table_space/local_db_study_01.dbf' SIZE 100M AUTOEXTEND ON; -- 創(chuàng)建臨時表空間并指定臨時表空間文件 CREATE TEMPORARY TABLESPACE DB_STUDY_TMP_LOCAL_01 TEMPFILE 'C:/app/FengYeHong/product/21c/custom_table_space/local_db_study_tmp_01.dbf' SIZE 100M AUTOEXTEND ON;
?查看創(chuàng)建的表空間
- 查看數(shù)據(jù)庫中所有的表空間與狀態(tài)
SQL> SELECT TABLESPACE_NAME, CONTENTS, STATUS FROM DBA_TABLESPACES; TABLESPACE_NAME CONTENTS STATUS ------------------------------ --------------------- --------- SYSTEM PERMANENT ONLINE SYSAUX PERMANENT ONLINE UNDOTBS1 UNDO ONLINE TEMP TEMPORARY ONLINE USERS PERMANENT ONLINE DB_STUDY_LOCAL_01 PERMANENT ONLINE DB_STUDY_TMP_LOCAL_01 TEMPORARY ONLINE 7 rows selected.
- 查看臨時表空間的路徑,字節(jié),狀態(tài)
SQL> set markup csv on SQL> SQL> select name,bytes,status from v$tempfile; "NAME","BYTES","STATUS" "C:\APP\FENGYEHONG\PRODUCT\21C\ORADATA\XE\XEPDB1\TEMP01.DBF",36700160,"ONLINE" "C:\APP\FENGYEHONG\PRODUCT\21C\CUSTOM_TABLE_SPACE\LOCAL_DB_STUDY_TMP_01.DBF",104857600,"ONLINE"
- 查看默認表空間的路徑,字節(jié),狀態(tài)
SQL> set markup csv on SQL> SQL> SELECT FILE_NAME, TABLESPACE_NAME, BYTES, STATUS FROM DBA_DATA_FILES; "FILE_NAME","TABLESPACE_NAME","BYTES","STATUS" "C:\APP\FENGYEHONG\PRODUCT\21C\ORADATA\XE\XEPDB1\SYSTEM01.DBF","SYSTEM",293601280,"AVAILABLE" "C:\APP\FENGYEHONG\PRODUCT\21C\ORADATA\XE\XEPDB1\SYSAUX01.DBF","SYSAUX",408944640,"AVAILABLE" "C:\APP\FENGYEHONG\PRODUCT\21C\ORADATA\XE\XEPDB1\UNDOTBS01.DBF","UNDOTBS1",104857600,"AVAILABLE" "C:\APP\FENGYEHONG\PRODUCT\21C\ORADATA\XE\XEPDB1\USERS01.DBF","USERS",5242880,"AVAILABLE" "C:\APP\FENGYEHONG\PRODUCT\21C\CUSTOM_TABLE_SPACE\LOCAL_DB_STUDY_01.DBF","DB_STUDY_LOCAL_01",104857600,"AVAILABLE"
?如果要刪除表空間的話,可以使用下面的命令。
DROP TABLESPACE DB_STUDY_01 INCLUDING CONTENTS AND DATAFILES; DROP TABLESPACE DB_STUDY_TMP_01 INCLUDING CONTENTS AND DATAFILES;
五. 為用戶分配默認表空間并指定表空間配額
?將用戶和表空間關(guān)聯(lián)起來,為用戶分配默認表空間和臨時表空間。
ALTER USER db_user DEFAULT TABLESPACE DB_STUDY_LOCAL_01 TEMPORARY TABLESPACE DB_STUDY_TMP_LOCAL_01;
?確認用戶和表空間的關(guān)聯(lián)
SQL> SELECT username, default_tablespace, temporary_tablespace FROM dba_users WHERE username = 'DB_USER'; USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE ------------ ------------------------------ ------------------------------ DB_USER DB_STUDY_LOCAL_01 DB_STUDY_TMP_LOCAL_01 SQL>
?用戶有了默認表空間之后,并不意味著可以創(chuàng)建表并成功插入數(shù)據(jù),還需要向用戶分配表空間的配額,指定用戶可以使用的存儲配額。
DBA_TS_QUOTAS
是 Oracle 數(shù)據(jù)庫中的一個數(shù)據(jù)字典視圖
,主要用于顯示用戶在各個表空間上的配額(Quota
)信息。- 記錄用戶在某個表空間中被分配的存儲空間限制(配額)。
- 可以查看配額是有限制的(如 1GB)還是無限制的(UNLIMITED)。
- 表空間配額示例
ALTER USER db_user QUOTA 50M ON DB_STUDY_LOCAL_01;
:指定用戶有50M的配額。ALTER USER db_user QUOTA UNLIMITED ON DB_STUDY_LOCAL_01;
:指定用戶有無限的配額。
-- 注意,需要在PDB的session中執(zhí)行 SQL> ALTER SESSION SET CONTAINER = XEPDB1; Session altered. SQL> -- 在未分配配額之前,查詢不到任何數(shù)據(jù) SQL> SELECT * FROM DBA_TS_QUOTAS WHERE USERNAME = UPPER('db_user'); no rows selected SQL> -- 指定 db_user 用戶對 DB_STUDY_LOCAL_01 表空間有無限的配額,可以隨意使用 SQL> ALTER USER db_user QUOTA UNLIMITED ON DB_STUDY_LOCAL_01; User altered. SQL> -- 分配完成之后,進一步查看 SQL> SELECT * FROM DBA_TS_QUOTAS WHERE USERNAME = UPPER('db_user'); "TABLESPACE_NAME","USERNAME","BYTES","MAX_BYTES","BLOCKS","MAX_BLOCKS","DROPPED" "DB_STUDY_LOCAL_01","DB_USER",0,-1,0,-1,"NO"
六. 通過創(chuàng)建的用戶進行登錄
?我們創(chuàng)建的是本地用戶,因此通過sqlplus命令進行登錄的時候,必須明確的指出使用的是名稱為XEPDB1
的PDB
- 在 Oracle 的多租戶架構(gòu)中,你需要確保連接到正確的
PDB
(可插入數(shù)據(jù)庫)而不是CDB
(容器數(shù)據(jù)庫)。 - 在連接數(shù)據(jù)庫時,服務(wù)名稱決定了連接的是哪個數(shù)據(jù)庫實例。
CDB
是容器數(shù)據(jù)庫,通常是你管理和創(chuàng)建多個PDB
的地方。- 每個
PDB
是一個獨立的數(shù)據(jù)庫,可以有自己的用戶、數(shù)據(jù)和表空間等。
apluser@ubuntu24-01:~$ sqlplus db_user/oracle@192.168.118.137/XEPDB1 SQL*Plus: Release 21.0.0.0.0 - Production on Wed Jan 1 10:11:43 2025 Version 21.16.0.0.0 Copyright (c) 1982, 2022, Oracle. All rights reserved. Last Successful login time: Tue Dec 31 2024 22:58:55 +09:00 Connected to: Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production Version 21.3.0.0.0 SQL> SHOW CON_NAME; CON_NAME ------------------------------ XEPDB1 SQL>
?修改tnsnames.ora
文件
- 如果我們在多臺服務(wù)器上有多個PDB的話,可以在
sqlplus
客戶端安裝的機器上,配置tnsnames.ora
文件的內(nèi)容,便于登錄
apluser@ubuntu24-01:~$ cat $ORACLE_HOME/network/admin/tnsnames.ora SERVICE_XE_CLIENT = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.118.137)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XE) ) ) SERVICE_XEPDB1_CLIENT = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.118.137)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XEPDB1) ) ) apluser@ubuntu24-01:~$
- 修改完成之后,就可以通過下面這種方式進行登錄了
apluser@ubuntu24-01:~$ sqlplus db_user/oracle@SERVICE_XEPDB1_CLIENT SQL*Plus: Release 21.0.0.0.0 - Production on Wed Jan 1 10:21:32 2025 Version 21.16.0.0.0 Copyright (c) 1982, 2022, Oracle. All rights reserved. Last Successful login time: Wed Jan 01 2025 10:11:44 +09:00 Connected to: Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production Version 21.3.0.0.0 SQL>
七. 創(chuàng)建腳本,簡化登錄
- 每次登錄oracle數(shù)據(jù)庫,都要輸入一長串的命令,很繁瑣。可以創(chuàng)建一個腳本簡化登錄。
apluser@ubuntu24-01:~$ which oralce_db_connect /home/apluser/bin/oralce_db_connect apluser@ubuntu24-01:~$ apluser@ubuntu24-01:~$ ls -l /home/apluser/bin/oralce_db_connect -rwxrwxr-x 1 apluser apluser 912 Jan 1 08:43 /home/apluser/bin/oralce_db_connect apluser@ubuntu24-01:~$ apluser@ubuntu24-01:~$ cat /home/apluser/bin/oralce_db_connect #!/bin/bash # ################################################ # 簡介: # 動態(tài)連接 Oracle 數(shù)據(jù)庫 # # 完整方式連接數(shù)據(jù)庫 # sqlplus db_user/oracle@192.168.118.137/XEPDB1 # # 用法 # 1. oralce_db_connect # 2. oralce_db_connect dba # # ################################################ # 默認參數(shù) USERNAME="db_user" PASSWORD="oracle" SERVICE="SERVICE_XEPDB1_CLIENT" MSG="通過普通用戶登錄..." # 如果傳入?yún)?shù)為 "dba",則使用 system 用戶登錄 if [ "$1" == "dba" ]; then USERNAME="system" PASSWORD="oracle" SERVICE="SERVICE_XE_CLIENT" MSG="通過dba用戶登錄..." fi # 構(gòu)造連接oracle數(shù)據(jù)庫的命令 connect_oracle_db_command="sqlplus ${USERNAME}/${PASSWORD}@${SERVICE}" # 打印連接oracle數(shù)據(jù)庫的命令 echo "${connect_oracle_db_command}" # 打印提示消息 echo -e "\e[1;31m$MSG\e[0m" # 連接oracle數(shù)據(jù)庫 eval "${connect_oracle_db_command}"
- 登錄效果
八. 查看用戶信息
8.1 無需DAB用戶
?USER_USERS
表
- 顯示當(dāng)前會話用戶的
基本信息
,例如用戶名、創(chuàng)建時間、默認表空間等。 - 僅適用于當(dāng)前登錄用戶,無法查看其它用戶的信息。
SQL> SELECT USERNAME, USER_ID, ACCOUNT_STATUS FROM USER_USERS; "USERNAME","USER_ID","ACCOUNT_STATUS" "DB_USER",110,"OPEN"
?ALL_USERS
表
- 不需要DBA用戶
- 顯示數(shù)據(jù)庫中所有用戶的基本信息,但比
DBA_USERS
提供的信息少。
SQL> SELECT USER_ID, USERNAME, COMMON FROM ALL_USERS WHERE USERNAME = 'DB_USER'; "USER_ID","USERNAME","COMMON" 110,"DB_USER","NO"
8.2 需要DAB用戶
??注意
??
當(dāng)使用system
的DBA用戶進行查看的時候,注意切換當(dāng)前的session為 PDB
(可插入數(shù)據(jù)庫)而不是 CDB
(容器數(shù)據(jù)庫)。
-- XEPDB1 為 PDB 的容器名稱 ALTER SESSION SET CONTAINER = XEPDB1;
?DBA_USERS
表
- 顯示數(shù)據(jù)庫中所有用戶的
詳細信息
,包括用戶名、賬戶狀態(tài)、默認表空間、密碼有效期等。 - 數(shù)據(jù)庫管理員(
DBA
)管理用戶時使用,查看所有用戶的賬戶狀態(tài),如是否鎖定、密碼是否過期等。
SQL> SELECT USER_ID, USERNAME, ACCOUNT_STATUS, COMMON, TO_CHAR(LAST_LOGIN, 'YYYY/MM/DD HH24:MI:SS') LAST_LOGIN FROM DBA_USERS WHERE USERNAME = 'DB_USER'; USER_ID USERNAME ACCOUNT_STATUS COM LAST_LOGIN ---------- ----------------- --------------------- --- ------------------- 110 DB_USER OPEN NO 2025/01/01 13:13:49
?DBA_SYS_PRIVS
表
- 查看用戶所有的權(quán)限
SQL> SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE = 'DB_USER'; GRANTEE PRIVILEGE ADM COM INH ------- ---------------------------------------- --- --- --- DB_USER CREATE PROCEDURE NO NO NO DB_USER CREATE SEQUENCE NO NO NO DB_USER CREATE VIEW NO NO NO DB_USER CREATE TABLE NO NO NO DB_USER CREATE SESSION NO NO NO
九. 創(chuàng)建表,并插入數(shù)據(jù)
9.1 查看當(dāng)前用戶的schema
?一般來說,用戶默認的schema和用戶名相同,也可以通過下面這種方式進行查看。
SQL> SELECT SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA') AS DEFAULT_SCHEMA FROM DUAL; "DEFAULT_SCHEMA" "DB_USER"
9.2 插入數(shù)據(jù)
?在創(chuàng)建表的時候,指定表空間,如果不指定的話,將會使用當(dāng)前用戶默認的表空間。
TABLESPACE DB_STUDY_LOCAL_01
:指定表空間DB_USER.PERSON_TABLE
:指定schema
CREATE TABLE DB_USER.PERSON_TABLE ( id NUMBER PRIMARY KEY, name VARCHAR2(50), age NUMBER, email VARCHAR2(100), created_date DATE ) TABLESPACE DB_STUDY_LOCAL_01;
?寫一個腳本,自動向表中插入100條數(shù)據(jù)
BEGIN FOR i IN 1..100 LOOP INSERT INTO DB_USER.PERSON_TABLE (id, name, age, email, created_date) VALUES ( i, 'Name_' || i, TRUNC(DBMS_RANDOM.VALUE(18, 60)), -- 隨機年齡 'user' || i || '@example.com', SYSDATE - DBMS_RANDOM.VALUE(0, 365) -- 隨機日期 ); END LOOP; COMMIT; END; /
9.3 查看
?本地用戶查看表名所在的表空間 ?? USER_TABLES
SQL> SELECT TABLE_NAME, TABLESPACE_NAME FROM USER_TABLES WHERE TABLE_NAME = 'PERSON_TABLE'; "TABLE_NAME","TABLESPACE_NAME" "PERSON_TABLE","DB_STUDY_LOCAL_01"
?DBA用戶查看表名所在的表空間 ?? DBA_TABLES
SQL> SELECT TABLE_NAME, OWNER, TABLESPACE_NAME FROM DBA_TABLES WHERE TABLE_NAME = 'PERSON_TABLE'; "TABLE_NAME","OWNER","TABLESPACE_NAME" "PERSON_TABLE","DB_USER","DB_STUDY_LOCAL_01"
?可以看到,數(shù)據(jù)插入成功后可以被查詢到。
總結(jié)
到此這篇關(guān)于Oracle數(shù)據(jù)庫創(chuàng)建本地用戶、授予權(quán)限、創(chuàng)建表并插入數(shù)據(jù)的文章就介紹到這了,更多相關(guān)Oracle創(chuàng)建本地用戶、授予權(quán)限內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
oracle 存儲過程、函數(shù)和觸發(fā)器用法實例詳解
這篇文章主要介紹了oracle 存儲過程、函數(shù)和觸發(fā)器用法,結(jié)合實例形式詳細分析了oralce 存儲過程、函數(shù)和觸發(fā)器具體功能、原理、定義、使用方法及相關(guān)操作注意事項,需要的朋友可以參考下2020-02-02Oracle PL/SQL中“表或視圖不存在“錯誤的解決方案
在Oracle PL/SQL開發(fā)中,許多開發(fā)者都遇到過這個令人困惑的錯誤表或視圖不存在,這個錯誤看似簡單,但背后可能有多種原因,特別是當(dāng)表確實存在時,這個錯誤更讓人摸不著頭腦,所以本文介紹了詳細的解決方案,需要的朋友可以參考下2025-04-04