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

autoextend query

is it possible to run a query to list autoextend settings for all tables spaces in an oracle database?
0
pma111
Asked:
pma111
  • 4
  • 4
  • 2
2 Solutions
 
slightwv (䄆 Netminder) Commented:
Yes.

Look at FILE_NAME, AUTOEXTENSIBLE and INCREMENT_BY in DBA_DATA_FILES.
0
 
pma111Author Commented:
is dba_data_files specific to tablespaces, or does it list information on other things above and beyond tables spaces. or is it a tidy view 1 row per tablespace?
0
 
slightwv (䄆 Netminder) Commented:
Tablespaces do not autoextend.  Only data files do.

A tablespace can have one to many datafiles.

A datafile belongs to one and only one tablesapce.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
pma111Author Commented:
is there also anywhere to see a list of all default oracle tablespaces that would be evident in every database?
0
 
pma111Author Commented:
in which case is there anywhere to tie up datafiles and tablespaces, to see which datafiles are in which tablespace?
0
 
slightwv (䄆 Netminder) Commented:
>>is there also anywhere to see a list of all default oracle tablespaces

The only real 'DEFAULT' tablespace is SYSTEM and I'm not 100% sure you cannot rename that one when creating a database.  There are some  sort of standard ones in the templates but they are up to the DBA that creates them.

You can see ALL tablespaces in, guess which view?  DBA_TABLESPACES.

Oracle is pretty good about naming the views.

Three levels:  USER_, ALL_ and DBA_ then the object you are looking for.

Differences:  USER is objects the USER owns.  ALL, the objects the user has permission to see, DBA everything in the database.

Looking for say, INDEXES?  DBA_INDEXES.

>>to see which datafiles are in which tablespace?

dba_data_files also has a TABLESPACE_NAME.
0
 
DavidSenior Oracle Database AdministratorCommented:
Depending upon the scripts used to install the database, there could be additional non-default tablespaces at creation time, but try:

select t.name, min(creation_time) creation
from v$datafile d, v$tablespace t where d.ts#=t.ts#
group by t.name
order by 2
/

Your results should resemble:

NAME                           CREATION
------------------------------ ---------
SYSTEM                         06-JUL-12
SYSAUX                         06-JUL-12
UNDOTBS1                       06-JUL-12
USERS                          06-JUL-12
RMAN                           06-JUL-12
0
 
slightwv (䄆 Netminder) Commented:
>>v$datafile d, v$tablespace t

I suggest using the views based on the USER/ALL/DBA labels over the V$ views for static objects.  The V$ views can be confusing.
0
 
pma111Author Commented:
dvz, yes those are the ones listed...
0
 
DavidSenior Oracle Database AdministratorCommented:
That particular question is answered, then.  Does that help or are we missing your intent?
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

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