Oracle數(shù)據(jù)庫(kù)表空間超詳細(xì)介紹

更新時(shí)間：2023年05月11日 09:17:37 作者：俊杰梓

Oracle數(shù)據(jù)庫(kù)的數(shù)據(jù)存放在表空間中,表空間是一個(gè)邏輯的概念,它是由數(shù)據(jù)文件組成,表空間大小由數(shù)據(jù)文件的數(shù)量和大小決定,下面這篇文章主要給大家介紹了關(guān)于Oracle數(shù)據(jù)庫(kù)表空間的相關(guān)資料,需要的朋友可以參考下

表空間概述

Oracle的表空間屬于Oracle中的存儲(chǔ)結(jié)構(gòu)，是一種用于存儲(chǔ)數(shù)據(jù)庫(kù)對(duì)象(如：數(shù)據(jù)文件)的邏輯空間，是Oracle中信息存儲(chǔ)的最大邏輯單元，其下還包含有段、區(qū)、數(shù)據(jù)塊等邏輯數(shù)據(jù)類(lèi)型。表空間是在數(shù)據(jù)庫(kù)中開(kāi)辟的一個(gè)空間，用于存放數(shù)據(jù)庫(kù)的對(duì)象，一個(gè)數(shù)據(jù)庫(kù)可以由多個(gè)表空間組成。可以通過(guò)表空間來(lái)實(shí)現(xiàn)對(duì)Oracle的調(diào)優(yōu)。（Oracle數(shù)據(jù)庫(kù)獨(dú)特的高級(jí)應(yīng)用）。

表空間的分類(lèi)

**永久表空間：**存儲(chǔ)數(shù)據(jù)庫(kù)中需要永久化存儲(chǔ)的對(duì)象，比如二維表、視圖、存儲(chǔ)過(guò)程、索引。

**臨時(shí)表空間：**存儲(chǔ)數(shù)據(jù)庫(kù)的中間執(zhí)行過(guò)程，如：保存order by數(shù)據(jù)庫(kù)排序，分組時(shí)產(chǎn)生的臨時(shí)數(shù)據(jù)。操作完成后存儲(chǔ)的內(nèi)容會(huì)被自動(dòng)釋放。臨時(shí)表空間是通用的，所的用戶都使用TEMP作為臨時(shí)表空間。一般只有temp一個(gè)臨時(shí)表空間，如果還需要?jiǎng)e的臨時(shí)表空間時(shí)，可以自己創(chuàng)建。

**UNDO表空間：**保存數(shù)據(jù)修改前的副本。存儲(chǔ)事務(wù)所修改的舊址，即被修改之前的數(shù)據(jù)。當(dāng)我們對(duì)一張表中的數(shù)據(jù)進(jìn)行修改的同時(shí)會(huì)對(duì)修改之前的信息進(jìn)行保存，為了對(duì)數(shù)據(jù)執(zhí)行回滾、恢復(fù)、撤銷(xiāo)的操作。

創(chuàng)建表空間參數(shù)詳解

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

說(shuō)明系統(tǒng)將創(chuàng)建一個(gè)回滾表空間。

數(shù)據(jù)庫(kù)管理員可以不必管理回滾段，只有建立了undo表空間，系統(tǒng)就會(huì)自動(dòng)管理回滾段的分配，回收的工作。當(dāng)然,也可以創(chuàng)建一般的表空間,在上面創(chuàng)建回滾段.不過(guò)對(duì)于用戶來(lái)說(shuō),系統(tǒng)管理比自己理要好很多.如果需要自己管理,當(dāng)沒(méi)有為系統(tǒng)指定回滾表空間時(shí),系統(tǒng)將使用system系統(tǒng)回滾段來(lái)進(jìn)行事務(wù)管理。

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是數(shù)據(jù)文件的全路徑名，size是文件的大小,REUSE表示文件是否被重用，

