We will discuss bigfile tablespace reorganization
in the next section.
Resizing a Bigfile Tablespace Using ALTER TABLESPACE
A bigfile tablespace consists of one and only one datafile. Although you will learn more about
bigfile tablespaces in Chapter 6, we will present a few details about how a bigfile tablespace can
be resized. Most of the parameters available for changing the characteristics of a tablespace??™s
datafile??”such as the maximum size, whether it can extend at all, and the size of the extents??”are
now modifiable at the tablespace level. Let??™s start with a bigfile tablespace created as follows:
create bigfile tablespace dmarts
datafile '/u05/oradata/dmarts.dbf' size 750m
autoextend on next 100m maxsize unlimited
extent management local
segment space management auto;
Operations that are valid only at the datafile level with smallfile tablespaces can be used with
bigfile tablespaces at the tablespace level:
SQL> alter tablespace dmarts resize 1g;
Tablespace altered.
Although using alter database with the datafile specification for the DMARTS tablespace will
work, the advantage of the alter tablespace syntax is obvious: You don??™t have to or need to know
where the datafile is stored. As you might suspect, trying to change datafile parameters at the
tablespace level with smallfile tablespaces is not allowed:
SQL> alter tablespace users resize 500m;
alter tablespace users resize 500m
*
ERROR at line 1:
ORA-32773: operation not supported for smallfile tablespace USERS
If a bigfile tablespace runs out of space because its single datafile cannot extend on the disk,
you need to relocate the datafile to another volume, as we will discuss in the next section,
???Moving Datafiles.
Pages:
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187