Uninstall SQLT or Drop SQLT ( SQLTXPLAIN ) in Oracle

Hi,

I will explain how to uninstall SQLT or Drop SQLT ( SQLTXPLAIN ) in Oracle.

Uninstall SQLT ( SQLTXPLAIN )

When you use SQLT ( SQLTXPLAIN ) tool, sometimes you may need to uninstall or drop it.

Before this post, If you want to learn how to install SQLT ( SQLTXPLAIN ) tool, you can read the following post.

Oracle SQLTXPLAIN (SQLT) Tips and Tricks | Oracle Database Performance Tuning Tutorial -18

Drop / Uninstall  SQLT ( SQLTXPLAIN )

If you want to uninstall the SQLT, then go to install directory and run the sqdrop.sql script.

Run sqdrop.sql script

Go to $SQLT_HOME/sqlt/install directory and run the sqdrop.sql as follows.

[oracle@msdbadm01 install]$ pwd
/home/oracle/sqlt/install
[oracle@msdbadm01 install]$ ls -ltr sqdrop*
-rw-r--r-- 1 oracle oinstall 1365 Oct 30 2014 sqdrop.sql

Run this sql as follows, it will uninstall / drop the SQLT tool.

[oracle@msdbadm01 install]$

[oracle@msdbadm01 install]$ sqlplus / as sysdba

SQL> @sqdrop.sql 
output:

SQL> @sqdrop.sql

PL/SQL procedure successfully completed.

... uninstalling SQLT, please wait
TADOBJ completed.

PL/SQL procedure successfully completed.


SQDOLD completed. Ignore errors from this script

PL/SQL procedure successfully completed.


SQDOBJ completed. Ignore errors from this script

PL/SQL procedure successfully completed.

SQL>
SQL> DECLARE
2 my_count INTEGER;
3
4 BEGIN
5 SELECT COUNT(*)
6 INTO my_count
7 FROM sys.dba_users
8 WHERE username = 'TRCADMIN'; <---
9
10 IF my_count = 0 THEN
11 BEGIN
12 EXECUTE IMMEDIATE 'DROP PROCEDURE sys.sqlt$_trca$_dir_set';
13 EXCEPTION
14 WHEN OTHERS THEN
15 DBMS_OUTPUT.PUT_LINE('Cannot drop procedure sys.sqlt$_trca$_dir_set. '||SQLERRM);
16 END;
17
18 FOR i IN (SELECT directory_name
19 FROM sys.dba_directories
20 WHERE directory_name IN ('SQLT$UDUMP', 'SQLT$BDUMP', 'SQLT$STAGE', 'TRCA$INPUT1', 'TRCA$INPUT2', 'TRCA$STAGE'))
21 LOOP
22 BEGIN
23 EXECUTE IMMEDIATE 'DROP DIRECTORY '||i.directory_name;
24 DBMS_OUTPUT.PUT_LINE('Dropped directory '||i.directory_name||'.');
25 EXCEPTION
26 WHEN OTHERS THEN
27 DBMS_OUTPUT.PUT_LINE('Cannot drop directory '||i.directory_name||'. '||SQLERRM);
28 END;
29 END LOOP;
30 END IF;
31 END;
32 /
Dropped directory TRCA$INPUT2.
Dropped directory TRCA$INPUT1.
Dropped directory SQLT$BDUMP.
Dropped directory SQLT$UDUMP.
Dropped directory TRCA$STAGE.
Dropped directory SQLT$STAGE.

PL/SQL procedure successfully completed.

SQL>
SQL> WHENEVER SQLERROR CONTINUE;
SQL>
SQL> PAU About to DROP users &&tool_repository_schema. and &&tool_administer_schema.. Press RETURN to continue.
About to DROP users SQLTXPLAIN and SQLTXADMIN. Press RETURN to continue.

SQL>
SQL> DROP USER &&tool_administer_schema. CASCADE;
old 1: DROP USER &&tool_administer_schema. CASCADE
new 1: DROP USER SQLTXADMIN CASCADE <---

User dropped.

SQL> DROP USER &&tool_repository_schema. CASCADE;
old 1: DROP USER &&tool_repository_schema. CASCADE
new 1: DROP USER SQLTXPLAIN CASCADE <---

User dropped.

SQL> DROP ROLE &&role_name.;
old 1: DROP ROLE &&role_name.
new 1: DROP ROLE SQLT_USER_ROLE <---

Role dropped.

SQL>
SQL> SET ECHO OFF;

SQDUSR completed.

SQDROP completed.
SQL>

If you want to learn more details about SQLT and SQLT examples, read the following post.

Performance Tuning with SQLTXPLAIN (SQLT) with Examples in Oracle | Oracle Database Performance Tuning Tutorial -19