ITPub博客

G -- Thanks for the question regarding "Rows into columns", version 9.0.1originally submitted on 24-Jan-2004 20:20 Eastern US time, last updated 13-Apr-2005 9:55Tom's latest followup | GOTO a Bookmarkable Page | Bottom You Asked (Jump to Tom's latest followup)Hi Tom,I have a querysql> SELECT tr_date, item_id, adult_price adult, child_price childFROM mytableWHERE tr_date BETWEEN '01-jan-2004' AND '31-jan-2004'AND   tr_code LIKE 'D%'The output isTR_DATE   &#16

David -- Thanks for the question regarding "Update statement and Null-values -- update a table from another table", version Oracle 7.3.4originally submitted on 5-Jun-2000 10:18 Eastern US time, last updated 13-Apr-2005 11:48Tom's latest followup | GOTO a Bookmarkable Page | Bottom You Asked (Jump to Tom's latest followup)Tom,When updating a column with an update-statement, the value of some records (records that don't need to be updated), are changed into the value NULL. I use the next

^_^....[@more@]

今天在asktom上看到这么一个帖子, 觉得比较简洁明了, 这也是tom的一贯风格.Can we use the init.ora created from spfile to recreate spfile in case of any spfile errors? I find a *. prefixed to all parameters. Should this be removed ?One more question: If I have to change init parameters for making my db to archivelog mode, then the steps what I understand is :a. Change the reqd parameters in init.ora.b. Startup the db as usual from spfile.c. Create spfile from pfile.d. shutdown normale. startup 

Mark -- Thanks for the question regarding "In list question with bind variables.", version originally submitted on 2-May-2000 13:21 Eastern US time, last updated 12-Apr-2005 21:02Tom's latest followup | GOTO a Bookmarkable Page | Bottom You Asked (Jump to Tom's latest followup) Hi Tom, I am using Pro*C and I need to send an array of strings in a "where <value> in :<value>" is this possible? And if not what should I do?     and we said.

Subhash -- Thanks for the question regarding "DBMS_JOB -- how to pass parameters to the job", version 8.0.5originally submitted on 11-Mar-2001 23:13 Eastern US time, last updated 11-Apr-2005 13:18Tom's latest followup | GOTO a Bookmarkable Page | Bottom You Asked (Jump to Tom's latest followup)Hi Tom   I have one procedure execute_job with two in parameters    (batchnumber and loaddate). Procedure as follows :-create or replace procedure execute_job(batchnumber in n

昨天出于无聊, 测试了一下关于Oracle9idual表的相关处理.发现一个问题, 对于很小的表, 使用full table 桑蚕并不是最好的选择..:-)SQL> create table dual_x(dummy varchar2(1) primary key);Table created.SQL> insert into dual_x values('X');1 row created.SQL> commit;Commit complete.SQL> select * from dual_x;D                                

出于无聊写了这个函数, 供大家赏玩.:-)上次贴这个数据的时候, 忘记将对应的数据类型贴出来, 这里将他补齐.create type DATABAK.date_obj as object (    month varchar2(20),    sun char(2),    mon char(2),    tue char(2),    wed char(2),    thu char(2),    fri char(2),    sat char(2));create type DATABAK.date_obj_tab as table of date_obj;function DATABAK.get_date_str(in_date in date def

Hi Tom,We have a similar requirement to have global values accesible across multiple sessions.We are currently in the process of upgrading to 9i, and was thinking of using the global context feature to do that.Can you provide us with a simple example to do this on 9i using global context?Appreciate your help,Thanks, Followup:  ops$tkyte@ORA920> create or replace context App_Ctx using My_pkg  2  ACCESSED GLOBALLY  3  /Context created.ops$tkyte@ORA920>ops$tkyte@

Reviewer: hk from Finland Original questioner asked also:"How do I know if I actually do need 3.5G undo spaceor Oracle is not reusing the undo space where it shouldbe?"That is good question anyhow and it's still open here?If undo_tablespace size is 100Megs and we run some batch job, which take 95megs undo space. Now, batch job is done and undo_tablespace is 95% full. Then immediately starts another transaction and it needs over 5 megs undo space. Is undo_retention ignored and trans

with sum_obj as(    select        nvl(county_code,'合计') county_code,        sum(case when color_flag = 0 then objective else 0 end) t_red,        sum(case when color_flag = 3 then objective else 0 end) t_yellow,        sum(objective) t_total,        sum(case when kind = 1 and color_flag = 0 then objective else 0 end) a_red, &#

Thanks for the quick response.Is there a way to force temp files to be created as non-sparse files in UNIX ? Followup:  Here, I'll let you tech edit a new section of "expert one on one Oracle" 2cnd edition :)Temp FilesTemporary data files (temp files) in Oracle are a special type of data file. Oracle will use temporary files to store the intermediate results of a large sort operation, hash operations, global temporary table data, or result set, when there is insufficient memory to

  May 07, 2002 Reviewer:  A reader Hi Tom,When user is doing sort operation.oracle first do sort in memory(sort_area_size) and if the space is not enough it go to temporary tablespace(if temp tablespace is TEMP) and create TEMP SEGMENT for sorting.Once process is done with sorting oracle deallocate extents.Am i right?As far as i konw we only create TEMP tablespace ,How Sort segment are created?.and how many sort segment(max) can be in a database.2) If many session are performing sort o

Reviewer: A reader Hi I just asked about meaning of connect_timeout... I wanted to ask somthing more, in some servers when I try to connect such asscott/tiger@testif test is not defined in tnsnames.ora I get an error straight away however in some servers it takes ages to tell me the errorwhich parameter defines that interval? Followup: look in your sqlnet.ora -- there is an entry like:NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)so, for me, if tnsnames.ora lookup fails (no test), it'll tr

please correct me if my understanding is wrong.LIO - from/to the buffer cache ( not disk ).PIO - to/from the disk.correct ?in many of the threads on tuning i notice that you mention, the less the number of PIO as well as LIO the better. i can understand the 'physical' part of it but not the logical.the query has to get the results and it has to do as much work as is necessary to get it, isnt it ? so at least that much of LIO is a must. how can we reduce that ? Followup: LIO = logical = buffer

1. use dual table and connect by . ( 转载自 hotsos )SQL> @aaa.sqlSQL> with x as (  2           select trunc(sysdate,'YEAR') + rownum -1  3           from dual  4           connect by 1 = 1  5           and level <= add_months(trunc(sysdate,'YEAR'),12) - trunc(sysdate,'

最近在itpub上看到这个帖子http://www.itpub.net/338792.html, 引发我下面要说的话. 1. :-) 楼主在描述问题的时候, 并没有将她/他遇到的问题描述清楚. 我在建实体化视图的时候个别create mv语句总是报ORA-01652: unable to extend temp segment by 1024 楼主并没有将他操作中遇到的实际的错误信息展示给我们,实际的错误信息,应该是这样的吧:-)下面我模拟一个空间不够的例子, 我们可以看到实际的错误应该是这样的.SQL> select bytes from user_segments where segment_name = 'T'; BYTES---------- 4194304SQL> crea

我们的系统每天都有一张大表, 主要是用来做报表处理的. 这个表的字段数目相当多, 目前已经达到50多个字段了, 其中有40个是标志字段. 受oracle系统表设计的影响, 我想到一下的方法来出里对应的表.1. Oracle的相关系统表. 打开sql.bsq的原始文件, 我们随处可见Oracle的设计中, 尽量将相应的具有类西属性的字段通过数字的位数来进行处理, 而后又通过bitand与视图联合处理的方式显示给最终用户. 我下面要举的例子来自oracle的动态视图,v$sql_shared_cursor.SQL> set long 5000SQL> select view_definition 2 from v$fixed_view_def

You Asked (Jump to Tom's latest followup)Hi TomI never really understood the usage of GROUPING_ID function in OTN.I heard it avoids using multiple GROUPING functions.Can you please illustrate with a small exampleThanks and we said...http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:18735507471922#18844335353167You use grouping_id with "grouping sets" or group by cube/rollup -- it tells you the level of aggregation.Consider, we'll show grouping sets, group b

Reviewer:  A reader  from NJ, USA Tom,I reviewed your book (Expert One-on-One Oracle and Oracle Perf. By design, Cary Millsap's Optimizing Oracle Performance. Based on these, I have come up with a little logic of how the Oracle kernel's latch acquisition algorithm will look like at a very high level. Please confirm if my understanding is correct. The code snippet is shown below :Code snippet for latch that sorround an Oracle kernel code==================================================