How to convert bigfile tablespace to smallfile tablespace
Hi, How would I convert bigfile tablespace to smallfile tablespace on 10.2.0.4 on unix? Thank you in advance.
1) Create a new tablespace 2) Move all the object from BIG to normal tablespace Asif Momen http://momendba.blogspot.com
Hello, The option BIGFILE cannot be altered to SMALLFILE. So you should create a new Tablespace. Before moving your datas, be ensure that you have enough capacity in this new Tablespace. So plan to get enough place on your server. After moving the content of the Tablespace (it can be done for instance by DataPump) you could DROP the BIGFILE Tablespace and RENAME the new SMALLFILE Tablespace so as to keep the original name, as follow: CREATE SMALLFILE TABLESPACE <NEW_TBS_NAME>; --> Move or export/import the content DROP TABLESPACE <ORIGINAL_TBS_NAME> INCLUDING CONTENTS AND DATAFILES; ALTER TABLESPACE <NEW_TBS_NAME> RENAME TO <ORIGINAL_TBS_NAME>; Hope this help. Best regards, Jean-Valentin Edited by: Lubiez Jean-Valentin on Feb 24, 2010 7:39 PM
In addition to the answers - you cannot simply convert the tablespace - why do you want to do that. What is so bad with your bigfile tablespace? Kind regards Uwe http://uhesse.wordpress.com