Managing Databases with Oracle Questions and Answers

Q: Given the following statementALTER TABLE employee MOVE TABLESPACE data 1.The action taken is :

The statement moves the data to a new segment.In Oracle, ALTER TABLE ... MOVE relocates the data of a non-partitioned table into a new segment, here in the tablespace named in the statement. It is used to change a table's storage or tablespace and to reorganise it, removing fragmentation; the indexes on the table become unusable and must be rebuilt afterwards.It does not rename a tablespace. It moves the table rather than leaving a copy behind, and it moves the rows, not just the table's structure.

Q: The online redo log contains :(i) A record of all committed transactions.(ii) A record of all rolled back transactions.(iii) A list of all the logged in users.(iv) A list of all the associated files.

The online redo log contains (i) and (ii).In Oracle, the online redo log records every change made to the database as redo entries, so that the changes can be reapplied in recovery. That covers transactions that were committed and also changes that were later rolled back, since the rollback is itself recorded through undo changes.It holds no list of logged-in users, which is session information kept in memory. The list of the database's associated files (data files and redo log files) is kept in the control file, not in the redo log. So options including (iii) or (iv) are wrong.

Q: In Oracle Database, what is the purpose of a synonym ?

A synonym provides an alias for database objects.In Oracle, CREATE SYNONYM gives an alternative name to a table, view, sequence, procedure or other object. Users can then refer to hr.employees simply as emp, and a public synonym hides the owning schema and even a remote database link from the people using it.A synonym stores no data, so it is not a backup. It does not enforce constraints, which are defined on the table itself, and it does not make queries faster; it is only another name for the same object.

Q: Which of the following is TRUE about mutating tables in PL/SQL triggers ?

Mutating tables cannot be queried or modified during trigger execution.A table is 'mutating' while a DML statement is changing it. Oracle does not allow a row-level trigger on that table to read or change it, because the result would depend on the order in which rows happen to be processed. A trigger that tries raises the run-time error ORA-04091 (table is mutating, trigger/function may not see it).So the table cannot be modified freely inside a row-level trigger. The problem shows at run time, not as a compilation error, and the restriction lasts only while the statement executes; nothing is locked permanently.

Q: In PL/SQL, what is the key difference between an implicit and explicit cursor ?

The key difference is that explicit cursors are defined by the programmer.Oracle opens an implicit cursor automatically for every SQL statement in a PL/SQL block, such as an INSERT or a SELECT INTO that returns one row; its attributes are read through SQL%ROWCOUNT, SQL%FOUND and so on. An explicit cursor is declared by the programmer for a query, then opened, fetched from and closed under the program's control, usually to process a multi-row result one row at a time.Implicit cursors are created by Oracle, not by the user. Explicit cursors are not inherently faster, and the multi-row point is a consequence of the difference rather than its definition.

Q: PL/SQL extends SQL by adding which type of features ?

PL/SQL adds procedural programming features to SQL.SQL is declarative: each statement says what data to work on, not how. PL/SQL, Oracle's procedural extension, wraps SQL in a block-structured language with variables, conditions (IF), loops, cursors, exception handling, procedures, functions, packages and triggers.It provides no graphical interface and is not a networking extension. Because one option is correct, 'None of these' is wrong.

Q: In what correct order must the following PL/SQL cursor operations be performed ?

The correct order is DECLARE → OPEN → FETCH → CLOSE.An explicit cursor is first declared with its query in the declaration section. OPEN then runs the query and identifies the result set. FETCH retrieves the rows one at a time into variables, usually in a loop until %NOTFOUND. CLOSE finally releases the cursor's resources.A cursor cannot be opened before it is declared, and nothing can be fetched before it is opened, so every other order fails at its first step.

Q: Which type of trigger fires once for each row affected by the triggering statement ?

A trigger that fires once for each affected row is a row-level trigger.A row-level trigger is declared FOR EACH ROW. If an UPDATE changes 50 rows, it fires 50 times, and in each firing it can read the old and new values of that row through :OLD and :NEW.A statement-level trigger, the default, fires once per triggering statement however many rows it affects. An INSTEAD OF trigger replaces the DML on a view, and 'before' describes when a trigger fires, which can apply to both row-level and statement-level triggers.

