In this example, you modify the create tablespace command to not only create the
new tablespace with a larger size, but also place the new tablespace on a different disk volume:
EXECUTE IMMEDIATE 'CREATE SMALLFILE TABLESPACE "XPORT_REORG0"
DATAFILE ''/u04/oradata/xport.dbf''
SIZE 300M REUSE
LOGGING EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO';
Once the script has been edited, run the script in SQL*Plus using an account with DBA
privileges. The output of the script looks like this:
SQL> @reorg1.sql
-- Target database: dw.world
-- Script generated at: 08-JUL-2007 23:38
Starting reorganization
Executing as user: RJB
CREATE SMALLFILE TABLESPACE "XPORT_REORG0" DATAFILE
'/u04/oradata/xport_reorg0.dbf' SIZE 300M REUSE LOGGING EXTENT MANAGEMENT
LOCAL SEGMENT SPACE MANAGEMENT AUTO
FIGURE 4-17 Review: Save Full Script
Chapter 4: Physical Database Layouts and Storage Management 91
ALTER TABLE "SYS"."OBJ_FILL" MOVE TABLESPACE "XPORT_REORG0"
DROP TABLESPACE "XPORT" INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS
ALTER TABLESPACE "XPORT_REORG0" RENAME TO "XPORT"
Completed Reorganization. Starting cleanup phase.
Starting cleanup of recovery tables
Completed cleanup of recovery tables
Starting cleanup of generated procedures
Completed cleanup of generated procedures
Script execution complete
SQL>
You can avoid using reorganization scripts in many cases if you use bigfile tablespaces
because they consist of only one datafile.
Pages:
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186