Forum Stats

  • 3,814,021 Users
  • 2,258,809 Discussions


ctx_doc.policy_filter is stuck processing file

Martin Giffy D'Souza
Martin Giffy D'Souza Member Posts: 484 Bronze Badge
edited Oct 29, 2019 5:01PM in Text

I'm trying to convert a blob to clob as part of my Oracle Text index definition. For the most part using ctx_doc.policy_filter works fine, however one file is is getting "stuck" in the call. It is not raising an error, just that the procedure does not finish. I've been able to isolate the issue using the sample code below.

Unfortunately I can not include the original PDF as it has sensitive information.

Does anyone have an idea why this may happen and potential work arounds?

-- Create Policy (only needs to be done once)begin  ctx_ddl.create_policy(    policy_name => 'my_ctx_policy',     filter=> 'ctxsys.auto_filter'  );end;/-- Process a file from the "documents" tabledeclare  l_blob blob;  l_clob clob := null;begin  select d.file_blob  into l_blob  from documents d  where d.document_id = 123;  -- Code gets "stuck" here on some types of documents   -- Current one is a scanned PDF (not good quality / 3 pages)  ctx_doc.policy_filter(    policy_name => 'my_ctx_policy',    document => l_blob,    restab => l_clob,    plaintext => true  );end;/


  • Roger Ford-Oracle
    Roger Ford-Oracle Member Posts: 1,132 Employee
    edited Oct 23, 2019 5:15AM

    If you create an explicit preference for AUTO_FILTER (rather than using the predefined preference CTXSYS.AUTO_FILTER) then you can apply a timeout setting. That should cause it to give up if it gets stuck for more than the set number of seconds.

    You don't mention your version or platform, but there are some known issues with filtering which we are investigating. The problems seem to come down to the encryption layer that encrypts and decrypts the temporary files used during filtering. If your database server is secure (no public access to /tmp files) you probably don't need encryption. In which case you can install patch 27921251 and do "alter system set events '30580 trace name context forever, level 65536" to disable filtering encryption.

  • Martin Giffy D'Souza
    Martin Giffy D'Souza Member Posts: 484 Bronze Badge
    edited Oct 23, 2019 11:33AM

    Roger, thanks for the quick response.

    DB Version is: Oracle Database 19c Enterprise Edition Release - Production Version

    According to our DBAs the patch you listed is only for 12.1 and 12.2. Has this patch already been applied to 19?

    I'll work on creating a custom filter with timeout as that's a good idea either way. Will post my solution here later once complete.

  • Roger Ford-Oracle
    Roger Ford-Oracle Member Posts: 1,132 Employee
    edited Oct 23, 2019 11:43AM

    Yes, 19.1 should contain the patch code already. You would still need to set the event to actuate it.

    You can of course set the event at session level rather than system level if that's easier:

    alter session set events '30580 trace name context forever, level 65536';

    That would need to be done in each session before calling POLICY_FILTER.

    I'm very interested to know if that solves your problem, so please report back here after trying it.

  • Martin Giffy D'Souza
    Martin Giffy D'Souza Member Posts: 484 Bronze Badge
    edited Oct 25, 2019 12:03PM

    I've tried all your suggestions (and many iterations of it) and have come to the following conclusion:

    When I call my procedure, standalone, with the row (based on rowid) in question: everything works properly. When the procedure is called within the context of ctx_ddl.sync_index it gets stuck.

    When I kill the session that is stuck I get the following error:

    alter system kill session '507,4408' immediateError report -ORA-00031: session marked for kill00031. 00000 -  "session marked for kill"*Cause:    The session specified in an ALTER SYSTEM KILL SESSION command           cannot be killed immediately (because it is rolling back or blocked           on a network operation), but it has been marked for kill.  This           means it will be killed as soon as possible after its current           uninterruptable operation is done.*Action:   No action is required for the session to be killed, but further           executions of the ALTER SYSTEM KILL SESSION command on this session           may cause the session to be killed sooner.

    It doesn't kill the session at all, rather marks it to be killed. The only way to kill it is to kill the OS process. What I don't understand is how/why the session can't be killed due to a rollback call. Nothing was written to tables at this point. Here's a high level summary of what's occurring:


    - First row to be processed is the one that's giving us a problem

    - Queries the table based on p_rowid

    - Does some stuff (for other indexed fields / no updates/inserts to tables)

    - Call to ctx_doc.policy_filter is made

       - Process is stuck

    Not sure if this helps or not. If you want I can do a Zoom session with you to show you it live. martin.dsouza    (gmail)

  • Roger Ford-Oracle
    Roger Ford-Oracle Member Posts: 1,132 Employee
    edited Oct 25, 2019 2:18PM

    On the general question about killing sessions - ALTER SYSTEM KILL SESSION is somewhat badly worded - it doesn't actually kill a session, it sets a flag telling the session to kill itself at a time convenient to the session. If the session is blocked, or trying to execute a rollback, it won't check that flag and hence won't kill itself.


    I can't see any obvious reason why this call should hang during a SYNC_INDEX call.

    But one thing you might try is using CTX_DOC.IFILTER instead of POLICY_FILTER. IFILTER takes just the input BLOB and an output CLOB locator - it doesn't take a POLICY or INDEX. It's basically the same as using POLICY_FILTER with a default policy, and hence has no dependency on the data dictionary.

    You might also want to try calling

        DBMS_LOB.CREATETEMPORARY (lclob, true)

    before calling POLICY_FILTER or IFILTER.  It's possible that just initializing the CLOB to null is not sufficient and we're hitting some sort of problem with an uninitialized LOB.  I don't think this would cause it to hang, and you've shown it working standalone, so I'm grasping at straws a bit here - but it's worth a try.

  • Martin Giffy D'Souza
    Martin Giffy D'Souza Member Posts: 484 Bronze Badge
    edited Oct 29, 2019 5:01PM

    I've been able to narrow down the issue. It seems it has nothing to do with the index. Here's what's failing:


      l_rowid varchar2(255) := 'AAAS5vAAHAAAgi8ABD';

      l_row documents%rowtype;

      l_clob clob;


      select d.*

      into l_row

      from documents d

      where 1=1

        and d.rowid = l_rowid;



      policy_name => 'DOCUMENTS_CTX_POL1',

    --    policy_name => 'mdsouza_ctx_policy',

        document => l_row.contents,

        restab => l_clob,

        plaintext => true,

        format => 'BINARY'




    If you run this code 3 times, the 3rd time it hangs. When viewing the session in SQL Dev, it says it has a wait event of "direct path read"

    I did try to use CTX_POLICY.IFILTER and it works fine. The reason why I haven't changed over to it is in the docs it says not to use that procedure anymore and use POLICY_FILTER instead.