8 Replies Latest reply: Jul 29, 2013 9:05 AM by 1028685 RSS

    Export/import . Difference between source and target schemas.

    1028685


      Hi,

      I have small question from the basics which I failed to undertsand.  There are two databases which are located on HP-UX 11i servers. Version of both of them is Oracle 11.2.0.3. I need to export schema from one db and import it into second database.

      Size of the schema on the source is 400 GB. Export and import operations are successful, all tables are imported. But on the target database size of the schema is 172 GB.

      I tried to estimate is there any way to resize schema on the source but stll there is big difference in sizes.

        Maybe someone can point into something what I missed. Thank you.

       

      Syntax which I used during exporting and importing (without compression option)

      userid=

      directory=

      dumpfile=

      compression=All

      parallel=

      cluster=n

       

       

      Best regards,

      Vik

        • 1. Re: Export/import . Difference between source and target schemas.
          Asif Muhammad

          Hi Vik,

           

          If the source database is heavily fragmented, then importing the database to another database will eliminate fragmentation and shows a reduced size. this my be one of the reason that I could think of.

           

          Please read the following doc for further reading:

          Original Export and Import

           

          Hope this helps!

          Best Regards

          • 2. Re: Export/import . Difference between source and target schemas.
            1028685

            Thank you, Arm  I will inform our senjors about this db on Monday. But how do you think is it possible that there is 200 Gb difference ?

             

             

            Best regards,

            Vik

            • 3. Re: Export/import . Difference between source and target schemas.
              Asif Muhammad

              Hi Vik,

               

              I would suggest that you issue the following command to get the list of all database objects (from source and target DBs), so that you can get a clue of what is missing, if any.

               

              SELECT object_type, count(*)
              FROM dba_objects
              WHERE username='&SCHEMA_NAME'
              GROUP BY object_type
              
              

               

              And in addition, You may query the dba_segments dictionary tables from the source database and sort it, and then compare it with the target database where the import was done. This will give you more clear picture of what has gone through and where the size has actually decreased.

               

              Hope this helps!

              Regards

              • 4. Re: Export/import . Difference between source and target schemas.
                1028685

                By the way, good idea, Arm! I checked and all objects from one database present in another. And:

                -  when I use something like this : select owner,segment_name,segment_type,(bytes/1024/1024/1024)MB from dba_segments  where owner=' ' order by 2 desc;

                I can see around 30 Gb difference which can be solved by resizing files on the source.

                - but when use something like this :  select owner, sum(bytes)/1024/1024/1024 Gbsize from dba_segments group by owner order by Gbsize desc. - it gives me 218 Gb differnce.

                 

                Thank you. Now I think that I am right. Another question why is that ? :-D

                 

                Best regards,

                Vik

                • 5. Re: Export/import . Difference between source and target schemas.
                  Asif Muhammad

                  Hi Vik,

                   

                  If you are able to find out which segment have size variance from Source db to  the target db. For example say TABLE1 is the segment which shows a higher size variance, then you can check whether all the row count in the target db matches the source db, if it does then you can come up with the conclusion its due to fragmentation.

                   

                  Best Regards,

                  • 6. Re: Export/import . Difference between source and target schemas.
                    Sathyanarayanan

                    Hi Vik,

                             i think you can check probably with 'all_tables ' view, the columns 'num_rows' , 'chained_count'  will give clear idea of fragmented rows and also to identify if the rows are defragmented..,

                     

                    Regards,

                    Rajagopal S

                    • 7. Re: Export/import . Difference between source and target schemas.
                      1028685

                      I am new in the company so don't know a lot of logic and what is going on. So I checked num_rows like Rajagopal advised me and found that numbers are the same. Than I checked size of the tablespaces and found that therre are few huges indexes and all of the them are occupied 200 GB and these indexes were analyzed  more than 4 months ago. So I simply asked about this database one of the senjors and found out that there are alot of purges going on on this database, a lot of deleting. That's why I have this difference.

                       

                      Arm, thank you so so much.

                       

                      Question is solved

                      • 8. Re: Export/import . Difference between source and target schemas.
                        1028685

                        I am new in the company so don't know a lot of logic and what is going on. So I checked num_rows like Rajagopal advised me and found that numbers are the same. Than I checked size of the tablespaces and found that therre are few huges indexes and all of the them are occupied 200 GB and these indexes were analyzed  more than 4 months ago. So I simply asked about this database one of the senjors and found out that there are alot of purges going on on this database, a lot of deleting. That's why I have this difference.

                         

                        Rajagopal, thank you so so much.

                         

                        Question is solved