sqlplus

数据库简易链接方法
sqlplus sys/oracle@192.168.137.10:1521/ora11g

在SQL*Plus输入exit或quit正常退出会话，事务将立即被提交

使用product_user_profile表，可以限制访问sql*plus和sql命令，也可以限制访问PL/SQL语句
可以限制一个用户执行以下命令：alter,begin,connect,declare,exec,execute,grant,host,insert,select,update
SQL> desc product_user_profile
Name Null? Type
------------------------------------ ----------- ----------------------------
PRODUCT NOT NULL VARCHAR2(30)
USERID VARCHAR2(30)
ATTRIBUTE VARCHAR2(240)
SCOPE VARCHAR2(240)
NUMERIC_VALUE NUMBER(15,2)
CHAR_VALUE VARCHAR2(240)
DATE_VALUE DATE
LONG_VALUE LONG

[oracle@gc admin]$ pwd
/u02/app/oracle/sqlplus/admin
[oracle@gc admin]$ ls
glogin.sql help libsqlplus.def plustrce.sql pupbld.sql

以system运行pupbld.sql建立product_user_profile表

insert into product_user_profile (product,userid,attribute) values ('SQL*PLUS','scott','DELETE');
insert into product_user_profile (product,userid,attribute) values ('SQL*PLUS','scott','UPDATE');
commit;

select product,userid,attribute from product_user_profile where userid='SCOTT';
PRODUCT USERID
------------------------------ ------------------------------
ATTRIBUTE
--------------------------------------------------------------------------------
SQL*PLUS scott
UPDATE

SQL*PLUS scott
DELETE

delete from product_user_profile where userid='scott' and attribute='DELETE';

SQL> help index 显示 SQL*PLUS 命令

Enter Help [topic] for help.

@ COPY PAUSE SHUTDOWN
@@ DEFINE PRINT SPOOL
/ DEL PROMPT SQLPLUS
ACCEPT DESCRIBE QUIT START
APPEND DISCONNECT RECOVER STARTUP
ARCHIVE LOG EDIT REMARK STORE
ATTRIBUTE EXECUTE REPFOOTER TIMING
BREAK EXIT REPHEADER TTITLE
BTITLE GET RESERVED WORDS (SQL) UNDEFINE
CHANGE HELP RESERVED WORDS (PL/SQL) VARIABLE
CLEAR HOST RUN WHENEVER OSERROR
COLUMN INPUT SAVE WHENEVER SQLERROR
COMPUTE LIST SET XQUERY
CONNECT PASSWORD SHOW

host是在不离开Sqlplus环境下执行主机下的命令的
SQL> host copy D:ORACLEORADATAORA10TS1.1 D:ORACLEORADATAORA10TS1.dbf
SQL> ！ copy D:ORACLEORADATAORA10TS1.1 D:ORACLEORADATAORA10TS1.dbf
SCOTT@ora11g>host
[oracle@gc ~]$ exit
exit
SCOTT@ora11g>

SCOTT@ora11g>!
[oracle@gc ~]$ exit
exit
SCOTT@ora11g>

help set
help copy
help list 等等命令的帮助
SQL> help set 查看可以用set命令控制的整个环境变量的集合

SET
---

Sets a system variable to alter the SQL*Plus environment settings
for your current session. For example, to:
- set the display width for data
- customize HTML formatting
- enable or disable printing of column headings
- set the number of lines per page

SET system_variable value

where system_variable and value represent one of the following clauses:

