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

     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.

Easy peasy Smile

Advertisements

2 thoughts on “How big is my offline datafile ?

  1. Unforunaltely this does not work with ASM

    sokrates@12.1 > create tablespace TS datafile '+DATA/TS.DBF' size 20m;
    
    Tablespace created.
    
    sokrates@12.1 > select bytes from dba_data_files where tablespace_name='TS';
    
         BYTES
    ----------
      20971520
    
    sokrates@12.1 > alter tablespace TS offline;
    
    Tablespace altered.
    
    sokrates@12.1 > select bytes from dba_data_files where tablespace_name='TS';
    
         BYTES
    ----------
    
    
    sokrates@12.1 > create or replace directory DF as '+DATA';
    
    Directory created.
    
    sokrates@12.1 > set serverout on
    sokrates@12.1 > declare
    sokrates@12.1 >   b bfile := bfilename('DF','TS.DBF');
    sokrates@12.1 > begin
    sokrates@12.1 >   dbms_output.put_line(dbms_lob.getlength(b));
    sokrates@12.1 > end;
    sokrates@12.1 > /
    declare
    *
    ERROR at line 1:
    ORA-22288: file or LOB operation GETLENGTH failed
    No such file or directory
    ORA-06512: at "SYS.DBMS_LOB", line 850
    ORA-06512: at line 4
    
    

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s