oracle資料庫-資料表空間基礎文法及舉例

標籤：

資料庫的儲存結構

資料庫主要用於儲存和檢索相關的資訊，Oracle資料庫包含邏輯結構和物理結構。

物理結構是指現實儲存單元，由一組檔案組成如資料檔案、記錄檔、控制檔案。

　　資料檔案：用於儲存資料的檔案。如表，索引和資料等都儲存在資料檔案中。

　　記錄檔：用於記錄對資料庫的修改資訊。

　　控制檔案：用於儲存 Oracle執行個體資訊、資料檔案資訊和記錄檔資訊的二進位檔案。由於儲存了資料檔案和記錄檔的資訊，所以 Oracle啟動時必須訪問呢控制檔案。　　

邏輯結構式是指資料概念性的組織。包括  資料表空間、表、行等概念組成。

 

一、資料表空間（tablespace）的概念

資料表空間是Oracle特殊的發明，用於儲存資料庫表，索引等對象的磁碟邏輯空間。在物理上資料表空間由1個或者多個資料檔案組成，而資料庫在邏輯上由1個或者多個資料表空間組成。

資料表空間在Oracle10g中北分為一下3類：

　　1、永久性資料表空間：一般保持基表、視圖、預存程序和索引等資料。SYSTEM、SYSAUX、USERS、EXAMPLE資料表空間是預設安裝的。

　　2、臨時性資料表空間：用於儲存系統中短期活動的資料，如排序資料等。

　　3、撤銷資料表空間；用來協助回退未提交的交易資料，已提交了的資料在這裡是不可以恢複的。

　　一般不需要建立暫存資料表空間和撤銷資料表空間，除非把它們轉移到其他磁碟以提高效能。

　　資料表空間的目的：

　　1、對不同使用者指派不同的資料表空間，對不同的模式對象分配不同的資料表空間，方便對使用者操作，對模式對象的管理。

　　2、可以將不同資料檔案建立到不同的磁碟中，有利於管理磁碟空間，有利於提高I/O效能，有利於備份和恢複資料等。

　　

 

二、系統內建的資料表空間：

　　SYSTEM資料表空間：用於存放Oracle系統內部表和資料字典的資料，如表名、列名、使用者名稱等。不建議將使用者建立的表、索引等對象存放到SYSTEM資料表空間中。

　　SYSAUS資料表空間：用於存放Oracle系統內部的常用的範例使用者的對象。SYSAUX資料表空間一般不儲存使用者的資料，由Oracle系統自動維護。

　　Undotbs資料表空間：Undotbs資料表空間用於存放撤銷資訊的資料表空間。當我們對資料庫表的資料進行增加、修改、刪除時，Oracle自動使用撤銷資料表空間來臨時儲存修改前的資料。當所做操作提交後， Oracle可根據需要保留修改前資料的時間長短來釋放撤銷資料表空間的部分空間。一般在建立Oracle執行個體後，Oracle會自動建立一個名為UNDOTBS1的撤銷資料表空間。

　　TEMP資料表空間：用於存放臨時資料的特殊資料表空間。例如：當使用者需要進行排序時，Oracle講排序的資料臨時放到該資料表空間內。排序完成後即可釋放排序資料所佔用的空間。

　　USERS資料表空間：Users資料表空間是Oracle建議使用者使用的資料表空間，可以在這個資料表空間上建立各種對象，如建立表、索引等。

　　Example資料表空間：（執行個體資料表空間）它用來存放我們安裝Oracle的時候建立的執行個體使用者模式的資料資訊。

　　除了 Oracle內建的資料表空間外，使用者可以根據需要建立多個資料表空間，以區分使用者資料和系統資料。一般在Oracle資料庫中，只建立一個資料庫執行個體，而建立多個資料表空間來管理儲存多重專案中的資料庫表。

　　

 三、建立資料表空間

　　文法：

　　CREATE TABLESPACE tablespacename

　　DATAFILE ‘filename‘[size integer]

　　[AUTOEXTEND[OFF|ON]]

　　tablespancename：表示建立資料表空間的名稱。

　　filename：表示組成資料表空間的一個活多個資料檔案，當有多個資料檔案時，使用，分隔。

　　size：指定檔案的大小，單位是K（位元組）或者M（兆）。

　　AUTOEXTEND：用來啟用或警用資料檔案的自動擴充，只有設定了AUTOEXTEND後，在儲存空間使用完畢後才會自動擴充，設定為ON即可，如果設定為OFF，則不能自動擴充。會導致儲存空間不足的情況。

　　樣本：　　

--建立一個資料表空間，物理檔案為shop_dev_data.dbf，初始大小為100M，為自動成長CREATE TABLESPACE shop_dev_datadatafile ‘E:\DB\Shop\shop_dev_data.dbf‘size 100m autoextend on

 

四、動作表空間

　　1、更改資料表空間大小：

　　ALTER DATABASE DATAFILE ‘資料檔案路徑‘ resize <新的大小>

--將資料表空間的物理檔案大小設定為50MALTER DATABASE DATAFILE ‘E:\DB\Shop\shop_dev_data.dbf‘ resize 50m

 

　　2、改變資料表空間的屬性和狀態

　　ALTER TABLESPACE <資料表空間名稱> READ WRITE;

--設定資料表空間為唯讀狀態ALTER TABLESPACE SHOP_DEV_DATA READ ONLY;--設定資料表空間為讀寫狀態ALTER TABLESPACE SHOP_DEV_DATA READ WRITE;--修改資料表空間狀態為offline;ALTER TABLESPACE SHOP_DEV_DATA OFFLINE;

　　

　　3、刪除資料表空間

　　DROP TABLESPACE tablespacename;

--刪除資料表空間DROP TABLESPACE SHOP_DEV_DATA

　　

　　4、查詢系統中所有的資料表空間　　

SELECT t.TABLESPACE_NAME,t.STATUS FROM DBA_TABLESPACES t

　　

　　5、查詢資料表空間和資料檔案對應關係

SELECT d.TABLESPACE_NAME,d.FILE_NAME FROM DBA_DATA_FILES d

　　

　　6、查詢使用者預設的資料表空間　　

--查詢使用者預設的資料表空間SELECT u.username,u.default_tablespace FROM DBA_USERS

　　

　　7、查詢資料表空間中包含哪些表　　

--查詢“SHOP_DEV_DATA”資料表空間下對應的所有的表 SELECT * FROM USER_TABLES ut WHERE ut.TABLESPACE_NAME = ‘SHOP_DEV_DATA‘  --名稱必須全大寫

 

　　8、移動表到一個新的資料表空間　　

--將newTable移動到users資料表空間下ALTER TABLE NEWTABLE MOVE TABLESPACE USERSSELECT * FROM USER_TABLES utWHERE ut.TABLESPACE_NAME = ‘USERS‘

 

　　9、修改使用者預設資料表空間　　

SELECT username,default_tablespace FROM DBA_USERS--修改shop_user使用者預設資料表空間為usersALTER USER shop_user DEFAULT TABLESPACE  USERS

 

oracle資料庫-資料表空間基礎文法及舉例