sed -i 's/XXX/<インスタンス名>/g' createdb.sql init.ora
======[createdb.sql]===============================================
startup nomount pfile='init.ora';
CREATE DATABASE XXX
   USER SYS IDENTIFIED BY P#ssw0rd
   USER SYSTEM IDENTIFIED BY P#ssw0rd
   SET DEFAULT BIGFILE TABLESPACE
   LOGFILE GROUP 1 ('+DG01/XXX/ONLINELOG/redo_grp1','+DG02/XXX/ONLINELOG/redo_grp1') SIZE 200M BLOCKSIZE 512,
           GROUP 2 ('+DG01/XXX/ONLINELOG/redo_grp2','+DG02/XXX/ONLINELOG/redo_grp2') SIZE 200M BLOCKSIZE 512,
           GROUP 3 ('+DG01/XXX/ONLINELOG/redo_grp3','+DG02/XXX/ONLINELOG/redo_grp3') SIZE 200M BLOCKSIZE 512
   MAXLOGHISTORY 1
   MAXLOGFILES 16
   MAXLOGMEMBERS 3
   MAXDATAFILES 1024
   CHARACTER SET AL32UTF8
   NATIONAL CHARACTER SET AL16UTF16
   EXTENT MANAGEMENT LOCAL
   ARCHIVELOG
   DATAFILE '+DG03/XXX/DATAFILE/SYSTEM' SIZE 700M
   SYSAUX DATAFILE '+DG03/XXX/DATAFILE/SYSAUX' SIZE 800M
   DEFAULT TABLESPACE TBLSP01 DATAFILE '+DG03/XXX/DATAFILE/TBLSP01' SIZE 15360M
   BIGFILE DEFAULT TEMPORARY TABLESPACE TEMPTBLSP01 TEMPFILE '+DG03/XXX/TEMPFILE/TEMPTBLSP01' SIZE 512M
   BIGFILE UNDO TABLESPACE UNDOTBLSP01 DATAFILE '+DG03/XXX/DATAFILE/UNDOTBLSP01' SIZE 512M;
alter database mount;
alter database open;
@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql
@?/rdbms/admin/utlrp.sql
conn system/premera
@?/sqlplus/admin/pupbld.sql
conn / as sysdba
create spfile='+DG03/XXX/spfileXXX.ora' from pfile='/home/oracle/createdb/init.ora';
host echo SPFILE='+DG03/XXX/spfileXXX.ora' > /opt/app/oracle/product/dbhome_1/12.2.0/dbs/initXXX.ora
shutdown immediate;
startup mount;
alter database archivelog;
alter database open;

その他SQLスクリプトは以下参照
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/refrn/sql-scripts.html#GUID-C308549C-A3DC-4CF2-9433-90650AD0F6DB

======[init.ora(Sample初期化パラメータファイル)]==========================
*._complex_view_merging=TRUE
*._fix_control='17760375:OFF'
*.audit_trail='XML','EXTENDED'
*.compatible='12.2.0.1.0'
*.control_file_record_keep_time=15
*.control_files='+DG01/XXX/CONTROLFILE/control01.ctl','+DG01/XXX/CONTROLFILE/control02.ctl'
*.db_block_checking='TRUE'
*.db_block_checksum='TRUE'
*.db_block_size=8192
*.db_cache_size=50331648
*.db_create_file_dest='+DG03'
*.db_create_online_log_dest_1='+DG01'
*.db_create_online_log_dest_2='+DG02'
*.db_domain=''
*.db_name='XXX'
*.db_writer_processes=4
*.ddl_lock_timeout=60
*.fast_start_mttr_target=1
*.log_archive_dest_1='LOCATION=+DG04'
*.log_archive_format='%T_%S_%R.dbf'
*.memory_target=0m
*.open_cursors=300
*.pga_aggregate_limit=0
*.pga_aggregate_target=10485760
*.processes=200
*.recyclebin='OFF'
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=1073741824
*.statistics_level='TYPICAL'
*.streams_pool_size=16777216
*.undo_retention=900
*.undo_tablespace='UNDOTBLSP01'

SET LINESIZE 300;
SET PAGESIZE 100;
COLUMN PROPERTY_NAME FORMAT A30;
COLUMN PROPERTY_VALUE FORMAT A35;
COLUMN DESCRIPTION FORMAT A100;
SELECT * FROM DATABASE_PROPERTIES ORDER BY PROPERTY_NAME;

set lin 10000 pagesize 10000 colsep |
col username format a10;
col program format a35;
col osuser format a10;
col spid format a10;
select sid,s.serial#,p.spid,s.username,s.osuser,
s.program,s.server,s.type,
round(p.PGA_USED_MEM/1024/1024) as "PGA_USED_MEM(MB)",
round(p.PGA_ALLOC_MEM/1024/1024) as "PGA_ALLOC_MEM(MB)",
round(p.PGA_MAX_MEM/1024/1024) as "PGA_MAX_MEM(MB)" 
from V$session s,v$process p where s.paddr=p.addr;

