Interview Questions.

Question 1. What Are The Steps To Take To Improve Performance Of A Poor Performing Query?

Answer :

 Maximum use of indexes, stored procures need to be done.
 Avoid excessive use of complex joins and cursors.
 Avoid the use of conditional operators using columns of various tables.
 Make use of computed columns and rewriting the query.
Question 2. What Is A Deadlock And What Is A Live Lock?

Answer :

Deadlock occur in interconnection n/w when institution of manner are not able to behave due to waiting each different to launch a few resource.

Stay lock packets hold to move via n/w, however does now not advance towards vacation spot.

SQL Server 2000 Interview Questions
Question 3. What Is Blocking And How Would You Troubleshoot It?

Answer :

Blocking happens whilst  or more rows are locked by means of one SQL connection and a 2nd connection to the SQL server requires a conflicting on lock on those rows. This results in the 2d connection to wait until the first lock is launched.

Troubleshooting blocking:

 SQL scripts can be written that continuously monitor the state of locking and blocking off on SQL Server
 The common blockading eventualities need to be identified and resolved.
 The scripts output have to be checked continuously.
 The SQL profilers information have to be examined regularly to stumble on blockading.
Question four. Explain The Different Types Of Backups Available In Sql Server.

Answer :

Complete database backup: This type of backup will backup all the facts in the database. Used maximum normally for catastrophe recuperation and takes the longest time to backup.

Differential databse backup: The database is split into partitions that have been changed considering last complete backup. Most suitable for massive databases. The most recent differential backup consists of the modifications from previous backups.

Transaction log backups: Backups handiest the modifications logged within the transaction log. The transaction log has all adjustments logged approximately a database. Once the changes are accommodated at the database, the log is truncated or backed up.

File/File Group backups: used to get better person documents or document businesses. Each filegroup may be personally sponsored up. This facilitates in recovery best the specified file or filegroup for disaster restoration.

T-SQL Tutorial
Question 5. What Is Database Isolation In Sql Server?

Answer :

Isolation in database defines how and whilst modifications made by using one transaction may be visible to other transactions. Different isolation tiers are:

 Serializable
 Repeatable read
 Read devoted
 Read uncommitted
PL/SQL Interview Questions
Question 6. What Is A Schema In Sql Server 2005? Explain How To Create A New Schema In A Database?

Answer :

A schema is used to create database gadgets. It may be created using CREATE SCHEMA announcement. The items created can be moved among schemas. Multiple database users can percentage a unmarried default schema.

CREATE SCHEMA sample;

Table introduction

Create desk sample.Sampleinfo

identity int primary key,
name varchar(20)

Question 7. Explain How To Create A Scrollable Cursor With The Scroll Option.

Answer :

Using the SCROLL keyword at the same time as asserting a cursor allows fetching of rows in any sequence.

Example:
DECLARE employee_curs SCROLL CURSOR FOR SELECT * FROM worker;

The energetic set of the cursor is stored can be accessed in any order without the need of starting and final the cursor. The Scroll cursors may be set for select and characteristic cursors however not insert or update statements.

SQL Server 2008 Tutorial T-SQL Interview Questions
Question 8. Explain How To Create A Dynamic Cursor With The Dynamic Option?

Answer :

When a cursor is said as DYNAMIC, the cursor reflects all changes made to the base tables as the cursor is scrolled round DYNAMIC alternative does not help ABSOLUTE FETCH.

Question nine. What Are Database Files And Filegroups?

Answer :

Database files are used for mapping the database over a few operating machine files. Data and log facts are separate. SQL server database has 3 varieties of database documents:

Primary: place to begin of a database. It additionally points to different documents in database.
Extension: .Mdf

Secondary: All statistics files besides primary information report is part of secondary documents.
Extension: .Ndf

Log files: All log records used to get better database.
Extension: .Ldf

SQL Server 2008 Interview Questions
Question 10. Describe In Brief Databases And Sql Server Databases Architecture.

Answer :

SQL Server consists of a set of various additives which fulfill records storage and data analysis needs for organization packages. Database structure: All the records is stored in databases that is prepared into logical additives visible to the stop customers. It’s handiest the administrator who needs to honestly deal with the bodily storage component of the databases, while customers only deal with database tables.

Every SQL Server example has commonly 4 machine database i.E. Grasp, model, tempdb and msdb. All other databases are person created databases as consistent with their wishes and requirements.

A unmarried SQL Server instance is capable of coping with thousands of customers operating on multiple databases.

SQL Database Tutorial
Question 11. What Are The Steps To Improve The Performance Of A Query?

Answer :

? Number of joins and use of complex perspectives/cursors must be decreased.
? The use of the stored approaches and indexes should be maximized.
? The optimized use of the complex conditional assessments and laptop columns need to be in place.
? Tracking of overall performance analysis for the question enables us in identifying the right elements to optimize.

SQL Server 2005 Interview Questions
Question 12. How Would You Use The Sp_ Functions To Identify The Blocking Problems?

Answer :

