How do I browse databases on an Oracle Server?

Question

So I'm coming from a position as a SQL Server developer who has written a little bit of PL/SQL way back in the mists of time but effectively knows nothing.

I've got a laptop, it's running Oracle. There is a database on the laptop which I need to have a look at and neither I, nor anyone else I can speak to, knows what it's called.

I have a couple of logins including one which alleges to be an Admin login.

What's the easiest way to browse the databases on the server and then connect to one?

I suspect that my assumptions about relationships between servers, users and databases from SQL Server are leading me astray but right now I can't get past idea that the client tools Oracle ships are someone's idea of a joke but I'm really not finding it that funny.

I'm pretty sure that it's version 9.2 that's running.

Happy to provide more information but right now I don't know where to start.

Answer 1

Check out Oracle SQL Developer. (It's free)

I'm not a SQL Server expert, but there are differences in terminology.

An Oracle "Instance" is a single entity of the Oracle software. A database is the actual logical data that the instance serves. You may have multiple instances of the Oracle database software running against one database (This is considered a RAC setup); or just one instance running against one database.

I'm assuming what you mean by database here is actually the different schemas/users/tablespaces/procedures etc. that you have within an Oracle database. In that case, Oracle SQL Developer should do the job nicely.

If you are able to utilize Enterprise Manager (this is a licensed option), it provides this functionality as well, though it is more cumbersome than SQL Developer IMHO. EM does provide many other tools for providing a general overview of your Oracle instance health, alerts, and many things used to help tune the performance of your Oracle instance.

Answer 2

I don't know what qualifies as "easiest" in your book. I'm moderately fond of Aqua Data Studio (www.aquafold.com); it's nice & visual & pretty & all. It's also Not Cheap, but might be worth a 2-week evaluation to see if it does anything helpful for you. I'm not an expert on this, and I joined an Oracle-based group from a SQL Server background; that's the tool they were using at the time, and I've found it extremely usable. But that's just me. Good luck.

Answer 3

If this is windows look in the services. Each database will have a separate service. By default these are named OracleService*DATABASENAME*.
You may be able to connect with just this information on the default port 1521.

If that does not work open the properties of that service you can find the location of the oracle home. The default place to keep your connection information in the ORACLEHOME\network\admin\tnsnames.ora . Using the database name you can find your connect information in this file.

If this is linux/unix look in the /etc/oratab file. This should have the database name and oracle home listed. The default location of the tnsnames.ora file is the same as on windows.

Once you have this information you can use any of the tools mentioned above to connect to the database.

Answer 4

In Windows, the following command lists running database services:

net start | findstr -i ora

In Unix/Linux, grepping for the Oracle SMON process will give you the names of running databases:

ps -ef | grep smon