Oracle dba Interview Questions

Get Technical Answers for Oracle dba Interview Questions by Oracle Developers, Advanced technical questions for Freshers and Experience Persons. Get Free practical tests

1. What Is Oracle Database?

Oracle Database is a relational database management system (RDBMS) which is used to store and retrieve the large amounts of data. Oracle Database had physical and logical structures. Logical structures and physical structures are separated from each other

2. Explain Oracle Grid Architecture?

Grid computing is a information technology architecture that provides lower cost enterprise information systems. Using grid computing, independent hardware, and software components can be connected and rejoined on demand to meet the changing needs of businesses. It also enables the use of smaller individual hardware components.

Oracle Database Administrator Certification Training

  • Master Your Craft
  • Lifetime LMS & Faculty Access
  • 24/7 online expert support
  • Real-world & Project Based Learning

 

3. What Is The Difference Between Large Dedicated Server And Oracle Grid?

Large dedicated server

It has expensive costly components.

High incremental costs.

It has single point of failure.

Enterprise service at higher cost.

Oracle Grid

It has low cost modular components.

Low incremental costs.

It has no single point of failure.

Enterprise service at low cost.

Subscribe to our youtube channel to get new updates..!

 

4. What Are The Computing Components Of Oracle Grid?

The computing componenets of oracle grid are

Oracle Enterprise Manager and Grid Control

Oracle 10g Database and Real Application Clusters.

ASM Storage Grid.

Oracle Database Administrator Certification Training

Weekday / Weekend Batches

 

5. What Is Server Virtualization?

Oracle Real Application Clusters 10g (RAC) enables a single database to run across multiple clustered nodes in a grid, pooling the processing resources of several standard machines.

6. What Is Storage Virtualization?

The Oracle Automatic Storage Management (ASM) is a feature of Oracle Database 10g which provides a virtual layer between the database and storage so that group of disks can be treated as a single disk group and disks can be dynamically added or removed while keeping databases online.

7. What Is Grid Management Feature?

The Grid Management feature of Oracle Enterprise Manager 10g provides a single console to manage multiple systems together as a logical group.

8. When Oracle Allocates An Sga?

When Oracle starts, it reads the initialization parameter file to determine the values of initialization parameters. After this, it allocates an SGA and creates background processes.

9. What Is An Oracle Instance?

When you start, the database instance comes into picture into system memory. Combination of the SGA and the Oracle processes is called an Oracle instance.

10. What Are The Several Tools For Interacting With The Oracle Database Using Sql?

There are several tools for interfacing with the database using SQL

Oracle SQL*Plus and iSQL*Plus

Oracle Forms, Reports, and Discoverer

Oracle Enterprise Manager

Third-party tools

11. How Oracle Works?

An instance has started on the database server.

A client established a connection to the server, using the proper Oracle Net Services driver.

The server creates a dedicated server process on behalf of the user process.

The user executes SQL statement and commits the transaction.

The server process receives the statement and checks for any shared SQL area that contains a similar SQL.

The server process retrieves data from datafile (table) or SGA.

The server process modifies data in the SGA area. The DBWn process writes modified blocks permanently to disk. The LGWR process records the transaction in the redo log file.

The server process sends a message to the application.

12. What Contains Oracle Physical Database Structure?

It contains

Datafiles

Control Files

Redo Log Files

Archive Log Files

Parameter Files

Alert and Trace Log Files

Backup Files

13. What Is A Tablespace

Oracle use Tablespace for logical data Storage. Physically, data will get stored in Datafiles. Datafiles will be connected to tablespace. A tablespace can have multiple datafiles. A tablespace can have objects from different schema's and a schema can have multiple tablespace's. Database creates "SYSTEM tablespace" by default during database creation. It contains read only data dictionary tables which contains the information about the database.

14. What Is A Control File?

Control file is a binary file which stores Database name, associated data files, redo files, DB creation time and current log sequence number. Without control file database cannot be started and can hamper data recovery.

15. What Are Data Blocks?

Oracle stores data in data blocks also called as logical blocks, Oracle blocks or pages. A data block represents specific number of bytes of space on disk.

16. What Is An Extent?

An extent is a specific number of consecutive data blocks allocated for storing a specific type of information

17. What Is A Segment?

A segment is a group of extents, each of which has been allocated for a specific data structure and all of which are stored in the same table-space

18. What Is Rollback Segment ?

Database contain one or more Rollback Segments to roll back transactions and data recovery.

19. What Are The Different Type Of Segments

Data Segment(for storing User Data), Index Segment (for storing index), Rollback Segment and Temporary Segment

20. What Is An Oracle Schema?

A user account and its associated data including tables, views, indexes, clusters, sequences,procedures, functions, triggers,packages and database links is known as Oracle schema. System, SCOTT etc are default schema's. We can create a new Schema/User. But we can't drop default database schema's.

21. When And How Oracle Database Creates A Schema?

Oracle Database automatically creates a schema when you create a user.

22. What Is A View?

