Oracle (database system)

from Wikipedia, the free encyclopedia
Oracle

logo
Basic data

developer Oracle
Publishing year 1979
Current  version 19c (19.3)
(April 25, 2019)
operating system Linux , Windows , Solaris , HP-UX , AIX
programming language Assembly language , C , C ++ , Java
category Database management system
License proprietary
German speaking Yes
www.oracle.com

Oracle Database (also Oracle Database Server , Oracle RDBMS ) is a database management system software from Oracle .

Both relational data and object-relational data can be stored.

properties

Together with Microsoft SQL Server and IBM Db2, Oracle is one of the market leaders in the RDBMS segment. In the mainframe area, Sun Fire machines with the Unix Solaris system or IBM machines are frequently used platforms. In the midrange area, almost all Unix systems are supported and used, but also OpenVMS . Along with Solaris, Linux was favored for a long time as the main strategic platform and is very popular. Windows is also strategically supported due to its widespread use. According to the DB Engines Ranking, Oracle is the most popular database management system.

The Oracle database management system can be used free of charge as an Express Edition (XE). However, this version is more limited than the corresponding free DB2 version from IBM because it may a. does not support loading Java classes into the database itself ( but JDBC driver access is possible). In addition, there is a limitation of 1  GiB RAM , use of a maximum of one CPU core and a maximum database size of 11 GiB for user data.

The Oracle database is freely available on the manufacturer's website for study purposes. There are no technical barriers such as license servers or license keys . The other products from Oracle are also available there.

With Release 10g, the vision of an Oracle Grid (see also Grid Computing ) was further implemented: The small “g” in the release name also stands for G rid. The core of the Oracle grid is the active / active cluster , which is marketed by Oracle under the name Real Application Cluster (RAC). Oracle has been offering its own cluster manager software under the name Oracle Cluster Ready Services since 10g . This was first released in 9i for Linux, later also for Windows. Since Oracle 10g this is available for every platform supported by Oracle. With the letter extension “c” in version 12c, the focus on cloud products is emphasized.

Since SQL is only a descriptive language, a proprietary procedural extension of SQL called PL / SQL was developed. PL / SQL stands for Procedural Language / Structured Query Language . PL / SQL commands can be entered ad hoc as anonymous blocks or stored in the database in the form of so-called "stored procedures".

It can XML data structures (XMLDB, XDK) are stored. The storage of non-relational data (video, music, documents, fax etc.) is supported with BLOBs ( binary large objects ) and CLOBs ( character large objects ). The indexing of numerous non-relational data formats is already included. The extension of your own programmatic indexes is supported. Spatial data can be stored relationally, spatial indexing and queries are supported ( spatial option ).

The default setting for the isolation level of an Oracle database is "Read committed". H. a query sees all data consistently in the state in which it was committed when the query began. You can also specify the isolation levels “Serialize” or “Read only” (no SQL standard) for a session or a transaction. The other two isolation levels defined in the SQL standard ("Read uncommitted", "Repeatable read") are not explicitly supported. By storing rollback information, read access never leads to blocking of writing access (non-blocking reads) and vice versa (non-blocking writes).

List of other properties

  • Implementation of the ACID properties
  • Cross-platform support for distributed databases
  • Data warehouse functionality
  • Messaging including JMS
  • OLAP and data mining
  • intelligent data backup (optionally with block or row change tracking)
  • Java (you can run Java within the database)
  • Stored procedures in PL / SQL or Java (in the XE version without Java)
  • Regular expression support in queries
  • Versioning of tables ( long-term transactions )
  • " Virtual Private Database " functionality
  • Protection against
    • Instance failure (e.g. power failure) through the option of an active / active cluster with failover functionality ( Oracle RAC ),
    • Media Failure (hard disk failure) through optional standby database and Oracle Dataguard as well
    • User errors by means of flashback (on record level, table level or also across the database)
  • Data warehouse features are e.g. B. Data types for storing large amounts of data ( CLOB , BLOB ), partitioning (dividing the data into sub-tables, e.g. by date), transportable tablespaces , big tablespaces (data files with a maximum of 128 PiB), bitmap indexes for OLAP queries, star transformation , Access to distributed databases, standby database
  • Large selection of built-in SQL functions and analytical functions, both of which can be expanded as required using user-definable functions
  • Cost-based optimizer that automatically creates execution plans at runtime according to the specified target throughput or response time
  • Comprehensive security concept for the administration and transfer of rights to all database objects, assignment of roles and policies
  • Line-by- line access control (FGAC) as a supplement to the existing column-by-line access control
  • Auditing (logging of all accesses or in parts)
  • Resource Manager for dynamic distribution of resources (memory, CPU time, I / O) to profiles
  • Sophisticated tuning options, from version 10g supplemented by so-called "Advisors" (database can tune itself or provide information, keyword "ADDM")
  • Performance features such as index-organized tables (tables are saved as an index, basic table is not required)
  • Proprietary built-in programming language PL / SQL interwoven with SQL (native compilation possible, native data types)
  • From version 8i, additional interlocking with Java in the Oracle Kernel (loading of Java into the database)
  • Enhancements such as Oracle Spatial and graph showing the storage and analysis of geospatial data allows
  • Integrated full text search (Oracle Text, in earlier versions intermedia Text)
  • Connection to Big Data and Hadoop with connectors

