Managed User account in Oracle

Managed User account in Oracle

Check User table Quota

SELECT * FROM DBA_TS_QUOTAS

Check the USER present in Oracle database

SELECT USERNAME FROM DBA_USERS;

Check session info of connection

SELECT * FROM V$SESSION_CONNECT_INFO;

SELECT * FROM V$SESSION;

Check the resource limit

SELECT * FROM USER_PASSWORD_LIMITS;

RESOURCE_NAME LIMIT
-------------------------------- ------------
FAILED_LOGIN_ATTEMPTS 10
PASSWORD_LIFE_TIME 180
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_VERIFY_FUNCTION NULL
PASSWORD_LOCK_TIME 1
PASSWORD_GRACE_TIME 7

Create user commands in Oracle with different options

CREATE USER user1 IDENTIFIED BY new_password
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA 100M ON users
QUOTA 50M ON data01
QUOTA 100M ON temp
PROFILE sales
ACCOUNT UNLOCK
PASSWORD EXPIRE;

Note:
USER1 is having two tablespace one default USERS and other temporary TEMP. Set quota on 3 tablespace.
USER1 is assigned profile SALES and account is unlocked and password expire means change password by user on first login.

Alter User commands in Oracle
Change exisiting setting of user by using alter command.

ALTER USER user1 IDENTIFIED BY new_password
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA 100M on users
QUOTA 100M on temp
PROFILE hr;

Drop user command

-- It worked when user1 is empty means no object
DROP USER user1;

-- if user1 having objects then use CASCADE option to drop the user and its objects
DROP USER user1 CASCADE;

Note: If you tried to drop without empty the HR Schema then error ORA-01922 occurs as show below:

SQL> drop user hr;
drop user hr
*
ERROR at line 1:
ORA-01922: CASCADE must be specified to drop 'HR'

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.