SQLT Installation and Report Generation

SQLT Installation:
*******************
1. Download the sqlt zip file from MOS: 215187.1
2. Copy to the server using scp/winscp
3. Unzip the sqlt zip file to the $ORACLE_HOME/bin directory
$ cp /home/oracle/sqlt_latest.zip /u01/app/oracle/product/12.1.0.2/db_1/bin/
$ unzip sqlt_latest.zip
Archive:  sqlt_latest.zip

4. After unzip completed it will creates the sqlt directory. Go to the sqlt directory
$ cd sqlt/
$ ls -lhrt
total 116K
drwxr-xr-x 3 oracle oinstall 4.0K Nov 13 12:21 input
drwxr-xr-x 2 oracle oinstall 4.0K Nov 20 16:57 install
drwxr-xr-x 2 oracle oinstall 4.0K Nov 20 16:57 run
drwxr-xr-x 7 oracle oinstall 4.0K Nov 20 16:57 utl
drwxr-xr-x 2 oracle oinstall 4.0K Nov 20 16:57 doc
-rw-r--r-- 1 oracle oinstall  56K Dec  6 19:26 sqlt_instructions.html
-rw-r--r-- 1 oracle oinstall  38K Dec  6 19:27 sqlt_instructions.txt

5. Connect to the database as sysdba and run the sqcreate.sql
$ sqlplus /nolog
SQL> connect / as sysdba
Connected.
SQL> start install/sqcreate.sql
It will prompt for the below options just give the respective values

Optional Connect Identifier (ie: @PROD): just type enter don't give any value in case of local server

Password for user SQLTXPLAIN: Proivde the new password for the SQLTXPLAIN user that must satisfy the password verify function otherwise you installation will fail
Re-enter password: Proivde the same above password

Default tablespace [UNKNOWN]: USERS  Provide the tablespace on which you want to install the SQLTXPLAIN user
Temporary tablespace [UNKNOWN]: TEMP Provide the temp tablespace
Main application user of SQLT: SYS
Oracle Pack license [T]: T

Once everything is completed then it will creates the below 2 users. And provide the necessary grants.

SQL> select USERNAME,ACCOUNT_STATUS from dba_users where trunc(CREATED)=trunc(sysdate);
USERNAME                                   ACCOUNT_STATUS
------------------------------------------ --------------------------------
SQLTXADMIN                                 EXPIRED & LOCKED
SQLTXPLAIN                                 OPEN

SQL> alter user SQLTXADMIN identified by SQLTXADMIN account unlock;

User altered.

SQL> grant create connect,resource,SQLT_USER_ROLE to SQLTXPLAIN,SQLTXADMIN;

Grant succeeded.
Note: All SQLT users must be granted the SQLT_USER_ROLE before they can use any of the main methods. If It's not possible to use SYS as SQLT user, SQLTXADMIN needs to be granded INHERIT PRIVILEGES on SYS.

SQL> GRANT INHERIT PRIVILEGES ON USER SYS TO SQLTXADMIN;

Grant succeeded.

SQLT report Generation for the specific sql_id:
***********************************************
1. Identify the which sql_id you want to generate the sqlt report
2.
SQL> ho pwd
/u01/app/oracle/product/12.1.0.2/db_1/bin/sqlt
SQL> conn SQLTXPLAIN/SQLTXPLAIN
Connected.
SQL> start run/sqltxtract.sql

The script will prompt for the sql_id and SQLTXPLAIN password please provide the same.
............
............
SQLTXTRACT completed.
SQL>exit
The script will generate the zip with the sql_id.
$ ls -lhrt|tail
-rw-r--r-- 1 oracle oinstall  29M Apr 22 03:59 sqlt_s62619_xtract_5xagduq334p7t.zip

Happy Learning :)