1Z0-082 Online Practice Questions and Answers

Which two statements are true about substitution variables?

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

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

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

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

E. A substitution variable can be used only in a SELECT statement.

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

Which three statements are true about inner and outer joins? (Choose three.)

A. A full outer join returns matched and unmatched rows

B. An inner join returns matched rows

C. Outer joins can only be used between two tables per query

D. A full outer join must use Oracle syntax

E. Outer joins can be used when there are multiple join conditions on two tables

F. A left or right outer join returns only unmatched rows

You must create a tablespace of non-standard block size in a new file system and plan to use this command:

The standard block size is 8k but other non-standard block sizes will also be used. Which two are requirements for this command to succeed?

A. DB_32K_CACHE_SIZE must be less than DB_CACHE_SIZE.

B. DB_32K_CACHE_SIZE must be set to a value that can be accommodated in the SGA.

C. DB_32K_CACHE_SIZE should be set to a value greater than DB_CACHE_SIZE.

D. The /u02 file system must have at least 100g space for the datafile.

E. The operating system must use a 32k block size.

F. DB_CACHE_SIZE must be set to a size that is smaller than DB_32K_CACHE_SIZE.

Which three statements are true about dropping and unused columns in an Oracle database? (Choose three.)

A. A primary key column referenced by another column as a foreign key can be dropped if using the CASCADE option.

B. An UNUSED column's space is reclaimed automatically when the block containing that column is next queried.

C. An UNUSED column's space is reclaimed automatically when the row containing that column is next queried.

D. Partition key columns cannot be dropped.

E. A DROP COLUMN command can be rolled back

F. A column that is set to UNUSED still counts towards the limit of 1000 columns per table

Examine these commands:

Which two statements are true about the sqlldr execution? (Choose two.)

A. It overwrites data in EMP with data in EMP.DAT

B. It uses the database buffer cache to load data

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

D. It generates a sql script that it uses to load data from EMP.DAT to EMP

E. It appends data from EMP.DAT to EMP

Which two statements are true about views used for viewing tablespace and datafile information? (Choose two.)

A. Tablespace free space can be viewed in V$TABLESPACE

B. V$TABLESPACE displays information that is contained in the controlfile about tablespaces

C. V$TABLESPACE displays information about tablespaces contained in the data dictionary

D. Tablespace free space can be viewed in DBA_TABLESPACES

E. A datafile can be renamed when the database is in MOUNT state and the new file name is displayed when querying DBA_DATA_FILES after the database is opened

The ORCL database has RESUMABLE__TIMEOUT = 7200 and DEFERRED_SEGMENT_CREATION = FALSE

User U1 has a 1 MB quota in tablespace DATA.

U1 executes this command:

SQL> CREATE TABLE t1 AS

(SELECT object_name, sharing, created

FROM dba_objects);

U1 complains that the command is taking too long to execute.

In the alert log, the database administrator (DBA) finds this:

2017-03-06T12:15:17.183438+05:30

statement in resumable session `User U1(136), Session 1, Instance 1' was suspended due to ORA-01536: space quota exceeded for tablespace `DATA'

Which are three actions any one of which the DBA could take to resume the session? (Choose three.)

A. Add a data file to DATA

B. Drop other U1 objects in DATA

C. Increase U1's quota sufficiently in DATA

D. Set DEFERRED_SEGMENT_CREATION to TRUE

E. Grant UNLIMITED TABLESPACE to U1

F. Set AUTOEXTEND ON for data files in DATA

Your database instance is started with a PFILE.

Examine these parameters:

You want to increase the size of the buffer cache.

Free memory is available to increase the size of the buffer cache.

You execute the command:

SQL> ALTER SYSTEM SET DB_CACHE_SIZE=1024M;

What is the outcome?

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

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

C. It fails because the SCOPE clause is missing

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

Which two are true about shrinking a segment online? (Choose two.)

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

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

C. To shrink a table it must have a PRIMARY KEY constraint

D. To shrink a table it must have a UNIQUE KEY constraint

E. To shrink a table it must have row movement enabled

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

Examine the data in the CUST_NAME column of the CUSTOMERS table:

You want to display the CUST_NAME values where the last name starts with Mc or MC. Which two WHERE clauses give the required result? (Choose two.)

A. WHERE UPPER (SUBSTR (cust_name, INSTR (cust_name, ' ') + 1)) LIKE UPPER ('MC%')

B. WHERE SUBSTR(cust_name, INSTR(cust_name, ' ') + 1 ) LIKE 'Mc%' OR 'MC%'

C. WHERE SUBSTR(cust_name, INSTR(cust_name, ' ') +1) LIKE 'Mc%'

D. WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name, ' ') +1)) LIKE 'Mc%'

E. WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name, ' ') +1)) IN (MC%', 'Mc%)