=========linux useful=======
nmon
====================================================spool log==============
column tm new_value tm
column db new_value db
set feed on
set echo on
set time on
set timing on
set lines 1000 pages 1000
select to_char(sysdate, 'DD_MM_YYYY') tm from dual ;
select name db from v$database;
SPOOL OAS_&no._&tm._&db..log;
===========BLACKOUT==============
./emctl start blackout DR_DRIL_ACTIVITY -nodelevel -d 48:00
./emctl stop blackout DR_DRIL_ACTIVITY
./emctl status blackout
ps -ef|grep agent
cd /oracle/axoraadm/AGENT13C/agent_13.5.0.0.0/bin
./emctl status agent
./emctl start blackout bk_downtime -nodelevel -d 03:00
./emctl status blackout
========================grep command==============
grep -i "GENERAL_ACCT_MAST_TABLE" *
grep -i "GENERAL_ACCT_MAST_TABLE" * | grep -i 'stat'
cat table_stats.log | grep -i 'GATHER'
===============linux commands===========
===================stale tables===========
set line 200
select owner,table_name,stale_stats,last_analyzed from dba_tab_statistics where owner=upper('&owner') and stale_stats='YES' and TABLE_NAME not like '%TMP';
===================active session=========
set line 300 pages 400
set feedback on;
col SPID for a15
col USERNAME for a15
col LOGONDATE for a25
col OSUSER for a15
col SQL_ID for a18
col MODULE for a25
select a.sid,b.spid,a.username,
to_char(a.logon_time,'DD-MON-YYYY HH24:MI:SS') LogonDate,a.osuser,a.sql_id , a.status, a.module,a.last_call_et
from gv$session a, gv$process b where a.paddr = b.addr
and a.status = 'ACTIVE' and a.username is not null
order by 9 desc;
====================INDEX==========
SELECT name, height,lf_rows,lf_blks,del_lf_rows FROM INDEX_STATS;
select count(*) from INDEX_STATS where height ='2';
select index_name,status,owner from dba_indexes where status!='VALID';
select index_name,status,owner from dba_indexes where status!='VALID';
select count(*) from dba_indexes where owner='MX_FIN29' and status='VALID';
select OBJECT_NAME,OWNER,CREATED from dba_objects where OBJECT_TYPE='INDEX' and OWNER='MX_REP'order by created FETCH FIRST 50 ROWS ONLY ;
create index MX_REP.IDX_231660001 on MX_REP.AXE_TO_FX_01_MK_REP("M_V_EVT_CLS","M_TP_INT","M_REF_DATA");
select degree,count(1) from dba_indexes where owner='MLC' group by degree;
select degree,INDEX_NAME from dba_indexes where owner='MX_FIN' and DEGREE='0';
select degree,INDEX_NAME from dba_indexes where owner='MX_FIN' and DEGREE='0';
alter
SELECT
'alter index '||OWNER||'.'||INDEX_NAME||' noparallel;'
FROM
DBA_INDEXES
WHERE
degree <> 'DEFAULT'
AND to_number(DEGREE) not like '%1%' and INDEX_TYPE not like '%LOB%' and owner='MX_REP'
-- put any exclusions here
--AND INDEX_NAME NOT IN ('BINARY_SEARCH_INDEX','TEXT_SEARCH_INDEX','SIMPLE_SEARCH_INDEX')
ORDER BY
INDEX_NAME
/
SELECT
'alter index '||OWNER||'.'||INDEX_NAME||' noparallel;'
FROM
DBA_INDEXES
WHERE
degree not like '%1%'
ANDINDEX_TYPE not like '%LOB%' and owner='MX_REP'
-- put any exclusions here
--AND INDEX_NAME NOT IN ('BINARY_SEARCH_INDEX','TEXT_SEARCH_INDEX','SIMPLE_SEARCH_INDEX')
ORDER BY
INDEX_NAME
/
select 'alter index "'||owner||'"."'||index_name||'" noparallel;' from dba_indexes where DEGREE like '%20%';
