Sunday, December 9, 2012

0 Oracle User

Here is the example for creating the Oracle user.

create user PO
identified by oracle
default tablespace POTS1
temporary tablespace tempts
quota 10M ON POTS1
password expire
account lock
profile default;

 

After user is created, we can use dba_users table to check the user status.

image

Switch users

We can use “connect username/password” to switch the user context during the connection.

image

unlock the user

Alter user USERNAME account unlock;

image

Because the password is set to expired during the account creation. we have been asked to change the password when login.

image

We can also force the password to expire by issuing

alter user USERNAME password expire;

 

drop user

user can not be drop if there is existing connection, we can not drop the user.

drop user PO;

image

Use below query to identify the user session and kill it.

select SID,SERIAL#,USERNAME,status,OSUSER,MACHINE,program from V$SESSION
where USERNAME='PO';

image

use below SQL to kill the sessions,

alter system kill session 'SID,SERIAL#';
alter system kill session 'SID,SERIAL#' IMMEDIATE;

image

image

 

If user own the objects, we can not drop either.

image

Use below query to identify what object own by the user

select owner,object_name from dba_objects where owner='PO';

image

drop user USERNAME cascade;

Drop user with cascade would drop all the user’s own object as well..

image

Alternatively, instead of dropping user objects, we can transfer the objects to other user. Here is the how to but I am not yet try it .. 

Reference

0 comments:

Post a Comment

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates