ORA OBJECTS
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
Objects INVALID
select object_type, object_name, status from dba_objects 
     where owner in ('USER1', 'USER2') 
             and status like '%INVALID%'
     ORDER BY 1;
Objects by type
select owner, object_name 
     from dba_objects 
     where object_type = 'PROCEDURE';
Objects 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';
USERS with creation_date
col username format a32
select username, to_char(created, 'YYYY-MM-DD HH24:MI:SS') as c_time 
     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') creat_time,
            to_char(ptime, 'YYYY/MM/DD HH24:MI:SS') cpwd_time
        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
select tablespace_name, max_extents, status, contents, logging, allocation_type, retention 
     from dba_tablespaces 
     order by 1;
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;
Tables like %table_name%
select owner, table_name,tablespace_name 
     from dba_tables 
     where table_name like '%AUDIT%';
Synonyms IN owner
col c1 format a16 heading "OWNER"
col c2 format a24 heading "SYN_NAME"
col c3 format a32 heading "TAB_NAME"
select owner c1, SYNONYM_NAME c2, TABLE_NAME c3 
    FROM dba_synonyms 
    WHERE owner in ('JMS_WFA_WS','JMS_WFA_AS','AUDIT_ADMIN','WFM_APP') 
    ORDER by 1;
Count: Objects IN owner
select OWNER,OBJECT_TYPE,count(*) NUM from dba_objects 
      where OWNER IN ('JMS_WFA_WS','JMS_WFA_AS','AUDIT_ADMIN')
      group by owner,object_type
      order by owner, OBJECT_TYPE;
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License