Deviations from the ANSI SQL standard

Like most database systems, the database system does not fully comply with the currently valid SQL standard - especially because the first versions of Oracle were programmed before SQL was first established by ANSI . But ANSI standards were successively met. In addition to the proprietary syntax, which is still available, the ANSI join syntax is now supported.

When porting other database systems to the Oracle database, the following deviations from the SQL standard are important:

  • The date data type also contains the time.
  • The data type boolean does not exist.
  • The numeric data type number has several numeric sub-data types: dec , decimal , numeric , double precision , float , int , integer , smallint and real .
  • Empty strings are not distinguished from the null value .
  • In UNIONs, the value NULL is only compatible with character strings instead of all data types (must be converted).
  • The data type for storing strings of different lengths is called "VARCHAR2" in Oracle and is limited to 4000 bytes. As of Oracle 12, 32767 bytes are also possible. Specifying the ANSI data type VARCHAR is also supported, but the column is still created internally as VARCHAR2. In earlier versions, table columns of the type VARCHAR could only store a maximum of 2000 characters (up to version 7) and a sequence of spaces was interpreted as NULL (up to version 6).

architecture

An Oracle database system that can be used by clients consists of:

  • one or more listener processes (Oracle listener)
  • one or more database instances (Oracle instance), the actual database management system (DBMS)
  • a lot of database files (Oracle database), the actual database (DB)

A number of database files can be opened by several database instances at the same time; this is known as Oracle RAC .
A database instance can only have a number of database files open at a time.

The system can be characterized as follows:

Listener process (Oracle listener)

Accepts connection requests from database clients and connects them to a database instance. Starts Oracle server processes for database clients.

Database instance (Oracle instance)

This is where resources are allocated for CPU and RAM. An instance consists of several Oracle server processes (foreground and background processes), which provide the common working memory in the form of shared memory. The foreground processes receive database queries (Query) or data modification instructions (DML) in the SQL language from the database clients, carry out these tasks and return result data. The Oracle server processes sometimes act directly on the database files, but sometimes they also transfer activities to the background processes of the database instance.
The most important background processes are:

  • The Database Writer (DBWR), which writes changes to the data blocks in the data files.
  • The log writer (LGWR) that writes redo information to the redo log files.
  • The archiver (ARCH), which archives redo log files, provided the database is operated in the so-called archive log mode.
  • The System Monitor (SMON), which writes consistency information in the control files as well as in the headers of data files and in redo log files. When a database is restarted after a crash, the system monitor checks this consistency information in a cross-check of all control files, data files and redo log files. If the SMON detects inconsistencies, the system monitor initiates a crash recovery in which missing transactions are transferred from the redo log files to the data files until the database is internally consistent with all data files .
  • The Process Monitor (PMON), which monitors the Oracle processes.

The Oracle System Identifier (short form: SID ) is the identifier for the instance of Oracle that runs on the server . This ID is required if a connection is to be established to a server that supports more than one instance of an Oracle database. The SID is saved in network / admin / tnsnames.ora under the Oracle installation directory.

The database instance also needs some files for control and logging, these are differentiated according to:

Parameter files
Storage location of the control files. Initial specification of main memory and other attributes.
Trace files
Files for diagnosis, mostly in the event of performance problems.
Alert files
Files specifically for error messages.

Amount of database files (Oracle database)

