1Z0-082 - Oracle Database Administration I Moving and Migrating Data Questions and Answers — Questions and Answers
Question 1: A DBA needs to export only the `HR` and `OE` schemas from a production database to a dump file using Oracle Data Pump. Which `expdp` parameter should be used to specify these schemas?
- TABLES=HR.*,OE.*
- OWNER=HR,OE
- SCHEMAS=HR,OE (Correct answer)
- FULL=N SCHEMAS=(HR,OE)
Correct answer: SCHEMAS=HR,OE
The `SCHEMAS` parameter in `expdp` is used to specify a list of one or more schemas to be exported in schema-mode. The `OWNER` parameter was used in the original `exp` utility and has been replaced by `SCHEMAS` in Data Pump. `TABLES` is used for exporting specific tables, not entire schemas.
Question 2: When using SQL*Loader to load data from a flat file into a database table, what is the primary purpose of the control file?
- To authenticate the user running the load process.
- To describe the format of the input data file and map its fields to the target table columns. (Correct answer)
- To log all errors that occur during the data load operation.
- To store the data that is being loaded before it is committed.
Correct answer: To describe the format of the input data file and map its fields to the target table columns.
The SQL*Loader control file is a text file containing DDL instructions that tell SQL*Loader where to find the data, how to parse it, which table and columns to load it into, and how to handle potential errors. It essentially provides the metadata map for the load operation.
Question 3: A data analyst needs to query data residing in a large comma-separated values (CSV) file located on the database server's file system without permanently loading it into the database. Which Oracle feature is best suited for this task?
- External Tables (Correct answer)
- Database Links
- SQL*Loader
- Transportable Tablespaces
Correct answer: External Tables
External Tables allow Oracle to treat a flat file on the server's file system as if it were a read-only database table. This enables users to query the data using standard SQL, including joins with other tables, without the need to load the data into the database first, which avoids data duplication and the overhead of insert operations.
Question 4: You are planning to move a large tablespace from an Oracle database on an AIX server to another database on a Linux server using the transportable tablespaces method. What is a critical prerequisite for this operation?
- The source and target databases must have the same DB_BLOCK_SIZE.
- The source tablespace must be taken offline during the entire transport process.
- The source and target databases must have compatible character sets and endian formats, or RMAN must be used for conversion. (Correct answer)
- The source and target databases must have the same database version and patch level.
Correct answer: The source and target databases must have compatible character sets and endian formats, or RMAN must be used for conversion.
Transportable tablespaces involve physically copying datafiles. When moving between platforms with different endian formats (byte ordering), such as AIX (big-endian) and Linux (little-endian), you must use the RMAN `CONVERT` command to reformat the datafiles. Additionally, the source and target databases must have compatible character sets.
Question 5: A DBA needs to perform a Data Pump Import (`impdp`) operation to create all tables and indexes from a dump file but without loading any of the row data. Which parameter and value should be specified?
- INCLUDE=TABLE,INDEX
- CONTENT=METADATA_ONLY (Correct answer)
- ROWS=N
- CONTENT=STRUCTURE_ONLY
Correct answer: CONTENT=METADATA_ONLY
The `CONTENT=METADATA_ONLY` parameter tells the Data Pump utility to process only the object definitions (DDL) from the dump file. This creates the structures like tables, indexes, and views but skips the actual data load.
Question 6: Which statement accurately describes a key advantage of using a direct path load with SQL*Loader compared to a conventional path load?
- It generates significantly more redo and undo data for better recoverability.
- It allows triggers and referential integrity constraints to be active on the target table during the load.
- It automatically builds all indexes on the table before the data load begins.
- It bypasses the database buffer cache and writes formatted data blocks directly to the datafiles, improving performance. (Correct answer)
Correct answer: It bypasses the database buffer cache and writes formatted data blocks directly to the datafiles, improving performance.
A direct path load significantly improves performance for large data loads by creating formatted data blocks and writing them directly to the datafiles. This bypasses much of the SQL command processing and the database buffer cache. A conventional path load uses standard `INSERT` statements, which is a slower process.
1Z0-082 - Oracle Database Administration I Moving and Migrating Data Questions and Answers
A DBA needs to export only the `HR` and `OE` schemas from a production database to a dump file using Oracle Data Pump.
Which `expdp` parameter should be used to specify these schemas?