Add_FK_locks_table

View All Public Scripts Login to Run Script

Script Name Add_FK_locks_table
Visibility Unlisted - anyone with the share link can access
Description During execution of DDL to add foreign key constraint (on unindexed column), both parent and child tables are locked and cannot be read by session2.
Area SQL General / DDL
Contributor jim.dickson@cashflows.com
Created Wednesday February 28, 2018

Statement 1
```
drop   table ct 
```
```
ORA-00942: table or view does not exist 
```
More Details: https://docs.oracle.com/error-help/db/ora-00942
Statement 2
```
drop   table pt 
```
```
ORA-00942: table or view does not exist 
```
More Details: https://docs.oracle.com/error-help/db/ora-00942
Statement 3
```
purge recyclebin 
```
```
Statement processed.
```

Statement 4

create table pt ( p_pk integer not null, dt date not null, payload varchar2(40) )

Table created.

Statement 5

create unique index pki_pt on pt (p_pk)

Index created.

Statement 6

alter table pt add constraint pkc_pt primary key (p_pk) using index pki_pt

Table altered.

Statement 7

create table ct ( c_pk integer not null, p_fk integer not null, dt date not null, payload varchar2(40) )

Table created.

Statement 8

create unique index pki_ct on ct (c_pk)

Index created.

Statement 9

alter table ct add constraint pkc_ct primary key (c_pk) using index pki_ct

Table altered.

Statement 10

insert into pt (p_pk, dt, payload) 
select level - 1 
     , date'2018-01-01' + mod(level,30) 
     , rpad('*',40,'*') 
from dual  
connect by level <= 100

100 row(s) inserted.

Statement 11

insert into ct (c_pk, p_fk, dt, payload) 
select level 
     , mod(level,100) 
     , date'2018-01-01' + mod(level,30) 
     , rpad('*',40,'*') 
from dual  
connect by level <= 10000000

ORA-01536: space quota exceeded for tablespace 'LIVESQL_USERS' ORA-06512: at "SYS.DBMS_SQL", line 1721

More Details: https://docs.oracle.com/error-help/db/ora-01536

Statement 12
```
commit 
```
```
Statement processed.
```

Statement 13

CALL DBMS_STATS.gather_table_stats(USER, 'pt')

ORA-20000: Unable to analyze TABLE "APEX_PUBLIC_USER"."PT", insufficient privileges or does not exist ORA-06512: at "SYS.DBMS_STATS", line 39094
ORA-06512: at "SYS.DBMS_STATS", line 38371
ORA-06512: at "SYS.DBMS_STATS", line 38530
ORA-06512: at "SYS.DBMS_STATS", line 39076
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_SQL", line 1721

More Details: https://docs.oracle.com/error-help/db/ora-20000

Statement 14

CALL DBMS_STATS.gather_table_stats(USER, 'ct')

ORA-20000: Unable to analyze TABLE "APEX_PUBLIC_USER"."CT", insufficient privileges or does not exist ORA-06512: at "SYS.DBMS_STATS", line 39094
ORA-06512: at "SYS.DBMS_STATS", line 38371
ORA-06512: at "SYS.DBMS_STATS", line 38530
ORA-06512: at "SYS.DBMS_STATS", line 39076
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_SQL", line 1721

More Details: https://docs.oracle.com/error-help/db/ora-20000

Statement 15

select p_fk from ct 
minus 
select p_pk from pt

no data found

Statement 16

alter table ct drop constraint fk_pt

ORA-02443: Cannot drop constraint  - nonexistent constraint

More Details: https://docs.oracle.com/error-help/db/ora-02443

Statement 17

alter table ct add constraint fk_pt foreign key (p_fk) references pt (p_pk)

Table altered.

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database