기존 블로그

대상 테이블에 대한 Before/After의 값이 보존할 필요가 없을 경우에는 2개의 대상 테이블에 대해 DB기능인 FGA(Fine Grained Auditing)를 설정하시면, 별도의 Trigger 생성 없이 Auditing 이 가능하다.
FGA를 통해 DML 이력뿐만아니라, SELECT 이력도 가능하면, 또한, 특정 조건의 데이터 및 특정 컬럼에 대해서만도 설정가능하다.

이때 Auditing 되는 정보는 DBA_FGA_AUDIT_TRAIL에 정보들이 로깅된다.

DBA_FGA_AUDIT_TRAIL displays all audit records for fine-grained auditing.
Column  Datatype  NULL  Description
SESSION_ID  NUMBER  NOT NULL  Session id of the query
TIMESTAMP  DATE     Date and time of the query in the local database session time zone
DB_USER  VARCHAR2(30)     Database username who executed the query
OS_USER  VARCHAR2(255)     Operating system username who executed the query
USERHOST  VARCHAR2(128)     Client host machine name
CLIENT_ID  VARCHAR2(64)     Client identifier in each Oracle session
ECONTEXT_ID  VARCHAR2(64)     Application execution context identifier
EXT_NAME  VARCHAR2(4000)     External name
OBJECT_SCHEMA  VARCHAR2(30)     Owner of the table or view
OBJECT_NAME  VARCHAR2(128)     Name of the table or view
POLICY_NAME  VARCHAR2(30)     Name of the Fine-Grained Auditing Policy
SCN  NUMBER     System change number (SCN) of the query
SQL_TEXT  NVARCHAR2(2000)     SQL text of the query
SQL_BIND  NVARCHAR2(2000)     Bind variable data of the query
COMMENT$TEXT  VARCHAR2(4000)     Comments
STATEMENT_TYPE      VARCHAR2(7)     Statement type of the query: SELECT/INSERT/UPDATE/DELETE
EXTENDED_TIMESTAMP  TIMESTAMP(6) WITH TIME ZONE     Timestamp of the query in UTC (Coordinated Universal Time) time zone
PROXY_SESSIONID  NUMBER     Proxy session serial number, if an enterprise user has logged in through the proxy mechanism
GLOBAL_UID  VARCHAR2(32)     Global user identifier for the user, if the user has logged in as an enterprise user
INSTANCE_NUMBER  NUMBER     Instance number as specified by the INSTANCE_NUMBER initialization parameter
OS_PROCESS  VARCHAR2(16)     Operating System process identifier of the Oracle process
TRANSACTIONID  RAW(8)     Transaction identifier of the transaction in which the object is accessed or modified
STATEMENTID  NUMBER     Numeric ID for each statement run (a statement may cause many actions)
ENTRYID  NUMBER     Numeric ID for each audit trail entry in the session

# sub
logmnr() {
  sqlplus '/as sysdba' <<EOF
    SET TIME ON TIMING ON ECHO ON
    SPOOL $1.log

    EXECUTE DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME => '/archive_temp/$1.arc', OPTIONS => DBMS_LOGMNR.NEW);
    EXECUTE DBMS_LOGMNR.START_LOGMNR( OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.COMMITTED_DATA_ONLY);

    select  * from V\$LOGMNR_CONTENTS
    where   row_id='AAALlqAKgAADUP9AAX';

    EXECUTE DBMS_LOGMNR.END_LOGMNR;

    SPOOL OFF
    EXIT
EOF
}

# main
logmnr ERPPRD_1_18735_672748494
logmnr ERPPRD_1_18736_672748494
logmnr ERPPRD_1_18737_672748494
logmnr ERPPRD_2_10368_672748494
logmnr ERPPRD_1_18738_672748494
logmnr ERPPRD_1_18739_672748494
logmnr ERPPRD_1_18740_672748494
logmnr ERPPRD_2_10369_672748494
logmnr ERPPRD_1_18741_672748494
logmnr ERPPRD_1_18742_672748494
logmnr ERPPRD_2_10370_672748494
logmnr ERPPRD_1_18743_672748494
logmnr ERPPRD_1_18744_672748494
logmnr ERPPRD_2_10371_672748494
logmnr ERPPRD_1_18745_672748494
logmnr ERPPRD_1_18746_672748494
logmnr ERPPRD_2_10372_672748494
logmnr ERPPRD_1_18747_672748494
logmnr ERPPRD_1_18748_672748494
logmnr ERPPRD_2_10373_672748494
logmnr ERPPRD_1_18749_672748494
logmnr ERPPRD_1_18750_672748494
logmnr ERPPRD_1_18751_672748494
logmnr ERPPRD_2_10374_672748494
logmnr ERPPRD_1_18752_672748494
logmnr ERPPRD_1_18753_672748494
logmnr ERPPRD_2_10375_672748494
logmnr ERPPRD_1_18754_672748494

