Oracle DBA Queries

Dear Reader,
I am writing an article on important DBA queries for Oracle Database. This is basically for my reference however 
you will find it useful.

Oracle DBA Queries:

1) To check latest SQLs executed in DB, if Oracle AWR Report is not giving proper results:
    SELECT LAST_LOAD_TIME, SQL_TEXT FROM  V$SQL ORDER BY LAST_LOAD_TIME DESC;
    SELECT * FROM V$SQL WHERE SQL_TEXT LIKE '%SELECT * FROM ENSTAGE_TABLE%' ORDER BY LAST_ACTIVE_TIME DESC;

2) Check All Databases (Table Spaces) sizes and used memory: 
   If you have the proper permission, below query will execute:

    SELECT DF.TABLESPACE_NAME "TABLESPACE", TOTALUSEDSPACE "USED MB", (DF.TOTALSPACE - TU.TOTALUSEDSPACE) "FREE MB",
    DF.TOTALSPACE "TOTAL MB", ROUND(100 * ( (DF.TOTALSPACE - TU.TOTALUSEDSPACE)/ DF.TOTALSPACE)) "PCT. FREE"
    FROM (SELECT TABLESPACE_NAME,ROUND(SUM(BYTES) / 1048576) TOTALSPACE FROM DBA_DATA_FILES 
    GROUP BY TABLESPACE_NAME) DF, (SELECT ROUND(SUM(BYTES)/(1024*1024)) TOTALUSEDSPACE, TABLESPACE_NAME
    FROM DBA_SEGMENTS GROUP BY TABLESPACE_NAME) TU WHERE DF.TABLESPACE_NAME = TU.TABLESPACE_NAME;
    

3) Table Size in Database in Megabytes:
    SELECT segment_name "Table_Name", bytes/1024/1024 "Megabytes", Blocks FROM USER_SEGMENTS
    WHERE segment_type = 'TABLE' AND segment_name IN ('ENSTAGE_TABLE')
    ORDER BY segment_name, tablespace_name;
    
4) List of Table_Spaces available (remove Where clause):
   SELECT * FROM DBA_TABLESPACES WHERE TABLESPACE_NAME = 'EGRD_DEEPAK';

5) Count of Tables, Indexes etc in a Table_Space:
   SELECT SEGMENT_TYPE, COUNT(SEGMENT_TYPE) FROM USER_SEGMENTS WHERE TABLESPACE_NAME='EGRD_DEEPAK' GROUP BY SEGMENT_TYPE;

6) To know if your table Spaces or tables are compressed:
   SELECT DEF_TAB_COMPRESSION, COMPRESS_FOR FROM DBA_TABLESPACES WHERE TABLESPACE_NAME = 'EGRD_DEEPAK';   
   Output: DISABLED  <null>  (Means not compressed).
   
   SELECT COMPRESSION, COMPRESS_FOR FROM DBA_TABLES WHERE TABLE_NAME = 'ENSTAGE_TABLE';
   Output: DISABLED  <null>  (Means not compressed).

7) To compress a table in Oracle using OLTP:
   ALTER TABLE ENSTAGE_TABLE move COMPRESS FOR OLTP;   
   Output: ENABLED   OLTP   (Means not compressed).
   
   After compressing, use Above Query(Point number: 2) to see the Size of Table, now it should be reduced.
   
   If you want to revert back the changes: means no-compression, use below query:
   ALTER TABLE OBSERVATION_201312 NOCOMPRESS;
   
8) To know BIND variables passed while executing a DML Query via Application:
   SELECT SQL_ID, SQL_TEXT FROM V$SQL WHERE SQL_TEXT LIKE 'UPDATE MGMT_TARGETS SET %';
   
   //Note-down this SQL_ID and replace the value in next query.
   SELECT NAME, VALUE_STRING, DATATYPE_STRING, LAST_CAPTURED FROM V$SQL_BIND_CAPTURE WHERE SQL_ID='57pfs5p8xc07w';

   To know complete details of the above query:
   SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('57pfs5p8xc07w',NULL,NULL,'ADVANCED'));
   
   And of course you can join these two queries in one step:
   SELECT NAME, VALUE_STRING, DATATYPE_STRING, LAST_CAPTURED FROM V$SQL_BIND_CAPTURE A, V$SQL B
        WHERE A.SQL_ID = B.SQL_ID AND SQL_TEXT LIKE 'UPDATE HISTORY_201312 SET FINAL_STATUS%'
        ORDER BY LAST_CAPTURED;

