1z0-082 Oracle Database Administration I Questions and Answers

A single database instance can support connections for multiple service names.

A service name is created by a listener.

A single connection string can refer to multiple database instances.

A connection string including a service name must be defined in the cnsnames.ora file.

A connection string must include the SID of a database Instance.

Different connection strings in the same tnsnames.ora file can contain the same service name, host and port parameters.

Aggregating functions and columns used in HAVING clauses must be specified in the SELECT list of a query

WHERE and HAVING clauses can be used in the same statement only if applied to different table columns

The HAVING clause can be used with aggregating functions in subqueries

The WHERE clause can be used to exclude rows before dividing them into groups

The WHERE clause can be used to exclude rows after dividing them into groups

ORDER BY “Last Name”

ORDER BY 2, 1

ORDER BY 2, cust_id

ORDER BY CUST_NO

ORDER BY “CUST_NO”

Statement 1 returns only one row of output.

Statement 2 returns only one row of output.

Both statements will return NULL if either UNIT_PRICE or QUANTITY contains NULL.

Both the statements give the same output.

Statement 2 may return multiple rows of output.

A substitution variable prefixed with & always prompts only once for a value in a session.

A substitution variable prefixed with to prompts only once for a value in a session unless it is set to undefined in the session

A substitution variable can be used only in a select statement.

A substitution variable used to prompt for a column name must be enclosed in single quotation marks.

A substitution variable can be used with any clause in a select statement.

A substitution variable used to prompt for a column name must be enclosed in double quotation marks.

Data Definition Language (DDL) statements

non-default database parameters

block corruption errors

deadlock errors

session logins and logouts

Configure a nonstandard block size for a new database

Register a new database with an available Enterprise Manager Management server

Change the standard block size of an existing database

Configure incremental backups for a new database

Enable flashback database for an existing database

SELECT prod_id FROM sales WHERE quantity_sold > 55000 GROUP BY prod_id HAVING COUNT(-) > 10;

SELECT prod_id FROM sales WHERE quantity_sold > 5S000 AND COUNT(-) > 10 GROUP BY prod_id HAVING COUNT(-) > 10;

SELECT COUNT|prod_id> FROM sales WHERE quantity_sold > 55000 GROUP BY prod_id;

SELECT COUNTlprod_id> FROM sales GROUP BY prod_id WHERE quantity_Sold > 55000;

SELECT prod_id FROM sales WHERE quantity_scld > 55000 AND COUNT(-) > 10 GROUP BY COUNT(-) > 10;

It is owned by the sys user.

Data dictionary base tables can be queried directly.

It is owned by the system user.

Data dictionary views are always created with queries that join two or more base tables.

All data dictionary view joins base tables to dynamic performance views.

Rolling back to a SAVEPOINT can undo a CREATE INDEX statement

Rolling back to a SAVEPOINT can undo a TRUNCATE statement

Only one SAVEPOINT may be issued in a transaction

A SAVEPOINT does not issue a COMMIT

Rolling back to a SAVEPOINT can undo a DELETE statement

Sequences can always have gaps.

A sequence can Issue duplicate values.

A sequence's unallocated cached values are lost if the instance shuts down.

Two or more tables cannot have keys generated from the same sequence.

A sequence can only be dropped by a DBA.

A sequence number that was allocated can be rolled back if a transaction fails.

Ensure that DB_RECOVERY_FILE_DEST and DB_CREATE_FILE_DEST each specify with at least 50 Mb of available space.

Specify a path in the DATAFILE clause of the command specifying a location with at least 100M of available space.

Ensure that DB_CREATE_FILE_DEST specifies a location with at least 100 Mb of available space.

Add the AUTOEXTEND ON clause with NEXT set to 100M.

Ensure that DB_RECOVERY_FILE_DEST and DB_CREATE_FILE_DEST each specify locations with at least 50 Mb of available space.

COLUMN STORE COMPRESS FOR QUERY LOW

ROW STORE COMPRESS BASIC

COLUMN STORE COMPRESS FOR ARCHIVE LOW

COLUMN STORE COMPRESS FOR ARCHIVE HIGH

ROW STORE COMPRESS ADVANCED

There can be only one undo tablespace created in a database.

An instance will crash if the active undo tablespace is lost.

An undo tablespace may be owned by only one instance.

undo segments are owned by SYSTEM.

undo segments are owned by SYSBACKUP.

A TRUNCATE command issued in a session causes all rows in a GLOBAL TEMPORARY TABLE for the issuing session to be deleted.

GLOBAL TEMPORARY TABLE rows inserted by a session are available to any other session whose user has been granted select on the table.

GLOBAL TEMPORARY TABLE space allocation occurs at session start.

Any GLOBAL TEMPORARY TABLE rows existing at session termination will be deleted.

A GLOBAL TEMPORARY TABLE’S definition is available to multiple sessions.

A DELETE command on a GLOBAL TEMPORARY TABLE cannot be rolled back.

INTERVAL year TO month columns support yearly intervals.

The value in an interval day to SECOND column can be copied into an interval year to month column.

INTERVAL day to second columns support fractions of seconds.

INTERVAL year to month columns only support monthly Intervals within a single year.

INTERVAL year to month columns only support monthly intervals within a range of years.

The year field in an interval year to month column must be a positive value.

SELECT promo_cost, promo_category FROM promotions ORDER BY by 1;

SELECT DISTINCT promo_cost || ’ in ‘ || DISTINCT promo_category FROM promotions ORDER BY 1;

SELECT DISTINCT promo_category || ‘ has ‘ || promo_cost AS COSTS FROM promotions ORDER BY 1;

SELECT promo_category, DISTINCT promo_cost FROM promotions ORDER BY 2;

SELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1;

The command succeeds and HR will be connected to the orcl and abc.sql databases

The command succeeds and HR will be connected to the orcl database instance, and the abc.sql script will be executed

The command fails because the script must refer to the full path name

The command fails and reports an error because @ is used twice