1Z0-082 - Question Set 4

Question 1: 
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, PFILE, and memory
B. in the SPFILE and in memory
C. only in the SPFILE
D. only in memory
E. in the SPFILE and PFILE
Answer :  B

Question 2:
Which two statements are true about Enterprise Manager (EM) Express?
A. You can shut down a database instance using EM Express.
B. You cannot start up a database instance using EM Express.
C. By default, EM express is available for a database after database creation using DBCA.
D. You can use a single instance of EM Express to manage multiple database running on the same server.
E. EM Express uses a separate repository database to store target database metadata.
BC

Note
You cannot start up or shut down a database Instance by using EM express.

Question 3:
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
AC

Question 4:
Which two statements are true about User Authentication in an Oracle Database? (Choose two.)
A. Password authentication must be used for system-privileged administrative users.
B. Password File authentication must be used for system-privileged administrative users.
C. Operating System authentication may be used for system-privileged administrative users.
D. Password File authentication is supported for any type of database user. -- administrative users
E. REMOTE_LOGIN_PASSWORDFILE must be set to exclusive to permit password changes for system-privileged administrative users.
CD

Question 5: 
Which three statements are true about the Oracle join and ANSI join syntax?
A. The Oracle join syntax supports natural joins.
B. The Oracle join syntax performs less well than the SQL:1999 compliant ANSI join syntax.
C. The Oracle join syntax supports creation of a Cartesian product of two tables.
D. The SQL:1999 compliant ANSI join syntax supports natural joins.
E. The Oracle join syntax performs better than the SQL:1999 compliant ANSI join syntax.
F. The Oracle join syntax only supports right outer joins.
G. The SQL:1999 compliant ANSI join syntax supports creation of a Cartesian product of two tables.
CDG

Question 6.
Which two statements are true about INTERVAL data types? (Choose two.)
Which two statements are true about INTERVAL data types?
A. The YEAR field in an INTERVAL YEAR TO MONTH column must be a positive value.
B. INTERVAL DAY TO SECOND columns support fractions of seconds.
C. INTERVAL YEAR TO MONTH columns only support monthly intervals within a single year.
D. INTERVAL YEAR TO MONTH columns support yearly intervals.
E. INTERVAL YEAR TO MONTH columns only support monthly intervals within a range of years.
F. The value in an INTERVAL DAY TO SECOND column can be copied into an INTERVAL YEAR TO MONTH column.
BD

Question 7: 
Which three actions are ways to apply the principle of least privilege? (Choose three.)
A. setting the O7_DICTIONARY_ACCESSIBILITY parameter to true
B. setting the REMOTE_OS_AUTHENT parameter to true
C. revoking execute privilege on UTL_SMTP, UTL_TCP, UTL_HTTP, and UTL_FILE from the SYSTEM user
D. enabling Unified Auditing
E. using Access Control Lists (ACLs)
F. revoking execute privilege on UTL_SMTP, UTL_TCP, UTL_HTTP, and UTL_FILE from the public user
DEF

Question 8: 
Which three statements are true about data block storage in an Oracle Database? (Choose three.)
A. An index block can contain row data.
B. A data block header is of a fixed length.
C. Row data is stored starting at the end of the block.
D. A table block must always contain row data.
E. A block header contains a row directory pointing to all rows in the block.
ACE

Question 9:
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
ACD

Question 10:
Which three statements are true about indexes and their administration in an Oracle database?
A. An index can be created as part of a CREATE TABLE statement.
B. A DROP INDEX statement always prevents updates to the table during the drop operation.
C. A UNIQUE and non-unique index can be created on the same table column.
D. A descending index is a type of function-based index.
E. If a query filters on an indexed column then it will always be used during execution of the query.
F. An INVISIBLE index is not maintained when Data Manipulation Language (DML) is performed on its underlying table.
ACD

https://docs.oracle.com/database/121/ADMIN/indexes.htm#ADMIN11720 -- For Point C

Question 11:
Which two statements are true about Oracle synonyms?
A. A synonym can have a synonym.
B. All private synonym names must be unique in the database.
C. Any user can create a PUBLIC synonym.
D. A synonym can be created on an object in a package.
E. A synonym has an object number.
AE

Question 12.
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
BCD

Question 13.
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
BDE

