In Oracle Database 11g with the initialization
parameter DB_CREATE_FILE_DEST set to an ASM or file system location, you can run a create
tablespace command like this:
create tablespace bi_02;
In this case, the tablespace BI_02 is created with a size of 100MB in a single datafile,
AUTOEXTEND is on, and the next extent is 100MB when the first datafile fills up. In addition,
extent management is set to LOCAL, space allocation is AUTOALLOCATE, and segment space
management set to AUTO.
The conclusion to be reached here is that we want to monitor the free and used space within
a tablespace to detect trends in space usage over time, and as a result be proactive in making sure
that enough space is available for future space requests. As of Oracle Database 10g, you can use
the DBMS_SERVER_ALERT package to automatically notify you when a tablespace reaches a
warning or critical space threshold level, either at a percent used, space remaining, or both.
Insufficient Space for Temporary Segments
A temporary segment stores intermediate results for database operations such as sorts, index builds,
distinct queries, union queries, or any other operation that necessitates a sort/merge operation that
cannot be performed in memory. Temporary segments should be allocated in a temporary tablespace,
which I introduced in Chapter 1. Under no circumstances should the SYSTEM tablespace be used for
temporary segments; when the database is created, a non-SYSTEM tablespace should be specified as
a default temporary tablespace for users who are not otherwise assigned a temporary tablespace.
Pages:
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304