6 Replies Latest reply: Jun 25, 2013 1:59 AM by jamorcillov RSS

    export/import table with XMLTYPE data_type and fine_grained policy

    jamorcillov

      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

          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

            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

              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

                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 .

                  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

                    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é