Oracle操作笔记.docx

Oracle操作笔记.docx

《Oracle操作笔记.docx》由会员分享，可在线阅读，更多相关《Oracle操作笔记.docx（34页珍藏版）》请在冰豆网上搜索。

Oracle操作笔记

TOP

SQL开始#Oracle安全#Oracle其他#Oracle表

Oracle约束#Oracle索引#Oracle表空间

#Oracle导入导出数据#Oracle一些函数的用法#Oracle锁

#Oracle表分区#Oracle同义词（别名）#Oracle序列

#Oracle视图#Oracle簇#Oracle自定义类型#Oracle角色#Oracle嵌套查询#Oracle触发器#数据库启动和关闭

#数据库备份和恢复#关联游标#报错解决办法

SQL开始#TOP

在以SYSDBA身份登陆时可以修改其他用户的密码，比如：

alteruser要修改的用户名identifiedby要修改为什么密码;（密码以字母开头）

存储过程是没有返回值的。

执行完这个过程后，要去查对应的表有没有插入数据。

函数是返回单个值。

即使是通过多条记录操作也是返回单个值。

在SQL*plus连接别的用户：

Connect用户名/密码

启动监听：

C:

\>lsnrctl；LSNRCTL>status

cmd连接数据库：

sqlplus/nologuser/password@sid或sqlplus/assysdba

显示当前连接用户:

showuser;

新建用户：

createuser新建的用户名identifiedby密码;

删除用户：

dropuser用户名;

删除用户时一并删除用户下表空间里的数据:

dropuser用户名cascade;

授权:

grantconnect,resourceto要授权给哪个用户;（resource是系统自带的角色）

提交:

commit;回退:

rollback;（当表被锁时，用这2个就相当与解锁）

创建事务保存点：

savepoint事务保存点的名;（创建后，表里的数据要是有过变动，不能commit，否则保存点失效。

若创建了多个保存点，回退到最前面的那个保存点的话，剩下的都失效了）

回退到事物保存点：

rollbackto事务保存点名

数据类型（varchar（size）:

可变字符数据.char（size）:

固定长度字符数据.number（p{代表数据类型的有效数据位},s{小数的位数}2者要是整数值）:

数值型.date:

日期时间型.）int类型都自动转换为number类型。

打开文本：

ed;

Createor replace:

意思是存在就替换它，没有就建立。

要不加orreplace，就只能建立，存在的话会报错。

查看所有用户的权限：

select*fromdba_tab_privs;

查看当前连接用户的权限：

select*fromuser_tab_privs

查看用户具有的角色：

select*fromdba_role_privs

2的2次方插入数据：

insertinto表名select*from表名;（表里的字段类型要一致，要插入一行数据先）

复制表结构和数据：

createtable新表名asselect*from要复制的表名;

别名：

selectmax（shuxue）dsd_dsfromhuang;

后面跟上加_号的就行。

ROWSBETWEENUNBOUNDEDPRECEDINGANDUNBOUNDEDFOLLOWING（在最前面的行和最后面的行之间）

ROWSUNBOUNDEDPRECEDING（前无限大）

ROWSBETWEEN1PRECEDINGAND1FOLLOWING（在前一行和后一行之间，包括自己本身）前面是加1。

后面是减1

RANGE：

（后面跟的是百分比划分的区间）

Rows（跟着记录的条数划分的区间）

探查字段是否为空：

select*from表名where字段名isnull//查不出数据是不能为空

（空的返回1）selectnvl（字段名,1）from表名;

查看字段是否可为空：

select*from表名where字段名isnull//查不出数据是不能为空

查看字段是否可为空:

（空的返回1）selectnvl（字段名,1）from表名;

查看2张表某个字段的数据有哪些差异：

select * from b

where id not in（select id from a）;--b表的中的id不在a表中,显示的数据是b表里的

select * from b

where not exists（select 1 from a where a.id=b.id）;--b表的中的id不在a表中,显示的数据是b表里的

Orderbydesc;--升序，第一行是最大的orderbyasc;--降序，第一行是最小的

双网卡的机子jdbc写法：

dbc:

oracle:

thin:

