IT俱乐部 Oracle Oracle数据库创建本地用户、授予权限、创建表并插入数据实例代码

Oracle数据库创建本地用户、授予权限、创建表并插入数据实例代码

发布: 2025年 7月 5日 38阅读

一. 用户的种类

⏹在 Oracle 多租户架构中（从 Oracle 12c 开始），用户分为

  • 普通用户（Common User）
    • 用户的名称必须以 C## 开头，在CDB中创建
    • 适用于管理员用户，用户管理CDB下的多个PDB
  • 本地用户（Local User）
    • 用户的名称无需以 C## 开头，在PDB中创建
    • 更适合应用开发和租户管理
    • 我们平时开发时用的多是本地用户

⏹数据库刚被安装后，并没有本地用户，我们需要通过system用户登录Oracle之后，创建本地用户。

二. 切换session为PDB

⏹SHOW CON_NAME;：显示当前会话所连接的容器名称

  • 在包含 CDB（容器数据库）和 PDB（可插拔数据库）的环境中，显示会话所连接的容器的名称。
  • 容器可以是根容器（CDB$ROOT）、种子数据库（PDB$SEED）或某个具体的 PDB。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
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>

⏹查看数据库中所有的PDB

  • SELECT PDB_ID, PDB_NAME, STATUS FROM DBA_PDBS;
  • SELECT NAME FROM V$PDBS;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- 方式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

⏹切换当前用户的session为PDB

  • ALTER SESSION SET CONTAINER = XEPDB1;
  • 💥我们只有在PDB的session中创建的才是PDB的用户，如果不切换session的话，创建的是CDB的用户。💥
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- 切换session到根容器
SQL> ALTER SESSION SET CONTAINER = CDB$ROOT;
 
Session altered.
 
-- 切换session到PDB
SQL> ALTER SESSION SET CONTAINER = XEPDB1;
 
Session altered.
 
-- 查看当前容器名称
SQL> SHOW CON_NAME;
 
CON_NAME
------------------------------
XEPDB1
SQL>

三. 创建用户并授予权限

⏹创建用户并指定密码

1
2
3
SQL> CREATE USER db_user IDENTIFIED BY oracle;
 
User created.

⏹为创建的用户赋予权限

1
2
3
4
5
-- 授予用户登录的权限
GRANT CREATE SESSION TO db_user;
 
-- 授予用户创建 表，视图，存储过程，序列对象的权限
GRANT CREATE TABLE, CREATE VIEW, CREATE PROCEDURE, CREATE SEQUENCE TO db_user;

⏹查看创建好的PDB用户

  • COMMON = 'NO'：滤出PDB的本地用户
  • COMMON = 'YES'：滤出CDB的普通用户
1
2
3
4
5
6
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  

四. 创建表空间

  • 默认表空间：用户在不指定表空间的情况下创建的对象（如表）会存储到默认表空间。
  • 临时表空间：用户在执行排序操作（如 ORDER BY 或 GROUP BY）时会使用临时表空间。
  • SIZE 100M AUTOEXTEND ON：表空间大小为100M，当空间不足时，会自动增加
1
2
3
4
5
6
7
-- 创建默认表空间并指定表空间文件
CREATE TABLESPACE DB_STUDY_LOCAL_01
DATAFILE 'C:/app/FengYeHong/product/21c/custom_table_space/local_db_study_01.dbf' SIZE 100M AUTOEXTEND ON;
 
-- 创建临时表空间并指定临时表空间文件
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;

⏹查看创建的表空间

  • 查看数据库中所有的表空间与状态
1
2
3
4
5
6
7
8
9
10
11
12
13
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.
  • 查看临时表空间的路径，字节，状态
1
2
3
4
5
6
7
SQL> set markup csv on
SQL>
SQL> select name,bytes,status from v$tempfile;
 
