Re:StoredProc SQL statistics
Thanks a lot everyone. I will start working on these things. Regards Sam -Original Message- [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 04, 2003 1:50 PM To: Multiple recipients of list ORACLE-L Hi People, Is there any way to find the statistics(like tkprof gives) of SQL's within storedproc , when storedrpoc is called. Basically I want to find culprit SQL within StoredProc. Any help is appreciated Regards Sam x$kglrd, x$kglcursor, sys.obj$, x$kgldp and sys.dependency$ ... I don't have the details here, so I let you work it out :-). The first three will let you find out by what a statement (from V$SQL) is fired, which is probably what you need. If x$kglnaown is null in x$kglrd, the row refers to a PL/SQL block (command type 47 in V$SQL) and you find the corresponding (address, hash_value) in x$kglcursor. Otherwise, it refers to the x$glnaown.x$kglcnam stored object. All this out of memory (you are lucky I was into this not long ago), so it's not guaranteed to be flawless but it should get you started. Regards, Stephane Faroult Oriole -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: sam d INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
StoredProc SQL statistics
Hi People, Is there any way to find the statistics(like tkprof gives) of SQL's within storedproc , when storedrpoc is called. Basically I want to find culprit SQL within StoredProc. Any help is appreciated Regards Sam __ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: sam d INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
pl/sql engine doubt
Hi List, I was going thru Oracle PL/SQL User's Guide and Reference. http://otn.oracle.com/docs/products/oracle9i/doc_library/release2/appdev.920/a96624/01_oview.htm#962 Paragraphs below (near the Figure 1-4 PL/SQL Engine in the doc) confused me little. para1 --- These two environments are independent. PL/SQL is bundled with the Oracle server but might be unavailable in some tools. In either environment, the PL/SQL engine accepts as input any valid PL/SQL block or subprogram. Figure 1-4 shows the PL/SQL engine processing an anonymous block. The engine executes procedural statements but sends SQL statements to the SQL Statement Executor in the Oracle server. para2-- In the Oracle Database Server: Application development tools that lack a local PL/SQL engine must rely on Oracle to process PL/SQL blocks and subprograms. When it contains the PL/SQL engine, an Oracle server can process PL/SQL blocks and subprograms as well as single SQL statements. The Oracle server passes the blocks and subprograms to its local PL/SQL engine. Now my doubt is: 1.In para1 - Does pl/sql engine only processes the procedural statements and always passes SQL statements to SQL Statement Executor ? 2.In para2 sentence When it contains ... says Oracle pl/sql engine can process SQL statements. Statements in both paragraphs seems ambiguous. Pls shade some light. Thanks Sam __ Do you Yahoo!? Yahoo! Platinum - Watch CBS' NCAA March Madness, live on your desktop! http://platinum.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: sam d INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE:connecting session to sqlarea to find the sql_text
Welcome mate , Same question I posted a month back First get the select osuser,sid from v$session; then use sid in foll. query SELECT sql_text FROM v$sqlarea WHERE (address, hash_value) IN (SELECT sql_address, sql_hash_value FROM v$session WHERE sid = sid_number) --- sam d [EMAIL PROTECTED] wrote: Date: Sun, 9 Jun 2002 20:47:31 -0700 (PDT) From: sam d [EMAIL PROTECTED] Subject: RE: Can we find SQL user To: [EMAIL PROTECTED] CC: Richard Huntley [EMAIL PROTECTED] Really appreciate it Richard, This is what I was missing wherea.sql_address=b.address(+) Thx a Lot list, Sam --- Richard Huntley [EMAIL PROTECTED] wrote: Sam, had problems sending this to the list, but thought this might help... here is what you need...enter the specific username then look at the MACHINE field: column username format a12; column osuser format a10; column machine format a20; select username,osuser,server,machine,sid,serial#,status,program, TO_CHAR(logon_time,'DD-MON- HH24:MI:SS')=20 from v$session where username =3D 'username' / -Original Message- From: sam d [mailto:[EMAIL PROTECTED]] Sent: Friday, June 07, 2002 1:18 AM To: Multiple recipients of list ORACLE-L Subject: Can we find SQL user Hi List, Suppose I have m1,m2,m3 machines, all the users sitting on these machines are using oracle 'user1' to connect to the server. As all the people are logged in with the same user name ,Can we find which user(or machine) has issued which SQL statement. Thanks Sam __ Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: sam d INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com __ Do You Yahoo!? Yahoo! Health - Feel better, live better http://health.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: sam d INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
V. Urgent - Got Stuck with trigger
Hi List, My friend has foll.(Big)problem. He'was testing the following trigger n now stuck. Do not execute this trigger on ur side. CREATE OR REPLACE TRIGGER tpp before alter or create or drop on database declare uname varchar2(20); begin select osuser into uname from v$session; if uname != 'Sachin' then raise_application_error(-20002,'not allowed); end if; end; Now he can't modify/drop this trigger, He gets the follow error: ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-01422: exact fetch returns more than requested number of rows ORA-06512: at line 4 - One thing I suggested is to make sure that there is only one session on server. Is that possible ? What are the other solutions ?? (Do not execute this trigger on ur side. if u don't know the solution) Thx Regards, Sam __ Do You Yahoo!? Yahoo! Health - Feel better, live better http://health.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: sam d INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Why DDL is not allowed in PL/SQL
Yes, But why then we can issue DLL in PL/SQL using DBMS_SQL package. -Original Message- From: Alroy Mascranghe [SMTP:[EMAIL PROTECTED]] Sent: Friday, July 12, 2002 12:33 PM To: Multiple recipients of list ORACLE-L Subject:RE: Why DDL is not allowed in PL/SQL Maybe it is to maintain transaction consistency, bcos the DDLs issue a commit implicitly. So if u do in the middle of DML-transaction it might mess up the transaction as a whole. -Original Message- Sent: Friday, July 12, 2002 10:58 AM To: Multiple recipients of list ORACLE-L Hi List, Why it is not allowed to write DDL(e.g. create table) in PL/SQL directly, but same can be done using DBMS_SQL package. Curious Sam __ Do You Yahoo!? Sign up for SBC Yahoo! Dial - First Month Free http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: sam d INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Performance Tuning and Backup Recovery
Sorry for the late reply, thx a lot all of u for the help Regards Sameer --- Godlewski, Melissa [EMAIL PROTECTED] wrote: Sam, Que1: If you have statistics on the table then the cost based optimizer is used. If you have out of data statistics you could be sending bad information to the optimizer for join conditions. Using the explain plan and plan table (utlplan.sql in $oracle_home/rdbms/admin) will show you cost information and access paths to the data. Que2: The trace file should have a date time stamp that is near the time you ran yours. Additionally if you look at the trace file it will have the osuer information in it. Que3: It's hard to say the cause. You will need to look at v$session and v$session_wait to identify what the session in waiting for. Is it possible there was other jobs executing on the system which slowed down your query the second time? On NT the perfmon on UNIX top will give you some information. You need to know what the waits are. Additionally you can trace or dump the session. Que4: Run an explain plan and tk_prof on the session to find out what objects the application is using. Verify all the indexes are there and not dropped accidentally. You need to find the objects and verify the access paths the optimizer is using for the application are the best ones. Hints or sql rewrite may be in order. Que5: I'm not sure what you mean by logical backup. Maybe below suggestions may work. Are you using RMAN for backups. If so look into tablespace recover. If not restore your backups to a development/test server recover the tablespace and associated data. Export/Import to your other machine. -Original Message- From: sam d [mailto:[EMAIL PROTECTED]] Sent: Tuesday, June 11, 2002 9:24 AM To: Multiple recipients of list ORACLE-L Subject: Performance Tuning and Backup Recovery Hi, I don't have much onhand experience, I thought list is the appropriate place to get information. These are related to Oracle Performance Tuning and Backup Recovery. I have a system of Oracle 8i under HP-Unix and with ARCHIVELOG on. It has been running and stable since last 2-3 years. This was a brief overview. Que 1: Assuming that I am using Cost based Optimizer (CBO); I have a query which is a join of three tables out of which only one table have been analyzed and in first case rest two were analyzed but long time back and after that I have changed the structure of the table and in second case the other two tables have not been analyzed. Will Oracle use CBO ? I know it will but then what will be the behavior of optimizer ? Will it take the best guess for the other two tables in both the cases and if yes then those best guesses will be depending upon on what ? Que 2: Suppose u have ran EXPLAIN PLAN for a particular query at sqlplus prompt and there are around 50 more users running EXPLAIN PLAN at the same time from the UNIX bos itself. In this case the OSUSER, TERMINAL, PROGRAM will be same (u know it better). Now suppose I want to run TKPROF against the trace file how will I identify which will be my trace file in USER_DUMP_DEST directory ? Que 3: Supposing if I have Oracle Report running on Oracle. I passed 3 parameters first time it took me around 3 seconds to execute and 4 parameters second time its hogging the system taking almost 25 minutes to run. There are no concatenated indexs on the underlying tables. What might be the cause and how would u go about identifying it ? Que 4: A user starts complaining about a particular part of an appln. What will be my first steps to optimize it ? Que 5: Let's say we have a situation where at point A I took a complete db backup. Somewhere after point B I lost an online redo log file. I recovered the db using the bck taken at point A and archived redo log files and started the new incarnation at point B. I took a complete logical backup at point B and taking incremental backup after that. Now again at point C I took the complete bck. If I have dropped a tablespace somewhere in between point B and C which are the possible ways to recover it and which one will be the fastest ? (Of course if possible). A---B---C---D If I am not clear on any of the part please let me know and I'll do the needful. Thanks __ Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: sam d INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL
RE: Once again : Oracle vs Sql Server
I guess this is the link u were talking abt. http://www.eweek.com/article/0,3658,s=1869a=27379,00.asp also had fun in reading http://gotdotnet.com/team/compare/oraclerespond.aspx -Original Message- From: DENNIS WILLIAMS [SMTP:[EMAIL PROTECTED]] Sent: Wednesday, June 19, 2002 3:40 AM To: Multiple recipients of list ORACLE-L Subject:RE: Once again : Oracle vs Sql Server Stephane - The closest I've seen is the eWeek benchmark. It actually put the databases head-to-head on the same server. I don't have the URL handy, but you can probably go to www.eweek.com and browse around. However, ultimately the issue comes down to your situation. For example, MSSQL only runs on W2K. In some situations that is okay, but for others that eliminates MSSQL from consideration. And so on with all the other differences. What is a big issue in your situation may not be an issue in my situation. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] __ Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: sam d INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Basic Question Again
Go Thru foll. topics in oracle manual Oracle8i Concepts 1 Introduction to the Oracle Server 3 Tablespaces and Datafiles 7 Memory Architecture Cheers Sam = __ Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: sam d INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Can we find SQL user
Really appreciate it Richard, This is what I was missing where a.sql_address=b.address(+) Thx a Lot list, Sam --- Richard Huntley [EMAIL PROTECTED] wrote: Sam, had problems sending this to the list, but thought this might help... here is what you need...enter the specific username then look at the MACHINE field: column username format a12; column osuser format a10; column machine format a20; select username,osuser,server,machine,sid,serial#,status,program, TO_CHAR(logon_time,'DD-MON- HH24:MI:SS')=20 from v$session where username =3D 'username' / -Original Message- From: sam d [mailto:[EMAIL PROTECTED]] Sent: Friday, June 07, 2002 1:18 AM To: Multiple recipients of list ORACLE-L Subject: Can we find SQL user Hi List, Suppose I have m1,m2,m3 machines, all the users sitting on these machines are using oracle 'user1' to connect to the server. As all the people are logged in with the same user name ,Can we find which user(or machine) has issued which SQL statement. Thanks Sam __ Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: sam d INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: sam d INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Can we find SQL user
We can definitely find the user , but considering my scenario 'all the people are logged in with the same oracle user' , I want to know:From what machine the SQL statement was fired. thx Sam name--- Reardon, Bruce (CALBBAY) [EMAIL PROTECTED] wrote: Have a look at v$session In particular the osuser, terminal and machine fields - these may help Also look at the listener log file - this may help Or do you have an application server sitting in the middle? HTH, Bruce Reardon -Original Message- From: sam d [mailto:[EMAIL PROTECTED]] Sent: Friday, 7 June 2002 15:18 Hi List, Suppose I have m1,m2,m3 machines, all the users sitting on these machines are using oracle 'user1' to connect to the server. As all the people are logged in with the same user name ,Can we find which user(or machine) has issued which SQL statement. Thanks Sam __ Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: sam d INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
[no subject]
no application server involved. thx Sameer -- We can definitely find the user , but considering my scenario 'all the people are logged in with the same oracle user' , I want to know:From what machine the SQL statement was fired. thx Sam name--- Reardon, Bruce (CALBBAY) [EMAIL PROTECTED] wrote: Have a look at v$session In particular the osuser, terminal and machine fields - these may help Also look at the listener log file - this may help Or do you have an application server sitting in the middle? HTH, Bruce Reardon -Original Message- From: sam d [mailto:[EMAIL PROTECTED]] Sent: Friday, 7 June 2002 15:18 Hi List, Suppose I have m1,m2,m3 machines, all the users sitting on these machines are using oracle 'user1' to connect to the server. As all the people are logged in with the same user name ,Can we find which user(or machine) has issued which SQL statement. Thanks Sam __ Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: sam d INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Can we find SQL user
Hi List, Suppose I have m1,m2,m3 machines, all the users sitting on these machines are using oracle 'user1' to connect to the server. As all the people are logged in with the same user name ,Can we find which user(or machine) has issued which SQL statement. Thanks Sam __ Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: sam d INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Archive log full
thx a lot all , for the info. (I'll look out for book Oracle9i for Windows 2000 Tips Techniques) -Original Message- From: Michael P Sale [SMTP:[EMAIL PROTECTED]] Sent: Friday, May 31, 2002 7:43 PM To: Multiple recipients of list ORACLE-L Subject:RE: Archive log full You absolutely need to keep the log archive destination disk space free to create new logs. You NEED to keep these archives for at least the time of the last beginning of a hot backup, or, if you're doing cold backups (with the database service stopped) then you need the archives from at least the time of the last cold backup. I would strongly suggest that you stop by your favorite bookstore and read the at least the first 2 chapters of my book Oracle9i for Windows 2000 Tips Techniques. This will give you a great background to avoid these kinds of problems in the future. The advantage of this book over the typical (and VERY good) books is that it is directed to the windows user. Regards, Michael Sale Author: Oracle9i for Windows(R) 2000 Tips Techniques http://www.amazon.com/exec/obidos/ASIN/0072194626 -Original Message- Sent: Friday, May 31, 2002 6:23 AM To: Multiple recipients of list ORACLE-L Hi List, I am new to oracle, Archive Log has filledup the entire hard disk. (No error thrown by the Oracle) Can I move those(zip) files safely to other location. (win2k,Oracle 8.1.6) (I did went thru the manual) thx Sam __ Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: sam d INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Archive log full
Hi List, I am new to oracle, Archive Log has filledup the entire hard disk. (No error thrown by the Oracle) Can I move those(zip) files safely to other location. (win2k,Oracle 8.1.6) (I did went thru the manual) thx Sam __ Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: sam d INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: SQL -Can this be done in a single query
Bhulu I made lil changes in the exists subqueries. (Query u sent selected the T1 row present in either t2 or t3 which was correct , but what it also did : t1 row got selected if t1.c2 = t2.c2 even if t2.c3 did not exist in t3.c3 (really complicated to explain in brief) ) foll. query now works: select t1.c2,t1.c4,t23.c2 c22,t23.c3,t4.c4 c44 from t1,t4,( select t2.c2 c2 ,t2.c3 c3 from t2,t3 where t2.c3=t3.c3) t23 where t1.c4=t4.c4(+) andt1.c2=t23.c2(+) and exists ( select 1 from t1 where t1.c2=t23.c2 union select 1 from t1 where t1.c4=t4.c4 ) Thx a lot Bhulu. Really appreciate all the efforts and time u gave. I only added a brick in structure ,u provided. Thx all u replied. -Original Message- From: S B [SMTP:[EMAIL PROTECTED]] Sent: Friday, May 10, 2002 4:14 PM To: Multiple recipients of list ORACLE-L Subject:RE: SQL -Can this be done in a single query Sam, I think this will work. Can you please try it out and let me know if I understood your problem correctly. select t1.c2,t1.c4,t23.c2,t23.c3,t4.c4 from t1,t4,( select t2.c2 c2 ,t2.c3 c3 from t2,t3 where t2.c3=t3.c3) t23 where t1.c4=t4.c4(+) andt1.c2=t23.c2(+) and exists ( select 1 from t2 where t2.c2=t1.c2 union select 1 from t4 where t2.c4=t4.c4 ) Regards Bhulu --- sam d [EMAIL PROTECTED] wrote: Bhulu,Amit your sol. is correct in the context ,But I forgot to mention(my apologies) that :if T1 has record which does not have corresponding entries in T2 as well as in T4 then that record from T1 should not get selected. The third col in table ( T2 ) will be null because there is no corresponding record in T3. Steven ,I have read the 'C. J. Date' but don't remember the edition. So can I have a single query (nested will also do)? rgds Sam --- sam d [EMAIL PROTECTED] wrote: Hi, Consider the following case. I have four tables as T1,T2,T3,T4 1.T1 and T2 has C2 as common field. 2.T2 and T3 has C3 as common field. 3.T1 and T4 has C4 as common field. cosider the foll. data:- |---| |T1 | |---| | C2 | C4 | |---| | 100 | 990 | | 101 | 991 | | 102 | 992 | | 103 | 993 | | 104 | 994 | --- |-| | T2 | |-| | C2| C3 | |-| | 100| 400| | 101| 401| | 102| 402|//this 402 is missing in the T3 table(affects the result) | 103| 403| | 104| 404| - (T2.C3=T3.C3) |-| | T3| |-| | C3 | || | 400 | | 401 | | 403 |//402 is missing | 404 | -- || | T4 | |---| | C4 | |---| | 990 | | 991 | | 992 | | 993 | -- //994 missing I want the result as :- - | Result | -- |( from T1)| (t2) | (t3)| (t4) | --| |100 |990 | 100 |400 | 990 | |101 |991 | 101 |401 | 991 | |102 |992 | null | null | 992|//null in place of 102,402 |103 |993 | 103 |403 | 993 | |104 |994 | 104 |404 | null |//null in place of 994 - Can this be done in a single query(no PL/SQL). Is this really tough one or i m lost??. (I have attached the script for table create/inserts.) - Do You Yahoo!? Yahoo! Shopping - Mother's Day is May 12th! CREATE TABLE T1 ( C2 NUMBER, C4 NUMBER) ; CREATE TABLE T2 ( C2 NUMBER, C3 NUMBER); CREATE TABLE T3 ( C3 NUMBER); CREATE TABLE T4 ( C4 NUMBER); INSERT INTO T1 ( C2, C4 ) VALUES ( 100, 990); INSERT INTO T1 ( C2, C4 ) VALUES ( 101, 991); INSERT INTO T1 ( C2, C4 ) VALUES ( 102, 992); INSERT INTO T1 ( C2, C4 ) VALUES ( 103, 993); INSERT INTO T1 ( C2, C4 ) VALUES ( 104, 994); INSERT INTO T2 ( C2, C3 ) VALUES ( 100, 400); INSERT INTO T2 ( C2, C3 ) VALUES ( 101, 401); INSERT INTO T2 ( C2, C3 ) VALUES ( 102, 402); INSERT INTO T2 ( C2, C3 ) VALUES ( 103, 403); INSERT INTO T2 ( C2, C3 ) VALUES ( 104, 404); INSERT INTO T3 ( C3 ) VALUES ( 400); INSERT INTO T3 ( C3 ) VALUES ( 401); INSERT INTO T3 ( C3 ) VALUES ( 403); INSERT INTO T3 ( C3 ) VALUES ( 404); INSERT INTO T4 ( C4 ) VALUES ( 990); INSERT INTO T4 ( C4 ) VALUES ( 991); INSERT INTO T4 ( C4 ) VALUES ( 992); INSERT INTO T4 ( C4 ) VALUES ( 993); __ __ Do You Yahoo!? Yahoo! Shopping - Mother's Day is May 12th! http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: sam d INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
VAX float to IEEE float
Hi , Did any one of u faced the problem of migrating VAX/VMS files having - float datat(8-bytes) to oracle8i(windows NT) platform. When using sqlldr for the same, there is a problem that VAX/VMS has different format for storing float data, and sqlldr expects it to be in IEEE format. any help is appreciated Thx SamDo You Yahoo!? Yahoo! Games - play chess, backgammon, pool and more
