9

I made terrible mistake in SQL index creation:

create index IDX_DATA_TABLE_CUSECO on DATA_TABLE (CUSTOMER_ID, SESSION_ID, CONTACT_ID)
  tablespace IDX_TABLESPACE LOCAL ;

As You can see I missed keyword "ONLINE" to create index without blocking PRODUCTION table with high usage with 600m+ records. Corrected SQL is:

create index IDX_DATA_TABLE_CUSECO on DATA_TABLE (CUSTOMER_ID, SESSION_ID, CONTACT_ID)
  tablespace IDX_TABLESPACE LOCAL ONLINE;

I was done it under PL/SQL Developer. When I was trying to stop it program stop responding and crashed.

Production system not working for 9 hours now and my boss wanna explode. :D

Is there any chance to see how many seconds/minutes/hours Oracle 11g left to process this index creation ? Or maybe is there any chance to see does Oracle still working on this request? (PL/SQL Developer crashed).

For haters: I know I should do this like mentioned here: (source)

CREATE INDEX cust_idx on customer(id) UNUSABLE LOCAL;
ALTER INDEX cust_idx REBUILD parallel 6 NOLOGGING ONLINE;
bebbo
  • 2,830
  • 1
  • 32
  • 37
WBAR
  • 4,924
  • 7
  • 47
  • 81
  • Your link does not appear to be valid. I would hope that whatever article you're looking at at least talks about the `skip_unusable_indexes` setting before telling you to create an unusable index and that it tells you to change the parallelism setting of the index once it is built. – Justin Cave Sep 11 '12 at 16:23
  • 2
    The article doesn't, but the comments do, and also explain why the advice isn't as helpful as you seem to think. I'm with Justin though, why haven't you killed the session that's running the index build? – Alex Poole Sep 11 '12 at 16:59

1 Answers1

15

You should be able to view the progress of the operation in V$SESSION_LONGOPS

SELECT sid, 
       serial#, 
       target, 
       target_desc, 
       sofar, 
       totalwork, 
       start_time, 
       time_remaining, 
       elapsed_seconds
  FROM v$session_longops
 WHERE time_remaining > 0

Of course, in a production system, I probably would have killed the session hours ago rather than letting the DDL operation continue to prevent users from accessing the application.

Justin Cave
  • 227,342
  • 24
  • 367
  • 384
  • I can't see my session because when PL/SQL Developer crash connection and session went out but when I'm trying to calculate statistics I receive an error: Error report: ORA-20000: this index object "PRODSCHM"."IDX_DATA_TABLE_CUSECO" is being online built or rebuilt – WBAR Sep 12 '12 at 00:41
  • @WBAR - I'm not sure that I understand what you're saying. The fact that the client application (PL/SQL Developer) crashed has nothing to do with whether the database session is still running. Do you see the session in v$session? – Justin Cave Sep 12 '12 at 01:24
  • 2
    @WBAR - If the session doesn't exist (and assuming this isn't a RAC system in which case you'd need to be using `gv$session` or `gv$session_longops`), it cannot possibly be holding a lock. – Justin Cave Sep 12 '12 at 13:46