By Franck Pachot

.
When you want to transport a SQL Tuning Set between production and test for example, you have to pack it into a table, then export the table, import it into the target database, and unpack the STS. This is a case where Enterprise Manager can help to do it quickly.

First I create a SQL Tuning Set

Capture001

Not showing all details here. Just loading the Top-5 queries from library cache:

Capture002

Here is my STS that I maned ‘TEST’ and I can export it to a DataPump dump file:

Capture003

I can choose or create a directory from there and there’s a default name for the dump file with the name of my STS within:

Capture004

For the example I use the same database. I have to drop the old one because I cannot rename the STS while importing.
In Enterprise Manager the buttons on the left are related with the object that is selected, but the import is on the right, like the ‘create’ one as it creates a new STS. You cannot import to an existing STS.

Capture005

Now enter the impdp parameters:

Capture006

and run the job:

Capture007

Then here is the STS imported:

Capture008

Note that there is also a ‘copy to database’ button that run all that. However, because it includes file transfer, you have to provide host credentials.

For this example, I’ve used the EM13c VirtualBox and have added the emrepus target. I didn’t find the SQL Tuning Set Menu at the place I expected it. Thanks to Twitter friend Hatem Mahmoud I know why:

Don’t forget you need Tuning Pack for SQL Tuning Sets.