0

databases Online Quiz - 212

Description: databases Online Quiz - 212
Number of Questions: 20
Created by:
Tags: databases
Attempted 0/20 Correct 0 Score 0
  1. With SQL, how can you return the number of records in the "Persons" table?
  1. SELECT COLUMNS() FROM Persons

  2. SELECT COUNT(*) FROM Persons

  3. SELECT COUNT() FROM Persons

  4. SELECT COLUMNS(*) FROM Persons


Correct Option: B
  1. Which SQL statement is used to return only different values?
  1. SELECT UNIQUE

  2. SELECT DISTINCT

  3. SELECT DIFFERENT

  4. None


Correct Option: B

The OR operator displays a record if ANY conditions listed are true. The AND operator displays a record if ALL of the conditions listed are true

  1. True

  2. False


Correct Option: A

How can you change "Hansen" into "Nilsen" in the "LastName" column in the Persons table?

  1. UPDATE Persons SET LastName='Hansen' INTO LastName='Nilsen'

  2. MODIFY Persons SET LastName='Nilsen' WHERE LastName='Hansen

  3. UPDATE Persons SET LastName='Nilsen' WHERE LastName='Hansen'

  4. MODIFY Persons SET LastName='Hansen' INTO LastName='Nilsen


Correct Option: B

AI Explanation

To change "Hansen" into "Nilsen" in the "LastName" column in the Persons table, you need to use an SQL UPDATE statement. Let's go through each option to understand why it is correct or incorrect:

Option A) UPDATE Persons SET LastName='Hansen' INTO LastName='Nilsen' - This option is incorrect because the syntax for the UPDATE statement is incorrect. The correct syntax is UPDATE table_name SET column_name = new_value WHERE condition.

Option B) MODIFY Persons SET LastName='Nilsen' WHERE LastName='Hansen' - This option is incorrect because the syntax for the MODIFY statement is incorrect. The correct syntax for updating data in SQL is using the UPDATE statement.

Option C) UPDATE Persons SET LastName='Nilsen' WHERE LastName='Hansen' - This option is correct because it follows the correct syntax for an SQL UPDATE statement. It updates the LastName column in the Persons table, setting the value to 'Nilsen' where the current value is 'Hansen'.

Option D) MODIFY Persons SET LastName='Hansen' INTO LastName='Nilsen' - This option is incorrect because the syntax for the MODIFY statement is incorrect. The correct syntax for updating data in SQL is using the UPDATE statement.

The correct answer is C. This option is correct because it uses the correct syntax for an SQL UPDATE statement to update the LastName column in the Persons table from 'Hansen' to 'Nilsen' where the current value is 'Hansen'.

Your database operates in ARCHIVELOGmode. User-managed consistent backups are performed every Sunday night. On Tuesday, client A drops a table at8:00 a.m.as follows: SQL> DROP TABLEcust_ordPURGE; Table dropped. Client B executes a query at9:00 a.m.on the same table as follows: SQL> SELECT * FROMcust_ord SELECT * FROMcust_ord * ERROR at line 1: ORA-00942: table or view does not exist Client B needs the dropped table and reports the problem to you. With reference to this scenario, which action should you take?

  1. retrieve the table by using the flashback feature

  2. restore all the data files from last Sunday's backup and then perform a time-based recovery

  3. restore all the data files from last Sunday's backup and then perform a log sequence recovery

  4. restore all the data files from last Sunday's backup and then perform a cancel-based recovery

  5. restore from last Sunday's backup only data files that belong to the tablespace in which the table was stored and then perform a complete recovery


Correct Option: B

One of the tablespaces is read-only in your database. The loss of all control files forced you to re-create the control file. Which operation do you need to perform after re-creating the control file and opening the database?

  1. drop and re-create the read-only tablespaces

  2. rename the read-only data files to their correct file names

  3. change the tablespace status from read/write to read-only

  4. re-create the read-only tablespace because it is automatically removed

  5. none of the above


Correct Option: B
  1. The data file cannot be recovered.

  2. The date file can be restored from the SYSTEM auto backup.

  3. Take thetablespaceoffline,drop,kand re-create it.

  4. Recovery Manager (RMAN) is used to recover the data file.

  5. The ALTER DATABSE CREATE DATAFILE


Correct Option: A

You work as a database administrator for TCS .com. In your production database, the size of Database Buffer Cache needs to be increased immediately for the current as well as future instances of the database. The Oracle instance has been configured to accommodate any changes in the size of the memory structures. At this is production database, you want to accomplish this task with no impact on the user's connections. Which activity must you have completed before accomplishing this task?

  1. You must have started the database instance in restricted mode.

  2. You must have started the database instance in NORMALmode.

  3. You must have started the database instance with server parameter file.

  4. You must have started the database instance but must not have mounted.

  5. You must have started the database instance but must not have opened.


