Tips on Automation, Cleanup, and Database Maintenance

Our Oracle expert illustrates automatic index maintenance, shows you how to clean up killed sessions faster, and explains differences in Oracle releases.

By Tom Kyte

March/April 2001

I have a Web (PL/SQL cartridge) text-search application that uses interMedia. I need to rebuild the interMedia text index on the bfile column of the mydocs table that contains the file references every time I create a new document and insert into the table, which leads to my first question: Why doesn't the text index pick up changes without my having to rebuild it? And then, how can I call "alter index rebuild" (i.e., a non-DML statement) from my PL/SQL procedure? Finally, I've read that with interMedia you can index, search, and analyze text and documents stored in Oracle8i , in files, and on the Web. I have seen code samples for docs stored in the database, but not for Word documents in the file system. Do you have sample interMedia code for indexing, searching, or analyzing?

The answer to the first part of your question is relatively straightforward. The text index would automatically pick up the new data if you run the ctxsrv process on the database server, enabling automatic index maintenance. Concerning your second question on calling a non-DML statement from a PL/SQL procedure, in the event you do not want to run the ctxsrv process, you can instead issue DDL in Oracle8ivia the "execute immediate" directive with dynamic SQL. For example:

   execute immediate 'alter index t rebuild';

Also, remember that roles are not enabled in a stored procedure, and if you get insufficient privileges when executing that statement, you probably have the ability to rebuild that index via a role.

But you really don't want to REBUILD the entire index. Hopefully you are using the SYNC parameter with the REBUILD command. In your case, this can be done even more easily by dynamically executing the DDL via an a supplied PL/SQL procedure:

   ctx_ddl.sync_index( idx_name );

For your final question, I asked one of Oracle's resident interMedia experts, Joel Kallman ( Joel.Kallman@oracle.com). He suggests two methods for indexing OS files that are not stored in the database: either by using BFILEs, or by using a file-system datastore. First, let's look at indexing by using BFILEs.

To begin, you must create an Oracle8i directory object where the files to be indexed are located. The GRANT is necessary so the interMedia Text process can access the files.

CREATE OR REPLACE DIRECTORY temp_dir AS 'c:\TEMP';
GRANT READ ON DIRECTORY temp_dir TO CTXSYS;

Next, you will create the table that will be used to reference these files (e.g., BFILEs).

CREATE TABLE mydocs( id NUMBER PRIMARY KEY,
                     title VARCHAR2(255),
thefile BFILE );

Now, you are ready to insert some data into the table, referencing some Word document files located in the TEMP directory. Note that these files exist in the TEMP directory where the database server is located.

INSERT INTO mydocs( id, title, thefile )

VALUES( 1, 'Document1',
BFILENAME('TEMP_DIR', 'WordDoc1.doc' ));
INSERT INTO mydocs( id, title, thefile )
VALUES( 2, 'Document2',
BFILENAME('TEMP_DIR', 'WordDoc2.doc' ));
INSERT INTO mydocs( id, title, thefile )
VALUES( 3, 'Document3',
BFILENAME('TEMP_DIR', 'WordDoc3.doc' ));
COMMIT;

Next, you are ready to create an interMedia Text index on the BFILE column.

CREATE INDEX mydocs_text_index ON mydocs(thefile) INDEXTYPE IS
ctxsys.context;

And finally, you can perform a query to test whether the files were properly filtered and indexed.

SELECT id
  FROM mydocs
 WHERE contains( thefile, 'Hello' ) > 0;

As you can see from this example, using BFILEs to index operating system files is a very straightforward approach when employing a simple CREATE INDEX command.

Using a file-system datastore is not that much more complicated but does involve an extra step.

First, create a preference that includes all of the directories where the files are located (note that you can include multiple paths in this preference if necessary).

BEGIN
CTX_DDL.CREATE_PREFERENCE( 'my_datastore_prefs',
'FILE_DATASTORE' );
CTX_DDL.SET_ATTRIBUTE(
 'my_datastore_prefs', 'path', 'c:\TEMP'
);
END;
/

Next, create the table that will be used to reference these files. In this case, the "thefile" column will be used to contain the filename.

CREATE TABLE mydocs( id NUMBER PRIMARY KEY,
title VARCHAR2(255),
thefile VARCHAR2(255) );

Now, insert some data into the table, referencing some Word document files located in our TEMP directory. Note that these files exist in the TEMP directory where the database server is located.

INSERT INTO mydocs( id, title, thefile )
VALUES( 1, 'Document1',
'WordDoc1.doc' );
INSERT INTO mydocs( id, title, thefile )
VALUES( 2, 'Document2',

'WordDoc2.doc' );
INSERT INTO mydocs( id, title, thefile )
VALUES( 3, 'Document3',
'WordDoc3.doc' );
COMMIT;
                      

Now, create an interMedia Text index, including the datastore preferences created earlier.

