Skip to Main Content
  • Questions

  • How to move contents data from one datafile to another?

Breadcrumb

XMAS_TREE

The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments, but we might still sneak in a few Office Hours sessions! Thanks for being a member of the AskTOM community.

Question and Answer

Connor McDonald

Thanks for the question, Richard.

Asked: November 07, 2011 - 1:03 pm UTC

Last updated: October 06, 2018 - 6:01 am UTC

Version: 10.2.0.5

Viewed 10K+ times! This question is

You Asked

Hi Tom,

Thanks for your asktom website.

A tablespace consists of several data files. My purpose is to cleanly move contents in one data file to another on and then I can drop that empty file from the tablespace. So I can reduce the data file number.

Thanks for your help.

Richard Xu

and Tom said...

we don't really do that - you would probably want to

a) create a new tablespace with however many datafiles you want
b) move the contents of this tablespace into that new one
c) drop the old tablespace
d) rename the new tablespace back to the old name.


You could, if you really wanted to, create a new table and use the alter table command to allocate extents in a particular partition - and then use conventional path inserts to move the data from the old table into the new table - index it, grant on it, etc - then drop the old table and rename the new one. But - that would be a ton of work for not much payback.


I wouldn't be too worried about reducing the number of datafiles by one, it won't have much of an impact on you day to day - probably you just want to skip the process altogether.

Rating

  (1 rating)

We're not taking comments currently, so please try again later if you want to add a comment.

Comments

Does not work for USERS

Rob Taft, October 05, 2018 - 1:33 pm UTC

You cannot remove the USERS tablespace, which makes it hard to swap out.
Connor McDonald
October 06, 2018 - 6:01 am UTC

Who says can't you remove USERS ?

As long it is not in here:

SQL> select * from database_Properties where property_name like '%TABLE%'
  2  @pr
==============================
PROPERTY_NAME                 : DEFAULT_PERMANENT_TABLESPACE
PROPERTY_VALUE                : USERS
DESCRIPTION                   : Name of default permanent tablespace
==============================
PROPERTY_NAME                 : DEFAULT_TEMP_TABLESPACE
PROPERTY_VALUE                : TEMP
DESCRIPTION                   : Name of default temporary tablespace

PL/SQL procedure successfully completed.


then its just like any other tablespace
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database