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.


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.


to recreate tablespaces on a new database could use following query example

set linesize 300
set pagesize 100
select distinct 'create bigfile tablespace '||tablespace_name
||' datafile ''+DATA'' size '||round(sum(bytes)/1024/1024)
||'M autoextend on next '||increment_by*8192/1024/1024
||'M maxsize '||round(maxbytes/1024/1024/1024)||'G;'
FROM dba_data_files
  where tablespace_name not in ('SYSTEM','SYSAUX','UNDOTBS1','UNDOTBS2')
  group by tablespace_name, maxbytes, increment_by
  order by 1; 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy