Oracle DBA Interview Questions and Answers for Freshers - 2

Question: 6

Explain the difference between function, procedure and package?

A PL/SQL function or procedures is used to carry out a single task.

Although there is no restriction, a procedure is generally used to carry out an operation while a function is used to apply a function on one or many columns to return a value.

A package is a collection of procedures or functions. Even though there is no restriction on grouping different though there is no restriction on grouping different procedures or functions, generally they are grouped based on the modular approach used in application.

Question: 7

Explain the use of GLOBAL_NAMES setting?

The GLOBAL_NAMES setting determines whether database link can be created with a different name rather than the remote database name.

If the parameter is set to TRUE, then the database link must be created with the same name as remote database.

If the parameter is set to FALSE, then the database link can be created with a different name.

Oracle lets you create database link with different name even when the GLOBAL_NAMES is set to TRUE but the link cannot be used.

You will receive the error ORA-02085, if you try to use a database link with different name when GLOBAL_NAMES is set to TRUE.

Question: 8

What is EXECUTE IMMEDIATE?

EXECUTE IMMEDIATE is a statement required to process most dynamic SQL statements.

Question: 9

What is an index organized table?

Generally, a table is stored independently of any index and the index stores index value with the rowed of the table to enhance the performance of the queries.

In case of index organized tables, the entire row is stored with the index value and not just the rowid.

Question: 10

What permissions must be granted to users to allow them to connect to the database?

You should grant the CONNECT permission to the user.