TableSpace is one of the logical structure of the database.The minimum tablespaces that are required to create a database are system and sysaux.
Sysaux tablespace was introduced in oracle10g version.Sysaux is considered as an auxilary tablespace for the system and some of the components meta data have been moved from system to sysaux.
In order to know the components that are moved from system to sysaux, issue the following select statement:
select * from V$sysaux_occupants;
We can not offline system tablespace but we can offline sysaux tablespace.we can not rename system & sysaux tablespace.
In oracle 10g, oracle has introduced Big file tablespace which contains only one datafile.Big file tablespace datafile can grow upto tera bytes.
A tablespace can have maximum 1024 datafiles.
Recommanded to create Big file tablespace if third party volume manager or ASm(Automatic Storage Management) is in place.
If the block size is 8K, a datafile can grow upto 32GB and if the block size is 16K, a datafile can grow upto 64GB.
Recommanded to create Big file in OLAP environments(dataware housing).It can also be created in OLTP systems.
