Autonomous Transaction Processing In this article, we will know about “ATP” Oracle Autonomous Transaction Processing delivers a self-driving, self-securing, self-repairing database service that can instantly scale to meet the demands of a variety of applications: mission-critical transaction processing, mixed transactions and analytics, IoT, JSON… Read More
HOW TO CONNECT AND ACCESS ATP DATABASE In this article, we will know “HOW TO CONNECT AND ACCESS ATP DATABASE” We can access ATP database through SQL developer for that we have to download wallet file of this particular database 1. Click on the ATP… Read More
Hi Dears, In this article, we will see Install Ubuntu 20.04.1 LTS On Windows.. Steps to Follow Download and Install Oracle VM VirtualBox Download Ubuntu 20.04.1 LTS Create New Virtual Machine Install Ubuntu 20.04.1 LTS in Virtual Machine Install Guest Additions 1. Download and Install… Read More
Dear Readers, In this article, we will see Oracle (SecureCRT) : Temp Tablespace Monitoring Queries. Menubar : TEMP Tabname : Def_temp
|
1 2 3 4 |
set linesize 300 col property_name for a35 col property_value for a20 col description for a45 |
|
1 2 3 4 5 |
SELECT * FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE'; PROPERTY_NAME PROPERTY_VALUE DESCRIPTION ----------------------------------- -------------------- --------------------------------------------- DEFAULT_TEMP_TABLESPACE TEMP Name of default temporary tablespace |
Tabname : TS_Temp_Usage
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
set linesize 300 SELECT A.tablespace_name tablespace, D.mb_total, SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used, D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free FROM v$sort_segment A, ( SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total FROM v$tablespace B, v$tempfile C WHERE B.ts#= C.ts# GROUP BY B.name, C.block_size ) D WHERE A.tablespace_name = D.name GROUP by A.tablespace_name, D.mb_total; TABLESPACE MB_TOTAL MB_USED MB_FREE ------------------------------ ---------- ---------- ---------- TEMP 32767 6 32761 |
Tabname : Temp_Space_Usage_SQLID
|
1 2 3 4 5 6 7 |
set lines 232 pages 1000 column username format a15 column osuser format a10 column module format a35 column program format a20 column Tablespace format a10 |
|
1 2 3 4 5 6 7 8 9 10 |
select * from (SELECT S.sid ,S.serial# , S.username, S.osuser, S.module,s.SQL_ID, P.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace, COUNT(*) statements FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P WHERE T.session_addr = S.saddr AND S.paddr = P.addr AND T.tablespace = TBS.tablespace_name GROUP BY S.sid, S.serial#, S.username, S.osuser,s.sql_id,P.spid, S.module, P.program, TBS.block_size, T.tablespace ORDER BY username) where mb_used > 1000; |
Tabname : Temp_sort_users
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 |
SET TERMOUT OFF; COLUMN current_instance NEW_VALUE current_instance NOPRINT; SELECT rpad(instance_name, 17) current_instance FROM v$instance; SET TERMOUT ON; PROMPT PROMPT +------------------------------------------------------------------------+ PROMPT | Report : Temporary Sort Users | PROMPT | Instance : ¤t_instance | PROMPT +------------------------------------------------------------------------+ SET ECHO OFF SET FEEDBACK 6 SET HEADING ON SET LINESIZE 180 SET PAGESIZE 50000 SET TERMOUT ON SET TIMING OFF SET TRIMOUT ON SET TRIMSPOOL ON SET VERIFY OFF CLEAR COLUMNS CLEAR BREAKS CLEAR COMPUTES COLUMN instance_name FORMAT a9 HEADING 'Instance' COLUMN tablespace_name FORMAT a15 HEADING 'Tablespace Name' COLUMN sid FORMAT 99999 HEADING 'SID' COLUMN serial_id FORMAT 99999999 HEADING 'Serial ID' COLUMN session_status FORMAT a9 HEADING 'Status' COLUMN oracle_username FORMAT a18 HEADING 'Oracle User' COLUMN os_username FORMAT a18 HEADING 'O/S User' COLUMN os_pid FORMAT a8 HEADING 'O/S PID' COLUMN session_terminal FORMAT a10 HEADING 'Terminal' TRUNC COLUMN session_machine FORMAT a30 HEADING 'Machine' TRUNC COLUMN session_program FORMAT a20 HEADING 'Session Program' TRUNC COLUMN contents FORMAT a9 HEADING 'Contents' COLUMN extents FORMAT 999,999,999 HEADING 'Extents' COLUMN blocks FORMAT 999,999,999 HEADING 'Blocks' COLUMN bytes FORMAT 999,999,999,999 HEADING 'Bytes' COLUMN segtype FORMAT a12 HEADING 'Segment Type' BREAK ON instance_name SKIP PAGE |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
SELECT i.instance_name instance_name , t.tablespace tablespace_name , s.sid sid , s.serial# serial_id , s.status session_status , s.username oracle_username , s.osuser os_username , p.spid os_pid , s.program session_program , t.contents contents , t.segtype segtype , (t.blocks * c.value) bytes FROM gv$instance i , gv$session s , gv$process p , gv$sort_usage t , (select value from v$parameter where name = 'db_block_size') c WHERE s.inst_id = p.inst_id AND p.inst_id = i.inst_id AND t.inst_id = i.inst_id AND s.inst_id = i.inst_id AND s.saddr = t.session_addr AND s.paddr = p.addr ORDER BY i.instance_name , s.sid; Instance Tablespace Name SID Serial ID Status Oracle User O/S User O/S PID Session Program Contents Segment Type Bytes --------- --------------- ------ --------- --------- ------------------ ------------------ -------- -------------------- --------- ------------ ---------------- ORCL11 TEMP 1513 59797 ACTIVE oracle 25309 oracle@server1234567 TEMPORARY LOB_DATA 5,242,880 TEMP 1513 59797 ACTIVE oracle 25309 oracle@server1234567 TEMPORARY LOB_INDEX 1,048,576 Instance Tablespace Name SID Serial ID Status Oracle User O/S User O/S PID Session Program Contents Segment Type Bytes --------- --------------- ------ --------- --------- ------------------ ------------------ -------- -------------------- --------- ------------ ---------------- ORCL12 TEMP 1513 40323 ACTIVE oracle 13260 oracle@serverAB34567 TEMPORARY LOB_DATA 5,242,880 TEMP 1513 40323 ACTIVE oracle 13260 oracle@serverAB34567 TEMPORARY LOB_INDEX 1,048,576 |
Tabname : Temp_Space_Usage_SQLID_Login_Active_InActive
|
1 2 3 4 5 6 7 |
set lines 232 pages 1000 column username format a15 column osuser format a10 column module format a35 column program format a30 column Tablespace format a10 |
|
1 2 3 4 5 6 7 8 9 10 |
select * from (SELECT S.sid ,S.serial# , S.username, S.osuser, S.module,s.SQL_ID, P.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,s.logon_time,s.status, COUNT(*) statements FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P WHERE T.session_addr = S.saddr AND S.paddr = P.addr AND T.tablespace = TBS.tablespace_name GROUP BY S.sid, S.serial#, S.username, S.osuser,s.sql_id,P.spid, S.module, P.program, TBS.block_size, T.tablespace,s.logon_time,s.status ORDER BY s.logon_time) where mb_used > 10; |
Tabname : Addspace… Read More
Dear Readers, In this article, we will see the following Oracle (SecureCRT) : Important Queries for UNDO Management. MenuBar : UNDO Tab Name : Undo_Usage
|
1 2 |
set linesize 200 pagesize 200 compute sum of "Total Space in GB" on report |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
select tablespace_name, status segment_status, count(extent_id) "Extent Count", sum(blocks) "Total Blocks", sum(blocks)*8/(1024) "Total Space in MB" from dba_undo_extents group by tablespace_name, status order by tablespace_name / TABLESPACE_NAME SEGMENT_S Extent Count Total Blocks Total Space in MB ------------------------------ --------- ------------ ------------ ----------------- UNDOTBS1 EXPIRED 173 10264 80.1875 UNDOTBS1 UNEXPIRED 68 5944 46.4375 UNDOTBS2 EXPIRED 177 9816 76.6875 UNDOTBS2 UNEXPIRED 76 6608 51.625 |
Tab Name : Undo_Contetion
|
1 2 3 4 |
SET TERMOUT OFF; COLUMN current_instance NEW_VALUE current_instance NOPRINT; SELECT rpad(instance_name, 17) current_instance FROM v$instance; SET TERMOUT ON; |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
PROMPT PROMPT +------------------------------------------------------------------------+ PROMPT | Report : Undo Contention | PROMPT | Instance : ¤t_instance | PROMPT +------------------------------------------------------------------------+ SET ECHO OFF SET FEEDBACK 6 SET HEADING ON SET LINESIZE 180 SET PAGESIZE 50000 SET TERMOUT ON SET TIMING OFF SET TRIMOUT ON SET TRIMSPOOL ON SET VERIFY OFF CLEAR COLUMNS CLEAR BREAKS CLEAR COMPUTES COLUMN instance_name FORMAT a10 HEAD 'Instance' COLUMN class FORMAT a18 HEADING 'Class' COLUMN ratio HEADING 'Wait Ratio' BREAK ON instance_name SKIP 2 |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
SELECT i.instance_name instance_name , w.class class , ROUND(100*(w.count/SUM(s.value)),8) ratio FROM gv$instance i , gv$waitstat w , gv$sysstat s WHERE i.inst_id = w.inst_id AND i.inst_id = s.inst_id AND w.class IN ( 'system undo header' , 'system undo block' , 'undo header' , 'undo block' ) AND s.name IN ('db block gets', 'consistent gets') GROUP BY i.instance_name , w.class , w.count ORDER BY i.instance_name , w.class; Instance Class Wait Ratio ---------- ------------------ ---------- ORCL11 system undo block 0 system undo header 0 undo block .0001595 undo header .0002848 ORCL12 system undo block 0 system undo header 0 undo block .00018723 undo header .00030043 |
Tab Name : Undo_Trend_Today
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
set linesize 300 SELECT TO_CHAR(BEGIN_TIME, 'MM/DD/YYYY HH24:MI:SS') BEGIN_TIME, TO_CHAR(END_TIME, 'MM/DD/YYYY HH24:MI:SS') END_TIME, UNDOTSN, UNDOBLKS, TXNCOUNT, MAXCONCURRENCY AS "MAXCON", MAXQUERYLEN, TUNED_UNDORETENTION FROM v$UNDOSTAT WHERE rownum <= 144; Instance Class Wait Ratio ---------- ------------------ ---------- ORCL11 system undo block 0 system undo header 0 undo block .0001595 undo header .0002848 ORCL12 system undo block 0 system undo header 0 undo block .00018723 undo header .00030044 |
Tab Name : Undo_Usage_For_Sessions
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
set linesize 300 set pagesize 300 col sid_serial for a30 SELECT TO_CHAR(s.sid)||','||TO_CHAR(s.serial#) sid_serial, NVL(s.username, 'None') orauser, s.program, r.name undoseg, t.used_ublk * TO_NUMBER(x.value)/1024||'K' "Undo" FROM sys.v_$rollname r, sys.v_$session s, sys.v_$transaction t, sys.v_$parameter x WHERE s.taddr = t.addr AND r.usn = t.xidusn(+) AND x.name = 'db_block_size'; |
Tab… Read More
Dear Readers, In this article, we will see GoldenGate 12c setup for Oracle Container Database. Separate user for Extract and Replication need to be created. User for Extract should be common user connected to Root Container in order to interact with the logmining server. For… Read More
Dear Readers, In this article, we will see UNDO segments not Releasing Space . UNDERSTANDING: There are three status of UNDO segments: ACTIVE: Currently in Used EXPIRED: Extend cannot be Used UNEXPIRED: unexpired extents were reused The UNDO records are not deleted when they are… Read More
Dear Readers, In this article, we will see Performance Investigation(Database / SQL) . UNDERSTANDING: Database running slow or application running slow. These are the basic and practical issue come in day today activity. Before Investigation have below answers from application or client or users 👉… Read More
Dear Readers, In this article, we will see DB scattered Read Wait Event. DB scattered Read wait event Adjacent blocks are read from disk allowing Oracle to read many blocks by performing large I/Os. Thus, from the storage perspective, db file scattered read is a… Read More
Dear Readers, In this article, we will see Direct Path ReadWait Event. Direct Path Read Wait event: The Direct Path Read Temp wait events are an access path in which multiple Oracle blocks are read directly to the Oracle process memory without being read into… Read More
