Home » SQL & PL/SQL » SQL & PL/SQL » Alter syntax
Alter syntax [message #319168] Fri, 09 May 2008 04:29 Go to next message
balaji23_d
Messages: 123
Registered: February 2007
Senior Member
Hi,

I need to give the privilage to alter the table

whats the syntax to give the access to alter the table(IE.to add the column in a table)
Re: Alter syntax [message #319175 is a reply to message #319168] Fri, 09 May 2008 04:51 Go to previous messageGo to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
grant dba
Re: Alter syntax [message #319177 is a reply to message #319175] Fri, 09 May 2008 04:54 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Hmmm....
On consideration, I'd have to go with not granting DBA privs, certainly without checking if this was a Prod or Dev database.

To grant a use r the priv to alter any table,
GRANT ALTER ANY TABLE TO <user>

Users automatically have the right to alter a table in their own schema if they have CREATE TABLE.
Re: Alter syntax [message #319179 is a reply to message #319175] Fri, 09 May 2008 04:57 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Huh, DBA is like using a sledge hammer to deal with a boring fly.

Alter table:
Quote:
Prerequisites

The table must be in your own schema, or you must have ALTER object privilege on the table, or you must have ALTER ANY TABLE system privilege.


[EDIT]

Add to the TO DO list:
a) learn to type faster
b) memorize all Oracle documentation URLs (so that you wouldn't have to search for them)

[Updated on: Fri, 09 May 2008 04:58]

Report message to a moderator

Re: Alter syntax [message #319223 is a reply to message #319168] Fri, 09 May 2008 06:50 Go to previous messageGo to next message
balaji23_d
Messages: 123
Registered: February 2007
Senior Member
HI,

Actual Problem is

we have to tried to add the new column in one table. when i tried, its giving me the exception like "table or view does not exist"

But we got in which oracle objects to belongs tht from "all_tables". I have confirmed its a table object type...

But when i am tried to add the new column in tht table to gives me that excception

Give me that solution to avoid this problem
Re: Alter syntax [message #319226 is a reply to message #319168] Fri, 09 May 2008 06:53 Go to previous messageGo to next message
abhishekswamy
Messages: 11
Registered: May 2008
Junior Member

The table must be in your own schema, or you must have ALTER object privilege on the table, or you must have ALTER ANY TABLE system privilege.
hope it helps
Re: Alter syntax [message #319227 is a reply to message #319223] Fri, 09 May 2008 06:54 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If the table is onwed by someone other than the user who is trying to alter it, you wil need to specify the table owner in the ALTER statement:
ALTER TABLE <owner>.<table_name>...
Re: Alter syntax [message #319229 is a reply to message #319168] Fri, 09 May 2008 07:07 Go to previous messageGo to next message
balaji23_d
Messages: 123
Registered: February 2007
Senior Member
Fine... But I got one error like "Resource busy and acquire with NOWAIT"..what can i do
Re: Alter syntax [message #319230 is a reply to message #319229] Fri, 09 May 2008 07:10 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
GOOGLE!
Re: Alter syntax [message #319265 is a reply to message #319229] Fri, 09 May 2008 09:21 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The table is in use by a transaction that has not finished and either been committed or rolled back.
Previous Topic: why to use purge
Next Topic: Small Help
Goto Forum:
  


Current Time: Sun Dec 04 16:39:48 CST 2016

Total time taken to generate the page: 0.05966 seconds