In wondering what was held in a BFILE column, I performed the below. In summary the BFILE column appears to hold the directory name (as a string, not as a object_id) and the file name within the directory. Consequently, one can drop the directory used in a BFILE data item without Oracle offering any resistance.
Upon further investigation, it appears that Oracle doesn't even care if the directory name given does not exist at the point in time at which the BFILENAME function is used to populate the BFILE column.
Create a dump file containing BFILE data (note that having lowercase directory names, as below, is generally not to be advised with BFILEs, even though quoted, there still appear to be issues with them):
CREATE TABLE bfile_explore (
name VARCHAR2(50),
os_file BFILE
);
INSERT INTO bfile_explore (name) VALUES ('Record with no BFILE element');
INSERT INTO bfile_explore VALUES ('/home/rik/public_html/meters.php', BFILENAME('public_html', 'meters.php'));
COMMIT;
COLUMN file_id NEW_VALUE absolute_file_number
COLUMN min_block_number NEW_VALUE minimum_block_number
COLUMN max_block_number NEW_VALUE maximum_block_number
COLUMN file_name FORMAT a40
WITH rowid_info AS (
SELECT DBMS_ROWID.rowid_relative_fno(rowid) rel_file_num,
MIN(DBMS_ROWID.rowid_block_number(rowid)) min_block_number,
MAX(DBMS_ROWID.rowid_block_number(rowid)) max_block_number
FROM bfile_explore
GROUP BY DBMS_ROWID.rowid_relative_fno(rowid)
)
SELECT rel_file_num, min_block_number, max_block_number, file_id, file_name
FROM rowid_info
JOIN dba_data_files
ON relative_fno = rel_file_num;
conn sys/<sys_password>@o12102nc as sysdba
ALTER SYSTEM CHECKPOINT;
ALTER SYSTEM DUMP DATAFILE &absolute_file_number
BLOCK MIN &minimum_block_number
BLOCK MAX &maximum_block_number;
The dump file produced from above is
[rik@o12102nc trace]$ cat /app/oracle/diag/rdbms/o12102nc/o12102nc/trace/o12102nc_ora_3426.trc
Trace file /app/oracle/diag/rdbms/o12102nc/o12102nc/trace/o12102nc_ora_3426.trc
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
ORACLE_HOME = /app/oracle/product/12.1.0.2/dbhome_1
System name: Linux
Node name: o12102nc
Release: 2.6.32-504.el6.x86_64
Version: #1 SMP Wed Oct 15 04:27:16 UTC 2014
Machine: x86_64
Instance name: o12102nc
Redo thread mounted by this instance: 1
Oracle process number: 27
Unix process pid: 3426, image: oracle@o12102nc
*** 2014-11-06 12:37:25.591
*** SESSION ID:(35.60782) 2014-11-06 12:37:25.591
*** CLIENT ID:() 2014-11-06 12:37:25.591
*** SERVICE NAME:(o12102nc) 2014-11-06 12:37:25.591
*** MODULE NAME:(sqlplus@hp (TNS V1-V3)) 2014-11-06 12:37:25.591
*** CLIENT DRIVER:(SQL*PLUS ) 2014-11-06 12:37:25.591
*** ACTION NAME:() 2014-11-06 12:37:25.591
Start dump data blocks tsn: 4 file#:6 minblk 77877 maxblk 77877
Block dump from cache:
Dump of buffer cache at level 4 for pdb=0 tsn=4 rdba=25243701
BH (0x97fed918) file#: 6 rdba: 0x01813035 (6/77877) class: 1 ba: 0x97e2a000
set: 3 pool: 3 bsz: 8192 bsi: 0 sflg: 0 pwc: 0,0
dbwrid: 0 obj: 93683 objn: 93683 tsn: [0/4] afn: 6 hint: f
hash: [0x9f6d45b8,0x9f6d45b8] lru: [0x97fed8c0,0x97fb5c40]
ckptq: [NULL] fileq: [NULL]
objq: [0x97fed8e8,0x97fb5c68] objaq: [0x97fed8f8,0x97fb5c78]
st: XCURRENT md: NULL fpin: 'ktspbwh2: ktspfmdb' fscn: 0x0.2e6de4 tch: 1
flags: block_written_once
LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [1]
Block dump from disk:
buffer tsn: 4 rdba: 0x01813035 (6/77877)
scn: 0x0.2e6de5 seq: 0x01 flg: 0x06 tail: 0x6de50601
frmt: 0x02 chkval: 0xd71a type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00007FE80A311E00 to 0x00007FE80A313E00
7FE80A311E00 0000A206 01813035 002E6DE5 06010000 [....50...m......]
7FE80A311E10 0000D71A 00000001 00016DF3 002E6DE4 [.........m...m..]
7FE80A311E20 00000000 00320002 01813030 00170006 [......2.00......]
7FE80A311E30 000008BF 01001E9E 001D0184 00002002 [............. ..]
7FE80A311E40 002E6DE5 00000000 00000000 00000000 [.m..............]
7FE80A311E50 00000000 00000000 00000000 00000000 [................]
7FE80A311E60 00000000 00020100 0016FFFF 1F141F2A [............*...]
7FE80A311E70 00001F14 1F780002 545B1F2A 545B69E5 [......x.*.[T.i[T]
7FE80A311E80 00000000 00000000 00000000 00000000 [................]
7FE80A311E90 01813031 00000000 00000000 00000008 [10..............]
7FE80A311EA0 00000008 01813038 00000000 00000000 [....80..........]
7FE80A311EB0 00000000 00000005 00000000 00000001 [................]
7FE80A311EC0 00016DF2 002E6C60 00000000 01813030 [.m..`l......00..]
7FE80A311ED0 00000008 00000000 00000000 00000000 [................]
7FE80A311EE0 00000000 00000000 00000000 00000000 [................]
Repeat 9 times
7FE80A311F80 00000000 00000000 00000000 55551511 [..............UU]
7FE80A311F90 00000000 00000000 00000000 00000000 [................]
Repeat 478 times
7FE80A313D80 00000000 00000000 00000000 012C0000 [..............,.]
7FE80A313D90 682F2002 2F656D6F 2F6B6972 6C627570 [. /home/rik/publ]
7FE80A313DA0 685F6369 2F6C6D74 6574656D 702E7372 [ic_html/meters.p]
7FE80A313DB0 00297068 08010027 00000008 95000001 [hp).'...........]
7FE80A313DC0 0000007F 6275700B 5F63696C 6C6D7468 [.....public_html]
7FE80A313DD0 656D0A00 73726574 7068702E 1C01012C [..meters.php,...]
7FE80A313DE0 6F636552 77206472 20687469 42206F6E [Record with no B]
7FE80A313DF0 454C4946 656C6520 746E656D 6DE50601 [FILE element...m]
Block header dump: 0x01813035
Object id on Block? Y
seg/obj: 0x16df3 csc: 0x00.2e6de4 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1813030 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0006.017.000008bf 0x01001e9e.0184.1d --U- 2 fsc 0x0000.002e6de5
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
bdba: 0x01813035
data_block_dump,data header at 0x7fe80a311e64
===============
tsiz: 0x1f98
hsiz: 0x16
pbl: 0x7fe80a311e64
76543210
flag=--------
ntab=1
nrow=2
frre=-1
fsbo=0x16
fseo=0x1f2a
avsp=0x1f14
tosp=0x1f14
0xe:pti[0] nrow=2 offs=0
0x12:pri[0] offs=0x1f78
0x14:pri[1] offs=0x1f2a
block_row_dump:
tab 0, row 0, @0x1f78
tl: 32 fb: --H-FL-- lb: 0x1 cc: 1
col 0: [28]
52 65 63 6f 72 64 20 77 69 74 68 20 6e 6f 20 42 46 49 4c 45 20 65 6c 65 6d
65 6e 74
tab 0, row 1, @0x1f2a
tl: 78 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [32]
2f 68 6f 6d 65 2f 72 69 6b 2f 70 75 62 6c 69 63 5f 68 74 6d 6c 2f 6d 65 74
65 72 73 2e 70 68 70
col 1: [41]
00 27 00 01 08 08 00 00 00 01 00 00 95 7f 00 00 00 0b 70 75 62 6c 69 63 5f
68 74 6d 6c 00 0a 6d 65 74 65 72 73 2e 70 68 70
end_of_block_dump
End dump data blocks tsn: 4 file#: 6 minblk 77877 maxblk 77877
Alternatively, after doing the system checkpoint, the block number from above can be used to take a copy of the block
(block number instead of 77877 in the skip option below).
PROMPT dd bs=8192 skip=77877 count=1 if=/app/oracle/oradata/o12102nc/users01.dbf of=bfile_block_dump.dbf
The bottom of the data block containing the BFILE data displayed using hexdmp:
[rik@o12102nc ~]$ hexdmp bfile_block_dump.dbf |tail -5
................................ 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
..............,.. /home/rik/publ 00 00 00 00 00 00 00 00 00 00 00 00 00 00 2C 01 02 20 2F 68 6F 6D 65 2F 72 69 6B 2F 70 75 62 6C
ic_html/meters.php).'........... 69 63 5F 68 74 6D 6C 2F 6D 65 74 65 72 73 2E 70 68 70 29 00 27 00 01 08 08 00 00 00 01 00 00 95
.....public_html..meters.php,... 7F 00 00 00 0B 70 75 62 6C 69 63 5F 68 74 6D 6C 00 0A 6D 65 74 65 72 73 2E 70 68 70 2C 01 01 1C
Record with no BFILE element...m 52 65 63 6F 72 64 20 77 69 74 68 20 6E 6F 20 42 46 49 4C 45 20 65 6C 65 6D 65 6E 74 01 06 E5 6D
[rik@o12102nc ~]$