From Databases to Datatypes
(Page 1 of 2 )
Handling database objects is an important aspect of using and administering Oracle Database XE. This seven-part article series covers what you need to know, starting with tablespaces and continuing through the basic Oracle datatypes, after which you'll learn how to create tables with these datatypes. This article excerpted from chapter 30 of the book Beginning PHP and Oracle: From Novice to Professional, written by W. Jason Gilmore and Bob Bryla (Apress; ISBN: 1590597702).
In Chapter 27, we gave you a whirlwind tour of Oracle Database XE administration, describing the logical and physical storage structures of a database, including tablespaces and the other structures that comprise a database.
In this chapter, we approach database objects from both a user’s and a developer’s point of view, starting with an overview of how to create and manage tablespaces. Inevitably, you’ll grow out of the five default tablespaces included with an Oracle Database XE installation. The type of tablespace you create is influenced by the type of data you store in it, such as undo tablespaces, temporary tablespaces, and permanent tablespaces.
Next, we give you a rundown of all basic Oracle datatypes. For the most part we cover the key datatypes that fall into three broad categories: numeric, character, and date. In addition, we present many of the object-oriented, binary, and even XML types. To expand on the multilingual support in Oracle Database XE, we explain how Oracle supports Unicode in the database.
Finally, once you know the datatypes available for a column, we show you how to create several different types of tables using these datatypes. To improve the performance of table access, you’ll also need to know about table indexes and when you need to create them.
Knowing how to create the right types of tables with appropriate column types, indexes, and other characteristics ensures that your application uses database storage efficiently while at the same time making your application perform adequately for your user base.Creating and Managing Tablespaces
As you learned in Chapter 27, a tablespace is the highest-level logical object in the database. A default installation of Oracle Database XE consists of five tablespaces: SYSTEM,SYSAUX,TEMP,USERS, andUNDO. Tablespaces typically store objects such as tables and indexes that share a common function or belong to a particular user application. Tablespaces are logical containers, comprising one or more physical files on disk that make it easy to map the storage of your tables and indexes onto physical disks as you please. For example, theSYSTEMtablespace contains, as you might expect, the database’s metadata, such as user accounts, table definitions, performance metrics, and so forth.
First, we’ll give you an overview of the tablespace types and how you use them in a typical database. The number and types of tablespaces in your database has a direct correlation to the performance and scalability of the applications your database supports. In addition, we’ll step through an example of creating a new tablespace to support your new Web- and PHP-based application.Tablespace Types
The primary types of tablespaces in any Oracle database are permanent, undo, and temporary. As of Oracle Database 10g (which provides the code base for Oracle Database XE), you can use a special kind of permanent, undo, or temporary tablespace called a bigfile tablespace to ease administrative tasks.
Your database will predominantly consist of permanent tablespaces to accommodate your growing application data needs. Your undo and temporary tablespaces will grow as your applications and number of users grow, but not as quickly.
Permanent tablespaces contain database objects (such as tables and indexes) that are retained beyond the end of a user session or transaction (we’ll cover transactional rules and usage in Chapter 32). In your Oracle XE database, theSYSTEM,SYSAUX, andUSERS tablespaces are examples of permanent tablespaces.
One of the permanent tablespaces in an installation of Oracle Database XE,SYSTEM, should never have any user or application tables. It is for database metadata such as user accounts, system statistics, and definitions for all tables and tablespaces across the database.
The companion tablespace to theSYSTEMtablespace, theSYSAUXtablespace, contains database objects for major database features such as the Enterprise Manager repository, Oracle Ultra Search, and Oracle Streams. While these are key features leveraged in many Oracle databases, they are not critical to the continued operation of the database. As a result, if theSYSAUXtablespace is damaged or you take it offline, the database continues to function.
Because of the amount of metadata managed by these additional Oracle features, theSYSAUXtablespace was added in Oracle Database 10g to keep the size of theSYSTEMtablespace more constant and easier to manage and optimize for the core Oracle features. If an Oracle feature uses an increasingly larger percentage of space inSYSAUX, you can create another permanent tablespace for a particular Oracle feature.
TheUSERStablespace, as the name implies, is the default tablespace for nonsystem users in the database. Unless you specify otherwise, when you create a new database user the user’s tables, indexes, sequences, and so forth reside in theUSERStablespace.
You can create multiple undo tablespaces in your database, but only one undo tablespace can be active at any one time. The database uses an undo tablespace to roll back failed or uncommitted transactions as well as to provide read consistency forSELECTstatements that may occur simultaneously with data manipulation language (DML) statements such asDELETE,INSERT, andUPDATE. In other words, undo tablespaces store the previous values of columns being updated or deleted, and therefore provide aSELECTstatement a view of the table that is consistent until theSELECT statement completes, even thoughINSERT,DELETE, andUPDATE activity may be occurring on any or all rows of the table.
While the monitoring and sizing of undo tablespaces is beyond the scope of this book, you want to make the size of the undo tablespace large enough to support your longest runningSELECTstatements during DML activity, but not so large as to use disk space that can be used for other applications, Oracle or otherwise.
Temporary tablespaces contain transient data that exists only for the duration of a user transaction or session, such as data to support a sort operation that will not fit in memory. You cannot save permanent objects in a temporary tablespace.
You can use a bigfile tablespace for permanent, undo, or temporary tablespaces. A bigfile tablespace consists of a single datafile up to a size of 131,072GB, and if you use a database block size of 32K, your database can have a total size up to 8 exabytes (8EB). Its primary advantage is for ease of maintenance; commands you use to maintain datafiles within a tablespace are now available at the logical tablespace level.
Even though Oracle Database XE restricts the total database size to 4GB, you can still create bigfile tablespaces to take advantage of their manageability features.
Note Other databases, such as MySQL, provide table performance, scalability, and availability benefits by using different tablespace types or storage engines; Oracle uses a single tablespace type for permanent tables and allows for different table types within the same tablespace. This simplifies tablespace management and permits tables and indexes with as variety of access methods to coexist within a single tablespace.
blog comments powered by Disqus