9) Check Indexes or Index validation after Compression: 
   SELECT INDEX_NAME,STATUS FROM DBA_INDEXES WHERE TABLE_NAME='MY_TABLE_NAME';

   Rebuild unused/invalid index: 
   ALTER INDEX INDEX NAME REBUILD ONLINE;


At last giving a complete example again to see Query Execution Plan, Hint and CPU/IO costs:

EXPLAIN PLAN for
    SELECT /*+ index(TABLE_NAME INDEX_CARD_NO) */  * FROM TABLE_NAME WHERE 
    TIME_STAMP>=To_Date('10/01/2014 00:04:20','dd/mm/yyyy hh24:mi:ss') AND 
    TIME_STAMP<=To_Date('15/01/2014 17:04:20','dd/mm/yyyy hh24:mi:ss')
    ORDER BY CARD_NO ASC; 

    SELECT LAST_LOAD_TIME, SQL_ID,SQL_TEXT FROM V$SQL WHERE SQL_TEXT LIKE '%TABLE_NAME%' ORDER BY 
    LAST_ACTIVE_TIME DESC;   

    SELECT PLAN_ID,CPU_COST,IO_COST,TIME,BYTES,COST,DEPTH,OPTIMIZER,OBJECT_TYPE,OBJECT_NAME,OBJECT_OWNER,
    OPTIONS,OPERATION,TIMESTAMP FROM PLAN_TABLE;

-----------------------------END------------------------------------

AWR Report in Oracle

How to take AWR Report in Oracle:
AWR: Automatic Workload Repository
The AWR is used to collect performance statistics including:
1. Wait events used to identify performance problems.
2. Time model statistics indicating the amount of DB time associated with a process from the V$SESS_TIME_MODEL and V$SYS_TIME_MODEL views.
3. Active Session History (ASH) statistics from the V$ACTIVE_SESSION_HISTORY view.
4. Some system and session statistics from the V$SYSSTAT and V$SESSTAT views.
5. Object usage statistics.
6. Resource intensive SQL statements.

The repository is a source of information for several other Oracle 10g features including:
1. Automatic Database Diagnostic Monitor
2. SQL Tuning Advisor
3. Undo Advisor
4. Segment Advisor
So basically you need to run SNAP_SHOT two times to get AWR Report in between. One before your application
runs and one after your application runs.
Oracle provide two scripts to produce workload repository reports (awrrpt.sql and awrrpti.sql). They are similar in
format to the statspack reports and give the option of HTML or plain text formats. The two reports give essential
the same output but the awrrpti.sql allows you to select a single instance.

How To Take AWR Report:
Login to Unix Systems where Oracle is running:
oracle@DBDomain:~$ sqlplus / as SYSDBA
SQL*Plus: Release 11.2.0.4.0 Production on Tue Oct 22 14:42:42 2013
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL>EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT;
SQL>SELECT SNAP_ID, DBID from DBA_HIST_SNAPSHOT order by SNAP_ID;
//Remember only the last record (last SNAP_ID, it will be a numeric value).
...........................................
RUNNNNNNNNNNNNN YOUR APPLICATION Who populates DATA into DBBBBBBBBBBBBBB..........
...........................................
SQL>EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT;
SQL>SELECT SNAP_ID, DBID from DBA_HIST_SNAPSHOT order by SNAP_ID;
//Remember the last two records (one SNAP_ID before your application run, second after run).
//Now Run below commands:

SQL> @$ORACLE_HOME/rdbms/admin/awrrpt.sql
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
2835430333 ENSDB 1 ENSDB

Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type: html
Type Specified: html

Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ -------- ------------ ------------ ------------
* 2835430333 1 ENSDB ENSDB DBDomain

