Live SQL

disable table locks to prevent insert append

View All Public Scripts Login to Run Script

Script Name disable table locks to prevent insert append
Visibility Unlisted - anyone with the share link can access
Description cf. https://jonathanlewis.wordpress.com/2019/03/08/append-hint/
Area SQL General / Data Manipulation
Contributor Franck Pachot
Created Friday March 08, 2019

Statement 1

create table DEMO (n number)

ORA-00955: name is already used by an existing object

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

Statement 2

insert /*+ append */ into DEMO select rownum from xmltable('1 to 1000')

1000 row(s) inserted.

Statement 3
```
select count(*) from DEMO
```
```
COUNT(*)
3000
```
Statement 4
```
commit
```
```
Statement processed.
```

Statement 5

alter table DEMO disable table lock

ORA-00069: cannot acquire lock -- table locks disabled for DEMO

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

Statement 6

insert /*+ append */ into DEMO select rownum from xmltable('1 to 1000')

1000 row(s) inserted.

Statement 7
```
select count(*) from DEMO
```
```
COUNT(*)
4000
```

Additional Information

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