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 |