if i will rate my oracle expertise i would give it 3/10. i just started learning oracle, solaris and linux 2months ago and was given this task to migrate. yes our oracle version is quite old and might not be supported anymore. Both platforms has the same Oracle Versions, only difference is patch version of linux is 10.2.1 compared to my source database solaris sparc 10.2.4.

our database is about 20GB in size. tablespace is about 16 including the default that came in during the installation of Oracle. 1 tablespace has 25 datafiles which i think would be the main tablespace that the system is using and other tablespaces averages 2-3 datafiles each.

i have tried the datapump method first. upon importing, errors are being encountered that users and roles does not exist. i think what the datapump import is doing is importing the data first before the users that is why it is looking for the users/schema's which it belongs.

i have decided to use the transportable tablespace method and was able to convert and transport tablespaces that are self contained or do not have objects that relies on other tablespaces. which will lead me to my main question, how to i proceed in migrating the tablespaces that have objects dependencies to other tablespaces? or if the datapump method is easier, i can go back to that method.

thank you in advance and we appreciate all the help we can get with this activity.

Regards, Mike

  • To be honest, 20 GB is such a small database, I would not bother with transportable tablespaces and platform conversion, a simple Data Pump export/import could do it in minutes. However if you perform schema level export/import, than yes, you will receive the above errors. Some objects are not owned by user schemas, yet they use them. You can just simply pre-create them before performing the import. This typically includes tablespaces, profiles, roles. – Balazs Papp Sep 21 '18 at 12:11
  • hi, thank you for the reply. do i have to create them based from my source? i have 16 tablespaces in my source, so i have to create 16 in my target with the exact tablespace name? what about the datafiles? some tablespaces have more than 1 datafiles. aside from tablespaces, profiles and roles, what else do i hae to pre create before doing a full impdp? with regards to users, how do i import them with the same credentials? – bagetsro Sep 22 '18 at 13:14

I'd do the following:

  • Pre-create the tablespaces: about the datafiles number or names, there should not be any issue, as long as the Tablespace names are the same and space in the Tablespace is the same or bigger than the source you should be ok.
  • Create the export with a dba privilege user: This way ,it will create the definitions for users and they should be able to be recreated in the destination.
  • Execute the import with a user with dba privileges, this way the users should be created in the new DB.

Since you will be moving to a different OS, transportable TS could give some issues, and since the DB is "small" it should be better to do the expdp/impdp process.

I think this process should be enough, give it a try and check if any issues.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.