Shulou(Shulou.com)05/31 Report--

It is believed that many inexperienced people have no idea about how to delete the data file completely in oracle. Therefore, this article summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

Physically delete data files:

Alter database datafile 8 offline drop

Alter database datafile 9 offline drop

Select file#,status$ from file$

FILE# STATUS$

--

1 2

2 2

3 2

4 2

5 2

6 1

7 2

8 2

9 2

10 1

11 1

12 1

13 1

14 1

15 1

16 1

17 1

18 1

19 1

20 1

21 1

22 1

23 1

24 1

SYS@hhw1 > delete file$ where FILE#=8

1 row deleted.

SYS@hhw1 > delete file$ where FILE#=9

1 row deleted.

SYS@hhw1 > commit

Commit complete.

SYS@hhw1 > select file_id,file_name,tablespace_name from dba_data_files

FILE_ID FILE_NAME TABLESPACE_NAME

-

1 + DATA/hhw/datafile/system.256.938483503 SYSTEM

2 + DATA/hhw/datafile/sysaux.257.938483503 SYSAUX

3 + DATA/hhw/datafile/undotbs1.258.938483503 UNDOTBS1

4 + DATA/hhw/datafile/users.259.938483505 USERS

5 + DATA/hhw/datafile/undotbs2.264.938483663 UNDOTBS2

At this point, the information in the data dictionary has been deleted, but the control file still has a record, and the control needs to be rebuilt.

Alter database backup controlfile to trace as'/ home/oracle/a.txt'

SYS@hhw1 > select file#,name from v$datafile

FILE# NAME

1 + DATA/hhw/datafile/system.256.938483503

2 + DATA/hhw/datafile/sysaux.257.938483503

3 + DATA/hhw/datafile/undotbs1.258.938483503

4 + DATA/hhw/datafile/users.259.938483505

5 + DATA/hhw/datafile/undotbs2.264.938483663

8 + DATA/hhw/datafile/users.291.970554721-still exists

9 + DATA/hhw/datafile/users.291.963587193-still exists

Alter system set cluster_database=false scope=spfile

Shutdown immediate

Startup nomount

The control file needs to be modified. Remove the data file of offline drop:

CREATE CONTROLFILE REUSE DATABASE "HHW" NORESETLOGS FORCE LOGGING ARCHIVELOG

MAXLOGFILES 192

MAXLOGMEMBERS 3

MAXDATAFILES 1024

MAXINSTANCES 32

MAXLOGHISTORY 292

LOGFILE

GROUP 1'+ DATA/hhw/onlinelog/group_1.261.938483583' SIZE 50m BLOCKSIZE 512

GROUP 2'+ DATA/hhw/onlinelog/group_2.262.938483583' SIZE 50m BLOCKSIZE 512

GROUP 3'+ DATA/hhw/onlinelog/group_3.265.938483763' SIZE 50m BLOCKSIZE 512

GROUP 4'+ DATA/hhw/onlinelog/group_4.266.938483763' SIZE 50m BLOCKSIZE 512

-- STANDBY LOGFILE

DATAFILE

'+ DATA/hhw/datafile/system.256.938483503'

'+ DATA/hhw/datafile/sysaux.257.938483503'

'+ DATA/hhw/datafile/undotbs1.258.938483503'

'+ DATA/hhw/datafile/users.259.938483505'

'+ DATA/hhw/datafile/undotbs2.264.938483663'

'+ DATA/hhw/datafile/users.267.940497227'

CHARACTER SET ZHS16GBK

Alter database open

Alter system set cluster_database=true scope=spfile

Shutdown immediate

Srvctl start database-d hhw

SYS@hhw1 > select file_id,file_name,tablespace_name from dba_data_files

FILE_ID FILE_NAME TABLESPACE_NAME

-

1 + DATA/hhw/datafile/system.256.938483503 SYSTEM

2 + DATA/hhw/datafile/sysaux.257.938483503 SYSAUX

3 + DATA/hhw/datafile/undotbs1.258.938483503 UNDOTBS1

4 + DATA/hhw/datafile/users.259.938483505 USERS

5 + DATA/hhw/datafile/undotbs2.264.938483663 UNDOTBS2

SYS@hhw1 > select file#,name from v$datafile

FILE# NAME

1 + DATA/hhw/datafile/system.256.938483503

2 + DATA/hhw/datafile/sysaux.257.938483503

3 + DATA/hhw/datafile/undotbs1.258.938483503

4 + DATA/hhw/datafile/users.259.938483505

5 + DATA/hhw/datafile/undotbs2.264.938483663

After reading the above, have you mastered the method of how to delete the data file completely in oracle? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.