Secure CRT – KTEXPERTS

Important Oracle v$parameters (SecureCRT)

Hi Dears, In this article, we will see Important Oracle v$parameters (SecureCRT) Tab Name : v$parameter Button Name : SGA

col name for a50 
set linesize 300 
col value for a40  
select name,value from v$parameter where name like '%sga%'; 
select &sga/1024/1024/1024 GB from dual;

col name for a50

set linesize 300

col value for a40

select name,value from v$parameter where name like '%sga%';

select &sga/1024/1024/1024 GB from dual;

Output :

NAME                                               VALUE
-------------------------------------------------- ----------------------------------------
sga_max_size                                       4294967296
pre_page_sga                                       TRUE
lock_sga                                           FALSE
allow_group_access_to_sga                          FALSE
sga_target                                         4294967296
unified_audit_sga_queue_size                       1048576

NAME VALUE

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

sga_max_size 4294967296

pre_page_sga TRUE

lock_sga FALSE

allow_group_access_to_sga FALSE

sga_target 4294967296

unified_audit_sga_queue_size 1048576

select &sga/1024/1024/1024 GB from dual; 
Enter value for sga: 4294967296

        GB
----------
         4

select &sga/1024/1024/1024 GB from dual;

Enter value for sga: 4294967296

----------

Button Name : SGA_TARGET

col name for a50 
set linesize 300 
col value for a40  
select name,value from v$parameter where name like '%sga_target%'; 
select &sga_target/1024/1024/1024 GB from dual;

col name for a50

set linesize 300

col value for a40

select name,value from v$parameter where name like '%sga_target%';

select &sga_target/1024/1024/1024 GB from dual;

Output :

NAME                                               VALUE
-------------------------------------------------- ----------------------------------------
sga_target                                         4294967296

select &sga_target/1024/1024/1024 GB from dual; 
Enter value for sga_target: 4294967296

        GB
----------
         4

NAME VALUE

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

sga_target 4294967296

select &sga_target/1024/1024/1024 GB from dual;

Enter value for sga_target: 4294967296

----------

Button Name : PGA_Aggregate_target

SQL>   col name for a50 
SQL> set linesize 300 
SQL> col value for a40  
select name,value from v$parameter where name like '%pga_aggregate_target%'; 
select &pga_aggregate_target/1024/1024/1024 GB from dual;

SQL> col name for a50

SQL> set linesize 300

SQL> col value for a40

select name,value from v$parameter where name like '%pga_aggregate_target%';

select &pga_aggregate_target/1024/1024/1024 GB from dual;

Output :

SQL> select &pga_aggregate_target/1024/1024/1024 GB from dual; 

NAME                                               VALUE
-------------------------------------------------- ----------------------------------------
pga_aggregate_target                               2147483648

SQL> select &pga_aggregate_target/1024/1024/1024 GB from dual;

NAME VALUE

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

pga_aggregate_target 2147483648

SQL> Enter value for pga_aggregate_target: 2147483648

        GB
----------
         2

SQL> Enter value for pga_aggregate_target: 2147483648

----------

Button Name :… Read More

Oracle : Important Commands for Dataguard (SecureCRT)

4 years ago by Vinod SiramSecure CRT

Dear Readers, In this article, we will see the Oracle : Important Commands for Dataguard (SecureCRT) Menubar Name : Dataguard Tab Name : Instance_info

set linesize 300 
col machine for a60 
select name||' - '||instance_name||'@'||host_name||' - '||DB_UNIQUE_NAME||' - '||version||' - '||open_mode||' - '||to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') || ' - '|| database_role from v$database, v$instance; 

NAME||'-'||INSTANCE_NAME||'@'||HOST_NAME||'-'||DB_UNIQUE_NAME||'-'||VERSION||'-'||OPEN_MODE||'-'||TO_CHAR(STARTUP_TIME,'DD-MON-YYYYHH24:MI:SS')||'-'||DATABASE_ROLE
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ORCLTEST - ORCLTEST1@SERVER1234.sephoraus.com - MSTORCLTEST - 12.1.0.2.0 - READ ONLY WITH APPLY - 04-FEB-2021 00:59:59 - PHYSICAL STANDBY