set lin 10000 pagesize 10000 colsep |
col username format a10;
col program format a35;
col osuser format a10;
col spid format a10;
select sid,s.serial#,p.spid,s.username,s.osuser,
s.program,s.server,s.type,
round(p.PGA_USED_MEM/1024/1024) as "PGA_USED_MEM(MB)",
round(p.PGA_ALLOC_MEM/1024/1024) as "PGA_ALLOC_MEM(MB)",
round(p.PGA_MAX_MEM/1024/1024) as "PGA_MAX_MEM(MB)" 
from V$session s,v$process p where s.paddr=p.addr 
and s.type != 'BACKGROUND';

set lin 10000 pagesize 10000
col SQL_EXEC_START format a23;
col username format a20;
col PROGRAM(Client) format a30;
col sql_id format a15;
col sql_address format a20;
col STATUS format a10;
col PROGRAM(Oracle) format a40;
col sql_text format a40;
select distinct s.SQL_EXEC_START,s.sid,s.serial#,s.sql_id,s.username,s.program "PROGRAM(Client)",s.status,(sysdate - s.SQL_EXEC_START) *60*60*24 as "time(s)",p.sql_text from V$session s, V$sql p where s.sql_address = p.address and s.SQL_HASH_VALUE = p.HASH_VALUE order by 1;

set lin 10000 pagesize 10000
col SQL_EXEC_START format a23;
col username format a20;
col PROGRAM(Client) format a30;
col sql_id format a15;
col sql_address format a20;
col STATUS format a10;
col PROGRAM(Oracle) format a40;
col sql_text format a40;
select distinct s.SQL_EXEC_START,s.sid,s.serial#,s.sql_id,s.sql_address,s.SQL_HASH_VALUE,s.username,s.program "PROGRAM(Client)",s.status,(sysdate - s.SQL_EXEC_START) *60*60*24 as "time(s)",p.sql_text from V$session s, V$sql p,V$PROCESS v where p.sql_text LIKE '%BEGIN%SYS_EXPORT%' order by 1;

col SQL_ID format a20;
col SQL_TEXT format a100;
col FIRST_LOAD_TIME format a20;
col PARSING_SCHEMA_NAME format a20;
SELECT SQL_ID,FIRST_LOAD_TIME, PARSING_SCHEMA_NAME,SQL_TEXT FROM V$SQL 
WHERE SQL_ID='&1' and FIRST_LOAD_TIME 
BETWEEN TO_CHAR(SYSDATE - (120 / 24 / 60), 'YYYY-MM-DD/HH24:MI:SS') 
AND TO_CHAR(SYSDATE, 'YYYY-MM-DD/HH24:MI:SS') 
ORDER BY FIRST_LOAD_TIME;

set linesize 800
set pagesize 1000
set trimspool on
set verify off
set tab off
set colsep ‘|’
alter session set nls_date_format='yyyy/mm/dd HH24:mi:ss';
col username for a10
col opname for a30
col target for a25
col target_desc for a15
col sql_id for a15

select username, opname, target, target_desc, sql_id,start_time,max(elapsed_seconds) seconds
from v$session_longops
where username is not null
  and elapsed_seconds > 1
group by username, opname, target, target_desc, start_time, sql_id
order by 2, 3;

set pagesize 1000 linesize 10000
col SEGMENT_NAME for a24
select SEGMENT_NAME, TABLESPACE_NAME, EXTENT_ID, BYTES, BLOCKS 
from USER_EXTENTS order by 1,2,3;  

set pagesize 1000 linesize 10000
col TABLE_NAME for a30
col BLOCKS for a40
SELECT TABLE_NAME,BLOCKS * 8 / 1024 as "HWM(MB)" from user_tables;

select pool,bytes/1024/1024 MB from v$sgastat where name = 'buffer_cache';
select pool,sum(bytes) / 1024/1024 MB from v$sgastat where name != 'free memory' group by pool;
select pool,sum(bytes) / 1024/1024 MB from v$sgastat group by pool;

col COMPONENT format a20;
col OPER_TYPE format a20;
col OPER_MODE format a20;
select COMPONENT,OPER_TYPE,OPER_MODE,INITIAL_SIZE,TARGET_SIZE,FINAL_SIZE,START_TIME,END_TIME from v$sga_resize_ops where COMPONENT = 'shared pool' or COMPONENT = 'buffer cache';   

col EVENT format a50;
col sql_id format a15;
col username format a20;
col WAIT_CLASS format a20;
col sql_text format a70;
col status format a13;
select a.sid,a.username,a.sql_id,a.event,a.wait_class,a.status,b.sql_text from v$session a,v$sql b where a.sql_id=b.sql_id and a.username='DP';

col WAIT_CLASS format a20;
select SID,EVENT,WAIT_CLASS,TOTAL_WAITS,TIME_WAITED from v$session_event where sid=&1 order by 5 desc;