@（DESCRIPTION=（LOAD_BALANCE=on）（ADDRESS=（PROTOCOL=TCP）（HOST=10.64.1.30）（PORT=1521））（ADDRESS=（PROTOCOL=TCP）（HOST=10.64.1.32）（PORT=1521））（CONNECT_DATA=（SERVICE_NAME=life）））

Delete2个表关联查出的结果：

deletefromtablenamewhere列名1in（select列名1fromtable1a,table2b

wehrea.aa=b.bb

anda.cc=b.cc）

拼批处理SQL：

select'createtable'||TNAME||''||'asselect*from'||TNAME||'@DG590;'frometl_interface

查看执行计划：

SQL>explainplanforselecta.*fromta,tbwherea.num=b.num;

SQL>select*fromtable（dbms_xplan.display）;

清空Oracle10g回收站中以BIN$开头的表：

  清除的方法如下：

   purgetableorigenal_tableName;--原来的表名

    或者在删除表时加上PURGE选项，如：

    DROPTABLEt_testPURGE;

    清空整个回收站的命令：

    PURGErecyclebin;

  以DBA身份清除所有回收站对象：

purgedba_recyclebin;

   查询回收站垃圾信息的SQL语句：

   SELECTobject_name,type,original_nameFROMuser_recyclebin;

恢复表：

FLASHBACKtablef_plan_bakTOBEFOREDROP

在命令窗口里导入.sql文件：

@f:

\aa.sql;（不支持中文）

用语句杀进程：

首先查看运行的job：

select*fromdba_jobs_running;--查看SID和正在执行的job

SELECT*FROMV$DB_OBJECT_CACHEWHEREOWNER='DMUSER'ANDLOCKS!

='0';--查看被锁的表

SELECTSID,SERIAL#,PADDRFROMV$SESSIONWHERESID=3126;--根据SID查看序列号

ALTERSYSTEMKILLSESSION'3126,895'—3126SID,895序列号

修改sga_max_size，从plife文件修改，停数据库shutdownimmediate：

createpfile='c:

\pfile.ora'fromspfile;修改sga_max_size改到1300M对应的bytes，然后createspfilefrompfile='c:

\pfile.ora'。

启动数据库。

SESSION最大会话数根据processes来决定，从plife文件修改，停数据库shutdownimmediate：

createpfile='c:

\pfile.ora'fromspfile;把processes修改为350,然后createspfilefrompfile='c:

\pfile.ora'。

启动数据库startup。

修改SESSION最大会话数sessions=processes*1.1+5：

altersystemsetSESSIONS=300scope=spfile;sessiones是个派生数，由processes决定需要shutdownimmediate数据库startup

查看processes相关信息：

showparameterprocesses;

查看并行的相关信息:

showparameterparallel;

查看会话数相关信息：

showparametersessions;

修改每次并行最大只能开4个session来跑并行：

altersystemsetparallel_max_servers=4;看Cpu有几个和Cup空闲程度

在insert或select里写：

/*+appendparallel（plcy_event,4）*/4是根据parallel_max_servers设置的大小和Cup空闲程序来设置

求区间的SQL：

先建立一张区间表，然后运行SQL：

selectb.startid,b.endid,count（a.g）,sum（a.h）

fromxuqia,bb

wherea.h>b.startid

anda.h<=b.endid

groupbyb.startid,b.endid

在sql窗口执行存储过程，可定位到第几行出错（CTRL+G）DECLAREv_par_datevarchar2（8）:

='20091130';v_batch_idvarchar2（10）:

='2009113001';n_source_system_idNUMBER:

=3;

Orcale安全#TOP

查看dba权限管理下的用户:

select*fromdba_users;

显示所有用户的数据字典：

select*fromall_users;

Createuser用户名identifiedby密码

defaulttablespace表空间名

Temporarytablespace表空间名（临时表空间）

Quota整数K/Mon表空间名（或者不限制表空间：

unlimitedon表空间名）

Quota整数K/Mon临时表空间名

锁定用户：

Alteruser用户名accountlock;

解锁：

alteruser用户名accountunlock;

密码失效：

alteruser用户名passwordexpire;

查看权限的数据字典：

select*fromuser_sys_privs;

查询某个字段是否唯一：

SELECTA,B,CFROMTABLEGROUPBYA,B,CHAVINGCOUNT（A）>1怀疑哪个字段有重复的，就写到count（）里

