Sybex OCA Oracle 10g Administration I Study Guide phần 4 ppt

Managing Datafiles 151

Managing Datafiles

If you are not using OMF, you will need to manage datafiles yourself. The database will create

or reuse one or more datafiles in the sizes and locations that you specify whenever you create a

tablespace. A datafile belongs to only one tablespace and only one database at a time. Temp files

are a special variety of datafile that are used in temporary tablespaces. When the database creates

or reuses a datafile, the operating system file is allocated and initialized—filled with a regular pat￾tern of mostly binary zeros. This initialization will not occur with temp files.

Operations that you may need to perform on datafiles include the following:

Resizing them

Taking them offline or online

Moving (renaming) them

Recovering them

A useful technique for managing disk space used by datafiles is to enable AUTOEXTEND, which

tells the database to automatically enlarge a datafile when the tablespace runs out of free space.

The AUTOEXTEND attributes apply to individual datafiles and not to the tablespace.

To resize a datafile manually, use the ALTER DATABASE DATAFILE statement, like this:

ALTER DATABASE DATAFILE

'C:\ORACLE\ORADATA\ORA10\DATA01.DBF' RESIZE 2000M;

To configure a datafile to automatically enlarge as needed by adding 100MB at a time up to

a maximum of 8,000MB, execute the following:

ALTER DATABASE DATAFILE

'C:\ORACLE\ORADATA\ORA10\DATA01.DBF'

AUTOEXTEND ON NEXT 100M MAXSIZE 8000M;

Even if you do not plan to manage disk space using AUTOEXTEND, consider

enabling it on your datafiles to avoid out-of-space failures in your applications.

To relocate a datafile, take it offline, move it using an operating system command, rename

it, recover it (sync the file header with the rest of the database), and then bring it back online.

Here is an example:

1. Take it offline:

ALTER DATABASE DATAFILE

'C:\ORACLE\DATA02.DBF' OFFLINE;

2. Copy it:

HOST COPY C:\ORACLE\DATA02.DBF

C:\ORACLE\ORADATA\ORA10\DATA02.DBF

4367.book Page 151 Wednesday, October 13, 2004 1:18 PM

152 Chapter 3 Database Storage and Schema Objects

3. Change the filename in the control files:

ALTER DATABASE RENAME FILE

'C:\ORACLE\DATA02.DBF' TO

'C:\ORACLE\ORADATA\ORA10\DATA02.DBF';

4. Sync the file header with the database:

RECOVER DATAFILE 'C:\ORACLE\ORADATA\ORA10\DATA02.DBF';

5. Bring it back online so it can be used.

ALTER DATABASE DATAFILE

'C:\ORACLE\ORADATA\ORA10\DATA02.DBF' ONLINE;

Working with Schema Objects

A schema is collection of database objects owned by a specific database user. In an Oracle10g

database, the schema has the same name as the database user, so the two terms are synonymous.

Schema objects include the segments (tables, indexes, and so on) you have seen in tablespaces

as well as nonsegment database objects owned by a user. These nonsegment objects include con￾straints, views, synonyms, procedures, and packages. Database objects that are not owned by

one user and thus are not schema objects include roles, tablespaces, and directories.

In this section, you will learn about the built-in datatypes that Oracle provides for use in

your tables, how to create and manage tables, how to implement business rules as constraints

on your tables, and how to improve the performance of your tables with indexes. Finally, we

will briefly cover other schema objects that you can use in your applications.

Specifying Datatypes

Oracle10g has several built-in datatypes that you can use in your tables. These datatypes fall

into six major categories:

Character

Numeric

Datetime

LOB (Large Object)

ROWID

Binary

Oracle 10g supports additional datatypes, but we will focus on these six

major categories.

4367.book Page 152 Wednesday, October 13, 2004 1:18 PM

Working with Schema Objects 153

Character Datatypes

Character datatypes store alphanumeric data in the database character set or the Unicode char￾acter set. The database character set is specified when the database is created and indicates

which languages can be represented in the database. The US7ASCII character set supports the

English language as well as any other language that uses a subset of the English alphabet. The

WE8ISO8859P1 character set supports several European languages, including English, French,

German, and Spanish. The Unicode character set AL16UTF16 is intended to concurrently sup￾port every known language, although there are a few not yet included, such as Egyptian hiero￾glyphs and cuneiform.

The database character datatypes are as follows:

CHAR(size [byte|char]), NCHAR(size) Fixed width types that always store the column￾width amount of data, right padding with spaces as needed. The size specification is in bytes if

you do not include the keyword char. The NCHAR variation uses the Unicode character set, and

the size is always given in characters.

VARCHAR(size [byte|char]), VARCHAR2(size [byte|char]), NVARCHAR2(size) Variable