REDO log file과 관련된 데이터베이스 복구 방법
** 테스트 환경
- OS : Windows XP
- DB : Oracle 9.2.0.1
- redo log : 3개의 Group, 각 1개의 member로 구성

** 시나리오
1. redo log의 각 Group에 각각 member 1개씩 추가한 후
임의의 member가 삭제되거나 파일에 문제가 있을 경우 복구 방법
(단, Current redo log중 반드시 1개의 member는 정상이어야 함)

-> 아무런 추가작업 없이 데이터베이스를 Open할 수 있음.
(Oracle이 스스로 쓸수 없게 된 redo log파일은 Invalid 상태로 만들어줌)

2. 모든 redo log파일이 삭제되었거나 파일에 문제가 생겨 쓸 수 없게 된 경우

-> Init parameter에 Hidden parameter인 _allow_resetlogs_corruption=true
항목을 추가한 후 recover database 하고, resetlogs 옵션으로 DB를 Open할 수 있다.

a. Init 파일에 _allow_resetlogs_corruption=true 추가
b. SQL> startup mount;
c. SQL> recover database until cancel;
d. SQL> alter database open resetlogs;

** 테스트 로그

-- 첫번째 시나리오

13:55:18 SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER
---------- -------------- -------------- --------------------------------------------
1 ONLINE D:\ORACLE\ORA92\ORA9\REDO01.LOG
2 ONLINE D:\ORACLE\ORA92\ORA9\REDO02.LOG
3 ONLINE D:\ORACLE\ORA92\ORA9\REDO03.LOG

경 과: 00:00:00.02
13:55:26 SQL> alter database add logfile member
13:56:00 2 'D:\ORACLE\ORA92\ORA9\REDO11.log' to group 1,
13:56:10 3 'D:\ORACLE\ORA92\ORA9\REDO12.log' to group 2,
13:56:17 4 'D:\ORACLE\ORA92\ORA9\REDO13.log' to group 3;

데이타베이스가 변경되었습니다.

경 과: 00:00:17.08

13:55:18 SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER
---------- -------------- -------------- --------------------------------------------
1 ONLINE D:\ORACLE\ORA92\ORA9\REDO01.LOG
2 ONLINE D:\ORACLE\ORA92\ORA9\REDO02.LOG
3 ONLINE D:\ORACLE\ORA92\ORA9\REDO03.LOG
1 ONLINE D:\ORACLE\ORA92\ORA9\REDO11.LOG
2 ONLINE D:\ORACLE\ORA92\ORA9\REDO12.LOG
3 ONLINE D:\ORACLE\ORA92\ORA9\REDO13.LOG

경 과: 00:00:00.02

13:56:42 SQL> alter system switch logfile;

시스템이 변경되었습니다.

경 과: 00:00:00.04
13:56:51 SQL> /

시스템이 변경되었습니다.

경 과: 00:00:05.01
13:56:56 SQL> /

시스템이 변경되었습니다.

경 과: 00:00:00.04
13:56:56 SQL> /

시스템이 변경되었습니다.

경 과: 00:00:00.05
13:56:57 SQL> shutdown immediate;
데이터베이스가 닫혔습니다.
데이터베이스가 마운트 해제되었습니다.
ORACLE 인스턴스가 종료되었습니다.


-- 이부분에서 redo log의 2번째 member 파일을 물리적으로 삭제함.


13:57:09 SQL> startup
ORACLE 인스턴스가 시작되었습니다.

Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
데이터베이스가 마운트되었습니다.
데이터베이스가 열렸습니다.