select dbid, snap_id, to_char(end_interval_time, 'yyyy/mm/dd hh24:mi') getsnapshot_time, snap_level from DBA_HIST_SNAPSHOT order by dbid, snap_id;

EXECUTE DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();

@?/rdbms/admin/awrrpt.sql

-- ALTER SESSION SET EVENTS '10046 trace name context forever, level 12'; と同じ
EXECUTE DBMS_SESSION.SESSION_TRACE_ENABLE(waits => true, binds => true);
-- ALTER SESSION SET EVENTS '10046 trace name context forever, level 16'; と同じ
EXECUTE DBMS_SESSION.SESSION_TRACE_ENABLE(waits => true, binds => true, plan_stat=>'ALL_EXECUTIONS');

ALTER SESSION SET TRACEFILE_IDENTIFIER = "XXXXXXXXX";

EXECUTE DBMS_SESSION.SESSION_TRACE_DISABLE;

set linesize 100
col TRACEFILE for a100
SELECT p.tracefile FROM   v$session s JOIN v$process p ON s.paddr = p.addr WHERE s.sid = SYS_CONTEXT('userenv','sid');

BEGIN 
  DBMS_MONITOR.SESSION_TRACE_ENABLE(
    session_id => 611, 
    serial_num => 32130,
    waits => true, 
    binds => true,
    plan_stat=>'ALL_EXECUTIONS');
END;
/

EXECUTE DBMS_MONITOR.SESSION_TRACE_DISABLE(session_id => 611, serial_num => 32130);

set lin 10000
col segment format a60
select tablespace_name tablespace,file_id,block_id,blocks,owner||'.'||segment_name segment
from dba_extents where tablespace_name='TBLSP01'
union
select tablespace_name tablespace,file_id,block_id,blocks,' '
from dba_free_space where tablespace_name='TBLSP01'
order by 1,2,3;

### パスワードファイル作成
orapwd file=$ORACLE_HOME/dbs/orapwXXX password=P#ssw0rd entries=10

### パスワードファイルの情報から、どのユーザーに特権が付与されているか確認
select * from v$pwfile_users;

ALTER SYSTEM SET control_files = '<new path1>','<new path2>' SCOPE = SPFILE;

col member format a100;
SELECT A.GROUP#,A.BYTES,A.STATUS,B.TYPE,B.MEMBER from v$LOG A right outer join v$logfile B ON A.group# = B.group#;

alter database add logfile group x ('<newパス>') SIZE xxxM;

alter database add logfile member '<newパス>' to group x;

ARCHIVE LOG LIST;
SELECT NAME,LOG_MODE from V$DATABASE;

ALTER DATABASE ARCHIVELOG;

col name format a100;
SELECT name,sequence# from V$ARCHIVED_LOG order by 2;

col TABLESPACE_NAME for a15
col EXTENT_MANAGEMENT for a17
col ALLOCATION_TYPE for a15
col SEGMENT_SPACE_MANAGEMENT for a24
col BIGFILE for a7
select TABLESPACE_NAME,EXTENT_MANAGEMENT,ALLOCATION_TYPE,SEGMENT_SPACE_MANAGEMENT,BIGFILE,STATUS from DBA_TABLESPACES;

col TABLESPACE_NAME  format a30
col FILE_NAME        format a50
col STATUS           format a10
col MBYTES           format 9,999,990
col INCRE            format 9,999,990
col AUTOEXTENSIBLE   format a5
col ONLINE_STATUS    format a6
select tablespace_name,file_name,status,bytes/1024/1024 mbytes,increment_by,autoextensible,online_status from dba_data_files;

select
   tablespace_name,
   to_char(nvl(total_bytes / 1024,0),'999,999,999') as "Total(KB)",
   to_char(nvl((total_bytes - free_total_bytes) / 1024,0),'999,999,999') as "Used(KB)",
   to_char(nvl(free_total_bytes/1024,0),'999,999,999') as "Free(KB)",
   round(nvl((total_bytes - free_total_bytes) / total_bytes * 100,100),2) as "Utilization(%)"
from
   ( select tablespace_name,sum(bytes) total_bytes
     from dba_data_files group by tablespace_name)
   left join 
   ( select tablespace_name as free_tablespace_name,sum(bytes) free_total_bytes
     from dba_free_space group by tablespace_name)
 on tablespace_name = free_tablespace_name order by 1;

col TABLESPACE_NAME format a30;
select * from dba_temp_free_space;

select * from v$sga;  　　　　　　　　#簡易
select * from v$sgastat;　　　　#詳細

SET SERVEROUTPUT ON;
DECLARE
  d_gets NUMBER;
  c_gets NUMBER;
  p_reads NUMBER;
  result NUMBER;
BEGIN
  select DB_BLOCK_GETS into d_gets from V$BUFFER_POOL_STATISTICS where NAME='DEFAULT';
  select CONSISTENT_GETS into c_gets from V$BUFFER_POOL_STATISTICS where NAME='DEFAULT';
  select PHYSICAL_READS into p_reads from V$BUFFER_POOL_STATISTICS where NAME='DEFAULT';
