BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

 

 
 

Monitoring Size of Tables

Oracle Tips by Burleson Consulting

One method to determine whether your default storage sizing is correct for a tablespace is to monitor the extents for each of the tables that reside in the tablespace. Another method is to monitor the used space against the available space for each table. Scripts to perform these functions are shown in Sources 10.2 and 10.3; Listing 10.2 shows the output from the report in Source 10.2.

SOURCE 10.2 SQL*Plus report to show extents for each table in each tablespace.

REM
REM NAME                : EXTENTS.SQL
REM FUNCTION            : GENERATE EXTENTS REPORT
REM USE                 : FROM SQLPLUS OR OTHER FRONT END
REM LIMITATIONS         : NONE
REM
CLEAR COLUMNS
COLUMN segment_name    HEADING 'Segment'     FORMAT A15
COLUMN tablespace_name HEADING 'Tablespace'  FORMAT A10
COLUMN owner           HEADING 'Owner'       FORMAT A10
COLUMN segment_type    HEADING 'Type'        FORMAT A10
COLUMN size            HEADING 'Size'         FORMAT 999,999,999
COLUMN extents         HEADING 'Current|Extents'
COLUMN max_extents     HEADING 'Max|Extents'
COLUMN bytes           HEADING 'Size|(Bytes)'
SET PAGESIZE 58 NEWPAGE 0 LINESIZE 130 FEEDBACK OFF
SET ECHO OFF VERIFY OFF
ACCEPT extents PROMPT 'Enter max number of extents: '
BREAK ON tablespace_name SKIP PAGE ON owner
START TITLE132 "Extents Report"
DEFINE output = rep_out\&db\extent
SPOOL &output
SELECT  tablespace_name,
     segment_name,
     extents,
     max_extents,
     bytes,
     owner "owner",
     segment_type
FROM DBA_segments
WHERE extents >= &extents AND owner LIKE UPPER('%&owner%')
ORDER BY tablespace_name,owner,segment_type,segment_name;
SPOOL OFF
CLEAR COLUMNS
CLEAR BREAKS
SET TERMOUT ON FEEDBACK ON VERIFY ON
UNDEF extents
UNDEF owner
TTITLE OFF
UNDEF OUTPUT
PAUSE Press enter to continue

LISTING 10.2 Example of output from the extents script.

Date: 06/12/97                                             Page:   1
Time: 09:55 PM              Extents Report                    SYSTEM
                           ORTEST1 database

                             Current     Max   Size                    
Tablespace Segment         Extents Extents (Bytes) Owner      Type
---------- --------------  ------- ------- ------- ---------  -------
SYSTEM     C_OBJ#               10     249 1323008 SYS        CLUSTER
           C_TOID_VERSION#       7     249  352256            CLUSTER
           I_ARGUMENT1           6     249  229376            INDEX
           I_COL1                8     249  565248            INDEX
           I_COL2                6     249  258048            INDEX
           I_COL3                5     249  176128            INDEX
           I_DEPENDENCY1         5     249  147456            INDEX                       I_DEPENDENCY2         5     249  147456            INDEX 
           I_OBJ2                5     249  147456            INDEX
           I_Source1            11     249 1765376            INDEX
           SYSTEM               16     249  983040            ROLLBACK
           ACCESS$               6     249  229376            TABLE
           ARGUMENT$             6     249  229376            TABLE
           COM$                  5     249  147456            TABLE
           DEPENDENCY$           5     249  147456            TABLE
           IDL_CHAR$             5     249  147456            TABLE
           IDL_SB4$              6     249  229376            TABLE
           IDL_UB1$              9     249  802816            TABLE                         IDL_UB2$             10     249 1191936            TABLE                        OBJ$                  6     249  229376            TABLE
           Source$              13     249 3915776            TABLE
           VIEW$                 9     249  802816           

SOURCE 10.3 Actual size report.

