Tuesday, April 1, 2014

Delete Attachment files for Oracle E-Bus Suite 11i or R12

If you face issue with huge space for attachment files on Oracle E-Bus Suite and you need to delete it from Cloned instance to reclaim your space, Please follow below procedure:

1-select distinct (entity_name) from fnd_attached_documents; /*to get all attachment entity name related to your module*/

2-Use below API to delete it.

DECLARE
    l_delete_document_flag  varchar2 (1):= 'Y';
    v_user_id               NUMBER := 0;
    v_resp_id               NUMBER := 20707;
    v_resp_appl_id          NUMBER := 201;
BEGIN
    FND_GLOBAL.APPS_INITIALIZE( user_id      => v_user_id
                               ,resp_id      => v_resp_id
                               ,resp_appl_id => v_resp_appl_id
                              );

    FOR I IN (SELECT  entity_name, pk1_value
              FROM fnd_attached_documents
              WHERE entity_name IN (  "Intity name from first select you can exclude any attachment you need on clone for any product")
             ) LOOP
    FND_ATTACHED_DOCUMENTS2_PKG.DELETE_ATTACHMENTS
                                                  ( X_entity_name                  => I.ENTITY_NAME
                                                  , X_pk1_value                    => I.PK1_VALUE
                                                  , X_delete_document_flag         => l_delete_document_flag);

    COMMIT;
END LOOP;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Err: '||SQLERRM);
END;

3-
- Create a new tablespace :
Create tablespace test add datafile  size 40G;

- Grant resouce access to the new tablespace :
-->alter user APPLSYS quota unlimited on test;
-->alter user APPLSYS quota unlimited on APPS_TS_MEDIA;

- Move the tables in question to the new tablespace by doing the following:
-->alter table applsys.fnd_lobs move lob (FILE_DATA) store as SYS_LOB0000057442C00004$$ (tablespace test);
-->alter table applsys.fnd_lobs move tablespace test;


-Move the tables back to the original tablespace
alter table applsys.fnd_lobs move lob (FILE_DATA) store as SYS_LOB0000061704C00012$$(tablespace apps_ts_media);
alter table applsys.fnd_lobs move tablespace apps_ts_media;


-Re-build indexes (need to provide the index list)
sql> spool index_rebuild.sql
sql> select 'ALTER INDEX '||owner||'.'||INDEX_NAME||' rebuild; ' from dba_indexes where TABLESPACE_NAME='APPS_TS_MEDIA' and owner='APPLSYS';
sql>spool off
sql>spool index_rebld.lst
sql>@index_rebuild.sql
sql>spool off

- Drop tablespace test including contents and datafiles;

drop tablespace Test including contents and datafiles;
Note: This action has to be done when there are no users logged on to applications.

3 comments:

  1. Hello Mansour

    While the solution looks the ONLY possible to delete attachments from R12 12.0.x releases, please explain the following concern.
    Once the attachments are manually deleted using the API, what kind of errors are expected from the application side? Would it break the application due to files not found?

    regards,

    ReplyDelete
    Replies
    1. Hello,

      Nothing will happened and i`m using this solution by the way once i cloned application to test or dev but not using it with production.

      as my attachment has around 400 G, so i`m try to free up some space.

      Regards

      Delete
  2. Hey, Thanks for the reply buddy. I did manage to remove the attachments, however the tablespace APPS_TS_MEDIA still has 29 datafiles with objects scattered in different datafiles. Is there a way I can bring up the objects to a single datafile and reclaim the file system storage space?

    ReplyDelete