No question at this time
DBA Top 10
1 M. Cadot 15900
2 B. Vroman 15500
3 A. Kavsek 10000
4 T. Boles 6800
5 J. Schnackenberg 5800
6 M. Hidayathullah ... 3900
7 P. Wisse 1800
8 T. P 700
9 B. Derous 500
9 R. Wauben 500
9 H. Steijntjes 500
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48209
Total active users1645
Act. users last 24h6
Act. users last hour0
Registered user hits last week186
Registered user hits last month812
Go up

IMPDP excluding table
Next thread: Ora-01031 Insufficient Privileges
Prev thread: Is there any way to check if a controlfile is a standby controlfile or current pre-startup?

Message Score Author Date
Hi I have a dumpfile using EXPDP of a complete ...... James Alcroft May 23, 2018, 18:33
" is a syntax delimiter for Windows you have to ...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts Michel Cadot May 23, 2018, 18:56
Hello James, ... or: use a parameter file. B...... Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts Bruno Vroman May 24, 2018, 08:32
Thanks guys, but unfortunately, neither of these o...... James Alcroft May 24, 2018, 10:31
If you don't show us (copy and paste) what you d...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts Michel Cadot May 24, 2018, 10:34
Hi Michel I tried both of your suggestions and ...... James Alcroft May 24, 2018, 11:09
Both work for me: <pre>C:\>impdp michel/michel ...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts Michel Cadot May 24, 2018, 11:21
Thanks Michel I think the Oracle message should...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts James Alcroft May 24, 2018, 11:32
With PS you have one more level of syntax analys...... Score: 400 PtsScore: 400 PtsScore: 400 PtsScore: 400 PtsScore: 400 Pts Michel Cadot May 24, 2018, 12:35

Follow up by mail Click here


Subject: IMPDP excluding table
Author: James Alcroft, United Kingdom
Date: May 23, 2018, 18:33, 29 days ago
Os info: Windows 2012
Oracle info: 12.1
Message: Hi

I have a dumpfile using EXPDP of a complete schema. I want to import this schema but exclude one table (due to size).

I have the following:

impdp orauser/pw@mydb dumpfile=export.dmp EXCLUDE=TABLE:"='DOCUMENT_REPOSITORY'" schemas=myschema

I get the following error:

LRM-00116: syntax error at 'TABLE:' following '='

I don't understand why I am getting this error. The syntax is correct, according the websites i have looked.

Anyone tell me what i have done wrong.

Many thanks.

James
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: IMPDP excluding table
Author: Michel Cadot, France
Date: May 23, 2018, 18:56, 29 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message:
" is a syntax delimiter for Windows you have to escape it.

impdp orauser/pw@mydb dumpfile=export.dmp EXCLUDE=TABLE:\"='DOCUMENT_REPOSITORY'\" schemas=myschema

or

impdp orauser/pw@mydb dumpfile=export.dmp EXCLUDE="TABLE:\"='DOCUMENT_REPOSITORY'\"" schemas=myschema

Regards
Michel
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: IMPDP excluding table
Author: Bruno Vroman, Belgium
Date: May 24, 2018, 08:32, 28 days ago
Score:   Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts
Message: Hello James,

... or: use a parameter file.

Best regards,

Bruno Vroman.
P.S. avoid writing passwords on command line
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: IMPDP excluding table
Author: James Alcroft, United Kingdom
Date: May 24, 2018, 10:31, 28 days ago
Message: Thanks guys, but unfortunately, neither of these options work. Same error.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: IMPDP excluding table
Author: Michel Cadot, France
Date: May 24, 2018, 10:34, 28 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message:
If you don't show us (copy and paste) what you do and get we can't help.

Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: IMPDP excluding table
Author: James Alcroft, United Kingdom
Date: May 24, 2018, 11:09, 28 days ago
Message: Hi Michel

I tried both of your suggestions and get the following

PS C:\Oracle\admin\mydb\dpdump> impdp orauser/pw@mydb dumpfile=export.dmp schemas=myschema EXCLUDE=TABLE:\"='DOCUMENT_REPOSITORY'\"
LRM-00118: syntax error at '=' at the end of input

PS C:\Oracle\admin\mydb\dpdump> impdp orauser/pw@mydb dumpfile=export.dmp schemas=myschema EXCLUDE="TABLE:\"='DOCUMENT_REPOSITORY'\""
LRM-00118: syntax error at '=' at the end of input
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: IMPDP excluding table
Author: Michel Cadot, France
Date: May 24, 2018, 11:21, 28 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message:
Both work for me:
C:\>impdp michel/michel dumpfile=expdat.dmp schemas=myschema EXCLUDE=TABLE:\"='DOCUMENT_REPOSITORY'\"


Import: Release 11.2.0.4.0 - Production on Jeu. Mai 24 11:19:56 2018

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39002: invalid operation
ORA-39165: Schema MYSCHEMA was not found.


C:\>impdp michel/michel dumpfile=expdat.dmp schemas=myschema EXCLUDE="TABLE:\"='DOCUMENT_REPOSITORY'\""

Import: Release 11.2.0.4.0 - Production on Jeu. Mai 24 11:20:15 2018

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39002: invalid operation
ORA-39165: Schema MYSCHEMA was not found.


(Of course, I have not the schema in my dump file but the parameter EXCLUDE is syntactically correct.)

Regards
Michel
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: IMPDP excluding table
Author: James Alcroft, United Kingdom
Date: May 24, 2018, 11:32, 28 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message: Thanks Michel

I think the Oracle message should be better - DO NOT USE POWERSHELL.

Works running it for command prompt.

You 'screenshot' gave me the pointer.

Thanks.

James
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: IMPDP excluding table
Author: Michel Cadot, France
Date: May 24, 2018, 12:35, 28 days ago
Score:   Score: 400 PtsScore: 400 PtsScore: 400 PtsScore: 400 PtsScore: 400 Pts
Message:
With PS you have one more level of syntax analysis and so have to escape once more the special characters (including \ itself).

Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here