result:=round((1-(p_reads/(d_gets+c_gets))),3)*100;
DBMS_OUTPUT.PUT_LINE('Database Buffer Cache Hit =>>' || result || '(%)');
END;
/

col OWNER for a8
col OBJECT_NAME for a16
select OWNER, OBJECT_NAME, count(*) "BUFFERS", count(*)*8/1024 "MB"
  from V$BH, DBA_OBJECTS
 where OBJD = DATA_OBJECT_ID and OWNER = '&1' and V$BH.STATUS != 'free'
 group by OWNER, rollup(OBJECT_NAME)
 order by 4 ;

col OWNER format a20
col OBJECT_NAME format a40
select o.owner,o.object_name,o.object_type,o.status,s.buffer_pool,count(*) as blocks
from dba_objects o,x$bh b,dba_segments s
where b.obj=o.data_object_id
and o.owner = &1
and s.owner = &1
and o.object_name=s.segment_name
and o.object_type=s.segment_type
group by o.owner,o.object_name,o.object_type,o.status,s.buffer_pool
order by blocks;

set lines 150 pages 5000
col NAME for a48
select S.NAME, M.VALUE
  from V$MYSTAT M, V$STATNAME S
 where M.STATISTIC# = S.STATISTIC#
   and S.NAME like 'physical read%'
 order by 1 ;

SET SERVEROUTPUT ON;
DECLARE
  pins NUMBER;
  reloads NUMBER;
  result NUMBER;
BEGIN
  select SUM(PINS) into pins from V$LIBRARYCACHE;
  select SUM(RELOADS) into reloads from V$LIBRARYCACHE;
result:=round((1-(reloads/pins)),3)*100;
DBMS_OUTPUT.PUT_LINE('Library Cache Hit =>>' || result || '(%)');
END;
/

SET SERVEROUTPUT ON;
DECLARE
  gets NUMBER;
  getmisses NUMBER;
  result NUMBER;
BEGIN
  select SUM(GETS) into gets from V$ROWCACHE;
  select SUM(GETMISSES) into getmisses from V$ROWCACHE;
result:=round((1-(getmisses/gets)),3)*100;
DBMS_OUTPUT.PUT_LINE('Dictionary Cache Hit =>>' || result || '(%)');
END;
/

select * from v$pgastat;

select snap_id, to_char(snap_time, 'yy-mm-dd hh24:mi:ss') snap_time from stats$snapshot order by snap_id;

select snap_level from stats$statspack_parameter;

execute statspack.snap;
execute statspack.snap(i_snap_level=> 6)

@?/rdbms/admin/spreport.sql;

select num_rows,sample_size,last_analyzed from dba_tables where table_name='<TABLE名>';

select index_name,sample_size,last_analyzed from dba_indexes where table_name='<TABLE名>';

select num_backets from dba_tab_columns where table_name='<TABLE名>' and column_name='<列名>'

col username format a15
col object_name format a30
col machine format a15
col osuser format a15
col last_sql_text format a100
SELECT a.sid,a.serial#,e.pid,e.spid,a.username,d.object_name,a.status,a.machine,a.osuser,b.type,b.lmode,a.program,b.CTIME lock_time,b.block,b.request,a.sql_id last_sql_id,(select sql_text from V$SQLAREA where sql_id=a.sql_id) as last_sql_text
FROM V$SESSION a,V$LOCK b,V$LOCKED_OBJECT c,dba_objects d,V$PROCESS e
WHERE 
a.SID = b.SID 
AND a.SID = c.SESSION_ID
AND b.TYPE IN ('TX','TM')
AND c.OBJECT_ID = d.OBJECT_ID
AND a.PADDR=e.ADDR
order by a.sid,b.type asc
;

alter system kill session '<SID>,<#SERIAL>';

$ ls $ORACLE_BASE/wallet
ewallet.p12
$ orapki wallet create -wallet $ORACLE_BASE/wallet -pwd "wallet_pass" -auto_login
$ ls $ORACLE_BASE/wallet
cwallet.sso  ewallet.p12

 SELECT t.name, e.encryptionalg algorithm  FROM  v$tablespace t, v$encrypted_tablespaces e WHERE t.ts# = e.ts#;

${ORACLE_HOME}/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -oh ${ORACLE_HOME} -phBaseDir <パッチTOP-DIR>

${ORACLE_HOME}/crs/install/roothas.sh -prepatch

${ORACLE_HOME}/OPatch/opatch apply -silent -oh ${ORACLE_HOME} <パッチTOP-DIR>

${ORACLE_HOME}/rdbms/install/rootadd_rdbms.sh
${ORACLE_HOME}/crs/install/roothas.sh -postpatch

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
  job_name         =>  'job1',
  job_type         =>  'STORED_PROCEDURE',
  job_action       =>  'MARIO.TEST_PROC',
  start_date       =>  to_date('2015/01/01 00:00:00','yyyy/mm/dd hh24:mi:ss'),
  repeat_interval  =>  'FREQ=DAILY;INTERVAL=5',
  end_date         =>  to_date('2015/12/31 00:00:00','yyyy/mm/dd hh24:mi:ss'),
  auto_drop        =>  FALSE,
  enabled          =>  TRUE);
