Oracle 23c New Features for DBA'sFrom 12c onwards,
Oracle 12c introduced truncate with cascade so when truncate operates on master table the child table rows also get truncated automatically.
SCOTT@PDB > create table MASTER (
2 ID number generated always as identity PRIMARY KEY,
3 NAME VARCHAR2(20)
4 );
Table created.
SCOTT@PDB > create table CHILD (
2 ID number generated always as identity PRIMARY KEY,
3 NAMECHILD VARCHAR2(20)
4 );
Table created.
SCOTT@PDB > alter table CHILD add constraint FK_MASTER FOREIGN KEY (ID) references MASTER(ID) ON DELETE CASCADE;
Table altered.
SCOTT@PDB > insert into MASTER (NAME) values ('Geek DBA');
1 row created.
SCOTT@PDB > insert into CHILD (NAMECHILD) values ('Geek DBA');
1 row created.
SCOTT@PDB > commit;
Commit complete.
SCOTT@PDB > select * from MASTER;
ID NAME
---------- --------------------
1 Geek DBA
SCOTT@PDB > select * from CHILD;
ID NAMECHILD
---------- --------------------
1 Geek DBA
SCOTT@PDB > truncate table MASTER cascade;
Table truncated.
SCOTT@PDB > select * from MASTER;
no rows selected
SCOTT@PDB > select * from CHILD;
no rows selected
Note: Truncate cascade only works when you have the referential integrity with on delete cascade on
Follow Me!!!