It’s very common to create a table as select for backup purpose before a change in a prod table. We know the flashback solve this situation in general cases, but sometime is quicker and more comfortable to create a table as select, specially when it affects just a few rows and is needed to “keep the backup” for a week, for example. Having the "backup" table available in the database is easier and quicker to manipulate than make a data pump with query to save the table, and import it if you have to recover this data.
As trade-off, the DBA have to, time-to-time, check the “BKP” tables, review if they can be removed, and then remove it. Sometimes nobody knows from where it comes, specially after Continuous Integration initiatives, where the DBA doesn’t execute all the changes manually and lots of "bkp" table keep appearing. It's inevitable that some of those become eternal, wasting diskspace, increasing backup size, data dictionary, etc.
To solve that, it could have a syntax to make a "global temporary long term physical backup table" like:
Actual Command: CREATE TABLE bla_bkp AS SELECT * FROM bla WHERE name LIKE ‘JOHN%’;
Could be: CREATE [BACKUP] TABLE bla_bkp FROM bla WHERE name LIKE ‘JOHN%’ [AUTODROP AFTER 5 [[DAYS|HOURS|MINUTES]];
Understanding that the table is a "temporary backup table" the "AS SELECT *" is not necessary, like shown in this example.
It could be integrated to ILM components, automatically moved to slow disks and avoided from buffer cache and other memory structures (keeping these pools only for the real 'application' hot blocks).