Home » SQL & PL/SQL » SQL & PL/SQL » Primary Key Creation on a Large Table (11.1.0.6/11.1.0.7, RHEL 64BIT)
Primary Key Creation on a Large Table [message #391787] Fri, 13 March 2009 08:38 Go to next message
andydba
Messages: 34
Registered: September 2008
Member

Hello All,

I have a table with more than 600 million rows. Some how the primary key constraint on the table was dropped and I have to create the primary key constraint on this table.

I ran that alter table command that ran for more than a day and I had to cancel that as I had no idea what's going on. I wasnt' even able to look up into v$session_longops about the expected end time. When ever I ran my query against v$session_longops, that query never came back.

I tried creating an empty table with primary key constraint and tried inserting data from original table into new table. That also failed with an ORA-600 error. ORA-600 error came because of the XMLTYPE column in the table.

What is the best way to recreate the primary key constraint on such a big table.

Thanks
Re: Primary Key Creation on a Large Table [message #391790 is a reply to message #391787] Fri, 13 March 2009 09:03 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>I have a table with more than 600 million rows.
Relatively speaking a 600 million row table is not that large.
It should take less than a few minutes to do a Full Table Scan.
Something is jamming up the operation.
Enable SQL TRACE to gain some visibility into what is & is not happening.
Re: Primary Key Creation on a Large Table [message #391839 is a reply to message #391790] Fri, 13 March 2009 16:41 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Creating an index means the data needs to be sorted, which takes memory and - when you run out of memory - TEMP space. Ultimately you will also run out of TEMP space.

Depending on your needs, it can be a good idea to create a GLOBAL PARTITIONED index. Such an index might - for instance - be hash-partitioned on the index key.

This divide and conquer approach may also improve the speed of the index creation.

Having said all that, it looks as though your problem is a bit more fundamental. If you cannot create an index on an empty table, I'd be raising an SR with Oracle.

Ross Leishman
Previous Topic: About the invoker privilege
Next Topic: Procedure needed without creating arrays
Goto Forum:
  


Current Time: Mon Dec 05 02:56:45 CST 2016

Total time taken to generate the page: 0.04370 seconds