Oracle 表空间管理

表空间配额 Quota

Oracle 可以使用配额（Quota）这个概念对用户所使用的表空间进行限制。如果不同用户共同使用同一个表空间，可以使用这种方法可以有效地限制每个用户对该表空间的使用。

• DBA_TS_QUOTAS: 查看所有用户的表空间配额限制情况
  • 只有使用 alter user user_name quota on tablespace_name; 命令显示的调整过用户的空间配额，该视图中才会有记录，否则该视图没有记录
  • 如果该视图没有信息，我们可以通过 user_ts_quotas 视图来了解用户的表空间限额信息

• USER_TS_QUOTAS: 查看当前用户对表空间的配额限制情况;
  • 可能有时我们查询该视图，用户在某个表空间的配额为0，但是我们依然可以使用该表空间，那么说明用户被赋予了UNLIMITED TABLESPACE。
  • select * from user_sys_privs; 查看用户是否具有UNLIMITED TABLESPACE权限

注意: resource 角色被赋予用户时，用户就自动被赋予了 UNLIMITED TABLESPACE 权限（虽然 resource 中并不包含该权限）如果我们把 resource 角色从用户收回，那么 UNLIMITED TABLESPACE 权限也会自动收回。

查看表空间配额限制情况

1. 查看当前用户相关表空间的配额使用情况

   SQL> select * from user_ts_quotas;
   
   TABLESPACE_NAME                     BYTES  MAX_BYTES     BLOCKS MAX_BLOCKS DRO
   ------------------------------ ---------- ---------- ---------- ---------- ---
   QDMWMS                         4599316480          0     561440          0 NO

2. 查看所有用户的表空间配额限制情况

   SQL> select * from dba_ts_quotas;
   
   TABLESPACE_NAME                USERNAME                            BYTES  MAX_BYTES     BLOCKS MAX_BLOCKS DRO
   ------------------------------ ------------------------------ ---------- ---------- ---------- ---------- ---
   SYSAUX                         APPQOSSYS                               0         -1          0         -1 NO
   SYSAUX                         OLAPSYS                           7667712         -1        936         -1 NO
   SYSAUX                         SYSMAN                           64159744         -1       7832         -1 NO
   SYSAUX                         FLOWS_FILES                             0         -1          0         -1 NO

   MAX_BYTES 内容为 “-1” 即表示配额为无限大。

配额管理

1. 建用户时指定配额

   create user java identified by oracle default tablespace users temporary tablespace temp quota 100m on users quota 100m on system;

   注意 temp 表空间不支持表空间配额

2. 更改用户表空间配额

   grant unlimited tablespace to java;     --不对用户作配额限制
   alter user java quota unlimited on users;   --对指定表空间不做限额
   alter user java quota 1000m on users;       --指定用户在某表空间限制
   revoke unlimited tablespace from java;      --收回用户的unlimited tablespace权限
   alter user java quota 0 on users;           --针对 alter … quota unlimited …;

   如果用户已经被授予了 unlimited tablespace 权限的话，那么 alter user java quota 0 on users; 限制是不起作用的

3. 表空间已删除，关于表空间配额信息还存在。表空间被删除以后但是关于该表空间的配额信息在视图中依然能够查到，这个是正常的，因为表空间被 drop 后,ts$ 和 tsq$ 中的信息并没有被清理，如果你重建一个同名的 tablespace，并限制 quota 为 0，tsq$中的记录就被删除了.
   从 10gR2 开始，可以使用下面的语法一并将quota信息删除,默认是keep quota.:

   drop tablespace mytsincluding contents and datafiles drop quota;

4. 表空间大小不足问题的解决：使用 ALTER TABLESPACE tablespace_name ADD DATAFILE filename SIZE size_of_file 命令向指定的数据增加表空间，根据具体的情况可以增加一个或多个表空间。

表空间配额不够时的现象

1. 如果用户对某个表空间使用配额不足时，会触发 ORA-01536 错误；具体的错误信息如下：

   ORA-01536: space quota exceeded for tablespace 'USERS'

表空间信息查看

1. 查看用户默认的表空间

   SQL> SELECT USERNAME,DEFAULT_TABLESPACE FROM DBA_USERS WHERE USERNAME='QDMWMS_PRE';
   
   USERNAME                       DEFAULT_TABLESPACE
   ------------------------------ ------------------------------
   QDMWMS_PRE                     QDMWMS

2. 查看表空间对应的数据文件

   select tablespace_name,file_id,file_name from dba_data_files order by file_id;

3. 查看表空间文件路径、大小、已使用、使用率

   SELECT
     B.FILE_NAME,  /* 物理文件名 */
     B.TABLESPACE_NAME,    /* 表空间名称 */
     B.BYTES/1024/1024,    /* 表空间大小（M) */
     (B.BYTES-SUM(NVL(A.BYTES,0)))/1024/1024,  /* 已使用（M) */
     SUBSTR((B.BYTES-SUM(NVL(A.BYTES,0)))/(B.BYTES)*100,1,5)
   FROM DBA_FREE_SPACE A,DBA_DATA_FILES B
   WHERE A.FILE_ID=B.FILE_ID
   GROUP BY B.TABLESPACE_NAME, B.FILE_NAME, B.BYTES
   ORDER BY B.TABLESPACE_NAME;

4. 查询表空间使用率

   SELECT total.tablespace_name,
       Round(total.MB, 2)           AS Total_MB,
       Round(total.MB - free.MB, 2) AS Used_MB,
       Round(( 1 - free.MB / total.MB ) * 100, 2)
       || '%'                       AS Used_Pct
   FROM   (SELECT tablespace_name,
               Sum(bytes) / 1024 / 1024 AS MB
           FROM   dba_free_space
           GROUP  BY tablespace_name) free,
       (SELECT tablespace_name,
               Sum(bytes) / 1024 / 1024 AS MB
           FROM   dba_data_files
           GROUP  BY tablespace_name) total
   WHERE  free.tablespace_name = total.tablespace_name;

5. 查看表空间是否开启自动扩展

   SELECT 
   FILE_NAME,
   TABLESPACE_NAME,
   AUTOEXTENSIBLE,
   STATUS,
   MAXBYTES,
   USER_BYTES,
   INCREMENT_BY
   FROM DBA_DATA_FILES;

6. 创建表空间并开启自动扩展

   create bigfile tablespace ahwms datafile '/u01/app/oracle/oradata/ahwms/ahwms.dbf' size 1G autoextend on next 1G maxsize unlimited;

7. 修改表空间大小

   ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/ahwms/ahwms.dbf' RESIZE 200M;

8. 删除表空间和数据文件

   DROP TABLESPACE XXXX INCLUDING CONTENTS AND DATAFILES;

9. 查看临时表空间信息

   SELECT * FROM DBA_TEMP_FILES