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.
lium1Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.