How to check Ansible version ?

check version of Ansible on Linux  

[root@ip-0.0.0.0 project1]#

[root@ip-0.0.0.0 project1]# ansible --version

ansible 2.9.27

  config file = /etc/ansible/ansible.cfg

  configured module search path = ['/root/.ansible/plugins/modules', '/usr/share/ansible/plugins/modules']

  ansible python module location = /usr/lib/python3.6/site-packages/ansible

  executable location = /usr/bin/ansible

  python version = 3.6.8 (default, Jan 11 2023, 08:43:50) [GCC 8.5.0 20210514 (Red Hat 8.5.0-16)]

[root@ip-0.0.0.0 project1]#

[root@ip-0.0.0.0 project1]#

How to install Ansible on Linux ?

Install Ansible on Linux OS

=> Login as root user 

[root@ip-0.0.0.0 ~]#

[root@ip-0.0.0.0 ~]# yum install ansible

Updating Subscription Management repositories.

Unable to read consumer identity

This system is not registered with an entitlement server. You can use subscription-manager to register.

Last metadata expiration check: 2:49:39 ago on Fri 14 Apr 2023 07:03:14 AM UTC.

Dependencies resolved.

==========================================================================================================================

 Package                     Architecture      Version                    Repository                                 Size

==========================================================================================================================

Installing:

 ansible                     noarch            2.9.27-1.el8ae             ansible-2-for-rhel-8-rhui-rpms             17 M

Installing dependencies:

 sshpass                     x86_64            1.09-4.el8                 rhel-8-appstream-rhui-rpms                 30 k

Installing weak dependencies:

 python3-jmespath            noarch            0.9.0-11.el8               rhel-8-appstream-rhui-rpms                 45 k

Transaction Summary

==========================================================================================================================

Install  3 Packages

Total download size: 17 M

Installed size: 96 M

Is this ok [y/N]: y

Downloading Packages:

(1/3): python3-jmespath-0.9.0-11.el8.noarch.rpm                                           825 kB/s |  45 kB     00:00

(2/3): sshpass-1.09-4.el8.x86_64.rpm                                                      526 kB/s |  30 kB     00:00

(3/3): ansible-2.9.27-1.el8ae.noarch.rpm                                                   52 MB/s |  17 MB     00:00

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

Total                                                                                      44 MB/s |  17 MB     00:00

Running transaction check

Transaction check succeeded.

Running transaction test

Transaction test succeeded.

Running transaction

  Preparing        :                                                                                                  1/1

  Installing       : sshpass-1.09-4.el8.x86_64                                                                        1/3

  Installing       : python3-jmespath-0.9.0-11.el8.noarch                                                             2/3

  Installing       : ansible-2.9.27-1.el8ae.noarch                                                                    3/3

  Running scriptlet: ansible-2.9.27-1.el8ae.noarch                                                                    3/3

  Verifying        : python3-jmespath-0.9.0-11.el8.noarch                                                             1/3

  Verifying        : sshpass-1.09-4.el8.x86_64                                                                        2/3

  Verifying        : ansible-2.9.27-1.el8ae.noarch                                                                    3/3

Installed products updated.

Installed:

  ansible-2.9.27-1.el8ae.noarch          python3-jmespath-0.9.0-11.el8.noarch          sshpass-1.09-4.el8.x86_64

Complete!

[root@ip-0.0.0.0 ~]#

How to check terraform version using command line

 

Check terraform version using command line :

PS C:\test_terraform> terraform  version

Terraform v1.2.8

on windows_amd64

+ provider registry.terraform.io/hashicorp/aws v4.22.0

PS C:\test_terraform>

PS C:\test_terraform>

Download Terraform binary software

 

Use below link to download the Terraform 

https://www.terraform.io/downloads

How to check DML operations oracle

check dml operations oracle

SQL> exec dbms_stats.gather_table_stats(ownname=>'TEST',tabname=>'TEST_1')

PL/SQL procedure successfully completed.

SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

SQL>
SQL>
SQL> select table_name,inserts,updates,deletes,truncated,timestamp from sys.dba_tab_modifications where table_owner='TEST' and table_name= 'TEST_1'
  2  ;

