Re: Relocating Tablespace

Thanks for all your comments and help. I am going to opt for option 3 and schedule in down time this evening.

I will let you know how I got on

regards

Tony
DMC Stratex Networks
(Scotland)

"Vladimir M. Zakharychev" <bob_at_dpsp-yes.com> wrote in message news:a3ohh6$b25$1_at_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_at_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 - 09:21:04 CST