Oracle user Info ....
set linesize 200 verify off
alter session set nls_timestamp_tz_format = 'dd-mon-yyyy hh24:mi:ss';
alter session set nls_date_format = 'dd-mon-yyyy hh24:mi:ss';
col username format a20
col account_status format a16
col default_tablespace format a15
col temporary_tablespace format a15
col profile format a15
col initial_rsrc_consumer_group for a23
SELECT username,
account_status,
lock_date,
expiry_date,
default_tablespace,
temporary_tablespace,
created,
profile,
initial_rsrc_consumer_group,
editions_enabled,
authentication_type
from dba_users
where username like upper('%&username%')
ORDER BY username;
USERNAME ACCOUNT_STATUS LOCK_DATE EXPIRY_DATE DEFAULT_TABLESP TEMPORARY_TABLE CREATED PROFILE INITIAL_RSRC_CONSUMER_G E AUTHENTI
-------------------- ---------------- -------------------- -------------------- --------------- --------------- -------------------- --------------- ----------------------- - --------
ANONYMOUS EXPIRED & LOCKED 26-jan-2017 15:27:28 26-jan-2017 15:27:28 SYSAUX TEMP 26-jan-2017 14:27:18 DEFAULT DEFAULT_CONSUMER_GROUP N PASSWORD
ANUJ OPEN 19-sep-2018 14:51:20 USERS TEMP 09-jan-2018 15:34:41 DEFAULT DEFAULT_CONSUMER_GROUP N PASSWORD
ANUJ1 OPEN 11-aug-2018 06:56:36 USERS TEMP 09-jan-2018 15:48:40 DEFAULT DEFAULT_CONSUMER_GROUP N PASSWORD
ANUJ10 OPEN 11-jul-2018 13:13:23 USERS TEMP 12-jan-2018 13:13:23 DEFAULT DEFAULT_CONSUMER_GROUP N PASSWORD
ANUJ3 OPEN 08-jul-2018 15:49:55 USERS TEMP 09-jan-2018 15:49:55 DEFAULT DEFAULT_CONSUMER_GROUP N PASSWORD
ANUJ4 OPEN 08-jul-2018 15:54:59 USERS TEMP 09-jan-2018 15:54:59 DEFAULT DEFAULT_CONSUMER_GROUP N PASSWORD
with server name !!!
set linesize 300 verify off
alter session set nls_timestamp_tz_format = 'dd-mon-yyyy hh24:mi:ss';
alter session set nls_date_format = 'dd-mon-yyyy hh24:mi:ss';
col username for a20
col account_status for a16
col default_tablespace for a15
col temporary_tablespace for a15
col profile for a15
col initial_rsrc_consumer_group for a23
col host for a38
col DATABASE_NAME for a14
SELECT
to_char(sysdate,'dd-mm-yyyy hh24:mi') sdate,SYS_CONTEXT('USERENV','HOST') host,SYS_CONTEXT('USERENV','DB_NAME') database_name,
username,
account_status,
lock_date,
expiry_date,
default_tablespace,
temporary_tablespace,
created,
profile,
initial_rsrc_consumer_group,
editions_enabled,
authentication_type
from dba_users
where 1=1
--and username like upper('%&username%')
ORDER BY username;
---with last change date
set linesize 300 pagesize 300 verify off
alter session set nls_timestamp_tz_format = 'dd-mon-yyyy hh24:mi:ss';
alter session set nls_date_format = 'dd-mon-yyyy hh24:mi:ss';
col username format a20
col account_status format a16
col default_tablespace format a15
col temporary_tablespace format a15
col profile format a15
col initial_rsrc_consumer_group for a23
col host for a38
col DATABASE_NAME for a14
SELECT
to_char(sysdate,'dd-mm-yyyy hh24:mi') sdate,SYS_CONTEXT('USERENV','HOST') host,SYS_CONTEXT('USERENV','DB_NAME') database_name,
d.username,
d.account_status,
d.created,
d.lock_date,
u.ptime "Last Changed",
d.expiry_date,
d.default_tablespace,
d.temporary_tablespace,
d.profile,
d.initial_rsrc_consumer_group,
d.editions_enabled,
d.authentication_type
from dba_users d,user$ u
where 1=1
--and d.username like upper('%&username%')
and d.username = u.name
ORDER BY username;
set linesize 200 pagesize 300 verify off
alter session set nls_timestamp_tz_format = 'dd-mon-yyyy hh24:mi:ss';
alter session set nls_date_format = 'dd-mon-yyyy hh24:mi:ss';
col username for a20
col account_status for a16
col default_tablespace for a15
col temporary_tablespace for a15
col profile for a15
col initial_rsrc_consumer_group for a23
SELECT username,
account_status,
lock_date,
expiry_date,
default_tablespace,
temporary_tablespace,
created,
profile,
initial_rsrc_consumer_group,
editions_enabled,
authentication_type
from dba_users
where 1=1
-- and username like upper('%username%')
and ACCOUNT_STATUS='EXPIRED(GRACE)'ORDER BY username;
===
SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON
BEGIN
DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true);
DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY', true);
END;
/
select
dbms_metadata.get_ddl('VIEW', 'CDB_USERS', 'SYS')
from
dual;
CREATE OR REPLACE FORCE NONEDITIONABLE VIEW "SYS"."CDB_USERS" CONTAINER_DATA
("USERNAME", "USER_ID", "PASSWORD", "ACCOUNT_STATUS", "LOCK_DATE", "EXPIRY_DATE", "DEFAULT_TABLESPACE", "TEMPORARY_TABLESPACE", "LOCAL_TEMP_TABLESPACE", "CREATED", "PROFILE", "INITIAL_RSRC_CONSUMER_GROUP", "EXTERNAL_NAME", "PASSWORD_VERSIONS", "EDITIONS_ENABLED", "AUTHENTICATION_TYPE", "PROXY_ONLY_CONNECT", "COMMON", "LAST_LOGIN", "ORACLE_MAINTAINED", "INHERITED", "DEFAULT_COLLATION", "IMPLICIT", "ALL_SHARD", "CON_ID", "CON$NAME", "CDB$NAME") AS
SELECT k."USERNAME",k."USER_ID",k."PASSWORD",k."ACCOUNT_STATUS",k."LOCK_DATE",k."EXPIRY_DATE",k."DEFAULT_TABLESPACE",k."TEMPORARY_TABLESPACE",k."LOCAL_TEMP_TABLESPACE",k."CREATED",k."PROFILE",k."INITIAL_RSRC_CONSUMER_GROUP",k."EXTERNAL_NAME",k."PASSWORD_VERSIONS",k."EDITIONS_ENABLED",k."AUTHENTICATION_TYPE",k."PROXY_ONLY_CONNECT",k."COMMON",k."LAST_LOGIN",k."ORACLE_MAINTAINED",k."INHERITED",k."DEFAULT_COLLATION",k."IMPLICIT",k."ALL_SHARD",k."CON_ID", k.CON$NAME, k.CDB$NAME
FROM CONTAINERS("SYS"."DBA_USERS") k;
--- cdb_users users
set linesize 300 pagesize 300 verify off
alter session set nls_timestamp_tz_format = 'dd-mon-yyyy hh24:mi:ss';
alter session set nls_date_format = 'dd-mon-yyyy hh24:mi:ss';
col username format a20
col account_status format a16
col default_tablespace format a15
col temporary_tablespace format a15
col profile format a15
col initial_rsrc_consumer_group for a23
col host for a38
col DATABASE_NAME for a14
SELECT distinct
to_char(sysdate,'dd-mm-yyyy hh24:mi') sdate,SYS_CONTEXT('USERENV','HOST') host,SYS_CONTEXT('USERENV','DB_NAME') database_name,
d.CON_ID,
d.username,
d.account_status,
d.created,
d.lock_date,
u.ptime "Last Changed",
d.expiry_date,
d.default_tablespace,
d.temporary_tablespace,
d.profile,
d.initial_rsrc_consumer_group,
d.editions_enabled,
d.authentication_type,
COMMON,
INHERITED
from cdb_users d,user$ u
where 1=1
--and d.username like upper('%&username%')
and d.username = u.name
and INHERITED!='YES'
ORDER BY username;
===========
alter session set nls_timestamp_tz_format = 'dd-mon-yyyy hh24:mi:ss';
alter session set nls_date_format = 'dd-mon-yyyy hh24:mi:ss';
set linesize 300
col username for a20
col profile for a15
col last_login for a25
select username,default_tablespace,temporary_tablespace,profile,account_status,created,last_login from dba_users where username like upper('%&1%');
USERNAME DEFAULT_TABLESP TEMPORARY_TABLE PROFILE ACCOUNT_STATUS CREATED LAST_LOGIN
-------------------- --------------- --------------- --------------- ---------------- -------------------- -------------------------
SYS SYSTEM TEMP DEFAULT OPEN 26-jan-2017 13:53:25
SYSTEM SYSTEM TEMP DEFAULT EXPIRED(GRACE) 26-jan-2017 13:53:26 09-feb-2018 16:32:19
set pagesize 200 lines 200
col ownr format a20 justify c heading 'Owner'
col name format a20 justify c heading 'Tablespace' trunc
col qota format a12 justify c heading 'Quota (KB)'
col used format 999,999,990 justify c heading 'Used (KB)'
set colsep '|'
select
username ownr,
tablespace_name name,
decode(greatest(max_bytes, -1),
-1, 'UNLIMITED',
to_char(max_bytes/1024, '999,999,990')
) qota,
bytes/1024 used
from dba_ts_quotas
where ( max_bytes!=0 or bytes!=0)
and username like upper('%&username%')
order by 1,2
/
Owner | Tablespace | Quota (KB) | Used (KB)
--------------------|--------------------|------------|------------
ANUJ |TEST_DATA |UNLIMITED | 0
ANUJ |TEST_DATA_IND |UNLIMITED | 0
ANUJ1 |TEST_DATA |UNLIMITED | 0
ANUJ1 |TEST_DATA_IND |UNLIMITED | 0
ANUJ10 |TEST_DATA |UNLIMITED | 0
ANUJ10 |TEST_DATA_IND |UNLIMITED | 0
ANUJ3 |TEST_DATA |UNLIMITED | 0
ANUJ3 |TEST_DATA_IND |UNLIMITED | 0
ANUJ4 |TEST_DATA |UNLIMITED | 0
ANUJ4 |TEST_DATA_IND |UNLIMITED | 0
10 rows selected.
set linesize 300 pagesize 300
alter session set nls_timestamp_tz_format = 'dd-mon-yyyy hh24:mi:ss';
alter session set nls_date_format = 'dd-mon-yyyy hh24:mi:ss';
set pagesize 66
ttitle skip 2 center 'USRS - Database Users' skip 2
column username format A25
column user_id format 99999999999 heading "ID"
column default_tablespace format A12 trunc heading "Default|Tablespace"
column temporary_tablespace format A12 trunc heading "Temporary|Tablespace"
column profile format A19 trunc heading "Profile"
select username, user_id, default_tablespace, temporary_tablespace, profile,created,LAST_LOGIN from dba_users
where 1=1
and user_id <1000000
;
Oracle 12c
set linesize 300 pagesize 300
alter session set nls_timestamp_tz_format = 'dd-mon-yyyy hh24:mi:ss';
alter session set nls_date_format = 'dd-mon-yyyy hh24:mi:ss';
set pagesize 66
ttitle skip 2 center 'USRS - Database Users' skip 2
col COMMON format a10
col username format A25
col user_id format 99999999999 heading "ID"
col default_tablespace format A12 trunc heading "Default|Tablespace"
col temporary_tablespace format A12 trunc heading "Temporary|Tablespace"
col profile format A19 trunc heading "Profile"
col LAST_LOGIN format a23
select username,COMMON, user_id, default_tablespace, temporary_tablespace, profile,created,LAST_LOGIN from dba_users
where 1=1
and user_id <1000000
;
col username format a23 heading 'Username' justify c
col role format a30 heading 'Role (admin,grant)' justify c
col dts format a12 heading 'Default|Tablespace' justify c
col tts format a12 heading 'Temporary|Tablespace' justify c
col prof format a30 heading 'Profile' justify c
break on username on role on dts on tts
select
username,
default_tablespace dts,
temporary_tablespace tts,
profile prof,
granted_role || '-' || decode(admin_option,'YES','Y','N') || decode(granted_role,'YES','Y','N') role,ACCOUNT_STATUS
from dba_users, dba_role_privs
where dba_users.username = dba_role_privs.grantee
and username != 'PUBLIC'
order by 1,2,3,4
/
set pagesize 300
col USERS for a20
col GRANTED_ROLE for a25
SELECT MAX(level_deep) level_deep, grantee users, granted_role
FROM
( SELECT DISTINCT level level_deep, grantee, granted_role
FROM dba_role_privs
-- START WITH granted_role ='DBA'
CONNECT BY prior grantee=granted_role
)
WHERE grantee IN (SELECT username FROM dba_users
where 1=1
and NOT regexp_like(username,'SYS|OUTLN|DBSNMP|ORACLE_OCM')
and username='ANUJ' ------ <<<<
)
GROUP BY grantee, granted_role
ORDER BY 1,2
;
LEVEL_DEEP|USERS |GRANTED_ROLE
----------|--------------------|-------------------------
5|ANUJ |DBA
col username format a23 heading 'Username' justify c
col role format a30 heading 'Role (admin,grant)' justify c
col dts format a12 heading 'Default|Tablespace' justify c
col tts format a12 heading 'Temporary|Tablespace' justify c
col prof format a30 heading 'Profile' justify c
break on username on role on dts on tts
select
username,
default_tablespace dts,
temporary_tablespace tts,
profile prof,
granted_role || '-' || decode(admin_option,'YES','Y','N') || decode(granted_role,'YES','Y','N') role,ACCOUNT_STATUS
from dba_users, dba_role_privs
where dba_users.username = dba_role_privs.grantee
and username != 'PUBLIC'
order by 1,2,3,4
/
Username Tablespace Tablespace Profile Role (admin,grant) ACCOUNT_STATUS
----------------------- ------------ ------------ ------------------------------ ------------------------------ -----------------
ABC USERS TEMP DEFAULT CONNECT-NN OPEN
USERS TEMP DEFAULT RESOURCE-NN OPEN
ANUJ USERS TEMP DEFAULT CONNECT-NN OPEN
USERS TEMP DEFAULT DBA-NN OPEN
USERS TEMP DEFAULT RESOURCE-NN OPEN
ANUJREP USERS TEMP DEFAULT CONNECT-NN OPEN
USERS TEMP DEFAULT RESOURCE-NN OPEN
ANUJTEST ANUJTEST TEMP DEFAULT CONNECT-NN OPEN
ANUJTEST TEMP DEFAULT RESOURCE-NN OPEN
APEX_030200 SYSAUX TEMP DEFAULT CONNECT-YN EXPIRED & LOCKED
SYSAUX TEMP DEFAULT RESOURCE-YN EXPIRED & LOCKED
-- Passwd file info
set echo off feedback 6 heading on linesize 200 pagesize 300 termout on timing off trimout on trimspool on verify off
clear columns clear breaks clear computes
col username format a30 head 'username'
col account_status format a17 head 'status'
col expiry_date head 'expire date'
col default_tablespace format a28 head 'default tablespace'
col temporary_tablespace format a15 head 'temp tablespace'
col created head 'created on'
col profile format a20 head 'profile'
col sysdba format a6 head 'sysdba'
col sysoper format a7 head 'sysoper'
select distinct
a.username username
, a.account_status account_status
, to_char(a.expiry_date, 'mm/dd/yyyy hh24:mi:ss') expiry_date
, a.default_tablespace default_tablespace
, a.temporary_tablespace temporary_tablespace
, to_char(a.created, 'mm/dd/yyyy hh24:mi:ss') created
, a.profile profile
, decode(p.sysdba,'true', 'true','') sysdba
, decode(p.sysoper,'true','true','') sysoper
from dba_users a , v$pwfile_users p
where p.username (+) = a.username
order by username
/
==
alter session set nls_date_format='dd-mm-yyyy hh24:mi';
col username for a30
col account_status for a20
col profile for a25
col expiry_date for a30
col lock_date for a30
set lines 200 pages 200
select username,account_status,profile,expiry_date,lock_date from dba_users order by 1;
alter session set nls_date_format='dd-mm-yyyy hh24:mi';
set linesize 300 pagesize 300
col USERNAME for a20
col PASSWORD_LIFE_TIME for a20
col PROFILE for a20
select u.username
, u.account_status
, u.lock_date
, u.expiry_date
, u.profile
, p1.limit as password_life_time
from dba_users u
, ( select * from dba_profiles where resource_name like 'PASSWORD_LIFE_TIME' ) p1
where u.profile = p1.profile
order by 1
;
=========
define username='SYS'
set linesize 200 verify off
alter session set nls_timestamp_tz_format = 'dd-mon-yyyy hh24:mi:ss';
alter session set nls_date_format = 'dd-mon-yyyy hh24:mi:ss';
col username format a20
col account_status format a16
col default_tablespace format a15
col temporary_tablespace format a15
col profile format a15
col initial_rsrc_consumer_group for a23
SELECT username,
account_status,
lock_date,
expiry_date,
default_tablespace,
temporary_tablespace,
created,
profile,
initial_rsrc_consumer_group,
editions_enabled,
authentication_type
from dba_users
where username like upper('%&username')
ORDER BY username;
col LIMIT for a20
SELECT PROFILE, RESOURCE_NAME, RESOURCE_TYPE, LIMIT
from dba_profiles
where PROFILE in ('DEFAULT')
and resource_name='PASSWORD_LIFE_TIME'
order by RESOURCE_NAME;
col NAME for a20
select NAME, TO_CHAR(EXPTIME, 'dd-mm-YYYY HH24:MI:SS') as "EXPTIME"
from SYS.USER$
where NAME in ('SYS');
===
After Changing SYS Password, DBA_USERS.EXPIRY_DATE Not Updated For SYS User (Doc ID 2518310.1)
col username for a20
col authentication_type for a20
SELECT username,
account_status,
authentication_type
FROM dba_users
WHERE 1=1
and username='SYS'
-- and ROWNUM < 10
;
define 1='enable_ptime_update_for_sys'
set linesize 300 pagesize 300
col parameter for a30
col session for a28
col instance for a12
col "Session Value" for a15
col "Instance Value" for a15
col IS_SESSION_MODIFIABLE for a25
col IS_SYSTEM_MODIFIABLE for a25
col description for a70
col comment for a30
col "Default Value" for a15
SELECT a.ksppinm "Parameter",
b.KSPPSTDF "Default Value",
b.ksppstvl "Session Value",
c.ksppstvl "Instance Value",
decode(bitand(a.ksppiflg/256,1),1,'TRUE','FALSE') IS_SESSION_MODIFIABLE,
decode(bitand(a.ksppiflg/65536,3),1,'IMMEDIATE',2,'DEFERRED',3,'IMMEDIATE','FALSE') IS_SYSTEM_MODIFIABLE
FROM x$ksppi a,
x$ksppcv b,
x$ksppsv c
WHERE a.indx = b.indx
AND a.indx = c.indx
AND a.ksppinm LIKE '/_%' escape '/'
and upper(a.ksppinm) like upper('%&1%')
/
Parameter Default Value Session Value Instance Value IS_SESSION_MODIFIABLE IS_SYSTEM_MODIFIABLE
------------------------------ --------------- --------------- --------------- ------------------------- -------------------------
_enable_ptime_update_for_sys TRUE FALSE FALSE TRUE IMMEDIATE
set linesize 300 pagesize 300
col PROFILE for a20
col LIMIT for a20
col COMMON for a10
col INHERITED for a10
col IMPLICIT for a10
select * from dba_profiles where PROFILE='DEFAULT';
=====================
set linesize 300
col FILE_NAME for a70
col IS_ASM for a10
select * from V$PASSWORDFILE_INFO;
FILE_NAME FORMAT IS_ASM CON_ID
---------------------------------------------------------------------- ------ ---------- ----------
/u01/app/oracle/product/19.3.0/db_1/dbs/orapwt24prodc 12.2 FALSE 0
SQ
How to Interpret the ACCOUNT_STATUS Column in DBA_USERS (Doc ID 260111.1)
select * from user_astatus_map;
STATUS# STATUS
---------- --------------------------------
0 OPEN
1 EXPIRED
2 EXPIRED(GRACE)
4 LOCKED(TIMED)
8 LOCKED
5 EXPIRED & LOCKED(TIMED)
6 EXPIRED(GRACE) & LOCKED(TIMED)
9 EXPIRED & LOCKED
10 EXPIRED(GRACE) & LOCKED
What do these mean ?
~~~~~~~~~~~~~~~~~~~
These values are directly related to two features 'Account Locking' and 'Password Aging
and Expiration' that are now explained briefly:
1) Account Locking - LOCKED / LOCKED(TIMED)
An account can be locked by a DBA or is locked automatically after a number of
failed login attempts. When a PASSWORD_LOCK_TIME is defined, the account
unlocks automatically after the set time: this is indicated by LOCKED(TIMED).
The LOCKED(TIMED) value is what you always see if the account was locked
due to the number of failed logins > FAILED_LOGIN_ATTEMPTS.
You only see LOCKED if the account is explicitly locked during create or
alter user/role.
You may expect that when the account is automatically locked and PASSWORD_LOCK_TIME is set to unlimited, the account would appear as just LOCKED. This is however not the case: it still shows as LOCKED(TIMED). However the account never unlocks automatically because of the unlimited PASSWORD_LOCK_TIME (infinity).
To add to the confusion, at some point it was decided that even if the account is locked automatically, we should not set it to LOCKED(TIMED) if the PASSWORD_LOCK_TIME is unlimited, since in that case the account would never automatically unlock, this would give up on a crucial piece of information, namely if the account was locked manually or automatically, this change was introduced in 11.2.0.1. However this change caused a regression in Bug 9693615 causing the lock_date to be NULL in dba_users in case the account was locked automatically, the fix to this bug backed out the change again and now we have the LOCKED(TIMED) for automatically locked accounts back with this fix.
So a DBA will know that when the ACCOUNT_STATUS is LOCKED(TIMED) that the
lock was a result of a failed login attempt, even if the lock will not expire. In 11.2.0.1 (without the fix to Bug 9693615) you can verify if the account was locked automatically if the lock_date in dba_users is null.
2) Password Expiration - EXPIRED / EXPIRED(GRACE)
A password can be set to expire, with or without a grace period:
when a password expires and no grace is defined, the password is set at
EXPIRED, meaning that the user is prompted for a new password upon the
next login attempt.
When a grace is defined, during the grace period, a warning is issued,
and the ACCOUNT_STATUS is set to EXPIRED(GRACE).
Conclusion
~~~~~~~~~~
Password Expiration and Account Locking are two separate features:
an account cannot be locked by exceeding the expire or subsequent grace
time.
An account that is neither expired nor locked appears as OPEN (STATUS# 0),
since account locking (based on failed login attempts) and expiration
(based on not changing password) are basically unrelated.
In addition to the EXPIRED (1,2) and LOCKED (4,8) bits being set, you can
have combinations of both with STATUS# 5,6,9 and 10: internally the
STATUS# are added for the combinations of expired and locked.
For example :
'EXPIRED' + 'LOCKED' = 1 + 8 = 9 = 'EXPIRED & LOCKED'
=======
https://anuj-singh.blogspot.com/2016/08/how-to-re-open-expired-or-expiredgrace_17.html
set pagesize 0
col alert1 for a140
SELECT 'ALTER USER '|| name ||' IDENTIFIED BY VALUES '''|| spare4 ||';'|| password ||''';' alter1 FROM sys.user$ WHERE name='SYS';
set pagesize 80
set long 5000
SELECT DBMS_METADATA.get_ddl ('USER', 'SYS') FROM DUAL;
====================
