What on earth is SCHEMA_EXPORT/STATISTICS/MARKER in datapump?



Recently when doing some datapump work i noticed an odd extra line appearing in 12c when doing schema exports - but only when exporting from a PDB - let me show you what i mean.

So this is the output from a schema export in a plain old (pre 12c style database)

expdp marker/marker reuse_dumpfiles=y directory=tmp

Export: Release 12.1.0.1.0 - Production on Fri Sep 5 21:20:11 2014

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "MARKER"."SYS_EXPORT_SCHEMA_01":  marker/******** reuse_dumpfiles=y directory=tmp
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Master table "MARKER"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for MARKER.SYS_EXPORT_SCHEMA_01 is:
  /tmp/expdat.dmp
Job "MARKER"."SYS_EXPORT_SCHEMA_01" successfully completed at Fri Sep 5 21:22:43 2014 elapsed 0 00:02:29



When we do the same thing against a PDB we see this (note you have to do an @ connection to datapump from a PDB)

expdp test/test@//localhost:1521/MARKER schemas=test directory=tmp

Export: Release 12.1.0.1.0 - Production on Fri Sep 5 09:15:14 2014

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "TEST"."SYS_EXPORT_SCHEMA_01":  test/********@//localhost:1521/MARKER schemas=test directory=tmp
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Master table "TEST"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TEST.SYS_EXPORT_SCHEMA_01 is:
  /tmp/expdat.dmp
Job "TEST"."SYS_EXPORT_SCHEMA_01" successfully completed at Fri Sep 5 09:15:45 2014 elapsed 0 00:00:29


So pretty much exactly the same - apart from 1 line

Processing object type SCHEMA_EXPORT/STATISTICS/MARKER

 Sowhat is this mysterious 'marker' - the fact that it is mentioned at all implies it thinks it needs to extract something - but what is that something?

Trying an import to a sqlfile to see what metadata it contained revealed nothing - no mention of anything to do with it.

Hmmmm

Lets try and do a bit of detective work

A look in DBA_EXPORT_OBJECTS makes no reference to this - so there is no comment to help us. So a bit of a dead end there.

Lets trace datapump and se if that tells us anything - activating full tracing and we see this in the trace file

KUPW:10:21:47.728: 1: DBMS_LOB.TRIM returned.
KUPW:10:21:47.728: 1: DBMS_METADATA.FETCH_XML_CLOB called. Handle: 200001
META:10:21:47.728: OBJECTS_FETCHED for TABLE_STATISTICS = 1
META:10:21:47.729:  get_xml_inputs SCHEMA_EXPORT/STATISTICS/MARKER:
SELECT /*+rule*/ SYS_XMLGEN(VALUE(KU$), XMLFORMAT.createFormat2('MARKER_T', '7')), 0 ,KU$.MARKER ,'MARKER' FROM SYS.KU$_MARKER_VIEW KU$
rowtag: ROW objnum_count: 0 callout: 0 Bind count: 0 Bind values:


Aha - some kind of progress - so there is a view KU$_MARKER_VIEW which datapump is using - maybe this contains some useful comments?

A quick look at the source code - and it's very basic......

select '1', '0', dbms_metadata_util.get_marker
  from dual


which when run shows

select * from SYS.KU$_MARKER_VIEW;

V V     MARKER
- - ----------
1 0         42


So lets have a look at the source of this.... (well as much as we can do as the package body is wrapped).

The only bit of any note is this

-- PACKAGE VARIABLES

  marker NUMBER := 42;  -- marker number: used in worker/mcp to take actions
                        -- at appropriate times, without depending on
                        -- pathnames


Which doesn't really tell us anything.

And that's pretty much the end of the road, we all know about 42 of course....... http://en.wikipedia.org/wiki/Phrases_from_The_Hitchhiker%27s_Guide_to_the_Galaxy

So it's either a coincidence that 42 exists or there is some deeper meaning to any of this existing (i suspect the former in this case :-)).

Anyone know what this is actually doing? It's off that it seems to be a 'schema level' thing to export not something associated with segments directly






3 comments:

  1. I was wondering the same because my exports goes really well then I hit the marker:

    Worker 1 Status:
    Instance ID: 2
    Instance name: proda2
    Host name: pdbhost02
    Process Name: DW00
    State: EXECUTING
    Object Schema: SYS
    Object Type: SCHEMA_EXPORT/STATISTICS/MARKER
    Completed Rows: 59,655
    Completed Bytes: 7,182,304
    Percent Done: 100
    Worker Parallelism: 1

    It just sits there forever. A bit frustrating when it's notifying: 100% completed.

    ReplyDelete
    Replies
    1. Hello Nicolas,

      Did the process ever end? Coz I have mine running for 18hrs now. It's rather frustrating this thing.

      Regards,
      Kenince

      Delete
  2. What do you expect from Oracle, nothing but another shitty addition that nobody knows about unless paid $100000 for another one of their db changelog trainings. They suck dirty, hairby balls pretty hard.

    ReplyDelete