1z0-082 Practice Exam Questions with Answers Oracle Database Administration I Certification

PRODUCT _ic can be assigned the PRIMARY KEY constraint.

PRODUCT _price contains the value zero by default if no value is assigned to it.

PRODUCT _price can be used in an arithmetic expression even if it has no value stored in it.

EXPIRY_ DATE contains the sysdate by default if no date is assigned to it.

PRODUCT_NAKE cannot contain duplicate values.

EXPIRY_ DATE cannot be used in arithmetic expressions.

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

It can be accessed only by the SYS user

It consists of a single row and single column of VARCHAR2 data type

It can display multiple rows but only a single column

It can be used to display only constants or pseudo columns

It can be accessed by any user who has the SELECT privilege in any schema

It can display multiple rows and columns

The primary key constraint will be enabled and deferred.

The foreign key constraint will be enabled and deferred.

The foreign key constraint will be disabled.

The foreign key constraint will be enabled and immediate.

The primary key constraint will be enabled and immediate.

SELECT REPLACE FROM customers;

SELECT REPLACE(SUBSTR(cust_last_name, -3), 'An', 'O') FROM customers;

SELECT REPLACE(REPLACE(cust_last_name, 'son', ''), 'An', 'O'> FROM customers;

SELECT INITCAP (REPLACE(TRIM('son' FROM cust_last_name), 'An', 'O*)) FROM customers;

The statement will fall if a row already exists in the sales table for product 23.

The sales table has five foreign keys.

A product can have a different unit price at different times.

A customer can exist in many countries.

The statement will execute successfully and a new row will be inserted into the sales table.

The statement will fail because a subquery may not be contained in a values clause.

writing inbound request to the common request queue from all shared server connections

checking for outbound shared server responses on the common outbound response queue

receiving inbound requests from processes using shared server connections

sending each connection input request to the appropriate shared server input queue

broadcasting shared server session responses back to requesters on all connections

sending shared server session responses back to requesters on the appropriate connection

Data dictionary views are created by joins of dictionary base tables and DBA-defined tables

The data dictionary is created and maintained by the database administrator

Views with the same name but different prefixes, such as CDB, DBA, ALL and USER, reference the same base tables from the data dictionary

Base tables can be queried directly

It is owned by the SYSTEM user

Usernames of all users including database administrators are stored in the data dictionary

A UNIQUE index can be altered to be non-unique

A SELECT statement can access one or more indices without accessing any tables

A table belonging to one user can have an index that belongs to a different user

An update to a table can result in updates to any or all of the table’s indexes

When a table is dropped and is moved to the RECYCLE BIN, all indexes built on that table are permanently dropped

An update to a table can result in no updates to any of the table’s indexes

Set OS_AUTHENT_PREFIX to OPS$

Have the OS administrator add KING to the OSDBA group

Grant DBA to KING

Unset REMOTE_LOGIN_PASSWORDFILE

Alter user KING to be IDENTIFIED EXTERNALLY

Duplicates are eliminated automatically by the UNION ALL operator

The number of columns selected in each SELECT statement must be identical

The names of columns selected in each SELECT statement must be identical

The output is sorted by the UNION ALL operator

NULLS are not ignored during duplicate checking

The second rollback command restores the row that was inserted.

The first rollback command leaves the table's 100 original rows locked.

The second rollback command rolls back the rollback to savepoint a command.

The first rollback command leaves the inserted row locked.

The first RollBack command restores the row that was inserted.

The second rollback command restores the 100 rows that were in the table originally.

The first rollback command restores the 100 rows that were in the table originally.

WHERE order_date > TO_CHAR(ADD_MONTHS(SYSDATE, 6), 'MON DD YYYY')

WHERE TO_CHAR(order_date, 'MON DD YYYY') = 'JAN 20 2019'

WHERE order_date > TO_DATE

WHERE order_date IN (TO_DATE ('Oct 21 2018', 'Mon DD YYYY'), TC_CHAR('Nov 21 2018', 'Mon DD YYYY'))

WHERE order date > TO DATE('JUL 10 2018', *MON DD YYYY')

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

It is not possible to shrink either indexes or Index Organized Tables (IOTs)

It always eliminates all migrated rows if any exist in the table

To shrink a table it must have a PRIMARY KEY constraint

To shrink a table it must have a UNIQUE KEY constraint

To shrink a table it must have row movement enabled

It must be in a tablespace that uses Automatic Segment Space Management (ASSM)

the first column in the first select of the compound query

the first varchab2 column in the first select of the compound query

the first number column in the first select of the compound query

the first number or varchar2 column in the last select of the compound query

the first column in the last select of the compound query

External table files can be used for other external tables in a different database

The ORACLE_LOADER access driver can be used to unload data from a database into an external table

The ORACLE_DATAPUMP access driver can be used to unload data from a database into an external table

They cannot be partitioned

The ORACLE_DATAPUMP access driver can be used to load data into a database from an external table

They support UPDATEs but not INSERTs and DELETEs

SELECT emp_id, ADD_MONTHS(hire_date, 6), NEXT_DAY(‘MONDAY’) FROM employees;

SELECT emp_id, NEXT_DAY(ADD_MONTHS(hire_date, 6), ‘MONDAY’) FROM employees;

SELECT emp_id, NEXT_DAY(MONTHS_BETWEEN(hire_date, SYSDATE), 6) FROM employees;

SELECT emp_id, NEXT_DAY(ADD_MONTHS(hire_date, 6), 1) FROM employees;

It generates a sqi script that it uses to load data from EMP.DAT TO EMP.

It overwrites the data for Alan and adds data for Curl and Bob

It appends data from EMP.DAT TO EMP.

It overwrites all data in emp with data from emp.dat.

It generates a log that contains control file entries, which can be used with normal SQL'Loader operations.

The high-water mark (HWM) of orders is adjusted.

Only queries are allowed on ORDERS while the shrink is executing.

Queries and DML statements are allowed on ORDERS while the shrink is executing.

The shrink operation causes rows to be moved to empty space starting from the beginning of the orders segment.

Dependent indexes become UNUSABLE.

The shrink operation causes rows to be moved to empty space starting toward the end of the orders segment.

A synonym cannot be created for a PL/SQL package

A synonym can be available to all users

A SEQUENCE can have a synonym

A synonym created by one user can refer to an object belonging to another user

Any user can drop a PUBLIC synonym

the number of hours for which an account is locked after the configured number of login attempts has been reached

the number of days for which an account may be inactive before it is locked

the maximum amount of CPU time allowed for a user’s sessions before their account is locked

the ability to prevent a password from ever being reused

the number of password changes required within a period of time before a password can be reused

the number of days for which an account is locked after the configured number of login attempts has been reached

the maximum number of sessions permitted for a user before the account is locked

SELECT TO_CHAR(SYSDATE,‘DD-MON-YYYY’) – ’01-JAN-2019’ FROM DUAL;

SELECT ROUND(SYSDATE – ’01-JAN-2019’) FROM DUAL;

SELECT ROUND(SYSDATE – TO_DATE(‘01/JANUARY/2019’)) FROM DUAL;

SELECT TO_DATE(SYSDATE, ‘DD/MONTH/YYYY’) – ‘01/JANUARY/2019’ FROM DUAL;

SELECT SYSDATE – TO_DATE(’01-JANUARY-2019’) FROM DUAL;

The Oracle join syntax supports creation of a Cartesian product of two tables

The Oracle join syntax performs better than the SQL:1999 compliant ANSI join syntax

The SQL:1999 compliant ANSI join syntax supports natural joins

The SQL:1999 compliant ANSI join syntax supports creation of a Cartesian product of two tables

The Oracle join syntax only supports right outer joins

The Oracle join syntax supports natural joins

The Oracle join syntax performs less well than the SQL:1999 compliant ANSI join syntax

Employee 100 will have SALARY set to the same value as the SALARY of employee 200

Employee 200 will have JOB_ID set to the same value as the JOB_ID of employee 100

Employee 200 will have SALARY set to the same value as the SALARY of employee 100

Employee 100 will have JOB_ID set to the same value as the JOB_ID of employee 200

Employees 100 and 200 will have the same JOB_ID as before the update command

Employees 100 and 200 will have the same SALARY as before the update command

The CURRENT_TIMESTAMP function returns data without time zone information

A TIMESTAMP WITH LOCAL TIMEZONE data type column is stored in the database using the time zone of the session that inserted the row

A TIMESTAMP data type column contains information about year, month, and day

The DBTIMEZONE function can return an offset from Universal Coordinated Time (UTC)

The SESSIONTIMEZONE function can return an offset from Universal Coordinated Time (UTC)

The ON clause must be used

The query must use two different aliases for the table

It must be an equijoin

It must be an inner join

The ON clause can be used

It can be an outer join

The value is changed only in the PFILE and takes effect at the next instance startup

The value is changed for the current instance and in the PFILE

It fails because the SCOPE clause is missing

Change is applied to the current instance, but does not persist after instance restart

Multiple INTERSECT operators are not possible in the same SQL statement

It processes NULLs in the selected columns

INTERSECT is of lower precedence than UNION or UNION ALL

It ignores NULLs

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.

The Oracle Net Configuration Assistant is only used when running the Oracle installer

Oracle Net Manager can be used to centrally configure listeners on any database server target

The lsnrctl utility requires a listener.ora file to exist before it is started

Oracle Net Manager can be used to locally configure naming methods on a database server

Enterprise Manager Cloud Control can be used to centrally configure listeners on any managed database server

Enterprise Manager Cloud Control can be used to centrally configure net service names for any database server target

Revoke the CREATE SESSION privilege from other users

Revoke the CREATE SESSION privilege from user HR

Log in to the database instance

Grant the CREATE SESSION privilege with ADMIN OPTION to other users

Execute DDL statements in the HR schema

Execute DML statements in the HR schema

It Is held Inside an Oracle database schema.

The ADR base is specified In the diagnostic_dest database parameter.

It is only used for Oracle Database diagnostic information.

It is a file-based repository held outside any database.

It can be used for problem diagnosis of a database when that database's instance is down.

SELECT * FROM customers WHERE city LIKE ‘D_%’;

SELECT * FROM customers WHERE city = ‘%D_’;

SELECT * FROM customers WHERE city LIKE ‘D_’;

SELECT * FROM customers WHERE city = ‘D_%’;

Add a data file to DATA

Drop other U1 objects in DATA

Increase U1’s quota sufficiently in DATA

Set DEFERRED_SEGMENT_CREATION to TRUE

Grant UNLIMITED TABLESPACE to U1

Set AUTOEXTEND ON for data files in DATA

All roles granted to a user are set on by default when the user logs in,

Object privileges may not be granted to roles.

The SET ROLE statement can disable one or more roles for a session.

Roles must be password protected.

Roles may be granted to roles.

The SET ROLE statement can enable one or more roles for a session.

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