Blocking is the impasse scenario while  SQL connections race to gain the manage over the identical set of rows in conflicting phrases. This may be tracked with the aid of the repute of WAIT present in the SP_LOCK manner’s output. All the energetic LOCKS and the exclusive rows which might be being concerned are proven on this output. The identity of the connections involved in the particular row rivalry lock may be recognized with sp_who and sp_who2 strategies. This way the causal dealers of the blocking off is identifies. KILL command issued towards the unique SQL connection inflicting the BLOCK can solve the issue. But the everlasting answer lies inside the proper design of the utility code to execute in concurrence throughout exclusive connections.

SQL Server 2000 Interview Questions
Question thirteen. What Are The Different Types Of Backups?

Answer :

The SQL server gives 4 sorts of backups to suit the want of the administrator.

? Complete backup- The whole returned up is just zipping the content of the entire database in terms of the unique tables and approaches and many others. This again up can server as an unbiased entity that can be restored in unique structures with just the base SQL server installed.
? Transaction log backup: This is the mechanism of backing up the transaction logs which have been maintained within the server. This way the info of the database getting updated is received. This can not be a stand-on my own returned up mechanism. But can shop loads of time if we have already got the document device related to the DB backed up on the brand new deployment server.
? Differential backup: This is a subset of the whole backup, in which handiest the modified datasets are subsidized up. This can store the time while we are simply looking to maintain a backup server to principal server.
? File backup: This is the quickest manner to take the backup of whole database. Instead of taking within the statistics truly stored in DB, the documents are sponsored up and the document machine accordingly obtained whilst mixed with the transaction logs of the authentic device will render the database that we are looking to lower back up.

MySQL Tutorial
Question 14. What Are The Different Levels Of Isolation?

Answer :

The isolation represents the way of keeping apart the database from the results of network accesses, thereby preserving the consistency. The different degrees of isolation are:

? study committed: This level of isolation uses the shared locks and the reads to the database give the constant and consistent values.
? study uncommitted: No locks carried out. This is the least powerful isolation stage.
? repeatable read: There are locks over the rows and values but the updates are maintained as a separate phantom row which is the next set of values for the precise file. Values can exchange within a specific transaction of a SQL characteristic.
? SERIALIZABLE reads: This is the implementation of natural lock mechanism in which one precise transaction isn't allowed get right of entry to to unique record before every other one completes.

Question 15. How Can You Start The Sql Server In The Single User Mode And The Minimal Configuration Mode?

Answer :

The SQLServer.Exe is the executable which can be referred to as in the command prompt with the parameters -m and -f. These are the alternatives as a way to start the SQL server inside the user mode and minimum configuration mode respectively.

MySQL Interview Questions
Question sixteen. How Can You Know That Statistics Should Be Updated?

Answer :

Statistics constitute the individuality for the indexes which can be being used for deciding on the statistics. This could make the query execution quite green. The tables that we're managing if truncated and repopulated, there is a superb danger that the indexes and statistics are out of sync and that is while we must update the data. There also are different conditions like while the desk has been modified and lot of rows have been added these days or like whilst a server has been updated with one of a kind model of software. These also deliver us the purpose to use the UPDATE_STATISTICS, DBCC SHOW_STATISTICS and so forth to update it as a consequence.

DB2 Using SQL Tutorial
Question 17. What Is Replication In Sql Server?

Answer :

Replication refers to the shifting or copying of the database elements from one device to another. This can be achieved in the SQL Server in one of the following techniques:
? Transactional.
? Snapshot.
? Merge replication.

DB2 Using SQL Interview Questions
Question 18. Can We Initiate A External Com Object From Within Sql?

Answer :

Yes we can use the saved process sp_OACreate to initiate the external COM object from the T-SQL.

PL/SQL Interview Questions
Question 19. What Is A Schema? How Is It Useful In Sql Servers?

Answer :

The Schema refers to the overall structure of the database with all associated information like customers, get entry to privileges, interplay information among the different tables and so on. The CREATE SCHEMA is the command that may be used to create the schema in the SQL Server. This when performed may be used to re installation the same database in every other gadget for demonstrative or test purposes. This holds intact the underlying framework over which the database has been built.

Software checking out Tutorial
Question 20. What Is A Write-beforehand Log?

Answer :

The write-ahead log is the logging gadget that just updates the buffer cache of the database for the transactions and updates the logs and simplest then the actual modifications are integrated within the real database. This is the cause why it is called “write beforehand”. This allows in maintaining the consistency within the database. This also can be beneficial in getting the actual database values even in case of disasters.

PHP+MySQL Interview Questions
Question 21. What Is The Use Of Check Points In The Transaction Logs?

Answer :

The take a look at factors are recovery points that suggest the specific country of the database. When there may be some failure inside the database that is happening before the following check point, the database may be reverted returned to the previous test point and thus the database might nonetheless be consistent.

Question 22. What Is A Column With Identity?

Answer :

The column with a described identity in turn manner that there's an precise price that the system assigns to the specific column. This is similar to the AUTONumber belongings of the Access backend.

Question 23. What Are Scrollable Cursors? How Are They Created?

Answer :

