[Update 2016-02] Since SQL Developer is free and powerful, there's not much sense in using any of the following tools. Toad may be the strongest contender. See what people say about their pros and cons.
Die-hard command line geeks may not like GUI tools such as Quest Software's Toad for Oracle to access a database. But these tools offer convenience that sqlplus can only achieve after a few or even tens of seconds of typing on the keyboard. The Referential tab of Schema Browser in Toad is an example; it shows all the parent and child tables of the current table in one screen. Unfortunately, unless you pay at least $870 for one seat, the latest free version Toad has very many limitations. The most fatal one is the limit of 2 simultaneous users in the same database;[note] if you're the 3rd user trying to login, you'll be kicked out before you can see anything in Toad.
How does Toad find out you're the third user? Toad.exe has a Win32 PE module called TOADSEATCOUNT that, upon user login, runs two SQLs like these, if it can:
Select count(*) from v$session where (upper(module) = 'T╖O╖A╖D╖') or (instr(upper(module), 'TOAD FREEWARE') > 0)
begin sys.dbms_application_info.set_module('TOAD Freeware 8.5.0.50', null); end;
(╖ is ascii 183. Old versions of Toad set DBMS application module string to 'T╖O╖A╖D╖' while new versions set it to 'TOAD FREEWARE' followed by version.) There's nothing you can do about the second SQL, which is always executed successfully. If you revoked execute on dbms_application_info from public, users would have all kinds of problems (see Note:247093.1). Fortunately, you can prevent Toad from successfully running the first SQL. Simply disallow the user you login as from selecting on v$session. Specifically, don't login as a user that is explicitly granted select privilege on sys.v_$session, select_catalog_role, select any dictionary privilege, or dba role. (If you must login with this privilege or role, simply revoke it from sqlplus first, login with Toad, and then temporarily grant it from your sqlplus session. But Toad seems to periodically check v$session so you may be kicked out after some time.)
Interestingly, very old versions of Toad no longer expire in spite of the warning such as "This freeware version of TOAD expires on 1-Aug-2002" under About -> Help. My Toad 6.3 freeware version allows me to select synonyms and allows multiple connections. It probably has all features a commercial version had back then. It runs much faster and installs as a bunch of files only, not touching registry. If you don't care about new features in newer versions, find an old version and install it on your hard drive. It never expires.
(Even though it's much harder to do so with new versions, you should not under any circumstances modify toad.exe to bypass its limitation. *That* will sure be considered hacking/cracking and violate copyright.)
If all you want is creating an ER Diagram, you probably don't need any of these (much less the expensive and over-engineered ERwin). Check to see if you have Microsoft Visio under Start -> Programs -> Microsoft Office. If you do, follow these steps: create an ODBC DSN to the database; launch Visio; go to File | New | Software and Database | Database Model Diagram (either Metric or US units); now you have Database menu; go to Database | Reverse Engineer; ... If it errors out right before the diagram is generated, you need a newer version of Visio such as 2007. But Visio has one serious problem on the database server; it generates more than one thousand SQLs with literal values in the where clause accessing data dictionary views, using at least 100 MB shared pool memory in total. Consider setting cursor_sharing to force before you launch Visio (you can limit it to your session by temporarily creating a logon trigger, and drop it right after you connect). Hope Visio developers will correct this problem soon.
To my OraNotes Page