Using 2835430333 for database Id
Using 1 for instance number

Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed. Pressing without
specifying a number lists all completed snapshots.

Enter value for num_days: 2

Listing the last 2 days of Completed Snapshots
Instance DB Name Snap Id Snap Started Snap Level
------------ ------------ --------- ------------------ -----
ENSDB ENSDB 119 22 Oct 2013 13:00 1
120 22 Oct 2013 14:01 1
121 22 Oct 2013 14:39 1

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 120
Begin Snapshot Id specified: 120

Enter value for end_snap: 121
End Snapshot Id specified: 121


Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_120_121.html. To use this name,
press to continue, otherwise enter an alternative.

Enter value for report_name: /home/oracle/DeepakModi/AWR_Report_22_OCT_SUCCESSFUL_100TPS.html
===============================================================
That's it. File has been created as "/home/oracle/DeepakModi/AWR_Report_22_OCT_SUCCESSFUL_100TPS.html".

===================END, Thank You.=================================

CMMI Levels in Industry

CMMI – Capability Maturity Model Integration

Dear Reader,
Today I am writing details about CMMI level certifications in Software Industry. You may be knowing this but
this is for my reference only. For software development processes, the CMM has been superseded by Capability 
Maturity Model Integration (CMMI), though the CMM continues to be a general theoretical process capability model 
used in the public domain. 

Within each of these maturity levels are Key Process Areas (KPAs) which characterise that level, and for each 
KPA there are five definitions identified:
1. Goals
2. Commitment
3. Ability
4. Measurement
5. Verification


The CMM provides a theoretical continuum along which process maturity can be developed incrementally from one 
level to the next. Skipping levels is not allowed/feasible.

NOTE: The CMM was originally intended as a tool to evaluate the ability of government contractors to perform a 
contracted software project. It has been used for and may be suited to that purpose, but critics pointed out that 
process maturity according to the CMM was not necessarily mandatory for successful software development. 

There were/are real-life examples where the CMM was arguably irrelevant to successful software development, and 
these examples include many Shrinkwrap companies (also called commercial-off-the-shelf or "COTS" firms or software 
package firms). Such firms would have included, for example, Claris, Apple, Symantec, Microsoft, and Lotus. Though 
these companies may have successfully developed their software, they would not necessarily have considered or defined 
or managed their processes as the CMM described as level 3 or above, and so would have fitted level 1 or 2 of the model. 
This did not - on the face of it - frustrate the successful development of their software.

Level 1 - Ad hoc (Chaotic)
    It is characteristic of processes at this level that they are (typically) undocumented and in a state of dynamic 
    change, tending to be driven in an ad hoc, uncontrolled and reactive manner by users or events. This provides a 
    chaotic or unstable environment for the processes.

Level 2 - Repeatable
    It is characteristic of processes at this level that some processes are repeatable, possibly with consistent results. 
    Process discipline is unlikely to be rigorous, but where it exists it may help to ensure that existing processes are 
    maintained during times of stress.
    
Level 3 - Defined
    It is characteristic of processes at this level that there are sets of defined and documented standard processes 
    established and subject to some degree of improvement over time. These standard processes are in place (i.e., they 
    are the AS-IS processes) and used to establish consistency of process performance across the organization.
    
Level 4 - Managed
    It is characteristic of processes at this level that, using process metrics, management can effectively control the 
    AS-IS process (e.g., for software development ). In particular, management can identify ways to adjust and adapt the 
    process to particular projects without measurable losses of quality or deviations from specifications. Process 
    Capability is established from this level.
    
Level 5 - Optimized
    It is a characteristic of processes at this level that the focus is on continually improving process performance through 
    both incremental and innovative technological changes/improvements.
    
At maturity level 5, processes are concerned with addressing statistical common causes of process variation and changing the 
process (for example, to shift the mean of the process performance) to improve process performance. This would be done at 
the same time as maintaining the likelihood of achieving the established quantitative process-improvement objectives.

Other definitions of CMM(Capability Maturity Model):
    CMM is an industry-standard model for defining and measuring the maturity of a software company's development process and 
    for providing direction on what they can do to improve their software quality. It was developed by the software development 
    community along with the software engineering institute(SEI).
    
