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
After user is created, we can use dba_users table to check the user status.
We can use “connect username/password” to switch the user context during the connection.
unlock the user
Alter user USERNAME account unlock;
Because the password is set to expired during the account creation. we have been asked to change the password when login.
We can also force the password to expire by issuing
alter user USERNAME password expire;
user can not be drop if there is existing connection, we can not drop the user.
drop user PO;
Use below query to identify the user session and kill it.
select SID,SERIAL#,USERNAME,status,OSUSER,MACHINE,program from V$SESSION
use below SQL to kill the sessions,
alter system kill session 'SID,SERIAL#';
alter system kill session 'SID,SERIAL#' IMMEDIATE;
If user own the objects, we can not drop either.
Use below query to identify what object own by the user
select owner,object_name from dba_objects where owner='PO';
drop user USERNAME cascade;
Drop user with cascade would drop all the user’s own object as well..
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 ..