Tips For DBA Part 7

How To get TK Prof

1. the execution plan for each query (see step 3.2 from NOTE.235530.1 Methods for Obtaining a Formatted Explain Plan)
2. the 10046 trace file for each execution:
SQL> alter session set max_dump_file_size=unlimited;
sql> ALTER SESSION SET SQL_TRACE = true;
SQL> alter session set events='10046 trace name context forever, level 12';
sql> select
  u_dump.value   || '/'     ||
  lower(db_name.value)  || '_ora_' ||
  v$process.spid ||
  nvl2(v$process.traceid,  '_' || v$process.traceid, null )
  || '.trc'  "Trace File" 
from
             v$parameter u_dump
  cross join v$parameter db_name
  cross join v$process
        join v$session
          on v$process.addr = v$session.paddr
where
 u_dump.name   = 'user_dump_dest' and
 db_name.name  = 'instance_name'        and
v$session.audsid=sys_context('userenv','sessionid');
SQL> -- run the query
SQL> select * from dual;
SQL> exit

==================

Create Domain INdex:

1) Run the below query where index was sucssfully created and in VALID mode:
set long 2000000000
set head off
set pagesize 10000
select ctx_report.create_index_script('<INDEX_NAME>') from dual;

2) Then Drop the index for with you received error:
Drop the failed index
Drop index <INDEX_NAME> force;
3) and recreate with above generated script.


=============================

Table Defragmentation Activity: ========================================================>
One Way :--->
alter table table_name enable row movement;
alter table table_name shrink space cascade ;
alter table table_name disable row movement;
Reason --> segment managment is not auto that is why we are using the below lines
Second Way :--->
alter table table_name move tablespace bv_data;
analyze table table_name estimate statistics sample 30 percent;
alter index index_name rebuild online;
--> Check if the indexs are valid
select * from all_indexes where status <> 'VALID' and owner = 'VIVEK';
--> Check the spaces but this query is customized check before use :
select table_name, act_size_mb, est_size_mb, act_size_mb- est_size_mb from(
select 'Table' type, table_name,  (st.bytes /1024/1024 )act_size_mb,
round((avg_row_len * num_rows * (1 + PCT_FREE/100) * 1.15)/1024/1024,2) est_size_mb,
st.blocks, st.extents
from all_tables dt, dba_segments st
where dt.table_name = st.segment_name
and st.owner = dt.owner
and st.owner = 'VIVEK'
--and dt.table_name = 'TABLE2'
)
where act_size_mb- est_size_mb  > 20
order by 2 desc;
2 .. select table_name, tablespace_name, num_rows, blocks, empty_blocks, avg_space,
chain_cnt, avg_row_len, last_analyzed, degree, buffer_pool 
from all_tables;
-----> Find the index for the tables :
select index_name,status from all_indexes where table_name = 'TABLE3';

=======================================================================================================>
Other's in table Defragmentations L
select table_name, tablespace_name, num_rows, blocks, empty_blocks, avg_space, chain_cnt,
avg_row_len, last_analyzed, degree, buffer_pool 
from all_tables;
select * from all_indexes where table_name = 'TABLE2';
select * from all_indexes where status <> 'VALID' and owner = 'VIVEK';
select segment_name, segment_type, (bytes)/1024/1024 from dba_segments where
owner = 'VIVEK' order by 3 desc;
select segment_name, segment_type, (bytes)/1024/1024 from dba_segments where
owner = 'VIVEK' order by 3 desc;
select 'Table' type, table_name,  (st.bytes /1024/1024 )act_size_mb,
round((avg_row_len * num_rows * (1 + PCT_FREE/100) * 1.15)/1024/1024,2) est_size_mb,
st.blocks, st.extents
from all_tables dt, dba_segments st
where dt.table_name = st.segment_name
and st.owner = dt.owner
and st.owner = 'VIVEK'
--and dt.table_name = 'TABLE2'
order by 3 desc;
alter table VIVEK.TABLE2 disable row movement;
alter table VIVEK.TABLE2 move tablespace bv_data;
analyze table VIVEK.TABLE2 estimate statistics sample 30 percent;
alter index VIVEK.INDEX2 rebuild online;
====================================================================================================>

Tips for DBA Part 6

Print The Ourput in HTML


sqlplus -S -M "HTML ON TABLE 'BORDER="2"'" username/passwd@server_name  @c:\a.txt > c:\a.html
set longchunksize 13000
set long 13000
set linesize 15000
select long_desc from ag_product_content where rownum <10 and long_desc is not null and rownum < 10;
exit



Table Count Script:

set serveroutput on
declare
v_str varchar2(4000):=null;
v_cnt number;
begin
for i in (select log_table, master from user_mview_logs)
loop
v_str:='Select count(1) from '||i.log_table;
execute immediate v_str into v_cnt;
if v_cnt>0 then
dbms_output.put_line('Master Table name: '||i.master||chr(9)||' Log Table_Name: '||i.log_table||chr(9)||' Count: '||v_cnt);
end if;
End loop;
End;



