Scripts to check the nologging tables and indexes

col index_name format a50
col table_name format a50
col db_name format a14
col type format a10

select sys_context(‘userenv’, ‘db_name’) db_name, owner||’.’||table_name table_name, LOGGING, ‘TABLE’ type
from dba_tables
where LOGGING =’NO’
  and owner not like ‘%SYS%’
  and owner not in (‘DBSNMP’, ‘XDB’)
  and (owner, table_name) not in (select owner, table_name
                                  from dba_external_tables)
union all
select sys_context(‘userenv’, ‘db_name’) db_name, owner||’.’||index_name index_name, LOGGING, ‘INDEX’ type
from dba_indexes
where LOGGING =’NO’
  and owner not like ‘%SYS%’
  and owner not in (‘DBSNMP’, ‘XDB’)
order by 1, 2
/

  • September 28, 2018 | 44 views
  • Comments