Oracle 表空间的使用与创建

Oracle 表空间的使用与创建（结合创建用户及权限管理）

在Oracle数据库中，表空间是数据库存储的逻辑单位，它用于存储数据库中的数据对象，如表、索引等。Oracle提供了不同类型的表空间（如普通表空间、大表空间、临时表空间等），适用于不同的应用场景。在创建表空间时，我们不仅需要关注存储策略，还需要注意表空间与用户的关系以及如何分配权限。

本文将介绍如何创建Oracle表空间，创建和管理用户及其权限，并结合具体的SQL语句示例，展示如何管理表空间和用户的使用。

1. Oracle 表空间类型

1.1 普通表空间

普通表空间是最常见的表空间类型，它包含一个或多个数据文件，用于存储数据库对象。每个数据文件的大小是固定的，但可以通过自动扩展功能来进行扩展。

创建普通表空间的SQL语句如下：

CREATE TABLESPACE DATACHANGE 
DATAFILE '/u01/app/oracle/oradata/ORCL/datachange01.dbf' 
SIZE 8G AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED;

解释：

• CREATE TABLESPACE：创建一个普通表空间。
• DATACHANGE：指定表空间的名称。
• DATAFILE：指定数据文件的路径及文件名。
• SIZE 8G：设置数据文件的初始大小为8GB。
• AUTOEXTEND ON NEXT 1G：设置数据文件的自动扩展功能，每次扩展1GB。
• MAXSIZE UNLIMITED：不限制数据文件的最大大小，允许其无限扩展。

1.2 大表空间（Bigfile Tablespace）

大表空间是为处理大规模数据量而设计的，它只能包含一个数据文件，并且数据文件的大小可以非常大。

创建大表空间的SQL语句如下：

CREATE BIGFILE TABLESPACE DATACHANGE 
DATAFILE '/u01/app/oracle/oradata/ORCL/datachange01.dbf' 
SIZE 8G AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED;

该SQL语句创建了一个名为DATACHANGE的大表空间，其中包含一个数据文件，初始大小为8GB，并支持自动扩展。

1.3 临时表空间（Temporary Tablespace）

临时表空间用于存储数据库在运行过程中产生的临时数据，如排序操作、哈希连接等。临时表空间通常使用临时文件进行存储，这些文件的内容在数据库重启或会话结束时会被清空。

创建临时表空间的SQL语句如下：

CREATE BIGFILE TEMPORARY TABLESPACE DATACHANGE_TEMP 
TEMPFILE '/u01/app/oracle/oradata/ORCL/datachange_temp.dbf'
SIZE 8G AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED;

该语句创建了一个名为DATACHANGE_TEMP的临时表空间，包含一个初始大小为8GB的临时文件，并支持自动扩展。

2. 创建用户与赋予权限

在Oracle数据库中，创建用户时需要为其指定默认的表空间和临时表空间，并根据需要为用户赋予相应的权限。下面是创建用户并为其分配表空间、权限以及目录访问权限的SQL示例。

2.1 创建用户

CREATE USER datachange IDENTIFIED BY drgs2019 
DEFAULT TABLESPACE datachange 
TEMPORARY TABLESPACE datachange_temp;

这条SQL语句创建了一个名为datachange的用户，并为其指定了默认表空间datachange和临时表空间datachange_temp。用户的初始密码为drgs2019。

2.2 授予权限

接下来，我们需要为datachange用户授予适当的权限，确保其可以正常操作数据库。

GRANT DBA TO datachange;

这条SQL语句将DBA角色授予datachange用户，使其具有管理员权限。DBA角色通常赋予数据库管理员操作所有数据库对象的权限。

2.3 创建并授予目录访问权限

Oracle数据库允许将外部目录映射到数据库中，从而方便用户访问操作系统中的文件。我们可以创建目录对象，并授予datachange用户对这些目录的访问权限。

CREATE DIRECTORY dp_temp AS '/data/sftp/fly/datasource/temp/';
GRANT READ, WRITE ON DIRECTORY dp_temp TO datachange;

CREATE DIRECTORY dp_dir AS '/data/sftp/fly/datasource/dp/';
GRANT READ, WRITE ON DIRECTORY dp_dir TO datachange;

这两条SQL语句分别创建了名为dp_temp和dp_dir的目录对象，并授予datachange用户对这些目录的读取和写入权限。这样，datachange用户就可以通过Oracle访问存储在这些目录中的文件。

2.4 授予连接和资源权限

