Alter table add primary key

[tompk]

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

[marist89]

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?

[DBAtrix]

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.

[omegamark]

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.

[sysdba]

useing nologging also helps a lot.