Q: Sequences guarantee valid surrogate key values.

Correct answer: FalseA sequence guarantees only uniqueness of the numbers it issues. Values are lost when a transaction rolls back or an instance fails, so gaps appear; nothing forces the value to be used in the intended column; and a value can be inserted directly, bypassing the sequence and colliding with it later. Uniqueness is not the same as validity.

Q: What Oracle backup and recover file contains user and system data?

Correct answer: DatafileThe datafiles hold the actual contents of the database - the user tables and indexes and the data dictionary alike. The control file records the physical structure of the database and the state of the files, and the redo logs record the changes made, so all three are needed for recovery but only the datafiles hold the data itself.

Given the following statement

ALTER TABLE employee MOVE TABLESPACE data 1.

The action taken is :

A.table space is renamed
B.data is moved to new segment
C.A copy of table is moved into new segment
D.The table structure is moved in to new segment

Exam Year(s)

MH SET - 2018, SET Exam - 2018,

Answer: B

The statement moves the data to a new segment.

In Oracle, ALTER TABLE ... MOVE relocates the data of a non-partitioned table into a new segment, here in the tablespace named in the statement. It is used to change a table's storage or tablespace and to reorganise it, removing fragmentation; the indexes on the table become unusable and must be rebuilt afterwards.

It does not rename a tablespace. It moves the table rather than leaving a copy behind, and it moves the rows, not just the table's structure.

Discuss in Forum

The online redo log contains :

(i) A record of all committed transactions.
(ii) A record of all rolled back transactions.
(iii) A list of all the logged in users.
(iv) A list of all the associated files.

A.(i) and (ii)
B.(i), (ii) and (iii)
C.(i), (ii) and (iv)
D.(ii), (iii) and (iv)

Exam Year(s)

MH SET - 2019, SET Exam - 2019,

Answer: A

The online redo log contains (i) and (ii).

In Oracle, the online redo log records every change made to the database as redo entries, so that the changes can be reapplied in recovery. That covers transactions that were committed and also changes that were later rolled back, since the rollback is itself recorded through undo changes.

It holds no list of logged-in users, which is session information kept in memory. The list of the database's associated files (data files and redo log files) is kept in the control file, not in the redo log. So options including (iii) or (iv) are wrong.

Discuss in Forum

In Oracle Database, what is the purpose of a synonym ?

A.To create a backup of a table
B.To provide an alias for database objects
C.To enforce constraints
D.To improve query speed

Exam Year(s)

Tripura Public Service Commission - 2026,

Answer: B

A synonym provides an alias for database objects.

In Oracle, CREATE SYNONYM gives an alternative name to a table, view, sequence, procedure or other object. Users can then refer to hr.employees simply as emp, and a public synonym hides the owning schema and even a remote database link from the people using it.

A synonym stores no data, so it is not a backup. It does not enforce constraints, which are defined on the table itself, and it does not make queries faster; it is only another name for the same object.

Discuss in Forum

Which of the following is TRUE about mutating tables in PL/SQL triggers ?

A.They cannot be queried or modified during trigger execution
B.They can be modified freely inside row-level triggers
C.They cause compilation errors only
D.They are automatically locked permanently

Exam Year(s)

Tripura Public Service Commission - 2026,

Answer: A

Mutating tables cannot be queried or modified during trigger execution.

A table is 'mutating' while a DML statement is changing it. Oracle does not allow a row-level trigger on that table to read or change it, because the result would depend on the order in which rows happen to be processed. A trigger that tries raises the run-time error ORA-04091 (table is mutating, trigger/function may not see it).

So the table cannot be modified freely inside a row-level trigger. The problem shows at run time, not as a compilation error, and the restriction lasts only while the statement executes; nothing is locked permanently.

Discuss in Forum

In PL/SQL, what is the key difference between an implicit and explicit cursor ?

A.Explicit cursors are faster
B.Explicit cursors are defined by the programmer
C.Implicit cursors are user-defined
D.Implicit cursors cannot fetch multiple rows

Exam Year(s)

Tripura Public Service Commission - 2026,

Answer: B

