Shulou(Shulou.com)06/01 Report--

Parameters related to flashback technology in the dream database

1 . ENABLE_FLASHBACK

To enable flashback function, you need to open the above parameters in the configuration file dm.ini of Dameng database.

2. UNDO_RETENTION

The duration of rollback segment retention, which represents the length of time in seconds that flashbacks can occur.

SQL> select para_name,para_value from v$dm_ini where para_name like '%FLASH%';

Line Number PARA_NAME PARA_VALUE

---------- ---------------- ----------

1 ENABLE_FLASHBACK 0

SQL> select para_name,para_value from v$dm_ini where para_name like '%UNDO_RE%';

Line Number PARA_NAME PARA_VALUE

---------- -------------- ----------

1 UNDO_RETENTION 90.000000

SQL> alter system set 'ENABLE_FLASHBACK'=1 both;

SQL> select name,type,value from v$parameter where name='ENABLE_FLASHBACK';

Line Number NAME TYPE VALUE

---------- ---------------- ---- -----

1 ENABLE_FLASHBACK SYS 1

SQL> select user();

Line Number USER()

---------- ------

1 LJW

SQL> create table fb (test varchar2(20));

SQL> insert into fb values('flashback');

SQL> select sysdate();

Line number SYSDATE()

---------- ---------------------------

1 2020-02-27 10:14:36

SQL>commit;

SQL> select * from fb;

Line No. TEST

---------- ---------

1 flashback

SQL> select sysdate();

Line number SYSDATE()

---------- ---------------------------

1 2020-02-27 10:15:24

SQL> select sysdate();

Line number SYSDATE()

---------- ---------------------------

1 2020-02-27 10:16:11

SQL> delete from fb;

SQL> select sysdate();

Line number SYSDATE()

---------- ---------------------------

1 2020-02-27 10:16:25

SQL> commit;

SQL> select * from fb when timestamp '2020-02-27 10:15:24';

Line No. TEST

---------- ---------

1 flashback

SQL> select * from fb;

unselected rows

SQL> select * from fb when timestamp '2020-02-27 10:15';

Line No. TEST

---------- ---------

1 flashback

1. Flashback query clause query

syntax format

::=WHEN |

parameters

1 . time_exp A date expression, usually expressed as a string

2 . trxid Specifies the transaction ID number

instructions for use

1 . Flashback query only supports ordinary tables (including encrypted tables and compressed tables), temporary tables and heap tables, and does not support horizontal partition tables, vertical partition tables, column storage tables, external tables and views;

2 . The value of trxid in flashback queries is generally determined by the pseudo-columns of flashback version queries (see section below). In actual use, the method of specifying time is often adopted.

2. Flashback version query

syntax format

parameters

::=VERSIONS BETWEEN |

1 . time_exp Date expression, usually expressed as a string. time_exp1 indicates the start time, time_exp2 indicates the end time

2. trxid Specifies the transaction ID number, expressed as an integer. trxid1 indicates the start trxid, trxid2 indicates the end trxid

instructions for use

1 . Flashback version query supports normal tables (including encrypted tables and compressed tables), temporary tables and heap tables, but does not support horizontal partition tables, vertical partition tables, column storage tables, external tables and views;

2 . Pseudo-columns as shown in Table 17.2.1 are supported as auxiliary information for flashback version queries.

statement function

By flashback version query clause, users can get all records of record changes caused by transactions in a specified table in a certain period of time in the past. The specified condition can be the time of day, or the transaction number.

3 Flashback Transaction Query

Flashback transaction queries provide a system view V$FLASHBACK_TRX_INFO for users to view changes made to the database at the transaction level. Depending on the view information, you can determine how to undo a specified transaction or modification within a specified time period.

instructions for use

The system view name is V$FLASHBACK_TRX_INFO and is defined as shown in Table 17.3.1.

exemplified

Example 1 Query transaction information after specified time, which can provide reference for flashback query operation.

SELECT * FROM V$FLASHBACK_TRX_INFO WHERE COMMIT_TIMESTAMP > ' 2020 -0 2 -0 2 12:00:00';

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.