Since the version 11G it is possible to exclude committed blocks from specific tablespace from being written to flashback recovery area. FBDA process writes committed blocks to flashback area and is quite IO intensive. There are, among other things, flashback checkpoints as well. They occur when when flashback logs fill up and start anew. It is, therefore, desirable to exclude tablespaces to which users don’t have access from having the committed changes written to flashback. Here is an example how to do that for the trace analyzer (TRCA) tablespace:
SQL> select tablespace_name from dba_tablespaces;
SQL> alter tablespace TRCANLZR flashback off;
This is somewhat typical situation when to turn the flashback off. Flashback is primarily a protection from the human error. I advocate turning the flashback off for tablespaces containing no user content or content that humans are extremely unlikely to modify, like the data dictionary for various applications.
There is, however, a problem:
DBA_TABLESPACES view contains no information about that and, even more problematic, DBMS_METADATA returns no information about that:
SQL> set long 1024
SQL> select dbms_metadata.get_ddl(‘TABLESPACE’,tablespace_name) SQL
2 from dba_tablespaces
3 where tablespace_name=’TRCANLZR’
CREATE TABLESPACE “TRCANLZR” DATAFILE
AUTOEXTEND ON NEXT 134217728 MAXSIZE 1073741824
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576 DEFAULT
NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO
The information about the flashback status is important enough to be included in V$DATABASE view and, in my modest opinion, it should be included in the DBA_TABLESPACES and returned by DBMS_METADATA.
By the way, in Oracle 12c, flashback is also common for all pluggable databases, not only redo logs and undo tablespace.