CMM software Maturity Levels:
Level1: Initial: The s/w development process at this level are adhoc and often chaotic. The project's success depends on heroes 
and luck. There are no general practices for planning monitoring or Controlling the process. It's impossible to predict the time 
and cost to develop the software. The test process is just as adhoc as the rest of the process.

Level2: Repeatable: This maturity level is best described as project level thinking. Basic project management processes are in 
place to track the cost schedule functionality and quality of the product. Lessons learned from previous similar projects are applied. 
There is a sense of discipline. Basic software testing practices such as test plans and test cases are used.

Level3: Defined: Organizational not just project specific thinking comes in to play at this level. Common management and 
engineering activities are standardized and documented. These standards are adapted and approved for use on different projects. 
The rules are not thrown out when things get stressful. Test documents and plans are reviewed and approved before testing begins. 
The test group is independent form developers. The test results are used to determine when the s/w is ready.

Level4: Managed: At this maturity level the organizations process is under statistical control. Product quality is specified 
quantitatively beforehand (for example this product wont release until it has fewer than 0.5 defects per 1 000 lines of code) 
and the s/w isn't released until that goal is met. Details of the development process and the s/w quality are collected over 
the projects development and adjustments are made to correct deviations and to keep the project on plan.

Level5: Optimizing: This level is called optimizing (not optimized) because it's continually improving from level 4. New 
technologies and processes are attempted the results are measured and both incremental and revolutionary changes are instituted 
to achieve even better quality levels. Just when everyone thinks the best has been obtained. The crank is turned one more time and 
the next level of improvement is obtained.

-------------------END--------------------------

Tomcat Users

Dear reader, Writing a very basic code for Roles and Permissions for Tomcat users. When you download Apache Tomcat and run the App, you are being asked a User Name and password to see Apps currently deployed in Tomcat. You need to edit "tomcat-users.xml" in "TOMCAT_INSTALLED_DIRECTORY/conf" directly. Replace the existing file with below code and restart tomcat.

<?xml version='1.0' encoding='utf-8'?>
<tomcat-users>
  <role rolename="tomcat"/>
  <role rolename="role1"/>
  <role rolename="admin"/>
  <role rolename="manager"/>
  <user username="tomcat" password="tomcat" roles="tomcat"/>
  <user username="both" password="tomcat" roles="tomcat,role1"/>
  <user username="role1" password="tomcat" roles="role1"/>
  <user username="admin" password="admin" roles="admin,manager"/>  
  <role rolename="manager-gui"/>
  <user username="admin" password="manager" roles="manager-gui"/>
</tomcat-users>

Oracle Query to see Used and Free TableSpace Size

Dear friend,
Below query will reveal the "Used Space" and "Free Space" in all the Tablespaces present in your database:

SELECT DF.TABLESPACE_NAME "TABLESPACE",
      DF.BYTES / (1024 * 1024) "SIZE (MB)",
      SUM(FS.BYTES) / (1024 * 1024) "FREE (MB)",
      NVL(ROUND(SUM(FS.BYTES) * 100 / DF.BYTES),1) "% FREE",
      ROUND((DF.BYTES - SUM(FS.BYTES)) * 100 / DF.BYTES) "% USED"
 FROM DBA_FREE_SPACE FS,
      (SELECT TABLESPACE_NAME,SUM(BYTES) BYTES
         FROM DBA_DATA_FILES
        GROUP BY TABLESPACE_NAME) DF
WHERE FS.TABLESPACE_NAME (+)  = DF.TABLESPACE_NAME
GROUP BY DF.TABLESPACE_NAME,DF.BYTES;


To know the space used by a Table, Index or anything use below query:

SELECT SEGMENT_TYPE,BYTES/1024/1024 "SIZE IN MB" FROM DBA_SEGMENTS WHERE SEGMENT_NAME = 'INDEX_NAME_OF_TABLE';

SELECT SEGMENT_TYPE,BYTES/1024/1024 "SIZE IN MB" FROM DBA_SEGMENTS WHERE SEGMENT_NAME = 'TABLE_NAME';