Oracle 1Z0-082 Online Practice Questions and Exam Preparation

Oracle 1Z0-082 Online Questions & Answers

• Question 71:

  Examine this description of the TRANSACTIONS table:

  

  Which two SQL statements execute successfully? (Choose two.)

  A. SELECT customer_id AS "CUSTOMER-ID", transaction_date AS DATE, amount + 100 "DUES" FROM transactions;
  B. SELECT customer_id AS "CUSTOMER-ID", transaction_date AS "DATE", amount + 100 DUES FROM transactions;
  C. SELECT customer_id AS CUSTOMER-ID, transaction_date AS TRANS_DATE, amount + 100 "DUES AMOUNT" FROM transactions;
  D. SELECT customer_id CUSTID, transaction_date TRANS_DATE, amount + 100 DUES FROM transactions;
  E. SELECT customer_id AS `CUSTOMER-ID', transaction_date AS DATE, amount + 100 `DUES AMOUNT' FROM transactions;
  B. SELECT customer_id AS "CUSTOMER-ID", transaction_date AS "DATE", amount + 100 DUES FROM transactions;
  D. SELECT customer_id CUSTID, transaction_date TRANS_DATE, amount + 100 DUES FROM transactions;

• Question 72:

  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%)
  A. WHERE UPPER (SUBSTR (cust_name, INSTR (cust_name, ' ') + 1)) LIKE UPPER ('MC%')
  D. WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name, ' ') +1)) LIKE 'Mc%'

  ---

  Explanation/Reference:

• Question 73:

  Examine these statements executed in a single Oracle session:

  

  Which three statements are true? (Choose three.)

  A. The code for pen is 10.
  B. There is no row containing fountain pen.
  C. There is no row containing pen.
  D. The code for fountain pen is 3.
  E. The code for pen is l.
  F. There is no row containing pencil.
  A. The code for pen is 10.
  D. The code for fountain pen is 3.
  F. There is no row containing pencil.

  ---

  Explanation/Reference:

• Question 74:

  In the spfile of a single instance database, LOCAL_LISTENER is set to LISTENER_1.

  The TNSNAMES.ORA file in $ORACLE_HOME/network/admin in the database home contains:

  

  Which statement is true?

  A. Dynamic service registration cannot be used for this database instance
  B. The LREG process registers services dynamically with the LISTENER_1 listener
  C. LISTENER_1 must also be defined in the LISTENER.ORA file to enable dynamic service registration
  D. There are two listeners named LISTENER and LISTENER_1 running simultaneously using port 1521 on the same host as the database instances
  E. The definition for LISTENER_1 requires a CONNECT_DATA section to enable dynamic service registration
  B. The LREG process registers services dynamically with the LISTENER_1 listener

  ---

  Explanation/Reference:

  The listener forwards client requests to supported services. These services are dynamically registered with the listener. This dynamic registration feature is called service registration. The registration is performed by the Listener Registration (LREG) process. Dynamic service registration does not require any manual configuration in the listener.ora file.

  Reference: https://docs.oracle.com/database/121/NETAG/listenercfg.htm#NETAG292

• Question 75:

  Which three statements are true about the Oracle Data Dictionary? (Choose three.)

  A. Data dictionary views are created by joins of dictionary base tables and DBA-defined tables
  B. The data dictionary is created and maintained by the database administrator
  C. Views with the same name but different prefixes, such as CDB, DBA, ALL and USER, reference the same base tables from the data dictionary
  D. Base tables can be queried directly
  E. It is owned by the SYSTEM user
  F. Usernames of all users including database administrators are stored in the data dictionary
  C. Views with the same name but different prefixes, such as CDB, DBA, ALL and USER, reference the same base tables from the data dictionary
  D. Base tables can be queried directly
  F. Usernames of all users including database administrators are stored in the data dictionary

  ---

  Explanation/Reference:

  https://docs.oracle.com/cd/B28359_01/server.111/b28318/datadict.htm#CNCPT1210

