Tips For DBA Part 2

select username, profile, account_status, lock_date, expiry_date from dba_users --where profile = 'DEFAULT'
order by 1
select * from dba_users
select resource_name, resource_type, max(DEFAULT_LIMIT) DEFAULT_LIMIT , max(MONITORING_PROFILE_LIMIT) MONITORING_PROFILE_LIMIT from (
select resource_name, resource_type, limit DEFAULT_LIMIT, null MONITORING_PROFILE_LIMIT from dba_profiles where profile = 'DEFAULT'
union all
select resource_name, resource_type, null DEFAULT_LIMIT,limit MONITORING_PROFILE_LIMIT from dba_profiles where profile = 'MONITORING_PROFILE'
)
where resource_type = 'PASSWORD'
group by resource_name, resource_type
order by 1
select distinct profile from dba_profiles
select resource_name, resource_type, max(DEFAULT_LIMIT) DEFAULT_LIMIT , max(PROFILE_EXEMPTED_LIMIT) PROFILE_EXEMPTED_LIMIT,
max(PROFILE_NON_EXPIRED_LIMIT) PROFILE_NON_EXPIRED_LIMIT from (
select resource_name, resource_type, limit DEFAULT_LIMIT, null PROFILE_EXEMPTED_LIMIT, null PROFILE_NON_EXPIRED_LIMIT from dba_profiles where profile = 'DEFAULT'
union all
select resource_name, resource_type, null DEFAULT_LIMIT,limit PROFILE_EXEMPTED_LIMIT, null PROFILE_NON_EXPIRED_LIMIT from dba_profiles where profile = 'PROFILE_EXEMPTED'
union all
select resource_name, resource_type, null DEFAULT_LIMIT, null PROFILE_EXEMPTED_LIMIT ,limit PROFILE_NON_EXPIRED_LIMIT from dba_profiles where profile = 'PROFILE_NON_EXPIRED'
)
where resource_type = 'PASSWORD'
group by resource_name, resource_type
order by 1
==Run below queries to find out Failed Login Attempt========
select os_username, username,userhost,timestamp,action_name,returncode from dba_audit_trail where returncode =1017 and username='BVREAD';
select os_username, username,userhost,timestamp,action_name,returncode from DBA_AUDIT_SESSION where returncode =1017 and username='BVREAD';