Oracle Interview Questions And Answers 1. What are the components of physical database structure of Oracle database? Oracle database is comprised of three types of files. One or more datafiles, two are more redo log files, and one or more control files. 2. What are the components of logical database structure of Oracle database? There are tablespaces and database's schema objects. 3. What is a tablespace? A database is divided into Logical Storage Unit called tablespaces. A tablespace is used to grouped related logical structures together. 4. What is SYSTEM tablespace and when is it created? Every Oracle database contains a tablespace named SYSTEM, which is automatically created when the database is created. The SYSTEM tablespace always contains the data dictionary tables for the entire database. 5. Explain the relationship among database, tablespace and data file. Each databases logically divided into one or more tablespaces one or more data files are explicitly created for each tablespace. 6. What is schema? A schema is collection of database objects of a user. 7. What are Schema Objects? Schema objects are the logical structures that directly refer to the database's data. Schema objects include tables, views, sequences, synonyms, indexes, clusters, database triggers, procedures, functions packages and database links. 8. Can objects of the same schema reside in different tablespaces? Yes. 9. Can a tablespace hold objects from different schemes? Yes. 10. What is Oracle table? A table is the basic unit of data storage in an Oracle database. The tables of a database hold all of the user accessible data. Table data is stored in rows and columns. 11. What is an Oracle view? A view is a virtual table. Every view has a query attached to it. (The query is a SELECT statement that identifies the columns and rows of the table(s) the view uses.) 12. What is Partial Backup ? A Partial Backup is any operating system backup short of a full backup, taken while the database is open or shut down. 13. What is Mirrored on-line Redo Log ? A mirrored on-line redo log consists of copies of on-line redo log files physically located on separate disks, changes made to one member of the group are made to all members. 14. What is Full Backup ? A full backup is an operating system backup of all data files, on-line redo log files and control file that constitute ORACLE database and the parameter. 15. Can a View based on another View ? Yes. 16. Can a Tablespace hold objects from different Schemes ? Yes. 17. Can objects of the same Schema reside in different tablespaces.? Yes. 18. 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. 19. Do View contain Data ? Views do not contain or store data. 20. What are the Referential actions supported by FOREIGN KEY integrity constraint ? UPDATE and DELETE Restrict - A referential integrity rule that disallows the update or deletion of referenced data. DELETE Cascade - When a referenced row is deleted all associated dependent rows are deleted. 21. What are the type of Synonyms? There are two types of Synonyms Private and Public. 22. What is a Redo Log ? The set of Redo Log files YSDATE,UID,USER or USERENV SQL functions, or the pseudo columns LEVEL or ROWNUM. 23. What is an Index Segment ? Each Index has an Index segment that stores all of its data. 24. Explain the relationship among Database, Tablespace and Data file.? Each databases logically divided into one or more tablespaces one or more data files are explicitly created for each tablespace 25. What are the different type of Segments ? Data Segment, Index Segment, Rollback Segment and Temporary Segment. 26. What are Clusters ? Clusters are groups of one or more tables physically stores together to share common columns and are often used together. 27. What is an Integrity Constrains ? An integrity constraint is a declarative way to define a business rule for a column of a table. 28. What is an Index ? An Index is an optional structure associated with a table to have direct access to rows, which can be created to increase the performance of data retrieval. Index can be created on one or more columns of a table. 29. What is an Extent ? An Extent is a specific number of contiguous data blocks, obtained in a single allocation, and used to store a specific type of information. 30. What is a View ? A view is a virtual table. Every view has a Query attached to it. (The Query is a SELECT statement that identifies the columns and rows of the table(s) the view uses.) 31. What is Table ? A table is the basic unit of data storage in an ORACLE database. The tables of a database hold all of the user accessible data. Table data is stored in rows and columns. 32. Can a view based on another view? Yes. 33. What are the advantages of views? Provide an additional level of table securrity, by restricting access to a predetermined set of rows and columns of a table. Hide data complexity. Simplify commands for the user. Present the data in a different perspectivve from that of the base table. Store complex queries. 34. What is an Oracle sequence? A sequence generates a serial list of unique numbers for numerical columns of a database's tables. 35. What is a synonym? A synonym is an alias for a table, view, sequence or program unit. 36. What are the types of synonyms? There are two types of synonyms private and public. 37. What is a private synonym? Only its owner can access a private synonym. 38. What is a public synonym? Any database user can access a public synonym. 39. What are synonyms used for? Mask the real name and owner of an objectt. Provide public access to an object Provide location transparency for tables, views or program units of a remote database. Simplify the SQL statements for database uusers. 40. What is an Oracle index? An index is an optional structure associated with a table to have direct access to rows, which can be created to increase the performance of data retrieval. Index can be created on one or more columns of a table. 41. How are the index updates? Indexes are automatically maintained and used by Oracle. Changes to table data are automatically incorporated into all relevant indexes.
42. What is a Tablespace? A database is divided into Logical Storage Unit called tablespaces. A tablespace is used to grouped related logical structures together
43. What is Rollback Segment ? A Database contains one or more Rollback Segments to temporarily store "undo" information.
44. What are the Characteristics of Data Files ?
A data file can be associated with only one database. Once created a data file can't change size. One or more data files form a logical unit of database storage called a tablespace. 45. How to define Data Block size ?
A data block size is specified for each ORACLE database when the database is created. A database users and allocated free database space in ORACLE datablocks. Block size is specified in INIT.ORA file and can’t be changed latter. 46. What does a Control file Contain ? A Control file records the physical structure of the database. It contains the following information. Database Name Names and locations of a database's files and redolog files. Time stamp of database creation. 47.What is difference between UNIQUE constraint and PRIMARY KEY constraint ? A column defined as UNIQUE can contain Nulls while a column defined as PRIMARY KEY can't contain Nulls. 48.What is Index Cluster ? A Cluster with an index on the Cluster Key 49.When does a Transaction end ? When it is committed or Rollbacked. 50. 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 in the Data Dictionary the OPTIMIZER_GOAL parameter of the ALTER SESSION command hints in the statement.
51. What is the effect of setting the value "CHOOSE" for OPTIMIZER_GOAL, parameter of the ALTER SESSION Command ? The Optimizer chooses Cost_based approach and optimizes with the goal of best throughput if statistics for atleast one of the tables accessed by the SQL statement exist in the data dictionary. Otherwise the OPTIMIZER chooses RULE_based approach. 52. What is the function of Optimizer ? The goal of the optimizer is to choose the most efficient way to execute a SQL statement. 53. What is Execution Plan ? The combinations of the steps the optimizer chooses to execute a statement is called an execution plan. 54. What are the different approaches used by Optimizer in choosing an execution plan ? Rule-based and Cost-based. 55. What does ROLLBACK do ? ROLLBACK retracts any of the changes resulting from the SQL statements in the transaction. 56. What is SAVE POINT ? For long transactions that contain many SQL statements, intermediate markers or savepoints can be declared which can be used to divide a transaction into smaller parts. This allows the option of later rolling back all work performed from the current point in the transaction to a declared savepoint within the transaction. 57. What are the values that can be specified for OPTIMIZER MODE Parameter ? COST and RULE. 58. What is COST-based approach to optimization ? Considering available access paths and determining the most efficient execution plan based on statistics in the data dictionary for the tables accessed by the statement and their associated clusters and indexes. 59. What does COMMIT do ? COMMIT makes permanent the changes resulting from all SQL statements in the transaction. The changes made by the SQL statements of a transaction become visible to other user sessions transactions that start only after transaction is committed.
60. What is RULE-based approach to optimization ?
Choosing an executing planbased on the access paths available and the ranks of these access paths.
61. What are the values that can be specified for OPTIMIZER_GOAL parameter of the ALTER SESSION Command ?
CHOOSE,ALL_ROWS,FIRST_ROWS and RULE. 62. Define Transaction ?
A Transaction is a logical unit of work that comprises one or more SQL statements executed by a single user. 63. What is Read-Only Transaction ?
A Read-Only transaction ensures that the results of each query executed in the transaction are consistant with respect to the same point in time.
64. What is a deadlock ? Explain .
Two processes wating to update the rows of a table which are locked by the other process then deadlock arises. In a database environment this will often happen because of not issuing proper row lock commands. Poor design of front-end application may cause this situation and the performance of server will reduce drastically.
These locks will be released automatically when a commit/rollback operation performed or any one of this processes being killed externally. 65. What is a Schema ?
The set of objects owned by user account is called the schema. 66. 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. 67. What is Parallel Server ? Multiple instances accessing the same database (Only In Multi-CPU environments) There are two impelling reasons everyone should learn about Parameterized Queries. One entails one keyboard character and is more of a hassle reliever than anything else. That character, in code, can become either of two different objects, the single quote and the apostrophe. When you're coding, either one can make your life truely miserable at times. The second, and MOST compelling reason to learn Parameterized Queries is to protect your database from SQL Injection Injection Attacks. If you have never heard of them, you need to hear about them now. These attacks can reak havoc on your server and, more importantly, your data. Check out these articles on SQL Injection Attacks: http://msdn.microsoft.com/msdnmag/issues/04/09/SQLInjection/ http://www.tech-faq.com/sql-injection-attack.shtml http://www.codeproject.com/aspnet/SqlInjection.asp Simply by using Parameterized Queries, this becomes a first line of defense, and SQL Injection attacks are stopped in their tracks. Anyone who has ever put together a long, involved SQL statement with variables, juggling single quotes, along with the double quotes (Tutorial on Single and Double Quotes), will tell you that it's not much fun. And - on top of that, when we then talk about the apostrophe, it gets even more complicated. Of course, as most of you know, in an Insert statement, if the last Name is O'Hara, the engine looks at the string, and only sees the apostrophe as a single quote, thereby truncating your perfectly structured SQL statement. Naturally, there are fixes for the latter (Replace statement to double up on the apostrophes, which tells the DataBase engine to interpret the two apostrophes as only one true apostrophe and not a single quote), but that's extra care you must take that's really not needed - but only if you use Parameterized Queries. Let's put together a scenario - we're searching Employees of the Northwind Trading Company by last name. We have a DropDownList (DDL) with all the last names. From that, the end user will choose one, so that the rest of that employee's information will appear. This, naturally, is a fairly simple scenario to start. We don't want to get too complicated too quickly. Since we're searching the database by the last name, The SQL statement, using single quotes would be something like this: sql = "Select * from Employees where Lastname = '" & ddl.selecteditem.text & "'" As you can see, this could get pretty complicated, adding AND clauses and/or OR clauses on the end of the WHERE clause. Using a Parameterized Query, it would look like this: sql = "Select * from Employees where Lastname =@LastName" All we'd need for this to work is to add the following to your code, after instantiating your Command: (assuming you have defined a Command called 'cmd') cmd.Parameters.Add(New SQLParameter("@LastName", ddl.SelectedItem.text)) Of course, this is an example for SQL Server. For OleDb (MS Access, and others), you would use (you guessed it!): cmd.Parameters.Add(New OleDbParameter("@LastName", ddl.SelectedItem.text)) Of course, if you wanted to do a LIKE query (Select * from tablename where fieldname LIKE, etc), you would substitute the '=' sign for 'Like'. THEN, you could add your wildcards ('%' for SQL Server and '*" for MS Access) into the actual data being used for the search. For instance, if you had a text box for the end user to enter data to do a LIKE search - just have them put the wildcards in where they want (at the end, start, or both). Where this really comes in handy is when you create an Insert or Update SQL statement. Suppose you were inserting a FirstName, LastName, Address, City, State, Zip, Phone, and Email address. Here's the older way you would have done this with single quotes: sql="Insert into Employees (Firstname, Lastname, City, State, Zip, Phone, Email) Values ('" & frmFirstname.text & "', '" & frmLastName & "', '" & frmCity & "', '" & frmState & "', '" & frmZip & "', '" & frmPhone & "', '" & frmEmail & "')" As you can see, this can get pretty cumbersome, doing it by hand. Here's the way you would take care of it with a Parameterized Query: Dim MySQL as string = "Insert into NewEmp (fname, LName, Address, City, State, Postalcode, Phone, Email) Values (@Firstname, @LastName, @Address, @City, @State, @Postalcode, @Phone, @Email)" Then, of course, you'd need to specify the parameters:
With SQL Server:
With cmd.Parameters:
.Add(New SQLParameter("@Firstname", frmFname.text))
.Add(New SQLParameter("@LastName", frmLname.text))
.Add(New SQLParameter("@Address", frmAddress.text))
.Add(New SQLParameter("@City", frmCity.text))
.Add(New SQLParameter("@state", frmState.text))
.Add(New SQLParameter("@Postalcode", frmPostalCode.Text))
.Add(New SQLParameter("@Phone", frmPhone.text))
.Add(New SQLParameter("@email", frmemail.text))
end with
With OleDb:
With cmd.Parameters:
.Add(New OleDbParameter("@Firstname", frmFname.text))
.Add(New OleDbParameter("@LastName", frmLname.text))
.Add(New OleDbParameter("@Address", frmAddress.text))
.Add(New OleDbParameter("@City", frmCity.text))
.Add(New OleDbParameter("@state", frmState.text))
.Add(New OleDbParameter("@Postalcode", frmPostalCode.Text))
.Add(New OleDbParameter("@Phone", frmPhone.text))
.Add(New OleDbParameter("@email", frmemail.text))
end with Here's one note I received from a reader, Randy, that I'd like to mention here: "I found out the hard way that the delineating of parameters must be in the SAME ORDER as they appear in the SQL command." As far as using Stored Procedures, check out this code sample on using a Stored Procedure to populate a DropDownList - naturally, using parameters. We'll discuss wldcards and using the 'Like' operator in another Tutorial called "Parameterized Queries - Part II". I'm sure at this point, it's easy to also see, that consistent naming, from the Form Field names, to the Parameter Names is by far the best way to go. It's much easier to see at a glance, exactly what's going on in the code. I've seen many examples on the net and in books that go into length concerning filetypes, sizes, etc when defining parameters. And while that's a more exact way of doing it, this tutorial shows that there is not just one way to do things in the ASP.Net world. Of course it's always best to make sure the data going in is in the exact form the database needs, in all cases. Therefore, whichever way you decide to do it, make sure your validation is the best it can be. go Oracle JSP JAVA Editor 90. What are the dictionary tables used to monitor a database spaces ?