END;
/

BEGIN
  DBMS_SCHEDULER.ENABLE('job1');
END;
/

BEGIN
  DBMS_SCHEDULER.DISABLE('job1');
END;
/

BEGIN
  DBMS_SCHEDULER.DROP_JOB('job1');
END;
/

SET LINESIZE 10000
SET PAGESIZE 0
SET SQLPROMPT OFF
SET FEEDBACK OFF
SET TERMOUT OFF
SET TRIMSPOOL ON
SET SERVEROUTPUT OFF

SPOOL test.csv
SELECT col1 ||','|| col2 FROM tbl1;
SPOOL OFF
QUIT

・ディクショナリ統計
prompt 'Statistics for SYS tables'
SELECT NVL(TO_CHAR(last_analyzed, 'YYYY/MM/DD HH:MI'), 'NO STATS') last_analyzed, COUNT(*) dictionary_tables
FROM dba_tables
WHERE owner = 'SYS'
GROUP BY TO_CHAR(last_analyzed, 'YYYY/MM/DD HH:MI')
ORDER BY 1 DESC;


EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

・固定オブジェクト統計
prompt 'Statistics for Fixed Objects'
select NVL(TO_CHAR(last_analyzed, 'YYYY/MM/DD HH:MI'), 'NO STATS') last_analyzed, COUNT(*) fixed_objects
FROM dba_tab_statistics
WHERE object_type = 'FIXED TABLE'
GROUP BY TO_CHAR(last_analyzed, 'YYYY/MM/DD HH:MI')
ORDER BY 1 DESC;

EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

##テーブル統計情報収集
set lin 10000 pagesize 10000 time on timing on colsep |
col table_name format a25
col partition_name format a25
select table_name,partition_name,object_type,num_rows,blocks,chain_cnt,
sample_size,avg_row_len,global_stats,stattype_locked,last_analyzed 
from user_tab_statistics order by 1;

##インデックス統計情報収集
set lin 10000 pagesize 10000 time on timing on colsep |
col index_name format a25
col table_name format a25
col partition_name format a25
select index_name,blevel,leaf_blocks,clustering_factor,table_name,
partition_name,sample_size,global_stats,stattype_locked,last_analyzed 
from user_ind_statistics order by 1;

###SQL実行時に実行結果と実行計画出力
set autotrace on;

###SQL実行時に実行計画のみ出力
set autotrace traceonly;

###同一セッションで直前に実行したSQLの実行計画取得(共有カーソルに残っている必要があり)
select * from table(DBMS_XPLAN.DISPLAY_CURSOR());

###SQLIDを指定して実行計画取得(共有カーソルに残っている必要があり)
1)SQLIDをSQLTEXTのwhere条件で出力
select sql_id, sql_text from v$sql where sql_text like 'select emp.first_name%';

2)実行計画取得
select * from table(DBMS_XPLAN.DISPLAY_CURSOR(<SQLID>));

###実行計画の見方
１．実行計画は、インデントがされていて、インデントが右に行くほど深い。そして深い順に実行される。
２．深さが同じ場合は、上から順。

下記の例だと、
1.XXX1のフルスキャン
２.XXX2のフルスキャン
3.XXX1とXXX2のHash Join結合

(例)
01:18:38 SQL> select a.col1,b.col2 from XXX11 A inner join XXX12 B on a.col1 = b.col1;

1000000 rows selected.

Elapsed: 00:00:15.69

Execution Plan
----------------------------------------------------------
Plan hash value: 3006947627

------------------------------------------------------------------------------------
| Id  | Operation      | Name  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |   996K|    12M|       | 10508   (1)| 00:00:01 |
|*  1 |  HASH JOIN     |       |   996K|    12M|    16M| 10508   (1)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| XXX11 |   996K|  4867K|       |  4365   (1)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| XXX12 |  1002K|  7829K|       |  4366   (1)| 00:00:01 |
------------------------------------------------------------------------------------

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

   1 - access("A"."COL1"="B"."COL1")


Statistics
----------------------------------------------------------
     45  recursive calls
     15  db block gets
      98443  consistent gets
      31742  physical reads
       3024  redo size
   22721309  bytes sent via SQL*Net to client
     733934  bytes received via SQL*Net from client
      66668  SQL*Net roundtrips to/from client
      6  sorts (memory)
      0  sorts (disk)
    1000000  rows processed

01:20:02 SQL> 

exec DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'TRY', options => 'GATHER STALE');

-- 閾値変更
exec DBMS_STATS.SET_SCHEMA_PREFS(ownname => 'TRY', pname => 'STALE_PERCENT', pvalue => 5);

