Oracle deletes data files

Before details, we must make it clear that Oracle does not provide a method to delete data files like tables and views. data files are part of the tablespace, therefore, the tablespace cannot be removed.

 

I. How to Use offline data files

Non-archive mode: alter database datafile '...' offline drop;

Archive mode: alter database datafile '...' offline;

Note:

1) The above command only OFFLINE the data file, rather than deleting the data file in the database. The data file information still exists in the control file type. Query v $ datafile. The file is still displayed.

2) In archive mode, the offline and offline drop effects are the same.

3) after offline, objects on this datafile cannot be accessed.

4) In noarchivelog mode, as long as the online redo log is not overwritten, you can perform online operations after the file is recovered.

 

Actual use cases:

Solution for failing to enter the system after deleting the data file directly

 

Under normal circumstances, the correct method to delete a tablespace is:

Drop tablespace tablespace_name including contents and datafiles;

 

If the data file is deleted without using the preceding command, the database cannot be opened.

 

If the data file is deleted directly

When a common user logs on, an error is returned:

ORA-01033: ORACLE initialization or shutdown in progress

Sys users can log on normally

However, when the operation is performed (SELECT count (1) FROM user_tables), an error is returned:

ORA-01219: Database not open: Allow queries only in fixed tables/Views

If you run the alter database open command to open the database, the following error is returned:

ORA-01157: unable to identify/lock data file 12-see DBWR trace file

ORA-01110: Data File 12: 'e: \ ORACLE \ PRODUCT \ 10.2.0 \ ORADATA \ ORCL \ tstest001.dbf'

 

This data file is not found in the database.

Because the data file is physically deleted without being offline, oracle data inconsistency occurs, and startup fails.

You can solve this problem by using the following methods:

 

Solution:

Sqlplus sys/orcl @ orcl as sysdba;

SQL> alter database datafile 'e: \ ORACLE \ PRODUCT \ 10.2.0 \ ORADATA \ ORCL \ tstest001.dbf' offline drop;

SQL> alter database open;

SQL> drop tablespace CTBASEDATA;

 

2. Oracle 10G R2, you can use: Alter tablespace tablespace_name drop datafile file_name; to delete an empty data file, and the corresponding data dictionary information will also be cleared:

 

Sys @ ORCL> select file_id, file_name, tablespace_name from dba_data_files

2 where tablespace_name = 'users ';

FILE_ID FILE_NAME TABLESPACE_NAME

---------------------------------------------------------------------

4/u01/app/oracle/oradata/orcl/users01.dbf USERS

 

Sys @ ORCL> alter tablespace users add datafile

2'/u01/app/oracle/oradata/orcl/users02.dbf' size 5 M autoextend off;

Tablespace altered.

Sys @ ORCL> select file_id, file_name, tablespace_name from dba_data_files

2 where tablespace_name = 'users ';

FILE_ID FILE_NAME TABLESPACE_NAME

----------------------------------------------------------------------

4/u01/app/oracle/oradata/orcl/users01.dbf USERS

9/u01/app/oracle/oradata/orcl/users02.dbf USERS

 

Sys @ ORCL> drop table test;

Table dropped.

Sys @ ORCL> create table test tablespace users

2

3 select * from dba_objects;

Table created.

Sys @ ORCL> select SEGMENT_NAME, FILE_ID, BLOCKS from dba_extents

2 where file_id = 9;

SEGMENT_NAME FILE_ID BLOCKS

--------------------------------------------------

TEST 9 8

TEST 9 8

TEST 9 8

TEST 9 8

TEST 9 8

TEST 9 8

TEST 9 8

TEST 9 8

TEST 9 8

TEST 9 8

TEST 9 8

TEST 9 8

TEST 9 8

TEST 9 8

TEST 9 8

TEST 9 128

TEST 9 128

 

17 rows selected.

Sys @ ORCL> alter table test move tablespace PERFSTAT; -- move the table to another tablespace

Table altered.

Sys @ ORCL> select SEGMENT_NAME, FILE_ID, BLOCKS from dba_extents

2 where file_id = 9;

No rows selected

Sys @ ORCL> alter tablespace users drop datafile

2'/u01/app/oracle/oradata/orcl/users02.dbf ';

Tablespace altered.

Sys @ ORCL> select file_id, file_name, tablespace_name from dba_data_files

2 where tablespace_name = 'users ';

FILE_ID FILE_NAME TABLESPACE_NAME

------------------------------------------------------------------------

4/u01/app/oracle/oradata/orcl/users01.dbf USERS

 

3. oracle 10 Gb can delete files in temporary tablespace

Alter database tempfile '/home/oracle/temp01.dbf' drop including datafiles;

 

Differences between alter database and ALTER TABLESPACE OFFLINE

1. There are two ways to use DataFile offline or online:

① Alter database statement modify a separate DataFile

② Alter tablespace statement to modify all datafiles

1. Change the DataFile status in ARCHIVRLOG Mode

Alter database datafile '/u02/oracle/rbdb1/stuff01.dbf' ONLINE;

Alter database datafile '/u02/oracle/rbdb1/stuff01.dbf' OFFLINE;

Or use the file number:

Alter database datafile 5 ONLINE;

Alter database datafile 5 OFFLINE;

Note: You can use alter database to change DataFile only in ARCHIVELOG mode.

 

2. Offline DataFile in NOARCHIVELOG Mode

In NOARCHIVELOG mode, data loss occurs when the data file is OFFLINE. Therefore, you can only use the option with the DATAFILE and offline drop clauses under the alter database statement to directly cancel the DataFile, for example, if the DataFile contains only temporary data segments and is not backed up

 

Alter database datafile '/u02/oracle/rbdb1/users3.dbf' offline drop;

3. Modify the availability of all DataFile or TempFile in TableSpace

Alter tablespace... DATAFILE {ONLINE | OFFLINE}

Alter tablespace... TEMPFILE {ONLINE | OFFLINE}

 

Note: modify all data files in a TableSpace, but the status of TableSpace does not change.

Summary:

① Alter tablespace can be released when the database is loaded without opening

② When it comes to system tablespace, undo tablespace, and default temporary tablespace, it must be an unopened Database

③ The full name of the file must be entered in the alter database datafile statement.

 

Ii. Differences between tablespace and data files offline

1. alter tablespace... OFFLINE

Does a checkpoint on the datafiles

Takes the datafiles offline

When the tablespace is Offline, the SCN of the data file will be frozen, and when the tablespace is offline/online, the file checkpoint will occur again, so that the single data file SCN is inconsistent with other database problems.

When the tablespace is online, Oracle will obtain the current SCN, unfreeze the offline file SCN, and synchronize with the current SCN.

Tablespace offline has several options for normal, temporary, immediate, for recovery, but not in datafile.

 

2. alter database datafile... OFFLINE

A simple offline datafile will not trigger the file checkpoint. It will only trigger the file checkpoint when targeting the offline tablespace. This is why online datafile requires media recovery instead of online tablespace.

Note: You can use alter database to change DataFile only in ARCHIVELOG mode.