How do I check index building status on Oracle 11?

oracle plsql oracle11g indexing plsqldeveloper
WBAR picture WBAR · Sep 11, 2012 · Viewed 21k times · Source

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;

Answer

Justin Cave picture Justin Cave · Sep 11, 2012

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.

Related questions

Using bind variables with dynamic SELECT INTO clause in PL/SQL

I have a question regarding where bind variables can be used in a dynamic SQL statement in PL/SQL. For example, I know that this is valid: CREATE OR REPLACE FUNCTION get_num_of_employees (p_loc VARCHAR2, p_job …

Read More
ORA-29283: invalid file operation ORA-06512: at "SYS.UTL_FILE", line 536

Below is the code i use to extract data from a table to a flat file. BEGIN DECLARE file_name VARCHAR2(50); file_handle utl_file.file_type; BEGIN file_name := 'table.txt'; file_handle := utl_file.fopen('SEND',file_name,…

Read More
Sleep function in ORACLE

I need execute an SQL query in ORACLE it takes a certain amount of time. So I wrote this function: CREATE OR REPLACE FUNCTION MYSCHEMA.TEST_SLEEP ( TIME_ IN NUMBER ) RETURN INTEGER IS BEGIN DBMS_LOCK.sleep(seconds => TIME_); …

Read More