Home » SQL & PL/SQL » Client Tools » export/import schemas (sql developer 3.3.20.09)
export/import schemas [message #575932] Tue, 29 January 2013 09:43 Go to next message
lg123
Messages: 225
Registered: August 2008
Senior Member
Hi,

can we export/import the schemas from one oracle database to another using sql developer tool?
Re: export/import schemas [message #575933 is a reply to message #575932] Tue, 29 January 2013 09:48 Go to previous messageGo to next message
BlackSwan
Messages: 23159
Registered: January 2009
Senior Member
>can we export/import the schemas from one oracle database to another using sql developer tool?
I don't know.
Can you?

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: export/import schemas [message #576110 is a reply to message #575932] Thu, 31 January 2013 09:58 Go to previous messageGo to next message
thatjeffsmith
Messages: 43
Registered: July 2009
Location: Raleigh, NC
Member

Yes. You have Tools > Database Copy and an interface for Data Pump in SQL Developer.

The more you tell us about your requirements, the better advice we can provide.
Re: export/import schemas [message #576240 is a reply to message #576110] Fri, 01 February 2013 14:11 Go to previous messageGo to next message
lg123
Messages: 225
Registered: August 2008
Senior Member
I tried using datapump option in oracle SQL Developer. But I am getting an error. I have attached the screenshot. Please let me know how to proceed further?
  • Attachment: error.png
    (Size: 85.79KB, Downloaded 190 times)
Re: export/import schemas [message #576241 is a reply to message #576240] Fri, 01 February 2013 14:26 Go to previous messageGo to next message
Michel Cadot
Messages: 60015
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What don't you understand in the message? It is pretty clear.

Regards
Michel
Re: export/import schemas [message #576242 is a reply to message #576241] Fri, 01 February 2013 15:13 Go to previous messageGo to next message
lg123
Messages: 225
Registered: August 2008
Senior Member
Ok, I realize that I do not have the privilege of using datapump without the DBA privilege. I tried to export a table alone from one schema (database A) to another schema (database B). These 2 databases are oracle but belong to different versions, one is 10g and the other one is 11g. The 10g database runs on linux and the 11g database runs on windows xp. While I was trying to import the table to another schema using Oracle SQL Developer tool, I got 2 errors.

SQL Error: ORA-02158: invalid CREATE INDEX option
02158. 00000 - "invalid CREATE INDEX option"
*Cause: An option other than COMPRESS, NOCOMPRESS, PCTFREE, INITRANS,
MAXTRANS, STORAGE, TABLESPACE, PARALLEL, NOPARALLEL, RECOVERABLE,
UNRECOVERABLE, LOGGING, NOLOGGING, LOCAL, or GLOBAL was specified.
*Action: Choose one of the valid CREATE INDEX options.



I understand that I get this error because there is some syntactical mistake. But this was the same script which ran in the source schema while creating the table/index. Am I getting this error because I am trying to export from Oracle 10g (on linux) to Oracle 11g ( on windows)?
Re: export/import schemas [message #576243 is a reply to message #576242] Fri, 01 February 2013 15:20 Go to previous messageGo to next message
BlackSwan
Messages: 23159
Registered: January 2009
Senior Member
export/import is valid between/across Oracle versions & different OS

The only "restriction" is that the export must be done using the lower Oracle version; which is what you are attempting

post results from SQL below from both DB

SELECT * FROM V$VERSION;
Re: export/import schemas [message #576291 is a reply to message #576243] Sat, 02 February 2013 10:35 Go to previous messageGo to next message
lg123
Messages: 225
Registered: August 2008
Senior Member
This is for the source database A

select * from v$version;

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
"CORE 11.2.0.2.0 Production"
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production



This is for the destination database B

SQL> select * from v$version;

BANNER
-------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production


I realize that i am exporting from 11.2.0.2 to 11.2.0.1. Is this the reason why I am getting errors?
Re: export/import schemas [message #576292 is a reply to message #576291] Sat, 02 February 2013 11:44 Go to previous messageGo to next message
Michel Cadot
Messages: 60015
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe.

Regards
Michel
Re: export/import schemas [message #576293 is a reply to message #576292] Sat, 02 February 2013 11:52 Go to previous messageGo to next message
lg123
Messages: 225
Registered: August 2008
Senior Member
It's wierd. Because I was able to export and import almost 20 tables which belonged to another schema.
Re: export/import schemas [message #576294 is a reply to message #576293] Sat, 02 February 2013 12:02 Go to previous messageGo to next message
lg123
Messages: 225
Registered: August 2008
Senior Member
I am attaching the script that I ran in the destination database. I got the script from the source database. Please let me know if I can proceed further.
  • Attachment: error.txt
    (Size: 2.34KB, Downloaded 116 times)
Re: export/import schemas [message #576295 is a reply to message #576294] Sat, 02 February 2013 12:12 Go to previous messageGo to next message
Michel Cadot
Messages: 60015
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is obvious that no statements ends with ";" or a "/".
As BlackSwan has told you, you must export with the LOWEST version.

Regards
Michel
Re: export/import schemas [message #576300 is a reply to message #576242] Sat, 02 February 2013 17:38 Go to previous messageGo to next message
BlackSwan
Messages: 23159
Registered: January 2009
Senior Member
>one is 10g and the other one is 11g.

Why did you mis-state reality as shown above?

>I realize that i am exporting from 11.2.0.2 to 11.2.0.1.

When you do something wrong, you should expect errors!
Re: export/import schemas [message #576397 is a reply to message #576300] Mon, 04 February 2013 08:18 Go to previous messageGo to next message
lg123
Messages: 225
Registered: August 2008
Senior Member
I apologize. One of my colleagues told that it was 10g. I guess someone has upgraded it to 11g recently.
Re: export/import schemas [message #576398 is a reply to message #576397] Mon, 04 February 2013 08:38 Go to previous messageGo to next message
Michel Cadot
Messages: 60015
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do not trust anyone, ALWAYS verify by yourself and post accurate information.

Regards
Michel
Re: export/import schemas [message #576401 is a reply to message #576398] Mon, 04 February 2013 10:34 Go to previous messageGo to next message
lg123
Messages: 225
Registered: August 2008
Senior Member
I want to upgrade oracle 11.2.0.1 to 11.2.0.2 so that I would be able to load the tables.

I have downloaded 6 files (11.2.0.2 patchset). Please let me know if i have to run the "setup.exe" in the "database" folder?
Re: export/import schemas [message #576403 is a reply to message #576401] Mon, 04 February 2013 10:40 Go to previous messageGo to next message
lg123
Messages: 225
Registered: August 2008
Senior Member
If I run "setup.exe" file, I would like to know if it will newly install 11.2.0.2 or upgrade the already existing Oracle 11.2.0.1 database?
Re: export/import schemas [message #576405 is a reply to message #576403] Mon, 04 February 2013 10:57 Go to previous messageGo to next message
Michel Cadot
Messages: 60015
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In the end, just read the readme file, if you can.

Regards
Michel

[Updated on: Mon, 04 February 2013 10:57]

Report message to a moderator

Re: export/import schemas [message #576408 is a reply to message #576405] Mon, 04 February 2013 11:54 Go to previous messageGo to next message
lg123
Messages: 225
Registered: August 2008
Senior Member
I got struck up here. Please refer to the snapshot.
Re: export/import schemas [message #576409 is a reply to message #576408] Mon, 04 February 2013 12:03 Go to previous messageGo to next message
lg123
Messages: 225
Registered: August 2008
Senior Member
Snapshot attached again.
  • Attachment: 13.jpg
    (Size: 69.23KB, Downloaded 152 times)
Re: export/import schemas [message #576410 is a reply to message #576409] Mon, 04 February 2013 12:07 Go to previous messageGo to next message
lg123
Messages: 225
Registered: August 2008
Senior Member
I am really sorry. The Installer window was hiding the database creation wizard. Please ignore my previous message.
Re: export/import schemas [message #576417 is a reply to message #576410] Mon, 04 February 2013 14:09 Go to previous messageGo to next message
lg123
Messages: 225
Registered: August 2008
Senior Member
I could successfully upgrade the database to 11.2.0.2, but I am not able to load the table from oracle 11.2.0.2 to oracle 11.2.0.2. I get the same errors that I got before.

SQL Error: ORA-02158: invalid CREATE INDEX option
02158. 00000 - "invalid CREATE INDEX option"
*Cause: An option other than COMPRESS, NOCOMPRESS, PCTFREE, INITRANS,
MAXTRANS, STORAGE, TABLESPACE, PARALLEL, NOPARALLEL, RECOVERABLE,
UNRECOVERABLE, LOGGING, NOLOGGING, LOCAL, or GLOBAL was specified.
*Action: Choose one of the valid CREATE INDEX options.

Don't know why I am getting this error.
Re: export/import schemas [message #576838 is a reply to message #576417] Fri, 08 February 2013 08:34 Go to previous messageGo to next message
_jum
Messages: 490
Registered: February 2008
Senior Member
Is there a table OADB.AAGENTCOMPCODE ?
Does the tablespace "HISTICAGENTCOMP" exist ?
What do you get if you build the index manually ?
CREATE INDEX OADB.AAGENTCOMPCODE_I ON OADB.AAGENTCOMPCODE (ROWDATE ASC, STARTTIME ASC) 
LOGGING 
TABLESPACE "HISTICAGENTCOMP" 
PCTFREE 10 
INITRANS 2 
STORAGE 
( 
  BUFFER_POOL DEFAULT 
);

Re: export/import schemas [message #576839 is a reply to message #576838] Fri, 08 February 2013 08:50 Go to previous messageGo to next message
lg123
Messages: 225
Registered: August 2008
Senior Member
Yes, I had to create manually.
Re: export/import schemas [message #576840 is a reply to message #576839] Fri, 08 February 2013 09:05 Go to previous messageGo to next message
_jum
Messages: 490
Registered: February 2008
Senior Member
Unfortunately the error text doesn't contain any hint for the problem and you could succesful create the index with the same options as in the error.txt ?!
I'm sorry - no idea...
Re: export/import schemas [message #576864 is a reply to message #576840] Fri, 08 February 2013 14:11 Go to previous message
lg123
Messages: 225
Registered: August 2008
Senior Member
I had to separate the ")" symbol and the keyword "create". It was a syntax error.
Previous Topic: No SQL Server option in Oracle SQL Developer
Next Topic: Out of memory using DDL export in Toad
Goto Forum:
  


Current Time: Mon Dec 22 02:37:52 CST 2014

Total time taken to generate the page: 0.11110 seconds