set linesize 300

col machine for a60

select name||' - '||instance_name||'@'||host_name||' - '||DB_UNIQUE_NAME||' - '||version||' - '||open_mode||' - '||to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') || ' - '|| database_role from v$database, v$instance;

NAME||'-'||INSTANCE_NAME||'@'||HOST_NAME||'-'||DB_UNIQUE_NAME||'-'||VERSION||'-'||OPEN_MODE||'-'||TO_CHAR(STARTUP_TIME,'DD-MON-YYYYHH24:MI:SS')||'-'||DATABASE_ROLE

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

ORCLTEST - ORCLTEST1@SERVER1234.sephoraus.com - MSTORCLTEST - 12.1.0.2.0 - READ ONLY WITH APPLY - 04-FEB-2021 00:59:59 - PHYSICAL STANDBY

Tab Name : DG_GAP

SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", 
(ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" 
FROM 
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME)  
FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH, 
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME)  
FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL 
WHERE 
ARCH.THREAD# = APPL.THREAD# 
ORDER BY 1; 

select thread#,applied,max(sequence#) from gv$archived_log where resetlogs_change#=(select resetlogs_change# from v$database) group by thread#,applied order by thread#,applied; 

    Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
         1                  44791                 44791          0
         2                  42354                 42354          0
         3                  40223                 40223          0

         

SQL> 
   THREAD# APPLIED   MAX(SEQUENCE#)
---------- --------- --------------
         1 IN-MEMORY          44791
         1 NO                 39800
         1 YES                44790
         2 NO                 38782
         2 YES                42354
         3 NO                 38673
         3 YES                40223

SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied",

(ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"

FROM

(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME)

FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,

(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME)

FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL

WHERE

ARCH.THREAD# = APPL.THREAD#

ORDER BY 1;

select thread#,applied,max(sequence#) from gv$archived_log where resetlogs_change#=(select resetlogs_change# from v$database) group by thread#,applied order by thread#,applied;

Thread Last Sequence Received Last Sequence Applied Difference

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

1 44791 44791 0

2 42354 42354 0

3 40223 40223 0

SQL>

THREAD# APPLIED MAX(SEQUENCE#)

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

1 IN-MEMORY 44791

1 NO 39800

1 YES 44790

2 NO 38782

2 YES 42354

3 NO 38673

3 YES 40223

Tab Name : ERR_DEST

set linesize 300 
col dest_name for a30 
col error for a70 
select dest_id,dest_name,status,error from v$archive_dest where dest_id in (1,2,3,4); 
select inst_id,dest_name,status,error from gv$archive_dest where dest_id in (1,2,3,4) and status='VALID' order by 1; 

   DEST_ID DEST_NAME                      STATUS    ERROR
---------- ------------------------------ --------- --------------
         1 LOG_ARCHIVE_DEST_1             VALID
         2 LOG_ARCHIVE_DEST_2             INACTIVE
         3 LOG_ARCHIVE_DEST_3             INACTIVE
         4 LOG_ARCHIVE_DEST_4             DEFERRED

SQL> 
   INST_ID DEST_NAME                      STATUS    ERROR
---------- ------------------------------ --------- --------------
         1 LOG_ARCHIVE_DEST_1             VALID

set linesize 300

col dest_name for a30

col error for a70

select dest_id,dest_name,status,error from v$archive_dest where dest_id in (1,2,3,4);

select inst_id,dest_name,status,error from gv$archive_dest where dest_id in (1,2,3,4) and status='VALID' order by 1;

DEST_ID DEST_NAME STATUS ERROR

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

1 LOG_ARCHIVE_DEST_1 VALID

2 LOG_ARCHIVE_DEST_2 INACTIVE

3 LOG_ARCHIVE_DEST_3 INACTIVE

4 LOG_ARCHIVE_DEST_4 DEFERRED

SQL>

INST_ID DEST_NAME STATUS ERROR

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

1 LOG_ARCHIVE_DEST_1 VALID

Tab Name : Date Format

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

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

Tab Name :… Read More

Oracle : Important Queries for Parallel Feature (SecureCRT)

4 years ago by Vinod SiramSecure CRT

Dear Readers, In this article, we will see the Oracle : Important Queries for Parallel Feature (SecureCRT). Menubar Name : Parallel Tab Name : Create table show parameter parallel_max_servers

create table owner.tablename_bkp as select /*+ parallel(tablename,64) */ * from owner.tablename ;

1	create table owner.tablename_bkp as select /+ parallel(tablename,64) / * from owner.tablename ;

create table vinod.tablename_bkp as select /*+ parallel(EMP,4) */ * from SCOTT.EMP;

1	create table vinod.tablename_bkp as select /+ parallel(EMP,4) / * from SCOTT.EMP;

Tab Name : Finding_Parallel_Queries

set linesize 300  
col username for a19  
col terminal for a15  
col osuser for a15  
col program for a45 
col time for a25

set linesize 300

col username for a19

col terminal for a15

col osuser for a15

col program for a45

col time for a25

select inst_id,sid,sql_id,program,username,to_char(LOGON_TIME,'DD-MON-YYYY HH24:MI:SS') time,TERMINAL,OSUSER   
from gv$session where program like '%P0%' and sql_id is not null;   

   INST_ID        SID SQL_ID        PROGRAM                                       USERNAME            TIME                      TERMINAL        OSUSER
---------- ---------- ------------- --------------------------------------------- ------------------- ------------------------- --------------- ---------------
         1         58 29d991q9rq5kn oracle@awsserver003 (P000)                    SYS                 27-FEB-2021 21:08:38      pts/1           oracle
         1         59 29d991q9rq5kn oracle@awsserver003 (P001)                    SYS                 27-FEB-2021 21:08:38      pts/1           oracle
         2         37 29d991q9rq5kn oracle@awsserver004 (P001)                    SYS                 27-FEB-2021 21:08:38      pts/1           oracle
         2        207 29d991q9rq5kn oracle@awsserver004 (P000)                    SYS                 27-FEB-2021 21:08:38      pts/1           oracle

select inst_id,sid,sql_id,program,username,to_char(LOGON_TIME,'DD-MON-YYYY HH24:MI:SS') time,TERMINAL,OSUSER

from gv$session where program like '%P0%' and sql_id is not null;

INST_ID SID SQL_ID PROGRAM USERNAME TIME TERMINAL OSUSER

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

1 58 29d991q9rq5kn oracle@awsserver003 (P000) SYS 27-FEB-2021 21:08:38 pts/1 oracle

1 59 29d991q9rq5kn oracle@awsserver003 (P001) SYS 27-FEB-2021 21:08:38 pts/1 oracle

2 37 29d991q9rq5kn oracle@awsserver004 (P001) SYS 27-FEB-2021 21:08:38 pts/1 oracle

2 207 29d991q9rq5kn oracle@awsserver004 (P000) SYS 27-FEB-2021 21:08:38 pts/1 oracle

Tab Name : Select Query with Parallel Option… Read More

Oracle: Important commands for Lock/Unlock Stats and Backup/Restore Old Stats (SecureCRT)

4 years ago by Vinod SiramSecure CRT

Dear Readers, In this article, we will see the Oracle: Important commands for Lock/Unlock Stats and Backup/Restore Old Stats (SecureCRT). MenuBar Name : Lock/Unlock Stats and Backup/Restore Old Stats Tab Name : Check Stats for Table

--example : 'DEPT','EMP','SALGRADE' 
set linesize 300 
set pagesize 100  
col owner for a15  
col LAST_ANALYZED for a30 
col table_name for a40 
select OWNER,TABLE_NAME ,  NUM_ROWS , BLOCKS , EMPTY_BLOCKS , 
CHAIN_CNT, AVG_ROW_LEN 
CACHE, to_char(LAST_ANALYZED,'DD-MON-YYYY HH24:MI:SS') LAST_ANALYZED 
from dba_tables where table_name in (&Selected_Tables_Check_Example) and owner='&OWNER'; 

Enter value for selected_tables_check_example: 'TEST'
Enter value for owner: VINOD
old   4: from dba_tables where table_name in (&Selected_Tables_Check_Example) and owner='&OWNER'
new   4: from dba_tables where table_name in ('TEST') and owner='VINOD'

OWNER           TABLE_NAME                                 NUM_ROWS     BLOCKS EMPTY_BLOCKS  CHAIN_CNT      CACHE LAST_ANALYZED
--------------- ---------------------------------------- ---------- ---------- ------------ ---------- ---------- ------------------------------
VINOD           TEST                                              2          5            0          0          3 18-FEB-2021 22:00:21

--example : 'DEPT','EMP','SALGRADE'

set linesize 300

set pagesize 100

col owner for a15

col LAST_ANALYZED for a30

col table_name for a40

select OWNER,TABLE_NAME , NUM_ROWS , BLOCKS , EMPTY_BLOCKS ,

CHAIN_CNT, AVG_ROW_LEN

CACHE, to_char(LAST_ANALYZED,'DD-MON-YYYY HH24:MI:SS') LAST_ANALYZED

from dba_tables where table_name in (&Selected_Tables_Check_Example) and owner='&OWNER';

Enter value for selected_tables_check_example: 'TEST'

Enter value for owner: VINOD

old 4: from dba_tables where table_name in (&Selected_Tables_Check_Example) and owner='&OWNER'

new 4: from dba_tables where table_name in ('TEST') and owner='VINOD'

OWNER TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS CHAIN_CNT CACHE LAST_ANALYZED

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

VINOD TEST 2 5 0 0 3 18-FEB-2021 22:00:21

Tab Name : Backup Existing Stats

exec dbms_stats.create_stat_table ( ownname => 'VINOD',stattab => 'TEST_STAT');
exec dbms_stats.export_table_stats ( ownname => 'VINOD',stattab => 'TEST_STAT', tabname => 'TEST',statid => 'stats04252016');
select count(*) from vinod.TEST_STAT;

SQL> exec dbms_stats.create_stat_table ( ownname => 'VINOD',stattab => 'TEST_STAT');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.export_table_stats ( ownname => 'VINOD',stattab => 'TEST_STAT', tabname => 'TEST',statid => 'stats04252016');

PL/SQL procedure successfully completed.

SQL> select count(*) from vinod.TEST_STAT;

  COUNT(*)
----------
         2

exec dbms_stats.create_stat_table ( ownname => 'VINOD',stattab => 'TEST_STAT');

exec dbms_stats.export_table_stats ( ownname => 'VINOD',stattab => 'TEST_STAT', tabname => 'TEST',statid => 'stats04252016');

select count(*) from vinod.TEST_STAT;

SQL> exec dbms_stats.create_stat_table ( ownname => 'VINOD',stattab => 'TEST_STAT');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.export_table_stats ( ownname => 'VINOD',stattab => 'TEST_STAT', tabname => 'TEST',statid => 'stats04252016');

PL/SQL procedure successfully completed.

SQL> select count(*) from vinod.TEST_STAT;

COUNT(*)

----------

Tab… Read More

Oracle : Important Queries to check Table Fragmentation (SecureCRT)

4 years ago by Vinod SiramSecure CRT

Dear Readers, In this article, we will see the Oracle : Important Queries to check Table Fragmentation (SecureCRT). Menubar Name : Fragmentation Tab name : Fragmented_tablesList in HTML

col spoolname new_value spoolname 
select 'Fragmented_tables'||to_char(sysdate, 'YYYYMONDD')||'.html' spoolname from dual; 
spool '&spoolname' 
set pagesize 200 
set markup html on 
WITH STAT_SPACE AS( 
  SELECT OWNER, TABLE_NAME, NUM_ROWS, AVG_ROW_LEN, NUM_ROWS*AVG_ROW_LEN/1024/1024/1024 tbl_STAT_SPACE 
  FROM   DBA_TABLES  
  WHERE  OWNER='SCOTT'), 
SEG_SPACE AS ( 
  SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, BLOCKS, BYTES/1024/1024/1024 SEGSIZE_GB  
  FROM DBA_SEGMENTS  
  WHERE 1=1 
  AND OWNER='SCOTT' 
  AND SEGMENT_TYPE='TABLE') 
SELECT sesp.tablespace_name, StSp.TABLE_NAME,  sesp.blocks, StSp.NUM_ROWS, StSp.AVG_ROW_LEN, StSp.TBL_STAT_SPACE, sesp.SEGSIZE_GB, abs(STSP.TBL_STAT_SPACE - sesp.SEGSIZE_GB) claim_gb 
FROM   STAT_SPACE STSP, SEG_SPACE SESP 
WHERE  abs(STSP.TBL_STAT_SPACE - sesp.SEGSIZE_GB) > 1 
AND    STSP.TABLE_NAME = SESP.SEGMENT_NAME 
AND    STSP.OWNER = SESP.OWNER 
ORDER BY CLAIM_GB desc; 
spool off 
exit

col spoolname new_value spoolname

select 'Fragmented_tables'||to_char(sysdate, 'YYYYMONDD')||'.html' spoolname from dual;

spool '&spoolname'

set pagesize 200

set markup html on

WITH STAT_SPACE AS(

SELECT OWNER, TABLE_NAME, NUM_ROWS, AVG_ROW_LEN, NUM_ROWS*AVG_ROW_LEN/1024/1024/1024 tbl_STAT_SPACE

FROM DBA_TABLES

WHERE OWNER='SCOTT'),

SEG_SPACE AS (

SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, BLOCKS, BYTES/1024/1024/1024 SEGSIZE_GB

FROM DBA_SEGMENTS

WHERE 1=1

AND OWNER='SCOTT'

AND SEGMENT_TYPE='TABLE')

SELECT sesp.tablespace_name, StSp.TABLE_NAME, sesp.blocks, StSp.NUM_ROWS, StSp.AVG_ROW_LEN, StSp.TBL_STAT_SPACE, sesp.SEGSIZE_GB, abs(STSP.TBL_STAT_SPACE - sesp.SEGSIZE_GB) claim_gb

FROM STAT_SPACE STSP, SEG_SPACE SESP

WHERE abs(STSP.TBL_STAT_SPACE - sesp.SEGSIZE_GB) > 1

AND STSP.TABLE_NAME = SESP.SEGMENT_NAME

AND STSP.OWNER = SESP.OWNER

ORDER BY CLAIM_GB desc;

spool off

exit

Output : Tab name : BLOCKS Size

set linesize 300 
col table_name for a30 
col "Blocks Size" for a30 
select table_name,round((blocks*8),2)||' KB' "Blocks Size",(round((blocks*8),2))/1024/1024 ||' GB' "Blocks Size" 
from dba_tables 
where owner='&OWNER' and table_name = upper('&table_name'); 


TABLE_NAME                     Blocks Size                    Blocks Size
------------------------------ ------------------------------ ------------------------------
EMPTEST123                     1231264 KB                     1.174224853515625 GB

set linesize 300

col table_name for a30

col "Blocks Size" for a30

select table_name,round((blocks*8),2)||' KB' "Blocks Size",(round((blocks*8),2))/1024/1024 ||' GB' "Blocks Size"

from dba_tables

where owner='&OWNER' and table_name = upper('&table_name');

TABLE_NAME Blocks Size Blocks Size

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

EMPTEST123 1231264 KB 1.174224853515625 GB

Tab name : Rows Size

set linesize 300 
select table_name,round((num_rows*avg_row_len/1024),2)||' KB' "Rows Size",(round((num_rows*avg_row_len/1024),2))/1024/1024||' GB' "Rows Size" 
from dba_tables 
where owner='&OWNER' and table_name = upper('&table_name'); 

TABLE_NAME                     Rows Size                                                                Rows Size
------------------------------ ------------------------------------------------------------------------ ----------------------------
EMPTEST123                     10597.63 KB                                                              .0101066875457763671875 GB

set linesize 300

select table_name,round((num_rows*avg_row_len/1024),2)||' KB' "Rows Size",(round((num_rows*avg_row_len/1024),2))/1024/1024||' GB' "Rows Size"

from dba_tables

where owner='&OWNER' and table_name = upper('&table_name');

TABLE_NAME Rows Size Rows Size

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

EMPTEST123 10597.63 KB .0101066875457763671875 GB

Tab name… Read More

Oracle : Important Queries to check Blocking sessions (SecureCRT)

4 years ago by Vinod SiramSecure CRT

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