• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 264
  • Last Modified:

Delete username

I tried to delete username, but I got the following error:
-01752 can not delete from view without exactly one key-preserved table.
Redefine the view or delete from the underlying base tables.

Any suggestions in details.
0
lium1
Asked:
lium1
  • 6
  • 6
  • 2
  • +3
3 Solutions
 
slightwv (䄆 Netminder) Commented:
Are you wanting to 'drop' an oracle user?

drop user user_name;

If they own objects like tables: drop user user_name cascade;
0
 
lium1Author Commented:
I don't want to drop him, just delete.
0
 
slightwv (䄆 Netminder) Commented:
What do you mean by 'delete'?

If you mean remove them from the database, drop is what you want.
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
lium1Author Commented:
Drop is permanent and delete is not. What I meant is by deleting and not dropping cascade, I can save some user objects and I can reassign the user. Is there any syntax for deleting?
0
 
slightwv (䄆 Netminder) Commented:
I'm still not understanding the terms you are using.

What are you wanting to do by 'reassign the user'?
Why is a 'delete' not permanent?

Anyway:  I don't think you can do what you are wanting to do.

You can export some objects using Oracle utilities.  Then you can import them into another schema.

You can create tables in another schema with a simple CTAS.
0
 
lium1Author Commented:
I meant delete just delete user name from the list, but not his other objects. Correct me if I am wrong. That's why I want to use delete.
0
 
slightwv (䄆 Netminder) Commented:
From what 'list'?

You cannot remove a user from dba_users (or any of the views).  The user either exists or does not.

You cannot 'hide' them.
0
 
johnsoneSenior Oracle DBACommented:
I'm not exactly sure what you are trying to do either.

You can lock an account.  This would prevent anyone from being able to log into the account, but the objects would remain.  The syntax would be:

ALTER USER <uname> ACCOUNT LOCK;

I'm not sure that is exactly what you are looking for, but there is no "delete" of an account.  If the user is gone, so are the objects that the user owns.
0
 
Mark GeerlingsDatabase AdministratorCommented:
Oracle does not offer a way to "delete" a user from the database, but keep any/all tables, procedures, views etc. that were owned by that user.  Also, Oracle does not offer a way to rename a user account (at least not a supported way).

Please clarify exacly which list you want to "delete" a user from.
0
 
KlausBarthelsCommented:
I think, that you will not drop a user from database.
Do you need to delete a user from a table, that contains a field "User"?
In which system try you this operation?

Regards

Klaus
0
 
gajmpCommented:
As johnsone:said, LOCK will be solution for your problem. but no one can access those objects owned by the locked user still we unlock the user.
That mean, you may give access privilege to someone else, but if we lock the owner account then the person who got access privilege can't access those objects
0
 
lium1Author Commented:
From OEM, I deleted user and it went successfully. But, later I saw that user was still there. Any explanation to this? Beside, markqeer said that Oracle does not offer a way to "delete" a user from the database, then what in OEM has capability to delete user, any explanation to this too!
0
 
slightwv (䄆 Netminder) Commented:
What OEM screen were you on?
What version of OEM are you using?

A screenshot would help.

It is likely a bug in your version of OEM.
0
 
Mark GeerlingsDatabase AdministratorCommented:
The Oracle database offers a "drop user" command.  Sorry, I've never tried to "delete" a user in OEM, so I don't know exactly what that is intended to do.  (Maybe that "delete" is just the permission to use OEM, but not actually changing dataabse permissions outside of OEM?)

I disagree with the comment from gajmp, especially this statement: "no one can access those objects owned by the locked user [until] we unlock the user".  That is *NOT* true.  Yes, database user accounts can be locked, because Oracle also offers an "alter user [username] account lock;" command.  That will prevent the user from logging in.  But, if that user had previously created tables and granted permissions on those tables to other users, those other users certainly are NOT LOCKED OUT of the tables now.
0
 
lium1Author Commented:
I am on Oracle Database 11.2.0.2.0 Grid Infrastrurure. Under OEM, go tab Server ==> Security ==> User
0
 
slightwv (䄆 Netminder) Commented:
That is the same as 'drop user'.

If the user owns objects, you should get a warning.

I just confirmed this by create a user called BOB and creating a table.

Confirmation
 
User BOB still owns object(s) . Are you sure you want to remove user BOB and the object(s) together, using the CASCADE option?

0
 
lium1Author Commented:
Thanks guys.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 6
  • 6
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now