1Z0-082: Oracle Database Administration IDemo

DBCA can configure a new custom database, including initialization parameters that support nonstandard block sizes, and it can register the database with Enterprise Manager Cloud Control through its Management Options workflow. The standard database block size cannot be changed after creation without recreating the database; incremental backups are RMAN operations.

A self join uses two separately aliased references to the same table so columns from each role can be distinguished. It can use an ON clause with ANSI join syntax, and it can be an outer join. It is not restricted to equijoins or inner joins.

Oracle sequences are not gap-free: generated values can be skipped, including after rollbacks. Cached values that have not been used can be lost during a system or instance failure. A sequence configured with CYCLE resumes at its minimum or maximum after reaching its limit, so it can reissue values.

INTERVAL DAY TO SECOND has a fractional-seconds precision for the SECOND field, so it supports fractional seconds. INTERVAL YEAR TO MONTH stores periods using YEAR and MONTH fields and supports intervals expressed in years. Oracle Database Data Types

A global temporary table’s definition is visible to multiple sessions with appropriate privileges, while its data remains private to the session that inserted it. A TRUNCATE issued by a session removes that session’s rows from the global temporary table. Oracle allocates table-segment space on the first DML operation, and DELETE remains rollback-capable.

When temporary undo is enabled, undo records for changes to temporary tables are stored in temporary undo segments allocated from the database’s temporary tablespaces rather than in the undo tablespace.

PMON performs process recovery for abnormally terminated processes and releases the resources they held. In Oracle Database versions in which PMON performs dynamic service registration, it registers the database instance’s service information with listeners known to the instance, including local and remote listeners. Checkpoint recording is a CKPT responsibility, while cleanup of unused temporary segments is a SMON responsibility.

UNION ALL combines the result sets from both tables without eliminating duplicate rows. It therefore displays every row from SALES1 and SALES2, including repeated sales data.

Both syntaxes can create a Cartesian product. ANSI SQL:1999 syntax supports this explicitly with CROSS JOIN, while Oracle’s traditional comma-separated table syntax produces a Cartesian product when no join condition is specified. Oracle documents that the traditional outer-join operator (+) supports left and right outer joins, and join syntax alone does not establish a universal performance advantage.

V$TABLESPACE reports tablespace names and numbers from the control file, whereas free extents are reported through DBA_FREE_SPACE. A datafile can be renamed while the database is mounted but closed by updating its control-file pointer; after opening the database, DBA_DATA_FILES displays the datafile name and location.

PMON performs process recovery when a process fails: it rolls back the failed process’s uncommitted transaction and frees resources the process held, including no-longer-needed locks and process state. Unused temporary segments are cleaned up by SMON, checkpoint information is recorded by CKPT, and current Oracle releases use LREG for listener registration.

Mounting an Oracle database opens and reads the control file identified by the CONTROL_FILES initialization parameter, associating the existing instance with the database. The initialization parameter file and background processes were handled when the instance entered NOMOUNT; data files and online redo logs are accessed only when the database is opened. Mount activity is written to the alert log.

Synonyms are database objects recorded with a dictionary object number. Oracle also allows the target of a synonym to be another synonym. Creating a public synonym requires the CREATE PUBLIC SYNONYM system privilege; a private synonym name need only be unique within its own schema; and an object contained in a package cannot be the target of a synonym.

SQL*Plus prompts only when a referenced substitution variable is undefined. A script argument defines positional substitution variable 1 for &1, and the DEFINE command can explicitly define variable 1 before execution. Both methods supply a value without an interactive prompt. Oracle SQL*Plus: Using Substitution Variables

Enterprise Manager Cloud Control provides centralized management for Oracle engineered systems and Oracle Fusion Middleware through its management plug-ins. It also integrates with My Oracle Support, allowing access to support resources and service-request capabilities from the Cloud Control console.

In SQL, the LIKE operator is used for pattern matching with wildcard characters: the underscore () represents exactly one character, and the percent sign (%) represents zero or more characters. Using LIKE 'D%' matches any CITY value that starts with 'D', followed by at least one required character (matched by ), and then zero or more additional characters (matched by %) — correctly filtering for cities beginning with D that have additional characters after it. The '=' operator (used in options B and D) does not interpret '' or '%' as wildcards; it performs an exact string comparison, so those queries would only match a literal string containing a percent sign or underscore character, not perform pattern matching. Option C ('D_') restricts results to exactly two-character city names starting with D, which is overly restrictive and does not allow for city names longer than two characters.

Private temporary tables store their definitions in memory only. Creating an index with the UNUSABLE attribute allocates no segment for that unusable index.

The LIKE '%AN%' condition matches character values containing AN anywhere in the city name. ORDER BY 1, LNAME DESC sorts by the first selected expression (city) in the default ascending order and then sorts the LNAME alias in descending order within each city. Oracle permits ordering by select-list position and alias.

Oracle permits ordinary unquoted column aliases such as CUSTID, TRANS_DATE, and DUES. Aliases containing special characters or spaces require double quotation marks, and a reserved word used as an alias must also be quoted. Single quotation marks create character literals, not quoted identifiers.

A primary key requires unique, non-null values; a NOT NULL numeric column such as PRODUCT_ID can be assigned that constraint when its values are unique. A numeric column whose value is NULL can still be used in an arithmetic expression, although the resulting expression evaluates to NULL.

ORACLE_DATAPUMP supports both unload operations, which write database data to an external table’s binary dump files, and load operations, which read those files into database tables. Its external-table files can be reused in another database when the external-table metadata is exactly the same. External tables are read-only, and Oracle supports partitioned external tables.

Single-row subqueries return at most one row and can provide values for conditions in WHERE and HAVING clauses. A SQL statement can contain multiple subquery blocks. Returning no rows does not itself cause the single-row-subquery error; returning more than one row does. Scalar subqueries are expressions and are not restricted to only one side of a comparison operator.

NVL with a numeric first expression requires its replacement expression to be compatible with a numeric return type; the nonnumeric literal 'Not Available' cannot be converted to NUMBER. Converting the numeric value with TO_CHAR before calling NVL makes both return alternatives character values. NVL2 requires three arguments and returns either its second or third expression; when both are character expressions, it executes successfully.

STOP_JOB=IMMEDIATE immediately stops the Data Pump Export job and detaches its clients. If the Data Pump control job table and dump-file set are not disturbed, the stopped job can later be attached to for monitoring and restarted with START_JOB; incomplete work may need to be redone after restart.