我有一张表格看起来占用的空间比需要的更多。有人建议我将数据复制到一个新表中并重命名新旧表以互换它们。如何确认当前的表确实是碎片化的?如何估计或计算包含相同数据的新表的新大小?
我有一张表格看起来占用的空间比需要的更多。有人建议我将数据复制到一个新表中并重命名新旧表以互换它们。如何确认当前的表确实是碎片化的?如何估计或计算包含相同数据的新表的新大小?
select table_name, round((num_rows * avg_row_len) /(8*1024)), blocks
from user_tables where ....
这个空间将用于未来插入,所以并不一定是问题。如果您对数据进行了大量的存档或删除,可能值得回收空间(特别是如果您执行了大量全表扫描)。[注意:我假设使用默认的8k块。]
如果您执行CREATE/DROP/RENAME操作,您将失去任何索引、约束、授权(以及如果您使用的话,表注释)。
最好检查当前表空间(在USER_SEGMENTS中查找),然后执行ALTER TABLE tablename MOVE current_tablespace;
之后还需要重建索引。从USER_INDEXES中选择它们,然后执行ALTER INDEX ... REBUILD;
set serveroutput on
DECLARE
libcac NUMBER (6, 2);
rowcac NUMBER (6, 2);
bufcac NUMBER (6, 2);
redlog NUMBER (6, 2);
spsize NUMBER;
blkbuf NUMBER;
logbuf NUMBER;
BEGIN
SELECT VALUE
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('> SGA CACHE STATISTICS');
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('> INIT.ORA SETTING');
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
then
DBMS_OUTPUT.put_line('*** HINT: Library Cache too low! Increase the Shared Pool Size.');
end if;
if rowcac < 85
then
DBMS_OUTPUT.put_line('*** HINT: Row Cache too low! Increase the Shared Pool Size.');
end if;
if bufcac < 90
then
DBMS_OUTPUT.put_line('*** HINT: Buffer Cache too low! Increase the DB Block Buffer value.');
end if;
if redlog > 100
then
DBMS_OUTPUT.put_line('*** HINT: Log Buffer value is rather low!');
end if;
end;
/