-
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?
Jeff Hunter
-
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.
Radhakrishnan.M
-
useing nologging also helps a lot.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|