Solved

tablespaces and risks

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Creating and Managing Databases with phpMyAdmin in cPanel.
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

749 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