Oracle Tutorial

Introduction

Oracle is a trademark of Oracle Corporation and in common usage, refers to the database ( a relational database with added object-oriented features) engine and a range of front-end products. Oracle database products deliver innovative technologies and leading price/performance, from the enterprise to small workgroups, from the cloud to mobile devices, from super-clusters to single servers.
Over the past few decades, Oracle grew from being one of many vendors that developed database product to being widely recognized as the database market leader. In every database release, Oracle has improved the scalability, functionality, and manageability of the database.

History:

• 1977: Larry Ellison, Bob Miner, and Ed Oates founded Software Development Laboratories.
• 1978: Oracle Version 1, written in assembly language, runs on PDP-11 under RSX, in 128K of memory.
• 1979: Software Development Laboratories changed its company-name to "Relational Software, Inc." (RSI) and introduced its product Oracle V2 as an early relational database system.
• 1982: RSI in its turn changed its name, becoming known as "Oracle Corporation".
• 1983: The company released Oracle version 3.
• 1984: Oracle Corporation released Oracle version 4, which supported read-consistency.
• 1985: Oracle Corporation released Oracle version 5, which supported the client-server model.
• 1986: Oracle version 5.1 started supporting distributed queries.
• 1988: Oracle RDBMS version 6 came out with support for PL/SQL embedded within Oracle Forms v3, row-level locking, and hot backups.
• 1989: Oracle Corporation entered the application-products market and developed its ERP product, based on the Oracle relational database.
• 1990: the release of Oracle Applications release 8.
• 1992: Oracle version 7 appeared with support for referential integrity, stored procedures and triggers.
• 1997: Oracle Corporation released version 8, which supported object-oriented development and multimedia applications.
• 2001: Oracle9i went into release with 400 new features, including the ability to read and write XML documents. 9i also provided an option for Oracle RAC, or "Real Application Clusters", a computer-cluster database, as a replacement for the Oracle Parallel Server (OPS) option.
• 2002: the release of Oracle 9i Database Release 2
• 2003: Oracle Corporation released Oracle Database 10g, which supported regular expressions.
• 2005: Oracle Database 10.2.0.1 also known as Oracle Database 10g Release 2.
• 2007: Oracle Database 10g release 2 sets a new world record TPC-H 3000 GB benchmark result.
• 2007: Oracle Corporation released Oracle Database 11g for Linux and for Microsoft Windows.
• 2013: Oracle Corporation released Oracle Database 12c for Linux (c stands for 'cloud'), Solaris and Windows.

Oracle Database Editions

Oracle database is available in five editions, each edition is suitable for different development and deployment scenarios. There are also several database options, packs, and other products that enhance the capabilities of Oracle database for specific purposes. Here is the list of Oracle database editions :

• Oracle Database Standard Edition One:
  • Delivers unprecedented ease of use, power, and performance for workgroup, department-level, and Web applications.
  • Includes all the facilities necessary to build business-critical applications from single-server environments for small business to highly distributed branch environments,
• Oracle Database Standard Edition : Delivers the unprecedented ease of use, power, and performance of Standard Edition One, with support for larger machines and clustering of services with Oracle Real Application Clusters (Oracle RAC).
• Oracle Database Enterprise Edition
  • Provides the performance, availability, scalability, and security required for mission-critical applications such as high-volume online transaction processing applications, query-intensive data warehouses, and demanding Internet applications.
  • Contains all of the components of Oracle Database, and can be further enhanced with the purchase of the options and packs
• Oracle Database Express Edition (Oracle Database XE):
  • It is an entry-level edition of Oracle Database that is quick to download, simple to install and manage, and is free to develop, deploy, and distribute.
  • It is easy to upgrade to the other editions of Oracle without costly and complex migrations.
  • It can be installed on any size machine with any number of CPUs, stores up to 11 GB of user data, using up to 1 GB of memory, and using only one CPU on the host machine.
  • Support is provided by an online forum.
• Oracle Database Personal Edition
  • This edition supports single-user development and deployment environments that require full compatibility with Oracle Database Standard Edition One.
  • Available on Windows and Linux platforms only.
  • The Management Packs are not included in Personal Edition.

