Solved

Capacity Planning and Query

Posted on 2002-04-08
5
970 Views
Last Modified: 2012-06-21
Calculating Tablespace Size using Query

I normally use tools to view space utilization and free-space left in database. I have to work for 'Capacity Planning', I have around 50 database and have to find the 'space utilization' for all the databases.

1. Can i get a query which tells me the total space 'used' by all the tablespaces in database and total 'free-space' left in all the tablespaces for current database.

2. What are the other factors i have to work for Capacity Planning, Any suggestions/ideas most welcome.

Thanks
Daman
0
Comment
Question by:Daman
5 Comments
 
LVL 3

Expert Comment

by:FlamingSword
ID: 6926815
Generally, count disk space
a) create file 10 rows by 10 columns
b) check disk space left
c) one row one column
d) check

e) continue for
 100 rows 10 columns
 10 rows 100 columns
100 rows 100 columns

f) go off to plot this out.
g) run a few more to smooth out the plot, as necessary

Capacity can also include totals, such as max for fields, tables, rows, columns, numbers, and networking bandwidth.
0
 
LVL 3

Expert Comment

by:FlamingSword
ID: 6926822
> and free-space left in database

use good design, compression, and you don't have free space.

eg, when I say delete, I mean delete, not hide empty space
0
 
LVL 9

Accepted Solution

by:
dbalaski earned 50 total points
ID: 6930415
I completely disagree with Flamingsword

Even databases with outstanding designs and excellent usage get freespace issues and have storage issues.


For an overall approach to space management,  I use the following script (see the end of the message)

It is pretty self exclaimitory.

Sincerely,
dBalaski


$ cat  space_report2.sql
set linesize 132
set pagesize 64
spool Space_reports.txt
SET SHOWMODE OFF TIMING OFF TIME OFF TERM OFF DOCUMENT ON;
rem
rem SpaceReports.sql
rem ---------------------------------------------------------------------------
rem written by:  darryl dB Balaski
rem www.rdbms.org
rem ---------------------------------------------------------------------------
rem Modification and Revision History
rem date         by      Description
rem 2/1/97       dB      create script
rem 5/1/98       dB      consolidated all other major scripts into this script
rem ---------------------------------------------------------------------------
rem
SET NULL "" TAB OFF TRIM ON ECHO OFF VERIFY OFF SCAN ON SQLPROMPT '';
clear columns
clear screen
set term on;
DOCUMENT
   This series of reports show space and extent utilization of the
   database and all objects in the database.

Report Series 1:   Tablespace Usage Report
This report lists each tablespace and the Percentage of space that is currently.

Report Series 2:   Tablespace Fragmentation Summary Report
This report is a summary list of block/extent fragmentation in the Tablespace. .

Report Series 3:   Product Object Space Report
This report lists the sum. in bytes, of all objects in a tablespace by owner.

Report Series 4:   Owner Table Extent Used Report
This report creates a listing of all objects (by owner) that will not be able t.

Report Series 5:   Table Extent Usage Report
Report Series 6:   Index Extent Usage Report
Both these reports detail the Space utilization of each object in the database,.
#
rem--------------------------------------------------------
rem Report Series 1  Tablespace Percentage Summary Report
rem
rem
set newpage 0
column PCT_USED format 990D00
column TODAY    new_value xTODAY noprint format a1 trunc
set echo off
ttitle center 'Percent Tablespace Used Report' right xTODAY skip 2
clear breaks
clear computes
break on tablespace skip 1
select substr(d.tablespace_name,1,25) TABLESPACE,
        to_char(sysdate,'fmMonth,ddth,yyyy') "TODAY",
       D.FILE_ID FILE_ID,
       D.BYTES/1024/1024  TOT_MBs,
       d.bytes/vp.value  ORACLE_BLKS,
       sum(e.blocks)  TOT_USED,
       round(sum(e.blocks)/(d.bytes/vp.value),4) *100 PCT_USED
from sys.dba_extents e,
     sys.dba_data_files d,
     v$parameter vp
where d.file_id = e.file_id (+)
  and vp.name ='db_block_size'
group by d.tablespace_name, d.file_id, d.bytes,vp.value
/
clear breaks
clear computes
rem
rem
rem--------------------------------------------------------
rem Report Series 2  Tablespace Fragmentation Summary Report
rem
rem
ttitle center 'Tablespace Fragmentation Summary Report' right xTODAY skip 2
clear breaks
clear computes
SELECT    SUBSTR(ts.name,1,25)                          "TSPACE",
          to_char(tf.blocks,'99,999,999')               "BLOCKS",
          to_char(SUM(f.length),'99,999,999')           "FREE",
          to_char(COUNT(*),'999,999')                   "PIECES",
          to_char(MAX(f.length),'999,999,999')          "BIGGEST",
          to_char(MIN(f.length),'999,999,999')           "SMALLEST",
          to_char(ROUND(AVG(f.length)),'999,999,999')   "AVERAGE",
          to_char(SUM(DECODE(SIGN(f.length-5), -1, f.length, 0)),'99,999')  "DE"
