Dear Readers, In this article, we will see the following Daily Tips for Linux. Tip-1 Date: 05-Feb-21 Provided By: Sridhar What is Operating System ? 👉 Operating system is an interface between user and the computer hardware.… Read More
Dear Readers, In this article, we will see the following Install Python Boto3 On AWS EC2. Steps to Follow : Logging to AWS Account Launch Linux Server Connect to Linux EC2 Instance by Using Putty Install Python Package Install Boto3 Package Verify boto3 Package … Read More
Dear Readers, In this article, we will see SQL Server Database Architecture. SQL Server MDF Files In the SQL Server Primary Data Files are the basic data files which are related to the database for the storing and supervision of the information. MDF file in… Read More
Dear Readers, In this article,we will see Download and Install Maven Plugin In Jenkins. Steps to Follow Logging to Jenkins. Download and Install Maven Plugin. Restart Jenkins. Logging to Jenkins. Verify plugin “maven integration” and “green balls”. First,we need to go Jenkins Logging page… Read More
Hello everyone, In this article, we will learn Generating DDL using existing dumpfile. To generate DDL of a schema or table from a dumpfile we use a parameter called SQLFILE. Purpose: SQLFILE is a paramater which can be used in IMPDP utility to generate DDL… Read More
Dear Readers, In this article, we will see the Upgradation and Data Migration in SQL Server. We have to download the Sql server patches from Sql server Builds site. (https://sqlserverbuilds.blogspot.com/) What is Cumulative updates (CU) 👉 Each new CU contains all the fixes that were… 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 Upgradation and Data Migration in SQL Server. Version upgradation 2014 to 2016 SQL 2014 evaluation edition….. Enterprise edition. In SQL server 2014 standard edition to enterprise edition The upgrading the version in SQL server 2014 it… 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
Hello Everyone, In this article , we will learn how to utilize PARFILE parameter in DATAPUMP Exports and Imports. what is parfile ? Parfile is a parameter in datapump exports and imports . The name of the parfile can give in any format but extension… Read More
