OraFAQ Forum: SQL & PL/SQL » ONLINE REDEFINITION

Home » SQL & PL/SQL » SQL & PL/SQL » ONLINE REDEFINITION

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

ONLINE REDEFINITION [message #290558]

Sat, 29 December 2007 22:50

faiz_hyd
Messages: 294
Registered: February 2006

Senior Member

Hi,
I am trying to do online redefinition for (partitioning )
a table and i used this statement

EXEC dbms_redefinition.can_redef_table('APP_OWNER','INVOICE');

ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_REDEFINITION' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
do we need to grant access to schema user to execute this Package.

Thanks

[Updated on: Sat, 29 December 2007 22:51]

Report message to a moderator

Re: ONLINE REDEFINITION [message #290559 is a reply to message #290558]

Sat, 29 December 2007 22:55

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>do we need to grant access to schema user to execute this Package.
Why don't/didn't you just try it yourself?

Report message to a moderator

Re: ONLINE REDEFINITION [message #290560 is a reply to message #290559]

Sat, 29 December 2007 23:02

faiz_hyd
Messages: 294
Registered: February 2006

Senior Member

Thanks

I don't have DBA access to grant.
have to ask DBA's so wanted to confirm before asking the DBA's.

Any way thanks for quick reply.

Thanks

[Updated on: Sat, 29 December 2007 23:04]

Report message to a moderator

Re: ONLINE REDEFINITION [message #290561 is a reply to message #290558]

Sat, 29 December 2007 23:08

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

who owns 'DBMS_REDEFINITION' ?
who is/was rtying to invoke 'DBMS_REDEFINITION' ?

>I don't have DBA access to grant.
Why do you NOT have your own Oracle DB on your own system so you can have DBA access?

[Updated on: Sat, 29 December 2007 23:09] by Moderator

Report message to a moderator

Re: ONLINE REDEFINITION [message #290564 is a reply to message #290561]

Sat, 29 December 2007 23:28

faiz_hyd
Messages: 294
Registered: February 2006

Senior Member

Hi,
>who owns 'DBMS_REDEFINITION' ?
who is/was rtying to invoke 'DBMS_REDEFINITION' ?
I think Sys owns it, Application Owner Schema was trying to invoke DBMS_REDEFINITION.

>Why do you NOT have your own Oracle DB on your own system so you can have DBA access?

I don't have local DB on my machine now, i was running on Development Server Database.

Thanks

Report message to a moderator

Re: ONLINE REDEFINITION [message #290579 is a reply to message #290560]

Sun, 30 December 2007 01:28

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

Senior Member
Account Moderator

Of course to be able to execute a package you must have the privilege to execute the package.
Isn't this obvious?

Why don't you directly ask your DBA?
Does he/she scare you?
Maybe you don't know how to speak to a DBA.
Never say him/her: "give me privilege to execute this package". This tends to turn the DBA against this.
Tell him/her exactly what you posted: "I have to online redefine a table, I tried to execute dbms_redefinition package, I got this error, maybe I don't have the privilege to do it, can I have it". There you will have a pretty good chance to get it.

Regards
Michel

Report message to a moderator

Re: ONLINE REDEFINITION [message #291010 is a reply to message #290579]

Wed, 02 January 2008 11:40

faiz_hyd
Messages: 294
Registered: February 2006

Senior Member

Thanks every body for suggestion,

Apart from execute grant on the package, we need some other access to perform ONLINE REDEFINITION
[code]
A user needs execute privilege on dbms_redefinition, alter any table privilege, create any table privilege, drop any table privilege, lock any table privilege and select any table privilege to effectively use this package.
[code]
I am able to compelte the task now after these grants.
Source is
http://www.mydatabasesupport.com/dbms-redefinition-package.html

Thanks

Report message to a moderator

Re: ONLINE REDEFINITION [message #291011 is a reply to message #291010]

Wed, 02 January 2008 11:45

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

Senior Member
Account Moderator

Why don't you use the official documentation instead of a lame copy?

Regards
Michel

Report message to a moderator

Previous Topic:	Which Method is Better Pl-Sql
Next Topic:	Instead of using Cursor is there any other option.

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Jul 12 02:08:16 CDT 2025