Secure CRT – Page 2

Oracle (SecureCRT) : Important Queries for UNDO Management

Dear Readers, In this article, we will see the following Oracle (SecureCRT) : Important Queries for UNDO Management. MenuBar : UNDO Tab Name : Undo_Usage

set linesize 200 pagesize 200 
compute sum of "Total Space in GB" on report

1 2	set linesize 200 pagesize 200 compute sum of "Total Space in GB" on report

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

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

SET TERMOUT OFF; 
COLUMN current_instance NEW_VALUE current_instance NOPRINT; 
SELECT rpad(instance_name, 17) current_instance FROM v$instance; 
SET TERMOUT ON;

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   : Undo Contention                                             | 
PROMPT | Instance : &current_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

PROMPT

PROMPT +------------------------------------------------------------------------+

PROMPT | Report : Undo Contention |

PROMPT | Instance : &current_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

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

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

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

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

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

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

Oracle : Important ASM Commands

4 years ago by Vinod SiramSecure CRT

Dear Readers, In this article, we will see the following Important ASM Commands. Tab Name : asm_free

set linesize 300
col name for a30

1 2	set linesize 300 col name for a30

select NAME, ALLOCATION_UNIT_SIZE, STATE, TYPE, TOTAL_MB, FREE_MB,(FREE_MB/TOTAL_MB)*100 PCt_FREE from v$asm_diskgroup order by PCt_FREE ; 

NAME                           ALLOCATION_UNIT_SIZE STATE       TYPE     TOTAL_MB    FREE_MB   PCT_FREE
------------------------------ -------------------- ----------- ------ ---------- ---------- ----------
REDO01                                        4194304 CONNECTED   HIGH      3051520    2657044 87.0728031
DATA01                                        4194304 CONNECTED   NORMAL   21368320   18917476 88.5304788
RECO01                                        4194304 CONNECTED   NORMAL    9154560    8677040 94.7838017

select NAME, ALLOCATION_UNIT_SIZE, STATE, TYPE, TOTAL_MB/1024 TOTAL_GB ,FREE_MB/1024 FREE_GB,(FREE_MB/TOTAL_MB)*100 PCt_FREE from v$asm_diskgroup order by PCt_FREE ; 

SQL> 
NAME                           ALLOCATION_UNIT_SIZE STATE       TYPE     TOTAL_GB    FREE_GB   PCT_FREE
------------------------------ -------------------- ----------- ------ ---------- ---------- ----------
REDO01                                        4194304 CONNECTED   HIGH         2980 2594.76953 87.0728031
DATA01                                        4194304 CONNECTED   NORMAL    20867.5 18474.0977 88.5304788
RECO01                                        4194304 CONNECTED   NORMAL       8940 8473.67188 94.7838017

select NAME, ALLOCATION_UNIT_SIZE, STATE, TYPE, TOTAL_MB, FREE_MB,(FREE_MB/TOTAL_MB)*100 PCt_FREE from v$asm_diskgroup order by PCt_FREE ;

NAME ALLOCATION_UNIT_SIZE STATE TYPE TOTAL_MB FREE_MB PCT_FREE

------------------------------ -------------------- ----------- ------ ---------- ---------- ----------

REDO01 4194304 CONNECTED HIGH 3051520 2657044 87.0728031

DATA01 4194304 CONNECTED NORMAL 21368320 18917476 88.5304788

RECO01 4194304 CONNECTED NORMAL 9154560 8677040 94.7838017

select NAME, ALLOCATION_UNIT_SIZE, STATE, TYPE, TOTAL_MB/1024 TOTAL_GB ,FREE_MB/1024 FREE_GB,(FREE_MB/TOTAL_MB)*100 PCt_FREE from v$asm_diskgroup order by PCt_FREE ;

SQL>

NAME ALLOCATION_UNIT_SIZE STATE TYPE TOTAL_GB FREE_GB PCT_FREE

