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.

  • What other otpion do you have than storing the data in tablespaces/datafiles? – miracle173 Sep 13 '13 at 5:24

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.
  • 1
    Datafiles can actually be shrunk without dropping the tablespace with "alter database datafile ... resize" – sjk Sep 13 '13 at 5:51
  • 1
    @sjk I know. But in real life the most probably you will have some of the remaining data near the end of datafile and resize command will fail. – Mindaugas Riauba Sep 13 '13 at 5:59
  • Yes, I have noticed that. It can be fixed with "alter table ... move", but you will need a script for this if there are many segments in the tablespace. Dropping a whole tablespace is certainly easier if possible. – sjk Sep 13 '13 at 6:04
  • Resizing depends on highwater mark, if unoccupied then resizable. – atenz Sep 14 '13 at 14:54

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.