The data is saved here. This is mostly done in files in a file system . However, raw devices or disk groups managed by ASM ( Oracle Automatic Storage Management Cluster File System ) are also used.
A distinction is made between the types of files:

Data files
the actual data files with the data content.
Redo log files
Very quickly writable files that serve as transaction logs and that record the data block changes (change vectors) of transactions. These saved change vectors are used to restore data blocks if the database management system has to be terminated unplanned or intentionally. Changes that have not yet been transferred to the data files can be reconstructed and followed up (roll forward). Subsequently, all changes made after the last successful commit process (check point) are written back ( roll back ).
Control files
Files that u. a. contain the structure and status information of the database. This includes the system change number (SCN) as well as the paths and names of all data files and redo log files.

Tools

Tools for development and database administration are provided by Oracle as well as by other manufacturers.

  • Tools from the manufacturer Oracle:
    • SQL * Plus is a command line-oriented administration tool for the administration and operation of Oracle databases. SQL * Plus is available on every computer system on which the Oracle client or server software is installed. This allows all administrative activities as well as the input, modification, query and deletion of the actual data content to be carried out. SQL * Plus is called - regardless of the operating system platform used - by entering sqlplus . SQL * Plus was available with the first Oracle versions. Due to the orientation towards command lines, extensive knowledge of SQL is required. With iSQL * Plus, Oracle also provides a web front end for SQL * Plus.
    • Oracle SQL Developer (Project Raptor) : Oracle's free tool for database developers runs as a Java program with a graphical user interface and enables the editing of database objects, creation and testing of SQL statements and scripts, creation and debugging of PL / SQL procedures and simple Database analysis. Oracle SQL Developer can also work on other databases (e.g. Microsoft Access , MySQL , DB2 ).
    • Oracle Enterprise Manager (Java Console): Graphical user interface for database management based on Java (development discontinued).
    • Oracle Enterprise Manager Database Control: Web-based, graphical user interface for managing a database
    • Oracle Enterprise Manager Grid Control: Web-based, graphical user interface for managing an Oracle environment including several databases, database clusters, standby systems
    • Data Guard Control : Command line-oriented tool for managing standby databases
    • Server Control: Command line-oriented tool for managing databases, services and applications in a cluster
    • Oracle JDeveloper : Integrated development environment for developing database applications with Oracle and Java
  • Tools from other manufacturers:
    • TOAD : Graphical tool for administration and development with Oracle databases from the company Quest
    • TOra: Graphical tool for administration and development with Oracle and PostgreSQL databases, meanwhile a SourceForge project, i.e. open source
    • SQL Navigator : Graphical tool for administration and development with Oracle databases from the company Quest
    • PL / SQL Developer: Graphical tool for administration and development with Oracle databases from the company Allround Automations
    • Omega9: Tool for the administration and development of PL / SQL code with Oracle databases
    • SQL Workbench / J: The free DBMS-independent SQL Tool
    • QueryAdvisor : The tool for advanced GUI based tracefile and wait interface analysis. Free and commercial licenses are available.
    • DBShadow: Standard solution with a graphical user interface for creating, monitoring and managing standby databases.
    • Aqua Data Studio : Graphic tool for the administration and development of databases from the company AQUAFOLD
    • SQuirreL SQL Client : Graphical tool under the open source license LGPL for the administration and development of many databases

Licensing

For a long time, Oracle licensed all of its products either according to the number of processor cores (not according to the number of physical CPU sockets, as for example Microsoft ) or according to the number of so-called named users, whereby the number of possible users is decisive is, not the number of actual users. This is also retained in the Enterprise Edition. With Intel systems you need a license for 2 cores, with Sparc a license for 4 cores.

For some time now, however, licensing has also been possible for the Standard Edition and Standard Edition One, depending on the number of CPU sockets. Changes to the licensing of the Standard Edition from 12c in connection with Oracle RAC as well as Oracle's license policy in the field of virtualized environments led to discussions and criticism from numerous users.

The Express Edition can be used free of charge and can also be passed on to third parties as a database for own program developments without having to purchase a license.

history

The beginnings of the Oracle database go back to the research work of Edgar F. Codd , who developed the database prototype System R on behalf of IBM in the 1970s . This study inspired Larry Ellison to further develop the results and to develop his own database called Oracle. The database was launched as version 2 right from the start because it was feared that version 1 would sell worse.

