As @ik_zelf mentioned, your PATH variable will need to include the "bin" directory of your Oracle software install. This will allow you to execute commonly-needed commands/utilities like "sqlplus" and "lsnrctl" without changing directories. This is typically $ORACLE_HOME/bin
Your TNS_ADMIN variable needs to be set in a similar matter, pointing to the location of your tnsnames.ora, listener.ora, etc. By default, SQL Plus checks this directory (defined by TNS_ADMIN) for tnsnames.ora. This is typically $ORACLE_HOME/network/admin
If you want to connect to your database via TNS, your tnsnames.ora file (assuming a ORACLE_SID of "myDatabase", a host of "myHostname" and the default port) would look something like this:
myDatabase =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = myHostname)(PORT = 1521))
(CONNECT_DATA = (SERVICE_NAME = myDatabase)))
This would allow you to connect locally simply by doing the following from a command line:
sqlplus username/password@myDatabase
Of course, as SQL Plus looks for the tnsnames.ora file in the location defined by $TNS_ADMIN, this will only function locally. To access this database via TNS (Transparent Network Substrate) from a remote machine, you would have to install the Oracle client and ensure that the TNS entry for myDatabase (as described above) is included in the remote machine's tnsnames.ora file as well.
Best Answer
Foglight Perf.Analysis for Oracle is a great performance workload tool which digs very deep into Oracle via Stealthcollect technologie direct from server memory areas, so no much overhead load in the database, also possible to review historical data, change tracking, advisories and much more...
StealthCollect – Delivers deep SQL-level collection and analysis at a fraction of the overhead of running a trace or executing direct SQL against production.
Performance Advisories – Reviews workload data and produces custom reports to maximize performance through built-in analytics and business intelligence.
IntelliProfile – Tracks database behavior and identifies abnormal activity with a sophisticated baselining algorithm.
Statement-Level Wait-Event Analysis – Reveals wait-event data down to the statement level, speeding resolution of resource-related performance problems.
QuickCompare – Provides a shortcut for identifying the root causes and symptoms of performance deviations in application environments.
Integrated Reporting – Provides targeted, built-in reports. With easy-to-use wizards, you can define, customize, generate, schedule, and deliver reports on system health, performance, trends, changes, and a variety of other topics and data points.
Long-Term Performance Repository – Delivers unlimited performance and environmental data storage and dimensional analysis in an Oracle database.
Automated Change Tracking – Identifies and sends notifications of changes to server, instance, database, schema environments, and application SQL degradations.
Please also take a look at Spotlight on Oracle (RAC) is real time diagnostics from an Oracle architecture level.
We use these tools with Oracle healthchecks and performance investigations.
Pros:
Cons: