SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 2
Current log sequence 4
SQL> select name from v$tablespace;
NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
USERS
TEMP
SQL> select file_name from dba_data_files;
FILE_NAME
-----------------------------------------------------------
C:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA20\USERS01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA20\SYSAUX01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA20\UNDOTBS01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA20\SYSTEM01.DBF
SQL> create tablespace test
datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA20\TEST01.DBF'
SIZE 1M;
Tablespace created.
SQL> create table test(id number) tablespace test;
Table created.
SQL> insert into test values(2);
1 row created.
SQL> insert into test values(5);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from test;
ID
----------
2
5
SQL> select tablespace_name,file_name from dba_data_files;
TABLESPACE_NAME FILE_NAME
---------------------------------------------------------------------------------------
USERS C:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA20\USERS01.DBF
SYSAUX C:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA20\SYSAUX01.DBF
UNDOTBS1 C:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA20\UNDOTBS01.DBF
SYSTEM C:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA20\SYSTEM01.DBF
TEST C:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA20\TEST01.DBF
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
Delete C:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA20\TEST01.DBF
SQL> startup;
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1247876 bytes
Variable Size 83887484 bytes
Database Buffers 75497472 bytes
Redo Buffers 7139328 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA20\TEST01.DBF'
SQL> alter database create datafile
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA20\TEST01.DBF';
Database altered.
SQL> recover datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA20\TEST01.DBF';
Media recovery complete.
SQL> alter database open;
Database altered.
SQL> select * from test;
ID
----------
2
5