Friday, November 1, 2013

How To Find Free Space in DBFS (Oracle Database File System)

To find free space in DBFS (Oracle Database File System) use Oracle packages like dbms_space.space_usage.

Sample Script: 


SQL> set serveroutput on

SQL> declare
 v_segment_size_blocks number;
 v_segment_size_bytes number;
 v_ number;
 v_used_blocks number;
 v_used_bytes number;
 v_expired_blocks number;
 v_expired_bytes number;
 v_unexpired_blocks number;
 v_unexpired_bytes number;
 begin
 dbms_space.space_usage ('DBFS_USER', 'LOB_SFS$_FST_1', 'LOB',
 v_segment_size_blocks, v_segment_size_bytes,
 v_used_blocks, v_used_bytes, v_expired_blocks, v_expired_bytes,
 v_unexpired_blocks, v_unexpired_bytes );
 dbms_output.put_line('Expired Blocks = '||v_expired_blocks);
 dbms_output.put_line('Expired Bytes = '||v_expired_bytes);
 dbms_output.put_line('UnExpired Blocks = '||v_unexpired_blocks);
 dbms_output.put_line('UnExpired Bytes = '||v_unexpired_bytes);
 end;
/

Sample Out: 


Expired Blocks = 42865646
Expired Bytes = 351155372032
UnExpired Blocks = 26808
UnExpired Bytes = 219611136

PL/SQL procedure successfully completed.

Expired Bytes = 351155372032 + UnExpired Bytes = 219611136) is considered as the free space for DBFSTS (Oracle Database File System Tablespace).


Reference / Additional Read


1. Oracle® Database SecureFiles and Large Objects Developer's Guide
12c Release 1 (12.1)
E17605-10

Chapter 10 Using DBFS

URL: http://docs.oracle.com/cd/E16655_01/appdev.121/e17605/adlob_client.htm

2. Oracle® Database SecureFiles and Large Objects Developer's Guide
11g Release 2 (11.2)
E18294-04

Chapter  6 DBFS File System Client

URL: http://docs.oracle.com/cd/E11882_01/appdev.112/e18294/adlob_client.htm#ADLOB0006

No comments:

Post a Comment