13 Replies Latest reply: Mar 6, 2013 6:46 AM by Dean Gagne RSS

    regarding export import

    user11221081
      Dear Friends

      my requirement is to do import data in 11g

      presently there are some users to which earlier seprate tablespace is alloted but before 6 months many of the users default tablespace changed to users tablespace.now some users are using their own tablespace rest moved to users tablespace.

      the client wants me to change all users to users tablespace and then make 3-4 user datafiles and distribute those files(locate them) on different mount points

      now i have below questions

      1)if earlier the users are using their own tablespace and then moved to users tablespace .then do their data also exist on previous tablespaces.
      do i need to create those tablespaces also before importing
      or if before import i create user and assing it default tablespace users it will take its data from previously assigned tablespace

      2)do i need to create users before import or it will get created automaticaaly

      3)is it good practice to assign a single tablespace to all users.or should i create 3-4 tablespaces and distribute them on different mount points rather then distributing users1.dbf user2.dbf user3.dbf on different mount points
        • 1. Re: regarding export import
          asahide
          Hi,
          1)if earlier the users are using their own tablespace and then moved to users tablespace .then do their data also exist on previous tablespaces.
          do i need to create those tablespaces also before importing
          You can use remap_tablespace parameter.
          2)do i need to create users before import or it will get created automaticaaly
          No, it will get created automaticaly.
          But You have to set password after impdp.
          3)is it good practice to assign a single tablespace to all users.or should i create 3-4 tablespaces and distribute them on different mount points rather then distributing users1.dbf user2.dbf user3.dbf on different mount points
          I think you may have to decide.
          There is a need to manage it with the same tablespace.

          Regards,
          • 2. Re: regarding export import
            user11221081
            thanks for your reply

            i want some more elaboration on 1

            if my user is A and i assigned default tablespace as A to it.it uses it for 3 months after that i moved it to user tablespace.

            now my doubt is will it transport the data from tablespace A to user or from today it start using users tablespace and rest data is also there on a.

            now if i need to import my user a data to another database.
            do i need to create tablespace A there or not.(means whole data of A is on users only or some might be on A)

            ----> finally i want to make default tablespace for allusers as user.(MY REQUIREMENT)
            so rather than using remap tablespace if in present database i change all users default tablespace to users then do i just need to create users tablespace in another tablespace and import.

            for example

            user b tablespace b...now in my present database i change it to user b tablespace b.

            now during import i just create user tablespace and import user b
            • 3. Re: regarding export import
              Sunny kichloo
              When you change the tablespace of User A did you revoke the quota of User A from its previous tablespace or not???


              When you will import User A on Some other Users For eg in your case its B it will try to import data of User A into default tablespace of User B.

              if you have any concerns do reply or close this thread if your issue is resolved.
              • 4. Re: regarding export import
                Richard Harrison .
                Hi,
                Unless you moved them objects created in tablespace a are in tablespace a still. Changing the default tablespace only affects any new objects created.

                Use remap_tablespace to change a into the new tablespace you created - then everything will end up in the same tablespace.

                So in your example

                Remap_tablespace=b:users

                Regards,
                Harry
                • 5. Re: regarding export import
                  user11221081
                  thanks richard

                  one last point

                  earlier i have a user ABC with tablespace ABC after certail time its tablespace switched to user tablespace.the tablespace there still exist and the datafiles also i dont know
                  whether user ABC objects still there on ABC or not..also i have a user CDE with tablespace CDE

                  finally my requirement is to do migration of database to reorganise its structure.i just want to use users tablespace(single tablespace for all users)

                  so please tell me how do i import both the users ABC and CDE so as to use users tablespace finally in the imported database
                  • 6. Re: regarding export import
                    Richard Harrison .
                    HI,
                    You can just list multiple remap_tablespace parameters during your import they will all be applied - so something like this:

                    impdp user/pass schemas=ABC,CDE remap_tablespace=ABC:USERS remap_tablespace=CDE:USERS

                    Cheers,
                    Harry
                    • 7. Re: regarding export import
                      user11221081
                      thanks a lot richard

                      means if my user ABC has objects in 2 tablespaces ABC,user then during import i should only remap abc:users and the object of user tablespace wil automatically move to user

                      also if i am using database in one server which is production.can i use the same server for testing .

                      actually we need to migrate database on same server with minimal downtime.

                      presently we are using standard edition database TEST at location /u01/app/oracle.

                      can i install enterprise edition at different home and creAte its datafiles and do import for testing with database name as TEST1

                      MEANS CAN I HAVE 2 databases on one server at different location in which one is production another testing
                      • 8. Re: regarding export import
                        Richard Harrison .
                        Hi,
                        Yes you can have 2 database on same server with different versions - no problems with that. The import will work as you desribe.

                        If you want to keep donwtime to a minimum you could do an impdp using a network_link. Instead of createing an export file then importing it you just create a database link between the two and import directly

                        so somethign like

                        login to test1 - create database link test connect to user identified by password using 'TEST';

                        then set environment to test1 and

                        impdp user/pass network_link=TEST remap=xxxxx schemas=xxxxx

                        Then the import just pulls all the data over the db link and its much faster than creating a file then importing from it.

                        Cheers,
                        Harry
                        • 9. Re: regarding export import
                          user11221081
                          CAN I IMPORT ALL TABLESPACES in one go rather than writing remap_tablespace=abc:users remap_tablespace=cde:users....i want to put all tablespaces of database in users at one time
                          • 10. Re: regarding export import
                            Richard Harrison .
                            Hi,
                            As long as all the default tablespaces for users has already been altered to USERS you can add the following option:

                            TRANSFORM=SEGMENT_ATTRIBUTES:n

                            This removes the tablespace clause completely from the sql that gets run so the objects will end up in the default tablespace of the user that owns them.

                            Cheers,
                            Harry
                            • 11. Re: regarding export import
                              user11221081
                              do i need to create all the tablespaces at target side which are there at source before using remap_tablespace
                              • 12. Re: regarding export import
                                Richard Harrison .
                                Hi,
                                The only tablespaces that need to exist are the ones you are remapping to

                                So in the case of

                                remap_tablespace=ABC:XYZ

                                Only the XYZ tablespace needs to exist in the target.

                                Cheers,
                                Harry
                                • 13. Re: regarding export import
                                  Dean Gagne
                                  The tablespace definition is included in a full=y export. If you do that type of export, you don't have to pre-create your tablespaces. If you do any other export, then you have to pre-create your target tablespace.

                                  Only exception is if you use transportable modes.

                                  Dean