Oracle Interview Questions
31.Can null keys be entered in cluster index, normal index ?
Yes.
32.Can Check constraint be used for self referential integrity ? How ?
Yes. In the CHECK condition for a column of a table, we can reference some other column of the same table and thus enforce self referential integrity.
33.What are the min. extents allocated to a rollback extent ?
Two
34.What are the states of a rollback segment ? What is the difference between partly available and needs recovery ?
The various states of a rollback segment are :ONLINE, OFFLINE, PARTLY AVAILABLE, NEEDS RECOVERY and INVALID.
35.What is the difference between unique key and primary key ?
Unique key can be null; Primary key cannot be null.
36.An insert statement followed by a create table statement followed by rollback ? Will the rows be inserted ?
Yes.
37.Can you define multiple savepoints ?
Yes.
38.Can you Rollback to any savepoint ?
Yes.
40.What is the maximum no. of columns a table can have ?
256.
21.What are mutating triggers ?
A trigger giving a SELECT on the table on which the trigger is written.
22.What are constraining triggers ?
A trigger giving an Insert / Update on a table having referential integrity constraint on the triggering table.
23.Describe Oracle database's physical and logical structure ?
Physical : Data files, Redo Log files, Control file.
Logical : Tables, Views, Tablespaces, etc.
24.Can you increase the size of a tablespace ? How ?
Yes, by adding datafiles to it.
25.Can you increase the size of datafiles ? How ?
No (for Oracle 7.0)Yes (for Oracle 7.3 by using the Resize clause ----- Confirm !!).
26.What is the use of Control files ?
Contains pointers to locations of various data files, redo log files, etc.
27.What is the use of Data Dictionary ?
Used by Oracle to store information about various physical and logical Oracle structures e.g. Tables, Tablespaces, datafiles, etc
28.What are the advantages of clusters ?
Access time reduced for joins.
29.What are the disadvantages of clusters ?
The time for Insert increases.
30.Can Long/Long RAW be clustered ?
No.
11.What are the constructs of a procedure, function or a package ?
The constructs of a procedure, function or a package are : variables and constants cursors exceptions
12.Why Create or Replace and not Drop and recreate procedures ?
So that Grants are not dropped.
13.Can you pass parameters in packages ? How ?
Yes. You can pass parameters to procedures or functions in a package.
14.What are the parts of a database trigger ?
The parts of a trigger are:
A triggering event or statement
A trigger restriction
A trigger action
15.What are the various types of database triggers ?
There are 12 types of triggers, they are combination of :
Insert, Delete and Update Triggers.
Before and After Triggers.
Row and Statement Triggers.(3*2*2=12)
16.What is the advantage of a stored procedure over a database trigger ?
We have control over the firing of a stored procedure but we have no control over the firing of a trigger.
17.What is the maximum no. of statements that can be specified in a trigger statement ?
One.
18.Can views be specified in a trigger statement ?
No
19.What are the values of :new and :old in Insert/Delete/Update Triggers ?
INSERT : new = new value, old = NULL
DELETE : new = NULL, old = old value
UPDATE : new = new value, old = old value
20.What are cascading triggers? What is the maximum no of cascading triggers at a time?
When a statement in a trigger body causes another trigger to be fired, the triggers are said to be cascading. Max = 32.
1.What are the various types of Exceptions ?
User defined and Predefined Exceptions.
2.Can we define exceptions twice in same block ?
No.
3.What is the difference between a procedure and a function ?
Functions return a single variable by value whereas procedures do not return any variable by value. Rather they return multiple variables by passing variables by reference through their OUT parameter.
4.Can you have two functions with the same name in a PL/SQL block ?
Yes.
5.Can you have two stored functions with the same name ?
Yes.
6.Can you call a stored function in the constraint of a table ?
No.
7.What are the various types of parameter modes in a procedure ?
IN, OUT AND INOUT.
8.What is Over Loading and what are its restrictions ?
OverLoading means an object performing different functions depending upon the no. of parameters or the data type of the parameters passed to it.
9.Can functions be overloaded ?
Yes.
10.Can 2 functions have same name & input parameters but differ only by return datatype?
No.
What is precedence in Oracle?
It is the order in which Oracle evaluates different conditions in the same expression
What is a predicate in SQL?
Syntax that specifies a subset of rows to be returned. Predicates are specified in the WHERE clause of a SQL statement.
What is a primary key?
The column or set of columns included in the definition of a table's PRIMARY KEY constraint. A primary key's values uniquely identify the rows in a table. Only one primary key can be defined for each table.
What is a Primary Key Constraint?
This constraint is used to identify the primary key for a table. This operation requires that the primary columns are unique, and Oracle will create a unique index on the target primary key.
What is Resource Definition Framework (RDF)?
A set of rules (a sort of language) for creating descriptions of information, especially information available on the World Wide Web. RDF could be used to describe a collection of books, or artists, or a collection of web pages as in the RSS data format which uses RDF to create machine-readable summaries of web sites.
RDF is also used in XPFE applications to define the relationships between different collections of elements, for example RDF could be used to define the relationship between the data in a database and the way that data is displayed to a user.
What is a redo log?
A set of files that protect altered database data in memory that has not been written to the datafiles. The redo log can consist of two parts: the online redo log and the archived redo log.
What is a REF CURSOR? Compare strong and week ref cursor types.
A REF CURSOR is a data type. A variable created based on such a data type is generally called a cursor variable. A cursor variable can be associated with different queries at run-time. The primary advantage of using cursor variables is their capability to pass result sets between sub programs (like stored procedures, functions, packages etc.).
A strong ref cursor type definition specifies a return type, a weak definition does not.
Since Oracle 9i you can use SYS_REFCURSOR as the type for the returning REF_CURSOR.
What is referential datatype?
A variable can have either a simple or "scalar" datatype, such as NUMBER or VARCHAR2. Alternately, a variable can have a referential datatype that uses reference to a table column to derive its datatype
What is a schema?
Collection of logical structures of data, or schema objects
What is Semantic Web?
The Semantic Web is an extension of the current Web that will allow you to find, share, and combine information more easily. It relies on machine-readable information and metadata expressed in RDF.
What is a Shared pool?
Portion of the system global area that contains shared memory constructs such as shared SQL areas. A shared SQL area is required to process every unique SQL statement submitted to a database.
flush shared pool – clear all SQL statements that are in the Shared Pool Area
What is SQL trace?
The SQL Trace facility provides performance information on individual SQL statements. It generates the following statistics for each statement:
-Parse, execute, and fetch counts
-CPU and elapsed times
-Physical reads and logical reads
-Number of rows processed
-Misses on the library cache
-Username under which each parse occurred
-Each commit and rollback
You can enable the SQL Trace facility for a session or for an instance. When the SQL Trace facility is enabled, performance statistics for all SQL statements executed in a user session or in the instance are placed into trace files.
What is SQLCODE?
The function SQLCODE returns the number code of the most recent exception
What is statistics?
Statistics calculate the data distribution and storage characteristics of tables, columns, indexes, and partitions. The cost-based optimization approach uses these statistics to calculate the selectivity of predicates and to estimate the cost of each execution plan. Selectivity is the fraction of rows in a table that the SQL statement's predicate chooses. The optimizer uses the selectivity of a predicate to estimate the cost of a particular access method and to determine the optimal join order and join method.
The statistics are stored in the data dictionary and can be exported from one database and imported into another
What is a table function?
Table functions are designed to return a set of rows through PL/SQL logic but are intended to be used as a normal table or view in a SQL statement.
Example: select * from table(a_function(a_parm));
What is tkprof?
TKPROF program formats the contents of the trace file and place the output into a readable output file.
What difference is between TRANSLATE and REPLACE functions?
TRANSLATE('char','search_str','replace_str')
Replace every occurrence of search_str with replace_str
Unlike REPLACE() if replace_str is NULL the function returns NULL
What is an UNIQUE key constraint?
A data integrity constraint requiring that every value in a column or set of columns (key) be unique--that is, no two rows of a table have duplicate values in a specified column or set of columns.
What is a view?
A logical representation of another table or combination of tables
What is a hash function?
A hash function returns a value for an input, and the output is generally shorter or more compact than the input
What is an index?
Optional structure associated with tables and clusters. You can create indexes on one or more columns of a table to speed access to data on that table.
Oracle's two major index types are Bitmap indexes and B-Tree indexes. B-Tree indexes are the regular type, and bitmap indexes are a highly compressed index type that tends to be used primarily for data warehouses.
The B-tree index is the most-used type of index that Oracle provides. It provides fast lookup of rows containing a desired key value. It is not suitable if the column(s) being indexed are of low cardinality (number of distinct values). For those situations, a bitmap index is very useful, but be aware that bitmap indexes are very expensive to update when DML is performed on the indexed table.
What is an Oracle instance?
A system global area (SGA) and the Oracle background processes constitute an Oracle database instance. Every time a database is started, a system global area is allocated and Oracle background processes are started. The SGA is deallocated when the instance shuts down.
After starting an instance, Oracle associates the instance with the specified database. This is called mounting the database. The database is then ready to be opened, which makes it accessible to authorized users.
What is integrity constraint?
Declarative method of defining a rule for a column of a table. Integrity constraints enforce the business rules associated with a database and prevent the entry of invalid information into tables.
What is JDBC?
Java Database Connectivity is an API (Applications Programming Interface) that allows Java to send SQL statements to an object-relational database such as Oracle.
What are Oracle logical structures
Logical structures of an Oracle database include tablespaces, schema objects, data blocks, extents, and segments. Because the physical and logical structures are separate, the physical storage of data can be managed without affecting the access to logical storage structures
What is a materialized view
A materialized view provides indirect access to table data by storing the results of a query in a separate schema object.
What is MERGE statement in Oracle?
The MERGE statement (AKA "UPSERT") released in Oracle 9i is possibly one of the most useful ETL-enabling technologies built into the Oracle kernel. It enables us to either UPDATE or INSERT a row into a target table in one statement. You tell Oracle your rules for determining whether a target row should UPDATEd or INSERTed from the source
What is a mutating table?
A table that is currently being modified by an UPDATE, DELETE, or INSERT statement, or a table that might be updated by the effects of a DELETE CASCADE constraint.
The error is encountered when a row-level trigger accesses the same table on which it is based, while executing.
What is a nested table?
Nested table collections are an extension of the index-by tables. The main difference between the two is that nested tables can be stored in a database column
Describe the normalization.
The process of organizing data to minimize redundancy. Normalization usually involves dividing a database into tables and defining relationships between the tables. The objective is to isolate data so that additions, deletions, and modifications of a field can be made in just one table and then propagated through the rest of the database via the defined relationships.
-First Normal Form (1NF): Each field in a table contains different information. No Repeating groups.
-Second Normal Form (2NF):
Primary key can not be subdivided into separate logical entities.
Every non-key attribute is fully dependent on the key.
-Third Normal Form (3NF)
No functional dependencies on non-key fields.
What is an outer join?
An outer join does not require each record in the two joined tables to have a matching record in the other table
What is Web Ontology Language?
An OWL ontology may include descriptions of classes, along with their related properties and instances. OWL is designed for use by applications that need to process the content of information instead of just presenting information to humans. It facilitates greater machine interpretability of Web content than that supported by XML, RDF, and RDF Schema
What is Oracle package?
A schema object that groups logically related PL/SQL types, items, and subprograms. Packages offer several advantages: modularity, easier application design, information hiding, added functionality, and better performance.
What are parsing and its phases? What is soft parse?
The first two phases of the parse are Syntax Check and Semantic Analysis happen for each and every SQL statement within the database. Then Oracle database needs to check in the Shared Pool to determine if the current SQL statement being parsed has already been processed by any other sessions.
If yes, the parse operation can skip the next two functions in the process: Optimization and Row Source Generation (it is soft parse)
What is partitioning?
Partitioning is a method of splitting large tables and indexes into smaller, more manageable pieces.
What are Oracle physical structures?
Physical database structures of an Oracle database include datafiles, redo log files, and control files.
Describe general concepts of PL/SQL.
PL/SQL is Oracle's procedural extension to SQL. With PL/SQL, you can manipulate data with SQL statements, and control program flow with procedural constructs such as IF-THEN and LOOP. You can also declare constants and variables, define procedures and functions, use collections and object types, and trap run-time errors.
Applications written using any of the Oracle programmatic interfaces can call PL/SQL stored procedures and send blocks of PL/SQL code to the server for execution.
Because it runs inside the database, PL/SQL code is very efficient for data-intensive operations, and minimizes network traffic in client/server applications.
Describe PLS_INTEGER datatype.
PLS_INTEGER and BINARY_INTEGER are identical datatypes and are only available in PL/SQL. You cannot create a column in a table with either of these data types. PLS_INTEGER is a highly efficient integer 32-bit data type. You will most commonly see PLS_INETGER (and BINARY_INETGER) in PL/SQL routines as an index variable. An associative array (INDEX BY TABLE) index. Both PLS_INTEGER and BINARY_INTEGER allow whole numbers only. Decimal fractions are rounded to the nearest whole number.
What is a pragma?
Pragma is a keyword in Oracle PL/SQL that is used to provide an instruction to the compiler like PRAGMA AUTONOMOUS_TRANSACTION
Describe the package DBMS_STATS.
With the DBMS_STATS package you can view and modify optimizer statistics gathered for database objects.
The statistics to be viewed or modified can reside in the dictionary or in a table created in the user's schema for this purpose. You can also collect and manage user-defined statistics for tables and domain indexes using this package. For example, if the DELETE_COLUMN_STATS procedure is invoked on a column for which an association is defined, user-defined statistics for that column are deleted in addition to deletion of the standard statistics.
Only statistics stored in the dictionary have an impact on the cost-based optimizer. You can also use DBMS_STATS to gather statistics in parallel
Describe the package DBMS_TRACE.
The DBMS_TRACE package contains the interface to trace PL/SQL functions, procedures, and exceptions.
DBMS_TRACE provides subprograms to start and stop PL/SQL tracing in a session. Oracle collects the trace data as the program executes and writes it to database tables.
What is the DCL language?
DCL is Data Control Language statements (GRANT, COMMIT)
What is the DDL language?
DDL (Data Definition Language) statements are used to define the database structure or schema (CREATE, ALTER, TRUNCATE)
What is a dedicated server?
A database server configuration in which a server process handles requests for a single user process.
What are dispatcher processes in Oracle?
Optional background processes, present only when a shared server configuration is used. At least one dispatcher process is created for every communication protocol in use (D000, . . ., Dnnn). Each dispatcher process is responsible for routing requests from connected user processes to available shared server processes and returning the responses back to the appropriate user processes.
What is distributed transaction?
A transaction that updates data on two or more networked computer systems
distributed transaction
What is the DML language?
DML (Data Manipulation Language) statements are used for managing data within schema objects. (INSERT, MERGE, EXPLAIN PLAN)
Describe the embedded SQL and Pro*C environment.
SQL statements embedded within a program and prepared before the program is executed.
It is a method of combining the computing power of a high-level language like C/C++ and the database manipulation capabilities of SQL. It allows you to execute any SQL statement from an application program. Oracle's embedded SQL environment is called Pro*C.
A Pro*C program is compiled in two steps. First, the Pro*C precompiler recognizes the SQL statements embedded in the program, and replaces them with appropriate calls to the functions in the SQL runtime library. The output is pure C/C++ code with all the pure C/C++ portions intact. Then, a regular C/C++ compiler is used to compile the code and produces the executable.
All SQL statements need to start with EXEC SQL and end with a semicolon
What is ERP System?
Enterprise Resource Planning, a system that is used to manage all aspects of a company's operations.
ERP is a way to integrate the data and processes of an organization into one single system
Describe ETL process.
Extract, Transform, and Load (ETL) is a process in data warehousing that involves extracting data from outside sources, transforming it to fit business needs (which can include quality levels), and loading into the end target, i.e. the data warehouse.
ETL can in fact refer to a process that loads any database
ETL can also be used for the integration with legacy systems
What is EXPLANE PLAN? What information can you get using this statement?
The EXPLAIN PLAN statement displays execution plans chosen by the Oracle optimizer for SELECT, UPDATE, INSERT, and DELETE statements. A statement's execution plan is the sequence of operations Oracle performs to run the statement.
The row source tree is the core of the execution plan. It shows the following information:
-An ordering of the tables referenced by the statement
-An access method for each table mentioned in the statement
-A join method for tables affected by join operations in the statement
-Data operations like filter, sort, or aggregation
In addition to the row source tree, the plan table contains information about the following:
-Optimization, such as the cost and cardinality of each operation
-Partitioning, such as the set of accessed partitions
-Parallel execution, such as the distribution method of join inputs
The EXPLAIN PLAN results let you determine whether the optimizer selects a particular execution plan, such as, nested loops join. It also helps you to understand the optimizer decisions, such as why the optimizer chose a nested loops join instead of a hash join, and lets you understand the performance of a query.
You can use the V$SQL_PLAN to display the execution plan of a SQL statement
Describe an external tables.
External tables feature is a complement to existing SQL*Loader functionality. It allows you to access data in external sources as if it were in a table in the database.
As of Oracle Database 10g, external tables can also be written to.
To unload data, you use the ORACLE_DATAPUMP access driver. The data stream that is unloaded is in a proprietary format and contains all the column data for every row being unloaded.
An unload operation also creates a metadata stream that describes the contents of the data stream. The information in the metadata stream is required for loading the data stream. Therefore, the metadata stream is written to the datafile and placed before the data stream.
What is a function-based indexes?
Traditionally, performing a function on an indexed column in the where clause of a query guaranteed an index would not be used. Oracle 8i introduced Function Based Indexes to counter this problem. Rather than indexing a column, you index the function on that column, storing the product of the function, not the original column data.
For the optimizer to use function based indexes, the following session or system variables must be set:
QUERY_REWRITE_ENABLED=TRUE
QUERY_REWRITE_INTEGRITY=TRUSTED
Function based indexes are only visible to the Cost Based Optimizer
What is a global temporary table?
The definition of a temporary table is visible to all sessions, but the data in a temporary table is visible only to the session that inserts the data into the table. Use the CREATE GLOBAL TEMPORARY TABLE statement to create a temporary table. The ON COMMIT clause indicate if the data in the table is transaction-specific (the default) or session-specific.
What is an aggregate function? Compare with analytic function.
Aggregate function operates against a collection of values, but returns a single value. Analytic functions differ from aggregate functions in that they return multiple rows for each group.
Describe an autonomous transaction.
An autonomous transaction is an independent transaction that is initiated by another transaction, and executes without interfering with the parent transaction. When an autonomous transaction is called, the originating transaction gets suspend. Control is returned when the autonomous transaction does a COMMIT or ROLLBACK.
A procedure can be marked as autonomous by declaring it as PRAGMA AUTONOMOUS_TRANSACTION;. You may need to increase the TRANSACTIONS parameter to allow for the extra concurrent transactions.
What is a bind variable and why to use it?
A variable in a SQL statement that must be replaced with a valid value, or the address of a value, in order for the statement to successfully execute.
Used to minimize hard parses and maximize SQL reuse in the shared pool.
PL/SQL itself takes care of most of the issues to do with bind variables.
The only time you need to consciously decide to use bind variables when working with PL/SQL is when using Dynamic SQL.
Describe BULK COLLECT.
This is a form of array processing inside PL/SQL, which makes possible high-speed retrieval of data.
When data involved is very large, we can use Bulk Collect clause to fetch the data into local PL/SQL variables faster without looping through one record at a time. We can store the result set into either individual collection variables, if we are fetching certain number of columns or collection records, if we are fetching all the columns of the table.
Oracle Database achieves significant performance gains with this statement by greatly reducing the number of context switches between the PL/SQL and SQL statement execution engines.
What is a cluster?
Optional structure for storing table data. Clusters are groups of one or more tables physically stored together because they share common columns and are often used together. Because related rows are physically stored together, disk access time improves.
Describe collections and collection types.
Oracle uses collections in PL/SQL the same way other languages use arrays. Oracle provides three basic collections, each with an assortment of methods.
-Index-By Tables
-Nested Table Collections
-Varray Collections
What is a compiler?
A program to translate source code into code to be executed by a computer (object code)
What is a correlated subquery and how it my effect performance?
A subquery that uses values from the outer query. The Oracle database wants to execute the subquery once and use the results for all the evaluations in the outer query. With a correlated subquery, the database must run the subquery for each evaluation because it is based on the outer query’s data.
What is a data block?
Smallest logical unit of data storage in an Oracle database. Also called logical blocks, Oracle blocks, or pages. One data block corresponds to a specific number of bytes of physical database space on disk.
What is data dictionary?
The central set of tables and views that are used as a read-only reference about a particular database. A data dictionary stores such information as:
-The logical and physical structure of the database
-Valid users of the database
-Information about integrity constraints
-How much space is allocated for a schema object and how much of it is in use
A data dictionary is created when a database is created and is automatically updated when the structure of the database is updated.
What means a Data mining?
The computer-assisted process of digging through and analyzing enormous sets of data and then extracting the meaning of the data. Data mining tools predict behaviors and future trends, allowing businesses to make knowledge-driven decisions. Data mining tools can answer business questions that traditionally were too time consuming to resolve. They search databases for hidden patterns, finding predictive information that experts may miss because it lies outside their expectations.
What is a data type?
The attribute of a field that determines the kind of data the field can contain.
Explain concepts of a data warehouse?
A data warehouse is a relational database that is designed for query and analysis. It usually contains historical data derived from transaction data, but it can include data from other sources. It separates analysis workload from transaction workload and enables an organization to consolidate data from several sources.
In addition to a relational database, a data warehouse environment includes an extraction, transportation, transformation, and loading (ETL) solution, an online analytical processing (OLAP) engine, client analysis tools, and other applications that manage the process of gathering data and delivering it to business users.
Most data warehouses use a staging area to clean and process your operational data before
You may want to customize your warehouse's architecture for different groups within your organization. You can do this by adding data marts, which are systems designed for a particular line of business
Describe a database trigger with some details
A database trigger is a stored procedure that Oracle invokes ("fires") automatically when certain events occur, for example, when a DML operation modifies a certain table. Triggers enforce business rules, prevent incorrect values from being stored, and reduce the need to perform checking and cleanup operations in each application.
Here are some important items to remember about triggers.
-On insert triggers have no :OLD values.
-On delete triggers have no :NEW values
-Triggers do not commit transactions. If a transaction is rolled back, the data changed by the trigger is also rolled back.
-Commits, rollbacks and save points are not allowed in the trigger body. A commit/rollback affects the entire transaction, it is all or none.
-Unhandled exceptions in the trigger will cause a rollback of the entire transaction, not just the trigger.
-If more than one trigger is defined on an event, the order in which they fire is not defined. If the triggers must fire in order, you must create one trigger that executes all the actions in the required order.
-A trigger can cause other events to execute triggers.
-A trigger can not change a table that it has read from. This is the mutating table error issue.
Describe the package DBMS_PIPE.
Package DBMS_PIPE allows different sessions to communicate over named pipes. (A pipe is an area of memory used by one process to pass information to another.) You can use the procedures pack_message and send_message to pack a message into a pipe, then send it to another session in the same instance.
31.Can null keys be entered in cluster index, normal index ?
Yes.
32.Can Check constraint be used for self referential integrity ? How ?
Yes. In the CHECK condition for a column of a table, we can reference some other column of the same table and thus enforce self referential integrity.
33.What are the min. extents allocated to a rollback extent ?
Two
34.What are the states of a rollback segment ? What is the difference between partly available and needs recovery ?
The various states of a rollback segment are :ONLINE, OFFLINE, PARTLY AVAILABLE, NEEDS RECOVERY and INVALID.
35.What is the difference between unique key and primary key ?
Unique key can be null; Primary key cannot be null.
36.An insert statement followed by a create table statement followed by rollback ? Will the rows be inserted ?
Yes.
37.Can you define multiple savepoints ?
Yes.
38.Can you Rollback to any savepoint ?
Yes.
40.What is the maximum no. of columns a table can have ?
256.
21.What are mutating triggers ?
A trigger giving a SELECT on the table on which the trigger is written.
22.What are constraining triggers ?
A trigger giving an Insert / Update on a table having referential integrity constraint on the triggering table.
23.Describe Oracle database's physical and logical structure ?
Physical : Data files, Redo Log files, Control file.
Logical : Tables, Views, Tablespaces, etc.
24.Can you increase the size of a tablespace ? How ?
Yes, by adding datafiles to it.
25.Can you increase the size of datafiles ? How ?
No (for Oracle 7.0)Yes (for Oracle 7.3 by using the Resize clause ----- Confirm !!).
26.What is the use of Control files ?
Contains pointers to locations of various data files, redo log files, etc.
27.What is the use of Data Dictionary ?
Used by Oracle to store information about various physical and logical Oracle structures e.g. Tables, Tablespaces, datafiles, etc
28.What are the advantages of clusters ?
Access time reduced for joins.
29.What are the disadvantages of clusters ?
The time for Insert increases.
30.Can Long/Long RAW be clustered ?
No.
11.What are the constructs of a procedure, function or a package ?
The constructs of a procedure, function or a package are : variables and constants cursors exceptions
12.Why Create or Replace and not Drop and recreate procedures ?
So that Grants are not dropped.
13.Can you pass parameters in packages ? How ?
Yes. You can pass parameters to procedures or functions in a package.
14.What are the parts of a database trigger ?
The parts of a trigger are:
A triggering event or statement
A trigger restriction
A trigger action
15.What are the various types of database triggers ?
There are 12 types of triggers, they are combination of :
Insert, Delete and Update Triggers.
Before and After Triggers.
Row and Statement Triggers.(3*2*2=12)
16.What is the advantage of a stored procedure over a database trigger ?
We have control over the firing of a stored procedure but we have no control over the firing of a trigger.
17.What is the maximum no. of statements that can be specified in a trigger statement ?
One.
18.Can views be specified in a trigger statement ?
No
19.What are the values of :new and :old in Insert/Delete/Update Triggers ?
INSERT : new = new value, old = NULL
DELETE : new = NULL, old = old value
UPDATE : new = new value, old = old value
20.What are cascading triggers? What is the maximum no of cascading triggers at a time?
When a statement in a trigger body causes another trigger to be fired, the triggers are said to be cascading. Max = 32.
1.What are the various types of Exceptions ?
User defined and Predefined Exceptions.
2.Can we define exceptions twice in same block ?
No.
3.What is the difference between a procedure and a function ?
Functions return a single variable by value whereas procedures do not return any variable by value. Rather they return multiple variables by passing variables by reference through their OUT parameter.
4.Can you have two functions with the same name in a PL/SQL block ?
Yes.
5.Can you have two stored functions with the same name ?
Yes.
6.Can you call a stored function in the constraint of a table ?
No.
7.What are the various types of parameter modes in a procedure ?
IN, OUT AND INOUT.
8.What is Over Loading and what are its restrictions ?
OverLoading means an object performing different functions depending upon the no. of parameters or the data type of the parameters passed to it.
9.Can functions be overloaded ?
Yes.
10.Can 2 functions have same name & input parameters but differ only by return datatype?
No.
What is precedence in Oracle?
It is the order in which Oracle evaluates different conditions in the same expression
What is a predicate in SQL?
Syntax that specifies a subset of rows to be returned. Predicates are specified in the WHERE clause of a SQL statement.
What is a primary key?
The column or set of columns included in the definition of a table's PRIMARY KEY constraint. A primary key's values uniquely identify the rows in a table. Only one primary key can be defined for each table.
What is a Primary Key Constraint?
This constraint is used to identify the primary key for a table. This operation requires that the primary columns are unique, and Oracle will create a unique index on the target primary key.
What is Resource Definition Framework (RDF)?
A set of rules (a sort of language) for creating descriptions of information, especially information available on the World Wide Web. RDF could be used to describe a collection of books, or artists, or a collection of web pages as in the RSS data format which uses RDF to create machine-readable summaries of web sites.
RDF is also used in XPFE applications to define the relationships between different collections of elements, for example RDF could be used to define the relationship between the data in a database and the way that data is displayed to a user.
What is a redo log?
A set of files that protect altered database data in memory that has not been written to the datafiles. The redo log can consist of two parts: the online redo log and the archived redo log.
What is a REF CURSOR? Compare strong and week ref cursor types.
A REF CURSOR is a data type. A variable created based on such a data type is generally called a cursor variable. A cursor variable can be associated with different queries at run-time. The primary advantage of using cursor variables is their capability to pass result sets between sub programs (like stored procedures, functions, packages etc.).
A strong ref cursor type definition specifies a return type, a weak definition does not.
Since Oracle 9i you can use SYS_REFCURSOR as the type for the returning REF_CURSOR.
What is referential datatype?
A variable can have either a simple or "scalar" datatype, such as NUMBER or VARCHAR2. Alternately, a variable can have a referential datatype that uses reference to a table column to derive its datatype
What is a schema?
Collection of logical structures of data, or schema objects
What is Semantic Web?
The Semantic Web is an extension of the current Web that will allow you to find, share, and combine information more easily. It relies on machine-readable information and metadata expressed in RDF.
What is a Shared pool?
Portion of the system global area that contains shared memory constructs such as shared SQL areas. A shared SQL area is required to process every unique SQL statement submitted to a database.
flush shared pool – clear all SQL statements that are in the Shared Pool Area
What is SQL trace?
The SQL Trace facility provides performance information on individual SQL statements. It generates the following statistics for each statement:
-Parse, execute, and fetch counts
-CPU and elapsed times
-Physical reads and logical reads
-Number of rows processed
-Misses on the library cache
-Username under which each parse occurred
-Each commit and rollback
You can enable the SQL Trace facility for a session or for an instance. When the SQL Trace facility is enabled, performance statistics for all SQL statements executed in a user session or in the instance are placed into trace files.
What is SQLCODE?
The function SQLCODE returns the number code of the most recent exception
What is statistics?
Statistics calculate the data distribution and storage characteristics of tables, columns, indexes, and partitions. The cost-based optimization approach uses these statistics to calculate the selectivity of predicates and to estimate the cost of each execution plan. Selectivity is the fraction of rows in a table that the SQL statement's predicate chooses. The optimizer uses the selectivity of a predicate to estimate the cost of a particular access method and to determine the optimal join order and join method.
The statistics are stored in the data dictionary and can be exported from one database and imported into another
What is a table function?
Table functions are designed to return a set of rows through PL/SQL logic but are intended to be used as a normal table or view in a SQL statement.
Example: select * from table(a_function(a_parm));
What is tkprof?
TKPROF program formats the contents of the trace file and place the output into a readable output file.
What difference is between TRANSLATE and REPLACE functions?
TRANSLATE('char','search_str','replace_str')
Replace every occurrence of search_str with replace_str
Unlike REPLACE() if replace_str is NULL the function returns NULL
What is an UNIQUE key constraint?
A data integrity constraint requiring that every value in a column or set of columns (key) be unique--that is, no two rows of a table have duplicate values in a specified column or set of columns.
What is a view?
A logical representation of another table or combination of tables
What is a hash function?
A hash function returns a value for an input, and the output is generally shorter or more compact than the input
What is an index?
Optional structure associated with tables and clusters. You can create indexes on one or more columns of a table to speed access to data on that table.
Oracle's two major index types are Bitmap indexes and B-Tree indexes. B-Tree indexes are the regular type, and bitmap indexes are a highly compressed index type that tends to be used primarily for data warehouses.
The B-tree index is the most-used type of index that Oracle provides. It provides fast lookup of rows containing a desired key value. It is not suitable if the column(s) being indexed are of low cardinality (number of distinct values). For those situations, a bitmap index is very useful, but be aware that bitmap indexes are very expensive to update when DML is performed on the indexed table.
What is an Oracle instance?
A system global area (SGA) and the Oracle background processes constitute an Oracle database instance. Every time a database is started, a system global area is allocated and Oracle background processes are started. The SGA is deallocated when the instance shuts down.
After starting an instance, Oracle associates the instance with the specified database. This is called mounting the database. The database is then ready to be opened, which makes it accessible to authorized users.
What is integrity constraint?
Declarative method of defining a rule for a column of a table. Integrity constraints enforce the business rules associated with a database and prevent the entry of invalid information into tables.
What is JDBC?
Java Database Connectivity is an API (Applications Programming Interface) that allows Java to send SQL statements to an object-relational database such as Oracle.
What are Oracle logical structures
Logical structures of an Oracle database include tablespaces, schema objects, data blocks, extents, and segments. Because the physical and logical structures are separate, the physical storage of data can be managed without affecting the access to logical storage structures
What is a materialized view
A materialized view provides indirect access to table data by storing the results of a query in a separate schema object.
What is MERGE statement in Oracle?
The MERGE statement (AKA "UPSERT") released in Oracle 9i is possibly one of the most useful ETL-enabling technologies built into the Oracle kernel. It enables us to either UPDATE or INSERT a row into a target table in one statement. You tell Oracle your rules for determining whether a target row should UPDATEd or INSERTed from the source
What is a mutating table?
A table that is currently being modified by an UPDATE, DELETE, or INSERT statement, or a table that might be updated by the effects of a DELETE CASCADE constraint.
The error is encountered when a row-level trigger accesses the same table on which it is based, while executing.
What is a nested table?
Nested table collections are an extension of the index-by tables. The main difference between the two is that nested tables can be stored in a database column
Describe the normalization.
The process of organizing data to minimize redundancy. Normalization usually involves dividing a database into tables and defining relationships between the tables. The objective is to isolate data so that additions, deletions, and modifications of a field can be made in just one table and then propagated through the rest of the database via the defined relationships.
-First Normal Form (1NF): Each field in a table contains different information. No Repeating groups.
-Second Normal Form (2NF):
Primary key can not be subdivided into separate logical entities.
Every non-key attribute is fully dependent on the key.
-Third Normal Form (3NF)
No functional dependencies on non-key fields.
What is an outer join?
An outer join does not require each record in the two joined tables to have a matching record in the other table
What is Web Ontology Language?
An OWL ontology may include descriptions of classes, along with their related properties and instances. OWL is designed for use by applications that need to process the content of information instead of just presenting information to humans. It facilitates greater machine interpretability of Web content than that supported by XML, RDF, and RDF Schema
What is Oracle package?
A schema object that groups logically related PL/SQL types, items, and subprograms. Packages offer several advantages: modularity, easier application design, information hiding, added functionality, and better performance.
What are parsing and its phases? What is soft parse?
The first two phases of the parse are Syntax Check and Semantic Analysis happen for each and every SQL statement within the database. Then Oracle database needs to check in the Shared Pool to determine if the current SQL statement being parsed has already been processed by any other sessions.
If yes, the parse operation can skip the next two functions in the process: Optimization and Row Source Generation (it is soft parse)
What is partitioning?
Partitioning is a method of splitting large tables and indexes into smaller, more manageable pieces.
What are Oracle physical structures?
Physical database structures of an Oracle database include datafiles, redo log files, and control files.
Describe general concepts of PL/SQL.
PL/SQL is Oracle's procedural extension to SQL. With PL/SQL, you can manipulate data with SQL statements, and control program flow with procedural constructs such as IF-THEN and LOOP. You can also declare constants and variables, define procedures and functions, use collections and object types, and trap run-time errors.
Applications written using any of the Oracle programmatic interfaces can call PL/SQL stored procedures and send blocks of PL/SQL code to the server for execution.
Because it runs inside the database, PL/SQL code is very efficient for data-intensive operations, and minimizes network traffic in client/server applications.
Describe PLS_INTEGER datatype.
PLS_INTEGER and BINARY_INTEGER are identical datatypes and are only available in PL/SQL. You cannot create a column in a table with either of these data types. PLS_INTEGER is a highly efficient integer 32-bit data type. You will most commonly see PLS_INETGER (and BINARY_INETGER) in PL/SQL routines as an index variable. An associative array (INDEX BY TABLE) index. Both PLS_INTEGER and BINARY_INTEGER allow whole numbers only. Decimal fractions are rounded to the nearest whole number.
What is a pragma?
Pragma is a keyword in Oracle PL/SQL that is used to provide an instruction to the compiler like PRAGMA AUTONOMOUS_TRANSACTION
Describe the package DBMS_STATS.
With the DBMS_STATS package you can view and modify optimizer statistics gathered for database objects.
The statistics to be viewed or modified can reside in the dictionary or in a table created in the user's schema for this purpose. You can also collect and manage user-defined statistics for tables and domain indexes using this package. For example, if the DELETE_COLUMN_STATS procedure is invoked on a column for which an association is defined, user-defined statistics for that column are deleted in addition to deletion of the standard statistics.
Only statistics stored in the dictionary have an impact on the cost-based optimizer. You can also use DBMS_STATS to gather statistics in parallel
Describe the package DBMS_TRACE.
The DBMS_TRACE package contains the interface to trace PL/SQL functions, procedures, and exceptions.
DBMS_TRACE provides subprograms to start and stop PL/SQL tracing in a session. Oracle collects the trace data as the program executes and writes it to database tables.
What is the DCL language?
DCL is Data Control Language statements (GRANT, COMMIT)
What is the DDL language?
DDL (Data Definition Language) statements are used to define the database structure or schema (CREATE, ALTER, TRUNCATE)
What is a dedicated server?
A database server configuration in which a server process handles requests for a single user process.
What are dispatcher processes in Oracle?
Optional background processes, present only when a shared server configuration is used. At least one dispatcher process is created for every communication protocol in use (D000, . . ., Dnnn). Each dispatcher process is responsible for routing requests from connected user processes to available shared server processes and returning the responses back to the appropriate user processes.
What is distributed transaction?
A transaction that updates data on two or more networked computer systems
distributed transaction
What is the DML language?
DML (Data Manipulation Language) statements are used for managing data within schema objects. (INSERT, MERGE, EXPLAIN PLAN)
Describe the embedded SQL and Pro*C environment.
SQL statements embedded within a program and prepared before the program is executed.
It is a method of combining the computing power of a high-level language like C/C++ and the database manipulation capabilities of SQL. It allows you to execute any SQL statement from an application program. Oracle's embedded SQL environment is called Pro*C.
A Pro*C program is compiled in two steps. First, the Pro*C precompiler recognizes the SQL statements embedded in the program, and replaces them with appropriate calls to the functions in the SQL runtime library. The output is pure C/C++ code with all the pure C/C++ portions intact. Then, a regular C/C++ compiler is used to compile the code and produces the executable.
All SQL statements need to start with EXEC SQL and end with a semicolon
What is ERP System?
Enterprise Resource Planning, a system that is used to manage all aspects of a company's operations.
ERP is a way to integrate the data and processes of an organization into one single system
Describe ETL process.
Extract, Transform, and Load (ETL) is a process in data warehousing that involves extracting data from outside sources, transforming it to fit business needs (which can include quality levels), and loading into the end target, i.e. the data warehouse.
ETL can in fact refer to a process that loads any database
ETL can also be used for the integration with legacy systems
What is EXPLANE PLAN? What information can you get using this statement?
The EXPLAIN PLAN statement displays execution plans chosen by the Oracle optimizer for SELECT, UPDATE, INSERT, and DELETE statements. A statement's execution plan is the sequence of operations Oracle performs to run the statement.
The row source tree is the core of the execution plan. It shows the following information:
-An ordering of the tables referenced by the statement
-An access method for each table mentioned in the statement
-A join method for tables affected by join operations in the statement
-Data operations like filter, sort, or aggregation
In addition to the row source tree, the plan table contains information about the following:
-Optimization, such as the cost and cardinality of each operation
-Partitioning, such as the set of accessed partitions
-Parallel execution, such as the distribution method of join inputs
The EXPLAIN PLAN results let you determine whether the optimizer selects a particular execution plan, such as, nested loops join. It also helps you to understand the optimizer decisions, such as why the optimizer chose a nested loops join instead of a hash join, and lets you understand the performance of a query.
You can use the V$SQL_PLAN to display the execution plan of a SQL statement
Describe an external tables.
External tables feature is a complement to existing SQL*Loader functionality. It allows you to access data in external sources as if it were in a table in the database.
As of Oracle Database 10g, external tables can also be written to.
To unload data, you use the ORACLE_DATAPUMP access driver. The data stream that is unloaded is in a proprietary format and contains all the column data for every row being unloaded.
An unload operation also creates a metadata stream that describes the contents of the data stream. The information in the metadata stream is required for loading the data stream. Therefore, the metadata stream is written to the datafile and placed before the data stream.
What is a function-based indexes?
Traditionally, performing a function on an indexed column in the where clause of a query guaranteed an index would not be used. Oracle 8i introduced Function Based Indexes to counter this problem. Rather than indexing a column, you index the function on that column, storing the product of the function, not the original column data.
For the optimizer to use function based indexes, the following session or system variables must be set:
QUERY_REWRITE_ENABLED=TRUE
QUERY_REWRITE_INTEGRITY=TRUSTED
Function based indexes are only visible to the Cost Based Optimizer
What is a global temporary table?
The definition of a temporary table is visible to all sessions, but the data in a temporary table is visible only to the session that inserts the data into the table. Use the CREATE GLOBAL TEMPORARY TABLE statement to create a temporary table. The ON COMMIT clause indicate if the data in the table is transaction-specific (the default) or session-specific.
What is an aggregate function? Compare with analytic function.
Aggregate function operates against a collection of values, but returns a single value. Analytic functions differ from aggregate functions in that they return multiple rows for each group.
Describe an autonomous transaction.
An autonomous transaction is an independent transaction that is initiated by another transaction, and executes without interfering with the parent transaction. When an autonomous transaction is called, the originating transaction gets suspend. Control is returned when the autonomous transaction does a COMMIT or ROLLBACK.
A procedure can be marked as autonomous by declaring it as PRAGMA AUTONOMOUS_TRANSACTION;. You may need to increase the TRANSACTIONS parameter to allow for the extra concurrent transactions.
What is a bind variable and why to use it?
A variable in a SQL statement that must be replaced with a valid value, or the address of a value, in order for the statement to successfully execute.
Used to minimize hard parses and maximize SQL reuse in the shared pool.
PL/SQL itself takes care of most of the issues to do with bind variables.
The only time you need to consciously decide to use bind variables when working with PL/SQL is when using Dynamic SQL.
Describe BULK COLLECT.
This is a form of array processing inside PL/SQL, which makes possible high-speed retrieval of data.
When data involved is very large, we can use Bulk Collect clause to fetch the data into local PL/SQL variables faster without looping through one record at a time. We can store the result set into either individual collection variables, if we are fetching certain number of columns or collection records, if we are fetching all the columns of the table.
Oracle Database achieves significant performance gains with this statement by greatly reducing the number of context switches between the PL/SQL and SQL statement execution engines.
What is a cluster?
Optional structure for storing table data. Clusters are groups of one or more tables physically stored together because they share common columns and are often used together. Because related rows are physically stored together, disk access time improves.
Describe collections and collection types.
Oracle uses collections in PL/SQL the same way other languages use arrays. Oracle provides three basic collections, each with an assortment of methods.
-Index-By Tables
-Nested Table Collections
-Varray Collections
What is a compiler?
A program to translate source code into code to be executed by a computer (object code)
What is a correlated subquery and how it my effect performance?
A subquery that uses values from the outer query. The Oracle database wants to execute the subquery once and use the results for all the evaluations in the outer query. With a correlated subquery, the database must run the subquery for each evaluation because it is based on the outer query’s data.
What is a data block?
Smallest logical unit of data storage in an Oracle database. Also called logical blocks, Oracle blocks, or pages. One data block corresponds to a specific number of bytes of physical database space on disk.
What is data dictionary?
The central set of tables and views that are used as a read-only reference about a particular database. A data dictionary stores such information as:
-The logical and physical structure of the database
-Valid users of the database
-Information about integrity constraints
-How much space is allocated for a schema object and how much of it is in use
A data dictionary is created when a database is created and is automatically updated when the structure of the database is updated.
What means a Data mining?
The computer-assisted process of digging through and analyzing enormous sets of data and then extracting the meaning of the data. Data mining tools predict behaviors and future trends, allowing businesses to make knowledge-driven decisions. Data mining tools can answer business questions that traditionally were too time consuming to resolve. They search databases for hidden patterns, finding predictive information that experts may miss because it lies outside their expectations.
What is a data type?
The attribute of a field that determines the kind of data the field can contain.
Explain concepts of a data warehouse?
A data warehouse is a relational database that is designed for query and analysis. It usually contains historical data derived from transaction data, but it can include data from other sources. It separates analysis workload from transaction workload and enables an organization to consolidate data from several sources.
In addition to a relational database, a data warehouse environment includes an extraction, transportation, transformation, and loading (ETL) solution, an online analytical processing (OLAP) engine, client analysis tools, and other applications that manage the process of gathering data and delivering it to business users.
Most data warehouses use a staging area to clean and process your operational data before
You may want to customize your warehouse's architecture for different groups within your organization. You can do this by adding data marts, which are systems designed for a particular line of business
Describe a database trigger with some details
A database trigger is a stored procedure that Oracle invokes ("fires") automatically when certain events occur, for example, when a DML operation modifies a certain table. Triggers enforce business rules, prevent incorrect values from being stored, and reduce the need to perform checking and cleanup operations in each application.
Here are some important items to remember about triggers.
-On insert triggers have no :OLD values.
-On delete triggers have no :NEW values
-Triggers do not commit transactions. If a transaction is rolled back, the data changed by the trigger is also rolled back.
-Commits, rollbacks and save points are not allowed in the trigger body. A commit/rollback affects the entire transaction, it is all or none.
-Unhandled exceptions in the trigger will cause a rollback of the entire transaction, not just the trigger.
-If more than one trigger is defined on an event, the order in which they fire is not defined. If the triggers must fire in order, you must create one trigger that executes all the actions in the required order.
-A trigger can cause other events to execute triggers.
-A trigger can not change a table that it has read from. This is the mutating table error issue.
Describe the package DBMS_PIPE.
Package DBMS_PIPE allows different sessions to communicate over named pipes. (A pipe is an area of memory used by one process to pass information to another.) You can use the procedures pack_message and send_message to pack a message into a pipe, then send it to another session in the same instance.
0 comments: