Oracle Database Discussions

Hello @ll,

during a database update I lost a foreign key between two tables. The tables are called werteart and werteartarchiv_pt. Because of its size, werteartarchiv_pt is a partitioned table. The missing foreign key was a constraint on table werteartarchiv_pt referencing werteart.

Some statistics about the sizes of the mentioned tables:
werteart 22 MB
werteartarchiv_pt 223 GB
werteartarchiv_pt (Index) 243 GB

I tried to create the foreign key again, but it failed with the following error (Excuses for the german error message):

sqlplus ORA-00604: Fehler auf rekursiver SQL-Ebene 1
sqlplus ORA-01652: Temp-Segment kann nicht um 128 in Tablespace TEMPS00 erweitert

The statement I used:

alter table werteartarchiv_pt
add constraint werteartarchiv_pt_fk1
foreign key (schiene, werteartadresse, merkmale)
references werteart (schiene, werteartadresse, merkmale)
on delete cascade
initially deferred deferrable;

So the problem seems to be, that Oracle needs a lot of temporary tablespace to generate the foreign key and I do not know how much and why.

My questions now are, and hopefully someone is here, who can answer all or a part of it:

1) Why does Oracle need temporary tablespace to create the foreign key? The foreign key uses the same columns like the primary key.

2a) Is it possible to tweak the statement without using the temporary tablespace?

2b) If it is not possible to avoid the usage of the temporary tablespace, is there a formula how to calculate the needed temporary tablespace?

3) Is it possible to modify data in the tables while the foreign key is created or is the whole table locked during the process?

Any help or hint is appreciated.

Regards,
Bjoern