CREATE INDEX mydocs_text_index ON
mydocs(thefile) INDEXTYPE IS
ctxsys.context
    PARAMETERS('datastore
my_datastore_prefs');
                      

And finally, you should perform a query to test whether the files were properly filtered and indexed.

SELECT id
  FROM mydocs
 WHERE contains( thefile, 'Hello' ) > 0;

I've noticed that sessions I've killed can take a while to disappear. How can I change the priority of KILL SESSION so the killed-session entry in v$session will be deleted faster?

When you kill a session, outstanding work must still be cleaned up. This may take quite a while, because cleaning up a killed session is a relatively low-priority background event.

Also, if the user is still physically connected and idle, possibly with an open sqlplus prompt, he or she may stay in the killed state for quite some time, until he or she returns to the keyboard.

If you are in the second case, the user can stay killed forever, but his or her locks are released. To see this, log in as Scott and issue "select * from emp for update". Then, in another session do this: ops$tkyte@ORA8I.WORLD> select * from scott.emp for update
nowait;
select * from scott.emp for update nowait
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified

This shows that user Scott has locked resources. You should identify Scott's session and kill it:

If after killing a session the resources remained locked, that means the rollback is taking place in the background. A long-running transaction will take a long time to be undone.

ops$tkyte@ORA8I.WORLD> alter system kill session '286,9195';
ops$tkyte@ORA8I.WORLD> select username, status from v$session
where username = 'SCOTT';

USERNAME     STATUS
________     ________

SCOTT           KILLED

The rows are now unlocked...

ops$tkyte@ORA8I.WORLD> select * from scott.emp for update nowait;

EMPNO   ENAME    JOBMGR          HIREDATE   SAL   COMM      DEPTNO
_____   ______   ___________     ________   ___   ______   ______


7369    SMITH    CLERK 7902      17-DEC-80  800   20       ....

In Oracle8i, PMON defers transaction recovery of "killed" sessions to SMON, which may use fast-start parallel rollback if necessary. This frees up locks held by the killed process immediately.

The bottom line is that you cannot get rid of the killed session entries in v$session. They will go away eventually, and having them in v$session is OK (they are not holding locks unless you are rolling them back, and even then, you might not hold the locks).

Is it possible to have the order by column passed as a parameter while declaring a cursor in a procedure?

Yes. This is a frequently asked question by developers who want to have a single query that can sort by any column dynamically at runtime. How you approach this task depends on the release of Oracle you use. In Oracle8i Release 8.1 and higher, this action is straightforward, using a ref cursor:
create or replace procedure test_proc( seq in varchar2 )
as
begin
type rc is ref cursor;
l_cursor rc;
begin open l_cursor for 'select * from t
order by ' || seq; loop
fetch l_cursor into ....
exit when l_cursor%notfound;
end loop; close l_cursor;
end;

The ability to dynamically open a ref cursor was introduced with Oracle8i. In Oracle8 and before, you would need to use one of two approaches:

1. Use DBMS_SQL: You can see a small sample of using dbms_sql on any query to dump to a file at asktom.oracle.com. DBMS_SQL is a package that allows PL/SQL to do dynamic SQL procedurally via procedure calls.

2. Use decode: Create the cursor like this:

cursor any_cursor(SEQ in number) is
 select a.string, a.date, a.number, ...
   from ....
  where ....
  order by decode( SEQ,
                   1, a.string,
                   2,
to_char(a.date,'yyyymmddhh24miss'),
                   3, translate(

to_char(a.number,'9999' ),'-',chr(0))
                  )

That decode will look at the runtime value of the variable SEQ and return any of the columns in the query from 1 to 3. Notice the pains I took to ensure that all columns are the same type and cast in such a way as to make them sortable. Numbers are the most problematic, especially if they include minus signs—translating the "-" to chr(0) gets them to sort correctly for me.

The major difference between these two approaches (dynamic SQL vs. decode) is that decode will never be able to take advantage of an index to order the data, but dynamic SQL might be able to on some of the queries. That also points out why you cannot just substitute a bind variable for the order by :x and have the query order by a random column. The query plans must be reevaluated for each column sorted on.

What is the difference between Oracle7 Release 7.3 and Oracle8i?

This chapter contains a matrix that tells you, among other things, that function-based indexes are part of enterprise and personal but not standard editions. You can find this document and other helpful information about database releases online at Oracle Technology Network ( /technetwork/index.html).

I have a program that I wrote in Visual Basic that reads data and then deletes it from my tables. I would like to delete all of the read records only at midnight, rather than immediately after they are read. Is there any way to do this by using the database instead of in my Visual Basic program?

Then, you would code a stored procedure to do the delete. Use the DBMS_JOB supplied package to schedule this job every night at midnight. So, let's say you have created the stored procedure "delete _data" and want to schedule it to run every night at midnight. Use SQL Plus or some other tool to do the following:

declare
  l_job  number;
begin
  dbms_job.submit( j_job,
                  'delete_data;',
                  trunc(sysdate)+1,
                                          'trunc(sysdate)+1' );
end;
/

Make sure to set the init.ora parameters as follows:

job_queue_interval
job_queue_processes

You can see the supplied-packages guide online for all of the details on DBMS_JOB, at Oracle Technology Network.