RE: Enable novalidate contains after import?

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Tue, 24 May 2016 08:10:30 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D9282D531C_at_EXMBX01.thus.corp>

Can you show us the parameter file you're using for the export and import ? How many columns are there in the table ? I supposed it's possible that the tablescans are something to with attempts to gather stats at different percentages

For testing purposes you could create a logon trigger to enable tracing for a schema, and a logoff trigger to dump v$mystat to the trace file; then import just that one table. This might give you a better idea of what the tablescans were actually doing.

Example of creating logon trigger (run by sys)

create or replace trigger log_on_trace_temp after logon
on test_user.schema
begin

        execute immediate 'alter session set tracefile_identifier = ''JPL''';
        execute immediate 'alter session set events ''10046 trace name context forever, level 8''';

end;
/

Example of logoff trigger (also run by sys)

create or replace trigger ses_logoff
before logoff on test_user.schema
declare

        m_output varchar2(100);

begin

        for r in (
                select
                        sn.name,
                        ms.value
                from
                        v$mystat        ms,
                        v$statname      sn
                where
                        sn.statistic#  = ms.statistic#
                and     ms.value      != 0
                order by
                        sn.statistic#
        ) loop

                m_output := rpad(r.name,65) || lpad(to_char(r.value,'FM999,999,999,999,999'),20);
                dbms_system.ksdwrt(1,m_output);

        end loop;

end;
/

The logoff trigger calls package dbms_system to write to the trace file; if that's not available on your system then you can create it by running $ORACLE_HOME/rdbms/admin/prvtsys.plb, but I think it installs by default on 11g.

Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
_at_jloracle

---

From: Norman Dunbar [oracle_at_dunbar-it.co.uk] Sent: 24 May 2016 08:18
To: Jonathan Lewis; oracle-l-freelists
Subject: RE: Enable novalidate contains after import?

Morning Jonathan,

The not null constraints are all part of the column definition rather than added check constraints.

There are definitely only 8 of them, including the pk column. There are a couple of unique indexes which have been created by the data import, but no unique constraints.

Interesting.

Cheers,
Norm.

--
Sent from my Android device with K-9 Mail. Please excuse my brevity.

--
http://www.freelists.org/webpage/oracle-l

Received on Tue May 24 2016 - 10:10:30 CEST