List all tables of a given user in Oracle

Question

I am new to Oracle and want to find all tables created by user 'john' .

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

sqlplus  john/passwd

How do i list all the tables created by a given user e.g. john?

Answer 1

This will get all the tables where the "JOHN" user is the owner:

SELECT * FROM USER_TABLES;

or

SELECT * FROM ALL_TABLES WHERE OWNER = 'JOHN';

([TL;DR] 'JOHN' typically needs to be in upper-case. Assuming that the user john was created using the CREATE USER john ... statement then Oracle's default behaviour is to convert all object names (i.e. tables, columns, users, etc) to upper case. When you query the data-dictionary the table details will be stored in this case (and not the case you used in the original command unless you wrap it in double quotes).)

Answer 2

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

Answer 3

You can use also

select * from 
USER_TABLES;

anyway you can find all the data dictionary explain here https://docs.oracle.com/cd/B28359_01/server.111/b28310/tables014.htm