FROM      sys.fet$           F,
          sys.file$          TF,
          sys.ts$            TS
WHERE     ts.ts#  =  f.ts#
 AND      ts.ts#  =  tf.ts#
GROUP BY  ts.name, tf.blocks
/
rem--------------------------------------------------------
rem Report Series 3  Tablespace Usage By Owner Report
rem
rem
ttitle center 'Tablespace Usage by Owner Report' right xTODAY skip 2
clear breaks
clear computes
break on owner skip 2
rem
select substr(owner,1,20)                       "Owner",
       substr(tablespace_name,1,20)             "Tablespace_name",
       substr(segment_type,1,15)                "Segment_type",
       to_char(sum(bytes),'999,999,999,999')    "Bytes"
  from sys.dba_extents
 group by substr(owner,1,20),
          substr(Tablespace_name,1,20),
          substr(segment_type,1,15)
/
rem--------------------------------------------------------
rem Report Series 4  Owner Table Extent Used Report
rem
rem
col segment_name heading "Segment" format a25
col segment_type heading "SegType" format a12
col tablespace_name heading "Tspace" format a12
col bytes heading "Bytes" format 999,999,999,999
col blocks heading "Blocks" format 999,999,999
col initial_extent heading "Initial" format 999,999,999
col next_extent heading "Next" format 999,999,999
clear breaks
clear computes
ttitle center 'Owner/Table Able to Extend Report' right xTODAY skip 2
rem
set heading off
select 'Checking Tables owned by all users'||
       ' Report will show any object not able to allocate a next extent'||
       ' due to a lack of tablespace free space.'
from DUAL;
set heading on
select segment_name, segment_type,tablespace_name,
       bytes,blocks,initial_extent, next_extent
from sys.dba_segments s
where not exists (select 'x' from sys.dba_free_space f
                where   s.tablespace_name = f.tablespace_name
                and     f.bytes >= (s.next_extent *(1 +(s.pct_increase/100))))
 and s.owner NOT IN ('SYS', 'SYSTEM')
order by segment_name, segment_type, tablespace_name
/
rem
rem--------------------------------------------------------
rem
rem--------------------------------------------------------
rem Report Series 5  Table Extents Used Report
rem
rem
clear breaks
clear computes
col segment_name format a25
col owner format a9
ttitle center 'Table Extents Used Report' right xTODAY skip 2
break on owner skip 1
select  s.owner,
        s.segment_name,
        substr(s.segment_type,1,1),
        TO_CHAR(s.extents,'9,999') "Ext",
        TO_CHAR(s.max_extents,'99,999') "MaxExt",
        to_char(s.Blocks,'9,999,999') "Blocks",
        to_char(s.bytes,'9,999,999,999') "Bytes",
        to_char(s.initial_extent, '999,999,999') "Initial",
        to_char(s.next_extent,  '999,999,999') "Next",
        to_char(t.PCT_used,  '999') "Used",
        to_char(t.PCT_free,  '999') "Free"
from sys.dba_segments s, sys.dba_tables t
where s.owner not in ('SYS','SYSTEM')
and   s.segment_type = 'TABLE'
and   s.segment_name = t.table_name
order by s.owner, s.segment_name
/
rem
rem--------------------------------------------------------
rem Report Series 6 Index Extents Used Report
rem
rem
clear breaks
clear computes
col segment_name format a40
col owner format a10
ttitle center 'Index Extents Used Report' right xTODAY skip 2
break on owner skip 1
select  owner,
        segment_name,
        substr(segment_type,1,1),
        TO_CHAR(extents,'9,999') "Ext",
        TO_CHAR(max_extents,'9,999') "MaxExt",
        to_char(Blocks,'999,999') "Blocks",
        to_char(bytes,'9,999,999,999') "Bytes",
        to_char(initial_extent, '99,999,999') "Initial",
        to_char(next_extent,  '99,999,999') "Next"
from sys.dba_segments
where owner not in ('SYS','SYSTEM')
and   segment_type = 'INDEX'
order by owner, segment_name
/
spool off
exit
0
 
LVL 54

Expert Comment

by:nico5038
ID: 7265662

No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
 - Answered by: dbalaski  
Please leave any comments here within the
next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER !

Nic;o)
0
 
LVL 5

Expert Comment

by:Netminder
ID: 7296624
Per recommendation, force-accepted.

Netminder
CS Moderator
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
MYSQL Record Number 3 54
understanding output of mysql version 2 24
compre toata in where clue oracle 4 42
Slow SQL query 12 26
I guess that all of us know that caching the data usually increase the performance, but I worried if all of us are aware about the risk that caching the data provides and how to minimize this.  That’s the reason why I decided to write this short art…
Creating and Managing Databases with phpMyAdmin in cPanel.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

757 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

19 Experts available now in Live!

Get 1:1 Help Now