The scrollable cursors are the ones that could get the whole set of rows as unmarried entity, inside which all the rows gift can be accessed in any order with out the open/near of cursor carried out for each row access. The scrollable cursors are created with the key-word SCROLL brought to the CREATE Cursor statements. The scrollable cursors are useful for the get right of entry to of records from distinctive rows however not for the delete/insert of recent rows.

PostgreSQL Interview Questions
Question 24. What Is Raid? How Does It Help Storage Of Databases?

Answer :

The RAID stands for Redundant Array of Independent Disks. With its very own RAID controllers, the RAID implements a fail-safe storage mechanism with its very own backup mechanisms. There are exceptional configurations of the RAID that all supply us the ACID homes of garage together with different such facilities. This form of garage will make the SQL Server database to be failsafe and stable. This can every so often suggest that the backup mechanisms and other such reliability measures can be taken off from the SQL Server stage of operations.

T-SQL Interview Questions
Question 25. How Can You Identify The Version Number Of The Sql Server Installed?

Answer :

The international variable version has the construct and model data for the SQL Server and the service packs.

Question 26. What Is The Use Of Cascade Constraints?

Answer :

cascading is used for maintaining referencial integrity policies, which says that foreign key attributes values have to be both subset of number one key values or null.

SQL DBA Interview Questions
Question 27. What Is The Function Of A Odbc Manager ?

Answer :

The ODBC Manager manages all the information sources that exists within the system.

SQL Server 2008 Interview Questions
Question 28. What Are The Different Types Of Indexes Available In Sql Server?

Answer :

“Clustered and Non-Clustered Indexes”. There are different styles of Indexes consisting of Unique, XML, Spatial and Filtered Indexes. 

Question 29. What Is The Difference Between Clustered And Non-clustered Index?

Answer :

 In a clustered index, the leaf degree pages are the actual statistics pages of the desk. When a clustered index is created on a table, the records pages are organized therefore primarily based at the clustered index key. There can most effective be one Clustered index on a table.
 In a Non-Clustered index, the leaf stage pages does no longer include records pages instread it contains recommendations to the records pages. There can a couple of non-clustered indexes on a single desk.
SQL Interview Questions
Question 30. What Are The New Features In Sql Server 2005 When Compared To Sql Server 2000?

Answer :

There are quite loads of changes and upgrades in SQL Server 2005. Few of them are listed right here :

 Database Partitioning
 Dynamic Management Views
 System Catalog Views
 Resource Database
 Database Snapshots
 SQL Server Integration Services
 Support for Analysis Services on a a Failover Cluster.
 Profiler being capable of hint the MDX queries of the Analysis Server.
 Peer-toPeer Replication
 Database Mirroring
Question 31. What Are The High-availability Solutions In Sql Server?

Answer :

Failover Clustering, Database Mirroring, Log Shipping and Replication are the High- Availability functions available in SQL Server.

Question 32. What Is Denormalization And When Would You Go For It?

Answer :

As the call shows, denormalization is the reverse system of normalization. It's the controlled advent of redundancy in to the database design. It allows improve the query overall performance because the quantity of joins will be reduced.

Software trying out Interview Questions
Question 33. How Do You Implement One-to-one, One-to-many And Many-to-many Relationships While Designing Tables?

Answer :

One-to-One dating may be carried out as a single table and infrequently as  tables with number one and overseas key relationships.

One-to-Many relationships are applied with the aid of splitting the facts into two tables with number one key and overseas key relationships.

Many-to-Many relationships are applied the usage of a junction desk with the keys from each the tables forming the composite number one key of the junction desk.

SQL Server 2005 Interview Questions
Question 34. What's The Difference Between A Primary Key And A Unique Key?

Answer :

Both primary key and particular implement forte of the column on which they are defined. But by means of default number one key creates a clustered index at the column, wherein are precise creates a nonclustered index by using default. Another main distinction is that, primary key doesn't allow NULLs, however specific key allows one NULL handiest.

Question 35. What Are User Defined Datatypes And When You Should Go For Them?

Answer :

User defined datatypes assist you to extend the base SQL Server datatypes through providing a descriptive call, and format to the database. Take as an instance, for your database, there's a column referred to as Flight_Num which seems in many tables. In a majority of these tables it need to be varchar(8). In this situation you may create a user described datatype called Flight_num_type of varchar(eight) and use it across all your tables.

Question 36. What Is Bit Datatype And What's The Information That Can Be Stored Inside A Bit Column?

Answer :

Bit datatype is used to shop boolean facts like 1 or 0 (authentic or false). Untill SQL Server 6.Five bit datatype could hold both a 1 or zero and there has been no guide for NULL. But from SQL Server 7.0 onwards, bit datatype can constitute a third nation, which is NULL.

MySQL Interview Questions
Question 37. Define Candidate Key, Alternate Key, Composite Key.

Answer :

A candidate key's one that could pick out each row of a table uniquely. Generally a candidate key turns into the number one key of the table. If the table has multiple candidate key, certainly one of them turns into the number one key, and the rest are called exchange keys.
A key shaped by means of combining at the least two or greater columns is referred to as composite key.

