Join the OracleApps88 Telegram group @OracleApps88to get more information on Oracle EBS R12/Oracle Fusion applications.

If you are facing any issues while copying the Code/Script or any issues with Posts, Please send a mail to OracleApp88@Yahoo.com or message me at @apps88 or +91 905 957 4321 in telegram.

Saturday, December 10, 2011

ORACLE DATABASE MONITORING QUERYS

1.DATABASE MEMORY HEALTH.
1. Data Dictionary Hit Ratio.
select sum(GETS),sum(GETMISSES),round((1 - (sum(GETMISSES) / sum(GETS))) * 100,2) from v$rowcache
2. Dictionary Cache Hit Ratio.
select PARAMETER, GETS,GETMISSES,round(GETMISSES/GETS,2)*100 Cache_Misses ,COUNT,USAGE from v$rowcache where GETS > 0 order by (GETMISSES/GETS)*100 desc
3. Buffer Hit Ratio.
select sum(decode(NAME, 'consistent gets',VALUE, 0)) Consistent_Gets ,sum(decode(NAME, 'db block gets',VALUE, 0)) DB_Block_Gets,sum(decode (NAME, 'physical reads',VALUE, 0)) Physical_Reads,round((sum(decode (name, 'consistent gets',value, 0)) + sum(decode(name, 'db block gets' ,value, 0)) -sum(decode(name, 'physical reads',value, 0)))/(sum(decode (name, 'consistent gets',value, 0)) +sum(decode(name, 'db block gets', value, 0))) * 100,2) Hit_Ratio from v$sysstat;
OR
select consistent_gets,db_block_gets,physical_reads,(1- (physical_reads / (db_block_gets + consistent_gets)))*100 Hit_Ratio from v$buffer_pool_statistics;
4. Library Cache Hit Ratio.
select sum(PINS) Pins, sum(RELOADS) Reloads, round((sum(PINS) - sum(RELOADS))/ sum(PINS) * 100,2) Hit_Ratio from v$librarycache
5. ALL Memory Hit/Miss Ratio
select
avg((select ((round((1 - (sum(GETMISSES) / sum(GETS))) * 100,2))) from v$rowcache )) "Data Dictionary HR",
avg((select ((sum(pins)-sum(reloads))/(sum(pins)))*100 from v$librarycache )) "library Cache HR",
avg((select (sum(gets)/(sum(waits)+sum(gets)))*100 from v$rollstat )) "RollBack HR",
avg((select (sum(misses)/(sum(gets)+sum(misses)))*100 from v$latch l where name like '%redo%' )) "Redolog buffr contentn",
avg((select (1- (physical_reads / (db_block_gets + consistent_gets)))*100 from v$buffer_pool_statistics)) "Buffer Cache HR",
avg((select (sum(gets)/(sum(gets)+sum(getmisses)))*100 from v$rowcache )) "Row Cache HR" from dual;
2. CPU UTILISATION NOTES.

  1. High CPU Usage Process.
select process,PIECE,SQL_TEXT,VALUE cpu_usage
from (select s.process,s.sid, p.spid PID,st. PIECE,st.SQL_TEXT from v$session s , v$process p,v$sqltext st where s.PADDR (+) =P.ADDR and s.sql_address = st.address order by st.PIECE) sq, v$sesstat ss, v$statname sn
where sq.sid=ss.sid and
ss.STATISTIC# = sn.STATISTIC# and
sn.NAME like '%CPU used by this session%'
and sq.PID=
2. CPU Usage By Session.
select nvl(ss.USERNAME,'ORACLE PROC') username,se.SID,VALUE cpu_usage from v$session ss, v$sesstat
se,v$statname sn where se.STATISTIC# = sn.STATISTIC# and NAME like '%CPU used by this session%'
and se.SID = ss.SID order by VALUE ;
3. Cursor Usage By Session.
select user_process username,Recursive_Calls,Opened_Cursors,
Current_Cursors from(select nvl(ss.USERNAME,'ORACLE PROC')|
|'('||se.sid||') ' user_process,sum(decode(NAME,'recursive calls'
,value)) Recursive_Calls,sum(decode(NAME,'opened cursors cumulative',
value)) Opened_Cursors,sum(decode(NAME,'opened cursors current',value)
) Current_Cursors from v$session ss, v$sesstat se, v$statname sn
where se.STATISTIC# = sn.STATISTIC# and (NAME like '%opened cursors
current%'or NAME like '%recursive calls%' or NAME like '%opened cursors
cumulative%')and se.SID = ss.SID and ss.USERNAME is not null group
by nvl(ss.USERNAME,'ORACLE PROC')||'('||se.SID||') ')orasnap_user_cursors
order by USER_PROCESS,Recursive_Calls ;
3.TABLESPACE INFORMATION.
1. TABLESPACE USAGE NOTES
COLUMN free_space_mb format 99999990.00;
COLUMN allocated_mb format 99999990.00;
COLUMN used_mb format 99999990.00;
COLUMN percent_used format 00000.00;

