oracle 資料檔案表空間管理

oracle 資料檔案 表空間[@more@]

(1) 檢視錶空間:
SQL> select * from v$tablespace;
(2) 檢視資料檔案:
SQL> select * from v$datafile;
(3) 檢視tablespace 和data files之間的對應關係(透過表空間的號連線在一起)：
SQL> select t1.name ttt,t2.name from v$tablespace t1, v$datafile t2 where t1.ts#=t2.ts#;
註釋：t1.name ttt 就是不用name作為列名，用ttt作為列名。

新增、刪除表空間
CREATE TABLESPACE pindb LOGGING DATAFILE'/opt/oracle/oradata/pindb/pindb.dbf' SIZE 200m EXTENT MANAGEMENT LOCAL autoallocte SEGMENT SPACE MANAGEMENT AUTO;
drop tablespace megaeyes including contents and datafiles;

建立臨時表空間：
CREATE TEMPORARY TABLESPACE temp TEMPFILE 'c:/oracle/oradata/temp02.dbf' SIZE 500M ;
臨時檔案不能read-only、rename、offline,不能用alter database 來建立臨時檔案

新增redo 組成員
SQL> alter database add logfile member '/opt/oracle/redo/redo01a.log' to group 1;
然後在切換一下日誌，這樣才能使新新增的日誌成員生效。
Alter database drop logfile group 1;

設定預設的臨時表空間：
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

增加表空間資料檔案&擴充套件表空間資料檔案。
增加資料檔案：
ALTER TABLESPACE mega ADD DATAFILE '/opt/oradata/mega02.dbf' SIZE 100M; (temp 表空間 tempfile）
擴充套件現有資料檔案：
ALTER DATABASE DATAFILE '/opt/oradata/mega02.dbf' RESIZE 1000M

移動資料檔案（注意：temp表空間的不能移動）
表空間必須是offline(使用alter tablespace xxx rename datafile 'xx' to 'xx');
目標資料檔案必須已經存在;
如果是不能處於offline狀態的表空間則要關閉資料庫，將其啟動到mount狀態，同時目標資料檔案必須已經存在。
具體步驟如下：----可以offline的資料檔案
1.alter tablespace M33_CTEST offline;
2.! cp /opt/oracle/oradata/conner/users01.dbf /opt/oracle/oradata/users01.dbf
3.alter tablespace M33_CTEST rename datafile '/opt/oracle/oradata/pindb/M33_ctest.dbf' to '/opt/oracle/oradata/m33_ctest.dbf';
4.alter tablespace m33_ctest online;

配置OMF
配置OMF檔案來建立表空間的時候DB_CREATE_FILE_DEST會將資料檔案存放在預設的位置。
如果想要更改位置則用 alter system set db_create_file_dest='xx';
用OMF建立表空間：create tablespace xxx; 刪除：drop tablespace xxx;

預設32位作業系統ORACLE的塊的大小是8k。塊定義在表空間一級。9i後一個庫支援5個塊大小
strings spfileallan.ora |grep -i db_block (-i是不區分大小寫查詢)
[oracle@mega dbs]$ strings spfilemega.ora |grep db_blo
*.db_block_size=8192 （system temp 表空間一定是這個值）

建立一個非標準塊的表空間：
create tablespace tbs1 datafile 'tab' size 10M blocksize 4k; (不過之前要先alter system set db_4k_cache_size=34603008;設定4k的cache值才可以）

truncate table m; 清空表，並把高水位恢復。
delete * from m;這個只能刪除資料，不能恢復高水位。

表空間重新命名
在 Oracle 10g 以前的版本，更改表空間名字是幾乎不可能的事情，除非刪除，重新建立，大費周章。
Oracle 10g 新新增了一項更改表空間名字的功能，使得更改表空間名字瞬間即可完成。是個較為人性化的功能。語法很簡單：
SQL> ALTER TABLESPACE tablespacename_old RENAME TO tablespacename_new;
因為 system 和 sysaux 這兩個表空間的特殊性，是不可以更名的。
undo表空間，如果你用的是spfile則可以線上更改，如果用的是pfile則需要重啟才能生效。

我們下面介紹一下刪除資料檔案

在我們詳細介紹之前，我們必須說清楚一點：Oracle不提供如刪除表、檢視一樣刪除資料檔案的方法，資料檔案是表空間的一部分，所以不能“移走”表空間。


一、使用offline資料檔案的方法

非歸檔模式使用：alter database datafile '...' offline drop;

歸檔模式使用： alter database datafile '...' offline;

說明：

1) 以上命令只是將該資料檔案OFFLINE，而不是在資料庫中刪除資料檔案。該資料檔案的資訊在控制檔案種仍存在。查詢v$datafile，仍顯示該檔案。

2) 歸檔模式下offline和offline drop效果是一樣的

