oracle數據庫表空間超詳細介紹
表空間概述
oracle的表空間屬于oracle中的存儲結構，是一種用于存儲數據庫對象(如：數據文件)的邏輯空間，是oracle中信息存儲的最大邏輯單元，其下還包含有段、區(qū)、數據塊等邏輯數據類型。表空間是在數據庫中開辟的一個空間，用于存放數據庫的對象，一個數據庫可以由多個表空間組成?？梢酝ㄟ^表空間來實現對oracle的調優(yōu)。（oracle數據庫獨特的高級應用）。
表空間的分類
**永久表空間：**存儲數據庫中需要永久化存儲的對象，比如二維表、視圖、存儲過程、索引。
**臨時表空間：**存儲數據庫的中間執(zhí)行過程，如：保存order by數據庫排序，分組時產生的臨時數據。操作完成后存儲的內容會被自動釋放。臨時表空間是通用的，所的用戶都使用temp作為臨時表空間。一般只有temp一個臨時表空間，如果還需要別的臨時表空間時，可以自己創(chuàng)建。
**undo表空間：**保存數據修改前的副本。存儲事務所修改的舊址，即被修改之前的數據。當我們對一張表中的數據進行修改的同時會對修改之前的信息進行保存，為了對數據執(zhí)行回滾、恢復、撤銷的操作。
創(chuàng)建表空間參數詳解
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
說明系統(tǒng)將創(chuàng)建一個回滾表空間。
數據庫管理員可以不必管理回滾段，只有建立了undo表空間，系統(tǒng)就會自動管理回滾段的分配，回收的工作。當然,也可以創(chuàng)建一般的表空間,在上面創(chuàng)建回滾段.不過對于用戶來說,系統(tǒng)管理比自己理要好很多.如果需要自己管理,當沒有為系統(tǒng)指定回滾表空間時,系統(tǒng)將使用system系統(tǒng)回滾段來進行事務管理。
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），包括表，索引，分區(qū)，物化視圖，物化視圖上的索引，分區(qū)。
7、force logging
使用這個子句指出表空間進入強制日志模式。此時，系統(tǒng)將記錄表空間上對象的所有改變，除了臨時段的改變。這個參數高于對象的nologging選項。
注意：設置這個參數數據庫不行open并且出于讀寫模式。而且，在臨時表空間和回滾表空間中不能使用這個選項。
8、default storage_clause
聲明缺省的存儲子句。
9、online|offline
改變表空間的狀態(tài)。online使表空間創(chuàng)建后立即有效.這是缺省值.offline使表空間創(chuàng)建后無效.這個值，可以從dba_tablespace中得到。
10、permanent|temporary
指出表空間的屬性，是永久表空間還是臨時表空間。永久表空間存放的是永久對象 ，臨時表空間存放的是session生命期中存在的臨時對象。這個參數 生成的臨時表空間創(chuàng)建后一直都是字典管理，不能使用extent management local選項。如果要創(chuàng)建本地管理表空間，必須使用create temporary tablespace。
注意，聲明了這個參數后，不能聲明block size
11、extent_management_clause
說明了表空間如何管理范圍。一旦聲明了這個子句，只能通過移植的方式改變這些參數。
如果希望表空間本地管理的話，聲明local選項。本地管理表空間是通過位圖管理的。autoallocate說明表空間自動分配范圍，用戶不能指定范圍的大小。只有9.0以上的版本具有這個功能。uniform說明表空間的范圍的固定大小，缺省是1m。不能將本地管理的數據庫的system表空間設置成字典管理。
如果沒有設置extent_management_clause，oracle會給他設置一個默認值。如果初始化參數compatible小于9.0.0,那么系統(tǒng)創(chuàng)建字典管理表空間。如果大于9.0.0,那么按照如下設置：
如果沒有指定default storage_clause,oracle創(chuàng)建一個自動分配的本地管理表空間。
否則，如果指定了mininum extent,那么oracle判斷mininum extent 、initial、next是否相等,以及pctincrease是否=0.如果滿足以上的條件，oracle創(chuàng)建一個本地管理表空間，extent size是initial.如果不滿足以上條件，那么oracle將創(chuàng)建一個自動分配的本地管理表空間。
如果沒有指定mininum extent。initial、那么oracle判斷next是否相等,以及pctincrease是否=0。如果滿足oracle創(chuàng)建一個本地管理表空間并指定uniform。否則oracle將創(chuàng)建一個自動分配的本地管理表空間。
注意：本地管理表空間只能存儲永久對象。如果你聲明了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;
查詢表空間剩余字節(jié)大小
select tablespace_name, sum(bytes)/1024/1024 as "free space(m)" from dba_free_space where 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_space where tablespace_name = '&tablespace_name';
dba_free_space表介紹
| 字段名稱 | 字段含義 |
|---|---|
| tablespace_name | 表示表空間名字 |
| file_id | 表示表空間所在文件ＩＤ |
| block_id | 表示空閑塊開始的塊ＩＤ |
| bytes | 表示空閑的字節(jié)數 |
| blocks | 表示空閑塊數量 |
| relative_fno | 表示相對文件ＩＤ |
sql> select * from dba_free_space; tablespace_name file_id block_id bytes blocks relative_fno ------------------------------------------------------------ ---------- ---------- ---------- ---------- ------------ system 1 111096 65536 8 1 system 1 112896 8388608 1024 1 sysaux 3 66728 30081024 3672 3 undotbs1 4 472 262144 32 4 undotbs1 4 1024 78643200 9600 4 undotbs1 4 10752 121634816 14848 4 undotbs1 4 25608 13631488 1664 4 undotbs1 4 27280 54394880 6640 4 undotbs1 4 34944 5242880 640 4 undotbs1 4 35712 53477376 6528 4 undotbs1 4 42368 2097152 256 4 undotbs1 4 42752 917504 112 4 undotbs1 4 43912 786432 96 4 undotbs1 4 44016 1179648 144 4 users 7 344 2424832 296 7 zhangtest 13 199456 96206848 11744 13 已選擇 16 行。 sql>
dba_date_files表介紹
| 字段名稱 | 字段含義 |
|---|---|
| file_name | 文件名字 |
| file_id | 文件id，整個數據庫中每個文件的id都是唯一的。 |
| tablespace_name | 文件所屬的表空間，oracle中每個數據文件都和表空間是對應的。 |
| bytes | 文件字節(jié)數量 |
| blocks | 文件的塊數量，和bytes是可以換算的。（bytes/1024/block_size就可計算得到blocks數量） |
| status | 狀態(tài)表示，文件當前是否可用。 |
| relative_fno | 相對文件號。相對文件號只在 表空間唯一，就是說每個表空間都有自己的相對文件號 |
| autoextensible | 是否自動擴展 |
| maxbytes | 如果可以擴展，最大可以到多大？（12c是 3.4360e+10，就是32g） |
| maxblocks | 如果可以擴展，最大可以多少數據塊？ |
| increment_by | 每次增加的塊數量 |
| user_bytes | 文件中實際有用的字節(jié)數。 |
| user_blocks | 文件中實際有用的塊。 |
| online_status | 在線狀態(tài)。 |
查看表空間大小以及使用率
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;
查詢表空間所有數據文件路徑
select tablespace_name, file_id, file_name, bytes/1024/1024 as "bytes(m)" from dba_data_files where tablespace_name = '&tablespace_name';
注：如果是臨時表空間，請查詢dba_temp_files
select tablespace_name, file_id, file_name, bytes/1024/1024 as "space(m)" from dba_temp_files where 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_files where 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';
創(chuàng)建新的臨時表空間
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 all
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 (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';
查看表空間大小以及使用率
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;
查看用戶使用的表空間
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;
創(chuàng)建用戶時指定表空間
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_space from dba_data_files order 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表空間內容請搜索碩編程以前的文章，希望以后支持碩編程！
學習SQL