Check progress on expdp and impdp:

In few cases we need to monitor the progress of an export or import job in oracle. below are the steps can be followed to get the details.

Datapump Client:

get the session details: 

SQL> select * from dba_datapump_jobs where state='EXECUTING';

attach the session:

from Command line:

    expdp system/********** attach=SYS_EXPORT_FULL_01

    Export > status

From Database:

Query 1: The % of work done for the running job can be found from this query:

    SELECT b.username, a.sid, b.opname, b.target,

            round(b.SOFAR*100/b.TOTALWORK,0) || '%' as "%DONE", b.TIME_REMAINING,

            to_char(b.start_time,'YYYY/MM/DD HH24:MI:SS') start_time

     FROM v$session_longops b, v$session a

     WHERE a.sid = b.sid      ORDER BY 6;

Query 2: The work done so far and status of the job:

SELECT sl.sid, sl.serial#, sl.sofar, sl.totalwork, dp.owner_name, dp.state, dp.job_mode

     FROM v$session_longops sl, v$datapump_job dp

     WHERE sl.opname = dp.job_name

     AND sl.sofar != sl.totalwork;

Query 3: The amount of work done so far:

SELECT sl.sid, sl.serial#, sl.sofar, sl.MESSAGE,sl.totalwork, dp.owner_name, dp.state, dp.job_mode

FROM v$session_longops sl, v$datapump_job dp

WHERE sl.opname = dp.job_name;

Reference:

How To Monitor The Progress Of Datapump Jobs (Doc ID 1471766.1)

Best Wishes!!

Oracle Home remove/Detach from Inventory

Remove/Detach Oracle Home from Inventory

Prechecks:

Check /etc/oraInst.loc

go to the inventory location:

cd ContentsXML

cat inventory.xml and check which one needs to be detached.

inventory.xml example:

<?xml version = '1.0' encoding = 'UTF-8' standalone = 'yes'?>

<!-- Copyright (c) 1999, 2024, Oracle. All rights reserved. -->

<!-- Do not modify the contents of this file by hand. --><INVENTORY>

   <VERSION_INFO>

      <SAVED_WITH>13.9.4.0.0</SAVED_WITH>

      <MINIMUM_VER>2.1.0.6.0</MINIMUM_VER>

   </VERSION_INFO>

   <HOME_LIST>

      <HOME NAME="OraDB19Home1" LOC="/u01/app/oracle/19.3.0/" TYPE="O" IDX="4"/>

      <HOME NAME="agent13c1" LOC="/u01/app/em13c/agent_13.4.0.0.0" TYPE="O" IDX="3"/>

      <HOME NAME="agent13c2" LOC="/u01/app/em13c/agent_13.5.0.0.0" TYPE="O" IDX="6"/>

   </HOME_LIST>

   <COMPOSITEHOME_LIST/>

Oracle database archive space utilization

 Query to check Archive space Utilization

set pagesize 0 feedback off verify off heading off echo off

SELECT decode( nvl( space_used, 0),0, 0, ceil ( ( space_used / space_limit) * 100) ) pct_used FROM v$recovery_file_dest;

Unix-Linux Shell Source of environment variables

Shell - Source of env variables

In many times, we need to know what are the script is being called while setting up the environment variables, this is very handy when we are getting errors like some env variables are not setup.

If zsh is the login shell:

zsh -xl

Execute the below command to get from which file the environment is setup:

PS4='+$BASH_SOURCE> ' BASH_XTRACEFD=7 bash -xl 7>&2

Best Wishes!!