Re: Relocating Tablespace

From: Vladimir M. Zakharychev <bob_at_dpsp-yes.com>
Date: Tue, 5 Feb 2002 15:04:13 +0300
Message-ID: <a3ohh6$b25$1@babylon.agtel.net>

You can do it at least in three ways (one of which you suggested already):

Variant A:

1. Create new tablespace on a disk that has enough space.
2. Create several new rollback segments in that tablespace (probably the same amount and same sizes as your existing segments) and put them online.
3. Take old rollbacks offline and wait until they go down.
4. Drop old rollback segments
5. Drop the old tablespace
6. delete data files (on OS level) and backup your control files.

Variant B (assumes you are under unix):

1. shutdown the database
2. move datafiles to the new location (mv)
3. create symbolic links to them in the old directory (ln -s)
4. startup the database

No need to backup control files in this case because the datafiles layout didn't change from Oracle's point of view. This approach requires you to bring the database down though and only works on unixes.

Variant C (will restrict database access for normal users for the duration of restricted session):
1. alter system enable restricted session

2. take all rollback segments offline (except those residing in SYSTEM).
3. copy data files to the new location (on OS level)
4. alter tablespace rename datafile '/old/path/datafile.ext' to '/new/path/datafile.ext'

    for each datafile in the tablespace

5. take rollbacks online again and verify there are no errors
6. alter system disable restricted session
7. delete old data files and backup control files

You should generally proceed with caution, but since rollback segments do not hold any data and there is always at least one system rollback segment it is pretty safe to play with them. In fact, to tune rollback segments for best performance (that is, determine their initial and next extent sizes and optimal size) you will have to drop and recreate them several times with adjusted parameters anyway.

--
Vladimir Zakharychev (bob@dpsp-yes.com)                http://www.dpsp-yes.com
Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
All opinions are mine and do not necessarily go in line with those of my employer.


"Tony" <ar_at_hotmail.com> wrote in message
news:1012904327.13225.0.nnrp-02.d4f0f429_at_news.demon.co.uk...


> Hi

>

> Am new to DBA type stuff lol. I have a reporting server here and have noted

> that one of the system drives has filled up. Examining the storage schema I

> have noted that a TABLESPACE called RBS has a DATAFILE and a number of

> ROLLBACK segments associated with it.

>

> What I would like to do is to relocate the DATAFILE.  How do I do this the

> most affective way with the knowledge that the rollback segments are

> aassociated with this tablespace?  Can I do this whilst users are on line or

> not ?

>

> Do I create a new Datafile on another drive then take the old one off line

> and delete the old one.? Do I need to take the rollback segments off line

> whilst i make this change ?

>

> Any help would be useful

>

> Thanks

>

> Tony Rees

> DMC Stratex Networks

> 5th Jan 2002

> .

>

>

Received on Tue Feb 05 2002 - 06:04:13 CST