Oracle - 如何获得特定 ROW 的实际大小？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5871369/
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
Oracle - How do I get the actual size of a specific ROW?
提问by danboh
Is this possible? Or at least I'm looking to have a list of the size of all rows in a table.
这可能吗？或者至少我希望有一个表格中所有行的大小列表。
回答by bpgergo
select vsize(col1) + vsize(col2) + vsize(col3) +
long_raw_length_function(long_col) + DBMS_LOB.GETLENGTH(blob_col)
from table
where id_col = id_val;
for the long_raw_length_function, see this Get the LENGTH of a LONG RAW
对于long_raw_length_function，请参阅此Get the LENGTH of a LONG RAW
回答by Vincent Malgrat
if you're interested in the average row length, you could analyze the table (with the DBMS_STATS package), then query ALL_TABLES.avg_row_len.
如果您对平均行长度感兴趣，您可以分析表（使用 DBMS_STATS 包），然后查询ALL_TABLES.avg_row_len.
回答by user2590687
Here below is the query I have modified to get the table row length when you don't have any data. This can help you with Capacity planning for Environment setup:
下面是我修改过的查询，用于在您没有任何数据时获取表行长度。这可以帮助您进行环境设置的容量规划：
SET serveroutput ON linesize 300
DECLARE
v_max_size NUMBER := 0;
v_owner VARCHAR2(30);
v_table_name VARCHAR2(30);
v_data_type VARCHAR2(30);
v_data_length NUMBER := 0;
v_data_precision NUMBER := 0;
CURSOR CUR_TABLE
IS
SELECT DISTINCT table_name
FROM all_tab_columns
WHERE owner='TMS_OWNER'
AND table_name NOT LIKE 'VIEW%'
ORDER BY table_name;
BEGIN
FOR Tab IN CUR_TABLE
LOOP
v_table_name := Tab.table_name;
v_max_size := 0;
FOR i IN
(SELECT owner,
table_name,
data_type,
data_length,
data_precision
FROM all_tab_columns
WHERE owner ='TMS_OWNER'
AND table_name = v_table_name
)
LOOP
IF i.data_type = 'NUMBER' THEN
v_max_size := (v_max_size + i.data_precision);
ELSE
v_max_size := (v_max_size + i.data_length);
END IF;
END LOOP;
dbms_output.put_line(chr(10));
dbms_output.put_line('Table ='||v_table_name||', Max Record Size = '||v_max_size||' bytes');
END LOOP;
END;
/
