Toad World® Forums

Abfrage Tabellengröße und Anzahl der Datensätze pro Tabelle

Hallo,

ich suche eine Abfrage, in der ich die Größe der Tabellen eines Schemas und die Anzahl der Datensätze pro Tabelle auswerten kann.

Danke

Karsten

Karsten,

Dies könnte Ihnen, was Sie suchen:

http://databobjr.blogspot.com/2012/01/retrieve-oracle-table-size-number.html

-John

Diese können auch helfen:

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1660875645686

http://stackoverflow.com/questions/264914/how-do-i-calculate-tables-size-in-oracle

Hallo John,

haben Sie das denn mal ausprobiert ?

Bei mir wirft die Abfrage einen Fehler aus …
Toad_fehler_01022017.jpeg

http://translate.google.com tells me you’re asking for table sizes and row counts. This isn’t specific to Toad and there are many answers depending on your goals, how you plan to use this information, and how much system overhead you can incur. Here’s what I use to calculate table sizes:

SELECT
owner,
segment_name,
bytes
FROM all_segments
WHERE
tablespace_name = ‘MYTABLESPACE’
AND segment_type = ‘TABLE’;

…substituting your tablespace name for MYTABLESPACE. Other columns could be filtered instead, including OWNER.

Row counts can be expensive to calculate. The easiest approximation is to use the NUM_ROWS column of the ALL_TABLES view. But I use this query when I need to be more accurate:

SELECT
owner,
table_name,
TO_NUMBER(
EXTRACTVALUE(
xmltype(
DBMS_XMLGEN.getxml(‘select count(*) c from ‘||owner||’.’||table_name)
)
,’/ROWSET/ROW/C’)
) COUNT
FROM
dba_tables
WHERE owner LIKE ‘MYSCHEMAS%’;

If one has the expensive Enterprise license, the counts can optionally be done with a parallel query on “larger” tables, by substituting this line in the above script:

          dbms_xmlgen.getxml('select /*'||case when blocks > 64000 then '+' end||' parallel(8) */ count(*) c from '||owner||'.'||table_name)

The “64000” and “parallel(8)” need to be modified according to your environment in order to prevent the DBA from raging. Since I am the DBA and am running this in our reporting environment, the above works well for me.

HTH! GL!

Rich

Also , ich kann mir nur schwer vorstellen, dass ein so mächtiges Werkzeug wie TOAD keine dementsprechenden Aussagen treffen kan.

I have found it,

under /database/optimize/estimated table size are the informations.

Thx on all