OraFAQ Forum: Server Administration » Problem with moving tables from older to the new DB

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)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Problem with moving tables from older to the new DB [message #580368]

Sat, 23 March 2013 09:54

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

Report message to a moderator

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

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

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

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

Report message to a moderator

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

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

As well a privileges, synonyms, types, procedures... Wink

Regards
Michel

Report message to a moderator

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

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

Report message to a moderator

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

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

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

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

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

Report message to a moderator

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

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

Report message to a moderator

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

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

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

and what about the data???

See above.
What did you do in the end?

Regards
Michel

Report message to a moderator

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

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

Report message to a moderator