1.How to check a specific user account details in a database
set lines 400 pages 400
col USERNAME for a20
col ACCOUNT_STATUS for a20
col PROFILE for a30
select USERNAME,ACCOUNT_STATUS,EXPIRY_DATE,LOCK_DATE,PROFILE from dba_users where USERNAME=’&username’;
2.Check active and inactive user count in a database
set lines 750 pages 9999
compute SUM of tot on report
compute SUM of active on report
compute SUM of inactive on report
col username for a50
select DECODE(username,NULL,’INTERNAL’,USERNAME) Username, count(*) TOT,
COUNT(DECODE(status,’ACTIVE’,STATUS)) ACTIVE, COUNT(DECODE(status,’INACTIVE’,STATUS)) INACTIVE from gv$session where status in (‘ACTIVE’,’INACTIVE’) group by username;
3.To check all the grants present in the user
set head off
set pagesize 0
set long 999999
set echo off
SELECT DBMS_METADATA.GET_DDL(‘USER’,upper(‘USER1’)) || ‘/’ DDL
FROM DUAL
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL(‘ROLE_GRANT’,upper(‘USER1’)) || ‘/’ DDL
FROM DUAL
where exists (select ‘x’ from dba_role_privs drp where drp.grantee=upper(‘USER1’))
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL(‘SYSTEM_GRANT’,upper(‘USER1’)) || ‘/’ DDL
FROM DUAL
where exists (select ‘x’ from dba_sys_privs drp where drp.grantee=upper(‘USER1’))
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL(‘OBJECT_GRANT’,upper(‘USER1’)) || ‘/’ DDL
FROM DUAL
where exists(select ‘x’ from dba_tab_privs drp where drp.grantee=upper(‘USER1’));
4.To reset the same password of a user in the database is password is expired
select name,password from sys.user$ where name=’USER1′;
alter user USER1 identified by values ‘XXXXXXXXXXXXXXXXXX’;
5. To unlock a user
select username,account_status from dba_users where username=’USER1′;
alter user USER1 account unlock;
6. To check if a user password is expired with expiry date
select username, account_status, expiry_date from dba_users where username=’USER1′;
7. To find a default tablespace name for a user in Oracle database
select username, account_status, default_tablespace from dba_users;
8. Create a user in oracle database
create user USER1 identified by password;
9. To change a user password in Oracle
alter user USER1 identified by password1;
10. To find user creation date in Oracle database
select username, created from dba_users where username=’USER1′;
