select * 
  from   (
    select f.*, 
	   count(*) over (partition by title, release_date) ct
    from   films f
  )
  where  ct > 1;

-- oracle
delete films
  where  rowid not in (
    select min(rowid)
    from   films
    group  by title, release_date
  );

-- sql server
delete films
  where  %%physloc%% not in (
    select min(%%physloc%%)
    from   films
    group  by title, release_date
  );

optimizer_capture_sql_plan_baselines=FALSE
optimizer_use_sql_plan_baselines=TRUE;


-- create tuning set
BEGIN
  dbms_sqltune.create_sqlset(
      sqlset_name => 'STS1', 
      sqlset_owner => 'SYSTEM');
END;
/

-- populate tuning set from cursor cache
DECLARE
  stscur dbms_sqltune.sqlset_cursor;
BEGIN
  
  open stscur for
    select value(p)
    from   table(dbms_sqltune.select_cursor_cache(
                   basic_filter      => 'sql_id = ''...''',
                   object_filter     => null,
                   ranking_measure1  => null,
                   ranking_measure2  => null,
                   ranking_measure3  => null,
                   result_percentage => null,
                   result_limit      => null,
                   attribute_list    => 'ALL')) P;
                   
  DBMS_SQLTUNE.LOAD_SQLSET(sqlset_name => 'STS1',
                           populate_cursor => stscur,
                           sqlset_owner    => 'SYSTEM');
END;
/

-- populate tuning set from AWR
DECLARE
  cur DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN  
  OPEN cur FOR
    SELECT VALUE(P)
      FROM table(
        DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(
                    begin_snap        => 1234,
                    end_snap          => 1235,
                    basic_filter      => 'sql_id = ''...''',
                    object_filter     => NULL, 
                    ranking_measure1  => NULL,
                    ranking_measure2  => NULL,
                    ranking_measure3  => NULL,
                    result_percentage => NULL,
                    result_limit      => NULL,
                    attribute_list    => 'ALL')) P;

  DBMS_SQLTUNE.LOAD_SQLSET(sqlset_name => 'STS1',
                           populate_cursor => cur,
                           sqlset_owner    => 'SYSTEM');
END;
/

-- select plans in tuning set
SELECT
     first_load_time,
     executions as execs,
     parsing_schema_name,
     elapsed_time  / 1000000 as elapsed_time_secs  ,
     cpu_time / 1000000 as cpu_time_secs           ,
     buffer_gets,
     disk_reads,
     direct_writes,
     rows_processed,
     fetches,
     optimizer_cost,
     sql_plan,
     plan_hash_value,
     sql_id,
     sql_text
   FROM TABLE(DBMS_SQLTUNE.SELECT_SQLSET(
                   sqlset_name  => 'STS1',
                   sqlset_owner => 'SYSTEM'))
;


-- create baseline using the plan in tunning set                           
DECLARE
  my_plans pls_integer;
BEGIN
  my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(
       sqlset_name  => 'STS1', 
       sqlset_owner => 'SYSTEM',
       basic_filter=>'plan_hash_value = ''1239572551'''
     );
 dbms_output.put_line(my_plans);                           
END;
/

-- check baselines
select sql_handle, 
       plan_name, 
       enabled, 
       accepted, 
       sql_text
  from dba_sql_plan_baselines
 where sql_text like '%...%'
;

-- accept plan for baseline
DECLARE
  report clob;
BEGIN
  report := dbms_spm.evolve_sql_plan_baseline(
    sql_handle => 'SQL_a...', 
    plan_name  => 'SQL_PLAN_a...');
END;
/

alter pluggable database pdb1 close instances=all;
alter pluggable database pdb1 unplug into '/tmp/pdb1.xml';
drop pluggable database pdb1 keep datafiles;
create pluggable database pdb1 using '/tmp/pdb1.xml' NOCOPY TEMPFILE REUSE;