-- 아무런 복구절차 없이 DB가 Open됨.

13:58:06 SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER
---------- -------------- -------------- --------------------------------------------
1 ONLINE D:\ORACLE\ORA92\ORA9\REDO01.LOG
2 ONLINE D:\ORACLE\ORA92\ORA9\REDO02.LOG
3 ONLINE D:\ORACLE\ORA92\ORA9\REDO03.LOG
1 INVALID ONLINE D:\ORACLE\ORA92\ORA9\REDO11.LOG
2 INVALID ONLINE D:\ORACLE\ORA92\ORA9\REDO12.LOG
3 INVALID ONLINE D:\ORACLE\ORA92\ORA9\REDO13.LOG


6 개의 행이 선택되었습니다.

경 과: 00:00:00.01
13:58:11 SQL> alter system switch logfile;

시스템이 변경되었습니다.

경 과: 00:00:00.03
13:58:18 SQL> /

시스템이 변경되었습니다.

경 과: 00:00:00.02
13:58:19 SQL> /

시스템이 변경되었습니다.

경 과: 00:00:00.05
13:58:20 SQL> /

시스템이 변경되었습니다.

경 과: 00:00:00.02
13:58:20 SQL> /

시스템이 변경되었습니다.

경 과: 00:00:00.01
13:58:21 SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER
---------- -------------- -------------- --------------------------------------------
1 ONLINE D:\ORACLE\ORA92\ORA9\REDO01.LOG
2 ONLINE D:\ORACLE\ORA92\ORA9\REDO02.LOG
3 ONLINE D:\ORACLE\ORA92\ORA9\REDO03.LOG
1 INVALID ONLINE D:\ORACLE\ORA92\ORA9\REDO11.LOG
2 INVALID ONLINE D:\ORACLE\ORA92\ORA9\REDO12.LOG
3 INVALID ONLINE D:\ORACLE\ORA92\ORA9\REDO13.LOG


6 개의 행이 선택되었습니다.

경 과: 00:00:00.02
13:58:26 SQL> shutdown immediate;
데이터베이스가 닫혔습니다.
데이터베이스가 마운트 해제되었습니다.
ORACLE 인스턴스가 종료되었습니다.

-- 두번째 시나리오

13:58:38 SQL> startup
ORACLE 인스턴스가 시작되었습니다.

Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
데이터베이스가 마운트되었습니다.
데이터베이스가 열렸습니다.
13:59:04 SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER
---------- -------------- -------------- --------------------------------------------
1 ONLINE D:\ORACLE\ORA92\ORA9\REDO01.LOG
2 ONLINE D:\ORACLE\ORA92\ORA9\REDO02.LOG
3 ONLINE D:\ORACLE\ORA92\ORA9\REDO03.LOG
1 INVALID ONLINE D:\ORACLE\ORA92\ORA9\REDO11.LOG
2 INVALID ONLINE D:\ORACLE\ORA92\ORA9\REDO12.LOG
3 INVALID ONLINE D:\ORACLE\ORA92\ORA9\REDO13.LOG


6 개의 행이 선택되었습니다.

경 과: 00:00:00.03
13:59:11 SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIV STATUS FIRST
_CHANGE# FIRST_TI
---------- ---------- ---------- ---------- ---------- ------ -------------------------------- -----
-------- --------
1 1 25 104857600 2 NO CURRENT
518277 06/04/04
2 1 23 104857600 2 NO INACTIVE
518273 06/04/04
3 1 24 104857600 2 NO INACTIVE
518275 06/04/04

경 과: 00:00:00.00
14:00:17 SQL> shutdown immediate;
데이터베이스가 닫혔습니다.
데이터베이스가 마운트 해제되었습니다.
ORACLE 인스턴스가 종료되었습니다.

-- redo log 중 정상이던 1번째 member 파일을 물리적으로 삭제함.

14:04:52 SQL> startup
ORACLE 인스턴스가 시작되었습니다.

Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
데이터베이스가 마운트되었습니다.
ORA-00314: 로그 1(스레드 1의, 시퀀스번호가 일)는 와 일치하지 않습니다
ORA-00312: 온라인 로그 1 스레드 1: 'D:\ORACLE\ORA92\ORA9\REDO01.LOG'
ORA-00312: 온라인 로그 1 스레드 1: 'D:\ORACLE\ORA92\ORA9\REDO11.LOG'

