Oracle Exam Z0-082.
Oracle Exam Z0-082.
Oracle Exam Z0-082.
➱Vendor: Oracle
NEW QUESTION 1
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 granted directly to a role that are then used by a user who has been granted that role
D. analysis of privileges that a user has on their own schema objects that they did use
E. analysis of privileges that a user has on their own schema objects that they did not use
F. analysis of privileges granted indirectly to a role that are then used by a user who has been granted that role
Answer: A,E,F
Explanation:
https://docs.oracle.com/database/121/ARPLS/d_priv_prof.htm#ARPLS74328
NEW QUESTION 2
Which two statements are true about the Automatic Diagnostic Repository (ADR)? (Choose two.)
NEW QUESTION 3
Examine the description of the CUSTOMERS table:
For customers whose income level has a value, you want to display the first name and due amount as 5% of their
credit limit. Customers whose due amount is null should not be displayed.
Which query should be used?
A. SELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMOUNTFROM customersWHERE
cust_income_level <> NULLAND due_amount <> NULL;
B. SELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMOUNTFROM customersWHERE
cust_income_level != NULLAND cust_credit_level !=NULL;
C. SELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMOUNTFROM customersWHERE
cust_income_level IS NOT NULLAND cust_credit_limit IS NOT NULL;
D. SELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMOUNTFROM customersWHERE
cust_income_level != NULLAND due_amount !=NULL;
E. SELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMOUNTFROM customersWHERE
cust_incoms_level IS NOT NULLAND due_amount IS NOT NULL;
Answer: C
NEW QUESTION 4
Which two are true about instance recovery for a database that is open in read write mode?
(Choose two.)
A. The database opens after the roll forward phase of instance recovery has completed.
B. Only the CURRENT REDO log group is required to perform instance recovery.
C. No users can log on until instance recovery has completed.
D. It is always performed when opening a database.
E. It is guaranteed to complete within fast_start_mttr_target number of seconds.
F. It always occurs during instance startup performed immediately after a SHUTDOWN ABORT.
Answer: D,F
NEW QUESTION 5
Which three statements are true about table data storage in an Oracle Database? (Choose three.)
A. A table row piece can be chained across several database blocks
B. Index block free space is always contiguous in the middle of the block
C. Data block headers contain their own Data Block Address (DBA)
D. Data block free space is always contiguous in the middle of the block
E. Multiple row pieces from the same row may be stored in different database blocks
F. Multiple row pieces from the same row may be stored in the same block
Answer: A,D,E
Explanation:
Explanation/Reference:
https://books.google.com.pk/books?id=lNtVjrmbzckC&pg=PA267&lpg=PA267&dq=Multiple+row
+pieces+from+the+same+row+may+be+stored+in+different+database
+blocks&source=bl&ots=lEIhFe2Q52&sig=ACfU3U1TETJPy194pARxfIJ5ROaQdySCMQ&hl=en&sa=X&ved=2
ahUKEwivveLa97_mAhWOzaQKHahYAVEQ6AEwAnoECAoQAQ#v=onepage&q=Multiple%20row%20pieces
%20from%20the%20same%20row%20may%20be%20stored%20in%20different%20database%
20blocks&f=false
NEW QUESTION 6
Which three failures require intervention for recovery? (Choose three.)
A. a user error such as dropping the wrong table
B. media failure
C. network interface card (NIC) failure
D. user process failure
E. statement failure
Answer: A,B,D
NEW QUESTION 7
Which two statements are true about the DUAL table? (Choose two.)
A. It can display multiple rows and columns
B. It can be used to display only constants or pseudo columns
C. It can be accessed only by the SYS user
D. It can display multiple rows but only a single column
E. It can be accessed by any user who has the SELECT privilege in any schema
F. It consists of a single row and single column of VARCHAR2 data type
Answer: C,F
Explanation:
https://en.wikipedia.org/wiki/DUAL_table
NEW QUESTION 8
Which three statements are true about external tables in Oracle 18c and later releases? (Choose three.)
A. They support UPDATEs but not INSERTs and DELETEs
B. They cannot be partitioned
C. The ORACLE_DATAPUMP access driver can be used to load data into a database from an external table
D. The ORACLE_DATAPUMP access driver can be used to unload data from a database into an external table
E. The ORACLE_LOADER access driver can be used to unload data from a database into an external table
F. External table files can be used for other external tables in a different database
Answer: A,E,F
NEW QUESTION 9
Which two statements are true about the WHERE and HAVING clauses in a SELECT statement? (Choose two.)
A. Aggregating functions and columns used in HAVING clauses must be specified in the SELECT list of a query
B. The WHERE clause can be used to exclude rows after dividing them into groups
C. WHERE and HAVING clauses can be used in the same statement only if applied to different table columns
D. The WHERE clause can be used to exclude rows before dividing them into groups
E. The HAVING clause can be used with aggregating functions in subqueries
Answer: D,E
NEW QUESTION 10
Examine the description of the CUSTOMERS table:
For customers whose income level has a value, you want to display the first name and due amount as 5% of their
credit limit. Customers whose due amount is null should not be displayed.
Which query should be used?
A. SELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMOUNT
FROM customers
WHERE cust_income_level IS NOT NULL
AND due_amount IS NOT NULL;
B. SELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMOUNT
FROM customers
WHERE cust_income_level != NULL
AND due_amount != NULL;
C. SELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMOUNT
FROM customers
WHERE cust_income_level != NULL
AND cust_credit_level !=NULL;
D. SELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMOUNT
FROM customers
WHERE cust_income_level IS NOT NULL
AND cust_credit_limit IS NOT NULL;
E. SELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMOUNT
FROM customers
WHERE cust_income_level <> NULL
AND due_amount <> NULL;
Answer: D
NEW QUESTION 11
The SALES_Q1 and USERS tablespaces exist in one of your databases and TEMP is a temporary tablespace.
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 SALES_Q1 tablespace to hold all the rows to be inserted into any
table in their schema
B. The sales user must have a quota on the TEMP tablespace
C. The sales user must have their quota on the users tablespace removed
D. 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
E. The sales user must have been granted the CREATE SESSION privilege
F. The sales user must have been granted the CREATE TABLE privilege
Answer: C,D,F
NEW QUESTION 12
Which three statements are true about time zones, date data types, and timestamp data types in an Oracle
database? (Choose three.)
A. The SESSIONTIMEZONE function can return an offset from Universal Coordinated Time (UTC)
B. The CURRENT_TIMESTAMP function returns data without time zone information
C. 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. A TIMESTAMP data type column contains information about year, month, and day
E. The DBTIMEZONE function can return an offset from Universal Coordinated Time (UTC)
Answer: A,C,E
NEW QUESTION 13
Examine the description of the SALES1 table:
D. UNION
E. INTERSECT
Answer: C
NEW QUESTION 14
Examine the description of the SALES1 table:
NEW QUESTION 15
In the SALES database, DEFERRED_SEGMENT_CREATION is TRUE.
Examine this command:
SQL> CREATE TABLE T1(c1 INT PRIMARY KEY, c2 CLOB);
Which segment or segments, if any, are created as a result of executing the command?
A. no segments are created
B. T1, an index segment for the primary key, and a LOB segment only
C. T1 and an index segment created for the primary key only
D. T1, an index segment for the primary key, a LOB segment, and a lobindex segment
E. T1 only
Answer: E
Explanation:
Explanation/Reference:
NEW QUESTION 16
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:
NEW QUESTION 17
Which two statements are true about single row functions? (Choose two.)
A. CEIL : can be used for positive and negative numbers
B. CONCAT : can be used to combine any number of values
C. MOD : returns the quotient of a division operation
D. FLOOR : returns the smallest integer greater than or equal to a specified number
E. TRUNC : can be used with NUMBER and DATE values
Answer: C,E
Explanation:
https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/Single-Row-
Functions.html#GUID-B93F789D-B486-49FF-B0CD-0C6181C5D85C
NEW QUESTION 18
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. Log in to the database instance
B. Revoke the CREATE SESSION privilege from other users
C. Execute DDL statements in the HR schema
D. Execute DML statements in the HR schema
E. Revoke the CREATE SESSION privilege from user HR
F. Grant the CREATE SESSION privilege with ADMIN OPTION to other users
Answer: B,C,F
NEW QUESTION 19
Which three instance situations are possible with the Oracle Database server without multi-tenant? (Choose
three.)
A. one instance on one server associated with one database
B. one instance on one server associated with two or more databases on the same server
C. two or more instances on separate servers all associated with one database
D. one instance on one server associated with two or more databases on separate servers
E. one instance on one server not associated with any database
Answer: B,D,E
NEW QUESTION 20
Which three activities are recorded in the database alert log? (Choose three.)
A. deadlock errors
B. Data Definition Language (DDL) statements
C. non-default database parameters
D. session logins and logouts
E. block corruption errors
Answer: A,C,E
Explanation:
Reference:
https://docs.oracle.com/cd/B28359_01/server.111/b28310/monitoring001.htm#ADMIN11247
NEW QUESTION 21
Examine the description of the EMPLOYEES table:
NEW QUESTION 22
You need to calculate the number of days from 1st January 2019 until today.
Dates are stored in the default format of DD-MON-RR.
Which two queries give the required output? (Choose two.)
A. SELECT TO_DATE(SYSDATE, `DD/MONTH/YYYY') ?`01/JANUARY/2019' FROM DUAL;
B. SELECT TO_CHAR(SYSDATE, `DD-MON-YYYY') ?'01-JAN-2019' FROM DUAL;
C. SELECT SYSDATE ?TO_DATE('01-JANUARY-2019') FROM DUAL;
D. SELECT ROUND(SYSDATE ?'01-JAN-2019') FROM DUAL;
NEW QUESTION 23
Which two statements are true about the SET VERIFY ON command? (Choose two.)
A. It displays values for variables created by the DEFINE command
B. It can be used in SQL Developer and SQL*Plus
C. It displays values for variables used only in the WHERE clause of a query
D. It displays values for variables prefixed with &&
E. It can be used only in SQL*Plus
Answer: B,D
NEW QUESTION 24
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:
NEW QUESTION 25
Which two statements are true about date/time functions in a session where NLS_DATE_FORMAT is set to
DD-MON-YYYY HH24:MI:SS? (Choose two.)
A. SYSDATE can be used in expressions only if the default date format is DD-MON-RR
B. SYSDATE can be queried only from the DUAL table
C. CURRENT_TIMESTAMP returns the same date and time as SYSDATE with additional details of fractional
seconds
D. CURRENT_TIMESTAMP returns the same date as CURRENT_DATE
E. SYSDATE and CURRENT_DATE return the current date and time set for the operating system of the database
server
F. CURRENT_DATE returns the current date and time as per the session time zone
Answer: D,F
Explanation:
https://www.w3resource.com/oracle/datetime-functions/oracle-current_timestamp-function.php
NEW QUESTION 26
Which three are true about the Oracle Optimizer? (Choose three.)
A. It can optimize only SELECT statements.
B. It can re-optimize execution plans after previous executions detect suboptimal plans.
C. It will only generate SQL Plan Directives specific to a given SQL statement.
D. It generates execution plans for SQL statements based on relevant schema objects, system and session
parameters, and information found in the Data Dictionary.
E. It updates stale object statistics in the Data Dictionary.
F. It determines the optimal table join order and method.
Answer: A,D,F
NEW QUESTION 27
Which two statements are true about the Automatic Diagnostic Repository (ADR)? (Choose two.)
A. The ADR base defaults to $ORACLE_HOME/rdbms/admin if neither 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 is held inside an Oracle database schema
D. It supports diagnostics for Oracle Clusterware
E. It supports diagnostics for Automatic Storage Management (ASM)
Answer: A,C
Explanation:
https://docs.oracle.com/cd/E11882_01/install.112/e27508/admin.htm
NEW QUESTION 28
Which two tasks can you perform using DBCA for databases? (Choose two.)
A. Configure a nonstandard block size for a new database
B. Change the standard block size of an existing database
C. Configure incremental backups for a new database
D. Register a new database with an available Enterprise Manager Management server
E. Enable flashback database for an existing database
Answer: B,D
NEW QUESTION 29
Which three statements are true about Oracle synonyms? (Choose three.)
A. A synonym created by one user can refer to an object belonging to another user
B. A SEQUENCE can have a synonym
C. A synonym can be available to all users
D. A synonym cannot be created for a PL/SQL package
E. Any user can drop a PUBLIC synonym
Answer: A,B,C
Explanation:
Explanation/Reference: https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_7001.htm
NEW QUESTION 30
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. Log in to the database instance
B. Revoke the CREATE SESSION privilege from other users
C. Execute DDL statements in the HR schema
D. Execute DML statements in the HR schema
E. Revoke the CREATE SESSION privilege from user HR
F. Grant the CREATE SESSION privilege with ADMIN OPTION to other users
Answer: B,C,F
Explanation:
https://docs.oracle.com/cd/B28359_01/network.111/b28531/authorization.htm#DBSEG224
NEW QUESTION 31
You want to write a query that prompts for two column names and the WHERE condition each time it is executed
in a session but only prompts for the table name the first time it is executed.
The variables used in your query are never undefined in your session.
Which query can be used?
A. SELECT &col1, &col2FROM &&tableWHERE &condition;
B. SELECT &&col1, &&col2FROM &tableWHERE &&condition = &&cond;
C. SELECT &col1, &col2FROM "&table"WHERE &condition;
D. SELECT &&col1, &&col2FROM &tableWHERE &&condition;
E. SELECT '&&col1', '&&col2'FROM &tableWHERE '&&condition' = '&cond';
Answer: A
NEW QUESTION 32
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 alert log records the execution details
B. The Oracle background processes are started
C. The online redo logs are opened
D. The control file is read
E. The online data files are opened
F. The initialization parameter file is read
Answer: B,D
NEW QUESTION 33
In one of your databases, the user HR has the password HRMGR.
You want to connect to a database instance whose listener listens on port 1531 by using this statement:
CONNECT HR/HRMGR@orcl
No name server is used.
Which statement is true about ORCL?
A. It must resolve to a valid connect descriptor in the server's tnsnames.ora file
B. It must be the value of the SERVICE_NAMES parameter on the client side
C. It must be the name of the database to whose instance HR wishes to connect
D. It must resolve to a valid connect descriptor in the client's tnsnames.ora file
E. It must be the name of the server running the database to whose instance HR wishes to connect
Answer: E
NEW QUESTION 34
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:
NEW QUESTION 35
Which three statements are true about single-row functions? (Choose three.)
A. They return a single result row per table
B. They can accept only one argument
C. They can be nested to any level
D. The data type returned can be different from the data type of the argument
E. They can be used only in the WHERE clause of a SELECT statement
F. The argument can be a column name, variable, literal or an expression
Answer: A,D,F
NEW QUESTION 36
Which two statements are true about the results of using the INTERSECT operator in compound queries?
(Choose two.)
A. Reversing the order of the intersected tables can sometimes affect the output
B. The number of columns in each SELECT in the compound query can be different
C. INTERSECT ignores NULLs
D. INTERSECT returns rows common to both sides of the compound query
E. Column names in each SELECT in the compound query can be different
Answer: D,E
Explanation:
https://www.oracletutorial.com/oracle-basics/oracle-intersect/
NEW QUESTION 37
Which is true about security rules on a compute node?
A. The port used for HTTPS connections is enabled by default based on a predefined security rule.
B. A security rule can restrict IP addresses that can connect to a database instance on a compute node.
C. A compute node port can be enabled only if it is associated with an existing security rule.
D. A new database deployment on a compute node automatically enables a set of predefined security rules.
E. New security rules can be defined to use only the TCP protocol but multiple port numbers.
Answer: D
NEW QUESTION 38
Which two statements are true about the rules of precedence for operators? (Choose two.)
A. Arithmetic operators with equal precedence are evaluated from left to right within an expression
B. NULLS influence the precedence of operators in an expression
C. Multiple parentheses can be used to override the default precedence of operators in an expression
D. The + binary operator has the highest precedence in an expression in a SQL statement
E. The concatenation operator | | is always evaluated before addition and subtraction in an expression
Answer: C,D
NEW QUESTION 39
What two are benefits of Data Pump? (Choose two.)
A. It makes use of client system resources.
B. It can export data into sequential media, such as tapes.
C. It represents database metadata information as DDL statements in the dump file.
D. It can execute in parallel.
E. It can restart failed export jobs.
Answer: D,E
NEW QUESTION 40
You have been tasked to create a table for a banking application.
One of the columns must meet three requirements:
1. Be stored in a format supporting date arithmetic without using conversion functions
2. Store a loan period of up to 10 years
3. Be used for calculating interest for the number of days the loan remains unpaid Which data type should you
use?
A. TIMESTAMP WITH TIMEZONE
B. INTERVAL YEAR TO MONTH
C. TIMESTAMP
D. INTERVAL DAY TO SECOND
E. TIMESTAMP WITH LOCAL TIMEZONE
Answer: D
NEW QUESTION 41
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. Execute DDL statements in the HR schema
C. Log in to the database instance
D. Execute DML statements in the HR schema
E. Revoke the CREATE SESSION privilege from user HR
F. Grant the CREATE SESSION privilege with ADMIN OPTION to other users
Answer: A,C,F
Explanation:
Reference:
https://docs.oracle.com/cd/B28359_01/network.111/b28531/authorization.htm#DBSEG224
NEW QUESTION 42
Which three statements are true about Oracle synonyms? (Choose three.)
A. A synonym created by one user can refer to an object belonging to another user
B. A SEQUENCE can have a synonym
C. A synonym can be available to all users
D. A synonym cannot be created for a PL/SQL package
E. Any user can drop a PUBLIC synonym
Answer: A,B,C
NEW QUESTION 43
Which two are true about conventional path SQL load? (Choose two.)
A. It cannot load data into clustered tables.
B. It locks objects being processed.
C. It always generates redo.
D. It enforces referential integrity constraints.
E. It does not generate UNDO.
Answer: D,E
NEW QUESTION 44
Which three activities are recorded in the database alert log? (Choose three.)
A. deadlock errors
B. Data Definition Language (DDL) statements
C. non-default database parameters
D. session logins and logouts
E. block corruption errors
Answer: A,C,E
Explanation:
https://docs.oracle.com/cd/B28359_01/server.111/b28310/monitoring001.htm#ADMIN11247
NEW QUESTION 45
In the SALES database, DEFERRED_SEGMENT_CREATION is TRUE.
Examine this command:
SQL> CREATE TABLE T1(c1 INT PRIMARY KEY, c2 CLOB);
Which segment or segments, if any, are created as a result of executing the command?
A. no segments are created
B. T1, an index segment for the primary key, and a LOB segment only
C. T1 and an index segment created for the primary key only
D. T1, an index segment for the primary key, a LOB segment, and a lobindex segment
E. T1 only
Answer: E
NEW QUESTION 46
View the Exhibits and examine the structure of the COSTS and PROMOTIONS tables.
You want to display PROD_IDS whose promotion cost is less than the highest cost PROD_ID in a promotion time
interval.
Examine this SQL statement:
Exhibit 1.
Exhibit 2.
NEW QUESTION 47
Which two statements are true about the configuration and use of UNDO_RETENTION with GURANTEED
RETENTION? (Choose two.)
A. UNDO_RETENTION specifies for how long Oracle attempts to keep expired and unexpired UNDO.
B. Active UNDO is always retained.
C. UNDO_RETENTION specifies for how long Oracle attempts to keep unexpired UNDO.
D. Unexpired UNDO is always retained.
E. UNDO_RETENTION specifies how long all types of UNDO are retained.
Answer: A,D
NEW QUESTION 48
Examine the description of the PROMOTIONS table:
You want to display the unique promotion costs in each promotion category.
Which two queries can be used? (Choose two.)
A. SELECT DISTINCT promo_category || ' has ' || promo_cost AS COSTS FROM promotions ORDER BY 1;
B. SELECT promo_cost, promo_category FROM promotions ORDER BY by 1;
C. SELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1;
D. SELECT DISTINCT promo_cost || ' in ' || DISTINCT promo_category FROM promotions ORDER BY 1;
E. SELECT promo_category, DISTINCT promo_cost FROM promotions ORDER BY 2;
Answer: C,E
NEW QUESTION 49
Examine this command and some partial output:
NEW QUESTION 50
Examine this SQL statement:
SELECT cust_id, cust_last_name "Last Name"
FROM customers
WHERE country_id = 10
UNION
SELECT cust_id CUST_NO, cust_last_name
FROM customers
WHERE country_id = 30
Identify three ORDER BY clauses, any one of which can complete the query successfully. (Choose three.)
A. ORDER BY 2, 1
B. ORDER BY "CUST_NO"
C. ORDER BY CUST_NO
D. ORDER BY 2, cust_id
E. ORDER BY "Last Name"
Answer: A,C,D
NEW QUESTION 51
What is true about non-equijoin statement performance? (Choose two.)
A. The BETWEEN condition always performs less well than using the >= and <= conditions
B. Table aliases can improve performance
C. The BETWEEN condition always performs better than using the >= and <= conditions
D. The Oracle join syntax performs better than the SQL:1999 compliant ANSI join syntax
E. The join syntax used makes no difference to performance
Answer: B,D
Explanation:
https://stackoverflow.com/questions/58476906/1z0-071-oracle-exam-question-which-answers-is-correc
NEW QUESTION 52
You need to calculate the number of days from 1st January 2019 until today.
Dates are stored in the default format of DD-MON-RR.
Which two queries give the required output? (Choose two.)
A. SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY') - '01-JAN-2019' FROM DUAL;
B. SELECT SYSDATE - TO_DATE('01-JANUARY-2019') FROM DUAL;
C. SELECT TO_DATE(SYSDATE, 'DD/MONTH/YYYY') - '01/JANUARY/2019' FROM DUAL;
D. SELECT ROUND(SYSDATE - TO_DATE('01/JANUARY/2019')) FROM DUAL;
E. SELECT ROUND(SYSDATE - '01-JAN-2019') FROM DUAL;
Answer: A,E
NEW QUESTION 53
Which three instance situations are possible with the Oracle Database server without multi-tenant? (Choose
three.)
A. one instance on one server associated with two or more databases on the same server
B. one instance on one server associated with one database
C. one instance on one server not associated with any database
D. one instance on one server associated with two or more databases on separate servers
E. two or more instances on separate servers all associated with one database
Answer: B,C,E
NEW QUESTION 54
You execute this command:
CREATE SMALLFILE TABLESPACE sales
DATAFILE '/u01/app/oracle/sales01.dbf
SIZE 5G
SEGMENT SPACE MANAGEMENT AUTO;
Which two statements are true about the SALES tablespace? (Choose two.)
NEW QUESTION 55
Which two statements are true about the rules of precedence for operators? (Choose two.)
A. NULLS influence the precedence of operators in an expression
B. Multiple parentheses can be used to override the default precedence of operators in an expression
C. Arithmetic operators with equal precedence are evaluated from left to right within an expression
D. The + binary operator has the highest precedence in an expression in a SQL statement
E. The concatenation operator | | is always evaluated before addition and subtraction in an expression
Answer: B,C
Explanation:
Reference:
https://docs.oracle.com/cd/B19306_01/server.102/b14200/operators001.htm
https://docs.oracle.com/cd/A87860_01/doc/server.817/a85397/operator.htm Precedence is the order in which
Oracle evaluates different operators in the same expression. When evaluating an expression containing multiple
operators, Oracle evaluates operators with higher precedence before evaluating those with lower precedence.
Oracle evaluates operators with equal precedence from left to right within an expression.
NEW QUESTION 56
You want to use table compression suitable for OLTP that will:
Compress rows for all DML statements on that table
Minimize the overheads associated with compression
Which compression option is best suited for this?
A. ROW STORE COMPRESS BASIC
B. COLUMN STORE COMPRESS FOR ARCHIVE LOW
C. COLUMN STORE COMPRESS FOR QUERY LOW
D. COLUMN STORE COMPRESS FOR ARCHIVE HIGH
E. ROW STORE COMPRESS ADVANCED
Answer: E
Explanation:
Reference:
https://www.oracle.com/technetwork/database/options/compression/advanced-compression-wp-12c-1896128.pd
f
NEW QUESTION 57
Which two types of files are opened by a database instance in the NOMOUNT state?
A. the alert log
B. data files
C. online redo logs
D. control files
NEW QUESTION 58
You have been tasked to create a table for a banking application.
One of the columns must meet three requirements:
Be stored in a format supporting date arithmetic without using conversion functions Store a loan period of up to 10
years Be used for calculating interest for the number of days the loan remains unpaid Which data type should you
use?
A. TIMESTAMP WITH TIMEZONE
B. INTERVAL YEAR TO MONTH
C. TIMESTAMP
D. INTERVAL DAY TO SECOND
E. TIMESTAMP WITH LOCAL TIMEZONE
Answer: D
NEW QUESTION 59
Examine this description of the TRANSACTIONS table:
NEW QUESTION 60
View the Exhibit and examine the structure of the PRODUCTS table.
Which two tasks require subqueries? (Choose two.)
NEW QUESTION 61
You execute this command:
CREATE SMALLFILE TABLESPACE sales
DATAFILE '/u01/app/oracle/sales01.dbf
SIZE 5G
SEGMENT SPACE MANAGEMENT AUTO;
Which two actions must you take to ensure UNDOTBS01 is used as the default UNDO tablespace? (Choose
two.)
A. It uses the database default blocksize
B. Any data files added to the tablespace must have a size of 5 gigabytes
C. It is a locally managed tablespace
D. It must be smaller than the smallest BIGFILE tablespace
E. Free space is managed using freelists
Answer: A,C
NEW QUESTION 62
Which three statements are true about inner and outer joins? (Choose three.)
A. A full outer join returns matched and unmatched rows
B. Outer joins can be used when there are multiple join conditions on two tables
C. An inner join returns matched rows
NEW QUESTION 63
Evaluate these commands which execute successfully:
Which two statements are true about the ORD_ITEMS table and the ORD_SEQ sequence? (Choose two.)
A. Any user inserting rows into table ORD_ITEMS must have been granted access to sequence ORD_SEQ
B. Sequence ORD_SEQ is guaranteed not to generate duplicate numbers
C. Sequence ORD_SEQ cycles back to 1 after every 5000 numbers and can cycle 20 times
D. If sequence ORD_SEQ is dropped then the default value for column ORD_NO will be NULL for rows inserted
into ORD_ITEMS
E. Column ORD_NO gets the next number from sequence ORD_SEQ whenever a row is inserted into
ORD_ITEMS and no explicit value is given for ORD_NO
Answer: A,B
NEW QUESTION 64
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. 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
C. V$TABLESPACE displays information that is contained in the controlfile about tablespaces
D. Tablespace free space can be viewed in DBA_TABLESPACES
E. V$TABLESPACE displays information about tablespaces contained in the data dictionary
Answer: B,C
NEW QUESTION 65
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 fails and returns the error message "ORA-30036: unable to extend segment by 8 in undo tablespace
'UNDOTBS1'".
B. It succeeds and the least recently written undo block of UNDOTBS1 is overwritten by the generated undo.
C. It succeeds and the generated undo is stored in SYSTEM.
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.
Answer: A
Explanation:
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.
NEW QUESTION 66
Examine these commands:
Which two statements are true about the sqlldr execution? (Choose two.)
A. It generates a sql script that it uses to load data from EMP.DAT to EMP
B. It generates a log that contains control file entries, which can be used with normal SQL*Loader operations
NEW QUESTION 67
Examine these commands:
Which two statements are true about the sqlldr execution? (Choose two.)
A. It generates a log that contains control file entries, which can be used with normal SQL*Loader operations
B. It uses the database buffer cache to load data
C. It generates a sql script that it uses to load data from EMP.DAT to EMP
D. It overwrites data in EMP with data in EMP.DAT
E. It appends data from EMP.DAT to EMP
Answer: A,E
NEW QUESTION 68
Which three statements are true about single-row functions? (Choose three.)
A. They can accept only one argument
B. The data type returned can be different from the data type of the argument
C. They return a single result row per table
D. They can be used only in the WHERE clause of a SELECT statement
E. The argument can be a column name, variable, literal or an expression
F. They can be nested to any level
Answer: B,E,F
NEW QUESTION 69
The SALES_Q1 and USERS tablespaces exist in one of your databases and TEMP is a temporary tablespace.
Segment creation is not deferred.
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 SALES_Q1 tablespace to hold all the rows to be inserted into any
table in their schema
B. The sales user must have a quota on the TEMP tablespace
C. The sales user must have their quota on the users tablespace removed
D. 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
E. The sales user must have been granted the CREATE SESSION privilege
F. The sales user must have been granted the CREATE TABLE privilege
Answer: C,D,F
NEW QUESTION 70
Examine the description of the PROMOTIONS table:
You want to display the unique promotion costs in each promotion category.
Which two queries can be used? (Choose two.)
A. SELECT promo_cost, promo_category FROM promotions ORDER BY by 1;
B. SELECT DISTINCT promo_category || ` has ` || promo_cost AS COSTS FROM promotions ORDER BY 1;
C. SELECT DISTINCT promo_cost || ' in ` || DISTINCT promo_category FROM promotions ORDER BY 1;
D. SELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1;
E. SELECT promo_category, DISTINCT promo_cost FROM promotions ORDER BY 2;
Answer: D,E
NEW QUESTION 71
Which two statements are true about views used for viewing tablespace and datafile information? (Choose two.)
A. 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. Tablespace free space can be viewed in DBA_TABLESPACES
C. Tablespace free space can be viewed in VSTABLESPACE
D. VSTABLESPACE displays information about tablespaces contained in the data dictionary
NEW QUESTION 72
In one of your databases, user KING is:
1. Not a DBA user
2. An operating system (OS) user
Examine this command and its output:
What must you do so that KING is authenticated by the OS when connecting to the database instance?
A. Alter user KING to be IDENTIFIED EXTERNALLY
B. Have the OS administrator add KING to the OSDBA group
C. Set OS_AUTHENT_PREFIX to OPS$
D. Grant DBA to KING
E. Unset REMOTE_LOGIN_PASSWORDFILE
Answer: C
Explanation:
https://oracle-base.com/articles/misc/os-authentication
NEW QUESTION 73
A user complains about poor database performance. You suspect that the user's session is waiting for an event to
complete.
You want to verify this.
Which two views may reveal for what the user's session is currently waiting? (Choose two.)
A. V$SESSION_WAIT
B. V$SESSION_EVENT
C. V$SESSION_WAIT_CLASS
D. V$SYSTEM_EVENT
E. V$SESSION
Answer: A,B
NEW QUESTION 74
You execute this command:
NEW QUESTION 75
Which two are true about RETENTION GUARANTEE? (Choose two.)
A. It is a static parameter.
B. It prevents out-of-space errors.
C. It is a tablespace attribute.
D. It prevents FLASHBACK DATABASE operation failure.
E. It prevents "Snapshot too old" errors.
Answer: C,D
NEW QUESTION 76
You want to use table compression suitable for OLTP that will:
1. Compress rows for all DML statements on that table
2. Minimize the overheads associated with compression
Which compression option is best suited for this?
A. ROW STORE COMPRESS BASIC
B. COLUMN STORE COMPRESS FOR ARCHIVE LOW
C. COLUMN STORE COMPRESS FOR QUERY LOW
D. COLUMN STORE COMPRESS FOR ARCHIVE HIGH
E. ROW STORE COMPRESS ADVANCED
Answer: E
Explanation:
Explanation/Reference:
https://www.oracle.com/technetwork/database/options/compression/advanced-compression-wp-
12c-1896128.pdf
NEW QUESTION 77
Which three statements are true about inner and outer joins? (Choose three.)
A. Outer joins can be used when there are multiple join conditions on two tables
B. A full outer join returns matched and unmatched rows
C. An inner join returns matched rows
D. A full outer join must use Oracle syntax
E. Outer joins can only be used between two tables per query
F. A left or right outer join returns only unmatched rows
Answer: B,C,F
Explanation:
Explanation/Reference: https://www.studytonight.com/dbms/joining-in-sql.php
NEW QUESTION 78
Which three statements are true about undo segments and the use of undo by transactions in an Oracle database
NEW QUESTION 79
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. A column that is set to UNUSED still counts towards the limit of 1000 columns per table
C. Partition key columns cannot be dropped.
D. An UNUSED column's space is reclaimed automatically when the block containing that column is next queried.
E. A DROP COLUMN command can be rolled back
F. An UNUSED column's space is reclaimed automatically when the row containing that column is next queried.
Answer: A,B,D
NEW QUESTION 80
Which three statements are true concerning logical and physical database structures? (Choose three.)
A. Segments can span multiple tablespsaces
B. A segment's blocks can be of different sizes
C. A segment can span multiple data files in some tablespaces
D. All tablespaces may have one or more data files
E. A segment might have only one extent
F. The extents of a segment must always reside in the same datafile
G. A smallfile tablespace might be bigger than a bigfile tablespace
Answer: A,E,G
NEW QUESTION 81
You want to use table compression suitable for OLTP that will:
* Compress rows for all DML statements on that table
* Minimize the overheads associated with compression
Which compression option is best suited for this?
A. ROW STORE COMPRESS BASIC
B. COLUMN STORE COMPRESS FOR ARCHIVE LOW
C. COLUMN STORE COMPRESS FOR QUERY LOW
D. COLUMN STORE COMPRESS FOR ARCHIVE HIGH
E. ROW STORE COMPRESS ADVANCED
Answer: E
NEW QUESTION 82
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 NVL2(cust_credit_limit, TO_CHAR(cust_credit_limit * .15), `Not Available') FROM customers;
E. SELECT TO_CHAR(NVL(cust_credit_limit * .15, `Not Available')) FROM customers;
Answer: B,C
NEW QUESTION 83
Which three statements are true about table data storage in an Oracle Database? (Choose three.)
A. A table row piece can be chained across several database blocks
B. Index block free space is always contiguous in the middle of the block
C. Data block headers contain their own Data Block Address (DBA)
D. Data block free space is always contiguous in the middle of the block
E. Multiple row pieces from the same row may be stored in different database blocks
F. Multiple row pieces from the same row may be stored in the same block
Answer: A,C,E
Explanation:
Reference:
https://antognini.ch/2016/10/whats-the-difference-between-row-migration-and-row-chaining/
NEW QUESTION 84
Which two statements are true about trace files produced by the Oracle Database server? (Choose two.)
A. They can be written by background processes
B. Trace files are written to the Fast Recovery Area (FRA)
C. All trace files contain error information that require contacting Oracle Support
D. Trace file names are based on the database name concatenated with a sequential number
E. They can be written by server processes
Answer: A,E
Explanation:
Explanation/Reference: https://gerardnico.com/db/oracle/trace_file
NEW QUESTION 85
You want to use table compression suitable for OLTP that will:
1. Compress rows for all DML statements on that table
2. Minimize the overheads associated with compression
Which compression option is best suited for this?
A. ROW STORE COMPRESS BASIC
B. COLUMN STORE COMPRESS FOR ARCHIVE LOW
C. COLUMN STORE COMPRESS FOR QUERY LOW
D. COLUMN STORE COMPRESS FOR ARCHIVE HIGH
E. ROW STORE COMPRESS ADVANCED
Answer: E
Explanation:
https://www.oracle.com/technetwork/database/options/compression/advanced-compression- wp-
12c-1896128.pdf
NEW QUESTION 86
Examine this command:
NEW QUESTION 87
Which two are true about complete recovery? (Choose two.)
A. Incremental backups can be used to roll forward the database while performing it.
B. The database must have FLASHBACK enabled in order to perform it.
C. Only RMAN backupsets can be used to restore a database as part of it.
D. It is possible only when the database is in MOUNT state
E. Uncommitted transactions will be rolled back after it completes.
Answer: B,D
NEW QUESTION 88
Examine these commands:
Which two statements are true about the sqlldr execution? (Choose two.)
A. It generates a sql script that it uses to load data from EMP.DAT to EMP
B. It generates a log that contains control file entries, which can be used with normal SQL*Loader operations
C. It uses the database buffer cache to load data
D. It overwrites data in EMP with data in EMP.DAT
E. It appends data from EMP.DAT to EMP
Answer: A,C
NEW QUESTION 89
View the Exhibits and examine the structure of the COSTS and PROMOTIONS tables.
You want to display PROD_IDS whose promotion cost is less than the highest cost PROD_ID in a promotion time
interval.
Examine this SQL statement:
Exhibit 1.
Exhibit 2.
NEW QUESTION 90
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:
NEW QUESTION 91
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:
NEW QUESTION 92
Which three statements are true about advanced connection options supported by Oracle Net for connection to
Oracle Database instances? (Choose three.)
A. Connect Time Failover requires the connect string to have two or more listener addresses configured
B. Load Balancing can balance the number of connections to dispatchers when using a Shared Server
configuration
C. Source Routing requires the use of a name server
D. Connect Time Failover requires the use of Transparent Application Failover (TAF)
E. Load Balancing requires the use of a name server
F. Source Routing enables the use of Connection Manager (CMAN) which enables network traffic to be routed
through a firewall
Answer: A,B,F
Explanation:
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/rilin/shared-server-configuration-for-an-oracle-r
ac-database.html#GUID-2EFBA08D-FEEE-407F-BC42-E548DA946DF9
NEW QUESTION 93
Which two statements are true about the rules of precedence for operators? (Choose two.)
A. Arithmetic operators with equal precedence are evaluated from left to right within an expression
B. NULLS influence the precedence of operators in an expression
C. Multiple parentheses can be used to override the default precedence of operators in an expression
D. The + binary operator has the highest precedence in an expression in a SQL statement
E. The concatenation operator | | is always evaluated before addition and subtraction in an expression
Answer: C,D
Explanation:
https://docs.oracle.com/cd/B19306_01/server.102/b14200/operators001.htm
NEW QUESTION 94
What is true about non-equijoin statement performance? (Choose two.)
A. The BETWEEN condition always performs less well than using the >= and <= conditions
B. Table aliases can improve performance
C. The BETWEEN condition always performs better than using the >= and <= conditions
D. The Oracle join syntax performs better than the SQL:1999 compliant ANSI join syntax
E. The join syntax used makes no difference to performance
Answer: B,D
NEW QUESTION 95
Which two statements are true about Enterprise Manager Database Express? (Choose two.)
A. The same port number can be used for multiple Database Express configurations for multiple databases on the
same host
B. The same port number can be used for Database Express configurations for databases on different hosts
C. It can be used to switch a database into ARCHIVELOGMODE
D. It is available only when the database is open
E. It can be used to perform database recovery
Answer: A,C
Explanation:
https://docs.oracle.com/en/database/oracle/oracle-database/19/admqs/getting-started-with-
database-administration.html#GUID-EB851101-07BE-4038-BB9D-06E01CC7F5D5
NEW QUESTION 96
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 SALES_Q1 tablespace to hold all the rows to be inserted into any
table in their schema
B. The sales user must have a quota on the TEMP tablespace
C. The sales user must have their quota on the users tablespace removed
D. 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
E. The sales user must have been granted the CREATE SESSION privilege
F. The sales user must have been granted the CREATE TABLE privilege
Answer: C,D,F
NEW QUESTION 97
Which two statements are true about UNDO and REDO? (Choose two.)
A. DML modifies Oracle database objects and only generates REDO
B. The generation of REDO generates UNDO
C. The generation of UNDO generates REDO
D. DML modifies Oracle database objects and only generates UNDO
Answer: B,C
NEW QUESTION 98
Examine these commands:
Which two statements are true about the sqlldrexecution? (Choose two.)
A. It generates a sql script that it uses to load data from EMP.DAT to EMP
B. It generates a log that contains control file entries, which can be used with normal SQL*Loader operations
C. It uses the database buffer cache to load data
D. It overwrites data in EMP with data in EMP.DAT
E. It appends data from EMP.DAT to EMP
Answer: A,C
NEW QUESTION 99
Which two statements are true about single row functions? (Choose two.)
A. CEIL : can be used for positive and negative numbers
B. CONCAT : can be used to combine any number of values
C. MOD : returns the quotient of a division operation
D. FLOOR : returns the smallest integer greater than or equal to a specified number
E. TRUNC : can be used with NUMBER and DATE values
Answer: A,E
Explanation:
Reference:
https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/Single-Row-Functions.html#GUID-B93F789
D-B486-49FF-B0CD-0C6181C5D85C
You want to display the unique promotion costs in each promotion category.
Which two queries can be used? (Choose two.)
A. SELECT promo_cost, promo_category FROM promotions ORDER BY by 1;
B. SELECT DISTINCT promo_category || ' has ' || promo_cost AS COSTS FROM promotions ORDER BY 1;
C. SELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1;
D. SELECT DISTINCT promo_cost || ' in ' || DISTINCT promo_category FROM promotions ORDER BY 1;
E. SELECT promo_category, DISTINCT promo_cost FROM promotions ORDER BY 2;
Answer: B,C