Home » SQL & PL/SQL » SQL & PL/SQL » Script prob after changing from Oracle 9i to 10g R2 ORA-02021 ORA-30041
Script prob after changing from Oracle 9i to 10g R2 ORA-02021 ORA-30041 [message #226429] Fri, 23 March 2007 12:20 Go to next message
t.summerfield
Messages: 39
Registered: March 2007
Location: UK
Member
Hello

I have recently formatted and setup one of the servers at work (windows server 2003) and have installed Oracle 10g R2 on it. It previously had Oracle 9i

My role in this is as the DBA and as such I have very little SQL experience!

I have a program I use to generate the DB script files and optionally build the databases. The problem I am having is when I check the build output of the scripts - they are riddled with errors; specifically:

ORA-02021: DDL operations are not allowed on a remote database

ORA-30041: Cannot grant quota on the tablespace



I think it is worth noting that these problems were non-existent when using Oracle 9i

In regards to the DDL operations not being allowed on a remote database; I connect with sys as sysdba so I don't think I am lacking the appropriate priviledges? Or is there anything I need to check in the enterprise manager?

And in regards to not being able to grant quota on the tablespace; I check the tablespaces in the enterprise manager and the temp tablespace is present and correctly named. Am I missing the point?

Looking around the web it seems Oracle 10g no longer supports quotas on temp tablespaces - is this the case? And if so is there a simple solution to my problem, or am I going to have to get the developers to change the scripts?

I appreciate these are prob two unrelated problems, however both are giving me grief and I am unable to find a solution. So any suggestions/help would be greatly appreciated.

Feel free to ask questions (I am fairly new to this, and prob could have made things more clear!)

Thanks in advance

Toby
Re: Script prob after changing from Oracle 9i to 10g R2 ORA-02021 ORA-30041 [message #226434 is a reply to message #226429] Fri, 23 March 2007 13:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
  1. You cannot execute a DDL on a remote database, nobody can, this is a fact. However you can call a remote procedure that executes a DDL, for instance dbms_utility.exec_ddl_statement.
  2. There is no quota on temp tablespace, they are useless, you don't need them. So you have no problem.

Regards
Michel

Re: Script prob after changing from Oracle 9i to 10g R2 ORA-02021 ORA-30041 [message #226676 is a reply to message #226429] Mon, 26 March 2007 03:10 Go to previous messageGo to next message
t.summerfield
Messages: 39
Registered: March 2007
Location: UK
Member
Thanks for the reply Michael.

So why was I not getting the DDL error when I was using Oracle 9i?

Just to reiterate, it is a problem with the sql files rather than the Oracle 10g configuration?

BTW it is the Synonyms that are producing the errors. An example is:

CREATE OR REPLACE SYNONYM &HKCONTROLDB_Schema_Name..KnowledgeGene FOR &HKDB_Schema_Name..KnowledgeGene@&db_name;
GRANT SELECT ON &HKCONTROLDB_Schema_Name..KnowledgeGene TO &HKCONTROLDB_Schema_Name;
COMMIT;


Thanks
Toby



Re: Script prob after changing from Oracle 9i to 10g R2 ORA-02021 ORA-30041 [message #226680 is a reply to message #226676] Mon, 26 March 2007 03:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

The create synonym can't generate the error you showed as it creates an object in the local database (unless you have an @ in your variable).
But your grant statement will generate it as it tries to grant a privilege on a remote object which is not allowed.

Regards
Michel

Re: Script prob after changing from Oracle 9i to 10g R2 ORA-02021 ORA-30041 [message #226682 is a reply to message #226429] Mon, 26 March 2007 03:29 Go to previous messageGo to next message
t.summerfield
Messages: 39
Registered: March 2007
Location: UK
Member
As I said earlier I am just responsible for the setting up and testing of the Oracle/Database. SQL isn't my strong point Embarassed

Would it be possible to show me the changes which need to be made to the grant statement!?!?

Thanks Michel (soz bout the Michael)
Re: Script prob after changing from Oracle 9i to 10g R2 ORA-02021 ORA-30041 [message #226711 is a reply to message #226682] Mon, 26 March 2007 04:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

There is no change to make in your grant statement.
The privileges on the remote object are given by the user you connect with through the database link.
If the user defined in "connect with" of your database link definition has the privilege to select the remote table then using this database link you also have this privilege.
Remove the grant statement it is useless and misleading.

Regards
Michel
Re: Script prob after changing from Oracle 9i to 10g R2 ORA-02021 ORA-30041 [message #227102 is a reply to message #226429] Tue, 27 March 2007 06:07 Go to previous messageGo to next message
t.summerfield
Messages: 39
Registered: March 2007
Location: UK
Member
It is worth metioning that the synonym is for a separate schema located in the same DB

I connect as sysdba. I know the script works as I have used it loads of times in Oracle 9i without problem.
Re: Script prob after changing from Oracle 9i to 10g R2 ORA-02021 ORA-30041 [message #227108 is a reply to message #227102] Tue, 27 March 2007 06:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/
- Never ever use SYS but for maintenance purpose (startup, shutdown, backup, recover)
- SYS is special
- SYS is Oracle proprietary (try to open a SR/TAR starting with "i did that with SYS" and you'll see the immediate answer)
- SYS does not act like any other user
- When you use SYS Oracle deactivates some code path and activates others
- Whatever you do with SYS will neither validate nor invalidate the same thing with any other user.

NEVER EVER use SYS for anything that can be done by another user.
Use SYS ONLY for something that can't be done by someone else.


2/
This should never work in any version unless:
- you miss something
- there is a bug

Regards
Michel

Re: Script prob after changing from Oracle 9i to 10g R2 ORA-02021 ORA-30041 [message #227155 is a reply to message #226429] Tue, 27 March 2007 07:58 Go to previous messageGo to next message
t.summerfield
Messages: 39
Registered: March 2007
Location: UK
Member
Fixed it.

Just had to remove the "@&db_name" from the CREATE line.

It now builds no problem

Thanks for the help Michel
Re: Script prob after changing from Oracle 9i to 10g R2 ORA-02021 ORA-30041 [message #227198 is a reply to message #227155] Tue, 27 March 2007 09:04 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

But this is not the same script.
You now create a synonym for a local object instead of a remote one.
But if this is OK for you, it's fine for me.

Regards
Michel
Previous Topic: ORA-01033: ORACLE intialization or shutdown in progress
Next Topic: Help With Outer Joins
Goto Forum:
  


Current Time: Fri Dec 09 02:03:38 CST 2016

Total time taken to generate the page: 0.08498 seconds