Code.GeekInterview.com
 
 

Oracle Code Base

 • Compare data in tables of the local Oracle schema
 • Converting Rows to Columns
 • Database Health check
 • Encryt and Decrypt Text messages
 • Function To Convert Amount to Words
 • Oracle Calendar SQL
 • Oracle Database Cold backup Unix
 • Oracle Database Growth Report
 • Partition Management package
 • Report Generator
 • SQL to Find Experience of an Employee
 
Code Samples Oracle
 

Database Health check


Code ResourceAuthor: Mohit Sharma  

Difficulty Level:

Published: 20th Dec 2006   Read: 13792 times  

Filed in: Oracle
Add Comment


 

 

Sponsored Links


 

 

Check the important areas of an Oracle instance. Save it as .sql and executed as sys.

 


Sample Code
  1.  
  2. prompt
  3. prompt
  4. prompt ######################################################################
  5. prompt
  6. prompt
  7. prompt BY Mohit sharma
  8. prompt mohit.sharma@india.com
  9. prompt
  10. prompt
  11. prompt
  12. prompt #####################################################################
  13. SET linesize 132
  14. SET pagesize 1000
  15. spool report.txt
  16. prompt #### Up TIME ####
  17. SELECT 'Hostname      : ' || host_name
  18.       ,'Instance Name : ' || instance_name
  19.  
  20.       ,'Started At    : ' || TO_CHAR(startup_time,'DD-MON-YYYY HH24:MI:SS') stime
  21.  
  22.       ,'Uptime        : ' || FLOOR(SYSDATE - startup_time) || ' days(s) ' ||
  23.  
  24.        TRUNC( 24*((sysdate-startup_time) -
  25.  
  26.        TRUNC(sysdate-startup_time))) || ' hour(s) ' ||
  27.  
  28.        MOD(TRUNC(1440*((sysdate-startup_time) -
  29.  
  30.        TRUNC(sysdate-startup_time))), 60) ||' minute(s) ' ||
  31.  
  32.        MOD(TRUNC(86400*((sysdate-startup_time) -
  33.  
  34.        TRUNC(sysdate-startup_time))), 60) ||' seconds' uptime
  35.  
  36. FROM sys.v_$instance;
  37. prompt
  38. prompt
  39. prompt ################################################################################
  40. prompt .>>>>>>    Object status
  41. SELECT object_type,status,COUNT(*) cnt FROM user_objects  GROUP BY  object_type,status;
  42. prompt
  43. prompt
  44. prompt ################################################################################
  45. prompt .>>>>>>  Invalid objects
  46. SELECT owner, object_type, SUBSTR(object_name,1,30) object_name
  47. FROM sys.dba_objects
  48. WHERE status='INVALID'
  49. ORDER BY object_type;
  50. prompt
  51. prompt
  52. prompt ################################################################################
  53. prompt .>>>>>>    Recover files
  54. SELECT * FROM sys.v_$recover_file;
  55. prompt
  56. prompt
  57. prompt ################################################################################
  58. prompt .>>>>>> partitioned TABLE
  59. SELECT table_name FROM user_tables WHERE partitioned='YES';
  60. prompt
  61. prompt
  62. prompt ################################################################################
  63. prompt .>>>>>> Free SPACE IN TABLESPACE
  64. SELECT tablespace_name,SUM(bytes/1024/1024) FROM sys.dba_free_space GROUP BY tablespace_name;
  65. prompt
  66. prompt
  67. prompt ################################################################################
  68. prompt .>>>>>>Max Extents Status
  69. SELECT segment_name, segment_type, extents, max_extents FROM sys.dba_segments WHERE max_extents-extents<100;
  70. prompt
  71. prompt
  72. prompt ################################################################################
  73. prompt .>>>>>>  ANY partitioned Object approaching TO MAX extents
  74. SELECT PARTITION_NAME,EXTENTS,MAX_EXTENTS,NEXT_EXTENT,max_extents-NVL(next_extent,0) FROM sys.dba_segments
  75. WHERE max_extents-NVL(next_extent,0) < 1000
  76. AND partition_name IS NOT NULL;
  77. prompt
  78. prompt
  79. prompt ################################################################################
  80. prompt .>>>>>>  Block Contention
  81. SELECT class,SUM(COUNT) total_waits, SUM(TIME) total_time FROM v$waitstat GROUP BY class;
  82. prompt
  83. prompt
  84. prompt ################################################################################
  85. prompt .>>>>>> Latch Contention
  86. SELECT a.name,100.*b.sleeps/b.gets ratio1,100.*b.immediate_misses/DECODE((b.immediate_misses+b.immediate_gets),0,1) ratio2
  87. FROM v$latchname a, v$latch b WHERE
  88. a.latch# = b.latch# AND b.sleeps > 0;
  89. prompt
  90. prompt
  91. prompt ################################################################################
  92. prompt .>>>>>> Top 10 Queries USING more disk reads
  93. SELECT disk_reads,sql_text FROM v$sqlarea WHERE disk_reads>10000 AND ROWNUM < 11 ORDER BY disk_reads DESC;
  94. prompt
  95. prompt
  96. prompt ################################################################################
  97. prompt .>>>>>> Top 10 Queries USING more buffer reads
  98. SELECT  buffer_gets,sql_text FROM v$sqlarea WHERE buffer_gets>10000 AND ROWNUM < 11 ORDER BY buffer_gets DESC;
  99. prompt
  100. prompt
  101. prompt ################################################################################
  102. prompt .>>>>>> buffer cache hit ratio
  103. SELECT (1- (SUM(DECODE(a.name,'physical reads',VALUE,0)))/
  104.  
  105.         (SUM(DECODE(a.name,'db block gets',VALUE,0)) +
  106.  
  107.         SUM(DECODE(a.name,'consistent gets',VALUE,0)))) * 100 pct
  108.  
  109.         FROM v$sysstat a;
  110. prompt
  111. prompt
  112. prompt ################################################################################
  113. prompt .>>>>>> Reloads/Pins IN v$librarycache
  114. SELECT SUM(pins) "executions",SUM(reloads) "cache Misses",SUM(reloads)/SUM(pins)*100 "Ratio" FROM v$librarycache;
  115. prompt
  116. prompt
  117. prompt ################################################################################
  118. prompt .>>>>>> Get miss ratio FROM v$rowcache  
  119. SELECT (SUM(getmisses)/SUM(gets)) * 100 "Hit Ratio" FROM v$rowcache;
  120. prompt
  121. prompt
  122. prompt ################################################################################
  123. prompt .>>>>>> Redo LOG SPACE request ratio IN v$systat
  124. SELECT (req.VALUE*5000)/entries.VALUE "ratio"
  125. FROM v$sysstat req,v$sysstat entries
  126. WHERE req.name='redo log space requests' AND entries.name='redo entries' ;
  127. prompt
  128. prompt
  129. prompt ################################################################################
  130. prompt .>>>>>> RBS Contention
  131. SELECT SUM(waits)/SUM(gets)*100 FROM v$rollstat;
  132. prompt
  133. prompt
  134. prompt ################################################################################
  135. prompt .>>>>>> sorts stats
  136. SELECT * FROM v$sysstat WHERE name LIKE '%sorts%';
  137. prompt
  138. prompt
  139. prompt ################################################################################
  140. prompt .>>>>>> datafiles I/O
  141. SELECT name, phyrds, phywrts FROM v$datafile df, v$filestat fs WHERE df.FILE# = fs.FILE# ORDER BY phyrds;  
  142. prompt
  143. prompt
  144. prompt
  145. prompt
  146. spool off
  147.  
