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?
Add a comment
|
5 Answers
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).)
1 Comment
William Robertson
If John owns any object tables (which he probably doesn't, as nobody uses them), you would need to query
ALL_ALL_TABLES to get everything.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
Comments
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
Comments
select * from
USER_TABLES;
The above code will show all the information of the tables under the user which is currently connected. This might clutter your SQL terminal.
To Specifically see only the table names under a user you should use the following code
select table_name
from USER_TABLES;
Comments
Try:
select *
from all_tables
where owner = 'jhon';
4 Comments
Francesco Serra
Quite strange ... it's an oracle db? Try: select * from user_tables; but in this way you can see only your tables
Rezaeimh7
SQL> select * from all_tables where owner= 'john'; no rows selected
Francesco Serra
Probably john has not defined any table! Try select * from all_tables; without any where conditions.
MT0
@m.r226 Use upper case:
'JOHN'