?
Solved

tablespaces and risks

Posted on 2013-12-23
4
Medium Priority
?
487 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 1000 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 77

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 35

Assisted Solution

by:ste5an
ste5an earned 1000 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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
This video shows how to recover a database from a user managed backup
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

719 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