Copyright GeekInterview.com


Next Article: Oracle Database Cold backup Unix


 

Latest Code Samples

 ♦ Converting Rows to Columns
 ♦ Oracle Database Growth Report
 ♦ Function To Convert Amount to Words
 ♦ Report Generator
 ♦ Database Health check
 ♦ Oracle Database Cold backup Unix
 ♦ SQL to Find Experience of an Employee
 ♦ Oracle Calendar SQL
 ♦ Partition Management package
 ♦ Encryt and Decrypt Text messages
 

Popular Code Samples

 ♦ Function To Convert Amount to Words
 ♦ Converting Rows to Columns
 ♦ Oracle Calendar SQL
 ♦ Database Health check
 ♦ Oracle Database Growth Report
 ♦ Encryt and Decrypt Text messages
 ♦ Partition Management package
 ♦ SQL to Find Experience of an Employee
 ♦ Oracle Database Cold backup Unix
 ♦ Compare data in tables of the local Oracle schema
 

Related Code Samples

 » Oracle Database Growth Report
 » Recover Tables Data from Corrupt MS Access Database
 » Check if the input number is a Amstrong number or not
 » Oracle Database Cold backup Unix
 » Check for Special characters
 

Post Your Comment:

Members Please Login
Your Name:*
e-mail ID:(required for notification)*
Image Verification: 
 
 Subscribe    



Comments

Hi
I think you have put this in a wrong place.
Comment posted by: jamesravid on 2006-12-22T01:36:05

Popular Coders

# Coder NameHits
1. jamesravid37445
2. krishnaindia200731196
3. Jim.Anderson27790
4. Mohit Sharma20063
5. Beena13473
6. jainsourabh28645
7. Satheesh5686
8. SriramKrishna4771

Active Coders

# Coder NameCodes
1. jamesravid3
2. Mohit Sharma2
3. Satheesh1
4. jainsourabh21
5. SriramKrishna1
6. krishnaindia20071
7. Beena1
8. Jim.Anderson1

Refined Tags