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..
Hi Bryan,
ReplyDeleteThanks for sharing.
I do face the same issue on DBFS in 11.2.0.3 BP20 Exadata platform.
Was the issue resolved on your end after patching ?
could you share the patch details as well.
I did open the SR with Oracle but all i got from Oracle was "Shrink LOB segment via online reorg option".
Appreciate your input.
LOB usage on my database:
SQL> set serveroutput on
SQL> .
SQL> declare
2 v_segment_size_blocks number;
3 v_segment_size_bytes number;
4 v_ number;
5 v_used_blocks number;
6 v_used_bytes number;
7 v_expired_blocks number;
8 v_expired_bytes number;
9 v_unexpired_blocks number;
10 v_unexpired_bytes number;
11 begin
12 dbms_space.space_usage ('DBFS', 'LOB_SFS$_FST_1', 'LOB',
13 v_segment_size_blocks, v_segment_size_bytes,
14 v_used_blocks, v_used_bytes, v_expired_blocks, v_expired_bytes,
15 v_unexpired_blocks, v_unexpired_bytes );
16 dbms_output.put_line('Expired Blocks = '||v_expired_blocks);
17 dbms_output.put_line('Expired Bytes = '||v_expired_bytes);
18 dbms_output.put_line('UNExpired Blocks = '||v_unexpired_blocks);
19 dbms_output.put_line('UNExpired Bytes = '||v_unexpired_bytes);
20 end;
21 /
Expired Blocks = 43685076
Expired Bytes = 357868142592
UNExpired Blocks = 0
UNExpired Bytes = 0
PL/SQL procedure successfully completed.
$ df -h .
Filesystem Size Used Avail Use% Mounted
dbfs-@LBFSBP.local:/ 495G 31G 464G 7% /dbfs_direct
==
Thanks,
Bal