Forum Stats

  • 3,770,126 Users
  • 2,253,070 Discussions
  • 7,875,331 Comments

Discussions

What precautions to take to Purge ECBATCHITEMS Table data to free up space it occupies?

Sanket24
Sanket24 Member Posts: 79 Blue Ribbon
edited Aug 19, 2020 6:10AM in WebCenter Content

Dear All,

We have an issue wherein Enterprise Capture table, ECBATCHITEMS is occupying lots of disk space because there are more than 8 lakh records in it which includes the BLOB content (in ECITEMDATA column).

May of the batches are not released and hence lying up in the table occupying the space.

Is there any way to free up the space occupied by the data in this table?

If we would like to Purge data in this table, than what precautions we need to take care of like Other table dependencies, etc.?

We tried to find out details on Oracle KBase but could not find any details on it .. So, if anybody could detail it out that would be helpful.

Thanks!

Regards,

Sanket

Sanket24

Answers

  • Srinath Menon-Oracle
    Srinath Menon-Oracle Posts: 6,291 Employee
    edited Jul 19, 2020 9:21PM

    Any need to purge the table data is based on whether the data is used for any report / audit purpose.  If it is not being used for any of those purposes then it can be purged.

    Sanket24
  • Sanket24
    Sanket24 Member Posts: 79 Blue Ribbon
    edited Jul 20, 2020 7:52AM

    Thanks Srinath for your helpful reply.

    Could you also provide comment on below query:

    If we would like to Purge data in this table, than what precautions we need to take care of like other table dependencies, etc.? because purging records in ECBATCHITEMS table might lead to some orphaned records in other dependent tables (if any).

    Thanks,

    Sanket

  • Srinath Menon-Oracle
    Srinath Menon-Oracle Posts: 6,291 Employee
    edited Jul 20, 2020 10:06PM

    Hi ,

    As part of space management you can run the following action plan:

    Delete unreleased batches from client itself.  Otherwise , you  have to run the following sql statements to delete all batches in a workspace :

    DELETE FROM ECDOCUMENTPAGES where ECDOCUMENTID in (select ECDOCUMENTID from ECDOCUMENTS where ECBATCHID in (select ECBATCHID from ECBATCHES where ECWORKSPACEID='<WORKSPACE_ID>'));DELETE FROM ECDOCUMENTS WHERE ECBATCHID IN (SELECT ECID FROM ECBATCHES WHERE ECWORKSPACEID='<WORKSPACE_ID>');DELETE FROM ECBATCHITEMS WHERE ECBATCHID IN (SELECT ECID FROM ECBATCHES WHERE ECWORKSPACEID='<WORKSPACE_ID>');DELETE FROM ECBATCHLOCKS WHERE ECBATCHID IN (SELECT ECID FROM ECBATCHES WHERE ECWORKSPACEID='<WORKSPACE_ID>');DELETE FROM ECBATCHES WHERE ECWORKSPACEID ='<WORKSPACE_ID>';
    Sanket24Sanket24
  • Sanket24
    Sanket24 Member Posts: 79 Blue Ribbon
    edited Aug 19, 2020 3:01AM

    Thanks Srinath for your reply.

    We will try out this approach...Just one more question...

    Is there any order or sequence (related to tables) that we need to follow to delete records successfully?

  • Srinath Menon-Oracle
    Srinath Menon-Oracle Posts: 6,291 Employee
    edited Aug 19, 2020 6:10AM

    Nothing in specific that I am aware of.