概述
数据文件，这一节还是比较简单的，都是一些简单的操作实验。
A physical operating system file on disk that was created by Oracle and contains data structures such as tables and indexes. A datafile can only belong to one database.
一、给表空间添加数据文件
1、新建一个表空间
SQL> create tablespace testdb datafile '/u01/app/oracle/oradata/db/testdb01.dbf' size 10M;
Tablespace created.2、新建一个临时表空间
SQL> create temporary tablespace tempts2 tempfile '/u01/app/oracle/oradata/db/tempts2.dbf' size 10M;
Tablespace created.3、添加一个数据文件
SQL> alter tablespace testdb add datafile '/u01/app/oracle/oradata/db/testdb02.dbf' size 10M;
Tablespace altered.4、添加一个临时表空间数据文件
SQL> alter tablespace tempts2 add tempfile '/u01/app/oracle/oradata/db/tempts201.dbf' size 10M;
Tablespace altered.二、改变数据文件大小
1、打开或关闭数据文件的自动扩展
SQL> select file_id,file_name,autoextensible from dba_data_files;
FILE_ID FILE_NAME AUT
---------- -------------------------------------------------- ---
1 /u01/app/oracle/oradata/db/system01.dbf NO
2 /u01/app/oracle/oradata/db/sysaux01.dbf NO
3 /u01/app/oracle/oradata/db/undotbs01.dbf NO
4 /u01/app/oracle/oradata/db/users01.dbf NO
5 /u01/app/oracle/oradata/db/testdb01.dbf NO
6 /u01/app/oracle/oradata/db/testdb02.dbf NO
6 rows selected.
-->开启数据文件的自动扩展
SQL> alter database datafile 5 autoextend on;
Database altered.
SQL> alter database datafile 6 autoextend on next 512M maxsize 1G;
Database altered.
SQL> select file_id,file_name,autoextensible from dba_data_files;
FILE_ID FILE_NAME AUT
---------- -------------------------------------------------- ---
1 /u01/app/oracle/oradata/db/system01.dbf NO
2 /u01/app/oracle/oradata/db/sysaux01.dbf NO
3 /u01/app/oracle/oradata/db/undotbs01.dbf NO
4 /u01/app/oracle/oradata/db/users01.dbf NO
5 /u01/app/oracle/oradata/db/testdb01.dbf YES
6 /u01/app/oracle/oradata/db/testdb02.dbf YES
6 rows selected.
-->关闭数据文件的自动扩展
SQL> alter database datafile 5 autoextend off;
Database altered.
SQL> alter database datafile 6 autoextend off;
Database altered.
SQL> select file_id,file_name,autoextensible from dba_data_files;
FILE_ID FILE_NAME AUT
---------- -------------------------------------------------- ---
1 /u01/app/oracle/oradata/db/system01.dbf NO
2 /u01/app/oracle/oradata/db/sysaux01.dbf NO
3 /u01/app/oracle/oradata/db/undotbs01.dbf NO
4 /u01/app/oracle/oradata/db/users01.dbf NO
5 /u01/app/oracle/oradata/db/testdb01.dbf NO
6 /u01/app/oracle/oradata/db/testdb02.dbf NO
6 rows selected.
.2、手动调整数据文件大小
-->不建将数据文件调小，可能导致数据丢失
SQL> alter database datafile 5 resize 20M;
Database altered.三、数据文件状态调整
-->数据文件有online和offline两种状态
-->在归档模式下
SQL> col name for a50
SQL> select file#,name,status from v$datafile;
FILE# NAME STATUS
---------- -------------------------------------------------- -------
1 /u01/app/oracle/oradata/db/system01.dbf SYSTEM
2 /u01/app/oracle/oradata/db/sysaux01.dbf ONLINE
3 /u01/app/oracle/oradata/db/undotbs01.dbf ONLINE
4 /u01/app/oracle/oradata/db/users01.dbf ONLINE
5 /u01/app/oracle/oradata/db/testdb01.dbf ONLINE
6 /u01/app/oracle/oradata/db/testdb02.dbf ONLINE
6 rows selected.
SQL> alter database datafile 5 offline;
Database altered.
SQL> alter database datafile 6 offline;
Database altered.
SQL> select file#,name,status from v$datafile;
FILE# NAME STATUS
---------- -------------------------------------------------- -------
1 /u01/app/oracle/oradata/db/system01.dbf SYSTEM
2 /u01/app/oracle/oradata/db/sysaux01.dbf ONLINE
3 /u01/app/oracle/oradata/db/undotbs01.dbf ONLINE
4 /u01/app/oracle/oradata/db/users01.dbf ONLINE
5 /u01/app/oracle/oradata/db/testdb01.dbf RECOVER
6 /u01/app/oracle/oradata/db/testdb02.dbf RECOVER
6 rows selected.
-->将状态修改会online状态
SQL> alter database datafile 5 online;
alter database datafile 5 online
*
ERROR at line 1:
ORA-01113: file 5 needs media recovery
ORA-01110: data file 5: '/u01/app/oracle/oradata/db/testdb01.dbf'
SQL> recover datafile 5;
Media recovery complete.
SQL> alter database datafile 5 online;
Database altered.
SQL> recover datafile 6;
Media recovery complete.
SQL> alter database datafile 6 online;
Database altered.
SQL> select file#,name,status from v$datafile;
FILE# NAME STATUS
---------- -------------------------------------------------- -------
1 /u01/app/oracle/oradata/db/system01.dbf SYSTEM
2 /u01/app/oracle/oradata/db/sysaux01.dbf ONLINE
3 /u01/app/oracle/oradata/db/undotbs01.dbf ONLINE
4 /u01/app/oracle/oradata/db/users01.dbf ONLINE
5 /u01/app/oracle/oradata/db/testdb01.dbf ONLINE
6 /u01/app/oracle/oradata/db/testdb02.dbf ONLINE
6 rows selected.
-->表空间offline操作将导致表空间下所有数据文件offline
SQL> alter tablespace testdb offline;
Tablespace altered.
SQL> select file#,name,status from v$datafile;
FILE# NAME STATUS
---------- -------------------------------------------------- -------
1 /u01/app/oracle/oradata/db/system01.dbf SYSTEM
2 /u01/app/oracle/oradata/db/sysaux01.dbf ONLINE
3 /u01/app/oracle/oradata/db/undotbs01.dbf ONLINE
4 /u01/app/oracle/oradata/db/users01.dbf ONLINE
5 /u01/app/oracle/oradata/db/testdb01.dbf OFFLINE
6 /u01/app/oracle/oradata/db/testdb02.dbf OFFLINE
6 rows selected.
-->将表空间online，数据文件也将同步online.
SQL> alter tablespace testdb online;
Tablespace altered.
SQL> select file#,name,status from v$datafile;
FILE# NAME STATUS
---------- -------------------------------------------------- -------
1 /u01/app/oracle/oradata/db/system01.dbf SYSTEM
2 /u01/app/oracle/oradata/db/sysaux01.dbf ONLINE
3 /u01/app/oracle/oradata/db/undotbs01.dbf ONLINE
4 /u01/app/oracle/oradata/db/users01.dbf ONLINE
5 /u01/app/oracle/oradata/db/testdb01.dbf ONLINE
6 /u01/app/oracle/oradata/db/testdb02.dbf ONLINE
6 rows selected.
-->在非归档模式下
ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/users03.dbf' OFFLINE DROP;四、修改数据文件名称和变更数据文件路径
1、修改一个表空间的数据文件名称or路径
SQL> select tablespace_name,file_name from dba_data_files;
TABLESPACE_NAME FILE_NAME
------------------------------ --------------------------------------------------
SYSTEM /u01/app/oracle/oradata/db/system01.dbf
SYSAUX /u01/app/oracle/oradata/db/sysaux01.dbf
UNDOTBS1 /u01/app/oracle/oradata/db/undotbs01.dbf
USERS /u01/app/oracle/oradata/db/users01.dbf
TESTDB /u01/app/oracle/oradata/db/testdb01.dbf
TESTDB /u01/app/oracle/oradata/db/testdb02.dbf
6 rows selected.
-->将表空间offline
SQL> alter tablespace testdb offline normal;
Tablespace altered.
-->使用系统命令重命名数据文件
SQL> ! mv /u01/app/oracle/oradata/db/testdb01.dbf /u01/app/oracle/oradata/db/testdb1.dbf
SQL> ! mv /u01/app/oracle/oradata/db/testdb02.dbf /u01/app/oracle/oradata/db/testdb2.dbf
-->修改datafile名称
SQL> alter tablespace testdb rename datafile '/u01/app/oracle/oradata/db/testdb01.dbf','/u01/app/oracle/oradata/db/testdb02.dbf' to '/u01/app/oracle/oradata/db/testdb1.dbf','/u01/app/oracle/oradata/db/testdb2.dbf';
Tablespace altered.
-->将表空间online
SQL> alter tablespace testdb online;
Tablespace altered.
SQL> select tablespace_name,file_name from dba_data_files;
TABLESPACE_NAME FILE_NAME
------------------------------ --------------------------------------------------
SYSTEM /u01/app/oracle/oradata/db/system01.dbf
SYSAUX /u01/app/oracle/oradata/db/sysaux01.dbf
UNDOTBS1 /u01/app/oracle/oradata/db/undotbs01.dbf
USERS /u01/app/oracle/oradata/db/users01.dbf
TESTDB /u01/app/oracle/oradata/db/testdb1.dbf
TESTDB /u01/app/oracle/oradata/db/testdb2.dbf
6 rows selected.2、多表空间数据文件迁移
在这里先说一下思路，后续将会有专门的章节来讨论这块内容。
1）停库
2）使用系统命令cp 或者 rman copy 命令将数据文件拷贝到新的路径下
3）修改控制文件中数据文件的路径信息
alter database rename file '' to '';
or
run{
set newname datafile '' to '';
switch datafile '';
}
五、删除数据文件
-->数据文件的删除还是比较危险的操作，在生产系统上一定不允许操作的。
SQL> select tablespace_name,file_name from dba_temp_files;
TABLESPACE_NAME FILE_NAME
------------------------------ --------------------------------------------------
TEMPTS1 /u01/app/oracle/oradata/db/temp01.dbf
TEMPTS2 /u01/app/oracle/oradata/db/tempts2.dbf
TEMPTS2 /u01/app/oracle/oradata/db/tempts201.dbf
SQL>
SQL>
SQL> alter database tempfile '/u01/app/oracle/oradata/db/tempts201.dbf' drop including datafiles;
Database altered.
SQL> select tablespace_name,file_name from dba_temp_files;
TABLESPACE_NAME FILE_NAME
------------------------------ --------------------------------------------------
TEMPTS1 /u01/app/oracle/oradata/db/temp01.dbf
TEMPTS2 /u01/app/oracle/oradata/db/tempts2.dbf--END--
最后
以上就是害羞哈密瓜为你收集整理的oracle-datafile管理的全部内容，希望文章能够帮你解决oracle-datafile管理所遇到的程序开发问题。
如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。
本图文内容来源于网友提供，作为学习参考使用，或来自网络收集整理，版权属于原作者所有。
发表评论 取消回复