pl-sql-scripts

Collection of PL/SQL scripts that I have written that I've used for Oracle database management

These are various PL/SQL scripts I have written to help me manage Oracle 11g, 12c, and 19c databases on AIX and Linux systems.

1. permit_truncate_trigger.sql : A user was required to be able to truncate certain application tables not owned by them, so the user was granted the "DROP ANY TABLE" system privilege (to truncate a table in Oracle, the table must be in your own schema or you must have the "DROP ANY TABLE" system privilege. But this privilege allows you to then truncate or drop every single table in the database). This trigger was written so that the user could only truncate the pre-approved application tables (the owner and names of which are stored in a table created by me: ALL_TRUNC8_PERMS) and would be prevented from being able to truncate every table in the database. If the user attempts to truncate a non-approved table, the trigger will prevent the truncate, cause the user to receive an error, the occurrence will be recorded in an Oracle table for record-keeping and auditing purposes (a table called CAPTURED_TRUNC8_ERRS), and an email will be sent to notify the DBA's.

2. migrate_large_table-DBLINK.sql : This script copies a very large table across a database link in batches. This is to be ran after the source table's metadata has been cloned in the target database. A database link and the following database table needs to already have been created in order for this script to successfully execute: DB_MIGRATION_TRACKER.

3. migrate_schema-DBLINK.sql : This script copies the table data from a schema in one database (the source database) to an identical schema in another database (the target database) across a database link. This is to be ran after the source schema's metadata has been cloned in the target database. A database link and the following database table needs to already have been created in order for this script to successfully execute: DB_MIGRATION_TRACKER.