OCA Oracle Database 11g: Database Administration I: A Real-World Certification Guide: Learn how to become an Oracle-certified Database Administrator

Imagine, for a moment, that you have telephone books for the twenty largest cities in the United States, and I ask you to find all the phone numbers for all individuals named "Rick Clark" in the greater Chicago area. In order to satisfy the request, you simply do the following:

Now imagine that I take each phone book, tear out all of the pages and throw them into the air. I then proceed to shuffle the thousands of pages on the ground into a completely disorganized mess. Now I repeat the same request to find all the phone numbers for individuals named "Rick Clark" in the greater Chicago area. How do you think you would do it? It would be nearly impossible. The data is all there, but is completely disorganized. Finding the individuals named "Rick Clark" of Chicago would involve individually examining each page to see if it satisfied the request—a very frustrating task, to say the least.

This example underscores the importance of an RDBMS. Today's RDBMS is the system that enables the storage, modification, and retrieval of massive amounts of data.

When the devices that we know as computers first came into existence, they were primarily used for one thing—computation. Computers became useful entities because they were able to do numeric computation on an unprecedented scale. For example, one of the first computers, ENIAC, was designed (although not used) for the US Army to calculate artillery trajectories, a task made simpler through the use of complex sequences of mathematical calculations. As such, originally, computers were primarily a tool for mathematical and scientific research. Eventually, the use of computers began to penetrate the business market, where the company's data itself became as important as computational speed. As the importance of this data grew, the need for data storage and management grew as well, and the concept of a database was born.

The earliest databases were simple to envision. Most of them were simply large files that were similar in concept to a spreadsheet or a comma-separated values (CSV) file. Data were stored as fields. A portion of these databases might look something like the following:

Susan, Bates, 123 State St, Somewhere, VA
Fred, Hartman, 234 Banner Rd, Anywhere, CA
Bill, Franklin, 345 Downtown Rd, Somewhere, MO
Emily, Thompson, 456 Uptown Rd, Somewhere, NY

In this example, the first field is determined by reading from left to right until a delimiter, in this case a comma, is reached. This first field refers to the first name of the individual. The next field is determined by reading from the first delimiter to the next. That second field refers to the last name of the individual. It continues in this manner until we have five fields: first name, last name, street address, city, and state. Each individual line or record in the file refers to the information for a distinct individual. Because this data is stored in a file, it is often referred to as a flat file database. To retrieve a certain piece of information, programs could be written that would scan through the records for the requested information. In this way, large amounts of data could be stored and retrieved in an ordered, structured way.

The world of databases changed in the early 1970s due in large part to the work done by Dr. Edgar "Ted" Codd. In his paper A Relational Model of Data for Large Shared Data Banks, Dr. Codd presented a new paradigm— the relational paradigm. The relational paradigm seeks to resolve the limitations of the flat file architecture by organizing our data in such a way that the data and its inter-relationships can be clearly identified. When we design a database, we begin by asking two questions: "What data do I have ?" and "How do the pieces of data relate to each other?" During this process, the data is identified and organized into entities. An entity is any person, place, or thing. An entity also has attributes , or characteristics that pertain to it.

These entities represent distinct pieces of information. We could have an Employee entity that represents information about employees, an Email entity that represents information about e-mail addresses, and so on. These entities, and any others we choose to add, make up our data model. We can also look a little closer at the attributes of a particular entity, as shown in the following diagram:

In our example, data such as First name, Last name, Address, and Branch name are the attributes of the Employee entity—they provide information about the employee. We can extend this idea to any other entities that represent a group of related information.

The true strength of the relational paradigm, however, is its ability to structure these entities in a way that forms relationships between them based on data that is common to both. Following is a simple diagram of this:

Here, we have two entities, Employee and Email, that form a relationship. The Employee entity contains information about the various employees in Companylink. The Email entity contains the e-mail addresses of these employees. Since any given employee can have one or more e-mail addresses, we say that there is a one-to-many relationship between Employee and Email, designated by the crowsfoot symbol between them. This relationship is the heart of the relational model that allows us to separate data into different entities. In the RDBMS world, we call these entities tables, while their attributes are known as columns . We will look much more closely at tables and their characteristics in future chapters. An entire data model can be visually displayed using an Entity Relationship Diagram (ERD). An ERD makes it easier to view the inter-relationships between entities.

Oracle Corporation as we know it, began in 1977 as a company called Software Development Laboratories (SDL), founded by Larry Ellison, Bob Miner, and Ed Oates. These men, inspired by the relational theory set forth by Dr. Ted Codd and the work done on an IBM project known as System R, set out to develop the relational idea into a commercial product. In 1979, the company, renamed Relational Software Inc. (RSI), sold the first version of their relational database software, called Oracle, to Wright-Patterson Air Force Base. Although it was the first version of the Oracle database, it was designated as Version 2 because Larry Ellison didn't believe that customers would buy a Version 1 product. In 1982, RSI was renamed Oracle Corporation to more closely align the company with its flagship database.

For many years, the Oracle database software was primarily available on mainframe and minicomputer systems, the primary computing platforms of that time. In 1983, in a move that would bode well for Oracle's future, Version 3 was rewritten in the C programming language, making it portable to many other platforms. It was ported to the PC architecture with Version 4. The modern history of Oracle Corporation can be traced to the release of Oracle Version 7 in 1992. Version 7 released many new enhancements, including new security and performance features. It is at this point that the Oracle database software began its rise to become the most widely used commercial RDBMS in the world.

