Schema Export using DBMS_DATAPUMP

For more granular control or when you want to execute Data Pump operations directly from SQL*Plus or a PL/SQL block (e.g., within a stored procedure or scheduled with DBMS_SCHEDULER ), you can use the DBMS_DATAPUMP PL/SQL package.

##################################################

DECLARE
    l_dp_handle NUMBER;
BEGIN
    -- 1. Open a Data Pump export job
    l_dp_handle := DBMS_DATAPUMP.OPEN(
        operation   => 'EXPORT',
        job_mode    => 'SCHEMA',
        job_name    => 'SCOTT_EXP_JOB',
        version     => 'LATEST'
    );
    -- 2. Specify dump file and directory
    DBMS_DATAPUMP.ADD_FILE(
        handle      => l_dp_handle,
        filename    => 'scott_schema_api.dmp',
        directory   => 'DATA_PUMP_DIR'
    );
    -- 3. Specify log file
    DBMS_DATAPUMP.ADD_FILE(
        handle      => l_dp_handle,
        filename    => 'scott_schema_api.log',
        directory   => 'DATA_PUMP_DIR',
        filetype    => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE
    );
    -- 4. Specify the schema to export
    DBMS_DATAPUMP.METADATA_FILTER(
        handle      => l_dp_handle,
        name        => 'SCHEMA_EXPR',
        value       => 'IN (''SCOTT'')'
    );
    -- 5. Start the job
    DBMS_DATAPUMP.START_JOB(l_dp_handle);
    -- 6. Detach from the job (it will continue running in the background)
    DBMS_DATAPUMP.DETACH(l_dp_handle);
    DBMS_OUTPUT.PUT_LINE('Data Pump job SCOTT_EXP_JOB started successfully.');
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
        DBMS_DATAPUMP.STOP_JOB(l_dp_handle); -- Stop the job in case of error
END;
/

#############################################################

Note: This PL/SQL block needs to be executed in SQLPlus with SET SERVEROUTPUT ON to see the DBMS_OUTPUT messages.