2

I understand that a tablespace is a logical collection of tables and database objects. It could be made up of multiple data files (which are physical collections of objects). Apart from this, what are some of the advantages or use cases of tablespaces?

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

1 Answer 1

5

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.
5
  • 1
    Datafiles can actually be shrunk without dropping the tablespace with "alter database datafile ... resize"
    – sjk
    Sep 13, 2013 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. Sep 13, 2013 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, 2013 at 6:04
  • Resizing depends on highwater mark, if unoccupied then resizable.
    – atenz
    Sep 14, 2013 at 14:54
  • This doesn't explain the need of grouping multiple datafiles into one structure and then calling it tablespace. May 10, 2023 at 13:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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