-- 사용할 수 있는 redo log 파일이 하나도 없으므로 DB가 Open되지 않음.(mount상태)


14:05:53 SQL>
14:05:54 SQL>
14:05:54 SQL> select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME
--------------- --------------------------------
HOST_NAME
----------------------------------------------------------------------------------------------------
----------------------------
VERSION STARTUP_ STATUS PARALL THREAD# ARCHIVER
LOG_SWITCH_WAIT LOGINS
---------------------------------- -------- ------------------------ ------ ---------- -------------
- ---------------------- --------------------
SHUTDO DATABASE_STATUS INSTANCE_ROLE ACTIVE_STATE
------ ---------------------------------- ------------------------------------ ------------------
1 ora9
HLIM
9.2.0.1.0 06/04/04 MOUNTED NO 1 STOPPED
ALLOWED
NO ACTIVE PRIMARY_INSTANCE NORMAL


경 과: 00:00:00.00
14:05:58 SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER
---------- -------------- -------------- --------------------------------------------
1 ONLINE D:\ORACLE\ORA92\ORA9\REDO01.LOG
2 ONLINE D:\ORACLE\ORA92\ORA9\REDO02.LOG
3 ONLINE D:\ORACLE\ORA92\ORA9\REDO03.LOG
1 INVALID ONLINE D:\ORACLE\ORA92\ORA9\REDO11.LOG
2 INVALID ONLINE D:\ORACLE\ORA92\ORA9\REDO12.LOG
3 INVALID ONLINE D:\ORACLE\ORA92\ORA9\REDO13.LOG

6 개의 행이 선택되었습니다.

경 과: 00:00:00.02

14:08:29 SQL> select * from v$recover_file;

선택된 레코드가 없습니다.

경 과: 00:00:00.08

-- Oracle 9i로서 Init parameter 를 spfile로 사용중이었으므로
-- init parameter를 수정하기 위해 spfile을 pfile로 변경함.

14:08:47 SQL> create pfile from spfile;

파일이 생성되었습니다.

경 과: 00:00:00.01
14:10:12 SQL> shutdown immediate;
ORA-01109: 데이터베이스가 개방되지 않습니다


데이터베이스가 마운트 해제되었습니다.
ORACLE 인스턴스가 종료되었습니다.

-- Init parameter에 _allow_resetlogs_corruption=true 항목을 추가한 후 DB를 mount함.

14:10:28 SQL> startup mount;
ORACLE 인스턴스가 시작되었습니다.

Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
데이터베이스가 마운트되었습니다.
14:11:50 SQL> alter database open resetlogs;
alter database open resetlogs
*
1행에 오류:
ORA-01139: RESETLOGS 옵션은 불완전한 데이터베이스의 복구 후에만 유효합니다


경 과: 00:00:00.02
14:12:00 SQL> recover database until cancel;
매체 복구가 완료되었습니다.
14:12:34 SQL> alter database open resetlogs;

데이타베이스가 변경되었습니다.

경 과: 00:00:42.03
14:13:28 SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER
---------- -------------- -------------- --------------------------------------------
1 ONLINE D:\ORACLE\ORA92\ORA9\REDO01.LOG
2 ONLINE D:\ORACLE\ORA92\ORA9\REDO02.LOG
3 ONLINE D:\ORACLE\ORA92\ORA9\REDO03.LOG
1 ONLINE D:\ORACLE\ORA92\ORA9\REDO11.LOG
2 ONLINE D:\ORACLE\ORA92\ORA9\REDO12.LOG
3 ONLINE D:\ORACLE\ORA92\ORA9\REDO13.LOG


6 개의 행이 선택되었습니다.

-- 기존에 Invalid 상태였던 redo log파일까지 모두 복구하였음.

경 과: 00:00:00.04
14:13:36 SQL> shutdown immediate;
데이터베이스가 닫혔습니다.
데이터베이스가 마운트 해제되었습니다.
ORACLE 인스턴스가 종료되었습니다.
14:15:37 SQL>

