In addition, I??™ll cover the most common ways to move datafiles,
online redo log files, and control files to different disks.
Resizing Tablespaces and Datafiles
In an ideal database, all tablespaces and the objects within them are created at their optimal
sizes. Resizing a tablespace proactively or setting up a tablespace to automatically extend can
potentially avoid a performance hit when the tablespace expands or an application failure occurs
if the datafile(s) within the tablespace cannot extend. More details on how to monitor space usage
can be found in Chapter 6.
The procedures and methods available for resizing a tablespace are slightly different, depending
on whether the tablespace is a smallfile or a bigfile tablespace. A smallfile tablespace, the only
type of tablespace available before Oracle 10g, can consist of multiple datafiles. A bigfile tablespace,
in contrast, can only consist of one datafile, but the datafile can be much larger than a datafile in
I
Chapter 4: Physical Database Layouts and Storage Management 77
a smallfile tablespace: A bigfile tablespace with 64K blocks can have a datafile as large as 128TB.
In addition, bigfile tablespaces must be locally managed.
Resizing a Smallfile Tablespace Using ALTER DATABASE
In the following examples, we attempt to resize the USERS tablespace, which contains one
datafile, starting out at 5MB. First, we make it 15MB, then realize it??™s too big, and shrink it down
to 10MB.
Pages:
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177