-- 閾値が変更されたかの確認
select TABLE_NAME, DBMS_STATS.GET_PREFS(pname => 'STALE_PERCENT', ownname => 'TRY', tabname => TABLE_NAME) "STALE_PERCENT" from USER_TABLES;

select TABLE_NAME, INSERTS, UPDATES, DELETES, TRUNCATED from USER_TAB_MODIFICATIONS ;
-- どうやら統計情報更新後はそのテーブルに関しては記録されないらしい。

SET SERVEROUTPUT ON
DECLARE
  obj_list DBMS_STATS.ObjectTab := DBMS_STATS.ObjectTab() ;
BEGIN
  DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>'TRY', objlist=> obj_list, options=>'LIST STALE') ;
  FOR i in obj_list.FIRST.. obj_list.LAST LOOP
    dbms_output.put_line(obj_list(i).objtype || ' ' || 
obj_list(i).ownname || '.' || 
obj_list(i).objname || ' ' || 
                         nvl(obj_list(i).partname, 'NonPartition') || ' – ' || 
nvl(obj_list(i).subpartname, '*'));
  END LOOP;
END;
/

exec DBMS_STATS.SET_TABLE_PREFS(ownname => 'TRY', tabname => 'TBL10', -
pname => 'PUBLISH', pvalue => 'FALSE');

select DBMS_STATS.GET_PREFS(pname => 'PUBLISH', ownname => 'TRY', tabname => 'TBL10') from DUAL ;

DBMS_STATS.GET_PREFS(PNAME=>'PUBLISH',OWNNAME=>'TRY',TABNAME=>'TBL10')
--------------------------------------------------------------------------------
FALSE

exec DBMS_STATS.GATHER_TABLE_STATS(ownname => 'TRY', tabname => 'TBL10');

set head off trim on linesize 1000 pagesize 0 long 1000000 longchunksize 1000000
select REPORT, MAXDIFFPCT from table(DBMS_STATS.DIFF_TABLE_STATS_IN_PENDING('TRY', 'TBL10'));

-- OPTIMIZER_USE_PENDING_STATISTICS を TRUE にする。
alter session set OPTIMIZER_USE_PENDING_STATISTICS = TRUE ;

-- Publish or DELETE Pending Statistics
BEGIN
  DBMS_STATS.PUBLISH_PENDING_STATS(ownname => 'TRY', tabname => 'TBL10', no_invalidate => FALSE);
END;
/

*セッション単位
ALTER SESSION FORCE PARALLEL QUERY PARALLEL 2;

*オブジェクト単位
スキーマオブジェクトのparallel属性に設定

*文単位
SELECT /*+ PARALLEL(XXX,2) */ ... from XXX;

ヒント句でパラレル指定するか、セッション単位でパラレル指定する。
例
<セッション単位>
ALTER SESSION FORCE PARALLEL DDL PARALLEL 4;

<文単位>
CREATE TABLE ... PARALLEL 4 AS SELECT...
CREATE INDEX ... PARALLEL 4;
ALTER INDEX ... REBUILD PARALLEL 4;

(注意)CREATE TABLE ... AS SELECT ...に関してはCREATE操作がパラレル化されれば問い合わせ(SELECT)部分も同一パラレル度でパラレル化される。仮にCREATE部分がパラレル化されない場合は、問い合わせ(SELECT)部分は、SELECT部分にPARALLELヒントがある場合、または選択される表(またはパーティション索引)にパラレル宣言がある場合のみパラレル化される。

1実行されている状態かつ、表定義または1で既にパラレル度を付与している場合は普通にDML文実行するとパラレル化される。
またはヒント句をつけて明示的にパラレル化する。

1.DML実行前にパラレルDMLを有効化する。これをしないと、後続で仮にント句をつけてもパラレル化されない。
alter session force parallel dml parallel 2;

2.パラレル処理は下記の優先順位で反映される。
DML文のパラレル・ヒントに指定されている値
alter session force parallel dml parallel 文で指定した値
表作成時に指定したパラレル度

<特殊なケース: INSERT INTO ... SELECT ...文の場合>
INSERT処理とSELECT処理両方に対して、ヒント句または対象表定義により、パラレル度を分割指定可能できる。

ダイレクトパスインサートを用いる。INSERT INTO ... SELECT ...で高速にデータを挿入する。
ダイレクトパスインサートはバッファキャッシュを経由せず、またREDOを生成せずに、
直接ディスク上のデータファイルに書き込みができるので高速にデータ挿入ができる。
バッファキャッシュを経由しないので、データをキャッシュから追い出すことがないというメリットもある。

*APPEND句付与
INSERT /*+ APPEND */ INTO 新テーブル SELECT * FROM 旧テーブル;

<ダイレクトパスインサートの注意点>
1.　REDO情報が書き込まれないので、コミット処理やロールバック処理等のトランザクションを制御することができない。(実行したらコミットかロールバックを実行しないと後続処理は実行不可)
2.　実行すると「テーブルロック」がかかる。
3.　ハイウォーターマーク以降からインサート処理が実行される。

