ORACLE Introduction to Oracle Oracle is a comprehensive operating environment that packs the power of mainframe
relation database management system into user's micro computer. It provides a set of functional
programs that user can use as tools to build structures and perform tasks. Because applications are
developed on oracle are completely portable to the other versions of the programmer can create a
complex application in a single user, environment and them move it to a multi user platform.Users
do not have to be an expert to appriciate Oracle but the better user understand the program,the
more productively and creatively he can use the tools it provides Relational Database Management system
A Relational Database Management System (RDBMS) can perform a wide
range of tasks. It acts as a transparent interface between the physical storage and the logical
presentation of data.It provides a set of more or less flexible and sofesticated tools for handling
information. Users can use the tools to · Define a Database
· Query the Database
· Add,Edit and Delete data.
· Modify the structure of the Database.
· Communicate with in the Networks.
· Exports and Imports the data.
· Security.
Oracle the Right Tool
To keep track of records is often the best reason to consider buying a
software package.Choosing a proven product increase the chance that we will get a system capable
of meeting all our needs .Oracle has had many years in the relational market to often on the
large,Complex applications. Although Oracle demands greater expertise on the part of the
application developed,an application developed oracle will be able to keep pace with growth and
change in the world at large.
Oracle Gives You Security and Control
Oracle has several features that ensure the integrity of the data base. If an interruption occurs in processing, a Rollback can reset the database to a point before the disaster. If a restore is
necessary, Oracle has a Roll forward command for recreating your database to its mast resent save
point.
Oracle provides user with several functions for securing data. Grant and Revoke commands limit access to information down to the row and column levels. Views are valuable
feature for limiting access to the primary tables in the database.
The Oracle Environment Oracle is a modular system that consists of the Oracle database and several functional programs. Oracle's tools do four major things of work. 1. Database Management.
2. Data access and Manipulation.
3. Programming
4. Connectivity.
Database Management Tools
This (is usually known as RDBMS by oracle) includes the core programs for Oracle's database management system, the Oracle database with its associated tables and views, which are
stores information related to every fact of the database system.User name, user access rights, table
attribute, storage information and auditing data for disaster recovery are stored in the data
dictionary.
The Database Administrator
The person responsible for a Database is called the DatabaseAdministrator(DBA).The DBA has a special set of previleges and has complete over the database.Any system with more
than one user should have a DBA.The function of DBA include.
Creating primary database storage structures.
Modifying the structure of the database.
Backing up and restoring the database.
Monitoring database performance and efficiency. Transfering data between database and external files. Manipulating the phisical location of the database.
THE END USER
End user have less complex but equally important tasks.They must be able to · Query the database.
· Generate printed output.
· Share data with or secure data from others.
· Use non-export methods to update the database.
· Define applications from a user point of view.
WHY ORACLE ?
Oracle is an open system i.e. the database is accessing by different data access
languages like SQL, Visual Basic, Power Builders,Delphi,VC++,JAVA etc.
Oracle supports database upto gigabytes in size.
* Oracle supports large number of concurrent users.
* Oracle supports true client/server environment.if enable processing to be spilt between
database server and the client application programs.
* Oracle provides high levelof data security in terms of users, passwords privileges, and
permissions.
* Oracle database behaves same on all platform line Windows,Unix,Dos,Mainframes etc.
STRUCTURE OF ORACLE
physical structure:
one or more data files, Two of more log files, One control file.
Logical structure:
Table spaces,
Segments, Extents, Data blocks.
The data files contain all user data in terms of tables, index, and views. The log files contain the information to open and be recovered, of Undone after a transaction(Rollback).
The control file physical data, media information to open and manage data files .If the control file is damaged the server will not be able to open are use the database even if the
database is undamaged.
What are the Back ground processes in Oracle and what are they. ? There are basically 9 Processes but in a general system we need to mention the first five background processes. They do the house keeping activities for the Oracle and are common in
any system.
a) Data Base Writer(DBWR) :: Data Base Writer Writes Modified blocks from Database buffer cache to Data Files.This is required since the data is not written whenever a transaction is commited.
b)LogWriter(LGWR) :: LogWriter writes the redo log entries to disk. Redo Log data is generated in redo log buffer of SGA. As transaction commits and log buffer fills, LGWR writes log entries into a online redo
log file.
c) System Monitor(SMON) :: The System Monitor performs instance recovery at instance startup. This is useful for recovery from system failure.
d)Process Monitor(PMON) :: The Process Monitor peforms process recovery when user Process fails. Pmon Clears and Frees resources that process was using.
e) CheckPoint(CKPT) :: At Specified times, all modified database buffers in SGA are written to data files by DBWR at Checkpoints and Updating all data files and control files of database to indicate the
most recent checkpoint.
f)Archieves(ARCH) :: The Archiver copies online redo log files to archival storal when they are busy. g) Recoveror(RECO) :: The Recoveror is used to resolve the distributed transaction in network h) Dispatcher (Dnnn) :: The Dispatcher is useful in Multi Threaded Architecture i) Lckn :: We can have upto 10 lock processes for inter instance locking in parallel sql. NORMALIZATION
Normalization is a process of simplifying the relationship between data elements in a record. Through Normalization a collection of data in a record structure is replaced by successive
record structures that are simpler and more predictable and therefore more manageable. It is
process of reducing redundancy and eliminating the anomalies. (anamali means inconsistent state.)
ORACLE DATABASE
1). What is difference between UNIQUE constraint and PRIMARY KEY constraint ?
Primary Key Column(s) cannot contain null values but UNIQUE key column(s) can contain null values.
2). Describe Referential Integrity ?
Referential integrity in a relational database is consistency between coupled tables. Referential integrity is usually enforced by the combination of a primary key or candidate key and a foreign key. For referential integrity to hold, any field in a table that is declared a foreign key can contain only values from a parent table's primary key or a candidate key. For instance, deleting a record that contains a value referred to by a foreign key in another table would break referential integrity. The relational database management system (RDBMS) enforces referential integrity, normally either by deleting the foreign key rows as well to maintain integrity, or by returning an error and not perfoming the delete. Which method is used would be defined by the definition of the referential integrity constraint.
Eg: An employee database stores the department in which each employee works. The field "DepartmentNumber" in the Employee table is declared a foreign key, and it refers to the field "Index" in the Department table which is declared a primary key. Referential integrity would be broken by deleting a department from the Department table if employees listed in the Employee table are listed as working for that the department.
3). What are the Referential actions supported by FOREIGN KEY integrity constraint ?
The referential actions supported by the FOREIGN KEY integrity constraints of Oracle are UPDATE and DELETE NO ACTION, and DELETE CASCADE.
4). What is self-referential integrity constraint ?
Self-referential integrity constraint is a type of foreign key that references the parent key in the same table. For example if in the Employee table we have a column called manager_id then the manager would also be an employee. So the manager should be defined first before defining the employees under him.i.e the employee_id u enter in manager_id field should have been already defined as an employee. So manager_id points to employee_id.
1. What constitute an ORACLE Instance ?
An Oracle Instance:
• Is a means to access an Oracle database
• Always opens one and only one database
• Consists of memory and background process structures
2. What is SGA ?
The System Global Area (SGA) is a shared memory region allocated by ORACLE that contains data and control information for one ORACLE instance.SGA is allocated at instance startup, and is a fundamental component of an Oracle Instance.
3. What are the components of SGA ?
The SGA consists of several memory structures:
– Shared Pool
– Database Buffer Cache
– Redo Log Buffer
– Other structures (for example, lock and latch
management, statistical data).
4. What do Database Buffers contain ?
Database buffers store the most recently used blocks of database data. It can also contain modified data that has not yet been permanently written to disk.
What is the difference between VARCHAR, VARCHAR2 and CHAR data types?
Varchar and varchar2 ... the only difference being the naming convention varchar followed by database vendors and varchar2 which is set by ansi and has been incorporated by almost all database vendors.
CHAR .. would add spaces and use the entire size allocated to it by filling the rest with spaces. eg gender CHAR(10) .. gender:='MALE' would store as 'MALE ' -- notice blank spaces
What is the use of DESC in SQL
1. To Describe the table structure.
2. To sort the column in descending order
How does one add a column to the middle of a table?
Anytime u add a new column .. its added after last column, You can only alter the way u want to access a column using SQL query
What is the advantage of specifying WITH GRANT OPTION in the GRANT command?
with the help of GRANT option the grantee can also grant the privileges that they hav got from the granter to the other users
Primary Key Column(s) cannot contain null values but UNIQUE key column(s) can contain null values.
2). Describe Referential Integrity ?
Referential integrity in a relational database is consistency between coupled tables. Referential integrity is usually enforced by the combination of a primary key or candidate key and a foreign key. For referential integrity to hold, any field in a table that is declared a foreign key can contain only values from a parent table's primary key or a candidate key. For instance, deleting a record that contains a value referred to by a foreign key in another table would break referential integrity. The relational database management system (RDBMS) enforces referential integrity, normally either by deleting the foreign key rows as well to maintain integrity, or by returning an error and not perfoming the delete. Which method is used would be defined by the definition of the referential integrity constraint.
Eg: An employee database stores the department in which each employee works. The field "DepartmentNumber" in the Employee table is declared a foreign key, and it refers to the field "Index" in the Department table which is declared a primary key. Referential integrity would be broken by deleting a department from the Department table if employees listed in the Employee table are listed as working for that the department.
3). What are the Referential actions supported by FOREIGN KEY integrity constraint ?
The referential actions supported by the FOREIGN KEY integrity constraints of Oracle are UPDATE and DELETE NO ACTION, and DELETE CASCADE.
4). What is self-referential integrity constraint ?
Self-referential integrity constraint is a type of foreign key that references the parent key in the same table. For example if in the Employee table we have a column called manager_id then the manager would also be an employee. So the manager should be defined first before defining the employees under him.i.e the employee_id u enter in manager_id field should have been already defined as an employee. So manager_id points to employee_id.
1. What constitute an ORACLE Instance ?
An Oracle Instance:
• Is a means to access an Oracle database
• Always opens one and only one database
• Consists of memory and background process structures
2. What is SGA ?
The System Global Area (SGA) is a shared memory region allocated by ORACLE that contains data and control information for one ORACLE instance.SGA is allocated at instance startup, and is a fundamental component of an Oracle Instance.
3. What are the components of SGA ?
The SGA consists of several memory structures:
– Shared Pool
– Database Buffer Cache
– Redo Log Buffer
– Other structures (for example, lock and latch
management, statistical data).
4. What do Database Buffers contain ?
Database buffers store the most recently used blocks of database data. It can also contain modified data that has not yet been permanently written to disk.
What is the difference between VARCHAR, VARCHAR2 and CHAR data types?
Varchar and varchar2 ... the only difference being the naming convention varchar followed by database vendors and varchar2 which is set by ansi and has been incorporated by almost all database vendors.
CHAR .. would add spaces and use the entire size allocated to it by filling the rest with spaces. eg gender CHAR(10) .. gender:='MALE' would store as 'MALE ' -- notice blank spaces
What is the use of DESC in SQL
1. To Describe the table structure.
2. To sort the column in descending order
How does one add a column to the middle of a table?
Anytime u add a new column .. its added after last column, You can only alter the way u want to access a column using SQL query
What is the advantage of specifying WITH GRANT OPTION in the GRANT command?
with the help of GRANT option the grantee can also grant the privileges that they hav got from the granter to the other users
Subscribe to:
Posts (Atom)