FIRST STEPS TOWARDS ORACLE 10G

FIRST STEPS TOWARDS ORACLE 10g

SOME THINGS WHAT WE SHOULD CONSIDER :

v  As per Oracle database is a collection of data in one or more files.

v  The database can contain Physical and Logical structure.

v  The Course of developing an application consists of

Ø  Creating structures (Tables & Indexes)

Ø  Creating Synonyms for the object names.

Ø  View objects in different databases

Ø  Restricting Access to the objects.

v  External Tables can be accessed for files outside the database, as if the rows in the files were rows in the table.

v  An Oracle Instance Comprises of

Ø  A memory area called System Global Area (SGA)

Ø  Background processes which interact between the SGA and the database files on disk.

Ø  The Oracle Read Application Cluster (RAC) can make more than one instance to use the same database.

Database as per Oracle :

v  With in the Oracle database, the basic structure is a table.

v  The different tables supported by Oracle Database are…..

1)  Relational Tables

2)  Object Relational Tables

3)  Index Organized Tables

4)  External Tables

5)  Partitioned Tables

6)  Materialized Views

7)  Temporary Tables

8)  Clustered Tables

9)  Dropped Tables

v  The Access to the data in the tables is tuned by indexes, they are

1)  B+ Tree Indexes

2)  Bitmap Indexes

3)  Reverse Key Indexes

4)  Function Based Indexes

5)  Partitioned Indexes

6)  Text Indexes

ORACLE STYLE FOR DATA STORAGE :

v  All the logical structures in the database must be stored in the database.

v  Oracle maintains a data dictionary, which records METADATA about object.

v  The OBJECTS which need physical storage space, are allocated space within a Tablespace.

Table Spaces :

v  It consist of one or more data files

v  A data file can be a part of one and only one table space

v  As per 10g minimum table space is created are

Ø  SYSTEM and SYSAUX : which are used to support its internal

management needs

v  In 10g a bigfile tablespace can be created , which can grow to the size of tera bytes

v  The Oracle managed files concept can be used to simplify the creation and maintenance of data files

Temporay Tablespace :

v  The actual table space is permanent , but the segments saved in the table space are temporary

v  It is used to support sorting operations such as index creation and joint processing.

v  Tables can be either Dictionary Managed / Logically Managed

v  Oracle maintains a Bitmap in each data file of the table space to trace space availability

AUTOMATED STORAGE MANAGEMENT (ASM) :

v  It automates the layout of datafiles and other operating system level files , used by the database

v  It is done by distributing the files across all available disks.

v  It supports multiplexing features, minimizing the possibility of data data loss

v  As new disks are added , the database files are automatically redistributed access all disks in the defined groups for optimal performance

AUTOMATIC UNDO MANAGEMENT :

v  The transaction are supported by dynamically creating and managing undo segments

v  It helps in maintaining prior images of the changed Blocks and Rows

v  The UNDO segments are managed directly without the intervention of the DBA

v  Flashback version Queries can be executed to see the different versions of a row as it changed during a specified time interval.

DROPPED DATA :

v  The Recycle Bin concept was introduced with Oracle Database 10g

v  This concept impacts the space requirements for the table spaces and datafiles

v  This architecture greatly simplifies recoveries of accidentally dropped tables

v  The concept may considerably increase the space used in the database

v  The PURGE Command is used to remove old entries from the recycle bin

PROGRAMMATICAL STRUCTURES IN ORACLE :

1)  PL/SQL :

v  It is the most critical component of all the application implementations

v  The component can be used to create stored procedures and functions

v  Using this component the stored procedures and functions can be collected into single unit called packages

v  Using the same component the Database trigger can also be designed.

2)  DYNAMIC SQL :

v  Using this concept SQL can be generated at runtime and pass it to procedures which execute it.

3)  SQL * PLUS :

v  It provides simple interface to the Oracle database.

v  It supports Rudimentary Reporting and is the best tool for scripting.

v  It provides consistent interface for

1.  Retrieving data from data dictionary.

2.  Creating database objects.

4)  JAVA & JDBC :

v  It supports JAVA and JDBC and help in using JAVA in the place of PL/SQL.