rem  *************************************************************
rem
rem  NAME: ACT_SIZE.sql
rem
rem  HISTORY:
rem  Date             Who              What
rem  ---------------  ---------------- -------------------------------
rem  09/??/90  Maurice C. Manton    Creation for IOUG
rem  12/23/92  Michael Brouillette  Assume TEMP_SIZE_TABLE exists.Use
rem  DBA info.
rem  Prompt for user name. Spool file = owner.
rem   07/15/96  Mike Ault Updated for Oracle 7.x, added indexes
rem   06/12/97  Mike Ault Updated for Oracle 8.x (use DBMS_ROWID)
rem  FUNCTION:  Will show actual blocks used vs allocated for all tables rem  for a user
rem  INPUTS:  owner = Table owner name.
rem  ************************************************************
ACCEPT owner PROMPT 'Enter table owner name: '
SET HEADING OFF FEEDBACK OFF VERIFY OFF ECHO OFF RECSEP OFF PAGES 0
COLUMN db_block_size NEW_VALUE blocksize NOPRINT
TTITLE OFF
DEFINE cr='chr(10)'
DEFINE qt='chr(39)'
TRUNCATE TABLE temp_size_table;
SELECT value db_block_size FROM v$parameter WHERE name='db_block_size';
SPOOL fill_sz.sql
SELECT
 'INSERT INTO temp_size_table'||&&cr||
 'SELECT '||&&qt||segment_name||&&qt||&&cr||
 ',COUNT(DISTINCT(dbms_rowid.rowid_block_number(rowid))) blocks'||&&cr||
 'FROM &&owner..'||segment_name, ';'
FROM
  DBA_segments
WHERE
  segment_type ='TABLE'
  AND owner = UPPER('&owner');
SPOOL OFF
SPOOL index_sz.sql
SELECT
    'CREATE TABLE stat_temp AS SELECT * FROM index_stats;'||&&cr||
    'TRUNCATE TABLE stat_temp;' 
FROM
    dual;
SELECT    
'ANALYZE INDEX '||owner||'.'||index_name||' VALIDATE STRUCTURE;'||&&cr||
'INSERT INTO stat_temp SELECT * FROM index_stats;'||&&cr||
'COMMIT;'
FROM
   DBA_indexes
WHERE
   owner=UPPER('&owner');
SPOOL OFF
SET FEEDBACK ON TERMOUT ON LINES 132
START index_sz.sql
INSERT INTO temp_size_table SELECT name,trunc(used_space/&&blocksize)
FROM stat_temp;
DROP TABLE stat_temp;
DEFINE temp_var = &&qt;
START fill_sz
HOST rm fill_size_table.sql
DEFINE bs = '&&blocksize K'
COLUMN t_date      NOPRINT NEW_VALUE t_date
COLUMN user_id     NOPRINT NEW_VALUE user_id
COLUMN segment_name      FORMAT A25         HEADING "SEGMENT|NAME"
COLUMN segment_type      FORMAT A7          HEADING "SEGMENT|TYPE"
COLUMN extents           FORMAT 999         HEADING "EXTENTS"
COLUMN kbytes            FORMAT 999,999,999 HEADING "KILOBYTES"
COLUMN blocks            FORMAT 9,999,999   HEADING "ALLOC.|&&bs|BLOCKS"
COLUMN act_blocks        FORMAT 9,999,990   HEADING "USED|&&bs|BLOCKS"
COLUMN pct_block         FORMAT 999.99      HEADING "PCT|BLOCKS|USED"
START title132 "Actual Size Report for &owner"
SET PAGES 55
BREAK ON REPORT ON segment_type SKIP 1
COMPUTE SUM OF kbytes ON segment_type REPORT
SPOOL rep_out\&db\&owner
SELECT
     segment_name,
     segment_type,
     SUM(extents) extents,
     SUM(bytes)/1024 kbytes,
     SUM(a.blocks) blocks,
     NVL(MAX(b.blocks),0) act_blocks,
    (MAX(b.blocks)/SUM(a.blocks))*100 pct_block
 FROM
     sys.DBA_segments a,
     temp_size_table b
 WHERE
     segment_name = UPPER( b.table_name )
 GROUP BY
     segment_name,
     segment_type
 ORDER BY
     segment_type,
     segment_name;
SPOOL OFF
TRUNCATE TABLE temp_size_table;
SET TERMOUT ON FEEDBACK 15 VERIFY ON PAGESIZE 20 LINESIZE 80 SPACE 1
UNDEF qt
UNDEF cr
TTITLE OFF
CLEAR COLUMNS
CLEAR COMPUTES
PAUSE press enter to continue

