Deployment Table backup considerations

ActionCommand or Notes
Table Name Length in CharactersSELECT LENGTH(‘MYNEWTABLE’) “Should be less than 30” FROM DUAL;
— Also not table name should only be alpha-numeric and ‘_’
— Do not ever put table name in quotes
Create a backup table (CTAS)CREATE TABLE <new table_name>
as
SELECT * FROM <source table_name> ;

— Verify row counts match
select count(*) from <new table_name>;
select count(*) from <source table_name>;

— Grant access to backup table to other users/roles as needed
grant select on to <new table_name> to <user or role>;

example:
CREATE TABLE backup_psammy_22122023
as
select * from psammy_orig;

select count(*) from backup_psammy_22122023;
select count(*) from psammy_orig;

grant select backup_psammy_22122023 to psammy_ro;
Date based backup tableCREATE TABLE <new table_name>
as
SELECT * FROM <source table_name>
WHERE <date col> >= to_date(<update with date>,’DDMMYYYY’);
Export a table if critical systemexpdp <credentials> directory=directory name tables=owner.tablename,owner.tablename \
dumpfile=<dumpfile name>.dmp logfile=<dumpfile name>.log
Flashback Table Supportselect owner, table_name, row_movement from all_tables
where table_name=’replace with table name’;

— Means ROWID can change for a row
alter table owner.table_name enable row movement;

— Perform DML or Work

flashback table table owner.table_name to timestamp to_timestamp(‘update to date’, ‘YYYY-MM-DD HH:MI:SS’);

alter table owner.table_name disable row movement;

select owner, table_name, row_movement from all_tables
where table_name=’replace with table name’;