Oracle 1Z0-082 Online Practice Questions and Exam Preparation

Oracle 1Z0-082 Online Questions & Answers

• Question 101:

  The SALES_Q1 and USERS tablespaces exist in one of your databases and TEMP is a temporary tablespace.

  Segment creation is not deferred.

  You execute this command:

  

  Which three statements must be true so that the SALES user can create tables in SALES_Q1? (Choose three.)

  A. The sales user must have a quota on the TEMP tablespace
  B. The sales user must have a quota on the SALES_Q1 tablespace to hold the initial extends of all tables they plan to create in their schema
  C. The sales user must have been granted the CREATE SESSION privilege
  D. The sales user must have their quota on the users tablespace removed
  E. The sales user must have a quota on the SALES_Q1 tablespace to hold all the rows to be inserted into any table in their schema
  F. The sales user must have been granted the CREATE TABLE privilege
  B. The sales user must have a quota on the SALES_Q1 tablespace to hold the initial extends of all tables they plan to create in their schema
  C. The sales user must have been granted the CREATE SESSION privilege
  F. The sales user must have been granted the CREATE TABLE privilege

  ---

  Explanation/Reference:

  https://docs.oracle.com/database/121/DBSEG/users.htm#DBSEG99778 When you create a new user account, you should enable this user to access the database

• Question 102:

  Examine the description of the employees table:

  

  Which query requires explicit data type conversion?

  A. SELECT join_date FROM employees WHERE join_date > '10-02-2018';
  B. SELECT join_date + '20' FROM employees;
  C. SELECT salary + '120.50' FROM employees;
  D. SELECT SUBSTR(join_date, 1, 2) - 10 FROM employees;
  E. SELECT join_date || ' ' || salary FROM employees;
  A. SELECT join_date FROM employees WHERE join_date > '10-02-2018';

  ---

  Explanation/Reference:

• Question 103:

  You execute this command:

  

  Sufficient storage is available in filesystem /u01.

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

  A. AUTOEXTEND is possible for the datafile
  B. It must be bigger than the largest SMALLFILE tablespace
  C. Additional data files may not be added
  D. It will be a dictionary-managed tablespace by default
  E. It will always have a 32K blocksize
  A. AUTOEXTEND is possible for the datafile
  C. Additional data files may not be added

• Question 104:

  Which two statements are true about the Automatic Diagnostic Repository (ADR)? (Choose two.)

  A. The ADR base defaults to $ORACLE_HOME/rdbms/admin ifneither DIAGNOSTIC_DEST nor ORACLE_BASE is set
  B. The ADR base defaults to $ORACLE_HOME/dbs if the DIAGNOSTIC_DEST parameter and the ORACLE_BASE environment variable are not set
  C. It supports diagnostics for Automatic Storage Management (ASM)
  D. It supports diagnostics for Oracle Clusterware
  E. It is held inside an Oracle database schema
  C. It supports diagnostics for Automatic Storage Management (ASM)
  D. It supports diagnostics for Oracle Clusterware

  ---

  Explanation/Reference:

  The database, Oracle Automatic Storage Management (Oracle ASM), the listener, Oracle Clusterware, and other Oracle products or components store all diagnostic data in the ADR.

  https://docs.oracle.com/database/121/ADMIN/diag.htm#ADMIN11260

• Question 105:

  In your data center, Oracle Managed Files (OMF) is used for all databases.

  All tablespaces are smallfile tablespaces.

  SALES_Q1 is a permanent user-defined tablespace in the SALES database.

  Examine this command which is about to be issued by a DBA logged in to the SALES database:

  ALTER TABLESPACE sales_q1 ADD DATAFILE;

  Which are two actions, either one of which you could take to ensure that the command executes successfully? (Choose two.)

  A. Ensure that DB_RECOVERY_FILE_DEST and DB_CREATE_FILE_DEST each specify with at least 50 Mb of available space.
  B. Specify a path in the DATAFILE clause of the command specifying a location with at least 100M of available space.
  C. Ensure that DB_CREATE_FILE_DEST specifies a location with at least 100 Mb of available space.
  D. Add the AUTOEXTEND ON clause with NEXT set to 100M.
  E. Ensure that DB_RECOVERY_FILE_DEST and DB_CREATE_FILE_DEST each specify locations with at least 50 Mb of available space.
  B. Specify a path in the DATAFILE clause of the command specifying a location with at least 100M of available space.
  C. Ensure that DB_CREATE_FILE_DEST specifies a location with at least 100 Mb of available space.

  ---

  Explanation/Reference:

  https://oracle-base.com/articles/9i/oracle-managed-files

  -Files typically have a default size of 100M

  - if OMF is used you do not need to specify a path for the new created datafile BUT we have to "ensure that the command executes successfully"

