I recently needed to find out the schema names of certain Oracle tables that belonged to schemas other than the connecting user. This is because the four-part queries in SQL Server require the schema/owner name of the tables referenced. This query (run in SQL*Plus) lists the owners and names of all tables that can be seen by the connecting user:
SELECT OWNER, TABLE_NAME FROM ALL_TABLES;
A full reference for the ALL_TABLES view can be found here.