How to Estimate Table Sizes within Schema (Oracle)

oracle

I'm trying to estimate the table sizes within my schema (in MB). This is what I have so far:

SELECT table_name, owner, last_analyzed
FROM all_tables

I'm fairly new to SQL so I have no idea how I would go around doing this.

Thank you.

Best Answer

Look at the "dba_segments" view (or user_segments if you don't have dba rights). The following query should give you what you're looking for:

select
  owner as "Schema"
  , segment_name as "Object Name"
  , segment_type as "Object Type"
  , round(bytes/1024/1024,2) as "Object Size (Mb)"
  , tablespace_name as "Tablespace"
from dba_segments
order by owner;

Related Solutions

Sql-server – How to execute multiple DDL statements within a transaction in Oracle

You can issue multiple DDL statements in a single transaction using the CREATE SCHEMA command though you are limited to just the CREATE TABLE, CREATE VIEW, and GRANT statements.

CREATE SCHEMA AUTHORIZATION <<Oracle username>>
  CREATE TABLE a( col1 number, col2 number ) -- Note no semicolon
  CREATE TABLE b( col3 number, col4 number );

If you are trying to upgrade an existing database, depending on the Oracle edition and configuration, I would tend to suspect that you'd be better served with something like Oracle Flashback Database. If you create a guaranteed restore point before you start the upgrade, for example, you can simply flashback the database to that restore point if the upgrade fails regardless of how many transactions you've committed during the upgrade.

Oracle PL/SQL – Reference Table from Another Schema in a Package

From section "6 Coding PL/SQL Subprograms and Packages" in the "Oracle Database Advanced Application Developer's Guide, 11g Release 2 (11.2), E41502-05"

To create without errors (to compile the subprogram or package successfully) requires these additional privileges:
- The owner of the subprogram or package must be explicitly granted the necessary object privileges for all objects referenced within the body of the code.
- The owner cannot obtain required privileges through roles.

You must have granted select permission on this table to the schema that owns the package. It is important that this permissions are granted directly to the package owner and not indirect using a role. Using a role is sufficient to select the table directly by Toad but it is not sufficient if you want to select the table in a package.

Best Answer

Related Solutions

Sql-server – How to execute multiple DDL statements within a transaction in Oracle

Oracle PL/SQL – Reference Table from Another Schema in a Package

Related Question