CODEWITHSUNDEEP
oracle11g
Steve Lloyd
Steve Lloyd

Reputation: 949

Query to show tables with LOGGING turned on?

Is there a way to query an ORACLE database to return tables that have LOGGING turned on? or off? I have searched the docs but cannot seem to find anywhere that answers this.

Upvotes: 0

Views: 2352

Answers (1)

Justin Cave
Justin Cave

Reputation: 231661

SELECT *
  FROM dba_tables
 WHERE logging = 'NO'

will literally answer the question. Depending on your privilege level and what set of tables you are interested in, you may want to use all_tables or user_tables rather than dba_tables.

Since the name LOGGING is somewhat misleading, though, hopefully you understand what the LOGGING attribute really means. A table with LOGGING set to NO will still generate just as much redo log information for normal DML operations as one with LOGGING set to YES. It is only when you are doing a direct path insert that redo generation can potentially be bypassed and whether that actually happens on the archivelog mode and whether force logging is set.

Upvotes: 2

Related Questions