• Question 76:

  The SCOTT/TIGER user exists in two databases, BOSTON_DB and DALLAS_DB, in two different locations.

  Each database has a tnsnames.ora file defining DALLAS_DB as a service name.

  Examine this command:

  CREATE DATABASE LINK dblink1 CONNECT TO scott IDENTIFIED BY tiger USING `dallas_db';

  How do you execute the command so that only SCOTT in BOSTON_DB can access the SCOTT schema in DALLAS_DB?

  A. as SCOTT in DALLAS_DB
  B. as SCOTT in BOSTON_DB
  C. as SCOTT in BOSTON_DB and SYS in DALLAS_DB
  D. as SYS in both the databases
  E. as SCOTT in both the databases
  B. as SCOTT in BOSTON_DB

  ---

  Explanation/Reference:

  https://docs.oracle.com/cd/B28359_01/server.111/b28310/ds_concepts002.htm#ADMIN12 Database links are either private or public. If they are private, then only the user who created the link has access; if they are public, then all database users have access. https://docs.oracle.com/database/121/SQLRF/statements_5006.htm#SQLRF01205

• Question 77:

  In one of your databases, you create a user, HR, and then execute this command:

  GRANT CREATE SESSION TO hr WITH ADMIN OPTION;

  Which three actions can HR perform? (Choose three.)

  A. Revoke the CREATE SESSION privilege from other users
  B. Revoke the CREATE SESSION privilege from user HR
  C. Log in to the database instance
  D. Grant the CREATE SESSION privilege with ADMIN OPTION to other users
  E. Execute DDL statements in the HR schema
  F. Execute DML statements in the HR schema
  A. Revoke the CREATE SESSION privilege from other users
  B. Revoke the CREATE SESSION privilege from user HR
  C. Log in to the database instance
  D. Grant the CREATE SESSION privilege with ADMIN OPTION to other users

  ---

  Explanation/Reference:

  Reference: https://docs.oracle.com/cd/B28359_01/network.111/b28531/authorization.htm#DBSEG224

• Question 78:

  Which three statements are true about time zones, date data types, and timestamp data types in an Oracle database? (Choose three.)

  A. The CURRENT_TIMESTAMP function returns data without time zone information
  B. A TIMESTAMP WITH LOCAL TIMEZONE data type column is stored in the database using the time zone of the session that inserted the row
  C. A TIMESTAMP data type column contains information about year, month, and day
  D. The DBTIMEZONE function can return an offset from Universal Coordinated Time (UTC)
  E. The SESSIONTIMEZONE function can return an offset from Universal Coordinated Time (UTC)
  B. A TIMESTAMP WITH LOCAL TIMEZONE data type column is stored in the database using the time zone of the session that inserted the row
  D. The DBTIMEZONE function can return an offset from Universal Coordinated Time (UTC)
  E. The SESSIONTIMEZONE function can return an offset from Universal Coordinated Time (UTC)

  ---

  Explanation/Reference:

  https://docs.oracle.com/database/121/NLSPG/ch4datetime.htm#GUID-CD2954CE-45E2-4938-A599-CCB96879510F

• Question 79:

  Which two statements are true about INTERVAL data types? (Choose two.)

  A. INTERVAL DAY TO SECOND columns support fractions of seconds.
  B. The YEAR field in an INTERVAL YEAR TO MONTH column must be a positive value.
  C. The value in an INTERVAL DAY TO SECOND column can be copied into an INTERVAL YEAR TO MONTH column.
  D. INTERVAL YEAR TO MONTH columns only support monthly intervals within a range of years.
  E. INTERVAL YEAR TO MONTH columns only support monthly intervals within a single year.
  F. INTERVAL YEAR TO MONTH support yearly intervals.
  A. INTERVAL DAY TO SECOND columns support fractions of seconds.
  F. INTERVAL YEAR TO MONTH support yearly intervals.

  ---

  Explanation/Reference:

• Question 80:

  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 two are types of analyses can be done using the DBMS_PRIVILEGE_CAPTURE package? (Choose two.)

  A. analysis of privileges that a user has on other schema's objects
  B. analysis of privileges that a user has on their own schema objects
  C. analysis of all privileges used by the sys user.
  D. analysis of privileges granted indirectly to a role that are then used by a user who has been granted that role
  E. analysis of privileges granted directly to a role that are then used by a user who has been granted that role
  D. analysis of privileges granted indirectly to a role that are then used by a user who has been granted that role
  E. analysis of privileges granted directly to a role that are then used by a user who has been granted that role

  ---

  Explanation/Reference: