-
web.groovymark@gmail.com
- December 5, 2024
Question 01
Which statement is true regarding the SYSTEM tablespace?
A) It can be made read-only.
B) It can be offline.
C) Data files can be renamed.
D) Data files cannot be resized.
Correct Answer: C) Data files can be renamed.
Explanation: The data files belonging to the SYSTEM tablespace can be renamed when the database is in the MOUNT state by using the ALTER DATABASE RENAME FILE statement.
Question 02
The default critical threshold for a tablespace is set at 97 percent, and you think that is too low. Which two options can you use to change the threshold value to 90 percent for tablespace APPS_DATA?
A) Use Oracle Enterprise Manager Database Express.
B) Use Oracle Enterprise Manager Cloud Control.
C) Use DBMS_SERVER_ALERT package.
D) Use DBMS_SPACE package.
Correct Answer: B, C) Use Oracle Enterprise Manager Cloud Control; Use DBMS_SERVER_ALERT package.
Explanation: OEM Database Express does not have an option to adjust the server-managed alerts in Database 12c Release 1. OEM Cloud Control and DBMS_SERVER_ALERT.SET_THRESHOLD can be used to set the threshold for tablespaces.
Question 03
Choose the statements that are resumable. (Choose three.)
A) ALTER TABLE … SPLIT PARTITION
B) SELECT
C) INSERT INTO … SELECT
D) CREATE TABLESPACE
E) ALTER TABLE … SHRINK SPACE
Correct Answer: A, B, C) ALTER TABLE ... SPLIT PARTITION; SELECT; INSERT INTO ... SELECT.
Explanation: Resumable operations include SQL statements like ALTER TABLE ... SPLIT PARTITION, SELECT, and INSERT INTO ... SELECT. ALTER TABLE ... SHRINK SPACE is not resumable because it does not involve extents.
Question 04
How do you ensure you are notified when a resumable session is suspended? (Choose two.)
A) You’ll be notified by email at the address registered in database properties.
B) Write a custom script to look for “statement in resumable session was suspended” in the alert log and notify the DBA.
C) Create an AFTER SUSPEND trigger and a code notification.
D) A suspended session displays a message on the screen.
Correct Answer: B, C) Write a custom script to look for "statement in resumable session was suspended" in the alert log and notify the DBA; Create an AFTER SUSPEND trigger and a code notification.
Explanation: There is no built-in notification mechanism for suspended resumable sessions. However, an AFTER SUSPEND trigger and custom alert monitoring can be set up.
Question 05
Which statement regarding reclaiming wasted space is true
A) Segment shrink is accomplished using the ALTER TABLE … MOVE and ALTER INDEX … REBUILD statements.
B) Segment shrink and reorganize are similar operations.
C) When a table segment shrink operation is completed, the dependent indexes are in an invalid state and need to be rebuilt.
D) A segment shrink operation is applicable only on tablespaces with automatic segment space management.
Correct Answer: D) A segment shrink operation is applicable only on tablespaces with automatic segment space management.
Explanation: The segment shrink operation is online and applicable only to tablespaces with automatic segment space management.
Question 06
Which compression option should be specified to compress blocks of tables that are used by the OLTP application?
A) COMPRESS
B) NOCOMPRESS
C) COMPRESS FOR OLTP
D) COMPRESS FOR ONLINE DML
Correct Answer: C) COMPRESS FOR OLTP
Explanation: The COMPRESS FOR OLTP option enables compression for OLTP applications, which helps to reduce storage without significant performance impact.
Question 07
Choose the best option regarding extents.
A) An extent is a grouping of Oracle blocks.
B) An extent is a grouping of OS blocks.
C) An extent is a grouping of segments.
D) An extent is allocated when a table is created.
Correct Answer: A) An extent is a grouping of Oracle blocks.
Explanation: An extent consists of one or more contiguous Oracle blocks. An extent is allocated when data is inserted into a table.
Question 08
You issue the statement CREATE TABLESPACE X. Which of the following is the best option?
A) The statement fails because mandatory properties are not defined.
B) The 100MB tablespace is created.
C) The mandatory DATAFILE clause is missing.
D) The tablespace name should be at least three characters long.
Correct Answer: B) The 100MB tablespace is created.
Explanation: The statement creates a 100MB tablespace with default properties. The DATAFILE clause is optional in this case.
Question 09
Choose the information that is not part of a ROWID.
A) Data object number
B) Relative file number
C) Segment ID where the block belongs
D) Block number in the file
Correct Answer: C) Segment ID where the block belongs.
Explanation: ROWID includes the data object number, relative file number, block number, and the row number, but not the segment ID.
Question 10
Which of the following files must be present on the Oracle server to start a nondefault Oracle listener?
A) listener.ora
B) lsnrctl.ora
C) sqlnet.ora
D) tnsnames.ora
Correct Answer: A) listener.ora
Explanation: The listener.ora file is required to configure and start a nondefault listener. The sqlnet.ora and tnsnames.ora files are optional and used for network configuration.
Question 11
Which of the following is the correct way to start a listener called LISTENER?
A) lsnrctl startup listener
B) lsnrctl start
C) netca start
D) netmgr start listener
Correct Answer: B) lsnrctl start
Explanation: The correct command is lsnrctl start. The name LISTENER is assumed by default, so specifying the name is not necessary unless using a custom listener.
Question 12
When dynamic service registration is used, you will not see the service listed in which of the following files where it would normally be located?
A) sqlnet.ora
B) tnsnames.ora
C) listener.ora
D) None of the above
Correct Answer: C) listener.ora
Explanation: With dynamic registration, services do not need to be manually added to the listener.ora file. Oracle automatically registers services with the listener.
Question 13
Connection Manager provides which of the following?
A) Multiplexing
B) Cross-protocol connectivity
C) Network access control
D) All of the above
Correct Answer: D) All of the above
Explanation: Oracle Connection Manager is a middleware solution that supports multiplexing, cross-protocol connectivity, and network access control.
Question 14
Which is a requirement for using host naming?
A) You must use tnsnames.ora on the client.
B) You must be using TCP/IP.
C) You must have an OID present.
D) You must have a sqlnet.ora file present on the client.
Correct Answer: B) You must be using TCP/IP.
Explanation: Host naming requires TCP/IP, and it is typically used in smaller environments to minimize client-side configuration.
Question 15
Which statements regarding Database Resident Connection Pooling (DRCP) are true? (Choose two.)
A) When a DRCP pooled server connection is made by an application, it is equivalent to a dedicated server connection.
B) When a database has DRCP enabled, all connections default to a pooled connection unless DEDICATED server is explicitly specified in the connect string.
C) When an application using the DRCP connection disconnects, the server connection (process) is handed off to the broker.
D) When using the Oracle Net Easy Connect method, it is not possible to utilize DRCP.
Correct Answer: A, C) When a DRCP pooled server connection is made by an application, it is equivalent to a dedicated server connection; When an application using the DRCP connection disconnects, the server connection (process) is handed off to the broker.
Explanation: DRCP provides connection pooling on the server-side, and when a connection is no longer needed, it is returned to the pool for reuse.
Question 16
A client receives the following error message: “ORA-12154 TNS
not resolve the connect identifier specified.” Which of the following could be possible causes of the error? (Choose all that apply.)
A) The listener is not running on the Oracle server.
B) The user entered an invalid net service name.
C) The user supplied the correct net service name, but the net service name is misspelled in the tnsnames.ora file on the client file.
D) The listener is not configured to listen for this service.
Correct Answer: B, C) The user entered an invalid net service name; The user supplied the correct net service name, but the net service name is misspelled in the tnsnames.ora file on the client file.
Explanation: ORA-12154 occurs when there is an issue with the TNS service name resolution. This can happen if the name is incorrect or misspelled in the configuration files.
Question 17
What portion of the tnsnames.ora file specifies the name or IP address of the server where the listener process is listening?
A) CONNECT_DATA
B) SERVER
C) SERVICE_NAME
D) HOST
Correct Answer: D) HOST
Explanation: The HOST field specifies the server name or IP address where the listener process is running.
Question 18
A client wants to connect to the database service dbprod.com located on the dbprod.com server through a nondefault port (1522) using Oracle Easy Connect. Which of the following connect strings are the choices for the client to use? (Choose two.)
A) CONNECT scott/tiger@dbprod.com:1522
B) CONNECT scott/tiger@1522
.com/dbprod.com
C) CONNECT scott/tiger@//dbprod.com/1522.com
D) CONNECT scott/tiger@dbprod.com:1522/dbprod.com
Correct Answer: A, D) CONNECT scott/tiger@dbprod.com:1522; CONNECT scott/tiger@dbprod.com:1522/dbprod.com.
Explanation: The correct Easy Connect syntax to connect to a nondefault port requires specifying the port number after the server name or IP address.
Question 19
All of the following are reasons to configure the server using Shared Server except which one?
A) Overall memory utilization is reduced.
B) The system is predominantly used for decision support with large result sets returned.
C) The system is predominantly used for small transactions with many users.
D) The number of idle connections on the server is reduced.
Correct Answer: B) The system is predominantly used for decision support with large result sets returned.
Explanation: Shared Server is designed for systems with many users and small transactions. It is not ideal for decision support systems that involve large result sets.
Question 20
Which of the following is true about Shared Server?
A) Dedicated connections cannot be made when Shared Server is configured.
B) It is recommended that DSS type batch jobs be performed when connected via Shared Server.
C) The database can be started when connected via Shared Server.
D) The database cannot be stopped when connected via Shared Server.
Correct Answer: D) The database cannot be stopped when connected via Shared Server.
Explanation: Users can still request dedicated connections in a Shared Server configuration, but administrative tasks such as stopping the database require a dedicated connection.