DBA_FREE_SPACE
DBA_SEGMENTS
DBA_DATA_FILES.
91. What are the roles and user accounts created automatically with the database?
DBA - role Contains all database system privileges.
SYS user account - The DBA role will be assigned to this account. All of the base tables and views for the database's dictionary are store in this schema and are manipulated only by ORACLE. SYSTEM user account - It has all the system privileges for the database and additional tables and views that display administrative information and internal tables and views used by oracle tools are created using this username.
92. What are the minimum parameters should exist in the parameter file (init.ora) ?
DB NAME - Must set to a text string of no more than 8 characters and it will be stored inside the datafiles, redo log files and control files and control file while database creation.
DB_DOMAIN - It is string that specifies the network domain where the database is created. The global database name is identified by setting these parameters
(DB_NAME & DB_DOMAIN) CONTORL FILES - List of control filenames of the database. If name is not mentioned then default name will be used.
DB_BLOCK_BUFFERS - To determine the no of buffers in the buffer cache in SGA.
PROCESSES - To determine number of operating system processes that can be connected to ORACLE concurrently. The value should be 5 (background process) and additional 1 for each user.
ROLLBACK_SEGMENTS - List of rollback segments an ORACLE instance acquires at database startup. Also optionally LICENSE_MAX_SESSIONS,LICENSE_SESSION_WARNING and LICENSE_MAX_USERS.