Version 2

In 1979 the database was first offered on the market as Oracle V2. It provided basic functions for executing queries and joins without transactions.

Version 3

Release 3 was offered in 1983 with the essential enhancement that transactions could now be controlled using COMMIT and ROLLBACK.

Version 4

In 1984 version 4 was released with read consistency. This means that reading transactions always have free and consistent access to the data, even if they are currently being changed by another transaction. In such a case, the reading transaction receives the previously valid version of the data, which is saved in rollback segments. The process is now called Multiversion Concurrency Control .

Version 5

In 1985 version 5 was released as a database in a client-server architecture, which has been retained until today.

Version 6

In 1988 version 6 was released with further additional functions:

  • PL / SQL
  • Oracle Forms v3
  • row-level locking (if a data record is changed, the entire data block (4 kB) does not have to be locked as in earlier versions, but only the one data record)

Version 7

Version 7 was released in 1992 with these new features:

  • Tables can be linked with foreign key relationships
  • Stored Procedures
  • Trigger
  • The LONG data type is newly introduced for storing data up to 2 GiB .
  • Package UTL_FILE for data input and output from the Oracle server to sequential files.

Version 8 and version 8i

Version 8 was released in 1997. In 1999 version 8i (numerically 8.1.x) was released.

They included these new features:

  • Cost-based query optimizer . The rule-based query optimizer used so far can also be used further.
  • Ability to save object-oriented data
  • The server software includes a native Java Virtual Machine (Oracle JVM).

Administration:

  • Drop column. In previous versions created table columns could not be deleted.
  • Stored outlines. This can be used to define the access path for an SQL statement.
  • Introduction of transportable tablespaces, however, initially with a number of restrictions
  • Unicode support ( UTF-8 )
  • Data types BLOB , CLOB , NCLOB
  • Function-based index

SQL:

  • Expansion of the grouping options through ROLLUP and CUBE

PL / SQL:

  • Execute Immediate as a PL / SQL command for executing dynamic SQL statements in a program.
  • Bulk collect for reading several records via a cursor into a collection with only one statement (only one context change required)
  • FORALL clause for inserting / changing / deleting several records that are stored in a collection.
  • Package UTL_SMTP for sending emails

Version 9i

In 2001 version 9i was released with these new features:

Administration:

  • online reorganization of tables (indexes already worked in version 8)
  • several block sizes possible for one database
  • Rollback segments are managed automatically
  • Partition splitting
  • List partitioning: Partitioning based on explicitly named values
  • The flashback concept is introduced for the recovery of data on session or statement level.
  • Use of the SPFILE (ServerParameterFile) to accept binary stored DB parameters

SQL:

  • Case statement ( ANSI compatibility)
  • Data types for time periods (e.g. interval year to month)
  • MERGE statement, a combination of INSERT and UPDATE
  • Multi-table insert: filling several tables in one processing step
  • Bitmap join index (a bitmap index that references multiple tables that are often joined)
  • Full outer joins are supported
  • With clause for the definition of "inline views" which are only valid for the formulation of a query and do not have to be stored permanently in the catalog
  • more functions for processing XML data

Tools:

  • iSQL * Plus: A browser for running queries and PL / SQL scripts over the Internet.

Version 10g

In 2003 version 10g was released with these new features:

Administration:

  • Recycle-Bin: Recycle bin function for storing tables that have been deleted (with DROP TABLE)
  • ASM (Automatic Storage Management)

SQL:

Tools:

  • Data pump for quick import / export
  • OWB (Oracle Warehouse Builder)
  • SQL Profile, an option that analyzes certain critical SQL statements that are frequently used in detail and stores an optimal access plan in the catalog.
  • ADDM Automatic Database Diagnosis Monitor: A function that creates performance reports and draws attention to critical points.
  • SQL Tuning Advisor: Support for the automatic generation of statistics and analysis of SQL statements
  • Package UTL_MAIL for easy sending of e-mails from a PL / SQL procedure. This will replace the previously available UTL_SMTP package.

Version 11g

In 2007 version 11g Release 1 was released with these new functions (selection):

  • Client-side query cache
  • Unicode 5.0 support
  • SecureFiles (completely new development of LOB storage)
  • Virtual table columns
  • Invisible indexes
  • Real application testing

