ITPub博客

Hi there,I am running Oracle 8.1.7 EE on SUN Solaris. I created two databasesSUGAR and TestDb (I need one to do some testing). Both are set remote_login_passwordfile = exclusive. The following shows how I had trouble to connect as SYSDBA. The big problem is that I cannot connect to TestDb as SYSDBA.$sqlplus /nologSQL> conn sys/change_on_install@testdbconnectedSQL> select * from v$pwfile_users;username sysdb sysop------------------------------internal true truesys

Consistenet gets is based upon re-constructing a block for consistent read. Hence it is a function of only the number of db_blocks to be read. If you say that it is altered by the arraysize, do you suggest that, due to arraysize, some blocks are read muliple times and hence some blocks have > 1 consistent read in the process Thanks Followup: No, you are wrong in your statement. A consistent get is a block gotten in read consistent mode (point in time mode). It MAY

i have 8174 db.how can i know that i have the db buffer too small?thanks Followup: trick question.you have to first make sure your SQL is as good as it gets (reduce the number of LIO's your sql does). Then, you need to understand if a sql cache of any size can cache some percentage of your data (maybe it can, maybe it can't)Then you can look at ratio to see if you have a satisfactory hit.But basically, you want to look at what you system is waiting for. Not waiting on physical IO? then y

-- Thanks for the question regarding "DIFFERENCES BETWEEN PROCESSES, SESSIONS AND CONNECTIONS", version 8.1.7.4originally submitted on 28-Sep-2002 10:19 Eastern US time, last updated 27-Apr-2005 16:37You Asked Hi TomWhat's the difference between connections, sessions and processes?I read a note from Metalink about the difference but I simply dont get it!May you give a brief explanation?Thank you and we said...A process is a physical process or thread.On unix, you can se

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:1993409963502Sudee -- Thanks for the question regarding "Calling Export from Pl/sql using C external proc.", version 8.1.6originally submitted on 16-Nov-2001 20:55 Eastern US time, last updated 27-Apr-2005 9:31You AskedHi Tom,Is there anything wrong with these steps,the exp does not create a .dmp file.Thanks #include <stdio.h> #include <stdlib.h> #include <string.h> __declspec(dllexport) v

Nag -- Thanks for the question regarding "sql to get data from flat file-- this is mind blowing", version 8.1.7originally submitted on 12-Oct-2001 19:26 Eastern US time, last updated 26-Apr-2005 20:08You Asked "In 9i, with the addition of external tables ( the ability to query a FLAT FILE with SQL) -- SQLLDR might be "moot". Using the external table and the insert /*+ append */ I can basically do a direct path load from file to database without running a command

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:25685777038588in general your query won't get the "next n rows".Why would you not just:select /*+ FIRST_ROWS */ * from ( select /*+ FIRST_ROWS */ a.*, rownum rnum from (select /*+ FIRST_ROWS */ * from t where .... order by ... ) a where rownum <= :MAX_ROW ) where rnum >= :MIN_ROW;done. that is how I write all pagination queries.(you do know that A||B has serious programming issues -- A=1, B=11

The purpose of this document is to provide an easy to use, step by step guide to resolving ORA-04031 errors. Contents: 1. Instance parameters related with the Shared Pool2. Diagnosing ORA-04031 errors3. Resolving error ORA-04031    * Known Oracle BUGs    * ORA-4031 when compiling Java code    * Small shared pool size          o Library Cache Hit Ratio        &#16

How to get the the date of first day of the week for a given week #? For example, the beginning day for week# 34 in 2003 is AUG-18-2003, how do I find the beginning date for the same week # 34 in 2002? 'iw' can't be used in the to_date function.Thanks! Followup: well, we can use the fact that at least from 1800 to 2199, jan 4th is in the first week... this:ops$tkyte@ORA920> select * 2 from ( 3 select dt1, to_char( dt1, 'yyyy' ), 4 to_char( dt4, 'iw' ), 5

