Important Query related to Tablesapce

Reader Mode

How to retrieve tablespace default storage Parameters?

SELECT TABLESPACE_NAME "TABLESPACE",
   INITIAL_EXTENT "INITIAL_EXT",
   NEXT_EXTENT "NEXT_EXT",
   MIN_EXTENTS "MIN_EXT",
   MAX_EXTENTS "MAX_EXT",
   PCT_INCREASE
   FROM DBA_TABLESPACES;

TABLESPACE  INITIAL_EXT  NEXT_EXT  MIN_EXT   MAX_EXT    PCT_INCREASE  
----------  -----------  --------  -------   -------    ------------ 
RBS             1048576   1048576        2        40               0
SYSTEM           106496    106496        1        99               1
TEMP             106496    106496        1        99               0
TESTTBS           57344     16384        2        10               1
USERS             57344     57344        1        99               1

How to retrieve information tablesapce and associated datafile?

SELECT  FILE_NAME, BLOCKS, TABLESPACE_NAME
   FROM DBA_DATA_FILES;

FILE_NAME                                      BLOCKS  TABLESPACE_NAME
------------                               ----------  -------------------
/U02/ORACLE/IDDB3/RBS01.DBF                      1536  RBS
/U02/ORACLE/IDDB3/SYSTEM01.DBF                   6586  SYSTEM
/U02/ORACLE/IDDB3/TEMP01.DBF                     6400  TEMP
/U02/ORACLE/IDDB3/TESTTBS01.DBF                  6400  TESTTBS
/U02/ORACLE/IDDB3/USERS01.DBF                     384  USERS

How to retrive Statistics for Free Space (Extents) of Each Tablespace?

SELECT TABLESPACE_NAME "TABLESPACE", FILE_ID,
   COUNT(*)    "PIECES",
   MAX(blocks) "MAXIMUM",
   MIN(blocks) "MINIMUM",
   AVG(blocks) "AVERAGE",
   SUM(blocks) "TOTAL"
   FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME, FILE_ID;

TABLESPACE    FILE_ID  PIECES   MAXIMUM    MINIMUM  AVERAGE    TOTAL
----------    -------  ------   -------    -------  -------   ------
RBS                 2       1       955        955      955      955
SYSTEM              1       1       119        119      119      119
TEMP                4       1      6399       6399     6399     6399
TESTTBS             5       5      6364          3     1278     6390
USERS               3       1       363        363      363      363

PIECES shows the number of free space extents in the tablespace file, MAXIMUM and MINIMUM show the largest and smallest contiguous area of space in database blocks, AVERAGE shows the average size in blocks of a free space extent, and TOTAL shows the amount of free space in each tablespace file in blocks. This query is useful when you are going to create a new object or you know that a segment is about to extend, and you want to make sure that there is enough space in the containing tablespace.

Post Views: 63

Responses

Sara Reid December 21, 2009

Never create rollback segments using multiple datafiles within a single tablespace. Always use a single datafile or multiple tablespaces with a single datafile each. Rollback segments will not autoextend for a single transaction across multiple datafiles with the same tablespace. An attempt to extend in this fashion will simply cause Oracle to send the calling process a break (Ctl-C) and the transaction will be aborted. There should be some evidence in the alert log showing a rollback segment running out of space.

4gb micro sd karte

Related Articles

Responses