Tablespace usage Options

[NAVEEN KP]

Hi DBAs,

Today i came across a strange situation in one of the oracle database. From OEM grid control when i checked the tablespace usage, found around 250 GBs of free space. But when i checked the allocated size and maxsize of datafiles from database side(sql prompt) found allocated datafile size reached maxsize for all datafiles in that tablespace. %age used was 100% and growable MB 0. (datafiles are in autoextend mode).

In alert log file i can find 'unable to extend table by 1024' for the same tablespace. users are unable to insert data to database.

further, from OEM i checked the datafile level usage in tablespace and found sufficient free space in datafiles also(from OEM). When tried to resize one of datafile to a lower value (more than used MB), getting error as 'resize value specified is below the datafile usage'.

What may be the reasons for this?

[NAVEEN KP]

Further, in this database rapid data insert and deletion/purging happening during testing. Purging activity not releasing space from database side even though some large tables are purged by application side.

[burleson]

GHi Naveen,

>> From OEM grid control when i checked the tablespace usage, found around 250 GBs of free space.

Try to wean yourself from OEM. I;ve been a DBA since 1983, and I've never used a GUI . . . Learn the dictionary views and commands! Get this free poster:


http://www.rampant-books.com/poster_oracle_database.htm


*********************************************************

>> But when i checked the allocated size and maxsize of datafiles from database side(sql prompt) found allocated datafile size reached maxsize for all datafiles in that tablespace.

How cabn you tell if "autoectend" datafiles are at their max size? Thet depends on the size of the filesystem!

Can you post the script please?

******************************************************

>> getting error as 'resize value specified is below the datafile usage'. What may be the reasons for this?

One reason is that the tablespace has allocated space for a table within it!

To coalecse it, try this (during scheduled downtime!):

http://www.dba-oracle.com/bp/teaser_rs_ts_frag.htm

See, a tablespace look "full" when it has plently of space.

Try running tsfree.sql, that's the script I use. Run below, and the scripts on this page:

http://www.dba-oracle.com/t_free_space_script.htm

CODE

[font="Courier New"]clear breaks;
clear computes;
set verify off;
set pagesize 66;
set linesize 79;
set newpage 0;[/font]

[font="Courier New"]column temp_col new_value spool_file noprint;
column today new_value datevar noprint;
column TABLESPACE_NAME        FORMAT A15    HEADING 'Tablespace';
COLUMN PIECES                 FORMAT 9,999  HEADING 'Tablespace|Pieces';
COLUMN FILE_MBYTES            FORMAT 99,999 HEADING 'Tablespace|Mbytes';
cOLUMN FREE_MBYTES            FORMAT 99,999 HEADING 'Free|Mbytes';
COLUMN CONTIGUOUS_FREE_MBYTES FORMAT 99,999 HEADING 'Contiguous|Free|Mbytes';
COLUMN PCT_FREE               FORMAT 999    HEADING 'Percent|FREE';
COLUMN PCT_CONTIGUOUS_FREE    FORMAT 999    HEADING 'Percent|FREE|Contiguous';[/font]

[font="Courier New"]ttitle left datevar right sql.pno -
       center ' Instance Data File Storage' SKIP 1 -
       center ' in ORACLE Megabytes (1048576 bytes)' -
       skip skip;[/font]

[font="Courier New"]BREAK ON REPORT
COMPUTE SUM OF FILE_MBYTES ON REPORT[/font]

[font="Courier New"]select to_char(sysdate,'mm/dd/yy') today,
       TABLESPACE_NAME,
       PIECES,
       (D.BYTES/1048576) FILE_MBYTES,
       (F.FREE_BYTES/1048576) FREE_MBYTES,
       ((F.FREE_BLOCKS / D.BLOCKS) * 100) PCT_FREE,
       (F.LARGEST_BYTES/1048576) CONTIGUOUS_FREE_MBYTES,
       ((F.LARGEST_BLKS / D.BLOCKS) * 100) PCT_CONTIGUOUS_FREE
from SYS.DBA_DATA_FILES D, SYS.FREE_SPACE F[/font] [font="Courier New"][size="2"]where D.STATUS = 'AVAILABLE' AND
      D.FILE_ID= F.FILE_ID AND
      D.TABLESPACE_NAME = F.TABLESPACE
order by TABLESPACE_NAME;

[/size][/font]