no rows selected

SQL>
SQL> insert  into test.test_1 select * from dba_objects;

73824 rows created.

SQL> /

73824 rows created.

SQL> commit;

Commit complete.

SQL> select table_name,inserts,updates,deletes,truncated,timestamp from sys.dba_tab_modifications where table_owner='TEST' and table_name= 'TEST_1';

TABLE_NAME                        INSERTS    UPDATES    DELETES TRU TIMESTAMP
------------------------------ ---------- ---------- ---------- --- ---------
TEST_1                             147648          0          0 NO

SQL>

Oracle Tablespace Utilization Script

Tablespace Utilization Script for Tablespace Space Used % more than 85 %
==========================================================================

Check the tablespace Utilization.
=========================================

set pages 500 lines 250
col tablespace_name format a30
col TABLESPACE_NAME heading "Tablespace|Name"
col Allocated_size heading "Allocated|Size(GB)" form 99999999.99
col Current_size heading "Current|Size(GB)" form 99999999.99
col Used_size heading "Used|Size(GB)" form 99999999.99
col Available_size heading "Available|Size(GB)" form 99999999.99
col Pct_used heading "%Used (vs)|(Allocated)" form 99999999.99

select a.tablespace_name
        ,a.alloc_size/1024/1024/1024 Allocated_size
        ,a.cur_size/1024/1024/1024 Current_Size
        ,(u.used+a.file_count*65536)/1024/1024/1024 Used_size
        ,(a.alloc_size-(u.used+a.file_count*65536))/1024/1024/1024 Available_size
        ,((u.used+a.file_count*65536)*100)/a.alloc_size Pct_used
from     dba_tablespaces t
        ,(select t1.tablespace_name
        ,nvl(sum(s.bytes),0) used
        from  dba_segments s
        ,dba_tablespaces t1
         where t1.tablespace_name=s.tablespace_name(+)
         group by t1.tablespace_name) u
        ,(select d.tablespace_name
        ,sum(greatest(d.bytes,nvl(d.maxbytes,0))) alloc_size
        ,sum(d.bytes) cur_size
        ,count(*) file_count
        from dba_data_files d
        group by d.tablespace_name) a
where t.tablespace_name=u.tablespace_name
and ((u.used+a.file_count*65536)*100)/a.alloc_size>85
and t.tablespace_name=a.tablespace_name
order by t.tablespace_name;


Check the details of the datafiles for a particular TableSpace 
=====================================================================


set pages 500 lines 250
col tablespace_name for a30
col CREATION_TIME for a15
col file_name for a70
select dd.tablespace_name TABLESPACE_NAME,dd.file_name,dd.bytes/1024/1024 Size_MB,dd.autoextensible,dd.maxbytes/1024/1024 MAXSIZE_MB,df.CREATION_TIME
from dba_data_files dd, v$datafile df where df.name=dd.file_name and tablespace_name=UPPER('&TABLESPACENAME') order by 1,2,6;



Get DDL for tablespace 
==============================

set pagesize 0
SET LONG 9999999
select dbms_metadata.get_ddl('TABLESPACE',upper('&TABLESPACE_NAME')) FROM DUAL;

Adding a Datafile/Tempfile to Temp Tablespace

Connect with oracle database and check tempfile location & information :

