How to Migrate to Big File Tablespace
Venkat, August 08, 2016 - 4:26 pm UTC
Hi,
My db is 11.2.0.4 and 2 node RAC cluster exadata db .
My tablespace size is 2TB and its production tablespace .
I want to migrate this tablespace to Big file tablespace .
Could you share me exact steps how to migrate to Bigfile tablespace with out impacting production.
August 09, 2016 - 2:49 am UTC
You would need to move each of the segments within this tablespace to the new tablespace. If you are looking to minimize/eliminate outage time, look at using DBMS_REDEFINITION.
Plenty of examples on the asktom site.
A reader, August 09, 2016 - 3:33 am UTC
I sometime confuse with big and small tablespace.
so
what is major difference between both?
and what's a performance can be get more compare to small tablespace?
August 09, 2016 - 3:48 am UTC
Performance-wise I dont think you will see any difference at all.
Two components of the rowid (the unique location of a row) are:
- the file the row is in
- the block number in that file.
The rowid is fixed in size, so that means there is a limit to the how high the block number can go, which means a cap on the size of the file (eg 32G for a 8k blocksize).
So if you wanted files bigger than 32G you are stuck. Then we invented bigfile tablespaces. They only allow 1 file per tablespace. So now we can use the bytes in the rowid that were originally used for the file number to allow for a larger block number. So the files can be much much larger.