Learning is not a spectator sport

How big is my offline datafile ?

December 18, 2015

1–2 minutes

“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

2 responses to “How big is my offline datafile ?”

Matthias Rogel

December 18, 2015 at 5:59 pm

Unforunaltely this does not work with ASM
[code language=”sql”]
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

[/code]

Loading…

Reply
connormcdonald

December 19, 2015 at 8:14 am

Ah….Good point. I wonder if there is a similar means possible (aka without heading into V$ territory)

Loading…

Reply