Note: Oracle Database Standard Edition, and Oracle Database Enterprise Edition. Personal Edition includes all of the components that are included with Enterprise Edition, as well as all of the options that are available with Enterprise Edition, with the exception of the Oracle Real Application Clusters option, which cannot be used with Personal Edition.

Oracle Database:

Provides efficient, reliable, and secure data management for enterprise level, critical transactional applications, query-intensive data warehouses, and mixed workloads. Here are some important information on Oracle databases:

Oracle Database Architecture

An Oracle database is a collection of data treated as a unit. The purpose of a database is to store and retrieve related information. A database server is the key to solving the problems of information management. The database has logical structures and physical structures. Because the physical and logical structures are separate, the physical storage of data can be managed without affecting the access to logical storage structures. Here are some important information on Oracle databases architecture :

• Grid Architecture : The grid style of computing aims to solve some common problems with enterprise IT: the problem of application silos that lead to under utilized, dedicated hardware resources, the problem of monolithic, unwieldy systems that are expensive to maintain and difficult to change, and the problem of fragmented and disintegrated information that cannot be fully exploited by the enterprise as a whole.
• Application Architecture : There are two common ways to architect a database: client/server or multitier. As internet computing becomes more prevalent in computing environments, many database management systems are moving to a multitier environment.
• Physical Database Structures : Every Oracle database has one or more physical datafiles (contain all the database data). The data of logical database structures, such as tables and indexes, is physically stored in the datafiles allocated for a database. The characteristics of datafiles are :
  • A datafile can be associated with only one database.
  • Datafiles can have certain characteristics set to let them automatically extend when the database runs out of space.
  • One or more datafiles form a logical unit of database storage called a tablespace.
• Logical Database Structures : The logical storage structures, including data blocks, extents, and segments, enable Oracle to have fine-grained control of disk space use.
• Schemas and Common Schema Objects : A schema is a collection of database objects and is owned by a database user and has the same name as that user. Schema objects are the logical structures that directly refer to the database's data. It includes structures like tables, views, and indexes.
• Oracle Data Dictionary : Each Oracle database has a data dictionary and the data dictionary is a set of tables and views that are used as a read-only reference about the database. For example, a data dictionary stores information about both the logical and physical structure of the database.
• Oracle Instance : Every time a Oracle database is started, a system global area (SGA) is allocated and Oracle background processes are started. The combination of the background processes and memory buffers is called an Oracle instance.
• Accessing the Database : Oracle Net Services is Oracle's mechanism for interfacing with the communication protocols used by the networks that facilitate distributed processing and distributed databases.
• Oracle Utilities : Oracle provides several utilities for data transfer, data maintenance, and database administration, including Data Pump Export and Import, SQL*Loader, and LogMiner.

Oracle Database Features

• Scalability and Performance
• Manageability Features
• Database Backup and Recovery
• High Availability
• Business Intelligence
• Content Management
• Security Features
• Data Integrity and Triggers
• Information Integration Features

Oracle Database Application Development

SQL and PL/SQL form the core of Oracle's application development stack. Most enterprise and web applications access databases using SQL. Enterprise Applications generate XML from SQL queries, and content-repositories are built on top of SQL tables. It is a simple, widely understood, unified data model and also used in many standalone applications. It is invoked directly from Java (JDBC), Oracle Call Interface (OCI), Oracle C++ Call Interface (OCCI), or XSU (XML SQL Utility). Stored packages, procedures, and triggers can all be written in PL/SQL or in Java.

You will get details from here.

ID ='TUTORIALS' Tutorial objectives

• The tutorial is based on Oracle Database Express Edition 11g Release 2.
• We have used Oracle Database 11g Express Edition to test the example code.
• A short and concise description to help you to understand about the topic / Oracle command.
• The syntax of the Oracle command with descriptions of all the parameters used.
• Display of the actual table on which the Oracle command is being applied.
• Explanation of the code.
• The output of the Oracle command when executed form SQL command prompt.
• You may refer Oracle Database Online Documentation 11g Release 2 along with this tutorial.

We have a comprehensive, SQL Tutorial -2003 standard , which will help you to understand how to prepare queries to fetch data against various conditions.

Note: If you are not habituated with database management system your can learn from here.

Next: Data Types