AUTOEXTEND表明是否自動(dòng)擴(kuò)展. OFF | ON 表示自動(dòng)擴(kuò)展是否被關(guān)閉.NEXT 表示數(shù)據(jù)文件滿了以后,擴(kuò)展的大小，

maxsize_clause表示數(shù)據(jù)文件的最大大小.形如MAXSIZE { UNLIMITED | integer [ K | M ] }.UNLIMITED 表示無(wú)限的表空間.integer是數(shù)據(jù)文件的最大大小，

DATAFILE ‘D:"oracle"oradata"IMAGEDATA01.dbf’ SIZE 2000M，

‘D:"oracle"oradata"IMAGEDATA02.dbf’ SIZE 2000M

4、MININUM EXTENT integer [k|m]

指出在表空間中范圍的最小值。這個(gè)參數(shù)可以減小空間碎片，保證在表空間的范圍是這個(gè)數(shù)值的整數(shù)倍。

5、BLOCKSIZE integer [k]

這個(gè)參數(shù)可以設(shè)定一個(gè)不標(biāo)準(zhǔn)的塊的大小。如果要設(shè)置這個(gè)參數(shù)，必須設(shè)置db_block_size,至少一個(gè)db_nk_block_size,并且聲明的integer的值必須等于db_nk_block_size。

注意：在臨時(shí)表空間不能設(shè)置這個(gè)參數(shù)。

6、logging clause

這個(gè)子句聲明這個(gè)表空間上所有的用戶對(duì)象的日志屬性（缺省是logging），包括表，索引，分區(qū)，物化視圖，物化視圖上的索引，分區(qū)。

7、FORCE LOGGING

使用這個(gè)子句指出表空間進(jìn)入強(qiáng)制日志模式。此時(shí)，系統(tǒng)將記錄表空間上對(duì)象的所有改變，除了臨時(shí)段的改變。這個(gè)參數(shù)高于對(duì)象的nologging選項(xiàng)。

注意：設(shè)置這個(gè)參數(shù)數(shù)據(jù)庫(kù)不行open并且出于讀寫(xiě)模式。而且，在臨時(shí)表空間和回滾表空間中不能使用這個(gè)選項(xiàng)。

8、DEFAULT storage_clause

聲明缺省的存儲(chǔ)子句。

9、online|offline

改變表空間的狀態(tài)。online使表空間創(chuàng)建后立即有效.這是缺省值.offline使表空間創(chuàng)建后無(wú)效.這個(gè)值，可以從dba_tablespace中得到。

10、PERMANENT|TEMPORARY

指出表空間的屬性，是永久表空間還是臨時(shí)表空間。永久表空間存放的是永久對(duì)象，臨時(shí)表空間存放的是session生命期中存在的臨時(shí)對(duì)象。這個(gè)參數(shù) 生成的臨時(shí)表空間創(chuàng)建后一直都是字典管理，不能使用extent management local選項(xiàng)。如果要?jiǎng)?chuàng)建本地管理表空間，必須使用create temporary tablespace。

注意，聲明了這個(gè)參數(shù)后，不能聲明block size

11、extent_management_clause

說(shuō)明了表空間如何管理范圍。一旦聲明了這個(gè)子句，只能通過(guò)移植的方式改變這些參數(shù)。

如果希望表空間本地管理的話，聲明local選項(xiàng)。本地管理表空間是通過(guò)位圖管理的。autoallocate說(shuō)明表空間自動(dòng)分配范圍，用戶不能指定范圍的大小。只有9.0以上的版本具有這個(gè)功能。uniform說(shuō)明表空間的范圍的固定大小，缺省是1m。不能將本地管理的數(shù)據(jù)庫(kù)的system表空間設(shè)置成字典管理。

如果沒(méi)有設(shè)置extent_management_clause，oracle會(huì)給他設(shè)置一個(gè)默認(rèn)值。如果初始化參數(shù)compatible小于9.0.0,那么系統(tǒng)創(chuàng)建字典管理表空間。如果大于9.0.0,那么按照如下設(shè)置：

