Initrans is most important for concurrent inserts in the same blocks of a table. You normally recognize this when there is contention for the block headers. As long as there is enough free space in the block, list can grow in the free space of the block. So if you know that for some tables you have many concurrent jobs inserting data in the same end of the table, it will help to set the INITRANS to a value similar to that of the number of concurrent jobs.
pct_used means that - in your case - as soon as a block gets 4% space free, that block will get to the free list to accept new rows.
pct_free means that as soon the free space in a block falls below pct_free, the block is no longer accepting new rows, in order to keep space for updates where rows can grow.
The table statistics show a statistic like AVG_SPACE, AVG_ROW_LEN.
You are receiving an Oracle ORA-03297 error because the HWM (High Water Mark) of a table is beyond the size you tried to shrink a datafile to.
First try and "shrink space" for each affected table:
alter table fragmentedtable enable row movement;
alter table fragmentedtable shrink space;
Next, check to see how much space can be freed from each of the existing tablespace files without moving the current database objects.
The following (taken from Ask Tom & assuming a block size of 8k) tells you how much space you can currently free from each datafile, in megabytes:
select file_name,
ceil( (nvl(hwm,1)*8192)/1024/1024 ) SHRINK_TO,
ceil( blocks*8192/1024/1024) CURRENT_SIZE,
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(+)
For example:
FILE_NAME SHRINK_TO CURRENT_SIZE SAVINGS
-------------------------------------------------- ---------- ------------ ----------
/u01/app/oracle/oradata/PHIL112/system01.dbf 696 700 4
/u01/app/oracle/oradata/PHIL112/sysaux01.dbf 579 610 31
/u01/app/oracle/oradata/PHIL112/users01.dbf 85 93 8
/u01/app/oracle/oradata/PHIL112/undotbs01.dbf 65 120 55
SQL>
To resize, use:
alter database datafile '/path/to/data/file.dbf' resize 100m;
The next option you have is to create a new tablespace (and associated datafile(s)) and move the current database objects to the new tablespace. You can do this by using datapump, or do it manually. Tim Hall has an excellent article on how to perform this here.
Best Answer
You can execute below query.
DROP TABLESPACE xxx_undo_01 INCLUDING CONTENTS AND DATAFILES;