I am not sure what's so hard to understand, even the note from Metalink is pasted here and it's clear stating
When you offline drop a datafile that datafile is not removed from dictionary, when Oracle has to allocate extents it will still try to allocate extents thereCode:NOTE: The ALTER DATABASE DATAFILE OFFLINE DROP command, is not meant to allow you to remove a datafile. What the command really means is that you are offlining the datafile with the intention of dropping the tablespace.
as you can see even you offline drop the datafile the file still exists in data dictionary. What does that mean? It means Oracle will try to use free extents in the drop file even it does not exist. What is the fix of this? Drop the tablespaceCode:SQL> conn / as sysdba Connected. SQL> create tablespace dropme 2 datafile 'D:\ORACLE\ORADATA\DEV901\DROPME01.DBF' size 5M, 3 'D:\ORACLE\ORADATA\DEV901\DROPME02.DBF' size 5M 4 extent management dictionary 5 minimum extent 128k 6 default storage (initial 128k 7 next 128k 8 pctincrease 0); Tablespace created. SQL> create table objects tablespace dropme as select * from dba_objects; Table created. SQL> select * from fet$ where file# in (11, 12); TS# FILE# BLOCK# LENGTH ---------- ---------- ---------- ---------- 15 12 450 831 15 11 482 799 SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area 105672596 bytes Fixed Size 282516 bytes Variable Size 71303168 bytes Database Buffers 33554432 bytes Redo Buffers 532480 bytes Database mounted. SQL> alter database datafile 'D:\ORACLE\ORADATA\DEV901\DROPME02.DBF' offline drop Database altered. SQL> alter database open; Database altered. SQL> select * from fet$ where file# in (11, 12); TS# FILE# BLOCK# LENGTH ---------- ---------- ---------- ---------- 15 12 450 831 15 11 482 799 SQL> select file#, tablespace_name, name, b.status 2 from dba_data_files a, v$datafile b 3 where a.file_id = b.file#; FILE# TABLESPACE_NAME NAME STATUS ---------- ------------------------------ -------------------------------------------------- ------- 1 SYSTEM D:\ORACLE\ORADATA\DEV901\SYSTEM01.DBF SYSTEM 2 UNDOTBS D:\ORACLE\ORADATA\DEV901\UNDOTBS01.DBF ONLINE 3 CWMLITE D:\ORACLE\ORADATA\DEV901\CWMLITE01.DBF ONLINE 4 DRSYS D:\ORACLE\ORADATA\DEV901\DRSYS01.DBF ONLINE 5 EXAMPLE D:\ORACLE\ORADATA\DEV901\EXAMPLE01.DBF ONLINE 6 INDX D:\ORACLE\ORADATA\DEV901\INDX01.DBF ONLINE 7 TOOLS D:\ORACLE\ORADATA\DEV901\TOOLS01.DBF ONLINE 8 USERS D:\ORACLE\ORADATA\DEV901\USERS01.DBF ONLINE 9 OEM_REPOSITORY D:\ORACLE\ORADATA\DEV901\OEM_REPOSITORY.DBF ONLINE 10 DATA_SMALL D:\ORACLE\ORADATA\DEV901\DATA_SMALL01.DBF ONLINE 11 DROPME D:\ORACLE\ORADATA\DEV901\DROPME01.DBF ONLINE 12 DROPME D:\ORACLE\ORADATA\DEV901\DROPME02.DBF OFFLINE SQL> drop table objects; Table dropped. SQL> select * from fet$ where file# in (11, 12); TS# FILE# BLOCK# LENGTH ---------- ---------- ---------- ---------- 15 12 450 831 15 11 386 32 15 11 482 799 15 12 386 32 15 12 258 32 15 11 418 32 15 12 418 32 15 11 450 32 15 11 258 32 15 11 290 32 15 12 290 32 15 11 322 32 15 12 322 32 15 11 354 32 15 12 354 32 15 11 130 32 15 12 130 32 15 11 162 32 15 12 162 32 15 11 194 32 15 12 194 32 15 11 226 32 15 12 226 32 15 11 2 32 15 12 2 32 15 11 34 32 15 12 34 32 15 11 66 32 15 12 66 32 15 11 98 32 15 12 98 32
Try it yourself




Reply With Quote