打开自动跟踪：

setautotraceon

创建Dblinks：

CREATE（public）公共的DATABASELINKpicc_ods

CONNECTTOodsuser

IDENTIFIEDBYodsuser

USING'（DESCRIPTION=

（ADDRESS_LIST=

（ADDRESS=（PROTOCOL=TCP）（HOST=10.64.1.32）（PORT=1521））

）

（CONNECT_DATA=

（SERVICE_NAME=life）

）

）';

在监听文件里如果没有配置监听的话用上面的方法建立

createdatabaselinkpiccods

connecttopiccdos

identifiedbyp_dos_#78

using'DG590';

createdatabaselinkPICCODS.REGRESS.RDBMS.DEV.US.ORACLE.COM

connecttoPICCDOSIDENTIFIEDBYodsuser

using'（DESCRIPTION=

（ADDRESS_LIST=

（ADDRESS=（PROTOCOL=TCP）（HOST=10.64.1.163）（PORT=1521））

）

（CONNECT_DATA=

（SERVICE_NAME=dg590）

）

）'

存储过程在页面输出信息：

dbms_output.put_line（v_tabname||'test'）;

其他#TOP

修改年月日格式：

altersessionsetNLS_date_format='YYYY-MM-DD';

改回原来的日期格式：

altersessionsetnls_date_format=‘DD-Mon-yy’;

格式化日期：

SELECTto_date（'30-6月-10','dd-mon-yy'）fromdual

填充值：

insertinto表名（列名,列名）values（数值,‘XX'）;注：

列名和数值要相对应，字符型数据要加单引号，数字型数据不加。

只显示年：

selectextract（yearfromsysdate）FROMdual

同时插入多条数据：

insertinto表名（列名,列名）values（&任意定义,‘&和前面一至'）;配合（run或者r,/使用）

表中行对齐的修改：

setlinesize大小;表中列对齐的修改：

setpagesize大小;

执行上一条语句：

run或者r;或者/

转换日期语句：

to_date（‘1998-3-4',‘yyyy-mm-dd'）

精确到秒：

to_date（'2005-12-239:

37:

01','yyyy-mm-ddhh24:

mi:

ss'）

更新：

update表名set要插入数据的列名=数据where排第一的列名=第一列名的数据;

updatexfqdsetyear=2008whereddatebetweento_date（'2008-9-1','yyyy-mm-dd'）andto_date（'2008-10-31','yyyy-mm-dd'）

更新2个字段或以上：

updateD_CHANGE_SERVICEsetPREM_FLAG=1,PREM_FLAG_NAME='补退费类'

whereSERVICE_NAMEin

UPDATEM_RC_CP_ASSET_DEBT_INFOaSET

（curr_classi_cd,AUDITED_ADJUST_DATE,CLASSI_PROGRESS_STAT,CLASSI_STAT_CHANGE_DATE,LOW_RISK_IND）=

（SELECTcurr_classi_cd,AUDITED_ADJUST_DATE,CLASSI_PROGRESS_STAT,CLASSI_STAT_CHANGE_DATE,LOW_RISK_INDFROMM_AA_CP_ASSETS_CLASS_RES_T0131b

WHEREa.AGREEMENT_NO=b.agreement_no

ANDb.report_term_date=DATE'2010-01-31'

ANDb.agreement_no

保存语句：

save‘路径:

\文件名.txt’

把保存的语句加载到SQL*：

Plus中：

get‘路径:

\文件名.txt’（只显示出不执行）

查询别人的数据库：

需要先得到授权。

然后：

select*from要查询的用户名.要查询的用户的表名;

Grantselect（查询）,update（修正）,insert（插入）,delete（删除）on要看的表的名to要授权给哪个用户的名;（如用sys进行授权的话：

on要查看的用户名.表名）

允许得到授权后的用户对其他用户授权：

grantinsert,updatedeleteon要查看的表名to要授权给哪个用户withadminoption;

给所有用户授权：

grantallon要查看的表名topublic;

收回权限：

revokeinsert,updateon用户名.表名from要收回权限的用户名;

收回所有的权限：

revokeall表名frompublic;

收回创建表等的权限：

revokecreatetable,createviewfrom要收回权限的用户名;

授与创建的权限：

grantcreatetable，createviewto要授权的用户;