Question 14:
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 || q”’s not available” FROM product_status WHERE status = ‘OUT OF STOCK’;
B. SELECT prod_id || q’(‘s not available)’ ‘CURRENT AVAILABILITY’ FROM product_status WHERE status = ‘OUT OF STOCK’;
C. SELECT prod_id q’s not available” FROM product_status WHERE status = ‘OUT OF STOCK’;
D. SELECT prod_id “CURRENT AVAILABILITY” || q’(‘s not available)’ FROM product_status WHERE status = ‘OUT OF STOCK’;
E. SELECT prod_id || q’(‘s not available)’ FROM product_status WHERE status = ‘OUT OF STOCK’; Most Voted
F. SELECT prod_id || q’(‘s not available)’ “CURRENT AVAILABILITY” FROM product status WHERE status = ‘OUT OF STOCK’;
EF

Question 15: 
Which two statements are true about UNDO and UNDO tablespaces? (Choose two.)
A. There can be only one UNDO tablespace created in a database. wrong - In case of RAC, each instance will have its own UNDO tablespace.
B. UNDO segments are owned by SYSTEM.
C. UNDO segments are owned by SYSBACKUP.
D. An UNDO tablespace may be owned by only one instance.
E. An instance will crash if the active undo tablespace is lost.
DE


Question 16:
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
B
https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/DEFERRED_SEGMENT_CREATION.html#GUID-29066C9A-1CC7-48E4-A278-BB9678EE3362
If set to true, then segments for tables and their dependent objects (LOBs, indexes) will not be created until the first row is inserted into the table.

Question 17:
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
BCF

Question 18: 
Which three statements are true about Deferred Segment Creation in Oracle databases? (Choose three.)
A. It is supported for Index Organized Tables (IOTs) contained in locally managed tablespaces.
B. It is the default behavior for tables and indexes. Most Voted
C. Sessions may dynamically switch back and forth from DEFERRED to IMMEDIATE segment creation. Most Voted
D. Indexes inherit the DEFERRED to IMMEDIATE segment creation attribute from their parent table Most Voted
E. It is supported for SYS-owned tables contained in locally managed tablespaces.
BCD

Question 19: 
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.
DE

Question 20:
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
ACE

Question 21: 
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 database link created in a database allows a connection from that database's instance to the target database's instance, but not vice versa.
C. 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.
D. A public database link can be created only by SYS.
E. A database link can be created only between two Oracle databases.
B

Question 22: 
Which compression method is recommended for Direct-Path Insert operations?
A. ROW STORE COMPRESS BASIC
B. ROW STORE COMPRESS ADVANCED
C. COLUMN STORE COMPRESS ADVANCED
D. COLUMN STORE COMPRESS BASIC
A

Question 23:
You want to create a table, DAILY_ORDERS, for an OLTP application, where data should be compressed during both direct-path INSERT and conventional DML.
The table will also be used for queries.
Which compression option should be used?
A. ROW STORE COMPRESS
B. COLUMN STORE COMPRESS FOR QUERY
C. COLUMN STORE COMPRESS FOR ARCHIVE LOW
D. ROW STORE COMPRESS ADVANCED
D
Note
basic compression for direct-path insert operationes 10x
advance row compression for all DML operations 2 -4x

Question 24:
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
BCD

Question 25:
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
AFG
CREATE TABLE employees (
id NUMBER,
name VARCHAR2(100),
salary NUMBER,
department_id NUMBER
);
INSERT INTO employees VALUES(1, 'John Doe', 50000, 1);
INSERT INTO employees VALUES(2, 'Jane Doe', 60000, 1);
INSERT INTO employees VALUES(3, 'Robert Smith', 70000, 2);
CREATE VIEW dept1_employees5 AS
SELECT id, count(*) cnt
FROM employees
group by id;
INSERT INTO dept1_employees5 (id)
VALUES (4);
This will not cause an error. Otherwise DELETE statement will:
SQL> delete from dept1_employees5 where id=4;
delete from dept1_employees5 where id=4
*
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view
In my opinion correct answer is AFG

Question 26:

Which two are true about aggregate functions? (Choose two.)

A. You can use aggregate functions in any clause of a SELECT statement.

B. You can use aggregate functions only in the column list of the SELECT clause and in the WHERE clause of a SELECT statement.

C. You can mix single row columns with aggregate functions in the column list of a SELECT statement by grouping on the single row columns.

D. You can pass column names, expressions, constants, or functions as parameters to an aggregate function.

E. You can use aggregate functions on a table, only by grouping the whole table as one single group.

F. You cannot group the rows of a table by more than one column while using aggregate functions.

Answer: C,D

Question 27:

Which two statements are true about the PMON background process? 

A.It rolls back transactions when a process fails

