• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3561
  • Last Modified:

Oracle index status --- INPRGORS--

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
joe_echavarria
Asked:
joe_echavarria
  • 10
  • 9
  • 4
  • +1
1 Solution
 
DavidSenior Oracle Database AdministratorCommented:
Hmmm, possibly, "IN PROGRESS"?
0
 
slightwv (䄆 Netminder) Commented:
>>  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
 
awking00Commented:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
joe_echavarriaDatabase AdministratorAuthor Commented:
Is there a way to find out how far is the progress of the index creation ?
0
 
awking00Commented:
I have not been able to find out how much progress has been made:-(
0
 
joe_echavarriaDatabase AdministratorAuthor Commented:
I just wanted to know if there is a way to know how far is the creation of an index ?
0
 
slightwv (䄆 Netminder) Commented:
>>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
 
awking00Commented:
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
 
DavidSenior Oracle Database AdministratorCommented:
Owner has to grant SELECT on xxview to other_user, and then add a synonym, either public or private.
0
 
slightwv (䄆 Netminder) Commented:
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
 
joe_echavarriaDatabase AdministratorAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
joe_echavarriaDatabase AdministratorAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
joe_echavarriaDatabase AdministratorAuthor Commented:
The documents have different sizes.  

Pdf files are the documents.
0
 
slightwv (䄆 Netminder) Commented:
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
 
joe_echavarriaDatabase AdministratorAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
>>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
 
joe_echavarriaDatabase AdministratorAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
>>"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
 
joe_echavarriaDatabase AdministratorAuthor Commented:
How can i  verified the PDF versions are compatible with your version of Text Index?
0
 
slightwv (䄆 Netminder) Commented:
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
 
joe_echavarriaDatabase AdministratorAuthor Commented:
After 4 weeks, index was created.
0
 
joe_echavarriaDatabase AdministratorAuthor Commented:
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
 
awking00Commented:
>>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

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

  • 10
  • 9
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now