How to defrag
Rukshan Soza, April 30, 2002 - 8:11 am UTC
Tom,
I read your response with great interest. We are running ctxsrv background process. But, according to your article with this method, it is likely that fragmentation will occur. How can I periodically, defrag/optimise these indexes ?
Thanks in advance
Rukshan
April 30, 2002 - 11:32 am UTC
You can rebuild them if you find it necessary. (see the intermedia guide, the process is well documented)
Make sure to gather METRICS before and after doing this -- so you can see if the rebuild did anything positive or negative with regards to performance and size. Only rebuild in the future if you see POSITIVE payback.
ctxsrv deprecated
Wynette Richards, April 30, 2002 - 12:53 pm UTC
Tom, When working with Oracle on a TAR about a year ago we were told that ctxsrv has been deprecated and that we should not use it. They said we should not call "alter index ... rebuild ... ('sync')" but, instead, that we should only do a sync by calling ctx_ddl.sync_index('index_name'). This is discussed in </code>
http://technet.oracle.com/products/text/x/Tech_Overviews/imt_816.html <code>
I was surprised to see you suggest using ctxsrv. Has anything changed in the past year?
Thanks for your outstanding web site. Thanks for the explanation re. need to run optimize.
April 30, 2002 - 2:11 pm UTC
The quote is:
<quote>
PL/SQL Sync and Optimize
PL/SQL functions for sync and optimize have been added. The preferred method for sync and optimize is alter index rebuild online. Ideally, these could be called in a dbms_job. However, separate sync and optimize jobs can conflict because two alter index operations cannot be run at the same time for a single index.
The new PL/SQL functions resolve both problems by proving an alternate PL/SQL API for sync and optimize:
ctx_ddl.sync_index(<idx_name>)
idx_name is the name of the index to sync. The effective user must be the owner of the index or ctxsys. If effective user is ctxsys, idx_name can be NULL, which will cause this call to examine all context indexes for pending DML. This uses the same code as ctxsrv. We now recommend processing pending DML through a dbms_job -- an individual job for each index, a ctxsys job for all indexes, or some combination of the two. This eliminates the need for ctxsrv, and thus eliminates the problem of starting ctxsrv each time the database starts. The ctxsrv program itself is now deprecated, and may be removed in some future release.
</quote>
It is still there in 9i.
You know, I read and re-read and read again my answer. No where do I "suggest running ctxsrv as being best practice"
I do suggest that it can lead to fragmented indexes.
That many people use dbms_job.
That you can use ctxsrv (and it is there, it is supported -- even in 9i)....
Additionally -- this question was against version 815 -- there was no ctx_ddl.sync_index call in that release (always look at the version in the subject for version info - i don't update questions just because of version changes!)
vs
Scott, December 16, 2002 - 2:41 am UTC
G'day
This information is fantastic, thankyou.
In the current release, and/or 817, would you suggest using ctxsrv or sync index with the occasional optimise?
The reason I ask is because one wonders what the optimal period is for the DBMS_JOBs.
How labour intensive is a synchronisation of all ctx indexes on a production database (using ctx_ddl.sync_index(NULL))?
Or does ctxsrv use similar decision making?
December 16, 2002 - 7:56 am UTC
On asktom -- I use ctxsrv so the index is "up to date" immediately after I publish a question or a review is added by you or whatever...
I perdiodically rebuild the index when I notice the "approximately so many hits" gets way off on the search page (dead entries from modified documents in the index).
You would have to benchmark using YOUR scenario (your "production" system might be less loaded them my "development" system. "production" infers nothing as far as load -- activity -- etc. production only infers you have tested and benchmarked the code that is running in order to know that it'll work ok).
sync and optimise
Scott, February 25, 2003 - 12:05 am UTC
G'day Tom
I'd like to clarify my understanding of the difference between ctx_ddl.sync_index and ctx_ddl.optimize_index.
Sync_index could be used in lieu of ctxsrv and would typically be executed during set intervals using DBMS_JOB. This ensures pending DML is available when querying the index.
Optimize_index is used periodically to reduce fragmentation of the index, regardless of how you synchronize. This would be executed less often than sync_index.
Are the statements accurate?
Does the optimize_index procedure issue the alter index statement as "online", to allow a non-blocking operation?
And our DBAs and I couldn't figure out what you meant by
"Make sure to gather METRICS before and after doing this -- so you can see if the rebuild did anything positive or negative with regards to performance and size. Only rebuild in the future if you see POSITIVE payback. "
ie - what METRICS?
And how you would determine positive payback?
Thankyou to your other answers in regards to Oracle Text, they've helped a lot.
Thanks.
February 25, 2003 - 9:27 am UTC
yes, one adds newly added documents to the index (sync)
the other in effect rebuilds it
You can use alter index rebuild online to get "non blocking", it'll allow concurrent access to the index during the rebuild.
What I mean by metrics (it is sad the dba's do not know how to do this ;( )
would be to collect metrics about the queries that use this index - say all of the TEXT queries. Save the average LIO's per query. Now rebuild. After a while, come back and make sure that they are "better"
Text indexes are more like Bitmaps -- not like B*Tree's. they do require a bit more care and feeding then B*Trees.
Response from our DBA
"Gavin", March 26, 2003 - 3:11 am UTC
G'day Tom.
This is what our DBA has to say in response to your comments...
<quote>
This Tom guy... how many are there, I'm assuming Tom1, Tom2 and at least Tom3...
His whole purpose in life is to get questions, research, and respond... He's not out in the field, trying to keep up with the flood of new technology that keeps coming out.
We don't have time to research new technology, especially when it's not being used in production. Obviously as intermedia/text/context (whatever the hell they're calling it this week) goes into production, we will learn to administer and tune it. For this Tom guy(s) to give this comment shows the lack of real-world experience he/they have...
My 2 cents.
Gavin
</quote>
March 26, 2003 - 7:41 am UTC
Your DBA needs a vacation....
I don't spend too much time researching actually. Sometimes -- let me show you an example:
</code>
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:8760267539329 <code>
that one I had to research -- you can see in the comments:
...
first of all I have to apologize for being so impatient ( when I saw some
answers on newer questions). ....
You see -- I answered the 10 "no brainers" and went on a camping trip. Saved the one to research for later.
Your DBA is the "wild west, I'm a hero DBA" -- obviously. There are many of those types. Just whip it out there -- don't think about what'll happen. We'll fix things as they happen. We don't need to think about planning for anything. We are too smart for that.
I usually get called into work with your DBA to fix things. Going to a customer this morning in fact to talk about "what the heck happened to our systems, why were we slow for a week". ('real world' DBA's decided to 'tune' a production box. Changed tons of things all at once -- didn't record WHAT they changed -- performance went down the tubes. No metrics from "when it was OK" to compare against. Not even numbers like "average response time needs to be X". Just "system is really slow -- whats wrong with Oracle??"... So after a week of unraveling what these real worlders did -- putting files back where they came from, unfixing all of their fixes, they think -- repeat THINK -- they are back where they started.)
There is one of me.
I live in the real world.
I actually run a couple of systems -- day to day.
I don't understand his comment about "new technology" as this technology has been part of Oracle since 7.3 days.
You need a new DBA. My 2 cents.
sync oracle text index
Sean, July 18, 2003 - 11:11 pm UTC
Hi Tom,
I am sorry that I posted this question on the wrong thread last time.
I want to setup job for sync Oracle text index. I got this error when I try to
create stored procedure or job:
PLS-00201: identifier 'CTX_DDL' must be declared
Thanks so much for your help.
Sean
--This one works.
SQL>exec ctx_ddl.sync_index('blob_index');
-- This onw works.
SQL> begin
2 ctx_ddl.sync_index('blob_index');
3 end;
4 /
PL/SQL procedure successfully completed.
--But this one does not work
SQL> create or replace procedure p1
2 IS
3 begin
4 ctx_ddl.sync_index('blob_index');
5 end;
6 /
Warning: Procedure created with compilation errors.
SQL> show error
Errors for PROCEDURE P1:
LINE/COL ERROR
-------- ----------------------------------------------------
4/8 PLS-00201: identifier 'CTX_DDL' must be declared
4/8 PL/SQL: Statement ignored
SQL>
-- The job wakes up every minute. But there is error in log file
SQL> exec dbms_job.submit( :l_job, 'ctx_ddl.sync_index(''blob_index'');', sysdate+5/1440, 'sysdate+1
/1440' );
PL/SQL procedure successfully completed.
-- Here is the error in alert.log file
Fri Jul 18 22:36:34 2003
Errors in file c:\oracle\admin\winter\udump\winter_j000_1064.trc:
ORA-12012: error on auto execute of job 22
ORA-06550: line 1, column 96:
PLS-00201: identifier 'CTX_DDL' must be declared
ORA-06550: line 1, column 96:
PL/SQL: Statement ignored
sync oracle text index
sean, July 18, 2003 - 11:23 pm UTC
Hi Tom,
I am sorry that I posted this question on the wrong thread last time.
I want to setup job for sync Oracle text index. I got this error when I tried to
create stored procedure or job:
PLS-00201: identifier 'CTX_DDL' must be declared
Thanks so much for your help.
Sean
--This one works.
SQL>exec ctx_ddl.sync_index('blob_index');
-- This onw works.
SQL> begin
2 ctx_ddl.sync_index('blob_index');
3 end;
4 /
PL/SQL procedure successfully completed.
--But this one does not work
SQL> create or replace procedure p1
2 IS
3 begin
4 ctx_ddl.sync_index('blob_index');
5 end;
6 /
Warning: Procedure created with compilation errors.
SQL> show error
Errors for PROCEDURE P1:
LINE/COL ERROR
-------- ----------------------------------------------------
4/8 PLS-00201: identifier 'CTX_DDL' must be declared
4/8 PL/SQL: Statement ignored
SQL>
-- The job wakes up every minute. But there is error in log file
SQL> exec dbms_job.submit( :l_job, 'ctx_ddl.sync_index(''blob_index'');', sysdate+5/1440, 'sysdate+1
/1440' );
PL/SQL procedure successfully completed.
-- Here is the error in alert.log file
Fri Jul 18 22:36:34 2003
Errors in file c:\oracle\admin\winter\udump\winter_j000_1064.trc:
ORA-12012: error on auto execute of job 22
ORA-06550: line 1, column 96:
PLS-00201: identifier 'CTX_DDL' must be declared
ORA-06550: line 1, column 96:
PL/SQL: Statement ignored
sync index using dbms_job
Sean, July 19, 2003 - 9:03 pm UTC
Hi Tom,
Here is the answer I found. It is strange that commit is needed in the stored procedure.
Thanks for your help.
Sean
SQL>connect system/password
SQL>grant execute on ctx_ddl to scott
SQL>connect scott/password
SQL>create or replace procedure p10
IS
begin
ctx_ddl.sync_index('blob_index');
commit;
end;
/
SQL>exec dbms_job.submit( :l_job, 'p10;', sysdate+5/1440, 'sysdate+5/1440' );
-- The following does not work though the job wakes up and there is no error in alter.log.
SQL> exec dbms_job.submit( :l_job, 'ctx_ddl.sync_index(''blob_index'');', sysdate+5/1440, 'sysdate+5/1440' );
Diff between ctx_ddl & dbms_utility
Sami, March 19, 2004 - 5:01 pm UTC
Dear Tom,
Thanks for all your help/support in the past. I visit this site everyday.
Q1) What is the difference between method (A) & (B)?
Q2) Which is the better/efficient way to go in 8i?
Q3) How to identify "exec ctx_ddl.optimize_index" is required?
Q4) Do you prefer to keep all the DOMAIN index in a tablespace which is created with NOLOGGING option?
(A)
exec ctx_ddl.sync_index('USER1.DOMAIN_INDEX1');
exec ctx_ddl.optimize_index('USER1.DOMAIN_INDEX1','FAST');
(B)
exec dbms_utility.exec_ddl_statement('alter index USER1.DOMAIN_INDEX1 rebuild online parameters(''sync'')');
exec dbms_utility.exec_ddl_statement('alter index USER1.DOMAIN_INDEX1 rebuild online parameters(''optimize fast'')');
March 20, 2004 - 9:50 am UTC
q1) one is easier to code then the other.
q2) see #1
q3) if you maintain statspacks over time and you see your lios going up measurably for you text queries, time to take a look. if you have a system whereby you bulk add lots of stuff at once -- that would be a good time to look as well.
q4) no, i do not use nologging very much. it is the grand EXCEPTION, not the rule. I use it when it is necessary (really big load that I can schedule a backup after)
"alter index rebuild online" locking rows
Tri, April 02, 2004 - 1:02 pm UTC
Hi Tom,
Thank you for this wonderful site.
Some background info:
We have an intermedia index (Oracle version 8.1.7.4) created like this:
create index members_im_search_idx
on members_intermedia(intermedia_dummy_indicator)
indextype is ctxsys.context
parameters('datastore members_con_mcd
storage members_con_bs
filter ctxsys.null_filter
section group members_con_bsg
wordlist members_con_wl
memory 50M');
Please let me know if you need to see how some of these parameters are set (I assume you don't need them for my questions below). This "members_intermedia" table is pretty much a shadow table for the real "members" table (btw, each of these tables contains millions of rows). It has a few columns from the real members table that we want to search upon, plus a dummy column that the index is built upon (as you can see in the create statement above). Rows in this members_intermedia table is synched with the real members table via update and insert triggers, and the dummy column value is reset/set for such rows. We chose to go with this shadowing approach so we can deploy the index without any downtime (we basically didn't turn on the update/insert triggers until the index creation is done initially) and if we decide to add more columns to the 2 tables in the future, we don't need to rebuild the index from scratch again.
We currently synch the index every hour via a dbms_job with:
dbms_utility.exec_ddl_statement('ALTER INDEX MEMBERS_IM_SEARCH_IDX REBUILD ONLINE PARAMETERS(''SYNC MEMORY 50M'')');
No other work has been done to the index. The index has been up and running for 3+ months now.
Sometimes, there are thousands of new or updated rows to be synched with. What we are seeing is during the index resynch process, a few handful of transactions into the real members table are being blocked. Also, the synch is taking a lot of gets (about 1.5 millions), which may or may not be a big deal given we have lots of rows for the index to work with.
My questions:
1) How are the rows being blocked and is the blocking that we are seeing preventable at all? (if so, please tell)
2) From what I've read on this site, it looks like we need to "optimize" the index in order to lower the LIO for future synchs?
Any other observations you may have is greatly appreciated.
April 02, 2004 - 1:53 pm UTC
1) to diagnose the blocking, we'd need to see the implementation - how is that shadow table that the index is really on maintained. what operations are being blocked on the main table -- and are all operations of that type being blocked.
2) given your batch sizes -- maybe, maybe not (i tend to sync much less data much more often -- leading to a break down over time, that and I UPDATE the indexed data leading to false positives that are discovered after fetching the source document). I would do this -- get a measure of the average LIOs for your big queries over time (stats pack would be a good way) and compare the before/afters.
Additional Information
Tri, April 02, 2004 - 9:20 pm UTC
Here's the implementation you requested:
CREATE TABLE members_intermedia AS
SELECT
member_id,
'A' as intermedia_dummy_indicator,
SYSDATE AS last_update,
last_name,
first_names,
email
FROM
members
;
ALTER TABLE members_intermedia add constraint members_intermedia_pk primary key (member_id);
This shadow table members_intermedia is maintained by 2 triggers on the real members table. Here's the meat of the update trigger:
-- ... lengthly logic (see description below)
IF intermedia_bool
THEN
UPDATE members_intermedia
SET
intermedia_dummy_indicator = 'A',
last_update = SYSDATE,
last_name = :NEW.last_name,
first_names = :NEW.first_names,
email = :NEW.email
WHERE
member_id = :NEW.member_id
;
END IF;
intermedia_bool is set to true when :OLD.last_name <> :NEW.last_name, or :OLD.first_names <> :NEW.first_names, or :OLD.email <> :NEW.email. There are other unrelated logic happening in the trigger to insert into a separate audit table when these and other column values are changed. I assume that's all irrelevant to this discussion.
The meat of the insert trigger:
INSERT INTO members_intermedia (
member_id,
intermedia_dummy_indicator,
last_update,
last_name,
first_names,
email
) VALUES (
:NEW.member_id,
'A',
SYSDATE,
:NEW.last_name,
:NEW.first_names,
:NEW.email
);
We notice the blocking transactions seems to be exactly the update statement in the trigger above:
UPDATE MEMBERS_INTERMEDIA
SET INTERMEDIA_DUMMY_INDICATOR='A',LAST_UPDATE=SYSDATE,
LAST_NAME=:b4,FIRST_NAMES=:b5,EMAIL=:b6 WHERE MEMBER_ID = :b7
These update statements are blocked while this SQL is executing:
insert into "GV2"."DR$MEMBERS_IM_SEARCH_IDX$I"
values (:token, :ttype, :first, :last, :count, :data)
This SQL is not in our application source code, but I
guess that it is part of what Oracle itself does during the index rebuid.
April 03, 2004 - 8:48 am UTC
that would indicate that Oracle Text is indexing a row that someone it trying to update at that point in time.
The indexing routine has most likely locked the rows it is working on -- this can be confirmed by running a sync. In fact, I just did and
ops$tkyte@ORA9IR2> create index t_idx on t(y) indextype is ctxsys.context;
Index created.
ops$tkyte@ORA9IR2> insert into t values ( 1, 'how now brown cow' );
1 row created.
ops$tkyte@ORA9IR2> commit;
Commit complete.
ops$tkyte@ORA9IR2> @trace
Session altered.
ops$tkyte@ORA9IR2> alter index t_idx rebuild online parameters( 'sync memory 50m' );
the trace file ultimately shows:
...
select 1
from
"OPS$TKYTE"."T" where rowid = :RID for update nowait
So, the syncing is locking the rows it is processing to ensure they are stable (so we don't miss a change made during this sync - we wouldn't be able to see the uncommitted changes)
Now, if this is ongoing problem -- one thing we can do is "background" the shadow table maintenance. since the index is updated infrequently in the first place, deferring the shadow table maintainance should not be an issue.
You can either
a) put the information needed to do the update into a 'scratch job table', and submit a job to process it, eg:
dbms_job.submit( l_job, 'update_shadow(JOB);' );
insert into scratch (pk, otherdata....) values ( l_job, ..... );
and have update_shadow:
procedure shadow( p_job in number )
is
l_rec scratch%rowtype;
begin
select * into l_rec from scratch where pk =p_job;
do the update, so what if it blocks for a bit, no humans involved;
delete from scratch where pk = p_job;
commit;
end;
b) have the trigger do a select for update on the row it wants to update and if it gets it -- do the update, else do the above. (so you are using jobs as little as you need to)
Additional Information
Tri, April 02, 2004 - 9:22 pm UTC
Forgot to mention that the insert trigger is:
AFTER INSERT ON members FOR EACH ROW
while the update trigger is:
BEFORE UPDATE ON members FOR EACH ROW
Gr8
Kiran Kumar Sabbisetti, April 05, 2004 - 2:31 am UTC
This thread is very useful to understand the CTX_DDL.
A reader, May 18, 2004 - 7:35 pm UTC
Hi Tom,
please,
how can we show text from pdf documents
This queyr
select * from web.test where contains( test,'Oracle') > 0
Returns three pdfs archives but you can't see too much in blob binary, how can you show
50 characters after where the word was found and 50 characters before, in text readable.
If you can please
May 19, 2004 - 7:44 am UTC
we do not have a pdf "parser".
you would/could use the inso filter to extract the text, index the clear text instead of the pdf. get the hit on the text (which of course would point to the pdf) and ask oracle text to mark up the clear text -- but to rewrite the pdf itself, that won't be happening inside the database like that.
this approach would be akin to what google, the gold standard for searching, does.
A reader, May 19, 2004 - 9:15 am UTC
A reader, May 19, 2004 - 10:44 am UTC
HI Tom sorry,
I searched again the documentation, and didn't found what you say.
"you would/could use the inso filter to extract the text"
Previous to asking the only thing I found was this
declare
resarr ctx_query.browse_tab;
begin
ctx_query.browse_words('SEARCH_IDX','Database',resarr,10,CTX_QUERY.BROWSE_AROUND);
for i in 1..resarr.count loop
dbms_output.put_line(resarr(i).word || ':' || resarr(i).doc_count);
end loop;
end;
/
But this didn't work because return this kind of output
Ð:2
DATABASEADMINISTRATORÕS:1
ÆÊÍ:1
Could you please giveme the command to extract the text from a blob column where there is a pdf, doc, etc. document.
Thank you
May 19, 2004 - 11:09 am UTC
ctx_doc.filter
read about the ctx_doc package.
A reader, May 19, 2004 - 1:10 pm UTC
Thanks Tom I should be blind, that was the answer :)
You are the man!
When export....
A reader, June 08, 2004 - 11:12 am UTC
Hi Tom,
This CREATE INDEX.... Query on CLOB, create some tables. with some data.
for example
table name: DR$SEARCH_IDX$R
data :
row_no data
-------- ------
1 (ORABLOB)
2 (ORABLOB)
My qns is
when i am exporting the SCHEMA, these tables are also
exported.
when i am importing to another SCHEMA these tables
imported with data.
Even after, ALTER INDEX.... I cant able to find this.
Kindly explain about this
thanks
June 08, 2004 - 12:56 pm UTC
not sure what you want explained?
When export....
A reader, June 09, 2004 - 8:20 am UTC
Hi Tom,
When I am using
" create index search_idx on ctx_demo(text) indextype is ctxsys.context"
this Query oracle creates some tables automatically as i said above.
When i am exporting this is also exported.
When i am importing this tablles only imported not data.( some other schema)
When I am using
" alter index search_idx rebuild parameters( 'sync' )"
The tables data are not filled as old schema.
SQL> select * from DR$SEARCH_IDX$I;
SP2-0678: Column or attribute type can not be displayed by SQL*Plus
SQL> select * from DR$SEARCH_IDX$K;
no rows selected
SQL> select * from DR$SEARCH_IDX$N;
no rows selected
SQL> select * from DR$SEARCH_IDX$R;
SP2-0678: Column or attribute type can not be displayed by SQL*Plus
My question are
1. Should I export these file also or need be excluded?
2. in the second schema, where i am importing, Whether query is re-created?
June 09, 2004 - 9:36 am UTC
No, those tables should not be exported/imported. just the index definition (in fact, in 9ir2 -- i cannot export them at all, don't know what version you have, you never said).
only the index definition should have been exported.
ops$tkyte@ORA9IR2> create table t as
2 select * from all_objects;
Table created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create index t_idx on t(object_name) indextype is ctxsys.context;
Index created.
ops$tkyte@ORA9IR2> @dbls
ops$tkyte@ORA9IR2> @sset
ops$tkyte@ORA9IR2> store set &SETTINGS rep
Wrote file /tmp/xtmpx.sql
ops$tkyte@ORA9IR2> set linesize 100
ops$tkyte@ORA9IR2> column object_name format a30
ops$tkyte@ORA9IR2> column tablespace_name format a30
ops$tkyte@ORA9IR2> column object_type format a12
ops$tkyte@ORA9IR2> column status format a1
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> break on object_type skip 1
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select object_type, object_name,
2 decode( status, 'INVALID', '*', '' ) status,
3 decode( object_type,
4 'TABLE', (select tablespace_name from user_tables where table_name = object_name),
5 'INDEX', (select tablespace_name from user_indexes where index_name = object_name),
6 'LOB', (select tablespace_name from user_segments where segment_name = object_name),
7 null ) tablespace_name
8 from user_objects a
9 order by object_type, object_name
10 /
OBJECT_TYPE OBJECT_NAME S TABLESPACE_NAME
------------ ------------------------------ - ------------------------------
INDEX DR$T_IDX$X USERS
SYS_IOT_TOP_47744 USERS
SYS_IOT_TOP_47749 USERS
T_IDX
LOB SYS_LOB0000047741C00006$$ USERS
SYS_LOB0000047746C00002$$ USERS
TABLE DR$T_IDX$I USERS
DR$T_IDX$K
DR$T_IDX$N
DR$T_IDX$R USERS
T USERS
11 rows selected.
ops$tkyte@ORA9IR2> column status format a10
ops$tkyte@ORA9IR2> @rset
ops$tkyte@ORA9IR2> set termout off
ops$tkyte@ORA9IR2> select count(*) from DR$T_IDX$I;
COUNT(*)
----------
18706
ops$tkyte@ORA9IR2> !exp userid=/ 'owner=ops$tkyte'
Export: Release 9.2.0.5.0 - Production on Wed Jun 9 09:16:20 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production
Export done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user OPS$TKYTE
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user OPS$TKYTE
About to export OPS$TKYTE's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export OPS$TKYTE's tables via Conventional Path ...
. . exporting table T 32071 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.
ops$tkyte@ORA9IR2> drop table t;
Table dropped.
ops$tkyte@ORA9IR2> @dbls
ops$tkyte@ORA9IR2> @sset
ops$tkyte@ORA9IR2> store set &SETTINGS rep
Wrote file /tmp/xtmpx.sql
ops$tkyte@ORA9IR2> set linesize 100
ops$tkyte@ORA9IR2> column object_name format a30
ops$tkyte@ORA9IR2> column tablespace_name format a30
ops$tkyte@ORA9IR2> column object_type format a12
ops$tkyte@ORA9IR2> column status format a1
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> break on object_type skip 1
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select object_type, object_name,
2 decode( status, 'INVALID', '*', '' ) status,
3 decode( object_type,
4 'TABLE', (select tablespace_name from user_tables where table_name = object_name),
5 'INDEX', (select tablespace_name from user_indexes where index_name = object_name),
6 'LOB', (select tablespace_name from user_segments where segment_name = object_name),
7 null ) tablespace_name
8 from user_objects a
9 order by object_type, object_name
10 /
no rows selected
ops$tkyte@ORA9IR2> column status format a10
ops$tkyte@ORA9IR2> @rset
ops$tkyte@ORA9IR2> set termout off
ops$tkyte@ORA9IR2> !imp userid=/ full=y
Import: Release 9.2.0.5.0 - Production on Wed Jun 9 09:16:38 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production
Export file created by EXPORT:V09.02.00 via conventional path
import done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
. importing OPS$TKYTE's objects into OPS$TKYTE
. . importing table "T" 32071 rows imported
Import terminated successfully without warnings.
ops$tkyte@ORA9IR2> @dbls
ops$tkyte@ORA9IR2> @sset
ops$tkyte@ORA9IR2> store set &SETTINGS rep
Wrote file /tmp/xtmpx.sql
ops$tkyte@ORA9IR2> set linesize 100
ops$tkyte@ORA9IR2> column object_name format a30
ops$tkyte@ORA9IR2> column tablespace_name format a30
ops$tkyte@ORA9IR2> column object_type format a12
ops$tkyte@ORA9IR2> column status format a1
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> break on object_type skip 1
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select object_type, object_name,
2 decode( status, 'INVALID', '*', '' ) status,
3 decode( object_type,
4 'TABLE', (select tablespace_name from user_tables where table_name = object_name),
5 'INDEX', (select tablespace_name from user_indexes where index_name = object_name),
6 'LOB', (select tablespace_name from user_segments where segment_name = object_name),
7 null ) tablespace_name
8 from user_objects a
9 order by object_type, object_name
10 /
OBJECT_TYPE OBJECT_NAME S TABLESPACE_NAME
------------ ------------------------------ - ------------------------------
INDEX DR$T_IDX$X USERS
SYS_IOT_TOP_47757 USERS
SYS_IOT_TOP_47762 USERS
T_IDX
LOB SYS_LOB0000047754C00006$$ USERS
SYS_LOB0000047759C00002$$ USERS
TABLE DR$T_IDX$I USERS
DR$T_IDX$K
DR$T_IDX$N
DR$T_IDX$R USERS
T USERS
11 rows selected.
ops$tkyte@ORA9IR2> column status format a10
ops$tkyte@ORA9IR2> @rset
ops$tkyte@ORA9IR2> set termout off
ops$tkyte@ORA9IR2> select count(*) from DR$T_IDX$I;
COUNT(*)
----------
18706
pdf 50 characters
Raj, June 25, 2004 - 10:22 pm UTC
Hi Tom,
To one of the user questions, asked on May 18 2004, on extracting the 50 characters before and after the search word found in the PDF document, similar to what google does
you said
<quote>
we do not have a pdf "parser".
you would/could use the inso filter to extract the text, index the clear text
instead of the pdf. get the hit on the text (which of course would point to the
pdf) and ask oracle text to mark up the clear text -- but to rewrite the pdf
itself, that won't be happening inside the database like that.
this approach would be akin to what google, the gold standard for searching,
does.
</quote>
can you please explain how to extract 50 characters before and after the match word in the document. My application needs to display text with the search word highlighted- similar to google output. They are all PDF's
<quote>
..... and ask oracle text to mark up the clear text -- but to rewrite the pdf ....
</quote>
Wouldn't this give the whole document markup? I just need 50 or so characters surrouding the match word. Do I use CTX_DOC.HIGHLIGHT for this purpose?
In one another question where the user asked how to search on a table containing columns c1, c2 c3 ..cn and the search word can occur in any of these columns, the solutions you suggested is using index on a procedure that returns the lob of combined columns or creating an index on each column. (this is for oralce 8.1.6) </code>
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:440419921146 <code>
In 9i release 2 I can solve the same problem with "multi_column_datastore"
Is one solution better than the other?
Sorry I am a novice in using oracle search and stumped with the problems.
Needless to say, you are a great guy ready to help many others like me. thanks much!!
June 26, 2004 - 6:40 pm UTC
you ask Oracle text to mark up the text, you can tell us what to wrap the hits with (eg: "mark up the text with #!" for example, then the terms of interest would be marked up with #!....#1, you can then "substr").
you would filter the text
you would markup the entire document
you would then find all occurences of what you are interested in, looking for your marker.
A multi-column datastore may well be easier than writing a procedure, yes.
can we create in other tablespace?
A reader, July 02, 2004 - 9:17 am UTC
Hi Tom,
Can this index created some other tablesapce ( other than SYSTEM). In my case it has been created in SYSTEM tablespce.
1. How to create other than SYSTEM
2. How to move to other tablespce ( if created in SYSTEM).
Thank you
How drop interMedia text search Index...
peru, July 13, 2004 - 9:10 am UTC
Hi Tom,
Oracle 8.1.7
I have table with column CLOB.
I have use the following query to create index
CREATE INDEX SEARCH_IDX ON NEWNEUTRON(NCONTENU) INDEXTYPE IS CTXSYS.CONTEXT;
This will create SEARCH_IDX index along with
DR$SEARCH_IDX$X,
SYS_IL0000065942C00008$$,
SYS_IL0000068393C00006$$,
SYS_IL0000068398C00002$$
This size of index will be very high.
If I drop the index using
DROP INDEX SEARCH_IDX FORCE;
SYS_IL0000065942C00008$$ - this can not be dropped.
If I issued
ALTER INDEX SYS_IL0000065942C00008$$
I have got error
ORA-22864: cannot ALTER or DROP LOB indexes
1. How to drop this index.
2. This index occupies very high space. is it true.
Thank you
July 13, 2004 - 12:11 pm UTC
that is the index on your lob, you don't want to drop that. you'd destroy the lob.
Intermedia and VPD
Steve Kiteley, September 03, 2004 - 11:34 am UTC
If the schema owning and synchronising the index as part of a dbms_job background process has no access to the data because access is denied via a vpd policy will the index be successfully rebuilt?
September 03, 2004 - 1:29 pm UTC
a normal text index (create index i on t(x) indextype is ctxsys.context) will index the entire table successfully.
Rebuild Text Indexes ...
VKOUL, August 15, 2006 - 12:48 pm UTC
Hi Tom,
I was rebuilding some text indexes (just to test how to rebuild text indexes) and I am getting some errors/warnings thrown out as
********************************************************
SQL> alter index FT_TABLE_M5PIK100 rebuild parameters ('lexer aspen_lexer storage aspen_storage stoplist aspen_stoplist');
alter index FT_TABLE_M5PIK100 rebuild parameters ('lexer aspen_lexer storage aspen_storage stoplist
*
ERROR at line 1:
ORA-29874: warning in the execution of ODCIINDEXALTER routine
ORA-29960: line 1,
DRG-10595: ALTER INDEX FT_TABLE_M5PIK100 failed
DRG-11000: invalid keyword LEXER
**********************************************************
But when I do the following, it re-creates it successfully.
***********************************************************
SQL> alter index FT_TABLE_M5PIK100 rebuild;
Index altered.
SQL>
***********************************************************
With the successful rebuilding of index, the parameters column in IND view shows NULL, whereas with index rebuild command with "parameters" string shows the exact string I supplied (which was originally there before I messed up with rebuilding) but it did throw an error/warning ...
Is it a bug or something I am doing wrong ?
Pls. comment ...
SQL> disc
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.7.0 - Production
SQL>
August 15, 2006 - 2:09 pm UTC
Rebuild Text Indexes
VKOUL, August 15, 2006 - 2:30 pm UTC
Thanks for the reply ...
After applying REPLACE, now I am getting REPLACE embedded with other parameters ...
*********************************************************
SQL> alter index FT_TABLE_M5PIK100 rebuild parameters ('replace lexer aspen_lexer storage aspen_storage stoplist aspen_stoplist');
Index altered.
SQL> SELECT parameters FROM IND WHERE index_name = 'FT_TABLE_M5PIK100';
PARAMETERS
--------------------------------------------------------------------------------
replace lexer aspen_lexer storage aspen_storage stoplist aspen_stoplist
SQL>
***********************************************************
Is there a way to get the original perferences 'lexer aspen_lexer storage aspen_storage stoplist aspen_stoplist' ?
Thanks ...
August 15, 2006 - 2:43 pm UTC
short of "substr" or replace() - not that I am aware of.
VKOUL, August 15, 2006 - 3:27 pm UTC
What is this REPLACE doing in there, as per the documentation,it says we have to apply replace but it does not say anything about replace(), I thought replace is a embedded keyword to actually replace the perferences ...
do you think I did something wrong while applying REBUILD on that very index ?
August 15, 2006 - 3:46 pm UTC
replace is necessary in the rebuild if you want to - well - specify those things.
that link does seem to go into it?
Using dbms_utility.exec_ddl_statement
A reader, October 30, 2006 - 9:21 am UTC
Tom,
What is a correct way to use dbms_utility.exec_ddl_statement.
I want to use the procedure to submit a job that archive current logfile (to send it to another mashine) by
exec dbms_utility.exec_ddl_statement('alter system archive log current');
I get no error message, but archiving log does not happen.
If I execute alter system archive log current as the same user from sql prompt it works.
What I do wrong. I tried dbms_utility.exec_ddl_statement('alter user XYZ identified by 123'); and this worked.
I use Oracle 10g.
Thanks
October 30, 2006 - 9:46 am UTC
you should never use it, it pre-dates the implementation of execute immediate, that is what you should use
But that alter system archive log current did work, unless it raised an error. You would have to prove otherwise. Not sure what you did to say "it did not work"
A reader, October 30, 2006 - 10:32 am UTC
Thanks for quick responce.
I tried this one:
SQL> create procedure archivecurrentlog as
2 begin
3 execute immediate 'alter system archive log current';
4 end;
5 /
Procedure created.
SQL> exec archivecurrentlog
BEGIN archivecurrentlog; END;
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "*****.ARCHIVECURRENTLOG", line 3
ORA-06512: at line 1
SQL> connect / as sysdba
Enter password:
Connected.
SQL> exec *****.archivecurrentlog
PL/SQL procedure successfully completed.
Why I get "insufficient privileges" error?
The user has a DBA role and can execute archive log current statement from sqlplus prompt (but not as a procedure...)
October 30, 2006 - 12:44 pm UTC
</code>
http://asktom.oracle.com/Misc/RolesAndProcedures.html <code>
please don't do things as sysdba, dangerous, dangerous, dangerous... things work entirely differently for sysdba then they do for real users.
and that procedure failed for sysdba by the way - if it was created as you created it.
A reader, October 30, 2006 - 12:12 pm UTC
to build, rebuild or optimize
Jay, May 14, 2008 - 3:59 pm UTC
Tom,
Can you briefly explain the difference, pros and cons, of :
ctx_ddl.optimize_index('USER1.DOMAIN_INDEX1','FULL');
and
alter index USER1.DOMAIN_INDEX1 rebuild online;
The reason I ask is that the
alter index DOMAIN_INDEX1 rebuild online;
happens much quicker than the
ctx_ddl.optimize_index('DOMAIN_INDEX1','FULL');
or
ctx_ddl.optimize_index('DOMAIN_INDEX1','FAST');
for that matter.
Also, and this may be the answer, the syntax I used was:
ALTER INDEX DR$DOMAIN_INDEX1$X REBUILD ONLINE;
I would appreciate it very much if you could clear this up for me.
the back story,
The table that is indexed is heavily transient, 1000's of new rows a day and 1000's of deletes. The assumption is that the indexes are getting highly fragmented, I'm not sure if that is the case.
Right now they have us building two separate indexes and swapping them out at when fulltext searches get "too slow". Building one index, enabling it in the application and deleting the original index. I thought that just optimizing the indexes would be a better solution but it seems to be taking longer than the straight up rebuild on the index. Either alternative solution, optimize or rebuild, seems better than the current solution of building completely new indexes.
the fulltext indexes sync on commit, if that matters.
Any advice?
Thanks,
Jay
May 16, 2008 - 11:55 am UTC
...
Also, and this may be the answer, the syntax I used was:
ALTER INDEX DR$DOMAIN_INDEX1$X REBUILD ONLINE;
.....
you did nothing to the context index with that, you rebuilt a conventional b*tree index on the table DR$<domain index name>$I
you didn't touch the context index with that.
Can't execute CTX_DDL
Chris, December 21, 2009 - 2:41 pm UTC
I am unable to execute the CTX_DDL calls on a server we have. I have confirmed that CTXSYS exists and I tried what was said on this list, which was to run this statement:
grant execute on ctx_ddl to myuser
But when I run the grant I get this error:
Error report:
SQL Error: ORA-00942: table or view does not exist
00942. 00000 - "table or view does not exist"
*Cause:
Why on earth would I be getting that error on a grant of ctx_ddl?
*Action:
December 21, 2009 - 4:12 pm UTC
because you are not logged in as CTXSYS I would presume.
grant execute on T to myuser;
would be
grant execute on CURRENTLY_LOGGED_IN_USERNAME.T to myuser;
ctxsys owns that package, become ctxsys.
Can't execute CTX_DDL
Chris Holt, December 23, 2009 - 7:29 am UTC
Found out my problem with this. The issue was that the CTXSYS user account was expired and locked for some reason.
When I removed the lock and reassigned the password all worked.
Thanks for your help.
December 31, 2009 - 11:54 am UTC
nope, that by itself would not do anything, you did something more like....
logged in as ctxsys
after doing that.
convert html tags to rtf tags in oracle
Narendra Rawat, June 01, 2010 - 9:25 am UTC
Hi,
I would like to convert the html tags to rtf tags.
Is there any way or some inbuilt package in oracle which support conversion of html tags to rtf tags.
June 08, 2010 - 8:27 am UTC
no, not in that direction.
Inexplicable observation
A reader, November 23, 2010 - 11:33 am UTC
Tom,
Recently we implemented text search in a test database 10.2.0.3. I ran only like 600-700 search queries in 15 minutes but in the AWR report there are almost 200k queries of this type:
SELECT/*+DYNAMIC_SAMPLING(0) INDEX(T "DR$DOM_NAME_PERS$X")*/ DISTINCT TOKEN_TEXT FROM "TEST"."DR$DOM_NAME_PERS$I" T WHERE TOKEN_TEXT LIKE :lkexpr and (TOKEN_TYPE = 0 OR TOKEN_TYPE = 4 OR TOKEN_TYPE BETWEEN 16 AND 74);
select /*+ dynamic_sampling(0) */ data from "TEST"."DR$DOM_ADDRESS_PERS$R" where row_no = :row_no;
Are these recursive SQLs? Is the dynamic_sampling telling me something?
Thanks...
November 23, 2010 - 1:18 pm UTC
yes, it is recursive sql, sql we did to run your sql
it is telling you that you have not gathered statistics in your database on many tables (looks like the text index tables) and therefore we are doing a sample scan of the tables while hard parsing queries that use them.
Inexplicable observation
A reader, November 23, 2010 - 2:04 pm UTC
Tom,
Thank you so much for responding so quickly. Just to make sure, is DBMS_STATS the right way to gather stats on these indexes?
Thanks...
November 23, 2010 - 3:41 pm UTC