Tips for DBA PArt 5

To find which users password is expiring till 30 days from now
-------------------------------------------------------------------------------------
Select upper (sys_context( 'USERENV', 'SERVER_HOST' ) ||','||sys_context( 'USERENV', 'DB_NAME' )||','|| username ||','||ACCOUNT_STATUS||','||EXPIRY_DATE||','|| LOCK_DATE) from dba_users where EXPIRY_DATE between sysdate and sysdate +  30;

Object Dependecies =============


--Dependency used by:
SELECT   owner,
         object_type,
         object_name,
         object_id,
         status
  FROM   sys.DBA_OBJECTS
 WHERE   object_id IN
               (    SELECT   object_id
                      FROM   public_dependency
                CONNECT BY   PRIOR object_id = referenced_object_id
                START WITH   referenced_object_id =
                                (SELECT   object_id
                                   FROM   sys.DBA_OBJECTS
                                  WHERE       owner = :Owner
                                          AND object_name = :name
                                          AND object_type = :TYPE));
---Dependency usages:
SELECT   a.object_type,
         a.object_name,
         b.owner,
         b.object_type,
         b.object_name,
         b.object_id,
         b.status
  FROM   sys.DBA_OBJECTS a,
         sys.DBA_OBJECTS b,
         (    SELECT   object_id, referenced_object_id
                FROM   public_dependency
          START WITH   object_id =
                          (SELECT   object_id
                             FROM   sys.DBA_OBJECTS
                            WHERE       owner = :owner
                                    AND object_name = :name
                                    AND object_type = :TYPE)
          CONNECT BY   PRIOR referenced_object_id = object_id) c
 WHERE       a.object_id = c.object_id
         AND b.object_id = c.referenced_object_id
         AND a.owner NOT IN ('SYS', 'SYSTEM')
         AND b.owner NOT IN ('SYS', 'SYSTEM')
         AND a.object_name <> 'DUAL'
         AND b.object_name <> 'DUAL'



select * from dba_registry
select * from v$session
select * from
select * from v$session where program like 'JDBC T%' and username = 'ADM' --and machine = 'abc24'
and status = 'ACTIVE'
select s.last_call_et, s.sid, S.BLOCKING_SESSION, S.BLOCKING_SESSION_STATUS , sw.event, sa.sql_id, sa.sql_fulltext from v$session s, v$sqlarea sa, v$session_wait sw
where program like 'JDBC T%'
and username = 'ADM' --and machine = 'abc24'
and status = 'ACTIVE'
--and sa.sql_id = '2xdxk0bdfwz5c'--'568fas464rsmt'
and s.sql_id = sa.sql_id
and s.sid = sw.sid
order by 1 desc

select * from v$sql_plan where sql_id in( '568fas464rsmt','gk0h0q5gb1b2z')
select * from v$sql where upper(sql_fulltext) like '%MULTILANG%'
select S.BLOCKING_SESSION, S.BLOCKING_SESSION_STATUS, count(1) from v$session s
group by S.BLOCKING_SESSION, S.BLOCKING_SESSION_STATUS
select s.sid, S.BLOCKING_SESSION, S.BLOCKING_SESSION_STATUS , sw.event, sa.sql_id, sa.sql_fulltext from v$session s, v$sqlarea sa, v$session_wait sw
where program like 'toad%'
and username = 'ADM' --and machine = 'abc24'
and status = 'ACTIVE'
--and sa.sql_id = '568fas464rsmt'
and s.sql_id = sa.sql_id
and s.sid = sw.sid
SELECT * FROM TABLE(AG_GET_PROD_LIB_DATA('','US','eng','All','1000001295:epsg:pro','34401A','PRODUCT'));

select event, count(1) from v$session_wait where sid in (select sid from v$session where program like 'JDBC T%' and username = 'ADM' --and machine = '192.23.43.12'
and status = 'ACTIVE')
group by event
select * from v$locked_object
select ddl.* from dba_ddl_locks ddl, v$session s
where s.sid = ddl.session_id
and program like 'JDBC T%'
and username = 'ADM'
--and machine = 'acomd24'
order by ddl.session_id;
select name, ddl.type, count(1) from dba_ddl_locks ddl, v$session s
where s.sid = ddl.session_id
and program like 'JDBC T%'
and username = 'ADM1T'
and machine = 'acomd24'
group by name, ddl.type
order by 3 desc;
SELECT sid, event, p1raw
  FROM sys.v_$session_wait
 WHERE event = 'library cache pin'
   AND state = 'WAITING';

select sql_id, count(*)
from dba_hist_active_sess_history
where event_id = (select event_id from v$event_name where name = 'control file sequential read')
and sample_time >= trunc(sysdate)
group by sql_id
order by 2 desc ;
select sql_fulltext from v$sqlarea where sql_id in
(select sql_id
from dba_hist_active_sess_history
where event_id = (select event_id from v$event_name where name = 'control file sequential read')
and sample_time >= trunc(sysdate) )
and lower(sql_fulltext) not like '%dual%';
SELECT kglnaown AS owner, kglnaobj as Object
  FROM sys.x$kglob

SELECT waiting_session, holding_session FROM dba_waiters;
select sesion.sid,
       sql_text
  from v$sqlarea sqlarea, v$session sesion
 where sesion.sql_hash_value = sqlarea.hash_value
   and sesion.sql_address    = sqlarea.address;

select * from dba_hist_sqlstat where sql_id = 'cy07djsd15920' and parsing_schema_name = 'ADM1T'

Tips for DBA part 3

Formating Output : =====================================>
set lines 200 pages 100
col username format a16
col ACCOUNT_STATUS format a20
select USERNAME, ACCOUNT_STATUS, LOCK_DATE,EXPIRY_DATE, PROFILE from DBA_USERS;
col object_name for a30;
col object_type format a20
col object_name format a30
select object_type,object_name from user_objects where status='INVALID' group by object_type,object_name ;
select object_type, count(0) from user_objects where status='INVALID' group by rollup(object_type);
select object_type,object_name from user_objects where status='INVALID' group by rollup(object_type,object_name);
select 'exec DBMS_MVIEW.refresh ('||''''||object_name||''''||','||''''||'C'||''''||');' MVQuery from user_objects where object_type = 'MATERIALIZED VIEW'
'GRANT SELECT ON '||table_name||' TO <read_only_user>;' from user_tables
select 'GRANT EXECUTE ON '||object_name||' TO campread;' from user_objects where object_type in ('FUNCTION','PACKAGE','PACKAGE BODY');
select 'exec DBMS_MVIEW.refresh ('||''''||object_name||''''||','||''''||'C'||''''||');' MVQuery from user_objects where object_type = 'MATERIALIZED VIEW'

 
 
Enable and Disable Constraints. : =====================================>
select 'alter table ' || table_name || ' disable constraint ' || constraint_name || ' ; 'from user_constraints where constraint_type = 'R';
select 'alter table ' || table_name || ' enable constraint ' || constraint_name || ' ; 'from user_constraints where constraint_type = 'R';