The key difference is that explicit cursors are defined by the programmer.

Oracle opens an implicit cursor automatically for every SQL statement in a PL/SQL block, such as an INSERT or a SELECT INTO that returns one row; its attributes are read through SQL%ROWCOUNT, SQL%FOUND and so on. An explicit cursor is declared by the programmer for a query, then opened, fetched from and closed under the program's control, usually to process a multi-row result one row at a time.

Implicit cursors are created by Oracle, not by the user. Explicit cursors are not inherently faster, and the multi-row point is a consequence of the difference rather than its definition.

Discuss in Forum

PL/SQL extends SQL by adding which type of features ?

A.Graphical interface
B.Network support
C.Procedural programming features
D.None of these

Exam Year(s)

Tripura Public Service Commission - 2026,

Answer: C

PL/SQL adds procedural programming features to SQL.

SQL is declarative: each statement says what data to work on, not how. PL/SQL, Oracle's procedural extension, wraps SQL in a block-structured language with variables, conditions (IF), loops, cursors, exception handling, procedures, functions, packages and triggers.

It provides no graphical interface and is not a networking extension. Because one option is correct, 'None of these' is wrong.

Discuss in Forum

In what correct order must the following PL/SQL cursor operations be performed ?

A.OPEN → DECLARE → FETCH → CLOSE
B.DECLARE → FETCH → OPEN → CLOSE
C.OPEN → FETCH → DECLARE → CLOSE
D.DECLARE → OPEN → FETCH → CLOSE

Exam Year(s)

Tripura Public Service Commission - 2026,

Answer: D

The correct order is DECLARE → OPEN → FETCH → CLOSE.

An explicit cursor is first declared with its query in the declaration section. OPEN then runs the query and identifies the result set. FETCH retrieves the rows one at a time into variables, usually in a loop until %NOTFOUND. CLOSE finally releases the cursor's resources.

A cursor cannot be opened before it is declared, and nothing can be fetched before it is opened, so every other order fails at its first step.

Discuss in Forum

Which type of trigger fires once for each row affected by the triggering statement ?

A.Statement-level trigger
B.Instead-of trigger
C.Row-level trigger
D.Before trigger

Exam Year(s)

Tripura Public Service Commission - 2026,

Answer: C

A trigger that fires once for each affected row is a row-level trigger.

A row-level trigger is declared FOR EACH ROW. If an UPDATE changes 50 rows, it fires 50 times, and in each firing it can read the old and new values of that row through :OLD and :NEW.

A statement-level trigger, the default, fires once per triggering statement however many rows it affects. An INSTEAD OF trigger replaces the DML on a view, and 'before' describes when a trigger fires, which can apply to both row-level and statement-level triggers.

Discuss in Forum

Sequences guarantee valid surrogate key values.

A.True
B.False

Answer: B

Correct answer: False

A sequence guarantees only uniqueness of the numbers it issues. Values are lost when a transaction rolls back or an instance fails, so gaps appear; nothing forces the value to be used in the intended column; and a value can be inserted directly, bypassing the sequence and colliding with it later. Uniqueness is not the same as validity.

Discuss in Forum

10.

What Oracle backup and recover file contains user and system data?

A.Control file
B.Datafile
C.OnLine ReDo file
D.Offline ReDo file

Answer: B

Correct answer: Datafile

The datafiles hold the actual contents of the database - the user tables and indexes and the data dictionary alike. The control file records the physical structure of the database and the state of the files, and the redo logs record the changes made, so all three are needed for recovery but only the datafiles hold the data itself.

Discuss in Forum

Loading…

Topics

Other Subjects

Tags: Managing Databases with Oracle Questions and Answers || Managing Databases with Oracle MCQ Questions and Answers || Managing Databases with Oracle GK Questions and Answers || Managing Databases with Oracle GK MCQ Questions || Managing Databases with Oracle Multiple Choice Questions and Answers || Managing Databases with Oracle GK || GK on Managing Databases with Oracle || Database Questions and Answers || Database MCQ Questions and Answers || Database GK Questions and Answers || GK on Database

Managing Databases with Oracle Questions and Answers

Topics

Other Subjects

About Us

Stay informed about all important Dates & Notifications