v  JAVA based stored procedures can be written.

5)  XML :

v  Oracles XML interfaces and XML types can be used to support inserting and retrieving of data via XML.

6)  Object Oriented SQL & PL/SQL :

v  Oracle can be used to create and access object oriented structures, including user defined data types, methods, large objects object tables nested tables.

7)  Data Pump :

v  Data Pump import and Data Pump Export, enhance the manageability and performance.

v  Data Pump can be used to quickly extract data and move it to different databases while altering the schema and changing the rows.

8)  SQL * LOADER :

v  It is used to load flat files quickly into Oracle Tables.

v  A single flat file can be loaded into multiple tables during the same load, and loads can be parallel zed.

9)  External Programs and Procedures :

v  SQL can be embedded with in external programs.

v  Procedural Libraries can be created and can be linked later to Oracle.

10 ) UTL – MAIL :

v  It is a package introduced in Oracle 10g.

v  It allows a PL/SQL application developer to see e-mails without having to know how to use the underlying SMTP Protocal stack.

TYPES OF EDITIONS :

1)  Enterprise Edition :

v  It contains all features and is the rich extensible version of the Oracle databse.

v  It includes features such as Flash Back Database.

v  The licensed functionalities that are supported are :

Ø  Oracle Spatial

Ø  Oracle OLAP

Ø  Oracle Label Security

Ø  Oracle Data Mining

2)  Standard Edition :

v  It provides some subset features of Enterprise Edition.

v  It includes features that are needed by a small business entity.

3)  Personal Edition :

v  It allows for the development of applications which run on either the standard or Enterprise Edition.

v  The Edition is not suitable for Production Environment.

DATABASE MANAGEMENT SYSTEMS :

v  DBMS is a program which takes care of

Ø  Storing of Information.

Ø  Retrieving of Information.

Ø  Modification of Information.

Ø  Deletion of Information.

v  Type of Databases

** Hierarchical ** Network ** Relational

** Object Relational ** Object Oriented

RELATIONAL MODELS :

v  The Model was first outlined by Dr. E. F. Codd in 1970.

v  The components of Relational Model are :

Ø  Collection of objects or relations that store the data.

Ø 

Ø  A set of operations that can act on the relations to produce other relations.

Ø  Data integrity for accuracy and consistency

v  A Relational database uses relations or two dimensional tables to store information.

DATA MODELS :

v  The Data Models help in exploring ideas and improve the understanding of the database design.

PURPOSE OF DATA MODELS :

** Communicate ** Categorize ** Describe ** Specify ** Investigate

** Evolve ** Analyze ** Imitate

OBJECTIVE OF THE MODELS :

v  To produce a model that fits a multitude of uses

v  Should be understood by an end user.

v  Should contain sufficient detail for a developer to build the database system

RELATIONAL DATABASE PROPERTIES :

v  It can be accessed and modified by executing Structured Query Language statements.

v  It contains a collection of tables with no physical pointers.

v  It uses a set of operators.

v  We need not specify the access route to the tables

v  There is no need to identify how the data is arranged physically.

COMMUNICATING WITH RDBMS :

v  The Structured Query Language is used to Communicate with RDBMS.

STRUCTURED QUERY LANGUAGE :

v  It allows the user to communicate as the server

v  It is Efficient

v  It is easy to learn and use

v  It is functionally complete, by allowing the use to define, retrieve and manipulate the data.

ABOUT ORACLE 8 :

v  It is the first object capable database.

v  It provides a new engine which supports

Ø  Object oriented programming

Ø  Complex data types

Ø  Complex business objects

Ø  Full compatibility with the relational concepts.

v  It provides OLTP applications, with sharing of runtime data structures, large buffer caches and deferrable constrains.

v  It supports Client Server and Web based application which are distributed and multitired.

v  It can scale tens and thousands of concurrent users.

ABOUT ORACLE 8i :

v  It is the database of Internet Computing.

v  It provides advanced tools to manage all types of data in Web Sites.

v  The Internet File System ( IFS) combines the power for ease of use a file system.

v  End users can easily access files and folders in ORACLE IFS via a variety of protocols.

v  It enables the users to web-enable their multimedia data

