czwartek, 7 kwietnia 2011

How to count size od tables in Oracle Spatial?

Generally:

SELECT round((bytes/1024/1024/1024),1) size_GB
FROM dba_segments
WHERE owner = <OWNER_NAME> and segment_name = <TABLE_NAME>;

Use the above select for:
1) Table itself.

2) Indexes for the table (put them as <TABLE_NAME> in the above statement). You can retrieve them as follows:

2.1) Indexes:

SELECT index_name
FROM all_indexes
WHERE table_owner = <OWNER_NAME>and table_name = <TABLE_NAME>;

2.2) Spatial indexes:

SELECT sdo_index_table
FROM all_sdo_index_info
WHERE table_owner = <OWNER_NAME>and table_name = <TABLE_NAME>;