------------------------------ -------------------- ----------- ------ ---------- ---------- ----------

REDO01 4194304 CONNECTED HIGH 2980 2594.76953 87.0728031

DATA01 4194304 CONNECTED NORMAL 20867.5 18474.0977 88.5304788

RECO01 4194304 CONNECTED NORMAL 8940 8473.67188 94.7838017

Tab Name : ASMCMD>find file find +DATA01 undo* Tab Name : WhatInstances

set linesize 300 
col instance_name for a15 
col SOFTWARE_VERSION for a15 
col COMPATIBLE_VERSION for a15

set linesize 300

col instance_name for a15

col SOFTWARE_VERSION for a15

col COMPATIBLE_VERSION for a15

SELECT * FROM V$ASM_CLIENT; 

GROUP_NUMBER INSTANCE_NAME   DB_NAME  CLUSTER_NAME                    STATUS       SOFTWARE_VERSIO COMPATIBLE_VERS     CON_ID
------------ --------------- -------- ------------------------------- ------------ --------------- --------------- ----------
           3 +ASM1           ORCL1234 server1234-c                   CONNECTED    18.0.0.0.0      18.0.0.0.0               0
           2 +ASM1           ORCL1234 server1234-c                   CONNECTED    18.0.0.0.0      18.0.0.0.0               0
           1 +ASM1           ORCL1234 server1234-c                   CONNECTED    18.0.0.0.0      18.0.0.0.0               0

SELECT * FROM V$ASM_CLIENT;

GROUP_NUMBER INSTANCE_NAME DB_NAME CLUSTER_NAME STATUS SOFTWARE_VERSIO COMPATIBLE_VERS CON_ID

------------ --------------- -------- ------------------------------- ------------ --------------- --------------- ----------

3 +ASM1 ORCL1234 server1234-c CONNECTED 18.0.0.0.0 18.0.0.0.0 0

2 +ASM1 ORCL1234 server1234-c CONNECTED 18.0.0.0.0 18.0.0.0.0 0

1 +ASM1 ORCL1234 server1234-c CONNECTED 18.0.0.0.0 18.0.0.0.0 0

Tab Name : Rebalance_status

set linesize 300 
select * from gv$asm_operation;

1 2	set linesize 300 select * from gv$asm_operation;

Tab Name : ASM_Candidate

BREAK ON report ON disk_group_name SKIP 1 
set pagesize 50 
set linesize 300 
col DISK_GROUP_NAME for a20 
col DISK_FILE_PATH for a50 
col DISK_FILE_FAIL_GROUP for a40 
col DISK_FILE_NAME for a25

BREAK ON report ON disk_group_name SKIP 1

set pagesize 50

set linesize 300

col DISK_GROUP_NAME for a20

col DISK_FILE_PATH for a50

col DISK_FILE_FAIL_GROUP for a40

col DISK_FILE_NAME for a25

SELECT 
    NVL(a.name, '[CANDIDATE]')      disk_group_name 
  , b.path                          disk_file_path 
  , b.name                          disk_file_name 
  , b.failgroup                     disk_file_fail_group 
FROM 
    v$asm_diskgroup a RIGHT OUTER JOIN v$asm_disk b USING (group_number) 
ORDER BY 
    a.name;

SELECT

NVL(a.name, '[CANDIDATE]') disk_group_name

, b.path disk_file_path

, b.name disk_file_name

, b.failgroup disk_file_fail_group

FROM

v$asm_diskgroup a RIGHT OUTER JOIN v$asm_disk b USING (group_number)

ORDER BY

a.name;

Tab… Read More

Oracle : TableSpace Menu Bar in SecureCRT

4 years ago by Vinod SiramSecure CRT

Dear Readers, In this article, we will see TableSpace Menu Bar in SecureCRT . TBS_FREE :

set linesize 130
set pagesize 1000
col tablespace format a30

set linesize 130

