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