Shulou(Shulou.com)05/31 Report--

This article mainly introduces how to use the view to quickly obtain Flashback Query flashback query data, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor take you to understand it.

1. Construct a flashback query view requirement description

1) prepare the employee table and payroll

2) Delete the record of the number of years of employment before 1994 in the payroll

3) create a view to query the records before the payroll is deleted

two。 Prepare the environment

1) prepare the employee table and payroll

Sec@ora10g > create table emp (id number,name varchar2 (20), e_date date); Table created. Sec@ora10g > create table salary (id number, salary number); Table created.

The emp table contains employee ID, employee name and employment time information

The salary table contains employee ID and salary information.

2) initialize data

(1) insert into emp values ('1991-01-01-01); insert into emp values (2); insert into emp values (' 1992-01-01)); insert into emp values (3). Insert into emp values (4 meme Shengwenfei to date ('2011-01-01-01)); commit; insert into salary values (1pm 60000); insert into salary values (2pm 50000); insert into salary values (3pm 40000); insert into salary values (4pm 30000); commit

3) get the content of initialization data

Sec@ora10g > select * from emp; ID NAME E_DATE-1 Secooler 19910101 00:00:00 2 Andy 19920101 00:00:00 3 HOU 20100101 00:00:00 4 Shengwen 20110101 00:00:00 sec@ora10g > select * from salary ID SALARY-1 60000 2 50000 3 40000 4 30000

3. Query the current time and SCN number for constructing subsequent flashback queries

1) query the current time

Sec@ora10g > select sysdate from dual; SYSDATE-20110809 21:34:11 2) query the current system SCN number sec@ora10g > select dbms_flashback.get_system_change_number from dual; GET_SYSTEM_CHANGE_NUMBER-3141326

4. Delete the record of years of employment prior to 1994 in the payroll

Sec@ora10g > delete from salary where id in (select id from emp where e_date

Commit; Commit complete. Sec@ora10g > select * from emp; ID NAME E_DATE-1 Secooler 19910101 00:00:00 2 Andy 19920101 00:00:00 3 HOU 20100101 00:00:00 4 Shengwen 20110101 00:00:00 sec@ora10g > select * from salary ID SALARY-3 40000 4 30000

At this point, the employee information in the salary table before 1994 has been deleted and the data changes have been submitted.

5. There are two ways to create a view, construct a flashback query and delete the data before it is deleted.

1) * methods: use timestamps to construct flashback query views

Sec@ora10g > create view v_salary_timestamp as select * from salary as of timestamp to_timestamp ('2011-08-09 / 21 / 34 / 11), View created.

2) the second method: construct a flashback query view using SCN

Sec@ora10g > create view v_salary_scn as select * from salary as of scn 3141326; View created.

6. Use views to get flashback query data

Sec@ora10g > select * from venerable timest ID SALARY-- 1 60000 2 50000 3 40000 4 30000 sec@ora10g > select * from vested salarytimest ID SALARY-- 1 60000 2 50000 3 40000 4 30000

So far, the two methods of constructing the view have successfully obtained the data of the flashback query. Complete the task smoothly.

Thank you for reading this article carefully. I hope the article "how to use views to get Flashback Query flashback query data quickly" shared by the editor will be helpful to everyone. At the same time, I also hope that you will support and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.