CODEWITHSUNDEEP
oracle-databaseoracle10g
enkiv2
enkiv2

Reputation: 196

Finding tablespace size on oracle without dba_* metatables

I need to find the current tablespace size for a db in Oracle 10g. Looking around, I've found many scripts that determine size using tables like dba_extents, but the database I'm using has none of these dba_* tables.

(I'm using this as a proxy for finding table size, which is complicated because most of the table size is in blobs.)

Upvotes: 2

Views: 5653

Answers (2)

Shannon Severance
Shannon Severance

Reputation: 18410

The DBA_* views are part of the "Static Data Dictionary Views" Generally there are three versions of every view, DBA_ that shows everything, ALL_ that shows what you have access to, and USER_ that will show what you own, and will not have the OWNER column.

For example DBA_TABLES has all tables, ALL_TABLES are the table you can select from, and USER_TABLES are the tables you own.

The views are documented in "Static Data Dictionary Views". A good resource if you need to translate from DBA_ to ALL_ or USER_.

Unfortunately _EXTENTS only comes in a DBA and USER version. So if want information on objects you can access but do not own, you will need to ask your resident DBA for help.

Upvotes: 6

kfinity
kfinity

Reputation: 9091

The dba_* views are part of the data dictionary, which exists in every Oracle database. They're in the sys schema, and if you can't query them, it probably just means you don't have SELECT access to them.

If the table you want to check is in your schema, you can replace e.g. dba_extents with user_extents and the query should work.

Here's my suggestion for a script to tell the size of a table in your schema (indexes included):

select segment_name, segment_type, bytes/1024/1024 as size_in_mb
from user_segments
where segment_name = :YOUR_TABLE
  or segment_name in (select segment_name from user_lobs where table_name = :YOUR_TABLE)
  or segment_name in (select index_name from user_indexes where table_name = :YOUR_TABLE);

Upvotes: 1

Related Questions