Question 38. What Are Defaults? Is There A Column To Which A Default Can't Be Bound?

Answer :

A default is a fee so that it will be utilized by a column, if no price is furnished to that column while placing records. IDENTITY columns and timestamp columns can't have defaults certain to them.

Question 39. What Is A Transaction And What Are Acid Properties?

Answer :

A transaction is a logical unit of work in which, all of the steps ought to be achieved or none. ACID stands for Atomicity, Consistency, Isolation, Durability. These are the homes of a transaction.

Question forty. Explain Different Isolation Levels?

Answer :

An isolation degree determines the diploma of isolation of statistics between concurrent transactions. The default SQL Server isolation stage is Read Committed. Here are the opposite isolation tiers (within the ascending order of isolation): Read Uncommitted, Read Committed, Repeatable Read, Serializable. See SQL Server books on line for a proof of the isolation ranges. Be sure to read about SET TRANSACTION ISOLATION LEVEL, which helps you to customize the isolation degree at the connection stage.
CREATE INDEX myIndex ON myTable(myColumn)

DB2 Using SQL Interview Questions
Question forty one. What Type Of Index Will Get Created After Executing The Above Statement?

Answer :

Non-clustered index. Important element to word: By default a clustered index gets created at the number one key, unless distinctive in any other case.

Question 42. What's The Maximum Size Of A Row?

Answer :

8060 bytes.

PHP+MySQL Interview Questions
Question forty three. Differences Between Active/energetic And Active/passive Cluster Configurations?

Answer :

Active/Active :

It is the bassically use for the default nodes, Here first node might be default and 2d node might be named instance. Both node can be energetic.
Its Move organization from cluster management is possible for each aspect.
System overall performance will go down, if each sources are in a single node.
Active/Passive :

Its additionally essentially use for nodes But in this case Only one Active node with default example. No device performance degradation could be there for this case even supposing we switchover to the alternative node. Both have same configuration.
Question 44. What Is Lock Escalation?

Answer :

Lock escalation is the system of changing plenty of low stage locks (like row locks, web page locks) into better degree locks (like table locks). Every lock is a reminiscence shape too many locks could imply, extra memory being occupied via locks. To prevent this from occurring, SQL Server escalates the many great-grain locks to fewer coarse-grain locks. Lock escalation threshold become definable in SQL Server 6.Five, however from SQL Server 7.0 onwards it is dynamically controlled through SQL Server.

Question forty five. What's The Difference Between Delete Table And Truncate Table Commands?

Answer :

DELETE TABLE is a logged operation, so the deletion of each row receives logged in the transaction log, which makes it slow. TRUNCATE TABLE additionally deletes all the rows in a table, however it might not log the deletion of every row, alternatively it logs the deallocation of the statistics pages of the desk, which makes it quicker. Of course, TRUNCATE TABLE may be rolled back.

Question forty six. What Are Constraints? Explain Different Types Of Constraints.

Answer :

Constraints enable the RDBMS put into effect the integrity of the database robotically, with no need you to create triggers, rule or defaults.
Types of constraints: NOT NULL, CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY

Question 47. Whar Is An Index? What Are The Types Of Indexes? How Many Clustered Indexes Can Be Created On A Table? I Create A Separate Index On Each Column Of A Table. What Are The Advantages And Disadvantages Of This Approach?

Answer :

Indexes in SQL Server are just like the indexes in books. They assist SQL Server retrieve the information faster.

Indexes are of two kinds. Clustered indexes and non-clustered indexes. When you craete a clustered index on a table, all of the rows inside the table are saved in the order of the clustered index key. So, there may be handiest one clustered index consistent with desk. Non-clustered indexes have their very own storage break free the table information garage. Non-clustered indexes are saved as B-tree systems (so do clustered indexes), with the leaf stage nodes having the index key and it is row locater. The row placed will be the RID or the Clustered index key, relying up on the absence or presence of clustered index on the table.

If you create an index on each column of a desk, it improves the query performance, because the query optimizer can select from all the present indexes to provide you with an efficient execution plan. At the same t ime, data change operations (which includes INSERT, UPDATE, DELETE) will become sluggish, as whenever facts changes inside the desk, all of the indexes want to be updated. Another downside is that, indexes need disk space, the extra indexes you've got, extra disk area is used.

Question forty eight. How To Restart Sql Server In Single User Mode? How To Start Sql Server In Minimal Configuration Mode?

Answer :

SQL Server may be commenced from command line, the use of the SQLSERVR.EXE. This EXE has a few very crucial parameters with which a DBA should be acquainted with. -m is used for beginning SQL Server in single person mode and -f is used to begin the SQL Server in minimal confuguration mode.

Question forty nine. What Are Statistics, Under What Circumstances They Go Out Of Date, How Do You Update Them?

Answer :

Statistics determine the selectivity of the indexes. If an listed column has particular values then the selectivity of that index is greater, in place of an index with non-specific values. Query optimizer makes use of these indexes in figuring out whether to pick out an index or no longer while executing a query.

Some conditions beneath that you need to replace statistics:

If there may be tremendous change within the key values inside the index
If a big quantity of statistics in an listed column has been introduced, modified, or eliminated (that is, if the distribution of key values has modified), or the table has been truncated the use of the TRUNCATE TABLE declaration after which repopulated
Database is upgraded from a previous version
Look up SQL Server books online for the following commands: UPDATE STATISTICS, STATS_DATE, DBCC SHOW_STATISTICS, CREATE STATISTICS, DROP STATISTICS, sp_autostats, sp_createstats, sp_updatestats.

Question 50. What Is Database Replicaion? What Are The Different Types Of Replication You Can Set Up In Sql Server?

Answer :

Replication is the system of copying/moving records between databases at the equal or exceptional servers. SQL Server helps the following sorts of replication eventualities:

 Snapshot replication
 Transactional replication (with on the spot updating subscribers, with queued updating subscribers)
 Merge replication
Question 51. What Are The Components Of Physical Database Structure Of Oracle Database?

Answer :

Oracle database is constituted of 3 types of documents. One or greater datafiles,  are extra redo log files, and one or extra manage documents.

Question fifty two. What Are The Components Of Logical Database Structure Of Oracle Database?

Answer :

There are tablespaces and database's schema gadgets.

Question fifty three. What Is A Tablespace?

Answer :

A database is divided into Logical Storage Unit known as tablespaces. A tablespace is used to group associated logical structures collectively.

Question fifty four. What Is System Tablespace And When Is It Created?

Answer :

Every Oracle database carries a tablespace named SYSTEM, which is mechanically created while the database is created. The SYSTEM tablespace always contains the statistics dictionary tables for the whole database.

Question 55. Explain The Relationship Among Database, Tablespace And Data File.

Answer :

Each databases logically divided into one or more tablespaces one or more records documents are explicitly created for every tablespace.

Question 56. What Is Schema?

Answer :

A schema is collection of database gadgets of a person.

Question fifty seven. What Are Schema Objects?

Answer :

Schema gadgets are the logical systems that immediately talk to the database's facts. Schema objects encompass tables, perspectives, sequences, synonyms, indexes, clusters, database triggers, strategies, features packages and database hyperlinks.

Question 58. Can Objects Of The Same Schema Reside In Different Tablespaces?

Answer :

Yes.

Question 59. Can A Tablespace Hold Objects From Different Schemes?

Answer :

Yes.

Question 60. What Is Oracle Table?

Answer :

A table is the simple unit of records storage in an Oracle database. The tables of a database hold all the consumer reachable facts. Table records is saved in rows and columns.

Question sixty one. What Is An Oracle View?

Answer :

A view is a digital desk. Every view has a query attached to it. (The question is a SELECT assertion that identifies the columns and rows of the table(s) the view makes use of.)

Question sixty two. What Is Partial Backup ?

Answer :

A Partial Backup is any running machine backup short of a full backup, taken even as the database is open or shut down.

Question 63. What Is Mirrored On-line Redo Log ?

Answer :

A reflected on line redo log consists of copies of online redo log documents bodily located on separate disks, changes made to one member of the organization are made to all participants.

Question sixty four. What Is Full Backup ?

Answer :

A full backup is an running machine backup of all data documents, on-line redo log documents and control file that represent ORACLE database and the parameter.

Question sixty five. Can A View Based On Another View ?

Answer :

Yes.

Question 66. Can A Tablespace Hold Objects From Different Schemes ?

Answer :

Yes.

Question 67. Can Objects Of The Same Schema Reside In Different Tablespaces.?

Answer :

Yes.

Question 68. What Is The Use Of Control File ?

Answer :

When an instance of an ORACLE database is started, its manage report is used to perceive the database and redo log documents that have to be opened for database operation to continue. It is also utilized in database recovery.

Question 69. Do View Contain Data ?

Answer :

Views do not comprise or store facts.

Question 70. What Are The Referential Actions Supported By Foreign Key Integrity Constraint ?

Answer :

UPDATE and DELETE Restrict - A referential integrity rule that disallows the update or deletion of referenced information.
DELETE Cascade - When a referenced row is deleted all associated based rows are deleted.

Question seventy one. What Are The Type Of Synonyms?

Answer :

There are two sorts of Synonyms Private and Public.

Question 72. What Is A Redo Log ?

Answer :

The set of Redo Log documents YSDATE,UID,USER or USERENV SQL features, or the pseudo columns LEVEL or ROWNUM.

Question 73. What Is An Index Segment ?

Answer :

Each Index has an Index section that stores all of its records.

Question 74. Explain The Relationship Among Database, Tablespace And Data File?

Answer :

Each databases logically divided into one or greater tablespaces one or extra statistics files are explicitly created for each tablespace.

Question seventy five. What Are The Different Type Of Segments ?

Answer :

Data Segment, Index Segment, Rollback Segment and Temporary Segment.

Question 76. What Are Clusters ?

Answer :

Clusters are groups of one or greater tables bodily stores collectively to percentage not unusual columns and are regularly used collectively.

Question seventy seven. What Is An Integrity Constrains ?

Answer :

