impdp via dblink on partitions and tbl$or$idx$part$num (It's not a curse, just a function)

/* ALL THE BELOW IS JUST FOR TESTING, DO NOT USE on PRODUCTION , YET */

I hate restrictions.
The worst one is ORA-14100 :

SQL> select * from "TABLE_OWNER"."TABLE_NAME"@XLK partition(P2011JAN01);
select * from "TABLE_OWNER"."TABLE_NAME"@XLK partition(P2011JAN01)
                          *
ERROR at line 1:
ORA-14100: partition extended table name cannot refer to a remote object

You may either define the borders of the partition or create a view on the target and select on the view via dblink. Defining the borders will only work with range and list partitions. For the hash ones the only way is to define a view. But there has to be another way.

I have seen that impdp over dblink can get partition or subpartition name as a parameter. Such that:

impdp ddsbase/xxxxx DIRECTORY=DPE1 NETWORK_LINK=XLK tables=TABLE_OWNER.TABLE_NAME:P2011JAN01 job_name=DPE1_P2011JAN01 LOGFILE=DPE1:P2011JAN01.log CONTENT=DATA_ONLY QUERY=\" order by contract_sk,content_sk \" 

. . imported "TABLE_OWNER"."TABLE_NAME":"P2011JAN01"."SP2011JAN01_02" 20303431 rows
. . imported "TABLE_OWNER"."TABLE_NAME":"P2011JAN01"."SP2011JAN01_14" 20303020 rows
. . imported "TABLE_OWNER"."TABLE_NAME":"P2011JAN01"."SP2011JAN01_01" 20222512 rows
. . imported "TABLE_OWNER"."TABLE_NAME":"P2011JAN01"."SP2011JAN01_03" 20215302 rows
. . imported "TABLE_OWNER"."TABLE_NAME":"P2011JAN01"."SP2011JAN01_04" 20261746 rows
. . imported "TABLE_OWNER"."TABLE_NAME":"P2011JAN01"."SP2011JAN01_05" 20225309 rows
. . imported "TABLE_OWNER"."TABLE_NAME":"P2011JAN01"."SP2011JAN01_06" 20301212 rows
. . imported "TABLE_OWNER"."TABLE_NAME":"P2011JAN01"."SP2011JAN01_07" 20252840 rows
. . imported "TABLE_OWNER"."TABLE_NAME":"P2011JAN01"."SP2011JAN01_08" 20254043 rows
. . imported "TABLE_OWNER"."TABLE_NAME":"P2011JAN01"."SP2011JAN01_10" 20220187 rows
. . imported "TABLE_OWNER"."TABLE_NAME":"P2011JAN01"."SP2011JAN01_13" 20237208 rows
. . imported "TABLE_OWNER"."TABLE_NAME":"P2011JAN01"."SP2011JAN01_15" 20218603 rows
. . imported "TABLE_OWNER"."TABLE_NAME":"P2011JAN01"."SP2011JAN01_16" 20225260 rows
. . imported "TABLE_OWNER"."TABLE_NAME":"P2011JAN01"."SP2011JAN01_09" 20267115 rows
. . imported "TABLE_OWNER"."TABLE_NAME":"P2011JAN01"."SP2011JAN01_11" 20239070 rows
. . imported "TABLE_OWNER"."TABLE_NAME":"P2011JAN01"."SP2011JAN01_12" 20203805 rows

So impdp has a way to do this, even on subpartitionwise. But even on impdp , if you define parallel=16 , only one partition is loaded at a time, because of the locking issues on the target table.
When I check the query running on the source side, I have seen the strange “undocumented” tbl$or$idx$part$num function.

FROM "TABLE_OWNER"."TABLE_NAME"@XLK KU$
WHERE   tbl$or$idx$part$num("TABLE_OWNER"."TABLE_NAME"@XLK,0,3,0,KU$.ROWID)=4791815

So oracle has a way to select a single subpartition without the subpartition keyword. As you may guess 4791815 is the object_id for the subpartition in dba_objects.

SQL> col owner format a10
SQL> col object_name format a15
SQL> col suboject_name format a15
SQL> select owner,object_name,subobject_name  from dba_objects where object_id=4791815;

OWNER      OBJECT_NAME     SUBOBJECT_NAME
---------- --------------- ------------------------------
TABLE_OWNER  TABLE_NAME SP2011JAN01_02

Let’s mimic impdp and try to create a 16 way parallel movement from one db to another based on subpartitions.

SQL> select  count(*) from
  2   TABLE_OWNER.TABLE_NAME@XLK 
  3   WHERE TBL$OR$IDX$PART$NUM ("TABLE_OWNER"."TABLE_NAME", 0,3,0,ROWID) = 4791815;
select /*+ OPAQUE_TRANSFORM NESTED_TABLE_GET_REFS NESTED_TABLE_GET_REFS */ count(*) from
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 28728
Session ID: 1430 Serial number: 1865

maxdb04: 160363               ORA 7445 [qesmaGetPam()+4]                                  2011-07-24 18:20:07.398000 +03:00

adrci> show incident -mode detail -p "incident_id=160363"

ADR Home = /u01/app/oracle/diag/rdbms/dbname/dbname_4:
*************************************************************************

**********************************************************
INCIDENT INFO RECORD 1
**********************************************************
   INCIDENT_ID                   160363
   STATUS                        ready
   CREATE_TIME                   2011-07-24 18:20:07.398000 +03:00
   PROBLEM_ID                    8
   CLOSE_TIME                    <NULL>
   FLOOD_CONTROLLED              none
   ERROR_FACILITY                ORA
   ERROR_NUMBER                  7445
   ERROR_ARG1                    qesmaGetPam()+4
   ERROR_ARG2                    SIGSEGV
   ERROR_ARG3                    ADDR:0x8
   ERROR_ARG4                    PC:0x13D9310
   ERROR_ARG5                    Address not mapped to object
   ERROR_ARG6                    <NULL>
   ERROR_ARG7                    <NULL>
   ERROR_ARG8                    <NULL>
   ERROR_ARG9                    <NULL>
   ERROR_ARG10                   <NULL>
   ERROR_ARG11                   <NULL>
   ERROR_ARG12                   <NULL>
   SIGNALLING_COMPONENT          PART
   SIGNALLING_SUBCOMPONENT       <NULL>
   SUSPECT_COMPONENT             <NULL>
   SUSPECT_SUBCOMPONENT          <NULL>
   ECID                          <NULL>
   IMPACTS                       0
   PROBLEM_KEY                   ORA 7445 [qesmaGetPam()+4]
   FIRST_INCIDENT                160386
   FIRSTINC_TIME                 2011-07-24 17:06:21.071000 +03:00
   LAST_INCIDENT                 160363
   LASTINC_TIME                  2011-07-24 18:20:07.398000 +03:00
   IMPACT1                       0
   IMPACT2                       0
   IMPACT3                       0
   IMPACT4                       0
   KEY_NAME                      PQ
   KEY_VALUE                     (0, 1311520807)
   KEY_NAME                      Client ProcId
   KEY_VALUE                     oracle@hostname (TNS V1-V3).6051_47869439584576
   KEY_NAME                      SID
   KEY_VALUE                     2708.15225
   KEY_NAME                      ProcId
   KEY_VALUE                     41.95
   OWNER_ID                      1
   INCIDENT_FILE                 /u01/app/oracle/diag/rdbms/dbname/dbname_4/trace/dbname_4_ora_6051.trc
   OWNER_ID                      1
   INCIDENT_FILE                 /u01/app/oracle/diag/rdbms/dbname/dbname_4/incident/incdir_160363/dbname_4_ora_6051_i160363.trc

Ups, it creates a dump on the source db.
Don’t give up,yet.
When I check the dump file it mentions about a dblink named “!”
TBL$OR$IDX$PART$NUM(“TABLE_OWNER”.”TABLE_NAME”@!

so this time change the sql to :

SQL> select  count(*) from TABLE_OWNER.TABLE_NAME@XLK
  2  WHERE TBL$OR$IDX$PART$NUM ("TABLE_OWNER"."TABLE_NAME"@XLK, 0,3,0,ROWID) = 4791815;

  COUNT(*)
----------
  17760539

SQL> 

When I add the db_link name to the TBL$OR$IDX$PART$NUM function , it is working.

And finally it’s easy to create 16 parallel insert /*+APPEND */ statements running parallel on the same partition which has 16 subpartitions. And combine them with our in-house code to run them as seperate jobs.

SQL> set pagesize 100
SQL> r
  1  select subobject_name,object_id from dba_objects@XLK
  2* where  owner='TABLE_OWNER' and object_name='TABLE_NAME'  and subobject_name like 'SP2011JAN01%'

SUBOBJECT_NAME                  OBJECT_ID
------------------------------ ----------
SP2011JAN01_01                    4769085
SP2011JAN01_02                    4769086
SP2011JAN01_03                    4769087
SP2011JAN01_04                    4769088
SP2011JAN01_05                    4769089
SP2011JAN01_06                    4769090
SP2011JAN01_07                    4769091
SP2011JAN01_08                    4769092
SP2011JAN01_09                    4769093
SP2011JAN01_10                    4769094
SP2011JAN01_11                    4769095
SP2011JAN01_12                    4769096
SP2011JAN01_13                    4769097
SP2011JAN01_14                    4769098
SP2011JAN01_15                    4769099
SP2011JAN01_16                    4769100

16 rows selected.

SQL> 

SQL> insert /*+ APPEND  */ into TABLE_OWNER.TABLE_NAME subpartition(SP2011JAN01_01)
  2  select * from "TABLE_OWNER"."TABLE_NAME"@XLK "KU$"
  3  WHERE TBL$OR$IDX$PART$NUM ("TABLE_OWNER"."TABLE_NAME"@XLK, 0, 3, 0,ROWID) = 4769085
  4  order by contract_sk,content_sk;

in another 15 sessions

SQL> insert /*+ APPEND  */ into TABLE_OWNER.TABLE_NAME subpartition(SP2011JAN01_02)
  2  select * from "TABLE_OWNER"."TABLE_NAME"@XLK "KU$"
  3  WHERE TBL$OR$IDX$PART$NUM ("TABLE_OWNER"."TABLE_NAME"@XLK, 0, 3, 0,ROWID) = 4769086
  4  order by contract_sk,content_sk;
.
.
.

It took 3 hours with impdp to move a single day, while it take only 10 minutes with this method.
I’m not planning to use it yet, but wondering about the comments.

About these ads