| Tablespace resize [message #171128] |
Mon, 08 May 2006 10:07  |
burned
Messages: 1
Registered: May 2006
Location: Bulgaria
|
Junior Member |
|
|
I use Oracle 10gR2 with ASM.I have one Tablespace about 300Gb defined as bigtablespace with one datafile that contains one big partitioned table, partioned by range (day of months).When i drop old partition i get free space in Tablespace about 100Gb, but i could't resize tablespace to lower size becouse i need this 100gb to create one new tablespace.I can't drop table and recreate becouse i have live loading every 5-10 min in currently day partitions.
please provide me some solutions that i can lower HWM on table and resize tablespace.
i use this script to check lower size of tablespace my TS block size is 8192K
select file_name,
ceil( (nvl(hwm,1)*8192)/1024/1024 ) smallest,
ceil( blocks*8192/1024/1024) currsize,
ceil( blocks*8192/1024/1024) -
ceil( (nvl(hwm,1)*8192)/1024/1024 ) savings
from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm
from dba_extents
group by file_id ) b
where a.file_id = b.file_id(+)
and a.TABLESPACE_NAME like '&TS'
order by file_name
best regards
|
|
|
|
| Re: Tablespace resize [message #171337 is a reply to message #171128] |
Tue, 09 May 2006 07:54  |
asif_oracle
Messages: 2
Registered: April 2006
|
Junior Member |
|
|
Hi,
Using DBMS_SPACE find out exactly how much space is waster in all the segments
SQL> DBMS_SPACE.SPACE_USAGE(segment_owner => 'scott',
segment_name => 'EMP',
segment_type => 'TABLE',
...........);
Ensure that the table is row-movement enabled.
SQL> Atler table emp enable row movement;
You can reorganize the existing rows of the table with:
SQL> Alter table emp shrink space compact;
Reset the HWM
SQL> Alter table emp shrink space;
Regards,
Asif
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
