issue :
Drop tablespace including contents and datafiles . The space is not released at disk levelSQL> select distinct owner from dba_segments where tablespace_name='APPCHE_DATA';
no rows selected
SQL> drop tablespace APPCHE_DATA INCLUDING CONTENTS AND DATAFILES ;
Tablespace dropped.
SQL> select BYTES/1024/1024,FILE_NAME,autoextensible,maxbytes/1024/1024 as mb from dba_Data_files where TABLESPACE_NAME='APPCHE_INDEX';
BYTES/1024/1024 FILE_NAME AUT MB
--------------- ---------------------------------------------------------------------------------------------------- --- ----------
100 /appl/oradata/MyappP/db01/MyappP_APPCHE_index_01.dbf YES 8192
100 /appl/oradata/MyappP/db02/MyappP_APPCHE_index_02.dbf YES 8192
100 /appl/oradata/MyappP/db03/MyappP_APPCHE_index_03.dbf YES 8192
100 /appl/oradata/MyappP/db04/MyappP_APPCHE_index_04.dbf YES 8192
SQL> select distinct owner from dba_segments where tablespace_name='APPCHE_INDEX';
no rows selected
SQL> drop tablespace APPCHE_INDEX INCLUDING CONTENTS AND DATAFILES ;
Tablespace dropped.
SQL> exit
> df -h |grep db
3.9G 804M 3.1G 21% /appl/oradata/xdb01
3.9G 804M 3.1G 21% /appl/oradata/xdb02
/dev/mapper/vg04-db01
50G 35G 15G 71% /appl/oradata/MyappP/db01
/dev/mapper/vg06-db02
50G 35G 15G 71% /appl/oradata/MyappP/db02
/dev/mapper/vg07-db03
50G 35G 15G 71% /appl/oradata/MyappP/db03
/dev/mapper/vg08-db04
50G 35G 15G 71% /appl/oradata/MyappP/db04
solution:
check whether the removed files are waiting using lsof command. In our case one of the process is using those files
> /usr/sbin/lsof | grep deleted
oracle_64 64540 oracle 282u REG 253,3 22548586496 20 /appl/oradata/MyappP/db02/MyappP_APPCHE_data_02.dbf (deleted)
oracle_64 64540 oracle 283u REG 253,6 22548586496 21 /appl/oradata/MyappP/db01/MyappP_APPCHE_data_01.dbf (deleted)
oracle_64 64540 oracle 284u REG 253,1 22548586496 20 /appl/oradata/MyappP/db04/MyappP_APPCHE_data_04.dbf (deleted)
oracle_64 64540 oracle 285u REG 253,2 22548586496 20 /appl/oradata/MyappP/db03/MyappP_APPCHE_data_03.dbf (deleted)
oracle_64 64540 oracle 286u REG 253,3 104865792 21 /appl/oradata/MyappP/db02/MyappP_APPCHE_index_02.dbf (deleted)
oracle_64 64540 oracle 287u REG 253,6 104865792 22 /appl/oradata/MyappP/db01/MyappP_APPCHE_index_01.dbf (deleted)
oracle_64 64540 oracle 288u REG 253,1 104865792 21 /appl/oradata/MyappP/db04/MyappP_APPCHE_index_04.dbf (deleted)
oracle_64 64540 oracle 289u REG 253,2 104865792 21 /appl/oradata/MyappP/db03/MyappP_APPCHE_index_03.dbf (deleted)
Now find the session and get the connection closed
oracle@ovmp1[LSNR_ADMIN]:/appl/oradata/MyappP/db01
> . oraenv
ORACLE_SID = [LSNR_ADMIN] ? MyappP
The Oracle base remains unchanged with value /appl/oracle
oracle@ovmp1[MyappP]:/appl/oradata/MyappP/db01
> sql
SQL*Plus: Release 12.1.0.2.0 Production on Thu Sep 21 13:33:17 2017
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
SQL> select addr from v$process where spid=64540 ;
ADDR
----------------
00000000DE25ACF0
SQL> select sid,username,program,machine from v$session where paddr='00000000DE25ACF0';
SID USERNAME
---------- ------------------------------
PROGRAM
------------------------------------------------
MACHINE
----------------------------------------------------------------
37 SYS
sqlplus@ovmp1.telecom.tcnz.net (TNS V1-V3)
ovmp1.telecom.tcnz.net
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
oracle@ovmp1[MyappP]:/appl/oradata/MyappP/db01
This will release the space back
