Re: size of a table

From: DA Morgan <damorgan_at_psoug.org>
Date: Thu, 06 Jul 2006 12:23:53 -0700
Message-ID: <1152213836.284466@bubbleator.drizzle.com>

Prasath wrote:
> How do we find out the size of a table in bytes?

Use the dbms_space.object_space_usage pipelined table function.

There is a full demo in Morgan's Library (www.psoug.org).

Also available in dbms_space are the following PTFs that will help. FREE_BLOCKS

OBJECT_GROWTH_TREND
OBJECT_GROWTH_TREND_CUR
OBJECT_GROWTH_TREND_SWRF
OBJECT_SPACE_USAGE_TBF

SPACE_USAGE

UNUSED_SPACE
VERIFY_SHRINK_CANDIDATE
VERIFY_SHRINK_CANDIDATE_TBF

Everyone: If you haven't tried the GROWTH_TREND pipelined table functions they are worth the price of admission. Here's a typical output from one of my tables:

TIMEPOINT                                SPACE_USAGE SPACE_ALLOC QUALITY
---------------------------------------- ----------- ----------- 
------------
17-JUN-06 12.22.16.156000 PM                  324799      524288 
INTERPOLATED
18-JUN-06 12.22.16.156000 PM                  324799      524288 
INTERPOLATED
19-JUN-06 12.22.16.156000 PM                  324799      524288 
INTERPOLATED
20-JUN-06 12.22.16.156000 PM                  324799      524288 
INTERPOLATED
21-JUN-06 12.22.16.156000 PM                  324799      524288 
INTERPOLATED
22-JUN-06 12.22.16.156000 PM                  324799      524288 
INTERPOLATED
23-JUN-06 12.22.16.156000 PM                  324799      524288 
INTERPOLATED
24-JUN-06 12.22.16.156000 PM                  324799      524288 
INTERPOLATED
25-JUN-06 12.22.16.156000 PM                  324799      524288 
INTERPOLATED
26-JUN-06 12.22.16.156000 PM                  324799      524288 
INTERPOLATED
27-JUN-06 12.22.16.156000 PM                  324799      524288 
INTERPOLATED
28-JUN-06 12.22.16.156000 PM                  324799      524288 
INTERPOLATED
29-JUN-06 12.22.16.156000 PM                  324799      524288 
INTERPOLATED
30-JUN-06 12.22.16.156000 PM                  307016      524288 
INTERPOLATED
01-JUL-06 12.22.16.156000 PM                  322342      524288 
INTERPOLATED
02-JUL-06 12.22.16.156000 PM                  323921      524288 
INTERPOLATED
03-JUL-06 12.22.16.156000 PM                  324523      524288 
INTERPOLATED
04-JUL-06 12.22.16.156000 PM                  325864      524288 
INTERPOLATED
05-JUL-06 12.22.16.156000 PM                  325864      524288 
INTERPOLATED
06-JUL-06 12.22.16.156000 PM                  325864      524288 
INTERPOLATED
07-JUL-06 12.22.16.156000 PM                  325802      524288 PROJECTED
08-JUL-06 12.22.16.156000 PM                  325741      524288 PROJECTED
09-JUL-06 12.22.16.156000 PM                  325679      524288 PROJECTED
10-JUL-06 12.22.16.156000 PM                  325617      524288 PROJECTED
11-JUL-06 12.22.16.156000 PM                  325556      524288 PROJECTED

Yes PROJECTED. It projects space requirements into the future.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Thu Jul 06 2006 - 14:23:53 CDT