OraFAQ Forum: Server Utilities » Need help in Export Import

Home » RDBMS Server » Server Utilities » Need help in Export Import (Windows 2012 EE)

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

Need help in Export Import [message #674198]

Wed, 09 January 2019 19:53

Akmmhto
Messages: 38
Registered: September 2018

Member

Dear team,
I want one help..

I have one schema user in 11G database.
I want to take export of it and import it in Oracle
12 C.

Challenge is that the User in 11g having default
Tablespace system.
All objects owned by this schema is in SYSTEM tablespace

So how can I import it to another database , so
That it can use tablespace "users" or any custom
Tablespace created by me..

Pls suggest..

Report message to a moderator

Re: Need help in Export Import [message #674199 is a reply to message #674198]

Wed, 09 January 2019 19:58

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Akmmhto wrote on Wed, 09 January 2019 17:53

Dear team,
I want one help..

I have one schema user in 11G database.
I want to take export of it and import it in Oracle
12 C.

Challenge is that the User in 11g having default
Tablespace system.
All objects owned by this schema is in SYSTEM tablespace

So how can I import it to another database , so
That it can use tablespace "users" or any custom
Tablespace created by me..

Pls suggest..

IMPDP supports tablespace remapping

REMAP_TABLESPACE
Tablespace objects are remapped to another tablespace.

Please Read The Fine Manual below to learn more

https://docs.oracle.com/database/121/SUTIL/toc.htm

Report message to a moderator

Re: Need help in Export Import [message #674200 is a reply to message #674199]

Wed, 09 January 2019 22:10

Akmmhto
Messages: 38
Registered: September 2018

Member

Some objects are in SYSTEM and some are in USERS..
So in this case what will be the query of impdp..

Pls suggest..

Thanks for your cooperation..

Report message to a moderator

Re: Need help in Export Import [message #674201 is a reply to message #674200]

Wed, 09 January 2019 22:47

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Akmmhto wrote on Wed, 09 January 2019 20:10

Some objects are in SYSTEM and some are in USERS..
So in this case what will be the query of impdp..

Pls suggest..

Thanks for your cooperation..

Stop changing the requirements.

You can invoke IMPDP twice; once for each source tablespace

Report message to a moderator

Re: Need help in Export Import [message #674202 is a reply to message #674198]

Thu, 10 January 2019 01:15

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

Senior Member
Account Moderator

Quote:

All objects owned by this schema is in SYSTEM tablespace

Very bad thing, NO user objects should be in SYSTEM tablespace.
As well as thinking that export is a backup policy (you post this in "Backup & Recovery" forum).

Quote:

Some objects are in SYSTEM and some are in USERS..

You can use several REMAP_TABLESPACE options.

Report message to a moderator

Re: Need help in Export Import [message #674216 is a reply to message #674202]

Thu, 10 January 2019 19:36

Akmmhto
Messages: 38
Registered: September 2018

Member

Thanks...I used remap for multiple tablespace in single import ..it worked..

Nickel: I know it is bad , but here I have some idiot application users .
They experimentally created objects in system TS and also in Users TS.

Black swan: I was not changing requiment , I just cleared my whole question.

Report message to a moderator

Re: Need help in Export Import [message #674217 is a reply to message #674216]

Thu, 10 January 2019 20:47

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Akmmhto wrote on Thu, 10 January 2019 17:36

Thanks...I used remap for multiple tablespace in single import ..it worked..

Nickel: I know it is bad , but here I have some idiot application users .
They experimentally created objects in system TS and also in Users TS.

Black swan: I was not changing requiment , I just cleared my whole question.

Consider to implement actual code reviews for any & ALL changes made to Production database.
IMO, no application user should ever have the ability to issue DDL against production database.

Report message to a moderator

Re: Need help in Export Import [message #674219 is a reply to message #674216]

Fri, 11 January 2019 00:40

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

Senior Member
Account Moderator

Quote:

Nickel: I know it is bad , but here I have some idiot application users .
They experimentally created objects in system TS and also in Users TS.

How users could have quota on SYSTEM tablespace?
You have to fix that.

[Updated on: Fri, 11 January 2019 00:40]

Report message to a moderator

Previous Topic:	Have to press any button to complete TFA installation
Next Topic:	impdp can't read the file

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Feb 07 01:10:27 CST 2025