One option: Run trace for a such alter statement (not necessarily on production) and look at recursive SQL containing both referenced tables. If I remember correctly it was some kind of outer join between the tables. OK it is something like this: select /*+ all_rows ordered */ A.rowid, :1, :2, :3 from "GINTS"."T77" A , "GINTS"."IEDZIVOTAJI" B where( "A"."ID" is not null) and( "B"."IED_ID" (+)= "A"."ID") and( "B"."IED_ID" is null) So knowing one, you can quite easily guess what others will be. If the join is done using nested loops then you won't find anything in v$session_longops. Gints Plivna http://www.gplivna.eu 2011/12/15 Rich <richa03@xxxxxxxxx> > Hi list, > I'm trying to determine how much time will be required to build a > constraint - something like: > ALTER TABLE <TABLE_NAME> ADD CONSTRAINT <NAME> FOREIGN KEY (<FK_NAME>) > REFERENCES <REF_TABLE_NAME> (<REF_COL_NAME) ENABLE; > > Testing this, I don't see any operation in v$session_longops, however > the test runs for hours. > There are many of these and we have limited amounts of time to do them > - we can do them in batches over time, however, we can't exceed our > windows due to performance. > > I also don't see anything in v$sql_plan for this SQL_ID... > > How, exactly, does Oracle build a constraint? > Ie, what plan/operations does it use. > Is there any way I can estimate time (blocks read, etc.) prior to > building the constraint? > > Thanks, > Rich > -- > //www.freelists.org/webpage/oracle-l > > > -- //www.freelists.org/webpage/oracle-l