List existing databases and tables in Oracle

Oracle has no "databases" but "schemas", you can list them with:

SELECT username FROM all_users ORDER BY username;

Or with:

SELECT username, account_status FROM dba_users ORDER BY 1;

Or with:

select distinct username from dba_objects;

Or with:

SELECT DISTINCT owner FROM dba_objects ORDER BY 1;

When connected to oracle you'll use by default the schema corresponding to your username (connecting as SCOTT all objects created by you will belong to SCOTT's schema) and you'll also be able to use objects in different schemas that you've been granted rights on. Say you are SYSTEM and you want to read all entries from table A that resides in SCOTT's schema, you'll write something like:

SELECT * FROM SCOTT.A;

You can also list existing tables with:

SELECT owner, table_name FROM dba_tables;

Or if you do not have access to DBA_TABLES, you can see all the tables that your account has access to through the ALL_TABLES view:

SELECT owner, table_name FROM all_tables;

If you are only concerned with the tables that you own, not those that you have access to, you could use USER_TABLES

SELECT table_name FROM user_tables;

Since USER_TABLES only has information about the tables that you own, it does not have an OWNER column-- the owner, by definition, is you.

Oracle also has a number of legacy data dictionary views-- TAB, DICT, TABS, and CAT for example-- that could be used. In general, I would not suggest using these legacy views unless you absolutely need to backport your scripts to Oracle 6. Oracle has not changed these views in a long time so they often have problems with newer types of objects. For example, the TAB and CAT views both show information about tables that are in the user's recycle bin while the [DBA|ALL|USER]_TABLES views all filter those out. CAT also shows information about materialized view logs with a TABLE_TYPE of "TABLE" which is unlikely to be what you really want. DICT combines tables and synonyms and doesn't tell you who owns the object.