Home » RDBMS Server » Server Utilities » data migration (oracle 11g)
data migration [message #506591] Tue, 10 May 2011 03:05 Go to next message
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
Re: data migration [message #506592 is a reply to message #506591] Tue, 10 May 2011 03:08 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Data pump (or export/import)?
Re: data migration [message #507578 is a reply to message #506592] Tue, 17 May 2011 03:35 Go to previous messageGo to next message
z_ashwini
Messages: 26
Registered: February 2008
Location: Mumbai
Junior Member

thx little
data migration [message #507582 is a reply to message #506591] Tue, 17 May 2011 03:55 Go to previous messageGo to next message
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
Re: data migration [message #507584 is a reply to message #507582] Tue, 17 May 2011 04:06 Go to previous messageGo to next message
Littlefoot
Messages: 21806
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>
Re: data migration [message #507585 is a reply to message #507584] Tue, 17 May 2011 04:09 Go to previous messageGo to next message
cookiemonster
Messages: 13917
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 
Re: data migration [message #507586 is a reply to message #507585] Tue, 17 May 2011 04:19 Go to previous messageGo to next message
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
Re: data migration [message #507587 is a reply to message #507586] Tue, 17 May 2011 04:21 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Read the whole topic again; you might find answers to your problems.
Re: data migration [message #507588 is a reply to message #507586] Tue, 17 May 2011 04:26 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
A quick look at the documentation shows you're missing a keyword.
Re: data migration [message #507600 is a reply to message #507588] Tue, 17 May 2011 05:18 Go to previous messageGo to next message
z_ashwini
Messages: 26
Registered: February 2008
Location: Mumbai
Junior Member

i AM STILL GETTING THE ERROR::
i HAVE TRIED USING WIZARD TOO 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:(
Re: data migration [message #507602 is a reply to message #507600] Tue, 17 May 2011 05:33 Go to previous messageGo to next message
Littlefoot
Messages: 21806
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 Go to previous messageGo to next message
z_ashwini
Messages: 26
Registered: February 2008
Location: Mumbai
Junior Member

its working.... thx a ton Smile
Re: data migration [message #507805 is a reply to message #507628] Wed, 18 May 2011 01:39 Go to previous messageGo to next message
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....

Re: data migration [message #507820 is a reply to message #507805] Wed, 18 May 2011 02:40 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
How do I locate those files.????

Where it says it is.

Regards
Michel
Re: data migration [message #507826 is a reply to message #507820] Wed, 18 May 2011 03:01 Go to previous messageGo to next message
Littlefoot
Messages: 21806
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).
Re: data migration [message #507855 is a reply to message #507826] Wed, 18 May 2011 05:06 Go to previous messageGo to next message
z_ashwini
Messages: 26
Registered: February 2008
Location: Mumbai
Junior Member

thank you.... i got it....
Re: data migration [message #507874 is a reply to message #507855] Wed, 18 May 2011 06:09 Go to previous messageGo to next message
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....
Re: data migration [message #507877 is a reply to message #507874] Wed, 18 May 2011 06:14 Go to previous messageGo to next message
Littlefoot
Messages: 21806
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 Go to previous messageGo to next message
cookiemonster
Messages: 13917
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 #508087 is a reply to message #507888] Thu, 19 May 2011 06:42 Go to previous messageGo to next message
z_ashwini
Messages: 26
Registered: February 2008
Location: Mumbai
Junior Member

thanks little... its working.... Smile
Re: data migration [message #508325 is a reply to message #508087] Fri, 20 May 2011 04:55 Go to previous message
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...
Previous Topic: Data Migration using Datapump
Next Topic: parameter files
Goto Forum:
  


Current Time: Thu Mar 28 15:28:55 CDT 2024