dba_logstdby_unsupported script Oracle Tips by Burleson Consulting December 3, 2011

Oracle Data Guard Limitations of the Logical Standby Database

The following limitations apply to logical standby databases:

A logical Oracle instance does not support LONG, LONG RAW, BFILE, ROWID, UROWID, NCLOB and any of the collection data types.

If the application contains any of these types, the usability of a logical Oracle instance in that environment should be evaluated.

The script, unsup_objects.sql, from the code depot can be used to find the tables in the database that cannot be supported in a logical standby database.

Limitations of the Logical Standby Database

set linesize 82
column owner format a30
column table_name format a30
column data_type format a20 trunc
 
select distinct
  owner,
  table_name,
  data_type
from
  see code depot for related scripts
  dba_logstdby_unsupported;

The output will be similar to the following:

OWNER                TABLE_NAME                 DATA_TYPE
------------------ -------------------------- --------------
REPORTS              ITEMDESC                   LONG

The same view can be queried to find the offending column of the table.

• Any user-defined objects in Sys schema are not supported. Keeping user-defined tables in Sys schema is a poor design and should be avoided. If an application is well written, this limitation should not be a problem.