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

Editor to share with you what are flashbacks in the database, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's learn about it!

There are three main types of flashback table, flashback query and flashback database (data comes from flashback log). Here we mainly understand flashback table and flashback query.

Flashback table: it can be divided into the following two categories:

Flashback table tablename to before drop (data from the Recycle Bin)

Flashback table tablename to scn/timestamp (data from UNDO, Row movement must be enabled)

Flashback query: there are three types of flashback queries

Flashback query AS OF SCN/TIMESTAMP (data from UNDO)

Flashback version query Flashback Version Query (data from UNDO)

Flashback transaction query Flashback Transaction Query (data from UNDO)

Knowledge points about Row movement and Recyclebin in the flashback table

The row_movement_clause lets you specify whether the database can move a table row. Specify ENABLE to allow the database to move a row, thus changing the rowid.

Row movement must be enabled for all tables in the Flashback list unless you are flashing back the table TO BEFORE DROP. That operation is called a flashback drop operation, and it uses dropped data in the recycle bin rather than undo data.

Row_movement_clause allows you to specify whether the database can move table rows. Specify ENABLE to allow the database to move a row, thus changing the rowid.

Flashback must enable row movement, but flashback DROP does not need to enable Row movement, and data comes from the Recycle Bin

After Flashback table, there is no need to submit, it will be submitted automatically.

Flashback table user_tables.TABLE_NAME to before drop (user_tables.TABLE_NAME is recyclebin.ORIGINAL_NAME)

Flashback table recyclebin.OBJECT_NAME to before drop (when a table is deleted twice, you can specify the names in the Recycle Bin to specify which deleted tables to recycle in order)

Flashback table TABLE_NAME to before drop rename to TABLE_NAME2 (when you delete a table and create another table with the same name, you can use rename to to revert to a table with a different name)

Purge recyclebin is just emptying the current user's Recycle Bin

Purge dba_recyclebin empties all users' Recycle Bin

An example of flashback table and flashback query

SQL > create table hr.test1 (id number primary key,name varchar2 (10))

Table created.

SQL > begin

2 insert into test1 values (1)

3 insert into test1 values (2)

4 insert into test1 values (3 recordings)

5 commit

6 dbms_lock.sleep (5)

7 update test1 set name='C-1' where id=3

8 insert into test1 values (4 recorder D')

9 commit

10 end

11 /

PL/SQL procedure successfully completed.

-Flashback version query

SQL > select versions_startscn,versions_endscn,versions_xid,versions_operation,name from test1 versions BETWEEN SCN MINVALUE AND MAXVALUE

VERSIONS_STARTSCN VERSIONS_ENDSCN VERSIONS_XID VE NAME

4083242 03002000610D0000 I D

4083242 03002000610D0000 U Cmur1

4083235 4083242 01000400970C0000 I C

4083235 01000400970C0000 I B

4083235 01000400970C0000 I A

Flashback query AS OF SCN

SQL > select * from test1 as of scn 4083235

ID NAME

--

1 A

2 B

3 C

-Flashback version query

SQL > select versions_startscn,versions_endscn,versions_xid,versions_operation,name from test1 versions BETWEEN TIMESTAMP TO_TIMESTAMP ('2017-08-16 14 HH24:MI:SS' 17 HH24:MI:SS') AND TO_TIMESTAMP (' 2017-08-16 14 14 HH24:MI:SS' 19V 26%)

VERSIONS_STARTSCN VERSIONS_ENDSCN VERSIONS_XID VE NAME

4083242 03002000610D0000 I D

4083242 03002000610D0000 U Cmur1

4083235 4083242 01000400970C0000 I C

4083235 01000400970C0000 I B

4083235 01000400970C0000 I A

Flashback query AS OF TIMESTAMP

SQL > select * from test1 AS OF TIMESTAMP TO_TIMESTAMP ('2017-08-16 14-14-14-14-17-14-16-8-16-14-14-17-14-16-14-14-14-17-16-14-14-14-17-14-16-14-14-17-14-17-14-16-14-14-14-17-14-17-14-14-14-17-14-17-14-17-14-14-14-17-14-14-14-17-14-14-14-14-14-14-14-17-14-17-14-14-14-14-14-14-17-14-14-14-14-14-14-14-14-14-14-14-14-14-14-14-14-14-14-14-14-14-14-14-

ID NAME

--

1 A

2 B

3 C

-flashback transaction query

SQL > SELECT undo_sql FROM flashback_transaction_query WHERE xid = HEXTORAW ('03002000610D0000')

UNDO_SQL

Delete from "HR". "TEST1" where ROWID = 'AAAWV2AAEAAAAJXAAD'

Update "HR". "TEST1" set "NAME" ='C 'where ROWID =' AAAWV2AAEAAAAJXAAC'

SQL > select * from test1

ID NAME

--

1 A

2 B

3 Cmur1

4 D

-- enable ROW MOVEMENT

SQL > ALTER TABLE test1 ENABLE ROW MOVEMENT

Table altered.

-- Flashback table to SCN

SQL > flashback table test1 to scn 4083235

Flashback complete.

SQL > select * from test1

ID NAME

--

1 A

2 B

3 C

SQL > drop table test1

Table dropped.

-- Flashback table to BEFORE DROP

SQL > FLASHBACK TABLE test1 TO BEFORE DROP

Flashback complete.

SQL > drop table test1

Table dropped.

-- Flashback table to BEFORE DROP RENAME

SQL > FLASHBACK TABLE test1 TO BEFORE DROP RENAME TO test1_2017

Flashback complete.

SQL > select * from test1_2017

ID NAME

--

1 A

2 B

3 C

The above is all the contents of the article "what are the flashes in the database"? thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.