Since Oracle's inception, the Oracle Database Server has been and remains Oracle's flagship product. It has maintained its position as the world's most widely used database for many years and stands essentially unchallenged in the market today. The Oracle database is designed to be high performing, highly available, and highly secure. It can run on a variety of hardware and operating system platforms. One of the Oracle Database Server's strengths is its ability to run on a range of system types, from small departmental servers to massive symmetric multiprocessing servers to enormous highly available clustered systems. Many of the largest companies in the world run their database systems on Oracle software. Oracle currently holds a larger revenue share of the worldwide RDBMS market than its five closest competitors combined.

The primary language used to access Oracle databases is Structured Query Language , (SQL). SQL (pronounced either "S-Q-L" or "sequel") is the language most commonly used to address relational databases today, and likely will be for some time to come. The structure and syntax of SQL are governed by the American National Standards Institute (ANSI) and the International Organization for Standardization (ISO). It is these organizations that decide, albeit with input from other companies such as Oracle, what comprises the accepted standard for SQL. The current revision is SQL 2008. Although most commercial RDBMS products generally conform to the ANSI standard, they can differ in their implementations. This means that not every RDBMS uses the exact same SQL syntax. Oracle database also supports the use of two other programming languages within the database: PL/SQL and Java. PL/SQL is Oracle's proprietary third-generation programming language. It allows programmers to write structured code that integrates easily with SQL and stores that code as objects within the database. Java is an industry standard object-oriented language used in many types of applications today. Oracle also supports the storage of Java code within the database.

Although the Oracle Database Server is the cornerstone of their product offerings, Oracle has offered additional types of software products for many years. Recently, however, Oracle has acquired an enormous number of software companies to add to their product family. In 2009, Oracle acquired Sun Microsystems, signaling an important shift in the direction of the company. With the acquisition, Oracle acquired Sun's well-respected line of hardware, as well as the Solaris operating system, the MySQL open source RDBMS, and the Java programming language. Although many in the industry believed that this move would signal the end of Sun's software product line, Oracle has continued to offer, promote, and support many of these products. As a result, Oracle now offers a staggering amount of hardware and software solutions that span every layer of enterprise IT, including hardware, operating systems, databases, application servers, and enterprise administration. An exhaustive discussion of the complete line of Oracle products is beyond the scope of this book; however, we mention some of the most important ones here.

Oracle Enterprise Manager (OEM ) is an interesting and useful tool that has been available since Oracle Database Server Version 8.0. With Version 8i, Oracle moved to a Java-capable browser console that could be run standalone or with a management server. The management server served as a central repository for database information, and would communicate with various agents on the database servers. These agents acted to gather information from all the databases in the enterprise and relayed it to the management server, giving the DBA a centralized single point of administration. Enterprise Manager followed this architecture through Version 9i.

Beginning with Version 10g, Enterprise Manager experienced a graphical and architectural overhaul. OEM 10g featured a completely integrated browser interface that offered complete control of nearly every aspect of database administration, including storage allocation, schema object manipulation, backup and recovery, database parameter configuration, and performance monitoring. OEM 10g and 11g can be used in either of the two configurations, Database Control or Grid Control . Database Control runs on a database server and gives the DBA full administrative control of all aspects of a single database running on that server. Grid Control can be used at the enterprise level in the same way as Database Control can be used at the database level. Grid Control is installed as a centralized server with agents deployed on individual database servers. Like the earlier versions of OEM, these agents communicate with the Grid Control server, allowing the DBA direct control over every administrative aspect of these databases.

Another makeover for OEM comes with the latest version, Oracle Enterprise Manager 12c, Cloud Control. OEM 12c features a cleaner graphical interface and a number of new features, a few of which are listed here:

The following screenshot shows the UI of OEM 11g:

The Tool for Oracle Application Developers (TOAD ) is a full-featured development and administration tool for Oracle as well as other relational database systems, including Microsoft SQL Server, Sybase, and IBM's DB2. It was originally created by Jim McDaniel for his own use; he later released it as freeware for the Oracle community at large. Eventually, Quest Software acquired the rights to TOAD and began distributing a licensed version, while greatly expanding on the original functionality. TOAD is immensely popular among both DBAs and developers, due to its large feature set. For DBAs, it is a complete administration tool, allowing the user to control every major aspect of the database, including storage manipulation, object creation, and security control. For developers, TOAD offers a robust coding interface, including advanced debugging facilities. TOAD is available for download in both freeware and trial licensed versions. A screenshot of the TOAD UI is shown as follows:

DBArtisan (now called DBArtisanXE), by Embarcadero Technologies, is another complete suite of database management tools that operate across multiple platforms. DBArtisan is only available as a licensed product, but has extensive administration capabilities, including the ability to do advanced capacity and performance management, all packaged in an attractive and user-friendly GUI front end. A trial version is available for download from Embarcadero's website.

Oracle SQL Developer , originally called Raptor, is a GUI database interface that takes a somewhat different approach from its competitors. While many of the major licensable GUI administration products have continued to expand their product offerings through more and more add-on components, SQL Developer is a much more dedicated tool. Although it lacks a full set of administration features, it serves well as a streamlined SQL interface to the Oracle database. You can create and manipulate database objects in the GUI interface, as well as write and execute SQL statements from a command line. Administration-oriented activities such as storage control are left to other tools. SQL Developer aims to be a strong SQL and PL/SQL editor with some GUI functionalities. SQL Developer has gained popularity in recent years, in large part owing to several benefits, which are listed as follows:

The UI of SQL Developer is as shown in the following screenshot:

For these reasons, the tools we use in this book for the purposes of demonstration will primarily be SQL Developer and SQL*Plus. From time to time, we'll also look at managing database operations with Enterprise Manager. But before we get ahead of ourselves, let's find out a little about the data we'll be using, and look at the Companylink database.