Solved

Free space tablespace

Posted on 2011-02-14
5
949 Views
Last Modified: 2012-05-11
How do we find how much is the free space available in oracle tablespace.
System tablespace has 23MB free. It grew by 34 MB in the past month and 26 MB in the previous month. Please advice as to if this tablespace should be increased or if this is a purge job that is not running.
0
Comment
Question by:hussainkhan22
5 Comments
 
LVL 8

Accepted Solution

by:
point_pleasant earned 333 total points
ID: 34888160
rem tsfree.sql - Shows all free space within tablespaces.

Prompt be sure that you have run free_space.sql prior to this script

clear breaks;
clear computes;
set verify off;
set pagesize 66;
set linesize 79;
set newpage 0;

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';

ttitle left datevar right sql.pno -
       center ' Instance Data File Storage' SKIP 1 -
       center ' in ORACLE Megabytes (1048576 bytes)' -
       skip skip;

BREAK ON REPORT
COMPUTE SUM OF FILE_MBYTES ON REPORT

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
where D.STATUS = 'AVAILABLE' AND
      D.FILE_ID= F.FILE_ID AND
      D.TABLESPACE_NAME = F.TABLESPACE
order by TABLESPACE_NAME;

Here is the report from this script.

Tablespace         Pieces     Mbytes  Mbytes  FREE       Mbytes Contiguous
--------------- ---------- ---------- ------- ------- ---------- ----------
MASTER1_DETAILS       1      18         2        10            2   10
MASTER1_DETAILS       1      20        20       100           20  100
MASTER2_DETAILS       1       2         1        65            1   65
MASTER3_DETAILS       1       5         5        95            5   95
MASTER4_DETAILS       2       3         1        36            1   35
RBS_ONE              11     490       380        78          280   57
RBS_TWO              11     490       379        77          279   57
SYSTEM               17      60        45        76           45   75
TEMP                  1     650       650       100          650  100
TOOLS                 2      15         9        61            8   55
USERS                41     100        31        31            4    4
----------
                      13,255
0
 

Author Comment

by:hussainkhan22
ID: 34888186
Can you please provide "Prompt be sure that you have run free_space.sql prior to this script". What exactly we must look in the above output for system tablespace.
System tablespace has 23MB free. It grew by 34 MB in the past month and 26 MB in the previous month. Please advice as to if this tablespace should be increased or if this is a purge job that is not running. What do you suggest me to inform client and do.
I really appreciate your help.

0
 
LVL 8

Assisted Solution

by:point_pleasant
point_pleasant earned 333 total points
ID: 34888255
here it is sorry

rem run this script first, to create the free_space view;
drOP VIEW SYS.FREE_SPACE;

CREATE VIEW SYS.FREE_SPACE AS
SELECT
       TABLESPACE_NAME TABLESPACE,
       FILE_ID,
       COUNT(*)    PIECES,
       SUM(BYTES)  FREE_BYTES,
       SUM(BLOCKS) FREE_BLOCKS,
       MAX(BYTES)  LARGEST_BYTES,
       MAX(BLOCKS) LARGEST_BLKS
FROM  
       SYS.DBA_FREE_SPACE
GROUP  BY TABLESPACE_NAME, FILE_ID;
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 34888472
I'm not clear on the exact problem you are trying to solve.  Is it shrinking free space in the SYSTEM tablespace?  Or, are you concerned about free space in other tablespaces also?  When you say: "It grew by 34 MB in the past month and 26 MB in the previous month", what exactly is "it"?  Is that free space, or used space?

The SYSTEM tablespace should *NOT* contain any custom objects.  This tablespace should have *ONLY* those objects that were part of this tablespace when the database was created, which is *BEFORE* any custom or application objects get created.  All application or custom objects should be in other tablespaces.  But, depending on who created objects in this database, it is possible that you have custom objects in this tablespace.  If you do, they should be moved to other tablespaces.

What does this query show you about objects in the SYSTEM tablespace?
select owner, segment_type, count(*) Qty
from dba_segments where tablespace_name = 'SYSTEM'
group by owner, segment_type
order by owner, segment_type;

Also, please tell us which Oracle version you have.
0
 
LVL 13

Assisted Solution

by:riazpk
riazpk earned 167 total points
ID: 34894103
I use following script to find out free tablespace:
column dummy noprint
column  pct_used format 999.9       heading "%|Used"
column  name    format a16      heading "Tablespace Name"
column  Kbytes   format 999,999,999    heading "KBytes"
column  used    format 999,999,999   heading "Used"
column  free    format 999,999,999  heading "Free"
column  largest    format 999,999,999  heading "Largest"
break   on report
compute sum of kbytes on report
compute sum of free on report
compute sum of used on report

select nvl(b.tablespace_name,
             nvl(a.tablespace_name,'UNKOWN')) name,
       kbytes_alloc kbytes,
       kbytes_alloc-nvl(kbytes_free,0) used,
       nvl(kbytes_free,0) free,
       ((kbytes_alloc-nvl(kbytes_free,0))/
                          kbytes_alloc)*100 pct_used,
       nvl(largest,0) largest
from ( select sum(bytes)/1024 Kbytes_free,
              max(bytes)/1024 largest,
              tablespace_name
       from  sys.dba_free_space
       group by tablespace_name ) a,
     ( select sum(bytes)/1024 Kbytes_alloc,
              tablespace_name
       from sys.dba_data_files
       group by tablespace_name
	union all
	select sum(bytes)/1024 Kbytes_alloc,
              tablespace_name
       from sys.dba_temp_files
       group by tablespace_name )b
where a.tablespace_name (+) = b.tablespace_name
order by 1
/

Open in new window

0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

FreeBSD on EC2 FreeBSD (https://www.freebsd.org) is a robust Unix-like operating system that has been around for many years. FreeBSD is available on Amazon EC2 through Amazon Machine Images (AMIs) provided by FreeBSD developer and security office…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
In a previous video, we went over how to export a DynamoDB table into Amazon S3.  In this video, we show how to load the export from S3 into a DynamoDB table.

743 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now