Home » RDBMS Server » Server Utilities » Need help in Export Import (Windows 2012 EE)
Need help in Export Import [message #674198] Wed, 09 January 2019 19:53 Go to next message
Akmmhto
Messages: 25
Registered: September 2018
Junior 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..
Re: Need help in Export Import [message #674199 is a reply to message #674198] Wed, 09 January 2019 19:58 Go to previous messageGo to next message
BlackSwan
Messages: 26643
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
Re: Need help in Export Import [message #674200 is a reply to message #674199] Wed, 09 January 2019 22:10 Go to previous messageGo to next message
Akmmhto
Messages: 25
Registered: September 2018
Junior 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..
Re: Need help in Export Import [message #674201 is a reply to message #674200] Wed, 09 January 2019 22:47 Go to previous messageGo to next message
BlackSwan
Messages: 26643
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
Re: Need help in Export Import [message #674202 is a reply to message #674198] Thu, 10 January 2019 01:15 Go to previous messageGo to next message
Michel Cadot
Messages: 66728
Registered: March 2007
Location: Nanterre, France, http://...
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.

Re: Need help in Export Import [message #674216 is a reply to message #674202] Thu, 10 January 2019 19:36 Go to previous messageGo to next message
Akmmhto
Messages: 25
Registered: September 2018
Junior 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.

Re: Need help in Export Import [message #674217 is a reply to message #674216] Thu, 10 January 2019 20:47 Go to previous messageGo to next message
BlackSwan
Messages: 26643
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.
Re: Need help in Export Import [message #674219 is a reply to message #674216] Fri, 11 January 2019 00:40 Go to previous message
Michel Cadot
Messages: 66728
Registered: March 2007
Location: Nanterre, France, http://...
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:
  


Current Time: Fri Dec 13 21:05:15 CST 2019