IZ0-082 Question Sets
1ZO-082 : Question Set 2
1ZO-082 : Question Set 3
1ZO-082 : Question Set 4
1ZO-082 : Question Set 5
Which three functions are performed by dispatchers in a shared server configuration?
06/03/2020 – by Mod_GuideK1
Which three functions are performed by dispatchers in a shared server configuration? (Choose three.)
A. writing inbound request to the common request queue from all shared server connections
B. checking for outbound shared server responses on the common outbound response queue
C. receiving inbound requests from processes using shared server connections
D. sending each connection input request to the appropriate shared server input queue
E. broadcasting shared server session responses back to requesters on all connections
F. sending shared server session responses back to requesters on the appropriate connection
Correct Answer: ACD
-----
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:
Question 1.
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
SCorrect Answer: BCF
Question 2.
View the Exhibit and examine the structure of the PRODUCTS table.
Which two tasks require subqueries? (Choose two.)
A. Display the number of products whose PROD_LIST_PRICE is more than the average PROD_LIST_PRICE
B. Display suppliers whose PROD_LIST_PRICE is less than 1000
C. Display products whose PROD_MIN_PRICE is more than the average PROD_LIST_PRICE of all products, and whose status is orderable
D. Display the total number of products supplied by supplier 102 which have a product status of obsolete
E. Display the minimum PROD_LIST_PRICE for each product status
Correct Answer: AC
Question 3.
Which three statements are true about Oracle synonyms? (Choose three.)
A. A synonym cannot be created for a PL/SQL package
B. A synonym can be available to all users
C. A SEQUENCE can have a synonym
D. A synonym created by one user can refer to an object belonging to another user
E. Any user can drop a PUBLIC synonym
Correct Answer: BCD
Question 4.
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
Correct Answer: C,D,F
Question 5.
A script abc.sql must be executed to perform a job.
A database user HR, who is defined in this database, executes this command:
$ sqlplus hr/hr@orcl @abc.sql
What will happen upon execution?
A. The command succeeds and HR will be connected to the orcl and abc.sql databases
B. The command succeeds and HR will be connected to the orcl database instance, and the abc.sql script will be executed
C. The command fails because the script must refer to the full path name
D. The command fails and reports an error because @ is used twice
Correct Answer: B
Question 6.
spool 'select * from emp' to emp.dat
sqlldr u1/oracle TABLE=emp
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
Correct Answer: C, E
Question 7.
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)
Correct Answer: E,F
Question 8.
Your database instance was shut down normally and then started in NOMOUNT state. You then execute this command:
ALTER DATABASE MOUNT;
Which two actions are performed? (Choose two.)
A. The online redo logs are opened
B. The online data files are opened
C. The alert log records the execution details
D. The Oracle background processes are started
E. The initialization parameter file is read
F. The control file is read
Correct Answer: CF
A. The online redo logs are opened - OPEN
B. The online data files are opened - OPEN
C. The alert log records the execution details - MOUNT
D. The Oracle background processes are started - NOMOUNT
E. The initialization parameter file is read - NOMOUNT
F. The control file is read - MOUNT
Question 9.
Which statement is true about aggregate functions?
A. Aggregate functions can be nested to any number of levels
B. The AVG function implicitly converts NULLS to zero
C. Aggregate functions can be used in any clause of a SELECT statement
D. The MAX and MIN functions can be used on columns with character data types
Correct Answer: D
Question 10.
The CUSTOMERS table has a CUST_CREDIT_LIMIT column of data type NUMBER.
Which two queries execute successfully? (Choose two.)
A. SELECT NVL(cust_credit_limit * .15, ‘Not Available’) FROM customers;
B. SELECT NVL2(cust_credit_limit * .15, ‘Not Available’) FROM customers;
C. SELECT NVL(TO_CHAR(cust_credit_limit * .15), ‘Not Available’) FROM customers;
D. SELECT TO_CHAR(NVL(cust_credit_limit * .15, ‘Not Available’)) FROM customers;
E. SELECT NVL2(cust_credit_limit, TO_CHAR(cust_credit_limit * .15), ‘Not Available’) FROM customers;
Correct Answer: C,E
Question 11.
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;
Correct Answer: B,D
Question 12.
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
Correct Answer: A,B,E
Question 13.
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)
Correct Answer: C,D,E
Question 14.
Which two are true about a SQL statement using SET operators such as UNION? (Choose two.)
A. The data type group of each column returned by the second query must match the data type of the corresponding column returned by the first query.
B. The number, but not names, of columns must be identical for all SELECT statements in the query.
C. The data type of each column returned by the second query must exactly match the data type of the corresponding column returned by the first query.
D. The names and number of columns must be identical for all SELECT statements in the query.
E. The data type of each column returned by the second query must be implicitly convertible to the data type of the corresponding column returned by the first query.
Correct Answer: A,B
Question 15.
Table EMPLOYEES contains columns including EMPLOYEE_ID, JOB_ID and SALARY.
Only the EMPLOYEE_ID column is indexed.
Rows exist for employees 100 and 200.
Examine this statement:
update employees
set (job_id, salary) =
(select job_id, salary
from employees
where employee_id = 200)
where employee_id = 100;
Which two statements are true? (Choose two.)
A. Employee 100 will have SALARY set to the same value as the SALARY of employee 200
B. Employee 200 will have JOB_ID set to the same value as the JOB_ID of employee 100
C. Employee 200 will have SALARY set to the same value as the SALARY of employee 100
D. Employee 100 will have JOB_ID set to the same value as the JOB_ID of employee 200
E. Employees 100 and 200 will have the same JOB_ID as before the update command
F. Employees 100 and 200 will have the same SALARY as before the update command
Correct Answer: A,D
Question 16.
Which two statements are true about Enterprise Manager Database Express? (Choose two.)
A. It is available only when the database is open
B. It can be used to perform database recovery
C. The same port number can be used for Database Express configurations for databases on different hosts
D. It can be used to switch a database into ARCHIVELOGMODE
E. The same port number can be used for multiple Database Express configurations for multiple databases on the same host
Correct Answer: A,C
Question 17.
Which three statements are true about undo segments and the use of undo by transactions in an Oracle database instance? (Choose three)
A. An undo segment may be used by multiple transactions simultaneously
B. Undo segments can wrap around to the first extent when a transaction fills the last extend of the undo segment
C. Undo segments have a minimum of three extents
D. Undo segments can extend when a transaction fills the last extent of the undo segment
E. A single transaction may use multiple undo segments simultaneously
F. Undo segments must be stored in a BIGFILE tablespace
G. Undo segments must be stored in a SMALLFILE tablespace
Correct Answer:A,B,D
-------------------------------------------------------
A. An undo segment may be used by multiple transactions simultaneously
Correct - "Multiple active transactions can write concurrently to the same undo segment or to different segments."
B. Undo segments can wrap around to the first extent when a transaction fills the last extend of the undo segment
Correct - If the 1st extent is full then the next extent will be used and overwritten if it contains unactive undo.
C. Undo segments have a minimum of three extents
Wrong - No documentation about minimum number of extents.
D. Undo segments can extend when a transaction fills the last extent of the undo segment
Correct, AUTOEXTEND Option with Automatic UNDO management allows extension of UNDO.
E. A single transaction may use multiple undo segments simultaneously
Wrong - Documentation says a transaction may use multiple extents simultaneosly not segments.
https://docs.oracle.com/en/database/oracle/oracle-database/19/cncpt/logical-storage-structures.html#GUID-BB42D7D8-2C93-4F5B-B0C1-6361AEEF4B37
F. Undo segments must be stored in a BIGFILE tablespace
G. Undo segments must be stored in a SMALLFILE tablespace
Wrong - Can be either Bigfile or Smallfile, smallfile is the default.
A B D are correct I think
---------------------------------------------------
Question 18.
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
Correct Answer: B,E,F
https://www.dbexam.com/oracle/oracle-1z0-082-certification-sample-questions-and-answers
https://www.databasejournal.com/oracle/oracle-database-12c-new-privilege-management-features/
-----------------------------------------------------
Question 19
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
Correct Answer: BE
Question 20
Which three statements are true about the Automatic Diagnostic Repository (ADR)? (Choose three.)
A. It can be used for problem diagnosis of a database when that database's instance is down.
B. The ADR base is specified in the DIAGNIOSTIC_DEST database parameter.
C. It is held inside an Oracle database schema.
D. It is a file-based repository held outside any database.
E. It is only used for Oracle Database diagnostic information.
Correct Answer: A,B,D.
A - The Automatic Diagnostic Repository (ADR) is a directory structure that is stored outside of the database. It is therefore available for problem diagnosis when the database is down.
B - They are replaced by the initialization parameter DIAGNOSTIC_DEST, which identifies the location of the ADR.
D - The data is then stored in the Automatic Diagnostic Repository (ADR)—a file-based repository outside the database—where it can later be retrieved by incident number and analyzed.
https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/diagnosing-and-resolving-problems.html#GUID-43A0C671-CE6F-492F-9032-A010E96DC257
Question 20.A
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
Correct Answer: C,D -- PLEASE CHECK
Question 20.B
Which two statements are true about the Automatic Diagnostic Repository (ADR)? (Choose two.)
A. The ADR base is shared across multiple instances
B. SQL*Plus provides the ADRI script, which can be used to work with ADR
C. The ADR base keeps all diagnostic information in binary format
D. The ADR can be used for problem diagnosis even when the database instance is down
E. The ADR can be used for problem diagnosis only when the database is open
F. The ADR can be disabled by settting the DIAGNOSTIC_DEST parameter to null
Correct Answer: A,D
Question 21
Which three statements are true about the tools used to configure Oracle Net Services? (Choose three.)
A. The Oracle Net Configuration Assistant is only used when running the Oracle installer
B. Oracle Net Manager can be used to centrally configure listeners on any database server target
C. The lsnrctl utility requires a listener.ora file to exist before it is started
D. Oracle Net Manager can be used to locally configure naming methods on a database server
E. Enterprise Manager Cloud Control can be used to centrally configure listeners on any managed database server
F. Enterprise Manager Cloud Control can be used to centrally configure net service names for any database server target
Correct Answer: D,E,F
A - false; netca can be run independently on the Oracle Installer
B - false; Oracle Net Manager can be used to configure listeners on the local server or server host
C - false; there need not exist the listener.ora file before lsnrctl utility is started
D - true; see answer B
E - true; EM Cloud Control: All the systems and services including enterprise application systems, databases, hosts, middleware application servers, listeners, and so on, are easily managed from one central location.
F - true; see answer E
ref.
https://docs.oracle.com/en/database/oracle/oracle-database/21/netag/managing-oracle-net-services.html#GUID-1F145906-24CC-4A63-86FD-31776C022209
Question 22
Why does the DB01.vceguide.com service show unknown status?
A. The service DB01.vceguide.com is dynamically registered
B. The LOCAL_LISTENER database parameter is not set to a service name that refers to LISTENER_1
C. The service DB01.vceguide.com is statically registered
D. The listener is not listening on the default port 1521
E. The SID_LIST_LISTENER section is not contained in the LISTENER.ORA file
Correct Answer: C
A - false; if a service is registered dynamically, its status handler is either READY, RESTRICTED or BLOCKED. UNKNOWN handler refers to static service registration
B - false; LOCAL_LISTENER parameter can be set regardless static or dynamic registration is used
C - true; service is registered statically using SID_LIST keyword in the config file listener.ora
D - false; listener port does not affect status handler
E - false; when SID_LIST_LISTENER is contained in listener.ora file, statical service registration is configured.
Question 23.
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.
Correct Answer: BC
Question 24.
What is true about non-equijoin statement performance? (Choose two.)
A. Table aliases can improve performance
B. The BETWEEN condition always performs better than using the >= and <= conditions
C. The join syntax used makes no difference to performance
D. The BETWEEN condition always performs less well than using the >= and <= conditions
E. The Oracle join syntax performs better than the SQL:1999 compliant ANSI join syntax
Correct Answer: AC
Question 25.
Which query is valid?
A. SELECT dept_id, AVG(MAX(salary)) FROM employees GROUP BY dept_id;
B. SELECT dept_id, join_date, SUM(salary) FROM employees GROUP BY dept_id;
C. SELECT dept_id, join_date, SUM(salary) FROM employees GROUP BY dept_id, join_date;
D. SELECT dept_id, MAX(AVG(salary)) FROM employees GROUP BY dept_id;
Correct Answer: C
Thought for the day
No comments:
Post a Comment