How do oracle databases work

In addition to a relational database, a data warehouse environment includes an extraction, transportation, transformation, and loading ETL solution, an online analytical proce ssing O LAP engine, client analysis tools, and other applications that manage the process of gathering data and delivering it to business users. You must load your data warehouse regularly so that it can serve its purpose of facilitating business analysis.

To do this, data from one or more operational systems must be extracted and copied into the warehouse. The process of extracting data from source systems and bringing it into the data warehouse is commonly called ETL , which stands for extraction, transformation, and loading. A materialized view provides indirect access to table data by storing the results of a query in a separate schema object.

Unlike an ordinary view, which does not take up any storage space or contain any data, a materialized view contains the rows resulting from a query against one or more base tables or views. A materialized view can be stored in the same database as its base tables or in a different database. Materialized views stored in the same database as their bas e tables can improve query performance through query rewrites.

Query rewrites are particularly useful in a data warehouse environment. Data warehousing environments typically have large amounts of data and ad hoc queries, but a low level of concurrent database manipulation language DML transactions.

For such applications, bitmap indexing provides:. Dramatic performance gains even on hardware with a relatively small number of CPUs or a small amount of memory. Fully indexing a large table with a traditional B-tree index can be prohibitively expensive in terms of space because the indexes can be several times larger than the data in the table.

Bitmap indexes are typically only a fraction of the size of the indexed data in the table. To reduce disk use and memory use specifically, the buffer cache , you can store tables and partitioned tables in a compressed format inside the database. This often leads to a better scaleup for read-only operations. Table compression can also speed up query execution. There is, however, a slight cost in CPU overhead. By dividing the work necessary to run a statement among multiple processes, Oracle can run the statement more quickly than if only a single process ran it.

This is called parallel execution or parallel processing. Parallel execution dramatically reduces response time for data-intensive operations on large databases, because statement processing can be split up among many CPUs on a single Oracle system. Oracle has many SQL operations for performing analytic operations in the database.

These include ranking, moving averages, cumulative sums, ratio-to-reports, and period-over-period comparisons. For additional analytic functionality, Oracle OLAP provides multidimensional calculations, forecasting, modeling, and what-if scenarios. This enables developers to build sophisticated analytic and planning applications such as sales and marketing analysis, enterprise budgeting and financial analysis, and demand planning systems.

Data can be stored in either relational tables or multidimensional objects. Oracle OLAP provides the query performance and calculation capability previously found only in multidimensional databases to Oracle's relational platform. Instead, it is truly an OLAP-enabled relational database. As a result, Oracle provides the benefits of a multidimensional database along with the scalability, accessibility, security, manageability, and high availability of the Oracle database.

With Oracle Data Mining, data never leaves the database — the data, data preparation, model building, and model scoring results all remain in the database. This enables Oracle to provide an infrastructure for application developers to integrate data mining seamlessly with database applications. Some typical examples of the applications that data mining are used in are call centers, ATMs, ERM, and business planning applications.

Data mining functions such as model building, testing, and scoring are provided through a Java API. Partitioning addresses key issues in supporting very large tables and indexes by letting you decompose them into smaller and more manageable pieces called partitions. However, after partitions are defined, DDL statements can access and manipulate individuals partitions rather than entire tables or indexes.

This is how partitioning can simplify the manageability of large database objects. Also, partitioning is entirely transparent to applications. Partitioning is useful for many different types of applications, particularly applications that manage large volumes of data. OLTP systems often benefit from improvements in manageability and availability, while data warehousing systems benefit from performance and manageability.

Oracle includes datatypes to handle all the types of rich Internet content such as relational data, object-relational data, XML, text, audio, video, image, and spatial. These datatypes appear as native types in the database. They can all be queried using SQL. A single SQL statement can include data belonging to any or all of these datatypes. Each consists of a standards-based set of components, tools, and utilities. They provide efficient, random, piece-wise access to the data.

Oracle Text indexes any document or textual content to add fast, accurate retrieval of information. Oracle Text allows text searches to be combined with regular database searches in a single SQL statement. The ability to find documents based on their textual content, metadata, or attributes, makes the Oracle Database the single point of integration for all data management. Oracle Ultra Search lets you index and search Web sites, database tables, files, mailing lists, Oracle Application Server Portals, and user-defined data sources.

As such, you can use Oracle Ultra Search to build different kinds of search applications. Oracle inter Media provides an array of services to develop and deploy traditional, Web, and wireless applications that include image, audio, and video in an integrated fashion. Multimedia content can be stored and managed directly in Oracle, or Oracle can store and index metadata together with external references that enable efficient access to media content stored outside the database.

