“Strange” things happen when you take a tablespace or its datafile(s) offline.
SQL> create tablespace TS datafile 'C:\ORACLE\ORADATA\NP12\TS.DBF' size 20m; Tablespace created. SQL> alter tablespace TS offline; Tablespace altered. SQL> select bytes from dba_data_files where file_name = 'C:\ORACLE\ORADATA\NP12\TS.DBF'; BYTES ----------
We lose access to the file size. So how can you tell how large that file is, without jumping into the OS or bringing the tablespace online again ?
We can treat the file as a blob.
SQL> create or replace directory DF as 'C:\ORACLE\ORADATA\NP12'; Directory created. SQL> set serverout on SQL> declare 2 b bfile := bfilename('DF','TS.DBF'); 3 begin 4 dbms_output.put_line(dbms_lob.getlength(b)); 5 end; 6 / 20979712 PL/SQL procedure successfully completed.
Unforunaltely this does not work with ASM
Ah….Good point. I wonder if there is a similar means possible (aka without heading into V$ territory)