Group of tables physically stored together because they share common columns and are often used together is called Cluster.
2. Explain What is a lookup table in database? where can we use these type of table?
Lookup tables are like constraints which hold a value. The content in the lookup tables dosen't change often, hence refered as lookup (reference).
Example : Countries table
Countries are not added on regular bases hence one can consider the table as a lookup table. As and when needed in some other table the countries information can be fetched base on PK - Country-Code column. Eg - Emp_Per_Info.
3. Explain Different types of table join?
different types of joins are
-Cartesian Product or Cross Join
-Inner Join
-Equi Join or Natural Join
-Non Equi Join
-Self Join
-Outer Join
-Left Outer Join
-Right Outer Join
-Full Outer Join
4. Explain When can hash cluster used?
Hash clusters are useful in cases where :
(i) There is a uniform, even and predictable no. of key values.
(ii) Queries using equality predicates.
(iii) The table is NOT growing constantly, and the keys are rarely updated.
5. Explain What is an Oracle sequence?
A sequence is a database object created by a user that can be used to generate unique integers. A typical usage of sequences is to generate primary key values which are unique for each row.
It is generated and incremented (or decremented) by an internal Oracle routine. It can be used by multiple users and for multiple tables too. A sequence can be used instead of writing an application code for sequence-generating routine.
An Extent is a collection of contagious blocks the second level of granuality of the storage in database is called extent.it is defined as the collectionof contagious datablocks.
7. Explain What is a cursor? Why do you need them? What are the different kinds of cursor?
oracle uses private sql area to execute sql queries and store the information.pl/sql uses cursors to name these private sql area and access its stored information
there r two types of cursors
implicit
explicit
implicit cursors are used for all dml and single row queries.these are system defined
explicit cursors are used for queries which return multiple row .these are user defined.
8. Explain What is hash cluster?
This is an alternate to index, as index fast retrieve rows from single column,same way hash cluster fast retrieve rows from group of table and reduce i/o .
9. Explain What is a tablespace?
An Oracle database consists of one or more logical storage units called tablespaces, which collectivley store all of the database's data.
Each tablespace in an ORACLE database consists of one or more files called datafiles, which are physical structures that conform with the operating system in which Oracle is running.
For Example::: A simple Oracle Database have one tablespace and one datafile.
10. Explain What is index cluster?
An index cluster uses an index to maintain data within the cluster. The structure of a cluster index is similar to a normal index.
Although a normal index does not store NULL key values, and index cluster can store it.
And, a cluster index is likely to be smaller than a normal index because, there is only one entry for each key value in the cluster index, even if that is repeating.
11. Tell us Do you need a commit after DDL statements?
DDL commands are auto commited commands, after DDL job execution
no need to commit again
12. What is the difference between indexes,views,synonyms?
An index is a method of allowing faster retrieval of records.
A view is an virtual table formed using a single table or one or more tables.
A synonym is an alternative name for objects such as tables, views, sequences, stored procedures, and other database objects
13. Explain What are the basic element of base configuration of an Oracle database?
It consists of
one or more data files.
one or more control files.
two or more redo log files.
The Database contains
multiple users/schemas
one or more rollback segments
one or more tablespaces
Data dictionary tables
User objects (table,indexes,views etc.,)
The server that access the database consists of
SGA (Database buffer, Dictionary Cache Buffers, Redo log buffers, Shared SQL pool)
SMON (System MONito)
PMON (Process MONitor)
LGWR (LoG Write)
DBWR (Data Base Write)
ARCH (ARCHiver)
CKPT (Check Point)
RECO
Dispatcher
User Process with associated PGS
its called snap shot too old error.
it comes if a user is updates a table with loop option, in another session an user to see the info(select) then its shows ora-1555
error
15. Explain What rule optimizations are possible in SQL query tuning?
FIRST_ROWS
LAST_ROWS
FIRST_ROWS_10
FIRST_ROWS_100
FIRST_ROWS_1000
ALL_ROWS
16. Explain How do we handle exceptions / errors in stored procedures?
Exceptions can be handled in various ways in the stored procedures. Method (1) could be using EXCEPTION block in the procedure within which use WHEN <exceptionname> THEN. Method (2) could be declare a variable in DECLARE section as EXCEPTION type, raise it wherever required using RAISE <exception variable name> and handle it in EXCEPTION part using WHEN clause. Method (3) could be using the keywords SQLCODE & SQLERRM to display error no and error code..
17. Explain Do a view contain data?
A view does not contain any data of its own, but is like a window through which data from other tables can be viewed and changed.
18. Explain What are the different types of segments?
temp segment,
data segment,
table segment,
index segment
19. Explain What does a control file contains?
information about the database,redologfile and datafile,archivelog file location and current scn no
and redo log file
1. Database name and identifier.
2. Time stamp of database creation.
3. Tablespace names.
4. Backup information.
5. Checkpoint information.
6. Current online redo log file sequence numbe
20. Explain What is a deadlock?
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.
Deadlock is a kind of situation,which occured in a network of database environment while multiple processes is waiting for their completion, whereas some other process is still running till the time we use the rollback or commit commands or stopped the blocked process by database systems program itself.
21. What is uses of rollback segment?
The use of RollBack Segment is Database is - to maintain read consistency between multiple transactions.
Rollback segments are the areas in your database which are used to temporarily save the previous values when some updates (by 'updates', inserts or deletes as well) or any other transactions going on.
22. Explain some built-in functions that can be used in SQL queries?
Character functions analyze and modify the contents of CHAR and VARCHAR2 string variables.
Numeric functions are a full range of operations that manipulate numbers, including trigonometric, logarithmic, and exponential functions.
Date functions are utilities that allow programmers to perform high-level actions on date variables, including date arithmetic.
Conversion functions convert from one datatype to another, often formatting the output data at the same time.
LOB functions allow operations on LOB (large object) data.
Miscellaneous functions perform operations that don't fall into any of the other categories.
Example of built in functions:
1. to_date - a convertion function that will convert a string date to a date datatype
2. sum - a group function that will return the summation of all values in the column
3. upper - a single row function that will return all characters in a column in upper case
23. What is Parallel Server in Oracle?
Multiple instances accessing the same database (Only In Multi-CPU environments)
24. What are the types of synonyms in Oracle?
A synonym is an alternative name for objects such as tables, views, sequences, stored procedures, and other database objects.
A synonym (synonym-name) is an alias for an object (such as a table). The object does not need to exist at the time of its creation. Synonyms can't be used in a drop and truncate statements. If this is tried, it results in a ORA-00942: table or view does not exist
The syntax for creating a synonym is:
create [or replace] [public] synonym [schema .] synonym_name
for [schema .] object_name [@ dblink];
The or replace phrase allows you to recreate the synonym (if it already exists) without having to issue a DROP synonym command.
The public phrase means that the synonym is a public synonym and is accessible to all users. Remember though that the user must first have the appropriate privileges to the object to use the synonym.
The schema phrase is the appropriate schema. If this phrase is omitted, Oracle assumes that you are referring to your own schema.
The object_name phrase is the name of the object for which you are creating the synonym. It can be one of the following:
table package
view materialized view
sequence java class schema object
stored procedure user-defined object
function synonym
Type of Synonym are:-
Public (Accessible to all)
Private (Accessible to owner)
25. Explain What is a database instance?
A database instance (Server) is a set of memory structure and background processes that access a set of database files.
The 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 against data file.
