Home » SQL & PL/SQL » SQL & PL/SQL » Partition truncate not releasing space? (Oracle DB 10.2 RHEL 4)
Partition truncate not releasing space? [message #362315] Mon, 01 December 2008 14:55 Go to next message
TLegend33
Messages: 203
Registered: March 2005
Senior Member
Below is a list of partitions I'm archiving:
SQL> set line 120           
column segment_name format a30
select segment_name, partition_name, tablespace_name, bytes/1024/1024 from dba_segments where segment_name = 'ACTIVITY'
order by 2;  

SEGMENT_NAME                   PARTITION_NAME                 TABLESPACE_NAME                BYTES/1024/1024
------------------------------ ------------------------------ ------------------------------ ---------------
ACTIVITY                       P200712                        ACTIVITY                                1
ACTIVITY                       P200801                        ACTIVITY                             5440


Some time ago, I exported the data in the p200712 partition and it's subsequent size was reduced to 1M. Below I issue an identical query to truncate p200801 and get the below results:
SQL> alter table activity truncate partition P200801;

Table truncated.

SQL> set line 120           
column segment_name format a30
select segment_name, partition_name, tablespace_name, bytes/1024/1024 from dba_segments where segment_name = 'ACTIVITY'
order by 2;  

SEGMENT_NAME                   PARTITION_NAME                 TABLESPACE_NAME                BYTES/1024/1024
------------------------------ ------------------------------ ------------------------------ ---------------
ACTIVITY                       P200712                        ACTIVITY                                1
ACTIVITY                       P200801                        ACTIVITY                             4096


I don't understand why the truncate command issued does not reduce the space used by the partition p200801. This is an identical command I've used in the past. Is there an attribute that exists that only specifies a default minimum for partitions?
Re: Partition truncate not releasing space? [message #362318 is a reply to message #362315] Mon, 01 December 2008 15:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
"Truncate" truncates accordingly to minextents and tablespace allocation type.

Regards
Michel
Re: Partition truncate not releasing space? [message #362321 is a reply to message #362318] Mon, 01 December 2008 15:15 Go to previous messageGo to next message
TLegend33
Messages: 203
Registered: March 2005
Senior Member
I'm not sure I follow. What about the following output indicates that a truncated partition would be reduced to 4096M and not 1M?
SQL> select tablespace_name, min_extents, allocation_type from dba_tablespaces where tablespace_name = 'ACTIVITY';

TABLESPACE_NAME                MIN_EXTENTS ALLOCATIO
------------------------------ ----------- ---------
ACTIVITY                                 1 SYSTEM
Re: Partition truncate not releasing space? [message #362361 is a reply to message #362321] Tue, 02 December 2008 00:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to query min_extent of your partition (dba_tab_partitions).

Regards
Michel
Re: Partition truncate not releasing space? [message #362495 is a reply to message #362361] Tue, 02 December 2008 08:03 Go to previous messageGo to next message
TLegend33
Messages: 203
Registered: March 2005
Senior Member
Hi, below is the min_extent setting for this tables partitions:

SQL> select partition_name, min_extent from user_tab_partitions where table_name = 'ACTIVITY';


PARTITION_NAME                 MIN_EXTENT
------------------------------ ----------
P200712                                 1
P200801                                 1


Where can I find this setting of 4096M for these partitions?
Thanks.

[Updated on: Tue, 02 December 2008 08:04]

Report message to a moderator

Re: Partition truncate not releasing space? [message #362496 is a reply to message #362495] Tue, 02 December 2008 08:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is the INITIAL for the partition?

Regards
Michel
Re: Partition truncate not releasing space? [message #362502 is a reply to message #362496] Tue, 02 December 2008 08:35 Go to previous messageGo to next message
TLegend33
Messages: 203
Registered: March 2005
Senior Member
Ahhhhh...excellent. Thank you very much.
SQL> select partition_name, initial_extent/1024/1024 init_extent, min_extent from user_tab_partitions where table_name = 'ACTIVITY';

PARTITION_NAME                 INIT_EXTENT MIN_EXTENT
------------------------------ ----------- ----------
P200712                                  1          1
P200801                               4096          1


From what I'm finding, it appears I cannot modify the initial extent size for these partitions whose initial extent is 4096M.

SQL> alter table activity modify partition p200801 storage (initial 1M);
alter table activity modify partition p200801 storage (initial 1M)
                                                               *
ERROR at line 1:
ORA-02203: INITIAL storage options not allowed


Is there any way around this?
Thanks.
Re: Partition truncate not releasing space? [message #362504 is a reply to message #362502] Tue, 02 December 2008 08:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
INITIAL can't be changed, you recreate the partition.
You can use a new table with the same columns and appropriate storage parameters, then use alter table partition exchange and drop the table.

Regards
Michel
Re: Partition truncate not releasing space? [message #362506 is a reply to message #362504] Tue, 02 December 2008 08:43 Go to previous message
TLegend33
Messages: 203
Registered: March 2005
Senior Member
Ok, that's what I thought.
Thanks for your help.
Previous Topic: ORA-00904: invalid identifier on Production
Next Topic: Help on stored procedure
Goto Forum:
  


Current Time: Wed Dec 07 14:17:11 CST 2016

Total time taken to generate the page: 0.09136 seconds