1Z0-184-25 Question 178
Single answerYou are tasked with migrating an Oracle Database schema that contains vector data (stored in SDO_GEOMETRY columns) from an on-premises environment to an Oracle Database running on Oracle Cloud Infrastructure. You plan to use Oracle Data Pump for both export and import. Which approach ensures that the spatial metadata and indexes remain valid so the vector data is fully recognized in the target database?
- A
Perform a table-mode export of only the vector data tables, then import them with CONTENT=DATA_ONLY into the target environment.
- B
Use a full transportable export (FULL=Y, TRANSPORTABLE=ALWAYS), ensuring the Oracle Spatial or Locator feature is available in the target database.
- C
Export the MDSYS schema separately and import it alongside the user schema that holds vector data tables.
- D
Perform an export with CONTENT=METADATA_ONLY first, then recreate and rebuild spatial indexes manually in the target database.
Show answer and explanation
Correct answer: B
Explanation
Using Oracle Data Pump with a full transportable export/import is the standard approach for migrating Oracle databases that contain vector data in SDO_GEOMETRY columns. This method preserves both the table data and the associated metadata (such as spatial indexes and geometry metadata) as long as the target database environment has either Oracle Spatial or Oracle Locator installed. Reference: Oracle Data Pump documentation and Oracle Spatial/Locator best practices.
- A. Incorrect.
Option 1 is incorrect because exporting only the tables in table mode and then importing with CONTENT=DATA_ONLY omits critical spatial metadata and index definitions. This approach can lead to inconsistencies or missing references needed for SDO_GEOMETRY columns.
- B. Correct.
Option 2 is correct. A full transportable export (using FULL=Y and TRANSPORTABLE=ALWAYS) is recommended for moving spatial data and its associated metadata to Oracle Cloud Infrastructure. It ensures that all required metadata (including spatial indexes and geometry metadata) is migrated, provided the target database has Oracle Spatial or Oracle Locator installed.
- C. Incorrect.
Option 3 is not the best practice. While including the MDSYS schema might seem helpful, you typically do not export the Oracle-supplied schemas separately. Instead, you ensure the target environment has the Spatial or Locator feature installed, and then allow the full transportable export/import to handle schema dependencies automatically.
- D. Incorrect.
Option 4 is incorrect. Using CONTENT=METADATA_ONLY and then manually re-creating indexes introduces extra complexity and risk of errors. Spatial indexes and metadata references are best preserved automatically during a full transportable export/import.