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'