Oracle includes built-in spatial features that let you store, index, and manage location content assets, buildings, roads, land parcels, sales regions, and so on. The Oracle Spatial Option adds advanced spatial features such as linear reference support and coordinate systems.

Oracle includes security features that control how a database is accessed and used. For example, security mechanisms:.

Associated with each database user is a schema by the same name. By default, each database user creates and has access to all objects in the corresponding schema. Database security can be classified into two categories: system security and data security. System security includes the mechanisms that control the access and use of the database at the system level. For example, system security includes:. System security mechanisms check whether a user is authorized to connect to the database, whether database auditing is active, and which system operations a user can perform.

Data security includes the mechanisms that control the access and use of the database at the schema object level. For example, data security includes:. The Oracle database provides discretionary access control , which is a means of restricting access to information based on privileges.

The appropriate privilege must be assigned to a user in order for that user to access a schema object. Appropriately privileged users can grant other users privileges at their discretion. Authentication to validate the identity of the entities using your networks, databases, and applications. Authorization processes to limit access and actions, limits that are linked to user's identities and roles.

Data must adhere to certain business rules, as determined by the database administrator or application developer. Oracle provides integrity constraints and database triggers to manage data integrity rules. An integrity constraint is a declarative way to define a business rule for a column of a table. An integrity constraint is a statement about table data that is always true and that follows these rules:.

If an integrity constraint is created for a table and some existing table data does not satisfy the constraint, then the constraint cannot be enforced. After a constraint is defined, if any of the results of a DML statement violate the integrity constraint, then the statement is rolled back, and an error is returned. Integrity constraints are defined with a table and are stored as part of the table's definition in the data dictionary, so that all database applications adhere to the same set of rules.

When a rule changes, it only needs be changed once at the database level and not many times for each application. The following integrity constraints are supported by Oracle:. Key is used in the definitions of several types of integrity constraints. A key is the column or set of columns included in the definition of certain types of integrity constraints.

Keys describe the relationships between the different tables and columns of a relational database. Individual values in a key are called key values. A primary key's values uniquely identify the rows in a table. Only one primary key can be defined for each table. Foreign key: The column or set of columns included in the definition of a referential integrity constraint.

Referenced key: The unique key or primary key of the same or a different table referenced by a foreign key. Triggers supplement the standard capabilities of Oracle to provide a highly customized database management system. For example, a trigger can restrict DML operations against a table to those issued during regular business hours.

A distributed environment is a network of disparate systems that seamlessly communicate with each other. Each system in the distributed environment is called a node.

The system to which a user is directly connected is called the local system. Any additional systems accessed by this user are called remote systems. A distributed environment allows applications to access and exchange data from the local and remote systems. All the data can be simultaneously accessed and modified. A homogeneous distributed database system is a network of two or more Oracle databases that reside on one or more machines. Distributed SQL enables applications and users to simultaneously access or modify the data in several databases as easily as they access or modify a single database.

An Oracle distributed database system can be transparent to users, making it appear as though it is a single Oracle database. Companies can use this distributed SQL feature to make all its Oracle databases look like one and thus reduce some of the complexity of the distributed system. Oracle uses database links to enable users on one database to access objects in a remote database.

A local user can access a link to a remote database without having to be a user on the remote database. Location transparency occurs when the physical location of data is transparent to the applications and users. For example, a view that joins table data from several databases provides location transparency because the user of the view does not need to know from where the data originates.

Oracle's provides query, update, and transaction transparency. Oracle ensures the integrity of data in a distributed transaction using the two-phase commit mechanism. Distributed query optimization reduces the amount of data transfer required between sites when a transaction retrieves data from remote tables referenced in a distributed SQL statement.

Oracle Streams enables the propagation and management of data, transactions, and events in a data stream either within a database, or from one database to another. The stream routes published information to subscribed destinations.

As users' needs change, they can simply implement a new capability of Oracle Streams, without sacrificing existing capabilities. Oracle Streams provides a set of elements that lets users control what information is put into a stream, how the stream flows or is routed from node to node, what happens to events in the stream as they flow into each node, and how the stream terminates.

By specifying the configuration of the elements acting on the stream, a user can address specific requirements, such as message queuing or data replication. Oracle Streams implicitly and explicitly captures events and places them in the staging area. Sophisticated subscription rules can determine what events should be captured. The staging area is a queue that provides a service to store and manage captured events. Changes to database tables are formatted as logical change records LCR , and stored in a staging area until subscribers consume them.

