ORACLE DBA INTERVIEW QUESTIONS PDF
This post has awesome oracle dba interview questions and answers Download of oracle dba interview questions as PDF is also given below. Oracle DBA Interview Questions 1. Explain the difference between a hot backup and a cold backup and the benefits associated with each. A hot backup is. + Oracle Dba Interview Questions and Answers, Question1: What is the difference between RMAN and a traditional hotbackup? Question2: What are bind.
|Language:||English, Spanish, Indonesian|
|Genre:||Fiction & Literature|
|ePub File Size:||28.82 MB|
|PDF File Size:||20.63 MB|
|Distribution:||Free* [*Regsitration Required]|
Oracle DBA interview questions - Free download as Word Doc .doc), PDF File . pdf), Text File .txt) or read online for free. Oracle DBA Interview Question. MID LEVEL ORACLE DBA INTERVIEW QUESTIONS. SAMPLE Question 1. what is difference between Co-related sub query and nested sub query? Answer: . Oracle DBA Interview Questions Answers. By. Gitesh Trivedi. Publisher: Kendba Services. Copy right protected @ Kendba Services.
If not, the new data will be appended to the existing tables. If the sequences are not dropped, they will generate numbers inconsistent with the rest of the database. Before form , After form , Before report, Between page, After report.
Because for each query, report has to open a separate cursor and has to rebind, execute and fetch data. What Is Coordination Event? Any event that makes a different record in the master block the current record is a coordination causing event. An Ole server application creates ole Objects that are embedded or linked in ole Containers, ex. OLE containers provide a place to store, display and manipulate objects that are created by ole server applications.
What Is An Object Group? An object group is a container for a group of objects; you define an object group when you want to package related objects, so that you copy or reference them in other modules. What Is An Lov? An LOV is a scrollable popup window that provides the operator with either a single or multi column selection list.
After the query is executed but before the report is executed and the records are displayed. A way in which to pass control and possibly arguments form Oracle report to another Oracle products of 3GL and then return control and back to Oracle reports. What Is The User-named Editor? A user named editor has the same text editing functionality as the default editor, but, because it is a named object, you can specify editor attributes such as windows display size, position, and title.
A static record group is not associated with a query, rather, you define its structure and row values at design time, and they remain fixed at runtime.
What Is A Record Group? However, unlike database tables, record groups are separate objects that belong to the form module which they are defined.
What Is A Property Clause? A property clause is a named object that contains a list of properties and their settings. Once you create a property clause you can base other object on it. An object based on a property can inherit the setting of any property in the clause that makes sense for that object. What Is A Physical Page? A physical page is a size of a page. That is output by the printer. The logical page is the size of one page of the actual report as seen in the Previewer.
This option can be shortened to TERM. This is created using the utlxplan. Once the explain plan table exists you run the explain plan command giving as its argument the SQL statement to be explained. Explain plans can also be run using tkprof. The tkprof tool is a tuning tool used to determine cpu and execution times for SQL statements. What Is A Cartesian Product? Cartesian product is the result of an unrestricted join of two or more tables. By use of the exclamation point "!
They can be used in exception handling to report, or, store in an error log table, the error that occurred in the code. They can be used to hold values for use in later queries or calculations.
This happens with triggers. It occurs because the trigger is trying to update a row it is currently using. The usual fix involves either use of views or temporary tables so the database is selecting from one while updating the other. INI file to load the required drivers to access that particular back end database.
The name of the DBMS, the location of the source and the database dependent information. ODBC driver. What Is Inheritance? Inheritance is a method by which properties and methods of an existing object are automatically passed to any object derived from it.
A file server just transfers all the data requested by all its client and the client processes the data while a database server runs the query and sends only the query output. Because an event driven program is always waiting for something to happen before processing. The application cannot use any special features of the backend server. The application can be connected to any back end server that is supported by the API.
Manage resources optimally across multiple clients. Controlling database access and security. Protecting the databse and recovering it from crashes. Enforcing integrity rules globally. A client process at first establishes connection with the Server.
Star, Bus, Ring.
Heterogeneity of the system results in reduced reliablity, it May not be suitable for all applications. Managing and tuning networks becomes difficult. Flexibility of the system, scalability, cost saving, centralised control and implementation of business rules, increase of developers productivity, portability, improved network and resource utilization.
Because an integrity constraint is automatically checked while data is inserted into a table. A stored has to be specifically invoked. Because it is easier to define an integrity constraint than a database trigger. What Is A Transaction? A transaction is a set of operations that begin when the first DML is issued and end when a commit or rollback is issued. Database triggers can be used to enforce business rules, to maintain derived values and perform value-based auditing.
What Is An Integrity Constraint? An integrity constraint allows the definition of certain restrictions, at the table level, on the data that is entered into a table. What Is A Event Handler? An event handler is a routine that is written to respond to a particular event. Foreground process and, Background process. When a stored procedure is called, only the procedure call is sent to the server and not the statements that the procedure contains.
What Is Event Trigger? An event trigger , a segment of code which is associated with each event and is fired when the event occurs. A host based application is re-engineered to run in smaller or LAN based environment.
With rightsizing , one would move applications to the most appropriate server platforms. Applications that have outgrown their environment are re-engineered to run in a larger environment. This is upsizing. To reduce the network traffic and for application sharing and implementing business rules. The client deals with the user interface part of the system. Preemptive refers to the fact that each task is alloted fixed time slots and at the end of that time slot the next task is started.
To protect some of the columns of a table from other users. To hide complexity of a query. To hide complexity of calculations.
CYCLE specifies that the sequence continue to generate values after reaching either maximum or minimum value. After pan-ascending sequence reaches its maximum value, it generates its minimum value. After a descending sequence reaches its minimum, it generates its maximum. What Is A Database Link? Database link is a named path through which a remote database can be accessed. To modify the datatype of a column the column must be empty. Only one LONG column is allowed. CHAR pads blank spaces to the maximum length.
What Is On Delete Cascade? It enables rolling back part of a transaction. Maximum of five save points are allowed. What Is Referential Integrity Constraint? Maintaining data integrity through a set of rules that restrict the values of one or more columns of the tables based on the values of primary key or unique key of the referenced table.
Integrity constraint is a rule that restricts values to a column in a table. What Is Rowid? ROWID is a pseudo column attached to each row of a table. MINUS - returns all distinct rows selected by the first query but not by the second. Explain Connect By Prior?
Retrieves rows in hierarchical order eg. What Is Correlated Sub-query? Correlated sub-query is a sub-query, which has reference to the main query. What Is The Sub-query? Sub-query is a query whose return values are used in filtering conditions of the main query. What Is A Join? 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. What's An Sql Injection? Deleted records can be rolled back or committed. Insert, update, delete, select, references, index, execute, alter, all. It is used to drop constraints specified on the table.
What Operator Performs Pattern Matching? When this clause is used with the DROP command, a parent table can be dropped even when a child table exists.
One can create one logical record from multiple physical records using one of the following two clauses:. Because, in a where clause the condition is applied during data retrievalthan after retrieving the data. Before report, Before form, After form , Between page, After report. By selecting the cross products tool and drawing a new group surrounding the base group of the cross products.
Block is a single mechanism for grouping related items into a functional unit for storing, displaying and manipulating records. What Is A Display Item? Display items are similar to text items but store only fetched or assigned values. What Is An Ole? Name of the parameter file to be passed for export. What Is The Optimal Parameter? It is used to set the optimal length of a rollback segment. What Erase Package Procedure Does? Copy is package procedure and writes values into a field.
Name in is a package function and returns the contents of the variable to which you apply. A break group is used to display one record for one group ones. While multiple related records in other group can be displayed. An anchoring object is a print condition object which used to explicitly or implicitly anchor other objects to itself. What Is A Library? A library is a collection of subprograms including user named procedures, functions and packages.
A master detail relationship is an association between two base table blocks- a master block and a detail block. The relationship between the blocks reflects a primary key to foreign key relationship between the tables on which the blocks are based. A panel is the no. Each server and background process can write an associated trace file. When an internal error is detected by a process or user process, it dumps information about the error to its trace.
This can be used for tuning the database. Choosing an executing planbased on the access paths available and the ranks of these access paths. The goal of the optimizer is to choose the most efficient way to execute a SQL statement.
Oracle DBA interview questions
What Is Execution Plan? The combinations of the steps the optimizer chooses to execute a statement is called an execution plan. Installing and upgrading the Oracle Server and application tools. Allocating system storage and planning future storage requirements for the database system. Managing primary database structures tablespaces Managing primary objects table,views, indexes Enrolling users and maintaining system security.
Ensuring compliance with Oralce license agreement Controlling and monitoring user access to the database. Monitoring and optimizing the performance of the database. Planning for backup and recovery of database information.
Maintain archived data on tape Backing up and restoring the database. Contacting Oracle Corporation for technical support. Difference Between Procedure And Function.? Form trigger FT Fires when user presses a key or navigates between fields on the screen -Can be row level or statement level No distinction between row level and statement level.
Can be fired only from the form that define the trigger. Can cause other database triggers to fire, but not other form triggers. Cursor For Loop is a loop where oracle implicitly declares a loop variable, the loop index that of the same record type as the cursor's record.
A hot backup is basically taking a backup of the database while it is still up and running and it must be in archive log mode. A cold backup is taking a backup of the database while it is shut down and does not require being in archive log mode. The benefit of taking a hot backup is that the database is still available for use while the backup is occurring and you can recover the database to any point in time.
The benefit of taking a cold backup is that it is typically easier to administer the backup and recovery process. In addition, since you are taking cold backups the database does not require being in archive log mode and thus there will be a slight performance gain as the database is not cutting archive logs to disk.
I would create a text based backup control file, stipulating where on disk all the data files where and then issue the recover command with the using backup control file clause. A data block is the smallest unit of logical storage for a database object. As objects grow they take chunks of additional storage that are composed of contiguous data blocks. These groupings of contiguous data blocks are called extents.
All the extents that an object takes when grouped together are considered the segment of the database object. Both the truncate and delete command have the desired outcome of getting rid of all the rows in a table. The difference between the two is that the truncate command is a DDL operation and just moves the high water mark and produces a now rollback.
The delete command, on the other hand, is a DML operation, which will produce a rollback and thus take longer to complete. Fact tables and dimension tables. A fact table contains measurements while dimension tables will contain data that will help describe the fact tables.
Disable the foreign key constraint to the parent, drop the table, re-create the table, enable the foreign key constraint. Alter database backup control file to trace. Create a plan table with utlxplan. Look at the explain plan with utlxplp. Explain An Ora You get this error when you get a snapshot too old within rollback. It can usually be solved by increasing the undo retention or increasing the size of rollbacks.
You should also look at the logic involved in the application getting the error message. Also one can use function in Select Sql statement but not procedure. What Is It's Default Setting? If it is not specified the tables that already exist will be skipped.
If it is specified, the error is ignored and the tables data will be inserted. The default value is N. The packages used by DBAs may include: This is bad since it makes tracking which table the constraint belongs to or what the constraint does harder. This results in the index that is automatically generated being placed in then users default tablespace. Since this will usually be the same tablespace as the table is being created in, this can cause serious performance problems.
The index is created in the users default tablespace and all sizing information is lost. Oracle doesn't store this information as a part of the constraint definition, but only as part of the index definition, when the constraint was disabled the index was dropped and the information is gone.
Why Or Why Not? You can't use hot backup without being in archivelog mode. So no, you couldn't recover. This is caused by large or long running transactions that have either wrapped onto their own rollback space or have had another transaction write on part of their rollback space.
This can be prevented or mitigated by breaking the transaction into a set of smaller transactions or increasing the size of the rollback segments and their extents. The quick and dirty way is to look at the number of blocks the table is actually using and ratio the number of rows in the table to its number of blocks against the number of expected rows. There are several ways. Another if you are on UNIX is to do a "ps -ef grep oracle wc -l?
Another method, although it is hard to document and isn? OPEN syntax is used. What Is Oci. What Are Its Uses? Oracle Call Interface is a method of accesing database from a 3GL program. What Is A Outer Join? Outer Join--Its a join condition used where you can query all the rows of one of the tables in the join condition even though they don? Rename is a permanent name given to a table or column whereas Alias is a temporary name given to a table or column which do not exist once the SQL statement is executed.
A trigger is one or more statements of SQL that are being executed in event of data modification in a table to which the trigger belongs. Triggers enhance the security, efficiency, and standardization of databases. Triggers can be beneficial when used: This is useful if you need to transform data from the way the user sees it to some internal database format. This is useful for maintaining relationships between data or in keeping audit trail information.
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.
What Causes It? A Cartesian product is the result of an unrestricted join of two or more tables. It is causes by specifying a table in the FROM clause without joining it to another table.
If changes are made to the tables and these tables are the base tables of a view, then the changes will be reference on the view. If Yes - Under What Conditions? It is not possible. Only if you access next value in the session, current value can be accessed.
Database resource manager allows us to create resource plans, which specify how much of our resources should go to various consumer groups. We can group users based on their resource requirement and we can have the database resource manager allocate a preset amount of resources to these groups.
We can easily prioritize our users and jobs. DBA's can automatically switch users from one resource group to another ,based on preset resource usage criteria,and can limit the amount of undo space a resource group can use. Database resource manager is composed of the following four elements: Resource consumer group, resource plan,resource allocation method,resource plan directive. A resource consumer group is used to group together similar users based on their resource needs.
What Is A Resource Plan? The resource plan lays how resource consumer groups are allocated resources. Each resource plan contains a set of resource consumer groups that belong to this plan, together with instructions as to how resources are to be allocated among these groups. For instance,a resource plan may dictate CPU resources be allocated among three resource consumer groups so that the first group gets 60 percent and the remaining two groups get 20 percent each of the total CPU time.
What Is A Subplan? A subplan is a part of the resource plan that enables the allocation of resources in greater detailamong the resource consumer groups.
The resource allocation method indictates the specific method we choose to use to allocate resources like the CPU. The available methods of allocating the database resources are CPU method, Idle time, Execution time limit, Undo pool, Active session pool, automatic consumer group switching, canelling SQL and terminating sessions, parallel degree limit.
What Is A Cpu Method? It is a type of resource allocation method. What Is An Idle Time? We can direct that a user's session be terminated after it has been idle for a specified period of time. We can also specify that only idle sessions blocking other sessions be terminated. We can control resource usageby setting a limit on the maximum execution time of an opration. What Is An Undo Pool?
Oracle DBA Interview Questions & Answers
By setting an undo pool directive, we can limit the total amount of undos that can be generated by a consumer resource group. We can set a maximum allowable number of concurrent sessions with in any consumer resource group. All sessions that are beyond the maximum limit are queued for execution after the freeing up of current active sessions. Using this method, we can specify that a user session be automatically switched to a different group after it runs more than a specified number of seconds.
The group that the session should switch to is called as switch group,and the time limit is the switch time. We can use this method to specify the limit of the degree of parallelism for an operation. It is an element of database resource manager.
It links a resource plan to a specific resource consumer group. What Is Oracle Database Vault? Oracle Database Vault is a security solution from oracle corporation.
It restricts access to certain areas of the database. Even users with administrative privileges have restricted access. This fine-grained access control protects the database from super-privileged users. Oracle Database Vault has the following components: It is formed of the following components that helps us manage security for the database instance.
They are: What Is A Realm? A realm is a functional grouping of database schemas,objects and roles that must be secured.
After grouping we can use realms to control the use of system privileges to specific accounts or roles. This provides fine-grained access control. What Is A Command Rule? A command rule is a special rule created to control how users can execute almost any SQL statement.
Do Command Rules Work Alone? They work with rule sets to determine whether or not a statement is allowed. What Is A Factor? A factor is a named variable or a attribute such as user location, database IP address, or session user, which oracle database vault can recognize and secure. We can use factors for activities such as authorizing database accounts to connect to the database or creating filter logic to restrict the visibility and manageability of data.
What Is An Identity? Each factor can have one or more identities. An identitiy is the actual value of a factor. A factor can have several identities depending on the factor retrieval method or its identity mapping logic. What Is A Rule Set? A rule set is a collection of one or more rules that we can associate with a realm authorization, command rule, factor assignment, or secure application role.
The rule set evaluates to true or false based on the evaluation of each rule it contains and the evaluation type All True or Any True. Can Rules Be Reused? We can have the same rule in multiple rule sets. What Are Secure Application Roles? A secure application role is a special Oracle Database role that can be enabled based on the evaluation of an Oracle Database Vault rule set. It is a command-line utility that is used to perform maintenance tasks on Oracle Database Vault Installation.
Give Details On Dvsys Schema? This schema contains the roles, views, accounts, functions, and other database objects that the Oracle Database Vault uses.
Give Details On Dvf Schema? The DVF schema contains public functions to retrieve at run time the factor values set in the Oracle Database Vault access control configuration. Oracle ultra search is made of the following components: The Oracle Ultra Search crawler is a Java process activated by the Oracle server based on a a set schedule.
When activated, the crawler spawns a configurable number of processor threads that fetch documents from various data sources and index them using Oracle Text.
This index is used for querying. Data sources can be Web sites,database tables, files, mailing lists, Oracle Application Server Portal page groups, or user-defined data sources. Oracle Text provides text indexing and search capabilities required to index and query data retrieved from the data sources.
The back end indexes information from the crawler and serves up the query results. The Oracle Ultra Search middle tier components are Web applications.
Top 40 oracle dba interview questions and answers - Techgoeasy
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. Oracle Ultra Search includes highly functional query applications to query and display search results. The administration tool is independent from the Oracle Ultra Search query application.
Therefore, the administration tool and query application can be hosted on different computers to enhance security and scalability. The application can completely customize the HTML interface. The e-mail Java API accesses archived e-mails and is used by the query application to display e-mails.
It can also be used to build our own custom query application. It accepts robot metatag instructions from the agent for the target document, and it transforms the original document contents for indexing control.
An Ultra Search instance can be created to provide isolation for the data collections that have been crawled. We can create a read-only snapshot of a master Oracle Ultra Search instance. This is useful for query processing or for backup.
We can also make a snapshot instance updatable. This is useful when the master instance is corrupted and we want to use a snapshot as a new master instance. Oracle Warehouse Builder OWB is an information integration tool that leverages the Oracle Database to transform data into high-quality information. The Oracle Database is a central component in the Warehouse Builder architecture because the Database hosts the Warehouse Builder repository and the code generated by Warehouse Builder.
Oracle Warehouse Builder OWB is composed of Design centre, Control center manager, target schema, Warehouse builder repository, Repository browser, control center service. What Is Omb Plus? We use OMB Plus to create, modify, delete, and retrieve object metadata in Warehouse Builder design and runtime repositories. From OMB Plus console enter the following: What Is Oracle Vm?
Oracle VM is the latest virtualization technology from oracle. It is built upon the open source project Xen. ASM is the recommended storage management solution from oracle as it provides an alternative to conventional volume managers,file systems and raw devices.
Within the diskgroups an file system interface maintains the Oracle database files. ASM simplifies database storage by consolidating disks into disk groups. The files distributed across the set of disks in a disk group are striped or distributed across the disks to eliminate hot spots and improve performance. Thus they perform equally well as raw disks. Disks can be added and removed from the disk groups online i. When disks are added or removed ASM automatically redistributes the contents with out any downtime.
Thus ASM eliminates down time. Give Details On Server-based Mirroring? This is a flexible option provided by ASM. The normal and redundant options of ASM provides two and three way mirroring. Files are created at specified location, renames files, deletes files when operations at tablespace level happens say tablespace deletion. Yes, ASM can co-exist with non-ASM third-party file systems and thus can be integrated into pre-existing environments.
Materialized views are objects that are reduced sets of information that have been summarized, grouped, or aggregated from base tables. They are typically used in data warehouse or decision support systems. When a user process fails, what background process cleans up after it?
PMON What background process refreshes materialized views? The Job Queue Processes. How would you determine what sessions are connected and what resources they are waiting for? Describe what redo logs are. Redo logs are logical and physical structures that are designed to hold all the changes made to a database and are intended to aid in the recovery of a database. How would you force a log switch?
Give two methods you could use to determine what DDL changes have been made. You could use Logminer or Streams What does coalescing a tablespace do? Coalescing is only valid for dictionary-managed tablespaces and de-fragments space by combining neighboring free extents into large single extents. A temporary tablespace is used for temporary objects such as sort structures while permanent tablespaces are used to store those objects meant to be used as the true objects of the database.
Name a tablespace automatically created when you create a database. When creating a user, what permissions must you grant to allow them to connect to the database? How do you add a data file to a tablespace? How do you resize a data file? What view would you use to look at the size of a data file? What view would you use to determine free space in a tablespace?
How would you determine who has added a row to a table? Turn on fine grain auditing for the table. How can you rebuild an index? Explain what partitioning is and what its benefit is. Partitioning is a method of taking large tables and indexes and splitting them into smaller, more manageable pieces.
How can you gather statistics on a table? How can you enable a trace for a session? 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. What is a redo log? The set of redo log files for a database is collectively known as the database redo log. What is the function of redo log? The primary function of the redo log is to record all changes made to data. 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.
What does a control file contains? 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. Data Base Administration.
What is a database instance? 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. What is Parallel Server? Multiple instances accessing the same database only in multi-CPU environments. What is a schema? The set of objects owned by user account is called the schema. 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.
Group of tables physically stored together because they share common columns and are often used together is called cluster. 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. What is the basic element of base configuration of an Oracle database?
It consists of one or more data files. What is a deadlock?
Two processes waiting to update the rows of a table, which are locked by other processes then deadlock arises. In a database environment this will often happen because of not issuing the proper row lock commands.
Poor design of front-end application may cause this situation and the performance of server will reduce drastically. What is SGA? The System Global Area in an Oracle database is the area in memory to facilitate the transfer 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. What is a shared pool? The data dictionary cache is stored in an area in SGA called the shared pool. This will allow sharing of parsed SQL statements among concurrent users. It is area in memory that is used by a single Oracle user process. Data segment are the physical areas within a database block in which the data associated with tables and clusters are stored.
Due to insufficient shared pool size. Monitor the ratio of the reloads takes place while executing SQL statements.
What is Database Buffers? ORA decides the size. What is dictionary cache? Dictionary cache is information about the database objects stored in a data dictionary table. What is meant by recursive hints? Number of times processes repeatedly query the dictionary table is called recursive hints. It is due to the data dictionary cache is too small. 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. How will you swap objects into a different table space for an existing database? This will create all definitions into newfile. How can we organize the tablespaces in Oracle database to have maximum performance? DATA - Standard operational tables. How will you force database to use particular rollback segment? What is meant by free extent?
A free extent is a collection of continuous free blocks in tablespace. When a segment is dropped its extents are reallocated and are marked as free. Which parameter in Storage clause will reduce number of rows per block? What is the significance of having storage clause? How does Space allocation table place within a block? This is used to reserve certain amount of space in a block for expansion of rows. It is used to set the optimal length of a rollback segment.
To manage the database level transactions such as modifications of the data dictionary table that record information about the free space usage. How will you create multiple rollback segments in a database? How the space utilization takes place within rollback segments?
It will try to fit the transaction in a cyclic fashion to all existing extents. Once it found an extent is in use then it forced to acquire a new extent number of extents is based on the optimal size. Why query fails sometimes? Rollback segment dynamically extent to handle larger transactions entry loads. A single transaction may wipeout all available free space in the rollback segment tablespace.
This prevents other user using rollback segments. How will you monitor rollback segment status? List the sequence of events when a large transaction that exceeds beyond its optimal value when an entry wraps and causes the rollback segment to expand into another extend.
Transaction Begins. An entry is made in the RES header for new transactions entry Transaction acquires blocks in an extent of RBS The entry attempts to wrap into second extent. None is available, so that the RBS must extent. RBS chooses its oldest inactive segment. Oldest inactive segment is eliminated. RBS extents The data dictionary tables for space management are updated.
Transaction Completes. 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. How will you estimate the space required by a non-clustered table? Calculate the total header size Calculate the available data space per data block Calculate the combined column lengths of the average row Calculate the total average row size. Calculate the average number rows that can fit in a block Calculate the number of blocks and bytes required for the table.
It is possible to use raw devices as data files and what are the advantages over file system files? Disk corruption will be very less. What is a Control file? Database's overall physical architecture is maintained in a file called control file. It will be used to maintain internal consistency and guide recovery operations. Multiple copies of control files are advisable. How to implement the multiple control files for an existing database?
Shutdown the database Copy one of the existing control file to new location Edit Config ora file by adding new control filename Restart the database. What is redo log file mirroring? How can be achieved? Process of having a copy of redo log files is called mirroring. This can be achieved by creating group of log files together, so that LGWR will automatically writes them to all the members of the current on-line redo log group. If any one group fails then database automatically switch over to next group.
It degrades performance. Shadow set of disks save as a backup in the event of disk failure. In most operating systems if any disk failure occurs it automatically switchover to place of failed disk. What is use of rollback segments in Oracle database? They allow the database to maintain read consistency between multiple transactions. What is a rollback segment entry? It is the set of before image data blocks that contain rows that are modified by a transaction. Each rollback segment entry must be completed within one rollback segment.
A single rollback segment can have multiple rollback segment entries. What is hit ratio? It is a measure of well the data cache buffer is handling requests for data. When will be a segment released? When Segment is dropped. What are disadvantages of having raw devices? List the factors that can affect the accuracy of the estimations? 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. 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. Data Definition Language: Data Manipulation Language: Transactional Control: What is a transaction?
Transaction is logical unit between two commits and commit and rollback. Deleted records can be rolled back or committed. 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.
- SQL INTERVIEW QUESTIONS WITH EXAMPLES PDF
- ORACLE 10G/11G DATA AND DATABASE MANAGEMENT UTILITIES PDF
- FDA EXAM QUESTION PAPERS PDF
- ORACLE APEX TUTORIAL PDF
- PREVIOUS YEAR QUESTION PAPER OF IBPS CLERK EXAM PDF
- APTITUDE QUESTIONS AND ANSWERS WITH EXPLANATION FOR FRESHERS PDF
- CODING INTERVIEW QUESTIONS 1ST EDITION NARASIMHA KARUMANCHI PDF
- AIPMT PREVIOUS YEAR QUESTION PAPERS WITH SOLUTIONS PDF
- ESSENTIAL HAEMATOLOGY 7TH EDITION PDF
- INSTANT RAPPORT MICHAEL BROOKS PDF