alter table ... row store compress basic;
alter tablespace ... row store compress basic;
alter table ... move row store compress basic;

alter table ... row store compress advanced;
alter tablespace ... row store compress advanced;
alter table ... move row store compress advanced;

insert /*+ APPEND */ into ITEM1 select * from ITEM;

set pagesize 1000 linesize 10000
col SEGMENT_NAME for a24
select SEGMENT_NAME, TABLESPACE_NAME, EXTENT_ID, BYTES, BLOCKS from USER_EXTENTS order by 1,2,3;  

SELECT TO_CHAR(BEGIN_TIME,'MM/DD HH24:MI') AS BEGIN_TIME, TO_CHAR(END_TIME,'MM/DD HH24:MI') AS END_TIME, UNDOBLKS, MAXQUERYLEN,MAXQUERYID,TUNED_UNDORETENTION FROM V$UNDOSTAT order by BEGIN_TIME;

col bytes for 999,999,999,999
select status, sum(bytes)/1024/1024 as mbytes from dba_undo_extents where tablespace_name = 'UNDOTBLSP01' and status in ('ACTIVE','UNEXPIRED','EXPIRED') group by status order by status;

col bytes for 999,999,999,999
select sum(bytes)/1024/1024 as mbytes from dba_free_space where tablespace_name = 'UNDOTBLSP01';

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
   job_name           =>  'statspack_remove', 
   job_type           =>  'PLSQL_BLOCK',
   job_action         =>  'BEGIN statspack.purge(2); END;',
   start_date         =>  TO_DATE('2021/01/31 17:00:00','yyyy/mm/dd hh24:mi:ss'),
   repeat_interval    =>  'FREQ=HOURLY',
   enabled            =>  TRUE   
   );
END;
/

BEGIN
   DBMS_SCHEDULER.DROP_JOB('statspack_remove');
END;
/

col owner format a20
col job_name format a20
col job_action format a70
col start_date format a40
col end_date format a40
col last_start_date format a40
select OWNER,JOB_NAME,JOB_TYPE,JOB_ACTION,START_DATE,END_DATE,ENABLED,STATE,RUN_COUNT,FAILURE_COUNT,LAST_START_DATE from ALL_SCHEDULER_JOBS;

col CLIENT_NAME format a50
col MEAN_JOB_DURATION format a30
col MAX_DURATION_LAST_7_DAYS format a30
select CLIENT_NAME,STATUS,WINDOW_GROUP, MEAN_JOB_DURATION,MAX_DURATION_LAST_7_DAYS from DBA_AUTOTASK_CLIENT;

CLIENT_NAME                   |STATUS  |WINDOW_GROUP                            |MEAN_JOB_DURATION         |MAX_DURATION_LAST_7_DAYS
--------------------------------------------------|--------|----------------------------------------------------------------|------------------------------|------------------------------
sql tuning advisor                |ENABLED |ORA$AT_WGRP_SQ                          |+000000000 00:00:00.065306122 |+000 00:00:00
auto optimizer stats collection           |ENABLED|ORA$AT_WGRP_OS                           |+000000000 00:00:37.436734694 |+000 00:07:07
auto space advisor                |ENABLED |ORA$AT_WGRP_SA                          |+000000000 00:00:05.044897959 |+000 00:00:29

BEGIN                                                                                      
DBMS_AUTO_TASK_ADMIN.DISABLE(                                                              
    client_name => 'auto optimizer stats collection',
    operation => NULL,                                                                     
    window_name => NULL);                                                                  
END;                                                                                       
/

BEGIN                                                                                      
DBMS_AUTO_TASK_ADMIN.ENABLE(                                                              
    client_name => 'auto optimizer stats collection',
    operation => NULL,                                                                     
    window_name => NULL);                                                                  
END;                                                                                       
/

-- WINDOW_GROUP_NAME に確認したいWINDOW_GROUPを指定。
select * from DBA_SCHEDULER_WINGROUP_MEMBERS where WINDOW_GROUP_NAME = 'ORA$AT_WGRP_OS';   

col WINDOW_NAME format a30
col REPEAT_INTERVAL for a70
col DURATION for a15
col ENABLED for a7
col WINDOW_NEXT_TIME for a50
col AUTOTASK_STATUS for a15
col OPTIMIZER_STATS for a15
col SEGMENT_ADVISOR for a15
col SQL_TUNE_ADVISOR for a16
select s.WINDOW_NAME
,s.REPEAT_INTERVAL
,s.DURATION
,s.ENABLED
,a.WINDOW_NEXT_TIME
,a.WINDOW_ACTIVE
,a.AUTOTASK_STATUS
,a.OPTIMIZER_STATS
,a.SEGMENT_ADVISOR
,a.SQL_TUNE_ADVISOR
from DBA_SCHEDULER_WINDOWS s,
DBA_AUTOTASK_WINDOW_CLIENTS a
where s.WINDOW_NAME = a.WINDOW_NAME;

