# set environment variable
export DISPLAY=hostname:0.0
export TMP=/u01/tmp
export TMPDIR=/u01/tmp

# check limits

--  open file descriptors (1024-65536)
$ ulimit -sn  
$ ulimit -Hn  

-- number of processes available to a single user (2047-16384)
$ ulimit -Su 
$ ulimit -Hu

-- size of the stack segment of the process (10240KB - 32768KB)
$ ulimit -Ss  
$ ulimit -Hs

# set limits (/etc/security/limits.conf)
oracle  hard    nproc   16384
oracle  soft    nproc   2047
oracle  hard    nofile  65536
oracle  soft    nofile  4096
oracle  hard    stack   32768
oracle  soft    stack   10240

./runIntaller

# dbca create database

export ORACLE_BASE=/uo1/app/oracle
export ORACLE_HOME=/uo1/app/oracle/product/12.2.0/dbhome_1
export PATH=$PATH:$ORACLE_HOME/bin
export DISPLAY=hostname:0.0

dbca

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;

1. create sql tunning set

2. add sql to tunning set from AWR

3. check sql tunning set
   SELECT SQL_ID, PARSING_SCHEMA_NAME AS "SCH", SQL_TEXT, 
          ELAPSED_TIME AS "ELAPSED", BUFFER_GETS
   FROM   TABLE( DBMS_SQLTUNE.SELECT_SQLSET( 
                    sqlset_name =>  '<sts_name>', 
                    sqlset_owner => '<sts_owner>' ) );

4. create staging table
   BEGIN
     DBMS_SQLTUNE.CREATE_STGTAB_SQLSET ( 
       table_name  => '<staging_table_name>',   
       schema_name => '<schema>',   
       db_version  => DBMS_SQLTUNE.STS_STGTAB_11_2_VERSION 
     );
   END;
   /

5. pack tunning set
   BEGIN
     DBMS_SQLTUNE.PACK_STGTAB_SQLSET (      
       sqlset_name          => '<sts_name>', 
       sqlset_owner         => '<sts_owner>',   
       staging_table_name   => '<staging_table_name>',   
       staging_schema_owner => '<schema>',   
       db_version           => DBMS_SQLTUNE.STS_STGTAB_11_2_VERSION 
     );
   END;
   / 

6. remap con_dbid
   BEGIN
     DBMS_SQLTUNE.REMAP_STGTAB_SQLSET (
       staging_table_name   => '<staging_table_name>',   
       staging_schema_owner => '<schema>',   
       old_con_dbid         => 1234,   
       new_con_dbid         => 5678
   );

7. expdp/impdp

8. unpack tunning set from staging table
   BEGIN
     DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET (
       sqlset_name        => '%',   
       replace            => true,   
       staging_table_name => '<staging_table_name>');
     END;
   /

-- step 1: create table
create table scott.weekly_orders
...

-- step 2: create materialized view
create materialized view scott.weekly_orders
  ON PREBUILT TABLE WITHOUT REDUCED PRECISION
  USING INDEX 
  REFRESH COMPLETE ON DEMAND
  USING DEFAULT LOCAL ROLLBACK SEGMENT
  USING ENFORCED CONSTRAINTS DISABLE QUERY REWRITE
as
select ...
from scott.orders
where ...
;