清空页面内容：

clearscreen;

设置表中的列处于无用状态：

altertable表名setunusedcolumn列名;（不可在恢复）

删除无用状态的列：

altertable表名dropunusedcolumns;

把提示符号修改：

setsqlprompt想修改成什么;

在Oracle中。

每个表里都有2个隐藏的列，列名为rowid,rownum要查询的话：

selectrowid,rownum（后面可以在跟表里的其他列名）from表名;

查看数据库的字符集：

select*fromv$nls_valid_valueswhereparameter='CHARACTERSET'

大写数字排序方法：

select*fromhuangorderbydecode（shuzi,'一',1,'二',2,'三',3,'四',4,'五',5,'六',6）;

例子的意思：

把大写数字转译为1,2,3..

查看详细的错误信息:

showerror;

Groupby使用方法：

selectbanji,max（shuxue）fromhuanggroupbybanji;（groupby后跟的和select后跟的要一样，select后如有多个，groupby后跟上一个也可。

）使用groupby能把重复数据省略。

如：

在一张表中求3个班的平均值

selectbanji,avg（shuxue）over（partitionbybanji）fromhuang;

返回9条记录。

但使用：

selectbanji,avg（shuxue）fromhuanggroupbybanji;

只返回3条记录。

查詢一行number類型合：

select字段1+字段2from表名;

求指定行的合：

select字段1+字段2from表名wherehnobetween1and4

select字段1+字段2from表名wherehno>=1andhno<=4

遇到空值時:

selectnvl（字段1，0）+（字段2，0）from表名;

返回查询的时间：

settimingon

模糊查询：

select*fromuser_tableswheretable_namelike'%TEMP%';

注意%符号和大写

拼'truncate（清空）语句：

selectTABLE_NAMEfromdba_tableswhereowner='SYS';注意用户名大小写。

然后可以在存储过程里定义一个变量,把这个select语句赋予V_SQL,在EXECUTEIMMEDIATEV_SQL;要授权:

grantselectondba_tablestoetl_test;

添加索引后,需更新下.要不有的索引所用不到:

executedbms_stats.GATHER_SCHEMA_STATS（'用户名',DBMS_STATS.AUTO_SAMPLE_SIZE）

整个schema的统计更新：

EXECDBMS_STATS.gather_schema_stats（ownname=>'',cascade=>true,degree=>4）;--单独收集某张表的统计信息

begin

dbms_stats.gather_table_stats

（ownname=>'bcrm_htkf',

tabname=>'B_GL_CREDIT_LOAN_SUBJECT_PARA',

--method_opt=>'forallindexedcolumns',

cascade=>TRUE）;

end;

定义jobs（定时执行）

在what值里：

如遇到带参数才能运行的存储过程：

ods_test1（to_date（'2007-01-01','yyyy-mm-dd'）,sysdate-2）;（带日期参数的。

）

在下次执行时间填好日期，如2009-1-114:

05:

00

间隔时间填：

trunc（sysdate）+1+14/24+05/1440（表示下次执行时间为：

2009-1-214:

05:

00）

（sysdate）+1为日期，14/24为24小时制的14点，05/1440为一天有1440分钟，在05分执行

TRUNC（sysdate,'mi'）+1/（24*60）–间隔每分钟运行

SYSDATE+10/（24*60*60）–系统时间加10秒

在存储过程里写上,遇到错误会弹出对话框:

exception

whenothersthen

raise;

当几个存储过程写在一起时，在commit后加上此语句，此存储过程要报错的话，还能执行下一个

EXCEPTION

WHENOTHERS

THEN

求2个日期的差大于10分钟

selectt.*,（t.taskendtime-t.taskstarttime）*24*60*60/60frometl_logtwhere（t.taskendtime-

t.taskstarttime）*24*60*60/60>10

精确到秒的2个日期相减后,得到的是毫秒级的,*24*60*60/60这样是求分钟.

selectt.*,round（to_number（t.taskendtime-t.taskstarttime）*1440）asafrometl_logt

whereround（to_number（t.taskendtime-t.taskstarttime）*1440）>10精确到分钟

去掉1440是到天的,换成*24是到小时,1440是到分钟,86400是到秒

这样也可以selectto_number（taskendtime-taskstarttime）*1440frometl_l