WINDOW_NAME           |REPEAT_INTERVAL                               |DURATION       |ENABLED|WINDOW_NEXT_TIME                  |WINDO|AUTOTASK_STATUS|OPTIMIZER_STATS|SEGMENT_ADVISOR|SQL_TUNE_ADVISOR
------------------------------|----------------------------------------------------------------------|---------------|-------|--------------------------------------------------|-----|---------------|---------------|---------------|----------------
MONDAY_WINDOW             |freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0             |+000 04:00:00  |TRUE   |08-FEB-21 10.00.00.000000 PM ETC/UTC      |FALSE|ENABLED        |ENABLED        |ENABLED        |ENABLED
TUESDAY_WINDOW            |freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0             |+000 04:00:00  |TRUE   |09-FEB-21 10.00.00.000000 PM ETC/UTC      |FALSE|ENABLED        |ENABLED        |ENABLED        |ENABLED
WEDNESDAY_WINDOW          |freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0             |+000 04:00:00  |TRUE   |10-FEB-21 10.00.00.000000 PM ETC/UTC      |FALSE|ENABLED        |ENABLED        |ENABLED        |ENABLED
THURSDAY_WINDOW           |freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0             |+000 04:00:00  |TRUE   |11-FEB-21 10.00.00.000000 PM ETC/UTC      |FALSE|ENABLED        |ENABLED        |ENABLED        |ENABLED
FRIDAY_WINDOW             |freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0             |+000 04:00:00  |TRUE   |12-FEB-21 10.00.00.000000 PM ETC/UTC      |FALSE|ENABLED        |ENABLED        |ENABLED        |ENABLED
SATURDAY_WINDOW           |freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0          |+000 20:00:00  |TRUE   |06-FEB-21 06.00.00.000000 AM ETC/UTC      |TRUE |ENABLED        |ENABLED        |ENABLED        |ENABLED
SUNDAY_WINDOW             |freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0          |+000 20:00:00  |TRUE   |07-FEB-21 06.00.00.000000 AM ETC/UTC      |FALSE|ENABLED        |ENABLED        |ENABLED        |ENABLED

7 rows selected.

BEGIN                                                                                      
DBMS_AUTO_TASK_ADMIN.DISABLE(                                                              
    client_name => 'auto optimizer stats collection',
    operation => NULL,                                                                     
    window_name => 'SUNDAY_WINDOW');                                                       
END;                                                                                       
/

BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(                                                              
    name      => 'FRIDAY_WINDOW',                                                          
    attribute => 'REPEAT_INTERVAL',                                                        
    value     => 'freq=daily;byday=FRI;byhour=21;byminute=0; bysecond=0');                                                              
DBMS_SCHEDULER.SET_ATTRIBUTE(                                                              
    name      => 'FRIDAY_WINDOW',                                                          
    attribute => 'DURATION',                                                               
    value     => numtodsinterval(3, 'hour'));                                                                                                
END;                                                                                       
/  

COLUMN CLIENT_NAME       FORMAT A33
COLUMN WINDOW_NAME       FORMAT A20
COLUMN WINDOW_START_TIME FORMAT A20
COLUMN JOB_START_TIME    FORMAT A30
COLUMN JOB_DURATION      FORMAT A20
COLUMN JOB_DURATION_HMS  FORMAT A12
SELECT CLIENT_NAME
     , WINDOW_NAME
     , JOB_STATUS
     , TO_CHAR(WINDOW_START_TIME, 'YYYY/MM/DD HH24:MI:SS') AS WINDOW_START_TIME
     , TO_CHAR(JOB_START_TIME, 'YYYY/MM/DD HH24:MI:SS') AS JOB_START_TIME
     , JOB_DURATION
     , JOB_ERROR
     , JOB_INFO
  FROM DBA_AUTOTASK_JOB_HISTORY
 ORDER BY CLIENT_NAME, JOB_START_TIME;

SELECT VALUE FROM DBA_SCHEDULER_GLOBAL_ATTRIBUTE WHERE ATTRIBUTE_NAME='DEFAULT_TIMEZONE';

begin
  dbms_scheduler.set_scheduler_attribute(
    attribute => 'default_timezone',
    value => 'Asia/Tokyo'
  );
end;
/

set lin 10000
col segment format a60
select tablespace_name tablespace,file_id,block_id,blocks,owner||'.'||segment_name segment
from dba_extents where tablespace_name='TBLSP01'
union
select tablespace_name tablespace,file_id,block_id,blocks,' '
from dba_free_space where tablespace_name='TBLSP01'
order by 1,2,3;

audit session by MARIO by access; 
audit all statements by MARIO by access; 

column user_name format a12
column proxy_name format a12
column audit_option format a20
column success format a12
column failure format a12
select * from DBA_STMT_AUDIT_OPTS;

column owner format a13
column object_name format a13
column object_type format a13
select * from DBA_OBJ_AUDIT_OPTS;