spool 파일명 // ex) /user/test/a.txt
-- spool 관련 명령어 . . .
ex) set echo off;
....명령 구문..... // 쿼리 또는 @쿼리파일명 ex) /user/test/a.sql
spool off
spool은 명령들을 일괄로 처리해 주는 명령어 이다. spool 명령에 파일명을 파라미터로 지정하면 spool에서 실행된 모든 명령 들이 파일로 저장된다.
----------------------------------
spool 관련 명령어
----------------------------------
set heading off : 헤더가 표시되지 않고 데이터만 표시한다.
set pagesize 0 : 기본값은 14이며 공백을 없애기 위해 0으로 세팅, pagesize가 0이어도 컬럼명이 출력되지 않는다. 50000 (max)
set echo off : 명령이 표시되지 않게 off한다.
set term off : 조회 결과를 표시되지 않게 한다.
set trimspool on : linesize 이하로 행이 잛을 때 나머지부분을 자른다. 줄 끝 공백제거
set linesize 300 :
set feedback off : 결과에 대한 결과 건수와 시간이 나타나지 않는다.
set colsep ',' : 컬럼 구분자
set autocommit on | off | n : 자동으로 커밋되거나 n개의 dml문 후에 자동커밋되도록 설정한다.
set feedback on | off | n : 결과 행수를 온오프한다. n개의 이상의 데이터만 표시되게 설정한다.
set linesize 100 : 행의 사이즈
set pagesize 20 : 한 페이지에 출력될 행의 갯수
set pause on | off : 한페이지마다 멈추게 한다.
set time on | off : 프롬프트에 시간나오기
set timing on | off : 명령어의 실행시간을 표시해 준다.
show 환경변수명 | all : 현재 설정되어있는 환경변수를 보기
column name heading 'student | name' : name 이라는 컬럼을 student|name으로바꾼다.
column sal format 0,000,000 : 포멧변경 ex) 0,035,823
column sal format 9,999,999 : 포맷변경 ex) 90,000
column [name] : 컬럼설정을 모두 출력하거나 지정된 컬럼설정을 출력한다.
column [name] clear : 지정된 컬럼설정을 삭제한다.
define_editor = notepad :버퍼에 들어있는 내용을 해당에디터를 수정가능
spool filename |off | out : 결과를 파일로 저장하기, out는 프린터로 내보내기
get filename : 파일에 데이터를 버퍼로 올리기
save filename : 버퍼의 내용을 파일로 저장하기
==============================================================
SQLPLUS 설정
==============================================================
SHOW ALL : 시스템 변수의 현재 설정 확인
HELP SET : 설정 도움말
HELP INDEX : HELP로 볼 수 있는 명령어 모음
SET SPOOL ON 또는 SET SPOOL PATH+파일명 : 쿼리 결과를 파일로 저장함
SHOW USER : 현재 자기가 접속한 유저를 보여줌
SHOW PARAMETER OPTI : OPTI로 시작하는 설정된 파라메터를 다 보여줌
COLSEP (TEXT ) : 칼럼이 표시될때 칼럼간의 구별문자, 기본값은 공백
COL(UMN) 칼럼이나 ALIAS 옵션
CLE(AR) : 칼럼에 지정된 형식을 지움
FOR(MAT) : 형식 칼럼에 대한 표시 형식과 폭을 바꿈
CLEAR COLUMNS : 지정된 형식을 다 지우고 싶은 경우
SET AUTOCOMMINT (ON|OFF) : 커밋 온, 오프
SET HEADING (ON|OFF) : 칼럼에 대한 HEADING를 표시함, 기본 값은 ON
SET FEEDBACK (ON|OFF) : 선택된 행이 몇행인지를 표시함, 기본값은 6행 이상인 경우에 ON
SET LINESIZE (N ) : 한 라인당 표시되는 문자의 수, 기본값은 80
SET PAGES (N ) : 한 페이지당 표시되는 라인수, 기본값은 24
SET TIMING (ON|OFF) : 명령문을 실행하는데 소요된 시간
SET TIME (ON|OFF) : 현재 시간 표시
SET PAUSE (ON|OFF) : 한 페이지씩 보기
SET UNDERLINE "=" : SELECT 문을 실행할때 헤더의 언더라인 모양을 지정
SET UNDERLINE (ON|OFF) : 언더라인 표시 유무
SET ECHO (ON|OFF)
SET VERIFY (ON|OFF)
SET TERM (ON|OFF)
SET SERVEROUTPUT (ON|OFF)
SET SHOWMODE (ON|OFF)
==============================================================
SQL PLUS 명령어
================================================================
REMARK : 보통 "REM 문자"의 형식으로 REM 다음에 나오는 문자는 주석으로 처리된다.
SET HEADSEP : HEADSEP은 HEAD SEPARATOR의 약자로, "SET HEADSEP |"일 경우, |이 두 문자를 두 라인에 걸쳐 나누어 출력되도록 하는 명령어이다.
TTITLE : 각 리포트의 위첨자를 설정한다.
BTITLE : 각 리포트의 아래첨자를 설정한다.
COLUMN : 컬럼의 목록명과 포맷을 설정한다.
BREAK ON : 각 행의 섹션을 나누고, 합을 구할 컬럼 사이를 나누는 명령어이다.
COMPUTE SUM : BREAK 명령으로 나누어진 컬럼의 데이터 행의 부분 합을 계산하는 명령어이다.
SPOOL/SPOOL OFF : 출력의 파일 저장을 실행/중지하며 저장 실행시에 출력 파일명을 "SPOOL 파일명"의 형식으로 지정한다.
/* */ : REM과 같이 주석을 사용할 때 쓰이며 "/* 주석 */"의 형식을 갖는다.
- : SQL문 안에 주석을 지정할 때 사용한다.
SAVE : 실행한 SQL문을 파일로 저장한다.
HOST : 호스트 OS로 명령을 보내며, 호스트와 오라클 사이의 INTERFACE를 제공한다. HOST 명령어 대신 !를 이용할 수 있다.
START : 파일에 저장한 SQL문 또는 PL/SQL문을 실행한다. START 명령어 대신 @을 이용할 수 있다.
EDIT : SQL 명령 창을 잠시 나가 메모장이나 VI 에디터와 같은 설정된 에디터 창을 실행한다.
DEFINE_EDITOR : EDIT 명령어로 실행시킬 에디터를 설정한다.
==================================================================================
SAVE 파일명 : SQL 버퍼에 있는 현재의 내용을 파일로 저장한다.
GET 파일명 : 현재 디렉토리에 저장된 파일의 내용을 버퍼로 불러낸다.
START 파일명 : 이전에 저장한 명령 파일을 실행한다.
@파일명 : 이전에 저장한 명령 파일을 실행한다.( START와 동일 )
EDIT : 편집기를 실행시켜 버퍼내용을 AFIDET.BUF로 불리는 파일에 저장한다.
EDIT 파일명 : 저장된 파일의 내용을 편집하기 위해 편집기를 실행한다.
EXIT : SQL*PLUS를 중단한다.
!유닉스명령어 : UNIX에서 사용하는 명령어를 실행시킬수 있다.
HOST : UNIX 형태에서 작업할수 있고, EXIT 를 치면 다시 돌아온다.
SPOOL 파일명 : RETRIEVE DATA를 파일명.LST로 저장한다.
SPOOL OFF : SPOOL 을 끝낸다.
SPOOL OUT : RETRIEVE DATA를 SYSTEM PRINTER로 출력하라.
============================================================================
* BUFFER편집 명령어
A(PPEND) : TEXT LINE의 끝에 TEXT를 추가함
C(HANGE)/OLD/NEW : OLD를 NEW로 바꿈
DEL N : N LINE을 지움
I(NPUT) : TEXT 다음 LINE에 TEXT를 추가함
L(IST) : 전체 문장을 보여줌
N TEXT : N LINE전체를 TEXT로 바꿈
R(UN) : BUFFER에 있는 문장을 실행함(/ 와 같음)
EDIT : BUFFER에 있는 문장을 파일로 부름(AFIEDT.BUF)
SAVE A : BUFFER에 있는 내용을 A.SQL 파일로 저장
GET A : 파일 A에 있는 내용을 BUFFER로 부름
START A : 파일 A를 실행함
! : UNIX SHELL로 나들이
!VI A.SQL : 파일 A.SQL을 VI편집기로 부름
ex)
1. spoolselect.sql
------------------------------------------------
set pagesize 0
set linesize 400
set echo off
set term off
set colsep '|'
set feedback off
set timing off
set trimspool on
spool ./data.txt
select * from dual;
spool off;
exit;
2. 호출
sqlplus 계정 @./spoolselect.sql
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.
-- spool 관련 명령어 . . .
ex) set echo off;
....명령 구문..... // 쿼리 또는 @쿼리파일명 ex) /user/test/a.sql
spool off
spool은 명령들을 일괄로 처리해 주는 명령어 이다. spool 명령에 파일명을 파라미터로 지정하면 spool에서 실행된 모든 명령 들이 파일로 저장된다.
----------------------------------
spool 관련 명령어
----------------------------------
set heading off : 헤더가 표시되지 않고 데이터만 표시한다.
set pagesize 0 : 기본값은 14이며 공백을 없애기 위해 0으로 세팅, pagesize가 0이어도 컬럼명이 출력되지 않는다. 50000 (max)
set echo off : 명령이 표시되지 않게 off한다.
set term off : 조회 결과를 표시되지 않게 한다.
set trimspool on : linesize 이하로 행이 잛을 때 나머지부분을 자른다. 줄 끝 공백제거
set linesize 300 :
set feedback off : 결과에 대한 결과 건수와 시간이 나타나지 않는다.
set colsep ',' : 컬럼 구분자
set autocommit on | off | n : 자동으로 커밋되거나 n개의 dml문 후에 자동커밋되도록 설정한다.
set feedback on | off | n : 결과 행수를 온오프한다. n개의 이상의 데이터만 표시되게 설정한다.
set linesize 100 : 행의 사이즈
set pagesize 20 : 한 페이지에 출력될 행의 갯수
set pause on | off : 한페이지마다 멈추게 한다.
set time on | off : 프롬프트에 시간나오기
set timing on | off : 명령어의 실행시간을 표시해 준다.
show 환경변수명 | all : 현재 설정되어있는 환경변수를 보기
column name heading 'student | name' : name 이라는 컬럼을 student|name으로바꾼다.
column sal format 0,000,000 : 포멧변경 ex) 0,035,823
column sal format 9,999,999 : 포맷변경 ex) 90,000
column [name] : 컬럼설정을 모두 출력하거나 지정된 컬럼설정을 출력한다.
column [name] clear : 지정된 컬럼설정을 삭제한다.
define_editor = notepad :버퍼에 들어있는 내용을 해당에디터를 수정가능
spool filename |off | out : 결과를 파일로 저장하기, out는 프린터로 내보내기
get filename : 파일에 데이터를 버퍼로 올리기
save filename : 버퍼의 내용을 파일로 저장하기
==============================================================
SQLPLUS 설정
==============================================================
SHOW ALL : 시스템 변수의 현재 설정 확인
HELP SET : 설정 도움말
HELP INDEX : HELP로 볼 수 있는 명령어 모음
SET SPOOL ON 또는 SET SPOOL PATH+파일명 : 쿼리 결과를 파일로 저장함
SHOW USER : 현재 자기가 접속한 유저를 보여줌
SHOW PARAMETER OPTI : OPTI로 시작하는 설정된 파라메터를 다 보여줌
COLSEP (TEXT ) : 칼럼이 표시될때 칼럼간의 구별문자, 기본값은 공백
COL(UMN) 칼럼이나 ALIAS 옵션
CLE(AR) : 칼럼에 지정된 형식을 지움
FOR(MAT) : 형식 칼럼에 대한 표시 형식과 폭을 바꿈
CLEAR COLUMNS : 지정된 형식을 다 지우고 싶은 경우
SET AUTOCOMMINT (ON|OFF) : 커밋 온, 오프
SET HEADING (ON|OFF) : 칼럼에 대한 HEADING를 표시함, 기본 값은 ON
SET FEEDBACK (ON|OFF) : 선택된 행이 몇행인지를 표시함, 기본값은 6행 이상인 경우에 ON
SET LINESIZE (N ) : 한 라인당 표시되는 문자의 수, 기본값은 80
SET PAGES (N ) : 한 페이지당 표시되는 라인수, 기본값은 24
SET TIMING (ON|OFF) : 명령문을 실행하는데 소요된 시간
SET TIME (ON|OFF) : 현재 시간 표시
SET PAUSE (ON|OFF) : 한 페이지씩 보기
SET UNDERLINE "=" : SELECT 문을 실행할때 헤더의 언더라인 모양을 지정
SET UNDERLINE (ON|OFF) : 언더라인 표시 유무
SET ECHO (ON|OFF)
SET VERIFY (ON|OFF)
SET TERM (ON|OFF)
SET SERVEROUTPUT (ON|OFF)
SET SHOWMODE (ON|OFF)
==============================================================
SQL PLUS 명령어
================================================================
REMARK : 보통 "REM 문자"의 형식으로 REM 다음에 나오는 문자는 주석으로 처리된다.
SET HEADSEP : HEADSEP은 HEAD SEPARATOR의 약자로, "SET HEADSEP |"일 경우, |이 두 문자를 두 라인에 걸쳐 나누어 출력되도록 하는 명령어이다.
TTITLE : 각 리포트의 위첨자를 설정한다.
BTITLE : 각 리포트의 아래첨자를 설정한다.
COLUMN : 컬럼의 목록명과 포맷을 설정한다.
BREAK ON : 각 행의 섹션을 나누고, 합을 구할 컬럼 사이를 나누는 명령어이다.
COMPUTE SUM : BREAK 명령으로 나누어진 컬럼의 데이터 행의 부분 합을 계산하는 명령어이다.
SPOOL/SPOOL OFF : 출력의 파일 저장을 실행/중지하며 저장 실행시에 출력 파일명을 "SPOOL 파일명"의 형식으로 지정한다.
/* */ : REM과 같이 주석을 사용할 때 쓰이며 "/* 주석 */"의 형식을 갖는다.
- : SQL문 안에 주석을 지정할 때 사용한다.
SAVE : 실행한 SQL문을 파일로 저장한다.
HOST : 호스트 OS로 명령을 보내며, 호스트와 오라클 사이의 INTERFACE를 제공한다. HOST 명령어 대신 !를 이용할 수 있다.
START : 파일에 저장한 SQL문 또는 PL/SQL문을 실행한다. START 명령어 대신 @을 이용할 수 있다.
EDIT : SQL 명령 창을 잠시 나가 메모장이나 VI 에디터와 같은 설정된 에디터 창을 실행한다.
DEFINE_EDITOR : EDIT 명령어로 실행시킬 에디터를 설정한다.
==================================================================================
SAVE 파일명 : SQL 버퍼에 있는 현재의 내용을 파일로 저장한다.
GET 파일명 : 현재 디렉토리에 저장된 파일의 내용을 버퍼로 불러낸다.
START 파일명 : 이전에 저장한 명령 파일을 실행한다.
@파일명 : 이전에 저장한 명령 파일을 실행한다.( START와 동일 )
EDIT : 편집기를 실행시켜 버퍼내용을 AFIDET.BUF로 불리는 파일에 저장한다.
EDIT 파일명 : 저장된 파일의 내용을 편집하기 위해 편집기를 실행한다.
EXIT : SQL*PLUS를 중단한다.
!유닉스명령어 : UNIX에서 사용하는 명령어를 실행시킬수 있다.
HOST : UNIX 형태에서 작업할수 있고, EXIT 를 치면 다시 돌아온다.
SPOOL 파일명 : RETRIEVE DATA를 파일명.LST로 저장한다.
SPOOL OFF : SPOOL 을 끝낸다.
SPOOL OUT : RETRIEVE DATA를 SYSTEM PRINTER로 출력하라.
============================================================================
* BUFFER편집 명령어
A(PPEND) : TEXT LINE의 끝에 TEXT를 추가함
C(HANGE)/OLD/NEW : OLD를 NEW로 바꿈
DEL N : N LINE을 지움
I(NPUT) : TEXT 다음 LINE에 TEXT를 추가함
L(IST) : 전체 문장을 보여줌
N TEXT : N LINE전체를 TEXT로 바꿈
R(UN) : BUFFER에 있는 문장을 실행함(/ 와 같음)
EDIT : BUFFER에 있는 문장을 파일로 부름(AFIEDT.BUF)
SAVE A : BUFFER에 있는 내용을 A.SQL 파일로 저장
GET A : 파일 A에 있는 내용을 BUFFER로 부름
START A : 파일 A를 실행함
! : UNIX SHELL로 나들이
!VI A.SQL : 파일 A.SQL을 VI편집기로 부름
ex)
1. spoolselect.sql
------------------------------------------------
set pagesize 0
set linesize 400
set echo off
set term off
set colsep '|'
set feedback off
set timing off
set trimspool on
spool ./data.txt
select * from dual;
spool off;
exit;
2. 호출
sqlplus 계정 @./spoolselect.sql
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.
set colsep "|"
with demo as
( select 'Various' as col1, 'test' as col2, 'examples' as col3 from dual
union all select 'How', 'are', 'you' from dual )
select col1, col2, col3
from demo;
COL1 |COL2|COL3
-------|----|--------
Various|test|examples
How |are |you
with demo as
( select 'Various' as col1, 'test' as col2, 'examples' as col3 from dual
union all select 'How', 'are', 'you' from dual )
select col1, col2, col3
from demo;
COL1 |COL2|COL3
-------|----|--------
Various|test|examples
How |are |you
출처: http://12bme.tistory.com/301 [충일함만이 명확함에 이른다.]
시작
# su oracle
$ sqlplus /nolog
sql> connect / as sysdba
sql> startup
종료
sql> shutdown
버전확인
sql> select * from v$version;
db접속
$ sqlplus /nolog
sql> connect scott/tiger@ORADB
테이블확인
sql> select * from tab;
외부접속 허용
$ lsnrctrl start
=ORA-12170 : TNS : 접속시간초과가 발생함=
시스템등록정보 > 환경변수 > 시스템 변수
변수이름 : USE_SHARED_SOCKET 을 TRUE 로변경
테이블 복사
create table newtablename as select * from oldtablename;
백업
c:\>exp userid=scott/tiger file='C:exp.dmp' tables=(ILBANE) log=exp.log
풀기 - 테이블 복사
c:\>imp userid=scott/tiger@orcl file=c:\exp.dmp
권한 부여
grant create database link to scott;
링크 생성/연결
create datadase link lyo
connect to scott
identified by tiger
using 'lyo';
sequence 생성
CREATE SEQUENCE TEST_ID INCREMENT BY 1 START WITH 1 ;
현재 세션의 모든 SEQUENCE를 조회
SELECT * FROM USER_SEQUENCES
오라클 8 이후 부터 SEQUENCES.CURRVAL 이 없어짐
SELECT LAST_NUMBER
FROM USER_SEQUENCES
WHERE SEQUENCE_NAME = '시퀀스명(대문자)'
# oracle version 확인
select banner from v$version;
# oracle 계정 생성
SQL> conn sys/oracle as sysdba
SQL> select * from all_users;
SQL> select * from all_users;
SQL> create user oncalls2012 identified by on201212;
SQL> grant create session to oncalls2012;
SQL> grant create table to oncalls2012;
SQL> select * from dba_tablespaces;
SQL> select name from v$datafile;
SQL> create tablespace oncalls datafile '/app/oracle/oradata/devdb/oncalls.dbf' size 10m autoextend on next 5m maxsize 1000m;
SQL> alter user oncalls2012 quota 1000m on oncalls;
SQL> select * from all_users;
# EXPORT
1. table 단위로 export
exp publicdb/publicdb@yongjin/publicdb tables=대표지번_강원도 rows=y file=d:\dd.dmp;
exp publicdb/publicdb@yongjin/publicdb tables=대표지번_강원도 rows=y file=d:\dd.dmp;
2. user 단위로 export
exp publicdb/publicdb@yongjin/publicdb owner=hr direct=y file=d:\dd.dmp;
* direct 는 빠르게 처리하겠다는 뜻.
exp publicdb/publicdb@yongjin/publicdb owner=hr direct=y file=d:\dd.dmp;
* direct 는 빠르게 처리하겠다는 뜻.
3. tablsspace 단위로 export
exp 'system/system@yongjin/publicdb as sysdba' TRANSPORT_TABLESPACE=y TABLESPACE=ts_emp log=ts_emp.log
exp 'system/system@yongjin/publicdb as sysdba' TRANSPORT_TABLESPACE=y TABLESPACE=ts_emp log=ts_emp.log
# IMPORT
1. table 단위로 import
imp publicdb/publicdb@yongjin/publicdb tables=대표지번_강원도 rows=y file=d:\dd.dmp;
imp publicdb/publicdb@yongjin/publicdb tables=대표지번_강원도 rows=y file=d:\dd.dmp;
2. user 단위로 export
imp publicdb/publicdb@yongjin/publicdb full=y file=d:\dd.dmp;
imp publicdb/publicdb@yongjin/publicdb full=y file=d:\dd.dmp;
3. tablsspace 단위로 export
imp 'system/system@yongjin/publicdb as sysdba' TRANSPORT_TABLESPACE=y TABLESPACE=ts_emp
ex; imp oncalls2012/on201212 owner=oncalls2012 direct=y file=/home/oracle/oncalls2012_20140522.dmp;
imp 'system/system@yongjin/publicdb as sysdba' TRANSPORT_TABLESPACE=y TABLESPACE=ts_emp
ex; imp oncalls2012/on201212 owner=oncalls2012 direct=y file=/home/oracle/oncalls2012_20140522.dmp;
# characterset 확인. sys 계정으로 sqlplus 접속
select * from sys.propS$ where name like 'NLS_CHARACTERSET';
# characterset 변경
update sys.props$ set value$='KO16WIN949' where name='NLS_CHARACTERSET';
-- sys 계정 (가장 높은 권한의 계정)
-- 서비스 중지 / 시작 권한
-- sqlplus sys/orcl as sysdba
-- 계정생성
-- create user 계정명 identified by 암호
-- drop user 계정명
create user kildong identified by kil123;
-- create user 계정명 identified by 암호
-- drop user 계정명
create user kildong identified by kil123;
--pw 변경
alter user kildong identified by kil456;
-- 일반 사용자에 데이터베이스를 관리하는 권한
-- grant create session, create view, create sequence,
-- create session -- 연결만됨
alter user kildong identified by kil456;
-- 일반 사용자에 데이터베이스를 관리하는 권한
-- grant create session, create view, create sequence,
-- create session -- 연결만됨
-- db 접근 권한 주기
grant connect, resource to kildong
-- 사요자에게 dml 문 사용 권한 설정하기
-- grant select, insert on 테이블명 to 사용자
-- system 권한주기 with admin option (sys권한은 아무에게도 줄수 없다)
grant create session to user2 with admin option;
-- revoke
grant create session to user2 with admin option;
-- revoke
-- 시스템계정의 권한 확인
select * from system_privilege_map;
select * from system_privilege_map;
-- 유저가 부여받은 권한 확인
select * from session_privs;
select * from session_privs;
-- 사용자에게 dml문 사용 권한 설정하기
grant select,insert,update,delete on sawon to user1, user2;
-- 모두 주기
grant all on buser to user1;
grant select,insert,update,delete on sawon to user1, user2;
-- 모두 주기
grant all on buser to user1;
-- 권한 회수
revoke delete on sawon from user1;
-- 모두 회수
revoke all on buser from user1;
revoke delete on sawon from user1;
-- 모두 회수
revoke all on buser from user1;
------- role 여러계정을 그룹에 담아 그룹에 권한 주기--------------------------------
-- 롤은 롤을 포함 할 수 있다
-- 롤을 생성
create role insa
-- 롤 삭제
drop role insa;
-- 롤에 계정을 추가
grant insa to tom;
-- 롤에서 계정 삭제
revoke insa from tom;
-- 롤(그룹) 별로 계정의 권한 관리
grant select, delete on sawon to insa;
-- 롤 로 부터 권한 뺏기
revoke delete on sawon from insa;
-- 롤은 롤을 포함 할 수 있다
-- 롤을 생성
create role insa
-- 롤 삭제
drop role insa;
-- 롤에 계정을 추가
grant insa to tom;
-- 롤에서 계정 삭제
revoke insa from tom;
-- 롤(그룹) 별로 계정의 권한 관리
grant select, delete on sawon to insa;
-- 롤 로 부터 권한 뺏기
revoke delete on sawon from insa;
-- role 목록 보기
select * from dba_roles;
-- 특정 ROLE 권한 보기
SELECT * FROM ROLE_SYS_PRIVS WHERE ROLE='YOUNGUP';
-- 특정 테이블에게 받은 권한 확인
select * from role_tab_privs where role='YOUNGUP';
select * from dba_roles;
-- 특정 ROLE 권한 보기
SELECT * FROM ROLE_SYS_PRIVS WHERE ROLE='YOUNGUP';
-- 특정 테이블에게 받은 권한 확인
select * from role_tab_privs where role='YOUNGUP';
-- synonym 동의어 만들기
-- 지역
create synonym saw for scott.sawon;
-- 전역
create public synonym saw2 for scott.sawon;
-- 지역
create synonym saw for scott.sawon;
-- 전역
create public synonym saw2 for scott.sawon;
-- user 별 사용량 조회
select sum(sm) from (
select tablespace_name, segment_type, sum(bytes/1048576) sm
from user_segments
group by tablespace_name, segment_type
)
- CharacterSet 확인
select * from sys.props$
where name = 'NLS_CHARACTERSET';
select * from nls_database_parameters;
-- 파라미터 확인
select * from v$parameter where name like 'nls%';
-- 문자셋/언어셋 변경
/* 문자셋 변경 */
SQL> update sys.props$ set value$='KO16KSC5601'
where name='NLS_CHARACTERSET';
SQL> update sys.props$ set value$='KO16KSC5601'
where name='NLS_NCHAR_CHARACTERSET';
/* 언어셋 변경 */
SQL>update sys.props$ set value$='AMERICAN_AMERICA.KO16KSC5601'
where name='NLS_LANGUAGE';
= sqlplus file call =
@D:\bakup\busan_test.sql
-- oracle columns comment query
SELECT T.COLUMN_NAME AS PHYSICAL_NAME,
C.COMMENTS AS LOGICAL_NAME,
T.DATA_TYPE AS TYPE,
CASE
WHEN T.DATA_TYPE = 'NUMBER' THEN T.DATA_PRECISION
ELSE CASE WHEN T.CHAR_LENGTH != 0 THEN T.CHAR_LENGTH ELSE NULL END
END
AS LENGTH,
T.DATA_SCALE,
T.DATA_DEFAULT,
T.NULLABLE
FROM USER_TAB_COLUMNS T, USER_COL_COMMENTS C
WHERE T.TABLE_NAME = C.TABLE_NAME
AND T.COLUMN_NAME = C.COLUMN_NAME
AND T.TABLE_NAME = 'TN_BBS_TY1'
ORDER BY T.COLUMN_ID ASC;
C.COMMENTS AS LOGICAL_NAME,
T.DATA_TYPE AS TYPE,
CASE
WHEN T.DATA_TYPE = 'NUMBER' THEN T.DATA_PRECISION
ELSE CASE WHEN T.CHAR_LENGTH != 0 THEN T.CHAR_LENGTH ELSE NULL END
END
AS LENGTH,
T.DATA_SCALE,
T.DATA_DEFAULT,
T.NULLABLE
FROM USER_TAB_COLUMNS T, USER_COL_COMMENTS C
WHERE T.TABLE_NAME = C.TABLE_NAME
AND T.COLUMN_NAME = C.COLUMN_NAME
AND T.TABLE_NAME = 'TN_BBS_TY1'
ORDER BY T.COLUMN_ID ASC;
-- table comment
SELECT
A.TABLE_NAME
, B.COMMENTS
FROM ALL_TABLES A
, ALL_TAB_COMMENTS B
WHERE A.TABLE_NAME = B.TABLE_NAME
ORDER BY A.TABLE_NAME;
댓글
댓글 쓰기