如果沒(méi)有指定default storage_clause,oracle創(chuàng)建一個(gè)自動(dòng)分配的本地管理表空間。

否則，如果指定了mininum extent,那么oracle判斷mininum extent 、initial、next是否相等,以及pctincrease是否=0.如果滿足以上的條件，oracle創(chuàng)建一個(gè)本地管理表空間，extent size是initial.如果不滿足以上條件，那么oracle將創(chuàng)建一個(gè)自動(dòng)分配的本地管理表空間。

如果沒(méi)有指定mininum extent。initial、那么oracle判斷next是否相等,以及pctincrease是否=0。如果滿足oracle創(chuàng)建一個(gè)本地管理表空間并指定uniform。否則oracle將創(chuàng)建一個(gè)自動(dòng)分配的本地管理表空間。

注意：本地管理表空間只能存儲(chǔ)永久對(duì)象。如果你聲明了local,將不能聲明default storage_clause,mininum extent、temporary。

EXTENT MANAGEMENT LOCAL

12、segment_management_clause

SEGMENT SPACE MANAGEMENT AUTO

實(shí)例：

CREATE TABLESPACE YNCCIP --表空間名
DATAFILE '/home/u02/oradata/orcl/YNCCIP01.dbf' --表空間對(duì)應(yīng)的數(shù)據(jù)文件
SIZE 100M --數(shù)據(jù)文件大小
AUTOEXTEND ON NEXT 10M --數(shù)據(jù)文件不夠用自動(dòng)擴(kuò)展，每次擴(kuò)展大小
MAXSIZE 1000M --數(shù)據(jù)文件最大文件大小
LOGGING --啟動(dòng)重做日志
PERMANENT --指定表空間為永久性的表空間
EXTENT MANAGEMENT LOCAL AUTOALLOCATE --指定新建表空間為本地管理方式的表空間
BLOCKSIZE 16K --塊大小
SEGMENT SPACE MANAGEMENT auto --指定本地管理表空間中段的存儲(chǔ)管理方式，AUTO自動(dòng)，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;

注：如果是臨時(shí)表空間，請(qǐng)查詢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	表示空閑塊開(kāi)始的塊ＩＤ
BYTES	表示空閑的字節(jié)數(shù)
BLOCKS	表示空閑塊數(shù)量
RELATIVE_FNO	表示相對(duì)文件ＩＤ

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，整個(gè)數(shù)據(jù)庫(kù)中每個(gè)文件的ID都是唯一的。
TABLESPACE_NAME	文件所屬的表空間，ORACLE中每個(gè)數(shù)據(jù)文件都和表空間是對(duì)應(yīng)的。
BYTES	文件字節(jié)數(shù)量
BLOCKS	文件的塊數(shù)量，和BYTES是可以換算的。（BYTES/1024/BLOCK_SIZE就可計(jì)算得到BLOCKS數(shù)量）
STATUS	狀態(tài)表示，文件當(dāng)前是否可用。
RELATIVE_FNO	相對(duì)文件號(hào)。相對(duì)文件號(hào)只在表空間唯一，就是說(shuō)每個(gè)表空間都有自己的相對(duì)文件號(hào)
AUTOEXTENSIBLE	是否自動(dòng)擴(kuò)展
MAXBYTES	如果可以擴(kuò)展，最大可以到多大？（12C是 3.4360E+10，就是32G）
MAXBLOCKS	如果可以擴(kuò)展，最大可以多少數(shù)據(jù)塊？
INCREMENT_BY	每次增加的塊數(shù)量
USER_BYTES	文件中實(shí)際有用的字節(jié)數(shù)。
USER_BLOCKS	文件中實(shí)際有用的塊。
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;

查詢表空間所有數(shù)據(jù)文件路徑

select tablespace_name, file_id, file_name, bytes/1024/1024 as "bytes(m)"
from dba_data_files
where tablespace_name = '&tablespace_name';

注：如果是臨時(shí)表空間，請(qǐng)查詢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';

為空間不足的表空間增加數(shù)據(jù)文件

alter tablespace &tablespace_name add datafile '&datafile_name' size 2G;

注：如果要為臨時(shí)表空間擴(kuò)容，使用下面的語(yǔ)句

alter tablespace &tablespace_name add tempfile '&datafile_name' size 2G;

查看臨時(shí)表空間的大小和數(shù)據(jù)文件路徑

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;

重建并修改默認(rèn)臨時(shí)表空間辦法

查詢當(dāng)前數(shù)據(jù)庫(kù)默認(rèn)臨時(shí)表空間名

select * from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';

創(chuàng)建新的臨時(shí)表空間

create temporary tablespace temp02 tempfile 'E:\oracle\oradata\lims\TEMP02.DBF' size 1024M autoextend on;

修改默認(rèn)表空間為剛剛建立的臨時(shí)表空間

alter database default temporary tablespace temp02;

查看用戶所用臨時(shí)表空間的情況

select USERNAME,TEMPORARY_TABLESPACE FROM DBA_USERS;

刪除原來(lái)的臨時(shí)表空間

drop tablespace temp including contents and datafiles;

查看所有表空間名確認(rèn)臨時(shí)表空間是否已刪除

select tablespace_name from dba_tablespaces;

查表空間使用率情況（含臨時(shí)表空間）

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;

修改數(shù)據(jù)文件大小

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)建用戶時(shí)指定表空間

create user username identified by passworddefault tablespace user_datatemporary tablespace user_temp;

修改用戶對(duì)應(yīng)的表空間

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;

方法一：增加數(shù)據(jù)文件

ALTER TABLESPACE game ADD DATAFILE '/oracle/oradata/db/GAME02.dbf' SIZE 1000M;

方法二：手動(dòng)增加數(shù)據(jù)文件尺寸

ALTER DATABASE DATAFILE '/oracle/oradata/db/GAME.dbf' RESIZE 4000M;

方法三：設(shè)定數(shù)據(jù)文件自動(dòng)擴(kuò)展

ALTER DATABASE DATAFILE '/oracle/oradata/db/GAME.dbf AUTOEXTEND ON NEXT 100M MAXSIZE 10000M;

總結(jié)

到此這篇關(guān)于Oracle數(shù)據(jù)庫(kù)表空間超詳細(xì)介紹的文章就介紹到這了,更多相關(guān)Oracle表空間內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章:

Oracle數(shù)據(jù)庫(kù)表空間超詳細(xì)介紹

目錄

表空間概述

表空間的分類(lèi)

創(chuàng)建表空間參數(shù)詳解

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

修改表空間名稱

查詢表空間剩余字節(jié)大小

dba_free_space表介紹

dba_date_files表介紹

查看表空間大小以及使用率

查詢表空間所有數(shù)據(jù)文件路徑

為空間不足的表空間增加數(shù)據(jù)文件

查看臨時(shí)表空間的大小 和 數(shù)據(jù)文件路徑

重建并修改默認(rèn)臨時(shí)表空間辦法

查表空間使用率情況（含臨時(shí)表空間）

查看表空間大小以及使用率

查看用戶使用的表空間

修改數(shù)據(jù)文件大小

查看用戶所在的表空間

創(chuàng)建用戶時(shí)指定表空間

修改用戶對(duì)應(yīng)的表空間

拓展表空間

總結(jié)

相關(guān)文章

最新評(píng)論

大家感興趣的內(nèi)容

最近更新的內(nèi)容

常用在線小工具

1、undo

2、tablespace

3、datafile datefile_spec1

5、BLOCKSIZE integer [k]

7、FORCE LOGGING

8、DEFAULT storage_clause

9、online|offline

10、PERMANENT|TEMPORARY

12、segment_management_clause

查看臨時(shí)表空間的大小和數(shù)據(jù)文件路徑