SELECT SUBSTR(df.tablespace_name,1,20) tablespace_name,
(df.bytes) / 1024 / 1024 allocated_mb,
((df.bytes)-nvl(dfs.bytes,0))/1024/1024 used_mb,
NVL ((dfs.bytes) / 1024 / 1024, 0) free_space_mb,
round( ((df.bytes-nvl(dfs.bytes,0))/df.bytes * 100),2) percent_used
FROM ( select sum(bytes) bytes,tablespace_name from dba_data_files group by tablespace_name) df,
( select sum(bytes) bytes , tablespace_name from dba_free_space group by tablespace_name) dfs
WHERE df.tablespace_name = dfs.tablespace_name
ORDER BY percent_used ;
OR-
SELECT A.TABLESPACE_NAME,A.BYTES BYTES_USED,B.BYTES BYTES_FREE,B.LARGEST,
ROUND(((A.BYTES-B.BYTES)/A.BYTES)*100,2) PERSENT_USED FROM
(SELECT TABLESPACE_NAME,SUM(BYTES) BYTES FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) A,
(SELECT TABLESPACE_NAME,SUM(BYTES) BYTES,MAX(BYTES) LARGEST FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME ) B WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME ORDER BY ((A.BYTES-B.BYTES)/A.BYTES) DESC
2. TABLESPACE / LARGEST EXT
SELECT TABLESPACE_NAME,SUM(BYTES) TOTAL_FREE_SPACE ,MAX(BYTES) LARGEST_FREE_EXTENT FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME

3. TABLESPACE INFORMATION
SELECT TABLESPACE_NAME, TOTAL_EXTENTS, EXTENTS_COALESCED, PERCENT_EXTENTS_COALESCED , TOTAL_BYTES ,BYTES_COALESCED , TOTAL_BLOCKS ,BLOCKS_COALESCED, PERCENT_BLOCKS_COALESCED FROM DBA_FREE_SPACE_COALESCED ORDER BY TABLESPACE_NAME

4. USER DEFAULT TABLESPACE

SELECT USERNAME, CREATED, PROFILE,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE FROM DBA_USERS ORDER BY USERNAME
5. OBJECT IN SYSTEM
SELECT OWNER ,SEGMENT_NAME, SEGMENT_TYPE ,TABLESPACE_NAME, BYTES FROM DBA_SEGMENTS WHERE
TABLESPACE_NAME='SYSTEM' AND OWNER NOT IN ('SYS','SYSTEM') ORDER BY OWNER ,SEGMENT_NAME
6. Objects that exists in SYSTEM but not belongs to SYS, SYSTEM.

select owner, substr(segment_name,1,30) segment_name, segment_type
from sys.dba_segments where owner not in ('PUBLIC', 'SYS', 'SYSTEM','OUTLN') and
tablespace_name = 'SYSTEM';
7. Objects that cannot extend (No space in Tablespace).

