Top 50 Oracle 10g Interview Questions You Must Prepare 24.Jun.2024

Q1. What Is The Functionality Of System Table Space?

To manage the database level tractions such as modifications of the data dictionary table that record information about the free space usage.

Q2. What Is Data Block?

Oracle database’s data is stored in data blocks. One data block corresponds to a specific number of bytes of physical database space on disk.

Q3. What Is Redo Log Buffer?

Changes made to the records are written to the on-line redo log files. So that they can be used in roll forward operations during database recoveries. Before writing them into the redo log files, they will first brought to redo log buffers in SGA and LGWR will write into files frequently. LOG_BUFFER parameter will decide the size.

Q4. What Is The Use Of Redo Log Information?

The information in a redo log file is used only to recover the database from a system or media failure prevents database data from being written to a database’s data files.

Q5. How Can We Plan Storage For Very Large Tables?

  • Limit the number of extents in the table
  • Separate table from its indexes.
  • Allocate sufficient temporary storage.

Q6. What Is An Extent?

An extent is a specific number of contiguous data blocks, obtained in a single allocation and used to store a specific type of information.

Q7. What Is User Account In Oracle Database?

A user account is not a physical structure in database but it is having important relationship to the objects in the database and will be having certain privileges.

Q8. What Is An Index Segment?

Each index has an index segment that stores all of its data.

Q9. What Are The Advantages Of View?

- To protect some of the columns of a table from other users.
- To hide complexity of a query.
- To hide complexity of calculations.

Q10. What Is The Optimal Parameter?

It is used to set the optimal length of a rollback segment.

Q11. What Is Public Database Link?

Public database link is created for the special user group PUBLIC. A public database link can be used when any user in the associated database specifies a global object name in a SQL statement or object definition.

Q12. What Is Dictionary Cache?

Dictionary cache is information about the database objects stored in a data dictionary table.

Q13. How Do I Display Row Number With Records?

To achive this use rownum pseudocolumn with query, like
SQL> select rownum, ename from emp;

Output:
-----------------
1 Scott
2 Millor
3 Jiyo
4 Smith

Q14. What Is Row Chaining?

In circumstances, all of the data for a row in a table may not be able to fit in the same data block. When this occurs, the data for the row is stored in a chain of data block (one or more) reserved for that segment.

Q15. What Are Clusters?

Group of tables physically stored together because they share common columns and are often used together is called cluster.

Q16. Any Three Pl/sql Exceptions?

Too_many_rows,
No_Data_Found,
Value_Error,
Zero_Error,
Others

Q17. What Is The Maximum Number Of Triggers, Can Apply To A Single Table?

12 triggers.

Q18. How Will You Activate/deactivate Integrity Constraints?

The integrity constraints can be enabled or disabled by ALTER TABLE ENABLE CONSTRAINT / DISABLE CONSTRAINT.

Q19. What Is An Integrity Constraint?

Integrity constraint is a rule that restricts values to a column in a table.

Q20. What Is A Datafile?

Every Oracle database has one or more physical data files. A database’s data files contain all the database data. The data of logical database structures such as tables and indexes is physically stored in the data files allocated for a database.

Q21. What Are The Factors Causing The Reparsing Of Sql Statements In Sga?

  • Due to insufficient shared pool size.
  • Monitor the ratio of the reloads takes place while executing SQL statements. If the ratio is greater than 1 then increase the SHARED_POOL_SIZE.
  • Database Logical & Physical Architecture

Q22. What Is An Index? How It Is Implemented In Oracle Database?

An index is a database structure used by the server to have direct access of a row in a table. An index is automatically created when a unique of primary key constraint clause is specified in create table command

Q23. What Is A Cluster Key?

The related columns of the tables are called the cluster key. The cluster key is indexed using a cluster index and its value is stored only once for multiple tables in the cluster.

Q24. What Is Sga?

The System Global Area in an Oracle database is the area in memory to facilitate the trfer of information between users. It holds the most recently requested structural information between users. It holds the most recently requested structural information about the database. The structure is database buffers, dictionary cache, redo log buffer and shared pool area.

Q25. Can A Tablespace Hold Objects From Different Schemes?

Yes.

Q26. Which Parameter In Storage Clause Will Reduce Number Of Rows Per Block?

PCTFREE parameter
Row size also reduces no of rows per block.

Q27. To See Current User Name

Sql> show user;

