Alter table add primary key

Hi,

I am trying to add a primary key on a table that has got 30 million records .

Here is my sql :
alter table tableA add constraint pk_tableA
primary key (col1,col2) using index tablespace tablespaceA;

this job is not completeing has been running last two hours and not finishing yet .

In v$session this session is active but i am not able find the
sql_text from v$sqltext .

Could any one can tell how will i make sure this sql is creating the primary key or not ?

Thanks
tom

30 million rows is a pretty big table. It might take it a while to index that many rows.

Some things to look at to make sure your process is still running:
v$session
v$process

look at the processes from the OS, are they taking any CPU?
is there any disk activity?

Check the temp seg usage. run 2x to see if usage is increasing or decreasing. Also take a look in the alert.log to see if you have maxed out anything. Oracle can take a long time to unwind transactions & operations. There are lots of ways to speed up index builds.

I think i got this from MetaLink.
SELECT sess.username, PROC.SPID osprc, sess.sid, sess.serial#, SESS.STATUS,
ROUND( BLOCKS*BLOCKSIZE/1024 ) KBYTES,
TO_CHAR(sess.LOGON_TIME, 'DD-MON-YY HH24:MI:SS') login_time
FROM v$session sess, V$PROCESS PROC,
(SELECT
USER, SESSION_ADDR, SESSION_NUM, SQLADDR, SQLHASH, TABLESPACE, CONTENTS,
SUM(EXTENTS) EXTENTS, SUM(BLOCKS) BLOCKS
FROM V$SORT_USAGE
GROUP BY
USER, SESSION_ADDR, SESSION_NUM, SQLADDR, SQLHASH, TABLESPACE, CONTENTS
) sortu,
(select bytes/blocks blocksize from sys.dba_segments where rownum=1)
WHERE sess.saddr = sortu.session_addr
and SESS.PADDR=PROC.ADDR(+);

D.

When you create index so big be careful to set the sort_area_size,if possible try doing a parallel querry.Temp tablespace extents should be big.

If you use these methods you can create your index in a short time.

useing nologging also helps a lot.