The last days I was asked the question how to invalidate one particular cursor in the shared pool. So there was a SQL query executed, it was (of course) parsed, an execution plan was generated and all this is now cached in the shared pool. When this query comes again the cached information is being used. There was a posting about this regarding the discussion of bind variables.

Sometimes there is the situation where you just don't want the database to use that cache. Now - the easiest way is then to add some comment to the SQL query - the SQL text changes, so you have a new query.

The goal is just to eliminate one particular cursor. And in Oracle11g this is possible with DBMS_SHARED_POOL (Metalink note 457309.1 states that it is also possible with 10.2.0.4 but I tested only on 11g).

Now we have one invalidation - the SQL query was parsed like a new query - the cursor object was infact purged from the shared pool. But DBMS_SHARED_POOL can do more: the SIZES procedure shows the objects currently residing in the shared pool. The following call lists all objects greater than 500 kilobytes.

Sometimes there are situations where developers have to look into the database's tracefiles. Here are two examples ...

If you activate a SQL trace to analyze a particular SQL command then this information is being written into a tracefile

If a java stored procedure (java in the database) throws an exception the java error stack is by default written to a tracefile

... and this means that you have to log into the operating system of the database machine, change to the tracefile directory, lookup the file, process it with tkprof (when necessary) and finally view the results. This costs at least time and in some cases you don't even have credentials for the operating system.

I'll -again- take the package for operating system interaction in order to create a PL/SQL package which does all the stuff automatically. This package TRACE_HELPER does only run in an Oracle11g database - previous versions don't have the V$DIAG_INFO view). To run in previous versions I have to write some code which determines the current session's tracefile ... this will be posted here when finished.

The function GET_SESSION_TRACE_FILE reads just the tracefile content and returns it as a CLOB. This is helpful when java stored procedures throw exceptions - you then see the java error stack in the returning CLOB.

The function GET_SESSION_TKPROF_TRACE executes tkprof on the session's tracefile and returns the tkprof output as a CLOB. During this process a temporary file with the tkprof output is being created, its contents are then being copied into a CLOB and finally it's being deleted. The directory into which this temporary file is placed, is determined by the PL/SQL constant C_OUTFILE_PREFIX - so you might want to adjust this according to your environment. The directory and the filename prefix can also be adjusted by the procedure SET_OUTPUT_TEMPFILE_PREFIX.

The procedure SET_OUTPUT_TEMPFILE_PREFIX is used to set the directory and filename prefix for the temporary file generated by tkprof.

Your database schema needs some privileges in order to use the package. Just run the following script
as the SYS user and change the TRCTEST user to the database user you're working with.

Oracle TEXT, the fulltext indexing engine inside the Oracle Database, can not only index ASCII or HTML documents but also common (binary) document formats like Microsoft Office, PDF or others. This is achieved with a document filter which converts the binary content to HTML - which is then being indexed.

The cool bit is now that this filter can be called separately and completely independent from an Oracle TEXT index. So we can write a simple PL/SQL function which takes a binary document (as BLOB) and returns the filtered result (as a CLOB). In a web application this could be used to display a preview of the document directly in the browser - the end user does not have to open the desktop application.

This function does not do too much - it's just for convenience: It first createas a temporary CLOB to hold the filter results and then calls the procedure CTX_DOC.POLICY_FILTER. This one does the actual work.

One remark for the experts: If we would use CTX_DOC.IFILTER we were not required to create the Policy object. We then also would not need the CTXAPP role. So why are we doing those things? Because the documentation recommends it: Applications should use POLICY_FILTER instead of IFILTER - and we want to follow that rule.

BTW: The actual filter engine is not part of the database kernel - the database does a callout instead. The executable called is $ORACLE_HOME/ctx/bin/ctxhx - and you can also call it directly - just give it a try ...

The views expressed on this blog are my own and do not necessarily reflect the views of Oracle Corporation or Oracle Germany. The views and opinions expressed by visitors to this blog are theirs and do not necessarily reflect mine.