I just ran into an issue on DBFS where I ran out of space.
First here is the df -k
First here is the df -k
Filesystem 1K-blocks Used Available Use% Mounted on
dbfs-dbfs_admin2@:/ 20983808 11443696 9540112 55% /dbfs/dba
OK, everything looks good.. I am using 11g and I have 9.5g available.
I go to copy a file on the os (you can see it is 240m). Lots of room
ls -al bsg.out
-rw-r--r-- 1 oracle oinstall 240794862 May 18 11:37 bsg.out
cp bsg.out bsg.out1
cp: writing `bsg.out1': No space left on device
cp: closing `bsg.out1': No space left on device
So where is my space. ?? I find this query..
set serveroutput on;
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_OWNER', 'LOB_SFS$_FST_12345', '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('Segment Size blocks = '||v_segment_size_blocks);
dbms_output.put_line('Segment Size bytes = '||v_segment_size_bytes);
dbms_output.put_line('Used blocks = '||v_used_blocks);
dbms_output.put_line('Used bytes = '||v_used_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;
/
And I see this output
Segment Size blocks = 2619024
Segment Size bytes = 21455044608
Used blocks = 1425916
Used bytes = 11681103872
Expired Blocks = 1190111
Expired Bytes = 9749389312
UNExpired Blocks = 0
UNExpired Bytes = 0
So.. according to this.. The segment is 21.4 g
11.7g is used space
9.7g is Expired space
0g is unexpired space.
So if I have 9.7g of Expired space why can't I use it ?? My file is only 244m, and I should have 9.7 g available.
So my questions out of this are (if anyone knows the answer).
1) How does this happen and how do I avoid it ?
2) How do I size tablespaces for DBFS ? They need more space to be available then I need for the file system.
3) How do I monitor the sizing since the DF -k does not report unexpired bytes that are available to be used ?
4) How does the clause "retention" fit into this ? retention defaults to "auto" rather than "none". Can I set it to "none", but what happens and does this solve my problem ?
Oh, and I wanted to make sure that I included the ouput of whats in the tablespace.
SEGMENT_NAME SEGMENT_TYPE SEGMENT_SIZE
-------------------------------------------------------- ------------------ ------------
LOB_SFS$_FST_12345 LOBSEGMENT 20461
T_ADMIN TABLE 17
IP_SFS$_FST_12345 INDEX 4
IPG_SFS$_FST_12345 INDEX 3
IG_SFS$_FST_12345 INDEX 2
SYS_IL0000095835C00007$$ LOBINDEX 0
UPDATE (6/13/12) --
After working with support on this, it was filed as a bug. This occured because I was using DBFS as a filesytem for my dbreplay capture. After thinking about it the dbcapture is probably the most intensive workload I could throw at DBFS. Not only does it simultaneously write to multiple files, but it writes to those files across multiple nodes. In my capture there were 4 nodes writing to 100's of files at the same time.
I will be testing the patch, and see if it corrects the problem. Support is telling me that the writing across multiple nodes is causing some of the issues..