oracle彻底删除部分数据,oracle彻底删除数据文件-CSDN博客

物理删除数据文件：

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

此时数据字典中的信息已删除，但控制文件仍留有记录，需要重建控制

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 -----仍存在

9 +DATA/hhw/datafile/users.291.963587193 -----仍存在

alter system set cluster_database=false scope=spfile;

shutdown immediate

startup nomount

控制文件需要修改，去掉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;