You execute this command:
$expdp system/oracle \
> FULL=Y \
> DUMPFILE = exp_dp_full.dmp \
> PARALLEL = 4 \
> LOGFILE=exp_dp_full.log \
> JOB_NAME=exp_dp_full
Question 51.
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
Correct Answer: BC
Question 52.
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)
Correct Answer: ADE
A - TRUE; Release 19, Database Administrator’s Guide, 17.3.3.6 CREATE DATABASE Statement Using Oracle Managed Files: Examples
B - FALSE; Release 19, Database Administrator’s Guide, 19.3.3 Shrinking Database Segments Online; Shrink operations can be performed only on segments in locally managed tablespaces with automatic segment space management (ASSM). Within an ASSM tablespace, all segment types are eligible for online segment shrink except these: IOT mapping tables, Tables with rowid based materialized views,...
C - FALSE; see B
D - TRUE; no restriction for autoextension mentioned with DMT in Oracle documentation
E - TRUE; see D
Question 53.
Examine the paremeters
memory_max_target 0
memory_target 0
sga_max_size 2G
sga_target 2G
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
Correct Answer: D
Unlike an SPFILE, the database server cannot write to and alter a PFILE. Therefore, to change parameter values in a PFILE and make them persist during shutdown and startup, you must manually edit the PFILE in a text editor and restart the database instance to refresh the parameter values.
When starting the database using a spfile you have three options for the scope (MEMORY, SPFILE and BOTH). Default value is BOTH.
Question 54.
Examine the description of the SALES1 table:
SALES_ID NUMBER
STORE_ID NUMBER
ITEMS_ID NUMBER
QUANTITY NUMBER
SALES_DATE DATE
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
Correct Answer: E
Question 55.
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
Correct Answer: ADE
Question 56.
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
Correct Answer: BE
Discussion: Refer 12c Database administrator I for detail under database instance background process and appendices, also refer 19c SQL workshop I under process Architecture
A is incorrect: LREG do the registration not PMON. A would be correct for oracle 11g version but for above version 12c LERG do the registration not PMON
B is correct: PMON frees resources that are used by user process
C is incorrect: CKPT record checkpoint information in control file and each datafile header not PMON
D is incorrect: SMON cleans up unused temporary segments
E is correct: PMON monitors sessions for idle session timeout
Question 57.
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
Discussion: Refer 19c Database administrator I for detail under temporary tables
Correct Answer: AB
A is correct: A global temporary table definition is visible to all sessions. However, the content in the table is specific to a session. Global temporary table definitions are stored on disk. A private temporary table definition is visible only to the session that created it. The table definition is stored only in memory.
B is correct: When an unusable index is created, no segment is created
C is incorrect: There are two types of durations for private temporary tables:
Transaction: The private temporary table is automatically dropped when the transaction in which it was created ends with either a ROLLBACK or COMMIT. This is the default behaviour if no ON COMMIT clause is defined at private temporary table creation.
Session: The private temporary table is automatically dropped when the session that created it ends. This is the behaviour if the ON COMMIT PRESERVE DEFINITION clause is defined at the private temporary table creation.
D is incorrect: see B
E is incorrect: TRUNCATE TABLE statement: Deallocates all space used by the removed rows except that specified by the MINEXTENTS storage parameter
Question 58.
You want to use table compression suitable for OLTP that will:
1. Compress rows for all DML statements on that table
2. Minimize the overheads associated with compression
Which compression option is best suited for this?
A. 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
Correct Answer: E
When you enable table compression by specifying ROW STORE COMPRESS ADVANCED, you enable Advanced Row Compression. Oracle Database compresses data during all DML operations on the table. This form of compression is recommended for OLTP environments.
Question 59.
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
Correct Answer: BCD
Question 60.
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
Correct Answer: BDEF
Question 61.
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
Correct Answer: BC
Question 62.
Which three instance situations are possible with the Oracle Database server without multi-tenant? (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
Correct Answer: ABD
A - TRUE; Oracle RAC
B - TRUE; typical configuration for standalone databases
C - FALSE; valid relation between database and instance is 1:N
D - TRUE; standalone database in NOMOUNT/MOUNTED state
E - FALSE; see A
Question 63.
CREATE SEQUENCE ord_seq
INCREMENT BY 1
START WITH 1
MAXVALUE 10000
CYCLE
CACHE 5000;
CREATE TABLE ORD_ITEMS
(
ord_no NUMBER DEFAULT ord_seq.NEXTVAL NOT NULL,
item_no NUMBER(3),
qty NUMBER(3),
exp_date DATE
CONSTRAINT it_pk PRIMARY_KEY(ord_no, item_no));
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
Correct Answer: BC
Question 64.
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
Characteristics of Bitmap Segment Management
Correct Answer: BD
Bitmap space management uses four bits inside each data block header to indicate the amount of available space in the data block. Unlike traditional space management with a fixed relink and unlink threshold, bitmap space managements allow Oracle to compare the actual row space for an INSERT with the actual available space on the data block. This enables better reuse of the available free space especially for objects with rows of highly varying size. Here are the values inside the four-bit space:
Value Meaning
0000 Unformatted Block
0001 Block is logically full
0010 <25% free space
0011 >25% but <50% free space
0100 > 50% but <75% free space
0101 >75% free space
A is wrong for obvious reasons. All segments include (tables, indexes, undo and temp). On top of that "all" compression is mentioned in the answer.
B is correct. This is a well known feature of ASSM and can be queried via the dbms_space.SPACE_USAGE procedure.
each block is divided into four sections, named FS1 (between 0 and 25% of free space), FS2 (25% to 50% free), FS3 (50% to 75% free), and FS4 (75% to 100% free)
C is wrong. The key to this answer is "always". If a block has sufficient free space to accomodate the row update then Oracle does not need to search for a different block with enough free space.
D is correct. Oracle does this by scanning the BMB in the segment to determine a block that can store the entire row. The main purpose here is to avoid row chanining.
E is wrong. It all depends what the PCTFREE value is for the segment. A block with a PCTFREE of 10% could have rows that uses 90% of the available space, that block is deemed full and not eligible for insert operations
Question 65.
Which three statements are true about dropping 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
Correct Answer: ADF
A - TRUE; e.g. ALTER TABLE departments DROP COLUMN deptno CASCADE CONSTRAINTS;
B - FALSE; In Oracle Database 19c, the space occupied by an unused column is automatically reclaimed when the row containing that column is next updated. The space is released and becomes available for reuse within the block.
C - FALSE; see B
D - TRUE; partition key column cannot be dropped. When an attempt to drop the column is made, an error "ORA-12984: cannot drop partitioning column" occurs.
E - FALSE; DDL commands cannot be rolled back
F - TRUE; ALTER TABLE documentation says "Until you actually drop these columns, they continue to count toward the absolute limit of 1000 columns in a single table."
Question 66.
ALTER DATABASE
MOVE '/u01/sales.dbf' TO '/u01/sales01.dbf' REUSE;
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
Correct Answer: AE
Question 67.
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_%’;
Correct Answer: A
Question 68.
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, &&col2
FROM &table
WHERE &&condition = &&cond;
B. SELECT &col1, &col2
FROM &&table
WHERE &condition;
C. SELECT &col1, &col2
FROM "&table"
WHERE &condition;
D. SELECT ‘&&col1’, ‘&&col2’
FROM &table
WHERE ‘&&condition’ = ‘&cond’;
E. SELECT &&col1, &&col2
FROM &table
WHERE &&condition;
Correct Answer: B
Question 69.
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 &&
Correct Answer: CE
The SET VERIFY ON command will enable the display of any substitution variable used in our last executed SQL statement. When you use the DEFINE clause to create a variable, and the variable is not used in the next SQL statement then that variable will not be "shown" by the SET VERIFY command. Oh, and the command is available in all apps that allow the execution of SQL language against your database.
Question 70.
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
Correct Answer: BEF
A - FALSE; use OLD syntax, e.g. SELECT ... FROM employees e, employees m WHERE e.mgr=m.empno ...
B - TRUE; when omitting, the error "ORA-00918: column ambiguously defined" appears
C - FALSE; e.g. SELECT ... FROM employees e1, employees e2 WHERE e1.msal > e2.msal
D - FALSE; valid query is e.g. SELECT e1.empno,e1.ename,e2.ename ... FROM employees e1 RIGHT OUTER JOIN employees e2 ON e1.mgr = e2.empno;
E - TRUE; valid query is e.g. SELECT e.ename employee,e.bdate birth, m.ename manager FROM employees e JOIN employees m ON e.mgr = m.empno;
F - TRUE; see answer B
Question 71.
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
Correct Answer: ACF
Question 72.
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
Correct Answer: DE
Question 73.
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
ACF
A is correct. Bigfile tablespace have only one datafile and smallfile tablespace have several datafiles.
B is wrong. The extents of a segment could reside in the some others datafiles in the same tablepsace
C is correct. The difference between smallfile tablespace and bigfile tablespace is the number of datafile. The max size of datafile is same.
D is wrong. A segment can span multiple data files in same tablespace, but not in some tablespaces
E is wrong. The block size can be defined in tablespace in creation.
F is correct.
G is wrong. Refer to D
Question 74.
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:
LISTENER_1 = (ADDRESS=(PROTOCOL=TCP)
(HOST=host1.abc.com)
(PORT=1521))
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.
Correct Answer: B
Question 75.
You have been tasked to create a table for a banking application.
One of the columns must meet three requirements:
1. Be stored in a format supporting date arithmetic without using conversion functions
2. Store a loan period of up to 10 years
3. Be used for calculating interest for the number of days the loan remains unpaid
Which data type should you use?
A. INTERVAL YEAR TO MONTH
B. INTERVAL DAY TO SECOND
C. TIMESTAMP WITH LOCAL TIMEZONE
D. TIMESTAMP
E. TIMESTAMP WITH TIMEZONE
Correct Answer: B
No comments:
Post a Comment