Oracle 1Z0-082 Online Practice Questions and Exam Preparation

Oracle 1Z0-082 Online Questions & Answers

• Question 41:

  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
  B. V$TABLESPACE displays information that is contained in the controlfile about 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

  ---

  Explanation/Reference:

  SQL> shutdown immediate;

  Database closed.

  E correct.

  Database dismounted.

  ORACLE instance shut down.

  SQL> startup mount;

  ORACLE instance started.

  Total System Global Area 1375731600 bytes

  Fixed Size 8896400 bytes

  Variable Size 838860800 bytes

  Database Buffers 520093696 bytes

  Redo Buffers 7880704 bytes

  Database mounted.

  SQL> alter database move datafile '/oradata/ORA19/testing1.dbf' to '/oradata/ORA19/testing01.dbf';

• Question 42:

  Which three statements are true regarding indexes? (Choose three.)

  A. A UNIQUE index can be altered to be non-unique
  B. A SELECT statement can access one or more indices without accessing any tables
  C. A table belonging to one user can have an index that belongs to a different user
  D. An update to a table can result in updates to any or all of the table's indexes
  E. When a table is dropped and is moved to the RECYCLE BIN, all indexes built on that table are permanently dropped
  F. An update to a table can result in no updates to any of the table's indexes
  B. A SELECT statement can access one or more indices without accessing any tables
  D. An update to a table can result in updates to any or all of the table's indexes
  F. An update to a table can result in no updates to any of the table's indexes

  ---

  Explanation/Reference:

  https://docs.oracle.com/cd/A87860_01/doc/server.817/a76956/indexes.htm https://docs.oracle.com/cd/B10501_01/server.920/a96521/indexes.htm

• Question 43:

  Which two statements are true regarding non-equijoins? (Choose two.)

  A. Table aliases must be used.
  B. The Oracle join syntax can be used.
  C. The SQL1999 compliant ANSI join syntax must be used.
  D. The USING clause can be used.
  E. The ON clause can be used.
  B. The Oracle join syntax can be used.
  E. The ON clause can be used.

• Question 44:

  Which three are benefits of using temp UNDO when performing DML on global temporary tables? (Choose three.)

  A. It reduces the amount of redo generated.
  B. It permits DML on Global temporary tables even if the database is opened read only.
  C. It reduces I/Os to the SYSAUX tablespace.
  D. It reduces I/Os to the SYSTEM tablespace.
  E. It reduces the amount of UNDO stored in the UNDO tablespace.
  A. It reduces the amount of redo generated.
  D. It reduces I/Os to the SYSTEM tablespace.
  E. It reduces the amount of UNDO stored in the UNDO tablespace.

• Question 45:

  Which three statements are true about Deferred Segment Creation in Oracle databases?

  A. It Is supported for Index Organized Tables (IOTs) contained in locally managed tablespaces.
  B. Sessions may dynamically switch back and forth from deferred to immediate segment creation.
  C. It Is the default behavior for tables and indexes.
  D. It is supported for SYS-owned tables contained in locally managed tablespaces.
  E. Indexes inherit the deferred or immediate segment creation attribute from their parent table
  B. Sessions may dynamically switch back and forth from deferred to immediate segment creation.
  C. It Is the default behavior for tables and indexes.
  E. Indexes inherit the deferred or immediate segment creation attribute from their parent table

• Question 46:

  The PRODUCT_INFORMATION table has a UNIT_PRICE column of data type NUMBER(8, 2).

  Evaluate this SQL statement:

  SELECT TO_CHAR(unit_price, '$9,999')

  FROM product_information;

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

  A. A row whose UNIT_PRICE column contains the value 10235.99 will be displayed as #######.
  B. A row whose UNIT_PRICE column contains the value 1023.99 will be displayed as $1,023.
  C. A row whose UNIT_PRICE column contains the value 10235.99 will be displayed as $1,023.
  D. A row whose UNIT_PRICE column contains the value 1023.99 will be displayed as $1,024.
  E. A row whose UNIT_PRICE column contains the value 10235.99 will be displayed as $1,0236.
  A. A row whose UNIT_PRICE column contains the value 10235.99 will be displayed as #######.
  D. A row whose UNIT_PRICE column contains the value 1023.99 will be displayed as $1,024.

