Solved

Oracle index status  --- INPRGORS--

Posted on 2012-04-03
26
2,658 Views
Last Modified: 2012-06-27
Hi,

  I would like to know what does it means when the creation of the index and the status keeps to be "INPRGORS".

CREATE INDEX CTXT_IDX_DVC_1
ON DVC_DOCUMENT_VAULT (DVC_CONTENT)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS('Sync (ON COMMIT)')
NOPARALLEL



SQL> select status from dba_indexes where index_name='CTXT_IDX_DVC_1';

STATUS
--------
INPROGRS
0
Comment
Question by:joe_echavarria
  • 10
  • 9
  • 4
  • +1
26 Comments
 
LVL 23

Expert Comment

by:David
ID: 37803226
Hmmm, possibly, "IN PROGRESS"?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37803241
>>  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.
0
 
LVL 31

Accepted Solution

by:
awking00 earned 500 total points
ID: 37807776
Creating function-based domain indexes on fields that contain large amounts of data such as clob or xmldata datatype fields can take a substantially long time, which is what I expect is happening in your case. The index is still being created so the status shows as in progress. I have actually had the experience of creating an xmlindex on an xmldata type column that took more than three weeks.
0
 
LVL 1

Author Comment

by:joe_echavarria
ID: 37808194
Is there a way to find out how far is the progress of the index creation ?
0
 
LVL 31

Expert Comment

by:awking00
ID: 37808231
I have not been able to find out how much progress has been made:-(
0
 
LVL 1

Author Comment

by:joe_echavarria
ID: 37808329
I just wanted to know if there is a way to know how far is the creation of an index ?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37808360
>>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.
0
 
LVL 31

Expert Comment

by:awking00
ID: 37812235
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).
0
 
LVL 23

Expert Comment

by:David
ID: 37812242
Owner has to grant SELECT on xxview to other_user, and then add a synonym, either public or private.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37812269
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
0
 
LVL 1

Author Comment

by:joe_echavarria
ID: 37813329
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
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37813387
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?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 1

Author Comment

by:joe_echavarria
ID: 37814089
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
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37814122
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.
0
 
LVL 1

Author Comment

by:joe_echavarria
ID: 37814151
The documents have different sizes.  

Pdf files are the documents.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37814657
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.
0
 
LVL 1

Author Comment

by:joe_echavarria
ID: 37827477
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  ?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37827520
>>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'.
0
 
LVL 1

Author Comment

by:joe_echavarria
ID: 37827541
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
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37827568
>>"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.
0
 
LVL 1

Author Comment

by:joe_echavarria
ID: 37827999
How can i  verified the PDF versions are compatible with your version of Text Index?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37828013
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.
0
 
LVL 1

Author Comment

by:joe_echavarria
ID: 37912561
After 4 weeks, index was created.
0
 
LVL 1

Author Comment

by:joe_echavarria
ID: 37914421
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.
0
 
LVL 31

Expert Comment

by:awking00
ID: 37914422
>>After 4 weeks, index was created.<<
>>It just needed more time for it to be created<<
Isn't that what I said in ID: 37807776?
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now