SQL> create tablespace tbs_1 DATAFILE '/u03/oradata/tbs_01.dbf' size 5M;
Tablespace created.
SQL> alter tablespace tbs_1 add datafile '/u03/oradata/tbs_02.dbf' size 1G;
Tablespace altered.
SQL> > create user zyj identified by zyj default tablespace tbs_1;
SP2-0734: unknown command beginning "> create u..." - rest of line ignored.
SQL> create user zyj identified by zyj default tablespace tbs_1;
User created.
SQL> grant dba to zyj;
Grant succeeded.
SQL> select file_id,file_name from dba_data_files where tablespace_name='TBS_1';
FILE_ID FILE_NAME
---------- ------------------------------
19 /u03/oradata/tbs_01.dbf
20 /u03/oradata/tbs_02.dbf
SQL> select owner,segment_name from dba_Extents where file_id=19;
no rows selected
SQL> select owner,segment_name from dba_Extents where file_id=20;
no rows selected
SQL> conn zyj/zyj
Connected.
SQL> create table test as select * from dba_objects;
Table created.
SQL> select distinct owner,segment_name from dba_Extents where file_id=19;
OWNER SEGMENT_NAME
------------------------------ ---------------------------------------------------------------------------------
ZYJ TEST
SQL> select distinct owner,segment_name from dba_Extents where file_id=20;
OWNER SEGMENT_NAME
------------------------------ ---------------------------------------------------------------------------------
ZYJ TEST
SQL> select count(*) from test;
COUNT(*)
----------
51532
SQL> alter database datafile '/u03/oradata/tbs_02.dbf' offline drop;
Database altered.
SQL> select count(*) from test;
select count(*) from test
*
ERROR at line 1:
ORA-00376: file 20 cannot be read at this time
ORA-01110: data file 20: '/u03/oradata/tbs_02.dbf'
SQL> select distinct owner,segment_name from dba_Extents where file_id=20;
no rows selected
SQL>
[oracle@jhoa oradata]$ rm tbs_02.dbf
[oracle@jhoa oradata]$
SQL> alter database create datafile '/u03/oradata/tbs_02.dbf' as '/u03/tbs_02.dbf';
SQL> alter database create datafile '/u03/oradata/tbs_02.dbf' as '/u03/tbs_02.dbf';
Database altered.
SQL> recover datafile 20;
ORA-00279: change 57693460721 generated at 08/21/2014 08:54:36 needed for
thread 1
ORA-00289: suggestion : /u03/arch/1_18253_760715863.dbf
ORA-00280: change 57693460721 for thread 1 is in sequence #18253
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00279: change 57693460790 generated at 08/21/2014 08:56:15 needed for
thread 1
ORA-00289: suggestion : /u03/arch/1_18254_760715863.dbf
ORA-00280: change 57693460790 for thread 1 is in sequence #18254
ORA-00278: log file '/u03/arch/1_18253_760715863.dbf' no longer needed for this
recovery
ORA-00279: change 57693460792 generated at 08/21/2014 08:56:17 needed for
thread 1
ORA-00289: suggestion : /u03/arch/1_18255_760715863.dbf
ORA-00280: change 57693460792 for thread 1 is in sequence #18255
ORA-00278: log file '/u03/arch/1_18254_760715863.dbf' no longer needed for this
recovery
ORA-00279: change 57693460795 generated at 08/21/2014 08:56:21 needed for
thread 1
ORA-00289: suggestion : /u03/arch/1_18256_760715863.dbf
ORA-00280: change 57693460795 for thread 1 is in sequence #18256
ORA-00278: log file '/u03/arch/1_18255_760715863.dbf' no longer needed for this
recovery
ORA-00279: change 57693460797 generated at 08/21/2014 08:56:22 needed for
thread 1
ORA-00289: suggestion : /u03/arch/1_18257_760715863.dbf
ORA-00280: change 57693460797 for thread 1 is in sequence #18257
ORA-00278: log file '/u03/arch/1_18256_760715863.dbf' no longer needed for this
recovery
ORA-00279: change 57693460800 generated at 08/21/2014 08:56:27 needed for
thread 1
ORA-00289: suggestion : /u03/arch/1_18258_760715863.dbf
ORA-00280: change 57693460800 for thread 1 is in sequence #18258
ORA-00278: log file '/u03/arch/1_18257_760715863.dbf' no longer needed for this
recovery
Log applied.
Media recovery complete.
SQL> alter database datafile 20 online;
Database altered.
SQL> col file_name format a30
SQL> /
FILE_NAME STATUS
------------------------------ ---------
/u03/oradata/tbs_01.dbf AVAILABLE
/u03/tbs_02.dbf AVAILABLE
alter database create datafile '/u03/oradata/tbs_02.dbf' as '/u03/tbs_02.dbf';
此时数据文件有原来的'/u03/oradata/tbs_02.dbf' 换成了'/u03/tbs_02.dbf'
alter database create datafile作用:
创建一个新的空的数据文件来代替老的使用的数据文件,来创建没有备份的数据文件,前提是DB开启归档模式且保留数据文件创建以来的归档日志可用
这种通常用在数据文件损坏,但是没有备份(数据文件)的情况下,但是有归档日志,可以通过归档日志来恢复数据文件！也就是说产生一个新的,
这个适合数据文件所在的磁盘损坏,不能恢复到原来的目录,而在其他路径下产生一个新的数据文件。