Oracle數(shù)據(jù)庫表空間超詳細介紹
表空間概述
Oracle的表空間屬于Oracle中的存儲結(jié)構(gòu)，是一種用于存儲數(shù)據(jù)庫對象(如：數(shù)據(jù)文件)的邏輯空間，是Oracle中信息存儲的最大邏輯單元，其下還包含有段、區(qū)、數(shù)據(jù)塊等邏輯數(shù)據(jù)類型。表空間是在數(shù)據(jù)庫中開辟的一個空間，用于存放數(shù)據(jù)庫的對象，一個數(shù)據(jù)庫可以由多個表空間組成?？梢酝ㄟ^表空間來實現(xiàn)對Oracle的調(diào)優(yōu)。（Oracle數(shù)據(jù)庫獨特的高級應(yīng)用）。
表空間的分類
**永久表空間：**存儲數(shù)據(jù)庫中需要永久化存儲的對象，比如二維表、視圖、存儲過程、索引。
**臨時表空間：**存儲數(shù)據(jù)庫的中間執(zhí)行過程，如：保存order by數(shù)據(jù)庫排序，分組時產(chǎn)生的臨時數(shù)據(jù)。操作完成后存儲的內(nèi)容會被自動釋放。臨時表空間是通用的，所的用戶都使用TEMP作為臨時表空間。一般只有temp一個臨時表空間，如果還需要別的臨時表空間時，可以自己創(chuàng)建。
**UNDO表空間：**保存數(shù)據(jù)修改前的副本。存儲事務(wù)所修改的舊址，即被修改之前的數(shù)據(jù)。當我們對一張表中的數(shù)據(jù)進行修改的同時會對修改之前的信息進行保存，為了對數(shù)據(jù)執(zhí)行回滾、恢復(fù)、撤銷的操作。
創(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
說明系統(tǒng)將創(chuàng)建一個回滾表空間。
數(shù)據(jù)庫管理員可以不必管理回滾段，只有建立了undo表空間，系統(tǒng)就會自動管理回滾段的分配，回收的工作。當然,也可以創(chuàng)建一般的表空間,在上面創(chuàng)建回滾段.不過對于用戶來說,系統(tǒng)管理比自己理要好很多.如果需要自己管理,當沒有為系統(tǒng)指定回滾表空間時,系統(tǒng)將使用system系統(tǒng)回滾段來進行事務(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表明是否自動擴展. OFF | ON 表示自動擴展是否被關(guān)閉.NEXT 表示數(shù)據(jù)文件滿了以后,擴展的大小，
maxsize_clause表示數(shù)據(jù)文件的最大大小.形如MAXSIZE { UNLIMITED | integer [ K | M ] }.UNLIMITED 表示無限的表空間.integer是數(shù)據(jù)文件的最大大小，
DATAFILE ‘D:"oracle"oradata"IMAGEDATA01.dbf’ SIZE 2000M，
‘D:"oracle"oradata"IMAGEDATA02.dbf’ SIZE 2000M
4、MININUM EXTENT integer [k|m]
指出在表空間中范圍的最小值。這個參數(shù)可以減小空間碎片，保證在表空間的范圍是這個數(shù)值的整數(shù)倍。
5、BLOCKSIZE integer [k]
這個參數(shù)可以設(shè)定一個不標準的塊的大小。如果要設(shè)置這個參數(shù)，必須設(shè)置db_block_size,至少一個db_nk_block_size,并且聲明的integer的值必須等于db_nk_block_size。
注意：在臨時表空間不能設(shè)置這個參數(shù)。
6、logging clause
這個子句聲明這個表空間上所有的用戶對象的日志屬性（缺省是logging），包括表，索引，分區(qū)，物化視圖，物化視圖上的索引，分區(qū)。
7、FORCE LOGGING
使用這個子句指出表空間進入強制日志模式。此時，系統(tǒng)將記錄表空間上對象的所有改變，除了臨時段的改變。這個參數(shù)高于對象的nologging選項。
注意：設(shè)置這個參數(shù)數(shù)據(jù)庫不行open并且出于讀寫模式。而且，在臨時表空間和回滾表空間中不能使用這個選項。
8、DEFAULT storage_clause
聲明缺省的存儲子句。
9、online|offline
改變表空間的狀態(tài)。online使表空間創(chuàng)建后立即有效.這是缺省值.offline使表空間創(chuàng)建后無效.這個值，可以從dba_tablespace中得到。
10、PERMANENT|TEMPORARY
指出表空間的屬性，是永久表空間還是臨時表空間。永久表空間存放的是永久對象 ，臨時表空間存放的是session生命期中存在的臨時對象。這個參數(shù) 生成的臨時表空間創(chuàng)建后一直都是字典管理，不能使用extent management local選項。如果要創(chuàng)建本地管理表空間，必須使用create temporary tablespace。
注意，聲明了這個參數(shù)后，不能聲明block size
11、extent_management_clause
說明了表空間如何管理范圍。一旦聲明了這個子句，只能通過移植的方式改變這些參數(shù)。
如果希望表空間本地管理的話，聲明local選項。本地管理表空間是通過位圖管理的。autoallocate說明表空間自動分配范圍，用戶不能指定范圍的大小。只有9.0以上的版本具有這個功能。uniform說明表空間的范圍的固定大小，缺省是1m。不能將本地管理的數(shù)據(jù)庫的system表空間設(shè)置成字典管理。
如果沒有設(shè)置extent_management_clause，oracle會給他設(shè)置一個默認值。如果初始化參數(shù)compatible小于9.0.0,那么系統(tǒng)創(chuàng)建字典管理表空間。如果大于9.0.0,那么按照如下設(shè)置：
如果沒有指定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' --表空間對應(yīng)的數(shù)據(jù)文件 SIZE 100M --數(shù)據(jù)文件大小 AUTOEXTEND ON NEXT 10M --數(shù)據(jù)文件不夠用自動擴展，每次擴展大小 MAXSIZE 1000M --數(shù)據(jù)文件最大文件大小 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é)數(shù) |
| BLOCKS | 表示空閑塊數(shù)量 |
| 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，整個數(shù)據(jù)庫中每個文件的ID都是唯一的。 |
| TABLESPACE_NAME | 文件所屬的表空間，ORACLE中每個數(shù)據(jù)文件都和表空間是對應(yīng)的。 |
| BYTES | 文件字節(jié)數(shù)量 |
| BLOCKS | 文件的塊數(shù)量，和BYTES是可以換算的。（BYTES/1024/BLOCK_SIZE就可計算得到BLOCKS數(shù)量） |
| STATUS | 狀態(tài)表示，文件當前是否可用。 |
| RELATIVE_FNO | 相對文件號。相對文件號只在 表空間唯一，就是說每個表空間都有自己的相對文件號 |
| AUTOEXTENSIBLE | 是否自動擴展 |
| MAXBYTES | 如果可以擴展，最大可以到多大？（12C是 3.4360E+10，就是32G） |
| MAXBLOCKS | 如果可以擴展，最大可以多少數(shù)據(jù)塊？ |
| INCREMENT_BY | 每次增加的塊數(shù)量 |
| USER_BYTES | 文件中實際有用的字節(jié)數(shù)。 |
| 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;
查詢表空間所有數(shù)據(jù)文件路徑
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';
為空間不足的表空間增加數(shù)據(jù)文件
alter tablespace &tablespace_name add datafile '&datafile_name' size 2G;
注：如果要為臨時表空間擴容，使用下面的語句
alter tablespace &tablespace_name add tempfile '&datafile_name' size 2G;
查看臨時表空間的大小 和 數(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;
重建并修改默認臨時表空間辦法
查詢當前數(shù)據(jù)庫默認臨時表空間名
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;
修改數(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)建用戶時指定表空間
create user username identified by passworddefault tablespace user_datatemporary tablespace user_temp;
修改用戶對應(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;
方法二：手動增加數(shù)據(jù)文件尺寸
ALTER DATABASE DATAFILE '/oracle/oradata/db/GAME.dbf' RESIZE 4000M;
方法三：設(shè)定數(shù)據(jù)文件自動擴展
ALTER DATABASE DATAFILE '/oracle/oradata/db/GAME.dbf AUTOEXTEND ON NEXT 100M MAXSIZE 10000M;
總結(jié)
到此這篇關(guān)于Oracle數(shù)據(jù)庫表空間超詳細介紹的文章就介紹到這了,更多相關(guān)Oracle表空間內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
這篇文章介紹了在Oracle中使用正則表達式的方法，文中通過示例代碼介紹的非常詳細。對大家的學(xué)習(xí)或工作具有一定的參考借鑒價值，需要的朋友可以參考下2022-05-05
Oracle是一種常用的關(guān)系型數(shù)據(jù)庫管理系統(tǒng),提供了許多內(nèi)置函數(shù),用于處理和操作數(shù)據(jù),下面這篇文章主要給大家介紹了關(guān)于Oracle常用函數(shù)超詳細整理的相關(guān)資料,文中通過實例代碼介紹的非常詳細,需要的朋友可以參考下2023-05-05
前幾天重新安裝了數(shù)據(jù)庫服務(wù)器,在服務(wù)器上使用都一切正常,然后在我的客戶端配置一個tns服務(wù)名卻出問題了,下面這篇文章主要給大家介紹了關(guān)于Oracle報錯ora-12514檢查以及解決方法的相關(guān)資料,需要的朋友可以參考下2023-03-03
oracle樹狀結(jié)構(gòu)查詢即層次遞歸查詢，是sql語句經(jīng)常用到的，在實際開發(fā)中組織結(jié)構(gòu)實現(xiàn)及其層次化實現(xiàn)功能也是經(jīng)常遇到的。這篇文章給大家介紹了Oracle遞歸樹形結(jié)構(gòu)查詢功能，需要的朋友參考下2019-09-09
Oracle存儲過程基本語法介紹，有需求的朋友可以參考下2012-11-11
Oracle查看和修改連接數(shù)(進程/會話/并發(fā)等等)
查詢數(shù)據(jù)庫當前進程的連接數(shù)及會話的連接數(shù)、并發(fā)連接數(shù)以及會話情況等等，感興趣的你可以參考下哈，希望可以幫助到你2013-03-03
PL/SQL實現(xiàn)Oracle數(shù)據(jù)庫任務(wù)調(diào)度
PL/SQL實現(xiàn)Oracle數(shù)據(jù)庫任務(wù)調(diào)度...2007-03-03
這篇文章主要介紹了Oracle關(guān)于重建索引爭論的總結(jié),本文總結(jié)了重建索引的理由、重建索引的本質(zhì)、反對重建索引的理由等內(nèi)容,需要的朋友可以參考下2014-09-09
Oracle ORA-22908(NULL表值的參考)異常分析與解決方法
這篇文章主要介紹了Oracle ORA-22908(NULL表值的參考)異常分析與解決方法，大家可以參考使用2013-11-11
這篇文章介紹了Oracle去除重復(fù)數(shù)據(jù)的方法，文中通過示例代碼介紹的非常詳細。對大家的學(xué)習(xí)或工作具有一定的參考借鑒價值，需要的朋友可以參考下2022-05-05