Missing Information

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;

TABLESPACE_NAME
——————————
SYSTEM
SYSAUX
TEMP
USERS
TRCANLZR

Elapsed: 00:00:00.12
SQL> alter tablespace TRCANLZR flashback off;

Tablespace altered.

Elapsed: 00:00:00.45

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’
4 /

SQL
——————————————————————————–

CREATE TABLESPACE “TRCANLZR” DATAFILE
SIZE 134217728
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.

About these ads

About mgogala

I am a long time Oracle DBA, who has worked on very large databases. I have worked with OPS and RAC since its inception.I am also a published book writer, having published two books about PHP. This blog is about the challenges and adventures in my professional life. Sorry, no family pictures here.
This entry was posted in Miscellaneous Oracle, Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s