Oracle 1z0-074 Free Practice Questions

NEW QUESTION 1
In an Oracle 12c Release 2 database,USER1hasCREATE SESSION,CREATE PROCEDURE, andCREATEFUNCTIONprivileges.
Examine these commands:

SYSexecutes this command:
SQL> SELECT add_num(1,2) FROM DUAL;
What is the result?

• A. An error is returned
• B. The message “The result is 3” only is displayed
• C. TESTDBAis created with theSYSDBAprivilege only
• D. Only “3” is displayed
• E. TESTDBA is created will theSYSDBAprivilege and the message “The result is 3” is displayed

Answer: C

NEW QUESTION 2
Which two statements are true about FLEX ASM? (Choose two.)

• A. A database instance on any node can connect to an ASM instance on any node.
• B. A separate ASM network is required.
• C. It is impossible to connect to a database instance on one node if the database password file for the same database is corrupt on another node in the cluster.
• D. Database instances that depend on an ASM instance that is terminated fail over to a node that has a surviving ASM instance.
• E. Database instances that depend on an ASM instance that is terminated connect to a surviving ASMinstance.

Answer: DE

NEW QUESTION 3
One of your databases supports an OLTP workload.
If a better plan is discovered for a statement, then you want that plan to be used. Which two can cause better plans to be used for a statement? (Choose two.)

• A. adaptive cursor sharing
• B. using literals in SQL statements
• C. SQL Access Advisor
• D. SQL Performance Analyzer
• E. adaptive execution plans

Answer: CE

NEW QUESTION 4
Which statement is true about policy expressions?

• A. A named policy expression is stored in the Data Masking format library.
• B. A named policy expression applied to a column in a redacted table overrides the default expression of its redaction policy.
• C. A redacted table can have only one policy expression defined on it.
• D. A policy expression can be applied to columns in different redacted tables.
• E. A named policy expression is stored in the Enterprise Manager Database Express repository.

Answer: C

NEW QUESTION 5
Which two are true about the ADR retention advisor in Oracle Database 12c Release 2? (Choose two.)

• A. ESTIMATE (SHORTP_POLICY = 192)estimates the space requirement based on theSHORTP_POLICYvalue of 192 minutes.
• B. ESTIMATE (SHORTP_POLICY = 192, LONGP_POLICY = 2160)estimates the space requirement based on current ADR diagnostics file space usage.
• C. ESTIMATE (SIZEP_POLICY = 209715200)estimates only the long retention policy.
• D. ESTIMATE (SHORTP_POLICY = 192, LONGP_POLICY = 8760)provides estimates that users can use to setSIZEP_POLICY.
• E. ESTIMATE (SIZEP_POLICY = 209715200)estimates the value ofLONGP_POLICY.

Answer: DE

NEW QUESTION 6
TheEMP_CDB1andEMP_CDB2CDBs are in ARCHIVELOG mode. Oracle Managed Files is enabled in both CDBs.
EMP_CDB1has a PDB,EMP_PDB1.
InEMP_CDB2, you log in as theSYSuser. You plan to execute this statement:
CREATE PLUGGABLE DATABASE emp_pdb2 FROM emp_pdb1@emp_link RELOCATE; Which two statements are true? (Choose two.)

• A. TheAVAILABILITY MAXclause should be added to the command to ensure transaction consistency during the relocation process.
• B. SYSOPERprivilege should be granted to the user defined inEMP_LINKand used to connect to the remotedatabase.
• C. EMP_PDB2should be opened in read-write mode to complete the relocation process.
• D. EMP_PDB1should be dropped manually fromEMP_CDB1after executing the relocating command.
• E. EMP_LINKshould exist as a public link inEMP_CDB1andEMP_CDB2.
• F. OnlyEMP_PDB1should be in local undo mode.

Answer: CE

NEW QUESTION 7
Examine this command:

Which statement is true?

• A. Up to four worker processes are allocated to export data and metadata
• B. Four worker processes are allocated to export only data
• C. Four worker processes are active to export only metadata
• D. Four worker processes are allocated to export data and metadata
• E. Four worker processes are allocated when there is sufficient CPU available

Answer: D

NEW QUESTION 8
Which three resources can be controlled or managed by using Oracle Database Resource Manager? (Choose three.)

• A. number of parallel queries issued by a consumer group
• B. maximum number of DML operations performed by a consumer group
• C. sessions that are blocking other sessions
• D. sessions that are not blocking other sessions
• E. UNDO quota for a consumer group

Answer: ACE

NEW QUESTION 9
Your CDB has a PDB,EMP_PDB.
You successfully execute these commands as theSYSTEM USERinCDB$ROOT:

Which two statements are true aboutC##USR1?(Choose two.)

