50 multiple-choice questions with answers and detailed explanations. Untimed — choose an answer to check it instantly.
0/50 answered0 correct
Question 1 of 50
Which statement is true regarding the SYSTEM tablespace?
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 2 of 50
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?
Correct answer: C) 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 3 of 50
Choose the statements that are resumable. (Choose three.)
Correct answer: C) 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 4 of 50
How do you ensure you are notified when a resumable session is suspended? (Choose two.)
Correct answer: C) 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 5 of 50
Which statement regarding reclaiming wasted space is true
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 6 of 50
Which compression option should be specified to compress blocks of tables that are used by the OLTP application?
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 7 of 50
Choose the best option regarding extents.
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 8 of 50
You issue the statement CREATE TABLESPACE X. Which of the following is the best option?
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 9 of 50
Choose the information that is not part of a ROWID.
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 of 50
Which of the following files must be present on the Oracle server to start a nondefault Oracle listener?
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 of 50
Which of the following is the correct way to start a listener called 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 of 50
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?
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 of 50
Connection Manager provides which of the following?
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 of 50
Which is a requirement for using host naming?
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 of 50
Which statements regarding Database Resident Connection Pooling (DRCP) are true? (Choose two.)
Correct answer: C) 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 of 50
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.)
Correct answer: 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.
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 of 50
What portion of the tnsnames.ora file specifies the name or IP address of the server where the listener process is listening?
Correct answer: D) HOST
Explanation: The HOST field specifies the server name or IP address where the listener process is running.
Question 18 of 50
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.)
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 of 50
All of the following are reasons to configure the server using Shared Server except which one?
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 of 50
Which of the following is true about 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.
Question 21 of 50
The administrator wants to allow a user to connect via a dedicated connection into a database configured in Shared Server mode. Which of the following options accomplishes this?
Correct answer: A) (SERVER=DEDICATED)
Explanation: To force a dedicated server connection in a shared server environment, the connect string must include the option (SERVER=DEDICATED).
Question 22 of 50
In which of the following files would you find the Shared Server configuration parameters?
Correct answer: C) init.ora
Explanation: The Shared Server configuration parameters exist in the init.ora or the SPFILE on the Oracle Server machine.
Question 23 of 50
What is the first step that the dispatcher performs after it receives a request from the user?
Correct answer: C) Place the request in a request queue in the SGA.
Explanation: The dispatcher places the user request in the SGA request queue to be processed by a shared server process.
Question 24 of 50
When configured in Shared Server mode, which of the following is contained in the PGA?
Correct answer: D) Stack space
Explanation: In a Shared Server environment, most user-specific information is stored in the SGA. Only a small amount of stack space remains in the PGA.
Question 25 of 50
Which of the following is false about request queues?
Correct answer: C) Each dispatcher has its own request queue.
Explanation: All dispatchers share a single request queue in the SGA, while each dispatcher has its own response queue.
Question 26 of 50
What is the process that notifies the listener after a database connection is established?
Correct answer: C) LREG
Explanation: The LREG process notifies the listener when a client connection is established. In Oracle versions before 12c, this function was performed by the PMON process.
Question 27 of 50
Which network architecture connection uses session memory from the SGA?
Correct answer: B) Shared Server
Explanation: In a Shared Server environment, session memory is allocated from the SGA, unlike Dedicated Server connections, where memory is allocated from the PGA.
Question 28 of 50
Communication between two Oracle databases is configured using:
Correct answer: B) Database link
Explanation: A database link enables communication between Oracle databases, allowing access to data from remote databases using a connection string.
Question 29 of 50
DBA user MIKE ran the SQL statement CREATE DATABASE LINK SCOTT.SALES_LINK.BJS.COM CONNECT TO SALES_INT IDENTIFIED BY SALESPWD1 USING ‘ocasvr:1522/ocadb’. Choose the option that is true.
Correct answer: B) A private database link is created under user MIKE.
Explanation: The private database link is created under the schema of the user who executes the CREATE DATABASE LINK statement, in this case, MIKE.
Question 30 of 50
Which of the following statements create an Oracle account but let the operating system authenticate logons? (Choose two.)
Correct answer: B) create user ops$admin identified externally;
Explanation: Oracle supports external authentication through the operating system by using the ops$ prefix and specifying identified externally .
Question 31 of 50
If you want to capture the SQL statement and bind variables when performing a standard statement audit, which value should the AUDIT_TRAIL parameter have?
Correct answer: C) DB,EXTENDED
Explanation: The DB,EXTENDED setting for AUDIT_TRAIL captures SQL statements and bind variables during audits.
Question 32 of 50
Which of the following statements give user desmond the ability to alter table gl.accounts?
Correct answer: A) grant alter on gl.accounts to desmond;
Explanation: The correct syntax for granting the ALTER privilege on a specific table is grant alter on gl.accounts to desmond .
Question 33 of 50
Which of the following statements has the correct syntax and gives the ability to grant the privilege to other users?
Correct answer: D) grant alter any table to desmond with admin option;
Explanation: The correct syntax for granting a system privilege with the ability to grant it to others is to use with admin option .
Question 34 of 50
Examine the CREATE USER statement and choose which of the following options best applies.
Correct answer: B) JOHN is authenticated by the database.
Explanation: The user JOHN is authenticated by the database based on the password specified in the CREATE USER statement.
Question 35 of 50
User system granted the SELECT privilege on sh.products to user ian using WITH GRANT OPTION. Ian then granted SELECT on sh.products to user stuart. Ian has left the company, and his account has been dropped. What happens to Stuart’s privileges on sh.products?
Correct answer: A) Stuart loses his SELECT privilege on sh.products.
Explanation: When object privileges are granted through an intermediary, they are revoked when the intermediary is dropped.
Question 36 of 50
User system granted the SELECT ANY TABLE privilege to user ian using WITH ADMIN OPTION. Ian then granted SELECT ANY TABLE to user stuart. Ian has left the company, and his account has been dropped. What happens to Stuart’s privileges?
Correct answer: B) Stuart retains his privileges.
Explanation: System privileges are not affected by the removal of the user who granted them.
Question 37 of 50
Which of the following system privileges should be granted judiciously because they can allow the grantee to masquerade as another user?
Correct answer: D) All of the above
Explanation: These system privileges allow actions that can be exploited to execute code or impersonate other users.
Question 38 of 50
Which of the following statements enables the role user_admin in the current session?
Correct answer: D) set role user_admin;
Explanation: The set role statement is used to enable roles within the current session.
Question 39 of 50
Which of the following SQL statements allows user augustin to use the privileges associated with the password-protected role info_czar that has been granted to him?
Correct answer: B) alter user augustin default role all;
Explanation: Specifying a role as a default role allows it to be used without explicitly setting it in the session.
Question 40 of 50
By default, how much space can any account use for a new table?
Correct answer: A) None
Explanation: By default, new user accounts do not have any space quota in tablespaces until a quota is explicitly assigned.
Question 41 of 50
Which of the following SQL statements disconnects a session after it has been idle for 30 minutes?
Correct answer: C) alter profile default limit idle_time 30;
Explanation: Profiles manage idle time, among other resources, and limit idle session time using the idle_time parameter.
Question 42 of 50
Which of the following options prevents a user from reusing a password when they change their password?
Correct answer: B) Altering that user’s profile to UNLIMITED for PASSWORD_REUSE_TIME and 1 for PASSWORD_REUSE_MAX
Explanation: The combination of setting PASSWORD_REUSE_TIME to UNLIMITED and PASSWORD_REUSE_MAX to 1 ensures that passwords cannot be reused.
Question 43 of 50
Which of the following components of a ROWID uniquely identifies the location of a row in a database?
Correct answer: C) Relative file number
Explanation: The ROWID includes the relative file number, block number, and row number within the block to uniquely identify a row's physical location.
Question 44 of 50
Which data dictionary view is used to view the current values of initialization parameters?
Correct answer: C) V$PARAMETER
Explanation: V$PARAMETER displays the current values of initialization parameters for the instance.
Question 45 of 50
Which SQL statement will prevent any new rows from being added to the JOBS table in the USERS tablespace, assuming the table currently exists?
Correct answer: B) ALTER USER JAMES QUOTA 0 ON USERS;
Explanation: Setting the user's quota on a tablespace to 0 prevents the allocation of additional space for the table, which will prevent new rows from being added.
Question 46 of 50
Which SQL statement creates a private database link?
Correct answer: D) CREATE DATABASE LINK linkname CONNECT TO username IDENTIFIED BY password;
Explanation: Private database links are created without the PUBLIC keyword and require credentials to establish the connection.
Question 47 of 50
Which of the following initialization parameters is required to use Automatic Storage Management (ASM)?
Correct answer: A) ASM_DISKGROUPS
Explanation: ASM_DISKGROUPS specifies the list of disk groups that will be mounted at instance startup.
Question 48 of 50
Which of the following SQL statements disables a role in the current session?
Correct answer: B) set role none;
Explanation: The set role none statement disables all roles for the current session.
Question 49 of 50
What happens when an ALTER SYSTEM statement is issued to change the value of the UNDO_RETENTION parameter?
Correct answer: B) The value is changed in memory, and the change is permanent if the database is using an spfile.
Explanation: ALTER SYSTEM changes take effect immediately in memory and are permanent if an spfile is in use.
Question 50 of 50
Which of the following privileges is required to create a public synonym in an Oracle database?
Correct answer: B) CREATE PUBLIC SYNONYM
Explanation: The CREATE PUBLIC SYNONYM privilege allows a user to create a synonym that is accessible by all users in the database.
Set 2 complete
You scored 0%
0 correct out of 0 answered (50 questions in this set).