OraFAQ Forum: Server Utilities » data migration

Home » RDBMS Server » Server Utilities » data migration (oracle 11g)

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

data migration [message #506591]

Tue, 10 May 2011 03:05

z_ashwini
Messages: 26
Registered: February 2008
Location: Mumbai

Junior Member

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

Report message to a moderator

Re: data migration [message #506592 is a reply to message #506591]

Tue, 10 May 2011 03:08

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Data pump (or export/import)?

Report message to a moderator

Re: data migration [message #507578 is a reply to message #506592]

Tue, 17 May 2011 03:35

z_ashwini
Messages: 26
Registered: February 2008
Location: Mumbai

Junior Member

thx little

Report message to a moderator

data migration [message #507582 is a reply to message #506591]

Tue, 17 May 2011 03:55

z_ashwini
Messages: 26
Registered: February 2008
Location: Mumbai

Junior Member

Hi All,

I am trying to export the data in Oracle 11g using data dump.
When I run the below comand:

exp ncfmdba/ncfmdba@orcl file = alert_export.dmp log = alert2.log tables = (alert_mstr) query =\"where created_by =\'ANUPAM\'\" rows = y

I get the ORA 00911 error.

I am unable to figure out where the error is.
Could someone help me with the same.

I have attached the screenshot for further reference.

Thanks,
Ash

Report message to a moderator

Re: data migration [message #507584 is a reply to message #507582]

Tue, 17 May 2011 04:06

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

It seems that you don't distinguish data pump from (original) export/import utilities. You say "data pump", I see "export". So, which one do you plan to use?

If it is data pump after all, something like this might do the job:

expdp ncfmdba/ncfmdba@oracl dumpfile=alert_export.dmp logfile=alert2.log tables=(alert_mstr) query=\"where created_by='ANUPAM'\" directory=<name of your directory object>

Report message to a moderator

Re: data migration [message #507585 is a reply to message #507584]

Tue, 17 May 2011 04:09

cookiemonster
Messages: 13962
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.

file = alert_export.dmp

Should be

file=alert_export.dmp

Report message to a moderator

Re: data migration [message #507586 is a reply to message #507585]

Tue, 17 May 2011 04:19

z_ashwini
Messages: 26
Registered: February 2008
Location: Mumbai

Junior Member

exp ncfmdba/ncfmdba@orcl file = alert_export.dmp log=alert2.log tables=alert_mstr query =\"created_by = \'ANUPAM\'\" rows = yes

ERROR IS:

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table ALERT_MSTR
EXP-00056: ORACLE error 933 encountered
ORA-00933: SQL command not properly ended
Export terminated successfully with warnings.

Export gets terminated... Sad

Sad

Report message to a moderator

Re: data migration [message #507587 is a reply to message #507586]

Tue, 17 May 2011 04:21

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Read the whole topic again; you might find answers to your problems.

Report message to a moderator

Re: data migration [message #507588 is a reply to message #507586]

Tue, 17 May 2011 04:26

cookiemonster
Messages: 13962
Registered: September 2008
Location: Rainy Manchester

Senior Member

A quick look at the documentation shows you're missing a keyword.

Report message to a moderator

Re: data migration [message #507600 is a reply to message #507588]

Tue, 17 May 2011 05:18

z_ashwini
Messages: 26
Registered: February 2008
Location: Mumbai

Junior Member

i AM STILL GETTING THE ERROR::
i HAVE TRIED USING WIZARD TOO Sad

Sad

MY QUERY RUNS LIKE THIS :

exp ncfmdba/ncfmdba@orcl file=alert_export.dmp log=alert2.log tables=(alert_mstr) query=\"where CREATED_BY=\'ANUPAM\'\" rows=yes

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Produc
tion
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table ALERT_MSTR
EXP-00056: ORACLE error 911 encountered
ORA-00911: invalid character
Export terminated successfully with warnings.

I AM STUCK WITH THE SAME ISSUE:(

Report message to a moderator

Re: data migration [message #507602 is a reply to message #507600]

Tue, 17 May 2011 05:33

Littlefoot
Messages: 21823
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 #507628 is a reply to message #507602]

Tue, 17 May 2011 07:02

z_ashwini
Messages: 26
Registered: February 2008
Location: Mumbai

Junior Member

its working.... thx a ton Smile

Smile

Report message to a moderator

Re: data migration [message #507805 is a reply to message #507628]

Wed, 18 May 2011 01:39

z_ashwini
Messages: 26
Registered: February 2008
Location: Mumbai

Junior Member

Hi,

The sql promp shows that the dump file alert_test is created and specifies the location also.
But I cannot find any file in the location specified.
Moreover if I run the same cmd again it denies the export and says a file alert_test is already present.
If I run search it does not locate any file called alert_test.dmp
I am confused.
C:\Documents and Settings\Administrator>expdp ncfmdba/ncfmdba@orcl dumpfile= alert_test.dmp logfile= alert_testlog.log tables=(alert_mstr) query=\"where CREATED_BY='ANUPAM'\"

Export: Release 11.1.0.7.0 - Production on Wednesday, 18 May, 2011 11:40:11

Copyright (c) 2003, 2007, Oracle. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31641: unable to create dump file "D:\app\Administrator\admin\orcl\dpdump\alert_test.dmp"
ORA-27038: created file already exists
OSD-04010: <create> option specified, file already exists.

How do I locate those files.????

Ash....

Report message to a moderator

Re: data migration [message #507820 is a reply to message #507805]

Wed, 18 May 2011 02:40

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

Senior Member
Account Moderator

Quote:

How do I locate those files.????

Where it says it is.

Regards
Michel

Report message to a moderator

Re: data migration [message #507826 is a reply to message #507820]

Wed, 18 May 2011 03:01

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

If you used EXPDP (data pump export), the file should be on database server (which is most probably different from your own PC).

Report message to a moderator

Re: data migration [message #507855 is a reply to message #507826]

Wed, 18 May 2011 05:06

z_ashwini
Messages: 26
Registered: February 2008
Location: Mumbai

Junior Member

thank you.... i got it....

Report message to a moderator

Re: data migration [message #507874 is a reply to message #507855]

Wed, 18 May 2011 06:09

z_ashwini
Messages: 26
Registered: February 2008
Location: Mumbai

Junior Member

Hi,

Can I use the same command and include about 200 tables in the same lines seperated by comma(,). There are about 300 tables in my DB.
Is it feasible to do so?

eg: expdp ncfmdba/ncfmdba@orcl dumpfile= alert_testdate.dmp logfile= alert_testdatelog.log tables=(table1,table2,....tble200) query=\" where created_dt IN (TO_DATE(TO_CHAR('11/4/2009'),'MM/DD/YYYY' ) , TO_DATE(TO_CHAR('10/26/2009'),'MM/DD/YYYY' ))\"

Ash....

Report message to a moderator

Re: data migration [message #507877 is a reply to message #507874]

Wed, 18 May 2011 06:14

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

As far as I can tell, yes - you can specify 200 tables.

[EDIT] Note that QUERY parameter will have to specify which table it is related to, such as

expdp ... query=alert_mstr:\"where created_by=...

[Updated on: Wed, 18 May 2011 06:16]

Report message to a moderator

Re: data migration [message #507888 is a reply to message #507877]

Wed, 18 May 2011 06:56

cookiemonster
Messages: 13962
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' )

Report message to a moderator

Re: data migration [message #508087 is a reply to message #507888]

Thu, 19 May 2011 06:42

z_ashwini
Messages: 26
Registered: February 2008
Location: Mumbai

Junior Member

thanks little... its working.... Smile

Smile

Report message to a moderator

Re: data migration [message #508325 is a reply to message #508087]

Fri, 20 May 2011 04:55

z_ashwini
Messages: 26
Registered: February 2008
Location: Mumbai

Junior Member

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...

Report message to a moderator

Previous Topic:	Data Migration using Datapump
Next Topic:	parameter files

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jan 10 21:39:14 CST 2025