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'; 

Tips For DBA part 3

Tnsnames.ora Path :: =====================================>
/etc/tnsnames.ora
DB Objects Queries:: =====================================>
select * from user_objects where status='INVALID' order by object_type;
select * from user_constraints;
select * from dba_objects where object_name = 'obj_name'
select * from dba_directories
select * from user_objects where trunc(last_ddl_time) > trunc(sysdate - 3) and object_type NOT IN ( 'TABLE', 'INDEX','VIEW','FUNCTION')
select * from user_objects where object_type = 'TABLE';
select * from v$session where status='ACTIVE' and type!='BACKGROUND' and schemaname='vivek'
select * from v$sql where sql_id = '81q8p3wcy6jjh'
select * from v$sql_plan where sql_id = '81q8p3wcy6jjh' and operation = 'HASH JOIN' order by bytes desc
select * from dba_objects where data_object_id = 2337498
select * from v$session_longops where sofar<totalwork
select INSTANCE_NUMBER,INSTANCE_NAME,STATUS,STARTUP_TIME,DATABASE_STATUS,ACTIVE_STATE from v$instance;
select * from dict where table_name like '%SORT%'
select * from V$TEMPSTAT
select * from V$TEMPSEG_USAGE
select * from V$TEMP_EXTENT_POOL
select * from V$TEMP_SPACE_HEADER
select * from V$TEMPSTATXS
select * from V$TEMP_HISTOGRAM
select * from V$SORT_SEGMENT
select * from V$SORT_USAGE
select * from v$parameter where name like 'pga%'
select * from v$instance@publish
select * from user_db_links
select * from dba_directories
select * from v$session where status = 'ACTIVE'
select * from dba_mview_refresh_times;
select inst_id, tablespace_name, total_blocks, used_blocks, free_blocks from gv$sort_segment
select OWNER,OBJECT_NAME,OBJECT_TYPE,STATUS from all_objects;
select segment_type , count(1) from dba_segments where tablespace_name = 'V_INDEX' group by segment_type;
select * from recyclebin;
select sum(bytes/1024/1024) from dba_free_space where tablespace_name='&tbname' / 2 Enter value for tbname: V_Index
select segment_type , count(1) from dba_segments where tablespace_name = 'V_Index' group by segment_type;
select * from recyclebin;
select sum(bytes/1024/1024) from dba_free_space where tablespace_name='&tbname'
select object_name, original_name, type, can_undrop as "UND", can_purge as "PUR", droptime from recyclebin
flashback table tst to before drop;


Utilities:: =====================================>
exec sys.alter_user ('alter user vivread account unlock');
exec dbms_utility.compile_schema(schema => '`echo $USERNAME | tr [:lower:] [:upper:]`',compile_all=>FALSE);
exec dbms_utility.compile_schema('VIVEK',FALSE);
exec alter_user('alter user username identified by passwd')
exec sys.kill_session(30,'52981');
exec sys.kill_session(sid,'serialno');
exec alter_user('alter user a2 account unlock');
exec alter_user('alter user a2 identified by nji90okm');
exec dbms_mview.refresh('MVIEW_NAME','C')
C -> complete
exec flush_sga('SHARED_POOL');
exec flush_sga('BUFFER_CACHE');
select * from dual@im_tns
select * from user_objects where object_type = 'TABLE';
select * from gv$session where type <> 'BACKGROUND' order by last_call_et desc;

Things for you DBA Part 1

what is DBA_Directories==============
select * from DBA_DIRECTORIES;
======================================
How to see when the last time object was modified===================
select * from user_objects;
select * from user_tables;
=========================================
How to create DB Link
=============================================
How to analyze tables ==========================================
ANALYZE TABLE emp VALIDATE STRUCTURE;
ANALYZE TABLE emp VALIDATE STRUCTURE CASCADE;
ANALYZE TABLE emp VALIDATE STRUCTURE CASCADE FAST;
ANALYZE TABLE emp VALIDATE STRUCTURE CASCADE ONLINE;
========================================================
What is Mview======================================
===================================================
How many ways of refresh==========================
=====================================================
How to updated a table==========================
update table table_name
set column_name ='new value'
where column_name = 'condition';
===============================================
How to see all objects===============================
select * from user_objects;
select * from user_tables;
=======================================================
How to see space consumed
==================================================
check your oracle version===============>
SELECT * FROM PRODUCT_COMPONENT_VERSION;
=================================================
parameters of database comes from below :
select * from V$PARAMETER
select * from V$PARAMETER2
select * from V$SESSION
select * from V$DATABASE
select * from GLOBAL_NAME
select * from DATABASE_PROPERTIES
================================================
ALTER ANY Table privilige is required
Enable Triggers
ALTER TABLE inventory ENABLE ALL TRIGGERS;
=================================================
ALTER ANY PROCEDURE privilige is required
Manually Recompile View
ALTER VIEW emp_dept COMPILE;
=================================================
ALTER ANY PROCEDURE privilige is required
Manually Recompile Procedures and function
ALTER PROCEDURE update_salary COMPILE;
============================================
ALTER ANY PROCEDURE privilige is required
ALTER PACKAGE acct_mgmt COMPILE BODY;
ALTER PACKAGE acct_mgmt COMPILE PACKAGE;
===========================================
search in sysnonyms
 SELECT TABLE_OWNER, TABLE_NAME, SYNONYM_NAME
    FROM DBA_SYNONYMS
    WHERE OWNER = 'JWARD';

=======================================
check for privileges
SELECT DISTINCT PRIVILEGE AS "Database Link Privileges"
FROM ROLE_SYS_PRIVS
WHERE PRIVILEGE IN ( 'CREATE SESSION','CREATE DATABASE LINK',
                     'CREATE PUBLIC DATABASE LINK')
/