Move tables/data of one datafile to another datafile - oracle

[Former Member]

Hi,

   is it possible to move certain amt of tables/data from  one datafile to another datafile of same tablespace to reduce

the size of the datafile.

one of the datafile is of size 10g . so  i want to reduce the size of the datafile by moving the content to another datafile which is of size 2G.

Kindly suggest.

Regards

Yugandhar.

[Reagan]

You will need to do a tablespace reorganization.

Regards

RB

[Former Member]

Agree with RB,

Simple option is to reorg/migrate data from old to new table space, it gives you lot of free space.

Not able to understand the requirement (specific that you have), why you are trying to shrink a data file which is 10G?

Regards,

Nick Loy

[ACE-SAP]

Hello

It is easily possible to move a table from a tablespace to another, but you do not have control over the datafile within that tablespace that will be used. Efficient reduction of datafile size will require to reorganize the tablespace.

10 G does not seem too big for a datafile.

Check how datafiles are defined and how extension is set,

select FILE_NAME, substr(TABLESPACE_NAME,1,15), ONLINE_STATUS, BYTES/1024/1024, MAXBYTES/1024/1024 from  dba_data_files;

You could block extension on the 'big' datafile

(setting maxsize to current size => MAXSIZE or disabling autoextend => AUTOEXTEND OFF ) and then only the second one will grow.

alter database datafile 'D:\ORACLE\SID\SAPDATA4\SID_10\SID.DATA10' autoextend off;

alter database datafile 'D:\ORACLE\QAS\SAPDATA4\QAS_10\QAS.DATA10' maxsize 10000M;

Regards

[Former Member]

Hi yves,

  Re-org will give some space. but my scenario is few datafiles is of 10G. i have to move some contents to other datafile which is of 4G to have size of all datafile size in uniform.

Is it possible in my case because Re-org wil provide space but will not reduce datafile size of 10g to <10g.

i have already made auto extend off.

Please suggest.

Regards

Yugandhar.

Message was edited by: yugandhar yugi

[Former Member]

Not sure why you want to make the datafile size uniform, there is no real benefit as oracle support 32 GB datafile. Unless you have all these datafile on on disk and you are facing some performance issue for this disk.

[Reagan]

Is it possible in my case because Re-org wil provide space but will not reduce datafile size of 10g to <10g.

Not correct. if you do a tablespace reorg then you have the preivilege to create the tablespace datafiles in the size you want.

Eg: if you are doing the reorg of the tablespace using export / import then you will export the contents to the OS level. Drop the tablespace and recreate them and start the import.

Regards

RB

[Former Member]

hi Sunil,

   I got requirement to do.

Regards

Yugandhar.

[ACE-SAP]

Hello

After a reorg it is possible to shrink the datafile to recover the freespace  alter database datafile 'D:\...\SID.DATA10' resize 8G;

You could also move all segments to a new tablespace with size limited datafiles and the rename the tablespace.

By the end I do not see major drawbacks to have a datafile bigger than the other, especially if it is as small as 10g .

Regards

[Former Member]

Hi Reagan,

Thanks for your reply.

i cannot create new datafile as i have already reached the max no of datafiles.

Please suggest

Regards

Yugandhar.

[Former Member]

you need to change parameter db_files = <no> (change the number as per your requirement)

for example db_files = 200

and restart the database.

[Former Member]

Hi, yves,

  Thanks for your reply.

i will check this and let you know now.

one more clarification, Post Re-org how do we identify that i could shrink datafile to certain size.

Please suggest

Regards

Yugandhar.