column owner format a12 heading 'Owner'
column segment_name format a30 heading 'Object Name'
column segment_type format a15 heading 'Object Type'
column tablespace_name format a12 heading 'Tablespace'
column next_extent format 999,999,999 heading 'Next'
column max_free_space format 999,999,999 heading 'Max Free space'
select owner, segment_name, segment_type, tablespace_name, next_extent,
(select max(sum(dfs.bytes))-100 bytes from dba_free_space dfs where dfs.tablespace_name = ds.tablespace_name group by dfs.file_id) max_free_space
from dba_segments ds
where next_extent >
(select max(sum(dfs.bytes))-100 bytes from dba_free_space dfs where dfs.tablespace_name = ds.tablespace_name group by dfs.file_id) ;
8. No space for all the Objects to extend in Tablespace.
column tablespace_name format a20 heading 'Tablespace'
column space_required format 99,999,999 heading 'Space Required by Objects'
column max_free_space format 99,999,999 heading 'Free space'
select s.tablespace_name, sum(s.next_extent)/1024/1024 space_required,
(select sum(bytes)/1024/1024 from dba_free_space dfs where dfs.tablespace_name = s.tablespace_name) free
from dba_segments s
group by s.tablespace_name
having sum(s.next_extent) >=
(
select sum(bytes)
from dba_free_space f
where f.tablespace_name = s.tablespace_name
);
9. Tablespace is fragmented.
SELECT SUBSTR(dfsc.tablespace_name,1,20) tablespace_name, DECODE (dfsc.percent_extents_coalesced,100,
(DECODE (GREATEST (
(SELECT COUNT (1) FROM dba_free_space dfs WHERE dfs.tablespace_name = dfsc.tablespace_name),
1
),
1,'No Frag','Bubble Frag'
)
),
'Possible Honey Comb Frag'
)
fragmentation_status
FROM dba_free_space_coalesced dfsc
ORDER BY dfsc.tablespace_name;
TTI off;
10. Tablespaces not online.
select tablespace_name , status from dba_tablespaces where status like 'OFFLINE';
4.GENERAL INFORMATION
1. Database Size.
select FILE_NAME,d.TABLESPACE_NAME,d.BYTES datafile_size,nvl(sum(e.BYTES),0) bytes_used,round(nvl(sum(e.BYTES),0) / (d.BYTES), 4) * 100 percent_used,
d.BYTES - nvl(sum(e.BYTES),0) bytes_free from DBA_EXTENTS e,DBA_DATA_FILES d where d.FILE_ID = e.FILE_ID (+) group by FILE_NAME,d.TABLESPACE_NAME,
d.FILE_ID , d.BYTES, Status order by d.TABLESPACE_NAME,d.FILE_ID
2. List of Archived Logs Generated in Last Three Days From Today.
column name format a60
select recid, name, completion_time
from v$archived_log
where completion_time >= trunc(sysdate-1) and completion_time < trunc(sysdate);
3. Segments approaching max extents.
select substr(ds.owner,1,10) owner, substr(ds.segment_name,1,30) segment_name , ds.max_extents, de.no_extents no_extents_reached, round(de.no_extents/ds.max_extents*100,2) no_extents_reached_pct
from dba_segments ds,
(select count(1) no_extents, owner,segment_name from dba_extents where owner in ('TBAADM','PERFSTAT') group by owner,segment_name ) de
where de.segment_name = ds.segment_name and
de.owner = ds.owner and
de.owner in ('TBAADM','PERFSTAT') and
de.no_extents/ds.max_extents*100 > 75;
4. Sequences approaching MAX value.
COLUMN used_pct format 00.00;
select substr(sequence_owner,1,10) owner, substr(sequence_name,1,30) sequence_name, max_value, last_number, round(last_number/max_value*100,2) used_pct
from dba_sequences
where last_number/max_value*100 > 75;
5. USERS having DBA privilages.
select * from dba_role_privs where GRANTED_ROLE = 'DBA';
6. No of rollback segments not online.
select SEGMENT_NAME, TABLESPACE_NAME,STATUS from dba_rollback_segs where STATUS ='OFFLINE';
7. Wait time for redo log process.
SELECT SUM(value) "Redo Buffer Waits"
FROM v$sysstat
WHERE name = 'redo log space wait time';
8. SGA free memory.
COLUMN pool HEADING "Pool";
COLUMN name HEADING "Name";
COLUMN sgasize HEADING "Allocated" FORMAT 999,999,999;
COLUMN bytes HEADING "Free" FORMAT 999,999,999;
SELECT f.pool, f.name , s.sgasize, f.bytes , ROUND(f.bytes/s.sgasize*100, 2) "% Free"
FROM
(SELECT SUM(bytes) sgasize, pool FROM v$sgastat GROUP BY pool) s
, v$sgastat f WHERE f.name = 'free memory' AND f.pool = s.pool;
9. DISK I/O.
select substr(file_name,1,70),sum(PHYRDS),sum(PHYWRTS),sum(READTIM),sum(WRITETIM)
from v$filestat,sys.dba_data_files where v$filestat.file#=sys.dba_data_files.file_id
group by substr(file_name,1,70);
10. PHYSICAL READS.
select value MISSES from v$sysstat where name='physical reads' ;
11. Sort area ratio ( sorts(memory)/sorts(disk)).
select disk.value "Disks" ,mem.value "Memeory" , (disk.value/mem.value) *100 "ratio" from v$sysstat disk , v$sysstat mem where mem.name = ' sorts (memory) ' and disk.name = ' sorts (disk) ' ;
set head off;
12. REDO LOG BUFFER STATISTICS there should be no Log Buffer Space Events.
select sid,event,seconds_in_wait,state from v$session_wait where event = 'log buffer space';
13. Buffer Retires value should be less then 1% of the value of redo entries.
select name , value from v$sysstat where name in ('redo buffer allocation retries','redo entries');
5. Analyze Database & schema.