[oracle@localhost ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 12.2.0.1.0 Production on Thu Sep 5 14:58:33 2019

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> set pages 999
set lines 400
col FILE_NAME format a50
col TABLESPACE_NAME format a10
select d.TABLESPACE_NAME, d.FILE_NAME, d.BYTES/1024/1024 SIZE_MB, d.AUTOEXTENSIBLE, d.MAXBYTES/1024/1024 MAXSIZE_MB, d.INCREMENT_BY*(v.BLOCK_SIZE/1024)/1024 INCREMENT_BY_MB
from dba_temp_files d,
 v$tempfile v
where d.FILE_ID = v.FILE#
order by d.TABLESPACE_NAME, d.FILE_NAME;SQL> SQL> SQL> SQL>   2    3    4    5

TABLESPACE FILE_NAME                                             SIZE_MB AUT MAXSIZE_MB INCREMENT_BY_MB
---------- -------------------------------------------------- ---------- --- ---------- ---------------
TEMP       /u01/app/oracle/oradata/risdev/temp01.dbf                  32 YES 32767.9844            .625

SQL>

Add tempfile to temp tablepsace : 

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/risdev/temp02.dbf' SIZE 5G AUTOEXTEND ON NEXT 1G MAXSIZE 32767M;

Tablespace altered.

SQL>



Verify added tempfile : 



SQL> set pages 999
set lines 400
col FILE_NAME format a50
col TABLESPACE_NAME format a10
select d.TABLESPACE_NAME, d.FILE_NAME, d.BYTES/1024/1024 SIZE_MB, d.AUTOEXTENSIBLE, d.MAXBYTES/1024/1024 MAXSIZE_MB, d.INCREMENT_BY*(v.BLOCK_SIZE/1024)/1024 INCREMENT_BY_MB
from dba_temp_files d,
 v$tempfile v
where d.FILE_ID = v.FILE#
order by d.TABLESPACE_NAME, d.FILE_NAME;SQL> SQL> SQL> SQL>   2    3    4    5

TABLESPACE FILE_NAME                                             SIZE_MB AUT MAXSIZE_MB INCREMENT_BY_MB
---------- -------------------------------------------------- ---------- --- ---------- ---------------
TEMP       /u01/app/oracle/oradata/risdev/temp01.dbf                  32 YES 32767.9844            .625
TEMP       /u01/app/oracle/oradata/risdev/temp02.dbf                5120 YES      32767            1024

SQL>

* Make sure enough space available at mount point .

Query:

set pages 999
set lines 400
col FILE_NAME format a50
col TABLESPACE_NAME format a10
select d.TABLESPACE_NAME, d.FILE_NAME, d.BYTES/1024/1024 SIZE_MB, d.AUTOEXTENSIBLE, d.MAXBYTES/1024/1024 MAXSIZE_MB, d.INCREMENT_BY*(v.BLOCK_SIZE/1024)/1024 INCREMENT_BY_MB
from dba_temp_files d,
 v$tempfile v
where d.FILE_ID = v.FILE#

order by d.TABLESPACE_NAME, d.FILE_NAME;

How to Find and Delete Duplicate Rows with Oracle SQL

How to Find and Delete Duplicate Rows with Oracle SQL

SQL> select * from test;

JOB_ID     JOB_TITLE                           MIN_SALARY MAX_SALARY

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

AD_PRES    President                                20080      40000

AD_VP      Administration Vice President            15000      30000

AD_ASST    Administration Assistant                  3000       6000

FI_MGR     Finance Manager                           8200      16000

FI_ACCOUNT Accountant                                4200       9000

AC_MGR     Accounting Manager                        8200      16000

AC_ACCOUNT Public Accountant                         4200       9000

SA_MAN     Sales Manager                            10000      20080

SA_REP     Sales Representative                      6000      12008

PU_MAN     Purchasing Manager                        8000      15000

PU_CLERK   Purchasing Clerk                          2500       5500

ST_MAN     Stock Manager                             5500       8500

ST_CLERK   Stock Clerk                               2008       5000

SH_CLERK   Shipping Clerk                            2500       5500

IT_PROG    Programmer                                4000      10000

MK_MAN     Marketing Manager                         9000      15000

MK_REP     Marketing Representative                  4000       9000

HR_REP     Human Resources Representative            4000       9000

PR_REP     Public Relations Representative           4500      10500

19 rows selected.

  

SQL> select min_salary ,count(*) from test group by min_salary having count(*)>1;

MIN_SALARY   COUNT(*)

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

      4200          2

      8200          2

      2500          2

      4000          3

SQL> select * from test where MIN_SALARY in  (select min_salary from test group by min_salary having count(*)>1);

JOB_ID     JOB_TITLE                           MIN_SALARY MAX_SALARY

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

FI_ACCOUNT Accountant                                4200       9000

AC_ACCOUNT Public Accountant                         4200       9000

FI_MGR     Finance Manager                           8200      16000

AC_MGR     Accounting Manager                        8200      16000

PU_CLERK   Purchasing Clerk                          2500       5500

SH_CLERK   Shipping Clerk                            2500       5500

IT_PROG    Programmer                                4000      10000

MK_REP     Marketing Representative                  4000       9000

HR_REP     Human Resources Representative            4000       9000

9 rows selected.

SQL>

SQL> select * from test where MIN_SALARY in  (select min_salary from test  group by min_salary having count(*)>1);

JOB_ID     JOB_TITLE                           MIN_SALARY MAX_SALARY

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

FI_ACCOUNT Accountant                                4200       9000

AC_ACCOUNT Public Accountant                         4200       9000

FI_MGR     Finance Manager                           8200      16000

AC_MGR     Accounting Manager                        8200      16000

PU_CLERK   Purchasing Clerk                          2500       5500

SH_CLERK   Shipping Clerk                            2500       5500

IT_PROG    Programmer                                4000      10000

MK_REP     Marketing Representative                  4000       9000

HR_REP     Human Resources Representative            4000       9000

9 rows selected.

SQL> delete from test where MIN_SALARY in  (select min_salary from test  group by min_salary having count(*)>1);

9 rows deleted.

SQL> select * from test;

JOB_ID     JOB_TITLE                           MIN_SALARY MAX_SALARY

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

AD_PRES    President                                20080      40000

AD_VP      Administration Vice President            15000      30000

AD_ASST    Administration Assistant                  3000       6000

SA_MAN     Sales Manager                            10000      20080

SA_REP     Sales Representative                      6000      12008

PU_MAN     Purchasing Manager                        8000      15000

ST_MAN     Stock Manager                             5500       8500

ST_CLERK   Stock Clerk                               2008       5000

MK_MAN     Marketing Manager                         9000      15000

PR_REP     Public Relations Representative           4500      10500

10 rows selected.

• Use "commit" after delete statement make as permanent delete.

Solution ORA-01940: cannot drop a user that is currently connected

SQL> drop user sprtsg4 cascade;
drop user sprtsg4 cascade
*
ERROR at line 1:
ORA-01940: cannot drop a user that is currently connected



///
Before the kill session lock the account for further connection establish.


SQL>
SQL> alter user sprtsg4 account lock;

User altered.

SQL>
SQL> SET LINESIZE 100
SQL> COLUMN spid FORMAT A10
SQL> COLUMN username FORMAT A10
SQL> COLUMN program FORMAT A45
SQL>
SQL> SELECT s.inst_id,
  2         s.sid,
  3         s.serial#,
  4         p.spid,
       s.username,
  5    6         s.program
  7  FROM   gv$session s
  8         JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE  s.type != 'BACKGROUND';  9

   INST_ID        SID    SERIAL# SPID       USERNAME   PROGRAM
---------- ---------- ---------- ---------- ---------- ---------------------------------------------
         1         49       7543 4360       SYS        sqlplus@acs7 (TNS V1-V3)
         1        100       4663 4153       SYS        sqlplus@acs7 (TNS V1-V3)
         1        141      15044 4626       SPRTSG4    JDBC Thin Client
         1         16      33035 4628       SPRTSG4    JDBC Thin Client
         1         71      16974 4630       SPRTSG4    JDBC Thin Client
         1         83      58119 4632       SPRTSG4    JDBC Thin Client
         1         90      61930 4634       SPRTSG4    JDBC Thin Client
         1        121      28526 3065       SYSMAN     OMS
         1         31      44985 3076       SYSMAN     OMS
         1        122      31487 3078       SYSMAN     OMS
         1        140      11579 3366       DBSNMP     emagent@acs7 (TNS V1-V3)

   INST_ID        SID    SERIAL# SPID       USERNAME   PROGRAM
---------- ---------- ---------- ---------- ---------- ---------------------------------------------
         1        129        121 25590      DBSNMP     emagent@acs7 (TNS V1-V3)
         1        152       3737 4636       SPRTSG4    JDBC Thin Client
         1         79        615 4615       SYSMAN     OMS
         1        119      22358 4638       SPRTSG4    JDBC Thin Client
         1          6       6899 4640       SPRTSG4    JDBC Thin Client
         1         46       7585 4644       SPRTSG4    JDBC Thin Client
         1        151      38346 4648       SPRTSG4    JDBC Thin Client

18 rows selected.

SQL> alter system kill session '&id,&serial';
Enter value for id: 151
Enter value for serial: 38346
old   1: alter system kill session '&id,&serial'
new   1: alter system kill session '151,38346'

System altered.

SQL> /
Enter value for id: 46
Enter value for serial: 7585
old   1: alter system kill session '&id,&serial'
new   1: alter system kill session '46,7585'

System altered.

SQL>

SQL>

SQL> drop user sprtsg4 cascade;

User dropped.

SQL> SQL> SQL> SQL> SQL> SQL>

AUTO START STOP ORACLE DATABASE SCRIPT

Save this script with oracle.sh

 execute with root user.


#! /bin/sh
##! /usr/bin/ksh
# Description: Starts and stops the Oracle database and listeners
# See how we were called.
export ORACLE_HOME=/u01/app/oracle/product
export LISTENER_NAME=LISTENER
case "$1" in
  start)
        echo -n "Starting Oracle Listeners: "
        su - oracle -c "lsnrctl start ${LISTENER_NAME}" >> /var/log/oracle
        echo "Done."
        echo ""
        echo -n "Starting Oracle Databases: "
        echo "----------------------------------------------------" >> /var/log/oracle
        date +"! %T %a %D : Starting Oracle Databases as part of system up." >> /var/log/oracle
        echo "----------------------------------------------------" >> /var/log/oracle
        su - oracle -c dbstart ${ORACLE_HOME} >> /var/log/oracle
        echo "Done."
        echo ""
        echo "----------------------------------------------------" >> /var/log/oracle
        date +"! %T %a %D : Finished." >> /var/log/oracle
        echo "----------------------------------------------------" >> /var/log/oracle
        ;;
  stop)
        echo -n "Shutting Down Oracle Listeners: "
        echo "----------------------------------------------------" >> /var/log/oracle
        date +"! %T %a %D : Shutting Down Oracle Databases as part of system down." >> /var/log/oracle
        echo "----------------------------------------------------" >> /var/log/oracle
        su - oracle -c "lsnrctl stop ${LISTENER_NAME}" >> /var/log/oracle
        echo "Done."
        echo -n "Shutting Down Oracle Databases: "
        su - oracle -c dbshut ${ORACLE_HOME} >> /var/log/oracle
        echo "Done."
        echo ""
        echo "----------------------------------------------------" >> /var/log/oracle
        date +"! %T %a %D : Finished." >> /var/log/oracle
        echo "----------------------------------------------------" >> /var/log/oracle
        ;;
  *)
        echo "Usage: oracle {start|stop}"
        exit 1
