Soft Delete

 Many applications are using soft delete approach,

So many applications implement a "soft delete" instead. This adds an "is deleted" flag to your tables. For example:

alter table toys add is_deleted varchar2(1) default 'N';

When adding new rows, ensure this value is N (No):

delete toys;

insert into toys values ('Baby Turtle', 0.01, 'N');
insert into toys values ('Miss Snuggles', 0.51, 'N');
insert into toys values ('Cuteasaurus', 10.01, 'N');
insert into toys values ('Sir Stripypants', 14.03, 'N');
insert into toys values ('Purple Ninja', 14.22, 'N');

select * from toys;

commit;

Now, to "delete" rows, you run an update. This sets the deleted flag to Yes:

update toys
set    is_deleted = 'Y'
where  toy_name = 'Cuteasaurus';

select * from toys;

But now you need to filter out the "deleted" values in most queries. This makes your code more complicated. To get only active rows, you need to add a where clause to all these queries. For example:

select * from toys
where is_deleted = 'N';

Luckily Oracle Database offers many ways to simplify this, including:

• Using views
• Virtual Private Database (VPD)
• In-Database Archiving

Views

The most universal way is to create a view over the top of the table. This contains the query excluding "deleted" rows. You change your application to query the view instead of the table.

For example:

create or replace view active_toys as
  select * from toys
  where is_deleted = 'N';

select * from active_toys;

Ensuring all code uses the view can be hard to police. And it can be a lot of work to change your code to use the view. This makes it tough to add to existing applications.

VPD

You use VPD to control which users can see which rows. It does this by adding where clauses to your queries based on policies. This is primarily a security feature, allowing you to stop people seeing sensitive data without clearance. But you can also use it to manage soft-deletion.

For more on VPD, read this article.

In-Database Archiving

Oracle Database 12c introduced In-Database Archiving. This offers a new way to show or hide removed rows. It adds the invisible column ora_archive_state to each table you enable it for.

Do this with the following command:

alter table toys row archival;

You then "delete" rows by setting ora_archive_state to any value other than zero. For example:

update toys 
set    ora_archive_state = '1'
where  toy_name = 'Baby Turtle';

select * from toys;

You control which rows are visible in the session. If you set the row archival visibility to all, you see everything:

alter session set row archival visibility = all;

select * from toys;

To auto-filter out the deleted rows, set the visibility to active:

alter session set row archival visibility = active;

select * from toys;

Note that you can enable In-Database Archiving on a table AND your own soft-delete method. So ensure whichever method you use to do soft deletion, you document it well. Explain how it works to all new developers on your team. And use the same approach throughout your application!