Oracle DB Admin – Page 6

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

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 19c Software Installation On Linux 7.6

4 years ago by Binnary Ajay KumarOracle 19c

In this article, we will see Oracle 19c (19.3) Software Installation On Linux 7.6 Click here to Download Oracle 19c Software from Oracle Support. Create a directory for ORACLE_HOME

[oracle@oracle19c ~]$ mkdir -p /oracle/app/product/19.3.0/dbhome_1

1	[oracle@oracle19c ~]$ mkdir -p /oracle/app/product/19.3.0/dbhome_1

Unzip the oracle 19c software

[oracle@oracle19c ~]$ unzip -d /oracle/app/product/19.3.0/dbhome_1/LINUX.X64_193000_db_home.zip

1	[oracle@oracle19c ~]$ unzip -d /oracle/app/product/19.3.0/dbhome_1/LINUX.X64_193000_db_home.zip

Goto Oracle home location and run the ./runinstaller

[oracle@oracle19c dbhome_1]$ ./runInstaller 
Launching Oracle Database Setup Wizard...

1 2	[oracle@oracle19c dbhome_1]$ ./runInstaller Launching Oracle Database Setup Wizard...

Start… Read More

Transport Plan from One Database to Another Database in Oracle

4 years ago by Venkata Sai PavanOracle DB Admin

Dear Readers, In this article, we will see the Transport Plan from One Database to Another Database in Oracle . Transport Good Execution Plan(Hash Plan) from one database to another database Requirement: ETL was taking longer period of time in Prod database where as in… Read More

STANDALONE to RAC Conversion

4 years ago by Venkata Sai PavanOracle DB Admin

Dear Readers, In this article, we will see the STANDALONE to RAC Conversion. 1)Take the existing database backup using the crontab job 2) Take the database information. (i.e datafile,tempfile,redolog & Controlfile) 3) Take the pfile backup to any location from spfile. 4) Export the SID… 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

Diag Collection of RAC

4 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