3) offline後，存在此datafile上的物件將不能訪問

4) noarchivelog模式下，只要online redo日誌沒有被重寫，可以對這個檔案recover後進行online操作



實際使用案例：

直接刪除資料檔案後無法進入系統的解決方案



正常情況下，刪除表空間的正確方法為：

DROP TABLESPACE tablespace_name INCLUDING CONTENTS AND DATAFILES;



如果沒有透過以上命令刪除而直接刪除了資料檔案，將導致資料庫無法開啟。



如果直接刪除了資料檔案

普通使用者登入時，則報錯：

ORA-01033: ORACLE initialization or shutdown in progress

sys使用者可以正常登入

但進行操作時（SELECT count(1) FROM user_tables），則會報錯：

ORA-01219: 資料庫未開啟: 僅允許在固定表/檢視中查詢

如果執行命令alter database open以開啟資料庫時，又報如下錯：

ORA-01157: 無法標識/鎖定資料檔案 12 - 請參閱 DBWR 跟蹤檔案

ORA-01110: 資料檔案 12: 'E:ORACLEPRODUCT10.2.0ORADATAORCLTSTEST001.DBF'



說明資料庫沒找到這個資料檔案

因為資料檔案在沒有被offline的情況下物理刪除了,導致oracle的資料不一致,因此啟動失敗.

透過以下方法即可解決



解決方法：

sqlplus sys/orcl@orcl as sysdba;

SQL> alter database datafile 'E:ORACLEPRODUCT10.2.0ORADATAORCLTSTEST001.DBF' offline drop;

SQL> alter database open;

SQL> drop tablespace CTBASEDATA;



二、Oracle 10G R2開始，可以採用：Alter tablespace tablespace_name drop datafile file_name;來刪除一個空資料檔案，並且相應的資料字典資訊也會清除:



sys@ORCL>select file_id,file_name,tablespace_name from dba_data_files

2 where tablespace_name='USERS';

FILE_ID FILE_NAME TABLESPACE_NAME

------- -------------------------------------------- ------------------

4 /u01/app/oracle/oradata/orcl/users01.dbf USERS



sys@ORCL>alter tablespace users add datafile

2 '/u01/app/oracle/oradata/orcl/users02.dbf' size 5M autoextend off;

Tablespace altered.

sys@ORCL>select file_id,file_name,tablespace_name from dba_data_files

2 where tablespace_name='USERS';

FILE_ID FILE_NAME TABLESPACE_NAME

------- -------------------------------------------- -------------------

4 /u01/app/oracle/oradata/orcl/users01.dbf USERS

9 /u01/app/oracle/oradata/orcl/users02.dbf USERS



sys@ORCL>drop table test;

Table dropped.

sys@ORCL>create table test tablespace users

2 as

3 select * from dba_objects;

Table created.

sys@ORCL>select SEGMENT_NAME,FILE_ID,BLOCKS from dba_extents where file_id=9;

SEGMENT_NAME FILE_ID BLOCKS

------------------------------ ---------- ----------

TEST 9 8

TEST 9 8

TEST 9 8

TEST 9 8

TEST 9 8

TEST 9 8

TEST 9 8

TEST 9 8

TEST 9 8

TEST 9 8

TEST 9 8

TEST 9 8

TEST 9 8

TEST 9 8

TEST 9 8

TEST 9 128

TEST 9 128



17 rows selected.

sys@ORCL>alter table test move tablespace PERFSTAT; --把表移動到其它表空間Table altered.

sys@ORCL>select SEGMENT_NAME,FILE_ID,BLOCKS from dba_extents

2 where file_id=9;

no rows selected

sys@ORCL>alter tablespace users drop datafile

2 '/u01/app/oracle/oradata/orcl/users02.dbf';

Tablespace altered.

sys@ORCL>select file_id,file_name,tablespace_name from dba_data_files

2 where tablespace_name='USERS';

FILE_ID FILE_NAME TABLESPACE_NAME

------- -------------------------------------------- ---------------------

4 /u01/app/oracle/oradata/orcl/users01.dbf USERS



三、oracle 10g可以刪除臨時表空間的檔案

alter database tempfile '/home/oracle/temp01.dbf' drop including datafiles;