Top 60 Oracle Blogs

Recent comments

How big is my offline datafile ?

“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';


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  /

PL/SQL procedure successfully completed.

Easy peasy Smile