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

一. 用户的种类

⏹在 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。

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
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的用户。💥

-- 切换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>

三. 创建用户并授予权限

⏹创建用户并指定密码

sql> create user db_user identified by oracle;

user created.

⏹为创建的用户赋予权限

-- 授予用户登录的权限
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的普通用户

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，当空间不足时，会自动增加

-- 创建默认表空间并指定表空间文件
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;

⏹查看创建的表空间

查看数据库中所有的表空间与状态

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.

查看临时表空间的路径，字节，状态

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"

查看默认表空间的路径，字节，状态

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;

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

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

alter user db_user 
default tablespace db_study_local_01 
temporary tablespace db_study_tmp_local_01;

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

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;：指定用户有无限的配额。

-- 注意，需要在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 是一个独立的数据库，可以有自己的用户、数据和表空间等。

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文件的内容，便于登录

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>

七. 创建脚本，简化登录

每次登录oracle数据库，都要输入一长串的命令，很繁琐。可以创建一个脚本简化登录。

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$msg\e[0m"

# 连接oracle数据库
eval "${connect_oracle_db_command}"

登录效果

八. 查看用户信息

8.1 无需dab用户

⏹user_users 表

显示当前会话用户的基本信息，例如用户名、创建时间、默认表空间等。
仅适用于当前登录用户，无法查看其它用户的信息。

sql> select username, user_id, account_status from user_users;

"username","user_id","account_status"
"db_user",110,"open"

⏹all_users表

不需要dba用户
显示数据库中所有用户的基本信息，但比 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用户

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

-- xepdb1 为 pdb 的容器名称
 alter session set container = xepdb1;

⏹dba_users表

显示数据库中所有用户的详细信息，包括用户名、账户状态、默认表空间、密码有效期等。
数据库管理员（dba）管理用户时使用，查看所有用户的账户状态，如是否锁定、密码是否过期等。

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表

查看用户所有的权限

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和用户名相同，也可以通过下面这种方式进行查看。

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

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条数据

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"

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