Oracle表空间、用户详解

朱雀 2023-09-25 13:16 220阅读 0赞

目录

  • 新建连接
  • 三者关系
  • 表空间
    • 创建表空间
    • 修改表空间和数据文件
      • 修改数据文件容量
      • 新增表空间的数据文件
      • 重命名数据文件
      • 修改表空间状态
      • 修改数据文件状态
    • 删除表空间
    • 查询
  • 用户
    • 创建
    • 删除
    • 查询
    • 修改

新建连接

工具选择:
我们一般会选择一个工具来连接本地的Oracle,而我这里选择的是Navicat 15 —— 6776e434219b4c2db1899be0c9353abd.png


开启服务:
为了Navicat能连接上Oracle,我们需要本地开启Oracle服务(因为Oracle很占内存,所以一般不会设置自动启动)。

wins键——服务——开启服务

只要把这几个启动了,就可以连接了(我也不清楚哪几个是必须的,如果还是连接不了,就把所有的Oracle服务都启动就没问题了)。

c8761d0fc6d6448f88b0396162b64dac.png


新建连接:

  • 我们先登录 system 用户,因为它拥有普通dba角色权限

    Oracle用户权限说明: http://www.kaotop.com/it/171606.html

eaca7b61fc5445bca5ff3761d814493b.png

  • 注意1: 如果你之前已经建立了一个Oracle连接,然后你想直接修改连接的用户来登录其他用户,那么其会报错:

2004344df40547d4ba2df0a35770ebe9.png

  1. 所以,请新建一个连接。
  • 注意2: 有些小伙伴就要问了,凭什么非要用 System 登录呢,是看不起我们的 SCOTT 或者 SH 吗?
    因为它们没有权限呀:

    1. select * from user_sys_privs;

    a3a3e9dcc8ba4257867efa6a8c74a8be.png 0fd4591c4a7f4ef691cd81501a50ad63.png

    Oracle数据库权限一览表: https://blog.csdn.net/oYinHeZhiGuang/article/details/104718403

    如果直接用 SCOTT 创建表空间或者用户,会报错:ORA-01031: 权限不足

三者关系

先来说明一下表空间、用户和表的关系,其关系如下:
f276ff9fc17f4ea5afd7c6129374d4f3.png

一个表空间下面可以有多个用户,而一个用户下面可以有多张表。

  • 表空间:数据库数据的物理存储空间
    那些后缀名为 .DBF 的文件就是表空间
    a3df218df08241fb906e478f96de2c4e.png
  • 用户:可以通过用户操作数据库(前提是该用户有相应权限)
    创建用户必须为其指定表空间,如果没有显性指定表空间,则默认指定为 USERS 表空间
  • 表:数据记录的集合
    1e5d6e3801fe4c2ca10029bc51ed540f.png

通过对这三者的关系分析,可知道创建流程:创建表空间 → 创建用户 → 创建表。

表空间

表空间与数据库的关系:
44541cf7bc324c02838e367930e0648b.png


创建表空间

创建表空间: 创建一个初始大小为20MB,每次扩容4M,最大容量为40MB的表空间。

注意: 在创建表空间的时候,一定要指定数据文件路径 。

  1. -- 创建表空间 HIPAY
  2. create tablespace hipay
  3. -- 指定表空间属下的数据文件,初始化容量为 20MB
  4. datafile 'D:\Applications\orcl\oradata\orcl\TEST.dbf' size 20m
  5. -- 设置数据文件为自动扩容,每次扩容为4MB,最大容量为40MB
  6. autoextend on next 4m maxsize 40m
  • Oracle大小写敏感,且默认会将表空间名、用户名、表名、字段名转换为大写
  • 如果不想名称转换为大写,可以在创建的时候,给名称加一个 "" 包起来,查询的时候用''包起来
  • 指定表空间的数据文件,可以指定已经存在的数据文件,也可以指定一个不存在的,然后Oracle会自动创建一个
  • 在指定表空间的数据文件的时候,可以指定多个数据文件

查看表空间:

  1. SELECT
  2. t.tablespace_name,-- 表空间
  3. t.file_name,-- 文件名
  4. t.autoextensible,-- 是否自增
  5. t.bytes / 1024 / 1024 "SIZE(M)",-- 初始值
  6. t.increment_by * 8 / 1024 "NEXT(M)",-- 步长 1blok = 8KB
  7. t.maxbytes / 1024 / 1024 "MAXSIZE(M)" -- 最大值
  8. FROM
  9. dba_data_files t
  10. WHERE
  11. t.tablespace_name = 'HIPAY';

4d52b455ec55438b9bbfa64613c1282f.png

创建表空间官方文档: https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/CREATE-TABLESPACE.html

修改表空间和数据文件

修改数据文件容量

  • 修改数据文件大小:
    修改数据文件初始大小为 10MB

    1. ALTER database datafile 'D:\Applications\orcl\oradata\orcl\TEST.dbf' resize 10m;
  • 修改数据文件自动扩容:
    每次扩容10MB,最大容量为500MB,可以把500m换成 unlimited,即无上限

    1. ALTER database datafile 'D:\Applications\orcl\oradata\orcl\TEST.dbf' autoextend ON NEXT 10m maxsize 500m;

新增表空间的数据文件

HIPAY 新增一个容量为 20MB 的数据文件。

  1. ALTER tablespace "HIPAY" ADD datafile 'D:\Applications\orcl\oradata\orcl\TEST01.dbf' SIZE 20M;