esac
bash-4.3# ./oracle.sh start
bash-4.3# ./oracle.sh stop

Enjoy

SCRIPT FOR CHECKING ARCHIVE LOG GENERATION ON ORACLE DATABASE

/////////////////////////On daily basis:

set pagesize 100
ALTER SESSION SET nls_date_format = 'YYYY-MM-DD';
-- As Table
SELECT TRUNC(completion_time) AS time, ROUND(SUM(blocks * block_size)/1024/1024,2) AS size_mb, ROUND(SUM(blocks * block_size)/1024/1024/1024,2) AS size_gb
FROM v$archived_log GROUP BY TRUNC(completion_time) ORDER BY 1 DESC;
-- As csv-File
SELECT TRUNC(completion_time) || ';' || ROUND(SUM(blocks * block_size)/1024/1024,2) || ';' || ROUND(SUM(blocks * block_size)/1024/1024/1024,2)
FROM v$archived_log GROUP BY TRUNC(completion_time) ORDER BY 1 DESC;



//////////////////////////On hourly basis:



set pagesize 100
ALTER SESSION SET nls_date_format = 'YYYY-MM-DD HH24';
-- As Table
SELECT TRUNC(completion_time,'HH24') AS time, ROUND(SUM(blocks * block_size)/1024/1024,2) AS size_mb, ROUND(SUM(blocks * block_size)/1024/1024/1024,2) AS size_gb
FROM v$archived_log GROUP BY TRUNC(completion_time,'HH24') ORDER BY 1 DESC;
-- As csv-File
SELECT TRUNC(completion_time,'HH24') || ';' || ROUND(SUM(blocks * block_size)/1024/1024,2) || ';' || ROUND(SUM(blocks * block_size)/1024/1024/1024,2)
FROM v$archived_log GROUP BY TRUNC(completion_time,'HH24') ORDER BY 1 DESC;

