An Actionable Plan
Last post, I talked about how I was playing with securefiles, fnd_lobs, deduplication and advanced compression. It took a bit of tweaking, but I think I can safely share my success at reigning in FND_LOBS. I’m going to outline the process of an online conversion from FND_LOBS defined as a basicfile (the default) to securefiles.
Jumping off a Bridge
Ever see Oracle do a presentation and the first slide is legalese called a Safe Harbor which basically says everything you are about to see and hear is subject to change and no business plans should be made on assumptions or potential future releases? I feel like I should make a similar warning, let’s call it a bridge jumper. Remember the old saying, “If all your friends jumped off a bridge, would you do it too?”. Here’s what I’ll say: This works in DEV on EBS version R12.2, and I’ve done some minimal functional testing. Don’t do it in PROD until you prove that it doesn’t impact something I forgot about or can’t foresee. I recommend a regression pass. Also don’t use products you’re not licensed for, such as advanced compression, even in non-production environments.
References
http://docs.oracle.com/cd/B28359_01/appdev.111/b28393/adlob_smart.htm
http://www.oracle.com/technetwork/articles/sql/11g-securefiles-084075.html
Just the Highlights
You can convert your FND_LOBS table via online redefintion from basicfiles to securefiles. In doing so, you will define the new lob table as securefile storage, and if you use deduplication and compression clauses, you will probably save a lot of space. I did, dropping from 150GB to about 50GB.
select a.owner, a.table_name, a.column_name, a.segment_name , b.bytes/1024/1024
from dba_lobs a, dba_segments b
where a.segment_name = b.segment_name
and a.owner = b.owner
and a.table_name like 'FND_LOBS%'
order by b.bytes/1024/1024 desc
SQL> /
OWNER TABLE_NAME COLUMN_NAME SEGMENT_NAME B.BYTES/1024/1024
------------------------------ ------------------------------ --------------------------------------------------
APPLSYS FND_LOBS FILE_DATA SYS_LOB0000034032C00004$$ 149115.25
By the Numbers
I really wish WordPress would let me paste code in better, as I could make this a lot easier on the eyes. Well you get what you get.
1. Increase the size of your APPS_TS_MEDIA tablespace until you can hold at least another table some percentage of the size of your FND_LOBs lob segment. See my prior post to figure out how to determine this.
2. Create FND_LOBS_NEW (yeah, creative name right?) using the same syntax as your old table. Note deduplicate, compress high clauses.
create
table applsys.fnd_lobs_new
(
file_id number
,file_name varchar2( 256)
,file_content_type varchar2( 256) not null
,file_data blob
,upload_date date
,expiration_date date
,program_name varchar2( 32)
,program_tag varchar2( 32)
,language varchar2( 4) default( userenv( 'LANG') )
,oracle_charset varchar2( 30) default( substr( userenv( 'LANGUAGE'),instr( userenv( 'LANGUAGE'),'.') + 1) )
,file_format varchar2( 10) not null
,primary key( file_id ) validate
)
tablespace apps_ts_media pctfree 10initrans 10maxtrans 255storage
(
initial 128k buffer_pool default
)
logging nocompress lob
(
file_data
)
store as securefile
(
deduplicate compress high tablespace apps_ts_media chunk 8192storage( initial 128k buffer_pool default) pctversion 0nocache logging
) ;
3. Take your new table out of logging and enable parallel dml in an effort to save time:
- alter table applsys.fnd_lobs_new nologging;
- alter session force parallel dml parallel 4;
- set timing on
4. Map the columns to the new table (this ran about 12 hours for me)
declare
l_col_mapping varchar2(1000);
begin
l_col_mapping :=
'file_id file_id, '
||'file_name file_name, '
||'file_content_type file_content_type, '
||'file_data file_data, '
||'upload_date upload_date, '
||'expiration_date expiration_date, '
||'program_name program_name, '
||'program_tag program_tag, '
||'language language, '
||'oracle_charset oracle_charset, '
||'file_format file_format'
;
dbms_redefinition.start_redef_table ('APPLSYS', 'FND_LOBS', 'FND_LOBS_NEW', l_col_mapping)
;
end;
/
5. Drop the existing index on APPLSYS.FND_LOBS_CTX (intermedia index). At this point, you may experience adverse performance on FND_LOBS for some users, but this index is of the insides of the LOBs as I understand it, so I doubt anyone will notice (your primary key remains available). If you don’t do this, the next step fails spectacularly
drop index applsys.fnd_lobs_ctx;
6. Begin the redefinition process. This ran really fast for me.
declare
l_error_count pls_integer := 0;
begin
dbms_redefinition.copy_table_dependents('APPLSYS', 'FND_LOBS', 'FND_LOBS_NEW', 1, TRUE, TRUE, TRUE, FALSE, l_error_count)
;
dbms_output.put_line('Errors Occurred := '|| to_char(l_error_count))
;
end;
/
7. Finish the redefinition process. Also very fast.
begin
dbms_redefinition.finish_redef_table
('ARUP', 'FND_LOBS', 'FND_LOBS_NEW');
end;
/
8. Confirm it worked
select securefile
from dba_lobs
where table_name = 'FND_LOBS'
/
SEC
---
YES
9. Space check
select a.owner, a.table_name, a.column_name, a.segment_name , b.bytes/1024/1024
from dba_lobs a, dba_segments b
where a.segment_name = b.segment_name
and a.owner = b.owner
and a.table_name like 'FND_LOBS%'
order by b.bytes/1024/1024 desc
OWNER TABLE_NAME COLUMN_NAME SEGMENT_NAME B.BYTES/1024/1024
------------------------------ ------------------------------ ------------------------------ ------------------------------
APPLSYS FND_LOBS FILE_DATA SYS_LOB0008283556C00004$$ 46987
Elapsed: 00:00:11.64
10. Drop the temp table
drop table fnd_lobs_new;
11. Recreate the index APPLSYS.FND_LOBS_CTX from the EBS script. This ran about 5 hours for me.
cd $FND_TOP/sql
sqlplus apps
SQL> @aflobbld.sql APPLSYS APPS
12. Gather statistics
EXEC FND_STATS.GATHER_TABLE_STATS(OWNNAME => ‘APPLSYS’,TABNAME => ‘FND_LOBS’);
Parting Shot
It occurs to me during cleanup, that if you create your “temporary” FND_LOBS table (in my case, FND_LOBS_NEW) in a new tablespace instead of apps_ts_media, once you’re done you could probably reclaim all the disk allocated for this one-time project. The alternative is that your apps_ts_media is now 3x (or more) what you need, as you eliminated your large, basicfiles FND_LOBS table, and now have a compressed and deduplicated version on securefiles which will presumably take a long, long time to fill up your apps_ts_media tablespace now that you’ve expanded it for the project. Because of high water marks, I was unable to shrink my tablespace in any significant way after I was done.
That’s It Folks
I tested this a few ways from the functional standpoint. I ran a report which printed a LOB (Invoice) which was part of a requisition. I opened an attachment in Quality. I saved an attachment in Quality. Everything checked out. On to a full regression cycle! Thanks for tuning in, let me know if you find this useful.
Kevin Kempf's Blog 