Oracle index status --- INPRGORS--

>> I would like to know what does it means when the creation of the index and the status keeps

In addition to the post above: The index is being created.

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

Is there a way to find out how far is the progress of the index creation ?

I have not been able to find out how much progress has been made:-(

ASKER

I just wanted to know if there is a way to know how far is the creation of an index ?

>>I just wanted to know if there is a way to know how far is the creation of an index ?

Check the idx_docid_count column in ctx_indexes:

select idx_name, idx_docid_count
from ctxsys.ctx_indexes where idx_name='CTXT_IDX_DVC_1'
/

You know how many rows are in the table. The count above 'should' show you how many have been processed.

slightwv,
What is the privilege needed to select on the views that belong to ctxsys? I have the select any dictionary privilege but am unable to view ctxsys.ctx_indexes (or any of the other ctxsys views).

David VanZandt

Owner has to grant SELECT on xxview to other_user, and then add a synonym, either public or private.

There is a special role for Text (and likely other CTX items): CTXAPP

http://docs.oracle.com/cd/E11882_01/text.112/e24435/admin.htm

ASKER

If i keep executing the below select the results keeps being the same, but the index status is still 'INPROGR'.

SQL> select idx_name, idx_docid_count
2 from ctxsys.ctx_indexes where idx_name='CTXT_IDX_DVC_1'
3 /

IDX_NAME IDX_DOCID_COUNT
------------------------------ ---------------
CTXT_IDX_DVC_1 93849

SQL> /

IDX_NAME IDX_DOCID_COUNT
------------------------------ ---------------
CTXT_IDX_DVC_1 93849

and the amount of data is
SQL> select count(*) from DVC_DOCUMENT_VAULT;

COUNT(*)
----------
222956

How long in between running that select?
How large are the documents you are indexing?

Are there any errors in the alert log?

Check the ctx errors view:
http://docs.oracle.com/cd/B14117_01/text.101/b10730/aviews.htm#i12656

You can see if progress is being made by seeing how many tokens (words) are being generated. If the count grows, it is still working:

Select count(*) from dr$CTXT_IDX_DVC_1$i;

Wait a few minutes, then run it again.

Also check to see if the dr table above is growing in size by querying bytes from user_objects.

Can you post your Oracle version, all 4 numbers please?

ASKER

Still having the same result for.

SQL> select idx_name, idx_docid_count
2 from ctxsys.ctx_indexes where idx_name='CTXT_IDX_DVC_1'
3 /

IDX_NAME IDX_DOCID_COUNT
------------------------------ ---------------
CTXT_IDX_DVC_1 93849

No errors in the alert log file.

This is the oracle version Oracle Database 10g Release 10.2.0.4.0

Running it keeps the same value , running after 10 minutes still the same value. SQL> select count(*) from smartsolve.DR$CTXT_IDX_DVC_1$I;

COUNT(*)
----------
1290791

And the status of the index is still INPROGR

You didn't answer the question about size of documents.

Also what type Of objects are you indexing.

I remember their being some bugs with Text in 10.2.0.4.

If this has been running for a long time, you might want to think about dropping the index and recreating it.

ASKER

The documents have different sizes.

Pdf files are the documents.

What about an average size? select avg(dbms_lob.getlength(some_column)) from some_table;

I would also make sure all the PDFs are 'supported' by the filter:
http://docs.oracle.com/cd/B19306_01/text.102/b14218/afilsupt.htm#sthref2463

Display Formats
Format Version Single-byte Asian (and Most Multi-byte) Bi-directional?
Adobe Portable Document Format (PDF) 1.1 (Acrobat 2.0) to 1.5 (Acrobat 6.0) Y Japanese, Simplified and Traditional Chinese, and Korean N

If dropping and recreating the index doesn't get you past this, I would contact Oracle Support. There is either a bug in your version or possible a 'bad' document. Support can help you trace what is happening and find the issue.

ASKER

select status from dba_indexes where index_name='CTXT_IDX_DVC_1';

The status of the index is still 'INPROGRS'.

If we execute select * from v$session order by status,username the session appears active, and the SID is 55.

If we execute
select /*+ ORDERED USE_NL(st) */ sql_text , ses.sid
from v$session ses,
v$sqltext st
where st.address = ses.sql_address
and st.hash_value=ses.sql_hash_value
and ses.sid=&&sid
order by piece;

the output is the syntax of the index creation.

But if we execute
select
sid,
message
from
v$session_longops
where
sid <> -1
order by
sid, start_time;

the session with SID 55 does not appears. It is suppose to appear because you find long DML and DDL in the v$session_longops.

Any idea why it does not appears ?

>>Any idea why it does not appears ?

Even though the 'process' takes a long time does not mean a single 'operation' takes a long time. If the whole process is made up of a lot of smaller operations, it will not be in that view.

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:751225652810

"because v$session_longops only reports long running "things""

Back to the original questipon: After this amount of time, if this is still running and the doc count is not increasing and you cannot see any database activity, there is likely something wrong with it.

If you do not want to kill it and start over for some reason, I would open an SR with Oracle Support. They will have the tools to determine if it is still actually doing anything or it is 'stuck'.

ASKER

What do you mean by "you cannot see any database activity".

The session appears ACTIVE, so it seems to be working.

Still having the same result for.

SQL> select idx_name, idx_docid_count
2 from ctxsys.ctx_indexes where idx_name='CTXT_IDX_DVC_1'
3 /

IDX_NAME IDX_DOCID_COUNT
------------------------------ ---------------
CTXT_IDX_DVC_1 93849

>>"you cannot see any database activity".

Disk IO, CPU usage, a lot of redo archiving (building a Text index generates a decent amount of redo).

>>The session appears ACTIVE, so it seems to be working.

Not if the doc count is not increasing. The count is the same as you posted in http:#a37813329 5 days ago. Since you have over 1 million records in the table, something is wrong.

Have you verified the PDF versions are compatible with your version of Text Index? You might have a newer PDF and the Indexer is hung up on it.

Working with Oracle Support can help you narrow down the exact reason it seems ot be hung up.

If you want to diagnose this yorself, again, drop the index and restart it. You can turn on Text logging and it will tell you what it is doing. Just like all logging: It takes overhead and will slow the process down some.

ASKER

How can i verified the PDF versions are compatible with your version of Text Index?

I posted the supported versions of PDF for your Oracle version in http:#a37814657.

You need to verify the PDFS stored in your database. There is no simple way to do this that I know of. you might be able to use SUBSTR on the blob but that would be a different question.

ASKER

After 4 weeks, index was created.

ASKER

I've requested that this question be closed as follows:

Accepted answer: 0 points for joe_echavarria's comment #37912561

for the following reason:

It just needed more time for it to be created.