• Question 106:

  Which three statements are true about views in an Oracle database? (Choose three.)

  A. Views can be updated without the need to re-grant privileges on the view
  B. Tables in the defining query of a view must always exist in order to create the view
  C. The WITH CHECK clause prevents certain rows from being displayed when querying the view
  D. Data Manipulation Language (DML) can always be used on views
  E. Inserting one or more rows using a view whose defining query contains a GROUP BY clause will cause an error
  F. Deleting one or more rows using a view whose defining query contains a GROUP BY clause will cause an error
  G. The WITH CHECK clause prevents certain rows from being updated or inserted
  A. Views can be updated without the need to re-grant privileges on the view
  F. Deleting one or more rows using a view whose defining query contains a GROUP BY clause will cause an error
  G. The WITH CHECK clause prevents certain rows from being updated or inserted

  ---

  Explanation/Reference:

• Question 107:

  Examine these SQL statements which execute successfully:

  

  Which two statements are true after execution? (Choose two.)

  A. The primary key constraint will be enabled and IMMEDIATE.
  B. The primary key constraint will be enabled and DEFERRED.
  C. The foreign key constraint will be enabled and DEFERRED.
  D. The foreign key constraint will be disabled.
  E. The foreign key constraint will be enabled and IMMEDIATE.
  A. The primary key constraint will be enabled and IMMEDIATE.
  D. The foreign key constraint will be disabled.

  ---

  Explanation/Reference:

• Question 108:

  You want to apply the principle of Least Privilege in all your live databases.

  One of your requirements is to revoke unnecessary privileges from all users who have them using Privilege Analysis.

  Which three types of analyses can be done using the DBMS_PRIVILEGE_CAPTURE package? (Choose three.)

  A. analysis of all privileges used by all users including administrative users in the database
  B. analysis of all privileges used by all users but excluding administrative users in the database
  C. analysis of privileges that a user has on their own schema objects that they did not use
  D. analysis of privileges that a user has on their own schema objects that they did use
  E. analysis of privileges granted directly to a role that are then used by a user who has been granted that role
  F. analysis of privileges granted indirectly to a role that are then used by a user who has been granted that role
  B. analysis of all privileges used by all users but excluding administrative users in the database
  E. analysis of privileges granted directly to a role that are then used by a user who has been granted that role
  F. analysis of privileges granted indirectly to a role that are then used by a user who has been granted that role

  ---

  Explanation/Reference:

• Question 109:

  View the Exhibit and examine the description of the tables. You execute this SQL statement:

  

  Which three statements are true?

  A. The statement will fall if a row already exists in the sales table for product 23.
  B. The sales table has five foreign keys.
  C. A product can have a different unit price at different times.
  D. A customer can exist in many countries.
  E. The statement will execute successfully and a new row will be inserted into the sales table.
  F. The statement will fail because a subquery may not be contained in a values clause.
  B. The sales table has five foreign keys.
  C. A product can have a different unit price at different times.
  E. The statement will execute successfully and a new row will be inserted into the sales table.

• Question 110:

  Which four statements are true regarding primary and foreign key constraints and the effect they can have on table data? (Choose four.)

  A. A table can have only one primary key but multiple foreign keys
  B. A table can have only one primary key and one foreign key
  C. The foreign key columns and parent table primary key columns must have the same names
  D. It is possible for child rows that have a foreign key to remain in the child table at the time the parent row is deleted
  E. It is possible for child rows that have a foreign key to be deleted automatically from the child table at the time the parent row is deleted
  F. Only the primary key can be defined at the column and table level
  G. Primary key and foreign key constraints can be defined at both the column and table level
  A. A table can have only one primary key but multiple foreign keys
  D. It is possible for child rows that have a foreign key to remain in the child table at the time the parent row is deleted
  E. It is possible for child rows that have a foreign key to be deleted automatically from the child table at the time the parent row is deleted
  G. Primary key and foreign key constraints can be defined at both the column and table level

  ---

  Explanation/Reference: