database 如何知道 Oracle 中的表是否存在碎片？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6342540/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I know if a table in Oracle is fragmented?
提问by Stuart Woodward
I have a table which seems to be taking up more space than it needs to. I have been advised to copy the data to a new table and rename the new table and old table to swap them. How can I confirm if a current table is actually fragmented? How can I estimate or calculate the new size of the fresh table containing the same data?
我有一张桌子，它似乎占用了比实际需要更多的空间。有人建议我将数据复制到新表并重命名新表和旧表以交换它们。如何确认当前表是否确实存在碎片？如何估计或计算包含相同数据的新表的新大小？
采纳答案by Gary Myers
If your stats are up to date, this should give a decent indication if tables have lots more blocks than the volume of row data suggests.
如果您的统计数据是最新的，如果表的块比行数据量建议的多得多，这应该会给出一个不错的指示。
select table_name, round((num_rows * avg_row_len) /(8*1024)), blocks
from user_tables where ....
This space would be used for future inserts, so isn't necessarily a problem. If you've done a large archive or delete of data, it maybe worth reclaiming space (especially if you do lots of full table scans). [Note: I've assumed 8k blocks, which are the default.]
这个空间将用于未来的插入，所以不一定是一个问题。如果您已经完成了大量数据的归档或删除，那么回收空间可能是值得的（尤其是在您进行了大量全表扫描的情况下）。[注意：我假设了 8k 块，这是默认值。]
If you do a CREATE/DROP/RENAME you'll lose any indexes, constraints, grants (plus table comments if you use them).
如果您执行 CREATE/DROP/RENAME，您将丢失任何索引、约束、授权（如果使用它们，还会丢失表注释）。
You are better off checking the current tablespace (look in USER_SEGMENTS) and doing an ALTER TABLE tablename MOVE current_tablespace;
您最好检查当前表空间（查看 USER_SEGMENTS）并执行 ALTER TABLE tablename MOVE current_tablespace;
You will need to rebuild indexes aftwards too. Pick them from USER_INDEXES and do an ALTER INDEX ... REBUILD;
您还需要向后重建索引。从 USER_INDEXES 中选取它们并执行ALTER INDEX ... REBUILD;
回答by gpeche
Consider usingdbms_space.space_usageand other procedures in the dbms_spacepackage.
考虑使用dbms_space.space_usage和其他程序dbms_space包中的程序。
回答by UltraCommit
-- Try to run this script using svrmgrl connected as DBA
-- 尝试使用作为 DBA 连接的 svrmgrl 运行此脚本
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;
/
