Shell Script to Add Date to Filenames

Below Shell Script will append Date to all FileNames in Directory with extension .TXT. Also it will create a folder with the Date and move the files to it.

FILE=”$(date +%Y%m%d)”
cd /bi_dataload
mkdir -p /bi_dataload/BI_$FILE
for f in *.TXT
mv -n “$f” /bi_dataload/BI_$FILE/”${f%%.*}”_”$FILE”.TXT


Rebuild All Indexes on a Schema in Oracle

spool index_rebuild.sql

select ‘alter index ‘||owner||’.’||index_name ||’ rebuild online nologging;’
from dba_indexes
where owner=upper(‘SAMPLE’);
spool off

Monitoring Oracle with oratop

Rick Miners' Oracle Blog


I recently had a situation where I was troubleshooting a system that had fallen victim to some intermittent and very high load averages. At first, there were no apparent reasons for the high load averages, so that sent me searching for some monitoring tools to help identify the issue. Besides using OSWBB and dstat, I also used the oratop utility which turns out to be a great way to get a quick overview and monitor active sessions in near real time in Oracle 11g and 12c databases. It is also RAC and ASM aware.


Here’s the MOS note for more info and downloads.
oratop – Utility for Near Real-time Monitoring of Databases, RAC and Single Instance (Doc ID 1500864.1)

Supported Oracle Releases

11gR2 (,


oratop in Action

There is a user guide available for download on the MOS note, but I will show my favorite options.

View original post 240 more words

First Day and Last Day of Current Month Oracle

Query to Select the First Day and Last Day of the Current Month


select trim(to_date(last_day(sysdate),’DD/MM/YYYY’)) as lastday from dual;

select trunc((sysdate),’month’) as FirstDay from dual;

Move Table to Another Tablespace Online Oracle Database 12c

The new PL/SQL procedure REDEF_TABLE in Oracle 12c  lets you redefine a table online in a single step. It can do the below changes

  • Move table,partition or index to another tablespace
  • Compression type change on a table including for table,partition and index

More information on DBMS_REDFINITION can be accessed on Oracle Support : Master Note:Overview of Online Redefinition of Tables (DBMS_REDEFINITION) (Doc ID 1357825.1)

We will now go ahead and move the table HR.EMP to another tablespace called TBS01 online.

uname => ‘HR’,
tname => ‘EMP’,
table_part_tablespace => ‘TBS01’

uname –> Schema Name in which Table resides
tname –> Table Name
table_part_tablespace –> New Tablespace to which table will be moved
index_tablespace => If you need to move the indexes to another table it can be done using an additional arguement

Data Recovery Advisor


Hello everyone!

During my career as an Oracle DBA, I saw many DBAs using this tool, the Data Recovery Advisor.
This is a tool that can assist with diagnosing media failure, providing RMAN commands to solved the problems.

Let’s start:

View original post 207 more words

Restore Table from RMAN Backup Oracle 12c


One of the amazing features of Oracle Database 12c is that now you can do a logical restore from a physical backup. Before 12c it was not possible to restore a table from a RMAN backup. This feature is amazing speciall when you have a large database and needs to restore a small table. One of the pre-requisites is you must have a Full Backup of the database and also archivelogs after the fully backup must be available. We can proceed to do the restore with below RMAN command

$ rman target /

AUXILIARY DESTINATION ‘/backups/rmanbkp’

HR.EMP is the table to be recovered from the RMAN backup

SYSDATE-0.22 is the time till where the table will be rolled back; this is to specify the point in time for recovery: UNTIL TIME, UNTIL SCN, or UNTIL SEQUENCE can be used

Auxiliary Destination is destination where a instance is created for the recovery process

REMAP TABLE clause is used to specify to restore the table with another name

Using this method even individual table paritions can be restored.