3 Replies Latest reply on Apr 2, 2013 4:42 PM by Richard Harrison .

    EXPDP & IMPDP public synonym & dblinks


      We are using oracle on AIX, we are going to perform database reorg activity using expdp & Impdp

      we will be taking the backup with full=y & impdp on schema level

      How we can export & import the public synonym & dblinks?

      Also, How can we expdp & impdp lob segments, as we are having some LOBs of 25-30GB?


      Edited by: user10745179 on Apr 2, 2013 7:08 PM
        • 1. Re: EXPDP & IMPDP public synonym & dblinks

          LOBS will be taken care during Schema import in data pump but not synonyms so use SQLFILE to get DDL for the same

          Refer this thread

          expdp / impdp - Private synonyms using DB link not imported

          Ajay More
          • 2. Re: EXPDP & IMPDP public synonym & dblinks
            Mark D Powell
            First of all if you just running the expdp/impdp to reorganize the objects there is no need to export the public synonyms or database links. These are only definitions stored in the rdbms dictionary and do not have segments to be reorganized. Unless that is, you actually intend to drop and re-create the database followed by a reload.

            If you are going to reorganize tables and indexes have you considered just using alter table move and alter index rebuild for the great majority of your objects? You would need to resort to export/import only for those table with long and long raw columns, which are not supported by alter table move.

            Finally I hope you have done the analysis that shows that your system would benefit from performing the reorganizations because on many systems there is little long-term benefit to performing reorganizations.

            HTH -- Mark D Powell --

            PS - we used to use exp full=y rows=n to capture all DDL and then run imp rows=n ignore=y to bring public synonyms and bring any objects not imported by table/schema specific imports. The datapump utilities have the same kind of ability.

            Edited by: Mark D Powell on Apr 2, 2013 7:22 AM
            • 3. Re: EXPDP & IMPDP public synonym & dblinks
              Richard Harrison .
              As Mark has already said you don't need to do db links and public synonyms - you can just leave them alone.

              If you did want to still do this for some other reason you can just say:
              expdp user/pass include=DB_LINK,PUBLIC_SYNONYM full=y