Possibly unused objects
More often that I would have imagined, a database system stores abandoned objects with names like 'TMP_IMPORT_2023_04', IMPORT_2023_OLD etc.
The following select statement tries to identify such objects.
select
*
from
dba_objects obj
where
(
obj.owner not in ('SYS', 'PUBLIC') and
obj.object_type not in ('INDEX')
) and
(
obj.object_name like 'XX%' or
(obj.object_name like '%TRY' and obj.object_name not like '%ENTRY') or
obj.object_name like 'TMP%' or
obj.object_name like 'ABC' or
obj.object_name like 'TEST%' or
obj.object_name like 'DROP%' or
obj.object_name like '%OLD' or
obj.object_name like '%COPY' or
obj.object_name like '%BKP' or
regexp_like(obj.object_name, '_$')
)
order by
obj.created;
Use with caution!
Join with V$PWFILE_USERS
select
usr.username,
usr.user_id,
pwf.sysdba,
pwf.sysoper,
pwf.sysasm
--pwf.sysbackup,
--pwf.sysdg,
--pwf.syskm
from
dba_users usr left join
v$pwfile_users pwf on usr.username = pwf.username
;
Column ACCOUNT_STATUS
select
count(*),
account_status
from
dba_users
group by
account_status
order by
account_status;