• A. It can connect toEMP_PDBand view the data only in tables created by common users.
• B. It can connect toEMP_PDBbut cannot view the data in any table in it.
• C. It can connect only to the root.
• D. It is a common user.
• E. It is a local user.

Answer: AE

NEW QUESTION 10
Which three statements are true about the SQL Performance Analyzer? (Choose three.)

• A. It recommends the use of SQL Tuning Advisor for regressed SQL statements.
• B. It recommends the creation of indexes for regressed SQL statements.
• C. It compares and analyzes execution plans for the SQL statements in a SQL Tuning Set (STS).
• D. It always executes triggers that are fired recursively inFULLDMLmode.
• E. It generates execution plans for each SQL statement in an STS.

Answer: ACE

NEW QUESTION 11
Which two are true about tables recovered by using theRMAN RECOVER TABLEcommand or the command itself, starting from Oracle Database 12c Release 2? (Choose two.)

• A. They may be recovered to a schema in a different PDB
• B. AnAUXILIARYinstance is started only if there is sufficient disk space
• C. They can be recovered by RMAN without anAUXILIARYinstance
• D. They must be un-partitioned tables
• E. They may be recovered to a different schema in the same pluggable database (PDB)

Answer: BE

NEW QUESTION 12
While performing a cross-platform PDB transport in an Oracle Database 12c Release 2 database, by usingXTTSfrom aLinux (x86-64)server to aSolaris (x86-64)server, this command is executed to back up the files in a compatible format:

What will be the outcome?

• A. It creates the/tmp/pdb_orcl.xmlfile, which contains the metadata and data of the source database.
• B. The backup fails because theUNPLUGoperation for cross-endian platforms is not supported.
• C. It successfully takes a backup but theUNPLUGoperation ofPDB_ORCLfails.
• D. It successfully completes.
• E. It creates a backupset of the source database in the/home/oracle/backupdirectory

Answer: E

NEW QUESTION 13
Identify two types of files that are generated by the Pre-Upgrade Information tool as part of the database upgrade prerequisite checks? (Choose two.)

• A. expdpscripts to export database objects belonging to the SYS schema
• B. a log file containing a list of pre-upgrade patches to be applied
• C. RMANdatabase backup scripts
• D. a log file with tool recommendations and requirements
• E. pre- and post-upgrade fixup scripts

Answer: DE

NEW QUESTION 14
You execute this command in your database:
ALTER SYSTEM SET ENCRYPT_NEW_TABLESPACES = DDL;
How does this affect the creation of tablespaces?

• A. User tablespaces with noENCRYPTIONclause specified in theCREATE TABLESPACEstatement are not encrypted anywhere.
• B. TEMPandUNDOtablespaces are transparently encrypted with AES128 by default only in the cloud.
• C. TEMPandUNDOtablespaces are transparently encrypted with AES128 by default in an on-premise database.
• D. User tablespaces with noENCRYPTIONclause specified in theCREATE TABLESPACEstatement are transparently encrypted with AES128 only in an on-premise database.
• E. User tablespaces with noENCRYPTIONclause specified in theCREATE TABLESPACEstatement are transparently encrypted with AES128 only in the cloud.

Answer: B

NEW QUESTION 15
In a database on an Oracle Exadata Database machine, a DBA executes this command: SQL> exec DBMS_INMEMORY_ADMIN.ENABLE_FASTSTART (‘fs_tbs’);
What is the result?

• A. All columns for all tables in theFS_TBStablespace are loaded into the In-Memory column store.
• B. Any data in the In-Memory column store is copied in compressed columnar format to theFS_TBS tablespace.
• C. Any data in the In-Memory column store is copied in uncompressed columnar format to theFS_TBS tablespace.
• D. Any data in the In-Memory column store is copied in row major format to theFS_TBStablespace.

Answer: A

NEW QUESTION 16
APP1is an application container that has an application seed and two application PDBs,APP_PDB1andAPP_PDB2.
1.1 is the current version of the application. You execute this block in the application root:

What is the outcome?

• A. An error is thrown because the version number should be sequential, either 1.1 or 2.0.
• B. An error is thrown because theCONTAINER=ALLclause is missing.
• C. An error is thrown because thec##prefix is missing in the role name.
• D. A common role is created only in the application root.
• E. A common role is created only in the application root and application seed.
• F. A common role is created in the application root, application seed,APP_PDB1, andAPP_PDB2.

Answer: D

NEW QUESTION 17
In your CDB1 container database, theDB_CREATE_FILE_DESTparameter is set to‘/u01/app/oracle/ oradata’.
CDB1has an application container calledREGIONS_APP.
There are no seeds forREGIONS_APPand there are no other application containers. You plan to execute this command.
CREATE PLUGGABLE DATABASE AS SEED ADMIN USER seed_adm IDENTIFIED BY admin_123;
What must you do for the command to execute successfully?

