How-to bulk delete ( or archive ) as fast as possible, using minimal undo, redo and temp

Share this on .. Tweet about this on TwitterShare on LinkedInShare on FacebookShare on Google+Email this to someoneShare on TumblrBuffer this page

Deleting some rows or tens of millions of rows from an Oracle database should be treated in a completely different fashion. Though the delete itself is technically the same, maintaining indexes and validating constraints may have such a time and resource consuming influence that a vast amount of undo and redo is necessary when deleting millions of rows, in contrast to just a bit when you delete some.

A classic recipe in order to limit the effect on undo and temp is to commit in batches. The result is that the delete is executed in the context of more than one transaction, with these transactions actually done sequentially, and thus saving on undo and temp. But this delete method is not very efficient as to using database resources, it generates a lot more redo than necessary, and it is quite detrimental to transaction time.
Another option is to chop the delete in parts and execute the parts in parallel… this way you are using your resources a lot better, but by hitting undo and temp at the same time, you end up using a lot of undo and temp again, as before. The best option probably is to setup partitioning… this way the delete will be converted into a DDL statement – alter table x drop partition p – and excessive generation of undo, redo and temp won’t be an issue any more. But this requires the Enterprise Edition and on top of that a separate license for the Partitioning Option, some customers cannot afford.

So let us assume this is an Oracle Standard Edition Database, and you want the delete of 10 million rows to be just one fast transaction, with no more than 2-4GB undo and 2-4GB temp usage, and redo should be as minimal as possible. This is how you could do it, presuming there is a short window allowing you to drop all indexes, with no exception, so even a drop of primary, unique and foreign key indexes is allowed.
During the delete an application that touches this table will slow down because of the dropped indexes, and the table reorganization after the delete with the “move” command will offline the table for a short time. If this is not allowed, you could use the dbms_redefinition package to reorganize the table online, but at the expense of considerably slowing down overall transaction time.

First of all, do a CXTAS of all rows you want to delete, including the rowid’s. You may wonder what CXTAS means… Create eXternal Table As Select. Because of all indexes still active the where clause predicate should be fast, and because this is effectively a DDL statement virtually no redo is generated. By the way, if no archiving or backup of the data to be deleted is needed, consider creating an external table with rowid’s only.
Analyze the external table.
Secondly, disable primary, unique and foreign key constraints, dropping the index. Also drop all other indexes or set unusable. Then delete all rows in the original table with rowid’s saved in the external table you just made, and commit when finished.
Thirdly, move the original table in nologging mode so as to reorganize and lower its high watermark, enable all constraints again and recreate or rebuild – if set unusable before – all other indexes.
Analyze the table.
And last, if the deleted data must be archived, move the external table file dump to some other database or filesystem.

Checkout the following code… this cleanup procedure is actually in use at one of our customers. It is a dynamic setup for cleanup of any table in any schema, provided the table contains just one (primary key ) index, possibly a lob or clob, and a timestamp or date column. If your environment demands another setup, feel free to adjust.

CREATE OR REPLACE PROCEDURE CLEANUP_TABLE
  ( P_schema       VARCHAR2    := 'ETL'
  , P_table_name   VARCHAR2    := 'LOG'
  , P_column_name  VARCHAR2    := 'TIMESTAMP'
  , P_constraint   VARCHAR2    := 'LOG_PK'
  , P_directory    VARCHAR2    := 'ETL_BCK_DIR'
  , P_keep_data    PLS_INTEGER := 1 -- 1 month
  , P_unload2file  PLS_INTEGER := 0
  , P_rollback     PLS_INTEGER := 0 )
