Home » RDBMS Server » Server Utilities » Import datapump issue (Oracle 19c & Windows server 2019 Standard)
Import datapump issue [message #683674] Fri, 12 February 2021 05:08 Go to next message
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
Re: Import datapump issue [message #683677 is a reply to message #683674] Fri, 12 February 2021 07:10 Go to previous messageGo to next message
Michel Cadot
Messages: 67842
Registered: March 2007
Location: Nanterre, France, http://...
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.

Re: Import datapump issue [message #683678 is a reply to message #683677] Fri, 12 February 2021 07:42 Go to previous messageGo to next message
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
Re: Import datapump issue [message #683679 is a reply to message #683678] Fri, 12 February 2021 07:52 Go to previous messageGo to next message
Michel Cadot
Messages: 67842
Registered: March 2007
Location: Nanterre, France, http://...
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?

Re: Import datapump issue [message #683680 is a reply to message #683679] Fri, 12 February 2021 08:00 Go to previous messageGo to next message
Kesavbas
Messages: 12
Registered: February 2021
Junior Member
Yes. Yes..
Re: Import datapump issue [message #683681 is a reply to message #683680] Fri, 12 February 2021 09:20 Go to previous messageGo to next message
Michel Cadot
Messages: 67842
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

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

Re: Import datapump issue [message #683682 is a reply to message #683681] Fri, 12 February 2021 09:37 Go to previous messageGo to next message
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.
Re: Import datapump issue [message #683683 is a reply to message #683682] Fri, 12 February 2021 09:50 Go to previous messageGo to next message
Michel Cadot
Messages: 67842
Registered: March 2007
Location: Nanterre, France, http://...
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.

Re: Import datapump issue [message #683688 is a reply to message #683683] Fri, 12 February 2021 14:33 Go to previous messageGo to next message
EdStevens
Messages: 1315
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.
Re: Import datapump issue [message #683689 is a reply to message #683688] Fri, 12 February 2021 17:51 Go to previous messageGo to next message
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.
Re: Import datapump issue [message #683700 is a reply to message #683683] Mon, 15 February 2021 03:33 Go to previous messageGo to next message
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.
Re: Import datapump issue [message #683702 is a reply to message #683700] Mon, 15 February 2021 05:24 Go to previous messageGo to next message
Michel Cadot
Messages: 67842
Registered: March 2007
Location: Nanterre, France, http://...
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.

Re: Import datapump issue [message #683703 is a reply to message #683702] Mon, 15 February 2021 05:54 Go to previous messageGo to next message
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,
Re: Import datapump issue [message #683704 is a reply to message #683703] Mon, 15 February 2021 06:06 Go to previous messageGo to next message
Michel Cadot
Messages: 67842
Registered: March 2007
Location: Nanterre, France, http://...
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.

Re: Import datapump issue [message #683705 is a reply to message #683704] Mon, 15 February 2021 06:19 Go to previous messageGo to next message
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.
Re: Import datapump issue [message #683709 is a reply to message #683705] Mon, 15 February 2021 07:51 Go to previous messageGo to next message
Michel Cadot
Messages: 67842
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

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

Re: Import datapump issue [message #683710 is a reply to message #683709] Mon, 15 February 2021 08:36 Go to previous messageGo to next message
Kesavbas
Messages: 12
Registered: February 2021
Junior Member
Same parfile works in other server.
Re: Import datapump issue [message #683712 is a reply to message #683710] Mon, 15 February 2021 10:39 Go to previous messageGo to next message
Michel Cadot
Messages: 67842
Registered: March 2007
Location: Nanterre, France, http://...
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.

Re: Import datapump issue [message #683741 is a reply to message #683712] Wed, 17 February 2021 08:52 Go to previous messageGo to next message
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.
Re: Import datapump issue [message #683743 is a reply to message #683741] Wed, 17 February 2021 09:59 Go to previous messageGo to next message
Michel Cadot
Messages: 67842
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

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

Re: Import datapump issue [message #683744 is a reply to message #683743] Wed, 17 February 2021 10:47 Go to previous messageGo to next message
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
Re: Import datapump issue [message #683746 is a reply to message #683744] Wed, 17 February 2021 11:56 Go to previous messageGo to next message
Michel Cadot
Messages: 67842
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

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

Re: Import datapump issue [message #683751 is a reply to message #683746] Wed, 17 February 2021 19:30 Go to previous message
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!
Previous Topic: Unable to load CSV file to Oracle Table
Next Topic: Slow application
Goto Forum:
  


Current Time: Sat May 08 14:44:09 CDT 2021