数据库培训序列之维护和使用经验.ppt

2019/12/6,CRM,数据库培训序列之维护和使用经验,Page2,SQL使用技巧,DBA使用技巧,Page3,查数据库中表的任n行的记录:select*from(selectrownumx,tab.*fromtab)wherexin(2,3);查询出数据库中表大于1000的记录:select*from(selectrownumx,tab.*fromtab)wherex1000,Page4,使用一条sql可以insert到多个表中,INSERTALLINTOsalesVALUES(product_id,customer_id,weekly_start_date,P,501,q_sun,sales_sun)INTOsalesVALUES(product_id,customer_id,weekly_start_date+1,P,501,q_mon,sales_mon)INTOsalesVALUES(product_id,customer_id,weekly_start_date+2,P,501,q_tue,sales_tue)INTOsalesVALUES(product_id,customer_id,weekly_start_date+3,P,501,q_wed,sales_wed)INTOsalesVALUES(product_id,customer_id,weekly_start_date+4,P,501,q_thu,sales_thu)SELECT*FROMsales_input_table;,Page5,层次查询,selectlevel,empno,ename,mgrfromempstartwithmgrisnullconnectbypriorempno=mgrSQL/LEVELEMPNOENAMEMGR-17839KING27566JONES783937902FORD756647369SMITH790227698BLAKE783937499ALLEN769837521WARD769837654MARTIN769837844TURNER769837900JAMES769827782CLARK7839LEVELEMPNOENAMEMGR-37934MILLER7782已选择12行。,Page6,在groupby中不能使用别名,SQLselecta1,a2,count(*)fromtestgroupbya1,a2;A1A2COUNT(*)-113121131SQLselecta1a,a2,count(*)fromtestgroupbya,a2;selecta1a,a2,count(*)fromtestgroupbya,a2*ERRORatline1:ORA-00904:invalidcolumnname,Page7,如何访问远程数据库上的表,SQLcreatedatabaselinkdsmp2connecttoimuse01identifiedbyimuse013usingdsmpdb;Databaselinkcreated.SQLselectcount(*)frombasetabdsmp2;COUNT(*)-2000SQLSQLcreatesynonymbasetabforbasetabdsmp;Synonymcreated.SQLselectcount(*)frombasetab;COUNT(*)-2000SQL,Page8,如何插入字,SQLselectfromdual;SQLselectascii()fromdual;SQLselectchr(39)fromdual;,Page9,如何删除表中的重记录,SQLselect*fromtest;A1A2-1111111213SQLselect*fromtestawhererowid(selectmin(rowid)fromtestbwherea.a1=b.a1anda.a2=b.a2);A1A2-1111,1deletefromtestawhererowid2(selectmin(rowid)fromtestb3*wherea.a1=b.a1anda.a2=b.a2)SQL/已删除2行。,Page10,如何把表中的记录横放,SQLselect*fromwd;REVA-110022003300,A1A2A3-100200300,Sqlselectsum(decode(re,1,va,0)a1,sum(decode(re,2,va,0)a2,sum(decode(re,3,va,0)a3fromwd,Page11,SQL使用技巧,DBA使用技巧,Page12,如何查询表空间中剩余的空间,selectTABLESPACE_NAME,sum(bytes)/1024/1024,sum(blocks)fromdba_free_spacegroupbytablespace_nameTABLESPACE_NAMESUM(BYTES)/1024/1024SUM(BLOCKS)-DRSYS15.86718752031IMUSE011795.0625229768IMUSE01_INDEX1999.4375255928IMUSE01_TEMP199.48437525534INDX19.99218752559RBS156.99218820095SYSTEM10.251312TEMP19.99218752559TOOLS4.8671875623USERS22.49218752879,Page13,如何知道数据库使用的字符集,selectvaluefromnls_database_parametersWherePARAMETER=NLS_CHARACTERSETVALUE-ZHS16GBK,Page14,如何修改数据库中的字符集,以sysdba用户的权限登陆数据库sqlplus“/assysdba”SQLSHUTDOWNIMMEDIATE;SQLSTARTUPMOUNT;SQLALTERSYSTEMENABLERESTRICTEDSESSION;SQLALTERSYSTEMSETJOB_QUEUE_PROCESSES=0;SQLALTERDATABASEOPEN;SQLALTERDATABASECHARACTERSETZHS16GBK;SQLSHUTDOWNIMMEDIATE;-orNORMALSQLSTARTUP;,Page15,如果错误删除了某个数据库文件，数据库起不来如何办,SQLstartupORACLEinstancestarted.TotalSystemGlobalArea96393244bytesFixedSize75804bytesVariableSize56918016bytesDatabaseBuffers39321600bytesRedoBuffers77824bytesDatabasemounted.ORA-01157:cannotidentify/lockdatafile10-seeDBWRtracefileORA-01110:datafile10:D:TEST.DBFSQLalterdatabasedatafiled:test.dbfofflinedrop;Databasealtered.SQLalterdatabaseopen;,Page16,为何数据库不能显示中文,与数据库的字符集有关。一般在安装数据库时选择的字符集为zhs16gbk。在客户端的字符集的选择是在注册表中（对于windows的机器），如果在UNIX中则要设置环境变量。服务器与客户端的字符集一定要一样。如果在更换字符集则要重安数据库，但可以用alterdatabasesetcharacter命令将字符集设为超集,Page17,如何知道当前用户操作权限,SQLselect*fromsession_roles;DBASELECT_CATALOG_ROLEHS_ADMIN_ROLEEXECUTE_CATALOG_ROLEDELETE_CATALOG_ROLEEXP_FULL_DATABASEIMP_FULL_DATABASEAQ_ADMINISTRATOR_ROLE,Page18,如果kill掉一个数据库中的用户,Selectsid,serial#fromv$sessionwheresid6SIDSERIAL#-71779879923101911191224132315161615Altersystemkillsession13,23,Page19,如何输出sqlplus中的结果,Sqlspoolc:a.txtSqlselect*fromtab;Sqlspooloff,Page20,如何在sqlplus中执行一个操作系统命令,使用!可以执行操作系统中的命令Sql!LsSql!Mva.datb.dat,Page21,如何生成自动脚本,充分利用数据字典例子如下：SQLsetheadingoffSQLsetfeedbackoffSQLsetpagesize0SQLselectdrop|tabtype|tname|;fromtabdropTABLEBONUS;dropTABLEDEPT;dropTABLEDUMMY;dropTABLEEMP;dropTABLEEMP1;dropTABLEEMP2;dropTABLESALGRADE;,Page22,如何在进入sqlplus时自动执行一个脚本,$ORACLE_HOMEsqlplusadminglogin.sql,Page23,你知道以下概念吗？,实例SidinstanceDb_nameServicenames,Page24,为何数据库连接不上,fzj:/export/home/fzjsqlplussystem/managerdsmpSQL*Plus:Release8.1.7.0.0-Productionon星期四7月1716:27:452003(c)Copyright2000OracleCorporation.Allrightsreserved.ERROR:ORA-12154:TNS:couldnotresolveservicenameEnteruser-name:,Page25,为何数据库连接不上,请关注你客户机器上以下的文件：$ORACLE_HOME/network/admin/sqlnet.ora$ORACLE_HOME/network/admin/tnsnames.ora请关注你服务器上以下的文件：$ORACLE_HOME/network/admin/listener.ora,Page26,为什么数据库启动非常慢,关心你的服务器上以下目录是否存在Admin/cdumpAdmin/bdumpAdmin/udump,Page27,为什么listener启动非常慢,请关注以下文件：$ORACLE_HOME/network/admin/listener.ora/etc/hosts,Page28,如何把表从一个表空间移到另一个表空间,Altertableempmovetablespaceothers;在移动之后要重新rebuild索引,Page29,如何rebuild索引,Alterindexindex_namerebuildtablespaceinx;,Page30,B-TreeIndex,Indexentryheader,Keycolumnlength,Keycolumnvalue,ROWID,Root,Branch,Leaf,Indexentry,Page31,CoalescingIndexes,Beforecoalescing,Aftercoalescing,ALTERINDEXsummit.orders_id_idxCOALESCE;,Page32,还有其它的index你知道吗？,ReversekeyindexFunctionindexBitmapindex,Page33,KEYROWIDID(BLOCK#ROW#FILE#)-12570000000F.0002.000128770000000F.0006.000145670000000F.0004.000166570000000F.0003.000189670000000F.0005.000196370000000F.0001.000199470000000F.0000.0001.,ReverseKeyIndex,IndexonEMPLOYEE(ID),EMPLOYEEtable,IDFIRST_NAMEJOB-7499ALLENSALESMAN7369SMITHCLERK7521WARDSALESMAN7566JONESMANAGER7654MARTINSALESMAN7698BLAKEMANAGER7782CLARKMANAGER.,Page34,CreatingReverseKeyIndexes,CREATEUNIQUEINDEXsummit.orders_id_idxONsummit.orders(id)REVERSEPCTFREE30STORAGE(INITIAL200KNEXT200KPCTINCREASE0MAXEXTENTS50)TABLESPACEindx;,Page35,CreatingFunction-BasedIndexes,CREATEINDEXsummit.item_quantity_to_deliver_idxONsummit.item(quantity-quantity_shipped);,SELECTord_id,item_idFROMITEMWHERE(quantity-quantity_shipped)0;,Page36,BitmapIndex,Table,Index,Block10,Block11,Block12,File3,Page37,ComparingB-TreeandBitmapIndexes,B-treeSuitableforhigh-cardinalitycolumnsUpdatesonkeysrelativelyinexpensiveInefficientforqueriesusingORpredicatesUsefulforOLTP,BitmapSuitableforlow-cardinalitycolumnsUpdatestokeycolumnsveryexpensiveEfficientforqueriesusingORpredicatesUsefulfordatawarehousing,Page38,CreatingBitmapIndexes,CREATEBITMAPINDEXorders_region_id_idxONsummit.orders(region_id)PCTFREE30STORAGE(INITIAL200KNEXT200KPCTINCREASE0MAXEXTENTS50)T