NO.1 Which two statements are true about the Oracle Data Dictionary?
(A). It is owned by the sys user.
(B). Data dictionary base tables can be
queried directly.
(C). It is owned by the system user.
(D). Data dictionary views are always
created with queries that join two or more base tables.
(E). All data dictionary view joins base tables to dynamic performance views.
Answer: A,B
NO.2 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 Answer: B,E
NO.3 Which three statements are true about table data storage in an
Oracle Database? (Choose three.)
(A). Data block headers contain their own
Data Block Address (DBA)
(B). A table row piece can be chained
across several database blocks
(C). Multiple row pieces from the same row
may be stored in different database blocks
(D). Multiple row pieces from the same row
may be stored in the same block
(E). Data block free space is always
contiguous in the middle of the block
(F). Index block free space is always contiguous in the middle of the block
Answer: A,B,C
NO.4 Which two statements are true about segment types in an Oracle
Database?
(A). Table segments always have two or
more extents.
(B). Temporary segments are only stored in
a temporary tablespace.
(C). Undo segments are only stored in an
undo tablespace.
(D). Cluster segments may contain data
from multiple tables.
(E). Index segments always have two or
more extents.
Answer: B
NO.5 Which three Oracle database space management features will work
with both Dictionary and
Locally managed tablespaces? (Choose
three.)
(A). Oracle Managed Files (OMF)
(B). Online table segment shrink
(C). Online index segment shrink
(D). Automatic data file extension
(AUTOEXTEND)
(E). Capacity planning growth reports
based on historical data in the Automatic Workload Repository (AWR)
Answer: A,D,E
NO.6 Examine the description of the EMPLOYEES table:
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;
Answer: C
NO.7 Which three statements are true about connection strings and
service names used to connect to an Oracle database instance?
(A). A single database instance can
support connections for multiple service names.
(B). A service name is created by a
listener.
(C). A single connection string can refer
to multiple database instances.
(D). A connection string including a
service name must be defined in the cnsnames.ora file.
(E). A connection string must include the
SID of a database Instance.
(F). Different connection strings in the same tnsnames.ora file can contain the same service name, host and port parameters.
Answer: A,C,F
NO.8 Which three statements are true regarding single row subqueries?
(A). They must be placed on the right side
of the comparison operator or condition.
(B). They must be placed on the left side
of the comparison operator or condition.
(C). They can be used in the where clause.
(D). A SQL statement may have multiple
single row subquery blocks.
(E). They must return a row to prevent
errors in the SQL statement.
(F). They can be used in the having clause.
Answer: C,D,F
NO.9 Examine this command:
CREATE UNDO TABLESPACE undotbs01
DATAFILE 'undotbs_01.dbf'
SIZE 100M
AUTOEXTEND ON;
Which two actions must you take to ensure
UNDOTBS01 is used as the default UNDO tablespace?
(Choose two.)
(A). Add the SEGMENT SPACE MANAGEMENT AUTO
clause
(B). Set UNDO_TABLESPACE to UNDOTBS01
(C). Add the NOLOGGING clause
(D). Make certain that the database operates in automatic undo management mode (E). Add the ONLINE clause
Answer: B,D
NO.10 Examine this command and some partial output:
Why does the DB01.abc.com service show
unknown status?
(A). The service DB01.abc.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.abc.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 Answer: C
NO.11 Examine this command:
Which two statements are true?
(A). Compressed objects in
SALES01.DBF will be uncompressed In SALES02.DBF after the move. (B). DML may be
performed on tables with one or more extents in this data file during the
execution of this command.
(C). It overwrites any existing file with
the name SALES02.DBF.dbf in /u02 by default.
(D). The "to" clause containing
the new file name must be specified even if Oracle Managed Files (OMF) is used.
(E). Tables with one or more extents in
this data file may be queried during the execution of this command.
Answer: B,E
NO.12 Which is the default column or columns for sorting output from
compound queries using SET operators such as intersect In a SQL statement?
(A). the first column in the first select
of the compound query
(B). the first varchab2 column in the
first select of the compound query
(C). the first number column in the first
select of the compound query
(D). the first number or varchar2 column
in the last select of the compound query
(E). the first column in the last select
of the compound query
Answer: A
NO.13 Which two statements are true about the SET VERIFY ON command?
(Choose two.)
(A). It can be used only in SQL*Plus
(B). It displays values for variables used
only in the WHERE clause of a query
(C). It can be used in SQL Developer and
SQL*Plus
(D). It displays values for variables
created by the DEFINE command
(E). It displays values for variables
prefixed with &&
Answer: C,E
NO.14 Table ORDER_ITEMS contains columns ORDER_ID, UNIT_PRICE and
QUANTITY, of data type NUMBER.
Examine these SQL statements:
Statement 1:
SELECT MAX(unit_price * quantity)
"Maximum Order" FROM order_items; Statement 2:
SELECT MAX(unit_price * quantity)
"Maximum Order"
FROM order_items
GROUP BY order_id;
Which two statements are true?
(A). Statement 1 returns only one row of
output.
(B). Statement 2 returns only one row of
output.
(C). Both statements will return NULL if
either UNIT_PRICE or QUANTITY contains NULL.
(D). Both the statements give the same
output.
(E). Statement 2 may return multiple rows
of output.
Answer: A,E https://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqlj27781.html
The Expression can contain multiple column
references or expressions, but it cannot contain another aggregate or subquery.
It must evaluate to a built-in data type. You can therefore call methods that
evaluate to built-in data types. (For example, a method that returns a
java.lang.Integer or int evaluates to an INTEGER.) If an expression evaluates
to NULL, the aggregate skips that value.
NO.15 Which two statements are true regarding a SAVEPOINT? (Choose two.)
(A). Rolling back to a SAVEPOINT can undo
a CREATE INDEX statement
(B). Rolling back to a SAVEPOINT can undo
a TRUNCATE statement
(C). Only one SAVEPOINT may be issued in a
transaction
(D). A SAVEPOINT does not issue a COMMIT
(E). Rolling back to a SAVEPOINT can undo
a DELETE statement
Answer: D,E
NO.16 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). UNDO_RETENTION specifies how long all
types of UNDO are retained.
(C). Unexpired UNDO is always retained.
(D). Active UNDO is always retained.
(E). UNDO_RETENTION specifies for
how long Oracle attempts to keep unexpired UNDO. Answer: A,C
NO.17 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 be the value of the
SERVICE_NAMES parameter on the client side
(B). It must resolve to a valid connect
descriptor in the server's tnsnames.ora file
(C). It must resolve to a valid connect
descriptor in the client's tnsnames.ora file
(D). It must be the name of the database
to whose instance HR wishes to connect
(E). It must be the name of the server
running the database to whose instance HR wishes to connect Answer: C
NO.18 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 Answer: A,C,E,G
NO.19 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 IS
NOT NULLAND due_amount IS NOT 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
<> NULLAND due_amount <> 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_income_level IS NOT NULLAND
cust_credit_limit IS NOT NULL; Answer: E
NO.20 The INVOICE table has a QTY_SOLD column of data type NUMBER and an
INVOICE_DATE column of data type DATE.
NLS_DATE_FORMAT is set to DD-MON-RR.
Which two are true about data type
conversions involving these columns in query expressions?
(Choose two.)
(A). CONCAT (qty_sold, invoice_date) :
requires explicit conversion
(B). invoice_date = '15-march-2019' : uses
implicit conversion
(C). invoie_date > '01-02-2019' : uses
implicit conversion
(D). qty_sold BETWEEN '101' AND '110' :
uses implicit conversion
(E). qty_sold = '0554982' uses
implicit conversion Answer: B,D
NO.21 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). INTERVAL YEAR TO MONTH
(B). INTERVAL DAY TO SECOND
(C). TIMESTAMP WITH LOCAL TIMEZONE
(D). TIMESTAMP
(E). TIMESTAMP WITH TIMEZONE Answer: B
NO.22 You execute this command:
During the export operation, you detach
from the job by using CTRL+C and then execute this command:
Export> STOP_JOB=immediate
Are you sure you wish to stop the job
([yes]/no): yes
Which two statements are true about the
job? (Choose two.)
(A). You can no longer monitor it
(B). You can reattach to it and monitor it
(C). It is paused and can be resumed
(D). It continues to run in the background
(E). It terminates
Answer: B,C
NO.23 Which two statements are true about the PMON background process?
(Choose two.)
(A). It registers database services with
all local and remote listeners known to the database instance
(B). It frees resources held by abnormally
terminated processes
(C). It records checkpoint information in
the control file
(D). It frees unused temporary segments
(E). It kills sessions that exceed idle time
Answer: B,E
Reference:
* Performs process recovery when a
user process fails - Cleans up the database buffer cache - Free s resources
that are used by the user process * Monitors sessions for idle session timeout
NO.24 Which two tasks can you perform using DBCA for databases? (Choose
two.)
(A). Configure a nonstandard block size
for a new database
(B). Register a new database with an
available Enterprise Manager Management server
(C). Change the standard block size of an
existing database
(D). Configure incremental backups for a
new database
(E). Enable flashback database for an
existing database
Answer: A,B
NO.25 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
Answer: B,E
NO.26 Which three statements are true about GLOBAL TEMPORARY TABLES?
(Choose three.) (A). A TRUNCATE command issued in a session causes all rows in
a GLOBAL TEMPORARY TABLE for the issuing session to be deleted.
(B). GLOBAL TEMPORARY TABLE rows inserted
by a session are available to any other session whose user has been granted
select on the table.
(C). GLOBAL TEMPORARY TABLE space
allocation occurs at session start.
(D). Any GLOBAL TEMPORARY TABLE rows
existing at session termination will be deleted.
(E). A GLOBAL TEMPORARY TABLE'S definition
is available to multiple sessions.
(F). A DELETE command on a GLOBAL TEMPORARY
TABLE cannot be rolled back.
Answer: A,D,E
https://docs.oracle.com/javadb/10.8.3.0/ref/rrefdeclaretemptable.html
NO.27 Examine this description of the books table containing 100 rows:
Now examine this sequence of statements
Issued In a new session;
Examine this description of the books
table containing 100 rows:
Which three statements are true?
(A). The second rollback command restores
the row that was inserted.
(B). The first rollback command leaves the
table's 100 original rows locked.
(C). The second rollback command rolls
back the rollback to savepoint a command.
(D). The first rollback command leaves the
inserted row locked.
(E). The first RollBack command restores
the row that was inserted.
(F). The second rollback command restores
the 100 rows that were in the table originally.
(G). The first rollback command restores the 100 rows that were in the table originally.
Answer: A,C,E
NO.28 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
Answer: A,B,F
NO.29 Which three statements are true about external tables in Oracle 18c
and later releases?
(Choose three.)
(A). External table files can be used for
other external tables in a different database
(B). The ORACLE_LOADER access driver can
be used to unload data from a database into an external table
(C). The ORACLE_DATAPUMP access driver can
be used to unload data from a database into an external table
(D). They cannot be partitioned
(E). The ORACLE_DATAPUMP access driver can
be used to load data into a database from an external table
(F). They support UPDATEs but not INSERTs
and DELETEs
Answer: A,B,C
https://docs.oracle.com/en/database/oracle/oracle-database/18/admin/managingtables.html#GUID-F6948F0E-0557-4C42-9145-1897DE974CC3
NO.30 Which three statements are true about a self join? (Choose three.)
(A). The ON clause must be used
(B). The query must use two different
aliases for the table
(C). It must be an equijoin
(D). It must be an inner join
(E). The ON clause can be used
(F). It can be an outer join
Answer: B,E,F
NO.31 Which three statements are true about the Automatic Diagnostic
Repository (ADR)?
(A). It Is held Inside an Oracle database
schema.
(B). The ADR base is specified In the
diagnostic_dest database parameter.
(C). It is only used for Oracle Database
diagnostic information.
(D). It is a file-based repository held
outside any database.
(E). It can be used for problem diagnosis
of a database when that database's instance is down.
Answer: A,B,D
NO.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 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
Answer: C,F http://facedba.blogspot.com/2014/07/oracle-database-startup-stages-and.html
NO.33 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
use of Transparent Application Failover (TAF)
(B). Source Routing requires the use of a
name server
(C). Source Routing enables the use of
Connection Manager (CMAN) which enables network traffic to be routed through a
firewall
(D). Load Balancing can balance the number
of connections to dispatchers when using a Shared Server configuration
(E). Load Balancing requires the use of a
name server
(F). Connect Time Failover requires the
connect string to have two or more listener addresses configured
Answer: C,D,F
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/rilin/shared-server-configurationfor-an-oracle-rac-database.html#GUID-2EFBA08D-FEEE-407F-BC42-E548DA946DF9
NO.34 A database is configured to use automatic undo management with
temporary undo enabled. An UPDATE is executed on a temporary table. Where is
the UNDO stored?
(A). in the undo tablespace
(B). in the SYSAUX tablespace
(C). in the SGA
(D). in the PGA
(E). in the temporary tablespace
Answer: E http://www.dba-oracle.com/t_temp_undo_enabled.htm
NO.35 Which two statements are true about single row functions? (Choose
two.)
(A). MOD : returns the quotient of a
division operation
(B). FLOOR : returns the smallest integer
greater than or equal to a specified number
(C). TRUNC : can be used with NUMBER and
DATE values
(D). CONCAT : can be used to combine any
number of values
(E). CEIL : can be used for positive and
negative numbers
Answer: C,E
NO.36 An Oracle database session has an uncommitted transaction in
progress which updated 5000 rows in a table.
Which three situations does the
transaction complete thereby committing the updates?
(A). when a DBA issues a successful
SHUTDOWN TRANSACTIONAL statement and the user then issues a COMMIT
(B). when a CREATE INDEX statement is
executed successfully in the same session
(C). when a COMMIT statement is issued by
the same user from another session in the same database instance
(D). when the session logs out
successfully
(E). when a DBA issues a successful
SHUTDOWN IMMEDIATE statement and the user then issues a
COMMIT
(F). when a CREATE TABLE AS SELECT
statement is executed unsuccessfully in the same session Answer: A,B,D
NO.37 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
Answer: A,C,E
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
NO.38 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
Answer: B,C,D
NO.39 Which three statements are true about single-row functions? (Choose
three.)
(A). They can be used only in the WHERE
clause of a SELECT statement
(B). The argument can be a column name,
variable, literal or an expression
(C). The data type returned can be
different from the data type of the argument
(D). They can be nested to any level
(E). They can accept only one argument
(F). They return a single result row per
table
Answer: B,C,D
https://www.tutorialspoint.com/sql_certificate/using_single_row_functions_questions.htm
NO.40 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.)
Answer: B,E,F
https://docs.oracle.com/en/database/oracle/oracle-database/19/dbseg/performing-privilegeanalysis-find-privilege-use.html#GUID-AD683DDB-D345-4702-B1F4-37B79F276B66
NO.41 Which two statements are true?
(A). SALES1 has not NULL constraints on
any selected columns which had those constraints in the sales table.
(B). SALES1 has primary key and unique
constraints on any selected columns which had those constraints in the sales
table.
(C). SALES1 is created with 55,000 rows.
(D). SALES1 is created with no rows.
(E). SALES1 is created with 1 row.
Answer: A,C
NO.42 Examine the description of the SALES1 table:
SALES2 is a table with the same
description as SALES1.
Some sales data is duplicated in both
tables.
You want to display the rows from the
SALES1 table which are not present in the SALES2 table. Which set operator
generates the required output?
(A). INTERSECT
(B). UNION ALL
(C). UNION
(D). SUBTRACT
(E). MINUS
Answer: E
NO.43 Examine the description of the BOOKS_TRANSACTIONS table:
(A). WHERE borrowed_date = SYSDATE AND
transaction_type = 'RM*) OR member_id IN ('A101', 'A102');
(B). WHERE borrowed_date = SYSDATE AND
{transaction_type = 'RM* AND (raeraber_id = 'A101' OR member_id = 'A102));
(C). WHERE borrowed_date = SYSDATE AND
(transaction_ype = 'RM' AND .-neraber_id = 'A101' OR member_id = 'A102');
(D). WHERE borrowed_date = SYSDATE AND
(transaction_type = 'RM* OR member_id IN ('A101', 'A102'));
Answer: A,D
E, WHERE borrowed_date = SYSDATE AND
transaction_type = 'RM' OR member_id IN ('A101', 'A102');
NO.44 Which three statements are true about data block storage in an
Oracle Database?
(A). A block header contains a row
directory pointing to all rows in the block.
(B). An index block can contain row data.
(C). Row data is stored starting at the
end of the block.
(D). A data block header is of a fixed
length.
(E). A table block must always contain row
data.
Answer: A,D
NO.45 You currently have an active transaction in your session and have
been granted select access to vstransaction.
In which three situations will re-executing this query still return a row but with a different XID, indicating a new transaction has started?
(A). after successfully executing a commit
or rollback followed by a select statement
(B). after successfully executing a create
table as select statement followed by a select for update statement
(C). after successfully executing a commit
or rollback followed by a DML statement
(D). after successfully executing a create
table statement followed by a create index statement
(E). after successfully executing a DML
statement following a failed DML statement
(F). after successfully executing a truncate statement followed by a DML statement
Answer: B,C,F
NO.46 Examine the description of the customers table:
You need to display last names and credit
limits of all customers whose last name starts with A or B in lower or upper
case, and whose credit limit Is below 1000.
Examine this partial query:
SELECT cust_last_name,
cust_credit_limit FROM customers Which two where conditions give the required
result?
A)
C)
D)
E)
(A). Option A
(B). Option B (C). Option C
(D). Option D
(E). Option E
Answer: C,E
NO.47 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.)
(A). It must be smaller than the smallest
BIGFILE tablespace
(B). Free space is managed using freelists
(C). Any data files added to the
tablespace must have a size of 5 gigabytes
(D). It uses the database default
blocksize
(E). It is a locally managed tablespace
Answer: D,E
NO.48 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;
Answer: B,D
NO.49 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:
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 Answer: A,D
NO.50 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
Answer: B
NO.51 Your database instance is started with an SPFILE.
A PFILE is also available. You
execute this command: ALTER SYSTEM SET DB_CACHE_SIZE=100K; Where Is the value
changed?
(A). in the SPFILE, SPFILE, and memory
(B). in the SPFILE and in memory
(C). only In the SPFILE
(D). in the SPFILE and SPFILEc
(E). only in memory
Answer: D
NO.52 Which two statements are true regarding Oracle database space
management within blocks managed by Automatic Segment Space Management (ASSM)?
(Choose two.)
(A). PCTFREE defaults to 10% for all
blocks in all segments for all compression methods
(B). ASSM assigns blocks to one of four
fullness categories based on what percentage of the block is allocated for rows
(C). Update operations always attempt to
find blocks with free space appropriate to the length of the row being updated
(D). Insert operations always attempt to
find blocks with free space appropriate to the length of the row being inserted
(E). A block will always be
eligible for inserts if the row is short enough to fit into the block Answer: C,E
NO.53 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
Answer: B,D,F
NO.54 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
Answer: A,C
NO.55 In the promotions table, the PROMO_BEGIN_DATE column is of data
type date and the default date format Is DD-MON-RR. h two statements are true
about expressions using PROMO_BEGIN_DATE contained In a query? (A).
to_numberipromo_begin_date) - s will return a number.
(B). PROMO_ BEGIN_DATE - sysdate will
return a number.
(C). PROMO_ BEGIN_DATE - sysdate will
return an error.
(D). PROMO_ BEGIN_DATE - 5 will return a
date.
(E). TO_date(PROMO_ BEGIN_DATE * 5| will
return a date.
Answer: C,D
NO.56 Which two statements are true about the results of using the
INTERSECT operator in compound queries? (Choose two.)
(A). Column names in each SELECT in the
compound query can be different
(B). The number of columns in each SELECT
in the compound query can be different
(C). Reversing the order of the
intersected tables can sometimes affect the output
(D). INTERSECT returns rows common
to both sides of the compound query (E). INTERSECT ignores NULLs
Answer: A,D https://www.oracletutorial.com/oracle-basics/oracle-intersect/
NO.57 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). T1, an index segment for the
primary key, a LOB segment, and a lobindex segment (B). no segments are created
(C). T1 only
(D). T1 and an index segment created for
the primary key only
(E). T1, an index segment for the primary
key, and a LOB segment only
Answer: B
NO.58 Which two statements are true about the rules of precedence for
operators? (Choose two.) (A). The concatenation operator | | is always
evaluated before addition and subtraction in an expression
(B). NULLS influence the precedence of
operators in an expression
(C). The + binary operator has the highest
precedence in an expression in a SQL statement
(D). Arithmetic operators with equal
precedence are evaluated from left to right within an expression
(E). Multiple parentheses can be used to override the default precedence of operators in an expression
Answer: D,E
Reference:
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.
NO.59 Which two statements are true about Enterprise Manager (EM)
Express?
(A). You can use a single instance of EM
Express to manage multiple database running on the same server.
(B). EM Express uses a separate repository
database to store target database metadata.
(C). By default, EM express is available
for a database after database creation using DBCA.
(D). You can shut down a database instance
using EM Express.
(E). You cannot start up a database instance using EM Express.
Answer: C,D
NO.60 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 Answer: D
NO.61 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
Answer: C,D,F
NO.62 Examine this command:
Which two statements are true? (Choose
two.)
(A). DML may be performed on tables with
one or more extents in this data file during the execution of this command.
(B). The tablespace containing SALES1.DBF
must be altered READ ONLY before executing the command.
(C). The tablespace containing SALES1.DBF
must be altered OFFLINE before executing the command. (D). If Oracle Managed
Files (OMF) is used, then the file is renamed but moved to DB_CREATE_FILE_DEST.
(E). The file is renamed and stored in the same location
Answer: A,E
NO.63 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 ROUND(SYSDATE - '01-JAN-2019')
FROM DUAL;
(C). SELECT ROUND(SYSDATE -
TO_DATE('01/JANUARY/2019')) FROM DUAL;
(D). SELECT TO_DATE(SYSDATE, 'DD/MONTH/YYYY') - '01/JANUARY/2019' FROM DUAL; (E). SELECT SYSDATE - TO_DATE('01-JANUARY-2019') FROM DUAL;
Answer: C,E
NO.64 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). An UNUSED column's space is reclaimed
automatically when the block containing that column is next queried.
(C). An UNUSED column's space is reclaimed
automatically when the row containing that column is next queried.
(D). Partition key columns cannot be
dropped.
(E). A DROP COLUMN command can be rolled
back
(F). A column that is set to UNUSED still
counts towards the limit of 1000 columns per table Answer: A,D,F
NO.65 Which three activities are recorded in the database alert log?
(Choose three.)
(A). Data Definition Language (DDL)
statements
(B). non-default database parameters
(C). block corruption errors
(D). deadlock errors
(E). session logins and logouts
Answer: B,C,D
NO.66 You must create a tablespace of non-standard block size in a new
file system and plan to use this command:
The standard block size is 8k but other
non-standard block sizes will also be used. Which two are requirements for this
command to succeed?
(A). d3_32k_cache_size should be set to a
value greater than db_cache_size.
(B). db_32k_cache_size must be set to a
value that can be accommodated in the SGA.
(C). db_32r_cache_si2e must be less than
db_ca=he_size.
(D). db_cache_s:ze must be set to a size
that is smaller than db_32k_cache_size.
(E). The /u02 file system must have at
least lOOg space for the datafile.
(F). The operating system must use a 32k
block size.
Answer: C,D
NO.67 While one of your databases was in mount state, the datafiles were
renamed because they had been moved to a new file system. The database was then
opened.
Which two statements are true?
(A). DBA_DATA_FILES displays the original
name for the data files.
(B). V$DATAFILE displays the new names for
the data files.
(C). DBA_DATA _FILES displays both the new
name and the old name for the data files.
(D). DBA_DATA_FILES displays the new name
for the data files.
(E). DBA_DATA_FILES must be
resynchronized manually with the control file an order to have it display the
new file names. Answer: A,C
NO.68 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
Answer: B
https://docs.oracle.com/cd/B28359_01/server.111/b28310/ds_concepts002.htm#ADMIN12085
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
NO.69
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 I I ' ' II salary
FROM employees;
(C). SELECT SUBSTR<join_date, 1, 2) -
10 FROM employees;
(D). SELECT join_date + '20' FROM
employees; SELECT salary * '120.50' FROM employees; Answer: A
NO.70 The stores table has a column START_DATE of data type DATE,
containing the date the row was inserted. You only want to display details of
rows where START_DATE is within the last 25 months. Which where clause can be
used?
(A). WHERE MON'THS_BETWEEN (SYSDATE,
start_date) <= 25
(B). WHERE ADD_MONTHS<start_date, 25)
<= SYSDATE
(C). WHERE TO_XUMBER(start_date - SYSDATE)
<= 25
(D). WHERE MON'THS_BETWEEN (start_date,
SYSDATE) <= 25
Answer: D
NO.71 Which three statements are true about sequences in a single
instance Oracle database?
(A). Sequences can always have gaps.
(B). A sequence can Issue duplicate
values.
(C). A sequence's unallocated cached
values are lost if the instance shuts down.
(D). Two or more tables cannot have keys
generated from the same sequence.
(E). A sequence can only be dropped by a
DBA.
(F). A sequence number that was allocated can be rolled back if a transaction fails.
Answer: B,C,D
NO.72 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.
Answer: B,C
NO.73 Which two statements are true about substitution variables?
(A). A substitution variable prefixed with
& always prompts only once for a value in a session. (B). A substitution
variable prefixed with to prompts only once for a value in a session unless it
is set to undefined in the session
(C). A substitution variable can be used
only in a select statement.
(D). A substitution variable used to
prompt for a column name must be enclosed in single quotation marks.
(E). A substitution variable can be used
with any clause in a select statement.
(F). A substitution variable used to prompt for a column name must be enclosed in double quotation marks.
Answer: B
NO.74 In which three situations does a new transaction always start?
(Choose three.)
(A). when issuing a SELECT FOR UPDATE
statement after a CREATE TABLE AS SELECT statement was issued in the same
session
(B). when issuing a TRUNCATE statement
after a SELECT statement was issued in the same session
(C). when issuing a CREATE TABLE statement
after a SELECT statement was issued in the same session
(D). when issuing the first Data
Manipulation Language (DML) statement after a COMMIT OR ROLLBACK statement was
issued in the same session
(E). when issuing a CREATE INDEX statement
after a CREATE TABLE statement completed successfully in the same session
(F). when issuing a DML statement after a
DML statement failed in the same session
Answer: A,D,E
https://docs.oracle.com/en/database/oracle/oracle-database/19/cncpt/transactions.html#GUID7690181F-8293-47B9-ADC1-905BD9C3AF57
NO.75 Which two statements are true about Oracle synonyms?
(A). A synonym has an object number.
(B). A synonym can be created on an object
in a package.
(C). A synonym can have a synonym.
(D). All private synonym names must be
unique in the database.
(E). Any user can create a PUBLIC synonym.
Answer: A,C
NO.76 Which three instance situations are possible with the Oracle
Database server without multitenant? (Choose three.)
(A). two or more instances on separate
servers all associated with one database
(B). one instance on one server associated
with one database
(C). one instance on one server associated
with two or more databases on the same server
(D). one instance on one server not associated
with any database
(E). one instance on one server associated with two or more databases on separate servers
Answer: A,B,D
NO.77 Examine the description of the product_details table:
Which two statements are true?
(A). PRODUCT _ic can be assigned the
PRIMARY KEY constraint.
(B). PRODUCT _price contains the value
zero by default if no value is assigned to it.
(C). PRODUCT _price can be used in an
arithmetic expression even if it has no value stored in it.
(D). EXPIRY_ DATE contains the sysdate by
default if no date is assigned to it.
(E). PRODUCT_NAKE cannot contain duplicate
values.
(F). EXPIRY_ DATE cannot be used
in arithmetic expressions. Answer: A,C
NO.78 Examine these commands:
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 Answer: C,E
NO.79 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
Answer: A,C,F https://docs.oracle.com/cd/B28359_01/server.111/b28310/manproc001.htm#ADMIN11168
NO.80 Which three statements are true about the Oracle join and ANSI join
syntax? (Choose three.)
(A). The Oracle join syntax supports
creation of a Cartesian product of two tables
(B). The Oracle join syntax performs
better than the SQL:1999 compliant ANSI join syntax
(C). The SQL:1999 compliant ANSI join
syntax supports natural joins
(D). The SQL:1999 compliant ANSI join
syntax supports creation of a Cartesian product of two tables
(E). The Oracle join syntax only supports
right outer joins
(F). The Oracle join syntax supports
natural joins
(G). The Oracle join syntax performs
less well than the SQL:1999 compliant ANSI join syntax Answer: A,C,D
https://oracle-base.com/articles/9i/ansi-iso-sql-support
NO.81 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.
What will be the result?
(A). It gives an error because the ALL
keyword is not valid
(B). It gives an error because the GROUP
BY clause is not valid
(C). It executes successfully but does not
give the required result
(D). It executes successfully and gives
the required result
Answer: D
NO.82 Examine the description of the members table:
Examine the partial query:
SELECT city, last__name 1NAME FROM members
You want to display all cities that
contain the string an. The cities must be returned in ascending order, with the
last names further sorted in descending order.
Which two clauses must you add to the
query?
(A). ORDER BY 1, LNAME DESC
(B). ORDER BY 1, 2
(C). WHERE city LIKE *%AN%*
(D). WHERE city = =%AN%'
(E). WHERE city IN (*%AN%')
(F). ORDER BY last_narae DESC, city
ASC Answer: A,C
NO.83 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 "Last Name"
(B). ORDER BY 2, 1
(C). ORDER BY 2, cust_id
(D). ORDER BY CUST_NO
(E). ORDER BY "CUST_NO" Answer: B,C,D
NO.84 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 Answer: A,C
NO.85 Which compression method is recommended for Direct-Path Insert
operations?
(A). COLUMN STORE COMPRESS BASIC
(B). COLUMN STORE COMPRESS ADVANCED
(C). RON STORE COMPRESS BASIC
(D). ROW STORE COMPRESS ADVANCED
Answer: C
NO.86 Which two statements are true about UNDO and REDO? (Choose two.)
(A). The generation of UNDO generates REDO
(B). DML modifies Oracle database objects
and only generates UNDO
(C). The generation of REDO generates UNDO
(D). DML modifies Oracle database objects
and only generates REDO
(E). DML modifies Oracle database objects
and generates UNDO and REDO
Answer: A,E
https://www.experts-exchange.com/articles/13880/UNDO-AND-REDO-IN-ORACLE.html
NO.87 Which three statements are true about the naming methods and their
features supported by Oracle database used to resolve connection information?
(A). Local naming can be used if
Connect-Time Failover Is required.
(B). A client can connect to an Oracle
database instance even If no client side network admin has been configured.
(C). Directory Naming requires setting the
TNS_ADMIN environment variable on the client side.
(D). Local Naming requires setting the
TNS_ADMIN environment variable on the client side.
(E). Directory Naming can be used if Connect-Time
Failover is required.
(F). Easy Connect supports TCP/IP and SSL.
Answer: A,B,C
NO.88 Which three are types of segments in an Oracle Database? (Choose
three.)
(A). undo
(B). index
(C). stored procedures
(D). sequences
(E). tables
(F). clusters
Answer: A,E,F
NO.89 You execute this query:
SELECT TO_CHAR(NEXT_DAY(LAST_DAY(SYSDATE),
'MON'), 'dd "Monday for" fmMonth rrrr') What is the result?
(A). It executes successfully but does not
return any result
(B). It returns the date for the first
Monday of the next month
(C). It generates an error
(D). It returns the date for the last Monday of the current month
Answer: B
NO.90 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).
REKOTE_LOGIN_PASSMOREFiLE must be set to exclusive to permit password changes
for systemprivileged 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.
Answer: A,C
NO.91 Which four account management capabilities can be configured using
Oracle profiles?
(Choose four.)
(A). the number of hours for which an
account is locked after the configured number of login attempts has been
reached
(B). the number of days for which an
account may be inactive before it is locked
(C). the maximum amount of CPU time
allowed for a user's sessions before their account is locked
(D). the ability to prevent a password
from ever being reused
(E). the number of password changes
required within a period of time before a password can be reused
(F). the number of days for which an
account is locked after the configured number of login attempts has been
reached
(G). the maximum number of sessions permitted for a user before the account is locked
Answer: D,E,F,G
NO.92 Which two statements are true about undo and undo tablespaces?
(A). There can be only one undo tablespace
created in a database.
(B). An instance will crash if the active
undo tablespace is lost.
(C). An undo tablespace may be owned by
only one instance.
(D). undo segments are owned by SYSTEM.
(E). undo segments are owned by
SYSBACKUP. Answer: B,C
NO.93 Examine this command:
SQL> ALTER TABLE ORDERS SHRINK
SPACE COMPACT Which two statements are true?
(A). The high-water mark (HWM) of orders
is adjusted.
(B). Only queries are allowed on ORDERS
while the shrink is executing.
(C). Queries and DML statements are
allowed on ORDERS while the shrink is executing.
(D). The shrink operation causes rows to
be moved to empty space starting from the beginning of the orders segment.
(E). Dependent indexes become UNUSABLE.
(F). The shrink operation causes rows to be moved to empty space starting toward the end of the orders segment.
Answer: C,D
NO.94 Which three files are used by conventional path SQL*Loader when the
TABLE option is not specified? (Choose three.)
(A). dump files
(B). control files
(C). password files
(D). bad files
(E). input files
Answer: B,D,E
NO.95 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 Answer: B,C,E
NO.96 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). COLUMN STORE COMPRESS FOR QUERY LOW
(B). ROW STORE COMPRESS BASIC
(C). COLUMN STORE COMPRESS FOR ARCHIVE LOW
(D). COLUMN STORE COMPRESS FOR ARCHIVE
HIGH
(E). ROW STORE COMPRESS ADVANCED Answer: E
NO.97 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
Answer: A,C
NO.98 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). CURRENT_TIMESTAMP returns the same
date and time as SYSDATE with additional details of fractional seconds
(B). SYSDATE can be queried only from the
DUAL table
(C). CURRENT_DATE returns the current date
and time as per the session time zone
(D). SYSDATE can be used in expressions
only if the default date format is DD-MON-RR
(E). SYSDATE and CURRENT_DATE return the
current date and time set for the operating system of the database server
(F). CURRENT_TIMESTAMP returns the same
date as CURRENT_DATE
Answer: C,F
https://www.w3resource.com/oracle/datetime-functions/oracle-current_timestamp-function.php
NO.99 Which statement is true about the INTERSECT operator used in
compound queries?
(A). Multiple INTERSECT operators are not
possible in the same SQL statement
(B). It processes NULLs in the selected
columns
(C). INTERSECT is of lower precedence than UNION or UNION ALL (D). It ignores NULLs
Answer: B
NO.100 You start your database instance in NOMOUNT state. Which two
actions are performed?
(A). The control files are opened.
(B). The consistency of the database is
checked.
(C). All required background processes are
started.
(D). SYS can access the database.
(E). Memory is allocated for the SG1
Answer: C,E
NO.101 Your database instance is started with a PFILE.
Examine these parameters:
You want to increase the size of the
buffer cache.
Free memory is available to increase the
size of the buffer cache.
You execute the command:
SQL> ALTER SYSTEM SET
DB_CACHE_SIZE=1024M; What is the outcome?
(A). The value is changed only in the
PFILE and takes effect at the next instance startup
(B). The value is changed for the current
instance and in the PFILE
(C). It fails because the SCOPE clause is
missing
(D). Change is applied to the current instance, but does not persist after instance restart
Answer: D
NO.102 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
Answer: B
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.
NO.103 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.
Answer: B,C,E
NO.104 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 Answer: A,C,D
NO.105 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
Answer: D,E,F
https://docs.oracle.com/en/database/oracle/oracle-database/19/netag/introducing-oracle-netservices.html#GUID-6B1F0584-887D-4D2A-B982-40ACF15DF985
* Oracle Enterprise Manager Cloud Control
combines configuration functionality across multiple file systems, along with
listener administrative control to provide an integrated environment for
configuring and managing Oracle Net Services. * Oracle Net Manager provides
configuration functionality for an Oracle home on a local client or server
host.
NO.106 Which two statements are true about the ORDER BY clause when used
with a SQL statement containing a SET operator such as UNION? (Choose two.)
(A). Column positions must be used in the
ORDER BY clause
(B). Only column names from the
first SELECT statement in the compound query are recognized (C). The first
column in the first SELECT of the compound query with the UNION operator is
used by default to sort output in the absence of an ORDER BY clause
(D). Each SELECT statement in the
compound query must have its own ORDER BY clause (E). Each SELECT statement in
the compound query can have its own ORDER BY clause Answer: B,C
NO.107 Which three statements are true concerning logical and physical
database structures?
(Choose three.)
(A). All tablespaces may have one or more
data files
(B). The extents of a segment must always
reside in the same datafile
(C). A smallfile tablespace might be
bigger than a bigfile tablespace
(D). A segment can span multiple data
files in some tablespaces
(E). A segment's blocks can be of
different sizes
(F). A segment might have only one extent
(G). Segments can span multiple
tablespaces
Answer: B,C,F
NO.108 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 = &&cond;
(B). SELECT &col1, &col2FROM
&&tableWHERE &condition;
(C). SELECT &col1, &col2FROM
"&table"WHERE &condition;
(D). SELECT '&&col1',
'&&col2'FROM &tableWHERE '&&condition' = '&cond';
(E). SELECT &&col1,
&&col2FROM &tableWHERE &&condition;
Answer: B
NO.109 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
Answer: C,D
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
NO.110 Which two statements are true about space-saving features in an
Oracle Database? (Choose two.)
(A). Private Temporary Tables (PTTS) store
metadata in memory only
(B). An index created with the UNUSABLE
attribute has no segment
(C). If they exist for a session, Private
Temporary Tables (PTTs) are always dropped at the next
COMMIT OR ROLLBACK statement
(D). An index that is altered to be
UNUSABLE will retain its segment
(E). A table that is truncated
will always have its segment removed Answer: B,C
NO.111 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_cost || ' in '
|| DISTINCT promo_category FROM promotions ORDER BY 1;
(C). SELECT DISTINCT promo_category || '
has ' || promo_cost AS COSTS FROM promotions ORDER BY 1;
(D). SELECT promo_category, DISTINCT
promo_cost FROM promotions ORDER BY 2;
(E). SELECT DISTINCT
promo_category, promo_cost FROM promotions ORDER BY 1; Answer: C,E
NO.112 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;
Answer: C,E
NO.113 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.
Answer: B
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.
NO.114 Which two statements are true about the DUAL table? (Choose two.)
(A). It can be accessed only by the SYS
user
(B). It consists of a single row and
single column of VARCHAR2 data type
(C). It can display multiple rows but only
a single column
(D). It can be used to display only
constants or pseudo columns
(E). It can be accessed by any user who has the SELECT privilege in any schema (F). It can display multiple rows and columns
Answer: B,F
NO.115 Which two statements are true about trace files produced by the
Oracle Database server?
(Choose two.)
(A). They can be written by server
processes
(B). Trace files are written to the Fast
Recovery Area (FRA)
(C). They can be written by background
processes
(D). All trace files contain error
information that require contacting Oracle Support
(E). Trace file names are based on the database name concatenated with a sequential number
Answer: A,C
Reference:
https://docs.oracle.com/html/E25494_01/monitoring001.htm
Each server and background process can
write to an associated trace file. When an internal error is detected by a
process, it dumps information about the error to its trace file. Some of the
information written to a trace file is intended for the database administrator,
and other information is for Oracle Support Services. Trace file information is
also used to tune applications and instances.
NO.116 The sales table has columns prod_id and quantity_sold of data type
number In two queries execute successfully? x
(A). SELECT prod_id FROM sales WHERE
quantity_sold > 55000 GROUP BY prod_id HAVING COUNT(-) > 10;
(B). SELECT prod_id FROM sales WHERE
quantity_sold > 5S000 AND COUNT(-) > 10 GROUP BY prod_id HAVING COUNT(-)
> 10;
(C). SELECT COUNT|prod_id> FROM sales
WHERE quantity_sold > 55000 GROUP BY prod_id;
(D). SELECT COUNTlprod_id> FROM sales
GROUP BY prod_id WHERE quantity_Sold > 55000;
(E). SELECT prod_id FROM sales WHERE
quantity_scld > 55000 AND COUNT(-) > 10 GROUP BY COUNT(-) > 10;
Answer: A,C
NO.117 Which two statements are true about the SET VERIFY ON command?
(Choose two.)
(A). It can be used only in SQL*Plus
(B). It displays values for variables used
only in the WHERE clause of a query
(C). It can be used in SQL Developer and
SQL*Plus
(D). It displays values for variables
created by the DEFINE command
(E). It displays values for
variables prefixed with && Answer: C,E
NO.118 Which two actions can you perform using DBCA for an existing
database?
(A). Create a template that can be used to
clone the database.
(B). Change the server mode from dedicated
to shared, and vice versa.
(C). Change the character set.
(D). Create an additional listener.
(E). Create nonstandard block size tablespaces.
Answer: B,C
NO.119 Which three statements are true about the Automatic Diagnostic
Repository (ADR)?
(A). It Is held Inside an Oracle database
schema.
(B). The ADR base is specified In the
diagnostic_dest database parameter.
(C). It is only used for Oracle Database
diagnostic information.
(D). It is a file-based repository held
outside any database.
(E). It can be used for problem diagnosis
of a database when that database's instance is down.
Answer: A,B,D
NO.120 Examine the description of the CUSTOMERS table:
You want to display details of all
customers who reside in cities starting with the letter D followed by at least
two characters.
Which query can be used?
(A). SELECT * FROM customers WHERE city
LIKE 'D_%';
(B). SELECT * FROM customers WHERE city =
'%D_';
(C). SELECT * FROM customers WHERE city
LIKE 'D_';
(D). SELECT * FROM customers WHERE
city = 'D_%'; Answer: A
NO.121 Which three statements are true about using SQL*Plus?
(A). It has its own commands that are
separate from any SQL statements.
(B). It must be downloaded from the Oracle
Technology Network (OTN).
(C). It can run scripts entered at the SQL
prompt.
(D). It can run Recovery Manager (RMAN)
commands.
(E). It has both command-line and
graphical user interfaces (GUI).
(F). It can run scripts passed to
it by a shell script. Answer: A,B,C
NO.122 Evaluate these commands which execute successfully:
Which two statements are true about the
ORD_ITEMS table and the ORD_SEQ sequence? (Choose two.)
(A). If sequence ORD_SEQ is dropped then
the default value for column ORD_NO will be NULL for rows inserted into
ORD_ITEMS
(B). Any user inserting rows into table
ORD_ITEMS must have been granted access to sequence
ORD_SEQ
(C). 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
(D). Sequence ORD_SEQ cycles back
to 1 after every 5000 numbers and can cycle 20 times (E). Sequence ORD_SEQ is
guaranteed not to generate duplicate numbers Answer: B,C
NO.123 The EMPLOYEES table contains columns EMP_ID of data type NUMBER and
HIRE_DATE of data type DATE.
You want to display the date of the
first Monday after the completion of six months since hiring. The NLS_TERRITORY
parameter is set to AMERICA in the session and, therefore, Sunday is the first
day on the week.
Which query can be used?
(A). SELECT emp_id, ADD_MONTHS(hire_date,
6), NEXT_DAY('MONDAY') FROM employees;
(B). SELECT emp_id,
NEXT_DAY(ADD_MONTHS(hire_date, 6), 'MONDAY') FROM employees;
(C). SELECT emp_id, NEXT_DAY(MONTHS_BETWEEN(hire_date, SYSDATE), 6) FROM employees; (D). SELECT emp_id, NEXT_DAY(ADD_MONTHS(hire_date, 6), 1) FROM employees;
Answer: B
NO.124 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 Answer: A,E,F
NO.125 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) Answer: B,D,E
NO.126 Which two queries execute successfully? (A). SELECT NULLIF(NULL,
100) FROM DUAL;
(B). SELECT COALESCE (100, 'AM FROM DUAL;
(C). SELECT 2TOLLIF(100, 'A') FROM
DUAL; (D). SELECT NULLIF(100,100) FROM DUAL;
(E). SELECT COALESCE(100, NULL,
200) FROM DUAL; Answer: E
NO.127 The customers table has a cust_last_name column of data type
varchar2.
The table has two rows whose
"jst_last_name values are Andersen and Ausson.
Which query produces output for
cust_last_xame containing Oder for the first row and Aus for the second?
(A). SELECT REPLACE<TRIM<TRAILING
'son* FROM cust_last_name), 'An', *O'> FROM customers;
(B). SELECT REPLACE(SUBSTR(cust_last_name,
-3), 'An', 'O') FROM customers;
(C). SELECT
REPLACE(REPLACE(cust_last_name, 'son', ''), 'An', 'O'> FROM customers;
(D). SELECT INITCAP (REPLACE(TRIM('son'
FROM cust_last_name), 'An', 'O*)) FROM customers; Answer: A
NO.128 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:
SQL> CREATE TABLE t1 AS
(SELECT object_name, sharing, created
FROM dba_objects);
U1 complains that the command is taking
too long to execute.
In the alert log, the database
administrator (DBA) finds this:
2017/3/6 14:45:17 statement in
resumable session 'User U1(136), Session 1, Instance 1' was suspended due to
ORA01536: space quota exceeded for tablespace 'DATA' Which are three actions
any one of which the DBA could take to resume the session? (Choose three.)
(A). Add a data file to DATA
(B). Drop other U1 objects in DATA
(C). Increase U1's quota sufficiently in
DATA
(D). Set DEFERRED_SEGMENT_CREATION to TRUE
(E). Grant UNLIMITED TABLESPACE to U1
(F). Set AUTOEXTEND ON for data files in
DATA
Answer: B,C,E
NO.129 Which three statements are true about the DESCRIBE command? (Choose
three.)
(A). It displays the PRIMARY KEY
constraint for any column or columns that have that constraint
(B). It can be used from SQL Developer
(C). It displays the NOT NULL constraint
for any columns that have that constraint
(D). It can be used to display the
structure of an existing view
(E). It displays all constraints that are
defined for each column
(F). It can be used only from SQL*Plus
Answer: C,D,F
NO.130 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 Answer: A,D,G
NO.131 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 Answer: B,C,E
NO.132 Examine these commands:
Which two statements are true about the
sqiidr execution?
(A). It generates a sqi script that
it uses to load data from EMP.DAT TO EMP. (B). It overwrites the data for Alan
and adds data for Curl and Bob (C). It appends data from EMP.DAT TO EMP.
(D). It overwrites all data in emp with
data from emp.dat.
(E). It generates a log that contains
control file entries, which can be used with normal SQL'Loader operations.
Answer: A,C
NO.133 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). Set OS_AUTHENT_PREFIX to OPS$
(B). Have the OS administrator add KING to
the OSDBA group
(C). Grant DBA to KING
(D). Unset REMOTE_LOGIN_PASSWORDFILE
(E). Alter user KING to be IDENTIFIED
EXTERNALLY
Answer: A
https://oracle-base.com/articles/misc/os-authentication
NO.134 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.
Answer: C,E
NO.135 Which two statements are true about Database Instances and Real
Application Clusters (RAC)?
(A). A RAC database can have one Instance.
(B). A RAC database can have instances on
separate servers.
(C). Two RAC databases can share their
instances.
(D). A RAC database must have three or
more Instances.
(E). A RAC database must have two or more instances.
Answer: A,B
NO.136 Examine the description of the PRODUCT_STATUS table:
The status column contains the values 'in
stock' or 'out of stock' for each row.
Which two queries will execute
successfully?
(A). SELECT prod_id Il q"'s not
available" FROM product_status where status = 'OUT OF STOCK';
(B). SELECT prod_id II q'l's not
available)' FROM product_status WHERE status = 'OUT OF STOCK'; (C). SELECT
prod_id "CURRENT AVAILABILITY" II q'<'s not available)' FROM
product_status WHERE status = 'OUT OF STOCK
(D). SELECT prod_id I I q' (*s not
available)' "CURRENT AVAILABILITY" FROM product_status WHERE status =
'OUT OF STOCK
(E). SELECT prod_id I I q' ('s not
available) * 'CURRENT AVAILABILITY' FROM product_status WHERE status = 'OUT OF
STOCK
(F). SELECT prod_id q's not
available" FROM product_status WHERE status = 'OUT OF STOCK*; Answer: B,D
NO.137 Examine the description of the BOOKS table:
The table has 100 rows.
Examine this sequence of statements issued
in a new session:
INSERT INTO books VALUES ('ADV112',
'Adventures of Tom Sawyer', NULL, NULL); SAVEPOINT a; DELETE FROM books;
ROLLBACK TO SAVEPOINT a; ROLLBACK; Which two statements are true?
(Choose two.)
(A). The second ROLLBACK command does
nothing
(B). The second ROLLBACK command replays
the delete
(C). The first ROLLBACK command restores
the 101 rows that were deleted, leaving the inserted row still to be committed
(D). The second ROLLBACK command undoes
the insert
(E). The first ROLLBACK command restores the 101 rows that were
deleted and commits the inserted row Answer: C,D
NO.138 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)
Answer: E,F
NO.139 Which two statements are true about interval data types?
(A). INTERVAL year TO month columns
support yearly intervals.
(B). The value in an interval day to
SECOND column can be copied into an interval year to month column.
(C). INTERVAL day to second columns
support fractions of seconds.
(D). INTERVAL year to month columns only
support monthly Intervals within a single year.
(E). INTERVAL year to month columns only
support monthly intervals within a range of years.
(F). The year field in an interval year to month column must be a positive value.
Answer: A,C
NO.140 The orders table has a column ORDER_DATE of data type date.
The default display format for a date Is
DD-MON-RR.
Which two where conditions demonstrate the
correct usage of conversion functions?
(A). WHERE order_date >
TO_CHAR(ADD_MONTHS(SYSDATE, 6), 'MON DD YYYY')
(B). WHERE TO_CHAR(order_date, 'MON DD
YYYY') = 'JAN 20 2019'
(C). WHERE order_date >
TO_DATE<ADD_MONTHS(SYSDATE, c) , 'MON DD YYYY')
(D). WHERE order_date IN (TO_DATE ('Oct 21
2018', 'Mon DD YYYY'), TC_CHAR('Nov 21 2018', 'Mon DD YYYY'))
(E). WHERE order date > TO DATE('JUL 10
2018', *MON DD YYYY')
Answer: B,E
NO.141 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
Answer: A,E
https://stackoverflow.com/questions/58476906/1z0-071-oracle-exam-question-which-answers-iscorrec
NO.142 Which statement is true about database links?
(A). Private database link creation
requires the same user to exist in both the local and the remote databases.
(B). A public database link can be created
only by sys.
(C). A database link can be created only
between two Oracle databases.
(D). A public database link can be used by
a user connected to the local database instance to connect to any schema in the
remote database instance.
(E). A database link created in a database allows a connection from that database's instance to the target database's Instance, but not vice versa.
Answer: D
NO.143 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). WHERE and HAVING clauses can be used
in the same statement only if applied to different table columns
(C). The HAVING clause can be used with
aggregating functions in subqueries
(D). The WHERE clause can be used
to exclude rows before dividing them into groups (E). The WHERE clause can be
used to exclude rows after dividing them into groups Answer: C,D
NO.144 Which three statements are true about roles?
(A). All roles granted to a user are
set on by default when the user logs in, (B). Object privileges may not be
granted to roles.
(C). The SET ROLE statement can disable
one or more roles for a session.
(D). Roles must be password protected.
(E). Roles may be granted to roles.
(F). The SET ROLE statement can enable one
or more roles for a session.
Answer: B,C,E
NO.145 Examine these SQL statements which execute successfully:
Which two statements are true after
execution?
(A). The primary key constraint will be
enabled and deferred.
(B). The foreign key constraint will be
enabled and deferred.
(C). The foreign key constraint will be
disabled.
(D). The foreign key constraint will be
enabled and immediate.
(E). The primary key constraint will be enabled and immediate.
Answer: C
Website không bao giờ chứa bất kỳ quảng cáo nào, mọi đóng góp để duy trì phát triển cho website (donation) xin vui lòng gửi về STK 90.2142.8888 - Ngân hàng Vietcombank Thăng Long - TRAN VAN BINH
=============================
Nếu bạn muốn tiết kiệm 3-5 NĂM trên con đường trở thành DBA chuyên nghiệp thì hãy đăng ký ngay KHOÁ HỌC ORACLE DATABASE A-Z ENTERPRISE, được Coaching trực tiếp từ tôi với toàn bộ kinh nghiệm, thủ tục, quy trình, bí kíp thực chiến mà bạn sẽ KHÔNG THỂ tìm kiếm trên Internet/Google giúp bạn dễ dàng quản trị mọi hệ thống Core tại Việt Nam và trên thế giới, đỗ OCP.
- CÁCH ĐĂNG KÝ: Gõ (.) hoặc để lại số điện thoại hoặc inbox https://m.me/tranvanbinh.vn hoặc Hotline/Zalo 090.29.12.888
- Chi tiết tham khảo:
https://bit.ly/oaz_w
=============================
2 khóa học online qua video giúp bạn nhanh chóng có những kiến thức nền tảng về Linux, Oracle, học mọi nơi, chỉ cần có Internet/4G:
- Oracle cơ bản: https://bit.ly/admin1_1200
- Linux: https://bit.ly/linux_1200
=============================
KẾT NỐI VỚI CHUYÊN GIA TRẦN VĂN BÌNH:
📧 Mail: binhoracle@gmail.com
☎️ Mobile/Zalo: 0902912888
👨 Facebook: https://www.facebook.com/BinhOracleMaster
👨 Inbox Messenger: https://m.me/101036604657441 (profile)
👨 Fanpage: https://www.facebook.com/tranvanbinh.vn
👨 Inbox Fanpage: https://m.me/tranvanbinh.vn
👨👩 Group FB: https://www.facebook.com/groups/DBAVietNam
👨 Website: https://www.tranvanbinh.vn
👨 Blogger: https://tranvanbinhmaster.blogspot.com
🎬 Youtube: https://www.youtube.com/@binhguru
👨 Tiktok: https://www.tiktok.com/@binhguru
👨 Linkin: https://www.linkedin.com/in/binhoracle
👨 Twitter: https://twitter.com/binhguru
👨 Podcast: https://www.podbean.com/pu/pbblog-eskre-5f82d6
👨 Địa chỉ: Tòa nhà Sun Square - 21 Lê Đức Thọ - Phường Mỹ Đình 1 - Quận Nam Từ Liêm - TP.Hà Nội
=============================
oracle tutorial, học oracle database, Tự học Oracle, Tài liệu Oracle 12c tiếng Việt, Hướng dẫn sử dụng Oracle Database, Oracle SQL cơ bản, Oracle SQL là gì, Khóa học Oracle Hà Nội, Học chứng chỉ Oracle ở đầu, Khóa học Oracle online,sql tutorial, khóa học pl/sql tutorial, học dba, học dba ở việt nam, khóa học dba, khóa học dba sql, tài liệu học dba oracle, Khóa học Oracle online, học oracle sql, học oracle ở đâu tphcm, học oracle bắt đầu từ đâu, học oracle ở hà nội, oracle database tutorial, oracle database 12c, oracle database là gì, oracle database 11g, oracle download, oracle database 19c, oracle dba tutorial, oracle tunning, sql tunning , oracle 12c, oracle multitenant, Container Databases (CDB), Pluggable Databases (PDB), oracle cloud, oracle security, oracle fga, audit_trail,oracle RAC, ASM, oracle dataguard, oracle goldengate, mview, oracle exadata, oracle oca, oracle ocp, oracle ocm , oracle weblogic, postgresql tutorial, mysql tutorial, mariadb tutorial, ms sql server tutorial, nosql, mongodb tutorial, oci, cloud, middleware tutorial, hoc solaris tutorial, hoc linux tutorial, hoc aix tutorial, unix tutorial, securecrt, xshell, mobaxterm, putty