• Question 47:

  Which two statements are true regarding the UNION and UNION ALL operators? (Choose two.)

  A. Duplicates are eliminated automatically by the UNION ALL operator
  B. The number of columns selected in each SELECT statement must be identical
  C. The names of columns selected in each SELECT statement must be identical
  D. The output is sorted by the UNION ALL operator
  E. NULLS are not ignored during duplicate checking
  B. The number of columns selected in each SELECT statement must be identical
  E. NULLS are not ignored during duplicate checking

• Question 48:

  In the ORCL database, UNDOTBS1 is the active undo tablespace with these properties:

  1.

  A size of 100 MB

  2.

  AUTOEXTEND is off

  3.

  UNDO_RETENTION is set to 15 minutes

  4.

  It has RETENTION GUARANTEE

  UNDOTBS1 fills with uncommitted undo 10 minutes after the database opens.

  What will happen when the next update is attempted by any transaction?

  A. It succeeds and the generated undo is stored in SYSTEM.
  B. It fails and returns the error message "ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1'".
  C. It succeeds and the least recently written undo block of UNDOTBS1 is overwritten by the generated undo.
  D. It succeeds and the generated undo is stored in SYSAUX.
  E. It succeeds and the least recently read undo block of UNDOTBS1 is overwritten by the generated undo.
  B. It fails and returns the error message "ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1'".

  ---

  Explanation/Reference:

  To guarantee the success of long-running queries or Oracle Flashback operations, you can enable retention guarantee. If retention guarantee is enabled, the specified minimum undo retention is guaranteed; the database never overwrites unexpired undo data even if it means that transactions fail due to lack of space in the undo tablespace. If retention guarantee is not enabled, the database can overwrite unexpired undo when space is low, thus lowering the undo retention for the system. This option is disabled by default.

• Question 49:

  Which two statements are true about User Authentication in an Oracle Database?

  A. Operating System authentication may be used for system-privileged administrative users.
  B. REMOTE_LOGIN_PASSWORDFILE must be set to exclusive to permit password changes for system-privileged administrative users.
  C. Password authentication must be used for system-privileged administrative users.
  D. Password File authentication must be used for system-privileged administrative users.
  E. Password File authentication is supported for any type of database user.
  A. Operating System authentication may be used for system-privileged administrative users.
  B. REMOTE_LOGIN_PASSWORDFILE must be set to exclusive to permit password changes for system-privileged administrative users.

  ---

  Explanation/Reference:

• Question 50:

  Which three statements are true about performing Data Manipulation Language (DML) operations on a view with no INSTEAD OF triggers defined? (Choose three.)

  A. Views cannot be used to query rows from an underlying table if the table has a PRIMARY KEY and the PRIMARY KEY columns are not referenced in the defining query of the view.
  B. The WITH CHECK clause has no effect when deleting rows from the underlying table through the view.
  C. Delete statements can always be done on a table through a view.
  D. Views cannot be used to add or modify rows in an underlying table if the defining query of the view contains the DISTINCT keyword.
  E. Views cannot be used to add rows to an underlying table if the table has columns with NOT NULL constraints lacking default values which are not referenced in the defining query of the view.
  F. Insert statements can always be done on a table through a view.
  B. The WITH CHECK clause has no effect when deleting rows from the underlying table through the view.
  D. Views cannot be used to add or modify rows in an underlying table if the defining query of the view contains the DISTINCT keyword.
  E. Views cannot be used to add rows to an underlying table if the table has columns with NOT NULL constraints lacking default values which are not referenced in the defining query of the view.

  ---

  Explanation/Reference: