Hi Tom
I have a question with regards to the resize of datafiles after a TABLE COMPRESSION and ALTER TABLE ... MOVE.
I am trying to claim space back from the datawarehouse after table compression on some very large tables in a tablespace. I use Oracle 10g on Solaris. I want to compress the tables within their own tablespace, contrary to what you suggest in the first follow-up post above.
Will the command 'ALTER TABLE X MOVE PARTITION Z',
after a table compression on X, bring down the HWM so that I can resize the datafiles and claim back space from the database thus reduce backup times, save space etc?
I have tested it like this:
1- As SYS I create the tablespace used in the test SQL> create tablespace app01 datafile '/u11/oradata/testdb1/app01.dbf' size 5M autoextend on ;
Tablespace created.
2- I create a Partitioned table called Y create table y parallel nologging partition by range (last_ddl_time)
(
partition y2000 values less than (to_date('01-01-2001', 'DD-MM-YYYY')) tablespace app01,
partition y2001 values less than (to_date('01-01-2002', 'DD-MM-YYYY')) tablespace app01,
partition y2002 values less than (to_date('01-01-2003', 'DD-MM-YYYY')) tablespace app01,
partition y2003 values less than (to_date('01-01-2004', 'DD-MM-YYYY')) tablespace app01,
partition y2004 values less than (to_date('01-01-2005', 'DD-MM-YYYY')) tablespace app01,
partition y2005 values less than (to_date('01-01-2006', 'DD-MM-YYYY')) tablespace app01,
partition y2006 values less than (to_date('01-01-2007', 'DD-MM-YYYY')) tablespace app01,
partition y2007 values less than (to_date('01-01-2008', 'DD-MM-YYYY')) tablespace app01
)
as
select * from dba_objects;
Table created.
Space after creation of the table.
TABLESPACE_NAME Total (MB) Free (MB) PCT_USED EXTENSIBLE Max Size (MB)
------------------------------ ----------- --------- --------- ---------- -------------
APP01 12 2 88 YES 32,768
3- I compress the table within the same tablespace alter table Y move partition Y2000 compress nologging parallel;
alter table Y move partition Y2001 compress nologging parallel;
alter table Y move partition Y2002 compress nologging parallel;
alter table Y move partition Y2003 compress nologging parallel;
alter table Y move partition Y2004 compress nologging parallel;
alter table Y move partition Y2005 compress nologging parallel;
alter table Y move partition Y2006 compress nologging parallel;
alter table Y move partition Y2007 compress nologging parallel;
Space after compression is done.
TABLESPACE_NAME Total (MB) Free (MB) PCT_USED EXTENSIBLE Max Size (MB)
------------------------------ ----------- --------- --------- ---------- -------------
APP01 16 12 24 YES 32,768
4- I issue the command ALTER TABLE... MOVE for each partition within the tablespace to reorganise extents within the segment. alter table Y move partition Y2001 ;
alter table Y move partition Y2002 ;
alter table Y move partition Y2003 ;
alter table Y move partition Y2004 ;
alter table Y move partition Y2005 ;
alter table Y move partition Y2006 ;
alter table Y move partition Y2007 ;
Space after the operation.
TABLESPACE_NAME Total (MB) Free (MB) PCT_USED EXTENSIBLE Max Size (MB)
------------------------------ ----------- --------- --------- ---------- -------------
APP01 16 11 29 YES 32,768
It seems that this resets the HWM, cause after I do this, I can resize the datafiles. How can I see this?
5- As SYS I check datafiles and resize if possible.
set lines 600
set pages 49999
column file_name format a100
SELECT fs.file_id , f.bytes, f.file_name
,sum(fs.bytes)/(1024*1024) free
FROM dba_free_space fs, dba_Data_files f
where fs.file_id=f.file_id
GROUP BY f.bytes, f.file_name, fs.file_id
order by 4 desc
/
FILE_ID BYTES FILE_NAME FREE
---------- ---------- ------------------------------- ----------
5 16252928 /u11/oradata/testdb1/app01.dbf 11
Resizing the datafile of app01.
SQL> alter database datafile '/u11/oradata/testdb1/app01.dbf' resize 6M;
Database altered.
FILE_ID BYTES FILE_NAME FREE
---------- ---------- ------------------------------ ----------
5 6291456 /u11/oradata/testdb1/app01.dbf 1.5
Conclusion: I have gained OS space after table compression, less disk usage in the system and faster backups!
My question to you briefly is:
Is it true that the step 4 above will always move the HWM down and will allow me to resize datafiles? If HWM is going down how can I see that?
Many Thanks!
Kubilay