Oracle數據庫表空間超詳細介紹
目錄
- 表空間概述
- 表空間的分類
- 創建表空間參數詳解
- 1、undo
- 2、tablespace
- 3、datafile datefile_spec1
- 4、MININUM EXTENT integer [k|m]
- 5、BLOCKSIZE integer [k]
- 6、logging clause
- 7、FORCE LOGGING
- 8、DEFAULT storage_clause
- 9、online|offline
- 10、PERMANENT|TEMPORARY
- 11、extent_management_clause
- 12、segment_management_clause
- 修改表空間名稱
- 查詢表空間剩余字節大小
- dba_free_space表介紹
- dba_date_files表介紹
- 查看表空間大小以及使用率
- 查詢表空間所有數據文件路徑
- 為空間不足的表空間增加數據文件
- 查看臨時表空間的大小 和 數據文件路徑
- 重建并修改默認臨時表空間辦法
- 查表空間使用率情況（含臨時表空間）
- 查看表空間大小以及使用率
- 查看用戶使用的表空間
- 修改數據文件大小
- 查看用戶所在的表空間
- 創建用戶時指定表空間
- 修改用戶對應的表空間
- 拓展表空間
- 總結
表空間概述
Oracle的表空間屬于Oracle中的存儲結構，是一種用于存儲數據庫對象(如：數據文件)的邏輯空間，是Oracle中信息存儲的最大邏輯單元，其下還包含有段、區、數據塊等邏輯數據類型。表空間是在數據庫中開辟的一個空間，用于存放數據庫的對象，一個數據庫可以由多個表空間組成?？梢酝ㄟ^表空間來實現對Oracle的調優。（Oracle數據庫獨特的高級應用）。
表空間的分類
**永久表空間：**存儲數據庫中需要永久化存儲的對象，比如二維表、視圖、存儲過程、索引。
**臨時表空間：**存儲數據庫的中間執行過程，如：保存order by數據庫排序，分組時產生的臨時數據。操作完成后存儲的內容會被自動釋放。臨時表空間是通用的，所的用戶都使用TEMP作為臨時表空間。一般只有temp一個臨時表空間，如果還需要別的臨時表空間時，可以自己創建。
**UNDO表空間：**保存數據修改前的副本。存儲事務所修改的舊址，即被修改之前的數據。當我們對一張表中的數據進行修改的同時會對修改之前的信息進行保存，為了對數據執行回滾、恢復、撤銷的操作。
創建表空間參數詳解
CREATE [UNDO] TABLESPACE tablespace_name[DATAFILE datefile_spec1 [,datefile_spec2] ......[{MININUM EXTENT integer [k|m] |BLOCKSIZE integer [k]|logging clause|FORCE LOGGING|DEFAULT {data_segment_compression} storage_clause|[online|offline]|[PERMANENT|TEMPORARY] |extent_manager_clause|segment_manager_clause}]1、undo
說明系統將創建一個回滾表空間。
數據庫管理員可以不必管理回滾段，只有建立了undo表空間，系統就會自動管理回滾段的分配，回收的工作。當然,也可以創建一般的表空間,在上面創建回滾段.不過對于用戶來說,系統管理比自己理要好很多.如果需要自己管理,當沒有為系統指定回滾表空間時,系統將使用system系統回滾段來進行事務管理。
2、tablespace
指出表空間的名稱
3、datafile datefile_spec1
指出表空間包含什么空間文件。datefile_spec1 是形如 [‘filename’] [SIZE integer [ K | M ]] [REUSE] [autoextend_clause]，
[autoextend_clause]是形如： AUTOEXTEND { OFF | ON [ NEXT integer [ K | M ] ] [maxsize_clause] }，
其中filename是數據文件的全路徑名，size是文件的大小,REUSE表示文件是否被重用，
AUTOEXTEND表明是否自動擴展. OFF | ON 表示自動擴展是否被關閉.NEXT 表示數據文件滿了以后,擴展的大小，
maxsize_clause表示數據文件的最大大小.形如MAXSIZE { UNLIMITED | integer [ K | M ] }.UNLIMITED 表示無限的表空間.integer是數據文件的最大大小，
DATAFILE ‘D:"oracle"oradata"IMAGEDATA01.dbf’ SIZE 2000M，
‘D:"oracle"oradata"IMAGEDATA02.dbf’ SIZE 2000M
4、MININUM EXTENT integer [k|m]
指出在表空間中范圍的最小值。這個參數可以減小空間碎片，保證在表空間的范圍是這個數值的整數倍。
5、BLOCKSIZE integer [k]
這個參數可以設定一個不標準的塊的大小。如果要設置這個參數，必須設置db_block_size,至少一個db_nk_block_size,并且聲明的integer的值必須等于db_nk_block_size。
注意：在臨時表空間不能設置這個參數。
6、logging clause
這個子句聲明這個表空間上所有的用戶對象的日志屬性（缺省是logging），包括表，索引，分區，物化視圖，物化視圖上的索引，分區。
7、FORCE LOGGING
使用這個子句指出表空間進入強制日志模式。此時，系統將記錄表空間上對象的所有改變，除了臨時段的改變。這個參數高于對象的nologging選項。
注意：設置這個參數數據庫不行open并且出于讀寫模式。而且，在臨時表空間和回滾表空間中不能使用這個選項。
8、DEFAULT storage_clause
聲明缺省的存儲子句。
9、online|offline
改變表空間的狀態。online使表空間創建后立即有效.這是缺省值.offline使表空間創建后無效.這個值，可以從dba_tablespace中得到。
10、PERMANENT|TEMPORARY
指出表空間的屬性，是永久表空間還是臨時表空間。永久表空間存放的是永久對象 ，臨時表空間存放的是session生命期中存在的臨時對象。這個參數 生成的臨時表空間創建后一直都是字典管理，不能使用extent management local選項。如果要創建本地管理表空間，必須使用create temporary tablespace。
注意，聲明了這個參數后，不能聲明block size
11、extent_management_clause
說明了表空間如何管理范圍。一旦聲明了這個子句，只能通過移植的方式改變這些參數。
如果希望表空間本地管理的話，聲明local選項。本地管理表空間是通過位圖管理的。autoallocate說明表空間自動分配范圍，用戶不能指定范圍的大小。只有9.0以上的版本具有這個功能。uniform說明表空間的范圍的固定大小，缺省是1m。不能將本地管理的數據庫的system表空間設置成字典管理。
如果沒有設置extent_management_clause，oracle會給他設置一個默認值。如果初始化參數compatible小于9.0.0,那么系統創建字典管理表空間。如果大于9.0.0,那么按照如下設置：
如果沒有指定default storage_clause,oracle創建一個自動分配的本地管理表空間。
否則，如果指定了mininum extent,那么oracle判斷mininum extent 、initial、next是否相等,以及pctincrease是否=0.如果滿足以上的條件，oracle創建一個本地管理表空間，extent size是initial.如果不滿足以上條件，那么oracle將創建一個自動分配的本地管理表空間。
如果沒有指定mininum extent。initial、那么oracle判斷next是否相等,以及pctincrease是否=0。如果滿足oracle創建一個本地管理表空間并指定uniform。否則oracle將創建一個自動分配的本地管理表空間。
注意：本地管理表空間只能存儲永久對象。如果你聲明了local,將不能聲明default storage_clause,mininum extent、temporary。
EXTENT MANAGEMENT LOCAL
12、segment_management_clause
SEGMENT SPACE MANAGEMENT AUTO
實例：
CREATE TABLESPACE YNCCIP --表空間名DATAFILE "/home/u02/oradata/orcl/YNCCIP01.dbf" --表空間對應的數據文件SIZE 100M --數據文件大小AUTOEXTEND ON NEXT 10M --數據文件不夠用自動擴展，每次擴展大小MAXSIZE 1000M --數據文件最大文件大小LOGGING --啟動重做日志PERMANENT --指定表空間為永久性的表空間EXTENT MANAGEMENT LOCAL AUTOALLOCATE --指定新建表空間為本地管理方式的表空間BLOCKSIZE 16K --塊大小SEGMENT SPACE MANAGEMENT auto --指定本地管理表空間中段的存儲管理方式，AUTO自動，MANUAL手工。
create tablespace NNC_INDEX01 datafile "D:\oracle\NNC_INDEX01.DBF" size 500M autoextend on next 50M extent management local uniform size 256k;
修改表空間名稱
alter tablespace TEST rename to TEST1;
查詢表空間剩余字節大小
select tablespace_name, sum(bytes)/1024/1024 as "free space(m)"from dba_free_spacewhere tablespace_name = "&tablespace_name"group by tablespace_name;
注：如果是臨時表空間，請查詢DBA_TEMP_FREE_SPACE
select tablespace_name, free_space/1024/1024 as "free space(m)"from dba_temp_free_spacewhere tablespace_name = "&tablespace_name";
dba_free_space表介紹
SQL> select * from dba_free_space;TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO------------------------------------------------------------ ---------- ---------- ---------- ---------- ------------SYSTEM 1 111096 65536 8 1SYSTEM 1 112896 8388608 1024 1SYSAUX 3 66728 30081024 3672 3UNDOTBS1 4472 262144 32 4UNDOTBS1 4 1024 78643200 9600 4UNDOTBS1 4 10752 121634816 14848 4UNDOTBS1 4 25608 13631488 1664 4UNDOTBS1 4 27280 54394880 6640 4UNDOTBS1 4 34944 5242880640 4UNDOTBS1 4 35712 53477376 6528 4UNDOTBS1 4 42368 2097152256 4UNDOTBS1 4 42752 917504112 4UNDOTBS1 4 43912 786432 96 4UNDOTBS1 4 44016 1179648144 4USERS 7344 2424832296 7ZHANGTEST13 199456 96206848 11744 13已選擇 16 行。SQL>
dba_date_files表介紹
查看表空間大小以及使用率
SELECTB.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 BWHERE A.FILE_ID=B.FILE_IDGROUP BY B.TABLESPACE_NAME,B.FILE_NAME,B.BYTESORDER BY B.TABLESPACE_NAME;
查詢表空間所有數據文件路徑
select tablespace_name, file_id, file_name, bytes/1024/1024 as "bytes(m)"from dba_data_fileswhere tablespace_name = "&tablespace_name";
注：如果是臨時表空間，請查詢DBA_TEMP_FILES
select tablespace_name, file_id, file_name, bytes/1024/1024 as "space(m)"from dba_temp_fileswhere tablespace_name = "&tablespace_name";
為空間不足的表空間增加數據文件
alter tablespace &tablespace_name add datafile "&datafile_name" size 2G;
注：如果要為臨時表空間擴容，使用下面的語句
alter tablespace &tablespace_name add tempfile "&datafile_name" size 2G;
查看臨時表空間的大小 和 數據文件路徑
select tablespace_name, file_id, file_name, bytes/1024/1024 as "space(m)"from dba_temp_fileswhere tablespace_name = "temp";
或者
select name, bytes/1024/1024 as "大小(M)" from v$tempfile order by bytes;
重建并修改默認臨時表空間辦法
查詢當前數據庫默認臨時表空間名
select * from database_properties where property_name="DEFAULT_TEMP_TABLESPACE";
創建新的臨時表空間
create temporary tablespace temp02 tempfile "E:\oracle\oradata\lims\TEMP02.DBF" size 1024M autoextend on;
修改默認表空間為剛剛建立的臨時表空間
alter database default temporary tablespace temp02;
查看用戶所用臨時表空間的情況
select USERNAME,TEMPORARY_TABLESPACE FROM DBA_USERS;
刪除原來的臨時表空間
drop tablespace temp including contents and datafiles;
查看所有表空間名確認臨時表空間是否已刪除
select tablespace_name from dba_tablespaces;
查表空間使用率情況（含臨時表空間）
select d.tablespace_name "name", d.status "status", to_char (nvl (a.bytes / 1024 / 1024, 0), "99,999,990.90") "size (m)", to_char (nvl (a.bytes - nvl (f.bytes, 0), 0) / 1024 / 1024,"99999999.99") use, to_char (nvl ((a.bytes - nvl (f.bytes, 0)) / a.bytes * 100, 0),"990.00") "used %" from sys.dba_tablespaces d, (select tablespace_name, sum (bytes) bytes from dba_data_files group by tablespace_name) a, (select tablespace_name, sum (bytes) bytes from dba_free_space group by tablespace_name) f where d.tablespace_name = a.tablespace_name(+) and d.tablespace_name = f.tablespace_name(+) and not (d.extent_management like "local" and d.contents like "temporary")union allselect d.tablespace_name "name", d.status "status", to_char (nvl (a.bytes / 1024 / 1024, 0), "99,999,990.90") "size (m)", to_char (nvl (t.bytes, 0) / 1024 / 1024, "99999999.99") use, to_char (nvl (t.bytes / a.bytes * 100, 0), "990.00") "used %" from sys.dba_tablespaces d, (select tablespace_name, sum (bytes) bytes from dba_temp_files group by tablespace_name) a, (select tablespace_name, sum (bytes_cached) bytes from v$temp_extent_pool group by tablespace_name) t where d.tablespace_name = a.tablespace_name(+) and d.tablespace_name = t.tablespace_name(+) and d.extent_management like "local" and d.contents like "temporary";
查看表空間大小以及使用率
selectb.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 bwhere a.file_id=b.file_idgroup by b.tablespace_name,b.file_name,b.bytesorder by b.tablespace_name;
查看用戶使用的表空間
select username,default_tablespace from dba_users;
修改數據文件大小
alter database datafile "D:\APP\ADMINISTRATOR\ORADATA\ORCL\NNC_DATA01.DBF" resize 10240M;
查看用戶所在的表空間
select username,default_tablespace from dba_users order by username;
創建用戶時指定表空間
create user username identified by passworddefault tablespace user_datatemporary tablespace user_temp;
修改用戶對應的表空間
alter user c##zhang default tablespace ZHANGTEST01;
拓展表空間
首先查看表空間的名字和所屬文件
select tablespace_name, file_id, file_name,round(bytes/(1024*1024),0) total_spacefrom dba_data_filesorder by tablespace_name;
方法一：增加數據文件
ALTER TABLESPACE game ADD DATAFILE "/oracle/oradata/db/GAME02.dbf" SIZE 1000M;
方法二：手動增加數據文件尺寸
ALTER DATABASE DATAFILE "/oracle/oradata/db/GAME.dbf" RESIZE 4000M;
方法三：設定數據文件自動擴展
ALTER DATABASE DATAFILE "/oracle/oradata/db/GAME.dbf AUTOEXTEND ON NEXT 100M MAXSIZE 10000M;
總結
到此這篇關于Oracle數據庫表空間超詳細介紹的文章就介紹到這了,更多相關Oracle表空間內容請搜索以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持！
網公網安備