为确保datachange用户能够正常连接数据库并使用必要的资源，我们需要授予其CONNECT和RESOURCE角色。此外，我们还需要为该用户解除表空间的限制。

GRANT CONNECT, RESOURCE, UNLIMITED TABLESPACE TO datachange;

这条SQL语句授予了datachange用户以下权限：

• CONNECT：允许用户连接到数据库。
• RESOURCE：允许用户创建和管理数据库对象（如表、视图等）。
• UNLIMITED TABLESPACE：允许用户使用任何表空间，并且不限制其表空间的使用。

2.5 修改密码有效期设置

在某些情况下，我们可能希望解除用户密码的有效期限制，使得密码不会过期。可以通过以下SQL语句来实现：

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

该语句修改了默认密码策略，取消了密码过期限制。这样，datachange用户的密码将不再自动过期。

3. 查询表空间使用情况

为了监控和管理表空间的使用情况，数据库管理员可以使用以下查询语句来检查表空间的容量和使用情况：

SELECT *
FROM   (SELECT A.FILE_ID AS 文件ID, A.TABLESPACE_NAME AS 表空间名称,
                A.FILE_NAME 文件名称,
                TRIM(TO_CHAR(A.BYTES / 1024 / 1024, '99999990.99')) AS 总容量,
                TRIM(TO_CHAR(B.BYTES / 1024 / 1024, '99999990.99')) AS 剩余容量,
                TRIM(TO_CHAR(A.BYTES / 1024 / 1024 - B.BYTES / 1024 / 1024,
                         '99999990.99')) AS 使用容量,
                TRIM(TO_CHAR((1 - B.BYTES / A.BYTES) * 100, '99999990.99')) || '%' AS 使用占比,
                '永久' AS 属性
         FROM   (SELECT TABLESPACE_NAME, FILE_NAME, FILE_ID, SUM(BYTES) BYTES
                  FROM   DBA_DATA_FILES
                  GROUP  BY TABLESPACE_NAME, FILE_NAME, FILE_ID) A,
                (SELECT TABLESPACE_NAME, FILE_ID, SUM(BYTES) BYTES
                  FROM   DBA_FREE_SPACE
                  GROUP  BY TABLESPACE_NAME, FILE_ID) B
         WHERE  A.TABLESPACE_NAME = B.TABLESPACE_NAME
         AND    A.FILE_ID = B.FILE_ID
         UNION ALL
         SELECT C.FILE_ID AS 文件ID, C.TABLESPACE_NAME AS 表空间名称,
                C.FILE_NAME AS 文件名称,
                TRIM(TO_CHAR(C.BYTES / 1024 / 1024, '99999990.99')) AS 总容量,
                TRIM(TO_CHAR((C.BYTES - NVL(D.BYTES_USED, 0)) / 1024 / 1024,
                         '99999990.99')) AS 剩余容量,
                TRIM(TO_CHAR(NVL(D.BYTES_USED, 0) / 1024 / 1024, '9999990.99')) AS 使用容量,
                TRIM(TO_CHAR(NVL(D.BYTES_USED, 0) * 100 / C.BYTES, '99999990.99')) || '%' AS 使用占比,
                '临时' AS 属性
         FROM   (SELECT TABLESPACE_NAME, FILE_NAME, FILE_ID, SUM(BYTES) BYTES
                  FROM   DBA_TEMP_FILES
                  GROUP  BY TABLESPACE_NAME, FILE_NAME, FILE_ID) C,
                (SELECT TABLESPACE_NAME, FILE_ID, SUM(BYTES_CACHED) BYTES_USED
                  FROM   V$TEMP_EXTENT_POOL
                  GROUP  BY TABLESPACE_NAME, FILE_ID) D
         WHERE  C.TABLESPACE_NAME = D.TABLESPACE_NAME(+)
         AND    C.FILE_ID = D.FILE_ID(+))
ORDER  BY 属性 ,表空间名称, 文件ID;

这条查询语句从DBA_DATA_FILES、DBA_FREE_SPACE、DBA_TEMP_FILES和V$TEMP_EXTENT_POOL视图中获取表空间的总容量、

剩余容量、使用容量和使用占比等信息，帮助数据库管理员进行存储空间的管理。

4. 总结

在本文中，我们讨论了如何创建和管理Oracle数据库的表空间、如何为用户分配表空间、权限及目录访问权限，以及如何监控表空间的使用情况。通过合理的表空间管理和用户权限分配，数据库管理员可以有效地维护数据库的稳定性和性能。