Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
In today’s article, we will be cover about how to Make Flashback Database Monitor in Oracle.
We get the necessary information about flashback from 3 views.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | [Primary-1] SQL> select table_name from dictionary where lower(table_name) like '%flashback%'; TABLE_NAME ------------------------------ DBA_FLASHBACK_ARCHIVE DBA_FLASHBACK_ARCHIVE_TABLES DBA_FLASHBACK_ARCHIVE_TS DBA_FLASHBACK_TXN_REPORT DBA_FLASHBACK_TXN_STATE USER_FLASHBACK_ARCHIVE USER_FLASHBACK_ARCHIVE_TABLES USER_FLASHBACK_TXN_REPORT USER_FLASHBACK_TXN_STATE GV$FLASHBACK_DATABASE_LOG GV$FLASHBACK_DATABASE_LOGFILE TABLE_NAME ------------------------------ GV$FLASHBACK_DATABASE_STAT V$FLASHBACK_DATABASE_LOG V$FLASHBACK_DATABASE_LOGFILE V$FLASHBACK_DATABASE_STAT V$FLASHBACK_TXN_GRAPH V$FLASHBACK_TXN_MODS 17 rows selected. |
1 2 3 | [Primary-1] SQL> desc GV$FLASHBACK_DATABASE_LOG; Name Null? Type ----------------------------------------- -------- ---------------------------- |
INST_ID(NUMBER): Provides instance information.
OLDEST_FLASHBACK_SCN(NUMBER): It gives the oldest SCN that the database can receive with flashback.
OLDEST_FLASHBACK_TIME(DATE): It gives the oldest time the database can receive with flashback.
RETENTION_TARGET(NUMBER): Returns the value of DB_FLASHBACK_RETENTION_TARGET.
FLASHBACK_SIZE(NUMBER): Gives the instantaneous value of the flashback data.
ESTIMATED_FLASHBACK_SIZE(NUMBER): Provides information on how many estimated Flashback Logs in FRA are needed to meet DB_FLASHBACK_RETENTION_TARGET.
1 2 3 | [Primary-1] SQL> desc GV$FLASHBACK_DATABASE_STAT Name Null? Type ----------------------------------------- -------- ---------------------------- |
BEGIN_TIME(DATE): Gives the begin time of the Flashback Log.
END_TIME(DATE): Returns the end time of the Flashback Log.
FLASHBACK_DATA(NUMBER): Gives the size of Flashback Data in the time period.
DB_DATA(NUMBER): Gives the size of the read and written data block in the time period.
REDO_DATA(NUMBER): Returns the size of Redo Data in the time period.
ESTIMATED_FLASHBACK_SIZE(NUMBER):
1 2 3 4 5 6 7 8 9 10 11 12 | [Primary-1] SQL> desc GV$FLASHBACK_DATABASE_LOGFILE Name Null? Type ----------------------------------------- -------- ---------------------------- INST_ID NUMBER NAME VARCHAR2(513) LOG# NUMBER THREAD# NUMBER SEQUENCE# NUMBER BYTES NUMBER FIRST_CHANGE# NUMBER FIRST_TIME DATE TYPE VARCHAR2(9) |
The sum of the BYTES here gives the size of the flashback logs in FRA.
Finally, information about the FRA field:
1 2 3 | [Primary-1] SQL> desc v$RECOVERY_FILE_DEST Name Null? Type ----------------------------------------- -------- ---------------------------- |
NAME(VARCHAR2(513)): Provides the name of the FRA field.
SPACE_LIMIT(NUMBER): Returns the value specified in DB_RECOVERY_FILE_DEST_SIZE.
SPACE_USED(NUMBER): Returns the space used in the FRA area.
SPACE_RECLAIMABLE(NUMBER): Returns the area that is reclaim (delete obselete, redundant, low-priority files).
NUMBER_OF_FILES(NUMBER): Returns the number of files.
