Hướng dẫn sử dụng oracle 11g Informational
An information system could be a set of cardboard boxes containing manila folders along with rules for how to store and retrieve the folders. However, most companies today use a to automate their information systems. A database is an organized collection of information treated as a unit. The purpose of a database is to collect, store, and retrieve related information for use by database applications. Show
Database Management System (DBMS)A database management system (DBMS) is software that controls the storage, organization, and retrieval of data. Typically, a DBMS has the following elements:
A database application is a software program that interacts with a database to access and manipulate data. The first generation of database management systems included the following types:
The preceding database management systems stored data in rigid, predetermined relationships. Because no data definition language existed, changing the structure of the data was difficult. Also, these systems lacked a simple query language, which hindered application development. Relational ModelIn his seminal 1970 paper "A Relational Model of Data for Large Shared Data Banks," E. F. Codd defined a relational model based on mathematical set theory. Today, the most widely accepted database model is the relational model. A relational database is a database that conforms to the relational model. The relational model has the following major aspects:
A relational database stores data in a set of simple relations. A relation is a set of tuples. A tuple is an unordered set of attribute values. A table is a two-dimensional representation of a relation in the form of rows (tuples) and columns (attributes). Each row in a table has the same set of columns. A relational database is a database that stores data in relations (tables). For example, a relational database could store information about company employees in an employee table, a department table, and a salary table. Relational Database Management System (RDBMS)The relational model is the basis for a relational database management system (RDBMS). Essentially, an RDBMS moves data into a database, stores the data, and retrieves it so that it can be manipulated by applications. An RDBMS distinguishes between the following types of operations:
Oracle Database is an RDBMS. An RDBMS that implements object-oriented features such as user-defined types, inheritance, and polymorphism is called an object-relational database management system (ORDBMS). Oracle Database has extended the relational model to an object-relational model, making it possible to store complex business models in a relational database. Brief History of Oracle DatabaseThe current version of Oracle Database is the result of over 30 years of innovative development. Highlights in the evolution of Oracle Database include the following:
Schema ObjectsOne characteristic of an RDBMS is the independence of physical data storage from logical data structures. In Oracle Database, a database is a collection of logical data structures, or . A database schema is owned by a database user and has the same name as the . Schema objects are user-created structures that directly refer to the data in the database. The database supports many types of schema objects, the most important of which are tables and indexes. TablesA table describes an entity such as employees. You define a table with a table name, such as A table is a set of rows. A column identifies an attribute of the entity described by the table, whereas a identifies an instance of the entity. For example, attributes of the employees entity correspond to columns for employee ID and last name. A row identifies a specific employee. You can optionally specify rules for each column of a table. These rules are called . One example is a IndexesAn is an optional data structure that you can create on one or more columns of a table. Indexes can increase the performance of data retrieval. When processing a request, the database can use available indexes to locate the requested rows efficiently. Indexes are useful when applications often query a specific row or range of rows. Indexes are logically and physically independent of the data. Thus, you can drop and create indexes with no effect on the tables or other indexes. All applications continue to function after you drop an index. Data AccessA general requirement for a DBMS is to adhere to accepted industry standards for a data access language. Structured Query Language (SQL)SQL is a set-based declarative language that provides an interface to an RDBMS such as Oracle Database. In contrast to procedural languages such as C, which describe how things should be done, SQL is nonprocedural and describes what should be done. Users specify the result that they want (for example, the names of current employees), not how to derive it. SQL is the ANSI standard language for relational databases. All operations on the data in an Oracle database are performed using SQL statements. For example, you use SQL to create tables and query and modify data in tables. A SQL statement can be thought of as a very simple, but powerful, computer program or instruction. A SQL statement is a string of SQL text such as the following: SELECT first_name, last_name FROM employees; SQL statements enable you to perform the following tasks:
SQL unifies the preceding tasks in one consistent language. Oracle SQL is an implementation of the ANSI standard. Oracle SQL supports numerous features that extend beyond standard SQL. PL/SQL and JavaPL/SQL is a procedural extension to Oracle SQL. PL/SQL is integrated with Oracle Database, enabling you to use all of the Oracle Database SQL statements, functions, and data types. You can use PL/SQL to control the flow of a SQL program, use variables, and write error-handling procedures. A primary benefit of PL/SQL is the ability to store application logic in the database itself. A or is a schema object that consists of a set of SQL statements and other PL/SQL constructs, grouped together, stored in the database, and run as a unit to solve a specific problem or to perform a set of related tasks. The principal benefit of server-side programming is that built-in functionality can be deployed anywhere. Oracle Database can also store program units written in Java. A Java stored procedure is a Java method published to SQL and stored in the database for general use. You can call existing PL/SQL programs from Java and Java programs from PL/SQL. Transaction ManagementOracle Database is designed as a multiuser database. The database must ensure that multiple users can work concurrently without corrupting one another's data. TransactionsAn 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. A is a logical, atomic unit of work that contains one or more SQL statements. 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 of the features that sets 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. Data ConcurrencyA requirement of a multiuser RDBMS is the control of , which is the simultaneous access of the same data by multiple users. Without concurrency controls, users could change data improperly, compromising . 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 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. Data ConsistencyIn 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 dirty reads, which occur when one transaction sees uncommitted changes made by another concurrent transaction. Oracle Database always enforces statement-level read consistency, which guarantees that the data returned by a single query is committed and consistent with respect to 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. See Also:
Oracle Database ArchitectureA database server is the key to information management. In general, a 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. Database and InstanceAn Oracle database server consists of a and at least one database (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. In the strictest sense the terms have the following meanings:
shows a database and its instance. For each user connection to the instance, the application is run by a . Each client process is associated with its own server process. The server process has its own private session memory, known as the . 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 The database has physical structures and logical structures. Because the physical and logical structures are separate, the physical storage of data can be managed 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. Database Storage StructuresAn essential task of a relational database is data storage. This section briefly describes the physical and logical storage structures used by Oracle Database. Physical Storage StructuresThe physical database structures are the files that store the data. When you execute the SQL command
Many other files are important for the functioning of an Oracle database server. These files include parameter files and diagnostic files. Backup files and are offline files important for backup and recovery. Logical Storage StructuresThis section discusses logical storage structures. The following logical storage structures enable Oracle Database to have fine-grained control of disk space use:
Database Instance StructuresAn Oracle database uses memory structures and processes to manage and access the database. All memory structures exist in the main memory of the computers that constitute the RDBMS. When applications connect to an Oracle database, they are connected 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. Oracle Database ProcessesA is a mechanism in an operating system that can run a series of steps. Some operating systems use the terms job, task, or thread. For the purpose of this discussion, a thread is equivalent to a process. An Oracle database instance has the following types of processes:
include server processes and background processes. In most environments, Oracle processes and client processes run on separate computers. Instance Memory StructuresOracle Database creates and uses memory structures for purposes such as memory for program code, data shared among users, and private data areas for each connected user. The following memory structures are associated with an instance:
Application and Networking ArchitectureTo take full advantage of a given computer system or network, Oracle Database enables processing to be split between the database server and the client programs. The computer running the RDBMS handles the database server responsibilities while the computers running the applications handle the interpretation and display of data. Application ArchitectureThe application architecture refers to the computing environment in which a database application connects to an Oracle database. The two most common database architectures are client/server and multitier. In a , the client application initiates a request for an operation to be performed on the database server. The server runs Oracle Database software and handles the functions required for concurrent, shared data access. The server receives and processes requests that originate from clients. In a traditional multitier architecture, one or more application servers perform parts of the operation. An application server contains a large part of the application logic, provides access to the data for the client, and performs some query processing, thus lessening the load on the database. The application server can serve as an interface between clients and multiple databases and provide an additional level of security. Service-oriented architecture (SOA) is a multitier architecture in which application functionality is encapsulated in services. SOA services are usually implemented as Web services. Web services are accessible through HTTP and are based on XML-based standards such as Web Services Description Language (WSDL) and SOAP. Oracle Database can act as a Web service provider in a traditional multitier or SOA environment. See Also:
Networking ArchitectureOracle Net Services is the interface between the database and the network communication protocols that facilitate and distributed databases. Communication protocols define the way that data is transmitted and received on a network. Oracle Net Services supports communications on all major network protocols, including TCP/IP, HTTP, FTP, and WebDAV. Oracle Net, a component of Oracle Net Services, establishes and maintains a network session from a client application to a database server. After a network session is established, Oracle Net acts as the data courier for both the client application and the database server, exchanging messages between them. Oracle Net can perform these jobs because it is located on each computer in the network. An important component of Net Services is the Oracle Net Listener (called the listener), which is a separate process that runs on the database server or elsewhere in the network. Client applications can send connection requests to the listener, which manages the traffic of these requests to the database server. When a connection is established, the client and database communicate directly. The most common ways to configure an Oracle database to service client requests are:
See Also:
Oracle Database Documentation RoadmapThis section explains how this manual should be read and where it fits into the Oracle Database documentation set as a whole. To a new user, the Oracle Database documentation library can seem daunting. Not only are there are over 175 manuals, but many of these manuals are several hundred pages long. However, the documentation is designed with specific access paths to ensure that users are able to find the information they need as efficiently as possible. The documentation set is divided into three layers or groups: basic, intermediate, and advanced. Users begin with the manuals in the basic group (Oracle Database 2 Day DBA, Oracle Database 2 Day Developer's Guide, or this manual), proceed to the manuals in the intermediate group (the 2 Day + series), and finally to the advanced manuals, which include the remainder of the documentation set. Basic GroupTechnical users who are new to Oracle Database begin by reading one or more manuals in the basic group from cover to cover. Each manual in this group is designed to be read in two days. In addition to this manual, the basic group includes:
The manuals in the basic group are closely related, which is reflected in the number of cross-references. For example, Oracle Database Concepts frequently sends users to a 2 Day manual to learn how to perform a task based on a concept. The 2 Day manuals frequently references Oracle Database Concepts for conceptual background about a task. Intermediate GroupThe next step up from the basic group is the intermediate group. The manuals in this group are prefixed with the word 2 Day + because they expand on and assume information contained in the 2 Day manuals. These manuals cover topics in more depth than was possible in the basic manuals, or cover topics of special interest. As shown in , the 2 Day + manuals are divided into manuals for DBAs and developers. Advanced GroupThe next step up from the intermediate group is the advanced group. These manuals are intended for expert users who require more detailed information about a particular topic than can be provided by the 2 Day + manuals. Essential reference manuals in the advanced group include:
The advanced guides are too numerous to list in this section. lists guides that are used by the majority of expert DBAs and developers at one time or another. |