I've got a table which appears to become trying out more room than it must. I've been advised copying the information to a different table and relabel the brand new table and old table to swap them. How do i confirm if your current table is really fragmented? How do i estimate or calculate the brand new size the new table that contains exactly the same data?

hi this link will let you. click here.

In case your stats are current, this will provide a decent indication if tables have much more blocks compared to amount of row data indicates.

select table_name, round((num_rows * avg_row_len) /(8*1024)), blocks 
from user_tables where ....

This space could be employed for future card inserts, so is not always an issue. If you have done a sizable archive or remove of information, it may be worth claiming back space (particularly if you do plenty of full table scans). [Note: I have assumed 8k blocks, what are default.]

Should you perform a CREATE/DROP/Relabel you'll lose any indexes, constraints, grants or loans (plus table comments if you are using them).

You're best checking the present tablespace (try looking in USER_SEGMENTS) and doing an ALTER TABLE tablename MOVE current_tablespace;

You will have to rebuild indexes aftwards too. Pick them from USER_INDEXES and do an ALTER INDEX ... REBUILD;

-- Attempt to run this script using svrmgrl connected as DBA

set serveroutput on

   libcac   NUMBER (6, 2);
   rowcac   NUMBER (6, 2);
   bufcac   NUMBER (6, 2);
   redlog   NUMBER (6, 2);
   spsize   NUMBER;
   blkbuf   NUMBER;
   logbuf   NUMBER;
     INTO redlog
     FROM v$sysstat
    WHERE name = 'redo log space requests';

   SELECT 100 * (SUM (pins) - SUM (reloads)) / SUM (pins)
     INTO libcac
     FROM v$librarycache;

   SELECT 100 * (SUM (gets) - SUM (getmisses)) / SUM (gets)
     INTO rowcac
     FROM v$rowcache;

   SELECT 100 * (cur.VALUE con.VALUE - phys.VALUE)/(cur.VALUE con.VALUE)
into bufcac
from v$sysstat cur,v$sysstat con,v$sysstat phys,
v$statname ncu,v$statname nco,v$statname nph
where cur.statistic# = ncu.statistic# and
ncu.name = 'db block gets' and
con.statistic# = nco.statistic# and
nco.name = 'consistent gets' and
phys.statistic# = nph.statistic# and
nph.name = 'physical reads';

select VALUE
into spsize
from v$parameter
where name = 'shared_pool_size';

select VALUE
into blkbuf
from v$parameter
where name = 'db_block_buffers';

select VALUE
into logbuf
from v$parameter
where name = 'log_buffer';

DBMS_OUTPUT.put_line('> ********************');
DBMS_OUTPUT.put_line('> SQL Cache Hit rate = '||libcac);
DBMS_OUTPUT.put_line('> Dict Cache Hit rate = '||rowcac);
DBMS_OUTPUT.put_line('> Buffer Cache Hit rate = '||bufcac);
DBMS_OUTPUT.put_line('> Redo Log space requests = '||redlog);
DBMS_OUTPUT.put_line('> ');
DBMS_OUTPUT.put_line('> ****************');
DBMS_OUTPUT.put_line('> Shared Pool Size = '||spsize||' Bytes');
DBMS_OUTPUT.put_line('> DB Block Buffer = '||blkbuf||' Blocks');
DBMS_OUTPUT.put_line('> Log Buffer = '||logbuf||' Bytes');
DBMS_OUTPUT.put_line('> ');

if libcac < 99
DBMS_OUTPUT.put_line('*** HINT: Library Cache too low! Increase the Shared Pool Size.');
end if;

if rowcac < 85
DBMS_OUTPUT.put_line('*** HINT: Row Cache too low! Increase the Shared Pool Size.');
end if;

if bufcac < 90
DBMS_OUTPUT.put_line('*** HINT: Buffer Cache too low! Increase the DB Block Buffer value.');
end if;

if redlog > 100
DBMS_OUTPUT.put_line('*** HINT: Log Buffer value is rather low!');
end if;


Think about using[cde] along with other methods within the dbms_space.space_usage package.