KTEXPERTS – Page 21 – KNOWLEDGE SHARING PLATFORM

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

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)

5 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

Diag Collection of RAC

5 years ago by Venkata Sai PavanOracle DB Admin

Dear Readers, In this article, we will see the following Install Python Boto3 On AWS EC2. Collecting Cluster Health Monitor Data 👉 Oracle recommends that you run the diagcollection.sh script on all nodes in the cluster to collect Cluster Health Monitor data. 👉 Running the… Read More

Daily Tips for Linux

5 years ago by KTEXPERTS TEAMLinux Tips

Dear Readers, In this article, we will see the following Daily Tips for Linux. Tip-1 Date: 05-Feb-21 Provided By: Sridhar What is Operating System ? 👉 Operating system is an interface between user and the computer hardware.… Read More

Install Python Boto3 On AWS EC2

5 years ago by Ramesh ReddyAWS

Dear Readers, In this article, we will see the following Install Python Boto3 On AWS EC2. Steps to Follow : Logging to AWS Account Launch Linux Server Connect to Linux EC2 Instance by Using Putty Install Python Package Install Boto3 Package Verify boto3 Package … Read More

SQL Server Database Architecture

5 years ago by KTEXPERTS TEAMSQL Server DBA

Dear Readers, In this article, we will see SQL Server Database Architecture. SQL Server MDF Files In the SQL Server Primary Data Files are the basic data files which are related to the database for the storing and supervision of the information. MDF file in… Read More

Download and Install Maven Plugin In Jenkins

5 years ago by Ramesh ReddyDevOps

Dear Readers, In this article,we will see Download and Install Maven Plugin In Jenkins. Steps to Follow Logging to Jenkins. Download and Install Maven Plugin. Restart Jenkins. Logging to Jenkins. Verify plugin “maven integration” and “green balls”. First,we need to go Jenkins Logging page… Read More

How to Generate DDL from existing dumpfile

5 years ago by Binnary Ajay KumarOracle Datapump (Export & Imports)

Hello everyone, In this article, we will learn Generating DDL using existing dumpfile. To generate DDL of a schema or table from a dumpfile we use a parameter called SQLFILE. Purpose: SQLFILE is a paramater which can be used in IMPDP utility to generate DDL… Read More

Updating Patches in SQL Server

5 years ago by KTEXPERTS TEAMSQL Server DBA

Dear Readers, In this article, we will see the Upgradation and Data Migration in SQL Server. We have to download the Sql server patches from Sql server Builds site. (https://sqlserverbuilds.blogspot.com/) What is Cumulative updates (CU) 👉 Each new CU contains all the fixes that were… Read More

Oracle (SecureCRT) : Temp Tablespace Monitoring Queries

5 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