Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am wanting to migrate all tablespaces in an existing db to use bigfile tablespace rather than the smallfile tablespace. The Oracle documentation hints that this is possible but doesn't explicitly show how to do it.

Any help/pointers would be greatly appreciated.

share|improve this question

migrated from stackoverflow.com Mar 5 '13 at 11:33

This question came from our site for professional and enthusiast programmers.

1 Answer 1

You have a few options.

Create your new bigfile tablespaces, then either:

1) Move the tables one by one with:

alter table mytable move tablespace bigfile_tablespace;

Remember to move indexes too!

alter index myindex rebuild tablespace bigfile_index_tablespace;

2) Export the database with Data Pump, drop the existing objects, then reimport using a remap_tablespace clause, for example:

impdp remap_tablespace=OLDSMALLTS1:NEWBIGTS1,OLDSMALLTS2:NEWBIGTS2 directory=mydir dumpfile=mydumpfile.dmp logfile=mylogfile.log

3) Use DBMS_REDEFINITION to move the objects. An example of this can be found in my answer to another question, here.

Once done, drop all of the old objects and then the tablespaces.

If you can afford the downtime, datapump will be the easiest option.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.