Messages in a staging area are consumed by the apply engine, where changes are applied to a database or consumed by an application. A flexible apply engine allows use of a standard or custom apply function.

Support for explicit dequeue lets application developers use Oracle Streams to reliably exchange messages. They can also notify applications of changes to data, by still leveraging the change capture and propagation features of Oracle Streams. It provides a unified framework for processing events. Events generated in applications, in workflow, or implicitly captured from redo logs or database triggers can be captured in a queue. These events can be consumed in a variety of ways.

They can be automatically applied with a user-defined function or database table operation, can be explicitly dequeued, or a notification can be sent to the consuming application. These events can be transformed at any stage. If the consuming application is on a different database, then the events are automatically propagated to the appropriate database.

Operations on these events can be automatically audited, and the history can be retained for the user-specified duration. Replication is the maintenance of database objects in two or more databases. Oracle Streams provides powerful replication features that can be used to keep multiple copies of distributed objects synchronized. Oracle Streams automatically determines what information is relevant and shares that information with those who need it.

This active sharing of information includes capturing and managing events in the database including data changes with DML and propagating those events to other databases and applications. Data changes can be applied directly to the replica database, or can call a user-defined procedure to perform alternative work at the destination database, for example, populate a staging table used to load a data warehouse.

Oracle Streams is an open information sharing solution, supporting heterogeneous replication between Oracle and non-Oracle systems. Using a transparent gateway, DML changes initiated at Oracle databases can be applied to non-Oracle platforms. Oracle Streams is fully inter-operational with materialized views, or snapshots, which can maintain updatable or read-only, point-in-time copies of data.

They can contain a full copy of a table or a defined subset of the rows in the master table that satisfy a value-based selection criterion. There can be multitier materialized views as well, where one materialized view is a subset of another materialized view. Materialized views are periodically updated, or refreshed, from their associated master tables through transactionally consistent batch updates. Oracle can work with non-Oracle data sources, non-Oracle message queuing systems, and non-SQL applications, ensuring interoperabilility with other vendor's products and technologies.

They translate third party SQL dialects, data dictionaries, and datatypes into Oracle formats, thus making the non-Oracle data store appear as a remote Oracle database. These technologies enable companies to seamlessly integrate the different systems and provide a consolidated view of the company as a whole.

Introducing a Transparent Gateway into an Oracle Streams environment enables replication of data from an Oracle database to a non-Oracle database.

Generic Connectivity is a generic solution, while Oracle Transparent Gateways are tailored solutions, specifically coded for the non-Oracle system.

It is a simple, widely understood, unified data model. SQL databases are relational databases, which means that data is stored in a set of simple relations. All operations on the information in an Oracle database are performed using SQL statements. A statement must be the equivalent of a complete SQL sentence, as in:.

Only a complete SQL statement can run successfully. A sentence fragment, such as the following, generates an error indicating that more text is required:. A SQL statement can be thought of as a very simple, but powerful, computer program or instruction.

SQL statements are divided into the following categories:. These statements create, alter, maintain, and drop schema objects. DDL statements also include statements that permit a user to grant other users the privileges to access the database and specific objects within the database. These statements manipulate data. For example, querying, inserting, updating, and deleting rows of a table are all DML operations. These statements manage the changes made by DML statements.

They enable a user to group changes into logical transactions. These statements let a user control the properties of the current session, including enabling and disabling roles and changing language settings. These statements change the properties of the Oracle database instance. It lets users change settings, such as the minimum number of shared servers, kill a session, and perform other tasks.

These statements incorporate DDL, DML, and transaction control statements in a procedural language program, such as those used with the Oracle precompilers. Network traffic between applications and the database is reduced, so application and system performance increases. They are created and stored in compiled form in the database and can be run by a user or a database application. Procedures and functions are identical, except that functions always return a single value to the user.

Procedures do not return values. Packages encapsulate and store related procedures, functions, variables, and other constructs together as a unit in the database.

They offer increased functionality for example, global package variables can be declared and used by any procedure in the package. They also improve performance for example, all objects of the package are parsed, compiled, and loaded into memory once. Java is an object-oriented programming language efficient for application-level programs. Java Stored Procedures are portable and secure in terms of access control, and allow non-Java and legacy applications to transparently invoke Java.

The entire functionality of the database is available in all the languages. All language-specific standards are supported. Developers can choose the languages in which they are most proficient or one that is most suitable for a specific task. It supports the entire Oracle Database feature set. OCI provides powerful functionality to build high performance, secure, scalable, and fault-tolerant applications.

