Solved

tablespaces and risks

Posted on 2013-12-23
4
459 Views
Last Modified: 2014-01-02
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
Comment
Question by:pma111
  • 2
4 Comments
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 250 total points
ID: 39736092
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
 
LVL 3

Author Comment

by:pma111
ID: 39736125
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39736153
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
 
LVL 33

Assisted Solution

by:ste5an
ste5an earned 250 total points
ID: 39737849
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

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question