Skip to Main Content
  • Questions
  • How to move contents data from one datafile to another?

Breadcrumb

May 4th

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)

Is this answer out of date? If it is, please let us know via 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 to Explore

Administration

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