drop a non-empty datafile

SQL>  select * from dba_data_files where tablespace_name='TESTBS';

FILE_NAME                                                                      
--------------------------------------------------------------------------------
   FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS         
---------- ------------------------------ ---------- ---------- ---------      
RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS     
------------ --- ---------- ---------- ------------ ---------- -----------     
ONLINE_                                                                        
-------                                                                        
/oracle/UAT/sapdata1/system_1/test1.dbf                                        
        74 TESTBS                           20971520       2560 AVAILABLE      
          74 NO           0          0            0   20905984        2552     
ONLINE                                                                         
                                                                               
/oracle/UAT/sapdata1/system_1/test2.dbf                                        
        75 TESTBS                           20971520       2560 AVAILABLE      
          75 NO           0          0            0   20905984        2552     
ONLINE                                                                         
                                                                               
-- This is an empty tablespace, now create a table in it.
SQL> create user user1 identified by user1 default tablespace testbs;

User created.

SQL> grant dba to user1;

Grant succeeded.

SQL> conn user1/user1
Connected.
SQL> create table t1 as select * from dba_objects;

Table created.

SQL> desc dba_extents;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                              VARCHAR2(30)
 SEGMENT_NAME                                       VARCHAR2(81)
 PARTITION_NAME                                     VARCHAR2(30)
 SEGMENT_TYPE                                       VARCHAR2(18)
 TABLESPACE_NAME                                    VARCHAR2(30)
 EXTENT_ID                                          NUMBER
 FILE_ID                                            NUMBER
 BLOCK_ID                                           NUMBER
 BYTES                                              NUMBER
 BLOCKS                                             NUMBER
 RELATIVE_FNO                                       NUMBER

SQL> select distinct file_id from user_extents;
select distinct file_id from user_extents
                *
ERROR at line 1:
ORA-00904: "FILE_ID": invalid identifier


SQL> desc user_extents
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SEGMENT_NAME                                       VARCHAR2(81)
 PARTITION_NAME                                     VARCHAR2(30)
 SEGMENT_TYPE                                       VARCHAR2(18)
 TABLESPACE_NAME                                    VARCHAR2(30)
 EXTENT_ID                                          NUMBER
 BYTES                                              NUMBER
 BLOCKS                                             NUMBER
-- Interesting no FILE_ID in user_extents.

SQL> select file_id,count(*)  from dba_extents where owner='USER1' group by file_id;

   FILE_ID   COUNT(*)                                                          
---------- ----------                                                          
        74         24                                                          
        75          9                                                          

--attempt to drop the 2nd datafile

SQL> alter tablespace testbs drop datafile '/oracle/UAT/sapdata1/system_1/test2.dbf';
alter tablespace testbs drop datafile '/oracle/UAT/sapdata1/system_1/test2.dbf'
*
ERROR at line 1:
ORA-03262: the file is non-empty


SQL> drop table t1;

Table dropped.

SQL> show parameter recyclebin

NAME                                 TYPE        VALUE                         
------------------------------------ ----------- ------------------------------
recyclebin                           string      off                           

-- repeat the test with recyclebin is on

SQL> alter system set recyclebin=on;

System altered.

SQL>  create table t1 as select * from dba_objects;

Table created.

SQL>
SQL> select file_id,count(*)  from dba_extents where owner='USER1' group by file_id;

   FILE_ID   COUNT(*)                                                          
---------- ----------                                                          
        74         24                                                          
        75          9                                                          

SQL> drop table t1;

Table dropped.

SQL> select file_id,count(*)  from dba_extents where owner='USER1' group by file_id;

no rows selected

SQL> alter tablespace testbs drop datafile '/oracle/UAT/sapdata1/system_1/test2.dbf';
alter tablespace testbs drop datafile '/oracle/UAT/sapdata1/system_1/test2.dbf'
*
ERROR at line 1:
ORA-03262: the file is non-empty


SQL> purge recyclebin;

Recyclebin purged.

SQL>  alter tablespace testbs drop datafile '/oracle/UAT/sapdata1/system_1/test2.dbf';

Tablespace altered.

SQL> select * from dba_data_files where tablespace_name='TESTBS';

FILE_NAME                                                                      
--------------------------------------------------------------------------------
   FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS         
---------- ------------------------------ ---------- ---------- ---------      
RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS     
------------ --- ---------- ---------- ------------ ---------- -----------     
ONLINE_                                                                        
-------                                                                        
/oracle/UAT/sapdata1/system_1/test1.dbf                                        
        74 TESTBS                           20971520       2560 AVAILABLE      
          74 NO           0          0            0   20905984        2552     
ONLINE                                                                         
                                                                               

-- continue to test what happens to DEFAULT_TABLESPACE in DBA_USERS when the tablespace is dropped.



SQL> prompt to see default tablespace change after the tablespace is dropped
to see default tablespace change after the tablespace is dropped
SQL> conn / as sysdba
Connected.
SQL> select default_tablespace from dba_users where username='USER1';

DEFAULT_TABLESPACE                                                             
------------------------------                                                 
TESTBS                                                                         

SQL> drop tablespace testbs including contents and datafiles;

Tablespace dropped.

SQL> select default_tablespace from dba_users where username='USER1';

DEFAULT_TABLESPACE                                                             
------------------------------                                                 
TESTBS                                                                         

SQL> conn user1/user1
Connected.
SQL>  create table t1 as select * from dba_users;
 create table t1 as select * from dba_users
                                  *
ERROR at line 1:
ORA-00959: tablespace 'TESTBS' does not exist



SQL>  alter user user1 default tablespace system;

User altered.

SQL> create table t1 as select * from dba_users;

Table created.

SQL> show user;
USER is "USER1"
SQL> conn  / as sysdba
Connected.
SQL> drop user user1 cascade;

User dropped.

SQL> spool off