Oracle Interview Questions – Set 04

by

What is the use of SHOW option in IMP command?

The SHOW option specifies when the value of show=y, the DDL within the export file is displayed.

How to add foreign keys in MySQL?

The foreign key is used to link one or more tables together. It matches the primary key field of another table to link the two tables. It allows us to create a parent-child relationship with the tables. We can add a foreign key to a table in two ways:

  • Using the CREATE TABLE Statement
  • Using the ALTER TABLE Statement
    Following is the syntax to define a foreign key using CREATE TABLE OR ALTER TABLE statement:

[CONSTRAINT constraint_name]
FOREIGN KEY [foreign_key_name] (col_name, …)
REFERENCES parent_tbl_name (col_name,…)

What is the meaning of recursive hints in Oracle?

The number of times a dictionary table is repeatedly called by various processes is known as recursive hint. Recursive hint is occurred because of the small size of data dictionary cache.

What will be the syntax to find current date and time in format “YYYY-MM-DD”?

SELECT TO_CHAR (SYSDATE, ‘YYYY-MM-DD HH24:MI:SS’) “Current_Date” FROM DUAL;

What is the use of FILE param in IMP command?

FILE param is used to specify the name of the export file to import. Multiple files can be listed, separated by commas.

What are the different types of database objects?

A list of different types of database objects:

  • Tables: This is a set of elements organized in vertical and horizontal fashion.
  • Tablespaces: This is a logical storage unit in Oracle.
  • Views: It is virtual table derived from one or more tables.
  • Indexes: This is a performance tuning method to process the records.
  • Synonyms: This is a name for tables.

What are the limitations of CHECK constraint?

The main limitation of CHECK constraint is that the condition must be a Boolean expression evaluated using the values in the row being inserted or updated and can’t contain sub queries.

How to convert a date to char in Oracle? Give one example.

The to_char() function is used to convert date to character. You can also specify the format in which you want output.

SELECT to_char ( to_date (’12-12-2012′, ‘DD-MM-YYYY’) , ‘YYYY-MM-DD’) FROM dual;
Or,

SELECT to_char ( to_date (’12-12-2012′, ‘DD-MM-YYYY’) , ‘DD-MM-YYYY’) FROM dual;

What is the usage of Save Points in Oracle database?

Save Points are used to divide a transaction into smaller phases. It enables rolling back part of a transaction. There are maximum 5 save points allowed in Oracle Database. Whenever an error is encountered, it is possible to rollback from the point where the SAVEPOINT has been saved.

What are actual and formal parameters?

Actual Parameters: Actual parameters are the variables or expressions referenced in the parameter list of a subprogram.

Let’s see a procedure call which lists two actual parameters named empno and amt:

raise_sal(empno, amt);
Formal Parameters: Formal parameters are variables declared in a subprogram specification and referenced in the subprogram body.

Following procedure declares two formal parameters named empid and amt:

PROCEDURE raise_sal(empid INTEGER, amt REAL) IS current_salary REAL;