• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 492
  • Last Modified:

tablespaces and risks

Can i ask if there are any queries to list all tablespaces in an oracle 11g database, the space left, and whether the auto extended feature is enabled or not? What is the risk if a table space becomes full/close to capacity? is this risk data loss?

As I am more exposed to MSSQL and Access, what exactly is "tablespace" , in comparison to say MSSQL? And why do you have to enforce storage quotas per "tablespace"?
0
pma111
Asked:
pma111
  • 2
2 Solutions
 
slightwv (䄆 Netminder) Commented:
tablespace list:
select tablespace_name from dba_tablesapces;

Autoextend is on the data file:
select tablespace_name, file_name, autoextensible from dba_data_files;

Free space is more detailed.  There can be allocated space but unused.  This is in the dba_free_space view.

>>What is the risk if a table space becomes full/close to capacity?

If it fills up:  The database stops processing data.

There is no risk of loss.

Close to full should generate warnings.
0
 
pma111Author Commented:
Is there one "tablespace" per table in an oracle database? Or is it a collection of tables? I am trying to get how it compares to say an MSSQL databases.
0
 
slightwv (䄆 Netminder) Commented:
I believe I have suggested the Concepts guide to you in previous questions.  It can help answer architecture type questions.

A table is allocated in Extents.

A tablespace can have extents from many objects.  An Extent is one or more blocks.
0
 
ste5anSenior DeveloperCommented:
Oranges and apples, but a tablespace is basically something like a filegroup. It can have mutliple datafiles like a filegroup. The autoextend feature is the same as auto-growth. So it should be used wisely.

Here you find a nice picture of it: Oracle® Database Concepts - Tablespaces, Datafiles, and Control Files
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now