An integrity constraint is a declarative manner to outline a business rule for a column of a desk.

Question 78. What Is An Index ?

Answer :

An Index is an non-obligatory shape associated with a desk to have direct access to rows, which may be created to boom the overall performance of data retrieval. Index may be created on one or greater columns of a table.

Question 79. What Is An Extent ?

Answer :

An Extent is a particular quantity of contiguous data blocks, acquired in a unmarried allocation, and used to save a particular sort of records.

Question eighty. What Is A View ?

Answer :

A view is a virtual desk. Every view has a Query connected to it. (The Query is a SELECT declaration that identifies the columns and rows of the table(s) the view makes use of.)

Question 81. What Is Table ?

Answer :

A desk is the fundamental unit of statistics garage in an ORACLE database. The tables of a database keep all of the user handy statistics. Table statistics is stored in rows and columns.

Question eighty two. Can A View Based On Another View?

Answer :

Yes.

Question 83. What Are The Advantages Of Views?

Answer :

Provide a further stage of table security, by using proscribing get admission to to a predetermined set of rows and columns of a desk.
Hide statistics complexity.
Simplify instructions for the person.
Present the information in a distinctive perspective from that of the base desk.
Store complex queries.
Question eighty four. What Is An Oracle Sequence?

Answer :

A series generates a serial listing of precise numbers for numerical columns of a database's tables.

Question eighty five. What Is A Synonym?

Answer :

A synonym is an alias for a desk, view, collection or program unit.

Question 86. What Are The Types Of Synonyms?

Answer :

There are  types of synonyms non-public and public.

Question 87. What Is A Private Synonym?

Answer :

Only its owner can get admission to a non-public synonym.

Question 88. What Is A Public Synonym?

Answer :

Any database consumer can access a public synonym.

Question 89. What Are Synonyms Used For?

Answer :

Mask the actual name and proprietor of an item.
Provide public access to an item
Provide location transparency for tables, perspectives or program units of a remote database.
Simplify the SQL statements for database customers.
Question 90. What Is An Oracle Index?

Answer :

An index is an optionally available shape associated with a desk to have direct access to rows, which can be created to boom the overall performance of statistics retrieval. Index may be created on one or more columns of a desk.

Question 91. How Are The Index Updates?

Answer :

Indexes are robotically maintained and used by Oracle. Changes to desk statistics are robotically included into all relevant indexes.

Question ninety two. What Is Rollback Segment ?

Answer :

Database contains one or greater Rollback Segments to quickly shop "undo" data.

Question 93. What Are The Characteristics Of Data Files ?

Answer :

A information report can be associated with most effective one database. Once created a information file can not alternate size. One or greater facts files shape a logical unit of database garage called a tablespace.

Question ninety four. How To Define Data Block Size ?

Answer :

A information block length is precise for every ORACLE database when the database is created. A database users and allocated unfastened database area in ORACLE datablocks. Block length is specified in INIT.ORA file and might’t be modified latter.

Question 95. What Does A Control File Contain ?

Answer :

A Control file records the physical shape of the database. It carries the following statistics.

Database Name
Names and places of a database's files and redolog files.
Time stamp of database advent.
Question ninety six. What Is Difference Between Unique Constraint And Primary Key Constraint ?

Answer :

A column defined as UNIQUE can include Nulls while a column described as PRIMARY KEY can't contain Nulls.

Question 97. What Is Index Cluster ?

Answer :

A Cluster with an index at the Cluster Key.

Question ninety eight. When Does A Transaction End ?

Answer :

When it's far dedicated or Rollbacked.

Question 99. What Is The Effect Of Setting The Value "select" For Optimizer_goal, Parameter Of The Alter Session Command ?

Answer :

The Optimizer chooses Cost_based method and optimizes with the aim of quality throughput if records for atleast one of the tables accessed through the SQL declaration exist in the statistics dictionary. Otherwise the OPTIMIZER chooses RULE_based technique.

Question 100. What Is The Effect Of Setting The Value "all_rows" For Optimizer_goal Parameter Of The Alter Session Command ? What Are The Factors That Affect Optimizer In Choosing An Optimization Approach ?

Answer :

The OPTIMIZER_MODE initialization parameter Statistics inside the Data Dictionary. The OPTIMIZER_GOAL parameter of the ALTER SESSION command pointers within the announcement.

Question 101. How Does One Create A New Database?

Answer :

One can create and regulate Oracle databases the use of the Oracle "dbca" (Database Configuration Assistant) application. The dbca software is located inside the $ORACLE_HOME/bin listing. The Oracle Universal Installer (oui) normally starts it after installing the database server software. One also can create databases manually using scripts. This option, however, is falling out of fashion, as it's miles pretty involved and error susceptible. Look at this example for creating and Oracle 9i database:

CONNECT SYS AS SYSDBA
ALTER SYSTEM SET DB_CREATE_FILE_DEST='/u01/oradata/';
ALTER SYSTEM SET DB_CREATE_ONLINE_LOG_DEST_1='/u02/oradata/';
ALTER SYSTEM SET DB_CREATE_ONLINE_LOG_DEST_2='/u03/oradata/';
CREATE DATABASE;
Question 102. What Database Block Size Should I Use?

