How to run SQLT for a sql_id in Oracle

Hiya Buddies, Topic: How to run SQLT for a particular sql_id

We would like to share a very important topic which may useful for both either core DBA or apps DBA.

Usually we used to get the request from users to provide SQLT from a sql_id.

It is very important and easy also but we need to be very careful.

Sometimes it may throw error also.  Same way I also got the

Request from users and ran the SQLT script but got error also which I would like to share with you all here…

How to run SQLT for a particular sql_id. Suppose, our sql_id is ora-data1234.

# pwd

/home/oracle/sqlt/sqlt/run

oracle@host:/home/oracle/sqlt/sqlt/run

# ls -ltr sqltxtract.sql

-rw-r—r--   1 oracle   dba         4431 Oct 30  2014 sqltxtract.sql

oracle@host:/home/oracle/sqlt/sqlt/run

#sqlplus ‘/as sysdba'

SQL>@sqltxtract.sql ora-data1234

It will ask for password SQLTXTRACT which may same as username or different if you have made any changes… In our case it is same as username.

If you provide wrong password,  no need to worry.  It will ask again username and password.

Now, also you may get the below error for tablespace, better to keep monitoring log file.

Issue:

ORA-01691: unable to extend lob segment SQLTXP

Solution:

SQL>Alter tablespace USERS add datafile ‘/path/TEST/datafile/path/datafile/users.9326.978199625’ size 1024 M;

 File ID  File Name                                         Megabytes

-------- -------------------------------------------------- ---------

     293 /path/TEST/datafile/path/datafile/dat       500

         afile/users.9326.978199624

     275 /path/TEST/datafile/path/datafile/dat       500

         afile/users.9326.978199623

Below is the few lines which you will get once it completed.

SQLDX files have been created.

Archive:  sqlt_s53282_sqldx.zip

  Length      Date    Time    Name

---------  ---------- -----   ----

   670545  11-12-2018 07:49   sqlt_s53282_sqldx_ora-data1234_csv.zip

   521831  11-12-2018 07:50   sqlt_s53282_sqldx_2017923558923286652_force_csv.zip

   662100  11-12-2018 07:51   sqlt_s53282_sqldx_table_csv.zip

    42639  11-12-2018 07:51   sqlt_s53282_sqldx_global_csv.zip

    10141  11-12-2018 07:51   sqlt_s53282_sqldx_ora-data1234_log.zip

---------                     -------

  1907256                     5 files

  adding: sqlt_s53282_sqldx.zip (stored 0%)

SQLTXTRACT completed.

SQL>

Note: If you face tablespace issues, just need to add/resize datafile. It will take care automatically, no need to restart the SQLT command.

Some more useful links:

How to deploy RPD in OBIEE 11g

Unable to find an Output Post Processor service to post-processrequest

Concurrent Managers are down error insert_fcp failed due to ORA-01403 no data found, ORA-06512

oracle apps dba interview questions and answers for experiencedasked Top MNC company