Correct Option: C

AI Explanation

To increase the size of the Database Buffer Cache without impacting user connections, you must have started the database instance with a server parameter file (SPFILE).

Explanation for the options:

A) Starting the database instance in restricted mode is not necessary for increasing the size of the Database Buffer Cache. Restricted mode is typically used for performing maintenance activities or troubleshooting specific issues.

B) Starting the database instance in NORMAL mode is not the correct option because it does not specify the use of a server parameter file (SPFILE), which is required to make changes to the memory structures.

C) This is the correct option. Starting the database instance with a server parameter file allows you to make changes to memory structures dynamically, including increasing the size of the Database Buffer Cache.

D) Starting the database instance without mounting it would not allow you to make changes to the memory structures.

E) Starting the database instance but not opening it would not allow you to make changes to the memory structures.

Therefore, the correct answer is C) You must have started the database instance with a server parameter file.

Your TCS .com database is in ARCHIVELOG mode. The data file that belongs to the SYSTEMtablespacehas become corrupted. Up to what point can you recover the database?

  1. Until last commit

  2. Until the time you perform recovery

  3. Until the time the date file got corrupted

  4. Until the point where the last transaction begun

  5. You cannot recover the SYSTEM tablespaceand must re-create the database.


Correct Option: A

You work as a database administrator for TCS .com. The UNDO_RETENTION parameter in your database is set to 1000 and undoretention is not guaranteed. Which statement regarding retention of undo data is correct?

  1. Undo data becomes obsolete after 1,000 seconds.

  2. Undo data gets refreshed after 1,000 seconds.

  3. Undo data will be stored permanently after 1,000 seconds.

  4. Committed undo data would be retained for 1,000 seconds if free undo space is available.

  5. Undo data will be retained in the UNDOtablespacefor 1,000 seconds, then it gets movedmovedto the TEMPORARY tablespace to provide read consistency.


Correct Option: D

AI Explanation

To answer this question, we need to understand the concept of the UNDO_RETENTION parameter and its effect on the retention of undo data in a database.

The UNDO_RETENTION parameter is used to specify the minimum amount of time (in seconds) that Oracle should attempt to retain undo data before it is overwritten. It does not guarantee the retention of undo data for the specified duration; rather, it provides a target for Oracle to aim for.

Option A) Undo data becomes obsolete after 1,000 seconds. This option is incorrect. The UNDO_RETENTION parameter does not make the undo data obsolete after a specific duration. It only sets a target retention time.

Option B) Undo data gets refreshed after 1,000 seconds. This option is incorrect. The UNDO_RETENTION parameter does not refresh the undo data after a specific duration. It only sets a target retention time.

Option C) Undo data will be stored permanently after 1,000 seconds. This option is incorrect. The UNDO_RETENTION parameter does not store undo data permanently. It only sets a target retention time.

Option D) Committed undo data would be retained for 1,000 seconds if free undo space is available. This option is correct. When the UNDO_RETENTION parameter is set, committed undo data will be retained for the specified duration if there is enough free undo space available. If free undo space is not available, then the oldest undo data may be overwritten.

Option E) Undo data will be retained in the UNDO tablespace for 1,000 seconds, then it gets moved to the TEMPORARY tablespace to provide read consistency. This option is incorrect. The UNDO_RETENTION parameter does not involve moving undo data to the TEMPORARY tablespace. The UNDO tablespace is specifically used to store undo data.

Therefore, the correct answer is Option D. Committed undo data would be retained for 1,000 seconds if free undo space is available.

You work as a database administrator for TCS .com. On a Monday morning, you find the database instance aborted. After inspecting the alert log file, you execute the STARTUP command in SQL*Plus to bring the instance up. What statement is true?

  1. PMON coordinates media recovery.

  2. SMON coordinates instance recovery.

  3. PMON coordinates instance recovery.

  4. Undo Advisor would roll back all uncommitted transactions.

  5. SQL*PLUS reports an error with the message asking you to perform instance recovery.


Correct Option: B

AI Explanation

To answer this question, you need to understand the roles of various background processes in Oracle database.

Option A) PMON coordinates media recovery - This option is incorrect. PMON (Process Monitor) is responsible for cleaning up the resources of failed or terminated processes. It does not coordinate media recovery.