Answer :

Oracle recommends that your database block size fit, or be multiples of your working gadget block size. One can use smaller block sizes, however the performance cost is widespread. Your preference need to rely on the sort of application you are strolling. If you have got many small transactions as with OLTP, use a smaller block size. With fewer but larger transactions, as with a DSS application, use a bigger block length. If you are the use of a volume supervisor, remember your "operating gadget block size" to be 8K. This is due to the fact quantity manager merchandise use 8K blocks (and this is not configurable).

Question 103. What Are The Different Approaches Used By Optimizer In Choosing An Execution Plan ?

Answer :

Rule-primarily based and Cost-based totally.

Question 104. What Does Rollback Do ?

Answer :

ROLLBACK retracts any of the modifications as a result of the SQL statements within the transaction.

Question one hundred and five. What Is Cost-based totally Approach To Optimization ?

Answer :

Considering available get right of entry to paths and determining the maximum efficient execution plan based totally on statistics inside the records dictionary for the tables accessed by using the assertion and their related clusters and indexes.

Question 106. What Does Commit Do ?

Answer :

COMMIT makes permanent changes as a consequence of all SQL statements in the transaction. The changes made by means of the SQL statements of a transaction emerge as visible to different user sessions transactions that begin best after transaction is dedicated.

Question 107. Define Transaction ?

Answer :

A Transaction is a logical unit of labor that contains one or extra SQL statements completed by using a unmarried user.

Question 108. What Is Read-most effective Transaction ?

Answer :

A Read-Only transaction guarantees that the consequences of every question performed in the transaction are consistent with admire to the same factor in time.

Question 109. What Is A Deadlock ? Explain .

Answer :

Two methods wating to update the rows of a desk that are locked by way of the opposite technique then impasse arises. In a database surroundings this could frequently show up because of no longer issuing right row lock commands. Poor design of the front-cease software can also cause this situation and the overall performance of server will reduce significantly.
These locks may be launched mechanically while a dedicate/rollback operation performed or any one of this tactics being killed externally.

Question a hundred and ten. What Is A Schema ?

Answer :

The set of objects owned by means of consumer account is called the schema.

Question 111. What Is A Cluster Key ?

Answer :

The associated columns of the tables are referred to as the cluster key. The cluster key's indexed using a cluster index and its value is stored most effective once for more than one tables within the cluster.

Question 112. What Is Parallel Server ?

Answer :

Multiple times gaining access to the equal database (Only In Multi-CPU environments).

Question 113. What Is Cluster?

Answer :

Group of tables bodily stored collectively due to the fact they share common columns and are often used collectively is known as Cluster.

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

Answer :

An index is a database structure used by the server to have direct get right of entry to of a row in a table. An index is robotically created when a completely unique of primary key constraint clause is laid out in create table comman (Ver 7.0)

Question 115. What Is A Database Instance ? Explain

Answer :

A database instance (Server) is a hard and fast of memory structure and heritage processes that get admission to a fixed of database documents.The manner can be shared by using all customers. The reminiscence shape that are used to keep maximum queried records from database. This helps up to enhance database performance by decreasing the amount of I/O executed towards statistics document.

Question 116. What Is The Use Of Analyze Command ?

Answer :

To perform one of these characteristic on an index,table, or cluster:
- To accumulate records about object utilized by the optimizer and keep them in the records dictionary.
- To delete facts about the item utilized by object from the facts dictionary.
- To validate the structure of the object.
- To discover migrated and chained rows of the table or cluster.

Question 117. What Is Default Tablespace ?

Answer :

The Tablespace to comprise schema items created with out specifying a tablespace call.

Question 118. What Are The System Resources That Can Be Controlled Through Profile ?

Answer :

The wide variety of concurrent periods the consumer can establish the CPU processing time to be had to the consumer's consultation.
The CPU processing time to be had to a single name to ORACLE made via a SQL announcement.
The quantity of logical I/O available to the consumer's consultation.
The amout of logical I/O to be had to a single call to ORACLE made by using a SQL announcement.
The allowed quantity of idle time for the user's consultation.
The allowed amount of connect time for the consumer's consultation.
Question 119. What Is Tablespace Quota ?

Answer :

The collective quantity of disk area available to the gadgets in a schema on a selected tablespace.

Question 120. What Are The Different Levels Of Auditing ?

Answer :

Statement Auditing, Privilege Auditing and Object Auditing.

Question 121. What Is Statement Auditing ?

Answer :

Statement auditing is the auditing of the effective gadget privileges with out regard to specially named objects.

Question 122. What Are The Database Administrators Utilities Avaliable ?

Answer :

SQL * DBA - This lets in DBA to screen and manage an ORACLE database.
SQL * Loader - It loads data from fashionable running system documents (Flat documents) into ORACLE database tables. Export (EXP) and Import (imp) utilities can help you move existing records in ORACLE layout to and from ORACLE database.

Question 123. How Can You Enable Automatic Archiving ?

Answer :

