Creating index/pk in a huge table is taking too long. I am using Oracle. How do I know if it is going well?

Question

Hi,

I have a really huge table, with ~200 million rows. It had no index/pk at all. Selects in this table were (obviously) running slow. I decided to create a PK using 3 columns. I did it in a test environment that has a smaller version of this table and it worked like a charm.

So, before going home i did a ALTER TABLE HUGETABLE ADD CONSTRAINT PK_HUGETABLE PRIMARY KEY (ID1, ID2, ID3);

I expected it to run over the night, but it has been already over 24 hours and it still running.

I know if i had kept the Session Id before starting my query i would be able to track it at V$SESSION_LONGOPS. But i didn't.

Is there any way to check how is my query going or how long will it still take?

Answer 1

You should still be able to query V$SESSION_LONGOPS. If you run something like

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

you'll probably see only one session that started yesterday and the other columns should corroborate that with indications that the session has done a lot of work. The MESSAGE should also indicate something like a full scan on HUGETABLE.