set pagesize 1000

col tablespace format a30

select tablespace_name tablespace,
round(sum(a.total1)/1048576, 0) Total,
round(sum(a.total1)/1048576, 0)-round(sum(a.sum1)/1048576, 0) used,
round(sum(a.sum1)/1048576, 0) free,
round(((sum(a.sum1)/1048576)/(sum(a.total1)/1048576))*100, 0) pct_free,
round(sum(a.maxb)/1048576, 0) largest,
max(a.cnt) fragment
from
(select tablespace_name, 0 total1, sum(bytes) sum1,
max(bytes) MAXB,
count(bytes) cnt
from dba_free_space
group by tablespace_name
union
select tablespace_name, sum(bytes) total1, 0, 0, 0 from dba_data_files
group by tablespace_name) a
group by a.tablespace_name
order by 5 desc;

select tablespace_name tablespace,

round(sum(a.total1)/1048576, 0) Total,

round(sum(a.total1)/1048576, 0)-round(sum(a.sum1)/1048576, 0) used,

round(sum(a.sum1)/1048576, 0) free,

round(((sum(a.sum1)/1048576)/(sum(a.total1)/1048576))*100, 0) pct_free,

round(sum(a.maxb)/1048576, 0) largest,

max(a.cnt) fragment

from

(select tablespace_name, 0 total1, sum(bytes) sum1,

max(bytes) MAXB,

count(bytes) cnt

from dba_free_space

group by tablespace_name

union

select tablespace_name, sum(bytes) total1, 0, 0, 0 from dba_data_files

group by tablespace_name) a

group by a.tablespace_name

order by 5 desc;

Out Put :

TABLESPACE                          TOTAL       USED       FREE   PCT_FREE    LARGEST   FRAGMENT
------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
USERS                                   5          2          3         65          3          1
UNDOTBS1                               60         24         36         59         25         13
SYSAUX                                730        690         40          5         38         14
SYSTEM                                800        792          8          1          8          2

TABLESPACE TOTAL USED FREE PCT_FREE LARGEST FRAGMENT

------------------------------ ---------- ---------- ---------- ---------- ---------- ----------

USERS 5 2 3 65 3 1

UNDOTBS1 60 24 36 59 25 13

SYSAUX 730 690 40 5 38 14

SYSTEM 800 792 8 1 8 2

TBS_FILE :

set linesize 130
set pagesize 1000
set echo on
col file_name format a80

set linesize 130

set pagesize 1000

set echo on

col file_name format a80

select file_id,file_name, bytes/1048576 mb,AUTOEXTENSIBLE,MAXBYTES/1024/1024/1024 MAX_BYTES_GB
from dba_data_files
where tablespace_name='&tbs'
order by substr(file_name,length(file_name)-5,6);

select file_id,file_name, bytes/1048576 mb,AUTOEXTENSIBLE,MAXBYTES/1024/1024/1024 MAX_BYTES_GB

from dba_data_files

where tablespace_name='&tbs'

order by substr(file_name,length(file_name)-5,6);

SEGMENTBYNAME :

set ver off
set trimspool on
set lines 199
col owner for a15
col segment_name for a30
col segment_type for a25
col tablespace_name for a22
set pagesize 150
col part_name for a30
compu sum of size_mb on report
break on report skip 2

set ver off

set trimspool on

set lines 199

col owner for a15

col segment_name for a30

col segment_type for a25

col tablespace_name for a22

set pagesize 150

col part_name for a30

compu sum of size_mb on report

break on report skip 2

select
owner, segment_name,partition_name part_name, buffer_pool, segment_type, tablespace_name, extents,
round(bytes/1024/1024) size_mb
from dba_segments where
segment_name like upper('&segname');

select

owner, segment_name,partition_name part_name, buffer_pool, segment_type, tablespace_name, extents,

round(bytes/1024/1024) size_mb

from dba_segments where

