Home » SQL & PL/SQL » SQL & PL/SQL » Unable to drop a column in table. (Oracle 12c, Windows)
Unable to drop a column in table. [message #624387] Mon, 22 September 2014 07:58 Go to next message
nmdivya@gmail.com
Messages: 12
Registered: September 2014
Junior Member
Hi ,

I'm trying to drop a column from the table which I created.(I'm learning oracle now. Not official use).

SQL> alter table employee drop column emp_dept;
alter table employee drop column emp_dept
*
ERROR at line 1:
ORA-12988: cannot drop column from table owned by SYS


Can anyone help me to execute this query.

Thanks,
Snowy

Report message to a moderator

Re: Unable to drop a column in table. [message #624388 is a reply to message #624387] Mon, 22 September 2014 08:18 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
[oracle@localhost ~]$ oerr ora 12988
12988, 00000, "cannot drop column from table owned by SYS"
// *Cause: An attempt was made to drop a column from a system table.
// *Action: This action is not allowed


you should not be using SYS for any daily activity.

Report message to a moderator

Re: Unable to drop a column in table. [message #624389 is a reply to message #624388] Mon, 22 September 2014 08:22 Go to previous messageGo to next message
nmdivya@gmail.com
Messages: 12
Registered: September 2014
Junior Member
How can I change this SYS setting?

Report message to a moderator

Re: Unable to drop a column in table. [message #624390 is a reply to message #624389] Mon, 22 September 2014 08:26 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
nmdivya@gmail.com wrote on Mon, 22 September 2014 06:22
How can I change this SYS setting?


login as different user who is not SYS.

Report message to a moderator

Re: Unable to drop a column in table. [message #624393 is a reply to message #624387] Mon, 22 September 2014 08:44 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Read SYS is special.

Report message to a moderator

Re: Unable to drop a column in table. [message #624395 is a reply to message #624389] Mon, 22 September 2014 09:14 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
it appears you created your sample tables while connected as use SYS - probably by starting sqlplus 'as sysdba'.
Sound familiar?

To clean up, you need to identify all the tables that got created as part of that sample setup, then connect as sys (AS SYSDBA) and DROP them.
After you have done that, you should review very carefully the instructions for setting up your sample schema. Most likely they will involve connecting as some non-privileged user, like SCOTT, and doing your work from there.

Report message to a moderator

Re: Unable to drop a column in table. [message #624404 is a reply to message #624395] Mon, 22 September 2014 10:22 Go to previous messageGo to next message
gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK
Senior Member
A query like this should help you with Ed's suggestion:

SQL> SELECT OWNER, OBJECT_NAME, DBO.CREATED
     FROM DBA_OBJECTS DBO, V$DATABASE V
     WHERE TRUNC(DBO.CREATED) > TRUNC(V.CREATED);


HTH
-g

Report message to a moderator

Re: Unable to drop a column in table. [message #624410 is a reply to message #624404] Mon, 22 September 2014 10:35 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
A query like this should help you with Ed's suggestion:


I think SYS is missing somewhere.
And you have to pray that no objects have been created the same day than the database was.

Report message to a moderator

Re: Unable to drop a column in table. [message #624413 is a reply to message #624410] Mon, 22 September 2014 10:38 Go to previous messageGo to next message
gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK
Senior Member
I agree, Michel. This is why I used the greater than sign and not ">=". Far better to leave existing non-SYS objects than delete required ones. Anyway, it was meant to be a clue for the OP to work out the rest. It depends how far he wants to go to unpick his mess.

Report message to a moderator

Re: Unable to drop a column in table. [message #624417 is a reply to message #624413] Mon, 22 September 2014 10:49 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
The mess could be worse for amateurs in 12c release, and OP is on 12c.




Edit : Perhaps, I should be more clear with my comment. OP has not told us about the details like which SYS account he is referring to, CDB or PDB.
		
		
		
[Updated on: Mon, 22 September 2014 10:51]
Report message to a moderator





	
		
	






	
		

		

			Re: Unable to drop a column in table. [message #624427 is a reply to message #624417]
			Mon, 22 September 2014 12:13 Go to previous messageGo to next message
		

		

			
				

				

				
					
						
						EdStevens
						
Messages: 1376
Registered: September 2013 
						
							
					
Senior Member

					
		

		

			




	
		Lalit Kumar B wrote on Mon, 22 September 2014 10:49
The mess could be worse for amateurs in 12c release, and OP is on 12c.




I must be going blind, but I see no evidence of what version the OP is using.


		
		
		
Report message to a moderator





	
		
	






	
		

		

			Re: Unable to drop a column in table. [message #624428 is a reply to message #624427]
			Mon, 22 September 2014 12:16 Go to previous messageGo to next message
		

		

			
				

				

				
					
						
						Lalit Kumar B
						
Messages: 3174
Registered: May 2013 
Location: World Wide on the Web
						
							
					
Senior Member

					
		

		

			




	
		EdStevens wrote on Mon, 22 September 2014 22:43
Lalit Kumar B wrote on Mon, 22 September 2014 10:49
The mess could be worse for amateurs in 12c release, and OP is on 12c.




I must be going blind, but I see no evidence of what version the OP is using.






I see it along with topic title(on top), "Oracle 12c, windows".


		
		
		
Report message to a moderator





	
		
	






	
		

		

			Re: Unable to drop a column in table. [message #624433 is a reply to message #624428]
			Mon, 22 September 2014 13:26 Go to previous message
		

		

			
				

				

				
					
						
						EdStevens
						
Messages: 1376
Registered: September 2013 
						
							
					
Senior Member

					
		

		

			




	
		Lalit Kumar B wrote on Mon, 22 September 2014 12:16




I see it along with topic title(on top), "Oracle 12c, windows".






My bad.  Once I get into the meat of a thread, I tend to overlook the subject line itself.



 Embarassed 
		
		
		
Report message to a moderator





	
		
	












	

	Previous Topic:
	Please solve my issue


	

	Next Topic:
	Data in AM/PM





Goto Forum:


	
	
	  
	





[ Syndicate this forum (XML) ] [ RSS ] 


Current Time: Fri Apr 26 05:16:10 CDT 2024









	.::
	Forum Home ::
	Blogger Home ::
	Wiki Home ::
	Contact ::
	Privacy
	::.