Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> Tablespace usage
NAVEEN KP
post Aug 2 2012, 11:45 AM
Post #1


Member
**

Group: Members
Posts: 23
Joined: 25-September 10
From: Bangalore
Member No.: 43,913



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?
Go to the top of the page
 
+Quote Post
NAVEEN KP
post Aug 2 2012, 11:56 AM
Post #2


Member
**

Group: Members
Posts: 23
Joined: 25-September 10
From: Bangalore
Member No.: 43,913



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.
Go to the top of the page
 
+Quote Post
burleson
post Aug 2 2012, 03:04 PM
Post #3


Advanced Member
***

Group: Members
Posts: 11,810
Joined: 26-January 04
Member No.: 13



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]







--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 20th December 2014 - 08:05 PM