Oracle 表空間詳解
- 2019 年 10 月 5 日
- 筆記
版權聲明：本文為博主原創文章，遵循 CC 4.0 BY-SA 版權協議，轉載請附上原文出處鏈接和本聲明。
本文鏈接：https://blog.csdn.net/Jmilk/article/details/51599260
目錄
- 目錄
- 表空間概述
- 表空間的分類
- 默認表空間
- 查看默認的永久表空間
- 查看默認的TEMP表空間
- 查看默認的表空間類型
- 邏輯結構到物理結構的映射
- 對錶空間的操作
- 查看錶空間使用情況
- 查看數據庫擁有的表空間
- 查看錶空間中的數據文件
- 查看用戶擁有的表空間
- 創建表空間
- 修改表空間
- 修改用戶的默認和臨時表空間
- 修改表空間的狀態
- 修改表空間的數據文件
- 刪除表空間
- 用戶表空間限額
表空間概述
Oracle的表空間屬於Oracle中的存儲結構，是一種用於存儲數據庫對象(如：數據文件)的邏輯空間，是Oracle中信息存儲的最大邏輯單元，其下還包含有段、區、數據塊等邏輯數據類型。表空間是在數據庫中開闢的一個空間，用於存放數據庫的對象，一個數據庫可以由多個表空間組成。可以通過表空間來實現對Oracle的調優。（Oracle數據庫獨特的高級應用）
表空間的分類
- 永久表空間：存儲數據庫中需要永久化存儲的對象，比如二維表、視圖、存儲過程、索引。
- 臨時表空間：存儲數據庫的中間執行過程，如：保存order by數據庫排序，分組時產生的臨時數據。操作完成後存儲的內容會被自動釋放。臨時表空間是通用的，所的用戶都使用TEMP作為臨時表空間。一般只有tmp一個臨時表空間，如果還需要別的臨時表空間時，可以自己創建。
- UNDO表空間：保存數據修改前的副本。存儲事務所修改的舊址，即被修改之前的數據。當我們對一張表中的數據進行修改的同時會對修改之前的信息進行保存，為了對數據執行回滾、恢復、撤銷的操作。
查看system用戶的默認表空間和臨時表空間：
SQL> select DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users where username='SYSTEM'; DEFAULT_TABLESPACE TEMPORARY_TABLESPACE ------------------------------ ------------------------------ SYSTEM TEM
默認表空間
用戶在登陸後創建數據庫對象時，如果沒有指定表空間，那麼這些數據就會存儲到默認表空間。
查看默認的永久表空間
注意：如果創建用戶時，不指定其永久表空間，則會使用默認的表空間。
SQL> SELECT PROPERTY_NAME, PROPERTY_VALUE 2 FROM DATABASE_PROPERTIES; PROPERTY_NAME PROPERTY_VALUE ------------------------------ ------------------ DEFAULT_TEMP_TABLESPACE TEMP DEFAULT_PERMANENT_TABLESPACE USERS
查看默認的TEMP表空間
系統管理員：
SQL> SELECT PROPERTY_NAME, PROPERTY_VALUE 2 FROM DATABASE_PROPERTIES 3 WHERE PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE' 4 ; PROPERTY_NAME PROPERTY_VALUE ------------------------------ ---------------------------- DEFAULT_TEMP_TABLESPACE TEMP
普通用戶
SELECT USERNAME, TEMPORARY_TABLESPACE FROM DBA_USERS
查看默認的表空間類型
注意：如果不指定表空間類型，就會默認使用DEFAULT_TBS_TYPE參數指定的表空間類型。
SQL> SELECT PROPERTY_NAME, PROPERTY_VALUE 2 FROM DATABASE_PROPERTIES 3 WHERE PROPERTY_NAME='DEFAULT_TBS_TYPE'; PROPERTY_NAME PROPERTY_VALUE ------------------------------ ------------------ DEFAULT_TBS_TYPE SMALLFILE
邏輯結構到物理結構的映射
表空間與數據文件：表空間實際上是由若干個數據文件來構成的，數據文件的位置和大小可以由我們自己來決定。數據被存放在表空間中的數據文件中。
注意：表空間是一種邏輯數據結構，其下最小的數據塊會映射到物理結構的磁盤塊中，一個數據塊由若干個磁盤塊組成。
或者由下圖來理解：表空間邏輯上有若干個段組成，物理上由多個數據文件組成。
對錶空間的操作
表空間的操作方式：
- 字典管理：Oracle中的數據字典可以存儲所有的表空間分配信息，但是如果數據庫中所有的空間分配都放在數據字典中。容易引起字典爭用，而導致性能問題，這是Oracle調優的方向之一。
- 本地管理：表空間分配不放在數據字典，而在每個數據文件頭部的第3到第8個塊的位圖塊，來管理空間分配。
查看錶空間使用情況
SELECT A.TABLESPACE_NAME AS TABLESPACE_NAME, ROUND(A.BYTES/(1024*1024*1024),2) AS "TOTAL(G)" , ROUND(B.BYTES/(1024*1024*1024),2) AS "USED(G)" , ROUND(C.BYTES/(1024*1024*1024),2) AS "FREE(G)" , ROUND((B.BYTES * 100) / A.BYTES,2) AS "% USED" , ROUND((C.BYTES * 100) / A.BYTES,2) AS "% FREE" FROM SYS.SM$TS_AVAIL A, SYS.SM$TS_USED B, SYS.SM$TS_FREE C WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME AND A.TABLESPACE_NAME = C.TABLESPACE_NAME;
計算表空間使用情況(考慮了數據文件自動增長情況)：
SELECT UPPER(F.TABLESPACE_NAME) AS "表空間名稱", ROUND(D.AVAILB_BYTES ,2) AS "表空間大小(G)", ROUND(D.MAX_BYTES,2) AS "最終表空間大小(G)", ROUND((D.AVAILB_BYTES - F.USED_BYTES),2) AS "已使用空間(G)", TO_CHAR(ROUND((D.AVAILB_BYTES - F.USED_BYTES) / D.AVAILB_BYTES * 100, 2), '999.99') AS "使用比", ROUND(F.USED_BYTES, 6) AS "空閑空間(G)", F.MAX_BYTES AS "最大塊(M)" FROM ( SELECT TABLESPACE_NAME, ROUND(SUM(BYTES) / (1024 * 1024 * 1024), 6) USED_BYTES, ROUND(MAX(BYTES) / (1024 * 1024 * 1024), 6) MAX_BYTES FROM SYS.DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F, (SELECT DD.TABLESPACE_NAME, ROUND(SUM(DD.BYTES) / (1024 * 1024 * 1024), 6) AVAILB_BYTES, ROUND(SUM(DECODE(DD.MAXBYTES, 0, DD.BYTES, DD.MAXBYTES))/(1024*1024*1024),6) MAX_BYTES FROM SYS.DBA_DATA_FILES DD GROUP BY DD.TABLESPACE_NAME) D WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME ORDER BY 4 DESC
查看數據庫擁有的表空間
可以通過下面幾個系統視圖來查看錶空間的基本信息：
#1.包含數據庫中所有表空間的描述信息 SELECT * FROM DBA_TABLESPACES; #2.包含當前用戶的表空間的描敘信息 SELECT * FROM USER_TABLESPACES; #3.包含從控制文件中獲取的表空間名稱和編號信息 SELECT * FROM V$TABLESPACE;
查看錶空間中的數據文件
#1.包含數據文件以及所屬的表空間的描述信息(永久表空間/UNDO表空間) SELECT * FROM DBA_DATA_FILES; #2.包含臨時數據文件以及所屬的表空間的描述信息 SELECT * FROM DBA_TEMP_FILES; SELECT * FROM V$TEMPFILE; #3.包含從控制文件中獲取的數據文件的基本信息，包括它所屬的表空間名稱、編號等 SELECT * FROM V$DATAFILE; #4.包含所有臨時數據文件的基本信息 SELECT * FROM V$TEMPFILE;
查看用戶擁有的表空間
不同的用戶會使用不同的表空間：
- 系統管理員用戶使用
dba_tablespaces數據字典來查看。
SQL> desc dba_tablespaces #Oracle管理員級別的數據字典中記錄了管理員級別用戶所使用的表空間名稱、默認表空間和臨時表空間。 TABLESPACE_NAME Name Null? Type ----------------------------------------- -------- ---------------------------- TABLESPACE_NAME NOT NULL VARCHAR2(30) BLOCK_SIZE NOT NULL NUMBER INITIAL_EXTENT NUMBER NEXT_EXTENT NUMBER MIN_EXTENTS NOT NULL NUMBER MAX_EXTENTS NUMBER PCT_INCREASE NUMBER MIN_EXTLEN NUMBER STATUS VARCHAR2(9) CONTENTS VARCHAR2(9) LOGGING VARCHAR2(9) FORCE_LOGGING VARCHAR2(3) EXTENT_MANAGEMENT VARCHAR2(10) ALLOCATION_TYPE VARCHAR2(9) PLUGGED_IN VARCHAR2(3) SEGMENT_SPACE_MANAGEMENT VARCHAR2(6) DEF_TAB_COMPRESSION VARCHAR2(8) RETENTION VARCHAR2(11) BIGFILE VARCHAR2(3) SQL> select tablespace_name from dba_tablespaces; #查看管理員用戶使用的表空間名稱列表 TABLESPACE_NAME ------------------------------ SYSTEM #系統表空間，是永久系統表空間，用於存儲SYS用戶的表、視圖、存儲過程對象。 UNDOTBS1 #存儲撤銷信息的undo表空間 SYSAUX #作為EXAMPLE的輔助表空間 TEMP #臨時表空間，用戶存儲SQL語句處理的表示索引信息 USERS #永久表空間，存儲數據庫用戶創建的數據庫對象 EXAMPLE #安裝Oracle數據庫示例的表空間
- 普通用戶使用
user_tablespaces數據字典來查看。
SQL> desc user_tablespaces Name Null? Type ----------------------------------------- -------- ---------------------------- TABLESPACE_NAME NOT NULL VARCHAR2(30) BLOCK_SIZE NOT NULL NUMBER INITIAL_EXTENT NUMBER NEXT_EXTENT NUMBER MIN_EXTENTS NOT NULL NUMBER MAX_EXTENTS NUMBER PCT_INCREASE NUMBER MIN_EXTLEN NUMBER STATUS VARCHAR2(9) CONTENTS VARCHAR2(9) LOGGING VARCHAR2(9) FORCE_LOGGING VARCHAR2(3) EXTENT_MANAGEMENT VARCHAR2(10) ALLOCATION_TYPE VARCHAR2(9) SEGMENT_SPACE_MANAGEMENT VARCHAR2(6) DEF_TAB_COMPRESSION VARCHAR2(8) RETENTION VARCHAR2(11) BIGFILE VARCHAR2(3) SQL> connect scott/tiger #連接到SCOTT用戶 Connected. SQL> show user; USER is "SCOTT" SQL> select tablespace_name from dba_tablespaces; select tablespace_name from dba_tablespaces * ERROR at line 1: ORA-00942: table or view does not exist #SCOTT用戶沒有權限去查看系統表空間dba_tablespaces SQL> select tablespace_name from user_tablespaces; #但是SCOTT用戶可以查看用戶表空間user_tablespaces TABLESPACE_NAME ------------------------------ SYSTEM UNDOTBS1 SYSAUX TEMP USERS EXAMPLE 6 rows selected.
創建表空間
基本語法：
Create [TEMPORARY] TABLESPACE tablespace_name TEMPFILE|DATAFILE 'fileName.dbf' size xx #TEMPFILE|DATAFILE 指的是我們存儲的數據文件的類型和名字。 #size 表示數據文件的大小 #[TEMPORARY] 如果我們創建的是臨時表空間，那麼需要在create關鍵字後加上TEMPORARY的關鍵字，而且使用TEMPFILE關鍵字。
注意：如果沒有指定數據文件存放的路徑，默認會將數據文件存放到Oracle的安裝目錄下
Example：
SQL> show user; USER is "SYSTEM" SQL> create tablespace test1_tablespace datafile 'test1file.dbf' size 10m; Tablespace created. SQL> create temporary tablespace temptest1_tablespace tempfile 'tempfile.dbf' size 10m; Tablespace created.
查看錶空間數據文件的存放路徑：表空間數據文件都存放在永久性表空間中
SQL> desc dba_data_files; #數據字典dba_data_file用於存放數據文件的屬性。 Name Null? Type ----------------------------------------- -------- ---------------------------- FILE_NAME VARCHAR2(513) FILE_ID NUMBER TABLESPACE_NAME VARCHAR2(30) BYTES NUMBER BLOCKS NUMBER STATUS VARCHAR2(9) RELATIVE_FNO NUMBER AUTOEXTENSIBLE VARCHAR2(3) MAXBYTES NUMBER MAXBLOCKS NUMBER INCREMENT_BY NUMBER USER_BYTES NUMBER USER_BLOCKS NUMBER ONLINE_STATUS VARCHAR2(7) SQL> select file_name from dba_data_files where tablespace_name='TEST1_TABLESPACE'; FILE_NAME -------------------------------------------------------------------------------- /u01/oracle/dbs/test1file.dbf
修改表空間
修改用戶的默認和臨時表空間
為了方便以後數據的備份和恢復，僅僅將有價值的數據做備份可以提高效率和節省空間。 基本語法：
ALTER USER username defalut|temporary TABLESPACE tablespace_name;
注意：普通用戶沒有修改默認表空間的權限，但是可以通過授權來實現普通用戶也能夠修改默認表空間。
Example：
SQL> show user; USER is "SYSTEM" SQL> select DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users where username='SYSTEM'; DEFAULT_TABLESPACE TEMPORARY_TABLESPACE ------------------------------ ------------------------------ USERS TEMP
修改表空間的狀態
- 設置表空間的聯機或脫機狀態：表空間的默認狀態是聯機狀態，如果表空間是脫機狀態，那麼我們就不能夠使用這個表空間了。
基本語法：
ALTER TABLESPACE tablespace_name ONLINE|OFFLINE;
Example：
SQL> alter tablespace test1_tablespace offline; Tablespace altered. SQL> select STATUS from dba_tablespaces where tablespace_name='TEST1_TABLESPACE'; // tablespace_name需要使用大些字母 STATUS --------- OFFLINE
- 設置表空間的只讀|可讀寫狀態：表空間默認為可讀寫狀態
注意：如果表空間狀態中擁有可read，那麼表空間就必須是聯機狀態的。 基本語法：
ALTER TABLESPACE tablespace_name [read only][read write];
Example：
SQL> alter tablespace test1_tablespace read only; Tablespace altered. SQL> select STATUS from dba_tablespaces where tablespace_name='TEST1_TABLESPACE'; STATUS --------- READ ONLY
修改表空間的數據文件
- 增加表空間中的數據文件 基本語法：
ALTER TABLESPACE tablespace_name ADD DATAFILE'xx.dbf' SIZE xx;
Example：
SQL> alter tablespace test1_tablespace add datafile 'test2_file.dbf' size 10m; Tablespace altered. SQL> select file_name from dba_data_fileS where tablespace_name='TEST1_TABLESPACE'; FILE_NAME -------------------------------------------------------------------------------- /u01/oracle/dbs/test1file.dbf /u01/oracle/dbs/test2_file.dbf
注意：如果發現某個表空間存儲空間不足時，可以為表空間添加新的數據文件，擴展表空間大小
SQL> ALTER TABLESPACE TBS_TR_IND ADD DATAFILE '/oradata/rTBS_TR_IND_002.dbf' SIZE 32G AUTOEXTEND OFF SQL> ALTER TABLESPACE TBS_EDS_DAT 2 ADD DATAFILE 'G:datafileTBS_EDS_DAT01.DBF' 3 SIZE 100M 4 AUTOEXTEND ON 5 NEXT 10M 6 MAXSIZE 20480M; SQL> ALTER TABLESPACE temp01 2 ADD TMPFILE 'D:ORACLEDATAtemp01_02.dbf' SIZE 10M REUSE;
注意：在添加新的數據文件時，如果同名的操作系統已經存在，ALTER TABLESPACE語句將失敗。如果要覆蓋同名的操作系統文件時，則必須在後面顯示的指定REUSE子句。
- 調整數據文件的大小
ALTER DATABASE DATAFILE '/database/oracle/oradata/gsp/tbs_dm_data_002.dbf' RESIZE 500M;
- 刪除數據文件
注意：不能夠刪除表空間中的第一個數據文件，如果將第一個數據文件刪除的話，相當於刪除了整個表空間。 基本語法：
ALTER TABLESPACE tablespace_name DROP DATAFILE'filename.dbf';
Example：
SQL> alter tablespace test1_tablespace drop datafile 'test2_file.dbf'; Tablespace altered. SQL> select file_name from dba_data_fileS where tablespace_name='TEST1_TABLESPACE'; FILE_NAME -------------------------------------------------------------------------------- /u01/oracle/dbs/test1file.dbf
刪除表空間
基本語法：
DROP TABLESPAC tablespace_name [INCLUDING CONTENTS];
注意：如果你希望在刪除表空間的同時將表空間中的數據文件一同刪除時，需要加上[INCLUDING CONTENTS] Example：
SQL> drop tablespace test1_tablespace including contents; Tablespace dropped. SQL> select file_name from dba_data_fileS where tablespace_name='TEST1_TABLESPACE'; no rows selected
用戶表空間限額
表空間存儲限制是用戶在某一個表空間中可以使用的存儲空間總數。 在創建或修改用戶時，可以由參數quota指出。若用戶在向表空間存儲數據時，超出了此限額，則會產生錯誤。 錯誤信息：ORA-01536:space quota exceeded for tablespace tablespacename..』。 可以通過查詢字典dba_ts_quotas查看錶空間限額信息。
查看用戶的表空間配額
#查看所有用戶表空間的配額情況 SELECT * FROM DBA_TS_QUOTAS #查看當前用戶表空間的配額情況 SELECT * FROM USER_TS_QUOTAS SQL> DESC DBA_TS_QUOTAS Name Type Nullable Default Comments --------------- ------------ -------- ------- ------------------------------------------------ TABLESPACE_NAME VARCHAR2(30) Tablespace name USERNAME VARCHAR2(30) User with resource rights on the tablespace BYTES NUMBER Y Number of bytes charged to the user MAX_BYTES NUMBER Y User's quota in bytes. NULL if no limit BLOCKS NUMBER Y Number of ORACLE blocks charged to the user MAX_BLOCKS NUMBER Y User's quota in ORACLE blocks. NULL if no limit DROPPED VARCHAR2(3) Y Whether the tablespace has been dropped
注意：若MAX_BYTES=-1表示沒有配額限制
