-
web.groovymark@gmail.com
- December 5, 2024
Question 21
Which SQL statement is used to view the current values of initialization parameters?
A) SELECT * FROM V$DATABASE;
B) SELECT * FROM V$PARAMETER;
C) SELECT * FROM V$SESSION;
D) SELECT * FROM V$INSTANCE;
Correct Answer: B) SELECT * FROM V$PARAMETER;
Explanation: V$PARAMETER displays the current values of initialization parameters for the running Oracle instance.
Question 22
When using a binary initialization parameter file (spfile), which clause would ensure that changes to parameters are persisted across restarts?
A) ALTER SYSTEM SET parameter=value SCOPE=MEMORY;
B) ALTER SYSTEM SET parameter=value SCOPE=BOTH;
C) ALTER SYSTEM SET parameter=value SCOPE=SPFILE;
D) ALTER SYSTEM SET parameter=value SCOPE=ALL;
Correct Answer: B) ALTER SYSTEM SET parameter=value SCOPE=BOTH;
Explanation: SCOPE=BOTH applies the changes in memory and saves them in the spfile, ensuring persistence across restarts.
Question 23
Which of the following statements is true regarding the SYSTEM tablespace in an Oracle database
A) It can be made read-only.
B) It can be taken offline.
C) Its data files can be renamed.
D) Its data files cannot be resized.
Correct Answer: C) Its data files can be renamed.
Explanation: Data files in the SYSTEM tablespace can be renamed, but only when the database is in the MOUNT state.
Question 24
Which of the following components is stored in a tablespace in an Oracle database?
A) Data file
B) Redo log file
C) Temporary segment
D) Control file
Correct Answer: C) Temporary segment
Explanation: Temporary segments are stored in a tablespace, while control files and redo log files are stored outside tablespaces.
Question 25
What SQL statement would you issue to make a tablespace read-only?
A) ALTER DATABASE READ ONLY;
B) ALTER SYSTEM READ ONLY;
C) ALTER TABLESPACE READ ONLY;
D) ALTER FILESPACE READ ONLY;
Correct Answer: C) ALTER TABLESPACE READ ONLY;
Explanation: The correct SQL statement to make a tablespace read-only is ALTER TABLESPACE READ ONLY.
Question 26
Which of the following views can be used to monitor tablespace utilization?
A) DBA_SEGMENTS
B) DBA_TABLESPACES
C) DBA_DATA_FILES
D) DBA_FREE_SPACE
Correct Answer: D) DBA_FREE_SPACE
Explanation: DBA_FREE_SPACE contains information about free space in tablespaces and can be used to monitor tablespace utilization.
Question 27
Which background process ensures that committed transactions are written to disk even if they have not yet been written to the data files?
A) DBWn
B) LGWR
C) ARCn
D) CKPT
Correct Answer: B) LGWR
Explanation: LGWR writes committed transactions to the redo log files, ensuring data persistence even if the data files are not yet updated.
Question 28
What happens when the ALTER SYSTEM command is used to change the value of an initialization parameter in a pfile?
A) The change is applied immediately and persists across restarts.
B) The change is applied in memory only and does not persist across restarts.
C) The change is not applied until the database is restarted.
D) The change is applied immediately and requires a database restart to take effect.
Correct Answer: C) The change is not applied until the database is restarted.
Explanation: When using a pfile, changes to initialization parameters are not applied until the database is restarte
Question 29
What is the purpose of the DBMS_FGA package in Oracle?
A) To manage fine-grained auditing policies
B) To manage memory allocation
C) To perform database backup and recovery
D) To monitor tablespace usage
Correct Answer: A) To manage fine-grained auditing policies
Explanation: The DBMS_FGA package is used to define and manage fine-grained auditing policies in Oracle.
Question 30
Which view can be queried to monitor session activity and resource usage in an Oracle database?
A) V$SESSION
B) V$DATABASE
C) V$LOG
D) V$INSTANCE
Correct Answer: A) V$SESSION
Explanation: V$SESSION displays information about the currently active sessions and resource usage in the database.
Question 31
Which statement is true regarding the ALTER TABLESPACE command in Oracle?
A) It can be used to increase the size of the control file.
B) It can be used to resize data files.
C) It can be used to change the default block size.
D) It can be used to take the system tablespace offline.
Correct Answer: B) It can be used to resize data files.
Explanation: The ALTER TABLESPACE command can be used to resize data files within a tablespace.
Question 32
Which background process coalesces free space in dictionary-managed tablespaces?
A) DBWn
B) CKPT
C) SMON
D) PMON
Correct Answer: C) SMON
Explanation: SMON (System Monitor) is responsible for coalescing free space in dictionary-managed tablespaces.
Question 33
Which SQL statement is used to add more space to an existing tablespace by increasing the size of a data file?
A) ALTER SYSTEM ADD DATAFILE SIZE 500M;
B) ALTER TABLESPACE ADD DATAFILE SIZE 500M;
C) ALTER DATABASE DATAFILE ‘filename’ RESIZE 500M;
D) ALTER DATAFILE RESIZE ‘filename’ 500M;
Correct Answer: C) ALTER DATABASE DATAFILE 'filename' RESIZE 500M;
Explanation: The ALTER DATABASE DATAFILE statement is used to resize an existing data file.
Question 34
What is the minimum number of redo log groups required in an Oracle database?
A) One
B) Two
C) Three
D) Four
Correct Answer: B) Two
Explanation: An Oracle database requires at least two redo log groups for proper functioning and to provide redundancy.
Question 35
Which of the following SQL statements is used to bring a tablespace online?
A) ALTER SYSTEM ONLINE;
B) ALTER DATABASE ONLINE;
C) ALTER TABLESPACE ONLINE;
D) ALTER DATAFILE ONLINE;
Correct Answer: C) ALTER TABLESPACE ONLINE;
Explanation: ALTER TABLESPACE ONLINE is the correct statement to bring a tablespace online.
Question 36
What privilege is required to create a private database link?
A) CREATE SYNONYM
B) CREATE PUBLIC DATABASE LINK
C) CREATE DATABASE LINK
D) DBA
Correct Answer: C) CREATE DATABASE LINK
Explanation: The CREATE DATABASE LINK privilege is required to create a private database link.
Question 37
What does the ALTER SYSTEM FLUSH SHARED_POOL command do in Oracle?
A) Clears all data from the database buffer cache
B) Flushes the shared pool and removes all cached SQL statements
C) Writes all dirty blocks to the data files
D) Forces a checkpoint
Correct Answer: B) Flushes the shared pool and removes all cached SQL statements
Explanation: The ALTER SYSTEM FLUSH SHARED_POOL command clears the shared pool by removing all cached SQL and PL/SQL statements.
Question 38
Which of the following is true about Oracle’s automatic segment space management (ASSM)?
A) ASSM uses bitmaps to manage free space.
B) ASSM is only available in temporary tablespaces.
C) ASSM requires manual intervention to coalesce free space.
D) ASSM is deprecated in Oracle 12c.
Correct Answer: A) ASSM uses bitmaps to manage free space.
Explanation: ASSM uses bitmaps to automatically manage and track free space in segments.
Question 39
Which view would you query to find out which data files are part of a tablespace?
A) DBA_SEGMENTS
B) DBA_TABLESPACES
C) DBA_DATA_FILES
D) DBA_TAB_COLUMNS
Correct Answer: C) DBA_DATA_FILES
Explanation: DBA_DATA_FILES contains information about the data files belonging to each table
Question 40
Which SQL statement disables automatic archiving in Oracle?
A) ALTER SYSTEM ARCHIVELOG OFF;
B) ALTER SYSTEM SET LOG_ARCHIVE_DEST=”;
C) ALTER DATABASE NOARCHIVELOG;
D) ALTER SYSTEM SET ARCHIVE_MODE=OFF;
Correct Answer: C) ALTER DATABASE NOARCHIVELOG
Explanation: The correct statement to disable automatic archiving is ALTER DATABASE NOARCHIVELOG.
