The following SQL Profile adds a FIRST_ROWS every time it sees the query specified by SELECT...%. You will need to update the WHERE Clause of the SELECT below. Verify that it returns the SQL you are expecting and that the query only returns one row.

Obtain the SQL_ID of the SQL you would like to create a profile for:
It is easier to obtain the SQL_ID instead of trying to cut and paste the text of the whole statement. Often the SQL text is too cumbersome to cut and paste reliably, so why not grab it from the sqlarea?

  SELECT sql_id, 
         sql_text 
  FROM gv$sqlarea 
 WHERE sql_text LIKE 'SELECT...%';

Implement the profile:

DECLARE
  cl_sql_text CLOB;
BEGIN

  SELECT sql_text 
  INTO cl_sql_text 
  FROM gv$sqlarea where sql_id = '&myid.';

  DBMS_SQLTUNE.IMPORT_SQL_PROFILE(sql_text => cl_sql_text, 
    profile => sqlprof_attr('FIRST_ROWS'), 
    name => 'FIRST_ROWS_TEST', 
    category => 'DEFAULT',
    force_match => TRUE);

end;
/

Notes
force_match : Ignores literals, allows SQL Profile to be applied to multiple versions of the same statement. This can be likened to the CURSOR_SHARING instance parameter which allows the optimizer to replace literals with bind variable placeholders. This allows the a single SQL Profile to be used for SQL statements with the same syntax but are submitted with literal values instead of Bind Variables.

sqlprof_attr can take any hint as input. In this case I kept it simple but you can take the text from between the /*+ ... */ hint separators and paste it into the sqlprof_attr parameter.

CDB and PDB Scope
It looks like PDBs and CDBs can own SQL Profiles. Use DBA_SQL_PROFILES view in a PDB and CDB_SQL_PROFILES view in the CDB to review SQL profiles.
If you log into the CDB with Cloud Control then it looks like all profile work will be done on the CDB level. Just be aware of this - if you create a SQL Profile in a PDB manually using a script like coe_xfr_sql_profile.sql, you may not be able to administrate it with the Cloud Control GUI.

Viewing
Profile Summary

-- Traditional DB or PDB
SELECT * FROM DBA_SQL_PROFILES;

-- Container database, note the CON_ID will show which DB the profiles are owned by
SELECT * FROM CDB_SQL_PROFILES;

Profile Attribute Detail

SELECT *
  FROM DBA_SQL_PROFILES prof, 
       DBMSHSXP_SQL_PROFILE_ATTR attr
  WHERE prof.NAME=attr.PROFILE_NAME
  ORDER BY prof.name,
           attr.attr_number;

Enable/Disable
Locate the name of the SQL Profile you would like to disable and plug it in to the following statement:

-- Enable an existing profile:
EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE('PROFILE_NAME','STATUS','DISABLED');

-- Disabling an existing profile:
EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE('PROFILE_NAME','STATUS','DISABLED');

Dropping a SQL Profile

EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE('PROFILE_NAME');

Jonathan Lewis SQL Profiles (10g)

Following 457531.1

Transporting the SQL Profile

====================

i) Creating a staging table to store the SQL Profiles

SQL> exec DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF (table_name=>'TEST',schema_name=>'SCOTT');

PL/SQL procedure successfully completed.

table_name => name of the table to store the SQL Profiles.

schema_name => name of the user where the table is to be created.

ii) Packing the SQL Profiles to the staging table

SQL> EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name => 'TEST',profile_name=>'my_sql_profile');

PL/SQL procedure successfully completed.

staging_table_name => name of the table to store the SQL Profiles.

profile_name => name of the SQL Profile to be packed.

Note: The table_name and schema_name are case-sensitive.

SQL> desc test
Name Null? Type
----------------------------------------------------------------- -------- --------------------------------------------
PROFILE_NAME VARCHAR2(30)
CATEGORY VARCHAR2(30)
SIGNATURE NUMBER
SQL_TEXT CLOB
DESCRIPTION VARCHAR2(500)
TYPE VARCHAR2(9)
STATUS VARCHAR2(8)
BOOLEAN_FLAGS NUMBER
ATTRIBUTES SQLPROF_ATTR
VERSION NUMBER
SPARE1 CLOB
SPARE2 BLOB

SQL> select count(*) from test;

COUNT(*)
----------
1

SQL> set autot on
SQL> select /*+ no_index(emp pk_emp) */ * from emp where empno=7839;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7839 KING PRESIDENT 17-NOV-81 5000 10

Execution Plan
----------------------------------------------------------
Plan hash value: 2949544139

--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 31 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 31 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("EMPNO"=7839)

Note
-----
- SQL profile "my_sql_profile" used for this statement

iii) Now export the table using Datapump or Export/Import.

[oracle@localhost oracle]$ exp scott/tiger tables=test

Export: Release 10.2.0.3.0 - Production on Fri Sep 7 01:18:06 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning and Data Mining options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table TEST 1 rows exported
Export terminated successfully without warnings.

iv) Execute the query in another database without SQL Profiles

SQL> select /*+ no_index(emp pk_emp) */ * from emp where empno=7839;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7839 KING PRESIDENT 17-NOV-81 5000 10

Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 348 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 4 | 348 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("EMPNO"=7839)

Note: The SQL Profiles are not there.

v) Importing the SQL Profiles to the database

[oracle@localhost oracle]$ imp scott/tiger file=expdat.dmp full=y

Import: Release 10.2.0.3.0 - Production on Fri Sep 7 01:24:15 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
. importing SCOTT's objects into SCOTT
. importing SCOTT's objects into SCOTT
. . importing table "TEST" 1 rows imported
Import terminated successfully without warnings.

vi) Now Unpack the SQL Profiles

SQL> conn scott/tiger /* connect to scott schema where the staging table is imported and then unpack */

SQL> EXEC DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(replace => TRUE,staging_table_name => 'TEST');

PL/SQL procedure successfully completed.

vii) Checking the SQL Profiles

SQL> select /*+ no_index(emp pk_emp) */ * from emp where empno=7839;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7839 KING PRESIDENT 17-NOV-81 5000 10

Execution Plan
----------------------------------------------------------
Plan hash value: 2949544139

--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 31 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 31 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("EMPNO"=7839)

Note
-----
- SQL profile "my_sql_profile" used for this statement

Now the SQL Profile is transported to another database.

The following articles cover info about and usage of coe_xfr_sql_profile.sql which is supplied with the SQLT package from Oracle.

http://kerryosborne.oracle-guy.com/2010/07/sqlt-coe_xfr_sql_profilesql/

http://kerryosborne.oracle-guy.com/scripts/coe_xfr_sql_profile.sql

http://carlos-sierra.net/2012/04/09/custom-sql-profile-and-plan-stabilit...

The following query will show the details of the most recently created SQL Profiles first. Each component hint of the Profile is returned one per row.

SELECT CREATED, PROFILE_NAME, ATTR_VALUE, SQL_TEXT 
  FROM DBA_SQL_PROFILES PROF, 
       DBMSHSXP_SQL_PROFILE_ATTR ATTR
 WHERE PROF.NAME=ATTR.PROFILE_NAME
 ORDER BY CREATED DESC, NAME, ATTR_NUMBER;