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…<\/p>\n
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.<\/p>\n
If I am violating any coding standards or a better way to accomplish the result, please let me know.<\/p>\n
This code is also a quick demonstration of how to use DBMS_DATAPUMP and UTL_FILE programs.<\/p>\n
<\/p>\n
\/* Formatted *\/\nCREATE OR REPLACE PROCEDURE XX_DROP_CR_BKUP_TABLES (\n threshold_days NUMBER DEFAULT 60)\nIS\n \/******************************************************************************\n NAME: XX_DROP_CR_BKUP_TABLES\n PURPOSE: Drop tables under CR_BKUP schema older than 60 days\n\n ******************************************************************************\/\n\n CURSOR tablist\n IS\n SELECT owner || '.' || object_name table_name\n FROM dba_objects\n WHERE owner = 'CR_BKUP'\n AND object_type = 'TABLE'\n AND last_ddl_time < (SYSDATE - threshold_days);\n\n log_file UTL_FILE.FILE_TYPE;\nBEGIN\n -- Export all the tables belonging to CR_BKUP schema to file system\n -- This dump file will be overwritten next time the job runs\n -- OS file backup will copy to the backup media\n DECLARE\n dp_file NUMBER;\n jstatus VARCHAR2 (200);\n ltmp VARCHAR2 (200);\n BEGIN\n EXECUTE IMMEDIATE\n 'CREATE OR REPLACE DIRECTORY CR_BKUP_DIR AS ''\/u01\/app\/oracle\/expdp''';\n\n BEGIN\n SELECT table_name\n INTO ltmp\n FROM user_tables\n WHERE table_name = 'CR_BKUP_TABLE_EXP';\n\n EXECUTE IMMEDIATE 'DROP TABLE CR_BKUP_TABLE_EXP';\n EXCEPTION\n WHEN NO_DATA_FOUND\n THEN\n NULL;\n END;\n\n --utl_file.fremove( 'CR_BKUP_DIR', 'cr_bkup_tabs.dmp' );\n --utl_file.fremove( 'CR_BKUP_DIR', 'cr_bkup_tabs.log' );\n\n dp_file :=\n DBMS_DATAPUMP.open (OPERATION => 'EXPORT',\n JOB_MODE => 'SCHEMA',\n JOB_NAME => 'CR_BKUP_TABLE_EXP');\n\n DBMS_DATAPUMP.add_file (\n HANDLE => dp_file,\n FILENAME => 'cr_bkup_tabs.dmp',\n DIRECTORY => 'CR_BKUP_DIR',\n FILETYPE => DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE,\n REUSEFILE => 1);\n DBMS_DATAPUMP.add_file (\n HANDLE => dp_file,\n FILENAME => 'cr_bkup_tabs.log',\n DIRECTORY => 'CR_BKUP_DIR',\n FILETYPE => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);\n DBMS_DATAPUMP.metadata_filter (HANDLE => dp_file,\n NAME => 'SCHEMA_LIST',\n VALUE => '''CR_BKUP''');\n DBMS_DATAPUMP.start_job (HANDLE => dp_file);\n DBMS_DATAPUMP.wait_for_job (HANDLE => dp_file, JOB_STATE => jstatus);\n DBMS_DATAPUMP.detach (HANDLE => dp_file);\n EXCEPTION\n WHEN OTHERS\n THEN\n DBMS_DATAPUMP.detach (HANDLE => dp_file);\n RAISE;\n END;\n\n -- Export completed, now drop the tables older than the create date threshold\n --\n log_file := UTL_FILE.FOPEN ('CR_BKUP_DIR', 'cr_bkup_table_drop.txt', 'W');\n UTL_FILE.put_line (log_file,\n 'The following tables dropped on ' || SYSTIMESTAMP);\n\n FOR rtab IN tablist\n LOOP\n EXECUTE IMMEDIATE 'DROP TABLE ' || rtab.table_name;\n\n UTL_FILE.put_line (\n log_file,\n 'Dropped at ' || SYSTIMESTAMP || ' : ' || rtab.table_name);\n END LOOP;\n\n UTL_FILE.put_line (log_file, 'Completed Drop at ' || SYSTIMESTAMP);\n UTL_FILE.FCLOSE (log_file);\nEND XX_DROP_CR_BKUP_TABLES;\n\/<\/pre>\n <\/p>\n
For this procedure to work, the schema owning this procedure should be granted these privileges explicitly.<\/p>\n
grant select on dba_segments to SYSTEM;\ngrant drop any table to SYSTEM;<\/pre>\n <\/p>\n
<\/p>\n","protected":false},"excerpt":{"rendered":"
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 … Read more<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[133,22],"tags":[41,71],"class_list":["post-195","post","type-post","status-publish","format-standard","hentry","category-oracledb","category-scripts-tools","tag-dbms_datapump","tag-utl_file"],"acf":[],"jetpack_featured_media_url":"","jetpack-related-posts":[],"jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/posts\/195","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/comments?post=195"}],"version-history":[{"count":1,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/posts\/195\/revisions"}],"predecessor-version":[{"id":3051,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/posts\/195\/revisions\/3051"}],"wp:attachment":[{"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/media?parent=195"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/categories?post=195"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/tags?post=195"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}