ITPub博客

oracle 实现按周,月,季度,年查询统计数据 //按自然周统计 select to_char(date,'iw'),sum() from where group by to_char(date,'iw') //按自然月统计 select to_char(date,'mm'),sum() from where group by to_char(date,'mm') //按季统计 select to_char(date,'q'),sum() from where group by to_char(date,'q') //按年统计 select to_char(date,'yyyy'),sum() from where group by to_char(date,'yyyy') Oracle 中 IW和WW 有何差别 SQL> select day, 2 to_cha

find . -mtime +7 -exec rm {} ;find . -type f|xargs grep -i "XXX" --grep -i "xxx" /tmp[@more@]

Normal 0 7.8 pt 0 2 false false false MicrosoftInternetExplorer4 /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan

Solution -- Create new undo tablespace with smaller size.SQL> create undo tablespace UNDO_RBS1 datafile 'undorbs1.dbf' size 100m;-- Set new tablespace as undo_tablespaceSQL> alter system set undo_tablespace=undo_rbs1;-- Drop the old tablespace.SQL> drop tablespace undo_rbs0 including contents.NOTE: Dropping the old tablespace may give ORA-30013 : undo tablespace '%s' is currently in use. This error indicates you must wait for the undo tablespace to become unavailable. In other wo

BMB versus Freelist Segment: DBMS_SPACE.UNUSED_SPACE and DBA_TABLES.EMPTY_BLOCKS [ID 149516.1] Modified 22-OCT-2010 Type BULLETIN Status PUBLISHED PURPOSE-------This bulletin explains how to interpret the DBA_TABLES.EMPTY_BLOCKS value and DBMS_SPACE.UNUSED_SPACE information regarding FREELIST and BMB (Bit Mapped Block) segments in 9i. SCOPE & APPLICATION-------------------The following examples should he

1.人长得漂亮不如活得漂亮！2.当裤子失去皮带，才懂得什麽叫做依赖。3.烟不听话，所以我们“抽烟”。4.你发怒一分钟，便失去60秒的幸福。5.做与不做的最大区别是：后者拥有对前者的评论权。6.当男人遇见女人，从此只有纪念日，没有独立日。7.闭上眼睛，我看到了我的前途……8.路见不平一声吼，吼完继续往前走。9.幸福是个比较级，要有东西垫底才感觉得到。10.人生有时就像电脑，说死机就死机，没得商量。11.海阔凭鱼跃，破鼓任人捶。12.有钱的捧个钱场，没钱的回家取点钱来捧个钱场。13.能够说出的委屈，便不算委屈；能够抢走的爱人，便不

Normal 0 7.8 pt 0 2 false false false MicrosoftInternetExplorer4 /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan

其实现在Oracle对于rank和dense_rank已经支持合计功能，不过这次我仅仅使用了其分析功能。具体语法如下：RANK ( ) OVER ( [query_partition_clause] order_by_clause )。 下面给出一些来自网上的示例： TABLE：S （subject，mark） 数学，80 语文，70 数学，90 数学，60 数学，100 语文，88 语文，65 语文，77 现在我想要的结果是：每门科目的前3名的分数 数学，100 数学，90 数学，80 语文，88 语文，77 语文，70 那么语句就这么写： select * from (select rank() over(partition by subject order by m

v:* {behavior:url(#default#VML);} o:* {behavior:url(#default#VML);} w:* {behavior:url(#default#VML);} .shape {behavior:url(#default#VML);} Normal 0 7.8 pt 0 2 false false false MicrosoftInternetExplorer4 /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes;

http://linux.vbird.org/linux_basic/0320bash.php#redirect[@more@]

指令下達情況說明 cmd1 && cmd21. 若 cmd1 執行完畢且正確執行($?=0)，則開始執行 cmd2。 2. 若 cmd1 執行完畢且為錯誤 ($?≠0)，則 cmd2 不執行。 cmd1 || cmd21. 若 cmd1 執行完畢且正確執行($?=0)，則 cmd2 不執行。 2. 若 cmd1 執行完畢且為錯誤 ($?≠0)，則開始執行 cmd2。[@more@]

1、主机名：#Public Network - (eth0)192.168.10.11 rac1192.168.10.12 rac2#Private Interconnect - (eth1)10.10.10.11 rac1priv10.10.10.12 rac2priv#Public Virtual IP (VIP) addresses - (eth0)192.168.10.21 rac1vip192.168.10.22 rac2vip2、生成listener的cap文件,用于重建ocr后注册监听(也可以用后面的netca)crs_stat -p ora.rac1.LISTENER_RAC1.lsnr > /oracle/backup/ora.rac1.LISTENER_RAC1.lsnr.capcrs_stat -p ora.rac2.LISTENER_RAC2.lsnr > /oracle/backup/ora.rac2.LISTENER_RAC2.lsnr.cap3、停掉所

1 drop diskgroup DATA including contents;2 drop diskgroup FLA including contents;3 srvctl remove diskgroup -g DATA4 srvctl remove diskgroup -g FLA5 srvctl remove asm6 Remove the directory of $ORACLE_BASE/admin/+ASM7 vi /etc/oratab--remove this line--+ASM:/opt/oracle/product/10.2.0/db_1:N[@more@]

Introduction to Parallel Execution Parallelismis the idea of breaking down a task so that, instead of one process doing all of the work in a query, many processes do part of the work at the same time. An example of this is when 12 processes handle 12 different months in a year instead of one process handling all 12 months by itself. The improvement in performance can be quite high. Parallel execution is useful for many types of operations that access significant amounts of data.

Checkpoint Tuning and Troubleshooting Guide [ID 147468.1] Modified 17-FEB-2011 Type BULLETIN Status PUBLISHED Purpose: This bulletin provides the Database Administrator a better understanding of incremental checkpoint and a description of four initialization parameters used for checkpoint tuning: - FAST_START_MTTR_TARGET - LOG_CHECKPOINT_INTER

SELECTa.ksppinm "Parameter",b.ksppstvl "Session Value",c.ksppstvl "Instance Value"FROMx$ksppi a,x$ksppcv b,x$ksppsv cWHEREa.indx = b.indxANDa.indx = c.indxANDa.ksppinm LIKE '/_%' escape '/'[@more@]

Linux性能分析工具重点推荐nmon analyser http://nmon.sourceforge.netLinux操作系统资源监控工具重点推荐collectl http://collectl.sourceforge.net[@more@]

sqlnet.oraENCRYPTION_WALLET_LOCATION = (SOURCE = (METHOD = FILE) (METHOD_DATA = (DIRECTORY = /oracle/app/product/11.2.0/dbhome_1/admin/testasm/wallet) ) )startup mountalter system set wallet open identified by ***alter database open;[@more@]

How to enable trace in Oracle 1. Enable trace at instance levelPut the following line in init.ora. It will enable trace for all sessions and the backgroundprocessessql_trace = TRUEto disable trace:sql_trace = FALSE- or -to enable tracing without restarting database run the following command in sqlplusSQLPLUS> ALTER SYSTEM SET trace_enabled = TRUE;to stop trace run:SQLPLUS> ALTER SYSTEM SET trace_enabled = FALSE;2. Enable trace at session levelto start trace:ALTER SESSION SET

WAITEVENT: "latch free" Reference Note [ID 34576.1] Modified 11-NOV-2002 Type REFERENCE Status PUBLISHED "latch free" Reference NoteThis is a reference note for the wait event "latch free" which includes the following subsections: * Brief definition * Individual wait details (eg: For waits seen in <<View:V$SESSION_WAIT>>) * Systemwide wait details (eg: For waits seen in <<View:V$SYSTEM_EVENT>>) * Reducing waits /