ALTER TABLE CTXSYS.DR$WAITING ENABLE ROW MOVEMENT;
ALTER TABLE CTXSYS.DR$WAITING SHRINK SPACE COMPACT CASCADE;
ALTER TABLE CTXSYS.DR$WAITING SHRINK SPACE CASCADE;
ALTER TABLE CTXSYS.DR$WAITING DISABLE ROW MOVEMENT;

_trace_file_public=true

다음의 내용들은 개인적인 권장하는 사항들이다.
오라클 10g의 경우 엔진 설치를 위한 디렉토리 공간을 20GB 이상 주는 것을 권장한다.
오라클의 adump, bdump, udump, 리스너 로그, 아카이브 로그 등을 백업할 수 있으면 백업하는 것을 권장한다.
하지만 대부분의 경우 백업의 필요성이 절실하지 않기 때문에 삭제한다.

각종 로그의 자동관리를 위하여 첨부한 파일과 같이 CRONTAB에 등록하여 관리하면 편리하다.
각각의 로그관리에 대한 정책이 필요하다.

정책 예시)
1. alert 로그       : 월별로 로그를 관리. 영구 보관하는 것이 좋다. compress 명령으로 압축하여 보관.
2. adump audit 파일 : 180일 정도 유지, 매일 180일이 지난 trc파일을 삭제
3. bdump trace 파일 : 90일 정도 유지, 매일 90일이 지난 trc파일을 삭제
4. udump trace 파일 : 90일 정도 유지, 매일 90일이 지난 trc파일을 삭제
5. 리스너 로그      : 리스너를 로깅하도록 설정했을 경우 월별로 로그를 관리. 180일이 지난 파일은 삭제. compress 명령으로 압축하여 보관.
6. 아카이브로그 파일 : 기본적으로 1주일에 1번 이상 FULL BACKUP을 받을 경우
   백업 툴에서 아카이브로그를 관리해 주지 않을 경우 등록하여 사용 7일전 아카이브로그 파일 삭제.

쉘 예시)
쉘 스크립트 작성 시 오타에 주의할 것. 반드시 테스트 후 적용할 것
#######################################################
#### alert.log                                     ####
#### (매월 1일 실행할 수 있도록 cron job 등록 )    ####
#######################################################
nDate=`date +%Y%m%d`
cp $ORACLE_BASE/admin/TESTDB/bdump/alert_TESTDB.log $ORACLE_BASE/TESTDB/bdump/alert_TESTDB.log.$nDate
cat /dev/null > $ORACLE_BASE/admin/TESTDB/bdump/alert_TESTDB.log
compress -vf $ORACLE_BASE/TESTDB/bdump/alert_TESTDB.log.$nDate

#######################################################
#### listener.log                                  ####
#### (매월 1일 실행할 수 있도록 cron job 등록 )    ####
#######################################################
nDate=`date +%Y%m%d`
cp $ORACLE_HOME/network/admin/listener.log $ORACLE_HOME/network/admin/listener.log.$nDate
cat /dev/null > $ORACLE_HOME/network/admin/listener.log
compress -vf $ORACLE_HOME/network/admin/listener.log.$nDate

#######################################################
#### audit                                         ####
#######################################################
# 180일이 지난 *.aud를 찾아 삭제
find $ORACLE_BASE/admin/TESTDB/adump \( -ctime +180 -name '*.aud' \) -exec rm -f {} \;

#######################################################
#### .trc                                          ####
# 90일이 지난 *.trc를 찾아 삭제                    ####
#######################################################
find $ORACLE_BASE/admin/TESTDB/bdump \( -ctime +90 -name '*.trc' \) -exec rm -f {} \;
find $ORACLE_BASE/admin/TESTDB/udump \( -ctime +90 -name '*.trc' \) -exec rm -f {} \;

#######################################################
#### archive log                                   ####
#######################################################
# 7일이 지난 *.arc를 찾아 삭제
find /archive_log \( -ctime +7 -name '*.arc' \) -exec rm -f {} \;

SQL*PLUS - SET Statement
========================

http://ss64.com/ora/syntax-sqlplus-set.html



Set sqlplus system settings and defaults.

Syntax:

   SET option value

   SHO[W] option

Options: most of the options listed below have an abbreviated and a long form
           e.g. APPINFO or APPI will do the same thing

