Home » RDBMS Server » Server Administration » Problem with moving tables from older to the new DB (DB's 10.2.0, OS: Win2003 32bit and 64 bit)
Problem with moving tables from older to the new DB [message #580368] Sat, 23 March 2013 09:54 Go to next message
greole
Messages: 7
Registered: March 2013
Location: Warsaw
Junior Member
I have problem with moving old DB to the new (the same DB 10.2.0 in Win 2003, first in 32 bit, second in 64 bit). I want move DB from 32 to 64 bit. Problem is that all objects in old DB were created in SYSTEM schema by SYS. I can't export that objets (with data) because impdp nor imp don't touch this objects (tables with indexes). I can't use export import procedure. I'm looking for another method to transfer data, which will be the best and the fastest??? Maybe files copy on OS??? I suppose it will be problems with configuration files, database have other tablespaces. Have You got any suggestions??

Sincerely,
Greg Oles
Re: Problem with moving tables from older to the new DB [message #580369 is a reply to message #580368] Sat, 23 March 2013 10:31 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ Fire the one that did this
2/ Create a DB link and use CREATE TABLE AS SELECT FROM X@dblink
or
2/ Create a new account and tablespace and use CREATE TABLE AS SELECT to create the new objects then you can export the new account

Regards
Michel

[Updated on: Sat, 23 March 2013 10:50]

Report message to a moderator

Re: Problem with moving tables from older to the new DB [message #580374 is a reply to message #580369] Sat, 23 March 2013 10:39 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>CREATE TABLE AS SELECT

realizing that INDEX & some CONSTRAINTS will need to be manually propagated to new DB
Re: Problem with moving tables from older to the new DB [message #580377 is a reply to message #580374] Sat, 23 March 2013 10:50 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As well a privileges, synonyms, types, procedures... Wink

Regards
Michel
Re: Problem with moving tables from older to the new DB [message #580378 is a reply to message #580368] Sat, 23 March 2013 11:05 Go to previous messageGo to next message
greole
Messages: 7
Registered: March 2013
Location: Warsaw
Junior Member
Thank You very much,
thought about the same procedure, but I have same problem with connect string in cluster mode (it's brand new thema for me now, forgive me Embarassed ). How to properly build connect string in cluster?? Cluster, nodes, servers name, copmuters names, Oracle services on nodes... I lost my mind. I would like to receive only schema how to rightly build connect string.
And other questions - primary key indexes will be rebuilt automatically if I use CREATE AS SELECT procedure
Re: Problem with moving tables from older to the new DB [message #580379 is a reply to message #580378] Sat, 23 March 2013 11:12 Go to previous messageGo to next message
greole
Messages: 7
Registered: March 2013
Location: Warsaw
Junior Member
It excite me.. "As well a privileges, synonyms, types, procedures", but it's true. So... I asked if it is possible to transfer files ( datafiels, controlfiles, redofiles)from the old database to the new, assuming that new DB is brand new and empty and all objects are stored in DB (old DB).

[Updated on: Sat, 23 March 2013 11:26]

Report message to a moderator

Re: Problem with moving tables from older to the new DB [message #580386 is a reply to message #580378] Sat, 23 March 2013 14:16 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
, but I have same problem with connect string in cluster mode (it's brand new thema for me now, forgive me Embarassed ). How to properly build connect string in cluster?? Cluster, nodes, servers name, copmuters names, Oracle services on nodes... I lost my mind. I would like to receive only schema how to rightly build connect string


There is no difference in cluster or not (unless you want to use specific cluster parameters).

Quote:
And other questions - primary key indexes will be rebuilt automatically if I use CREATE AS SELECT procedure


No.

Quote:
I asked if it is possible to transfer files ( datafiels, controlfiles, redofiles)from the old database to the new


No.

Regards
Michel
Re: Problem with moving tables from older to the new DB [message #580389 is a reply to message #580386] Sat, 23 March 2013 16:27 Go to previous messageGo to next message
greole
Messages: 7
Registered: March 2013
Location: Warsaw
Junior Member
Thank You very, very much,
especially for Michel, thank You for Your commitment. Now I have a clear situation and I have to start to prepare the environment for the transfer of data.

Best regards
Greg Ole
Re: Problem with moving tables from older to the new DB [message #580685 is a reply to message #580389] Tue, 26 March 2013 17:05 Go to previous messageGo to next message
alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California
Senior Member
Another way would be to expdp one table at a time and then remap the schema and tablespace on the impdp.
expdp  system/tiger tables=ALAN_TOTAL directory=MY_DIR dumpfile=ALAN_TOTAL.dmp logfile=expdpALAN_TOTAL.log
impdp dumpfile=ALAN_TOTAL.dmp logfile=impdpALAN_TOTAL.log userid=system remap_schema=system:alan remap_tablespace=SYSTEM:NEW_TABLESPACE
Re: Problem with moving tables from older to the new DB [message #580687 is a reply to message #580685] Tue, 26 March 2013 18:22 Go to previous messageGo to next message
greole
Messages: 7
Registered: March 2013
Location: Warsaw
Junior Member
It does not work- expdp can not see "non standard Oracle SYS objects" created by SYS, in SYSTEM tablespace. When I tried to use expdp in TABLE mode (SYS.table_name) I received a message "object not exist"!!!!. The same result when I tried expdp procedure with tablespace=SYSTEM. The only effective method to move this objects was CTAS. I already done the job (move objects from old DB to new) with CTAS and DB link. Be careful, because expdp, even in full export DB, doesn't touch objects created hmm... accidentally (not by me) in SYSTEM tablespace. Backup of DB is useless recovery of these objects!!!! It's a trap!!!!

Advice from Oracle: "By design Data Pump will not export objects under the SYS schema" and "use DBMS_METADATA.GET_DDL procedure.....". Very funny.. and what about the data???


Regards,
Greg Ole

[Updated on: Tue, 26 March 2013 18:44]

Report message to a moderator

Re: Problem with moving tables from older to the new DB [message #580702 is a reply to message #580687] Wed, 27 March 2013 01:51 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
and what about the data???


See above.
What did you do in the end?

Regards
Michel
Re: Problem with moving tables from older to the new DB [message #580710 is a reply to message #580702] Wed, 27 March 2013 03:21 Go to previous messageGo to next message
greole
Messages: 7
Registered: March 2013
Location: Warsaw
Junior Member
Hi,
as I said I moved tables [with data] using CTAS procedure and database link. Other objects created in old database I moved with DBMS_METADATA.GET_DDL procedure. Other objects was only: indexes and triggers. Fortunately, in old database wasn't other objects like procedures or functions, packages, but I suppose that You can use DBMS_METADATA.GET_DDL procedure to generate DDL statements. But I have not tested this!!!

Regards,
Greg Ole
Re: Problem with moving tables from older to the new DB [message #580711 is a reply to message #580710] Wed, 27 March 2013 03:25 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks for the feedback.

Regards
Michel
Re: Problem with moving tables from older to the new DB [message #580712 is a reply to message #580711] Wed, 27 March 2013 03:51 Go to previous message
greole
Messages: 7
Registered: March 2013
Location: Warsaw
Junior Member
Thanks for Your help. I don't like expdp Smile)

Regards
Greg Ole
Previous Topic: Comparing DB parameters
Next Topic: How to Reclaiming Wasted Space throughly
Goto Forum:
  


Current Time: Thu Mar 28 12:55:30 CDT 2024