How can I create backup script for selected tables in Oracle?

Question

Aryan Kumar · Answer

Sure, here is how you can create a backup script for selected tables in Oracle:

SQL

-- Create a backup script for selected tables.

SET SERVEROUTPUT ON;

DECLARE
  v_table_name VARCHAR2(30);
BEGIN
  -- Loop through the list of tables and create a backup for each one.
  FOR v_table_name IN ('table1', 'table2', 'table3') LOOP
    dbms_output.put_line('Backing up table ' || v_table_name);
    EXECUTE IMMEDIATE 'CREATE TABLE BAK_' || v_table_name || ' AS SELECT * FROM ' || v_table_name;
  END LOOP;
END;
/

This script will create a backup for the tables table1, table2, and table3. The backup tables will be named BAK_table1, BAK_table2, and BAK_table3.

To run the script, you would save it as a text file and then execute it from SQLPlus. For example, if you saved the script as backup_script.sql, you would run it by typing the following command into SQLPlus:

SQL

@backup_script.sql

This would create the backup tables for the specified tables.

Here is an explanation of the code in the script:

The SET SERVEROUTPUT ON statement tells Oracle to display output from the script.
The DECLARE statement declares a variable named v_table_name to hold the name of the current table.
The FOR loop iterates through the list of tables and creates a backup for each one.
The dbms_output.put_line() procedure displays a message to the console.
The EXECUTE IMMEDIATE statement executes the specified SQL statement.
The END statement marks the end of the FOR loop.

forum

How can I create backup script for selected tables in Oracle?

Can you answer this question?

1 Answers