OCI is also used within the server for the data access needs of database kernel components, along with distributed database access. OCI lets an application developer use C function calls to access the Oracle data server and control all phases of business logic execution. OCI is exposed as a library of standard database access and retrieval functions in the form of a dynamic runtime library that can be linked in by the application.

In conjunction with an ODBC driver, an application can access any data source including data stored in spreadsheets, like Excel. Oracle also provides. A transaction is a logical unit of work that comprises one or more SQL statements run by a single user. A transaction ends when it is explicitly committed or rolled back by that user. Transactions let users guarantee consistent changes to data, as long as the SQL statements within a transaction are grouped logically.

A transaction should consist of all of the necessary parts for one logical unit of work—no more and no less. Data in all referenced tables are in a consistent state before the transaction begins and after it ends. Transactions should consist of only the SQL statements that make one consistent change to the data. Consider a banking database. When a bank customer transfers money from a savings account to a checking account, the transaction can consist of three separate operations: decrease the savings account, increase the checking account, and record the transaction in the transaction journal.

The transfer of funds the transaction includes increasing one account one SQL statement , decreasing another account one SQL statement , and recording the transaction in the journal one SQL statement. All actions should either fail or succeed together; the credit should not be committed without the debit. Other nonrelated actions, such as a new deposit to one account, should not be included in the transfer of funds transaction.

Such statements should be in other transactions. Oracle must guarantee that all three SQL statements are performed to maintain the accounts in proper balance. When something prevents one of the statements in the transaction from running such as a hardware failure , then the other statements of the transaction must be undone. This is called rolling back. If an error occurs in making any of the updates, then no updates are made. The changes made by the SQL statements that constitute a transaction can be either committed or rolled back.

After a transaction is committed or rolled back, the next transaction begins with the next SQL statement. To commit a transaction makes permanent the changes resulting from all SQL statements in the transaction.

The changes made by the SQL statements of a transaction become visible to other user sessions' transactions that start only after the transaction is committed.

To undo a transaction retracts any of the changes resulting from the SQL statements in the transaction. After a transaction is rolled back, the affected data is left unchanged, as if the SQL statements in the transaction were never run. Savepoints divide a long transaction with many SQL statements into smaller parts. With savepoints, you can arbitrarily mark your work at any point within a long transaction.

This gives you the option of later rolling back all work performed from the current point in the transaction to a declared savepoint within the transaction. Each column value and constant in a SQL statement has a datatype , which is associated with a specific storage format, constraints, and a valid range of values.

When you create a table, you must specify a datatype for each of its columns. New object types can be created from any built-in database types or any previously created object types, object references, and collection types. An object type differs from native SQL datatypes in that it is user-defined, and it specifies both the underlying persistent data attributes and the related behaviors methods. The approach to store and operate with chunks of LOB data was totally re-engineered, so that large objects in LOB columns are internally manipulated very much similar to files in file systems.

Based on Securefile architecture it's now possible to mount a file system on a Linux platform which would store its files in the Securefile LOB columns of the database. All file operations in that mount point, i. External tables. But what if you have some text files on a database server which you need to read but have no access to the server? In this case external tables might resolve the issue. Even more, starting from 11g it's possible to run a preprocessor directive and execute some shell script, for example unzip a file from an archive.

You can find some examples here. Java stored procedures. If you need additional processing speed or require some specific Java features you can create Java classes directly in the database.

JVM embedded into the database will interpret java code. Your DBA does not allow you to create database links? Not a problem. You can create a Java class in the target database which would open a connection to the source database, select from tables of interest and return required rowset.

Multitable insert. Assume some upstream system populates the staging table in a database, and your interface must read that table and insert data into multiple destinations. Oracle products are scalable and secure, with high performance ability, compared to other databases available in the market.

Its database is also known as simply Oracle also. It is a multi-model relational database management system, mainly designed for enterprise grid computing and data warehousing. It is one of the first choices for enterprises for cost-effective solutions for their applications and data management. It supports SQL as a query language to interact with the database. An Oracle database offers the following features to meet the requirements of powerful database management:.

It is among the oldest companies which provide database management solutions. The company has always focused on Enterprise requirements and acknowledged the latest technology trends. For instance, the latest Oracle database 19C is available on Oracle Cloud as well. Oracle offers users to choose from the different database editions which suit their needs in order to provide a cost-effective solution. We have talked about the features of its database.

These features give Oracle an edge over other competitors. The database must ensure that multiple users can work concurrently without corrupting one another's data. A transaction is a logical, atomic unit of work that contains one or more SQL statements. An RDBMS must be able to group SQL statements so that they are either all committed, which means they are applied to the database, or all rolled back, which means they are undone.

