Friday, November 28, 2008

Tracing while exporting or importing

 

Sql trace for export and import session can be obtained in 4 ways:

1.Setting 10046 at system level.

alter system set events '10046 trace name context forever, level 12';

-- This will generate a 10046 trace file for import session in the user_dump_destination.

2.Using dbms_system.set_sql_trace_in_session

Obtain sid,serail#,program from v$session using the following query:

SQL> select sid,serial#,program from v$session where program like '%exp%' or program like '%imp%';

       SID    SERIAL# PROGRAM
---------- ---------- ------------------------------------------------
       151       3859 exp@hpoce118 (TNS V1-V3)

-- If any export is running

SQL> show parameters user

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
license_max_users                    integer     0
parallel_adaptive_multi_user         boolean     TRUE
user_dump_dest                       string      /mnt/app/oracle/db/admin/orcl/udump

oracle@hpoce118(rp3440):/mnt/app/oracle/db/admin/orcl/udump
=>ll *10046*
-rw-rw----   1 oracle     oinstall      1888 Nov 11 01:55 orcl_ora_10046.trc

execute dbms_system.set_sql_trace_in_session(sid,serial#,True);

--Use the sid and serial# obtained from previous query.

3.Using oradebug:

select s.sid, p.pid, p.spid from v$session s, v$process p where s.paddr = p.addr and s.sid =;

spid indicates the OS pid for export /import process.

connect as SYS (internal or AS SYSDBA) oradebug setospid
oradebug event 10046 trace name context forever, level 12

4.Trace =y option

Setting trace =y in export and import enables sql_trace and timed_statistics.

This is an undocumented feature in export and import.

This is available from release 8.1.6.

By default trace option is set to 'no'.

This is the easiest way to obtain sql trace for export and import session.

exp 'a1/a1' file=expa1.dmp log=expa1.log trace=y

exp 'scott/tiger' file=scott_data.dmp log=scott_data.log trace=y

This will generate a 10046 trace file for export session in the user_dump_destination.

****************************************************************

Typical Contents of "scott_data.log"

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user SCOTT
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user SCOTT
About to export SCOTT's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export SCOTT's tables via Conventional Path ...
. . exporting table                          BONUS          0 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table          CREATE$JAVA$LOB$TABLE          1 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table                           DEPT          4 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table                            EMP         14 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table                   JAVA$OPTIONS          2 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table                       SALGRADE          5 rows exported
EXP-00091: Exporting questionable statistics.
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully with warnings.

*****************************************************************

Similarly for import,

imp system/ fromuser=a1 touser=b1 file=expa1.dmp log=impb1.log trace=y

This will generate a 10046 trace file for import session in the user_dump_destination.

 

From "Ask Tom"

 

to trace tools like that, I typically use a trigger like this:

create or replace trigger logon_trigger
after logon on schema
begin
    execute immediate
    'alter session set events ''10046 trace name context forever, level 12'' ';
end;
/

(note: you have to have ALTER SESSION granted to you for this to work)

put that into the schema that is going to run the tool, start running the tool and then
drop the trigger.  There are other methods, but this gets the entire session (you could
for example start the tool - query v$session for the sid, serial# and use dbms_system to
set sql trace in session as well)

sqlldr - bulk loads by default, set bindsize to be the size of the bind buffer and it
array inserts.

import does the same.

but array processing is *not* direct pathing - direct path operations are those that
write directly to the Oracle datafiles.  sqlldr can do it, imp cannot (but impdp = the
data pump - can)

 

 

 

*************************************************************************************************************

Note:271782.1

**************************************************************************************************************

No comments:

Post a Comment