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