Moving Segments with INITIAL Extents Different to the LMT’s UNIFORM Extent Size
So you’ve just migrated your Oracle8i databases in which all your various sized segments reside (which incidentally were stored in the single “USERS” Tablespace) and now you want to place them in appropriately sized Locally-Managed Tablespaces (LMT’s). Here is a brief guide to help you get started in this process.
In order to start the migration process you’ll have to import the objects from the old database to the new database (a process that isn’t discussed in detail here). Remember, if you create a new Oracle9i instance with the SYSTEM Tablespace Locally-Managed, you will no longer be able to create Dictionary-Managed Tablespaces.
Therefore, you’ll have to create a LMT with AUTO extent management and AUTO Segment management, in which you “temporarily” store the segments prior to moving them.
CREATE TABLESPACE DUMMY_MED
LOGGING
DATAFILE 'DUMMY_MED01.dbf' SIZE 200M REUSE
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO
The user who is performing the import will require this TABLESPACE to be their nominated default.
ALTER USER
This will allow the IMP user to create the objects into the new Tablespace. Once this is completed, you may proceed with the migration.
Create at least one more TABLESPACE for this demonstration (a more appropriately sized Tablespace should also be created i.e. DATA_SML, DATA_MED, DATA_LGE, INDEX_SML, INDEX_MED, INDEX_LGE — I usually place the instance SID as a prefix e.g. SID_DATA_SML). Sizing objects into appropriately sized Tablespaces helps logical object administration.
CREATE TABLESPACE DATA_MED LOGGING
DATAFILE 'NPD_DATA_MED01.dbf' SIZE 51265K
REUSE EXTENT MANAGEMENT LOCAL UNIFORM SIZE 4096K
SEGMENT SPACE MANAGEMENT AUTO;
Moving Associated TABLES and INDEXES
Let’s look at the following table to be migrated.
1. CR_ANTENNA (As it appeared prior to EXPORT on the Oracle8i Database)
Connected to:
Oracle8i Enterprise Edition Release 8.1.6.0.0 – Production
With the Partitioning option
JServer Release 8.1.6.0.0 – Production
SQL> SELECT SEGMENT_NAME "NAME", TABLESPACE_NAME "TABLESPACE", BYTES, INITIAL_EXTENT "INITIAL", NEXT_EXTENT "NEXT" FROM DBA_SEGMENTS WHERE SEGMENT_NAME = 'CR_ANTENNA'; NAME --------------------------------------------------------------- TABLESPACE BYTES INITIAL NEXT ------------------------------ ---------- ---------- ---------- CR_ANTENNA USERS 19398656 19333120 9666560
2. CR_ANTENNA (after IMPORT)
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 – 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 – Production
SQL> SELECT SEGMENT_NAME "NAME", TABLESPACE_NAME "TABLESPACE", BYTES, INITIAL_EXTENT "INITIAL", NEXT_EXTENT "NEXT", EXTENTS FROM USER_SEGMENTS WHERE SEGMENT_NAME = 'CR_ANTENNA'; NAME -------------------------------------------------------------------------- TABLESPACE BYTES INITIAL NEXT EXTENTS ------------------------------ ---------- ---------- ---------- ---------- CR_ANTENNA DUMMY_MED 19922944 19333120 19
Notice there is no NEXT extent. This is due to the AUTO extent management of the LMT. The CR_ANTENNA table currently has an INITIAL extent of 1922944 (19MB). We want to move this segment to the DATA_MED tablespace that has a UNIFORM EXTENT of 4 MB.
Use the ALTER TABLE
SQL> ALTER TABLE CR_ANTENNA MOVE TABLESPACE DATA_MED;
Table altered.
3. CR_ANTENNA (initial MOVE)
Then we look at the segment in the new Tablespace.
SQL> SELECT SEGMENT_NAME "NAME", TABLESPACE_NAME "TABLESPACE", BYTES, INITIAL_EXTENT "INITIAL", NEXT_EXTENT "NEXT", EXTENTS FROM USER_SEGMENTS WHERE SEGMENT_NAME = 'CR_ANTENNA'; NAME -------------------------------------------------------------------------- TABLESPACE BYTES INITIAL NEXT EXTENTS ------------------------------ ---------- ---------- ---------- ---------- CR_ANTENNA DATA_MED 20971520 19333120 4194304 5
Well, the NEXT extent size is correct, but the INTIAL is STILL 19333120? As frustrating as it seems, the Segment won’t take on the DEFAULT storage parameters from the Locally Managed Tablespace. We want it to be 4194304, like the NEXT, and while the OBJECT STILL ONLY occupies 5 EXTENTS, it is still inappropriate for the initial to be 19333120 as it’s a little untidy. In order to “tidy up” you MUST specify the INTIAL extent sizing in the ALTER TABLE MOVE clause.
Prior to performing that action, move the table back to it’s original Tablespace. (Thereby keeping segment extents as close to each other on disk as possible).
SQL> ALTER TABLE CR_ANTENNA MOVE TABLESPACE DUMMY_MED; NAME -------------------------------------------------------------------------- TABLESPACE BYTES INITIAL NEXT EXTENTS ------------------------------ ---------- ---------- ---------- ---------- CR_ANTENNA DUMMY_MED 19922944 19333120 19
Now specify the INITIAL in the STORAGE clause.
SQL> ALTER TABLE CR_ANTENNA MOVE TABLESPACE DATA_MED STORAGE (INITIAL 4096K);
Table altered.
Now if we look, we’ll find the size is right, if not an EXTENT smaller than previously shown.
4. CR_ANTENNA table after final MOVE
SQL> SELECT SEGMENT_NAME "NAME", TABLESPACE_NAME "TABLESPACE", BYTES, INITIAL_EXTENT "INITIAL", NEXT_EXTENT "NEXT", EXTENTS FROM USER_SEGMENTS WHERE SEGMENT_NAME = 'CR_ANTENNA'; NAME -------------------------------------------------------------------------- TABLESPACE BYTES INITIAL NEXT EXTENTS ------------------------------ ---------- ---------- ---------- ---------- CR_ANTENNA DATA_MED 16777216 4194304 4194304 4
The same should be performed for associated indexes when moving to appropriate INDEX Tablespaces.
About the Author: Greg Johnson is an OCP 8i Database Administrator working for Dialog Information Technology as an Oracle Database Consultant. Dialog Information Technology is one of Australia’s leading technology services organizations trading nationally and internationally. Dialog provides a wide range of information technology services including outsourcing and technology management, systems integration and application development, implementation, training and support. Greg can be contacted at Johnsog123@hotmail.com.
