ORACLE - 管理資料表空間和資料檔案

標籤：style   blog   io   ar   color   使用   sp   strong   on   

ORACLE資料表空間是一個邏輯分區，一個資料檔案只能屬於一個資料表空間，一個資料表空間可以擁有多個資料檔案。

一般情況下，如果一個執行個體分配給多個應用使用，需要建立不同的資料表空間，每個使用者使用自己的資料表空間。

一、資料表空間的建立與授權

首先查看錶空間的使用方式：

select tablespace_name,sum(bytes)/1024/1024 as MB from dba_data_files group by tablespace_name;---剩餘容量（在固定大小的情況下）select tablespace_name, count(*) as extends, round(sum(bytes) / 1024 / 1024, 2) as MB, sum(blocks) as blocks from dba_free_space group by tablespace_name;

--查看錶空間與資料檔案對應關係
select tablespace_name, file_name, bytes/1024/1024 from dba_data_files;

--查詢資料表空間存放的對象及其擁有者
 select distinct segment_type, owner, tablespace_name from dba_segments where tablespace_name=‘SC‘;

 

1. 建立資料表空間

create tablespace you_tabs_name datafile  ‘/opt/oracle/oradata/orcl/ts_01.dbf‘ size 50m autoextend on;

2. 建立暫存資料表空間(一般用於資料查詢時排序操作，在資料量較大記憶體不足時)

SQL> create temporary tablespace temp2 tempfile ‘/opt/oracle/oradata/temp02.dbf‘ size 100m autoextend on;--可以為新建立的資料表空間增加資料檔案SQL> alter tablespace temp2 add tempfile ‘/opt/oracle/oradata/temp03.dbf‘ size 100m ;

3. 建立一個使用者指定預設資料表空間和暫存資料表空間

create user youuname identified by “yourpwd” default tablespace ts_01 temporary tablespace temp2 profile default quota unlimited on ts_01

4. 建立完成後可查詢某個使用者的預設資料表空間

select default_tablespace, temporary_tablespace, d.username  from dba_users d where d.username=‘SCOTT‘;

5.如果使用者已經建立，可以授權新的資料表空間給使用者使用

alter user uname quota unlimited on  ts_01;
alter user scott default tablespace ts_01; -- 更改使用者的預設資料表空間

 

6. 當一個資料表空間不再使用時，可對該資料表空間下的資料檔案離線（離線前千萬別在作業系統層刪除資料檔案）

alter database datafile ‘/opt/oracle/oradata/orcl/ts_01.dbf‘ offline;

 

7. 設定資料表空間為唯讀，用於備份和恢複

資料表空間唯讀狀態下，不能進行insert,update,delete，但可以刪除索引和目錄。必須滿足以下條件：(1)    必須為online(2)    不能包含任何復原段(3)    資料表空間不能在歸檔模式或資料發行中SQL>alter tablespace tablespace_name read only;恢複讀寫狀態SQL>alter tablespace tablespace_name read write;

 

8. 重新命名資料表空間

更變名稱後，原資料表空間存放的對象也同時更變，包括使用者對於這資料表空間的許可權也同步更改條件:(1)    只能對一般資料表空間更名，不能更變system，sysaux更名(2)    必須處於online狀態SQL>alter tablespace old_name rename to new_name;

 

 

二、暫存資料表空間操作

暫存資料表空間一般用於資料量比較大的查詢結果中排序操作（當可使用記憶體比較小的情況下）：

1. 查看目前現有的暫存資料表空間

SQL> select tablespace_name,file_name,bytes/1024/1024 file_size,autoextensible from dba_temp_files;
SQL> select name,bytes/1024/1024 from v$tempfile;

2. 建立暫存資料表空間和更改一個使用者暫存資料表空間

SQL> create temporary tablespace temp2 tempfile ‘/opt/oracle/oradata/temp02.dbf‘ size 100m autoextend on;--可以為新建立的資料表空間增加資料檔案SQL> alter tablespace temp2 add tempfile ‘/opt/oracle/oradata/temp03.dbf‘ size 100m ;

--更改使用者暫存資料表空間

  SQL> alter user scott temporary tablespace temp1;

3. 可修改資料庫管理預設使用的暫存資料表空間

SQL> alter database default temporary tablespace temp1;

4. 如果一個暫存資料表空間沒有使用者在使用時，可刪除

SQL> drop tablespace temp;
然後再通過作業系統命令刪除資料檔案即可

5. 更改資料表空間大小或設定自動擴充

alter database tempfile ‘/opt/oracle/oradata/orcl/sc_temp_01.dbf‘ resize 50M;alter database tempfile ‘opt/oracle/oradata/oracle/sc_temp_01.dbf‘ autoextend on next 3M maxsize unlimited;

6. 刪除一個資料檔案

alter database tempfile ‘/opt/oracle/oradata/orcl/sc_temp_01.dbf‘ offline;//先把檔案離線alter database tempfile ‘/opt/oracle/oradata/orcl/sc_temp_01.dbf‘ drop;

--徹底刪除一個暫存資料表空間，謹慎使用
drop tablespace temp1 including contents and datafiles

 

三、UNDO資料表空間

undo資料表空間作用:回退事務，讀一致性，事務恢複(斷電，記憶體故障，後台進程故障等)，閃回操作，UNDO 不屬於任何使用者，由庫公用，預設是自動成長的，直至耗完磁碟.

 

--顯示正在使用的undo資料表空間show parameter undo_tablespace;--查詢所有undo資料表空間select tablespace_name from dba_tablespaces where contents=‘UNDO‘;

 

 

 

1 .建立UNDO資料表空間，這裡設定固定大小2G

SQL>create undo tablespace undotbs02 datafile ‘/xx/xxx/‘ size 2G;

2. UNDO資料表空間離線和刪除

SQL>alter system set undo_tablespace=undotbs02;SQL>alter tablespace tablespace_name offline;SQL>drop tablespace undotbs02;

離線時，必須要有替代的undo資料表空間。若某個事務正在使用，則還不能離線,查詢SQL如下:

SQL>SELECT SEGMENT_NAME, XACTS, V.STATUS FROM V$ROLLSTAT V, DBA_ROLLBACK_SEGS WHERE TABLESPACE_NAME = ‘undotbs02‘ AND SEGMENT_ID = USN; 

當沒有資料行時才可以離線，如果有資料行，可查看是否有事務在進行： 
SELECT S.SID, S.SERIAL#, S.USERNAME, R.NAME "ROLLBACK" FROM V$SESSION S, V$TRANSACTION T, V$ROLLNAME R WHERE S.TADDR = T.ADDR AND T.XIDUSN = R.USN;

 

ORACLE - 管理資料表空間和資料檔案