数据库简易链接方法
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,updateSQL> 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]$ lsglogin.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 scottUPDATESQL*PLUS scott
DELETEdelete 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 SHOWhost是在不离开Sqlplus环境下执行主机下的命令的
SQL> host copy D:\ORACLE\ORADATA\ORA10\TS1.1 D:\ORACLE\ORADATA\ORA10\TS1.dbfSQL> ! copy D:\ORACLE\ORADATA\ORA10\TS1.1 D:\ORACLE\ORADATA\ORA10\TS1.dbfSCOTT@ora11g>host[oracle@gc ~]$ exitexitSCOTT@ora11g>SCOTT@ora11g>!
[oracle@gc ~]$ exitexitSCOTT@ora11g>help set
help copyhelp 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 pageSET 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命令,缺省为onSQL> set echo on //设置运行命令是是否显示语句SQL> set feedback on; //设置显示“已选择XX行”SQL>set feedback off; //回显本次sql命令处理的记录条数,缺省为onSQL>set heading off; //输出域标题,缺省为onSQL>set pagesize 0; //输出每页行数,缺省为24,为了避免分页,可设定为0。SQL>set linesize 80; //输出一行字符个数,缺省为80SQL>set numwidth 12; //输出number类型域长度,缺省为10SQL>set termout off; //显示脚本中的命令的执行结果,缺省为onSQL>set trimout on; //去除标准输出每行的拖尾空格,缺省为offSQL>set trimspool on; //去除重定向(spool)输出每行的拖尾空格,缺省为offSQL>set serveroutput on; //设置允许显示输出类似dbms_outputSQL> 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 0set pagesize n 设置每页的行数,将 n 设为 0 可以不显示所有 headings, page breaks, titles, the initial blank line, and other formatting informationset 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_wrappedSQL> set errorlogging on 打开错误记录
SQL> show errorloggingerrorlogging is ON TABLE SYS.SPERRORLOGSQL> desc sperrorlog Name Null? Type ----------------------------------------- -------- ---------------------------- USERNAME VARCHAR2(256) TIMESTAMP TIMESTAMP(6) SCRIPT CLOB IDENTIFIER VARCHAR2(256) MESSAGE CLOB STATEMENT CLOBshow all
show system-variableshow errorsshow errors [function|procedure|package|package body|trigger|view|type|type body|dimension|java class name-of-function-procedure-etc]show lnoshow parameters param-nameshow pnoshow recyclebinshow releaseshow repfootershow repheadershow sgashow spoolshow sqlcodeshow titleshow userSQL> 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 recyclebinNAME TYPE VALUE------------ ----------- ------recyclebin string onSQL> set sqlprompt "_USER'@'_CONNECT_IDENTIFIER >"
SYS@ora11g >SYS@ora11g >SYS@ora11g >CONN scott/tigerConnected.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.TXTSELECT * 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 pausePAUSE is OFFSYS@ora11g>set pause onSYS@ora11g>break指定格式变化的位置和类型,每个on指定一个断点,须和order by一起用
break on deptno skip page on job skip 1 on sal skip 1
select deptno,job,sal,ename
from empwhere sal>1000order 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 WARD1500 TURNER
1600 ALLEN
select to_char(sal,'$9999') from emp;
列格式
column column_name format a50 heading '别名' (设置column_name为50个字符宽)col sal for $99991st 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 offSCOTT@ora11g>btitle offSCOTT@ora11g>ttitle offSave
将当前 SQLPLUS缓冲区内的 SQL 语句保存到指定的文件中如save create c:\2.txt save replace save appendGet
将文件中的SQL语句调入到SQLPLUS缓冲区内。如 get c:\2.txtlist查看要执行的代码
/不显示所执行的代码run显示所执行的代码用&替换变量
define department=&deptnoundefine 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 ProductionWith the Partitioning, Automatic Storage Management, OLAP, Data Miningand 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,enamewhere salary>1000 *ERROR at line 3:ORA-00904: "SALARY": invalid identifierSCOTT@ora11g>3 3* where salary>1000SCOTT@ora11g>c/salary/sal 修改SQL文本
3* where sal>1000SCOTT@ora11g>l 1 select deptno,job,sal,ename 2 from emp 3 where sal>1000 4* order by deptno,job,sal,enameSCOTT@ora11g>r 1 select deptno,job,sal,ename 2 from emp 3 where sal>1000 4* order by deptno,job,sal,enamei是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,enameSCOTT@ora11g>3 3* where sal>1000SCOTT@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,enameSCOTT@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,enameappend给特定的行添加一个或两个词
SCOTT@ora11g>l 1 select deptno,job,sal,ename 2 from emp 3 where sal>1000 4* order by deptno,job,sal,enameSCOTT@ora11g>1 1* select deptno,job,sal,enameSCOTT@ora11g>append ,comm 1* select deptno,job,sal,ename,commSCOTT@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 onset timing onset head onset echo onset copycommit 10set 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 onSQL>SQL>set markup html off spool offremark加入注释
注释的注意事项:1:在语句开始的时候插入注释,sqlplus会根据刚开始的关键字判断用户输入的语句是SQL命令,还是PL/SQL语句;2:不要在一个语句的结束符后加注释。 会干扰sqlplus的判断3:行注释后不能加分号;4:不要在注释中加入& 符号使用 /*...*/
可以在脚本中同一行、不同行使用/*...*/,也可以在PL/SQL块中使用。必须在/*后面的注释内容前增加一个空格。该注释可以跨越多行,但不能嵌套。) 使用--
可以在SQL、PL/SQL块、SQL*Plus命令中使用--注释。由于没有结束符,该注释不能跨多行。对于PL/SQL和SQL,在命令行后使用该注释或单独占用一行。