Q28. Explicit Cursor Attributes

There are four cursor attributes used in Oracle

cursor_name%Found,
cursor_name%NOTFOUND,
cursor_name%ROWCOUNT,
cursor_name%ISOPEN

Q29. What Is An Oracle View?

A view is a virtual table. Every view has a query attached to it. (The query is a SELECT statement that identifies the columns and rows of the table(s) the view uses.)

Q30. List The Factors That Can Affect The Accuracy Of The Estimations?

- The space used traction entries and deleted records, does not become free immediately after completion due to delayed cleanout.
- Trailing nulls and length bytes are not stored.
- Inserts of, updates to and deletes of rows as well as columns larger than a single data block, can cause fragmentation a chained row pieces.

Q31. Which Date Function Returns Number Value?

months_between

Q32. How Will You Enforce Security Using Stored Procedures?

Don’t grant user access directly to tables within the application.
Instead grant the ability to access the procedures that access the tables.
When procedure executed it will execute the privilege of procedures owner. Users cannot access tables except via the procedure.

Q33. What Is A Shared Pool?

Shared pool in oracle contains cache information that collects, parses, interprets and executes SQL statements that goes against database. This shared pool acts like a buffer for these SQL statements.

Q34. What Are Disadvantages Of Having Raw Devices?

We should depend on export/import utility for backup/recovery (fully reliable)
The tar command cannot be used for physical file backup, instead we can use dd command, which is less flexible and has limited recoveries.

Q35. What Is A Database Instance? Explain.

A database instance (Server) is a set of memory structure and background processes that access a set of database files. The processes can be shared by all of the users.

The memory structure that is used to store the most queried data from database. This helps up to improve database performance by decreasing the amount of I/O performed against data file.

Q36. What Are Some Advantages To Using Oracle's Create Database Statement To Create A New Database Manually?

You can script the process to include it in a set of install scripts you deliver with a product.

You can put your create database script in CVS for version control, so as you make changes or adjustments to it, you can track them like you do changes to software code.

You can log the output and review it for errors. You learn more about the process of database creation, such as what options are available and why.

Q37. What Is Use Of Rollback Segments In Oracle Database?

They allow the database to maintain read consistency between multiple tractions.

Q38. Display Odd/ Even Number Of Records

Odd number of records:
select * from emp where (rowid,1) in (select rowid, mod(rownum,2) from emp);
1
3
5

Even number of records:
select * from emp where (rowid,0) in (select rowid, mod(rownum,2) from emp)
2
4
6

Q39. What Is Hit Ratio?

It is a measure of well the data cache buffer is handling requests for data.
Hit Ratio = (Logical Reads – Physical Reads – Hits Misses)/ Logical Reads.

Q40. What Is A Database Link?

Database link is a named path through which a remote database can be accessed.

Q41. Can A View Based On Another View?

Yes.

Q42. Do A View Contain Data?

Views do not contain or store data.

Q43. What Is A Data Segment?

Data segment are the physical areas within a database block in which the data associated with tables and clusters are stored.

Q44. What Are The Characteristics Of Data Files?

A data file can be associated with only one database. Once created a data file can’t change size. One or more data files form a logical unit of database storage called a tablespace.

Q45. What Is A Redo Log?

The set of redo log files for a database is collectively known as the database redo log.

Q46. How To Implement The Multiple Control Files For An Existing Database?

Shutdown the database
Copy one of the existing controlfile to new location
Edit Config ora file by adding new control filename
Restart the database.

Q47. What Is The Use Of Control File?

When an instance of an Oracle database is started, its control file is used to identify the database and redo log files that must be opened for database operation to proceed. It is also used in database recovery.

Q48. What Is A Join? Explain The Different Types Of Joins?

Join is a query, which retrieves related columns or rows from multiple tables.

Self Join – Joining the table with itself.

Equi Join – Joining two tables by equating two common columns.

Non-Equi Join – Joining two tables by equating two common columns.

Outer Join – Joining two tables in such a way that query can also retrieve rows that do not have corresponding join value in the other table.

Q49. What Are The Components Of Logical Database Structure Of Oracle Database?

There are tablespaces and database’s schema objects.

Q50. It Is Possible To Use Raw Devices As Data Files And What Are The Advantages Over File System Files?

The advantages over file system files are that I/O will be improved because Oracle is bye-passing the kernel which writing into disk. Disk corruption will be very less.