A few weeks ago I received a question about how to generate DDL for all the tables, procedures, functions, and packages of a set of schemas. Furthermore, the asker wanted each object’s script to be in a distinct file within a directory hierarchy.
Specifically, given a base directory, each schema should have its own subdirectory and within each of those, each object type would have a subdirectory of its own, and then files would be there for each object.
For example:
/base/SCHEMA1/tables/tab1 /base/SCHEMA1/tables/tab2 /base/SCHEMA1/functions/func1 /base/SCHEMA2/tables/tab1 /base/SCHEMA2/packages/pack1 etc.
In older versions of the database using utl_file_dir init parameter set to “*”, it would be relatively easy. Simply iterate through the objects, concatenate the schema, object type, and object name to create the path, open the file, and write the ddl. However, in 18c and above that parameter no longer exists. Even if it did, using “*” is bad practice as it creates a large security hole. So, while that would have been easier, pursuing that option is not recommended.
Instead, we’ll create a temporary directory object and then reassign its path as needed during iteration. That solves the problem of how to define a path for the UTL_FILE package but leaves another problem of how to create the directory hierarchy within the file system. UTL_FILE does not provide a “mkdir” functionality, nor do any of the other built in packages.
To solve this we’ll use a java stored procedure. This not only provides the needed functionality, it also uses the JVMs native fine-grained security so our final code will only be able to write within the allowed target directory tree. For the purposes here, if the directory already exists, we won’t do anything but we’ll still return a success. It’s not important how the path came into being as long as it exists when we need it.
CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "DirectoryTools" AS
import java.io.*;
public class DirectoryTools{
public static boolean CreateDirectory ( String p_path) throws IOException
{
File dir = new File(p_path);
// mkdir will create the directory if all parent directories already exist
// mkdirs will create the directory and all missing parent directories as well
if (dir.exists())
return true;
else
return dir.mkdirs();
}
}
/
Then create a pl/sql api for the CreateDirectory method
CREATE OR REPLACE FUNCTION create_directory(p_path IN VARCHAR2)
RETURN BOOLEAN
IS
LANGUAGE JAVA
NAME 'DirectoryTools.CreateDirectory( java.lang.String ) return boolean' ;
/
That gives a method to create the directory paths as needed for each schema and object type. To generate the scripts, we’ll use DBMS_METADATA. For this exercise just the object itself were needed, no dependent objects or grants required. To iterate through the objects we’ll need a cursor to filter for the desired object types and exclude special cases such as IOT over flows which are tables, but not objects that stand on their own. The last special requirement is the procedure should be able to accept a list of schemas. To accommodate that I’ll use a nested table type collection.
CREATE OR REPLACE TYPE vctab AS TABLE OF VARCHAR2(4000);
Putting all of these elements together we end up with a procedure like this:
CREATE OR REPLACE PROCEDURE extract_schemas(p_basepath IN VARCHAR2,
p_schemas IN vctab)
AUTHID CURRENT_USER
IS
v_prev_owner dba_objects.owner%TYPE;
v_prev_type dba_objects.object_type%TYPE;
v_dir_name dba_directories.directory_name%TYPE := 'TMPDIR_' || TO_CHAR(SYSTIMESTAMP, 'yyyymmdd_hh24missff6'); v_file UTL_FILE.file_type;
BEGIN
FOR obj
IN ( SELECT owner,
object_type,
object_name,
DBMS_METADATA.get_ddl(object_type, object_name, owner) ddl_text
FROM dba_objects
WHERE owner MEMBER OF p_schemas
AND object_type IN ('TABLE',
'VIEW',
'SEQUENCE',
'PACKAGE',
'PACKAGE BODY',
'PROCEDURE',
'FUNCTION')
AND secondary = 'N'
AND (object_type != 'TABLE'
OR ((owner, object_name) IN
(SELECT owner, table_name
FROM dba_tables
WHERE owner MEMBER OF p_schemas
AND nested = 'NO'
AND (iot_type IS NULL OR iot_type != 'IOT_OVERFLOW'))))
ORDER BY owner, object_type, object_name)
LOOP
IF v_prev_owner IS NULL OR v_prev_owner != obj.owner
OR v_prev_type IS NULL OR v_prev_type != obj.object_type
THEN
IF (create_directory(p_basepath || '/' || obj.owner || '/' || obj.object_type))
THEN
EXECUTE IMMEDIATE 'create or replace directory '
|| v_dir_name
|| ' as '''
|| p_basepath
|| '/'
|| obj.owner
|| '/'
|| obj.object_type
|| '''';
v_prev_owner := obj.owner;
v_prev_type := obj.object_type;
ELSE
-- Any errors causing the function to fail should be raised
-- as java exceptions, so this is not expected to happen.
raise_application_error(-20001, 'Failed to create directory path');
END IF;
END IF;
v_file := UTL_FILE.fopen(v_dir_name,
obj.object_name,
'w',
32767;
-- If the DDL will be larger then 32k,
-- it can be split on new-line boundaries and written in pieces
UTL_FILE.put_line(v_file, obj.ddl_text);
UTL_FILE.fflush(v_file);
UTL_FILE.fclose(v_file);
END LOOP;
EXECUTE IMMEDIATE 'drop directory ' || v_dir_name;
EXCEPTION
WHEN OTHERS
THEN
EXECUTE IMMEDIATE 'drop directory ' || v_dir_name;
RAISE;
END;
/
An example usage:
BEGIN
extract_schemas('/oratmp', vctab('HR', 'SH', 'OE'));
END;
This resulted in the following output files:
/oratmp/HR/PROCEDURE/ADD_JOB_HISTORY /oratmp/HR/PROCEDURE/SECURE_DML /oratmp/HR/SEQUENCE/DEPARTMENTS_SEQ /oratmp/HR/SEQUENCE/EMPLOYEES_SEQ /oratmp/HR/SEQUENCE/LOCATIONS_SEQ /oratmp/HR/TABLE/COUNTRIES /oratmp/HR/TABLE/DEPARTMENTS /oratmp/HR/TABLE/EMPLOYEES /oratmp/HR/TABLE/JOB_HISTORY /oratmp/HR/TABLE/JOBS /oratmp/HR/TABLE/LOCATIONS /oratmp/HR/TABLE/REGIONS /oratmp/HR/VIEW/EMP_DETAILS_VIEW /oratmp/OE/FUNCTION/GET_PHONE_NUMBER_F /oratmp/OE/SEQUENCE/ORDERS_SEQ /oratmp/OE/TABLE/CUSTOMERS /oratmp/OE/TABLE/INVENTORIES /oratmp/OE/TABLE/ORDER_ITEMS /oratmp/OE/TABLE/ORDERS /oratmp/OE/TABLE/PRODUCT_DESCRIPTIONS /oratmp/OE/TABLE/PRODUCT_INFORMATION /oratmp/OE/TABLE/PROMOTIONS /oratmp/OE/TABLE/WAREHOUSES /oratmp/OE/VIEW/ACCOUNT_MANAGERS /oratmp/OE/VIEW/BOMBAY_INVENTORY /oratmp/OE/VIEW/CUSTOMERS_VIEW /oratmp/OE/VIEW/OC_CORPORATE_CUSTOMERS /oratmp/OE/VIEW/OC_CUSTOMERS /oratmp/OE/VIEW/OC_INVENTORIES /oratmp/OE/VIEW/OC_ORDERS /oratmp/OE/VIEW/OC_PRODUCT_INFORMATION /oratmp/OE/VIEW/ORDERS_VIEW /oratmp/OE/VIEW/PRODUCT_PRICES /oratmp/OE/VIEW/PRODUCTS /oratmp/OE/VIEW/SYDNEY_INVENTORY /oratmp/OE/VIEW/TORONTO_INVENTORY /oratmp/SH/TABLE/CAL_MONTH_SALES_MV /oratmp/SH/TABLE/CHANNELS /oratmp/SH/TABLE/COSTS /oratmp/SH/TABLE/COUNTRIES /oratmp/SH/TABLE/CUSTOMERS /oratmp/SH/TABLE/FWEEK_PSCAT_SALES_MV /oratmp/SH/TABLE/PRODUCTS /oratmp/SH/TABLE/PROMOTIONS /oratmp/SH/TABLE/SALES /oratmp/SH/TABLE/SALES_TRANSACTIONS_EXT /oratmp/SH/TABLE/SUPPLEMENTARY_DEMOGRAPHICS /oratmp/SH/TABLE/TIMES /oratmp/SH/VIEW/PROFITS
The procedure above, as noted, does not include related objects such as indexes or grants. I also make the assumption the generated DDL will be less than 32K so it will fit within a VARCHAR2 value. If that isn’t true, I’ve noted with a comment where the code should be modified to split a large clob into smaller values consumable by UTL_FILE.
The last thing to consider are permissions. The procedure is defined with AUTHID CURRENT_USER so users that invoke it can write to whichever paths they have grants. Because of the recursive nature the write permission should be granted with the “-” syntax to the invoking user. For my id (SDS) to write to /oratmp and any subdirectories under it, the java grant looks like this:
DECLARE
KEYNUM NUMBER;
BEGIN
SYS.DBMS_JAVA.GRANT_PERMISSION(
grantee => 'SDS'
,permission_type => 'SYS:java.io.FilePermission'
,permission_name => '/oratmp/-'
,permission_action => 'write'
,key => KEYNUM
);
END;
/
The code above could be expanded to a richer functionality set but hopefully provides a solid base to build on. Comments and questions, as always, are welcome.