Top 50 Oracle Dba Interview Questions You Must Prepare 27.Jul.2024
In Oracle database 10g at the time of database creation default temporary tablespace and default permanent tablespace for all users will be created during database-creation process. Once these two tablespaces are created, we don't have to specify them again during the database creation process.
Rollback segment holds the previous version of data block until either it gets committed or the space is not available for reuse. If a session is reading specific block from rollback segment while the other session has committed the information, then more rollback space is required to maintain more rollback information. In that case, this specific block ages out of the rollback segment and the reading or selecting session gets an error.
Applications can receive FAN event notification in the following situations:
- RAC service up
- RAC service down
- Node down
- Load balancing advisory
The shared pool portion of the SGA contains the library cache, the dictionary cache, buffers for parallel execution messages, and control structures. The total size of the shared pool is determined by the initialization parameter SHARED_POOL_SIZE.
The default value of this parameter is 8MB on 32-bit platforms and 64MB on 64-bit platforms. Increasing the value of this parameter increases the amount of memory reserved for the shared pool.
We can have the same rule in multiple rule sets.
Recovery catalog is an inventory of the backup taken by RMAN for the database. It is used to restore a physical backup, reconstruct it, and make it available to the Oracle server.
A Read-Only traction ensures that the results of each query executed in the traction are consistant with respect to the same point in time.
an interconnect network is a private network that connects all of the servers in a cluster. The interconnect network uses a switch/multiple switches that only the nodes in the cluster can access.
Oracle VM is the latest virtualization technology from oracle. It is built upon the open source project Xen.
PCTFREE parameter Row size also reduces no. of rows per block.
Snapshot is an object used to dynamically replicate data between distribute database at specified time intervals. In ver 7.0 they are read only.
The efficiency of an index comes from the fact that it lets us find necessary rows without having to scan all the rows of a table. They need a fewer disk I/O's than if we had to scan the table and hence are efficient.
- Mask the real name and owner of an object.
- Provide public access to an object.
- Provide location trparency for tables, views or program units of a remote database.
- Simplify the SQL statements for database users.
The following query gives the default temporary and permanent tablespace for a user:
SQL>SELECT default_tablespace,temporary_tablespace from dba_users where username='(username)';
A database instance (Server) is a set of memory structure and background processes that access a set of database files.
The process can be shared by all users. The memory structure that are used to store most queried data from database. This helps up to improve database performance by decreasing the amount of I/O performed against data file.
A row is stored in a hash cluster based on the result of applying a hash function to the row's cluster key value. All rows with the same hash key values are stored together on disk.
To generate read-consistent database information during database recovery and to rollback uncommitted tractions by the users.
Multiplexing is used to ensure availability of the control file. It me creating multiple copies of the control file. Ideally, you should keep those copies in different physical locations so that in case of media failure, you have at least one copy of control file that can be used.
Apps Listener usually running on All Oracle Applications 11i Nodes with listener alias as APPS_$SID is mainly used for listening requests for services like FNDFS and FNDSM.
The parse tree generation takes place in the library cache portion of the SGA(system global Area).
Utility adrepgen is used to compile Reports.Synatx is given below adrepgen userid=apps
The triggers will be fired N+1 number of times. Row level trigger will be fired n number of times, once for each row-level trigger and the statement-level trigger will be fired once.
A PFILE is a static, text file located in $ORACLE_HOME/dbs - UNIX.
An SPFILE (Server Parameter File) is a persistent server-side binary file that can only be modified with the "ALTER SYSTEM SET" command.
The set of Redo Log files YSDATE,UID,USER or USERENV SQL functions, or the pseudo columns LEVEL or ROWNUM.
You can use the tnsnames.ora configuration file to ensure that you have the INSTANCE_NAME parameter specified in it.
The administration tool is a J2EE-compliant Web application. We can use it to manage Oracle Ultra Search instances and access it from the intranet.
A stored procedure is pre-loaded in memory for faster execution. It allows the DBMS control of permissions for security purposes. It also eliminates the need to recompile components when minor changes occur to the database.
Performance tuning is an iterative process. We as a DBA may have to tune applications which is designed and implemented in production. The performance tuning at htis stage is referred to as reactive performance tuning.
In a RAC setup, each node has its own memory; however, they share physical datafiles. As we know that data blocks are read into memory for updates or query in a single instance database. Similarly, in a RAC set up, a node reads a block from datafile when the node needs it. However, in RAC setting, the required block may be already available in the memory of another node and it is faster to read block from the memory of another block than from a datafile. Therefore, RAC provides a mechanism to read block from memory of one node to the memory of another node. This mechanism is called cache fusion.
Oracle uses high-speed interconnect to communicate between nodes. GES monitors and the Instance Enqueue process manages the cache fusion.
An ASM metadata includes the following:
1) The disks that belong to a disk group
2) Amount of space available within a disk group
3) The filenames of the files within a disk group
4) The location of disk group datafile data extents
5) A redo log that records information about automatically changing data blocks
Row Migration:
Row Migration refers to rows that were moved to another blocks due to an update making them too large to fit into their original blocks.
Oracle will leave a forwarding pointer in the original block so indexes will still be able to "find" the row. Note that Oracle does not discriminate between chained and migrated rows, even though they have different causes. A chained row is a row that is too large to fit into a single database data block.
For example, if you use a 4KB blocksize for your database, and you need to insert a row of 8KB into it, Oracle will use 3 blocks and store the row in pieces.
Some conditions that will cause row chaining are:
- Tables whose row size exceeds the blocksize.
- Tables with long and long raw columns are prone to having chained rows.
- ables with more then 255 columns will have chained rows as Oracle break wide tables up into pieces.
Detecting row chaining:
This query will show how many chained (and migrated) rows each table has:
SQL>SELECT owner, table_name, chain_cnt FROM dba_tables WHERE chain_cnt > 0;
To see which rows are chained:
SQL>ANALYZE TABLE tablename LIST CHAINED ROWS;
This will put the rows into the INVALID_ROWS table which is created by the utlvalid.sql script (located in $ORACLE_HOME/rdbms/admin).
Oracle Warehouse Builder(OWB) is composed of Design centre, Control center manager, target schema, Warehouse builder repository, Repository browser, control center service.
Oracle clusterware manages CRS resources based on the configuration information of CRS resources stored in OCR(Oracle Cluster Registry).
The goal of database security is to prevent unauthorized use of database or its components.Database security depends on system and network security.
Following statements can be used to start or stop an instance on RAC:
- Find_group(function),
- Find_column(function).
A latch is a semaphore or an on/off switch in Oracle database that a process must access in order to conduct certain type of activities. Latches govern the usage of Oracle's internal resources by its processes. They enforce serial access to the resources and limit the amount of time for which a single process can use a resource.
There are over 80 latches available in Oracle.
We have v$ views that are instance specific. In addition we have GV$ views called as global views that has an INST_ID column of numeric data type. GV$ views obtain information from individual V$ views.
There are three most common types of complex master-detail relationships:
- master with dependent details
- master with independent details
- detail with two masters
A RAC setup shares datafiles, control files, SP Files, redo log files; therefore, these files must be stored in the cluster-aware shared storage.
Indexes are automatically maintained and used by Oracle. Changes to table data are automatically incorporated into all relevant indexes.
A distributed database is a network of databases managed by multiple database servers that appears to a user as single logical database. The data of all databases in the distributed database can be simultaneously accessed and modified.
The client deals with the user interface part of the system.
By changing the order of the columns that are used in the index, in the Where condition, or by concatenating the columns with some constant values.
Data guard is a setup in which you can have one or more standby database available for a production database.
A database trigger is event driven program. You do not need to invoke a trigger. It is fired whenever triggering event happens.
Vbx control provide a simple method of building and enhancing user interfaces. The controls can use to obtain user inputs and display program outputs. vbx control where originally develop as extensions for the ms visual basic environments and include such items as sliders, rides and knobs.
Parallel degree limit is an allocation method for degree of parallelism. It specifies the maximum degree of parallelism for an operation. It can be set using the PARALLEL_DEGREE_LIMIT_P1 parameter.
The default value of this parameter is null which indicates no limit is imposed.
USER_TAB_PRIVS_MADE, USER_TAB_PRIVS_RECD.
Canvas views are the back ground objects on which you place the interface items (Text items), check boxes, radio groups etc., and boilerplate objects (boxes, lines, images etc.,) that operators interact with us they run your form . Each canvas views displayed in a window.