AS
  v_curr_schema          VARCHAR2(30 CHAR)  := SYS_CONTEXT('USERENV','CURRENT_SCHEMA');
  v_directory            VARCHAR2(30 CHAR)  := UPPER(TRIM(P_directory));
  v_schema               VARCHAR2(30 CHAR)  := UPPER(TRIM(P_schema));
  v_table_name           VARCHAR2(30 CHAR)  := UPPER(TRIM(P_table_name));
  v_column_name          VARCHAR2(30 CHAR)  := UPPER(TRIM(P_column_name));
  v_constraint           VARCHAR2(30 CHAR)  := UPPER(TRIM(P_constraint));
  v_dmp                  VARCHAR2(100 CHAR) := LOWER(TRIM(P_table_name))||'.dmp';
  v_external_table_name  VARCHAR2(30 CHAR)  := 'XDEL_'||UPPER(TRIM(substr(P_table_name,1,25)));
  v_stat                 VARCHAR2(4000 CHAR);

-----------------------------------------------------------------------------------------------------
PROCEDURE Delete_Backupped_Table_Data
IS

BEGIN

  BEGIN
    EXECUTE IMMEDIATE 'DROP TABLE '||v_external_table_name||' PURGE';
  EXCEPTION
  WHEN OTHERS THEN
    NULL;
  END;

  BEGIN
    UTL_FILE.FREMOVE ( v_directory, v_dmp );
  EXCEPTION
  WHEN OTHERS THEN
    NULL;
  END;

END Delete_Backupped_Table_Data;
----------------------------------------------------------------------------------------------------
PROCEDURE Backup_Table_Data_2b_Deleted
IS
BEGIN

