Oracle DBA Architecture Interview Questions and Answers for Experienced - 1

Question: 1

What do you understand by PL/SQL collections?

  • PL/SQL collections are the composite types. These are group of elements, which can be manipulated individually, and are similar to arrays in C.
  • There are three types of collections in PL/SQL given as follows:
  • Index by tables or associative arrays – Contains elements of same datatype indexed by an integer. The number of elements in associative arrays is not limited.
  • Nested tables – Refer to the tables that can be used in SQL statements and bulk insert operation.
  • Variable arrays – Refer to the tables that can be used in SQL statements and stored in database tables, such as nested tables. However, they can hold a limited number of elements.

Question: 2

Explain the use of table functions?

  • Table function is PL/SQL code that performs functions on PL/SQL table to return a PL/SQL table.
  • Oracle 9i has added a feature that allows these functions to be used as a regular table. The only requirement to use table functions is to use a keyword TABLE.

Question: 3

What is a database link?

  • A database link is created to access a remote database from a local database.
  • One requirement to create a database link is that the database server should be able to connect to the remote database.
  • A private database link is available to the owner of the link while a public database link is available to all the users.
  • A fixed user database link is available only to the user specified in the link.

Question: 4

What is a profile?

  • Profile means to limit resources a user can use.
  • It can be created using the create profile ; statement
  • After a profile is created, it can be assigned to any user using the alter user statement.

Question: 5

What is B-tree index?

  • B tree index is the default index in Oracle.
  • A B-tree generally indicates down tree like structure where top node generally indicates the mid value. All leaves towards the left will have lower value than the node value and all the leaves on the right will have higher value than the node value.
  • This keeps on going until the entire set of index value is arranged in the tree structure.
  • A B-tree structure reduces the number of look up required to search any value.

Related Questions