9.6 How do I…Deallocate unused space in a segment?

Problem

I suspect that the initial extent for some of my database objects is much too large. Also, the value of PCTINCREASE for some tables is so large it’s likely that Oracle8 has allocated much more space than necessary to these tables. How can I find out whether there is unused space allocated to some objects in the database? If there is unused space, how can I transform the wasted space back into free space?

Technique

The UNUSED_SPACE procedure in the DBMS_SPACE package reveals some segment information absent from the Oracle8 data dictionary. Table 9.4 summarizes this procedure’s input and output parameters. This How-To uses this procedure to return the total space and the unused space allocated to a segment.

Parameter Name Parameter Type Parameter Description
segment_owner IN Owner of the target segment
segment_name IN Name of the target segment
segment_type IN Type of the segment to be analyzed (TABLE, INDEX, or CLUSTER)
total_blocks OUT Total number of blocks in the segment
total_bytes OUT Total number of bytes in the segment
unused_blocks OUT Number of blocks not used in the segment
unused_bytes OUT Number of bytes not used in the segment
last_used_extent_file_id OUT File ID of the last extent that contains data
last_used_extent_block_id OUT Block ID of the last extent containing data
last_used_block OUT Last block within the last extent containing data
partition_name IN Partition name of the target segment

The ALTER TABLE statement with the DEALLOCATE option can dynamically return unused space in database segments. Use this statement after the UNUSED_SPACE procedure indicates that some segments are larger than necessary.

Steps

1. Run SQL*Plus and connect as the WAITE user. Use the START command to load and execute the script CHP9_9.SQL. The script appears in List-ing 9.3.

Listing 9.3 The script CHP9_9.SQL creates a nested table

2 DROP TABLE dept09;

3 DROP TYPE emp_t_table09;

4 DROP TYPE emp_t09;


5 CREATE TYPE emp_t09 AS OBJECT

6 (empno number(4),

7 ename varchar2(10)

8 );

9 CREATE TYPE emp_t_table09 AS TABLE OF emp_t09;

10 CREATE TABLE dept09

11 (

12 deptno number(2),

13 dname varchar2(14),

14 emps emp_t_table09

15 )

16 NESTED TABLE emps STORE AS dept_emps09

17 STORAGE

18 (INITIAL 50k

19 NEXT 50k

20 );

21 INSERT INTO dept09 VALUES

22 (

23 1, 'IT', emp_t_table09(emp_t09(1, 'JOE')

24 emp_t09(2, 'JANE'))

25 );

This script creates a table called DEPT09 that contains a nested table column called EMPS (read more about nested tables in Chapter 16). These tables are similar to the EMP and DEPT tables traditionally found in the schema SCOTT.

The preceding DDL contains a new twist on these common tables using the nested table capability of the Oracle8 server. Lines 5 through 7 create an object type called EMP_T09 to describe an employee. Line 9 creates a table of the newly defined EMP_T09 type. Lines 10 through 20 create the DEPT09 table, which stores departments and, using the nested table, the employees who work in each department. Line 16 specifies the storage table to hold the nested table EMPS. This is an Oracle8 segment that users cannot query directly, although it does have its own physical storage parameters. In this case, the storage clause in lines 17 through 20 specifies 50KB for the size of the initial and next extents of the table DEPT. The storage table, EMPS09, inherits the default storage parameters for the tablespace. In lines 21 through 25, a single record is inserted into table DEPT for the Information Technology department. This department currently has two employees: Joe and Jane.

2. Although it is unlikely that the one now in the DEPT09 table has consumed all 50KB of the initial extent, the data dictionary does not contain information indicating how much space that single row requires. The script CHP9_10.SQL creates a stored procedure called SEGSPACE, which can reveal how much segment space is used and how much is free in any data segment; the script appears in Listing 9.4. Use the START command to load and execute the CHP9_10.SQL script.

Listing 9.4 The script CHP9_10.SQL creates the SEGSPACE procedure

1 CREATE OR REPLACE PROCEDURE segspace

2 (owner IN varchar2,

3 name IN varchar2,

4 type IN varchar2

5 )

6 IS

7 freespace number(10);

8 totalspace number(10);

9 dummyn number(10);

10 using number(10);

11 needs number(10);

12 BEGIN

13 dbms_space.unused_space(segment_owner => owner,

14 segment_name => name,

15 segment_type => type,

16 total_blocks => dummyn,

17 total_bytes => totalspace,

18 unused_blocks => dummyn,

19 unused_bytes => freespace,

20 last_used_extent_file_id => dummyn,

21 last_used_extent_block_id => dummyn,

22 last_used_block => dummyn,

23 partition_name => null);

24 using := round(totalspace/1024, 0);

25 needs := round((totalspace-freespace)/1024, 0);

26 dbms_output.put_line ('object ' || name || ' is allocated

' ||

27 to_char(using) || 'k and is using ' ||

28 to_char(needs) || 'k');

29 END;

SEGSPACE calls the UNUSED_SPACE procedure in lines 13 through 23, using named notation to pass the appropriate parameters. Lines 24 through 26 convert the values returned by the UNUSED_SPACE procedure from bytes to kilobytes. The PUT_LINE procedure that appears in lines 26 through 28 displays the object’s name, how much space the object is using, and how much space is free.

3. Use the START command to load and execute the SEGSPACE procedure for the database objects you want to query for space consumption. Figure 9.14 contains the results of running script CHP9_11.SQL, which runs the SEGSPACE procedure against the DEPT09 table and the DEPT_EMPS09 storage table.

Notice that the type passed to the SEGSPACE procedure is TABLE for the DEPT_EMPS09 storage table and for the DEPT09 table.

4. Use the ALTER TABLESPACE DEALLOCATE UNUSED command to free allocated but unused space. The script CHP9_12.SQL contains two invocations of this command, one for the DEPT09 table and one for the DEPT_EMPS09 storage table. The script and its output appear in Figure 9.15.

5. Run the UNUSED_SPACE procedure again for the resized tables. Figure 9.16 contains the results of the CHP9_11.SQL script when it runs after the deallocation operation in Step 4.

How It Works

Steps 2 and 3 summarize creation and use of the SEGSPACE procedure, which acts as a parameterized interface to the UNUSED_SPACE procedure in the Oracle-supplied package_DBMS_SPACE. Step 4 deallocates unused segment space using the ALTER TABLESPACE command with the DEALLOCATE option.

Comments

The alternative to the DEALLOCATE OPTION of the ALTER_TABLESPACE command is to re-create the table with a more appropriate storage clause or to create tablespaces with storage clauses tailored for the tablespace’s planned contents. These are admirable goals, but even the most watchful DBA occasionally sizes a table incorrectly. The technique in the How-To provides a much more straightforward method of deallocating unused segment space if it becomes necessary to adjust a table in this way. Note that without the KEEP option in Step 4, the size of the initial extent or space allocated via the number of minimum extents is preserved throughout a deallocation attempt.