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.
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.
Hello Mansour
ReplyDeleteWhile 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,
Hello,
DeleteNothing 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
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?
ReplyDeleteHi, yes you can but you have to use another solution to move all objects on tablespace media to another tablespace then drop media and create it again with single datafile then move back all object related to it again
ReplyDeleteIt's amazing that the questions are being answered :)) Hello Emad Greetings from Kuwait.
ReplyDeleteHello Greeting from Saudi Arabia
ReplyDelete