APPI[NFO] {ON|OFF|text}
   Application info for performance monitor (see DBMS_APPLICATION_INFO)

ARRAY[SIZE] {15|n}
   Fetch size (1 to 5000) the number of rows that will be retrieved in one go.

AUTO[COMMIT] {OFF|ON|IMM[EDIATE]|n}
   Autocommit commits after each SQL command or PL/SQL block

AUTOP[RINT] {OFF|ON}
   Automatic PRINTing of bind variables.(see PRINT)

AUTORECOVERY [ON|OFF]
   Configure the RECOVER command to automatically apply
   archived redo log files during recovery - without any user confirmation.

AUTOT[RACE] {OFF|ON|TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
   Display a trace report for SELECT, INSERT, UPDATE or DELETE statements
   EXPLAIN shows the query execution path by performing an EXPLAIN PLAN.
   STATISTICS displays SQL statement statistics.
   Using ON or TRACEONLY with no explicit options defaults to EXPLAIN STATISTICS

BLO[CKTERMINATOR] {.|c|OFF|ON}
   Set the non-alphanumeric character used to end PL/SQL blocks to c

CMDS[EP] {;|c|OFF|ON}
   Change or enable command separator - default is a semicolon (;)

COLSEP { |text}
   The text to be printed between SELECTed columns normally a space.

COM[PATIBILITY] {V5|V6|V7|V8|NATIVE}
   Version of oracle - see also init.ora COMPATIBILITY=
   You can set this back by up to 2 major versions e.g. Ora 9 supports 8 and 7

CON[CAT] {.|c|OFF|ON}
   termination character for substitution variable reference
   default is a period.

COPYC[OMMIT] {0|n}
   The COPY command will fetch n batches of data between commits.
   (n= 0 to 5000) the size of each fetch=ARRAYSIZE.
   If COPYCOMMIT = 0, COPY will commit just once - at the end.

COPYTYPECHECK {OFF|ON}
   Suppres the comparison of datatypes while inserting or appending to DB2

DEF[INE] {&|c|OFF|ON}
   c =  the char used to prefix substitution variables.
   ON or OFF controls whether to replace substitution variables with their values.
   (this overrides SET SCAN)

DESCRIBE [DEPTH {1|n|ALL}][LINENUM {ON|OFF}][INDENT {ON|OFF}]
   Sets the depth of the level to which you can recursively describe an object
   (1 to 50) see the DESCRIBE command

ECHO {OFF|ON}
   Display commands as they are executed

EMB[EDDED] {OFF|ON}
   OFF = report printing will start at the top of a new page.
   ON = report printing may begin anywhere on a page.

ESC[APE] {\|c|OFF|ON}
    Defines the escape character. OFF undefines. ON enables.

FEED[BACK] {6|n|OFF|ON}
   Display the number of records returned (when rows >= n )
   OFF (or n=0) will turn the display off
   ON will set n=1

FLAGGER {OFF|ENTRY|INTERMED[IATE]|FULL}
   Checks to make sure that SQL statements conform to the ANSI/ISO SQL92 standard.
   non-standard constructs are flagged as errors and displayed
   See also ALTER SESSION SET FLAGGER.

FLU[SH] {OFF|ON}
   Buffer display output (OS)
   (no longer used in Oracle 9)

HEA[DING] {OFF|ON}
   print column headings

HEADS[EP] {||c|OFF|ON}
   Define the heading separator character (used to divide a column heading onto > one line.)
   OFF will actually print the heading separator char
   see also: COLUMN command

INSTANCE [instance_path|LOCAL]
   Change the default instance for your session, this command may only be issued when
   not already connected and requires Net8

LIN[ESIZE] {150|n}
   Width of a line (before wrapping to the next line)
   Earlier versions default to 80, Oracle 9 is 150

LOBOF[FSET] {n|1}
   Starting position from which CLOB and NCLOB data is retrieved and displayed

LOGSOURCE [pathname]
   Change the location from which archive logs are retrieved during recovery
   normally taken from LOG_ARCHIVE_DEST

LONG {80|n}
   Set the maximum width (in chars) for displaying and copying LONG values.

LONGC[HUNKSIZE] {80|n}
   Set the fetch size (in chars) for retrieving LONG values.

MARK[UP] HTML [ON|OFF]
  [HEAD text] [BODY text] [TABLE text]
     [ENTMAP {ON|OFF}][SPOOL {ON|OFF}]
        [PRE[FORMAT] {ON|OFF}]
   Output HTML text, which is the output used by iSQL*Plus.

NEWP[AGE] {1|n}
   The number of blank lines between the top of each page and the top title.
   0 = a formfeed between pages.

NULL text
   Replace a null value with 'text'
   The NULL clause of the COLUMN command will override this for a given column.

NUMF[ORMAT] format
   The default number format.
   see COLUMN FORMAT.

NUM[WIDTH] {10|n}
   The default width for displaying numbers.

PAGES[IZE] {14|n}
   The height of the page - number of lines.
   0 will suppress all headings, page breaks, titles

PAU[SE] {OFF|ON|text}
   press [Return] after each page
   enclose 'text' in single quotes

RECSEP {WR[APPED]|EA[CH]|OFF}
   Print a single line of the RECSEPCHAR between each record.
   WRAPPED = print only for wrapped lines
   EACH=print for every row

RECSEPCHAR {_|c}
   Define the RECSEPCHAR character, default= ' '

SCAN {OFF|ON}
   OFF = disable substitution variables and parameters

SERVEROUT[PUT] {OFF|ON} [SIZE n] [FOR[MAT] {WRA[PPED]|WOR[D_WRAPPED]|TRU[NCATED]}]
   whether to display the output of stored procedures (or PL/SQL blocks)
   i.e., DBMS_OUTPUT.PUT_LINE

   SIZE = buffer size (2000-1,000,000) bytes

SHOW[MODE] {OFF|ON}
   Display old and new settings of a system variable

SPA[CE] {1|n}
   The number of spaces between columns in output (1-10)

SQLBL[ANKLINES] {ON|OFF}
   Allow blank lines within an SQL command. reverts to OFF after the curent command/block.

SQLC[ASE] {MIX[ED]|LO[WER]|UP[PER]}
   Convert the case of SQL commands and PL/SQL blocks
   (but not the SQL buffer itself)

SQLPLUSCOMPAT[IBILITY] {x.y[.z]}
  Set the behavior or output format of VARIABLE to that of the
  release or version specified by x.y[.z].

SQLCO[NTINUE] {> |text}
   Continuation prompt (used when a command is continued on an additional line using a hyphen -)

SQLN[UMBER] {OFF|ON}
   Set the prompt for the second and subsequent lines of a command or PL/SQL block.
   ON = set the SQL prompt = the line number.
   OFF = set the SQL prompt = SQLPROMPT.

SQLPRE[FIX] {#|c}
   set a non-alphanumeric prefix char for immediately executing one line of SQL (#)

SQLP[ROMPT] {SQL>|text}
   Set the command prompt.

SQLT[ERMINATOR] {;|c|OFF|ON}|
   Set the char used to end and execute SQL commands to c.
   OFF disables the command terminator - use an empty line instead.
   ON resets the terminator to the default semicolon (;).

SUF[FIX] {SQL|text}
   Default file extension for SQL scripts

TAB {OFF|ON}
   Format white space in terminal output. 
   OFF = use spaces to format white space.
   ON = use the TAB char.
   Note this does not apply to spooled output files.
   The default is system-dependent. Enter SHOW TAB to see the default value.

TERM[OUT] {OFF|ON}
   OFF suppresses the display of output from a command file
   ON displays the output.
   TERMOUT OFF does not affect the output from commands entered interactively.

TI[ME] {OFF|ON}
   Display the time at the command prompt.

TIMI[NG] {OFF|ON}
   ON = display timing statistics for each SQL command or PL/SQL block run.
   OFF = suppress timing statistics

TRIM[OUT] {OFF|ON}
   Display trailing blanks at the end of each line.
   ON = remove blanks, improving performance
   OFF = display blanks.
   This does not affect spooled output.
   SQL*Plus ignores TRIMOUT ON unless you set TAB ON.

TRIMS[POOL] {ON|OFF}
   Allows trailing blanks at the end of each spooled line.
   This does not affect terminal output.

UND[ERLINE] {-|c|ON|OFF}
   Set the char used to underline column headings to c.

VER[IFY] {OFF|ON}
   ON = list the text of a command before and after replacing substitution variables with values.
   OFF = dont display the command.

WRA[P] {OFF|ON}
   Controls whether to truncate or wrap the display of long lines.
   OFF = truncate
   ON = wrap to the next line
   The COLUMN command (WRAPPED and TRUNCATED clause) can override this for specific columns.
The items in Gray on this page are deprecated from Oracle 9 onwards - also note that several of the options above have 'gone missing' from the official documentation set - HELP SET is a more accurate reference.

Get a list of these SET options in sql*plus with the command:
SQLPLUS> HELP SET

select  *
from    dba_objects
where   object_name like 'XXX/_%' ESCAPE '/'

explain plan for
select user_name from fnd_user where user_name = 'KALIT';
select * from table(dbms_xplan.display(null, null, 'advanced'));


begin
  dbms_sqltune.import_sql_profile(
    name => 'test_prof',
    sql_text =>
'select /*+ full(t1) */
  *
from t1
where c1 = 1 or c2 = 2',
    profile => sqlprof_attr('USE_CONCAT(@"SEL$1" 8)',
                            'INDEX(@"SEL$1_2" "T1"@"SEL$1_2" ("T1"."C1"))',
                            'INDEX(@"SEL$1_1" "T1"@"SEL$1" ("T1"."C2"))')
  );
end;
/

DROP OUTLINE STX_OUTLINE_01_T;

CREATE OR REPLACE OUTLINE STX_OUTLINE_01_T ON
SELECT /*+ USE_CONCAT INDEX(A ENG_REVISED_ITEMS_SN2) */ *
FROM   ENG_REVISED_ITEMS a
WHERE  (REVISED_ITEM_SEQUENCE_ID = :B1
        OR     PARENT_REVISED_ITEM_SEQ_ID = :B1 )
AND    STATUS_TYPE <> 5;

SELECT  '''' || REPLACE(h.hint_text, '''','"') || ''','
FROM    outln.ol$hints h
WHERE   ol_name = 'STX_OUTLINE_01_T'
ORDER BY ol_name, hint# DESC;

----------------------


1. Plan Hint 추출을 위한 Outline 설정 (Tuning된 SQL)

CREATE OR REPLACE OUTLINE SAMPLE_OL ON
SELECT COUNT (*) "TOTAL"
FROM   (select /*+ index(b mtl_system_items_b_u1) */distinct a.INVENTORY_ITEM_ID "AhlucEN_invItemID",
               nvl(a.UOM_CODE, c.UOM_CODE) "AhlucEN_uomCode",
...;


2. Outline에서 Hint Text 추출 (추출 후 마지막 라인의 comma 제거)

SELECT  '''' || REPLACE(h.hint_text, '''','"') || ''','
FROM    outln.ol$hints h
WHERE   ol_name = 'SAMPLE_OL'
ORDER BY ol_name, hint# DESC;


3. SQL Profile 등록

DECLARE
BEGIN
    dbms_sqltune.import_sql_profile(
        name => 'SAMPLE_PR',  -- Profile Name
        description => 'Description',  -- Description
        force_match => TRUE,
        -- Source SQL
        sql_text => '
SELECT COUNT (*) "TOTAL"
FROM   (select distinct a.INVENTORY_ITEM_ID "AhlucEN_invItemID",
...
                WHERE  NVL(operating_unit, mo_global.get_current_org_id()) = mo_global.get_current_org_id())
        order by 4)
',
        -- Tuning된 Plan Hint
        profile => sqlprof_attr (
--'IGNORE_OPTIM_EMBEDDED_HINTS',
--'OPTIMIZER_FEATURES_ENABLE("10.2.0.4")', -- 불필요한 Hint 제거
'OUTLINE_LEAF(@"SEL$F68927DC")',
...
'USE_NL(@"SEL$F68927DC" "MP"@"SEL$8")',
'USE_NL(@"SEL$F68927DC" "TL"@"SEL$3")'
            )
    );

    COMMIT;
END;


4. Outline 삭제

DROP OUTLINE SAMPLE_OL;


5. Profile 삭제 (참고)

dbms_sqltune.drop_sql_profile(name => 'SAMPLE_PR');