Database Learning (Grooming)----6--oracle How to quickly back up and back up data tables multiple times

1. Description:

Here is a hypothetical application scenario!

Suppose that there is a large amount of data in the banking system that needs to be backed up in time, how quickly and efficiently!

Condition Requirements:

(1), can not set the Sync lock (set will affect the normal business of the bank to carry out!) Make the banking system in a maintenance state, this is not allowed! ）

(2), as efficient as possible, because the bank's data is massive, the general SQL statement efficiency can not meet high performance!

(3) ...

2. Solution:

1. The backup result integrates a new backup table:

Description: A result set is backed up here!

SQL statements:

CREATE TABLE Emp_bak

(

SELECT * FROM emp

);

2. How to back up an empty table structure:

SQL statements:

CREATE TABLE Emp_bak

(

SELECT * FROM emp

where 1 <> 1

);

3. After the first backup, if you need to back up the data again:

SQL statements:

INSERT INTO Emp_bak

(

SELECT * FROM EMP where rownum<=10

);

4, how to determine the next backup is not the first time to back up data!

Description

To make this judgment very simple, because the first backup of the data is to cover the operation of creating a backup of the data table, after which the backup is a new action, which is different from the 2 type of operator! So you need to differentiate!

SQL statement: (here we need to query the view, get the view to back up the data of the user's object, and then go to the user under the User query table!) ）

1, select * from User_tables; ----Query the user view to find the user who needs verification in the found data result set

2, select * from Emp_bak; ---to query the data under this user (that is, the user-wide table:), if the result set is empty (indicating that there is no backup table exists), then it is indicated that the data table was not previously backed up

　　　　　　　　　　

5. How to insert multiple data into an INSERT statement!

Description: You need to use the Union keyword

SQL statements:

INSERT INTO EMP

Select ' s001 ', one ' Jason ' from dual

Union

Select ' s002 ', ' Jack ' from dual

Union

Select ' s003 ', ' Rose ' from dual;

Execute the above SQL statement to add three data at once!

Database Learning (Grooming)----6--oracle How to quickly back up and back up data tables multiple times