"NAME","BYTES","STATUS"
"C:APPFENGYEHONGPRODUCT21CORADATAXEXEPDB1TEMP01.DBF",36700160,"ONLINE"
"C:APPFENGYEHONGPRODUCT21CCUSTOM_TABLE_SPACELOCAL_DB_STUDY_TMP_01.DBF",104857600,"ONLINE"
  • 查看默认表空间的路径，字节，状态
1
2
3
4
5
6
7
8
9
10
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:APPFENGYEHONGPRODUCT21CORADATAXEXEPDB1SYSTEM01.DBF","SYSTEM",293601280,"AVAILABLE"
"C:APPFENGYEHONGPRODUCT21CORADATAXEXEPDB1SYSAUX01.DBF","SYSAUX",408944640,"AVAILABLE"
"C:APPFENGYEHONGPRODUCT21CORADATAXEXEPDB1UNDOTBS01.DBF","UNDOTBS1",104857600,"AVAILABLE"
"C:APPFENGYEHONGPRODUCT21CORADATAXEXEPDB1USERS01.DBF","USERS",5242880,"AVAILABLE"
"C:APPFENGYEHONGPRODUCT21CCUSTOM_TABLE_SPACELOCAL_DB_STUDY_01.DBF","DB_STUDY_LOCAL_01",104857600,"AVAILABLE"

⏹如果要删除表空间的话，可以使用下面的命令。

1
2
DROP TABLESPACE DB_STUDY_01 INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE DB_STUDY_TMP_01 INCLUDING CONTENTS AND DATAFILES;

五. 为用户分配默认表空间并指定表空间配额

⏹将用户和表空间关联起来，为用户分配默认表空间和临时表空间。

1
2
3
ALTER USER db_user
DEFAULT TABLESPACE DB_STUDY_LOCAL_01
TEMPORARY TABLESPACE DB_STUDY_TMP_LOCAL_01;

⏹确认用户和表空间的关联

1
2
3
4
5
6
7
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>

⏹用户有了默认表空间之后，并不意味着可以创建表并成功插入数据，还需要向用户分配表空间的配额，指定用户可以使用的存储配额。

  • DBA_TS_QUOTAS 是 Oracle 数据库中的一个数据字典视图，主要用于显示用户在各个表空间上的配额（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;：指定用户有无限的配额。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- 注意，需要在PDB的session中执行
SQL> ALTER SESSION SET CONTAINER = XEPDB1;
 
Session altered.
 
SQL>
-- 在未分配配额之前，查询不到任何数据
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"

六. 通过创建的用户进行登录

⏹我们创建的是本地用户，因此通过sqlplus命令进行登录的时候，必须明确的指出使用的是名称为XEPDB1的PDB

  • 在 Oracle 的多租户架构中，你需要确保连接到正确的 PDB（可插入数据库）而不是 CDB（容器数据库）。
  • 在连接数据库时，服务名称决定了连接的是哪个数据库实例。
  • CDB 是容器数据库，通常是你管理和创建多个 PDB 的地方。
  • 每个 PDB 是一个独立的数据库，可以有自己的用户、数据和表空间等。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
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文件

  • 如果我们在多台服务器上有多个PDB的话，可以在sqlplus客户端安装的机器上，配置tnsnames.ora文件的内容，便于登录
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
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:~$
  • 修改完成之后，就可以通过下面这种方式进行登录了
1
2
3
4
5
6
7
8
9
10
11
12
13
14
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>

七. 创建脚本，简化登录

  • 每次登录oracle数据库，都要输入一长串的命令，很繁琐。可以创建一个脚本简化登录。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
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
# ################################################
# 简介:
# 动态连接 Oracle 数据库
#
# 完整方式连接数据库
# sqlplus db_user/oracle@192.168.118.137/XEPDB1
#
# 用法
# 1. oralce_db_connect
# 2. oralce_db_connect dba
# # ################################################
 
