Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
oratop is a utility that provides a general knowledge of database performance, similar to the top command in linux systems. When used with the Top command, we can get general information about the system performance.
It can be run as compiled script without requiring any installation. You can access the required file from document 1500864.1 at Oracle Support. There are compiled scripts for different operating systems and different database versions.
After downloading the relevant file, the file must be runnable by the oracle user.
Then you can set the required environment variables and run.
In our example, we downloaded the file named oratop.RDBMS_11.2_LINUX_X64, which is the necessary file for our 11.2 version database in Linux x86-64 platform.
Let’s rename the existing file and make it executable for oracle.
1 2 3 | [root@db01 ~]$ mv oratop.RDBMS_11.2_LINUX_X64 oratop [root@db01 ~]$ chown oracle:oinstall oratop [root@db01 ~]$ chmod 755 oratop |
With oracle, we can set the necessary variables and run them as follows. Commands must be run in the following order.
1 2 3 4 5 | [oracle@db01 ~]$ export TERM=xterm #ya da vt100 [oracle@db01 ~]$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/dbhome_1 [oracle@db01 ~]$ export LD_LIBRARY_PATH=$ORACLE_HOME/lib [oracle@db01 ~]$ export PATH=$ORACLE_HOME/bin:$PATH [oracle@db01 ~]$ export ORACLE_SID=ORCL1 |
We can run the oratop for our database in the local as below. The refresh interval we specified with -i may not provide accurate information in values less than 10 seconds. The default value is 5.
1 | [oracle@db01 ~]$ ./oratop -i 10 / as sysdba |
The sample screenshot is as follows.
It is also possible to monitor a remote database. We can also run the oratop command by connecting to other databases as follows.
1 | [oracle@db01 ~]$ ./oratop -i 10 username/password@tns |
For example;
1 | $ ./oratop -i 5 system/manager@tns |
Oratop must be run with a user who is either dba or a user who has the authority to select the following.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 | v$active_services v$instance v$latchname v$parameter V$PDBS (12c) v$recovery_file_dest v$sqlcommand v$system_event [g]v$servicemetric [g]v$session [g]v$sql [g]v$sysmetric GV$ASM_DISKGROUP GV$DIAG_INFO GV$PGASTAT GV$PROCESS GV$OSSTAT GV$RECOVERY_PROGRESS GV$SESSION_WAIT_CLASS GV$SESSTAT GV$SERVICE_EVENT GV$SERVICE_STATS GV$SGA GV$SORT_SEGMENT GV$SQLAREA GV$SQL_PLAN GV$SYSTEM_EVENT GV$SYSSTAT dba_data_files dba_free_space dba_tablespaces dba_temp_files dba_undo_extents sys.ts$ |
You can authorize a user for these views as follows.
1 | SQL> grant select on <view> to <username>; |
To get help about oratop, you can run it with the -h parameter as follows.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | [oracle@db01 ~]$ ./oratop -h oratop: Release 14.1.2 Usage: oratop [ [Options] [Logon] ] Logon: {username[/password][@connect_identifier] | / } [AS {SYSDBA|SYSOPER}] connect_identifier: o Net Service Name, (TNS) or o Easy Connect (host[:port]/[service_name]) Options: -d : real-time (RT) wait events, section 3 (default is Cumulative) -k : FILE#:BLOCK#, section 4 lt is (EVENT/LATCH) -m : MODULE/ACTION, section 4 (default is USERNAME/PROGRAM) -s : SQL mode, section 4 (default is process mode) -c : database service mode (default is connect string) -f : detailed format, 132 columns (default: standard, 80 columns) -b : batch mode (default is text-based user interface) -n : maximum number of iterations (requires number) -i : interval delay, requires value in seconds (default: 5s) -v : oratop release version number -h : this help |
