Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
What other otpion do you have than storing the data in tablespaces/datafiles? –  miracle173 Sep 13 '13 at 5:24
add comment

1 Answer

up vote 3 down vote accepted

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.
share|improve this answer
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. –  zenzax Sep 14 '13 at 14:54
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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