The script to calculate the actual size of a table or index (shown in Source 10.3) uses the TEMP_SIZE_TABLE, which is created with the script shown in Source 10.4. As shown, the act_size script will work only with Oracle8 and Oracle8i. To use act_size with Oracle7, replace the call to the dbms_rowid.rowid_block_number procedure with SUBSTR( ROWID,1,8). The act_size.sql report cannot resolve index-only overflow tables or complex objects involving nested tables. Output from the act_size report is shown in Listing 10.3.

SOURCE 10.4 Script to create TEMP_SIZE_TABLE.

rem
rem Create temp_size_table for use by actsize.sql
rem
CREATE TABLE temp_size_table (
     table_name VARCHAR2(64),
     blocks NUMBER);

LISTING 10.3 Example of output of actual size report.

Date: 06/12/97                                                               Page: 1     
Time: 11:28 PM               Actual Size Report for tele_DBA                 SYSTEM
                                      ORTEST1 database

                                                              ALLOC.       USED
PC
SEGMENT                    SEGMENT                            4096 K     4096 K
BLOCKS
NAME                       TYPE      EXTENTS    KILOBYTES     BLOCKS     BLOCKS
USED
-------------------------  -------   -------    ---------     ------     ------     ----
FK_ACCOUNT_EXECS_1         INDEX          1           12          3           0      .00
FK_ADDRESSES_1                            1       10,240      2,560           0      .00
FK_ADDRESSES_2                            1       51,200     12,800       2,480    19.38
FK_ADDRESSES_3                            1       51,200     12,800       2,967    23.18
FK_FRANCHISE_CODES_1                      1       10,240      2,560         461    18.01
FK_SIC_CODES_1                            1       51,200     12,800       3,893    30.41
FK_USERS_1                                1      102,400     25,600           0      .00
LI_LOAD_TEST                              1       40,960     10,240       5,536    54.06
OID_CLIENTSV8                             1            20          5          0      .00
OID_EARNINGS_INFO_NMBRS                   1            20          5          0      .00
...

PK_ADDRESSES                              1       102,400     25,600      5,203    20.32
PK_CLIENTS                                1       102,400     25,600      3,212    12.55
PK_EARNINGS_INFO_NMBRS                    1       102,400     25,600      2,780    10.86
PK_FRANCHISE_CODES                        1        51,200     12,800        573     4.48
PK_SIC_CODES                              1        51,200     12,800      4,863    37.99
UI_EARNINGS_INFO_NMBRS_ID                 1        51,200     12,800      4,466    34.89
UK_CLIENTS                                1        51,200     12,800      4,292    33.53
UK_LOAD_TEST                              1        51,200     12,800      4,650    36.33
                             *******            ---------
                             sum                1,116,564                              
                                             
ACCOUNT_EXECS                TABLE        1            12          3          0      .00
ADDRESSES                                 1       204,800     51,200     32,827    64.12
ADDRESS_TEST                              1            20          5          1    20.00
CLIENTS                                   2       307,200     76,800     61,587    80.19
....
EARNINGS_INFO_NMBRS                       1       204,800     51,200     28,485    55.63
EARNINGS_INFO_NUMBERSV8                   1        20,480      5,120          0      .00
EMPLOYEES                                 1            20          5          0      .00
FRANCHISE_CODES                           1        76,800     19,200        803     4.18
INTERACTION_LOG_ACTIVITY                  1            12          3          0      .00
LOAD_TEST                                 3       615,420    153,855    140,441    91.28
LOOKUPS                                   1            12          3          0      .00
SIC_CODES                                 1       102,400     25,600     16,765    65.49
USERS                                     1       204,800     51,200          1      .00
                             *******            ---------
                             sum                2,036,056

                                                -------
                             sum                3,152,620

Each of the above reports gives specific information. In the report from Source 10.2, if a table shows more than 1,000 extents, the DBA should review its size usage via the report in Source 10.3, and rebuild the table with better storage parameters. In the report in Listing 10.3, if a table shows that it is using far less space than it has been allocated, and history has shown it won’t grow into the space, it should be re-created accordingly.


See Code Depot for Full Scripts


This is an excerpt from Mike Ault, bestselling author of "Oracle 10g Grid and Real Application Clusters".

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.

Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.

 

   
 

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.



Hit Counter