Ora User
main monitor ddl objects dml stato e configurazione rman & dpump pl/sql cluster ASM
main config start-shut process memory monitor diag
user rman & dpump pl/sql cluster ASM

USER

User METADATA

undef user
select dbms_metadata.get_ddl('USER',username) || '/' usercreate 
     FROM dba_users 
     WHERE USERNAME = upper('&&user');

User STATUS

set line 200 pages 500
col username for a32
col profile for a24
select username, profile, account_status, lock_date, expiry_date, to_char(created, 'DD-MON-YYYY HH24:MI:SS') created 
from dba_users 
where username like '%RMAN%' 
order by created;

user PRIVS

set line 200 pages 500
col USER_PRIVS for a48
SELECT LPAD(' ', 2*level) || granted_role USER_PRIVS
FROM (
  SELECT NULL grantee, username AS GRANTED_ROLE
  FROM dba_users
  WHERE username LIKE UPPER('%&uname%')
  UNION
  SELECT grantee, granted_role
  FROM dba_role_privs
  UNION
  SELECT grantee, privilege
  FROM dba_sys_privs)
START WITH grantee IS NULL
CONNECT BY grantee = prior granted_role;

DROP users metadata

set pages 5000 line 200
spool drop_user
select 'drop user ' || username || ' cascade;' uname from dba_users where username like 'RMAN%';
spool off

User: Re-create

drop user "WFM_APP" cascade;
CREATE USER "WFM_APP" IDENTIFIED BY VALUES '0AA3535A4767BF57'
      DEFAULT TABLESPACE "WFM_DATA"
      TEMPORARY TABLESPACE "TEMP"
      PROFILE "FW_USER_APP";

Objects owned by USER

select object_name, OWNER,OBJECT_TYPE from dba_objects where owner= 'SI_INFORMTN_SCHEMA';

Objects by USER and TYPE

select OWNER,OBJECT_TYPE, OBJECT_NAME 
     FROM DBA_OBJECTS 
     WHERE OWNER = 'AUDIT_ADMIN' 
          AND OBJECT_TYPE='INDEX';

Objects INVALID by User

select object_type, object_name, status from dba_objects 
     where owner in ('USER1', 'USER2') 
             and status like '%INVALID%'
     ORDER BY 1;

USERS with creation_date

col username format a32
select username, to_char(created, 'YYYY-MM-DD HH24:MI:SS') as created 
     from dba_users 
     order by created;
clear col

Users that have changed pwd from c_time

col name format a32
select name, 
            to_char(ctime, 'YYYY/MM/DD HH24:MI:SS') crea_pwd,
            to_char(ptime, 'YYYY/MM/DD HH24:MI:SS') chg_pwd
        from user$
        where ptime <> ctime
    order by ptime;
clear col

USERS with PWD (hash)

col name format a32
col password format a32
undef usern
select name, password
        from user$
        where name = UPPER('&&usern');
 
clear col
undef usern

DROP USERS

set pages 5000 line 200
select 'drop user ' || username || ' cascade;' uname from dba_users where username like 'RMAN%';

Tablespaces by User

select USERNAME, DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE, PROFILE 
    from dba_users 
    where USERNAME IN ('DWH_DATA_OWN','MD_BO_OWN','NEPTUNY')
    order by 1;
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License