Table size and column size

The table size has two meanings. One is the number of physical spaces allocated to a table, regardless of whether the space is used or not. You can query the number of bytes as follows:

SELECT segment_name, BYTES
FROM user_segments
WHERE segment_type = 'table ';

 

Space actually used by another table. Query: the maximum size of a row.

Analyze table emp compute statistics;
SELECT num_rows * avg_row_len
FROM user_tables
WHERE table_name = 'emp ';

 

What is the maximum size of a row in an Oracle table? The answer depends on the version of Oracle used. 8.0 and later versions are 4000 GB (each LOB4GB, each table can have a maximum of 1000 LOB columns ). For structured data, varchar2 can contain a maximum of 4000 bytes and char can contain a maximum of 2000*4000 = 1000 bytes.

Oracle 7.x, each table has a maximum of 254 columns and varchar2 has a maximum of 2000 bytes. Therefore, the total structured data is 508,000 bytes. Or each table can have at most one long type, 2 GB.

Query the minimum, average, and maximum sizes of a table column: The 'vsize' function cannot be used for LONG/BLOB columns. You can use 'dbms _ lob. getlength (blob-field )'.

Select min (VSIZE (c1), AVG (VSIZE (c1), MAX (VSIZE (c1 ))
FROM t1;