I've got this table with millions of rows that I loaded via the append
hint.
Now I go to turn the constraints back on, I get the following:
2012-03-23 01:08:53,065 ERROR [SQL] [main]: Error in executing SQL
:
alter table summarydata add constraint table_pk primary key (a, b, c, d, e, f)
java.sql.SQLException: ORA-30032: the suspended (resumable) statement has timed out
ORA-01652: unable to extend temp segment by 128 in tablespace MY_TEMP_TABLESPACE
Are there any best practices to avoid this? I'm adding some more datafiles, but why would this even be a problem?
The error is related to the temporay tablespace, not the data tablespace that holds the table and/or the primary key. You need to increase the size of the MY_TEMP_TABLESPACE
so it has enough space to do the comparison, as @Lamak indicated.
If you don't know bow much space it wil need the you can turn AUTOEXTEND
on as @DCookie said, and if it already on (for the temp, not data, tablespace!) then check the MAXSIZE
setting and increase if necessary. On some platforms the maximum size of a datafile (or for a temp tablespace, hopefully a tempfile) is constrainted so you may need to add additional tempfiles.
If this is a one-off task and you don't want temp to stay big you can shrink it afterwards, but you also have the option to: create a new, large temporary tablespace; modify the user so it uses that instead; build the constraint; modify the user back to the original temp area; drop the new, large temp tablespace.