Oracle表空间、用户详解

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

#### 目录 ####

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

## 新建连接 ##

**工具选择：**  
我们一般会选择一个工具来连接本地的Oracle，而我这里选择的是Navicat 15 —— ![6776e434219b4c2db1899be0c9353abd.png][] 。

--------------------

**开启服务：**  
为了Navicat能连接上Oracle，我们需要本地开启Oracle服务（因为Oracle很占内存，所以一般不会设置自动启动）。

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

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

![c8761d0fc6d6448f88b0396162b64dac.png][]

--------------------

**新建连接:**

*  我们先登录 `system` 用户，因为它拥有普通dba角色权限
    
    > **Oracle用户权限说明：** [http://www.kaotop.com/it/171606.html][http_www.kaotop.com_it_171606.html]

![eaca7b61fc5445bca5ff3761d814493b.png][]

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

![2004344df40547d4ba2df0a35770ebe9.png][]

所以，请新建一个连接。

*  **注意2：** 有些小伙伴就要问了，凭什么非要用 *System* 登录呢，是看不起我们的 *SCOTT* 或者 *SH* 吗？  
    因为它们没有权限呀：
    
        select * from user_sys_privs;
    
    ![a3a3e9dcc8ba4257867efa6a8c74a8be.png][] ![0fd4591c4a7f4ef691cd81501a50ad63.png][]
    
    > **Oracle数据库权限一览表：** [https://blog.csdn.net/oYinHeZhiGuang/article/details/104718403][https_blog.csdn.net_oYinHeZhiGuang_article_details_104718403]
    
    如果直接用 *SCOTT* 创建表空间或者用户，会报错：***ORA-01031: 权限不足***

## 三者关系 ##

先来说明一下表空间、用户和表的关系，其关系如下：  
![f276ff9fc17f4ea5afd7c6129374d4f3.png][]

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

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

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

## 表空间 ##

**表空间与数据库的关系：**  
![44541cf7bc324c02838e367930e0648b.png][]

--------------------

### 创建表空间 ###

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

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

-- 创建表空间 HIPAY
    create tablespace hipay
    -- 指定表空间属下的数据文件，初始化容量为 20MB
    datafile 'D:\Applications\orcl\oradata\orcl\TEST.dbf' size 20m
    -- 设置数据文件为自动扩容，每次扩容为4MB，最大容量为40MB
    autoextend on next 4m maxsize 40m

>  *  **Oracle大小写敏感，且默认会将表空间名、用户名、表名、字段名转换为大写**
>  *  **如果不想名称转换为大写，可以在创建的时候，给名称加一个 `""` 包起来，查询的时候用`''`包起来**
>  *  **指定表空间的数据文件，可以指定已经存在的数据文件，也可以指定一个不存在的，然后Oracle会自动创建一个**
>  *  **在指定表空间的数据文件的时候，可以指定多个数据文件**

**查看表空间：**

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

![4d52b455ec55438b9bbfa64613c1282f.png][]

**创建表空间官方文档：** [https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/CREATE-TABLESPACE.html][https_docs.oracle.com_en_database_oracle_oracle-database_21_sqlrf_CREATE-TABLESPACE.html]

### 修改表空间和数据文件 ###

#### 修改数据文件容量 ####

*  修改数据文件大小：  
    修改数据文件初始大小为 10MB
    
        ALTER database datafile 'D:\Applications\orcl\oradata\orcl\TEST.dbf' resize 10m;
 *  修改数据文件自动扩容：  
    每次扩容10MB，最大容量为500MB，可以把500m换成 **`unlimited`**，即无上限
    
        ALTER database datafile 'D:\Applications\orcl\oradata\orcl\TEST.dbf' autoextend ON NEXT 10m maxsize 500m;

--------------------

#### 新增表空间的数据文件 ####

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

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

--------------------

#### 重命名数据文件 ####

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

--------------------

#### 修改表空间状态 ####

*  离线状态：
    
        ALTER tablespace HIPAY OFFLINE;
 *  在线状态：
    
        ALTER tablespace HIPAY ONLINE;
    
    > **oracle表空间offline的三种方式区别：** [https://blog.51cto.com/jiujian/1033891][https_blog.51cto.com_jiujian_1033891]
 *  只读状态：
    
        ALTER tablespace HIPAY read ONLY;
 *  可读写状态：
    
        ALTER tablespace HIPAY read write;

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

--------------------

#### 修改数据文件状态 ####

*  离线状态：  
    设置数据文件为离线状态需要先设置表空间为离线状态
    
        ALTER tablespace HIPAY OFFLINE;
        ALTER database datafile 'D:\Applications\orcl\oradata\orcl\TEST.dbf' OFFLINE;
 *  在线状态：
    
        ALTER database datafile 'D:\Applications\orcl\oradata\orcl\TEST.dbf' ONLINE;
 *  删除数据文件：
    
        ALTER database datafile 'D:\Applications\orcl\oradata\orcl\TEST.dbf' OFFLINE DROP;
    
    ![cab16faa95a440b4be9bef967c098bc7.png][]

### 删除表空间 ###

*  脱机（表空间为空）
    
        DROP tablespace hipay;
 *  脱机（表空间有数据）
    
        DROP tablespace hipay including contents;
 *  完全删除（表空间与数据文件）
    
        DROP tablespace hipay including contents and datafiles;

### 查询 ###

*  查询数据文件
    
        SELECT
        	* 
        FROM
        	dba_data_files;
 *  查询表空间
    
        SELECT
        	* 
        FROM
        	dba_tablespaces;
        SELECT
        	* 
        FROM
        	dba_free_space;
 *  查看表空间的使用情况
    
        WITH temp_data_files AS ( SELECT ddf.tablespace_name, sum( bytes ) total FROM dba_data_files ddf GROUP BY ddf.tablespace_name ),
        temp_free_space AS ( SELECT dfs.tablespace_name, sum( bytes ) free FROM dba_free_space dfs GROUP BY dfs.tablespace_name ) SELECT
        dt.tablespace_name 表空间名称,
        dt.contents 类型,
        ( tdf.total / 1024 / 1024 ) "总大小(M)",
        ( tfs.free / 1024 / 1024 ) "空闲(M)",
        round( ( tdf.total - tfs.free ) / 1024 / 1024, 2 ) "已使用(M)",
        round( ( tdf.total - tfs.free ) / tdf.total * 100, 2 ) "占比(%)" 
        FROM
        	dba_tablespaces dt,
        	temp_data_files tdf,
        	temp_free_space tfs 
        	
        WHERE
        	tdf.tablespace_name = dt.tablespace_name 
        	AND tfs.tablespace_name = dt.tablespace_name;
    
    ![b13ed3b25fff46718d6e902b6e108a71.png][]

## 用户 ##

### 创建 ###

用户的创建依赖于表空间，所以在创建用户的时候需要指定表空间（默认为 *USERS*）：

-- 创建test用户，密码为123456
    CREATE USER test IDENTIFIED BY 123456
    -- 默认表空间
    DEFAULT tablespace hipay
    -- 临时表空间
    temporary tablespace temp
    -- 表空间额度
    quota unlimited ON hipay;
    
    -- 授权（可以登录）
    GRANT CREATE SESSION TO test;
    
    ## 查询

### 删除 ###

DROP USER test cascade;

### 查询 ###

**查看用户：**

SELECT
    	* 
    FROM
    	dba_users t 
    WHERE
    	t.username = 'TEST';

![ca3cfdaf859f4f768eb3278b6d16a3db.png][]

--------------------

**查看当前用户的的权限：**

SELECT
    	* 
    FROM
    	user_role_privs;
    	
    SELECT
    	* 
    FROM
    	session_privs

--------------------

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

SELECT
    	* 
    FROM
    	user_sys_privs;
    
    SELECT
    	* 
    FROM
    	user_tab_privs;

--------------------

**查看所有用户：**

SELECT
    	* 
    FROM
    	dba_users;
    SELECT
    	* 
    FROM
    	all_users;
    SELECT
    	* 
    FROM
    	user_users;

### 修改 ###

**修改密码：**

ALTER USER test IDENTIFIED BY 234556;

**设置权限：**

GRANT CONNECT,
    RESOURCE TO test;

**撤销权限：**

REVOKE CONNECT,
    RESOURCE FROM test;

> **Oracle数据库权限一览表：** [https://blog.csdn.net/oYinHeZhiGuang/article/details/104718403][https_blog.csdn.net_oYinHeZhiGuang_article_details_104718403]

[6776e434219b4c2db1899be0c9353abd.png]: https://img-blog.csdnimg.cn/6776e434219b4c2db1899be0c9353abd.png
[c8761d0fc6d6448f88b0396162b64dac.png]: https://img-blog.csdnimg.cn/c8761d0fc6d6448f88b0396162b64dac.png
[http_www.kaotop.com_it_171606.html]: http://www.kaotop.com/it/171606.html
[eaca7b61fc5445bca5ff3761d814493b.png]: https://img-blog.csdnimg.cn/eaca7b61fc5445bca5ff3761d814493b.png
[2004344df40547d4ba2df0a35770ebe9.png]: https://img-blog.csdnimg.cn/2004344df40547d4ba2df0a35770ebe9.png
[a3a3e9dcc8ba4257867efa6a8c74a8be.png]: https://img-blog.csdnimg.cn/a3a3e9dcc8ba4257867efa6a8c74a8be.png
[0fd4591c4a7f4ef691cd81501a50ad63.png]: https://img-blog.csdnimg.cn/0fd4591c4a7f4ef691cd81501a50ad63.png
[https_blog.csdn.net_oYinHeZhiGuang_article_details_104718403]: https://blog.csdn.net/oYinHeZhiGuang/article/details/104718403
[f276ff9fc17f4ea5afd7c6129374d4f3.png]: https://img-blog.csdnimg.cn/f276ff9fc17f4ea5afd7c6129374d4f3.png
[a3df218df08241fb906e478f96de2c4e.png]: https://img-blog.csdnimg.cn/a3df218df08241fb906e478f96de2c4e.png
[1e5d6e3801fe4c2ca10029bc51ed540f.png]: https://img-blog.csdnimg.cn/1e5d6e3801fe4c2ca10029bc51ed540f.png
[44541cf7bc324c02838e367930e0648b.png]: https://img-blog.csdnimg.cn/44541cf7bc324c02838e367930e0648b.png
[4d52b455ec55438b9bbfa64613c1282f.png]: https://img-blog.csdnimg.cn/4d52b455ec55438b9bbfa64613c1282f.png
[https_docs.oracle.com_en_database_oracle_oracle-database_21_sqlrf_CREATE-TABLESPACE.html]: https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/CREATE-TABLESPACE.html
[https_blog.51cto.com_jiujian_1033891]: https://blog.51cto.com/jiujian/1033891
[cab16faa95a440b4be9bef967c098bc7.png]: https://img-blog.csdnimg.cn/cab16faa95a440b4be9bef967c098bc7.png
[b13ed3b25fff46718d6e902b6e108a71.png]: https://img-blog.csdnimg.cn/b13ed3b25fff46718d6e902b6e108a71.png
[ca3cfdaf859f4f768eb3278b6d16a3db.png]: https://img-blog.csdnimg.cn/ca3cfdaf859f4f768eb3278b6d16a3db.png