Vinod Siram – Page 5

Oracle : Important Queries to check Blocking sessions (SecureCRT)

Dear Readers, In this article, we will see the Oracle : Important Queries to check Blocking sessions (SecureCRT). Menubar : Blocking Tab : Blocking Sessions

set linesize 300 
select B.USERNAME ||' ('||B.SID||','||B.SERIAL#||',@'||B.INST_ID||') is Currently '||B.STATUS||' for last '||B.LAST_CALL_ET||' Sec and it''s BLOCKING user '|| W.USERNAME|| ' ('|
|W.SID||','||W.SERIAL#||',@'||W.INST_ID||')' from 
(select INST_ID,SID,SERIAL#,USERNAME,STATUS,BLOCKING_INSTANCE,BLOCKING_SESSION, LAST_CALL_ET from gv$session where BLOCKING_SESSION >0) W, 
(select INST_ID,SID,SERIAL#,USERNAME,STATUS,LAST_CALL_ET from gv$session ) B 
where W.BLOCKING_INSTANCE=B.INST_ID and W.BLOCKING_SESSION=B.SID; 

B.USERNAME||'('||B.SID||','||B.SERIAL#||',@'||B.INST_ID||')ISCURRENTLY'||B.STATUS||'FORLAST'||B.LAST_CALL_ET||
--------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------
VINOD (51,25124,@1) is Currently INACTIVE for last 48 Sec and it's BLOCKING user VINOD (57,30908,@1)

set linesize 300

select B.USERNAME ||' ('||B.SID||','||B.SERIAL#||',@'||B.INST_ID||') is Currently '||B.STATUS||' for last '||B.LAST_CALL_ET||' Sec and it''s BLOCKING user '|| W.USERNAME|| ' ('|

|W.SID||','||W.SERIAL#||',@'||W.INST_ID||')' from

(select INST_ID,SID,SERIAL#,USERNAME,STATUS,BLOCKING_INSTANCE,BLOCKING_SESSION, LAST_CALL_ET from gv$session where BLOCKING_SESSION >0) W,

(select INST_ID,SID,SERIAL#,USERNAME,STATUS,LAST_CALL_ET from gv$session ) B

where W.BLOCKING_INSTANCE=B.INST_ID and W.BLOCKING_SESSION=B.SID;

B.USERNAME||'('||B.SID||','||B.SERIAL#||',@'||B.INST_ID||')ISCURRENTLY'||B.STATUS||'FORLAST'||B.LAST_CALL_ET||

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

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

VINOD (51,25124,@1) is Currently INACTIVE for last 48 Sec and it's BLOCKING user VINOD (57,30908,@1)

Tab : Blocking_session_event

set linesize 300 
set pagesize 100 
col machine for a50 
col event for a50 
col username for a25 
select inst_id,sid,serial#,username,event,status,sql_id,BLOCKING_SESSION,machine from gv$session 
where event like '%lock%' or event like '%latch%' or event like '%buffer%' or event='latch: row cache objects' or event='enq: TM - contention' 
or event='enq:_TX_-_index_contention' 
and type!='BACKGROUND' order by 1; 


   INST_ID        SID    SERIAL# USERNAME                  EVENT                                              STATUS   SQL_ID        BLOCKING_SESSION MACHINE
---------- ---------- ---------- ------------------------- -------------------------------------------------- -------- ------------- ---------------- -------------
         1         57      30908 VINOD                     enq: TX - row lock contention                      ACTIVE   dbc1d3gdvz06s               51 awsserver123

set linesize 300

set pagesize 100

col machine for a50

col event for a50

col username for a25

select inst_id,sid,serial#,username,event,status,sql_id,BLOCKING_SESSION,machine from gv$session

where event like '%lock%' or event like '%latch%' or event like '%buffer%' or event='latch: row cache objects' or event='enq: TM - contention'

or event='enq:_TX_-_index_contention'

and type!='BACKGROUND' order by 1;

INST_ID SID SERIAL# USERNAME EVENT STATUS SQL_ID BLOCKING_SESSION MACHINE

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

1 57 30908 VINOD enq: TX - row lock contention ACTIVE dbc1d3gdvz06s 51 awsserver123

Tab : SQLs INVOLVED IN BLOCKING

set head on 
PROMPT ##################################################################################### 
PROMPT SQLs INVOLVED IN BLOCKING 
PROMPT ##################################################################################### 
set pagesize 100 
set linesize 300 
col sql_text for a100 
col SQL_ID for a30

set head on

PROMPT #####################################################################################

PROMPT SQLs INVOLVED IN BLOCKING

PROMPT #####################################################################################

set pagesize 100

set linesize 300

col sql_text for a100

col SQL_ID for a30

SELECT distinct /*+ RULE */ 
  SQL_ID, sql_text 
  FROM GV$SQL 
WHERE SQL_ID IN ( 
select SQL_ID   
        from  
        (select SQL_ID from gv$session where BLOCKING_SESSION >0) 
union 
select B.SQL_ID    --Blocker Current SQL 
        from  
        (select INST_ID,SID,BLOCKING_INSTANCE,BLOCKING_SESSION from gv$session where BLOCKING_SESSION >0) W, 
        (select INST_ID,SID,SERIAL#,SQL_ID from gv$session ) B 
        where W.BLOCKING_INSTANCE=B.INST_ID and W.BLOCKING_SESSION=B.SID 
union 
select B.PREV_SQL_ID --Blocker PRIV SQL 
        from  
        (select INST_ID,SID,BLOCKING_INSTANCE,BLOCKING_SESSION from gv$session where BLOCKING_SESSION >0) W, 
        (select INST_ID,SID,SERIAL#,PREV_SQL_ID from gv$session ) B 
        where   W.BLOCKING_INSTANCE=B.INST_ID and W.BLOCKING_SESSION=B.SID 
) ORDER BY sql_id; 

SQL_ID                         SQL_TEXT
------------------------------ --------------------------------
dbc1d3gdvz06s                  update test set a=3 where a=1

SELECT distinct /*+ RULE */

SQL_ID, sql_text

FROM GV$SQL

WHERE SQL_ID IN (

select SQL_ID

from

(select SQL_ID from gv$session where BLOCKING_SESSION >0)

union

select B.SQL_ID --Blocker Current SQL

from

(select INST_ID,SID,BLOCKING_INSTANCE,BLOCKING_SESSION from gv$session where BLOCKING_SESSION >0) W,

(select INST_ID,SID,SERIAL#,SQL_ID from gv$session ) B

where W.BLOCKING_INSTANCE=B.INST_ID and W.BLOCKING_SESSION=B.SID

union

select B.PREV_SQL_ID --Blocker PRIV SQL

from

(select INST_ID,SID,BLOCKING_INSTANCE,BLOCKING_SESSION from gv$session where BLOCKING_SESSION >0) W,

(select INST_ID,SID,SERIAL#,PREV_SQL_ID from gv$session ) B

where W.BLOCKING_INSTANCE=B.INST_ID and W.BLOCKING_SESSION=B.SID

) ORDER BY sql_id;

SQL_ID SQL_TEXT

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

dbc1d3gdvz06s update test set a=3 where a=1

Tab : Objects involved in Blocking Lock… Read More

SecureCRT Articles

4 years ago by Vinod SiramSecure CRT

Please check below articles to get good knowledge on SecureCRT . SNO DESCRIPTION ARTICLE LINK 1 Ease of DB Administration Via SecureCRT https://www.ktexperts.com/ease-of-db-administration-via-securecrt/ 2 SecureCRT Buttons for Default https://www.ktexperts.com/securecrt-buttons-for-default/ 3 Oracle : TableSpace Menu Bar in SecureCRT https://www.ktexperts.com/oracle-tablespace-menu-bar-in-securecrt/ 4 Oracle : Important ASM Commands https://www.ktexperts.com/oracle-important-asm-commands/… Read More

Oracle : Important Monitoring commands for EXPDP/IMPDP (SecureCRT)

4 years ago by Vinod SiramSecure CRT

Dear Readers, In this article, we will see the following Important Monitoring commands for EXPDP/IMPDP (SecureCRT). MenuBar : Datapump (Exports and Imports) Tab : DataPump_Sessions

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   : Data Pump Sessions                                          | 
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 a9         HEADING 'Instance' 
COLUMN owner_name        FORMAT a15        HEADING 'Owner Name' 
COLUMN job_name          FORMAT a20        HEADING 'Job Name' 
COLUMN session_type      FORMAT a15        HEADING 'Session Type' 
COLUMN sid               FORMAT 999999     HEADING 'SID' 
COLUMN serial_id         FORMAT 99999999   HEADING 'Serial ID' 
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' 

BREAK ON report ON instance_name_print ON owner_name ON job_name

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 : Data Pump Sessions |

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 a9 HEADING 'Instance'

COLUMN owner_name FORMAT a15 HEADING 'Owner Name'

COLUMN job_name FORMAT a20 HEADING 'Job Name'

COLUMN session_type FORMAT a15 HEADING 'Session Type'

COLUMN sid FORMAT 999999 HEADING 'SID'

COLUMN serial_id FORMAT 99999999 HEADING 'Serial ID'

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'

BREAK ON report ON instance_name_print ON owner_name ON job_name

SELECT 
    i.instance_name    instance_name 
  , dj.owner_name      owner_name  
  , dj.job_name        job_name 
  , ds.type            session_type 
  , s.sid              sid 
  , s.serial#          serial_id 
  , s.username         oracle_username 
  , s.osuser           os_username 
  , p.spid             os_pid 
FROM 
    gv$datapump_job         dj 
  , gv$datapump_session     ds 
  , gv$session              s 
  , gv$instance             i 
  , gv$process              p 
WHERE 
      s.inst_id  = i.inst_id 
  AND s.inst_id  = p.inst_id 
  AND ds.inst_id = i.inst_id 
  AND dj.inst_id = i.inst_id 
  AND s.saddr    = ds.saddr 
  AND s.paddr    = p.addr (+) 
  AND dj.job_id  = ds.job_id 
ORDER BY 
    i.instance_name 
  , dj.owner_name 
  , dj.job_name 
  , ds.type; 

Instance  Owner Name      Job Name             Session Type        SID Serial ID Oracle User        O/S User           O/S PID
--------- --------------- -------------------- --------------- ------- --------- ------------------ ------------------ --------
orcltest12 SYS             SYS_EXPORT_FULL_01   DBMS_DATAPUMP        38     27306 SYS                oracle             94647
orcltest12                                      MASTER              213     48974 SYS                oracle             94669
orcltest12                                      WORKER               59     38644 SYS                oracle             94689

SELECT

i.instance_name instance_name

, dj.owner_name owner_name

, dj.job_name job_name

, ds.type session_type

, s.sid sid

, s.serial# serial_id

, s.username oracle_username

, s.osuser os_username

, p.spid os_pid

FROM

gv$datapump_job dj

, gv$datapump_session ds

, gv$session s

, gv$instance i

, gv$process p

WHERE

s.inst_id = i.inst_id

AND s.inst_id = p.inst_id

AND ds.inst_id = i.inst_id

AND dj.inst_id = i.inst_id

AND s.saddr = ds.saddr

AND s.paddr = p.addr (+)

AND dj.job_id = ds.job_id

ORDER BY

i.instance_name

, dj.owner_name

, dj.job_name

, ds.type;

Instance Owner Name Job Name Session Type SID Serial ID Oracle User O/S User O/S PID

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

orcltest12 SYS SYS_EXPORT_FULL_01 DBMS_DATAPUMP 38 27306 SYS oracle 94647

orcltest12 MASTER 213 48974 SYS oracle 94669

orcltest12 WORKER 59 38644 SYS oracle 94689

Tab : Datapump_Progress

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   : Data Pump Job Progress                                      | 
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 a9                  HEADING 'Instance|Name' 
COLUMN owner_name        FORMAT a15                 HEADING 'Owner|Name' 
COLUMN job_name          FORMAT a20                 HEADING 'Job|Name' 
COLUMN session_type      FORMAT a8                  HEADING 'Session|Type' 
COLUMN start_time        FORMAT a19                 HEADING 'Start|Time' 
COLUMN time_remaining    FORMAT 9,999,999,999       HEADING 'Time|Remaining (min.)' 
COLUMN sofar             FORMAT 9,999,999,999,999   HEADING 'Bytes Completed|So Far'
COLUMN totalwork         FORMAT 9,999,999,999,999   HEADING 'Total Bytes|for Job' 
COLUMN pct_completed     FORMAT a10                 HEADING 'Percent|Completed' 

BREAK ON report ON instance_name_print ON owner_name ON job_name

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 : Data Pump Job Progress |

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 a9 HEADING 'Instance|Name'

COLUMN owner_name FORMAT a15 HEADING 'Owner|Name'

COLUMN job_name FORMAT a20 HEADING 'Job|Name'

COLUMN session_type FORMAT a8 HEADING 'Session|Type'

COLUMN start_time FORMAT a19 HEADING 'Start|Time'

COLUMN time_remaining FORMAT 9,999,999,999 HEADING 'Time|Remaining (min.)'

COLUMN sofar FORMAT 9,999,999,999,999 HEADING 'Bytes Completed|So Far'

COLUMN totalwork FORMAT 9,999,999,999,999 HEADING 'Total Bytes|for Job'

COLUMN pct_completed FORMAT a10 HEADING 'Percent|Completed'

BREAK ON report ON instance_name_print ON owner_name ON job_name

SELECT 
    i.instance_name                                        instance_name 
  , dj.owner_name                                          owner_name  
  , dj.job_name                                            job_name 
  , ds.type                                                session_type 
  , TO_CHAR(sl.start_time,'mm/dd/yyyy HH24:MI:SS')         start_time 
  , ROUND(sl.time_remaining/60,0)                          time_remaining 
  , sl.sofar                                               sofar 
  , sl.totalwork                                           totalwork 
  , TRUNC(ROUND((sl.sofar/sl.totalwork) * 100, 1)) || '%'  pct_completed 
FROM 
    gv$datapump_job         dj 
  , gv$datapump_session     ds 
  , gv$session              s 
  , gv$instance             i 
  , gv$session_longops      sl 
WHERE 
      s.inst_id  = i.inst_id 
  AND ds.inst_id = i.inst_id 
  AND dj.inst_id = i.inst_id 
  AND sl.inst_id = i.inst_id 
  AND s.saddr    = ds.saddr 
  AND dj.job_id  = ds.job_id 
  AND sl.sid     = s.sid 
  AND sl.serial# = s.serial# 
  AND ds.type    = 'MASTER' 
ORDER BY 
    i.instance_name 
  , dj.owner_name 
  , dj.job_name 
  , ds.type; 

Instance  Owner           Job                  Session  Start                           Time                       Percent
Name      Name            Name                 Type     Time                Remaining (min.)      SOFAR  TOTALWORK Completed
--------- --------------- -------------------- -------- ------------------- ---------------- ---------- ---------- ----------
orcltest11 SYS             SYS_EXPORT_FULL_01   MASTER   02/17/2021 14:18:50                6      13009      26840 48%

SELECT

i.instance_name instance_name

, dj.owner_name owner_name

, dj.job_name job_name

, ds.type session_type

, TO_CHAR(sl.start_time,'mm/dd/yyyy HH24:MI:SS') start_time

, ROUND(sl.time_remaining/60,0) time_remaining

, sl.sofar sofar

, sl.totalwork totalwork

, TRUNC(ROUND((sl.sofar/sl.totalwork) * 100, 1)) || '%' pct_completed

FROM

gv$datapump_job dj

, gv$datapump_session ds

, gv$session s

, gv$instance i

, gv$session_longops sl

WHERE

s.inst_id = i.inst_id

AND ds.inst_id = i.inst_id

AND dj.inst_id = i.inst_id

AND sl.inst_id = i.inst_id

AND s.saddr = ds.saddr

AND dj.job_id = ds.job_id

AND sl.sid = s.sid

AND sl.serial# = s.serial#

AND ds.type = 'MASTER'

ORDER BY

i.instance_name

, dj.owner_name

, dj.job_name

, ds.type;

Instance Owner Job Session Start Time Percent

Name Name Name Type Time Remaining (min.) SOFAR TOTALWORK Completed

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

orcltest11 SYS SYS_EXPORT_FULL_01 MASTER 02/17/2021 14:18:50 6 13009 26840 48%

Tab : Datapump_jobs

set linesize 300 
col state for a15 
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   : Data Pump Jobs                                              | 
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 owner_name         FORMAT a25            HEADING 'Owner Name' 
COLUMN job_name           FORMAT a20            HEADING 'Job Name' 
COLUMN operation          FORMAT a10            HEADING 'Operation' 
COLUMN job_mode           FORMAT a10            HEADING 'Job Mode' 
COLUMN state              FORMAT a15            HEADING 'State' 
COLUMN degree             FORMAT 999999         HEADING 'Degree' 
COLUMN attached_sessions  FORMAT 999,999        HEADING 'Attached Sessions'

set linesize 300

col state for a15

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 : Data Pump Jobs |

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 owner_name FORMAT a25 HEADING 'Owner Name'

COLUMN job_name FORMAT a20 HEADING 'Job Name'

COLUMN operation FORMAT a10 HEADING 'Operation'

COLUMN job_mode FORMAT a10 HEADING 'Job Mode'

COLUMN state FORMAT a15 HEADING 'State'

COLUMN degree FORMAT 999999 HEADING 'Degree'

COLUMN attached_sessions FORMAT 999,999 HEADING 'Attached Sessions'

SELECT 
    dpj.owner_name           owner_name 
  , dpj.job_name             job_name 
  , dpj.operation            operation 
  , dpj.job_mode             job_mode 
  , dpj.state                state 
  , dpj.degree               degree 
  , dpj.attached_sessions    attached_sessions 
FROM 
    dba_datapump_jobs      dpj 
ORDER BY 
    dpj.owner_name 
  , dpj.job_name; 

Owner Name                Job Name             Operation  Job Mode   State            Degree Attached Sessions
------------------------- -------------------- ---------- ---------- --------------- ------- -----------------
SYS                       SYS_EXPORT_FULL_01   EXPORT     FULL       EXECUTING             1                 1

SELECT

dpj.owner_name owner_name

, dpj.job_name job_name

, dpj.operation operation

, dpj.job_mode job_mode

, dpj.state state

, dpj.degree degree

, dpj.attached_sessions attached_sessions

FROM

dba_datapump_jobs dpj

ORDER BY

dpj.owner_name

, dpj.job_name;

Owner Name Job Name Operation Job Mode State Degree Attached Sessions

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

SYS SYS_EXPORT_FULL_01 EXPORT FULL EXECUTING 1 1

Tab : DBA Directories

set linesize 300 
col owner format a10 
col directory_name for a60 
col directory_path format a100

set linesize 300

col owner format a10

col directory_name for a60

col directory_path format a100

SELECT * FROM dba_directories; 
SQL> 
OWNER      DIRECTORY_NAME        DIRECTORY_PATH                                         ORIGIN_CON_ID
---------- --------------------- ------------------------------------------------------ -------------
SYS        DATA_PUMP_DIR         /u01/app/oracle/product/12.1.0.2/dbhome_1/rdbms/log/               0
SYS        DBA_EXPORT            /dbbackup/datapump/orcltest                                        0

SELECT * FROM dba_directories;

SQL>

OWNER DIRECTORY_NAME DIRECTORY_PATH ORIGIN_CON_ID

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

SYS DATA_PUMP_DIR /u01/app/oracle/product/12.1.0.2/dbhome_1/rdbms/log/ 0

SYS DBA_EXPORT /dbbackup/datapump/orcltest 0

Tab :… Read More

Oracle : Important commands for Flashback (SecureCRT)

4 years ago by Vinod SiramSecure CRT

Dear Readers, In this article, we will see the following Important commands for Flashback (SecureCRT). Menubar : Flashback (FB) Tab : FRA_Usage

set linesize 300 
col file_type for a40 
select * from V$FLASH_RECOVERY_AREA_USAGE; 

FILE_TYPE                                PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES     CON_ID
---------------------------------------- ------------------ ------------------------- --------------- ----------
CONTROL FILE                                              0                         0               0          0
REDO LOG                                                  0                         0               0          0
ARCHIVED LOG                                            .86                       .24              14          0
BACKUP PIECE                                              0                         0               0          0
IMAGE COPY                                                0                         0               0          0
FLASHBACK LOG                                          3.43                         0              15          0
FOREIGN ARCHIVED LOG                                      0                         0               0          0
AUXILIARY DATAFILE COPY                                   0                         0               0          0

set linesize 300

col file_type for a40

select * from V$FLASH_RECOVERY_AREA_USAGE;

FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES CON_ID

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

CONTROL FILE 0 0 0 0

REDO LOG 0 0 0 0

ARCHIVED LOG .86 .24 14 0

BACKUP PIECE 0 0 0 0

IMAGE COPY 0 0 0 0

FLASHBACK LOG 3.43 0 15 0

FOREIGN ARCHIVED LOG 0 0 0 0

AUXILIARY DATAFILE COPY 0 0 0 0

Tab : Check Restore Point

set linesize 300 
col time for a35 
set numwidth 30 
col name for a40 
col RESTORE_POINT_TIME for a25
select SCN,GUARANTEE_FLASHBACK_DATABASE,STORAGE_SIZE,TIME,PRESERVED,NAME from v$restore_point order by time

                           SCN GUA                   STORAGE_SIZE TIME                                PRE NAME
------------------------------ --- ------------------------------ ----------------------------------- --- ----------------------------------------
                14354029433707 YES                    34359738368 20-JAN-21 06.05.04.000000000 PM     YES ORCLTEST1_POST_REFRESH_SCOTT_20JAN2021
                14354044238464 YES                    34359738368 29-JAN-21 12.13.53.000000000 PM     YES ORCLTEST1_REFRESH_SCOTT_29JAN2021
				14354115444165 YES                    34359738368 16-FEB-21 10.41.17.000000000 AM     YES ORCLTEST1_REFRESH_SCOTT_16FEB2021

set linesize 300

col time for a35

set numwidth 30

col name for a40

col RESTORE_POINT_TIME for a25

select SCN,GUARANTEE_FLASHBACK_DATABASE,STORAGE_SIZE,TIME,PRESERVED,NAME from v$restore_point order by time

SCN GUA STORAGE_SIZE TIME PRE NAME

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

14354029433707 YES 34359738368 20-JAN-21 06.05.04.000000000 PM YES ORCLTEST1_POST_REFRESH_SCOTT_20JAN2021

14354044238464 YES 34359738368 29-JAN-21 12.13.53.000000000 PM YES ORCLTEST1_REFRESH_SCOTT_29JAN2021

14354115444165 YES 34359738368 16-FEB-21 10.41.17.000000000 AM YES ORCLTEST1_REFRESH_SCOTT_16FEB2021

Tab : Flashback_DB_Logfile

set linesize 300 
col name for a60 
set pagesize 100 
set numwidth 20 
select NAME,LOG#,SEQUENCE#,to_char(first_time,'DD-MON-YYYY HH24:MI:SS')     from v$flashback_database_logfile;

NAME                                                                         LOG#            SEQUENCE# TO_CHAR(FIRST_TIME,'DD-MON-YYYYHH24:MI:SS')
------------------------------------------------------------ -------------------- -------------------- --------------------------------------------
+RECO/AWSORCLTEST1/FLASHBACK/log_1.562.1061727163                                1                  344 09-FEB-2021 12:31:18
+RECO/AWSORCLTEST1/FLASHBACK/log_2.559.1061732237                                2                  290 19-JAN-2021 20:41:58
+RECO/AWSORCLTEST1/FLASHBACK/log_3.560.1061734601                                3                  345 11-FEB-2021 15:13:32
+RECO/AWSORCLTEST1/FLASHBACK/log_4.556.1061738273                                4                  291 24-JAN-2021 21:37:53
+RECO/AWSORCLTEST1/FLASHBACK/log_5.554.1061768543                                5                  340 20-JAN-2021 17:48:04
+RECO/AWSORCLTEST1/FLASHBACK/log_6.568.1064243615                                6                  346 14-FEB-2021 06:04:08
+RECO/AWSORCLTEST1/FLASHBACK/log_7.548.1064328399                                7                    1
+RECO/AWSORCLTEST1/FLASHBACK/log_8.579.1064469849                                8                    1
+RECO/AWSORCLTEST1/FLASHBACK/log_96.345.1058080845                              96                  292 30-JAN-2021 07:41:49
+RECO/AWSORCLTEST1/FLASHBACK/log_97.533.1058397145                              97                  293 03-FEB-2021 10:44:47
+RECO/AWSORCLTEST1/FLASHBACK/log_98.531.1059051627                              98                  341 27-JAN-2021 21:46:13
+RECO/AWSORCLTEST1/FLASHBACK/log_99.535.1059218007                              99                  294 07-FEB-2021 16:03:51
+RECO/AWSORCLTEST1/FLASHBACK/log_100.538.1059868943                            100                  342 02-FEB-2021 08:16:36
+RECO/AWSORCLTEST1/FLASHBACK/log_101.542.1059869209                            101                  343 05-FEB-2021 01:10:02
+RECO/AWSORCLTEST1/FLASHBACK/log_102.545.1059869431                            102                  295 12-FEB-2021 14:46:38

set linesize 300

col name for a60

set pagesize 100

set numwidth 20

select NAME,LOG#,SEQUENCE#,to_char(first_time,'DD-MON-YYYY HH24:MI:SS') from v$flashback_database_logfile;

NAME LOG# SEQUENCE# TO_CHAR(FIRST_TIME,'DD-MON-YYYYHH24:MI:SS')

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

+RECO/AWSORCLTEST1/FLASHBACK/log_1.562.1061727163 1 344 09-FEB-2021 12:31:18

+RECO/AWSORCLTEST1/FLASHBACK/log_2.559.1061732237 2 290 19-JAN-2021 20:41:58

+RECO/AWSORCLTEST1/FLASHBACK/log_3.560.1061734601 3 345 11-FEB-2021 15:13:32

+RECO/AWSORCLTEST1/FLASHBACK/log_4.556.1061738273 4 291 24-JAN-2021 21:37:53

+RECO/AWSORCLTEST1/FLASHBACK/log_5.554.1061768543 5 340 20-JAN-2021 17:48:04

+RECO/AWSORCLTEST1/FLASHBACK/log_6.568.1064243615 6 346 14-FEB-2021 06:04:08

+RECO/AWSORCLTEST1/FLASHBACK/log_7.548.1064328399 7 1

+RECO/AWSORCLTEST1/FLASHBACK/log_8.579.1064469849 8 1

+RECO/AWSORCLTEST1/FLASHBACK/log_96.345.1058080845 96 292 30-JAN-2021 07:41:49

+RECO/AWSORCLTEST1/FLASHBACK/log_97.533.1058397145 97 293 03-FEB-2021 10:44:47

+RECO/AWSORCLTEST1/FLASHBACK/log_98.531.1059051627 98 341 27-JAN-2021 21:46:13

+RECO/AWSORCLTEST1/FLASHBACK/log_99.535.1059218007 99 294 07-FEB-2021 16:03:51

+RECO/AWSORCLTEST1/FLASHBACK/log_100.538.1059868943 100 342 02-FEB-2021 08:16:36

+RECO/AWSORCLTEST1/FLASHBACK/log_101.542.1059869209 101 343 05-FEB-2021 01:10:02

+RECO/AWSORCLTEST1/FLASHBACK/log_102.545.1059869431 102 295 12-FEB-2021 14:46:38

Tab : dba_recyclebin

set linesize 300
col object_name for a30
col ORIGINAL_NAME for a30
col type for a10
col owner for a10
col CREATETIME for a20
col DROPTIME for a20
select OBJECT_NAME, ORIGINAL_NAME, TYPE,owner,CREATETIME,DROPTIME from dba_recyclebin;

OBJECT_NAME                    ORIGINAL_NAME                  TYPE       OWNER      CREATETIME           DROPTIME
------------------------------ ------------------------------ ---------- ---------- -------------------- --------------------
BIN$uWPP5TBJJc/gU9b1ugrHHg==$0 EMP11_13_1                  	  TABLE      SCOTT        2021-01-20:22:02:09  2021-01-20:22:02:09
BIN$uXgQosMnT4PgU9b1ugrqow==$0 EMP11_13_1                  	  TABLE      SCOTT        2021-01-21:22:11:54  2021-01-21:22:11:54
BIN$uYwI+n44bgPgU9b1ugo+qQ==$0 EMP11_3031_1                	  TABLE      SCOTT        2021-01-22:22:01:25  2021-01-22:22:01:25

set linesize 300

col object_name for a30

col ORIGINAL_NAME for a30

col type for a10

col owner for a10

col CREATETIME for a20

col DROPTIME for a20

select OBJECT_NAME, ORIGINAL_NAME, TYPE,owner,CREATETIME,DROPTIME from dba_recyclebin;

OBJECT_NAME ORIGINAL_NAME TYPE OWNER CREATETIME DROPTIME

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

BIN$uWPP5TBJJc/gU9b1ugrHHg==$0 EMP11_13_1 TABLE SCOTT 2021-01-20:22:02:09 2021-01-20:22:02:09

BIN$uXgQosMnT4PgU9b1ugrqow==$0 EMP11_13_1 TABLE SCOTT 2021-01-21:22:11:54 2021-01-21:22:11:54

BIN$uYwI+n44bgPgU9b1ugo+qQ==$0 EMP11_3031_1 TABLE SCOTT 2021-01-22:22:01:25 2021-01-22:22:01:25

Tab : Check FB is or not?

select FLASHBACK_ON from v$database;

FLASHBACK_ON
------------------
YES

select FLASHBACK_ON from v$database;

FLASHBACK_ON

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

YES

Oracle (SecureCRT) : Temp Tablespace Monitoring Queries

4 years ago by Vinod SiramSecure CRT

Dear Readers, In this article, we will see Oracle (SecureCRT) : Temp Tablespace Monitoring Queries. Menubar : TEMP Tabname : Def_temp

set linesize 300 
col property_name for a35 
col property_value for a20 
col description for a45

set linesize 300

col property_name for a35

col property_value for a20

col description for a45

SELECT * FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE'; 

PROPERTY_NAME                       PROPERTY_VALUE       DESCRIPTION
----------------------------------- -------------------- ---------------------------------------------
DEFAULT_TEMP_TABLESPACE             TEMP                 Name of default temporary tablespace

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

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

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

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

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

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;

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

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

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

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

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

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

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

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;

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

Oracle (SecureCRT) : Important Queries for UNDO Management

4 years ago by Vinod SiramSecure CRT

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

Installation of SQL Server On AWS Cloud

4 years ago by Vinod SiramSQL Server DBA

Dear Readers, In this article, we will see the following Installation of SQL Server On AWS Cloud. This provides a step by step procedure for installing a new instance of SQL server 2014 by using the SQL server setup installation wizard. Now I will show… Read More

Introduction to SQL Server

4 years ago by Vinod SiramSQL Server DBA

Dear Readers, In this article, we will see the following Introduction to SQL Server. DBMS: Human needs have increased tremendously. Now people are doing much more composite tasks than ever before. Society has become very complex if a person has to work in a huge… 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