Oracle 12c Upgrade: 19 – Oracle Data Pump, SQL*Loader, External Tables Enhancements
Home > Flashcards > Print Preview
The flashcards below were created by user
on FreezingBlue Flashcards
. What would you like to do?
What does a full transportable export contain?
All the objects and data needed to create a copy of the database
To perform a fully transportable export, what parameters must be defined in the command?
If performing a fully transportable export and there is a failure, what happens?
The process must be restarted, it's not resumable
What's the lowest RDBMS version of the source database that can be used
Why might a Full Transportable Export/Import be useful?
- Upgrading the database
- Transporting a non-CDB into a CDB
- Moving a database to a different system
What are the restrictions for Full Transportable Export/Import?
- Must specify the ENCRYPTED_PARAMETER if the database has any
- encrypted tablespaces or encrypted columns
- If the endian formats differ, use DBMS_FILE_TRANSFER to convert
- Auditing can't be enabled on SYSTEM/SYSAUX
- Tables with LONG and LONG RAW not exported when transporting a database over the network
- Encrypted tablespaces not supported if endianness of both platforms differ
In impdp, what does TRANSFORM option DISABLE_ARCHIVE_LOGGING do?
- Disable some or all redo log information being generated
How is TRANSFORM option DISABLE_ARCHIVE_LOGGING used in impdp
- transform=disable_archive_logging:y - all objects
- transform=disable_archive_logging:y:index - all indexes
- transform=disable_archive_logging:y:table - all tables
What option of TRANSFORM parameter in impdp disables redo?
When is TRANSFORM=DISABLE_ARCHIVE_LOGGING ignored?
If the database/tablespace is in FORCED_LOGGNG mode
To export a view as a table, what is the expdp parameter?
When exporting/importing a view as a table, how can the view be renamed?
Using REMAP_TABLE SCHEMA.TAB1:TAB2
What does the TRANSFORM=TABLE_COMPRESSION_CLAUSE do?
Allows for the compression of data during an import
What are the options for TRANSFORM=TABLE_COMPRESSION_CLAUSE?
- NONE - Inherit compression from the tablespace settings
- COMPRESS - Same as ROW STORE COMPRESS BASIC
- ROW STORE COMPRESS BASIC - Basic compression
- ROW STORE COMPRESS ADVANCED - Uses Advance compression
What is the syntax for TRANSFORM=TABLE_COMPRESSION_CLAUSE?
TRANSFORM=TABLE_COMPRESSION_CLAUSE:[NONE|COMPRESS|"ROW STORE COMPRESS BASIC"|"ROW STORE COMPRESS ADVANCED"]
How can data be compressed during an import?
How can the storage method for LOB be changed during an import?
What are the options for TRANSFORM=LOB_STORAGE?
- DEFAULT - LOB segment is created with the default settings for the system
- BASICFILE - Use Basicfile LOB
- SECUREFILE - Use Securefile LOB
- NO_CHANGE - Same as the source database (DEFAULT SETTING)
What is the syntax for TRANSFORM=LOB_STORAGE?
What feature allows data to be loaded without the need to a control file?
SQL*Loader Express Mode
What minimum parameters are required to load data using SQL*Loader Express Mode?
- Username and table name
- ie sqlldr scott table=test
Which two files are generated by SQL*Loader Express Mode?
- A log file containing a SQL*Loader control file
- A sql script for creating an external table to load the data using INSERT
- AS SELECT
- These files are provided for the DBA convenience, for further work.
What column types are supported by SQL*Loader Express Mode?
NUMBER, CHARACTER or DATE
If the input datafile name is omitted, what file names does SQL*Loader Express Mode use?
- table_name.dat - to load the data
- table_name.log - log file
- table_name_%p.bad - bad file
- table_name_%p.log_xt - Oracle database log file
What is the default load method for SQL*Loader Express Mode?
External table load
What DEGREE_OF_PARALLELISM does SQL*Loader Express Mode use?
If data already exists in the table, what happens when SQL*Loader Express Mode attempts to load data into it?
The data is appended
In what format must the input data be in SQL*Loader Express Mode?
delimited character data
How are table column definitions defined in SQL*Loader Express Mode?
They are derived from the loading table column definitions
What is identity column type?
This column type is an auto increasing integer value which can be used for replacement of sequences
What is the syntax when creating an identity column?
- [ ALWAYS | BY DEFAULT [ ON NULL ] ]
- AS IDENTITY [ ( identity_options ) ]
CREATE TABLE identity_test_tab (
- id NUMBER GENERATED ALWAYS AS IDENTITY,
- description VARCHAR2(30)
What are the options for identity column?
- ALWAYS (Default) - forces the use of the identity. If an insert statement references the identity column, even to specify a NULL value, an error is produced.
- BY DEFAULT - allows you to use the identity if the column isn't referenced in the insert statement, but if the column is referenced, the specified value will be used in place of the identity. Null generates an error
- BY DEFAULT ON NULL - Same as BY DEFAULT, but NULL permitted
What is a specific enhancement regarding datafile names in SQL Loader?
- They can now contain wildcards
What does FIELD NAMES clause do in SQL Loader?
Defines the names and order of the fields in the first record in the data file
In SQL Loader, what would FOR ALL FIELDS DATE FORMAT "DD-MM-YYYY HH24:MI:SS" and FOR ALL FIELDS NULLIF = "NA" achieve?
- All DATE columns would have the defined format
- All NULL character fields would have "NA" loaded
In SQL Loader, what does ALL FIELDS OVERRIDE do?
- Only for external tables
- All the fields in the file are in the same order as the external table
- Only specify fields with special definitions
What would you like to do?
Home > Flashcards > Print Preview