# 默认参数
USERNAME="db_user"
PASSWORD="oracle"
SERVICE="SERVICE_XEPDB1_CLIENT"
MSG="通过普通用户登录..."
 
# 如果传入参数为 "dba"，则使用 system 用户登录
if [ "$1" == "dba" ]; then
  USERNAME="system"
  PASSWORD="oracle"
  SERVICE="SERVICE_XE_CLIENT"
  MSG="通过dba用户登录..."
fi
 
# 构造连接oracle数据库的命令
connect_oracle_db_command="sqlplus ${USERNAME}/${PASSWORD}@${SERVICE}"
 
# 打印连接oracle数据库的命令
echo "${connect_oracle_db_command}"
 
# 打印提示消息
echo -e "e[1;31m$MSGe[0m"
 
# 连接oracle数据库
eval "${connect_oracle_db_command}"
  • 登录效果

八. 查看用户信息

8.1 无需DAB用户

⏹USER_USERS 表

  • 显示当前会话用户的基本信息，例如用户名、创建时间、默认表空间等。
  • 仅适用于当前登录用户，无法查看其它用户的信息。
1
2
3
4
SQL> SELECT USERNAME, USER_ID, ACCOUNT_STATUS FROM USER_USERS;
 
"USERNAME","USER_ID","ACCOUNT_STATUS"
"DB_USER",110,"OPEN"

⏹ALL_USERS表

  • 不需要DBA用户
  • 显示数据库中所有用户的基本信息，但比 DBA_USERS 提供的信息少。
1
2
3
4
SQL> SELECT USER_ID, USERNAME, COMMON FROM ALL_USERS WHERE USERNAME = 'DB_USER';
 
"USER_ID","USERNAME","COMMON"
110,"DB_USER","NO"

8.2 需要DAB用户

💥注意💥
当使用system的DBA用户进行查看的时候，注意切换当前的session为 PDB（可插入数据库）而不是 CDB（容器数据库）。

1
2
-- XEPDB1 为 PDB 的容器名称
 ALTER SESSION SET CONTAINER = XEPDB1;

⏹DBA_USERS表

  • 显示数据库中所有用户的详细信息，包括用户名、账户状态、默认表空间、密码有效期等。
  • 数据库管理员（DBA）管理用户时使用，查看所有用户的账户状态，如是否锁定、密码是否过期等。
1
2
3
4
5
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表

  • 查看用户所有的权限
1
2
3
4
5
6
7
8
9
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

九. 创建表，并插入数据

9.1 查看当前用户的schema

⏹一般来说，用户默认的schema和用户名相同，也可以通过下面这种方式进行查看。

1
2
3
4
SQL> SELECT SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA') AS DEFAULT_SCHEMA FROM DUAL;
 
"DEFAULT_SCHEMA"
"DB_USER"

9.2 插入数据

⏹在创建表的时候，指定表空间，如果不指定的话，将会使用当前用户默认的表空间。

  • TABLESPACE DB_STUDY_LOCAL_01：指定表空间
  • DB_USER.PERSON_TABLE：指定schema
1
2
3
4
5
6
7
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条数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
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

1
2
3
4
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

1
2
3
4
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"

⏹可以看到，数据插入成功后可以被查询到。

总结 

到此这篇关于Oracle数据库创建本地用户、授予权限、创建表并插入数据的文章就介绍到这了,更多相关Oracle创建本地用户、授予权限内容请搜索IT俱乐部以前的文章或继续浏览下面的相关文章希望大家以后多多支持IT俱乐部！

本文收集自网络，不代表IT俱乐部立场，转载请注明出处。https://www.2it.club/database/oracle/15515.html
43赞
标签:
上一篇
下一篇

相关推荐

联系我们

联系我们

在线咨询: QQ交谈

邮箱: 1120393934@qq.com

工作时间：周一至周五，9:00-17:30，节假日休息

关注微信
微信扫一扫关注我们

微信扫一扫关注我们

返回顶部