SCRIPT TO CALCULATE UNDO SIZE AND RETENTION TIME IN ORACLE DATABASE FOR EXP-00008: ORACLE error 1555 encountered ORA-01555: snapshot too old: rollback segment number 10 with name "_SYSSMU10$" too small error

//////////////////////maximum time take to execute query in sec


SQL>select max(maxquerylen) from v$undostat;


//////////////////////////Needed UNDO Size in MB


SQL>SELECT
ROUND(d.undo_size/(1024*1024),2) "ACTUAL UNDO SIZE [MByte]",
SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
ROUND((TO_NUMBER(e.value)*TO_NUMBER(f.value)*g.undo_block_per_sec)/(1024*1024),2) "NEEDED UNDO SIZE [MByte]"
FROM
(
SELECT
SUM(a.bytes) undo_size
FROM
v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE
c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.TS# = b.TS#
) d,
v$parameter e,
v$parameter f,
(
SELECT
MAX(undoblks/((end_time-begin_time)*3600*24))
undo_block_per_sec
FROM
v$undostat
) g
WHERE
e.name = 'undo_retention'
AND f.name = 'db_block_size';


///////////////////////Optimal UNDO Retention in seconds





SQL>SELECT
ROUND(d.undo_size/(1024*1024),2) "ACTUAL UNDO SIZE [MByte]",
SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
ROUND((d.undo_size/(TO_NUMBER(f.value)*g.undo_block_per_sec))) "OPTIMAL UNDO RETENTION [Sec]"
FROM
(
SELECT
SUM(a.bytes) undo_size
FROM
v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE
c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.TS# = b.TS#
) d,
v$parameter e,
v$parameter f,
(
SELECT
MAX(undoblks/((end_time-begin_time)*3600*24))
undo_block_per_sec
FROM
v$undostat
) g
WHERE
e.name = 'undo_retention'
AND f.name = 'db_block_size';