You Asked (Jump to Tom's latest followup)Hi TomI hope I'm phrasing this correctly...I'd like to be able to send an HTML formatted email from the database using the UTL_SMTP package. I don't see any way of setting the MIME type.Is this beyond the scope of UTL_SMTP?thanks in advance!Ken and we said...I asked Tyler Muth (tyler.muth@oracle.com) to answer this one since I knew he already had the code (we use it on our Oracle Calendar software shipped with the email server to send reminde

Mohammad -- Thanks for the question regarding "list all user(s) group by group_list", version 8.1.7originally submitted on 27-Jun-2003 10:46 Eastern US time, last updated 21-Apr-2005 12:02 Tom's latest followup | GOTO a Bookmarkable Page | BottomYou Asked (Jump to Tom's latest followup)Hello Tom,i have the follwing table:LOGIN_NAME GROUP_LIST--------------- --------------------------------------------------Demo 1; 9000; 1000;Koo 1000;Feh

很多人都会为如何创建索引发愁, 在什么情况下需要创建索引? 对哪些字段创建索引? 创建什么索引?下面就是对应的答案, 来自Oracle perf tunning guide.Choosing Columns and Expressions to Index A key is a column or expression on which you can build an index. Follow these guidelines for choosing keys to index: Consider indexing keys that are used frequently in WHERE clauses.Consider indexing keys that are used frequently to join tables in SQL statements. For more information on optimizing joins

Building a Random Character Value Generator Oracle Database 10g provides a random value generator via a package named DBMS_RANDOM, which can be used to generate random values. However, those values are arbitrary, not representative of the real-life system. For instance, the following an example of how you can generate a string 30 characters

create or replace procedure who_called_me( owner out varchar2, name out varchar2, lineno out number, caller_t out varchar2 )as call_stack varchar2(4096) default dbms_utility.format_call_stack; n number; found_stack BOOLEAN default FALSE; line varchar2(255); cnt number := 0;begin-- loop n := instr( call_stack, chr(10) ); exit when ( cnt = 3 or n is NUL

上次过去Rudolf问我的问题, 一时不知怎么回答, 其实我很少使用trprof进行诊断, 只是在使用执行计划与相关系统信息都不能找出较优或者无法找到问题的时候, 才会考虑使用tkprof来解决问题. 这位老兄的帖子刚好解决了我的疑惑.. 就直接引用过来了:-) 如有版权问题, 请联系本人^_^Why dose Oracle have 2 fetches for 1 exectuion on dual? Have you ever noticed that there are 2 fetches for 1 exectuion of such simple sql SQL>select * from dual; Let's turn on the 10046 event and check the trace file. PARSING IN

Preventing Oracle from Using an IndexCompliments of Oracle FAQ In certain cases, you may want to disable the use of a specific, or all indexes for a given query. Here are some examples demonstrating several methods for doing so:-- Adding an expression to the indexed columnSQL>select count(*) from t where empno+0=1000; COUNT(*)---------- 1Execution Plan--------------------------------------------- ----- -------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=3)

initial and lmt - 8i  July 09, 2003 Reviewer:  M. Bento  from Lisbon, Portugal When moving a table into a uniform LMT using alter table without the storage clause the table's initial is not set to the LMT default initial parameter but keeps its previous value ending up with tables with different initial extents inside the same LM tablespace.Should one set the initial along with the alter table in order to prevent fragmentation?Thanks. Followup:  you are confusing the initial

Raghu -- Thanks for the question regarding "moving tables across tablespaces", version originally submitted on 2-May-2000 13:20 Eastern US time, last updated 14-Apr-2005 18:04Tom's latest followup | GOTO a Bookmarkable Page | Bottom You Asked (Jump to Tom's latest followup)Tom, What is the best way to move tables from one tablespace to another tablespace in the same instance? and we said...I'll give you the Oracle8.0 and before answer as well as the Oracle8i and above answer (in 8i,

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