SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
SQL> insert into test_chained_table
2 select object_id, rpad(object_name,1000,' ') from dba_objects;
17392 rows created.
SQL> commit;
Commit complete.
SQL> select sum(bytes) from user_extents where segment_name='TEST_CHAINED_TABLE';
SUM(BYTES)
----------
75497472
SQL>
SQL> set serveroutput on
SQL> @show_space_table.sql
SQL> DECLARE
2 p_segname VARCHAR2 (30);
3 p_owner VARCHAR2 (30) DEFAULT USER;
4 p_type VARCHAR2 (30) DEFAULT 'TABLE';
5 p_partition VARCHAR2 (30) DEFAULT NULL;
6 l_free_blks NUMBER;
7 l_total_blocks NUMBER;
8 l_total_bytes NUMBER;
9 l_unused_blocks NUMBER;
10 l_unused_bytes NUMBER;
11 l_lastusedextfileid NUMBER;
12 l_lastusedextblockid NUMBER;
13 l_last_used_block NUMBER;
14 l_segment_space_mgmt VARCHAR2 (255);
15 l_unformatted_blocks NUMBER;
16 l_unformatted_bytes NUMBER;
17 l_fs1_blocks NUMBER;
18 l_fs1_bytes NUMBER;
19 l_fs2_blocks NUMBER;
20 l_fs2_bytes NUMBER;
21 l_fs3_blocks NUMBER;
22 l_fs3_bytes NUMBER;
23 l_fs4_blocks NUMBER;
24 l_fs4_bytes NUMBER;
25 l_full_blocks NUMBER;
26 l_full_bytes NUMBER;
27
28 PROCEDURE p (p_label IN VARCHAR2, p_num IN NUMBER)
29 IS
30 BEGIN
31 DBMS_OUTPUT.put_line ( RPAD (p_label, 40, '.')
32 || TO_CHAR (p_num, '999,999,999,999')
33 );
34 END;
35 BEGIN
36 p_segname := 'TEST_CHAINED_TABLE';
37 p_owner := 'DONGHUA';
38 p_type := 'TABLE';
39
40 IF p_partition IS NOT NULL
41 THEN
42 SELECT ts.segment_space_management
43 INTO l_segment_space_mgmt
44 FROM dba_segments seg, dba_tablespaces ts
45 WHERE seg.segment_name = p_segname
46 AND seg.partition_name = p_partition
47 AND seg.owner = p_owner
48 AND seg.tablespace_name = ts.tablespace_name;
49 ELSE
50 SELECT ts.segment_space_management
51 INTO l_segment_space_mgmt
52 FROM dba_segments seg, dba_tablespaces ts
53 WHERE seg.segment_name = p_segname
54 AND seg.owner = p_owner
55 AND seg.tablespace_name = ts.tablespace_name;
56 END IF;
57
58 IF l_segment_space_mgmt = 'AUTO'
59 THEN
60 DBMS_SPACE.space_usage (p_owner,
61 p_segname,
62 p_type,
63 l_unformatted_blocks,
64 l_unformatted_bytes,
65 l_fs1_blocks,
66 l_fs1_bytes,
67 l_fs2_blocks,
68 l_fs2_bytes,
69 l_fs3_blocks,
70 l_fs3_bytes,
71 l_fs4_blocks,
72 l_fs4_bytes,
73 l_full_blocks,
74 l_full_bytes,
75 p_partition
76 );
77 --
78 p ('Unformatted Blocks ', l_unformatted_blocks);
79 p ('FS1 Blocks (0-25) ', l_fs1_blocks);
80 p ('FS2 Blocks (25-50) ', l_fs2_blocks);
81 p ('FS3 Blocks (50-75) ', l_fs3_blocks);
82 p ('FS4 Blocks (75-100)', l_fs4_blocks);
83 p ('Full Blocks ', l_full_blocks);
84 ELSE
85 DBMS_SPACE.free_blocks (segment_owner => p_owner,
86 segment_name => p_segname,
87 segment_type => p_type,
88 freelist_group_id => 0,
89 free_blks => l_free_blks,
90 partition_name => p_partition
91 );
92 --
93 p ('Free Blocks', l_free_blks);
94 END IF;
95
96 DBMS_SPACE.unused_space (segment_owner => p_owner,
97 segment_name => p_segname,
98 segment_type => p_type,
99 partition_name => p_partition,
100 total_blocks => l_total_blocks,
101 total_bytes => l_total_bytes,
102 unused_blocks => l_unused_blocks,
103 unused_bytes => l_unused_bytes,
104 last_used_extent_file_id => l_lastusedextfileid,
105 last_used_extent_block_id => l_lastusedextblockid,
106 last_used_block => l_last_used_block
107 );
108 p ('Total Blocks', l_total_blocks);
109 p ('Total Bytes', l_total_bytes);
110 p ('Total MBytes', TRUNC (l_total_bytes / 1024 / 1024));
111 p ('Unused Blocks', l_unused_blocks);
112 p ('Unused Bytes', l_unused_bytes);
113 p ('Last Used Ext FileId', l_lastusedextfileid);
114 p ('Last Used Ext BlockId', l_lastusedextblockid);
115 p ('Last Used Block', l_last_used_block);
116 END;
117 /
Unformatted Blocks ..................... 748
FS1 Blocks (0-25) ..................... 1
FS2 Blocks (25-50) ..................... 1
FS3 Blocks (50-75) ..................... 0
FS4 Blocks (75-100)..................... 60
Full Blocks ..................... 8,267
Total Blocks............................ 9,216
Total Bytes............................. 75,497,472
Total MBytes............................ 72
Unused Blocks........................... 0
Unused Bytes............................ 0
Last Used Ext FileId.................... 4
Last Used Ext BlockId................... 2,688
Last Used Block......................... 1,024
PL/SQL procedure successfully completed.
SQL>
SQL> delete from test_chained_table where mod(id,3)=0;
11589 rows deleted.
SQL> commit;
Commit complete.
SQL> set echo off
SQL> @show_space_table.sql
Unformatted Blocks ..................... 748
FS1 Blocks (0-25) ..................... 0
FS2 Blocks (25-50) ..................... 7,742
FS3 Blocks (50-75) ..................... 254
FS4 Blocks (75-100)..................... 62
Full Blocks ..................... 271
Total Blocks............................ 9,216
Total Bytes............................. 75,497,472
Total MBytes............................ 72
Unused Blocks........................... 0
Unused Bytes............................ 0
Last Used Ext FileId.................... 4
Last Used Ext BlockId................... 2,688
Last Used Block......................... 1,024
PL/SQL procedure successfully completed.
SQL> select sum(bytes) from user_extents where segment_name='TEST_CHAINED_TABLE';
SUM(BYTES)
----------
75497472
SQL> alter table test_chained_table shrink space compact;
alter table test_chained_table shrink space compact
*
ERROR at line 1:
ORA-10636: ROW MOVEMENT is not enabled
SQL> alter table test_chained_table enable row movement;
Table altered.
SQL> alter table test_chained_table shrink space compact;
Table altered.
SQL> select sum(bytes) from user_extents where segment_name='TEST_CHAINED_TABLE';
SUM(BYTES)
----------
75497472
SQL> @show_space_table.sql
Unformatted Blocks ..................... 748
FS1 Blocks (0-25) ..................... 0
FS2 Blocks (25-50) ..................... 1
FS3 Blocks (50-75) ..................... 0
FS4 Blocks (75-100)..................... 3,169
Full Blocks ..................... 5,159
Total Blocks............................ 9,216
Total Bytes............................. 75,497,472
Total MBytes............................ 72
Unused Blocks........................... 0
Unused Bytes............................ 0
Last Used Ext FileId.................... 4
Last Used Ext BlockId................... 2,688
Last Used Block......................... 1,024
PL/SQL procedure successfully completed.
SQL> alter table test_chained_table shrink space;
Table altered.
SQL> select sum(bytes) from user_extents where segment_name='TEST_CHAINED_TABLE';
SUM(BYTES)
----------
43057152
SQL> @show_space_table.sql
Unformatted Blocks ..................... 0
FS1 Blocks (0-25) ..................... 0
FS2 Blocks (25-50) ..................... 1
FS3 Blocks (50-75) ..................... 0
FS4 Blocks (75-100)..................... 0
Full Blocks ..................... 5,159
Total Blocks............................ 5,256
Total Bytes............................. 43,057,152
Total MBytes............................ 41
Unused Blocks........................... 4
Unused Bytes............................ 32,768
Last Used Ext FileId.................... 4
Last Used Ext BlockId................... 11,648
Last Used Block......................... 4
PL/SQL procedure successfully completed.
SQL> alter table test_chained_table disable row movement;
Table altered.
SQL>
Sunday, November 14, 2010
Claim freespace using shrink and shrink space compact
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment