Three Great Things You Didn’t Know About SQLT

SQLT is an amazing tool with a host of user friendly features. This post covers three I’ve found particularly useful; how to use SQLT on a test database where it isn’t installed, how to get rid of histograms and how, when your system is large, you can get the information you need, faster.

English: Labeled (SQL) database icon with shor...
English: Labeled (SQL) database icon with shortcut identification (Photo credit: Wikipedia)

SQLT is a fabulous tool. You probably already know that, or you wouldn’t be reading this!  What you may not know is that in addition to all the wonderful information SQLT provides, it also has a lot of user friendly features. For example:


(1) You Can Get Rid of Those Pesky Histograms


Hidden away, deep in the bowels of SQLT there is a utility to remove the column histogram from one column on a table or partition. You can also cascade this to all partitions. At the bottom of this script is a call to


exec delete_column_hgrm
(p_owner=> ‘<OWNER>’,
p_tabname=> ‘<TABLE_NAME>’,
p_colname=>'<COLUMN NAME>’,
p_cascade_parts=>'<TRUE| FALSE>’,
p_no_invalidate=>'<TRUE| FALSE>’,
p_force=>'<TRUE| FALSE>’);


You can find this script in <SQLT Install area>/utl/xgram and it is called sqlt_delete_column_hgrm.sql. There are cases where you are experimenting with a test case where this may be useful to determine if you can ‘fix’ an execution plan.


(2) I Don’t Want It Fast! I Want It XPRESS


When collecting information, SQLT can sometimes take a long time. This is because there is a lot of information to collect. On very large systems there is so much information to collect, SQLT can take hours to run.  If you really can’t wait or don’t have the patience, you may need the express version of the common methods. In version of SQLT (March 2013) there are a couple of utilities called




These are the EXPRESS versions of the XTRACT and XECUTE routines, which execute the scripts in sqltcommon11.sql


The command to call this is


@sqltxprext <sql-id>


Please note in some early versions of SQLT you need to change this line in sqltcommon11.sql


EXEC ^^tool_administer_schema..sqlt$a.set_sess_param(‘ash_reports’, ‘NONE’);




EXEC ^^tool_administer_schema..sqlt$a.set_sess_param(‘ash_reports’, 0);


(3) You don’t need SQLT?


If you want to build a test case to display some behavior of your SQL your first option is to use




which you can get from the SQLT zip file. This is described in detail in Chapter 11 of my book, but the important thing to know is that you need the installation of SQLT on your test database to be able to build this test case. Then you can use all the utilities built in to SQLT to explore the execution plan. If you need to move your test case to a database which does not have SQLT installed then you are stuck. There is no way to install the TC file into this database. Luckily Carlos (at the request of many support engineers and development) provided a way to do this. There is an alternative file




The TCX file (Test Case Xpress) is for precisely this purpose. It can be used to install the test case into your disposable database, WITHOUT SQLT being installed before you start. It does not require the SQLT repository or schema objects (functions and so on) to be able to do its work. Once you unzip the TCX file, you need only have access to SYS on your disposable database and run


SQL> @install


This does all the usual magic required. There are no other steps. The SQL is available, the schema is created and the statistics are imported. A truly useful feature.


There are many other cool features in SQLT and I hope to document at least some of them in future posts, so stop  by often or sign up for posts by email.


1 thought on “Three Great Things You Didn’t Know About SQLT”

  1. I would like to try SQLT on my laptop, but don’t have an Oracle support account. Is there any other way to get it? Thanks.

Leave a Reply

Your email address will not be published. Required fields are marked *