管理数据文件
一 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=)