1. Execute Database & schema analyze.

exec dbms_utility.analyze_database('ESTIMATE', estimate_percent => 30);
exec dbms_utility.analyze_schema ('SYS','ESTIMATE');
exec dbms_utility.analyze_schema ('SYSTEM','ESTIMATE');
exec dbms_utility.analyze_schema ('OUTLN','ESTIMATE');
exec dbms_utility.analyze_schema ('DBSNMP','ESTIMATE');
exec dbms_utility.analyze_schema ('KASTLE_DB','ESTIMATE');
exec dbms_utility.analyze_schema ('TBAADM','ESTIMATE');
exec dbms_utility.analyze_schema ('TBACUST','ESTIMATE');
exec dbms_utility.analyze_schema ('TBAGEN','ESTIMATE');
exec dbms_utility.analyze_schema ('TBAUTIL','ESTIMATE');
exec dbms_utility.analyze_schema ('TBABKP','ESTIMATE');
exec dbms_utility.analyze_schema ('CUSTOM','ESTIMATE');
exec dbms_utility.analyze_schema ('INFY','ESTIMATE');
exec dbms_utility.analyze_schema ('DBSPI','ESTIMATE');
exec dbms_utility.analyze_schema ('L3_DBA','ESTIMATE');
exec dbms_utility.analyze_schema ('PERFSTAT','ESTIMATE');

2. Analyzed tables with un-analyzed indexes.

select 'Index '||i.index_name||' not analyzed but table '||
i.table_name||' is.'
from user_tables t, user_indexes i
where t.table_name=i.table_name
and t.num_rows is not null
and i.distinct_keys is null;
6. SESSION STATISTICS

1. NOS OF ACTIVE AND INACTIVE SESSION.

select
(select count(*) from v$process p,v$session s,v$sess_io si where s.sADDR=P.ADDR (+)
and si.sid (+)=s.sid and s.status='ACTIVE') "NOS OF ACTIVE SESSION",
(select count(*) from v$process p,v$session s,v$sess_io si where s.sADDR=P.ADDR (+)
and si.sid (+)=s.sid and s.status='INACTIVE') "NOS OF INACTIVE SESSION"
from dual
select s.sid,s.STATUS,s.USERNAME,s.OSUSER,to_char(s.LOGON_TIME,'dd-mm-yyyy hh24:mm:ss') from v$process p,v$session s,v$sess_io si where s.sADDR=P.ADDR (+) and si.sid (+)=s.sid and s.status='ACTIVE' order by to_char(s.LOGON_TIME,' hh24:mm:ss') desc
select s.sid,s.STATUS,s.USERNAME,s.OSUSER,to_char(s.LOGON_TIME,'dd-mm-yyyy hh24:mm:ss') from v$process p,v$session s,v$sess_io si where s.sADDR=P.ADDR (+) and si.sid (+)=s.sid and s.status='INACTIVE' order by to_char(s.LOGON_TIME,' hh24:mm:ss') desc

7. UNDO STATISTICS

1. Actual Undo Size.

SELECT SUM(a.bytes) "UNDO_SIZE"
FROM v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#;

2. Undo Blocks per Second.

SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
"UNDO_BLOCK_PER_SEC"
FROM v$undostat;

3. DB Block Size.

SELECT TO_NUMBER(value) "DB_BLOCK_SIZE [KByte]"
FROM v$parameter
WHERE name = 'db_block_size';

