Oracle Database 12c: Installation and Administration
Question No: 121
Examine the contents of SQL loader control file:
Which three statements are true regarding the SQL* Loader operationperformed using the control file?
An EMP table is created if a table does not exist. Otherwise, if the EMP table is appended with the loaded data.
The SQL* Loader data file myfile1.dat has the column names for the EMP table.
The SQL* Loader operation fails because no record terminators are specified.
Field names should be the first line in the both the SQL* Loader data files.
The SQL* Loader operation assumes that the file must be a stream record format file with the normal carriage return string as the record terminator.
Answer: A,B,E Explanation:
A: The APPEND keyword tells SQL*Loader to preserve any preexisting data in the table. Other options allow you to delete preexisting data, or to fail with an error if the table is not empty to begin with.
B (not D): Note:
* SQL*Loader-00210: first data file is empty, cannot process the FIELD NAMES record Cause: The data file listed in the next message was empty. Therefore, the FIELD NAMES
FIRST FILE directive could not be processed.
Action: Checkthe listed data file and fix it. Then retry the operation
A comma-separated values (CSV) (also sometimes called character-separated values, because the separator character does not have to be a comma) file stores tabular data (numbers and text) in plain-text form. Plain text means that the file is a sequence of characters, with no data that has to be interpreted instead, as binary numbers. A CSV file consists of any number of records, separated by line breaks of some kind; each record consists of fields, separated by some other character or string, most commonly a literal comma or tab. Usually, all records have an identical sequence of fields.
Fields with embedded commas must be quoted. Example:
1997,Ford,E350,quot;Super, luxurious truckquot;
SQL*Loader is a bulk loader utility used for moving data from external files into the Oracle database.
Question No: 122
You conned using SQL Plus to the root container of a multitenant container database (CDB) with SYSDBA privilege.
The CDB has several pluggable databases (PDBs) open in the read/write mode. There are ongoing transactions in both the CDB and PDBs.
What happens alter issuing the SHUTDOWN TRANSACTIONAL statement?
The shutdown proceeds immediately. The shutdown proceeds as soon as all transactions in the PDBs are either committed or rolled hack.
The shutdown proceeds as soon as all transactions in the CDB are either committed or rolled back.
The shutdown proceeds as soon as all transactions in both the CDB and PDBs are either committed or rolled back.
The statement results in an error because there are open PDBs.
Answer: B Explanation:
SHUTDOWN [ABORT | IMMEDIATE | NORMAL | TRANSACTIONAL [LOCAL]]
Shuts down a currently runningOracle Database instance, optionally closing and dismounting a database. If the current database is a pluggable database, only the pluggable database is closed. The consolidated instance continues to run.
Shutdown commands that wait for current calls tocomplete or users to disconnect such as SHUTDOWN NORMAL and SHUTDOWN TRANSACTIONAL have a time limit that the SHUTDOWN command will wait. If all events blocking the shutdown have not occurred within the time limit, the shutdown command cancels with the following message:
ORA-01013: user requested cancel of current operation
* If logged into a CDB, shutdown closes the CDB instance.
To shutdown a CDB or non CDB, you must be connected to the CDB or non CDB instance that you want to close, and then enter
Database closed. Database dismounted. Oracle instance shut down.
To shutdown a PDB, you must log into the PDB to issue the SHUTDOWN command. SHUTDOWN
Pluggable Database closed.
* Prerequisites for PDB Shutdown
When the current container isa pluggable database (PDB), the SHUTDOWN command can only be used if:
The current user has SYSDBA, SYSOPER, SYSBACKUP, or SYSDG system privilege. The privilege is either commonly granted or locally granted in the PDB.
The current user exercises the privilege using AS SYSDBA, AS SYSOPER, AS SYSBACKUP, or AS SYSDG at connect time.
To close a PDB, the PDB must be open.
Question No: 123
You upgrade your Oracle database in a multiprocessor environment. As a recommended you execute the following script:
Which two actions does the script perform?
Parallel compilation of only the stored PL/SQL code
Sequential recompilation of only the stored PL/SQL code
Parallel recompilation of any stored PL/SQL code
Sequential recompilation of any stored PL/SQL code
Parallel recompilation of Java code
Sequential recompilation of Java code
Answer: C,E Explanation:
utlrp.sql and utlprp.sql
The utlrp.sql and utlprp.sql scripts are provided by Oracle to recompile all invalid objects in the database. They are typically run after major database changes such as upgrades or patches. They are located in the $ORACLE_HOME/rdbms/admin directory and provide a wrapper on the UTL_RECOMP package. The utlrp.sql script simply calls the utlprp.sql script with a command line parameter of quot;0quot;. The utlprp.sql accepts a single integer parameter that indicates the level of parallelism as follows.
0 – The level of parallelism is derived based on the CPU_COUNT parameter. 1 – The recompilation is run serially, oneobject at a time.
N – The recompilation is run in parallel with quot;Nquot; number of threads.
Both scripts must be run as the SYS user, or another user with SYSDBA, to work correctly. References:
Question No: 124
Oracle Grid Infrastructure for a stand-alone server is installed on your production host before installing the Oracle Database server. The database and listener are configured byusing Oracle Restart.
Examine the following command and its output:
$ crsctl config has
CRS-4622: Oracle High Availability Services auto start is enabled. What does this imply?
When you start an instance on a high with SQL *Plus dependent listenersand ASM disk groups are automatically started.
When a database instance is started by using the SRVCTL utility and listener startup fails, the instance is still started.
When a database is created by using SQL* Plus, it is automatically added to theOracle Restart configuration.
When you create a database service by modifying the SERVICE_NAMES initialization parameter, it is automatically added to the Oracle Restart configuration.
Answer: B Explanation:
About Startup Dependencies
Oracle Restartensures that Oracle components are started in the proper order, in accordance with component dependencies. For example, if database files are stored in
Oracle ASM disk groups, then before starting the database instance, Oracle Restart ensures that the Oracle ASM instance is started and the required disk groups are mounted. Likewise, if a component must be shut down, Oracle Restart ensures that dependent components are cleanly shut down first.
Oracle Restart also manages the weak dependency between databaseinstances and the Oracle Net listener (the listener): When a database instance is started, Oracle Restart attempts to start the listener. If the listener startup fails, then the database is still started. If the listener later fails, Oracle Restart does not shut down and restart any database instances. http://docs.oracle.com/cd/E16655_01/server.121/e17636/restart.htm#ADMIN12710
Question No: 125
You create a table with the PERIOD FOR clause to enable the use of the Temporal Validity feature of Oracle Database 12c.
Examine the table definition:
Which three statementsare true concerning the use of the Valid Time Temporal feature for the EMPLOYEES table?
The valid time columns employee_time_start and employee_time_end are automatically created.
The same statement may filter on both transaction time and valid temporal time by using the AS OF TIMESTAMP and PERIOD FOR clauses.
The valid time columns are not populated by the Oracle Server automatically.
The valid time columns are visible by default when the table is described.
Setting the session valid time using DBMS_FLASHBACK_ARCHIVE.ENABLE_AT_VALID_TIME sets the visibility for data manipulation language (DML), data definition language (DDL), and queries performed by the session.
Answer: A,B,E Explanation:
A: To implement Temporal Validity(TV), 12c offersthe option to have two date columns in that table which is having TV enabled using the new clause Period For in the Create Table for the newly created tables or in the Alter Table for the existing ones. The columns that are used can be defined while creating the table itself and will be used in the Period For clause or you can skip having them in the table’s definition in the case of which, the Period For clause would be creating them internally.
E: ENABLE_AT_VALID_TIME Procedure
This procedure enables session level valid time flashback.
Question No: 126
Identify three valid options for adding a pluggable database (PDB) to an existing multitenant container database (CDB).
Use the CREATE PLUGGABLE DATABASE statement to create a PDB using the files from the SEED.
Use the CREATE DATABASE . . . ENABLE PLUGGABLE DATABASE statement to provision a PDB by copying file from the SEED.
Use the DBMS_PDB package to clone an existing PDB.
Use the DBMS_PDB package to plug an Oracle 12c non-CDB database into an existing CDB.
Use the DBMS_PDB package to plug an Oracle 11 g Release 2 (126.96.36.199.0) non-CDB database into an existing CDB.
Answer: A,C,D Explanation:
Use the CREATE PLUGGABLEDATABASE statement to create a pluggable database (PDB).
This statement enables you to perform the following tasks:
(A) Create a PDB by using the seed as a template
Use the create_pdb_from_seed clause to create a PDB by using the seed in the multitenant container database (CDB) as a template. The files associated with the seed are copied to a new location and the copied files are then associated with the new PDB.
(C) Create a PDB by cloning an existing PDB
Use the create_pdb_clone clause to createa PDB by copying an existing PDB (the source PDB) and then plugging the copy into the CDB. The files associated with the source PDB are copied to a new location and the copied files are associated with the new PDB. This operation is called cloning a PDB.
The source PDB can be plugged in or unplugged. If plugged in, then the source PDB can be in the same CDB or in a remote CDB. If the source PDB is in a remote CDB, then a database link is used to connect to the remote CDB and copy the files.
Create a PDBby plugging an unplugged PDB or a non-CDB into a CDB
Use the create_pdb_from_xml clause to plug an unplugged PDB or a non-CDB into a CDB, using an XML metadata file.
Question No: 127
On your Oracle 12c database, you Issue the following commands to createindexes
SQL gt; CREATE INDEX oe.ord_customer_ix1 ON oe.orders (customers_id, sales_rep_id) INVISIBLE;
SQLgt; CREATE BITMAP INDEX oe.ord_customer_ix2 ON oe.orders (customers_id, sales_rep_id);
Which two statements are correct?
Both the indexes are created; however, only the ORD_COSTOMER index is visible.
The optimizer evaluates index access from both the Indexes before deciding on which index to use for query execution plan.
Only the ORD_CUSTOMER_IX1 index is created.
Only the ORD_CUSTOMER_IX2 index is created.
Both the indexes are updated when a new row is inserted, updated, or deleted In the orders table.
Answer: A,E Explanation:
11G has a new feature called Invisible Indexes. An invisible index is invisible to the optimizer as default. Using this feature we can test a new index without effecting the execution plans of the existing sql statements or we can test the effect of dropping an index without dropping it.
Question No: 128
Which twostatements are true about Automatic Database Diagnostic Monitor (ADDM)?
It calls SQL advisors automatically if required.
It provides recommendations only for poorly performing SQL statements.
Its results are stored in AWR.
It runs automatically after each AWR snapshot is created and requires at least two snapshots for analysis.
It requires at least one Automatic Workload Repository (AWR) snapshot for analysis.
Question No: 129
What is the benefit of running the catctl.pl script during an upgrade of a pre-12c database to an Oracle 12c database?
It provides a summary of the upgrade results.
It recompiles all invalid PL/SQL and Java code.
It generates a log file containing the fixes that can be made to thesource database.
It provides parallel upgrade options to finish the upgrade process with a reduced down time.
It generates fix-up scripts to be run on the source database before upgrade.
Answer: C Explanation:
Question No: 130
Which three factors influence the optimizer#39;s choice of an execution plan?
the optimizer_mode initialization parameter
operating system (OS) statistics
object statistics in the data dictionary
Answer: A,B,D Explanation: References:
|Lowest Price Guarantee||Yes||No||No|
|Free VCE Simulator||Yes||No||No|