Oracle Tablespaces – Advantages and Use Cases

oracletablespaces

I understand that tablespace is a logical collection of tables and database objects. It could be made up of multiple data files (which is the physical collection of objects). Apart from this, what is the aparent advantages that tablespaces provide? Anyone care to share use case(s) of how tablespace/data files can help solve problems.

Best Answer

Few uses for the tablespaces:

  1. When you no longer need the data you can drop the tablespace and delete datafiles. This is basically the only way to release space back from Oracle to the operating system.
  2. When doing backups you can exclude tablespace from backup. E.g. if it contains non essential or easily recreatable data.
  3. When doing restore you can skip tablespace. E.g. if you need only specific table or you need to start as soon as possible you can skip index tablespaces and rebuild indexes online.
  4. Tablespace per user can be useful. Then when user is no longer needed you drop the user then drop tablespace and have disk space back. Also if user suddenly starts to generate lots of data only his tasks will break when tablespace becomes full. Other users will be unaffected.
  5. Data blocks corruption usually is contained within tablespace. In such cases only one tablespace is affected.
  6. You can store tablespaces (datafiles) on different types of disks. So you can manually manage which data goes on SSD and which on SATA.
  7. In really rare cases you may need different block size for the tablespace.
  8. You can choose to compress the whole tablespace content.
  9. You can move selected data to a tablespace, unplug it from the database and attach to another database. This is transportable tablespace.
Related Question