export/import schemas [message #575932] |
Tue, 29 January 2013 09:43 |
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 #576242 is a reply to message #576241] |
Fri, 01 February 2013 15:13 |
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 #576291 is a reply to message #576243] |
Sat, 02 February 2013 10:35 |
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 #576401 is a reply to message #576398] |
Mon, 04 February 2013 10:34 |
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 #576417 is a reply to message #576410] |
Mon, 04 February 2013 14:09 |
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 |
_jum
Messages: 577 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 #576840 is a reply to message #576839] |
Fri, 08 February 2013 09:05 |
_jum
Messages: 577 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...
|
|
|
|