Benutzer-Werkzeuge
Webseiten-Werkzeuge
cloud:import_data_adb_oracle_cloud_26_ai
Inhaltsverzeichnis
Oracle 26ai ADB- Autonomous AI Database - in der Cloud - Import eines Oracle DataPump Exports - Fehleranalyse und Fallstricke
Aufgabe
Daten aus einer 23ai Free Edition on-premise in eine Oracle 26ai in einer Cloud-Umgebung umziehen.
Ablauf:
- Daten mit expdp „normal“ aus der Datenbank auf die Platte exportieren
- Export-Modus Table oder Schema aufschreiben! Wird später dringend benötigt!
- Daten in die Cloud hochladen
- URL auf die Datei erzeugen, um diese ohne Credentials laden zu können, oder Credentials anlegen
- Daten in die Zieldatenbank direkt aus dem Cloud Store laden
- Importieren
- Mit etwas Glück klappt es – wenn nicht, dann viel Zeit für die Fehlersuche einplanen
Auf den richtigen Export-Modus für den Import achten!
Wie die Fehler eingrenzen
Wichtig ist es hier, sehr strukturiert vorzugehen – das Verfahren ist sehr fehleranfällig!
Wir testen also immer in dieser Reihenfolge:
- MD5 und Größe des Originalexports auf der Exportmaschine vor dem Upload in die Cloud aufschreiben
- Daten sind nach dem Hochladen immer noch dieselben nach dem Herunterladen per wget aus der Cloud (MD5 und Größe)
- Datei kann in die ADB geladen werden und ist dann immer noch dieselbe Datei (MD5 und Größe)
- Datei kann in der ADB als Oracle DataPump-Datei identifiziert werden
- Das SQL kann aus dem Dump ausgelesen werden und wir finden auch die Objektnamen wieder, die wir erwarten
- Nun können wir versuchen zu importieren
Jede andere Reihenfolge wird den Tag verderben, wenn beim ersten Versuch des Imports ein Fehler auftritt.
Siehe dazu in der Folge die entsprechenden Prozeduren, um das schrittweise in PL/SQL zu testen und auszulesen.
Die API dazu findet sich unter ⇒ https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_DATAPUMP.html#GUID-DAD6C613-9A49-4A7D-9224-FF6AA54ABBE4
Daten exportieren in der 23ai
Problem in der 23ai Free Edition:
.. ORA-31693: Table data object "GPI"."THESAURUS_PHRASES" failed to load/unload and is being skipped due to error: ORA-00923: FROM keyword not found where expected ..
Lösung:
Alle Annotationen auf Spalten und Tabellen dieser Tabelle löschen!
DBMS_METADATA hat bei der 23ai noch Probleme damit!
Importdaten hochladen und freigeben
In meiner Cloud habe ich einen Object Store, in dem ich den Export hochladen kann.
In der Cloud wird dieser dann mit „Create pre-authenticated request“ (Drei-Punkte-Menü auf der hochgeladenen Datei) freigegeben und die dabei generierte URL kann in DataPump für den Import genutzt werden. Danach über „Object Details“ den MD5-Hash auslesen zum späteren Vergleichen – allerdings stimmt dieser dann am Ende überhaupt nicht mit der Originaldatei überein, daher den Hash von der Originaldatei ermitteln und verwenden.
Danach muss auf jeden Fall zunächst mit einem wget geprüft werden, ob sich das Objekt herunterladen lässt und noch dasselbe ist! Hash und Größe vergleichen!
Eine defekte Importdatei führt zu seltsamen Fehlern und ist eine der dutzenden möglichen Fehlerquellen an dieser Stelle!
wget -v https://objectstorage.eu-frankfurt-1.oraclecloud.com/p/xxxxx/o/export_20260224.dmp md5sum export_20260224.dmp 5d8c551697c17bbe848bbbc894f862c8
Klappt das, ist schon mal eine große Hürde genommen!
DataPump-Importroutinen anlegen
Unter dem User ADMIN in der ADB wird dann eine Reihe von Routinen für den Import angelegt.
Die umfangreichen Debug-Routinen sind entscheidend, um die Vielzahl möglicher Fehlerursachen systematisch einzugrenzen und trotz der mitunter unnötig komplexen Mechanismen von Oracle einen klaren, reproduzierbaren Weg zu finden, der am Ende zuverlässig zum Erfolg führt.
Architektur – Logfile-Handling
Auch in der ADB bleibt ein DataPump-Directory erhalten.
Dort kann ein Logfile gefunden werden, das uns hilft, die zahlreichen Fehler einzugrenzen und Lösungen zu finden.
Abfrage als „admin„-User:
SELECT * FROM DBMS_CLOUD.LIST_FILES('DATA_PUMP_DIR') order by LAST_MODIFIED desc;
Wenn nach vielen Testanläufen es etwas unübersichtlich wird, können die Logs auch wieder gelöscht werden:
BEGIN DBMS_CLOUD.DELETE_FILE( directory_name => 'DATA_PUMP_DIR', file_name => 'ADMIN_IMPORT_25_14_29.dmp' ); END; /
Logfiles auslesen aus dem DataPump-Dir
Um die Logfiles einfach auszulesen, legen wir uns eine Hilfsfunktion an:
- datapump_show_log.sql
---------------------------------------------------------------------------- -- Procedure : datapump_show_log -- Purpose : Reads a DataPump log file from DATA_PUMP_DIR and prints -- its content line by line via DBMS_OUTPUT -- Parameters: p_filename - name of the log file (e.g. 'export_job.log') -- Modified : 2026-02-25 - initial version ---------------------------------------------------------------------------- CREATE OR REPLACE PROCEDURE datapump_show_log( p_filename IN VARCHAR2 ) AUTHID current_user IS v_file UTL_FILE.file_type; v_line VARCHAR2(32767); BEGIN v_file := UTL_FILE.fopen( location => 'DATA_PUMP_DIR', filename => p_filename, open_mode => 'r', max_linesize => 32767 ); DBMS_OUTPUT.put_line('--- BEGIN LOG: ' || p_filename || ' ---'); <<READ_LOG_LINES>> LOOP UTL_FILE.get_line(v_file, v_line); DBMS_OUTPUT.put_line(v_line); END LOOP READ_LOG_LINES; EXCEPTION WHEN NO_DATA_FOUND THEN -- expected EOF: close file and print footer IF UTL_FILE.is_open(v_file) THEN UTL_FILE.fclose(v_file); END IF; DBMS_OUTPUT.put_line('--- END LOG: ' || p_filename || ' ---'); WHEN UTL_FILE.invalid_path THEN DBMS_OUTPUT.put_line( 'ERROR: invalid_path for directory DATA_PUMP_DIR (READ privilege missing?)' ); WHEN UTL_FILE.invalid_operation THEN DBMS_OUTPUT.put_line( 'ERROR: invalid_operation accessing file: ' || p_filename ); WHEN UTL_FILE.read_error THEN DBMS_OUTPUT.put_line( 'ERROR: read_error while reading file: ' || p_filename ); WHEN OTHERS THEN -- ensure file handle is released before re-raise BEGIN IF UTL_FILE.is_open(v_file) THEN UTL_FILE.fclose(v_file); END IF; EXCEPTION WHEN OTHERS THEN NULL; END; RAISE; END datapump_show_log; /
1. Test – URI-Analyse – Kann die Datei eingelesen werden
Eine der vielen möglichen Fehlerquellen ist das Einlesen der Dumpfile an sich in die DB – von Zertifikaten über Bitfehler, falsche URLs bis hin zu Zwischenseiten kann hier alles passieren, was die Fehleranalyse massiv erschwert.
Wir müssen also das Objekt zunächst in die DB laden, solange es nicht zu groß ist!
- datapump_check_uri.sql
---------------------------------------------------------------------------- -- Procedure : datapump_check_uri -- Purpose : Validates accessibility of a DataPump dump file in Object -- Storage by fetching it via DBMS_CLOUD.GET_OBJECT and -- computing MD5 and SHA-256 checksums on the BLOB content. -- Parameters: p_objectstorage_url - full HTTPS URL to the .dmp file -- p_credential_name - DBMS_CLOUD credential name -- (NULL = pre-authenticated request / PAR) -- Notes : Requires EXECUTE on DBMS_CLOUD and DBMS_CRYPTO. -- MD5 is deprecated since Oracle 21c but kept here because -- OCI Object Storage exposes MD5 content hashes for comparison. -- Use SHA-256 for security-relevant verification. -- Modified : 2026-02-25 - initial version ---------------------------------------------------------------------------- CREATE OR REPLACE PROCEDURE datapump_check_uri( p_objectstorage_url IN VARCHAR2, p_credential_name IN VARCHAR2 DEFAULT NULL ) AUTHID current_user IS -- ============================================================ -- NOTE: Oracle 19c+ feature — DBMS_CLOUD.GET_OBJECT (BLOB return) -- Feature : Fetch cloud object as BLOB into PGA memory -- Since : Oracle 19c (pre-installed on ADB; installable on-prem) -- Docs : https://docs.oracle.com/en/cloud/paas/autonomous-database/serverless/adbsb/dbms-cloud-subprograms.html -- ============================================================ v_blob blob := NULL; v_size_bytes INTEGER := 0; v_size_mb NUMBER; -- raw hash buffers v_hash_md5 RAW(16); -- MD5 = 128 bit = 16 bytes v_hash_sha256 RAW(32); -- SHA-256 = 256 bit = 32 bytes -- human-readable hex strings v_md5_hex VARCHAR2(32); v_sha256_hex VARCHAR2(64); BEGIN DBMS_OUTPUT.put_line('=============================================='); DBMS_OUTPUT.put_line('DataPump URI Check'); DBMS_OUTPUT.put_line('URL : ' || p_objectstorage_url); IF p_credential_name IS NOT NULL THEN DBMS_OUTPUT.put_line('Cred: ' || p_credential_name); ELSE DBMS_OUTPUT.put_line('Cred: (none - PAR / public URL)'); END IF; DBMS_OUTPUT.put_line('----------------------------------------------'); -- fetch the file as BLOB -- for PAR / public URLs credential_name is omitted (NULL branch) IF p_credential_name IS NOT NULL THEN v_blob := dbms_cloud.get_object( credential_name => p_credential_name, object_uri => p_objectstorage_url ); ELSE v_blob := dbms_cloud.get_object( object_uri => p_objectstorage_url ); END IF; -- size v_size_bytes := DBMS_LOB.getlength(v_blob); v_size_mb := ROUND(v_size_bytes / 1048576, 3); DBMS_OUTPUT.put_line('Status : OK - file is readable from DB'); DBMS_OUTPUT.put_line( 'Size : ' || TO_CHAR(v_size_bytes, 'FM999,999,999,999') || ' bytes (' || TO_CHAR(v_size_mb) || ' MB)' ); -- ============================================================ -- NOTE: Oracle 21c deprecation — DBMS_CRYPTO.HASH_MD5 -- MD5 (HASH_MD5 = constant 2) is deprecated since Oracle 21c. -- Kept here for OCI Object Storage compatibility (OCI stores -- the MD5 content hash per object for download verification). -- Do NOT use MD5 for security-relevant checksums. -- ============================================================ v_hash_md5 := dbms_crypto.hash( src => v_blob, typ => dbms_crypto.hash_md5 -- = 2 ); v_md5_hex := RAWTOHEX(v_hash_md5); -- SHA-256: preferred algorithm for integrity verification (Oracle 11g+) v_hash_sha256 := dbms_crypto.hash( src => v_blob, typ => dbms_crypto.hash_sh256 -- = 4 ); v_sha256_hex := RAWTOHEX(v_hash_sha256); DBMS_OUTPUT.put_line('MD5 : ' || LOWER(v_md5_hex) || ' (deprecated, OCI-compat)'); DBMS_OUTPUT.put_line('SHA-256 : ' || LOWER(v_sha256_hex) || ' (recommended)'); DBMS_OUTPUT.put_line('=============================================='); -- release BLOB from PGA IF DBMS_LOB.istemporary(v_blob) = 1 THEN DBMS_LOB.freetemporary(v_blob); END IF; EXCEPTION WHEN OTHERS THEN -- release BLOB on error to avoid PGA leak BEGIN IF v_blob IS NOT NULL AND DBMS_LOB.istemporary(v_blob) = 1 THEN DBMS_LOB.freetemporary(v_blob); END IF; EXCEPTION WHEN OTHERS THEN NULL; END; DBMS_OUTPUT.put_line('Status : ERROR - URI not accessible'); DBMS_OUTPUT.put_line('Detail : ' || SQLERRM); DBMS_OUTPUT.put_line('URL : ' || p_objectstorage_url); DBMS_OUTPUT.put_line('=============================================='); RAISE; END datapump_check_uri;
Aufruf:
SET serveroutput ON BEGIN DATAPUMP_CHECK_URI(p_objectstorage_url => 'https://objectstorage.eu-frankfurt-1.oraclecloud.com/p/xxxxx/o/export_20260224.dmp'); END; / ============================================== DataPump URI CHECK URL : https://objectstorage.eu-frankfurt-1.oraclecloud.com/p/xxxxx/o/export_20260224.dmp Cred: (NONE - PAR / public URL) ---------------------------------------------- STATUS : OK - file IS readable FROM DB SIZE : 5,668,864 bytes (5.406 MB) MD5 : 5d8c551697c17bbe848bbbc894f862c8 (deprecated, OCI-compat) SHA-256 : 5440788cc2de5c99e7ba8269507f309a9a55b5ecb89cd1049d1a9ab474c43d2c (recommended) ==============================================
Mit diesen Informationen können wir dann vergleichen ob die gleiche Größe und Checksum der Datei vorliegt und haben damit bewiesen das die URL funktioniert und eine Datei in die ADB hochgeladen werden kann.
2. Test – Dump-File analysieren – Ist das überhaupt eine gültige Oracle-DataPump-Datei
Als nächste prüfen wir ob die Daten überhaupt eine gültige ORacle Datapump Export Datei ist und in welchen Modus die Daten angelegt wurden.
datapump_analyze_dumpfile.sql:
- datapump_analyze_dumpfile.sql
---------------------------------------------------------------------------- -- Procedure : datapump_analyze_dumpfile -- Purpose : Downloads a DataPump dump file from an Object Storage URL -- to DATA_PUMP_DIR (temp) and analyzes its header metadata -- via DBMS_DATAPUMP.GET_DUMPFILE_INFO. -- Detects the original export job mode from the Job Name header -- item (G_DFHDR_JOB_NAME) and prints the recommended p_job_mode -- parameter for datapump_extract_sql / datapump_import. -- Parameters: p_objectstorage_url - full HTTPS URL to the .dmp file -- p_credential_name - DBMS_CLOUD credential -- (NULL = skip download, file already local) -- p_directory - Oracle directory object -- Modified : 2026-02-25 - initial version -- : 2026-02-25 - added job mode detection from G_DFHDR_JOB_NAME ---------------------------------------------------------------------------- CREATE OR REPLACE PROCEDURE datapump_analyze_dumpfile( p_objectstorage_url IN VARCHAR2, p_credential_name IN VARCHAR2 DEFAULT NULL, p_directory IN VARCHAR2 DEFAULT 'DATA_PUMP_DIR' ) AUTHID current_user IS G_DFHDR_FILE_VERSION CONSTANT NUMBER := 1; G_DFHDR_MASTER_PRESENT CONSTANT NUMBER := 2; G_DFHDR_GUID CONSTANT NUMBER := 3; G_DFHDR_FILE_NUMBER CONSTANT NUMBER := 4; G_DFHDR_CHARSET_ID CONSTANT NUMBER := 5; G_DFHDR_CREATION_DATE CONSTANT NUMBER := 6; G_DFHDR_FLAGS CONSTANT NUMBER := 7; G_DFHDR_JOB_NAME CONSTANT NUMBER := 8; G_DFHDR_PLATFORM CONSTANT NUMBER := 9; G_DFHDR_INSTANCE CONSTANT NUMBER := 10; G_DFHDR_LANGUAGE CONSTANT NUMBER := 11; G_DFHDR_BLOCKSIZE CONSTANT NUMBER := 12; G_DFHDR_DIRPATH CONSTANT NUMBER := 13; G_DFHDR_CHECKSUM CONSTANT NUMBER := 14; G_DFHDR_DB_VERSION CONSTANT NUMBER := 15; G_DFHDR_MAX_ITEM_CODE CONSTANT NUMBER := 16; v_info_table ku$_dumpfile_info; v_filetype NUMBER; v_item ku$_dumpfile_item; v_label VARCHAR2(40); v_local_file VARCHAR2(255); v_downloaded BOOLEAN := FALSE; -- job mode detection v_job_name_raw VARCHAR2(2048); v_detected_mode VARCHAR2(30) := 'UNKNOWN'; e_no_dumpfile_info EXCEPTION; PRAGMA exception_init(e_no_dumpfile_info, -39211); ---------------------------------------------------------------------------- FUNCTION extract_filename(p_url IN VARCHAR2) RETURN VARCHAR2 IS v_pos NUMBER; BEGIN v_pos := INSTR(p_url, '/', -1); IF v_pos > 0 THEN RETURN SUBSTR(p_url, v_pos + 1); END IF; RETURN p_url; END extract_filename; BEGIN DBMS_OUTPUT.put_line('=============================================='); DBMS_OUTPUT.put_line('DataPump Dump File Analysis'); DBMS_OUTPUT.put_line('URL : ' || p_objectstorage_url); DBMS_OUTPUT.put_line('=============================================='); v_local_file := extract_filename(p_objectstorage_url); IF p_credential_name IS NOT NULL THEN DBMS_OUTPUT.put_line( 'Downloading TO ' || p_directory || ' / ' || v_local_file || ' ...' ); dbms_cloud.get_object( credential_name => p_credential_name, object_uri => p_objectstorage_url, directory_name => p_directory, file_name => v_local_file ); v_downloaded := TRUE; DBMS_OUTPUT.put_line('Download complete.'); ELSE DBMS_OUTPUT.put_line( 'No credential — assuming file already in ' || p_directory || ' as: ' || v_local_file ); END IF; dbms_datapump.get_dumpfile_info( filename => v_local_file, directory => p_directory, info_table => v_info_table, filetype => v_filetype ); DBMS_OUTPUT.put_line( 'File TYPE : ' || CASE v_filetype WHEN 0 THEN '0 - Unknown / not a DataPump file' WHEN 1 THEN '1 - DataPump dump file (expdp)' WHEN 2 THEN '2 - Classic Export dump file (exp)' WHEN 3 THEN '3 - External Tables dump file' ELSE TO_CHAR(v_filetype) || ' - unrecognized' END ); IF v_filetype = 0 THEN DBMS_OUTPUT.put_line('WARNING: File not recognized as a DataPump dump file.'); ELSE DBMS_OUTPUT.put_line('----------------------------------------------'); DBMS_OUTPUT.put_line('Header Metadata:'); DBMS_OUTPUT.put_line('----------------------------------------------'); <<PRINT_INFO_ITEMS>> FOR v_ind IN 1 .. v_info_table.COUNT LOOP v_item := v_info_table(v_ind); -- collect job name for mode detection (item_code 8) IF v_item.item_code = G_DFHDR_JOB_NAME THEN v_job_name_raw := v_item.VALUE; END IF; v_label := CASE v_item.item_code WHEN G_DFHDR_FILE_VERSION THEN 'File Version' WHEN G_DFHDR_MASTER_PRESENT THEN 'Master TABLE Present' WHEN G_DFHDR_GUID THEN 'Job GUID' WHEN G_DFHDR_FILE_NUMBER THEN 'File NUMBER' WHEN G_DFHDR_CHARSET_ID THEN 'Charset ID' WHEN G_DFHDR_CREATION_DATE THEN 'Creation DATE' WHEN G_DFHDR_FLAGS THEN 'Flags' WHEN G_DFHDR_JOB_NAME THEN 'Job Name' WHEN G_DFHDR_PLATFORM THEN 'SOURCE Platform' WHEN G_DFHDR_INSTANCE THEN 'Instance Name' WHEN G_DFHDR_LANGUAGE THEN 'NLS LANGUAGE' WHEN G_DFHDR_BLOCKSIZE THEN 'Block SIZE (bytes)' WHEN G_DFHDR_DIRPATH THEN 'DirectPath Flag' WHEN G_DFHDR_CHECKSUM THEN 'Checksum Present' WHEN G_DFHDR_DB_VERSION THEN 'DB Version (SOURCE)' WHEN G_DFHDR_MAX_ITEM_CODE THEN 'MAX Item Code' ELSE 'Item #' || TO_CHAR(v_item.item_code) END; DBMS_OUTPUT.put_line( RPAD(v_label, 24) || ': ' || v_item.VALUE ); END LOOP PRINT_INFO_ITEMS; -- ------------------------------------------------------------------ -- Job Mode Detection — derived from Oracle auto-generated job names: -- SYS_EXPORT_TABLE_nn => TABLE -- SYS_EXPORT_SCHEMA_nn => SCHEMA -- SYS_EXPORT_FULL_nn => FULL -- SYS_EXPORT_TRANSPORTABLE_nn => TRANSPORTABLE -- Only reliable for auto-named jobs (no p_job_name was set at export). -- Custom job names won't match — mode remains UNKNOWN in that case. -- ------------------------------------------------------------------ IF v_job_name_raw IS NOT NULL THEN DECLARE v_jn VARCHAR2(2048) := UPPER(v_job_name_raw); BEGIN IF INSTR(v_jn, 'EXPORT_TABLE') > 0 THEN v_detected_mode := 'TABLE'; ELSIF INSTR(v_jn, 'EXPORT_SCHEMA') > 0 THEN v_detected_mode := 'SCHEMA'; ELSIF INSTR(v_jn, 'EXPORT_FULL') > 0 THEN v_detected_mode := 'FULL'; ELSIF INSTR(v_jn, 'EXPORT_TRANSPORTABLE') > 0 THEN v_detected_mode := 'TRANSPORTABLE'; END IF; END; END IF; DBMS_OUTPUT.put_line('----------------------------------------------'); DBMS_OUTPUT.put_line('Detected Job Mode : ' || v_detected_mode); IF v_detected_mode != 'UNKNOWN' THEN DBMS_OUTPUT.put_line( '>>> p_job_mode => ''' || v_detected_mode || '''' ); ELSE DBMS_OUTPUT.put_line( '>>> Could not detect mode — custom job name or classic export.' ); DBMS_OUTPUT.put_line( ' Check Job Name above: TABLE=TABLE, SCHEMA=SCHEMA, FULL=FULL' ); END IF; END IF; DBMS_OUTPUT.put_line('=============================================='); IF v_downloaded THEN UTL_FILE.fremove(p_directory, v_local_file); DBMS_OUTPUT.put_line('Temp file removed: ' || v_local_file); END IF; EXCEPTION WHEN e_no_dumpfile_info THEN DBMS_OUTPUT.put_line( 'ERROR: ORA-39211 - Cannot read dump file info from: ' || v_local_file ); IF v_downloaded THEN BEGIN UTL_FILE.fremove(p_directory, v_local_file); EXCEPTION WHEN OTHERS THEN NULL; END; END IF; WHEN OTHERS THEN DBMS_OUTPUT.put_line( 'ERROR: datapump_analyze_dumpfile - ' || SQLERRM ); IF v_downloaded THEN BEGIN UTL_FILE.fremove(p_directory, v_local_file); EXCEPTION WHEN OTHERS THEN NULL; END; END IF; RAISE; END datapump_analyze_dumpfile; /
Aufrufen mit:
SET serveroutput ON BEGIN datapump_analyze_dumpfile(p_objectstorage_url => 'https://objectstorage.eu-frankfurt-1.oraclecloud.com/p/xxxxx/o/export_20260224.dmp'); END; / ============================================== DataPump Dump File Analysis URL : https://objectstorage.eu-frankfurt-1.oraclecloud.com/p/xxxxx/o/export_20260224.dmp ============================================== Downloading TO DATA_PUMP_DIR / export_20260224.dmp ... Download complete. File TYPE : 1 - DataPump dump file (expdp) ---------------------------------------------- Header Metadata: ---------------------------------------------- File Version : 5.1 DB Version (SOURCE) : 19.00.00.00.00 Master TABLE Present : 1 Flags : 514 Item #25 : 0 Job GUID : 4B932BF86FE82744E06301161FACC70E Charset ID : 873 File NUMBER : 1 Job Name : "SYSTEM"."SYS_EXPORT_TABLE_01" SOURCE Platform : x86_64/Linux 2.4.xx Instance Name : DBPROD01 NLS LANGUAGE : AL32UTF8 Creation DATE : Tue Feb 24 14:51:43 2026 Block SIZE (bytes) : 4096 Checksum Present : 1 Item #18 : 0 Item #23 : 3 Item #26 : 0 Item #27 : 0 Item #19 : 0 Item #20 : 0 Item #21 : 0 Item #22 : 2 MAX Item Code : 1 Item #17 : 1 ---------------------------------------------- Detected Job Mode : TABLE >>> p_job_mode => 'TABLE' ============================================== ============================================== Temp file removed: export_20260224.dmp
Das scheint also mal ein gültiger DataPump-Export zu sein und wir erkennen, dass der Job-Modus TABLE ist.
3. Test – Was steht überhaupt in dem Dump-File
Nachdem wir nun die Datei in die ADB bekommen haben und die Datei wirklich ein Oracle-DataPump-Export ist, müssen wir noch wissen, was in der Datei an Objekten steht, um zu wissen, mit welchen Filtern wir diese im letzten Schritt aus dem Dump auslesen können, damit die richtigen Objekte in das richtige Schema geschrieben werden.
Wir legen also eine SQL-Datei mit dem gesamten DDL des Dump-Files an. Damit können wir bei Bedarf die Tabellen auch zuvor anlegen, wenn wir z.B. an den Speichereigenschaften etwas ändern wollen.
Zuvor laden wir uns allerdings die Datei in das DataPump-Directory herunter, da es mit der URI sonst zu kompliziert wird.
BEGIN DBMS_CLOUD.GET_OBJECT( object_uri => 'https://objectstorage.eu-frankfurt-1.oraclecloud.com/p/xxxxx/o/export_20260224.dmp', directory_name => 'DATA_PUMP_DIR', file_name => 'export_20260224.dmp' ); END; / -- anzeigen lassen ob es wirklich da ist! SELECT * FROM DBMS_CLOUD.LIST_FILES('DATA_PUMP_DIR') WHERE OBJECT_NAME='export_20260224.dmp';
datapump_extract_sql.sql:
- datapump_extract_sql.sql
---------------------------------------------------------------------------- -- Procedure : datapump_extract_sql -- Purpose : Extracts DDL SQL from a DataPump dump file into a SQL script. -- IMPORTANT: job_mode must match the original export mode: -- TABLE export → job_mode = 'TABLE' (TABLE_EXPORT/...) -- SCHEMA export → job_mode = 'SCHEMA' (SCHEMA_EXPORT/...) -- FULL export → job_mode = 'FULL' (DATABASE_EXPORT/...) -- Mismatch causes ORA-31655 (no objects found). -- Parameters: p_dumpfile - dump file name (local in p_directory) -- p_sqlfile - output SQL script filename -- p_directory - Oracle directory object for both files -- p_job_mode - must match original export mode: -- 'TABLE' | 'SCHEMA' | 'FULL' (default: TABLE) -- p_schema_filter - source schema name to filter on -- (required when job_mode = TABLE and tables -- belong to a schema other than current user) -- p_remap_schema - optional: source schema to remap FROM -- p_remap_schema_to - optional: target schema to remap TO -- p_job_name - optional: DataPump job name -- Modified : 2026-02-25 - initial version -- : 2026-02-25 - added p_job_mode + p_schema_filter -- fix ORA-31655 on TABLE-mode dumps ---------------------------------------------------------------------------- CREATE OR REPLACE PROCEDURE datapump_extract_sql( p_dumpfile IN VARCHAR2, p_sqlfile IN VARCHAR2, p_directory IN VARCHAR2 DEFAULT 'DATA_PUMP_DIR', p_job_mode IN VARCHAR2 DEFAULT 'TABLE', -- TABLE | SCHEMA | FULL p_schema_filter IN VARCHAR2 DEFAULT NULL, -- e.g. 'GPI' p_remap_schema IN VARCHAR2 DEFAULT NULL, p_remap_schema_to IN VARCHAR2 DEFAULT NULL, p_job_name IN VARCHAR2 DEFAULT NULL ) AUTHID current_user IS v_dp_handle NUMBER; v_job_state VARCHAR2(30) := 'UNDEFINED'; v_job_name VARCHAR2(128); v_status ku$_status; v_log_entry ku$_logentry; v_job_status ku$_jobstatus; v_ind PLS_INTEGER; e_success_with_info EXCEPTION; PRAGMA exception_init(e_success_with_info, -31627); BEGIN v_job_name := COALESCE( p_job_name, 'SQLFILE_' || TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS') ); DBMS_OUTPUT.put_line('=============================================='); DBMS_OUTPUT.put_line('DataPump SQL File Extraction'); DBMS_OUTPUT.put_line('Dump : ' || p_directory || ' / ' || p_dumpfile); DBMS_OUTPUT.put_line('SQL : ' || p_directory || ' / ' || p_sqlfile); DBMS_OUTPUT.put_line('Job Mode : ' || UPPER(p_job_mode)); DBMS_OUTPUT.put_line('Job : ' || v_job_name); IF p_schema_filter IS NOT NULL THEN DBMS_OUTPUT.put_line('Schema : ' || p_schema_filter); END IF; IF p_remap_schema IS NOT NULL THEN DBMS_OUTPUT.put_line( 'Remap : ' || p_remap_schema || ' -> ' || COALESCE(p_remap_schema_to, '(none)') ); END IF; DBMS_OUTPUT.put_line('----------------------------------------------'); -- open SQL_FILE job — mode MUST match the original export mode v_dp_handle := dbms_datapump.OPEN( operation => 'SQL_FILE', job_mode => UPPER(p_job_mode), -- TABLE / SCHEMA / FULL remote_link => NULL, job_name => v_job_name, version => 'COMPATIBLE' ); -- source dump file dbms_datapump.add_file( handle => v_dp_handle, filename => p_dumpfile, directory => p_directory, filetype => dbms_datapump.ku$_file_type_dump_file ); -- output SQL file (written natively by DataPump — no BLOB needed) dbms_datapump.add_file( handle => v_dp_handle, filename => p_sqlfile, directory => p_directory, filetype => dbms_datapump.ku$_file_type_sql_file ); -- schema filter: required when TABLE-export contains tables from a foreign -- schema (e.g. GPI) — without this DataPump finds nothing → ORA-31655 IF p_schema_filter IS NOT NULL THEN dbms_datapump.metadata_filter( handle => v_dp_handle, name => 'SCHEMA_EXPR', VALUE => '= ''' || p_schema_filter || '''' ); END IF; -- ----------------------------------------------------------------------- -- FILTER OPTION 1 (default / active): all DDL — no further filter -- ----------------------------------------------------------------------- -- ----------------------------------------------------------------------- -- FILTER OPTION 2 (commented): only OBJECT_GRANTs -- equivalent to: impdp ... sqlfile=grants.sql include=OBJECT_GRANT -- -- dbms_datapump.metadata_filter( -- handle => v_dp_handle, -- name => 'INCLUDE_PATH_EXPR', -- value => '= ''OBJECT_GRANT''' -- ); -- ----------------------------------------------------------------------- -- ----------------------------------------------------------------------- -- FILTER OPTION 3 (commented): metadata only -- equivalent to: impdp ... content=metadata_only -- -- dbms_datapump.set_parameter( -- handle => v_dp_handle, -- name => 'INCLUDE_METADATA', -- value => 1 -- ); -- dbms_datapump.data_filter( -- handle => v_dp_handle, -- name => 'INCLUDE_ROWS', -- value => 0 -- ); -- ----------------------------------------------------------------------- -- optional schema remap (equivalent to: remap_schema=PROD:DEV) IF p_remap_schema IS NOT NULL AND p_remap_schema_to IS NOT NULL THEN dbms_datapump.metadata_remap( handle => v_dp_handle, name => 'REMAP_SCHEMA', old_value => p_remap_schema, VALUE => p_remap_schema_to ); END IF; dbms_datapump.start_job(v_dp_handle); DBMS_OUTPUT.put_line('Job started — monitoring ...'); <<MONITOR_JOB>> LOOP dbms_datapump.get_status( handle => v_dp_handle, mask => dbms_datapump.ku$_status_job_error + dbms_datapump.ku$_status_job_status + dbms_datapump.ku$_status_wip, timeout => -1, job_state => v_job_state, status => v_status ); IF BITAND(v_status.mask, dbms_datapump.ku$_status_wip) != 0 THEN v_log_entry := v_status.wip; IF v_log_entry IS NOT NULL THEN v_ind := v_log_entry.FIRST; <<PRINT_WIP>> WHILE v_ind IS NOT NULL LOOP DBMS_OUTPUT.put_line(' WIP : ' || v_log_entry(v_ind).logtext); v_ind := v_log_entry.next(v_ind); END LOOP PRINT_WIP; END IF; END IF; IF BITAND(v_status.mask, dbms_datapump.ku$_status_job_error) != 0 THEN v_log_entry := v_status.error; IF v_log_entry IS NOT NULL THEN v_ind := v_log_entry.FIRST; <<PRINT_ERRORS>> WHILE v_ind IS NOT NULL LOOP DBMS_OUTPUT.put_line(' ERR : ' || v_log_entry(v_ind).logtext); v_ind := v_log_entry.next(v_ind); END LOOP PRINT_ERRORS; END IF; END IF; EXIT MONITOR_JOB WHEN v_job_state IN ('COMPLETED', 'STOPPED'); END LOOP MONITOR_JOB; dbms_datapump.detach(v_dp_handle); DBMS_OUTPUT.put_line('----------------------------------------------'); DBMS_OUTPUT.put_line('Final state : ' || v_job_state); IF v_job_state = 'COMPLETED' THEN DBMS_OUTPUT.put_line( 'SQL file written: ' || p_directory || ' / ' || p_sqlfile ); ELSE DBMS_OUTPUT.put_line('WARNING: Job ended with state STOPPED — check log.'); END IF; DBMS_OUTPUT.put_line('=============================================='); EXCEPTION WHEN e_success_with_info THEN BEGIN dbms_datapump.get_status( handle => v_dp_handle, mask => dbms_datapump.ku$_status_job_error, timeout => 0, job_state => v_job_state, status => v_status ); IF BITAND(v_status.mask, dbms_datapump.ku$_status_job_error) != 0 THEN v_log_entry := v_status.error; IF v_log_entry IS NOT NULL THEN v_ind := v_log_entry.FIRST; <<PRINT_SWI_INFO>> WHILE v_ind IS NOT NULL LOOP DBMS_OUTPUT.put_line(' INFO: ' || v_log_entry(v_ind).logtext); v_ind := v_log_entry.next(v_ind); END LOOP PRINT_SWI_INFO; END IF; END IF; EXCEPTION WHEN OTHERS THEN NULL; END; BEGIN dbms_datapump.detach(v_dp_handle); EXCEPTION WHEN OTHERS THEN NULL; END; DBMS_OUTPUT.put_line('----------------------------------------------'); DBMS_OUTPUT.put_line('Final state : COMPLETED (with info)'); DBMS_OUTPUT.put_line( 'SQL file written: ' || p_directory || ' / ' || p_sqlfile ); DBMS_OUTPUT.put_line('=============================================='); WHEN OTHERS THEN DBMS_OUTPUT.put_line('ERROR: datapump_extract_sql - ' || SQLERRM); BEGIN IF v_dp_handle IS NOT NULL THEN dbms_datapump.detach(v_dp_handle); END IF; EXCEPTION WHEN OTHERS THEN NULL; END; RAISE; END datapump_extract_sql; /
Ausführen und wundern:
-- erster Versuch ohne Job Mode: SET serveroutput ON BEGIN datapump_extract_sql( p_dumpfile => 'export_20260224.dmp', p_sqlfile => 'export_20260224.sql' ); END; / ============================================== DataPump SQL File Extraction Dump : DATA_PUMP_DIR / export_20260224.dmp SQL : DATA_PUMP_DIR / export_20260224.sql Job : SQLFILE_20260225_163502 ---------------------------------------------- INFO: ORA-31627: API CALL succeeded but more information IS available INFO: ORA-31655: no DATA OR metadata objects selected FOR job ---------------------------------------------- Final state : COMPLETED (WITH info) SQL file written: DATA_PUMP_DIR / export_20260224.sql ============================================== -- nach der obigen Erkenntnis das Datapump nichts aus der Datei lesen kann -- erweitert um den p_job_mode! -- neuer Aufruf SET serveroutput ON BEGIN datapump_extract_sql( p_dumpfile => 'export_20260224.dmp', p_sqlfile => 'export_20260224.sql', p_job_mode => 'TABLE', -- TABLE-Export! p_schema_filter => 'GPI', -- Quell-Schema aus dem Dump p_remap_schema => 'GPI', -- optional: GPI → GPI_DEV remappen p_remap_schema_to => 'GPI_DEV' ); END; / -- hier nun korrekt mit Job Mode ============================================== DataPump SQL File Extraction Dump : DATA_PUMP_DIR / export_20260224.dmp SQL : DATA_PUMP_DIR / export_20260224.sql Job Mode : TABLE Job : SQLFILE_20260225_164629 Schema : GPI Remap : GPI -> GPI_DEV ---------------------------------------------- Job started — monitoring ... WIP : Master TABLE "ADMIN"."SQLFILE_20260225_164629" successfully loaded/unloaded WIP : Starting "ADMIN"."SQLFILE_20260225_164629": WIP : Processing object TYPE TABLE_EXPORT/TABLE/TABLE WIP : Processing object TYPE TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT WIP : Processing object TYPE TABLE_EXPORT/TABLE/COMMENT WIP : Processing object TYPE TABLE_EXPORT/TABLE/INDEX/INDEX WIP : Processing object TYPE TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT WIP : Processing object TYPE TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS WIP : Processing object TYPE TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT WIP : Processing object TYPE TABLE_EXPORT/TABLE/TRIGGER WIP : Processing object TYPE TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS WIP : Job "ADMIN"."SQLFILE_20260225_164629" successfully completed at Wed Feb 25 16:46:37 2026 elapsed 0 00:00:05 ---------------------------------------------- Final state : COMPLETED SQL file written: DATA_PUMP_DIR / export_20260224.sql ============================================== SELECT * FROM DBMS_CLOUD.LIST_FILES('DATA_PUMP_DIR') ORDER BY LAST_MODIFIED DESC ; BEGIN datapump_show_log('export_20260224.sql'); END; / --
Nun haben wir auch das SQL in diesem Dump, das wir für die weitere Analyse benötigen. Wir wissen jetzt dann auch unter welchen Schema Namen die Objekte im Dump definiert sind.
Eigentliche Importroutine anlegen
Nachdem wir uns nun sicher sind das die Import Datei auch funktionieren kann, legen wir die Routine für den eigentlichen Import an und hoffen das es beim nächsten mal einfach so funktioniert.
Procedure für den Import anlegen:
- datapump_import.sql
---------------------------------------------------------------------------- -- Procedure : datapump_import -- Purpose : Performs a DataPump IMPORT directly from Object Storage URI. -- Equivalent to: -- impdp .../... credential=X dumpfile=<URL> -- directory=Y job_mode=Z -- schemas=... remap_schema=... -- Uses KU$_FILE_TYPE_URIDUMP_FILE for direct URI access — -- no local download required (unlike datapump_analyze_dumpfile -- and datapump_extract_sql which need local files). -- Parameters: p_objectstorage_url - full HTTPS URL to the .dmp file -- p_credential_name - DBMS_CLOUD credential name for -- Object Storage access -- (NULL = PAR / public URL) -- p_directory - Oracle directory for log file output -- p_job_mode - must match original export mode: -- 'TABLE' | 'SCHEMA' | 'FULL' -- p_schema_filter - source schema to filter on -- (required for TABLE-mode dumps with -- foreign schema — prevents ORA-31655) -- p_remap_schema - optional: source schema to remap FROM -- p_remap_schema_to - optional: target schema to remap TO -- p_job_name - optional: DataPump job name -- (default: auto-generated with timestamp) -- Notes : Requires KU$_FILE_TYPE_URIDUMP_FILE (Oracle 21c+). -- Credential is passed as 'directory' parameter in ADD_FILE -- for URI-based dump files — this is the Oracle API convention. -- For PAR/public URLs a dummy credential name must still be -- supplied due to Oracle API requirements; credentials are -- ignored for pre-authenticated requests. -- Modified : 2026-02-25 - initial version ---------------------------------------------------------------------------- CREATE OR REPLACE PROCEDURE datapump_import( p_objectstorage_url IN VARCHAR2, p_credential_name IN VARCHAR2 DEFAULT NULL, p_directory IN VARCHAR2 DEFAULT 'DATA_PUMP_DIR', p_job_mode IN VARCHAR2 DEFAULT 'TABLE', p_schema_filter IN VARCHAR2 DEFAULT NULL, p_remap_schema IN VARCHAR2 DEFAULT NULL, p_remap_schema_to IN VARCHAR2 DEFAULT NULL, p_table_exists_action IN VARCHAR2 DEFAULT 'SKIP', -- SKIP | APPEND | TRUNCATE | REPLACE p_job_name IN VARCHAR2 DEFAULT NULL ) AUTHID current_user IS -- ============================================================ -- NOTE: Oracle 21c+ feature — KU$_FILE_TYPE_URIDUMP_FILE -- Feature : URI-based dump file import (Object Storage / HTTP) -- Since : Oracle 21c (fully supported in 23ai) -- Docs : https://docs.oracle.com/en/database/oracle/oracle-database/21/arpls/DBMS_DATAPUMP.html -- ============================================================ v_dp_handle NUMBER; v_job_state VARCHAR2(30) := 'UNDEFINED'; v_job_name VARCHAR2(128); v_log_file VARCHAR2(255); v_status ku$_status; v_log_entry ku$_logentry; v_job_status ku$_jobstatus; v_ind PLS_INTEGER; -- ORA-31627: job completed with additional info — not a real error e_success_with_info EXCEPTION; PRAGMA exception_init(e_success_with_info, -31627); ---------------------------------------------------------------------------- -- Private: extract filename from URL (last path segment after '/') -- used to derive a meaningful log file name ---------------------------------------------------------------------------- FUNCTION extract_filename(p_url IN VARCHAR2) RETURN VARCHAR2 IS v_pos NUMBER; v_name VARCHAR2(255); BEGIN v_pos := INSTR(p_url, '/', -1); IF v_pos > 0 THEN v_name := SUBSTR(p_url, v_pos + 1); ELSE v_name := p_url; END IF; -- strip extension and append _imp.log v_pos := INSTR(v_name, '.', -1); IF v_pos > 0 THEN v_name := SUBSTR(v_name, 1, v_pos - 1); END IF; RETURN SUBSTR(v_name, 1, 100) || '_imp.log'; END extract_filename; ---------------------------------------------------------------------------- -- Private: print all entries from a ku$_logentry collection ---------------------------------------------------------------------------- PROCEDURE print_log_entries( p_entries IN ku$_logentry, p_prefix IN VARCHAR2 ) IS v_i PLS_INTEGER; BEGIN IF p_entries IS NULL THEN RETURN; END IF; v_i := p_entries.FIRST; WHILE v_i IS NOT NULL LOOP DBMS_OUTPUT.put_line(p_prefix || p_entries(v_i).logtext); v_i := p_entries.next(v_i); END LOOP; END print_log_entries; BEGIN v_job_name := COALESCE( p_job_name, 'IMP_' || TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS') ); v_log_file := extract_filename(p_objectstorage_url); DBMS_OUTPUT.put_line('=============================================='); DBMS_OUTPUT.put_line('DataPump URI Import'); DBMS_OUTPUT.put_line('URL : ' || p_objectstorage_url); DBMS_OUTPUT.put_line('Job Mode : ' || UPPER(p_job_mode)); DBMS_OUTPUT.put_line('Job : ' || v_job_name); DBMS_OUTPUT.put_line('Log : ' || p_directory || ' / ' || v_log_file); IF p_credential_name IS NOT NULL THEN DBMS_OUTPUT.put_line('Cred : ' || p_credential_name); ELSE DBMS_OUTPUT.put_line('Cred : (none - PAR / public URL)'); END IF; IF p_schema_filter IS NOT NULL THEN DBMS_OUTPUT.put_line('Schema : ' || p_schema_filter); END IF; IF p_remap_schema IS NOT NULL THEN DBMS_OUTPUT.put_line( 'Remap : ' || p_remap_schema || ' -> ' || COALESCE(p_remap_schema_to, '(none)') ); END IF; DBMS_OUTPUT.put_line('On Exists : ' || UPPER(p_table_exists_action)); DBMS_OUTPUT.put_line('----------------------------------------------'); -- open import job — mode must match the original export mode v_dp_handle := dbms_datapump.OPEN( operation => 'IMPORT', job_mode => UPPER(p_job_mode), remote_link => NULL, job_name => v_job_name, version => 'COMPATIBLE' ); -- register URI dump file -- NOTE: for KU$_FILE_TYPE_URIDUMP_FILE the 'directory' parameter -- receives the CREDENTIAL NAME (not a directory object) — this is -- Oracle's API convention for URI-based file access IF p_credential_name IS NOT NULL THEN dbms_datapump.add_file( handle => v_dp_handle, filename => p_objectstorage_url, directory => p_credential_name, filetype => dbms_datapump.ku$_file_type_uridump_file ); ELSE -- PAR / public URL: credential parameter still required by Oracle API -- but will be ignored for pre-authenticated requests dbms_datapump.add_file( handle => v_dp_handle, filename => p_objectstorage_url, directory => NULL, filetype => dbms_datapump.ku$_file_type_uridump_file ); END IF; -- log file goes to local directory (SQL_FILE for URI not supported) dbms_datapump.add_file( handle => v_dp_handle, filename => v_log_file, directory => p_directory, filetype => dbms_datapump.ku$_file_type_log_file ); -- schema filter: required for TABLE-mode dumps with foreign schema -- prevents ORA-31655 (no objects found) IF p_schema_filter IS NOT NULL THEN dbms_datapump.metadata_filter( handle => v_dp_handle, name => 'SCHEMA_EXPR', VALUE => '= ''' || p_schema_filter || '''' ); END IF; -- optional schema remap (equivalent to: remap_schema=GPI:GPI_DEV) IF p_remap_schema IS NOT NULL AND p_remap_schema_to IS NOT NULL THEN dbms_datapump.metadata_remap( handle => v_dp_handle, name => 'REMAP_SCHEMA', old_value => p_remap_schema, VALUE => p_remap_schema_to ); END IF; -- validate TABLE_EXISTS_ACTION — fail fast before job start IF UPPER(p_table_exists_action) NOT IN ('SKIP', 'APPEND', 'TRUNCATE', 'REPLACE') THEN raise_application_error( -20001, 'Invalid TABLE_EXISTS_ACTION: ''' || p_table_exists_action || ''' — valid values: SKIP | APPEND | TRUNCATE | REPLACE' ); END IF; dbms_datapump.set_parameter( handle => v_dp_handle, name => 'TABLE_EXISTS_ACTION', VALUE => UPPER(p_table_exists_action) ); -- start the job dbms_datapump.start_job(v_dp_handle); DBMS_OUTPUT.put_line('Job started — monitoring ...'); -- monitor until COMPLETED or STOPPED <<MONITOR_JOB>> LOOP dbms_datapump.get_status( handle => v_dp_handle, mask => dbms_datapump.ku$_status_job_error + dbms_datapump.ku$_status_job_status + dbms_datapump.ku$_status_wip, timeout => -1, job_state => v_job_state, status => v_status ); -- work-in-progress messages (progress updates) IF BITAND(v_status.mask, dbms_datapump.ku$_status_wip) != 0 THEN print_log_entries(v_status.wip, ' WIP : '); END IF; -- error messages (non-fatal warnings during job execution) IF BITAND(v_status.mask, dbms_datapump.ku$_status_job_error) != 0 THEN print_log_entries(v_status.error, ' ERR : '); END IF; EXIT MONITOR_JOB WHEN v_job_state IN ('COMPLETED', 'STOPPED'); END LOOP MONITOR_JOB; dbms_datapump.detach(v_dp_handle); DBMS_OUTPUT.put_line('----------------------------------------------'); DBMS_OUTPUT.put_line('Final state : ' || v_job_state); IF v_job_state = 'COMPLETED' THEN DBMS_OUTPUT.put_line('Import completed successfully.'); DBMS_OUTPUT.put_line( 'Log file : ' || p_directory || ' / ' || v_log_file ); ELSE DBMS_OUTPUT.put_line( 'WARNING: Job ended with state STOPPED — check log: ' || p_directory || ' / ' || v_log_file ); END IF; DBMS_OUTPUT.put_line('=============================================='); EXCEPTION WHEN e_success_with_info THEN -- ORA-31627: import completed — additional info messages available BEGIN dbms_datapump.get_status( handle => v_dp_handle, mask => dbms_datapump.ku$_status_job_error, timeout => 0, job_state => v_job_state, status => v_status ); IF BITAND(v_status.mask, dbms_datapump.ku$_status_job_error) != 0 THEN print_log_entries(v_status.error, ' INFO: '); END IF; EXCEPTION WHEN OTHERS THEN NULL; END; BEGIN dbms_datapump.detach(v_dp_handle); EXCEPTION WHEN OTHERS THEN NULL; END; DBMS_OUTPUT.put_line('----------------------------------------------'); DBMS_OUTPUT.put_line('Final state : COMPLETED (with info)'); DBMS_OUTPUT.put_line('Import completed successfully.'); DBMS_OUTPUT.put_line( 'Log file : ' || p_directory || ' / ' || v_log_file ); DBMS_OUTPUT.put_line('=============================================='); WHEN OTHERS THEN DBMS_OUTPUT.put_line('ERROR: datapump_import - ' || SQLERRM); DBMS_OUTPUT.put_line('URL : ' || p_objectstorage_url); BEGIN IF v_dp_handle IS NOT NULL THEN dbms_datapump.detach(v_dp_handle); END IF; EXCEPTION WHEN OTHERS THEN NULL; END; RAISE; END datapump_import; /
Aufrufen mit:
-- Truncate + neu laden (Standard-Refresh-Szenario) SET serveroutput ON BEGIN datapump_import( p_objectstorage_url => 'https://objectstorage.eu-frankfurt-1.oraclecloud.com/p/xxxxx/o/export_20260224.dmp', p_credential_name => NULL, p_job_mode => 'TABLE', p_schema_filter => 'GPI', p_remap_schema => 'GPI', p_remap_schema_to => 'GPI_DEV', p_table_exists_action => 'TRUNCATE' -- Daten ersetzen, DDL bleibt ); END; / ============================================== DataPump URI Import URL : https://objectstorage.eu-frankfurt-1.oraclecloud.com/p/xxxxx/o/export_20260224.dmp Job Mode : TABLE Job : IMP_20260225_170011 Log : DATA_PUMP_DIR / export_20260224_imp.log Cred : (NONE - PAR / public URL) Schema : GPI Remap : GPI -> GPI_DEV ON EXISTS : TRUNCATE ---------------------------------------------- Job started — monitoring ... WIP : Master TABLE "ADMIN"."IMP_20260225_170011" successfully loaded/unloaded WIP : Starting "ADMIN"."IMP_20260225_170011": WIP : Processing object TYPE TABLE_EXPORT/TABLE/TABLE WIP : TABLE "GPI_DEV"."GPI_ZU" EXISTS AND has been truncated. DATA will be loaded but ALL dependent metadata will be skipped due TO table_exists_action OF TRUNCATE .... WIP : . . imported "GPI_DEV"."GPI_ZU" 4.1 MB 27496 ROWS WIP : Processing object TYPE TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT WIP : Job "ADMIN"."IMP_20260225_170011" successfully completed at Wed Feb 25 17:00:57 2026 elapsed 0 00:00:43 ---------------------------------------------- Final state : COMPLETED Import completed successfully. Log file : DATA_PUMP_DIR / export_20260224_imp.log ==============================================
Und kaum ist alles korrekt … funktioniert es auch wie erwartet.
Probleme
Das Projekt wurde durch zahlreiche Probleme, die in diesem Umfeld auftreten können, arg verzögert – und wie meist sitzt das größte Problem vor der Tastatur …
ORA-31655: no data or metadata objects selected for job
Dieser Fehler hat mehrere mögliche Ursachen – nicht immer ist es ein reines Job-Mode-Problem.
1. Job-Mode-Mismatch (häufigste Ursache)
Der Export-Modus muss beim Import und bei SQL_FILE exakt übereinstimmen. DataPump sucht intern nach unterschiedlichen Metadaten-Pfaden je nach Modus – ein falscher Modus führt dazu, dass schlicht nichts gefunden wird.
-- Exportiert mit: expdp ... tables=GPI.MY_TABLE → job_mode = TABLE -- Exportiert mit: expdp ... schemas=GPI → job_mode = SCHEMA -- Exportiert mit: expdp ... full=y → job_mode = FULL -- Falscher Modus beim SQL_FILE / IMPORT → ORA-31655: v_dp_handle := DBMS_DATAPUMP.OPEN( operation => 'SQL_FILE', job_mode => 'SCHEMA', -- FALSCH wenn Export job_mode=TABLE war! ... ); -- Richtiger Modus: v_dp_handle := DBMS_DATAPUMP.OPEN( operation => 'SQL_FILE', job_mode => 'TABLE', -- muss dem Export-Modus entsprechen ... );
Den Export-Modus aus dem Dump-Header auslesen mit datapump_analyze_dumpfile –
der Job-Name verrät den Modus (siehe Abschnitt 2. Test weiter oben):
-- Job Name im Header zeigt den Export-Modus: -- "SYSTEM"."SYS_EXPORT_TABLE_01" → job_mode = 'TABLE' -- "SYSTEM"."SYS_EXPORT_SCHEMA_01" → job_mode = 'SCHEMA' -- "SYSTEM"."SYS_EXPORT_FULL_01" → job_mode = 'FULL' BEGIN datapump_analyze_dumpfile( p_objectstorage_url => 'https://objectstorage.eu-frankfurt-1.oraclecloud.com/p/xxxxx/o/export_20260224.dmp' ); END; /
2. Fehlendes Schema-Filter bei TABLE-Mode-Export aus fremdem Schema
Wurde mit TABLE-Modus aus einem Schema exportiert, das nicht dem aktuellen Datenbankuser entspricht, findet DataPump ohne expliziten Schema-Filter nichts.
-- Ohne Filter → ORA-31655 (DataPump sucht im falschen Schema) -- Mit Filter → Objekte werden korrekt gefunden: DBMS_DATAPUMP.METADATA_FILTER( handle => v_dp_handle, name => 'SCHEMA_EXPR', VALUE => '= ''GPI''' -- Quell-Schema aus dem Dump angeben );
3. Objekt-/Tabellenname nicht im Dump enthalten
Bei TABLE-Mode-Exporten kann zusätzlich ein NAME_EXPR-Filter greifen der auf nicht-existente Tabellen filtert:
-- Prüfen ob ein NAME_EXPR-Filter gesetzt ist der zu eng filtert: DBMS_DATAPUMP.METADATA_FILTER( handle => v_dp_handle, name => 'NAME_EXPR', VALUE => 'IN (''MY_TABLE'', ''MY_OTHER_TABLE'')' -- Tabellen wirklich im Dump? ); -- Zur Kontrolle: welche Tabellen sind überhaupt im Dump? -- → datapump_extract_sql ausführen und SQL-Datei prüfen BEGIN datapump_extract_sql( p_dumpfile => 'export_20260224.dmp', p_sqlfile => 'export_20260224.sql', p_job_mode => 'TABLE', p_schema_filter => 'GPI' ); END; /
4. Schnell-Checkliste bei ORA-31655
-- Schritt 1: Job-Modus aus Dump-Header lesen BEGIN datapump_analyze_dumpfile( p_objectstorage_url => 'https://objectstorage.eu-frankfurt-1.oraclecloud.com/p/xxxxx/o/export_20260224.dmp' ); END; / -- → "Detected Job Mode : TABLE" → p_job_mode => 'TABLE' verwenden -- Schritt 2: DDL aus Dump extrahieren und Objekte/Schemas prüfen BEGIN datapump_extract_sql( p_dumpfile => 'export_20260224.dmp', p_sqlfile => 'export_20260224.sql', p_job_mode => 'TABLE', -- aus Schritt 1 p_schema_filter => 'GPI' -- Quell-Schema aus dem Dump ); END; / -- Schritt 3: SQL-Datei auf Schema- und Tabellennamen prüfen BEGIN datapump_show_log('export_20260224.sql'); END; / -- → CREATE TABLE "GPI"."..." bestätigt Schema und Tabellennamen
ORA-31631: privileges are required
Dieser Fehler hat mehrere mögliche Ursachen – am besten systematisch durchgehen:
1. Fehlende Rolle DATAPUMP_CLOUD_IMP
Die häufigste Ursache. Aktive Rollen prüfen:
-- Aktive Rollen der aktuellen Session prüfen SELECT * FROM SESSION_ROLES ORDER BY 1; -- Rolle gezielt suchen SELECT * FROM SESSION_ROLES WHERE ROLE = 'DATAPUMP_CLOUD_IMP'; -- Falls nicht vorhanden, als ADMIN vergeben: GRANT DATAPUMP_CLOUD_IMP TO <username>;
2. Falscher directory-Parameter bei URI-Import (ADD_FILE)
Ein sehr häufiger Fallstrick: Bei ku$_file_type_uridump_file darf directory nicht auf ein Oracle-Directory-Objekt (z.B. DATA_PUMP_DIR) gesetzt werden – das löst ORA-31631 aus.
Bei URI-basierten Dumps übernimmt der directory-Parameter den Credential-Namen oder muss null sein (bei PAR-URLs).
-- FALSCH: directory auf DATA_PUMP_DIR bei URI-Import → ORA-31631 !! DBMS_DATAPUMP.ADD_FILE( handle => v_dp_handle, filename => p_objectstorage_url, directory => 'DATA_PUMP_DIR', -- FALSCH bei URI! filetype => DBMS_DATAPUMP.ku$_file_type_uridump_file ); -- RICHTIG: Mit Credential (Object Storage mit eigenem Credential) DBMS_DATAPUMP.ADD_FILE( handle => v_dp_handle, filename => p_objectstorage_url, directory => 'MY_OCI_CREDENTIAL', -- Credential-Name, kein Directory! filetype => DBMS_DATAPUMP.ku$_file_type_uridump_file ); -- RICHTIG: PAR / public URL (kein Credential nötig) DBMS_DATAPUMP.ADD_FILE( handle => v_dp_handle, filename => p_objectstorage_url, directory => NULL, -- null bei PAR-URL filetype => DBMS_DATAPUMP.ku$_file_type_uridump_file );
3. Credential existiert nicht oder ist ungültig
-- Verfügbare Credentials prüfen SELECT credential_name, username, enabled FROM all_credentials ORDER BY credential_name; -- Credential anlegen (falls fehlend) BEGIN DBMS_CLOUD.CREATE_CREDENTIAL( credential_name => 'MY_OCI_CREDENTIAL', username => 'user@example.com', password => '<auth-token>' ); END; /
4. ku$_file_type_uridump_file nicht verfügbar (Oracle < 21c)
ku$_file_type_uridump_file erfordert Oracle 21c+. Auf älteren Versionen muss die Datei zuerst lokal ins DATA_PUMP_DIR geladen werden:
-- Oracle-Version prüfen SELECT banner FROM v$version; -- Workaround für Oracle < 21c: Datei zuerst lokal laden BEGIN DBMS_CLOUD.GET_OBJECT( object_uri => 'https://objectstorage.eu-frankfurt-1.oraclecloud.com/p/xxxxx/o/export_20260224.dmp', directory_name => 'DATA_PUMP_DIR', file_name => 'export_20260224.dmp' ); END; / -- Dann normalen file_type_dump_file mit DATA_PUMP_DIR verwenden
5. Schnell-Checkliste bei ORA-31631
-- Alles auf einmal prüfen: SELECT 'ROLE: ' || ROLE AS check_item, 'OK' AS STATUS FROM session_roles WHERE ROLE = 'DATAPUMP_CLOUD_IMP' UNION ALL SELECT 'CREDENTIAL: ' || credential_name, CASE WHEN enabled = 'TRUE' THEN 'OK' ELSE 'DISABLED' END FROM all_credentials UNION ALL SELECT 'DB VERSION', substr(banner, 1, 50) FROM v$version WHERE banner LIKE 'Oracle%';
Fehlgeschlagene Import-Jobs bereinigen
Nach fehlgeschlagenen oder abgebrochenen Testläufen bei den ganzen Versuchen, es richtig durchzuführen, bleiben gelegentlich DataPump-Jobs als „NOT RUNNING“ in der dba_datapump_jobs zurück.
Diese verwaisten Jobs (Orphaned Jobs) stören neue Jobs zwar nicht direkt, hinterlassen aber Master-Tabellen im Schema, die Platz belegen und die Übersicht erschweren.
1. Vorhandene Jobs prüfen
-- Alle DataPump-Jobs anzeigen (Status NOT RUNNING = verwaist) col owner_name format a15 col job_name format a30 col operation format a10 col job_mode format a10 col state format a12 SELECT owner_name, job_name, operation, job_mode, state, attached_sessions FROM dba_datapump_jobs ORDER BY owner_name, job_name; -- Typische Ausgabe nach vielen Testläufen: -- OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE ATTACHED -- ----------- ------------------------------ ---------- --------- ------------ -------- -- ADMIN ADMIN_IMPORT_25_13_57 IMPORT SCHEMA DEFINING 1 -- ADMIN ADMIN_IMPORT_25_13_58 IMPORT SCHEMA DEFINING 1 -- ADMIN IMP_20260225_163502 IMPORT TABLE NOT RUNNING 0 -- ADMIN IMP_20260225_164011 IMPORT TABLE NOT RUNNING 0 -- ADMIN SQLFILE_20260225_163101 SQL_FILE TABLE NOT RUNNING 0
2. Master-Tabellen der verwaisten Jobs identifizieren
Jeder DataPump-Job legt intern eine Master-Tabelle im Schema des ausführenden Users an – bei Abbruch bleibt diese zurück:
-- Master-Tabellen zu verwaisten Jobs finden SELECT o.status, o.object_id, o.object_type, o.owner || '.' || o.object_name AS "OWNER.OBJECT" FROM dba_objects o JOIN dba_datapump_jobs j ON o.owner = j.owner_name AND o.object_name = j.job_name WHERE j.job_name NOT LIKE 'BIN$%' ORDER BY o.owner, o.object_name; -- Ausgabe: -- STATUS OBJECT_ID OBJECT_TYPE OWNER.OBJECT -- ------- --------- ----------- ---------------------------- -- VALID 147547 TABLE ADMIN.ADMIN_IMPORT_25_13_57 -- VALID 147649 TABLE ADMIN.ADMIN_IMPORT_25_13_58 -- VALID 147751 TABLE ADMIN.ADMIN_IMPORT_25_14_02
3. Versuch: Job per DBMS_DATAPUMP.STOP_JOB beenden
Bei Jobs im Status NOT RUNNING schlägt dies meist fehl – das ist erwartet:
-- Versuch den Job sauber zu stoppen (schlägt bei NOT RUNNING fehl → ORA-31626) -- Job-Name aus vorheriger Abfrage DECLARE v_handle NUMBER; BEGIN v_handle := dbms_datapump.attach('IMP_20260225_163502', 'ADMIN'); dbms_datapump.stop_job(v_handle, 1, 0); END; / -- ORA-31626: job does not exist → Job ist bereits tot, Master-Tabelle direkt droppen
4. Master-Tabellen droppen (eigentliche Bereinigung)
-- DROP-Statements erzeugen und einzeln ausführen SELECT 'drop table ' || owner_name || '.' || job_name || ' purge;' AS command FROM dba_datapump_jobs ORDER BY owner_name, job_name; -- Einzeln droppen: DROP TABLE ADMIN.IMP_20260225_163502 purge; DROP TABLE ADMIN.IMP_20260225_164011 purge; .... -- Prüfen ob alles weg ist: SELECT owner_name, job_name, state FROM dba_datapump_jobs ORDER BY owner_name, job_name; -- → no rows selected
5. Externe Tabellen prüfen (ET$-Objekte)
Bei External-Table-Exporten können zusätzlich temporäre externe Tabellen
(ET$…) zurückbleiben:
-- Verwaiste externe DataPump-Tabellen finden SELECT owner, object_name, object_type, created, last_ddl_time FROM dba_objects WHERE object_name LIKE 'ET$%' AND object_type IN ('TABLE', 'INDEX') ORDER BY owner, object_name; -- Gefundene ET$-Tabellen ebenfalls droppen: -- drop table ADMIN.ET$00654E1E0001 purge;
6. Alles auf einmal – Cleanup-Script für die ADB
-- Alle NOT RUNNING DataPump-Jobs des aktuellen Users automatisch bereinigen DECLARE v_handle NUMBER; v_count PLS_INTEGER := 0; BEGIN DBMS_OUTPUT.put_line('=============================================='); DBMS_OUTPUT.put_line('DataPump Orphaned Job Cleanup'); DBMS_OUTPUT.put_line('----------------------------------------------'); <<CLEANUP_JOBS>> FOR r_job IN ( SELECT j.owner_name, j.job_name, j.operation, j.state FROM dba_datapump_jobs j WHERE j.state = 'NOT RUNNING' AND j.owner_name = SYS_CONTEXT('USERENV', 'SESSION_USER') ORDER BY j.job_name ) LOOP DBMS_OUTPUT.put_line( 'Dropping: ' || r_job.owner_name || '.' || r_job.job_name || ' (' || r_job.operation || ')' ); -- Master-Tabelle droppen (= Job entfernen) BEGIN EXECUTE IMMEDIATE 'drop table ' || r_job.owner_name || '.' || r_job.job_name || ' purge'; v_count := v_count + 1; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line( ' WARN: ' || SQLERRM ); END; END LOOP CLEANUP_JOBS; DBMS_OUTPUT.put_line('----------------------------------------------'); DBMS_OUTPUT.put_line('Dropped ' || v_count || ' orphaned job(s).'); DBMS_OUTPUT.put_line('=============================================='); END; /
Wichtig vor dem Cleanup:
Sicherstellen, dass ein Job mit Status NOT RUNNING wirklich verwaist ist und
nicht ein absichtlich pausierter Job (
STOP_JOB im interaktiven Modus).
Ein pausierter Job kann mit DBMS_DATAPUMP.ATTACH + START_JOB wieder
aufgenommen werden – nach dem DROP der Master-Tabelle ist das nicht mehr möglich!
Quellen
Oracle:
Web:
Bei Job-Problemen:
- How To Cleanup Orphaned DataPump Jobs In DBA_DATAPUMP_JOBS ? (Doc ID 336014.1)
"Autor: Gunther Pipperr"
cloud/import_data_adb_oracle_cloud_26_ai.txt · Zuletzt geändert: von gpipperr