Shut the database
Backup the database
Modify/Include LOG_ARCHIVE_START_TRUE in init.Ora record.
Start up the database.
Question 124. What Are Roles? How Can We Implement Roles ?

Answer :

Roles are the very best way to provide and control common privileges wanted by means of specific corporations of database users. Creating roles and assigning provides to roles. Assign every position to group of users. This will simplify the job of assigning privileges to character customers.

Question 125. What Are Roles ?

Answer :

Roles are named organizations of associated privileges which are granted to customers or different roles.

Question 126. What Are The Uses Of Roles ?

Answer :

REDUCED GRANTING OF PRIVILEGES - Rather than explicitly granting the identical set of privileges to many customers a database administrator can grant the privileges for a set of associated consumer's granted to a function and then supply simplest the function to every member of the institution.
DYNAMIC PRIVILEGE MANAGEMENT - When the privileges of a set ought to trade, simplest the privileges of the position need to be modified. The security domains of all customers granted the organization's role routinely replicate the adjustments made to the role.
SELECTIVE AVAILABILITY OF PRIVILEGES - The roles granted to a person may be selectively allow (to be had for use) or disabled (no longer to be had for use). This lets in specific manipulate of a consumer's privileges in any given situation.
APPLICATION AWARENESS - A database utility can be designed to routinely permit and disable selective roles when a user attempts to apply the application.
Question 127. What Is Privilege Auditing ?

Answer :

Privilege auditing is the auditing of the usage of powerful gadget privileges with out regard to especially named items.

Question 128. What Is Object Auditing ?

Answer :

Object auditing is the auditing of accesses to precise schema items without regard to person.

Question 129. What Is Auditing ?

Answer :

Monitoring of person access to useful resource within the research of database use.

Question 130. Where Are My Tempfiles, I Don't See Them In V$datafile Or Dba_data_file?

Answer :

Tempfiles, in contrast to everyday datafiles, aren't listed in v$datafile or dba_data_files. Instead question v$tempfile or dba_temp_files:

SELECT * FROM v$tempfile;
SELECT * FROM dba_temp_files;
Question 131. How Do I Find Used/free Space In A Temporary Tablespace?

Answer :

Unlike ordinary tablespaces, authentic transient tablespace records is not indexed in DBA_FREE_SPACE. Instead use the V$TEMP_SPACE_HEADER view:

SELECT tablespace_name, SUM (bytes used), SUM (bytes loose)
FROM V$temp_space_header
GROUP BY tablespace_name;
Question 132. What Is A Profile ?

Answer :

Each database person is assigned a Profile that specifies limitations on diverse gadget assets to be had to the person.

Question 133. How Will You Enforce Security Using Stored Procedures?

Answer :

Don't grant consumer get right of entry to at once to tables within the utility. Instead provide the potential to get admission to the methods that access the tables. When technique accomplished it will execute the privilege of strategies owner. Users cannot get entry to tables besides thru the technique.

Question 134. How Does One Get The View Definition Of Fixed Views/tables?

Answer :

Query v$fixed_view_definition.
Example: SELECT * FROM v$fixed_view_definition WHERE view_name='V$SESSION';

Question 135. What Are The Dictionary Tables Used To Monitor A Database Spaces ?

Answer :

DBA_FREE_SPACE
DBA_SEGMENTS
DBA_DATA_FILES.
Question 136. What Is User Account In Oracle Database?

Answer :

An consumer account isn't a physical structure in Database however it's far having critical courting to the objects inside the database and will be having certain privileges.

Question 137. What Is Dynamic Data Replication?

Answer :

Updating or Inserting statistics in remote database thru database triggers. It can also fail if far off database is having any problem.

Question 138. What Is Two-phase Commit ?

Answer :

Two-phase commit is mechanism that guarantees a distributed transaction either commits on all concerned nodes or rolls returned on all worried nodes to keep information consistency across the worldwide dispensed database. It has  section, a Prepare Phase and a Commit Phase.

Question 139. How Can You Enforce Referential Integrity In Snapshots ?

Answer :

Time the references to occur when grasp tables are not in use. Peform the reference manually at once locking the master tables. We can be part of tables in snapshots through creating a complicated snapshots so as to be primarily based on the master tables.

Question 140. What Is A Sql * Net?

Answer :

SQL *NET is ORACLE's mechanism for interfacing with the verbal exchange protocols utilized by the networks that facilitate dispensed processing and disbursed databases. It is utilized in Clint-Server and Server-Server communications.

Question 141. What Is A Snapshot ?

Answer :

Snapshots are examine-simplest copies of a grasp table positioned on a faraway node which is periodically refreshed to reflect modifications made to the grasp table.

Question 142. What Is The Mechanism Provided By Oracle For Table Replication ?

Answer :

Snapshots and SNAPSHOT LOGs.

Question 143. What Is Snapshot?

Answer :

Snapshot is an item used to dynamically replicate statistics among distribute database at distinctive time periods. In version 7.Zero they may be study only.

Question 144. What Are The Various Type Of Snapshots?

Answer :

Simple and Complex.

Question one hundred forty fiv