Table Space Size Details

SELECT  t. tablespace_name,t.total_space_in_GB , f.free_space_in_GB--, TO_CHAR((f.free_space_in_MB*100/t.total_space_in_MB),'99990.000') "Free%"
FROM (SELECT tablespace_name, trunc(SUM(bytes)/1024/1024/1024,2) Total_space_in_GB FROM DBA_DATA_FILES GROUP BY tablespace_name) t, 
(SELECT tablespace_name, trunc(SUM(bytes)/1024/1024/1024,2) Free_space_in_GB FROM DBA_FREE_SPACE GROUP BY tablespace_name) f
WHERE  t.tablespace_name= f.tablespace_name
order by 1;
SELECT aus.tablespace_name, df.file_name, df.TOTAL_SPACE_MB, NVL(dfs.FREE_SPACE_MB,0) Free_space_MB,
NVL(trunc( (free_space_mb*100/total_space_mb),2) ,0)"FREE%", TRUNC(df.TOTAL_SPACE_MB-USED_SPACE_MB ) Reclaim_space_MB, AUTOEXTENSIBLE, MAXBYTES_MB,INCREMENT_BY_MB
FROM (SELECT  tablespace_name, file_id,SUM(bytes)/1024/1024 AS "FREE_SPACE_MB" FROM DBA_FREE_SPACE GROUP BY tablespace_name, file_id) dfs, (SELECT file_id, file_name, SUM(bytes)/1024/1024 AS "TOTAL_SPACE_MB" FROM DBA_DATA_FILES GROUP BY file_id, file_name) df, (SELECT DISTINCT f.TABLESPACE_NAME, file_name,((ROUND(f.bytes / 1024 / 1024) - NVL(ROUND(s.bytes / 1024 / 1024),0))) "USED_SPACE_MB", f.AUTOEXTENSIBLE, TRUNC(f.MAXBYTES/1024/1024,2) MAXBYTES_MB, TRUNC(f.INCREMENT_BY/128,2) INCREMENT_BY_MB FROM DBA_DATA_FILES f, DBA_FREE_SPACE s WHERE f.file_id = s.file_id (+)
AND NVL(s.block_id,0) IN (NVL((SELECT MAX(block_id) FROM DBA_FREE_SPACE WHERE file_id = s.file_id),0))) aus
WHERE dfs.FILE_ID (+)= df.FILE_ID
AND aus.file_name (+)= df.file_name
ORDER BY 1,2;

select tablespace_name, count(1) No_of_DataFiles, sum(TOTAL_SPACE_MB) TOTAL_SPACE_MB, sum(FREE_SPACE_MB) FREE_SPACE_MB, sum(Reclaim_space_MB) Reclaim_space_MB from
(SELECT aus.tablespace_name, df.file_name, df.TOTAL_SPACE_MB, NVL(dfs.FREE_SPACE_MB,0) Free_space_MB,
NVL(trunc( (free_space_mb*100/total_space_mb),2) ,0)"FREE%", TRUNC(df.TOTAL_SPACE_MB-USED_SPACE_MB ) Reclaim_space_MB, AUTOEXTENSIBLE, MAXBYTES_MB,INCREMENT_BY_MB
FROM (SELECT  tablespace_name, file_id,SUM(bytes)/1024/1024 AS "FREE_SPACE_MB" FROM DBA_FREE_SPACE GROUP BY tablespace_name, file_id) dfs, (SELECT file_id, file_name, SUM(bytes)/1024/1024 AS "TOTAL_SPACE_MB" FROM DBA_DATA_FILES GROUP BY file_id, file_name) df, (SELECT DISTINCT f.TABLESPACE_NAME, file_name,((ROUND(f.bytes / 1024 / 1024) - NVL(ROUND(s.bytes / 1024 / 1024),0))) "USED_SPACE_MB", f.AUTOEXTENSIBLE, TRUNC(f.MAXBYTES/1024/1024,2) MAXBYTES_MB, TRUNC(f.INCREMENT_BY/128,2) INCREMENT_BY_MB FROM DBA_DATA_FILES f, DBA_FREE_SPACE s WHERE f.file_id = s.file_id (+)
AND NVL(s.block_id,0) IN (NVL((SELECT MAX(block_id) FROM DBA_FREE_SPACE WHERE file_id = s.file_id),0))) aus
WHERE dfs.FILE_ID (+)= df.FILE_ID
AND aus.file_name (+)= df.file_name)
group by tablespace_name
ORDER BY 1;
select owner, sum(bytes)/1024 from dba_segments
where owner in ('USER','READ','DBA')
group by owner
order by 1
select owner, trunc(sum(bytes)/1024/1024/1024,2) size_in_gb from dba_segments group by owner order by 2 desc;
select * from dba_objects where owner in ('TOAD');
select * from dba_recyclebin;
select trunc(sum(bytes)/1024/1024,2) size_in_mb from dba_segments where (owner, segment_name)  in (select owner, object_name from dba_recyclebin);

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;