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 |
Table of Contents
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;
page revision: 5, last edited: 07 Jun 2017 12:49