| reclaim unused space for partitioned tables [message #360386] |
Thu, 20 November 2008 15:36  |
kumar.joy
Messages: 69
Registered: August 2007
Location: morrisville
|
Member |
|
|
Experts,
i want to find how much unused pace for a partitioned table and indexes. and i need to reclaim that space.
how can i do that.
currently we are dropping old partition. will it release entire space that partition is occupied?
could you please provide me any document for this. i am using dbms_space package for partitioned tables. i am getting some errors.
|
|
|
|
|
|
| Re: reclaim unused space for partitioned tables [message #360626 is a reply to message #360447] |
Fri, 21 November 2008 12:09  |
kumar.joy
Messages: 69
Registered: August 2007
Location: morrisville
|
Member |
|
|
here is the error message i am getting. IN THE SEGMENT NAME PARAMETER I HAVE USED TABLE NAME FIRST. AFTER I HAVE CHANGED PARTITION NAME. i am getting same problem. please guide me
SYSTEM> declare
2 l_fs1_bytes number;
3 l_fs2_bytes number;
4 l_fs3_bytes number;
5 l_fs4_bytes number;
6 l_fs1_blocks number;
7 l_fs2_blocks number;
8 l_fs3_blocks number;
9 l_fs4_blocks number;
10 l_full_bytes number;
11 l_full_blocks number;
12 l_unformatted_bytes number;
13 l_unformatted_blocks number;
14 begin
15 dbms_space.space_usage(
16 segment_owner => 'CDSVZW',
17 segment_name => 'RAWTXN',
18 segment_type => 'TABLE PARTITION',
19 fs1_bytes => l_fs1_bytes,
20 fs1_blocks => l_fs1_blocks,
21 fs2_bytes => l_fs2_bytes,
22 fs2_blocks => l_fs2_blocks,
23 fs3_bytes => l_fs3_bytes,
24 fs3_blocks => l_fs3_blocks,
25 fs4_bytes => l_fs4_bytes,
26 fs4_blocks => l_fs4_blocks,
27 full_bytes => l_full_bytes,
28 full_blocks => l_full_blocks,
29 unformatted_blocks => l_unformatted_blocks,
30 unformatted_bytes => l_unformatted_bytes
31 );
32 dbms_output.put_line(' FS1 Blocks = '||l_fs1_blocks||' Bytes = '||l_fs1_bytes);
33 dbms_output.put_line(' FS2 Blocks = '||l_fs2_blocks||' Bytes = '||l_fs2_bytes);
34 dbms_output.put_line(' FS3 Blocks = '||l_fs3_blocks||' Bytes = '||l_fs3_bytes);
35 dbms_output.put_line(' FS4 Blocks = '||l_fs4_blocks||' Bytes = '||l_fs4_bytes);
36 dbms_output.put_line('Full Blocks = '||l_full_blocks||' Bytes = '||l_full_bytes);
37 end;
38 /
declare
*
ERROR at line 1:
ORA-03205: partition name is required when partitioned type is specified
ORA-06512: at "SYS.DBMS_SPACE", line 175
ORA-06512: at line 15
==============================================
SEGMENT REPORT
Segment
Name PARTITION_NAME SEGMENT_TYPE
---------- --------------- --------------------
RAWTXN RAWTXN_200701 TABLE PARTITION
RAWTXN RAWTXN_200702 TABLE PARTITION
RAWTXN RAWTXN_200703 TABLE PARTITION
RAWTXN RAWTXN_200704 TABLE PARTITION
RAWTXN RAWTXN_200705 TABLE PARTITION
RAWTXN RAWTXN_200706 TABLE PARTITION
RAWTXN RAWTXN_200707 TABLE PARTITION
RAWTXN RAWTXN_200708 TABLE PARTITION
RAWTXN RAWTXN_200709 TABLE PARTITION
RAWTXN RAWTXN_200710 TABLE PARTITION
RAWTXN RAWTXN_200711 TABLE PARTITION
RAWTXN RAWTXN_200712 TABLE PARTITION
RAWTXN RAWTXN_200801 TABLE PARTITION
RAWTXN RAWTXN_200802 TABLE PARTITION
RAWTXN RAWTXN_200803 TABLE PARTITION
RAWTXN RAWTXN_200804 TABLE PARTITION
RAWTXN RAWTXN_200805 TABLE PARTITION
RAWTXN RAWTXN_200806 TABLE PARTITION
RAWTXN RAWTXN_200807 TABLE PARTITION
RAWTXN RAWTXN_200808 TABLE PARTITION
RAWTXN RAWTXN_200809 TABLE PARTITION
RAWTXN RAWTXN_200810 TABLE PARTITION
RAWTXN RAWTXN_200811 TABLE PARTITION
RAWTXN RAWTXN_200812 TABLE PARTITION
RAWTXN RAWTXN_MAX TABLE PARTITION
|
|
|
|
|
|
| Re: reclaim unused space for partitioned tables [message #362711 is a reply to message #360627] |
Wed, 03 December 2008 19:49 |
kumar.joy
Messages: 69
Registered: August 2007
Location: morrisville
|
Member |
|
|
Michel,
same error i am getting.
declare
2 l_fs1_bytes number;
3 l_fs2_bytes number;
4 l_fs3_bytes number;
5 l_fs4_bytes number;
6 l_fs1_blocks number;
7 l_fs2_blocks number;
8 l_fs3_blocks number;
9 l_fs4_blocks number;
10 l_full_bytes number;
11 l_full_blocks number;
12 l_unformatted_bytes number;
13 l_unformatted_blocks number;
14 begin
15 dbms_space.space_usage(
16 segment_owner => 'CDSVZW',
17 segment_name => 'RAWTXN_200802',
18 segment_type => 'TABLE PARTITION',
19 fs1_bytes => l_fs1_bytes,
20 fs1_blocks => l_fs1_blocks,
21 fs2_bytes => l_fs2_bytes,
22 fs2_blocks => l_fs2_blocks,
23 fs3_bytes => l_fs3_bytes,
24 fs3_blocks => l_fs3_blocks,
25 fs4_bytes => l_fs4_bytes,
26 fs4_blocks => l_fs4_blocks,
27 full_bytes => l_full_bytes,
28 full_blocks => l_full_blocks,
29 unformatted_blocks => l_unformatted_blocks,
30 unformatted_bytes => l_unformatted_bytes
31 );
32 dbms_output.put_line(' FS1 Blocks = '||l_fs1_blocks||' Bytes = '||l_fs1_bytes);
33 dbms_output.put_line(' FS2 Blocks = '||l_fs2_blocks||' Bytes = '||l_fs2_bytes);
34 dbms_output.put_line(' FS3 Blocks = '||l_fs3_blocks||' Bytes = '||l_fs3_bytes);
35 dbms_output.put_line(' FS4 Blocks = '||l_fs4_blocks||' Bytes = '||l_fs4_bytes);
36 dbms_output.put_line('Full Blocks = '||l_full_blocks||' Bytes = '||l_full_bytes);
37 end;
38 /
declare
*
ERROR at line 1:
ORA-03205: partition name is required when partitioned type is specified
ORA-06512: at "SYS.DBMS_SPACE", line 175
ORA-06512: at line 15
|
|
|
|
| Re: reclaim unused space for partitioned tables [message #362723 is a reply to message #362711] |
Thu, 04 December 2008 00:12 |
 |
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
And same answer:
| Michel Cadot wrote on Fri, 21 November 2008 12:19 | The message is clear if type is "TABLE PARTITION" then a partition must be given.
So do it.
If you want for each partition then repeat for each partition.
Regards
Michel
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