width types. Unlike their CHAR counterparts, the VARCHAR types store only the amount of data that

is actually used. The size specification is in bytes if you do not include the keyword char. The

NVARCHAR2 variation uses the Unicode character set and is always given in characters. VARCHAR and

VARCHAR2 are synonymous in Oracle10g, but Oracle reserves the right to change comparison

semantics of VARCHAR in future releases; so the VARCHAR2 type is preferred.

LONG A legacy datatype that exists for backward compatibility. It stores variable-length alpha￾numeric data up to 2GB in size. There are many restrictions on the usage of the columns of type

LONG: there is a limit of one column of type LONG per table, tables containing a LONG cannot be

partitioned, LONG datatypes cannot be used in subqueries, and few functions will work with

LONG data. The CLOB datatype is the preferred datatype for character data larger than VARCHAR2.

Here is an example of the character datatypes in use:

CREATE TABLE number_samples

(name VARCHAR2(48)

,country_code CHAR(2)

,address NVARCHAR2(100)

,city NVARCHAR2(64)

);

Numeric Datatypes

Numeric datatypes can store positive and negative fixed and floating-point numbers, zero,

infinity, and the special value Not A Number.

The database numeric datatypes are as follows:

NUMBER[(precision[, scale])] Stores zero, positive numbers, and negative numbers.

precision is the total number of digits and defaults to 38—the maximum. Scale is the number

of digits to the right of the decimal point and defaults to 0. A negative scale tells the database

4367.book Page 153 Wednesday, October 13, 2004 1:18 PM

154 Chapter 3 Database Storage and Schema Objects

to round off data to the left of the decimal point. scale has a valid range of –84 to 127.

Table 3.1 shows how precision and scale affect the way number types are stored.

BINARY_FLOAT, BINARY_DOUBLE Store single-precision and double-precision floating-point

data or one of the special floating-point values listed in Table 3.2.

TABLE 3.1 Precision, Scale, and Rounding

Specification Actual Value Stored Value

NUMBER(11,4) 12345.6789 12345.6789

NUMBER(11,2) 12345.6789 12345.68

NUMBER(11,-2) 12345.6789 12300

NUMBER(5,2) 12345.6789 Error – Precision is too small

NUMBER(5,2) 123456 Error – Precision is too small

TABLE 3.2 Floating-Point Constants

Constant Description

BINARY_FLOAT_NAN Not a number

BINARY_FLOAT_INFINITY Infinite

BINARY_FLOAT_MAX_NORMAL 3.40282347e+38

BINARY_FLOAT_MIN_NORMAL 1.17549435e-038

BINARY_FLOAT_MAX_SUBNORMAL 1.17549421e-038

BINARY_FLOAT_MIN_SUBNORMAL 1.40129846e-045

BINARY_DOUBLE_NAN Not a number

BINARY_DOUBLE_INFINITY Infinite

BINARY_DOUBLE_MAX_NORMAL 1.7976931348623157E+308

BINARY_DOUBLE_MIN_NORMAL 2.2250738585072014E-308

4367.book Page 154 Wednesday, October 13, 2004 1:18 PM

Working with Schema Objects 155

Here is an example of the number datatypes in use:

CREATE TABLE number_samples

(id NUMBER

,cost NUMBER(11,2)

,mass BINARY_FLOAT

,velocity BINARY_DOUBLE

);

Datetime Datatypes

Oracle10g has several datetime datatypes that can store dates, time, and time periods:

DATE Stores a date and time with a one-second granularity. The date portion can be from

January 1, 4712 BCE to December 31, 9999. The time portion of a DATE datatype defaults to

midnight, or 00:00:00 hours, minutes, and seconds.

TIMESTAMP[(precision)] Stores a date and time with subsecond granularity. The date

portion can be from January 1, 4712 BCE to December 31, 9999. precision is the number of

digits of subsecond granularity. precision defaults to 6 and can range from 0 to 9.

TIMESTAMP[(precision)] WITH TIMEZONE Extends the TIMESTAMP datatype by also stor￾ing a time zone offset. This time zone offset defines the difference (in hours and minutes) from

the local time zone and UTC (Coordinated Universal Time, also known as Greenwich mean

time or GMT). Like TIMESTAMP, precision defaults to 6 and can range from 0 to 9. Two

TIMESTAMP WITH TIMEZONE values are considered equal if they represent the same chronolog￾ical time. For example, 10:00AM EST is equal to 9:00AM CST or 15:00 UTC.

TIMESTAMP[(precision)] WITH LOCAL TIMEZONE Extends the TIMESTAMP datatype by

also storing a time zone offset. The TIMESTAMP WITH LOCAL TIMEZONE datatype does not

store the time zone offset with the column data. Instead, the timestamp value is converted

from the local time to the database time zone. Likewise, when data is retrieved, it is con￾verted from the database time zone to the local time zone. Like TIMESTAMP, precision

defaults to 6 and can range from 0 to 9.

INTERVAL YEAR[(precision)] TO MONTH Stores a period of time in years and months.

precision is the maximum number of digits needed for the year portion of this period, with

BINARY_DOUBLE_MAX_SUBNORMAL 2.2250738585072009E-308

BINARY_DOUBLE_MIN_SUBNORMAL 4.9406564584124654E-324

TABLE 3.2 Floating-Point Constants (continued)

Constant Description

4367.book Page 155 Wednesday, October 13, 2004 1:18 PM

156 Chapter 3 Database Storage and Schema Objects

a default of 2 and a range of 0 to 9. Use the INTERVAL YEAR TO MONTH datatype to store the

difference between two datetime values if yearly or monthly granularity is needed.

INTERVAL DAY[(d_precision)] TO SECOND[(s_precision)] Stores a period of time in

days, hours, minutes, and seconds. d_precision is the maximum number of digits needed for the

day portion of this period, with a default of 2 and a range of 0 to 9. s_precision is the number

of digits to the right of the decimal point needed for the fractional seconds portion of this period,

with a default of 6 and a range of 0 to 9. Use the INTERVAL DAY TO SECOND datatype to store the

difference between two datetime values if granularity down to a fractional second is needed.

Here is an example of the datetime datatypes in use:

CREATE TABLE datetime_samples

(dt DATE

,ts TIMESTAMP(3)

,ts2 TIMESTAMP

,tstz TIMESTAMP(3) WITH TIME ZONE

,tsltz TIMESTAMP WITH LOCAL TIME ZONE

,long_duration INTERVAL YEAR(4) TO MONTH

,short_duration INTERVAL DAY(3) TO SECOND(2)

);

LOB Datatypes

As the name implies, LOB datatypes store large objects, up to 232 – 1, or 4,294,967,295

data blocks. With an 8KB data block size, this comes out to about 32TB per field. LOBs are

designed for text, image video, audio, and spatial data. When you create a table with LOB col￾umns, you can specify a different tablespace and different attributes for the LOB data than for

the rest of the table. The LOB locator, a kind of pointer, is stored inline with the row and is

used to access the LOB data.

The database LOB datatypes are as follows:

CLOB Stores variable-length character data.

NCLOB Stores variable-length character data using the Unicode character set.

BLOB Stores binary variable-length data inside the database. BLOB data does not undergo char￾acter set conversion when passed between databases or between client and server processes.

BFILE Stores binary variable-length data outside the database. BFILEs are limited to a maxi￾mum of 4GB of data and even less in some operating systems.

Here is an example of the LOB datatypes in use:

CREATE TABLE lob_examples

( id NUMBER

, name VARCHAR2(32)

, description VARCHAR2(4000)

4367.book Page 156 Wednesday, October 13, 2004 1:18 PM

Working with Schema Objects 157

, definition CLOB

, mp3 BLOB

)TABLESPACE USERS

LOB (definition) STORE AS

(TABLESPACE user3_data);

ROWID Datatypes

ROWIDs are either physical or logical addresses that uniquely identify each row in an

Oracle10g table. The database ROWID datatypes are as follows:

ROWID Stores the base64-encoded physical address of any row in a heap-organized table in the

database. ROWIDs incorporate the Object ID (OID), relative file number, block number, and row slot

within the block. They are used internally in indexes and via the ROWID pseudocolumn in SQL. You

can use ROWID datatype columns in your tables to store “row pointers” to rows in other tables.

UROWID (Universal ROWID) Stores the base64-encoded string representing the logical

address of a row in an index-organized table.

Here is an example of the ROWID datatypes in use:

CREATE TABLE rowid_samples

(tab_rowid ROWID

,iot_rowid UROWID

);

Binary Datatypes

Oracle10g binary datatypes can be used to store unstructured data. Unlike regular character

data, binary data does not undergo character set conversion when passed from database to

database via a database link or export/import utility or when passed between database client

and server processes.

The database binary datatypes are as follows:

RAW(size) Stores unstructured data up to 2000 bytes in size.

LONG RAW Stores unstructured data up to 2GB in size. Like the LONG datatype, it exists to sup￾port backward compatibility, and there are several restrictions on LONG RAW columns—Oracle

discourages their use. Consider using the BLOB datatype instead.

Here is an example of the binary datatypes in use:

CREATE TABLE binary_samples

(init_string RAW(2000)

,logo_image LONG RAW

);

4367.book Page 157 Wednesday, October 13, 2004 1:18 PM