APPI[NFO]{OFF|ON|text} NEWP[AGE] {1|n|NONE}
ARRAY[SIZE] {15|n} NULL text
AUTO[COMMIT] {OFF|ON|IMM[EDIATE]|n} NUMF[ORMAT] format
AUTOP[RINT] {OFF|ON} NUM[WIDTH] {10|n}
AUTORECOVERY {OFF|ON} PAGES[IZE] {14|n}
AUTOT[RACE] {OFF|ON|TRACE[ONLY]} PAU[SE] {OFF|ON|text}
[EXP[LAIN]] [STAT[ISTICS]] RECSEP {WR[APPED]|EA[CH]|OFF}
BLO[CKTERMINATOR] {.|c|ON|OFF} RECSEPCHAR {_|c}
CMDS[EP] {;|c|OFF|ON} SERVEROUT[PUT] {ON|OFF}
COLSEP {_|text} [SIZE {n | UNLIMITED}]
CON[CAT] {.|c|ON|OFF} [FOR[MAT] {WRA[PPED] |
COPYC[OMMIT] {0|n} WOR[D_WRAPPED] |
COPYTYPECHECK {ON|OFF} TRU[NCATED]}]
DEF[INE] {&|c|ON|OFF} SHIFT[INOUT] {VIS[IBLE] |
DESCRIBE [DEPTH {1|n|ALL}] INV[ISIBLE]}
[LINENUM {OFF|ON}] [INDENT {OFF|ON}] SHOW[MODE] {OFF|ON}
ECHO {OFF|ON} SQLBL[ANKLINES] {OFF|ON}
EDITF[ILE] file_name[.ext] SQLC[ASE] {MIX[ED] |
EMB[EDDED] {OFF|ON} LO[WER] | UP[PER]}
ERRORL[OGGING] {ON|OFF} SQLCO[NTINUE] {> | text}
[TABLE [schema.]tablename] SQLN[UMBER] {ON|OFF}
[TRUNCATE] [IDENTIFIER identifier] SQLPLUSCOMPAT[IBILITY] {x.y[.z]}
ESC[APE] {|c|OFF|ON} SQLPRE[FIX] {#|c}
ESCCHAR {@|?|%|$|OFF} SQLP[ROMPT] {SQL>|text}
EXITC[OMMIT] {ON|OFF} SQLT[ERMINATOR] {;|c|ON|OFF}
FEED[BACK] {6|n|ON|OFF} SUF[FIX] {SQL|text}
FLAGGER {OFF|ENTRY|INTERMED[IATE]|FULL} TAB {ON|OFF}
FLU[SH] {ON|OFF} TERM[OUT] {ON|OFF}
HEA[DING] {ON|OFF} TI[ME] {OFF|ON}
HEADS[EP] {||c|ON|OFF} TIMI[NG] {OFF|ON}
INSTANCE [instance_path|LOCAL] TRIM[OUT] {ON|OFF}
LIN[ESIZE] {80|n} TRIMS[POOL] {OFF|ON}
LOBOF[FSET] {1|n} UND[ERLINE] {-|c|ON|OFF}
LOGSOURCE [pathname] VER[IFY] {ON|OFF}
LONG {80|n} WRA[P] {ON|OFF}
LONGC[HUNKSIZE] {80|n} XQUERY {BASEURI text|
MARK[UP] HTML [OFF|ON] ORDERING{UNORDERED|
[HEAD text] [BODY text] [TABLE text] ORDERED|DEFAULT}|
[ENTMAP {ON|OFF}] NODE{BYVALUE|BYREFERENCE|
[SPOOL {OFF|ON}] DEFAULT}|
[PRE[FORMAT] {OFF|ON}] CONTEXT text}

SqlPlus Set常用设置

SQL>set colsep' ';　　　　 //-域输出分隔符
SQL>set echo off;　　　　 //显示start启动的脚本中的每个sql命令，缺省为on
SQL> set echo on //设置运行命令是是否显示语句
SQL> set feedback on; //设置显示“已选择XX行”
SQL>set feedback off;　 //回显本次sql命令处理的记录条数，缺省为on
SQL>set heading off;　　 //输出域标题，缺省为on
SQL>set pagesize 0;　　 //输出每页行数，缺省为24,为了避免分页，可设定为0。
SQL>set linesize 80;　　 //输出一行字符个数，缺省为80
SQL>set numwidth 12;　 //输出number类型域长度，缺省为10
SQL>set termout off;　　 //显示脚本中的命令的执行结果，缺省为on
SQL>set trimout on;　　　//去除标准输出每行的拖尾空格，缺省为off
SQL>set trimspool on;　　//去除重定向（spool）输出每行的拖尾空格，缺省为off
SQL>set serveroutput on; //设置允许显示输出类似dbms_output
SQL> set timing on; //显示每个sql语句花费的执行时间
SQL> set autotrace on-; //设置允许对执行的sql进行分析
SQL>set blockterminator on 设置点号（.）
SQL>set sqlterminator on 设置分号（;）
set verify off //可以关闭和打开提示确认信息old 1和new 1的显示.

set echo on/off 是否显示脚本中的需要执行的命令
set feedback on/off 是否显示 select 结果之后返回多少行的提示
set linesize n 设置一行最多显示多少字符，之前就是因为 n 设得过大，导致行与行之间有空白行
set termout on/off 在执行脚本时是否在屏幕上输出结果，如果 spool 到文件可以将其关闭
set heading on/off 是否显示查询结果的列名，如果设置为 off，将用空白行代替，如果要去除该空白行，
可以用 set pagesize 0
set pagesize n 设置每页的行数，将 n 设为 0 可以不显示所有 headings, page breaks, titles, the initial blank line, and other formatting information

set trimspool on/off 在 spool 到文件时是否去除输出结果中行末尾的空白字符，之前的隔行可以用该参数去掉，和该参数对应的是 trimout，后者用于屏幕输出
set trimout on/off 是否去掉屏幕上输出结果行末尾的空白字符
set space on/off 输出列间空格数设置
SET NEWP[AGE] {1|n|NONE}
设置页与页之间的分隔。
当SET NEWPAGE 0 时，会在每页的开头有一个小的黑方框。
当SET NEWPAGE n 时，会在页和页之间隔着n个空行。
当SET NEWPAGE NONE 时，会在页和页之间没有任何间隔。
SET NULL text
显示时，用text值代替NULL值

如果需要sqlplus下次启动的时候自动调整这些格式，
可以将上面的设置保存到$ORACLE_HOME/sqlplus/admin/glogin.sql文件

set serveroutput on 确定PL/SQL代码段或存储过程的输出是否显示在屏幕上
set serveroutput on format word_wrapped

SQL> set errorlogging on 打开错误记录
SQL> show errorlogging
errorlogging is ON TABLE SYS.SPERRORLOG
SQL> desc sperrorlog
Name Null? Type
----------------------------------------- -------- ----------------------------
USERNAME VARCHAR2(256)
TIMESTAMP TIMESTAMP(6)
SCRIPT CLOB
IDENTIFIER VARCHAR2(256)
MESSAGE CLOB
STATEMENT CLOB

SQL> alter system set recyclebin=off;
系统已更改。
SQL> alter system set recyclebin=on;
系统已更改。
SQL> alter session set recyclebin=off;
会话已更改。
SQL> alter session set recyclebin=on;
会话已更改。
SQL> show parameter recyclebin
NAME TYPE VALUE
------------ ----------- ------
recyclebin string on

SQL> set sqlprompt "_USER'@'_CONNECT_IDENTIFIER >"
SYS@ora11g >
SYS@ora11g >
SYS@ora11g >CONN scott/tiger
Connected.
SCOTT@ora11g >set sqlprompt "SQL>"
SQL>

SQL>set sqlprompt "_USER 'ON' _DATE 'AT' _CONNECT_IDENTIFIER>"
SCOTT ON 03-SEP-13 AT ora11g>
可以将上述语句并入到login.sql中，每当登陆时，此文件会自动设置回话值，而不必每次手动设置会话值。

SPO[OL] [file_name[.ext] [CRE[ATE] | REP[LACE] | APP[END]] | OFF | OUT]

Spool 命令是将屏幕的显示输入到文本文件内，以便查看，有点象屏幕转存。
SPOOL create /u02/app/oracle/1.TXT
SELECT * FROM EMP;
SPOOL OFF
以上三行就将SPOOL 和SPOOL OFF 所夹的屏幕输出到 /u02/app/oracle/1.TXT 文件中。

Spool append /u02/app/oracle/1.TXT
Select * from dept;
Spool off
加 APPEND 命令的含义是续写 /u02/app/oracle/1.TXT,如果不加，将会把原来的 /u02/app/oracle/1.TXT 覆盖

pause可以使输出信息满一屏后暂停显示，按enter键看下一屏
SYS@ora11g>show pause
PAUSE is OFF
SYS@ora11g>set pause on
SYS@ora11g>

break指定格式变化的位置和类型，每个on指定一个断点,须和order by一起用

break on deptno skip page on job skip 1 on sal skip 1

select deptno,job,sal,ename
from emp
where sal>1000
order by deptno,job,sal,ename;

DEPTNO JOB SAL ENAME
---------- --------- ---------- ----------
10 CLERK 1300 MILLER

MANAGER 2450 CLARK

PRESIDENT 5000 KING

DEPTNO JOB SAL ENAME
---------- --------- ---------- ----------
20 ANALYST 3000 FORD
SCOTT

CLERK 1100 ADAMS

MANAGER 2975 JONES

DEPTNO JOB SAL ENAME
---------- --------- ---------- ----------
30 MANAGER 2850 BLAKE

SALESMAN 1250 MARTIN
WARD

1500 TURNER

1600 ALLEN

select to_char(sal,'$9999') from emp;

列格式
column column_name format a50 heading '别名' （设置column_name为50个字符宽）
col sal for $9999

1st quarter results意思是第一季度业绩
repfooter命令在报告底部打印指定的页脚文本
repfooter page right 'end of the 1st quarter results report'

repheader命令在报告顶部打印指定的页脚文本
repheader page center '1st quarter results report for 2008'

将标题放在每页报告的顶部
ttitle 'annual financial report for the women club, 2008'
将标题放在每页报告的底部
btitle '2005 report'

用完类似命令必须手动关闭它们，防止后续SQL继承那些设置
SCOTT@ora11g>repfooter off
SCOTT@ora11g>btitle off
SCOTT@ora11g>ttitle off

Save
将当前 SQLPLUS缓冲区内的 SQL 语句保存到指定的文件中
如save create c:2.txt
save replace
save append

Get
将文件中的SQL语句调入到SQLPLUS缓冲区内。
如 get c:2.txt

list查看要执行的代码
/不显示所执行的代码
run显示所执行的代码

用&替换变量
define department=&deptno
undefine department 终止变量

SCOTT@ora11g>define
DEFINE _DATE = "04-SEP-13" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "ora11g" (CHAR)
DEFINE _USER = "SCOTT" (CHAR)
DEFINE _PRIVILEGE = "" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1102000200" (CHAR)
DEFINE _EDITOR = "vim" (CHAR)
DEFINE _O_VERSION = "Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options" (CHAR)

修改SQL文本的一般模式为C/old/new，C是change的简写，此命令修改当前行中第一次出现的指定文本
old表示打算修改的SQL文本，new表示要替换的新SQL文本
SCOTT@ora11g>r
1 select deptno,job,sal,ename
2 from emp
3 where salary>1000
4* order by deptno,job,sal,ename
where salary>1000
*
ERROR at line 3:
ORA-00904: "SALARY": invalid identifier
SCOTT@ora11g>3
3* where salary>1000

SCOTT@ora11g>c/salary/sal 修改SQL文本
3* where sal>1000
SCOTT@ora11g>l
1 select deptno,job,sal,ename
2 from emp
3 where sal>1000
4* order by deptno,job,sal,ename
SCOTT@ora11g>r
1 select deptno,job,sal,ename
2 from emp
3 where sal>1000
4* order by deptno,job,sal,ename

i是input的简写，在一个SQL脚本的末尾新行上插入文本，
插入完新行后，输入点号 . 返回SQL提示符
SCOTT@ora11g>l
1 select deptno,job,sal,ename
2 from emp
3 where sal>1000
4* order by deptno,job,sal,ename
SCOTT@ora11g>3
3* where sal>1000

SCOTT@ora11g>i
4i and sal<3200
5i .
SCOTT@ora11g>l
1 select deptno,job,sal,ename
2 from emp
3 where sal>1000
4 and sal<3200
5* order by deptno,job,sal,ename

del或d删除指定的行，默认删除最后一行
SCOTT@ora11g>l
1 select deptno,job,sal,ename
2 from emp
3 where sal>1000
4 and sal<3200
5* order by deptno,job,sal,ename

SCOTT@ora11g>del 4
SCOTT@ora11g>l
1 select deptno,job,sal,ename
2 from emp
3 where sal>1000
4* order by deptno,job,sal,ename

append给特定的行添加一个或两个词
SCOTT@ora11g>l
1 select deptno,job,sal,ename
2 from emp
3 where sal>1000
4* order by deptno,job,sal,ename
SCOTT@ora11g>1
1* select deptno,job,sal,ename
SCOTT@ora11g>append ,comm
1* select deptno,job,sal,ename,comm
SCOTT@ora11g>l
1 select deptno,job,sal,ename,comm
2 from emp
3 where sal>1000
4* order by deptno,job,sal,ename

使用sqlplus Copy 命令从远程数据库读取数据.
需要从9个地市的计费数据库中读取一些表,存储到运营分析系统中.
有几个表数据量超过1亿条.

对于这么大的数据量,如果用create table as select 或者使用cursor的话,对回滚段的压力肯定非常大.

经过同事提示,想到了sqlplus的copy命令.

connect ods/ods
set time on
set timing on
set head on
set echo on
set copycommit 10
set arraysize 5000

-- 长春
copy from yyfx/******@ccbill1 to changchun/******@ora1 create serv using select * from lbas.serv;

这个命令不用太多解释.
需要注意的是: create处有4中选择: create,append,insert,replace.
具体命令下面有一个itput的帖子,解释得很详细.

补充点: 在sqlplus执行这个命令的时候,有3个选项需要注意:
set long 1000 -----------long型字符的长度
set copycommit 10 -----------读多少次提交
set arraysize 5000 -----------每次读取数据的行数
根据以上的设置,copy命令每次读取5000行数据,没读取10次(50000行)commit一次.

下面是itpub的文档:

Copy Command的初步研究

SQL*Plus Copy Command
前言
这段时间论坛里有好几个贴子讨论到了在表之间复制数据的问题，也讨论到了SQL*Plus Copy Command。
在数据表间复制数据是Oracle DBA经常面对的任务之一，Oracle为这一任务提供了多种解决方案，SQL*Plus Copy Command便是其中之一。SQL*Plus Copy Command通过SQL*Net在不同的表(同一服务器或是不同服务器)之间复制数据或移动数据。

SET MARKUP习惯称作SQLPLUS -MARKUP命令。
使用SQLPLUS -MARKUP HTML ON或者SQLPLUS -MARKUP HTML ON SPOOL ON 产生标准的web页
SQL*Plus 自动生成完整的用<HTML>和<BODY>标签封装HTML网页

把输出的html页嵌入在一个已有的网页中
SQL>set markup html on spool on
SQL>
SQL>set markup html off spool off

remark加入注释
注释的注意事项：
1:在语句开始的时候插入注释，sqlplus会根据刚开始的关键字判断
用户输入的语句是SQL命令，还是PL/SQL语句;
2:不要在一个语句的结束符后加注释。会干扰sqlplus的判断
3：行注释后不能加分号;
4：不要在注释中加入& 符号

使用 /*...*/
可以在脚本中同一行、不同行使用/*...*/,也可以在PL/SQL块中使用。
必须在/*后面的注释内容前增加一个空格。
该注释可以跨越多行，但不能嵌套。

) 使用--
可以在SQL、PL/SQL块、SQL*Plus命令中使用--注释。由于没有结束符，该注释不能跨多行。
对于PL/SQL和SQL,在命令行后使用该注释或单独占用一行。