data migration [message #506591] |
Tue, 10 May 2011 03:05 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
z_ashwini
Messages: 26 Registered: February 2008 Location: Mumbai
|
Junior Member |
![qute_ria%40yahoo.com](/forum/theme/orafaq/images/yahoo.png)
|
|
Hi guys,
I am trying to insert data in one of the tables called as
tstcntr_mstr in ibpslive instance by ibpslive user.
My source tables are on ncfiiidv instance.
Query is as follows:
insert into tstcntr_mstr
(select * from tstcntr_mstr@dlink_ncfmdv)
Error that I get is remote operations not permitted on object tables or user-defined type columns.
Table tstcntr_mstr@dlink_ncfmdv contains types.
Please help me with the migration of the data.
Thanks,
Ash
|
|
|
|
|
|
|
Re: data migration [message #507585 is a reply to message #507584] |
Tue, 17 May 2011 04:09 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
1) You haven't attached anything
2) It's better to copy and paste command line output as text (in [code] tags) rather than a screen shot.
3) The program you are trying to run is export. Littlefoot suggested data pump (not dump) - that program is expdp.
4) Try removing the brackets around the table name and the spaces before and after =
ie.
Should be
|
|
|
|
|
|
|
Re: data migration [message #507602 is a reply to message #507600] |
Tue, 17 May 2011 05:33 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/72104.gif) |
Littlefoot
Messages: 21818 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
This is 10g XE example (which, I believe, would work fine on 11g as well): DATAPUMP first:M:\>expdp scott/tiger@ora10 dumpfile=scott.dmp logfile=scott.log tables=(emp) query=\"where ename='KING'\" directory=ext_dir
Export: Release 10.2.0.1.0 - Production on Utorak, 17 Svibanj, 2011 12:29:48
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "SCOTT"."SYS_EXPORT_TABLE_01": scott/********@ora10 dumpfile=scott.dmp logfile=scott.log t
ables=(emp) query="where ename='KING'" directory=ext_dir
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/TRIGGER
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."EMP" 7.296 KB 1 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
C:\TEMP\SCOTT.DMP
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 12:29:24
M:\>
The ORIGINAL EXPORT UTILITY:M:\>exp scott/tiger@ora10 file=scott.dmp log=scott.log tables=(emp) query=\"where ename='KING'\"
Export: Release 10.2.0.1.0 - Production on Uto Svi 17 12:32:42 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in EE8MSWIN1250 character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table EMP 1 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
Export terminated successfully with warnings.
M:\>
Compare the above with your code, find the difference(s), fix the error(s).
[Updated on: Tue, 17 May 2011 05:34] Report message to a moderator
|
|
|
|
|
|
|
|
|
|
Re: data migration [message #507888 is a reply to message #507877] |
Wed, 18 May 2011 06:56 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
TO_DATE(TO_CHAR('10/26/2009'),'MM/DD/YYYY' )
Anything in quotes is a char by definition. So that is equivalent to:
TO_DATE('10/26/2009','MM/DD/YYYY' )
|
|
|
|
Re: data migration [message #508325 is a reply to message #508087] |
Fri, 20 May 2011 04:55 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
z_ashwini
Messages: 26 Registered: February 2008 Location: Mumbai
|
Junior Member |
![qute_ria%40yahoo.com](/forum/theme/orafaq/images/yahoo.png)
|
|
Hi,
If I am specifying some 200 tables in a single command then is it feasible to write some 200 queries for those same tables in 1 command.
for e.g. eg: expdp ncfmdba/ncfmdba@orcl dumpfile=
alert_testdate.dmp logfile= alert_testdatelog.log tables=
(table1,table2,....tble200)query=table1:\" where query1 \",query=table2:\" where query 2'\",tablen:\"query=n \"
I have tried for 3 tables and it works. Just wanted to know if it is feasible/right way for 200 tables .
I am new to all this and I am getting confused whether I should go ahead or not....
Thanks,
Ash...
|
|
|