m.r226 m.r226 - 4 months ago 18
SQL Question

List all tables of a given user in Oracle

I am new to Oracle and want to calculate the size of all tables created by 'john' user.
First off all i have to find all john's tables.

I connect to Oracle database via command line by the following command:

sqlplus john/passwd

I dont want to know how to calculate table size, but also i want to know
How do i list all tables created by a given user e.g. john?

Answer

To list the table you can use

SELECT * FROM ALL_TABLES WHERE OWNER = 'JOHN';

TO see the size of the schema you can use

SELECT sum(bytes)
  FROM dba_segments
 WHERE owner = 'JOHN'

Since you are logged in as the schema owner, you can also use

SELECT SUM(bytes)
  FROM user_segments