Important interview questions and answers asked during the interview,
Hello Readers, here is a compilation of basic important questions based on ORACLE & SQL. Hope this will benefits you for quick revision.
1. What are ORACLE PRECOMPILERS ?
Using ORACLE PRECOMPILERS ,SQL statements and PL/SQL blocks can be contained inside 3GL programs written in C,C++,COBOL,PASCAL, FORTRAN,PL/1 AND ADA.
The Precompilers are known as Pro*C,Pro*Cobol,…
This form of PL/SQL is known as embedded pl/sql,the language in which pl/sql is embedded is known as the host language.
2. Which parameter specified in the DEFAULT STORAGE clause of CREATE TABLESPACE cannot be altered after creating the tablespace?
All the default storage parameters defined for the tablespace can be changed using the ALTER TABLESPACE command. When objects are created their INITIAL and MINEXTENS values cannot be changed.
3. What are the Built-ins used for sending Parameters to forms?
You can pass parameter values to a form when an application executes the call_form, New_form, Open_form or Run_product.
4. Can you have more than one content canvas view attached with a window?
Yes. Each window you create must have atleast one content canvas view assigned to it. You can also create a window that has manipulated content canvas view. At run time only one of the content canvas views assign to a window is displayed at a time.
5. What is default tablespace ?
The Tablespace to contain schema objects created without specifying a tablespace name.
6. What is Tablespace Quota ?
The collective amount of disk space available to the objects in a schema on a particular tablespace.
7. What is Log Switch ?
The point at which ORACLE ends writing to one online redo log file and begins writing to another is called a log switch.
8. What are the different modes of mounting a Database with the Parallel Server ?
Exclusive Mode If the first instance that mounts a database does so in exclusive mode, only that Instance can mount the database.
Parallel Mode If the first instance that mounts a database is started in parallel mode, other instances that are started in parallel mode can also mount the database.
9. What is Execution Plan ?
The combinations of the steps the optimizer chooses to execute a statement is called an execution plan.
10. 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.
11. Define Transaction ?
A Transaction is a logical unit of work that comprises one or more SQL statements executed by a single user.
12. 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 comman (Ver 7.0)
13. What is a Database instance ? Explain
A database instance (Server) is a set of memory structure and background processes that access a set of database files. The process can be shared by all users. The memory structure that are used to store most queried data from database. This helps up to improve database performance by decreasing the amount of I/O performed.
14. What is user Account in Oracle database?
An 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.
15. What are the Built-ins used for sending Parameters to forms?
You can pass parameter values to a form when an application executes the call_form, New_form, Open_form or Run_product.
16. Explain about stacked canvas views?
Stacked canvas view is displayed in a window on top of, or “stacked” on the content canvas view assigned to that same window. Stacked canvas views obscure some part of the underlying content canvas view, and or often shown and hidden programmatically.
17. How do you call other Oracle Products from Oracle Forms?
Run_product is a built-in, Used to invoke one of the supported oracle tools products and specifies the name of the document or module to be run. If the called product is unavailable at the time of the call, Oracle Forms returns a message to the operator.
18. What is Log Switch ?
The point at which ORACLE ends writing to one online redo log file and begins writing to another is called a log switch.
19. 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.
20. Can you have more than one content canvas view attached with a window?
Yes. Each window you create must have atleast one content canvas view assigned to it. You can also create a window that has manipulated content canvas view. At run time only one of the content canvas views assign to a window is displayed at a time.
21. What are the factors that affect OPTIMIZER in choosing an Optimization approach?
The OPTIMIZER_MODE initialization parameter Statistics in the Data Dictionary the OPTIMIZER_GOAL parameter of the ALTER SESSION command hints in the statement.
22. What is Function of Optimizer?
The goal of the optimizer is to choose the most efficient way to execute a SQL statement.
23. What is execution plan?
The combinations of the steps the optimizer chooses to execute a statement is called an execution plan.
24. What does rollback do?
ROLLBACK retracts any of the changes resulting from the SQL statements in the transaction.
25. 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.
26. How could I get distinct entries from a table?
The SELECT statement in conjunction with DISTINCT lets you select a set of distinct values from a table in a database. The values selected from the database table would of course depend on the various conditions that are specified in the SQL query.
Example
SELECT DISTINCT empname FROM emptable
27. How to get the results of a Query sorted in any order?
You can sort the results and return the sorted results to your program by using ORDER BY keyword thus saving you the pain of carrying out the sorting yourself. The ORDER BY keyword is used for sorting.
SELECT empname, age, city FROM emptable ORDER BY empname
28. How can I find the total number of records in a table?
You could use the COUNT keyword , example
SELECT COUNT(*) FROM emp WHERE age>40
29. What is GROUP BY?
The GROUP BY keywords have been added to SQL because aggregate functions (like SUM) return the aggregate of all column values every time they are called. Without the GROUP BY functionality, finding the sum for each individual group of column values was not possible.
30. What is the difference among “dropping a table”, “truncating a table” and “deleting all records” from a table.
Dropping : (Table structure + Data are deleted), Invalidates the dependent objects ,Drops the indexes.
Truncating: (Data alone deleted), Performs an automatic commit, Faster than delete
Delete : (Data alone deleted), Doesn?t perform automatic commit
31. What are the Large object types supported by Oracle?
Blob and Clob
32. What are cursors?
Cursors allow row-by-row prcessing of the resultsets.
Types of cursors: Static, Dynamic, Forward-only, Keyset-driven. See books online for more information.
Disadvantages of cursors: Each time you fetch a row from the cursor, it results in a network roundtrip, where as a normal SELECT query makes only one rowundtrip, however large the resultset is. Cursors are also costly.
33. What are triggers? How to invoke a trigger on demand?
Triggers are special kind of stored procedures that get executed automatically when an INSERT, UPDATE or DELETE operation takes place on a table.
Triggers can’t be invoked on demand. They get triggered only when an associated action (INSERT, UPDATE, DELETE) happens on the table on which they are defined.
Triggers are generally used to implement business rules.
34. What is a join and explain different types of joins.
Joins are used in queries to explain how different tables are related. Joins also let you select data from a table depending upon data from another table.
Types of joins: INNER JOINs, OUTER JOINs, CROSS JOINs. OUTER JOINs are further classified as LEFT OUTER JOINS, RIGHT OUTER JOINS and FULL OUTER JOINS.
35. What is a self join?
Self join is just like any other join, except that two instances of the same table will be joined in the query.
36. How would you go about increasing the buffer cache hit ratio?
Use the buffer cache advisory over a given workload and then query the v$db_cache_advice table. If a change was necessary then I would use the alter system set db_cache_size command.
37. Explain an ORA-01555.
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.
38. Explain the difference between $ORACLE_HOME and $ORACLE_BASE.
ORACLE_BASE is the root directory for oracle. ORACLE_HOME located beneath ORACLE_BASE is where the oracle products reside.
39. What are the components of physical database structure of Oracle database ?
Oracle database is comprised of three types of files. One or more data files, two are more redo log files, and one or more control files.
40. 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.
41. 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.
42. What is schema?
A schema is collection of database objects of a user.
43. 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.
44. What is private database link ?
Private database link is created on behalf of a specific user. A private database link can be used only when the owner of the link specifies a global object name in a SQL statement or in the definition of the owner’s views or procedures.
45. What is public database link ?
Public database link is created for the special user group PUBLIC. A public database link can be used when any user in the associated database specifies a global object name in a SQL statement or object definition.
46. What is network database link ?
Network database link is created and managed by a network domain service. A network database link can be used when any user of any database in the network specifies a global object name in a SQL statement or object definition.
47. What is data block ?
Oracle database’s data is stored in data blocks. One data block corresponds to a specific number of bytes of physical database space on disk.
48. 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 data blocks. Block size is specified in init.ora file and cannot be changed latter.
49. What is row chaining ?
In circumstances, all of the data for a row in a table may not be able to fit in the same data block. When this occurs, the data for the row is stored in a chain of data block (one or more) reserved for that segment.
50. What is a data segment ?
Each non-clustered table has a data segment. All of the table’s data is stored in the extents of its data segment. Each cluster has a data segment. The data of every table in the cluster is stored in the cluster’s data segment.
Hope you like this article
5/5
