OraFAQ Forum: Server Utilities » Import datapump issue

Home » RDBMS Server » Server Utilities » Import datapump issue (Oracle 19c & Windows server 2019 Standard)

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

Import datapump issue [message #683674]

Fri, 12 February 2021 05:08

Kesavbas
Messages: 12
Registered: February 2021

Junior Member

I have tried to import list of tables. I am able to import using same command in other databases in different servers. But I am facing below error in one of database server during import of list of tables. Can someone please suggest reason for this.

Executed Command: impdp '/ as sysdba' parfile=D:\script\SAPSR3DBPAR.txt

ORA-39071: Value for TABLES is badly formed.
ORA-00936: missing expression
ORA-31655: no data or metadata objects selected for job

Par file contents: I have removed some of the tables from par-file to keep as below.
DIRECTORY=DATA_PUMP_DIR
DUMPFILE=SAPSR3DB_BKP08022021
TABLES=SAPSR3DB.J2EE_CONFIGENTRY,
SAPSR3DB.JJ2EE_CONFIG,
SAPSR3DB.XMII_SCHED_STATUS
TABLE_EXISTS_ACTION=TRUNCATE
Logfile=IMP37SAPSR3DB11022021.log

Attachment: Execution_Log.JPG
(Size: 72.53KB, Downloaded 2647 times)

Report message to a moderator

Re: Import datapump issue [message #683677 is a reply to message #683674]

Fri, 12 February 2021 07:10

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

Senior Member
Account Moderator

Quote:

Par file contents: I have removed some of the tables from par-file to keep as below

What you posted is this what is working? what is not working? something you did not try?

Post the actual parfile.
Was the parfile written in the same OS that it is used?

Note: do NOT use SYSDBA for something that can be done by another account, read SYS/SYSDBA is special.

Report message to a moderator

Re: Import datapump issue [message #683678 is a reply to message #683677]

Fri, 12 February 2021 07:42

Kesavbas
Messages: 12
Registered: February 2021

Junior Member

I have used another user. Same error has come. I have attached the parfile. Please let me know if there is any suggestion for solution.
Regards, Keshab Basnet

Attachment: SAPSR3DBPAR.txt
(Size: 5.78KB, Downloaded 3099 times)

Report message to a moderator

Re: Import datapump issue [message #683679 is a reply to message #683678]

Fri, 12 February 2021 07:52

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

Senior Member
Account Moderator

Before knowing if there is any solution we must determine what the problem is.

You didn't answer to my question: "Was the parfile written in the same OS that it is used?"
Are all servers on Windows?

Report message to a moderator

Re: Import datapump issue [message #683680 is a reply to message #683679]

Fri, 12 February 2021 08:00

Kesavbas
Messages: 12
Registered: February 2021

Junior Member

Yes. Yes..

Report message to a moderator

Re: Import datapump issue [message #683681 is a reply to message #683680]

Fri, 12 February 2021 09:20

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

Senior Member
Account Moderator

Does this work if you remove some tables, say half of them?

Report message to a moderator

Re: Import datapump issue [message #683682 is a reply to message #683681]

Fri, 12 February 2021 09:37

Kesavbas
Messages: 12
Registered: February 2021

Junior Member

These are relational tables to each other which I can not exclude. I have to import all tables which is listed in parfile at the same time. Please suggest if any.

Note:
If Architecture of Database schema objects are badly constructed during development. it will exclude related record during import. If you have asked considering above my sentence. Please consider above sentence while providing solution to me. I hope to hear from you.

Report message to a moderator

Re: Import datapump issue [message #683683 is a reply to message #683682]

Fri, 12 February 2021 09:50

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

Senior Member
Account Moderator

It does not matter the import succeeds or not, it is just to pass the first step.
Add the following parameter to the command line: "abort_step=-1" the it will just create the master table and stop.
Copy and paste the complete session; before Please read How to use [code] tags and make your code easier to read.

Report message to a moderator

Re: Import datapump issue [message #683688 is a reply to message #683683]

Fri, 12 February 2021 14:33

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

Quote:

These are relational tables to each other which I can not exclude. I have to import all tables which is listed in parfile at the same time

No such thing as "at the same time". Even if they are all imported in the same execution of impdp, each table is still being imported in a serial sequence. Really no different than if you loaded each one with a separate import.

Report message to a moderator

Re: Import datapump issue [message #683689 is a reply to message #683688]

Fri, 12 February 2021 17:51

Kesavbas
Messages: 12
Registered: February 2021

Junior Member

Some application has pointed to schema now as well. I need to import all. Table size is not large. All listed table include not more than 10 GB. I will try your suggestion and update you regarding that parameter u asked me to keep. Sat & Sunday is off here. Monday I update u outcome of that script execution.

Report message to a moderator

Re: Import datapump issue [message #683700 is a reply to message #683683]

Mon, 15 February 2021 03:33

Kesavbas
Messages: 12
Registered: February 2021

Junior Member

Hi/Bonjour, I have added "parameter abort_step=-1" and executed as below. Please find the output as below.

C:\Users\valadm>impdp sysimp/sysimp@val parfile=D:\script\SAPSR3DBPAR.txt
Import: Release 19.0.0.0.0 - Production on Mon Feb 15 03:26:12 2021
Version 19.8.0.0.0
Copyright (c) 1982, 2020, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
ORA-31655: no data or metadata objects selected for job
ORA-39071: Value for TABLES is badly formed.
ORA-00936: missing expression
Master table "SYSIMP"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYSIMP"."SYS_IMPORT_TABLE_01": sysimp/********@val parfile=D:\script\SAPSR3DBPAR.txt
Job "SYSIMP"."SYS_IMPORT_TABLE_01" stopped by user request at Mon Feb 15 03:26:20 2021 elapsed 0 00:00:07

Can you please suggest.

Report message to a moderator

Re: Import datapump issue [message #683702 is a reply to message #683700]

Mon, 15 February 2021 05:24

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

Senior Member
Account Moderator

I tried the parfile you posted and there is no error, so either this parfile is not the actual one or you encountered a bug.

Report message to a moderator

Re: Import datapump issue [message #683703 is a reply to message #683702]

Mon, 15 February 2021 05:54

Kesavbas
Messages: 12
Registered: February 2021

Junior Member

Allo, I have just kept one single table I am able to import. I am trying to make in chunck by finding dependency of tables. for example, getting metadeta of each tables and importing list of tables in chuncks. Is there other way, please let me know. Cordialements,

Report message to a moderator

Re: Import datapump issue [message #683704 is a reply to message #683703]

Mon, 15 February 2021 06:06

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

Senior Member
Account Moderator

The only thing I can do is to repeat my previous post.
I think you didn't post the actual parfile.

Report message to a moderator

Re: Import datapump issue [message #683705 is a reply to message #683704]

Mon, 15 February 2021 06:19

Kesavbas
Messages: 12
Registered: February 2021

Junior Member

I have sent you actual parfile by replacing only schema name with new schemaname and adding additional 1 character in table name considering company specific privacy law.

Report message to a moderator

Re: Import datapump issue [message #683709 is a reply to message #683705]

Mon, 15 February 2021 07:51

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

Senior Member
Account Moderator

So this is not the actual file, so we can't help more.

Report message to a moderator

Re: Import datapump issue [message #683710 is a reply to message #683709]

Mon, 15 February 2021 08:36

Kesavbas
Messages: 12
Registered: February 2021

Junior Member

Same parfile works in other server.

Report message to a moderator

Re: Import datapump issue [message #683712 is a reply to message #683710]

Mon, 15 February 2021 10:39

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

Senior Member
Account Moderator

Same maybe not same, who knows?
My advice: copy the file from the server it works onto the server it does not.

Report message to a moderator

Re: Import datapump issue [message #683741 is a reply to message #683712]

Wed, 17 February 2021 08:52

Kesavbas
Messages: 12
Registered: February 2021

Junior Member

Just for information, I made 4 separate parfiles and kept different tables in 4 parfiles considering relation of tables to each other. I have successfully imported. But why not from single parfile with all tables. I do no know exact reason. Total schema size is not very large & size of schema imported is 3 times less than RAM being used in
server. There is not such application running. If u know reason please let me know.

Report message to a moderator

Re: Import datapump issue [message #683743 is a reply to message #683741]

Wed, 17 February 2021 09:59

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

Senior Member
Account Moderator

Once again, the reason is IN the parfile you refuse to post.

Report message to a moderator

Re: Import datapump issue [message #683744 is a reply to message #683743]

Wed, 17 February 2021 10:47

Kesavbas
Messages: 12
Registered: February 2021

Junior Member

Thanks for your support. Problem is solved. Vous venez de France. Je suis desole pour cette question. Merci

Report message to a moderator

Re: Import datapump issue [message #683746 is a reply to message #683744]

Wed, 17 February 2021 11:56

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

Senior Member
Account Moderator

No problem with the question, too bad we can't investigate in the root of it.

Report message to a moderator

Re: Import datapump issue [message #683751 is a reply to message #683746]

Wed, 17 February 2021 19:30

Kesavbas
Messages: 12
Registered: February 2021

Junior Member

Salu Michel, je vais contacter a vous si'l y a quelque problem encore!! Merci.. Bonne journee!!
Thanks!

Report message to a moderator

Previous Topic:	impdp bypassing DUMPFILE
Next Topic:	Slow application

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jan 30 15:42:48 CST 2025