An illustration of the need for transactions is a funds transfer from a savings account to a checking account. The transfer consists of the following separate operations:. Oracle Database guarantees that all three operations succeed or fail as a unit. For example, if a hardware failure prevents a statement in the transaction from executing, then the other statements must be rolled back. Transactions are one feature that set Oracle Database apart from a file system. If you perform an atomic operation that updates several files, and if the system fails halfway through, then the files will not be consistent.

In contrast, a transaction moves an Oracle database from one consistent state to another. The basic principle of a transaction is "all or nothing": an atomic operation succeeds or fails as a whole. A requirement of a multiuser RDBMS is the control of data concurrency , which is the simultaneous access of the same data by multiple users.

Without concurrency controls, users could change data improperly, compromising data integrity. For example, one user could update a row while a different user simultaneously updates it. If multiple users access the same data, then one way of managing concurrency is to make users wait. However, the goal of a DBMS is to reduce wait time so it is either nonexistent or negligible.

All SQL statements that modify data must proceed with as little interference as possible. Destructive interactions, which are interactions that incorrectly update data or alter underlying data structures, must be avoided. Oracle Database uses locks to control concurrent access to data. A lock is a mechanism that prevents destructive interaction between transactions accessing a shared resource.

Locks help ensure data integrity while allowing maximum concurrent access to data. In Oracle Database, each user must see a consistent view of the data, including visible changes made by a user's own transactions and committed transactions of other users. For example, the database must prevent the lost update problem, which occurs when one transaction sees uncommitted changes made by another concurrent transaction.

Oracle Database always enforces statement-level read consistency , which guarantees that the data that a single query returns is committed and consistent for a single point in time.

Depending on the transaction isolation level, this point is the time at which the statement was opened or the time the transaction began. The Flashback Query feature enables you to specify this point in time explicitly.

The database can also provide read consistency to all queries in a transaction, known as transaction-level read consistency. In this case, each statement in a transaction sees data from the same point in time, which is the time at which the transaction began. Data Concurrency and Consistency. In general, a server reliably manages a large amount of data in a multiuser environment so that users can concurrently access the same data.

A database server also prevents unauthorized access and provides efficient solutions for failure recovery. An Oracle database server consists of a database and at least one database instance , commonly referred to as simply an instance. Because an instance and a database are so closely connected, the term Oracle database is sometimes used to refer to both instance and database. A database is a set of files, located on disk, that store data.

These files can exist independently of a database instance. An instance is a set of memory structures that manage database files. The instance consists of a shared memory area, called the system global area SGA , and a set of background processes. An instance can exist independently of database files. Figure shows a database and its instance. For each user connection to the instance, a client process runs the application. Each client process is associated with its own server process.

The server process has its own private session memory, known as the program global area PGA. A database can be considered from both a physical and logical perspective.

Physical data is data viewable at the operating system level. For example, operating system utilities such as the Linux ls and ps can list database files and processes. Logical data such as a table is meaningful only for the database. A SQL statement can list the tables in an Oracle database, but an operating system utility cannot.

The database has physical structures and logical structures. Because the physical and logical structures are separate, you can manage the physical storage of data without affecting access to logical storage structures. For example, renaming a physical database file does not rename the tables whose data is stored in this file.

An essential task of a relational database is data storage. This section briefly describes the physical and logical storage structures that Oracle Database uses.

Every Oracle database has one or more physical data files, which contain all the database data. The data of logical database structures, such as tables and indexes, is physically stored in the data files. Every Oracle database has a control file. A control file contains metadata specifying the physical structure of the database, including the database name and the names and locations of the database files.

Every Oracle Database has an online redo log , which is a set of two or more online redo log files. An online redo log is made up of redo entries also called redo log records , which record all changes made to data.

Many other files are important for the functioning of an Oracle database server. These include parameter files and networking files. Backup files and archived redo log files are offline files important for backup and recovery. Logical storage structures enable Oracle Database to have fine-grained control of disk space use. At the finest level of granularity, Oracle Database data is stored in data blocks.

One data block corresponds to a specific number of bytes on disk. An extent is a specific number of logically contiguous data blocks, obtained in a single allocation, used to store a specific type of information. A segment is a set of extents allocated for a user object for example, a table or index , undo data , or temporary data.

A database is divided into logical storage units called tablespaces. A tablespace is the logical container for a segment. Each tablespace consists of at least one data file. An Oracle database uses memory structures and processes to manage and access the database. When applications connect to an Oracle database, they connect to a database instance.

The instance services applications by allocating other memory areas in addition to the SGA, and starting other processes in addition to background processes.