Oracle Technical Interview Questions

 

• List the various components of the physical database structure of an Oracle database.

  The different components of the physical database structure of an Oracle database are listed down below:-

  • Greater than equal to two redo log files.
  • Greater than equal to one data file.
  • Greater than equal to one control file.

• The Oracle database has been developed using which language?

  The C language was used to develop the Oracle database.

• What do you understand about the logical storage structure of Oracle? List the main components of the logical database structure in the Oracle database.

  The Oracle Database allocates logical space for all data in the database in addition to the physical files. The logical units of database space allocation are data blocks, extents, segments, and tablespaces. At a physical level, the data is stored in data files on a disk. The data in the data files are stored in operating system blocks.

  There are two main components in the logical database structure in the Oracle database:-

  • Tablespaces: The basic storage allocation in an Oracle database is a tablespace. Each tablespace is composed of one or more physical (operating system) files. The SYSTEM tablespace is used to create each and every database. Other tablespaces are created by the Database Administrator.
  • Database's schema objects: A schema in Oracle is similar to an account or a username. Each object in the database is owned by a schema. Every Oracle database is created with two initial schemas: SYS, which is used to store the data dictionary, and SYSTEM, which often stores some data dictionary extensions as well as critical tables for other tools. Other schemas are created by the DBA. Each schema can be granted quotas in any tablespace.

  

• Define a tablespace in context with the Oracle database.

  The Logical Storage Unit of an Oracle database is called a tablespace. It is nothing but a set of related logical structures. Tablespace groups relate the logical structures of the database together. Logically, the data is stored in the tablespaces in Oracle and physically, the data is stored in the datafiles associated with the respective tablespaces.

  

• List the default tablespaces of Oracle.

  The default tablespaces of Oracle are as follows:-

  • The SYSTEM and SYSAUX tablespaces - These store system-generated objects such as data dictionary tables in them. One should not store any object in these tablespaces.
  • The USERS tablespace - It is used for ad-hoc users.
  • The UNDOTBS1 tablespace - Is used for holding the undo data.
  • The TEMP tablespace - It is the temporary tablespace that is used for storing intermediate results of sorting, hashing, and large object processing operations.

• What is the fundamental difference between an online and an offline tablespace?

  If a tablespace is offline, we cannot access data stored in it. In contrast to that, if a tablespace is online, the data of the tablespace is available for reading as well as writing.

• Define a SYSTEM tablespace. When it is created?

  When any database is created in the Oracle database system, a SYSTEM tablespace is automatically generated. The Data dictionary tables for the entire database are present in The SYSTEM tablespace. It is important that the SYSTEM tablespace remains online always as it contains the data dictionary that must be available to Oracle.

• Define an Oracle table.

  An Oracle table is a basic unit of data storage in an Oracle database. Every accessible information of any user is present in rows and columns of an Oracle table.

• Define a snapshot in the context of an Oracle database.

  Oracle uses snapshots, which are also known as materialized views, in order to replicate data to the non-master sites in a replicated environment. Snapshots are also used to cache the "expensive" queries in a data warehouse environment. So in short, a snapshot is nothing but a copy of the target master table from a single point in time.

  

• What do you understand by the RAW datatype?

  The RAW datatype is used to store values in binary data format in an Oracle Database. The maximum size for a RAW datatype in a table is 32767 bytes.

• What do you understand about the memory layers that are in the Oracle shared pool?

  There are two memory layers in the Oracle shared pool:-

  • Library Cache - The information about the SQL statements that were parsed, data about cursors, and any plan data that might be present is present in the Library Cache.
  • Data Dictionary Cache - The Data Dictionary Cache layer contains information about the accounts of the users, their privileges, and information about segments.

• What do you understand by a save point in the Oracle database?

  Savepoints can be used to break a transaction into smaller parts. It also allows the rolling back of a transaction. At any given time, at most five save points are allowed. Savepoints save our data, and therefore, whenever we encounter an error, we can roll back to the point where we had saved our SAVEPOINT.

• Describe the various Oracle database objects in brief.

  The description of various Oracle database objects is as follows:-

  • Tables: Tables are a set of elements organized in a vertical and horizontal fashion.
  • Tablespaces: Tablespaces are the logical storage unit in Oracle.
  • Views: A view can be defined as a virtual table that has been derived from one or multiple tables.
  • Indexes: Indexes are a performance tuning method to process the records.
  • Synonyms: Synonyms are names for tables.

• State the usage of the ANALYZE command in Oracle.

  The ANALYZE command in Oracle can be used to perform a number of functions on indexes, tables, or clusters. Some of the use cases of the ANALYZE command in Oracle are as follows:-

  • The ANALYZE command can be used to find out migrated and chained rows of the table or cluster. Also, it can be used to validate the structure of the object.
  • It helps in collecting the statistics about objects which the optimizer is using. After that, they are stored in the data dictionary.
  • It helps in deleting statistics used by objects from the data dictionary.

• Define hash clusters in Oracle.

  A hash cluster provides an alternative to a non clustered table with an index or an index cluster. Using indexed tables or index clusters, the Oracle Database is able to locate the rows in a table using key values that the database stores in a separate index.

