常用oracle命令 计算机.docx
OraCle查询表的注释，及列的注释selectt3.table_name,ments,ments,tl.CO1.UMN_NAME,tl.DATATYPEI,(,IIdecode(tl.DATAjYPE,',tl.DATA_PRECISION+tl.DATA_SCA1.E,tl.data_length),fromuser_tab_colstl,user_col_commentst2,user_tab_commentst3wheretl.TAB1.E_NAME-t2.table_name(+)andtl.C01.UMN_NAME=t2.column-name(+)andtl.TAB1.E_NAME-t3.table-name(+)selectA.TableName表名，A.columnname字段名，A.datatype数据类型，A.data_length长度，A.data_precision整数位，A.Data_Scale小数位,A.nullable允许空值,A.Data_default缺省值，B.comments备注fromuser_tab_co1umnsA,user_col_commentsBwhereA.Table_Name=B.Table_NameandA.Column_Name=B.Column_NameandB.comments1.IKE'%检查'oracle设置select*fromnls_database_parameters;select*fromnls_instance_parameters;SE1.ECT*FROMn1s_sessiOnparameters;select*fromv$n1s_parameters;altersystemsetn1s_dateformat三,YYYY-MM-DDHH24:MI:SS'scope=spfile;altersessionsetnlsdateformat='YYYY-MMT)DHH24:MI:SS'selectsysdatefromdual;修改系统环境变量N1.S_DATE_FORMAT:YYYY-MM-DDHH24:MI:SSN1.S_TIMESTAMP_FORMAT:YYYY-MM-DDHH24:MI:SS:FF6(2)修改注册表：N1.S_DATE_FORMAT：YYYY-MM-DDHH24:Mi:SS(3)修改系统时间：任务栏时间OraCle9i更改字符集A1.32UTF8为ZHS16GBK(1)修改注册表N1.S_1.ANG：SIMP1.IFIEDCHlNESE_CHINA.ZHSl6GBK或者SIMP1.IFIEDCHINESECHINA.A1.32UTF8SQ1.>conn/assysdbaSQ1.>shutdownimmediate;SQ1.>startupmountSQ1.>A1.TERSYSTEMENAB1.ERESTRICTEDSESSION;SQ1.>A1.TERSYSTEMSETJOB_QUEUE_PROCESSES=O;SQ1.>A1.TERSYSTEMSETAQjrM.PROCESSES二。；SQ1.>alterdatabaseopen;SQ1.>A1.TERDATABASECHARACTERSETZHS16GBKcSIMP1.IFIEDCHINESE_CHINA.A1.32UTF8SQ1.>A1.TERDATABASEcharactersetINTERNA1._USEZHS16GBK;或者SIMP1.IFIEDCHINESE_CHINA.A1.32UTF8SQ1.>shutdownimmediate;SQ1.>startup查看表空间利用selecta.tablespace_name,a.bytes/1024/1024/1024”SumMB”,(a.bytes-b.bytes)/1024/1024/1024"usedMB",b.bytes/1024/1024/1024z,freeMB，round(a.bytes-b.bytes)a.bytes)*100,2)percent_used”from(selecttablespace_name,sum(bytes)bytesfromdba_data_fliesgroupbytablespace_name)a,(selecttablespace_name,sum(bytes)bytes,max(bytes)largestfromdba_free_spacegroupbytab1espace_name)bwherea.tab1espace_name-b.tablespace_nameorderby(a.bytes-b.bytes)a.bytes)desc创建表和主键createtable0_JKDAFBGWiddoc_idissbNUMBEr(IO)notnull,NUMBEr(IO)notnull,CHAR(I)default'0',sb_dateDATE,create_dateDATEdefaultSYSDATE,create_depNUMBER(10),CreateJ1.oginidVARCHAR2(30),create_empVARCHAR2(30),last_dateDATE）；commentontable0JKDFBGWisaltertableO_JKDA_FB_GWaddconstraintPK_JKDA_FB_GWprimarykey(ID);创建系列CreatesequenceSE_TEST_SYS111999999999StartwithIncrementbyMinvalueMaxvalueNocycleOrderNocachecreatesequenceQ_JKDA_FB_GWminvalue1maxvalue999999999startwith1incrementby1nocacheorder;导入命令impIdscgl/dbaorclfile-e:scgl.dmpfull=yignore=y删除重复记录sqlDE1.ETEFROMTB_MINE_STATUS_INFOEWHEREE.ROWID>(SE1.ECTMIN(X.ROWID)FROMTB_MINE_STATUS_INFOXWHEREX.mine_no=E.mine_no)重置oracle系列值(1)重置单个系列createorreplaceprocedureseq_reset(v_seqnamevarchar2)asnnumber(10);tsqlvarchar2(100);beginexecuteimmediate,selectv_seqname.nextvalfromduainton;ifn>lthenn:=(n-l);tsql:=,altersequenceIIv_seqnameII,incrementby,Nn;executeimmediatetsql;executeimmediate,selectv_seqname,.nextvalfromduainton;tsql:=，altersequencevseqname,incrementby;executeimmediatetsql;endif;endseq_reset;(2)重置多个系列createorreplaceprocedureall_seq_resetascur_resultsys_refcursor;v_seqnameVarchar(100);beginopencur_resultforselectsequence_namefromuser_sequences;loopfetchcur_resultintov_seqname;exitwhencur_result%notfound;seq_reset(v_seqname);endloop;endall_seq_reset;
