Thursday, December 5, 2013

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