4. Optimal Undo Retention.

SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
ROUND((d.undo_size / (to_number(f.value) *
g.undo_block_per_sec))) "OPTIMAL UNDO RETENTION [Sec]"
FROM (
SELECT SUM(a.bytes) undo_size
FROM v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#
) d,
v$parameter e,
v$parameter f,
(
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
undo_block_per_sec
FROM v$undostat
) g
WHERE e.name = 'undo_retention'
AND f.name = 'db_block_size'

5. Needed UNDO Size.

SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
(TO_NUMBER(e.value) * TO_NUMBER(f.value) *
g.undo_block_per_sec) / (1024*1024)
"NEEDED UNDO SIZE [MByte]"
FROM (
SELECT SUM(a.bytes) undo_size
FROM v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#
) d,
v$parameter e,
v$parameter f,
(
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
undo_block_per_sec
FROM v$undostat
) g
WHERE e.name = 'undo_retention'
AND f.name = 'db_block_size'

8.STATPACK UTILITY
a) conn sys /as sysdba
b) sql>create tablespace STATPACK_DAT datafile '/database1/UCO/datafiles/STATPACK.dbf' size 500 M
c) sql>create temporary tablespace STATPACK_TEMP tempfile '/database1/UCO/datafiles/STATPACK_TEMP.dbf' size 500 M
d) *spcreate.sql script(run this script)
e) *statspack.snap script( run for get the snap)
.......................wait for some transactions .......................
f) exec statspack.snap //////script( run for get the snap)
g) exec spreport.sql //////script( to analyze the snap)
exec statspack.snap(i_snap_level => 7, i_modify_parameter => 'true')

exec statspack.snap(i_snap_level => 7 )
2. start /cbsora/OraHome1/rdbms/admin/spreport.sql
1. select SNAP_ID, to_char(SNAP_TIME,'dd-mm-yyyy hh24:mi:ss') from STATS$SNAPSHOT order by snap_id; (To get Snap ID)
3. start /cbsora/OraHome1/rdbms/admin/sprepsql.sql #…… (Put hash value)

9. Explain Plan
@$ORACLE_HOME/rdbms/admin/utlxplan.sql
rename plan_table to my_plan_table
1. explain plan for ………………… (SQL Statement)
(INTO my_plan_table)
set statement_id='ST!'
FOR
SQL statment *****************
Or
explain plan
set statement_id=’sujay’
(INTO my_plan_table)
FOR
(SQL statment *****************)
2. @$ORACLE_HOME/rdbms/admin/utlxpls.sql (After the plan has been explained)
@$ORACLE_HOME/rdbms/admin/utlxplp.sql
explain plan
set statement_id=’sujay’ for
SELECT TABLE_ABBR, TABLE_KEY, TABLE_FUNC, CONTEXT_SOLID from SO
D where ROWNUM <= :1

select plan_table_output from table (DBMS_XPLAN.DISPLAY());

10. MISCELLANEOUS

Locked Object

SELECT b.session_id AS sid, NVL(b.oracle_username, '(oracle)') AS username, a.owner AS object_owner, a.object_name,Decode(b.locked_mode, 0, 'None', 1, 'Null (NULL)', 2, 'Row-S (SS)',3, 'Row-X (SX)', 4, 'Share (S)', 5, 'S/Row-X (SSX)',6, 'Exclusive (X)', b.locked_mode) locked_mode, b.os_user_name FROM dba_objects a,v$locked_object b WHERE a.object_id = b.object_id ORDER BY 1, 2, 3, 4;


select SERIAL# , sid from v$session where sid=
alter system kill session '3838,1264';
  1. recover standby database parallel 20
  2. select max(sequence#) from v$loghist
  3. select to_char(creation_time, 'RRRR Month') "Month",
sum(bytes)/1024/1024 "Growth in Meg"
from sys.v_$datafile
where creation_time > SYSDATE-365
group by to_char(creation_time, 'RRRR Month')

select ts.name, to_char(creation_time, 'YYYY') "YYYY", to_char(creation_time, 'MM') "MM", sum(bytes)/1024/1024 "Growth in Meg"
from sys.v_$datafile df,v$tablespace ts
where df.ts#=ts.ts#
and creation_time > SYSDATE-365
and ts.ts# in ( select ts# from (select ts#, sum(bytes) ss from v$datafile d group by ts# order by ss desc )
where rownum<=10)
group by ts.name ,to_char(creation_time, 'YYYY'),to_char(creation_time, 'MM')
order by ts.name ,to_char(creation_time, 'YYYY') desc, to_char(creation_time, 'MM') desc
  1. select count(*) from gam where sol_id in ('1984')
  2. ll -lrt |grep "Feb" |awk '{print $9}' |xargs ll
