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 2

select username, profile, account_status, lock_date, expiry_date from dba_users --where profile = 'DEFAULT'
order by 1
select * from dba_users
select resource_name, resource_type, max(DEFAULT_LIMIT) DEFAULT_LIMIT , max(MONITORING_PROFILE_LIMIT) MONITORING_PROFILE_LIMIT from (
select resource_name, resource_type, limit DEFAULT_LIMIT, null MONITORING_PROFILE_LIMIT from dba_profiles where profile = 'DEFAULT'
union all
select resource_name, resource_type, null DEFAULT_LIMIT,limit MONITORING_PROFILE_LIMIT from dba_profiles where profile = 'MONITORING_PROFILE'
)
where resource_type = 'PASSWORD'
group by resource_name, resource_type
order by 1
select distinct profile from dba_profiles
select resource_name, resource_type, max(DEFAULT_LIMIT) DEFAULT_LIMIT , max(PROFILE_EXEMPTED_LIMIT) PROFILE_EXEMPTED_LIMIT,
max(PROFILE_NON_EXPIRED_LIMIT) PROFILE_NON_EXPIRED_LIMIT from (
select resource_name, resource_type, limit DEFAULT_LIMIT, null PROFILE_EXEMPTED_LIMIT, null PROFILE_NON_EXPIRED_LIMIT from dba_profiles where profile = 'DEFAULT'
union all
select resource_name, resource_type, null DEFAULT_LIMIT,limit PROFILE_EXEMPTED_LIMIT, null PROFILE_NON_EXPIRED_LIMIT from dba_profiles where profile = 'PROFILE_EXEMPTED'
union all
select resource_name, resource_type, null DEFAULT_LIMIT, null PROFILE_EXEMPTED_LIMIT ,limit PROFILE_NON_EXPIRED_LIMIT from dba_profiles where profile = 'PROFILE_NON_EXPIRED'
)
where resource_type = 'PASSWORD'
group by resource_name, resource_type
order by 1
==Run below queries to find out Failed Login Attempt========
select os_username, username,userhost,timestamp,action_name,returncode from dba_audit_trail where returncode =1017 and username='BVREAD';
select os_username, username,userhost,timestamp,action_name,returncode from DBA_AUDIT_SESSION where returncode =1017 and username='BVREAD';