Oracle Delete Statements
Version 19.2.0.1

Library Note Morgan's Library Page Header
For how many years have you been working with physical servers that are starving your database of the memory necessary to deploy important new performance features such as the Result Cache, Memoptimize Pool, In-Memory Aggregation, In-Memory Column Store, and Full Database Caching? Too long? Contact me to learn how to improve all queries ... not just some queries.
 
Basic Delete Statements
Delete All Rows DELETE [<schema_name>.]<table_name>
or
DELETE FROM [<schema_name>.]<table_name>;
conn uwclass/uwclass@pdbdev

CREATE TABLE t AS
SELECT *
FROM all_tables;

SELECT COUNT(*)
FROM t;

DELETE FROM t;

COMMIT;

SELECT COUNT(*)
FROM t;
Delete Selective Rows DELETE FROM [<schema_name>.]<table_name>
WHERE <filter_condition>;
conn uwclass/uwclass@pdbdev

CREATE TABLE t AS
SELECT *
FROM all_tables;

SELECT COUNT(*)
FROM t;

DELETE FROM t
WHERE table_name LIKE '%MAP';

COMMIT;

SELECT COUNT(*)
FROM t;
Delete From A SELECT Statement: May be Selective or Non-Selective Depending on whether the SELECT statement contains a WHERE clause DELETE FROM (<SELECT Statement>);
conn uwclass/uwclass@pdbdev

CREATE TABLE t AS
SELECT *
FROM all_tables;

SELECT COUNT(*)
FROM t;

DELETE FROM (SELECT * FROM t WHERE table_name LIKE '%MAP');

SELECT COUNT(*)
FROM t;
Delete With Returning Clause DELETE FROM (<SELECT Statement>);
conn uwclass/uwclass@pdbdev

CREATE TABLE t AS
SELECT *
FROM all_tables;

set serveroutput on

DECLARE
 r  urowid;
BEGIN
  DELETE FROM t
  WHERE rownum = 1
  RETURNING rowid INTO r;

  dbms_output.put_line(r);
END;
/
Delete Restricted to a Partition in a Partitioned Table DELETE FROM [<schema_name>.]<table_name>
PARTITION (<partition_name>);
DELETE FROM sales PARTITION (q1_2001_invoices);
Delete from a Remote Database DELETE FROM [<schema_name>.]<table_name>@<database_link>
DELETE FROM t@remote_db;

Related Topics
Built-in Functions
Conditions
Database Link
Error Logging
INSERT Statements
Joins
MERGE Statements
Nested Tables
Object Tables
Partitioned Tables
SELECT Statements
Types
UPDATE Statements
WHERE Clause
What's New In 18cR3
What's New In 19cR2

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2019 Daniel A. Morgan All Rights Reserved