grep -i 'dctd_acli' *scr
grep -i 'DCTD_ACLI' *sql
ndd -get /dev/tcp tcp_keepalive_interval
select to_char(creation_time, 'RRRR Month') "Month",
sum(bytes)/1024/1024 "Growth in Meg" from sys.v_$datafile
where creation_time > SYSDATE-365
group by to_char(creation_time, 'RRRR Month')


SELECT a.index_name,a.index_type,a.table_name,b.column_name from
USER_INDEXES a,USER_IND_COLUMNS b WHERE a.index_name = b.index_name


col status for a35
select s.sid,
s.sql_hash_value,
decode(w.wait_time, 0, w.event , 'CPU') as status,
w.p1, w.p2, w.p3
from v$session s,
v$session_wait w
where w.sid=s.sid
and s.status='ACTIVE'
and s.type='USER'
and w.event not in ('jobq slave wait',
'rdbms ipc reply');

select substr(ds.owner,1,10) owner,dt.TABLESPACE_NAME,substr(ds.segment_name,1,30) segment_name ,(ds.bytes/(1024*1024)) MB,dt.INITIAL_EXTENT
,dt.NEXT_EXTENT,dt.FREELISTS,dt.PCT_INCREASE,ds.max_extents, de.no_extents
no_extents_reached
from dba_segments ds,
(select count(1) no_extents, owner,segment_name from dba_extents where owner not in ('SYS','PERFSTAT','PUBLIC','SYSTEM','OUTLN') group by owner,
segment_name ) de,dba_tables dt
where de.segment_name = ds.segment_name and
ds.segment_name = dt.table_name and
de.owner = ds.owner and
de.owner = dt.owner and
de.owner not in ('SYS','PERFSTAT','PUBLIC','SYSTEM','OUTLN') and ds.segment_type='TABLE'
;
select substr(ds.owner,1,10) owner,dt.TABLESPACE_NAME,substr(ds.segment_name,1,30) segment_name ,dt.table_name ,ds.bytes,dt.INITIAL_EXTENT
,dt.NEXT_EXTENT,dt.FREELISTS,dt.PCT_INCREASE,ds.max_extents, de.no_extents
no_extents_reached
from dba_segments ds,
(select count(1) no_extents, owner,segment_name from dba_extents where owner not in ('SYS','PERFSTAT','PUBLIC','SYSTEM','OUTLN') group by owner,
segment_name ) de,dba_indexes dt
where de.segment_name = ds.segment_name and
ds.segment_name = dt.Index_name and
de.owner = ds.owner and
de.owner = dt.owner and
de.owner not in ('SYS','PERFSTAT','PUBLIC','SYSTEM','OUTLN') and ds.segment_type='INDEX'
;
select count(1) ,to_char (RCRE_TIME,'MM-YYYY') from gam where RCRE_TIME between '01-01-2006' and '31-12-2006'
and acct_ownership='C' group by to_char (RCRE_TIME,'MM-YYYY') order by to_char(RCRE_TIME,'MM-YYYY');
select count(1) ,to_char (RCRE_TIME,'MM-YYYY') from upr where RCRE_TIME between '01-01-2007' and '31-12-2007'
group by to_char (RCRE_TIME,'MM-YYYY') order by to_char(RCRE_TIME,'MM-YYYY');
SELECT username, sql_text, sofar, totalwork, units FROM v$sql,
v$session_longops
WHERE sql_address = address
AND sql_hash_value = hash_value
ORDER BY address, hash_value, child_number;

Here is a query that only shows queries that are long running (which means take more than 6 seconds), have some time remaining (so they are active), and shows you who is running and what is being run.
select a.sid,(a.sofar/a.totalwork)*100 "% Done", to_char(a.last_update_time, 'HH24:MI:SS'), a.username,a.time_remaining "Time Left",
a.opname ,s.sql_text from v$session_longops a, v$session b, v$sqltext s
where a.sid =b.sid and b.sql_address = s.address
and a.sofar <> a.totalwork
order by b.sid, last_update_time

