Find and replace a string in several text files

Maybe you are looking for a particular phrase in log files or maybe you are a programmer and need to find some string that is inside many different code files?

Here is how you can find and replace a string in several text files.

For this example, the Oracle DBA wants to replace  strings NSR* in all rman scripts:


for base in /oracle/scripts/rman/rcv/*.rcv
TMP=$(mktemp test.XXXXXX)
sed 's/NSR_SERVER=networker-hme0, NSR_DATA_VOLUME_POOL=BU UNT FULL/NSR_SERVER=freppax-laaf02h, NSR_DATA_VOLUME_POOL=FullHP/g' $base > "$TMP" && mv "$TMP" $base

export PS1="\$ORACLE_SID $LOGNAME@$(hostname):\${PWD}> "
stty erase ^H
export HISTSIZE=100
export EDITOR=vi
set -o vi
alias ll="ls -altr"
umask 022

View Oracle hidden parameters

Hidden parameters in Oracle always start with an underscore.

Résultats de recherche d'images pour « hidden »

It is not possible for the DBA to see the hidden parameters with the SQL*Plus command “show parameter” or by querying v$parameter. Unless the hidden parameter is explicitly set in spfile/init.ora file.

How Can I list all Hidden Parameters set in The database?

As they are explicitely set in the init file, you can create a report that shows all the hidden parameters using the v$parameter view.
The following sql statemant lists undocumented parameters but can also be used to list documented parameters, that can be set in the spfile or init.ora file:

col name for A45
set lines 120
col value for A40
set pagesize 100
select name, value from v$parameter where name like '\_%' escape '\';


How can I list all hidden parameters available?

If you want to list all hidden parameters available for your version along with a description:

substr(ksppinm,1,1) = '_';

How can I set the value of a hidden parameter?

You can change a hidden parameter, the same way as you would any other init.ora parameters but you need to put double quotes for the parameter name:

alter system set "_pga_max_size"=5G scope=spfile sid='*';


A Good DBA needs to know what hidden parameters are set in the database and their values. Especially during upgrade, database migrations or performance tuning problems.

Oracle has hundreds of initialization parameters, which are hidden and undocumented. Many savvy Oracle professionals are known to commonly adjust the hidden parameters to improve the overall performance of their systems.

Disclaimer: It is not recommended to change hidden parameter without consent of Oracle Support since Oracle can make your system unsupported. You can be responsible for data corruption, performance degradation because of bad SQL plans or other problem. the undocumented init parameters are only used in emergencies or to fix a bug. Some of these parameters are Operating system specific and used in unusual recovery situations. Hence, these parameters should be manipulated carefully and preferably not without recommendation from an Oracle Database Administrator.


Author: Vincent Fenoll Oracle DBA

Ultimate Database Health Check (DBMS_HM)

The Oracle DBA wants to run Health Checks command with Health Monitor upon his database.

With Oracle 12c/18c, these checks can be done  on a regular basis daily/monthly:
– DB Structure Integrity Check
– CF Block Integrity Check
– Data Block Integrity Check
– Redo Integrity Check
– Transaction Integrity Check
– Undo Segment Integrity Check
– Dictionary Integrity Check
– ASM Allocation Check

Perhaps you have datafile, dictionary, block, undo, redo, or another corruption in your database? You might actually be running just fine and not even know it.

Oracle Database 12c/18c includes a framework called Health Monitor for running diagnostic checks on your database.


How to run a health check on the Oracle database?

   DBMS_HM.run_check ('Dictionary Integrity Check', 'report_dictionary_integrity');


   DBMS_HM.RUN_CHECK (check_name     => 'Transaction Integrity Check',
                      run_name       => 'my_transaction_run',
                      input_params   => 'TXN_ID=22.87.1');

Viewing the first report in text format with DBMS_HM (HTML & XML format are also available):

SET LONG 100000
SELECT DBMS_HM.get_run_report ('report_dictionary_integrity') FROM DUAL;

Listing all the Health Check executed (Health Monitor View):

SELECT run_id,
  FROM v$hm_run;

Viewing the list of checks that can be done on your database

 SELECT name
  FROM v$hm_check
 WHERE internal_check = 'N';

Health checks accept input parameters, some are mandatory while others are optional.


Displaying parameter information for all health checks

  SELECT check_name, parameter_name,
    FROM v$hm_check_param p, v$hm_check c
   WHERE p.check_id = AND c.internal_check = 'N'

Periodic database health checks help keep your database running smoothly without corruption and prevent more serious conditions from developing later.

Health Monitor checks and examine the several parts of the Oracle database stack. This tool detects data dictionary corruptions, datafile corruptions. It will check logical or physical logical block corruptions and rollback (undo) or redo corruptions.

The health checks generate reports of their findings and, in many cases, recommendations for resolving problems.

For a Healthy database!

Vincent Fenoll – Oracle OCP Database administrator in Montreal

How to Kill a datapump job and cleanup orphaned jobs in Oracle

Download Brave : Secure, Fast & Private Browser with Adblocker

In this article you will learn how to:

  • Kill a datapump job from  dba_datapump_jobs using expdp or with an internal package
  • Cleanup orphaned datapump jobs in Oracle

Perhaps you were looking for stop/drop job in DBMS_SCHEDULER.


How to kill a datapump job in DBA_DATAPUMP_JOBS?

You can kill an oracle datapump job using two methods:
– First method includes killing data pump job via data pump export prompt
– The second method is by running a SQL package in SQL*Plus.

Cancel a datapump job in Oracle

1- Kill Oracle datapump export job from expdp

— Find the job name of the datapump export
select job_name from dba_datapump_jobs;

-- Connect to datapump export and Kill the job
$ expdp system/my-password@ORCL attach=SYS_EXPORT_FULL_05
Export> KILL_JOB
Are you sure you wish to stop this job ([yes]/no): yes

2- How to Interrupt and Kill a Data Pump Job from a SQL package

— Find the job name of the datapump export
select job_name, owner_name from dba_datapump_jobs;

-- Connect to datapump export and Kill the job

After stopping/cancelling the datapump job, oracle removes the dmp files that have been produced. You can check again the dba_datapump_jobs view to verify:
SQL> select * from dba_datapump_jobs;

More info on view dba_datapump_jobs in official the Oracle Documentation.


How can I cleanup old (orphaned) datapump jobs in DBA_DATAPUMP_JOBS ?

Cause: In many cases you have stop Oracle data pump jobs, shutdown database during export/import or use undocumented parameter KEEP_MASTER=Y. In these cases the master table remains in the database and it’s better to delete them.

Below is a step-by-step instruction on how to do this.

Step 1. Determine in SQL*Plus if Data Pump jobs exist in the dictionary

Identify these jobs and ensure that the listed jobs in dba_datapump_jobs are not export/import Data Pump jobs that are active: status should be ‘NOT RUNNING’ and not attached to a session:

SET lines 150
COL owner_name FORMAT a10
COL job_name FORMAT a20
COL operation FORMAT a10

SELECT owner_name, job_name, operation
FROM dba_datapump_jobs where state='NOT RUNNING' and attached_sessions=0;

Step 2: Drop the master tables

set head off
SELECT 'drop table ' || owner_name || '.' || job_name || ';'
FROM dba_datapump_jobs WHERE state='NOT RUNNING' and attached_sessions=0;

Execute the generated script.

Step 3: Identify orphan DataPump external tables

Check  and drop external tables created for datapump jobs with select  object_name, created from dba_objects where object_name like ‘ET$%’

Step 4: Purge recycle bin

If using recycling bin:
SELECT ‘purge table ‘ || owner_name || ‘.’ || ‘”‘ || job_name || ‘”;’
FROM dba_datapump_jobs WHERE state=’NOT RUNNING’ and attached_sessions=0;

Step 5: Confirm that the job has been removed

Run sql statement from step 1.

Now you should be able to run your script with the same job name without any issues.

Hope this post will help!

Author: Vincent Fenoll – Oracle DBA

Compatibility:  Oracle Database – Standard/Enterprise Edition – Version 10g to 18c [Release 10.1 to 12.2/18]

Lock, expire and delete Linux account

delete Linux account

Sometimes the Oracle DBA has to I delete a Unix user account under Linux operating systems including the home directory.

How do I expire, delete or remove a user’s access from my server?

Deleting user account in Linux is an administrative task to remove user login credentials from system configuration files such as /etc/passwd, /etc/shadow and files which are owned by that particular user from the Unix server.

These command must be run as root user on Linux.

# Just Lock the password
usermod -L myusername
# Just Expire the account
chage -E0 myusername
# Delete the account. userdel is a low level utility for removing users. On Debian, administrators should usually use deluser instead.

# Be careful, User deletion is irreversible!
userdel myusername

-- Use these 2 options to delete that user's home directory and the spool of mails
-r : Remove Unix user account including home directory and mail spool
-f : Delete Linuxuser account with force removal of files

The userdel command modifies the following system account files:
/etc/group, /etc/login.defs, /etc/shadow, /etc/subgid and /etc/subuid.


How to clean associated objects?

If you want to clean other objects like cron jobs, files, print jobs; you will have to do it manually like that.

How to clean cron table
crontab -r -u myusername

How to clean print jobs
lprm myusername

How to change the owner of files owned by myusername
find / -user myusername -exec chown newUserName:newGroupName {} \;


Author: Vincent Fenoll, Oracle DBA in Montreal