Wrote a little pl/sql program after several years… glad it worked as expected… thought of sharing the code, might be useful for someone out there…

The requirement is to drop tables that are older than 60 days from a schema named CR_BKUP periodically. Did not want to just drop the tables, so taking a backup of the whole schema using data pump api and then dropping the tables. The table names dropped are also written to a log file. The code is created as a procedure, so that I can schedule it using the database scheduler.

If I am violating any coding standards or a better way to accomplish the result, please let me know.

This code is also a quick demonstration of how to use DBMS_DATAPUMP and UTL_FILE programs.

 

 

For this procedure to work, the schema owning this procedure should be granted these privileges explicitly.

 

 

%d bloggers like this: