oracle数据文件的管理
来源：互联网 发布：nba球队数据统计 编辑：程序博客网 时间：2024/05/19 10:36
数据文件的管理
1. datafile add
2. datafile delete
3. datafile resize
4. datafile rename
1. datafile add
添加表空间，从而添加数据文件
SQL> create tablespace testtbs
2 datafile '/oracle/oradata/demo/testtbs01.dbf'
3 size 30m;
Tablespace created.
SQL> select tablespace_name,block_size,status,contents from dba_tablespaces;
TABLESPACE_NAME BLOCK_SIZE STATUS CONTENTS
------------------------------ ---------- --------- ---------
SYSTEM 8192 ONLINE PERMANENT
UNDOTBS 8192 ONLINE UNDO
SYSAUX 8192 ONLINE PERMANENT
TEMPTBS 8192 ONLINE TEMPORARY
USERS 8192 ONLINE PERMANENT
LXTBS 8192 ONLINE PERMANENT
TESTTBS 8192 ONLINE PERMANENT
7 rows selected.
SQL> select file_id,file_name,tablespace_name from dba_data_files;
FILE_ID FILE_NAME TABLESPACE_NAME
---------- -------------------------------------------------- ------------------------------
1 /oracle/oradata/demo/system01.dbf SYSTEM
2 /oracle/oradata/demo/undotbs01.dbf UNDOTBS
3 /oracle/oradata/demo/sysaux01.dbf SYSAUX
4 /oracle/oradata/demo/users01.dbf USERS
6 /oracle/oradata/demo/lxtbs02.dbf LXTBS
5 /oracle/oradata/demo/lxtbs01.dbf LXTBS
7 /oracle/oradata/demo/testtbs01.dbf TESTTBS
7 rows selected.
给表空间添加数据文件
SQL> alter tablespace testtbs
2 add datafile '/oracle/oradata/demo/testtbs02.dbf'
3 size 30m;
Tablespace altered.
SQL> select file_id,file_name,tablespace_name from dba_data_files;
FILE_ID FILE_NAME TABLESPACE_NAME
---------- -------------------------------------------------- ------------------------------
1 /oracle/oradata/demo/system01.dbf SYSTEM
2 /oracle/oradata/demo/undotbs01.dbf UNDOTBS
3 /oracle/oradata/demo/sysaux01.dbf SYSAUX
4 /oracle/oradata/demo/users01.dbf USERS
6 /oracle/oradata/demo/lxtbs02.dbf LXTBS
5 /oracle/oradata/demo/lxtbs01.dbf LXTBS
7 /oracle/oradata/demo/testtbs01.dbf TESTTBS
8 /oracle/oradata/demo/testtbs02.dbf TESTTBS
8 rows selected.
2. datafile delete
SQL> alter tablespace lxtbs
2 drop datafile '/oracle/oradata/demo/lxtbs02.dbf';
Tablespace altered.
SQL> select file_id,file_name,tablespace_name from dba_data_files;
FILE_ID FILE_NAME TABLESPACE_NAME
---------- -------------------------------------------------- ------------------------------
1 /oracle/oradata/demo/system01.dbf SYSTEM
2 /oracle/oradata/demo/undotbs01.dbf UNDOTBS
3 /oracle/oradata/demo/sysaux01.dbf SYSAUX
4 /oracle/oradata/demo/users01.dbf USERS
5 /oracle/oradata/demo/lxtbs01.dbf LXTBS
7 /oracle/oradata/demo/testtbs01.dbf TESTTBS
8 /oracle/oradata/demo/testtbs02.dbf TESTTBS
7 rows selected.
注意：若表空间只有一个数据文件，则会删除失败。
SQL> select file_id,file_name,tablespace_name from dba_data_files;
FILE_ID FILE_NAME TABLESPACE_NAME
---------- -------------------------------------------------- ------------------------------
1 /oracle/oradata/demo/system01.dbf SYSTEM
2 /oracle/oradata/demo/undotbs01.dbf UNDOTBS
3 /oracle/oradata/demo/sysaux01.dbf SYSAUX
4 /oracle/oradata/demo/users01.dbf USERS
5 /oracle/oradata/demo/lxtbs01.dbf LXTBS
7 /oracle/oradata/demo/testtbs01.dbf TESTTBS
8 /oracle/oradata/demo/testtbs02.dbf TESTTBS
7 rows selected.
SQL> alter tablespace LXTBS
2 drop datafile '/oracle/oradata/demo/lxtbs01.dbf';
alter tablespace LXTBS
*
ERROR at line 1:
ORA-03261: the tablespace LXTBS has only one file
3. datafile resize
SQL> select file_id,file_name,bytes/1024/1024||'m' "size",tablespace_name from dba_data_files;
FILE_ID FILE_NAME size TABLESPACE_NAME
---------- -------------------------------------------------- ----------------------------------------- ------------------------------
1 /oracle/oradata/demo/system01.dbf 300m SYSTEM
2 /oracle/oradata/demo/undotbs01.dbf 500m UNDOTBS
3 /oracle/oradata/demo/sysaux01.dbf 200m SYSAUX
4 /oracle/oradata/demo/users01.dbf 300m USERS
5 /oracle/oradata/demo/lxtbs01.dbf 50m LXTBS
7 /oracle/oradata/demo/testtbs01.dbf 30m TESTTBS
8 /oracle/oradata/demo/testtbs02.dbf 30m TESTTBS
7 rows selected.
SQL> alter database
2 datafile '/oracle/oradata/demo/lxtbs01.dbf' resize 70m;
Database altered.
SQL> select file_id,file_name,bytes/1024/1024||'m' "size",tablespace_name from dba_data_files;
FILE_ID FILE_NAME size TABLESPACE_NAME
---------- -------------------------------------------------- ----------------------------------------- ------------------------------
1 /oracle/oradata/demo/system01.dbf 300m SYSTEM
2 /oracle/oradata/demo/undotbs01.dbf 500m UNDOTBS
3 /oracle/oradata/demo/sysaux01.dbf 200m SYSAUX
4 /oracle/oradata/demo/users01.dbf 300m USERS
5 /oracle/oradata/demo/lxtbs01.dbf 70m LXTBS
7 /oracle/oradata/demo/testtbs01.dbf 30m TESTTBS
8 /oracle/oradata/demo/testtbs02.dbf 30m TESTTBS
7 rows selected.
注意：
RESIZE Specify RESIZE if you want Oracle Database to attempt to increase or decrease the size of the data file to the specified absolute size in bytes. There is no default, so you must specify a size.
If sufficient disk space is not available for the increased size, or if the file contains data beyond the specified decreased size, then Oracle Database returns an error.
4. datafile rename
通过alter database来重命名数据文件
SQL> alter database rename file '/oracle/oradata/demo/lxtbs01.dbf' to '/oracle/oradata/demo/lxtbs03.dbf';
alter database rename file '/oracle/oradata/demo/lxtbs01.dbf' to '/oracle/oradata/demo/lxtbs03.dbf'
*
ERROR at line 1:
ORA-01511: error in renaming log/data files
ORA-01121: cannot rename database file 5 - file is in use or recovery
ORA-01110: data file 5: '/oracle/oradata/demo/lxtbs01.dbf'
报错了，提示：数据文件5正在被使用，那我们就把对应的表空间离线
SQL> alter tablespace lxtbs offline;
Tablespace altered. /*表空间离线，不再对外提供访问*/
SQL> alter database rename file '/oracle/oradata/demo/lxtbs01.dbf' to '/oracle/oradata/demo/lxtbs03.dbf';
alter database rename file '/oracle/oradata/demo/lxtbs01.dbf' to '/oracle/oradata/demo/lxtbs03.dbf'
*
ERROR at line 1:
ORA-01511: error in renaming log/data files
ORA-01141: error renaming data file 5 - new file '/oracle/oradata/demo/lxtbs03.dbf' not found
ORA-01110: data file 5: '/oracle/oradata/demo/lxtbs01.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
又报错了，提示：新的数据文件'/oracle/oradata/demo/lxtbs03.dbf'不存在。要先把文件'/oracle/oradata/demo/lxtbs01.dbf'物理得拷贝到'/oracle/oradata/demo/lxtbs03.dbf'，重新执行上述的
命令，就可以了。
SQL> alter database rename file '/oracle/oradata/demo/lxtbs01.dbf' to '/oracle/oradata/demo/lxtbs03.dbf';
Database altered.
SQL> alter tablespace lxtbs online; /*别忘记了，将已离线的表空间拉上了*/
Tablespace altered.
SQL> select file_id,file_name,bytes/1024/1024||'m' "size",tablespace_name from dba_data_files;
FILE_ID FILE_NAME size TABLESPACE_NAME
---------- -------------------------------------------------- ----------------------------------------- ------------------------------
1 /oracle/oradata/demo/system01.dbf 300m SYSTEM
2 /oracle/oradata/demo/undotbs01.dbf 500m UNDOTBS
3 /oracle/oradata/demo/sysaux01.dbf 200m SYSAUX
4 /oracle/oradata/demo/users01.dbf 300m USERS
5 /oracle/oradata/demo/lxtbs03.dbf 70m LXTBS
7 /oracle/oradata/demo/testtbs01.dbf 30m TESTTBS
8 /oracle/oradata/demo/testtbs02.dbf 30m TESTTBS
7 rows selected.
通过alter tablespace来重命名数据文件
SQL> select status from v$instance;
STATUS
------------
OPEN
SQL> alter tablespace lxtbs offline;
Tablespace altered.
SQL> select file_id,file_name,tablespace_name,status,online_status from dba_data_files;
FILE_ID FILE_NAME TABLESPACE_NAME STATUS ONLINE_
---------- -------------------------------------------------- ------------------------------ --------- -------
1 /oracle/oradata/demo/system01.dbf SYSTEM AVAILABLE SYSTEM
2 /oracle/oradata/demo/undotbs01.dbf UNDOTBS AVAILABLE ONLINE
3 /oracle/oradata/demo/sysaux01.dbf SYSAUX AVAILABLE ONLINE
4 /oracle/oradata/demo/users01.dbf USERS AVAILABLE ONLINE
5 /oracle/oradata/demo/lxtbs04.dbf LXTBS AVAILABLE OFFLINE
7 /oracle/oradata/demo/testtbs01.dbf TESTTBS AVAILABLE ONLINE
8 /oracle/oradata/demo/testtbs02.dbf TESTTBS AVAILABLE ONLINE
7 rows selected.
通过操作系统命令将要改名的数据文件拷到指定路径下。
[oracle@redhat4 demo]$ mv /oracle/oradata/demo/lxtbs04.dbf /oracle/oradata/demo/lxtbs01.dbf
SQL> alter tablespace lxtbs
2 rename datafile '/oracle/oradata/demo/lxtbs04.dbf' to '/oracle/oradata/demo/lxtbs01.dbf';
Tablespace altered.
SQL> select file_id,file_name,tablespace_name,status,online_status from dba_data_files;
FILE_ID FILE_NAME TABLESPACE_NAME STATUS ONLINE_
---------- -------------------------------------------------- ------------------------------ --------- -------
1 /oracle/oradata/demo/system01.dbf SYSTEM AVAILABLE SYSTEM
2 /oracle/oradata/demo/undotbs01.dbf UNDOTBS AVAILABLE ONLINE
3 /oracle/oradata/demo/sysaux01.dbf SYSAUX AVAILABLE ONLINE
4 /oracle/oradata/demo/users01.dbf USERS AVAILABLE ONLINE
5 /oracle/oradata/demo/lxtbs01.dbf LXTBS AVAILABLE OFFLINE
7 /oracle/oradata/demo/testtbs01.dbf TESTTBS AVAILABLE ONLINE
8 /oracle/oradata/demo/testtbs02.dbf TESTTBS AVAILABLE ONLINE
7 rows selected.
此时，表空间LXTBS仍处于离线状态，将其改为上线状态(ONLINE)
QL> alter tablespace LXTBS online;
Tablespace altered.
SQL> select file_id,file_name,tablespace_name,status,online_status from dba_data_files;
FILE_ID FILE_NAME TABLESPACE_NAME STATUS ONLINE_
---------- -------------------------------------------------- ------------------------------ --------- -------
1 /oracle/oradata/demo/system01.dbf SYSTEM AVAILABLE SYSTEM
2 /oracle/oradata/demo/undotbs01.dbf UNDOTBS AVAILABLE ONLINE
3 /oracle/oradata/demo/sysaux01.dbf SYSAUX AVAILABLE ONLINE
4 /oracle/oradata/demo/users01.dbf USERS AVAILABLE ONLINE
5 /oracle/oradata/demo/lxtbs01.dbf LXTBS AVAILABLE ONLINE
7 /oracle/oradata/demo/testtbs01.dbf TESTTBS AVAILABLE ONLINE
8 /oracle/oradata/demo/testtbs02.dbf TESTTBS AVAILABLE ONLINE
7 rows selected.
下面是oracle官方文档的语法：
ALTER TABLESPACE tablespace RENAME DATAFILE 'filename' to 'filename';
注意：
Specify RENAME DATAFILE to rename one or more of the tablespace data files. The database must be open, and you must take the tablespace offline before renaming it. Eachfilename must fully specify a data file using the conventions for filenames on your operating system.
This clause merely associates the tablespace with the new file rather than the old one. This clause does not actually change the name of the operating system file. You must change the name of the file through your operating system.
相关文档：
http://blog.csdn.net/wyzxg/article/details/5623700
- oracle数据文件的管理
- 管理oracle的表空间和数据文件
- oracle表空间和数据文件的管理！
- oracle 数据文件管理
- ORACLE 数据文件管理
- 管理数据文件（oracle）
- Oracle 数据文件管理
- oracle数据文件管理
- oracle 表空间、数据文件管理
- Oracle表空间及数据文件的操作与管理
- 修改oracle的数据文件
- Oracle数据文件的特性
- Oracle数据文件的特性
- oracle数据文件的收缩
- Oracle数据文件的大小
- Oracle的数据文件
- oracle管理表空间和数据文件
- oracle管理表空间与数据文件
- 2014百度之星1001
- 发送AT指令
- Oracle Primavera Unifier 异常
- CCCardinalSplineBy概念
- 教你如何用Android画一个几何图形
- oracle数据文件的管理
- Tomcat启动报Error listenerStart错误
- 05_android入门_sqlite数据库创建
- Tcl&NS2学习笔记
- C# 点绕某点旋转某角度
- Eclipse启动无响应 停留在Loading workbench状态
- kmGLPushMatrix/kmGLPopMatrix概念
- Quadratic Residues
- log4net配置