• State the most common types of modules in Oracle forms.

  The most common types of modules in Oracle forms are as follows :-

  • Form module
  • Menu module
  • Pl/SQL Library module
  • Object Library module

• Define a synonym in the Oracle database and state its types.

  Synonyms are defined as aliases for tables, views, sequences or program units. There are two types of synonyms in the Oracle database:-

  • Private Synonyms: These synonyms are only accessible to the owner.
  • Public Synonyms: These synonyms are accessible to any database user.

• Which data type in the Oracle database has a varying length binary string?

  The BLOB data type in the Oracle database has a varying length binary string. It is used to store two gigabytes of memory and for it, the length needs to be specified in bytes. An example to illustrate the usage of the BLOB data type is given below:-

  Creating a table:-

  create table photos(name varchar(32) not null primary key, picture blob(10M));

  Querying for all logotype pictures:-

  select name,length(picture) from photos where name like '%logo%';

• Describe Logical backup in Oracle.

  The Logical backup mechanism is for reading a set of database records and writing them into a file. We can use an Export utility to take the backup while an Import utility can be used to recover from the backup.

• What do you understand by recursive hints in Oracle.

  We can define a recursive hint as to the number of times a dictionary table is continuously called by various processes. Recursive hints occur because of the small size of the data dictionary cache.

• State the limitations of the CHECK constraint.

  The biggest limitation of the CHECK constraint is as follows:- The condition needs to be a boolean expression that is being evaluated using the values in the row being inserted or updated. It can also not contain any subqueries whatsoever.

• State the usage of the GRANT option in the IMP command.

  The GRANT option in the IMP command can be used to import object grants.

• State the usage of the ROWS option in the IMP command.

  The ROWS option in the IMP command can be used to indicate whether the table rows should be imported or not.

• State the usage of the IGNORE option in the IMP command.

  The IGNORE option in the IMP command can be used to understand how object creation errors should be handled.

• State the usage of the INDEXES option in the IMP command.

  The INDEXES option in the IMP command can be used to determine whether indexes are imported or not.

• Give an example to convert a date to a char in Oracle.

  SELECT to_char(to_date('12-07-2021', 'DD-MM-YYYY'), 'YYYY-MM-DD') FROM dual;

  As we can see from the above example, the to_char() function can be used to convert a date to char in Oracle.

• Give an example to convert a string to a date in Oracle

  SELECT to_date ('2021-07-12', 'YYYY/MM/DD') FROM dual;

  As we can see from the above example, the to_date() function can be used to convert a string to a date in Oracle.

• Which function can be used to find the current date and time of the operating system on which the Oracle database is running?

  The SYSDATE() function can be used to find the current date and time of the operating system on which the Oracle database is running. The example given below shows how to do the same:-

  SELECT TO_CHAR (SYSDATE, 'YYYY-MM-DD HH24:MI:SS') "Current Date:" FROM DUAL;

• Explain actual parameters with an example.

  Actual parameters are defined as the variables or expressions referenced in the parameter list of a subprogram. An example of a procedure call that lists two actual parameters named empNumber and amount is as follows:-

  updateSalary(empNumber,amount);

• Explain formal parameters with an example.

  Formal parameters can be defined as variables declared in a subprogram specification and referenced in the subprogram body. An example of a procedure that declares two formal parameters named empID and amount: is as follows:-

  PROCEDURE updateSalary(empID INTEGER, amount REAL) IS currentSalary REAL;

• Write a query to find the average salary of employees from the Emp Oracle Table.

  A query to find the average salary of employees from the Emp Oracle Table is given below:-

  SELECT AVG(SALARY) FROM EMP GROUP BY SALARY; 

• Write a query to list the duplicate values in an Oracle table.

  Written below is a query to list the duplicate values in an Oracle table:-

  SELECT NAME, COUNT (NAME) FROM EMPLOYEE GROUP BY NAME HAVING COUNT (NAME) > 1; 

• Write a query to find out the student with the 2nd rank from the table STUDENTS_DETAILS.

  Written below is a query to find out the student with the 2nd rank from the table STUDENTS_DETAILS:-

   SELECT TOP 1 rank FROM (SELECT TOP 2 rank FROM STUDENT_DETAILS ORDER BY rank DESC) AS STUDENT ORDER BY rank ASC; 

• Illustrate the use of the TRANSLATE function with an example.

  The TRANSLATE function of Oracle is used for replacing a sequence of characters in a string with some other set of characters. The TRANSLATE function replaces a single character at a time. Let us take a look at an example to translate the string "ROLL41" to "RAMM42" with the help of the TRANSLATE function.

  TRANSLATE("ROLL41", "OL1", "AM2");

• Illustrate the use of the REPLACE function with an example.

  The REPLACE function of Oracle is used for replacing a sequence of characters in a string with some other set of characters. Let us take a look at an example to translate the string "R12S121" to "R34S341" with the help of the REPLACE function.

  REPLACE("R12S121", "12", "34");

  Note that every occurrence of the substring "12" is replaced by the substring "34".