Solved

tablespaces and risks

Posted on 2013-12-23
4
482 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 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 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 34

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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Suggested Solutions

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
A company’s centralized system that manages user data, security, and distributed resources is often a focus of criminal attention. Active Directory (AD) is no exception. In truth, it’s even more likely to be targeted due to the number of companies …
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

751 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