select
total.tablespace_name tsname,
count(free.bytes) nfrags,
nvl(max(free.bytes)/1024,0) mxfrag,
total.bytes/1024 totsiz,
nvl(sum(free.bytes)/1024,0) avasiz,
(1-nvl(sum(free.bytes),0)/total.bytes)*100 pctusd
from dba_data_files total, dba_free_space free
where total.tablespace_name = free.tablespace_name(+)
and total.file_id=free.file_id(+)group by
total.tablespace_name,total.bytes
select TABLESPACE_NAME,CLUSTER_NAME,IOT_NAME,PCT_FREE,PCT_USED,INI_TRANS,MAX_TRANS,INITIAL_EXTENT,NEXT_EXTENT,MIN_EXTENTS,MAX_EXTENTS,PCT_INCREASE,FREELISTS from dba_tables
alter table custom.CUSTOM_TRAN_HIST_TABLE storage (pctincrease 0);
alter table custom.CUSTOM_TRAN_HIST_TABLE storage (NEXT 20480);
NEXT 20480
select * from
(SELECT rownum rownumber,
sql_text,
ceil(cpu_time/greatest(executions,1)) ave_cpu_time,
ceil(elapsed_time/greatest(executions,1)) ave_elapsed_time,
ceil(disk_reads/greatest(executions,1)) ave_disk_reads,
persistent_mem per_mem, runtime_mem run_mem,
ceil(sorts/greatest(executions,1)) ave_sorts,
ceil(parse_calls/greatest(executions,1)) ave_parse_calls,
ceil(Buffer_gets/greatest(executions,1)) ave_buffer_gets,
ceil(rows_processed/greatest(executions,1)) ave_row_proc,
ceil(Serializable_aborts/greatest(executions,1)) ave_ser_aborts
FROM v$sqlarea order by elapsed_time,cpu_time,disk_reads) where rownumber<10
select
(select count(*) from v$session where status='ACTIVE' ) "No Of Active Session",
(select count(*) from v$session where status='INACTIVE') "No Of Inactive Session",
(select count(*) from v$session) "Total Session" from dual
select SEQUENCE#,APPLIED ,to_char(FIRST_TIME, 'DD:MM:YYYY:HH24:MI:SS'),to_char(NEXT_TIME,'DD:MM:YYYY:HH24:MI:SS'), to_char(COMPLETION_TIME, 'DD:MM:YYYY:HH24:MI:SS')from v$archived_log

csisora 26411 17531 0 18:07:03 pts/td 0:00 grep listener*
oracle 343 1 0 03:14:44 ? 0:00 /cbsora/OraHome1/bin/tnslsnr listener_fin2 -inherit
csisora 13131 1 0 01:23:12 ? 0:00 /csisora/OraHome1/bin/tnslsnr listener_csis2 -inheri
csisora 13128 1 0 01:23:12 ? 0:00 /csisora/OraHome1/bin/tnslsnr listener_csis1 -inheri
oracle 340 1 0 03:14:43 ? 15:44 /cbsora/OraHome1/bin/tnslsnr listener_fin1 -inherit
****************************************************
select TABLE_NAME,NUM_ROWS,BLOCKS,AVG_ROW_LEN,to_char(LAST_ANALYZED,'DD:MM:YYYY:HH24:MI:SS') from dba_tables where owner not in ('SYS','SYSTEM') order by NUM_ROWS desc;

ACD_TBLSPC 610.00 450.08 159.92 00073.78

ANALYSED TABLE
exec dbms_stats.gather_table_stats(OWNNAME=>'TBAADM',TABNAME=>'SOL_DATA_DISTRBTN_TABLE',DEGREE=>12,CASCADE=>TRUE)

Best Blogger TemplatesBest Blogger Tips
Best Blogger Gadgets
Posted by Raju Chinthapatla at 10:57:00 AM
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)
If you are facing any issues while copying the Code/Script or any issues with Posts, Please send a mail to OracleApp88@Yahoo.com or message me at @apps88 or +91 905 957 4321 in telegram.
Best Blogger TipsGet Flower Effect