Category: Script

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.

/* Formatted */
CREATE OR REPLACE PROCEDURE XX_DROP_CR_BKUP_TABLES (
   threshold_days NUMBER DEFAULT 60)
IS
   /******************************************************************************
      NAME:       XX_DROP_CR_BKUP_TABLES
      PURPOSE:    Drop tables under CR_BKUP schema older than 60 days

   ******************************************************************************/

   CURSOR tablist
   IS
      SELECT owner || '.' || object_name table_name
        FROM dba_objects
       WHERE     owner = 'CR_BKUP'
             AND object_type = 'TABLE'
             AND last_ddl_time < (SYSDATE - threshold_days);

   log_file   UTL_FILE.FILE_TYPE;
BEGIN
   -- Export all the tables belonging to CR_BKUP schema to file system
   -- This dump file will be overwritten next time the job runs
   -- OS file backup will copy to the backup media
   DECLARE
      dp_file   NUMBER;
      jstatus   VARCHAR2 (200);
      ltmp      VARCHAR2 (200);
   BEGIN
      EXECUTE IMMEDIATE
         'CREATE OR REPLACE DIRECTORY CR_BKUP_DIR AS ''/u01/app/oracle/expdp''';

      BEGIN
         SELECT table_name
           INTO ltmp
           FROM user_tables
          WHERE table_name = 'CR_BKUP_TABLE_EXP';

         EXECUTE IMMEDIATE 'DROP TABLE CR_BKUP_TABLE_EXP';
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            NULL;
      END;

      --utl_file.fremove( 'CR_BKUP_DIR', 'cr_bkup_tabs.dmp' );
      --utl_file.fremove( 'CR_BKUP_DIR', 'cr_bkup_tabs.log' );

      dp_file :=
         DBMS_DATAPUMP.open (OPERATION   => 'EXPORT',
                             JOB_MODE    => 'SCHEMA',
                             JOB_NAME    => 'CR_BKUP_TABLE_EXP');

      DBMS_DATAPUMP.add_file (
         HANDLE      => dp_file,
         FILENAME    => 'cr_bkup_tabs.dmp',
         DIRECTORY   => 'CR_BKUP_DIR',
         FILETYPE    => DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE,
         REUSEFILE   => 1);
      DBMS_DATAPUMP.add_file (
         HANDLE      => dp_file,
         FILENAME    => 'cr_bkup_tabs.log',
         DIRECTORY   => 'CR_BKUP_DIR',
         FILETYPE    => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);
      DBMS_DATAPUMP.metadata_filter (HANDLE   => dp_file,
                                     NAME     => 'SCHEMA_LIST',
                                     VALUE    => '''CR_BKUP''');
      DBMS_DATAPUMP.start_job (HANDLE => dp_file);
      DBMS_DATAPUMP.wait_for_job (HANDLE => dp_file, JOB_STATE => jstatus);
      DBMS_DATAPUMP.detach (HANDLE => dp_file);
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_DATAPUMP.detach (HANDLE => dp_file);
         RAISE;
   END;

   -- Export completed, now drop the tables older than the create date threshold
   --
   log_file := UTL_FILE.FOPEN ('CR_BKUP_DIR', 'cr_bkup_table_drop.txt', 'W');
   UTL_FILE.put_line (log_file,
                      'The following tables dropped on ' || SYSTIMESTAMP);

   FOR rtab IN tablist
   LOOP
      EXECUTE IMMEDIATE 'DROP TABLE ' || rtab.table_name;

      UTL_FILE.put_line (
         log_file,
         'Dropped at ' || SYSTIMESTAMP || ' : ' || rtab.table_name);
   END LOOP;

   UTL_FILE.put_line (log_file, 'Completed Drop at ' || SYSTIMESTAMP);
   UTL_FILE.FCLOSE (log_file);
END XX_DROP_CR_BKUP_TABLES;
/

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

grant select on dba_segments to SYSTEM;
grant drop any table to SYSTEM;