Mixed Deck — All 1Z0-082 Topics Flashcards

When a table is dropped using the DROP TABLE command in Oracle, where does it go by default?

Answer: It is moved to the Recycle Bin

By default in Oracle 10g and later, dropped tables are moved to the Recycle Bin and can be recovered using the FLASHBACK TABLE ... TO BEFORE DROP statement.

A DBA needs to analyze historical undo generation rates and identify the longest-running queries over the past few days to properly size the undo tablespace. Which dynamic performance view is best suited for this task?

Answer: V$UNDOSTAT

`V$UNDOSTAT` collects statistics on undo space usage over 10-minute intervals. It is the primary tool for monitoring undo generation (`UNDOBLKS`), transaction counts (`TXNCOUNT`), and identifying the duration of the longest query (`MAXQUERYLEN`) within each interval, making it ideal for tuning `UNDO_RETENTION` and sizing the undo tablespace. [2, 9, 20]

Which Oracle feature is primarily used for monitoring database performance in real-time?

Answer: Automatic Workload Repository (AWR)

The Automatic Workload Repository (AWR) is an Oracle feature that automatically collects, processes, and maintains performance statistics for problem detection and self-tuning. It captures snapshots of database performance metrics at regular intervals, providing a historical record that can be analyzed to identify performance bottlenecks and trends. AWR is crucial for proactive performance management and diagnostics.

Which of the following is a mandatory background process in an Oracle Database?

Answer: Log Writer (LGWR)

The Log Writer (LGWR) is a mandatory background process in an Oracle Database. Its crucial role is to write the contents of the redo log buffer to the redo log files on disk. This ensures that all changes made to the database are permanently recorded, providing a vital mechanism for database recovery and maintaining data integrity.

When using Automatic Undo Management (AUM), what happens if the instance starts and the undo tablespace specified in the `UNDO_TABLESPACE` parameter is not available?

Answer: The instance will start without an undo tablespace and use the SYSTEM tablespace for undo records.

If the specified undo tablespace is unavailable, or if none is specified and no undo tablespace is available, the instance will still start. However, it will store undo records in the `SYSTEM` tablespace. This is a non-recommended configuration, and an alert will be written to the alert log to warn the DBA. [1, 5]

Which DDL command is used to modify an existing column's data type or add a new column to a table?

Answer: ALTER TABLE

The ALTER TABLE statement is used to modify existing table structure, including adding, modifying, or dropping columns and constraints.

Which data dictionary view lists all indexes owned by the current user?

Answer: USER_INDEXES

USER_INDEXES shows all indexes owned by the currently connected user, providing details such as index type, uniqueness, and associated table.

You are tasked with reclaiming fragmented free space within a table's segment to improve the performance of full table scans. The space is currently below the high water mark (HWM). Which Oracle feature must be used to accomplish this online?

Answer: Online Segment Shrink

Online Segment Shrink is the feature designed to reclaim fragmented space both above and below the high water mark (HWM) by compacting data and moving the HWM. This is an online operation. `ALTER TABLE ... DEALLOCATE UNUSED` only reclaims space above the HWM. Resumable Space Allocation is for suspending and resuming large operations, and while Data Pump can be used to rebuild objects, it is a much more involved process than the dedicated shrink operation.

What does the CONTAINERS() table function allow a DBA to do when connected to CDB$ROOT?

Answer: Query a specific table across all open PDBs simultaneously

The CONTAINERS() function enables cross-container queries, allowing a single SELECT to retrieve data from a table across all open PDBs.

A DBA issues the `SHUTDOWN IMMEDIATE` command. Which of the following best describes the actions the Oracle instance will take?

Answer: Terminates all active sessions, rolls back uncommitted transactions, and then shuts down.

The `SHUTDOWN IMMEDIATE` command does not wait for current user sessions to disconnect. It proceeds to terminate all active sessions, roll back any uncommitted transactions, and then performs a clean shutdown. No instance recovery is needed upon the next startup.

A DBA is creating a new locally managed tablespace and wants to simplify space management within segments by letting Oracle manage free and used space automatically using bitmaps. Which clause should be included in the `CREATE TABLESPACE` statement?

Answer: SEGMENT SPACE MANAGEMENT AUTO

Automatic Segment Space Management (ASSM) is enabled for a locally managed tablespace by specifying `SEGMENT SPACE MANAGEMENT AUTO`. This method uses bitmaps to track the status of blocks within a segment, which is more efficient and simplifies administration compared to the manual method that uses freelists. `EXTENT MANAGEMENT DICTIONARY` is an older method for managing extents at the data dictionary level, and `MANUAL` specifies the use of freelists, not automatic bitmap-based management.

What is the primary advantage of operating an Oracle database in ARCHIVELOG mode?

Answer: It enables online ('hot') backups and supports point-in-time recovery.

The main purpose of ARCHIVELOG mode is to save filled online redo log files before they are overwritten. This archive of all transactions enables two critical capabilities: performing backups while the database is open and running (online/hot backups) and recovering the database to any specific point in time since the last valid backup, thus preventing data loss from media failure.

Which clause must be included in the CREATE DATABASE statement to create a Container Database (CDB)?

Answer: ENABLE PLUGGABLE DATABASE

The ENABLE PLUGGABLE DATABASE clause in CREATE DATABASE instructs Oracle to create a CDB rather than a traditional non-CDB.

What is Unified Auditing introduced in Oracle 12c?

Answer: A single consolidated audit framework that replaces all previous auditing methods with one policy-based system

Unified Auditing consolidates standard auditing, FGA, SYS auditing, and RMAN auditing into a single framework stored in AUDSYS.AUD$UNIFIED and managed via audit policies.

What is the primary function of the System Global Area (SGA) in the Oracle Database architecture?

Answer: Containing shared memory structures for caching data and control information

The System Global Area (SGA) is a shared memory region allocated by Oracle when the instance starts. Its primary function is to store shared memory structures like the Buffer Cache, Shared Pool, and Redo Log Buffer. These structures are crucial for caching frequently accessed data, SQL statements, and transaction information to significantly improve database performance.

Which data dictionary view and column identify whether a user is a common user or a local user?

Answer: DBA_USERS — COMMON column

DBA_USERS includes a COMMON column that returns 'YES' for common users (defined in CDB$ROOT) and 'NO' for local users.

How do you configure a PDB to automatically open whenever the CDB instance starts?

Answer: ALTER PLUGGABLE DATABASE pdb_name SAVE STATE

ALTER PLUGGABLE DATABASE ... SAVE STATE persists the PDB's current open mode so it is automatically restored after the next CDB restart.

What does ADDM stand for in Oracle Database?

Answer: Automatic Database Diagnostic Monitor

ADDM stands for Automatic Database Diagnostic Monitor, the self-diagnosing engine that analyzes AWR data to identify and report database performance issues.

What is the primary purpose of a sequence object in Oracle Database?

Answer: To generate unique numeric values automatically

A sequence is a schema object that automatically generates unique sequential integers, commonly used to populate primary key columns.

What does 'unplugging' a PDB accomplish?

Answer: Closes the PDB and generates an XML manifest file describing its structure for future use

Unplugging closes the PDB and creates an XML metadata file so the PDB can later be plugged into another CDB.