1.查看当前锁信息中HWenqueue等待。selectcount(*)fromv$lockwheretype='HW';
--sessionlogicalreads"逻辑读"--physicalreads,"物理读"--redosize"日志量"--userrollbacks"用户ROLLBACK"--usercommits"用户COMMIT"
SELECTSNAP_ID||'-->'||to_char(SNAP_ID-1)"SNAP_SCOPE",to_char(TIME,'mm-ddhh24:mi')time,stat_name"LoadProfile",round((VALUE-LAG(VALUE)OVER(PARTITIONBYstat_nameORDERBYstat_name,time))/(SECONDS-LAG(SECONDS)OVER(PARTITIONBYstat_nameORDERBYstat_name,time)),2)"PerSecond"FROM(selectto_number(b.snap_id)"SNAP_ID",b.end_interval_time"TIME",stat_name,a.value,EXTRACT(HOURFROMb.end_interval_time)*3600+EXTRACT(MINUTEFROMb.end_interval_time)*60+EXTRACT(SECONDFROMb.end_interval_time)"SECONDS"fromDBA_HIST_SYSSTATa,DBA_HIST_SNAPSHOTbwherea.snap_id=b.snap_idanda.instance_number=SYS_CONTEXT('USERENV','INSTANCE')andb.instance_number=SYS_CONTEXT('USERENV','INSTANCE')andstat_namein('sessionlogicalreads')andb.end_interval_timebetweento_date('2009-03-0100:00:00','yyyy-MM-ddHH24:mi:ss')andto_date('2009-03-0123:00:00','yyyy-MM-ddHH24:mi:ss')orderbystat_name,time)ORDERBY"TIME","LoadProfile";
2.查看当前HWenqueue所对应的sql。selectse.username,sq.sql_textfromv$lockl,v$sessionse,v$sqltextsqwherel.sid=se.sidandse.sql_hash_value=sq.hash_valueandl.type='HW';
selectcount(*)fromv$lockl,v$sessionswherel.type='HW'andl.sid=s.sidands.status='ACTIVE';
3.查看每个machine下的连接数selectmachine,count(*)fromv$sessionwherestatus='ACTIVE'groupbymachine;
--修改数据库连接数altersystemsetprocesses=300scope=spfile;
shutdownimmediate;startup;
--杀除oracle实例下的远程连接，用于关闭数据库ps-ef|grep'test(LOCAL=NO)'|grep-vgrep|awk'{print$2}'|xargs-ikill{}
4.显示当前undosegment信息selectnamefromv$rollstata,v$rollnamebwherea.usn=b.usn;
5.显示undosegment头信息。selectheader_file,header_block,blocksfromdba_segmentswheresegment_name='_SYSSMU10$';
6.查看数据库中的temp表空间，sort信息。
7.查看数据库中的锁信息。
8.查看当前持有锁的sessionselectt2.username,t2.sid,t2.serial#,t2.logon_timefromv$locked_objectt1,v$sessiont2wheret1.session_id=t2.sidorderbyt2.logon_time;
9.检查加锁属于哪个表。selectsql_textfromv$sessiona,v$sqltext_with_newlinesbwhereDECODE(a.sql_hash_value,0,prev_hash_value,sql_hash_value)=b.hash_valueanda.sid=&sidorderbypiece;
10.杀指定sessionaltersystemkillsession'111,22222';--从操作系统杀进程select'kill-9'||a.spidfromv$processa,v$sessionbwherea.addr=b.paddrandtype='USER'andb.sql_id='19gwmjusr9mnh';
11.dropcolumn删除列altertablequot_receive_recorddropcolumnreceive_status;
12.addcolumn增加列altertablequot_receive_recordadd(statusVARCHAR2(10)DEFAULT'enable');
13.modifycolumn修改列，修改列时，只需要标识出需要修改的模式ALTERTABLEquot_item_templatemodify(titleVARCHAR2(50)NOTNULL);
14.创建sequence，序列CREATESEQUENCEseq_quot_reject_distribtSTARTWITH1;
CREATEINDEXquot_reject_distribt_pkONquot_reject_distribt(id)TABLESPACEmytbs;ALTERTABLEquot_reject_distribtADDCONSTRAINTquot_reject_distribt_pkPRIMARYKEY(id)USINGINDEXquot_reject_distribt_pk;
--添加unique约束CREATEINDEXquot_reject_distribt_ukONquot_reject_distribt(id)TABLESPACEmytbs;ALTERTABLEquot_reject_distribtADDCONSTRAINTquot_reject_distribt_ukunique(id)USINGINDEXquot_reject_distribt_uk;
ALTERTABLEquot_reject_distribtADDCONSTRAINTquot_reject_distribt_ukunique(id)USINGINDEXquot_reject_distribt_ukenablenovalidate;--不检查已有数据项
--删除约束，保留索引altertablebuyer_infodropconstraintBUYER_INFO_MID_UKkeepindex;
--禁用，启用约束altertablebkeep3disableconstraintbkeep3_pk;altertablebkeep3modifyconstraintbkeep3_pkdisable;
altertablebkeep3enableconstraintbkeep3_pk;altertablebkeep3modifyconstraintbkeep3_pkenable;
17.查看日志组selectgroup#,memberfromv$logfile;
18.查看日志组状态selectgroup#,statusfromv$log;
19.给定rowid，查询其所属的file#，block#，rownum#selectdbms_rowid.rowid_object(m.rowid)"OBJECT",dbms_rowid.rowid_relative_fno(m.rowid)"FILE",dbms_rowid.rowid_block_number(m.rowid)"BLOCK",dbms_rowid.rowid_row_number(m.rowid)"ROW"frommembermwhererownum=1;
--给定rowid，查询所对应的数据项select*fromtest.t1whererowid='AAAXUZAH4AABa6pABE';
--通过起至rowid来取数据，相当于用于自定义并行SELECT/*+rowid(a)*/Count(*)FROMt1aWHEREROWIDBETWEENCharToRowid('AAACPSAAgAAAFcJAAB')ANDCharToRowid('AAACPSAAgAAAFeJCcP');19.1并行处理--查询当前并行进程selectb.QCSID,count(*)fromv$sessiona,v$px_sessionbwherea.sid=b.sidgroupbyqcsid;--查询并行进程的等待selectsid,sql_text,username,machine,audsid,a.hash_valuefromv$sqla,v$sessionbwherea.sql_id=b.sql_idandb.sid=&sid;
selecta.sid,b.QCSID,a.audsid,a.eventfromv$sessiona,v$px_sessionbwherea.sid=b.sid;
20。dump数据文件altersystemdumpdatafile5blockmin50blockmax55;--file#canbefindinv$datafile
--dump数据文件（从rowid中dump）altersystemdumpdatafiledbms_rowid.rowid_relative_fno(p_rowid)blockdbms_rowid.rowid_block_number(p_rowid);
--dumplibrarycachealtersystemsetevents'immediatetracenamelibrary_cachelevel4';
22.根据表名获得tablespace名selecttable_name,tablespace_name,ownerfromdba_tableswheretable_name=upper('t1');
23.查询有primarykey，uniquekey，foreignkey约束，同时没有lob字段的表--有主键或唯一键，外键，且不含clob的selecta.table_namefrom(selectdistincttable_namefromdba_constraintswhereconstraint_typein('U','P')andtable_namenotin('t1')andowner='test')a,(selectdistincttable_namefromdba_lobswhereowner='test')bwherea.table_name=b.table_name(+)andb.table_nameisnullorderby1;
--查询外键selecta.table_name,a.column_name,b.table_name,b.column_namefrom(selecta.constraint_name,b.table_name,b.column_name,a.r_constraint_namefromuser_constraintsa,user_cons_columnsbWHEREa.constraint_type='R'anda.constraint_name=b.constraint_name)a,(selectdistincta.r_constraint_name,b.table_name,b.column_namefromuser_constraintsa,user_cons_columnsbWHEREa.constraint_type='R'anda.r_constraint_name=b.constraint_name)bwherea.r_constraint_name=b.r_constraint_name
24.修改数据库系统参数方案一：altersystemsetprocesses=1000scope=spfile;
--createpfilefromspfile;
注意：在shutdownimmediate之前一定要kill掉所有的LOCAL=NO进程shutdownimmediate;startup;
方案二：直接修改$ORACLE_HOME/dbs/spfile$SID.ora文件
select*fromv$sgastatwherepool='sharedpool'andnamelike'%process%';
25.查看tablespace，以及tablespace下的datafilesselectts.name,df.namefromv$tablespacets,v$datafiledfwherets.ts#=df.ts#orderbyts.name;
selectts.name,count(*)fromv$tablespacets,v$datafiledfwherets.ts#=df.ts#groupbyts.nameorderbyts.name;
--查询数据文件的使用情况SELECTSUBSTR(max(A.TABLESPACE_NAME),1,16)"Tablespace",A.FILE_ID"FileID",substr(max(A.file_name),1,43)"Datafile",substr(max(A.status),1,10)"Status",(MAX(A.BYTES)-nvl(sum(B.BYTES),0))/1024/1024"USEDSIZE(Mb)",MAX(A.BLOCKS)-nvl(sum(B.BLOCKS),0)"USEDBLOCKS",TO_CHAR((MAX(A.BYTES)-nvl(sum(B.BYTES),0))*100/MAX(A.BYTES),'999.99')||'%'"USEDUSAGE",nvl(sum(B.BYTES),0)/1024/1024"FREESIZE(Mb)",nvl(SUM(B.BLOCKS),0)"FREEBLOCKS",TO_CHAR(nvl(SUM(B.BYTES),0)*100/MAX(A.BYTES),'999.99')||'%'"FREEUSAGE",MAX(A.bytes)/1024/1024"TOTALSIZE(Mb)",MAX(A.blocks)"TOTALBLOCKS"fromdba_data_filesA,DBA_FREE_SPACEBWHEREA.FILE_ID=B.FILE_ID(+)groupbya.file_idorderby7;
--resize数据文件
alterdatabasedatafile'/data/oracle10g/oradata/test/system01.dbf'resize2048M;
26.查看指定数据文件内是否有extens在dba_extents中select*fromdba_extentswherefile_idin(103,104);
27.使数据文件offlinealterdatafile'/opt/oracle/product/.../.dbf'offline;
28.数据库恢复sqlrecoverdatabaseuntilcancelrecoverdatabaseuntiltime'2004-03-21:22:59:04'recoverdatabaseuntilchange123456
recoverdatafile'filename'untilcancelrecoverdatafile'filename'untiltime'2004-03-21:22:59:04'recoverdatafile'filename'untilchange123456
recovertablespacets_nameuntilcancelrecovertablespacets_nameuntiltime'2004-03-21:22:59:04'recovertablespacets_nameuntilchange123456
recoverdatabaseusingbackupcontrolfile
29.
30.--在线添加索引，更新统计信息createindext1_c1_indont1(c1)tablespacets1online;
analyzetablet1estimatestatisticsfortableforallindexesforallindexedcolumns;
execdbms_stats.gather_table_stats(ownname=>'owner',tabname=>'table_name',estimate_percent=>null,method_opt=>'forallindexedcolumns',cascade=>true);
execDBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'test',TABNAME=>'t1',ESTIMATE_PERCENT=>10,method_opt=>'FORALLINDEXEDCOLUMNSSIZE1',no_invalidate=>FALSE,CASCADE=>TRUE,degree=>1);--也可以让数据库自动选择samplesize--estimate_percent=>dbms_stats.AUTO_SAMPLE_SIZE
--设置表的统计信息DBMS_STATS.SET_TABLE_STATS('test','t1',numrows=>10000000,numblks=>50000,avgrlen=>50);DBMS_STATS.SET_COLUMN_STATS('test','t1','c1',distcnt=>10000000,density=>0.0000001,avgclen=>20);DBMS_STATS.SET_INDEX_STATS('test','t1_c1_ind',numrows=>10000000,numlblks=>10000,numdist=>10000000,avglblk=>1,avgdblk=>1,clstfct=>50000,indlevel=>3);
begindbms_stats.set_column_stats(ownname=>'test',tabname=>'t1',colname=>'c1',no_invalidate=>FALSE,distcnt=>1000000,density=>0.000001);end;/--删除统计信息analyzetablecredit_gnt_accountdeletestatistics;execdbms_stats.delete_table_stats(ownname=>'owner',tabname=>'table_name');
--查询统计信息是否加锁selectSTATTYPE_LOCKEDfromdba_tab_statisticswheretable_name='t1'andSTATTYPE_LOCKEDisnotnull;selectSTATTYPE_LOCKEDfromdba_tab_statisticswheretable_name='t1'andSTATTYPE_LOCKEDisnotnull;--解锁指定表上的统计信息execdbms_stats.unlock_table_stats('test','t1');
31.查看sql的执行计划explainplanfor
select*fromtable(dbms_xplan.display);--此处需要注意的是，explainplanfor语句的执行会产生insert动作，而此动作是需要用户手动commit，或者是rollback的--不然就会导致长事务执行超时。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。--EXPLAINPLANisaDMLstatementandhenceyouneedtoCOMMITthetransactionexplicitlyasOracledoesnot--implicitlycommitthechangesmadebyDMLstatements.
--通过setautotrace的方式SQL>setautotracetraceonlyexpstat;
SQL>selectuserfromdual;
32.分页查询（两表join）selectcount(*)from(selectb.*from(selecta.*,ROWNUMasrownfrom(select*fromtest.t1wherec1='aaaa'orderbyid)awhereROWNUM<=200)bwhereb.rown>180)c,test.t2dwherec.id=d.uid;
--先对表一分页，然后将分页结果与表二进行join，如此大大减少了两表join量。--分页语句必须使用orderby，同时，orderby的字段必须是unique值
--快速分页，访问全表数据，可用于全表数据dump，迁移等--用id来分页，保证每次分页，都访问索引，不至于访问全表SELECT*FROMtest.t1WHEREidbetween111991and121991andc_time 33.--查看指定表的索引信息，constraint信息selectconstraint_name,constraint_typefromuser_constraintswheretable_name=upper('t1');selectindex_namefromuser_indexeswheretable_name=upper('t1');--查看索引的列信息selectindex_name,column_namefromuser_ind_columnswheretable_name=upper('t1'); 34.--删除指定索引，指定表的约束altertablet1dropconstraintt1_c1_uk;dropindext1_c1_uk; 35.--统计多列的distinct值--先groupby，然后统计group的个数selectsum(count(*))asdistinct_numfromquot_receive_recordgroupbyquotation_id,distributor_id,recipient_id; --将多列连接，然后通过distinct算子计算selectcount(distinctquotation_id||distributor_id||recipient_id)distinct_numfromquot_receive_record; 36.--oracle连接符||selectst_name||'的学号是'||st_nofromstudent; 37.--查看被锁的表信息SELECT/*+rule*/a.sid,b.owner,object_name,object_typeFROMv$locka,all_objectsbWHERETYPE='TM'anda.id1=b.object_id; 38.--查看失效对象，并且重新编译alterPACKAGEBODYb1compile;alterPACKAGEp1compile;alterPROCEDUREp1compile;alterVIEWv1compile;alterTRIGGERt1compile; 39.--离线查看oracleerror错误oerrora01555oerrtns12541oerreman202242oerrexp00091oerrNID131 40.--新建keepcache，并将指定表移动到keeppool中altersystemsetdb_keep_cache_size=3M;altertabledualcachestorage(buffer_poolkeep); 41.--创建同义词createorreplacepublicsynonymdualformydual; 42. 43.--收集统计信息语法：AnalyzeDbms_utility.analyze_schemadbms_stats.gather_***_stats ANALYZEtabletableName{compute|estimate|delete)statisticsoptionsANALYZEtableindexName{compute|estimate|delete)statisticsoptions 实例：ANALYZEtablescottcomputestatistics; analyzetable***estimatestatisticsfortableforallindexesforallindexedcolumns; ANALYZEtablescottestimatestatisticssample25percent;ANALYZEtablescottestimatestatisticssample1000rows;analyzeindexsc_idxvalidatestructure; execDBMS_UTILITY.ANALYZE_SCHEMA('SCOTT','COMPUTE');execDBMS_UTILITY.ANALYZE_SCHEMA('SCOTT','ESTIMATE',estimate_rows=>1000);execDBMS_UTILITY.ANALYZE_SCHEMA('SCOTT','ESTIMATE',estimate_percent=>25);execDBMS_UTILITY.ANALYZE_SCHEMA('SCOTT','DELETE'); EXECDBMS_STATS.gather_table_stats('SCOTT','EMPLOYEES');EXECDBMS_STATS.gather_index_stats('SCOTT','EMPLOYEES_PK');execDBMS_STATS.DELETE_SCHEMA_STATS('SCOTT'); 44.--获得指定表的averagerowlengthselectavg_row_lenfromuser_tableswheretable_name='T1'; 45.--定位当前数据库中的blockedsessions信息selectl1.sid,'ISBLOCKING',l2.sidfromv$lockl1,v$lockl2wherel1.block=1andl2.request>0andl1.id1=l2.id1andl1.id2=l2.id2/ 不带条件，返回所有隐含参数SELECTNAME,value,descriptionFROM(--GV$SYSTEM_PARAMETERSELECTx.inst_idasinstance,x.indx+1,ksppinmasNAME,ksppity,ksppstvlasvalue,ksppstdfasisdefault,decode(bitand(ksppiflg/256,1),1,'TRUE','FALSE')asISEM,decode(bitand(ksppiflg/65536,3),1,'IMMEDIATE',2,'DEFERRED','FALSE')asISYM,decode(bitand(ksppstvf,7),1,'MODIFIED','FALSE')asIMOD,decode(bitand(ksppstvf,2),2,'TRUE','FALSE')asIADJ,ksppdescasDESCRIPTIONFROMx$ksppix,x$ksppsvyWHEREx.indx=y.indxANDsubstr(ksppinm,1,1)='_'ANDx.inst_id=USERENV('Instance'))ORDERBYNAME; 指定隐含参数关键字，返回匹配的隐含参数setlinesize132columnnameformata30columnvalueformata25selectx.ksppinmname,y.ksppstvlvalue,y.ksppstdfisdefault,decode(bitand(y.ksppstvf,7),1,'MODIFIED',4,'SYSTEM_MOD','FALSE')ismod,decode(bitand(y.ksppstvf,2),2,'TRUE','FALSE')isadjfromsys.x$ksppix,sys.x$ksppcvywherex.inst_id=userenv('Instance')andy.inst_id=userenv('Instance')andx.indx=y.indxandx.ksppinmlike'%_&par%'orderbytranslate(x.ksppinm,'_','')/ 47.--查看CR块信息selectfile#,block#,status,objdfromv$bhwhereobjd=13389andstatus='cr'orderbyblock#; 查询索引所含的object_id号selectobject_id,data_object_id,object_typefromdba_objectswhereobject_name=upper('t1'); 48.--建立databaselink，查询dblinkcreatedatabaselinkl1connecttotestidentifiedbyxxxxxxxxxxxxxusing'xxx'; CREATEPUBLICDATABASELINKl2CONNECTTOtestIDENTIFIEDBY"xxxxxxx"USING'(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1234)))(CONNECT_DATA=(SID=tsid)))'/ selectowner,object_namefromdba_objectswhereobject_type='DATABASELINK'; createviewview_test_rawasselectutl_raw.cast_to_raw(name)asnamefromtest_raw; 其实RAW和VARCHAR是类似的,只是存储在RAW里的是二进制值,在任何时候会做自动的字符集转换,这是RAW和VARCHAR的同,RAW只是一种外部类型,其内部存储是VARRAW VARCHAR的Oracle内部定义是:struct{ub2len;chararr[n]} VARRAW的ORACLE内部定义是:struct{ub2len;unsignedchararr[n]} SELECTdecode(sorted,1,'CREATEVIEWV_'||table_name||'ASSELECT',2,chr(9)||decode(data_type,'VARCHAR2','utl_raw.cast_to_raw('||column_name||')AS'||column_name,'CHAR','utl_raw.cast_to_raw('||column_name||')AS'||column_name,column_name)||decode(rid,1,NULL,','),3,'FROMmoney.'||table_name||';'||chr(10))FROM(SELECTtc.table_name,tc.column_name,tc.data_type,2ASsorted,row_number()over(PARTITIONBYtable_nameORDERBYcolumn_idDESC)ASridFROMdba_tab_columnstcWHEREowner='test'UNIONALLSELECTtable_name,'','',1,0FROMdba_tablesWHEREowner='test'UNIONALLSELECTtable_name,'','',3,0FROMdba_tablesWHEREowner='test')ORDERBYtable_name,sorted,ridDESC; 49.--查询当前session所对应的tracefilename--启用trace，关闭，查看trace SETLINESIZE100COLUMNtrace_fileFORMATA60 SELECTs.sid,s.serial#,pa.value||'/'||LOWER(SYS_CONTEXT('userenv','instance_name'))||'_ora_'||p.spid||'.trc'AStrace_fileFROMv$sessions,v$processp,v$parameterpaWHEREpa.name='user_dump_dest'ANDs.paddr=p.addrANDs.audsid=SYS_CONTEXT('USERENV','SESSIONID'); altersessionsetevents'10046tracenamecontextforever,level12';altersessionsetevents'10046tracenamecontextoff'; tkprofdev1_ora_367660.trctranslated.txtexplain=test/testtable=sys.plan_tablesys=nowaits=yes 50.--定位job，查看job内容selectjob,whatfromdba_jobs;--定位jobselecttextfromdba_sourcewherename=upper('t1');--查看job内容 51.--获得指定表的ddlselectdbms_metadata.get_ddl('TABLE','t1')fromdual;selectdbms_metadata.get_ddl('TABLE','t1','test')fromdual; 52.--查看数据库监听端口信息tnspingservice_name--得到service所对应的sidlsnrctlstatus--lsnrctlservice-- 53.--重命名表名，sequence名，表中字段名altertableold_namerenametonew_name;renameold_seqtonew_seq;altertable***renamecolumn***to***; 54.--获取表注释信息（comments）selecttable_name,commentsfromdba_tab_commentswheretable_name=&1unionallselecttable_name,column_name,commentsfromdba_col_commentswheretable_name=&1; 55.--Linux远程copy命令scpscpfilenameuser@ip:/target/ 56.--定义一个每天晚上零点30运行的job printjob1; --在完成submit之后，必须commit;commit; --方案二，variable变量variablejob1number;begindbms_job.submit(job=>:job1,what=>'pro_hdc_test_0708;',next_date=>trunc(sysdate+1)+1/48,interval=>'trunc(sysdate+1)+1/48');end;/ print:job1; commit; --手动运行jobexecdbms_job.run(82);--删除jobexecdbms_job.remove(4); 57.--dbms_output.putline过程，调整buffer_size setserveroutputonsize100000dbms_output.enable(999999); --指定timestamp，闪回selectcount(*)fromflashback_query_testasoftimestampto_timestamp('2004-03-2913:34:12','yyyy-mm-ddhh24:mi:ss'); --指定scn，闪回selectcount(*)fromflashback_query_testasofscn722452; --flashbackversionqueryselectversions_startscn,versions_starttime,versions_endscn,versions_endtime,versions_xid,versions_operation,descriptionfromflashback_version_query_testversionsbetweentimestamp***and***; --flashbacktableflashbacktableflashback_table_testtoscn715315;FLASHBACKTABLEflashback_table_testTOTIMESTAMPTO_TIMESTAMP('2004-03-0310:00:00','YYYY-MM-DDHH:MI:SS'); --flashbackdropshowrecyclebin;flashbacktableflash_back_testtobeforedrop;select*from"BIN$TDGqmJZKR8u+Hrc6PGD8kw==$0";--recyclebin --查询分区信息selectPARTITION_NAME,blocks/1024/1024MB,high_value,high_value_length,partition_positionfromdba_tab_partitionswheretable_name='t1';--(是否存在分区) --指定分区查询selectmax(event_gmt_occur)fromtest.t1partition(t1_200810); selectTABLE_NAME,tablespace_name,PARTITION_NAME,HIGH_VALUE,PARTITION_POSITION,SUBPARTITION_COUNTfromuseR_TAB_PARTITIONSwheretable_name=upper('t1'); selectcount(*)fromctu.t1partition(t1_201010);--(指定分区查询)--查询分区类型，分区键值SELECTp.table_name,decode(p.partitioning_key_count,1,'mainpar'),p.partitioning_type,p.column_name,decode(nvl(q.subpartitioning_key_count,0),0,'nonsub',1,'sub')sub_par,q.subpartitioning_type,q.column_nameFROM(SELECTa.table_name,a.partitioning_type,b.column_name,a.partitioning_key_countFROMall_part_tablesa,all_part_key_columnsbWHEREa.table_name=b.NAMEANDb.object_type='TABLE')p,(SELECTa.table_name,a.subpartitioning_type,b.column_name,a.subpartitioning_key_countFROMall_part_tablesa,all_subpart_key_columnsbWHEREa.table_name=b.NAMEANDa.subpartitioning_key_count<>0ANDb.object_type='TABLE')qWHEREp.table_name=q.table_name(+)ORDERBY5,4,1; --添加分区，子分区ALTERTABLEt1ADDPARTITIONt1_201101VALUESLESSTHAN(TO_DATE('2011-04-0100:00:00','YYYY-MM-DDHH24:MI:SS'))tablespacets1; ALTERTABLESALESMODIFYPARTITIONP3ADDSUBPARTITIONP3SUB1VALUES('COMPLETE');--交换分区，重命名表（exchangepartition，renametable）--exchangepartitionexecuteimmediate('altertablet1exchangepartitionp_allwithtablet1_tmpincludingindexeswithoutvalidation'); --renametablealtertableold_namerenametonew_name; --删除分区ALTERtabletrain_partDROPpartitionacct_p1; --失效分区索引select'alterindex'||t.index_name||'rebuildpartition'||t.partition_namefromdba_ind_partitionstwheret.index_name='t1_ind'andt.status='unusable' 61.--动态绑定变量语句--动态游标的绑定变量declaremsqlvarchar2(500);mcurnumber;mstatnumber;jgvarchar2(4000);cgnumber;beginmcur:=dbms_sql.open_cursor;--注意，此语句千万不能放在循环中msql:='selectmyidfromt4wheremyid=:x';foriin1..5000loopdbms_sql.parse(mcur,'selectmyidfromt4wheremyid=:x',dbms_sql.native);dbms_sql.bind_variable(mcur,':x',i);dbms_sql.define_column(mcur,1,jg,4000);mstat:=dbms_sql.execute(mcur);cg:=dbms_sql.fetch_rows(mcur);dbms_sql.column_value(mcur,1,jg);dbms_output.put_line('查询结果:'||jg);endloop;dbms_sql.close_cursor(mcur);end;/ 62.查看指定对象上的依赖关系selectcount(*)fromdba_dependencieswhereREFERENCED_NAME='t1';selectowner,name,typefromdba_dependencieswhereREFERENCED_NAME='&1'; 63.oracle字符集问题--深入理解（操作系统字符集，客户端字符集，oracle服务器字符集）操作系统字符集：显示字符集，以何种编码显示数据客户端字符集：转换字符集，数据存入服务器，是否需要字符集转换oracle服务器字符集：存储字符集，数据在oracle中，是以何种字符集存储的（例外：如果客户端欺骗了服务器，那么就会违反这一条） --消除乱码1.客户端字符集，必须要与操作系统字符集一致。（保证显示的字符集，就是转换后的字符集）2.服务器字符集，必须是客户端字符集的超集。（保证转换前的字符集，能被完全的转换为转换后的字符集）3.例外：将客户端字符集与服务器字符集设置为一致。存储与读取都不需要进行字符集转换。所有的数据，都按照操作系统字符集存入数据库服务器。此时，仅仅需要保证用同样的操作系统字符集读取，就能正确显示。（缺陷在于，服务器中存储的数据，并不是以服务器自身字符集存储，此时如果操作系统字符集设置不对，或是客户端字符集与服务器不一致，都不能保证正确的读取） ---可以参考"D:\mydocument\日常工作脚本\搞懂oracle字符集.pdf" NLS_LANGUAGE,NLS_TERRITORY,NLS_CHARACTERSET：语言_地区.字符集AMERICAN_AMERICA.ZHS16GBK --nls_database_parameters,nls_instance_parametersselectuserenv('language')fromdual; 64.定位oracle中的热点块（hotblock）--定位热点块所属的segment 65.定位dbfilesequentialread产生的segmentselectb.sid,nvl(substr(a.object_name,1,30),'P1='||b.p1||'P2='||b.p2||'P3='||b.p3)object_name,a.subobject_name,a.object_typefromdba_objectsa,v$session_waitb,x$bhcwherec.obj=a.object_id(+)andb.p1=c.file#(+)andb.p2=c.dbablk(+)andb.event='dbfilesequentialread' union selectb.sid,nvl(substr(a.object_name,1,30),'P1='||b.p1||'P2='||b.p2||'P3='||b.p3)object_name,a.subobject_name,a.object_typefromdba_objectsa,v$session_waitb,x$bhcwherec.obj=a.object_id(+)andb.p1=c.file#(+)andb.p2=c.dbablk(+)andb.event='dbfilesequentialread'orderby1; 66.查询数据库中某一文件对象被cache的数据块数（v$bh，x$bh）selectsum(blocks)fromdba_segmentswheresegment_name='&2';selectcount(*)fromv$bhwhereobjd=(selectdata_object_idfromdba_objectswhereowner='test'andobject_name='&2')andstatus!='free'; 67.Linux下批量删除数据cd/data/oradatals-Frt|grepctrdm|wc-lls-Frt|grepctrdm|head-n400|xargsrm ls-Frt|head-n15|xargsrm 68.获得一个对象的lastddlselectobject_name,object_type,to_char(created,'yyyy-mm-ddhh24:mi:ss')created,to_char(last_ddl_time,'yyyy-mm-ddhh24:mi:ss')last_ddlfromuser_objectswhereobject_name='&1'; 69.物化视图（materializedviews）--BeforeIcameonthesceneasaDBA/Consultant,thepreviousteamhadimplementedreplicationofatable/subsetbyusingmanual/--scriptedmethodstocopythetable/data.Sincethetablehasbeencopiedlastnightandtherehavebeennochangesatthesourcesince--then,IjustCREATEMATERIALIZEDVIEW..PREBUILTandthenletOraclerefreshthetable"automagically"forme!creatematerializedviewlogonhdc_test_0818;--创建物化视图日志createtablehdc_mv_test_0818asselect*fromhdc_test_0818;--创建表结构creatematerializedviewhdc_mv_test_0818onprebuilttablerefreshfastasselect*fromhdc_test_0818;--创建依赖于现有表的物化视图--全量刷新物化视图begindbms_mview.refresh(TAB=>'HDC_MV_TEST_0818',METHOD=>'COMPLETE');end;/execdbms_mview.refresh('HDC_MV_TEST_0818','C');--增量刷新物化视图execdbms_mview.refresh('HDC_MV_TEST_0818','F'); selecttextfromdba_sourcewherename='triger1';--限制delete操作createorreplacetriggertri_del_3beforedeleteont1beginraise_application_error(-20001,'cannotdeletedataont1');end;/ 72.like操作中转译特殊字符（escape）A.c1like'%'||REPLACE(REPLACE(REPLACE(:1,'/','//'),'%','/%'),'_','/_')||'%'escape'/' 73.查看sql的执行频率，给定sql_idselecta.snap_id||','||to_char(t.snap_time,'yyyy-mm-ddhh24:mi:ss')||','||executions_total||','||buffer_gets_total||','||EXECUTIONS_DELTA||','||buffer_Gets_delta||','||trunc(buffer_Gets_delta/decode(EXECUTIONS_DELTA,0,1))asresultfromDBA_HIST_SQLSTATa,(selectsnap_id,END_INTERVAL_TIMEassnap_timefromdba_hist_snapshot)twheresql_id='&1'anda.snap_id=t.snap_idorderbya.snap_id; 74.查询数据库中的隐含参数(x$ksppi,x$ksppcv)selectx.ksppinm,y.ksppstvl,x.ksppdescfromx$ksppix,x$ksppcvywherex.indx=y.indxandx.ksppinmlike'\_%'escape'\'andksppinmlike'%_db_block_hash_buckets%'; PRAGMAAUTONOMOUS_TRANSACTION; expuserid=test/xxxxfile=t1.dmplog=t1.logtables=wp_imagebuffer=4096000feedback=1000000statistics=nonequery=\"whererownum\<=40000000\"impuserid=test/xxxxbuffer=40960000file=t1.dmpfromuser=testtouser=testcommit=yindexes=nIGNORE=yFEEDBACK=1000000 --得到b2，e2selectbuffer_gets,executionsfromv$sqlwheresql_id='&1'; --得到sql的评价逻辑读select(b2-b1)/(e2-e1)fromdual; 79.查询某条sql的动态绑定变量（给定sql_id）selectname,datatype_string,value_stringfromv$sql_bind_capturewheresql_id='&1'; 80.添加redo日志--每个redo日志组，可以在线添加，删除组成员，但是必须保证每个组中，至少包含一个成员 --查看当前日志组select*fromv$log; --查询每个日志组中的日志成员select*fromv$logfile; --添加redo日志组alterdatabaseaddlogfilegroup4('/data/oradata/dd/redo04.log')size512m;alterdatabaseaddlogfilegroup5('/data/oradata/dd/redo05.log')size512m;--查询数据库可以创建多少日志文件（多少数据文件...）V$CONTROLFILE_RECORD_SECTION selecttype,records_used,records_total,records_used/records_total*100"PCT_USED"fromsys.v_$controlfile_record_section; --查询备库当前模式selectopen_mode,DATABASE_ROLE,GUARD_STATUSfromv$database; --查询备库的恢复进度selectmax(checkpoint_time)fromv$datafile; --查询数据文件的checkpointSELECTFILE#file_nr,TO_CHAR(CHECKPOINT_TIME,'DD/MM/YYYYHH24:MI:SS')checkpoint_time,NAMEfile_nameFROMv$datafile_header; --通过lsdev命令可以看到网卡的个数与类型#lsdev-Ccadapter|grepent --查看网卡的IP可以用ifconfig或者是netstat，如#ifconfig-a#netstat-in --to_date函数selectto_date('2005-01-0113:14:20','yyyy-MM-ddHH24:mi:ss')fromdual; selects.snap_id||','||t.snap_time||','||executionsasresultfromperfstat.stats$sql_summarys,perfstat.stats$snapshottwherehash_value=3245291238ands.snap_id=t.snap_idorderbyt.snap_time 88.查找被锁定的表selectt2.sid,t2.machine,t4.spid,t3.*fromv$locked_objectt1,v$sessiont2,dba_objectst3,v$processt4wheret1.session_id=t2.sidandt1.object_id=t3.object_idandt2.paddr=t4.addr; select'alteruser'||username||'quotaunlimitedon'||TABLESPACE_NAME||';'fromDBA_TS_QUOTASwhereusernamein('test')orderbyusername; 92.使用dd建立文件分区ddif=/dev/zeroof=swapfilebs=1024kcount=xxmkswapswapfileswaponswapfile --查看系统磁盘繁忙程度iostat-x110 --查询系统磁盘读写sar-B110 --查询disk的大小fdisk 97.oracle9i处理varchar2中的数字类型（oracle9i不支持正则表达式）--全数字wheretranslate(id,'x1234567890','x')isnull; --包含数字wherelength(translate(id,'x1234567890','x'))<>length(id); --不包含数字wheretranslate(id,'x1234567890','x')=id; translate：以字符级进行替换操作，同时tostring不能为空replace：以字符串级(即整个字符串完全匹配才进行替换)进行替换操作 0.准备工作--将logmnr的默认表空间，从system移出executedbms_logmnr_d.set_tablespace('ts1');--创建数据字典executedbms_logmnr_d.build(dictionary_filename=>'ts1_dev01.dbf',dictionary_location=>'/u03/utl_file_dir');1.2个脚本$ORACLE_HOME/rdbms/admin/dbmslm.sql#用来创建DBMS_LOGMNR包，该包用来分析日志文件。$ORACLE_HOME/rdbms/admin/dbmslmd.sql#用来创建DBMS_LOGMNR_D包，该包用来创建数据字典文件。 2.3种创建数据字典方式1）直接访问数据库中在线数据字典2）将数据字典导出为一个文本文件3）将数据字典导出到log文件中 3.添加要分析的日志文件executedbms_logmnr.add_logfile(LogFileName=>'***',Options=>dbms_logmnr.new); executedbms_logmnr.add_logfile(LogFileName=>'***',Options=>dbms_logmnr.addfile); executedbms_logmnr.add_logfile(LogFileName=>'***',Options=>dbms_logmnr.removefile); 4.开始分析--无限制条件(使用导出的文本文件数据字典)executedbms_logmnr.start_logmnr(DictFileName=>'***');--直接使用在线的数据字典executedbms_logmnr.start_logmnr(options=>dbms_logmnr.DICT_FROM_ONLINE_CATALOG);--使用导出到log文件中的数据字典executedbms_logmnr.start_logmnr(options=>dbms_logmnr.DICT_FROM_REDO_LOGS); 5.查看分析结果v$logmnr_contentsselectoperation,sql_redo,sql_undofromv$logmnr_contentswhereusername='&1'andseg_name='&2'; 6.最后，终止日志分析executedbms_logmnr.end_logmnr; --批量替换目录下文件中的^M--Linux下，^M输入为：Ctrl+v+Enter--`find.-typef`命令两边的字符，为1边上的~sed-i"s/oldString//g"`grepoldString-rl/data/tempdata/0215`perl-p-i-e"s/^M//g"`find.-typef` 103.Oracle事件跟踪--10053，10046Event10053-DumpOptimizerDecisionsThiseventcanbeusedtodumpthedecisionsmadebytheoptimizerwhenparsingastatement.Level1isthemostdetailed Event10046-EnableSQLStatementTraceThiseventcanbeusedtodumpSQLstatementsexecutedbyasessionwithexecutionplansandtatistics.Bindvariableandwaitstatisticscanoptionallybeincluded.Level12isthemostdetailed. Level0Tracingisdisabled.ThisisthesameassettingSQL_TRACE=FALSE.Level1StandardSQLtraceinformation(SQL_TRACE=TRUE).Thisisthedefaultlevel.Level4SQLtraceinformationplusbindvariablevalues.Level8SQLtraceinformationpluswaiteventinformation.Level12SQLtraceinformation,waiteventinformation,andbindvariablevalues. --直接设置10046--跟踪本sessionaltersessionsetevents'10046tracenamecontextforever,level12';--跟踪其他sessionselectb.spid,a.sid,a.serial#,a.machinefromv$sessiona,v$processbwherea.paddr=b.addranda.machine='SYS_F85';executesys.dbms_system.set_ev(15,196,10046,1,'');--停止跟踪altersessionsetevents'10046tracenamecontextoff';execdbms_system.set_ev(1082,186,10046,0,'') --通过oradebug设置10046SQL>oradebugsetmypidSQL>oradebugevent10046tracenamecontextforever,level8;*******SQL>oradebugtracefile_name 其他SESSIONSELECTs.username,p.spidos_process_id,p.pidoracle_process_idFROMv$sessions,v$processpWHEREs.paddr=p.addrANDs.username=UPPER('&user_name');oradebugsetospid12345; --查看跟踪的trace文件selectvaluefromv$parameterwherename='user_dump_dest';tkprofora9i_ora_24722.trcora9i_ora_24722.sql --进入RMAN，删除过期归档rmantargetsys/passrmantarget/nocataloglistarchivelogall;crosscheckarchivelogall;deleteexpiredarchivelogall;deletearchiveloguntiltime'sysdate-1';--删除截止到前一天的所有archivelogdeletenopromptarchiveloguntilsequence27971;--删除到某指定sequence之前的所有归档changearchiveloguntillogseq=35760delete;--删除指定sequence之前的归档 --查询flash_recovery_area使用率select*fromV$FLASH_RECOVERY_AREA_USAGE; --查询physicalstandy使用归档的情况selectNAME,SEQUENCE#,FIRST_TIME,APPLIEDfromv$archived_logwhereAPPLIED='NO';--设置归档删除策略，强制删除未被APPLIED的归档CONFIGUREARCHIVELOGDELETIONPOLICYTOAPPLIEDONSTANDBY;--可删CONFIGUREARCHIVELOGDELETIONPOLICYTONONE;--归档未applied，不可删 106.取number类型精度（from叶正盛）col_str:=''||rs.column_name||''||rs.data_type;ifrs.data_typein('CHAR','VARCHAR2','NVARCHAR2')thencol_str:=col_str||'('||rs.data_length||')';elsifrs.data_typein('NUMBER')thenifrs.data_precisionisnotnullthencol_str:=col_str||'('||rs.data_precision||','||rs.data_scale||')';endif;endif; 107.oracle数据库大页（hugepages配置）--优势1.Pagetable大小降低：cat/proc/meminfo2.提高TLB命中率3.内存不替换，减少替换开销4.PGA不能使用hugepages --diretory创建，查询，赋权sqlplus"/assysdba"createorreplacedirectorydir1as'/data/tempdata/dir1';select*fromdba_directories;grantread,writeondirectorydir1totest; 111.查询sql的执行次数selectto_char(b.end_interval_time,'dd')ast,sum(executions_delta)asxfromdba_hist_sqlstata,dba_hist_snapshotbwheresql_id=':1'anda.snap_id=b.snap_idgroupbyto_char(b.end_interval_time,'dd')orderby1; 113.核心应用表dml监控selecttable_namefromdba_tableswhereowner='test'andtable_namelike'%CHG_DATA%'orderby1; 115.通过hash_value定位应用机器--当v$session中过期，可以在open_cursor中查询selects.username,s.sid,s.serial#,s.saddr,s.machine,s.sql_hash_valuefromv$sessions,v$open_cursorowheres.sid=o.sidands.saddr=o.saddr--ands.SQL_HASH_VALUE=o.HASH_VALUEando.HASH_VALUE=3851840776; --通过sql_text，查询machineselects.username,s.sid,s.serial#,s.saddr,s.machine,o.SQL_TEXTfromv$sessions,v$open_cursorowheres.sid=o.sidands.saddr=o.saddr--ands.SQL_HASH_VALUE=o.HASH_VALUEando.SQL_TEXTlike'%TRADE_TIMER%'; --在数据库中，定位被锁的存储过程，functionSELECTa.SID,a.serial#,a.status,a.username,a.osuser,b.owner,a.program,b.OBJECT,b.TYPE,'altersystemkillsession'||''''||a.SID||','||a.serial#||''''||'immediate;'sql_killFROMv$sessiona,v$accessbWHEREa.SID=b.SIDANDb.OBJECTLIKE'INSERT_KOSTOS_ETOS_ANA_YPHR%'--ANDowner='HF2006'--ANDb.TYPELIKE'PACKAGE%'--ANDstatus='ACTIVE'; coleventfora30colSAMPLE_TIMEfora40colMODULEfora25selectSAMPLE_ID,SAMPLE_TIME,session_id,USER_ID,event,p3,modulefromV$ACTIVE_SESSION_HISTORYwhereSAMPLE_TIME>=to_date('2011-05-0917:20:00','yyyy-mm-ddhh24:mi:ss')andSAMPLE_TIME<=to_date('2011-05-0918:05:00','yyyy-mm-ddhh24:mi:ss')orderbysample_time; 118.函数索引对应的实际列信息coltable_nameformata25colindex_nameformata30colcolumn_nameformata20coltablespace_nameformata15colindex_typeformata22colcolumn_expressionformata20colcolumn_positionheading'COLUMN|POSITION'selecta.table_name,a.index_name,a.column_name,b.tablespace_name,b.index_type,c.column_expressionfromdba_ind_columnsa,dba_indexesb,dba_ind_expressionscwherea.index_name=b.index_nameanda.index_name=c.index_name(+)anda.table_name=c.table_name(+)anda.column_position=c.column_position(+)anda.table_name=upper('&table_name')orderbya.index_name,a.column_position/ 119.11g里看一个库里是否有全表扫描，以及这些表的大小。colobject_ownerfora10colobject_namefora30selectsql_id,trunc(s/z)asexec_daily,object_owner,object_name,(selectsum(bytes)/1024/1024fromdba_segmentswwherew.segment_name=n.object_nameandw.owner=n.object_owner)asMsizefrom(selecta.sql_id,object_name,object_owner,(selectsum(executions_delta)fromdba_hist_sqlstatbwhereb.sql_id=a.sql_id)ass,(selectcount(distinctto_char(c.begin_interval_time,'yyyy-mm-dd'))fromdba_hist_sqlstatb,dba_hist_snapshotcwhereb.sql_id=a.sql_idandb.snap_id=c.snap_id)aszfrom(selectdistinctsql_id,object_owner,object_namefromDBA_HIST_SQL_PLANwhereOBJECT_OWNER='test'andOPERATION='TABLEACCESS'andoptions='FULL')a)nwherez>0ands/z>100orderbyexec_daily; 121.数据去重DELETEFROMWIZARDEWHEREE.ROWID>(SELECTMIN(X.ROWID)FROMWIZARDXWHEREX.VACCOUNT_ID=E.VACCOUNT_IDandX.WIZARD_NAME=E.WIZARD_NAME); selectidfrom(selectid,row_number()over(partitionbyc1orderbyid)rnfromtab)wherern>1;