Option B) SMON coordinates instance recovery - This option is correct. SMON (System Monitor) is responsible for coordinating instance recovery. When an instance aborts or crashes, SMON ensures that the database is recovered to a consistent state when it is brought back up.

Option C) PMON coordinates instance recovery - This option is incorrect. While PMON plays a role in instance recovery by cleaning up failed processes and releasing resources, it does not coordinate the overall instance recovery process.

Option D) Undo Advisor would roll back all uncommitted transactions - This option is unrelated to the situation described in the question. The Undo Advisor is a feature that helps optimize undo retention and undo space allocation.

Option E) SQL*PLUS reports an error with the message asking you to perform instance recovery - This option is incorrect. SQL*Plus is a command-line interface tool for Oracle database, and it does not report any error related to instance recovery. It is the responsibility of the background processes (such as SMON) to coordinate instance recovery.

The correct answer is B) SMON coordinates instance recovery. This option is correct because SMON is responsible for coordinating instance recovery, ensuring that the database is recovered to a consistent state when the instance is brought back up.

  1. The instance would hang.

  2. The instance needs to be shut down.

  3. The instance would be in the open state.

  4. The instance would abort in such cases.

  5. The instance would be in the open and invalid state.

  6. The instance would in the open state, but all the background processes will be restarted.


Correct Option: D
  1. alert_TCS.log

  2. alertlog_TCS.log

  3. TCS _alert_log.log

  4. log_alert_TCS.log

  5. trace_alert_TCS.log


Correct Option: A

In your DB -multiplexed online redo log files,one of the members in a group is lost due to media failure? You wud..

  1. import the database from the last export

  2. restore all the members in the group from the last backup

  3. drop the lost member from the database and then add a new member to the group

  4. restore all the database files from the backup and then perform a complete recovery

  5. restore all the database files from the backup and then perform an incomplete recovery


Correct Option: C
  1. You do not need to restore all the data files.

  2. You do not need to open the database with the RESETLOGS operation

  3. You do not need to perform a full backup after the RESETLOGS operation.

  4. You do not need to recover all the data files to the same system change number (SCN).


Correct Option: C

In which scenarios would you rebuild an index?

  1. when you need to disable the index usage

  2. when you need to move the index to another tablespace

  3. when you need to enable index monitoring

  4. none of the above


Correct Option: B

A redo log file is corrupted while the database is open;You would ..

  1. clear the redo log group

  2. perform redo log file import

  3. perform an incomplete recovery

  4. perform a redo log recovery using Recovery Manager (RMAN)

  5. shut down the database and open the database in the NOARCHIVELOGmode


Correct Option: A

AI Explanation

To answer this question, you need to understand the purpose and functionality of redo log files in a database.

Redo log files are a crucial component of a database system. They record all changes made to the database, allowing for recovery in the event of a system failure. When a redo log file becomes corrupted while the database is open, it is essential to address the issue to ensure data integrity.

Let's go through each option to understand why it is correct or incorrect:

Option A) Clear the redo log group - This option is correct. Clearing the redo log group involves removing the corrupted redo log file(s) and creating new ones. By doing this, you ensure that the database can continue to operate with a clean set of redo log files.

Option B) Perform redo log file import - This option is incorrect. Redo log file import is not a valid solution for dealing with a corrupted redo log file. Importing redo log files is typically used when restoring a database from a backup.

Option C) Perform an incomplete recovery - This option is incorrect. Incomplete recovery is used when restoring a database to a specific point in time but does not address a corrupted redo log file issue.

Option D) Perform a redo log recovery using Recovery Manager (RMAN) - This option is incorrect. While RMAN is a powerful tool for database recovery, it is not specifically designed to handle a corrupted redo log file issue.

Option E) Shut down the database and open the database in the NOARCHIVELOG mode - This option is incorrect. Shutting down the database and operating it in NOARCHIVELOG mode does not directly address the issue of a corrupted redo log file.

Therefore, the correct answer is A) Clear the redo log group. Clearing the redo log group allows for the removal of the corrupted redo log file(s) and the creation of new ones, ensuring the integrity of the database.

Which statement regarding an incomplete recovery is true(10g)?

  1. You do not need to restore all the data files.

  2. You do not need to open the database with the RESETLOGS operation

  3. You do not need to perform a full backup after the RESETLOGS operation.

  4. You do not need to recover all the data files to the same system change number (SCN).


Correct Option: C

Which file is not supported with RMANBACKUP command?

  1. data file

  2. password file

  3. archivelog file

  4. temporary file

  5. online redo log file


Correct Option: E
- Hide questions