管理資料檔案
一 Creating Datafiles and Adding Datafiles to a Tablespace
1 alter tablespace zx add datafile '/oracle/CRM2/CRM/zx04.dbf' size 1M;
2 alter tablespace ltemp add tempfile '/oracle/CRM2/CRM/ltemp01.dbf' size 200m;
二：Changing Datafile Size
1 開啟或禁止資料檔案自動擴充
通過查詢dba_data_files視圖字段autoextensible以确定資料檔案是否自動擴充
select file_id,file_name,tablespace_name,autoextensible from dba_data_files
FILE_ID FILE_NAME TABLESPACE_NAME AUT
---------- ------------------------------ ------------------------------ ---
8 /oracle/CRM2/CRM/zxbig1.dbf ZXBIGTBS NO
7 /oracle/CRM2/CRM/zx3.dbf ZX NO
6 /oracle/CRM2/CRM/undotbs2.dbf UNDOTBS2 NO
5 /oracle/CRM2/CRM/zx1.dbf ZX NO
4 /oracle/CRM2/CRM/users01.dbf USERS YES
3 /oracle/CRM2/CRM/sysaux01.dbf SYSAUX YES
2 /oracle/CRM2/CRM/zx2.dbf ZX NO
1 /oracle/CRM2/CRM/system01.dbf SYSTEM YES
更改資料檔案為自動擴充
alter database datafile '/oracle/CRM2/CRM/zx1.dbf' autoextend on;
禁止資料檔案的自動擴充
alter database datafile '/oracle/CRM2/CRM/zx1.dbf' autoextend off；
eg：更改資料檔案自動擴充
SQL> alter database datafile '/oracle/CRM2/CRM/zx1.dbf' autoextend on;
Database altered.
select file_id,file_name,tablespace_name,autoextensible from dba_data_files
5 /oracle/CRM2/CRM/zx1.dbf ZX YES
eg 禁止資料檔案自動擴充
SQL> alter database datafile '/oracle/CRM2/CRM/zx1.dbf' autoextend off;
SQL> select file_id,file_name,tablespace_name,autoextensible from dba_data_files;
2 調整資料檔案的大小
語句：alter database datafile ...... resize xxx
eg 增加資料檔案大小
看目前資料檔案的大小
SQL> select file_id,file_name,tablespace_name,bytes/1024/1024 ,autoextensible from dba_data_files;
FILE_ID FILE_NAME TABLESPACE_NAME BYTES/1024/1024 AUT
---------- ------------------------------ ------------------------------ --------------- ---
8 /oracle/CRM2/CRM/zxbig1.dbf ZXBIGTBS 2048 NO
7 /oracle/CRM2/CRM/zx3.dbf ZX 1 NO
6 /oracle/CRM2/CRM/undotbs2.dbf UNDOTBS2 200 NO
5 /oracle/CRM2/CRM/zx1.dbf ZX 100 NO
4 /oracle/CRM2/CRM/users01.dbf USERS 5 YES
3 /oracle/CRM2/CRM/sysaux01.dbf SYSAUX 280 YES
2 /oracle/CRM2/CRM/zx2.dbf ZX 10 NO
1 /oracle/CRM2/CRM/system01.dbf SYSTEM 480 YES
增加資料檔案/oracle/CRM2/CRM/zx3.dbf為10M;
SQL> alter database datafile '/oracle/CRM2/CRM/zx3.dbf' resize 10M;
7 /oracle/CRM2/CRM/zx3.dbf ZX 10 NO
eg 減小資料檔案大小
注意能否減少取決于資料檔案目前使用值。
SQL> alter database datafile '/oracle/CRM2/CRM/zx3.dbf' resize 1M;
SQL> select file_id,file_name,tablespace_name,bytes/1024/1024,autoextensible from dba_data_files;
三 更改資料檔案可用性
那些情況需要我們offline資料檔案
a 執行一個離線備份
b 重命名或者遷移資料檔案，必須先離線資料檔案
c 資料檔案丢失或者損壞，打開資料之前，這些檔案必須offline
注意，對隻讀表空間的資料檔案offline後online并不會影響表空間的隻讀狀态。
1 歸檔模式下offline或者online 資料檔案
語句 alter database datafile ........ online|offline;
不過注意，offline的資料online的時候需要recover
eg
SQL> select file#,name,status from v$datafile;
FILE# NAME STATUS
---------- ---------------------------------------- -------
1 /oracle/CRM2/CRM/system01.dbf SYSTEM
2 /oracle/CRM2/CRM/zx2.dbf ONLINE
3 /oracle/CRM2/CRM/sysaux01.dbf ONLINE
4 /oracle/CRM2/CRM/users01.dbf ONLINE
5 /oracle/CRM2/CRM/zx1.dbf ONLINE
6 /oracle/CRM2/CRM/undotbs2.dbf ONLINE
7 /oracle/CRM2/CRM/zx3.dbf ONLINE
8 /oracle/CRM2/CRM/zxbig1.dbf ONLINE
8 rows selected.
SQL> alter database datafile '/oracle/CRM2/CRM/zx3.dbf' offline;
7 /oracle/CRM2/CRM/zx3.dbf RECOVER
SQL> alter database datafile '/oracle/CRM2/CRM/zx3.dbf' online; 注意這裡，不能直接online，上面status字段已經提示需要recover
alter database datafile '/oracle/CRM2/CRM/zx3.dbf' online
*
ERROR at line 1:
ORA-01113: file 7 needs media recovery
ORA-01110: data file 7: '/oracle/CRM2/CRM/zx3.dbf'
SQL> recover datafile 7;
Media recovery complete.
SQL> alter database datafile 7 online;
2 同時更改表空間所有資料檔案狀态
語句
alter tablespace ..... datafile offline|online
alter tablespace ...... tempfile offline|online
注意
a 該語句影響表空間的所有資料檔案，而不影響表空間的狀态。
b 對于離線system，undo，預設臨時表空間所有資料檔案時，資料庫必須mount。而其它表空間資料檔案的離線無限制，mount open狀态都可以。
eg ：測試離線表空間所有資料檔案而表空間狀态不變
目前表空間狀态
SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
TEMP ONLINE
USERS ONLINE
UNDOTBS2 ONLINE
ZX ONLINE
ZXBIGTBS ONLINE
LTEMP1 ONLINE
LTEMP2 ONLINE
離線zx表空間所有資料檔案
SQL> alter tablespace zx datafile offline;
Tablespace altered.
FILE# NAME STATUS
---------- ------------------------------ -------
1 /oracle/CRM2/CRM/system01.dbf SYSTEM
2 /oracle/CRM2/CRM/zx2.dbf RECOVER
3 /oracle/CRM2/CRM/sysaux01.dbf ONLINE
4 /oracle/CRM2/CRM/users01.dbf ONLINE
5 /oracle/CRM2/CRM/zx1.dbf RECOVER
6 /oracle/CRM2/CRM/undotbs2.dbf ONLINE
7 /oracle/CRM2/CRM/zx3.dbf RECOVER
8 /oracle/CRM2/CRM/zxbig1.dbf ONLINE
eg：測試system表空間所有資料檔案和undo表空間所有資料檔案隻能在資料庫mount狀态下離線。
SQL> alter tablespace system datafile offline;
alter tablespace system datafile offline
ORA-01541: system tablespace cannot be brought offline; shut down if necessary
SQL> alter tablespace undotbs2 datafile offline;
alter tablespace undotbs2 datafile offline
ORA-30021: Operation not allowed on undo tablespace
SQL> startup force mount;
ORACLE instance started.
Total System Global Area 322961408 bytes
Fixed Size 2020480 bytes
Variable Size 96471936 bytes
Database Buffers 218103808 bytes
Redo Buffers 6365184 bytes
Database mounted.
1 /oracle/CRM2/CRM/system01.dbf SYSOFF
6 /oracle/CRM2/CRM/undotbs2.dbf RECOVER
四 重命名和遷移資料檔案
注意：對于重命名或者遷移系統表空間資料檔案，預設臨時表空間資料檔案、或者還原表空間資料檔案則必須使用alter database 方式。
重命名單個表空間的資料檔案步驟:
1 normal離線表空間的所有資料檔案
alter tablespace zx offline normal；
2 在作業系統上更改資料檔案名
3 使用alter tablespace ........rename datafile 語句改變資料檔案名字
4 online表空間，查詢相應視圖确認更改效果
--------------------------------------------------------------------------
遷移單個表空間資料檔案的步驟:
2 在作業系統上拷貝資料檔案到目标位置
3 使用alter tablespace ........rename datafile 語句改變資料檔案位置。
---------------------------------------------------------------------------
重命名或者遷移多個表空間資料檔案的步驟：
1 確定資料庫處于mount狀态
2 基于作業系統拷貝或者重命名資料檔案
3 使用alter database語句更改檔案位置或者名字。
-----------------------------------------------------------------------------
以下三個例子分别展示如何操作：
eg1：更改zx表空間中所有資料檔案名字
查詢zx表空間中所有資料檔案名字：
select tb.name as tablespace_name,df.file#,df.name as datafile,df.status from v$tablespace tb,v$datafile df where tb.ts#=df.ts#
TABLESPACE_NAME FILE# DATAFILE STATUS
------------------------------ ---------- ---------------------------------------- -------
SYSTEM 1 /oracle/CRM2/CRM/system01.dbf SYSTEM
SYSAUX 3 /oracle/CRM2/CRM/sysaux01.dbf ONLINE
USERS 4 /oracle/CRM2/CRM/users01.dbf ONLINE
UNDOTBS2 6 /oracle/CRM2/CRM/undotbs2.dbf ONLINE
ZX 5 /oracle/CRM2/CRM/zx1.dbf ONLINE
ZX 7 /oracle/CRM2/CRM/zx3.dbf ONLINE
ZX 2 /oracle/CRM2/CRM/zx2.dbf ONLINE
ZXBIGTBS 8 /oracle/CRM2/CRM/zxbig1.dbf ONLINE
normal離線zx表空間：
SQL> alter tablespace zx offline normal;
作業系統上重命名zx表空間：
SQL> host;
[oracle@oracle ~]$ mv /oracle/CRM2/CRM/zx1.dbf /oracle/CRM2/CRM/zxa.dbf
[oracle@oracle ~]$ mv /oracle/CRM2/CRM/zx2.dbf /oracle/CRM2/CRM/zxb.dbf
[oracle@oracle ~]$ mv /oracle/CRM2/CRM/zx3.dbf /oracle/CRM2/CRM/zxc.dbf
[oracle@oracle ~]$ ls -l /oracle/CRM2/CRM/zx*
-rw-r----- 1 oracle oinstall 104865792 Nov 2 20:18 /oracle/CRM2/CRM/zxa.dbf
-rw-r----- 1 oracle oinstall 10493952 Nov 2 20:18 /oracle/CRM2/CRM/zxb.dbf
-rw-r----- 1 oracle oinstall 2147491840 Nov 2 19:47 /oracle/CRM2/CRM/zxbig1.dbf
-rw-r----- 1 oracle oinstall 1056768 Nov 2 20:18 /oracle/CRM2/CRM/zxc.dbf
使用alter tablespace ........rename datafile 語句改變資料檔案名字：
語句:alter tablespace zx
rename datafile '/oracle/CRM2/CRM/zx1.dbf',
'/oracle/CRM2/CRM/zx2.dbf',
'/oracle/CRM2/CRM/zx3.dbf'
to '/oracle/CRM2/CRM/zxa.dbf',
'/oracle/CRM2/CRM/zxb.dbf',
'/oracle/CRM2/CRM/zxc.dbf' ;
SQL> alter tablespace zx
2 rename datafile '/oracle/CRM2/CRM/zx1.dbf',
3 '/oracle/CRM2/CRM/zx2.dbf',
4 '/oracle/CRM2/CRM/zx3.dbf'
5 to '/oracle/CRM2/CRM/zxa.dbf',
6 '/oracle/CRM2/CRM/zxb.dbf',
7 '/oracle/CRM2/CRM/zxc.dbf' ;
online表空間并檢查更改效果如下：
SQL> alter tablespace zx online;
SQL> select tb.name as tablespace_name,df.file#,df.name as datafile,df.status from v$tablespace tb,v$datafile df where tb.ts#=df.ts#;
ZX 5 /oracle/CRM2/CRM/zxa.dbf ONLINE
ZX 7 /oracle/CRM2/CRM/zxc.dbf ONLINE
ZX 2 /oracle/CRM2/CRM/zxb.dbf ONLINE
eg2:遷移表空間zx所有資料檔案到位置/oracle/CRM2/
select tablespace_name,file_id,file_name,status from dba_data_files where tablespace_name like 'ZX'
TABLESPACE_NAME FILE_ID FILE_NAME STATUS
------------------------------ ---------- ------------------------------ ---------
ZX 7 /oracle/CRM2/CRM/zxc.dbf AVAILABLE
ZX 5 /oracle/CRM2/CRM/zxa.dbf AVAILABLE
ZX 2 /oracle/CRM2/CRM/zxb.dbf AVAILABLE
2 /oracle/CRM2/CRM/zxb.dbf OFFLINE
5 /oracle/CRM2/CRM/zxa.dbf OFFLINE
7 /oracle/CRM2/CRM/zxc.dbf OFFLINE
在作業系統上拷貝資料檔案到目标位置：
[oracle@oracle ~]$ cp /oracle/CRM2/CRM/zx*.dbf /oracle/CRM2/
[oracle@oracle ~]$ ls -l /oracle/CRM2/
total 2213032
-rw-r----- 1 oracle oinstall 104865792 Nov 2 22:37 zxa.dbf
-rw-r----- 1 oracle oinstall 10493952 Nov 2 22:37 zxb.dbf
-rw-r----- 1 oracle oinstall 1056768 Nov 2 22:39 zxc.dbf
使用alter tablespace ........rename datafile 語句改變資料檔案位置
語句：alter tablespace zx
rename datafile '/oracle/CRM2/CRM/zxa.dbf',
'/oracle/CRM2/CRM/zxb.dbf',
'/oracle/CRM2/CRM/zxc.dbf'
to '/oracle/CRM2/zxa.dbf',
'/oracle/CRM2/zxb.dbf',
'/oracle/CRM2/zxc.dbf';
2 rename datafile '/oracle/CRM2/CRM/zxa.dbf',
3 '/oracle/CRM2/CRM/zxb.dbf',
4 '/oracle/CRM2/CRM/zxc.dbf'
5 to '/oracle/CRM2/zxa.dbf',
6 '/oracle/CRM2/zxb.dbf',
7 '/oracle/CRM2/zxc.dbf';
online表空間，查詢相應視圖确認更改效果:
ZX 7 /oracle/CRM2/zxc.dbf AVAILABLE
ZX 5 /oracle/CRM2/zxa.dbf AVAILABLE
ZX 2 /oracle/CRM2/zxb.dbf AVAILABLE
eg3：移動system表空間資料檔案和更改資料檔案名字
啟動資料庫到mount狀态：
拷貝資料檔案到目标位置：
[oracle@oracle ~]$ cp /oracle/CRM2/CRM/system01.dbf /oracle/CRM2/
[oracle@oracle ~]$ ls -l /oracle/CRM2
total 2705044
-rw-r----- 1 oracle oinstall 503324672 Nov 2 23:17 system01.dbf
通過alter database rename file .....to 移動system表空間位置：
alter database rename file '/oracle/CRM2/CRM/system01.dbf' to '/oracle/CRM2/system01.dbf';
啟動資料庫到open狀态并确認更改有效：
SQL> alter database open;
SQL> col name for a30
1 /oracle/CRM2/system01.dbf SYSTEM
2 /oracle/CRM2/zxb.dbf ONLINE
5 /oracle/CRM2/zxa.dbf ONLINE
7 /oracle/CRM2/zxc.dbf ONLINE
更改system表空間資料檔案的名字：
[oracle@oracle ~]$ mv /oracle/CRM2/system01.dbf /oracle/CRM2/system1.dbf
[oracle@oracle ~]$ ls /oracle/CRM2/
CRM ERP system1.dbf zxa.dbf zxb.dbf zxbig1.dbf zxc.dbf
SQL> alter database rename file '/oracle/CRM2/system01.dbf' to '/oracle/CRM2/system1.dbf';
1 /oracle/CRM2/system1.dbf SYSTEM
五 Dropping Datafiles
語句：alter tablespace xxx drop datafile 'xxxxxxxxxx';
alter tablespace xxx drop tempfile 'xxxxxxxxxxxx';
restrictions for drop datafile
1 資料庫必須open
2 system表空間的資料檔案不能drop
3 如果表空間離線，則資料檔案不能drop
4 如果表空間有一個資料檔案，則該資料檔案不能drop
5 對于大表空間drop datafile語句不适用。
eg1大表空間資料檔案drop
select tablespace_name,file_name,autoextensible from dba_data_files
TABLESPACE_NAME FILE_NAME AUT
------------------------------ ------------------------------ ---
ZXBIGTBS /oracle/CRM2/CRM/zxbig1.dbf NO
ZX /oracle/CRM2/zxc.dbf NO
UNDOTBS2 /oracle/CRM2/CRM/undotbs2.dbf NO
ZX /oracle/CRM2/zxa.dbf NO
USERS /oracle/CRM2/CRM/users01.dbf YES
SYSAUX /oracle/CRM2/CRM/sysaux01.dbf YES
ZX /oracle/CRM2/zxb.dbf NO
SYSTEM /oracle/CRM2/system1.dbf YES
SQL> alter tablespace zxbigtbs drop datafile '/oracle/CRM2/CRM/zxbig1.dbf ';
alter tablespace zxbigtbs drop datafile '/oracle/CRM2/CRM/zxbig1.dbf '
ORA-01565: error in identifying file '/oracle/CRM2/CRM/zxbig1.dbf '
eg2:測試表空間隻有一個資料檔案能不能drop
select tablespace_name,file_name,autoextensible from dba_data_files
SQL> alter tablespace users drop datafile '/oracle/CRM2/CRM/users01.dbf';
alter tablespace users drop datafile '/oracle/CRM2/CRM/users01.dbf'
ORA-03261: the tablespace USERS has only one file
eg3：測試表空間離線，則資料檔案不能删除
SQL> alter tablespace zx offline;
SQL> alter tablespace zx drop datafile '/oracle/CRM2/zxc.dbf';
alter tablespace zx drop datafile '/oracle/CRM2/zxc.dbf'
ORA-03264: cannot drop offline datafile of locally managed tablespace
eg4：要删除資料檔案，資料庫必須open
SQL> select open_mode from v$database;
OPEN_MODE
----------
MOUNTED
SQL> alter tablespace zx drop datafile '/oracle/CRM2/zxc.dbf';
alter tablespace zx drop datafile '/oracle/CRM2/zxc.dbf'
ORA-01109: database not open
eg5 system表空間資料檔案不能drop
SQL> alter tablespace system drop datafile '/oracle/CRM2/system1.dbf ';
alter tablespace system drop datafile '/oracle/CRM2/system1.dbf '
本文轉自 zhangxuwl 51CTO部落格，原文連結：http://blog.51cto.com/jiujian/1049809，如需轉載請自行聯系原作者
![【MySQL資料庫】資料庫索引事務1.索引2.事務[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)