forum

Home / DeveloperSection / Forums / How can I create backup script for selected tables in Oracle?

Ask Question
Utpal Vishwas

Utpal Vishwas

Follow
I am Utpal Vishwas from Uttar Pradesh. Have completed my B. Tech. course from MNNIT campus Prayagraj in 2022. I have good knowledge of computer networking.

Can you answer this question?

Answer

1 Answers

Aryan Kumar

Aryan Kumar

18-Jul-2023

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.
MindStick Training

Liked By