【网学网提醒】：文章导读：在新的一年中，各位网友都进入紧张的学习或是工作阶段。网学会员整理了数据库常用SQL语法的相关内容供大家参考，祝大家在新的一年里工作和学习顺利！
数据库常用SQL语法1.表空间的使用情况：表空间的使用情况：
selecta.file_id"FileNo",substr(max(a.FILE_NAME),1,30),a.tablespace_name"Tablespace_name",a.bytes"Bytes",a.bytes-sum(nvl(b.bytes,0))"Used",sum(nvl(b.bytes,0))"Free",sum(nvl(b.bytes,0))/a.bytes*100"%free",a.STATUSfromdba_data_filesa,dba_free_spacebwherea.file_id=b.file_id(+)groupbya.tablespace_name,a.file_id,a.bytes,a.STATUSorderbya.tablespace_name;SELECTD.TABLESPACE_NAME"表空间名",SPACE"总空间(G)",BLOCKS"最大空间块",SPACE-NVL(FREE_SPACE,0)"使用空间(G)",ROUND((1-NVL(FREE_SPACE,0)/SPACE)*100,2)"使用率%",FREE_SPACE"剩余空间(G)"FROM(SELECTTABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024*1024),2)SPACE,MAX(BLOCKS)BLOCKSFROMDBA_DATA_FILESGROUPBYTABLESPACE_NAME)D,(SELECTTABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024*1024),2)FREE_SPACEFROMDBA_FREE_SPACEGROUPBYTABLESPACE_NAME)FWHERED.TABLESPACE_NAME=F.TABLESPACE_NAME(+)UNIONALLSELECTD.TABLESPACE_NAME"表空间名",SPACE"总空间(G)",BLOCKS"总块数",USED_SPACE"使用空间(G)",ROUND(NVL(USED_SPACE,0)/SPACE*100,2)"使用率",NVL(FREE_SPACE,0)"剩余空间(G)"FROM(SELECTTABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024*1024),2)SPACE,MAX(BLOCKS)BLOCKSFROMDBA_TEMP_FILESGROUPBYTABLESPACE_NAME)D,(SELECTTABLESPACE_NAME,ROUND(SUM(BYTES_USED)/(1024*1024*1024),2)USED_SPACE,ROUND(SUM(BYTES_FREE)/(1024*1024*1024),2)FREE_SPACEFROMV$TEMP_SPACE_HEADERGROUPBYTABLESPACE_NAME)FWHERED.TABLESPACE_NAME=F.TABLESPACE_NAME(+);--创建物化视图语法
CREATEMATERIALIZEDVIEWtd_m_depart_plusBUILDIMMEDIATEREFRESHfastONDEMANDSTARTWITHsysdateNEXTSYSDATE+40/(24*60*60)withprimarykeyASSELECT*FROMtd_m_depart_plus@DL_bsqx;
2.--编译数据库失效对象语法编译数据库失效对象语法
setpages500setline100setheadoffspoolcompile1.sqlselect'alter'||decode(object_type,'PACKAGEBODY','PACKAGE',object_type)||''||object_name||'compile;'fromuser_objectswherestatus='INVALID';spooloff@compile1.sql
3.--tuxedo服务无法shutdown的处理
ps-ef|grep-vgrep|greptuxtest|awk'{print$2}'ipcs-q|greptuxtest|awk'{print$1$2}'
4.--物化视图手工刷新物化视图手工刷新
EXECDBMS_MVIEW.REFRESH('TD_M_DEPART','FAST');
5.取数据库序列语法
selectLPAD(substr(SEQ_STA_10086_YWLX.NEXTVAL,-length(SEQ_STA_10086_YWLX.NEXTVAL)),10,'0')fromdual;
6.数据库记录去重的语法
deletefromtb_sta_singlechk_rootawherea.rowid<>(selectmax(rowid)fromtb_sta_singlechk_rootbwherea.area_id=b.area_idanda.country_id=b.country_idanda.level_id=b.level_id);selectcount(*)fromtb_sta_singlechk_rootcommit;rollback;
7.服务器管理命令
HP服务器管理命令:samIBM服务器管理命令:smitty
8.--建立运行比较久SQL的检查结果表建立运行比较久
CREATETABLETL_SYS_SCAN_SQL(USERNAMEVARCHAR2(30BYTE),SIDNUMBER,OPNAMEV
ARCHAR2(64BYTE),PROGRESSVARCHAR2(41BYTE),TIME_REMAININGNUMBER,SQL_TEXTVARCHAR2(1000BYTE))/
通过dbtool来运行下面的SQL采集insertintoTL_SYS_SCAN_SQLSELECTusername,SID,opname,ROUND(sofar*100/totalwork,0)||''ASprogress,time_remaining,sql_textFROMv$session_longops,v$sqlWHEREtime_remaining<>0ANDsql_address=addressANDsql_hash_value=hash_value
9.--ORACLE毫秒时间处理
SELECTTO_CHAR(SYSDATE,'YYYYMMDDHH24:MI:SS'),TO_CHAR(SYSTIMESTAMP,'yyyymmddhh24:mi:SS.FF3')fromdual;
10.查看数据库阻塞锁
select/*+rule*/a.kaddr,a.sid,a.type,a.id1,a.id2,a.lmode,a.request,a.block,b.sidblocking_sidfromv$locka,(select*fromv$lockwhererequest>0andtype<>'MR')bwherea.id1=b.id1(+)anda.id2=b.id2(+)anda.lmode>0anda.type<>'MR'andb.sidisnotnull;
11.--解除表锁解除表锁
select'altersystemkillsession'''||sid||','||serial#||''';'fromv$sessionwheresidin(selectsession_idfromv$locked_objectwhereobject_idin(selectobject_idfromdba_objectswhereowner='IVRDWS'
and
object_name='TL_IVR_TRACK_HIS'));select'altersystemkillsession'''||c.sid||','||c.serial#||''';',object_name,logon_time,machine,a.object_id,session_id,oracle_username,os_user_name,a.process,program,subobject_name,b.created,username,c.osuser,c.status,c.process,terminal,b.last_ddl_time,last_call_et,row_wait_block#fromv$locked_objecta,user_objectsb,v$sessioncwherea.object_id=b.object_idanda.session_id=c.sidandc.status='ACTIVE'orderbyc.sid,logon_time;select'altersystemkillsession'''||sid||','||serial#||''';'fromv$sessionwhereusername='DXTT'SELECT*fromv$sessionwhereusername='DXTT';
12.--怎样释放存储过程被锁怎样释放存储过程被锁
SELECT*FROMV$LOCK_ACTIVITYselect*fromdictwheretable_namelike'%ACCE%'SELECT*FROMV$ACCESSWHEREOBJECT='TL_IVR_TRACK_HIS'SELECT*FROMV$SESSIONWHERESID='551';altersystemkillsession'551,13618';selecta.sid,a.serial#,b.spid,a.username,a.program,a.machine,a.terminal,a.sql_hash_valuehash_valuefromv$sessiona,v$processbwherea.paddr=b.addranda.paddrin('C0000003FFD9E910')kill-9b.spid
13.--解除删除表列锁语法解除删除表列
SQL>altertabletl_ivr_track_hisdropunusedcolumnscheckpoint1000;
ERRORatline1:ORA-12986:columnsinpartiallydroppedstate.SubmitALTERTABLEDROPCOLUMNSCONTINUESQL>select*fromDBA_PARTIAL_DROP_TABS;OWNERTABLE_NAME-----------------------------------------------------------IVRDWSTL_IVR_TRACK_HISSQL>ALTERTABLEIVRDWS.TL_IVR_TRACK_HISDROPCOLUMNSCONTINUE;Tablealtered.
14.怎样查看后台进程在运行什么SQL语法
selectp.spid,s.SQL_HASH_VALUE,s.program,p.addr,s.sid,s.serial#,s.schemaname,s.SQL_ADDRESS,s.USERNAME,s.MACHINEfromv$processp,v$sessionswherep.addr=s.paddrandp.spid=19135;selectsql_textfromv$sqltextwherehash_value=3957121598orderbypiece;
15.--查询S
QL缓冲区查询
selectsql_textfromv$sqlarea;--修改数据库用户口令:alteruserydxtidentifiedbyydxt;--修改数据库用户缺省表空间:alterusercbbildefaulttablespacedata_fare;$su-oracleconnect/assysdba;grantexecuteonutl_filetoccbs;
16.--建立用来存放文本的目录建立用来存放文本的目录
createorreplacedirectoryTOTXTas'/disk2/app/ora9i/xxm';grantreadondirectoryTOTXTtopublic;GRANTWRITEONDIRECTORYTOTXTTOpublic;
17.--查看系统用户视图查看系统用户视图
selectowner,view_namefromdba_viewswhereview_namelike'USER%';
18.--查看存储过程什么时间创建查看存储过程什么时间创建查看存储过程什
select*fromuser_objectswhereOBJECT_NAME='SP_REP_1861_AREA_AUTO';
19.--查看存储过程的内容查看存储过程的内容
selecttextfromUSER_SOURCEwherename='SP_REP_1861_AREA_AUTO';
20.--查看数据库用户连接数查看数据库用户连接数
selects.username,count(*)fromv$processp,v$sessionswherep.addr=s.paddrgroupbys.username;
21.强制走索引强制走索引:
select/*+INDEX(b)*/num,acc_date,max(custom_name)custom_name,nvl(sum(decode(b.bcash_id,'B1',b.cash_val))/100,0)fb1,nvl(sum(decode(b.bcash_id,'B2',b.cash_val))/100,0)fb2fromcbtd_acc_infoa,cbtd_acc_btotalbwherea.numin(:ls_tel)anda.acc_id=b.acc_idanda.acc_date<:v_accdate
anda.pay_flagin(:ls_flag)groupbynum,acc_date;
22.--查看DBLINK语法查看
selecttable_namefromdictwheretable_namelike'%LINK%';
23.oracle10g启动及关闭命令
cd/u01/app/oracle/binexportORACLE_BASE=/u01/app/oracleexportORACLE_SID=ORCLexportORACLE_HOME=$ORACLE_BASEexportPATH=$PATH:$ORACLE_HOME/bin#启动监听lsnrctlstart#关闭监听lsnrctlstop#启动数据库sqlplussql>/assysdbasql>startup#停止数据库sql>shutdownimmediate#启动dbconsole服务emctlstartdbconsole#停止dbconsole服务emctlstopdbconsole#启动或停止iSQL*Plus：isqlplusctlstart|stop
24.启动lsnrctl进程
a.进入oracle用户b.$lsnrctlstatc.$lsnrctlstopd.$lsnrctlstart$su-ora(查看lsnrctl进程状态)(断开lsnrctl进程)(启动lsnrctl进程)
25.创建序列语法创建序列语法:
dropsequencecbseq_query_log;createsequencecbseq_query_logINCREMENTBY1STARTWITH1000MAXVALUE999999999MINVALUE1000CYCLEORDER;selectcbseq_query_log.nextvalfromdual;
26.查看ORACLE数据库参数:数据库参数
%svrmgrlSVRMGR>connectinternalSVRMGR>showparameters
27.查看SQL执行计划
a)、setautotraconselectcontract_id,sum(total_cash)tcashfromcbtd_acc_infowherepay_flagin('0','5','7')andnvl(acc_stat,'0')<>'2'groupbycontract_id;b)、SQL>altersessionsetsql_trace=true;hnyd735yw01$cd/opt1/oracle/admin/czmobile/udump$ls-ltr
$tkprofczmobile_ora_2214.trcaa.sqlSQL>altersessionsetsql_trace=false;$moreaa.sqlC)、explainplanforse
lectcontract_id,sum(total_cash)tcashfromcbtd_acc_infowherepay_flagin('0','5','7')andnvl(acc_stat,'0')<>'2'groupbycontract_id;selectobject_name,substr(operation,1,20),substr(OPTIONS,1,22),id,parent_id,Cost,CARDINALITY,Bytesfromplan_table;selectsid,status,machine,substr(username,1,20)fromv$sessionwheremachinelike'%CESH%';selectSEQ#,substr(EVENT,1,40),substr(P1TEXT,1,20)fromv$session_waitwheresidin(162);
28.OracleRound函数四舍五入)函数(四舍五入四舍五入
描述:传回一个数值，该数值是按照指定的小数位元数进行四舍五入运算的结果。SELECTROUND(number,[decimal_places])FROMDUAL参数:number:欲处理之数值decimal_places:四舍五入,小数取几位(预设为0)Sample:selectround(123.456,0)fromdual;selectround(123.456,1)fromdual;selectround(123.456,2)fromdual;selectround(123.456,3)fromdual;回传123回传123.5回传123.46回传123.456
selectround(-123.456,2)fromdual;
回传-123.46
29.OracleTRUNC函数四舍五入函数(四舍五入四舍五入)
TRUNC（number[,decimals]）其中：number待做截取处理的数值decimals指明需保留小数点后面的位数。可选项，忽略它则截去所有的小数部分
下面是该函数的使用情况：TRUNC（89.985，2）=89.98TRUNC（89.985）=89TRUNC（89.985，-1）=80注意：第二个参数可以为负数，表示为小数点左边指定位数后面的部分截去，即均以0记。
30.如何查看数据库的版本？如何查看数据库的版本？
select*fromv$version;