B.It registers database services with all local and remote listeners known to the database instance 

C.It frees unused temporary segments

D.It frees resources held by abnormally terminated processes 

E.It records checkpoint information in the control file

AD

Question 28:

Which two are true about reclaiming space used by Flashback logs in Oracle Database 19c and later releases?

A)Space is only reclaimed when there is space pressure in the Fast Recovery Area(FRA)

B)Space is always reclaimed automatically when the retention period for Flashback logs is lowered

C)Space might be reclaimed proactively before space pressure occurs 

D)Space is always reclaimed proactively before space pressure occurs

E) Space might be reclaimed automatically when the retention period for Flashback logs  is lowered

CE

Question 29:

Which two are true about server-generated alerts? 

A)Stateless alerts can be cleared manually

B)Stateless alerts are automatically cleared after one day.

C)Stateful alerts are purged automatically from the alert history after one day 

D)Stateless alerts can be purged manually from the alert history.

E) Stateful alerts must be cleared by a DBA to resolve the problem identified in the alert.

AD

Question 30:

Which three are true about upgrading Oracle Grid Infrastructure?

A)The newer version is installed in a separate Oracle Grid Infrastructure home on the same server as the existing version

B)A direct upgrade can be performed only from the immediately preceding Oracle Grid Infrastructure version

C)The upgrade process will automatically install all mandatory patches for the current version of Oracle Grid Infrastructure

D)Only the grid user can perform the upgrade

E)Existing Oracle Database instances must be shut down before starting the upgrade 

F）An existing Oracle base can be used.

AEF

Question 31:

Which two are true about the Program Global Area(PGA)and its management in an Oracle database instance?

A)PGA_AGGREGATE_LIMIT is a hard limit on the PGA size for any one session

B)The entire PGA is located in the System Global Area(SGA)when using shared servers

C)The private SQL area(UGA)is located in the System Global Area(SGA)when using shared servers

D)Sorts and Hash Joins use PGA memory.

E) The private SQL area(UGA)is located in the System Global Area(SGA)when using dedicated servers

CD

Question 32:

You plan to perform cross-platform PDB transport using XTTS. Which two are true?

A)A backup of the PDB must exist, taken using the BACKUP command with the TO PLATFORM clause

B) The source PDB can be in MOUNT or OPEN state 

C)The source PDB must be in MOUNT state.

D)The source PDB must not be an application root.

E)Automatic conversion of endianess occurs.

F)The source and target platforms must have the same endianess

BF

Question 33:

Which two are true about the automatic execution of operating system scripts when performing silent mode installation starting from Oracle Database 19c?

A) The installer will prompt for the root or sudo password 

B) Silent install always runs operating scripts automatically.

C) The response file can specify the root or sudo password 

D)The response file must contain the root or sudo password

E) The response file can specify the path of the sudo program

AE

Question 34:

Which three statements are true about connection strings and service names used to connect to an Oracle database instance?

A.A connection string must include the SID of a database instance.

B.A single connection string can refer to multiple database instances

C.A connection string including a service name must be defined in the tnsnames.ora file

D.A service name is created by a listener

E.Different connection strings in the same tnsnames. ora file can contain the same service name host and port parameters

F. A single database instance can support connections for multiple service names

BEF

Question 35:

Which two statements are true about substitution variables?

A.A substitution variable used to prompt for a column name must be enclosed in double quotation marks.

B.A substitution variable prefixed with s always prompts only once for a value in a session

C.A substitution variable used to prompt for a column name must be enclosed in single quotation marks

D.A substitution variable prefixed with && prompts only once for a value in a session unless it is set to undefined in the session

E.A substitution variable can be used only in a SELECT statement

F.A substitution variable can be used with any clause in a select statement

DF

Question 36:

Which two statements are true about the configuration and use of UNDO RETENTION with no GUARANTEED RETENTION?

A.Unexpired UNDO is always retained

B.UNDO RETENTION specifies for how long Oracle attempts to keep unexpired UNDO 

C.UNDO_RETENTION specifies for how long Oracle attempts to keep expired and unexpired UNDO.

D.UNDO RETENTION specifies how long all types of UNDO are retained 

E.Active UNDO is always retained

BE

Question 37:

Which two statements are true about single row functions?

A.FLOOR: retums the smallest integer greater than or equal to a specified number

B.CEIL: can be used for positive and negative numbers

C.CONCAT: can be used to combine any number of values 

D.TRUNC: can be used only with NUMBER data types 

E.MOD: returns the remainder of a division operation

BE