Home » SQL & PL/SQL » SQL & PL/SQL » ONLINE REDEFINITION
ONLINE REDEFINITION [message #290558] Sat, 29 December 2007 22:50 Go to next message
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 Go to previous messageGo to next message
BlackSwan
Messages: 25047
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?
Re: ONLINE REDEFINITION [message #290560 is a reply to message #290559] Sat, 29 December 2007 23:02 Go to previous messageGo to next message
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 Go to previous messageGo to next message
BlackSwan
Messages: 25047
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 Go to previous messageGo to next message
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
Re: ONLINE REDEFINITION [message #290579 is a reply to message #290560] Sun, 30 December 2007 01:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: ONLINE REDEFINITION [message #291010 is a reply to message #290579] Wed, 02 January 2008 11:40 Go to previous messageGo to next message
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

Re: ONLINE REDEFINITION [message #291011 is a reply to message #291010] Wed, 02 January 2008 11:45 Go to previous message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why don't you use the official documentation instead of a lame copy?

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


Current Time: Fri Dec 09 09:22:35 CST 2016

Total time taken to generate the page: 0.06309 seconds