In 2009 version 11g Release 2 was released with these new functions (selection):

  • Intelligent data placement with Automatic Storage Management (ASM)
  • Compressing data
  • New database option "Oracle RAC One Node" (= one-node version of Real Application Clusters)

Version 12c

In 2013 version 12c Release 1 was released with these new functions (selection):

  • The data type VARCHAR2 supports up to 32767 bytes
  • Unicode 6.1 support
  • Invisible columns
  • Select with line offset and limitation (OFFSET, FETCH NEXT ..)
  • Multitenant option, up to 252 "pluggable" databases possible within a container database
  • Like a VM, the container database organizes the RAM and CPU for the "pluggable" databases
  • Automatic data optimization (ADO) with heat maps for automatic compression of data ( information lifecycle management )
  • Use of JSON data (JavaScript Object Notation) in the database
  • In-memory database

Oracle Database 12c Release 2 was released in the Oracle Cloud in December 2016. In March 2017, 12c Release 2 was also released for installations in your own data center or on your own hosts.

Version 12c Release 2 contains these new functions (selection):

  • An Oracle database can now be used as an NFS (Network File System) server
  • Automatic data optimization (ADO) now supports Hybrid Columnar Compression (HCC)
  • Extended index compression, increased compression of indexes is now possible
  • Sharding: horizontally partitioned databases
  • Approximate query processing: faster evaluations of large amounts of data
  • Performance & Optimizer: Improvements in adaptive queries and joins, cursor management
  • In-memory option: improvements in administration and availability
  • Security: New features in password management and encryption, auditing improvements
  • Designation of objects: the maximum length of the identifiers has been increased to 128 bytes for most identifiers, in previous releases it was 30 bytes

Version 18c

Version 18c was released in July 2018. In this version, new features in the areas of security, performance, data warehousing , multitenant and RMAN have been integrated.

Version 19c

In the meantime version 19c has been published.

criticism

The license policy of the manufacturer is often criticized by users of the Oracle database.

Trivia

After the company was founded in 1977, Bruce Scott was one of the first employees; his daughter had a cat named Tiger. This resulted in the user name “scott” with password “tiger” for a demo user that is still popular today.

See also

literature

Web links

Further content in the
sister projects of Wikipedia:

Commons-logo.svg Commons - multimedia content
Wikibooks-logo.svg Wikibooks - Textbooks and non-fiction books

Individual evidence

  1. ↑ Oracle Database 19c Now Available on Linux
  2. ^ The Programming Languages ​​Beacon, v11.1 . Retrieved October 15, 2013.
  3. ↑ DB Engines Ranking
  4. ↑ Oracle Unveils Oracle Enterprise Manager 12c
  5. ↑ Oracle Virtual Private Database by Heinz-Wilhelm Fabry, ORACLE Deutschland GmbH
  6. ↑ Oracle Documentation. Retrieved April 28, 2016 .
  7. ↑ Oracle Docs - Table 3-6 Predefined Subtypes of NUMBER Data Type
  8. ↑ Computer Week . Retrieved March 27, 2017 .
  9. ↑ Oracle Database New Features Guide 10g Release 2 (10.2)
  10. ↑ Oracle Database New Features Guide 11.1
  11. ↑ Oracle Database New Features Guide 11.2
  12. ↑ Oracle Database 12c Release 1 (12.1) New Features
  13. ^ Tom Kyte's Top 12 New Features of the Oracle Database 12c
  14. ↑ Oracle Wiki: Oracle 12c
  15. ↑ Oracle Database 12c Release 1 (12.1.0.2) New Features
  16. ↑ Oracle July 22, 2014: Upgrade your Database - NOW!
  17. ↑ Oracle Metalink document "Release Schedule of Current Database Releases" (Doc ID 742060.1)
  18. ↑ Oracle Database 12c Release 2 (12.2) New Features
  19. ↑ Oracle Database 12c Release 2 Available Everywhere!
  20. ↑ Introducing Oracle Database 18c , PDF
  21. ↑ Oracle Database 18c - What's New
  22. ↑ Oracle Database 19c - What's New
  23. ↑ Martin Bayer: License models under fire: dispute between Oracle and its customers intensifies. In: computerwoche.de. July 6, 2017, accessed December 4, 2018 .
  24. ^ Oracle Wiki: Bruce Scott. In: orafaq.com. August 25, 2008, accessed December 4, 2018 .