Oracle Storage in Action: Delete physical data files, oraclestorage

Oracle Storage in Action: Delete physical data files, oraclestorage

Alter tablespace XH_DM drop datafile 'f: \ DEV \ ORACLE-DATA \ ORCL \ XH_DM_1.DBF ';

SQL> ALTER TABLESPACE XH_DM
2 drop datafile 'f: \ DEV \ ORACLE-DATA \ ORCL \ xh_dm_1.dbf ';
Alter tablespace XH_DM
*
ERROR at line 1:
ORA-03262: the file is non-empty

 

1 -- Oracle Storage deletes an invalid data file and the above invalid Table 2 -- 1. view the file ID 3 select file_id, file_name, tablespace_name from dba_data_files order by file_id; 4 5 6 -- 2.G search for the segments, tables, and partition names stored on the file by ID 7 select owner, SEGMENT_NAME, FILE_ID from dba_extents where file_id = 9; 8 9 10 -- 3. permanently delete the table. If the table content is valid, first move the table to another tablespace e.g. alter table SQL _LOG move tablespace users; 11 drop table temp_user_extent purge; 12 drop table TEMP_USER_SEGMENTS purge; 13 drop table SQL _log purge; 14 15 16 -- 4. delete the DATA file 17 alter tablespace XH_DM drop datafile 'f: \ DEV \ ORACLE-DATA \ ORCL \ xh_dm_1.dbf ';

 

Tablespace altered.