Tablespace And Table Data Gathering

While the estat-bstat utilities are great for measuring memory usage, the DBA may still want to capture information about Oracle tables and tablespaces. For example, an alert report can easily be generated that shows all tables and indexes that have extended more than twice in the past 24 hours. A tablespace alert report could be written to display all tablespaces that have free space less than 10 percent of the tablespace size.

A routine can easily be written to interrogate all tablespaces and dump the information into a statistical table (Listing 11.7), and this tablespace information can be tracked to predict the rate of growth and the time when it will become necessary to add a data file to the tablespace.

Listing 11.7 A tablespace data gathering script.

INSERT INTO tablespace_stat VALUES (
SELECT   dfs.tablespace_name,
         round(sum(dfs.bytes)/1048576,2),
         round(max(dfs.bytes)/1048576,2)
FROM     sys.dba_free_space dfs
GROUP BY dfs.tablespace_name
ORDER BY dfs.tablespace_name);

Tables are usually tracked to provide information on unexpected growth. A high growth rate can indicate that the table was undersized on the next parameter, or it can indicate an upturn in end-user processing. In either case, the DBA wants to be informed whenever any table extends more than twice within any 24 hour period.

Gathering the table extent information is a simple matter, as shown in Listing 11.8. This script can be attached to a cron process to gather the extent information at a specified time interval.

Listing 11.8 A table data gathering script.

INSERT INTO tab_stat VALUES(
SELECT  ds.tablespace_name,
        dt.owner,
        dt.table_name,
        ds.bytes/1024,
        ds.extents,
        dt.max_extents,
        dt.initial_extent/1024,
        dt.next_extent/1024,
        dt.pct_increase,
        dt.pct_free,
        dt.pct_used
FROM    sys.dba_segments ds,
        sys.dba_tables dt
WHERE   ds.tablespace_name = dt.tablespace_name
AND   ds.owner = dt.owner
AND   ds.segment_name = dt.table_name
ORDER BY 1,2,3);