/////////////////////for change undo retention time



SQL>ALTER SYSTEM SET UNDO_RETENTION = [values based on your calculation];


/////////////////////////////number of error ora-01555



SQL>select 'Number of "ORA-01555 (Snapshot too old)" encountered since
the last startup of the instance : ' || sum(ssolderrcnt)
from v$undostat;




/////////////////////Actual undo tablespace size


SQL>SELECT SUM(a.bytes/1024/1024) undo_size
  FROM v$datafile a,
       v$tablespace b,
       dba_tablespaces c
WHERE c.contents = 'UNDO'
   AND c.status = 'ONLINE'
   AND b.name = c.tablespace_name
   AND a.ts# = b.ts#;

SCRIPT TO DATAPUMP/NORMAL EXPORT/IMPORT STATUS MONITORING

/////////////import job  status


SELECT
SUBSTR(sql_text, INSTR(sql_text,'INTO "'),50) table_name
, rows_processed
, ROUND( (sysdate-TO_DATE(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60,1) minutes
, TRUNC(rows_processed/((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60)) rows_per_minute
FROM sys.v_$sqlarea
WHERE
sql_text like 'INSERT %INTO "%'
AND command_type = 2
AND open_versions > 0;

///////////export job status

select

   round(sofar/totalwork*100,2)  percent_completed, 

   v$session_longops.* 

from 

   v$session_longops 

where

   sofar <> totalwork 

order by

   target, sid;