This content has been marked as final. Show 6 replies
user9009534 wrote:Prior to issuing the "imp",
I am attempting to copy an entire schema from one 11gR2 instance to another. The users are not the same, for example olduser defaults to oldtablespace in the old database, newuser defaults to newtablespace in the new database. The default tablespace for the two users is different between the two databases. The user has about 1300 tables. When I do the import all of the are imported fine except for 6 or 8 tables.
manually "pre-create" these 6 - 8 tables within newtablespace (as empty tables).
After doing so, the imp will not throw any errors regarding these tables.
I am attempting to copy an entire schema from one 11gR2 instance to anotherAs you are on 11gR2, why not use EXPDP/IMPDP. You can use the remap_schema and remap_tablespace clauses. Did you check on the 6 to 8 objects in the older database, which tablespace they belong it? Is there any LOB columns in the tables and different tablespace being used for those columns?
I guess that might be an option in this case. I find imp/exp much easier to use than impdp/expdp and, at least up to now, never found any capability that I couldn't get from the older tools. I've done this sort of operation hundreds of times and never run into the problem before. Why on earth with over 1300 tables, all coming out of a single tablespace (and no LOBs), does it refuse to create 6 in a different tablespace?
From what I remember, exp/imp would remap tablespaces by first trying the create statement and if it failed, it would remove the tablespace clasue. My guess is that it is missing the tablespace clause for those 6-8 tables. Are those tables somehow different? Are they partitioned/subpartitiioned? Not that I have a solution, but just trying to help you understand why they are not moved. I also don't know if the remap tablepsace was really supported in imp. I know there is no parameter so I think it was just done under the covers.
p.s. I think if you try Data Pump, you will be happy with it. Take a little to get to know all of the new parameters, but the top 3 features that I think are very useful are:
1. recovery from failed/stopped jobs
2. parallel export/import of data
3. network import
Edited by: Dean Gagne on Aug 10, 2012 11:24 AM
Thanks for the input Dean. I have searched for what is different about those 6 tables and can find nothing. None have a lot of data, no partitioning and a couple of them have 0 data. They were originally in the same tablespace as the other 1300+. It might be something obvious but I sure am missing it.
I guess I need to learn Data Pump. Just the ability to recover a failed job probably makes it worth the effort. Occasionally I need to import some pretty large dmp files. I've had failures with insufficient temp space and other problems. I can't understand why Oracle requires that you create and use a directory object rather than simply making it an option.
I guess I need to learn Data Pump. Just the ability to recover a failed job probably makes it worth the effort.I'm not sure what version you are on, but in either 11.1 or 11.2 there is legacy support for old exp/imp syntax. It will take the exp/imp parameters and translate them to expdp/impdp parameters and then run the job. It will also tell you what your expdp/impdp commands should be. So, it makes is a little easier to migrate to Data Pump.
Occasionally I need to import some pretty large dmp files. I've had failures with insufficient temp space and other problems.Recovery here would be real nice for you.
I can't understand why Oracle requires that you create and use a directory object rather than simply making it an option.Data Pump wants to be as fast as possible, writing to the client side can slow down the export and then the import. One of the bigger hits (from what I was told) was if your client character set was not the same as the server character set. If this was true for both export and import, then you could see character set conversions 3 times.
1. export server to export client
2. export client to import client
3. import client to import server
By writing from the server to a directory object, on conversion on export and at most 1 conversion on import.
Hope this helps.