Shulou(Shulou.com)05/31 Report--

This article mainly explains "how big is the table in Oracle database". The content in the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "how big is the table in Oracle database".

1. View the size of a general table

Select segment_name,segment_type,bytes/1024/1024 M from dba_segments where segment_name='SETTINGS$' and segment_type='TABLE'

two。 View the size of a table with a LOB field

The LOB field has an additional special area to store. Viewing the size of the table with LOB field is more complex, which is divided into three parts: normal field size, LOB field size, LOB index field size.

SELECT (SELECT SUM (S.BYTES/1024/1024/1024)-The Table Segment size FROM DBA_SEGMENTS S WHERE S.OWNER = UPPER ('owner' of the table) AND (S.SEGMENT_NAME = UPPER (' table name')) + (SELECT SUM (S.BYTES/1024/1024/1024)-The Lob Segment Size FROM DBA_SEGMENTS S DBA_LOBS L WHERE S.OWNER = UPPER ('owner' of the table) AND (L.SEGMENT_NAME = S.SEGMENT_NAME AND L.TABLE_NAME = UPPER (' table name') AND L.OWNER = UPPER ('table owner') + (SELECT SUM (S.BYTES/1024/1024/1024)-The Lob Index size FROM DBA_SEGMENTS S DBA_INDEXES I WHERE S.OWNER = UPPER ('owner' of the table) AND (I. INDEX_NAME = S.SEGMENT_NAME AND I.TABLE_NAME = UPPER (' table name') AND INDEX_TYPE= 'LOB' AND I.OWNER = UPPER (' table owner') "TOTAL TABLE SIZE" FROM DUAL

For more information, please see Doc ID 118531.1.

How many tables with LOB fields are there in this library if you want to see them? You can view it through dba_lobs.

For example: select distinct owner,table_name from dba_lobs where owner in ('* *,'* *)

If you find hundreds of tables with thousands of LOB fields and check the size one by one, it will take a long time.

How to list a table with a LOB field at one time?

First create a temporary table:

SQL > create table candidates (owner varchar2 (30), segment_name varchar2 (81))

Then insert the table with the LOB field found above into the temporary table:

SQL > insert into candidates values ('owner','table_name')

Then use the cursor:

SQL > set serveroutput on SQL > declare cursor cur_temp is select owner, segment_name from candidates; v_owner varchar2 (30); v_segment_name varchar2 (81); v_total_table_size number; begin open cur_temp; fetch cur_temp into While cur_temp%FOUND loop SELECT (SELECT nvl (SUM (S.BYTES/1024/1024/1024), 0) FROM DBA_SEGMENTS S WHERE S.OWNER = UPPER (v_owner) AND (S.SEGMENT_NAME = UPPER (v_segment_name) + (SELECT nvl (SUM (S.BYTES/1024/1024/1024), 0) FROM DBA_SEGMENTS S DBA_LOBS L WHERE S.OWNER = UPPER (v_owner) AND (L.SEGMENT_NAME = S.SEGMENT_NAME AND L.TABLE_NAME = UPPER (v_segment_name) AND L.OWNER = UPPER (v_owner)) + (SELECT nvl (SUM (S.BYTES/1024/1024/1024), 0) FROM DBA_SEGMENTS S DBA_INDEXES I WHERE S.OWNER = UPPER (v_owner) AND (I.INDEX_NAME = S.SEGMENT_NAME AND I.TABLE_NAME = UPPER (v_segment_name) AND INDEX_TYPE= 'LOB' AND I.OWNER = UPPER (v_owner)) "TOTAL TABLE SIZE" into v_total_table_size FROM DUAL DBMS_OUTPUT.PUT_LINE ('Table' | | v_segment_name | |':'| | v_total_table_size); fetch cur_temp into vandalism ownerdirection vantage segmentationname; end loop; end; /

The table size with the LOB field is listed.

In general, only dba_segments is checked for size, but not much for LOB. Just check it occasionally with the method of step 2, which is also provided by the government.

Thank you for your reading, the above is the content of "how big is the table in Oracle database". After the study of this article, I believe you have a deeper understanding of how big the table in Oracle database is, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.