segment_name like upper('&segname');

DBSIZE :

select sum(bytes)/1024/1024/1024 GB from dba_segments;
GB
----------
1.46954346

select sum(bytes)/1024/1024/1024 GB from dba_segments;

----------

1.46954346

select sum(bytes)/1024/1024/1024 GB from dba_data_files;
GB
----------
1.55761719

select sum(bytes)/1024/1024/1024 GB from dba_data_files;

----------

1.55761719

select sum(bytes)/1024/1024/1024 GB from dba_temp_files;
GB
----------
.05859375

select sum(bytes)/1024/1024/1024 GB from dba_temp_files;

----------

.05859375

Seg_With_Highest_IO_Reads :

set pagesize 200
set linesize 300
col segment_name format a40
col owner format a10

set pagesize 200

set linesize 300

col segment_name format a40

col owner format a10

select segment_name,object_type,total_physical_reads
from ( select owner||'.'||object_name as segment_name,object_type,
value as total_physical_reads
from v$segment_statistics
where statistic_name in ('physical reads')
order by total_physical_reads desc)
where rownum <=10;

select segment_name,object_type,total_physical_reads

from ( select owner||'.'||object_name as segment_name,object_type,

value as total_physical_reads

from v$segment_statistics

where statistic_name in ('physical reads')

order by total_physical_reads desc)

where rownum <=10;

Out Put :

SEGMENT_NAME                             OBJECT_TYPE        TOTAL_PHYSICAL_READS
---------------------------------------- ------------------ --------------------
SYS.REFCON$                              TABLE                              6665
SYS.OBJ$                                 TABLE                              3057
SYS.TYPE$                                TABLE                              2728
SYS.SEG$                                 TABLE                              2694
SYS.IDL_UB1$                             TABLE                              1803
SYS.I_OBJ5                               INDEX                               962
SYS.HISTGRM$                             TABLE                               958
SYS.I_OBJ1                               INDEX                               615
SYS.IDL_UB2$                             TABLE                               542
SYS.WRI$_OPTSTAT_HISTGRM_HISTORY         TABLE PARTITION                     462

SEGMENT_NAME OBJECT_TYPE TOTAL_PHYSICAL_READS

---------------------------------------- ------------------ --------------------

SYS.REFCON$ TABLE 6665

SYS.OBJ$ TABLE 3057

SYS.TYPE$ TABLE 2728

SYS.SEG$ TABLE 2694

SYS.IDL_UB1$ TABLE 1803

SYS.I_OBJ5 INDEX 962

SYS.HISTGRM$ TABLE 958

SYS.I_OBJ1 INDEX 615

SYS.IDL_UB2$ TABLE 542

SYS.WRI$_OPTSTAT_HISTGRM_HISTORY TABLE PARTITION 462

Seg_With_Highest_IO_Writes :… Read More

SecureCRT Buttons for Default

5 years ago by Vinod SiramSecure CRT

Hello Reader In this article , we will cover SecureCRT Buttons for Default. ############Setting up Format (format): To convert invalid timestamp value and to check current date.

alter session set nls_date_format='MM/DD/YYYY HH24:MI:SS';
Session altered.

1 2	alter session set nls_date_format='MM/DD/YYYY HH24:MI:SS'; Session altered.

######### Instance Information (inst_info): This SQL outputs the information of instance that is up and running like… Read More

Ease of DB Administration Via SecureCRT

6 years ago by Vinod SiramSecure CRT

Hello Readers I Standardized and customized the SecureCRT to group and link the frequently used commands. It will make the Oracle DBA job easy and fast. Download SecureCRT Software binaries using below link. https://www.vandyke.com/cgi-bin/account_login.php?pid=scrt_x64_870 Or Download SecureCRT binaries from Google Drive https://drive.google.com/file/d/1jfGtmKKwPwmOcjO1t5BJoN_b77V09a_q/view?usp=sharing Let me know… Read More