Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted Jul 22, 2002

Oracle 9i Locally-Managed Tablespaces (LMT) Migration Method

By DatabaseJournal.com Staff

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 DEFAULT TABLESPACE DUMMY_ MED;

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 MOVE statement.

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.




Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM