No question at this time
DBA Top 10
1 A. Kavsek 8600
2 M. Cadot 7200
3 B. Vroman 6200
4 T. Boles 4550
5 J. Schnackenberg 4100
6 P. Wisse 3200
7 T. P 1200
8 G. Lambregts 1100
8 . Lauri 1100
10 R. Pattyn 800
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48373
Total active users1542
Act. users last 24h7
Act. users last hour0
Registered user hits last week192
Registered user hits last month1051
Go up

multiple tablespace remap in single
Next thread: explain plan getting full table scan.
Prev thread: Store Oracle TDE keys in Azure Key Vault

Message Score Author Date
Hi masters, i am having multiple tablespaces on...... javed akhtar May 10, 2019, 06:36
Hello Javed, to be tested -<I>I don't use 11g f...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts Bruno Vroman May 10, 2019, 09:50
Hi, to start with, I believe your syntax is not...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts Ales Kavsek May 10, 2019, 17:36
Hello Javed, thanks to Ales you know the answer...... Bruno Vroman May 11, 2019, 11:01

Follow up by mail Click here


Subject: multiple tablespace remap in single
Author: javed akhtar, India
Date: May 10, 2019, 06:36, 12 days ago
Os info: linux
Oracle info: 11.2
Message: Hi masters,

i am having multiple tablespaces on PROD whereas i am having only two tablespace on test server

is any possibility i can avoid the repeating remap_tablespace

remap_tablespace=PROD_TBS1:TEST_TBS1,PROD_TBS2:TEST1,PROD_TBS3:TEST_TBS1

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

Subject: Re: multiple tablespace remap in single
Author: Bruno Vroman, Belgium
Date: May 10, 2019, 09:50, 12 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message: Hello Javed,

to be tested -I don't use 11g for a long long time, I don't remember- but when you import a schema, if an object is to be created in a non-existing tablespace it will be created in the default tablespace of the owner.
Example:
  in source, scott.ttt is in tablespace prod_tbs1
  in target, scott.ttt default tablespace is test_tbs1
  in target, there is no tablespace prod_tbs1
  at import time scott.ttt will be created in test_tbs1
This is "just one test away"... either it works or the impdp fails "cannot create TTT in prod_tbs1"

But anyway, why don't you use the remap_tablespace way that has the advantage of being clear? Even if you have many tablespaces, it is easy to generate the content of a parameter file with for example:
SELECT 'remap_tablespace=' || tablespace_name ||':TEST_TBS1'

FROM dba_segments
WHERE owner = 'SCOTT'
GROUP BY tablespace_name
ORDER BY 1;
Best regards,

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

Subject: Re: multiple tablespace remap in single
Author: Ales Kavsek, Slovenia
Date: May 10, 2019, 17:36, 12 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message: Hi,

to start with, I believe your syntax is not correct, you have to specify REMAP_TABLESPACE for each remapped tablespace....(don't have 11g at hand to test it, but I'm pretty much sure)....

REMAP_TABLESPACE=PROD_TBS1:TEST_TBS1 REMAP_TABLESPACE=PROD_TBS2:TEST1 ...

You can easily generate the clause as Bruno suggested.
Using REMAP_TABLESPACE is the only way to change tablespace during impdp import. (Fortunately Oracle removed notorious behavior of classic exp/imp where some segments where "automatically" remapped to user default tablespace, but some not).

Regards,
Ales

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

Subject: Re: multiple tablespace remap in single
Author: Bruno Vroman, Belgium
Date: May 11, 2019, 11:01, 11 days ago
Message: Hello Javed,

thanks to Ales you know the answer: the "automatic remap to default tablespace" was with the old "exp/imp", but is not a feature of datapump.
So just generate the right list to create a parameter file, it is not "more work" for you to have 4 "remap_tabespace" or 50 (the SELECT I've provided should provide the list to review).

Best regards,

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