• A. Set the current container toREGIONS_APP
• B. Set the current container toCDB1
• C. Add theFILE_NAME_CONVERTclause to the command
• D. Add theCREATE_FILE_DESTclause to the command
• E. Set thePDB_FILE_NAME_CONVERTparameter to‘/u01/app/oracle/oradata’

Answer: E

NEW QUESTION 18
You plan to execute this command:

Which statement is true and why?

• A. It creates partitionsP1andP2whereCOUNTRYis a single column partition key andCHANNELis a subpartition key.
• B. It returns an error because only one set of multiple values can be specified for each partition.
• C. It returns an error because only a single column key can be used for a list-partitioned table.
• D. It returns an error because theAUTOMATICoption must be specified.
• E. It creates partitionsP1andP2whereCOUNTRYandCHANNELare multi-column partition keys.

Answer: E

NEW QUESTION 19
Which three database objects can have columns stored in the In-Memory (IM) column store? (Choose three.)

• A. subpartitions
• B. clustered tables
• C. materialized views
• D. index-organized tables
• E. heap tables
• F. any SYS owned objects

Answer: BDF

NEW QUESTION 20
U1.T1is in a tablespaceTBS_UNCMPwhich has no default compression andU1.T2is in a tablespace,TBS_CMPwith default compression for query low.
You execute commands:

Which three statements are true? (Choose three.)

• A. The primary key constraints onT1are dropped
• B. T1is in the TBS_UNCMP tablespace
• C. T1is in the TBS_CMP tablespace
• D. T1is in compressed format
• E. T1is in uncompressed format
• F. All indexes onT1remain valid

Answer: ACE

NEW QUESTION 21
A multi-tenant container database (CDB) contains three PDBs.
Which three are prerequisites for performing a cross-platform transport of any of the PDBs? (Choose three.)

• A. The CDB should be inMOUNTstate.
• B. The source and target platforms must have the same endianness.
• C. TheCOMPATIBLEparameter must be set to 12.2 or greater in the CDB.
• D. The source PDBs must be closed for a cross-platform PDB backup.
• E. Flashback database must be enabled for the CDB.
• F. The CDB must use Automatic Memory Management.

Answer: BCD

NEW QUESTION 22
InTESTDB, U1hasSYSDBAprivileges and the passwordOracle_4U. You recreate the password file:
$ orapwd FILE=orapwtestdb FORMAT=12.2 password=Oracle_123 ENTRIES=5 Examine the command and result:
$ sqlplus u1/Oracle_4U@testdb as sysdba ERROR:
ORA-01017: invalid username/password; logon denied What can you do to fix this error?

• A. Recreate the password file with the password set toOracle_4U.
• B. Change the password ofU1 to Oracle_123.
• C. Set theREMOTE_LOGIN_PASSWORDFILEparameter value to NONE.
• D. RegrantSYSDBA to U1.
• E. Restart the database instance.

Answer: A

NEW QUESTION 23
Examine these commands executed in thecdb1container database:

Which statement is true about thec##USER1account?

• A. It is locked for two days after the password expires.
• B. It is locked if the password is not changed within three weeks.
• C. It is locked for 90 minutes after four failed login attempts.
• D. It is locked after for consecutive failed login attempts.
• E. It is not affected by the profile because the user has theSYSDBAprivilege.

Answer: D

NEW QUESTION 24
You receive complaints about poor performance in an Oracle 12c database while long running queries execute.
You must obtain statistics such as elapsed time, CPU time, number of reads and writes, I/O wait time, and other wait times for each step of each execution plan to help diagnose the problem during query execution.
How would you achieve this?

• A. by viewing Active Reports for that time period
• B. by enabling SQL trace for each query
• C. by running the Automatic Workload Repository (AWR) report for that time period
• D. by running the Active Session History (ASH) report for that time period
• E. by enabling tracing for sessions that run these queries

Answer: E

NEW QUESTION 25
You notice a large number oflatch: cache buffers lru chain and latch: cache buffers chainswait events.
Which four might be reasons for these? (Choose four.)

• A. The same small number of blocks are repeatedly accessed by different sessions simultaneously.
• B. The buffer cache is undersized.
• C. Cursors are closed explicitly after each execution.
• D. A large number of connected user sessions are idle.
• E. DBWR is not writing the dirty buffers fast enough.
• F. Blocks are ageing out of the buffer cache frequently.
• G. Too many sort operations are being performed.

Answer: AEFG

NEW QUESTION 26
......