IF unload2file = 1 THEN
  v_stat := 'CREATE TABLE '||v_external_table_name||chr(10)
          ||'ORGANIZATION EXTERNAL'||chr(10)
          ||'('||chr(10)
          ||'TYPE ORACLE_DATAPUMP'||chr(10)
          ||'DEFAULT DIRECTORY '||v_directory||chr(10)
          ||'ACCESS PARAMETERS ( NOLOGFILE )'||chr(10)
          ||'LOCATION ( '''||v_dmp||''' )'||chr(10)
          ||')'||chr(10)
          ||'AS SELECT ROWID RID, t.* FROM '||v_schema||'.'||v_table_name||' t'||chr(10)
          ||'WHERE TO_NUMBER(TO_CHAR('||v_column_name||',''YYYYMMDD'')) < '||chr(10)
          ||'TO_NUMBER(TO_CHAR(TRUNC(ADD_MONTHS(SYSDATE, -'||P_keep_data||')),''YYYYMMDD''))';
ELSE
  v_stat := 'CREATE TABLE '||v_external_table_name||chr(10)
          ||'ORGANIZATION EXTERNAL'||chr(10)
          ||'('||chr(10)
          ||'TYPE ORACLE_DATAPUMP'||chr(10)
          ||'DEFAULT DIRECTORY '||v_directory||chr(10)
          ||'ACCESS PARAMETERS ( NOLOGFILE )'||chr(10)
          ||'LOCATION ( '''||v_dmp||''' )'||chr(10)
          ||')'||chr(10)
          ||'AS SELECT ROWID RID FROM '||v_schema||'.'||v_table_name||chr(10)
          ||'WHERE TO_NUMBER(TO_CHAR('||v_column_name||',''YYYYMMDD'')) < '||chr(10)
          ||'TO_NUMBER(TO_CHAR(TRUNC(ADD_MONTHS(SYSDATE, -'||P_keep_data||')),''YYYYMMDD''))';
END IF;
  dbms_output.put_line ( v_stat );
  execute immediate v_stat;

END Backup_Table_Data_2b_Deleted;
-----------------------------------------------------------------------------------------------------
PROCEDURE Exec_Cleanup
IS
BEGIN

-- drop index
  v_stat := ' ALTER TABLE '||v_schema||'.'||v_table_name||' MODIFY CONSTRAINT '||v_constraint||' DISABLE DROP INDEX';
  dbms_output.put_line ( v_stat );
  execute immediate v_stat;

-- delete rows
  v_stat := ' DELETE '||v_schema||'.'||v_table_name||chr(10)
          ||' WHERE ROWID IN ( SELECT RID FROM '||v_external_table_name||' )';
  dbms_output.put_line ( v_stat );
  execute immediate v_stat;
  if P_rollback = 1 then
    rollback;
  end if;

-- move (reorganize) table and clobs
  v_stat := ' ALTER TABLE '||v_schema||'.'||v_table_name||' MOVE NOLGGING';
  dbms_output.put_line ( v_stat );
  execute immediate v_stat;
  for i in ( select column_name, tablespace_name
             from dba_lobs
             where owner = v_schema
             and table_name = v_table_name )
  loop
    v_stat := ' ALTER TABLE '||v_schema||'.'||v_table_name||' MOVE NOLOGGING '||chr(10)
            ||' LOB ('||i.column_name||')  STORE AS ( TABLESPACE '||i.tablespace_name||' )';
    dbms_output.put_line ( v_stat );
    execute immediate v_stat;
  end loop;

-- create pk index again
  v_stat := ' ALTER TABLE '||v_schema||'.'||v_table_name||' MODIFY CONSTRAINT '||v_constraint||' ENABLE';
  dbms_output.put_line ( v_stat );
  execute immediate v_stat;

END Exec_Cleanup;
-----------------------------------------------------------------------------------------------------

BEGIN
  dbms_output.put_line ( '************************************************************************' );
  dbms_output.put_line ( TO_CHAR(SYSDATE,'DY DD-MON-YYYY HH24:MI:SS')||' >> START CLEANUP TABLE '||v_schema||'.'||v_table_name );
  dbms_output.put_line ( '************************************************************************' );
-- Drop external table and dump file, if they already exist
  Delete_Backupped_Table_Data;
-- Create external table and dump file, using a select stat of the data 2b deleted
  Backup_Table_Data_2b_Deleted;
-- Analyze the external table you just made
  DBMS_STATS.GATHER_TABLE_STATS( v_curr_schema, v_external_table_name );
-- Delete table data, using the rowids in the external table
  Exec_Cleanup;
-- Analyze the table after reorg
  DBMS_STATS.GATHER_TABLE_STATS( v_schema, v_table_name );
  dbms_output.put_line ( '************************************************************************' );
  dbms_output.put_line ( TO_CHAR(SYSDATE,'DY DD-MON-YYYY HH24:MI:SS')||' >> END CLEANUP TABLE '||v_schema||'.'||v_table_name );
  dbms_output.put_line ( '************************************************************************' );

EXCEPTION
  WHEN OTHERS THEN
  RAISE_APPLICATION_ERROR( -20010, SQLERRM );
END;
/

Share this on .. Tweet about this on TwitterShare on LinkedInShare on FacebookShare on Google+Email this to someoneShare on TumblrBuffer this page

How-to set the current database schema of an application using a global context

Share this on .. Tweet about this on TwitterShare on LinkedInShare on FacebookShare on Google+Email this to someoneShare on TumblrBuffer this page

Question:
We have customers who want to work with different database schemas and we want to determine dynamically – depending on a choice during or after login – which database schema the application in a given session should use. Is it possible to set this up in the database?
Answer:
Yes, with a global context, available to all sessions in the instance, and parameter client id to distinguish between customers.
Question:
How?
Answer:
Like in the following example, using a 11gR2 XE database.

CREATE USERS

-- with sys as sysdba: create admin user "sys_gctx" for the global context
conn sys/xe@localhost:1521/xe as sysdba
CREATE USER SYS_GCTX
IDENTIFIED BY xe
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT
ACCOUNT UNLOCK;
GRANT CREATE SESSION
    , CREATE ANY CONTEXT
    , CREATE PROCEDURE
    , CREATE TRIGGER
    , ADMINISTER DATABASE TRIGGER TO SYS_GCTX;
GRANT EXECUTE ON DBMS_SESSION TO SYS_GCTX;

-- with sys as sysdba: create application user "app_usr"
CREATE USER APP_USR
IDENTIFIED BY xe
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT
ACCOUNT UNLOCK;
GRANT CREATE SESSION, SELECT ANY TABLE TO APP_USR;

CREATE AND FILL GLOBAL CONTEXT

-- with sys_gctx: create the global context
conn sys_gctx/xe@localhost:1521/xe
CREATE CONTEXT GCTX_SCHEMA USING SET_GCTX_SCHEMA ACCESSED GLOBALLY;

-- with sys_gctx: create a procedure to set the global context
CREATE OR REPLACE PROCEDURE SET_GCTX_SCHEMA
 ( P_schema IN VARCHAR2,
   P_client IN VARCHAR2 ) 
AS
BEGIN
   DBMS_SESSION.SET_CONTEXT ( NAMESPACE => 'GCTX_SCHEMA',
                              ATTRIBUTE => 'SCHEMA_NAME',
                              VALUE     => P_schema,
                              CLIENT_ID => P_client );
END;
/

-- with sys_gctx: fill the global context ( stays in SGA )
--                at startup of the application, or with
--                an "after startup of database" trigger
BEGIN
  SET_GCTX_SCHEMA ('ALTER SESSION SET current_schema=HR','CLIENT_A');
  SET_GCTX_SCHEMA ('ALTER SESSION SET current_schema=APEX_040000','CLIENT_B');
END;
/

SETUP CLIENT_A, AND CONFIRM THE CHANGE OF CURRENT SCHEMA

-- with app_usr: setup the client as client_a and set the context value for this client
conn app_usr/xe@localhost:1521/xe
begin
  dbms_session.set_identifier ('CLIENT_A');
  execute immediate sys_context( 'GCTX_SCHEMA', 'SCHEMA_NAME');
end;
/

-- with app_usr: check if the current schema of this "app_usr" session is indeed HR
select sys_context('USERENV', 'session_user') from dual;
select sys_context('USERENV', 'current_schema') from dual;

SETUP CLIENT_B, AND CONFIRM THE CHANGE OF CURRENT SCHEMA

-- with app_usr: setup the client as client_b and set the context value for this client
begin
  dbms_session.set_identifier ('CLIENT_B');
  execute immediate sys_context( 'GCTX_SCHEMA', 'SCHEMA_NAME');
end;
/

-- with app_usr: check if the current schema of this "app_usr" session is indeed APEX_040000
select sys_context('USERENV', 'session_user') from dual;
select sys_context('USERENV', 'current_schema') from dual;

REMOVE CLUTTER

-- with sys as sysdba
-- remove users and context
conn sys/xe@localhost:1521/xe as sysdba
drop user SYS_GCTX cascade;
drop user APP_USR cascade;
drop context GCTX_SCHEMA;

With many thanks to Lucas Jellema who posed a very similar question on our Yammer site, with just one distinction… not
Is it possible to set this up in the database?” but “Is it possible to set this up in ADF BC?“, and who already published on global contexts on this site here.

Share this on .. Tweet about this on TwitterShare on LinkedInShare on FacebookShare on Google+Email this to someoneShare on TumblrBuffer this page

SQL> Select * From Alert_XML_Errors;

Share this on .. Tweet about this on TwitterShare on LinkedInShare on FacebookShare on Google+Email this to someoneShare on TumblrBuffer this page

Once you are able to show the xml version of the alert log as data in database table Alert_XML, it would be nice to checkout the errors with accompanying timestamps from within view Alert_XML_Errors. Like this, with the help of 2 types and a pipelined function.

su - oracle
. oraenv [ orcl ]
[oracle@localhost ~]$ sqlplus harry/*****
....
SQL> desc alert_xml
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
TEXT                                               VARCHAR2(400 CHAR)

SQL> CREATE OR REPLACE TYPE v2_row AS OBJECT ( text varchar2(400));
/

Type created.

SQL> CREATE OR REPLACE TYPE v2_table AS TABLE OF v2_row;
/

Type created.

SQL> CREATE OR REPLACE FUNCTION Get_Errors
( P sys_refcursor )
RETURN v2_table PIPELINED
IS
out_rec  v2_row := v2_row(NULL);
this_rec alert_xml%ROWTYPE;
currdate VARCHAR2(400) := 'NA';
last_printed_date VARCHAR2(400) := currdate;
testday VARCHAR2(3);
testerr VARCHAR2(4);
firstdate BOOLEAN := TRUE;
BEGIN
currdate := 'NA';
last_printed_date := currdate;
LOOP
FETCH p INTO this_rec;
EXIT WHEN p%NOTFOUND;

this_rec.text := LTRIM(this_rec.text);

-- check if this line contains a date stamp
testday := SUBSTR(this_rec.text,1,3);
IF testday = '201'
THEN
-- show dates as in de text version of the alert log
currdate := to_char(to_date(substr(this_rec.text,1,19),'YYYY-MM-DD HH24:MI:SS'),'Dy Mon DD hh24:mi:ss yyyy','NLS_DATE_LANGUAGE = AMERICAN');
ELSIF
testday = 'Sat'
OR testday = 'Sun'
OR testday = 'Mon'
OR testday = 'Tue'
OR testday = 'Wed'
OR testday = 'Thu'
OR testday = 'Fri'
THEN
currdate := this_rec.text;
END IF;

testerr := SUBSTR(this_rec.text,1,4);
IF testerr = 'ORA-'
OR testerr = 'TNS-'
THEN
IF last_printed_date != currdate
OR ( currdate != 'NA' AND firstdate )
THEN
last_printed_date := currdate;
firstdate := FALSE;
out_rec.text := '****';
PIPE ROW(out_rec);
out_rec.text := currdate;
PIPE ROW(out_rec);
out_rec.text := '****';
PIPE ROW(out_rec);
END IF;
out_rec.text := this_rec.text;
pipe ROW(out_rec);

END IF;
END LOOP;

CLOSE P;
RETURN;
END Get_Errors;
/

Function created.

SQL> CREATE OR REPLACE FORCE VIEW ALERT_XML_ERRORS
AS
SELECT "TEXT"
FROM TABLE (get_errors (CURSOR (SELECT * FROM alert_xml)));

View created.

And checkout the errors now:

SQL> set pagesize 0
SQL> select * from alert_xml_errors;
****
Tue Aug 26 11:01:08 2014
****
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/u02/oradata/orcl/users01.dbf'
ORA-27037: unable to obtain file status
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/u02/oradata/orcl/users01.dbf'
ORA-1157 signalled during: ALTER DATABASE OPEN...
****
Tue Aug 26 11:12:51 2014
****
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/u02/oradata/orcl/users01.dbf'
ORA-27037: unable to obtain file status
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/u02/oradata/orcl/users01.dbf'
ORA-1157 signalled during: ALTER DATABASE OPEN...
****
Tue Aug 26 13:39:36 2014
****
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/u02/oradata/orcl/users01.dbf'
ORA-27037: unable to obtain file status
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/u02/oradata/orcl/users01.dbf'
ORA-1157 signalled during: ALTER DATABASE OPEN...
****

< snip >

SQL>

And yes, the pipelined function will only work till 2020 on the xml version of the alert log – see if you can find the code line! – , and yes, it should be functional on the text version of the alert log too, provided the external table describes like alert_xml.

Share this on .. Tweet about this on TwitterShare on LinkedInShare on FacebookShare on Google+Email this to someoneShare on TumblrBuffer this page

SQL> Select * From Alert_XML;

Share this on .. Tweet about this on TwitterShare on LinkedInShare on FacebookShare on Google+Email this to someoneShare on TumblrBuffer this page

By mapping an external table to some text file, you can view the file contents as if it were
data in a database table. External tables are available since Oracle 9i Database, and from Oracle
11gR2 Database on, it is even possible to do some inline preprocessing on the file.

The following example of this feature picks up on standard output of shell script “get_alert_xml.sh”.
It isn’t referencing any file, but take notice of the fact that an empty “dummyfile” must still be
present and readable by oracle. By pre-executing some ADRCI commands and redirecting output to screen,
external table Alert_XML will show the last 7 days of entries of the xml version of the alert log.

su - oracle
. oraenv [ orcl ]

$ cd /u01/app/oracle/admin/scripts
$ touch dummyfile
$ echo '#!/bin/sh'                                                                     > get_alert_xml.sh
$ echo 'ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1'                          >> get_alert_xml.sh
$ echo 'DIAG_HOME=diag/rdbms/orcl/orcl'                                               >> get_alert_xml.sh
$ echo 'DAYS=\\"originating_timestamp > systimestamp-7\\"'                            >> get_alert_xml.sh
$ echo '$ORACLE_HOME/bin/adrci exec="set home $DIAG_HOME;show alert -p $DAYS -term;"' >> get_alert_xml.sh
$ chmod 744 get_alert_xml.sh
$ sqlplus / as sysdba
SQL> create directory exec_dir as '/u01/app/oracle/admin/scripts';
SQL> grant read,execute on directory exec_dir to harry;
SQL> connect harry/****
SQL> CREATE TABLE ALERT_XML ( TEXT VARCHAR2(400 CHAR) )
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY EXEC_DIR
ACCESS PARAMETERS
( RECORDS DELIMITED BY NEWLINE
PREPROCESSOR EXEC_DIR:'get_alert_xml.sh'
nobadfile
nodiscardfile
nologfile
)
LOCATION ('dummyfile')
)
REJECT LIMIT UNLIMITED
NOPARALLEL
NOMONITORING;
SQL> select * from alert_xml;

TEXT
--------------------------------------------------------------------------------

ADR Home = /u01/app/oracle/diag/rdbms/orcl/orcl:
*************************************************************************
2014-08-26 10:21:19.018000 +02:00
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 2
Using LOG_ARCHIVE_DEST_1 parameter default value as USE_DB_RECOVERY_FILE_DEST
2014-08-26 10:21:20.066000 +02:00

> snip <

SQL>

Check out Alert_XML_Errors here.

—–Add-on 5 September 2014——
if confronted with more than 1 database instance on the database server, you can either use 1 dummyfile for all instances and a different shell script for each instance in order to set the correct DIAG HOME, or you use a different LOCATION file for each instance, and reference this in one shell script. I’d like to opt for the latter, because the LOCATION file only has to contain the instance name, so it’s lesser code in total.

For instance, with LOCATION (‘orcl.txt’), and file orcl.txt just containing the instance name orcl, the following shell script code:

 
ORACLE_SID=`/bin/cat $1`         
ORACLE_DBS=`/usr/bin/expr $ORACLE_SID | /usr/bin/tr '[:lower:]' '[:upper:]' `
DIAG_HOME=diag/rdbms/$ORACLE_DBS/$ORACLE_SID

generates this DIAG_HOME: diag/rdbms/ORCL/orcl

Share this on .. Tweet about this on TwitterShare on LinkedInShare on FacebookShare on Google+Email this to someoneShare on TumblrBuffer this page

Fast delete of many files in LINUX and WINDOWS

Share this on .. Tweet about this on TwitterShare on LinkedInShare on FacebookShare on Google+Email this to someoneShare on TumblrBuffer this page

One of my customers ( Oracle Database 11gR2 on LINUX ) never noticed the Oracle software LUN filling up until a “df -h” reported 97% used, wondered why, and of course wanted it to be solved asap. Well, after some investigation I found the adump directory to be the problem.
At this site, database auditing is active, and although a proper cleanup and archiving of the audit log files is in place, this process stalls from time to time for reasons unknown. In this case the result was an adump directory with just under 700.000 files!

Removing the audit files proved to be a problem with LINUX command “rm”, because of the Error: “Argument list too long”. This can be solved with a combination of find and exec, for instance, with adump as current directory, “[ find ./ -type f -exec rm {} \;” or “[find ./ -type f –delete ]”. But I wasn’t very impressed by the performance, and looked for better and faster… and this is what I came to use:

[oracle@PRD ~]$
[oracle@PRD ~]$ mkdir /tmp/empty
[oracle@PRD ~]$ ls /u01/app/oracle/admin/PRODDB/adump | wc -l
694088
[oracle@PRD ~]$ time rsync -aq --include=*.aud --include=*.xml --exclude=* --delete /tmp/empty/ /u01/app/oracle/admin/PRODDB/adump/

real 0m39.604s
user 0m2.597s
sys 0m19.086s
[oracle@PRD ~]$ ls /u01/app/oracle/admin/PRODDB/adump | wc -l
15
[oracle@PRD ~]$ rmdir /tmp/empty
[oracle@PRD ~]$

As you can see, this “rsync” statement is really fast, it takes a mere 20 seconds to remove all files, and can be configured to delete files with certain extensions, leaving any other file in the directory. The trick is that you synchronize an empty directory ( /tmp/empty ) with another directory ( /u01/…./adump ), and telling rsync with “–delete” to remove certain files from adump ( see the include list ), not present in the empty directory. Result is the delete of all files, configured in the include list. Rather surprising is the fact that you can do the same trick with robocopy under Windows, as demonstrated:

-1- Create empty directory and test directory with 6 test files and 3 different extensions

C:\Users\harry_d>
C:\Users\harry_d>mkdir test empty
C:\Users\harry_d\test>type nul> test01.xml
C:\Users\harry_d\test>type nul> test02.xml
C:\Users\harry_d\test>type nul> test01.aud
C:\Users\harry_d\test>type nul> test02.aud
C:\Users\harry_d\test>type nul> test01.txt
C:\Users\harry_d\test>type nul> test02.txt

C:\Users\harry_d\test>dir
Volume in drive C has no label.
Volume Serial Number is 90E1-85B4
Directory of C:\Users\harry_d\test
30-03-2014 14:56 .
30-03-2014 14:56 ..
30-03-2014 14:55 0 test01.aud
30-03-2014 14:55 0 test01.txt
30-03-2014 14:55 0 test01.xml
30-03-2014 14:55 0 test02.aud
30-03-2014 14:56 0 test02.txt
30-03-2014 14:55 0 test02.xml
6 File(s) 0 bytes
2 Dir(s) 26.941.235.200 bytes free
C:\Users\harry_d\test>

-2- Remove the 2 files with .aud extensie

C:\Users\harry_d\test>
C:\Users\harry_d\test>robocopy C:\Users\harry_d\empty\ C:\Users\harry_d\test\ *.aud /purge /e /r:0 /w:0
C:\Users\harry_d\test>
-----------------------------------------------------------------------------
ROBOCOPY :: Robust File Copy for Windows
-------------------------------------------------------------------------------
Started : Sun Mar 30 15:03:29 2014
Source : C:\Users\harry_d\empty\
Dest : C:\Users\harry_d\test\
Files : *.aud
Options : /S /E /COPY:DAT /PURGE /R:0 /W:0
------------------------------------------------------------------------------
0 C:\Users\harry_d\empty\
*EXTRA File 0 test01.aud
*EXTRA File 0 test02.aud
------------------------------------------------------------------------------
Total Copied Skipped Mismatch FAILED Extras
Dirs : 1 0 1 0 0 0
Files : 0 0 0 0 0 2
Bytes : 0 0 0 0 0 0
Times : 0:00:00 0:00:00 0:00:00 0:00:00
Ended : Sun Mar 30 15:03:29 2014
C:\Users\harry_d\test>

… The 2 .aud files are deleted:

C:\Users\harry_d\test>
C:\Users\harry_d\test>dir
Volume in drive C has no label.
Volume Serial Number is 90E1-85B4
Directory of C:\Users\harry_d\test
30-03-2014 15:03 .
30-03-2014 15:03 ..
30-03-2014 14:55 0 test01.txt
30-03-2014 14:55 0 test01.xml
30-03-2014 14:56 0 test02.txt
30-03-2014 14:55 0 test02.xml
4 File(s) 0 bytes
2 Dir(s) 26.941.235.200 bytes free
C:\Users\harry_d\test>

-3- Remove files with .aud en .xml extension… notice the fact that all files with .aud extension are already deleted

C:\Users\harry_d\test>
C:\Users\harry_d\test>robocopy C:\Users\harry_d\empty\ C:\Users\harry_d\test\ *.aud *.xml /purge /e /r:0 /w:0
-------------------------------------------------------------------------------
ROBOCOPY :: Robust File Copy for Windows
-------------------------------------------------------------------------------
Started : Sun Mar 30 15:04:50 2014
Source : C:\Users\harry_d\empty\
Dest : C:\Users\harry_d\test\
Files : *.aud
*.xml
Options : /S /E /COPY:DAT /PURGE /R:0 /W:0
------------------------------------------------------------------------------
0 C:\Users\harry_d\empty\
*EXTRA File 0 test01.xml
*EXTRA File 0 test02.xml
------------------------------------------------------------------------------
Total Copied Skipped Mismatch FAILED Extras
Dirs : 1 0 1 0 0 0
Files : 0 0 0 0 0 2
Bytes : 0 0 0 0 0 0
Times : 0:00:00 0:00:00 0:00:00 0:00:00
Ended : Sun Mar 30 15:04:50 2014
C:\Users\harry_d\test>

… Now the 2 .xml files are also deleted… only 2 .txt files remaining:

C:\Users\harry_d\test>
C:\Users\harry_d\test>dir
Volume in drive C has no label.
Volume Serial Number is 90E1-85B4
Directory of C:\Users\harry_d\test
30-03-2014 15:04 .
30-03-2014 15:04 ..
30-03-2014 14:55 0 test01.txt
30-03-2014 14:56 0 test02.txt
2 File(s) 0 bytes
2 Dir(s) 26.941.235.200 bytes free
C:\Users\harry_d\test>

-4- Remove all files with .aud and .xml and .txt extension … but no output this time!

C:\Users\harry_d\test>
C:\Users\harry_d\test>robocopy C:\Users\harry_d\empty\ C:\Users\harry_d\test\ *.aud *.xml *.txt /purge /e /r:0 /w:0 > nul
C:\Users\harry_d\test>

… The remaining 2 .txt files are deleted… no files left!…:

C:\Users\harry_d\test>dir
Volume in drive C has no label.
Volume Serial Number is 90E1-85B4
Directory of C:\Users\harry_d\test
30-03-2014 15:06 .
30-03-2014 15:06 ..
0 File(s) 0 bytes
2 Dir(s) 26.941.194.240 bytes free
C:\Users\harry_d\test>cd ..
C:\Users\harry_d>rmdir empty test
C:\Users\harry_d>

Use the provided code at your own discretion, and be careful. Rsync and robocopy are both very powerful commands, and should be tested properly before applied in production environments. For both rsync and robocopy this delete|purge trick is a nice but not well-known  feature, because it is more of an option in synchronizing or backing up directories or even complete file systems. But there’s no harm in picking up such an option, and putting it to good use where it’s clearly very useful!

Share this on .. Tweet about this on TwitterShare on LinkedInShare on FacebookShare on Google+Email this to someoneShare on TumblrBuffer this page