This discussion is archived
6 Replies Latest reply: Jun 24, 2013 11:59 PM by jamorcillov RSS

export/import table with XMLTYPE data_type and fine_grained policy

jamorcillov Newbie
Currently Being Moderated

Hi friends!

 

 

 

I'm trying to export a table with XMLTYPE and faine-grained policy.

 

 

 

Source: HP-UX - Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi

Target: Linux 2.6.18-238.el5 - Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

 

I do it thru exp/imp old utilities as mention in metalink ID 1318012.1.

 

One of the things that surprised me after exp/imp is that the number of objects increased:

 

Objects at Source:

 

exp \"/ as sysdba\" owner=xml log=xml file=xml.dmp

 

TypeNumber of Objects
TYPE431
TRIGGER6
TABLE17
PROCEDURE2
LOB120
INDEX17
FUNCTION1

 

On Target:

 

imp \"/ as sysdba\" file=xml.dmp fromuser=xml touser=xml log=xml.log

 

TypeNumber of Objects
TYPE431
TABLE32
PROCEDURE2
LOB429
INDEX478
FUNCTION1

 

 

Why is this happening? Is it normal?

 

Other problem that I found is triggers are not imported, why!?!?

 

 

Thank you very much for your help!

José

  • 1. Re: export/import table with XMLTYPE data_type and fine_grained policy
    DK2010 Guru
    Currently Being Moderated

    Hi,

     

    have you taken the object count of xml schema before import.you can assume lob indexes included in dba_objects from oracle 11.2.0.3 so index and lob count would be differ..if you are Query from dba_objects

    if you want to compare the you can Query from dba_lobs in previous version..

    About the Trigger.. did you get ant Error during Import..

     

    HTH

  • 2. Re: export/import table with XMLTYPE data_type and fine_grained policy
    jamorcillov Newbie
    Currently Being Moderated

    Hi HTH!

     

    from 10g:

     

    Connected to:

    Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

    With the Partitioning, OLAP, Data Mining and Real Application Testing options

     

     

    SQL> SELECT COUNT(*) FROM DBA_lobs WHERE OWNER = 'XML';

     

     

      COUNT(*)

    ----------

           120

     

     

    from 11g:

     

    SQL*Plus: Release 11.2.0.3.0 Production on Mon Jun 24 11:44:21 2013

     

     

    Copyright (c) 1982, 2011, Oracle.  All rights reserved.

     

     

     

    Connected to:

    Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

    With the Partitioning, Automatic Storage Management, OLAP and Data Mining options

     

     

    SQL> SELECT COUNT(*) FROM DBA_lobs WHERE OWNER = 'XML';

     

     

      COUNT(*)

    ----------

           429

     

     

     

    Why this difference in the number of lobs objects?

     

    And about triggers: "Import terminated successfully with warnings" so I don't know if it wasn't imported...

     

    Thanks a lot!

    José

  • 3. Re: export/import table with XMLTYPE data_type and fine_grained policy
    DK2010 Guru
    Currently Being Moderated

    Hi,

     

    use the Query and Compare.. May be some objects already exist on your target schema.

     

    col owner for a10
    col table_name for a20
    ool column_name for a20
    col segment_name for a30
    select owner,table_name,column_name,segment_name,index_name
    from  dba_lobs
    where owner='XML'
    order by owner,table_name
    /
  • 4. Re: export/import table with XMLTYPE data_type and fine_grained policy
    jamorcillov Newbie
    Currently Being Moderated

    Hi!

     

    The new squema was created empty. If I run the following query:

     

    select '10g', TABLE_NAME,COUNT(*)
    from  DBA_LOBS@DSN_HP
    where OWNER='XML'
    group by TABLE_NAME
    union
    select '11g', table_name,count(*)
    from  DBA_LOBS
    where OWNER='XML'
    group by TABLE_NAME
    order by 2,1

     

    As a result:

     

    VersionTable_nameTotal
    10gACTION_TABLE1
    11gACTION_TABLE1
    10gDocument1767_TAB14
    11gDocument1767_TAB13
    10gDocument1852_TAB14
    11gDocument1852_TAB13
    10gDocument1941_TAB16
    11gDocument1941_TAB15
    10gDocument2016_TAB14
    11gDocument2016_TAB13
    10gDocument2087_TAB13
    11gDocument2087_TAB12
    10gIBT_XML_RECIBIDOS1
    11gIBT_XML_RECIBIDOS1
    10gLINEITEM_TABLE2
    11gLINEITEM_TABLE2
    10gPURCHASEORDER7
    11gPURCHASEORDER7
    10gPurchaseOrder1145_TAB9
    11gPurchaseOrder1145_TAB7
    10gRICARDO13
    10gRICARDO21
    11gRICARDO21
    10gRITNTFER1
    11gRITNTFER1
    10gRITNTFRE_0813
    11gSYS_NT3+LEU6vbfGLgQ18DLgrURw==69
    11gSYS_NT3+LEU6vffGLgQ18DLgrURw==76
    11gSYS_NT3+LEU6vjfGLgQ18DLgrURw==63
    11gSYS_NT3+LEU6vpfGLgQ18DLgrURw==1
    11gSYS_NT3+LEU6vqfGLgQ18DLgrURw==2
    11gSYS_NT3+LEU6vTfGLgQ18DLgrURw==65
    11gSYS_NT3+LEU6vXfGLgQ18DLgrURw==66
    10gTESTCLOB1
    11gTESTCLOB1

     

     

    There are many new tables created...I suppose because the differences between versions...

     

    Any ideas?

     

    Thanks a lot!

    José

  • 5. Re: export/import table with XMLTYPE data_type and fine_grained policy
    Richard Harrison . Expert
    Currently Being Moderated

    Hi José,

    I suspect the triggers failed due to a missing privilege or something like that - check the logfile to see if you can find an error related to that. For the additional LOBS it would help ti find out which tables the LOBS that start with the name SYS_NT3 relate to as they account for most of the difference - they look like some internally generated name rather than something you would type yourself.....

     

    Regards,

    Harry

     

    http://dbaharrison.blogspot.de/

  • 6. Re: export/import table with XMLTYPE data_type and fine_grained policy
    jamorcillov Newbie
    Currently Being Moderated

    From:


    Oracle® XML DB Developer's Guide 10g Release 2 (10.2):

     

    Guidelines for Exporting Hierarchy-Enabled Tables

    The following describes guidelines for exporting hierarchy-enabled tables:

    • The row-level security (RLS) policies and path-index triggers are not exported for hierarchy-enabled tables: when these tables are imported, they arenot hierarchy-enabled.
    • Hidden columns ACLOID and OWNERID are not exported for these tables. In an imported database the values of these columns could be different, so they should be re-initialized.

     

     

    May be this is why triggers and fine-grained policy are not imported, what I don't understand is the increase in the number of tables and lobs...

     

     

    Regards,

    José

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points