A view is a tailored presentation of the data contained in one or more tables or other views. A view is output of a query and treats it as a table. Therefore, a view can be thought of as a stored query or a virtual table. A view is not assigned any storage space, nor does a view actually contain data

23. How Views Are Used?

It provides security by restricting access to a predetermined set of rows or columns of a table. It hides data complexity. It simplifies statements for the user.

An example would be the views, which allow users to select data from multiple tables without actually knowing how to perform a join.

It presents the data in a different perspective from that of the base table. It isolate applications from changes in definitions of base tables. It saves complex queries.

24. What Are Materialized Views?

These are schema objects that are used to summarize, compute, replicate, and distribute data. They can be used in various environments for computation such as data warehousing, decision support, and distributed or mobile computing and it also provides local access to data rather than accessing from remote sites. In data warehouses, MVs are used to compute and store aggregated data.

25. What Is A Dimension?

A dimension is hierarchical relationships between pairs of columns or column sets. Each value at the child level is tied with one value at the parent level. A dimension is a container of logical relationships between columns and it does not contain any data.

26. Explain Sequence Generator In Oracle?

The sequence generator gives a sequential series of numbers.The sequence generator is especially useful for generating unique sequential numbers. Sequence numbers are Oracle integers of up to 38 digits defined in the database. A sequence definition provides information, such as

The sequence name

ascending or descending sequence

The interval between numbers

Whether Oracle should cache sequence numbers in memory

Sequence numbers are generated independent of any tables. The same sequence generator can be used for many tables. Sequence number generation can be used to produce primary keys for your data automatically. Oracle stores the definitions of all sequences for a particular database as rows in data dictionary table in the SYSTEM table- space.

27. What Is An Index?

Indexes are structures associated with tables and clusters. You can create indexes on one or more columns of a table to enhance the speed of SQL statement execution on that table. Just as the index in Oracle manual helps you to locate information faster than if there were no indexes. An Oracle index provides a faster access path to table data.

28. List Out Indexing Scheme That Oracle Provides?

Oracle provides several indexing schemes

B-tree indexes

B-tree cluster indexes

Hash cluster indexes

Reverse key indexes

Bitmap indexes

Bitmap join indexes

29. What Is A Synonyms?

A synonym is an alias for database objects such as table, view, materialized view, sequence, procedure, function, package. Because a synonym is simply an alias, it does not require storage other than its data dictionary definition.

30. Give An Example Of Synonyms?

CREATE PUBLIC SYNONYM sales FOR jward.sales_data;

After the public synonym is created, you can query the table SALES_DATA with a simple SQL statement

SELECT * FROM sales;

31. What Is Concurrency In Oracle?

The multiuser database management system's concern is how to control concurrency, which is the concurrent access of the same data by multiple users. Without sufficient concurrency controls, data could be updated or changed improperly, trading off with data integrity. To manage data concurrency is to make each user wait for a turn.

The goal of a database management system is to decrease the waiting time so it is either nonexistent or negligible to each user. The data manipulation language statements should proceed with as little intervention as possible, and destructive interactions among concurrent transactions should be prevented.

Destructive interaction is any interaction that updates data or alters underlying data structures incorrectly. Neither performance nor data integrity can be sacrificed. Oracle solves such problems by using various types of locks and a multi-version consistency model.

32. Explain Briefly Shared Server Architecture?

This architecture removes the need for a dedicated server process for each connection. A dispatcher routes various incoming network session requests to a shared server processes pool. An idle shared server process from a shared pool of server processes chooses a request from a common queue, which means a small number of shared servers can do the same amount of processing as many dedicated servers.

It is because of the amount of memory required for each user is comparatively small, less memory and process management are required, and more users can be supported.

33. What Are Archiver Processes?

It copies redo log files to a designated storage device after a log switch has occurred. Archiver processes are there only when the database is in ARCHIVELOG mode, and automatic archiving is enabled. An Oracle instance can have up to 10 Archi ver processes (ARC0 to ARC9). The LGWR process starts a new ARCn process whenever the current number of Archiver processes is insufficient to handle the workload.

34. What Are Job Queue Processes?

Job queue processes are used for processing batch. They run user jobs. They can be viewed as a scheduler service that can be used to schedule jobs like PL/SQL statements or procedures on an Oracle instance.

 

35. What Is Recoverer Process?

The re-coverer process (RECO) is a background process used with the distributed database configuration that automatically resolves failures. The RECO process of a node automatically connects to other databases involved in an in- doubt distributed transaction.

Submit an interview question

Categories

Request for more information

Webinar

Python tutorial for beginners

5th April | 08:00 AM

150 Registered

Tabres
Tabres
cloud computing
My name is tabres Am from hkr trainings I have good knowledge about the cloud computing services. I have been working on this field more than 10 years. Being here it is good opportunity because possibilities for enterprise engagement and as well as chance to attract with very good PhD students. Cloud computing has become one of the most used structures to installation High Availability (HA) solutions for its flexibility, and elasticity. Connect with me LinkedIn and twitter. Thank you

To Top