重命名数据文件

  1. ALTER tablespace HIPAY RENAME datafile 'D:\Applications\orcl\oradata\orcl\TEST01.dbf' TO 'D:\Applications\orcl\oradata\orcl\TEST02.dbf';

修改表空间状态

  • 离线状态:

    1. ALTER tablespace HIPAY OFFLINE;
  • 在线状态:

    1. ALTER tablespace HIPAY ONLINE;

    oracle表空间offline的三种方式区别: https://blog.51cto.com/jiujian/1033891

  • 只读状态:

    1. ALTER tablespace HIPAY read ONLY;
  • 可读写状态:

    1. ALTER tablespace HIPAY read write;

注意:离线状态不可设置只读,只有在只读状态才可修改为可读写,因为默认可读写。


修改数据文件状态

  • 离线状态:
    设置数据文件为离线状态需要先设置表空间为离线状态

    1. ALTER tablespace HIPAY OFFLINE;
    2. ALTER database datafile 'D:\Applications\orcl\oradata\orcl\TEST.dbf' OFFLINE;
  • 在线状态:

    1. ALTER database datafile 'D:\Applications\orcl\oradata\orcl\TEST.dbf' ONLINE;
  • 删除数据文件:

    1. ALTER database datafile 'D:\Applications\orcl\oradata\orcl\TEST.dbf' OFFLINE DROP;

    cab16faa95a440b4be9bef967c098bc7.png

删除表空间

  • 脱机(表空间为空)

    1. DROP tablespace hipay;
  • 脱机(表空间有数据)

    1. DROP tablespace hipay including contents;
  • 完全删除(表空间与数据文件)

    1. DROP tablespace hipay including contents and datafiles;

查询

  • 查询数据文件

    1. SELECT
    2. *
    3. FROM
    4. dba_data_files;
  • 查询表空间

    1. SELECT
    2. *
    3. FROM
    4. dba_tablespaces;
    5. SELECT
    6. *
    7. FROM
    8. dba_free_space;
  • 查看表空间的使用情况

    1. WITH temp_data_files AS ( SELECT ddf.tablespace_name, sum( bytes ) total FROM dba_data_files ddf GROUP BY ddf.tablespace_name ),
    2. temp_free_space AS ( SELECT dfs.tablespace_name, sum( bytes ) free FROM dba_free_space dfs GROUP BY dfs.tablespace_name ) SELECT
    3. dt.tablespace_name 表空间名称,
    4. dt.contents 类型,
    5. ( tdf.total / 1024 / 1024 ) "总大小(M)",
    6. ( tfs.free / 1024 / 1024 ) "空闲(M)",
    7. round( ( tdf.total - tfs.free ) / 1024 / 1024, 2 ) "已使用(M)",
    8. round( ( tdf.total - tfs.free ) / tdf.total * 100, 2 ) "占比(%)"
    9. FROM
    10. dba_tablespaces dt,
    11. temp_data_files tdf,
    12. temp_free_space tfs
    13. WHERE
    14. tdf.tablespace_name = dt.tablespace_name
    15. AND tfs.tablespace_name = dt.tablespace_name;

    b13ed3b25fff46718d6e902b6e108a71.png

用户

创建

用户的创建依赖于表空间,所以在创建用户的时候需要指定表空间(默认为 USERS):

  1. -- 创建test用户,密码为123456
  2. CREATE USER test IDENTIFIED BY 123456
  3. -- 默认表空间
  4. DEFAULT tablespace hipay
  5. -- 临时表空间
  6. temporary tablespace temp
  7. -- 表空间额度
  8. quota unlimited ON hipay;
  9. -- 授权(可以登录)
  10. GRANT CREATE SESSION TO test;
  11. ## 查询

删除

  1. DROP USER test cascade;

查询

查看用户:

  1. SELECT
  2. *
  3. FROM
  4. dba_users t
  5. WHERE
  6. t.username = 'TEST';

ca3cfdaf859f4f768eb3278b6d16a3db.png


查看当前用户的的权限:

  1. SELECT
  2. *
  3. FROM
  4. user_role_privs;
  5. SELECT
  6. *
  7. FROM
  8. session_privs

查看当前用户的系统权限和表级权限:

  1. SELECT
  2. *
  3. FROM
  4. user_sys_privs;
  5. SELECT
  6. *
  7. FROM
  8. user_tab_privs;

查看所有用户:

  1. SELECT
  2. *
  3. FROM
  4. dba_users;
  5. SELECT
  6. *
  7. FROM
  8. all_users;
  9. SELECT
  10. *
  11. FROM
  12. user_users;

修改

修改密码:

  1. ALTER USER test IDENTIFIED BY 234556;

设置权限:

  1. GRANT CONNECT,
  2. RESOURCE TO test;

撤销权限:

  1. REVOKE CONNECT,
  2. RESOURCE FROM test;

Oracle数据库权限一览表: https://blog.csdn.net/oYinHeZhiGuang/article/details/104718403

发表评论

表情:
评论列表 (有 0 条评论,220人围观)

还没有评论,来说两句吧...

相关阅读

    相关 Oracle用户空间管理

    表空间的设置与管理 概述 Oracle数据库的数据文件是存放数据库中所有数据的容器,分为永久性数据文件和临时数据文件两种。 表空间是Oracle数据库的