Oracle DBA Interview Questions and Answers - 1

Question: 1

What is the significance of pseudo column in oracle?

• Pseudo column is an Oracle assigned value used in the same context as a column.
• It is an item of data, which is treated like a table column even though it does not exist as part of the definition of any table.
• CURRVAL, NEXTVAL, USER, SYSDATE, LEVEL, ROWID, and ROWNUM are the examples of pseudo column.
• A pseudo column can be queried but Data Manipulation Language (DML) cannot be applied to a pseudo column.

Question: 2

What is a global index and local index?

• A global index is associated with the entire table and gets affected when any one of the partitions is modified.
• Local indexes are associated with individual partitions.
• Therefore, only a person of the index gets affected when a table partition is modified.

Question: 3

What is the difference between DELETE and TRUNCATE statements?

• DELETE is a Data Manipulation Language (DML) statement and can be rolled back while TRUNCATE is a DDL statement and cannot be rolled back.
• You can delete data selectively by using the DELETE statement while TRUNCATE statement removes all data in the table.
• DELETE does not affect High Water Mark (HWM) while TRUNCATE moves HWM to the beginning of the table.

Question: 4

Can you import objects from Oracle version 7.3 to 9i?

Yes, you can use the exp or imp utilities to import the objects from Oracle version 7.3 to 9i.

Question: 5

Can you pivot the results of a query?

You can use hierarchical query with the sys_connect_by_path function in Oracle 9i and above to pivot the result.