v  It provides full, native integration with Microsoft Transaction Server (MTS).

v  It provides high performance Internet platform for e-commerce and data warehousing.

v  The core pieces of Oracle Internet Platform are :

Ø  Browser based clients to process presentation.

Ø  Application servers to execute business logic and serve presentation logic to browser based clients.

v  Databases to execute database intensive business logic and serve data.

ENTITY RELATIONSHIP MODEL :

v  In an effective system data is divided into discrete categories or entities.

v  An ER – Model is an illustration of various entities in a business and the relationships between them.

v  It is built during the analysis phase of the System Developing Life Cycle.

v  ER – Model separates the information required & the business from the activities performed.

ER – MODEL BENEFITS :

v  It documents information for the organization in a clear, precise format.

v  Provides a clear picture of the scope of the information requirement.

v  Provides an easily understood pictorial map for the database design

v  It offers an effective framework for integrating multiple application.

KEY COMPONENTS IN DIA ER – MODEL :

v  ENTITY : It is a thing of significance about which the information need to be known.

v  ATTRIBUTES : It is something that describes or qualifies an entity.

** Each attribute may be mandatory or optional.

RELATIONAL DATABASE TERMINOLOGY :

v  ROW OR TUPLE :

v  It represents all data required for a particular instance in an entity.

v  Each row is an entity is uniquely identified by declaring it as PRIMARY KEY or UNIQUE .

v  The order of the rows is not significant, while retrieving the data.

v  COLUMN OR ATTRIBUTE :

v  It represents one kind of data in a table

v  The column order is not significant when storing the data.

v  A FIELD :

v  It can be found at the Intersection of a row and a column.

v  A Field can have only one value, or may not have a value at all, the absence of value in Oracle is represented as NULL.

RELATING MULTIPLE TABLES :

v  Each table contains data that describes exactly only one entity.

v  Date about different entities is stored in different tables.

v  RDBMS enables the data in one table to be related to another table by using the foreign keys.

v  A Foreign Key is a column or a set of Column that refer to a Primary Key in the same table or another table.

RATIONAL DATABASE PROPERTIES :

v  Should not specify the access route to the tables, and should not reveal the physical arrange.

v  The Database is accessed using Structured Query Language ( SQL )

v  The language is a collection of set of operators.

STRUCTURED QUERY LANGUAGE STATEMENTS :

v  ORCLE SQL Compiles with industry accepted standards.

v  The different categories into which the SQL statements fall are

Ø  Data Retrieval Statements :

·  SELECT

Ø  Data Manipulation Language Statement ( DML )

·  INSERT UPDATE DELETE

Ø  Data Definition Language Statement (DDL )

·  CREATE ALTER DROP

·  RENAME TRUNCATE

Ø  Transaction Control Language Statement ( TCL )

·  COMMIT ROLLBACK SAVEPOINT

Ø  Data Control Language ( DCL )

·  GRANT REVOKE

ABOUT THE SQL BUFFER :

v  All Commands of SQL are typed at the SQL prompt.

v  Only one SQL Statement is managed in the SQL Buffer.

v  The Current SQL statement replaces the previous SQL statement in the Buffer.

v  The SQL statement can be divided into different lines within the SQL Buffer.

v  Only one line i.e., the current line can be active at a time in the SQL Buffer.

v  At SQL prompt, editing is possible only in the current SQL Buffer line.

v  Every statement of SQL should be terminated Using ;

v  One SQL statement can contains only one.

v  To run the previous or current SQL statement in the Buffer type ‘ / ‘at SQL prompt.

v  To open the SQL Editor type ed at SQL prompt.

CREATING AND MANAGING TABLES :

DATABASE OBJECTS :

v  A Oracle database can contain multiple data structures.

v  The different Database objects in Oracle are :

TABLE : Used to store date, Basic Unit

VIEW : Logically represents subsets of data from one or more tables.

SEQUENCE : Used to Generate Primary Key values.

INDEX : It is used to improve the performance of some queries.

SYNONYM : Used to give alternate names to objects.

Tables in ORACLE 8 / 9 / 10 :

v  Tables can be crated at any time, even when the users are using the database.

v  Size of the table need not be specified.