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 |
Table of Contents
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;
page revision: 5, last edited: 21 Jul 2017 09:17