Comments by Gareth Barlow (NBNi) (Top 1 by date)

Wont work on oracle but you can use
"select * from user_tables" for schema level tables or

"select * from all_tables where schema_name = 'schema_name'" to get tables from a different schema