sort aggregate vs sort order by
Hi All, I have tried the Oracle Doc's but can't find the answer to what is the difference between the two Anybody can explain?? TIA Jack === De informatie verzonden met dit E-mail bericht is uitsluitend bestemd voor de geadresseerde. Gebruik van deze informatie door anderen dan de geadresseerde is verboden. Openbaarmaking, vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan derden is niet toegestaan. Ernst Young staat niet in voor de juiste en volledige overbrenging van de inhoud van een verzonden E-mail, noch voor tijdige ontvangst daarvan. === The information contained in this communication is confidential and may be legally privileged. It is intended solely for the use of the individual or entity to whom it is addressed and others authorised to receive it. If you are not the intended recipient you are hereby notified that any disclosure, copying, distribution or taking any action in reliance on the contents of this information is strictly prohibited and may be unlawful. Ernst Young is neither liable for the proper and complete transmission of the information contained in this communication nor for any delay in its receipt. === -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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).
Exp / Imp Utility Questions
I'm doing a DB conversion to 8.1.7 on W2000. I have converted the old tables / data to the new application on my conversion server. Now I have to load it onto a test server at a different site. I will be using tables= parameter to a select group of tables / data. I have not found the answers to my questions in the documentation. 1. Can I use Exp / Imp to just move the data? Or, does this utility move the data and the table structures? 2. The conversion DB that I have only has the tables, PK's, FK's, and indexes and no triggers, functions, cursors, etc. (they are created by another script). There is no application code attached to this DB. The DB I will be loading to is fully functional - all the PK's, FK's, triggers, functions, et al. Will loading the data from my conversion DB cause problems with the test DB? 3. I know I will have to disable the FK's on the test DB. But, what about the triggers on the applicable tables? Should they be disabled? Anything else I need to know before I get rolling on this? Thanks, Ken Janusz, CPIM Database Conversion Lead Sufficient Systems, Inc. Minneapolis, MN -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ken Janusz 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: Oracle registry
change ... [HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\ALL_HOMES] HOME_COUNTER=2 DEFAULT_HOME=OraHome81 LAST_HOME=1 to [HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\ALL_HOMES] HOME_COUNTER=2 DEFAULT_HOME=OraHome81 LAST_HOME=0 Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- Sent: Thursday, December 27, 2001 10:15 PM To: Multiple recipients of list ORACLE-L HELP ME.it take me one day to trigger this...but still no outcome When I start install oracle and I accidently cancel the process and continous install again without cleaninig all the registry. below is my registry I try to replace HOME1 TO HOME 0but I still can't tnsping my host, the error message popup 'message file not found for facility network ... I need advice on this... [HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE] inst_loc=C:\\Program Files\\Oracle\\Inventory ORACLE_HOME=e:\\oracle\\ora81 ORACLE_HOME_NAME=OraHome81 API=e:\\oracle\\ora81\\dbs ORACLE_GROUP_NAME=Oracle - OraHome81 NLS_LANG=NA OLEDB=c:\\oracle\\ora81\\oledb\\mesg VOBHOME2.0=c:\\oracle\\ora81 OO4O=c:\\oracle\\ora81\\oo4o\\mesg [HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\ALL_HOMES] HOME_COUNTER=2 DEFAULT_HOME=OraHome81 LAST_HOME=1 [HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\ALL_HOMES\ID0] NAME=OraHome81 PATH=e:\\oracle\\ora81 NLS_LANG=NA [HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\ALL_HOMES\ID1] NAME=ORACLEHOME PATH=c:\\oracle\\ora81 NLS_LANG=NA [HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0] ID=0 ORACLE_GROUP_NAME=Oracle - OraHome81 ORACLE_HOME_NAME=OraHome81 ORACLE_HOME=e:\\oracle\\ora81 NLS_LANG=NA ORACLE_HOME_KEY=Software\\ORACLE\\HOME0 SQLPATH=c:\\oracle\\ora81\\dbs ORACLE_BASE=c:\\oracle MSHELP_TOOLS=c:\\oracle\\ora81\\MSHELP RDBMS_CONTROL=c:\\oracle\\ora81\\DATABASE RDBMS_ARCHIVE=c:\\oracle\\ora81\\DATABASE\\ARCHIVE ORA_SPD_AUTOSTART=hex(2):54,00,52,00,55,00,45,00,00,00 ORA_SPD_PFILE=hex(2):63,00,3a,00,5c,00,6f,00,72,00,61,00,63,00,6c,00,65,00 ,\ 5c,00,61,00,64,00,6d,00,69,00,6e,00,5c,00,73,00,70,00,64,00,5c,00,70,00,66,\ 00,69,00,6c,00,65,00,5c,00,69,00,6e,00,69,00,74,00,73,00,70,00,64,00,2e,00,\ 6f,00,72,00,61,00,00,00 ORA_SPD_SHUTDOWN=hex(2):54,00,52,00,55,00,45,00,00,00 ORA_SPD_SHUTDOWNTYPE=hex(2):69,00,00,00 ORA_SPD_SHUTDOWN_TIMEOUT=hex(2):33,00,30,00,00,00 ORACLE_SID=spd [HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME1] ID=1 ORACLE_GROUP_NAME=Oracle - ORACLEHOME ORACLE_HOME_NAME=ORACLEHOME ORACLE_HOME=c:\\oracle\\ora81 NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1 ORACLE_HOME_KEY=Software\\ORACLE\\HOME1 SQLPATH=c:\\oracle\\ora81\\dbs ORACLE_BASE=c:\\oracle MSHELP_TOOLS=c:\\oracle\\ora81\\MSHELP RDBMS_CONTROL=c:\\oracle\\ora81\\DATABASE RDBMS_ARCHIVE=c:\\oracle\\ora81\\DATABASE\\ARCHIVE ORA_SPD_AUTOSTART=hex(2):54,00,52,00,55,00,45,00,00,00 ORA_SPD_PFILE=hex(2):63,00,3a,00,5c,00,6f,00,72,00,61,00,63,00,6c,00,65,00 ,\ 5c,00,61,00,64,00,6d,00,69,00,6e,00,5c,00,73,00,70,00,64,00,5c,00,70,00,66,\ 00,69,00,6c,00,65,00,5c,00,69,00,6e,00,69,00,74,00,73,00,70,00,64,00,2e,00,\ 6f,00,72,00,61,00,00,00 ORA_SPD_SHUTDOWN=hex(2):54,00,52,00,55,00,45,00,00,00 ORA_SPD_SHUTDOWNTYPE=hex(2):69,00,00,00 ORA_SPD_SHUTDOWN_TIMEOUT=hex(2):33,00,30,00,00,00 ORACLE_SID=spd [HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\OLEDB] CacheType=Memory ChunkSize=100 DistribTX=1 FetchSize=100 OSAuthent=0 PLSQLRSet=0 PwdChgDlg=1 SchRstLng=1 UserDefFn=0 DisableRetClause=1 TraceCategory=0 TraceFileName=c:\\OraOLEDB.trc TraceLevel=0 TraceOption=0 [HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\OO4O] CacheBlocks=20 FetchLimit=100 FetchSize=4096 HelpFile=c:\\oracle\\ora81\\MSHELP\\oracleo.hlp PerBlock=16 SliceSize=256 TempFileDirectory=c:\\temp OO4O_HOME=c:\\oracle\\ora81\\oo4o -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: RAYMOND 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). *1 This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.
RE: sort aggregate vs sort order by
Jack, I assume you are asking this question in reference to the access path, or explain plan, for a query. Sort (Aggregate) -- Query returns a single row using a summary function but does not include a GROUP BY clause. Sort (Order By) -- Query includes an ORDER BY clause. Sort (Group By) -- Query includes a GROUP BY clause. In the 8i documentation, this info can be found in Table 5-4 in the Designing and Tuning for Performance manual. FWIW, though mentioned elsewhere in the same manual, Table 5-4 doesn't mention the SORT (GROUP BY NO SORT) -- ordered data is being fed into the GROUP BY step negating the need for the sort operation that is needed for a GROUP BY. Can occur under certain conditions -- sometimes when index access supporting the ordering is used, after a sort merge, etc. I attached some examples to illustrate. Regards, Larry G. Elkins [EMAIL PROTECTED] 214.954.1781 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of [EMAIL PROTECTED] Sent: Friday, December 28, 2001 5:00 AM To: Multiple recipients of list ORACLE-L Subject: sort aggregate vs sort order by Hi All, I have tried the Oracle Doc's but can't find the answer to what is the difference between the two Anybody can explain?? TIA Jack === De informatie verzonden met dit E-mail bericht is uitsluitend bestemd voor de geadresseerde. Gebruik van deze informatie door anderen dan de geadresseerde is verboden. Openbaarmaking, vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan derden is niet toegestaan. Ernst Young staat niet in voor de juiste en volledige overbrenging van de inhoud van een verzonden E-mail, noch voor tijdige ontvangst daarvan. === The information contained in this communication is confidential and may be legally privileged. It is intended solely for the use of the individual or entity to whom it is addressed and others authorised to receive it. If you are not the intended recipient you are hereby notified that any disclosure, copying, distribution or taking any action in reliance on the contents of this information is strictly prohibited and may be unlawful. Ernst Young is neither liable for the proper and complete transmission of the information contained in this communication nor for any delay in its receipt. === -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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). Examples: SQL select min(sal) from emp; Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=2) 10 SORT (AGGREGATE) 21 TABLE ACCESS (FULL) OF 'EMP' (Cost=1 Card=8 Bytes=16) SQL select ename from emp order by ename; Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=8 Bytes=40) 10 SORT (ORDER BY) (Cost=3 Card=8 Bytes=40) 21 TABLE ACCESS (FULL) OF 'EMP' (Cost=1 Card=8 Bytes=40) SQL select min(sal) from emp group by deptno; Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=2 Bytes=8) 10 SORT (GROUP BY) (Cost=3 Card=2 Bytes=8) 21 TABLE ACCESS (FULL) OF 'EMP' (Cost=1 Card=8 Bytes=32) 1 select foo_date, count(*) 2 from code_master 3 where foo_date sysdate - 1000 4* group by foo_date -- foo_date is indexed SQL / Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=5000 Bytes=35000) 10 SORT (GROUP BY NOSORT) (Cost=4 Card=5000 Bytes=35000) 21 INDEX (RANGE SCAN) OF 'CM_FD_IDX' (NON-UNIQUE) (Cost=4 Card=5000 Bytes=35000) 1 select foo_date 2 from code_master 3 where foo_date sysdate - 1000 4* order by foo_date -- foo_date is indexed SQL / Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=5000 Bytes=35000) 10 INDEX (RANGE SCAN) OF 'CM_FD_IDX' (NON-UNIQUE) (Cost=4 Card=5000 Bytes=35000)
RE: Exp / Imp Utility Questions
Title: RE: Exp / Imp Utility Questions 1) You cannot have read the docs to well not to know the answer to Q1 Hint - look at the ignore parameter to imp 2) It all depends if the triggers have been run once to change the data that is now in the conversion db. You may have a trigger that adds 1 to a field. If the data has been populated into a table with the trigger enabled it will have value of field + 1. If you then import the data into your test db with a trigger enabled it will action the trigger and the field will then have the value of field +1 +1. Remember import is only a fancy way of typing insert into for each row of data 3) As you say, it is a test database, experiment with a couple of tables and find out the answers for yourself. That is the best way of learning John -Original Message- From: Ken Janusz [mailto:[EMAIL PROTECTED]] Sent: 28 December 2001 12:45 To: Multiple recipients of list ORACLE-L Subject: Exp / Imp Utility Questions I'm doing a DB conversion to 8.1.7 on W2000. I have converted the old tables / data to the new application on my conversion server. Now I have to load it onto a test server at a different site. I will be using tables= parameter to a select group of tables / data. I have not found the answers to my questions in the documentation. 1. Can I use Exp / Imp to just move the data? Or, does this utility move the data and the table structures? 2. The conversion DB that I have only has the tables, PK's, FK's, and indexes and no triggers, functions, cursors, etc. (they are created by another script). There is no application code attached to this DB. The DB I will be loading to is fully functional - all the PK's, FK's, triggers, functions, et al. Will loading the data from my conversion DB cause problems with the test DB? 3. I know I will have to disable the FK's on the test DB. But, what about the triggers on the applicable tables? Should they be disabled? Anything else I need to know before I get rolling on this? Thanks, Ken Janusz, CPIM Database Conversion Lead Sufficient Systems, Inc. Minneapolis, MN -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ken Janusz 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). = This electronic message contains information from the mmO2 plc Group which may be privileged or confidential. The information is intended to be for the use of the individual(s) or entity named above. If you are not the intended recipient be aware that any disclosure, copying, distribution or use of the contents of this information is prohibited. If you have received this electronic message in error, please notify us by telephone or email (to the numbers or address above) immediately. =
RE: Firing order of triggers
Although there are already so many responses I would like to add one more You can of course put all logic in one trigger and then have control over events Sometimes it is impossible in the trigger because there is limitation of trigger size (32 K) So in that case You have to put logic in packages/procedures and call them in the appropriate order in the trigger It is also easier to maintain such a code I think Gints Plivna IT Sistçmas, Meríeïa 13, LV1050 Rîga http://www.itsystems.lv/gints/ Kimberly SmithTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] ksmith2@myfirs cc: tlink.netSubject: RE: Firing order of triggers Sent by: [EMAIL PROTECTED] m 2001.12.28 00:51 Please respond to ORACLE-L Why have three triggers? I am not sure if things have changed in 9i but previously there was not way to guarantee which trigger would fire first. -Original Message- Darren Sent: Thursday, December 27, 2001 1:25 PM To: Multiple recipients of list ORACLE-L Happy Holidays to everybody. We have three triggers (all BEFORE EACH ROW on INSERT ) on a table, is there anyway we can control which one fires first,second, third, .. n th Thanks Darren -- Darren Browett P.Eng This message was transmitted Data Administrator using 100% recycled electrons Information and Communication Technology City of Coquitlam P:(604)927 - 3614 E:[EMAIL PROTECTED] --- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Browett, Darren 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kimberly Smith 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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:
Re: What's Oracle Trying to Hide ???
Earlier versions of ORacle did not wrap the packages. Since the source code was available well meaning dba's decided to help Oracle by changing and recompiling these packages. This led to more than one corruption and many a confused support analyst.BTW, the wrapped package bodies supposedly take less space in the shared pool. John [EMAIL PROTECTED] wrote: Steve, nm parameter is used as event name. i.e what to do if that triggering event occurs. You can set either SYSTEMSTATE or PROCESSSTATE or ERRORSTACK to dump those things. Consider a case , you want to dump the errorstack when ORA-00060 (deadlock) occurs. Here you use SID,SERIAL#,60,65535,ERRORSTACK. Triggering event 65535 is IMMEDIATE . Hope this helps. Best Regards, K Gopalakrishnan (408) 934 9310 -Original Message- Sent: Wednesday, December 26, 2001 3:10 PM To: Multiple recipients of list ORACLE-L I generally share your speculation but in the case of dbms_system it is referenced by other sources yet it is undocumented. If you can do a describe on it from SQL*Plus then it should be documented. So dbms_system is undocumented and incomplete but available for our use?? In dbms_system there's a procedure called set_ev with 5 parameters. I'm thinking this is to set events like 10046 and that the si parm is for SID, the se parm is for serial#, the ev parm is the event number, the le parm is the level. But what would the nm parm be and how would it be used? Curiosity may have killed the cat but with sensitive whiskers we should be able to poke our noses into certain places without incident. Here's to growing longer whiskers, Steve -Original Message- Sent: Wednesday, December 26, 2001 3:15 PM To: Multiple recipients of list ORACLE-L I suspect that they are trying to prevent headaches by hiding 'features' that are intended for internal use only. Sometimes a DBA will get hold of some undocumented feature and use it just because it's there, sometimes causing database problems in the process. This type of behavior is common in the PC world. Just take a look at PC magazine and Windoze websites and you will find all kinds of tips and tricks that add no value to the system and usually eat up resources. It seems that this mentality carries over from the Windoze world to those that are new with Oracle, and the DBA starts twisting and turning every knob available. Kind of messes things up for times when one of those undoc features might be useful. This may be pure speculation on my part , but it's my story, and I'm sticking to it. Jared -Original Message- Here's a paste from the $ORACLE_HOME/rdbms/admin/dbmsutil.sql script: Rem dbms_system - database system level commands (moved to Rem prvtutil.sql for more obscurity) Not only do they want to obscure the dbms_system package but I can't find the prvtutil.sql script. Where is this stuff and why do they want to hide it? Any ideas? ...Just found the ./rdbms/admin/prvtutil.plb file and now I'm wishing I could un-wrap this present on boxing day! Eschew Obfuscation, Steve Orr Skiing Bridger Bowl -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve 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!? Get your free @yahoo.com address at http://mail.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: orantdba 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: Exp / Imp Utility Questions
Ken Janusz wrote: I'm doing a DB conversion to 8.1.7 on W2000. I have converted the old tables / data to the new application on my conversion server. Now I have to load it onto a test server at a different site. I will be using tables= parameter to a select group of tables / data. I have not found the answers to my questions in the documentation. 1. Can I use Exp / Imp to just move the data? Or, does this utility move the data and the table structures? If you do not specify IGNORE=Y, if the table already exists (which I presume is the case since you only are interested by the data) the default behaviour is to skip the data. So, the answer is yes if you specify IGNORE=Y. 2. The conversion DB that I have only has the tables, PK's, FK's, and indexes and no triggers, functions, cursors, etc. (they are created by another script). There is no application code attached to this DB. The DB I will be loading to is fully functional - all the PK's, FK's, triggers, functions, et al. Will loading the data from my conversion DB cause problems with the test DB? No. 3. I know I will have to disable the FK's on the test DB. But, what about the triggers on the applicable tables? Should they be disabled? Yes, because imp fires them if they preexist. Anything else I need to know before I get rolling on this? You will probably get lots of error messages in the process ... You may find this useful : http://www.oriole.com/frameindexFS.html and check for the 'All you ever wanted to know about exp and imp' paper. HTH, Stephane Faroult Oriole Ltd -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult 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: What's Oracle Trying to Hide ???
Not quite true. Wrapped packages take same space (in fact more in dictionary cache and same space in library cache). Best Regards, K Gopalakrishnan (408) 934 9310 -Original Message- Sent: Friday, December 28, 2001 6:30 AM To: Multiple recipients of list ORACLE-L Earlier versions of ORacle did not wrap the packages. Since the source code was available well meaning dba's decided to help Oracle by changing and recompiling these packages. This led to more than one corruption and many a confused support analyst.BTW, the wrapped package bodies supposedly take less space in the shared pool. John [EMAIL PROTECTED] wrote: Steve, nm parameter is used as event name. i.e what to do if that triggering event occurs. You can set either SYSTEMSTATE or PROCESSSTATE or ERRORSTACK to dump those things. Consider a case , you want to dump the errorstack when ORA-00060 (deadlock) occurs. Here you use SID,SERIAL#,60,65535,ERRORSTACK. Triggering event 65535 is IMMEDIATE . Hope this helps. Best Regards, K Gopalakrishnan (408) 934 9310 -Original Message- Sent: Wednesday, December 26, 2001 3:10 PM To: Multiple recipients of list ORACLE-L I generally share your speculation but in the case of dbms_system it is referenced by other sources yet it is undocumented. If you can do a describe on it from SQL*Plus then it should be documented. So dbms_system is undocumented and incomplete but available for our use?? In dbms_system there's a procedure called set_ev with 5 parameters. I'm thinking this is to set events like 10046 and that the si parm is for SID, the se parm is for serial#, the ev parm is the event number, the le parm is the level. But what would the nm parm be and how would it be used? Curiosity may have killed the cat but with sensitive whiskers we should be able to poke our noses into certain places without incident. Here's to growing longer whiskers, Steve -Original Message- Sent: Wednesday, December 26, 2001 3:15 PM To: Multiple recipients of list ORACLE-L I suspect that they are trying to prevent headaches by hiding 'features' that are intended for internal use only. Sometimes a DBA will get hold of some undocumented feature and use it just because it's there, sometimes causing database problems in the process. This type of behavior is common in the PC world. Just take a look at PC magazine and Windoze websites and you will find all kinds of tips and tricks that add no value to the system and usually eat up resources. It seems that this mentality carries over from the Windoze world to those that are new with Oracle, and the DBA starts twisting and turning every knob available. Kind of messes things up for times when one of those undoc features might be useful. This may be pure speculation on my part , but it's my story, and I'm sticking to it. Jared -Original Message- Here's a paste from the $ORACLE_HOME/rdbms/admin/dbmsutil.sql script: Rem dbms_system - database system level commands (moved to Rem prvtutil.sql for more obscurity) Not only do they want to obscure the dbms_system package but I can't find the prvtutil.sql script. Where is this stuff and why do they want to hide it? Any ideas? ...Just found the ./rdbms/admin/prvtutil.plb file and now I'm wishing I could un-wrap this present on boxing day! Eschew Obfuscation, Steve Orr Skiing Bridger Bowl -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve 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!? Get your free @yahoo.com address at http://mail.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: orantdba 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!? Get your free @yahoo.com address at http://mail.yahoo.com -- Please see the
RE: Exp / Imp Utility Questions
You can use imp to move just the data. Just set ignore=y so that when the object already exists it will move on. You are right about the FK's and disabling them. As for triggers, it really depends on whether or not you want them to fire... -Original Message- Sent: Friday, December 28, 2001 4:45 AM To: Multiple recipients of list ORACLE-L I'm doing a DB conversion to 8.1.7 on W2000. I have converted the old tables / data to the new application on my conversion server. Now I have to load it onto a test server at a different site. I will be using tables= parameter to a select group of tables / data. I have not found the answers to my questions in the documentation. 1. Can I use Exp / Imp to just move the data? Or, does this utility move the data and the table structures? 2. The conversion DB that I have only has the tables, PK's, FK's, and indexes and no triggers, functions, cursors, etc. (they are created by another script). There is no application code attached to this DB. The DB I will be loading to is fully functional - all the PK's, FK's, triggers, functions, et al. Will loading the data from my conversion DB cause problems with the test DB? 3. I know I will have to disable the FK's on the test DB. But, what about the triggers on the applicable tables? Should they be disabled? Anything else I need to know before I get rolling on this? Thanks, Ken Janusz, CPIM Database Conversion Lead Sufficient Systems, Inc. Minneapolis, MN -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ken Janusz 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kimberly Smith 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: What's Oracle Trying to Hide ???
I havn't tested, and did use the very ambiguous "supposedly", this was in the documentation when they first came out. Thanks for the correction. John [EMAIL PROTECTED] wrote: Not quite true. Wrapped packages take same space (in fact more in dictionarycacheand same space in library cache).Best Regards,K Gopalakrishnan(408) 934 9310-Original Message-Sent: Friday, December 28, 2001 6:30 AMTo: Multiple recipients of list ORACLE-LEarlier versions of ORacle did not "wrap" the packages. Since thesource code was available well meaning dba's decided to help Oracle bychanging and recompiling these packages. This led to more than onecorruption and many a confused support analyst.BTW, the wrappedpackage bodies supposedly take less space in the shared pool.John[EMAIL PROTECTED] wrote: Steve,"nm" parameter is used as event name. i.e what to do if that triggeringevent occurs.You can set either SYSTEMSTATE or PROCESSSTATE or ERRORSTACK to dump thosethings.Consider a case , you want to dump the errorstack when ORA-00060 (deadlock)occurs.Here you use SID,SERIAL#,60,65535,ERRORSTACK.Triggering event 65535 is IMMEDIATE .Hope this helps.Best Regards,K Gopalakrishnan(408) 934 9310-Original Message-Sent: Wednesday, December 26, 2001 3:10 PMTo: Multiple recipients of list ORACLE-LI generally share your "speculation" but in the case of dbms_system it isreferenced by other sources yet it is undocumented. If you can do a describe on it from SQL*Plus then it should be documented. So dbms_system isundocumented and incomplete but available for our use??In dbms_system there's a procedure called "set_ev" with 5 parameters. I'mthinking this is to set events like 10046 and that the "si" parm is for SID, the "se" parm is for serial#, the "ev" parm is the event number, the "le"parm is the level. But what would the "nm" parm be and how would it be used? Curiosity may have killed the cat but with sensitive whiskers we should beable to poke our noses into certain places without incident.Here's to growing longer whiskers,Steve-Original Message-Sent: Wednesday, December 26, 2001 3:15 PMTo: Multiple recipients of list ORACLE-LI suspect that they are trying to prevent headachesby hiding 'features' that are intended for internaluse only.Sometimes a "DBA" will get hold of some undocumentedfeature and use it just because it's there, sometimes causingdatabase problems in the process.This type of behavior is common in the PC world. Just takea look at PC magazine and Windoze websites and youwill find all kinds of "tips" and "tricks" that add no value tothe system and usually eat up resources.It seems that this mentality carries over from the Windozeworld to those that are new with Oracle, and the "DBA" starts twisting and turning every knob available.Kind of messes things up for times when one of thoseundoc features might be useful.This may be pure speculation on my part , but it'smy story, and I'm sticking to it.Jared-Original Message-Here's a paste from the $ORACLE_HOME/rdbms/admin/dbmsutil.sql script:Rem dbms_system - database system level commands (moved toRem prvtutil.sql for more obscurity)Not only do they want to "obscure" the dbms_system package but I can't findthe prvtutil.sql script. Where is this stuff and why do they want to hideit? Any ideas?...Just found the ./rdbms/admin/prvtutil.plb file and now I'm wishing Icould un-"wrap" this "present" on boxing day!Eschew Obfuscation,Steve OrrSkiing Bridger Bowl--Please see the official ORACLE-L FAQ: http:/ /www.orafaq.com--Author: Orr, Steve INET: [EMAIL PROTECTED]Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051San Diego, California-- Public Internet access / Mailing ListsTo REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing)._Do You Yahoo!?Get your free @yahoo.com address at http://mail. yahoo.com --Please see the official ORACLE-L FAQ: http://www.orafaq.com--Author: orantdba INET: [EMAIL PROTECTED]Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051San Diego, California-- Public Internet access / Mailing ListsTo REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other
RE: Firing order of triggers
Actually, you bring up a good point. I never put logic in my triggers. I always use packages. That way, you can modify one piece of logic without affecting the whole trigger. In many ways its much easier to maintain. -Original Message- [EMAIL PROTECTED] Sent: Friday, December 28, 2001 6:15 AM To: Multiple recipients of list ORACLE-L Although there are already so many responses I would like to add one more You can of course put all logic in one trigger and then have control over events Sometimes it is impossible in the trigger because there is limitation of trigger size (32 K) So in that case You have to put logic in packages/procedures and call them in the appropriate order in the trigger It is also easier to maintain such a code I think Gints Plivna IT Sistçmas, Meríeïa 13, LV1050 Rîga http://www.itsystems.lv/gints/ Kimberly SmithTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] ksmith2@myfirs cc: tlink.netSubject: RE: Firing order of triggers Sent by: [EMAIL PROTECTED] m 2001.12.28 00:51 Please respond to ORACLE-L Why have three triggers? I am not sure if things have changed in 9i but previously there was not way to guarantee which trigger would fire first. -Original Message- Darren Sent: Thursday, December 27, 2001 1:25 PM To: Multiple recipients of list ORACLE-L Happy Holidays to everybody. We have three triggers (all BEFORE EACH ROW on INSERT ) on a table, is there anyway we can control which one fires first,second, third, .. n th Thanks Darren -- Darren Browett P.Eng This message was transmitted Data Administrator using 100% recycled electrons Information and Communication Technology City of Coquitlam P:(604)927 - 3614 E:[EMAIL PROTECTED] --- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Browett, Darren 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kimberly Smith 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kimberly Smith 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).
SQL Loader Parfile
Does anyone know how to put more than one control (.ctl) file in a single parfile? Thanks, Ken Janusz, CPIM Database Conversion Lead Sufficient Systems, Inc. Minneapolis, MN -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ken Janusz 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: RE: database administration questions
Evelyn WoodDavid A. BarbourOracle DBA, OCPAISD512-414-1002Boivin, Patrice J [EMAIL PROTECTED]Sent by: [EMAIL PROTECTED]12/27/2001 10:50 AM PSTPlease respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: bcc: Subject: RE: RE: database administration questions A problem with RTFM is when the manual is over 10,000 pages long.Regards,Patrice BoivinSystems Analyst (Oracle Certified DBA) -Original Message-Sent:Thursday, December 27, 2001 2:15 PMTo:Multiple recipients of list ORACLE-LSubject:RE: RE: database administration questionsYeah, once they find out this job isn't a cakewalk, they vanish.For those that:* live for challenge* are afraid of nothing and not intimidated by complexity* make that, 'love complexity' )* can't forget about a problem until it's fixed* willing to RTFM til they drop* design and execute tests to understand how things work* RTFM some more* drive technology. Hey, there's more to a good DBA than databases.* did I mention RTFM?* learn from their mistakes* admit they make mistakes* RTFM to minimize mistakesAll others need not apply.JaredNo guts, no glory ) Kimberly SmithTo: Multiple recipients oflist ORACLE-L [EMAIL PROTECTED] ksmith2@myfirscc: tlink.net Subject: RE: RE: databaseadministration questions Sent by: [EMAIL PROTECTED] m 12/27/01 09:00 AM Please respond to ORACLE-LYou need to find some new cooks then.-Original Message-[EMAIL PROTECTED]Sent: Thursday, December 27, 2001 6:50 AMTo: Multiple recipients of list ORACLE-LIn our shop we've tried the cross training tact. Problem was that thosewhoaccepted the challenge could not take the heat, so they left the kitchen.Damn!!!Dick GouletReply SeparatorAuthor: CHAN Chor Ling Catherine (CSC) [EMAIL PROTECTED]Date:12/26/2001 4:50 PMI quite agree with Kimberly. I used to be a full-fledge Oracle programmerbut wanted to dabble with database administration. I asked my boss whetherIcould be a database administrator. His answer is yes but I still need tomaintain my current systems. Now, I am a databaseadministrator-cum-programmer. I support turnkey projects, automateprocesses for my users, maintain current projects, install/maintaindatabase support Oracle Applications etc.Prepare for lots of OT but what the heck, U will get to learn a lots ofinteresting things in the process New Bees-Original Message-From: Kimberly Smith [mailto:[EMAIL PROTECTED]]Sent: Thursday, December 27, 2001 1:40 AMTo: Multiple recipients of list ORACLE-LSubject:RE: database administration questionsIf you work on a site like I do you could always crosstrain. I am alwayslooking for suckers (um, people) to be my backup. RightnowI use one fromthe Unix team and one from the development team. Its theonly way I get totake vacations and what no. Check with your current DBAandsee if they arewilling to train you while you are off doing your 'real'job. Of course,there are some folks who are worried about job security (orare justassholes)and they would not give you the time of day. You don'twantto learn fromthose folks anyway. The excuse, I'm too busy is notreally valid either.Training someone allowed me to offload some of my work.-Original Message-[EMAIL PROTECTED]Sent: Wednesday, December 26, 2001 5:30 AMTo: Multiple recipients of list ORACLE-LWith your current background, your more likely to land ajobas a networkadministrator LONG before you'll end up in a databaseposition. Access isstilllooked on as a single user system and frankly I haven't runinto any largescaleapplications that use it, period. One of my current tasksis working with aforecasting package that states in the manual that Accessshould only beusedfor the demos. Any other application of the package shoulduse Oracle orDB2.Also a SPC (Statistical Process Control, for those whodon'tknow, don't askfurther) package we're evaluating (actually two of them)won't work withAccesseven for the demos. Therefore, I'd suggest staying withthenetworkingworld.It will be around as long as database administration, ifnotlonger. Youralready trained and certified, and getting a Ciscocertification is no smallfeat. If you really want to move into database admin, yourprobably lookingat2 to 3 years of learning and smaller paychecks as you payyour dues.BTW: A database restore usually takes a lot longer thanfixing a networkoutage,to boot.Dick GouletReply SeparatorAuthor: [EMAIL PROTECTED]Date:12/23/2001 11:15 PM
Training Recommendations
I am looking at my 2002 training schedule and would like to know if you can recommend a class in any of the following dba topics : 1. RMAN Setting and configuring RMAN Backing up using RMAN Recovery using RMAN Note that I have taken the Oracle Backup and Recovery class for 7.3 before they introduced RMAN - therefore I am looking for a RMAN centric class. 2. Oracle Performance Tuning Using and analyzing StatsPack General instance tuning 3. Oracle High Availability Configuration AND Advanced Failover Configurations Standby Databases Parallel Server Replication etc If you are recommending a class please supply the name of the company and possibly the instructor (the same company can have good and bad instructors). Also let me know why you liked the class. Thanks in advance !! _ Patrick J. Howe Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Pat Howe 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: RE: database administration questions
Is she an OCP DBA? ;-) -Original Message- Sent: Friday, December 28, 2001 10:40 AM To: Multiple recipients of list ORACLE-L Evelyn Wood David A. Barbour Oracle DBA, OCP AISD 512-414-1002 Boivin, Patrice J [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 12/27/2001 10:50 AM PST Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] A problem with RTFM is when the manual is over 10,000 pages long. Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) -Original Message- Sent:Thursday, December 27, 2001 2:15 PM To:Multiple recipients of list ORACLE-L Subject:RE: RE: database administration questions Yeah, once they find out this job isn't a cakewalk, they vanish. For those that: * live for challenge * are afraid of nothing and not intimidated by complexity * make that, 'love complexity' ) * can't forget about a problem until it's fixed * willing to RTFM til they drop * design and execute tests to understand how things work * RTFM some more * drive technology. Hey, there's more to a good DBA than databases. * did I mention RTFM? * learn from their mistakes * admit they make mistakes * RTFM to minimize mistakes All others need not apply. Jared No guts, no glory ) You need to find some new cooks then. -Original Message- [EMAIL PROTECTED] Sent: Thursday, December 27, 2001 6:50 AM To: Multiple recipients of list ORACLE-L In our shop we've tried the cross training tact. Problem was that those who accepted the challenge could not take the heat, so they left the kitchen. Damn!!! Dick Goulet Reply Separator Author: CHAN Chor Ling Catherine (CSC) [EMAIL PROTECTED] Date: 12/26/2001 4:50 PM I quite agree with Kimberly. I used to be a full-fledge Oracle programmer but wanted to dabble with database administration. I asked my boss whether I could be a database administrator. His answer is yes but I still need to maintain my current systems. Now, I am a database administrator-cum-programmer. I support turnkey projects, automate processes for my users, maintain current projects, install/maintain database support Oracle Applications etc. Prepare for lots of OT but what the heck, U will get to learn a lots of interesting things in the process New Bees -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve 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).
What's the DBA_DML_LOCKS.LAST_CONVERT column ??
What's the DBA_DML_LOCKS.LAST_CONVERT column ?? In catblock.sql for the DBA_LOCKS view it's described as follows: last_convert - time (in seconds) since last convert completed. What precisely does that mean? (Is it a religious term? :-) Steve Orr Snowing nicely in Montana now and the slopes are calling me. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve 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: What's the DBA_DML_LOCKS.LAST_CONVERT column ??
I think the number of seconds since the lock was established (or converted from/to) in this mode. My guess. Regards, Waleed -Original Message- Sent: Friday, December 28, 2001 1:15 PM To: Multiple recipients of list ORACLE-L What's the DBA_DML_LOCKS.LAST_CONVERT column ?? In catblock.sql for the DBA_LOCKS view it's described as follows: last_convert - time (in seconds) since last convert completed. What precisely does that mean? (Is it a religious term? :-) Steve Orr Snowing nicely in Montana now and the slopes are calling me. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Khedr, Waleed 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).
Upgrade question
List, I plan to upgrade to 9.0.1 soon, and looking through the documentation it is not clear to me whether the 9.0.1 listener needs to be running prior to the upgrade migration utility or not. My thinking is it should be running and tested prior to the upgrade. TIA M.Godlewski
DBA_TAB_MODIFICATIONS - Performance impact?
Title: DDL alter in execute immediate pl/sql procedure - dynamic sql Anybody using the "ALTER TABLE tablename MONITORING;" functionality? Just wondering what the performance impact of implementing the features outlined in MetaLink Doc 102334.1 was. Or is this another performance debate like setting "timed_sadistics= true" was/is? http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOTp_id=102334.1
Re: DDL alter in execute immediate pl/sql procedure - dynamic sql
Hagedorn, Linda wrote: If someone has a few minutes to read through this, I'd be most appreciative. I could use a second set of eyes looking this over. This procedure is designed to maintain a table/sequence map, executed after an import and increment any sequences which have a lower nextval that the max value in the column it's supposed to be matching. Often the sequences are out-of-sync after an import (even full=y and direct=y) and we have to manually adjust them. This is an effort to automate the process. These are the displays and error from the procedure, and the code follows. The problem is in the execute immediate which is doing DDL. It's Oracle 8.1.7 on Solaris so DDL in execute immediate alter is supposed to work. The execute immediate insert does work. Any suggestions or comments are welcome. Thanks, Linda Linda, DDL is forbidden in PL/SQL - except in some EXECUTE IMMEDIATE statement (or when using the older DBMS_SQL package) but as a SINGLE statement. When you are executing your statement you are trying to execute an anonymous block - which happens to contain a DDL statement, which is forbidden in PL/SQL (sorry, looping). I have never tried it but there is no reason why it shouldn't work, an EXECUTE IMMEDIATE within the EXECUTE IMMEDIATE is probably what you shoud try, i. e. something like : my_statement := 'begin' || chr(10) || 'execute immediate ''' || 'fancy DDL here' || '''; end;'; execute immediate :my_statement; Another solution would be to simplify the logic, but I am in Europe and currently a bit tired to suggest something reasonably intelligent. -- Regards, Stephane Faroult Oriole Ltd -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult 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:RE: DBA_TAB_MODIFICATIONS - Performance impact?
Of course Ross. How else do we know how long sadists like you are at work?? :-) Dick Goulet Reply Separator Author: Mohan; Ross [EMAIL PROTECTED] Date: 12/28/2001 12:55 PM LoL timed sadistics -Original Message- Sent: Friday, December 28, 2001 3:36 PM To: Multiple recipients of list ORACLE-L Anybody using the ALTER TABLE tablename MONITORING; functionality? Just wondering what the performance impact of implementing the features outlined in MetaLink Doc 102334.1 was. Or is this another performance debate like setting timed_sadistics = true was/is? http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_datab ase_id=NOT http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_data base_id=NOTp_id=102334.1 p_id=102334.1 !DOCTYPE HTML PUBLIC -//W3C//DTD HTML 4.0 Transitional//EN HTMLHEAD META HTTP-EQUIV=Content-Type CONTENT=text/html; charset=iso-8859-1 TITLEDDL alter in execute immediate pl/sql procedure - dynamic sql/TITLE META content=MSHTML 5.50.4616.200 name=GENERATOR/HEAD BODY DIVSPAN class=229555420-28122001FONT face=Arial color=#ff size=2LoLnbsp; timed sadistics/FONT/SPAN/DIV BLOCKQUOTE dir=ltr style=MARGIN-RIGHT: 0px DIV class=OutlookMessageHeader dir=ltr align=leftFONT face=Tahoma size=2-Original Message-BRBFrom:/B Brian MacLean [mailto:[EMAIL PROTECTED]]BRBSent:/B Friday, December 28, 2001 3:36 PMBRBTo:/B Multiple recipients of list ORACLE-LBRBSubject:/B DBA_TAB_MODIFICATIONS - Performance impact?BRBR/FONT/DIV DIVSPAN class=050582420-28122001FONT face=Courier New size=2Anybody using the ALTER TABLE lt;tablenamegt; MONITORING; functionality?nbsp; Just wondering what the performance impact of implementing the features outlined in MetaLink Doc 102334.1 was.nbsp; Or is this another performance debate like setting timed_sadisticsnbsp;= true was/is?/FONT/SPAN/DIV DIVFONT face=Tahoma size=2/FONTnbsp;/DIV DIVFONT face=Tahoma size=2A href=http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_d atabase_id=NOTamp;p_id=102334.1http://metalink.oracle.com/metalink/plsql/ml2_ documents.showDocument?p_database_id=NOTamp;p_id=102334.1/A/FONT/DIV DIVFONT face=Tahoma size=2/FONTnbsp;/DIV DIVFONT face=Tahoma size=2/FONTnbsp;/DIV DIVFONT face=Tahoma size=2/FONTnbsp;/DIV/BLOCKQUOTE/BODY/HTML -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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 : RE: DBA_TAB_MODIFICATIONS - Performance impact?
LoL timed sadistics Must be a RDBSM. -- Regards, Stephane Faroult Oriole Ltd -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult 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: DBA_TAB_MODIFICATIONS - Performance impact?
I've seen references that it is less than 1% impact, though I haven't tried it myself. You might try Steve Adams' site at www.ixora.com.au. Jared Brian MacLean bmaclean@vcom To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] merce.com cc: Sent by: Subject: DBA_TAB_MODIFICATIONS - Performance impact? [EMAIL PROTECTED] om 12/28/01 12:35 PM Please respond to ORACLE-L Anybody using the ALTER TABLE tablename MONITORING; functionality? Just wondering what the performance impact of implementing the features outlined in MetaLink Doc 102334.1 was. Or is this another performance debate like setting timed_sadistics = true was/is? http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOTp_id=102334.1 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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: Training Recommendations
Pat, thats easy, geekcruises.com. www.geekcruises.com, oracle odyssey, may 2002. i personally will be talking about backup/recovery concepts(which will include rman), oracle 9i data guard(failover, etc), both of those 3 hr presentations. check out the .pdf brochure, i'm sure what you are looking for will be covered. joe Pat Howe wrote: I am looking at my 2002 training schedule and would like to know if you can recommend a class in any of the following dba topics : 1. RMAN Setting and configuring RMAN Backing up using RMAN Recovery using RMAN Note that I have taken the Oracle Backup and Recovery class for 7.3 before they introduced RMAN - therefore I am looking for a RMAN centric class. 2. Oracle Performance Tuning Using and analyzing StatsPack General instance tuning 3. Oracle High Availability Configuration AND Advanced Failover Configurations Standby Databases Parallel Server Replication etc If you are recommending a class please supply the name of the company and possibly the instructor (the same company can have good and bad instructors). Also let me know why you liked the class. Thanks in advance !! _ Patrick J. Howe Oracle DBA -- Joe Testa, Oracle DBA Want to have a good time with a bunch of geeks? Check out: http://www.geekcruises.com/standard_interface/future_cruises.html I'm presenting, when registering drop my name :) -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Joe Testa 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: Training Recommendations
Joe, when people ask questions during these presentations, do you reply: a. RTFM b. Try it yourself and see c. nope ? ;-) --- Joe Testa [EMAIL PROTECTED] wrote: Pat, thats easy, geekcruises.com. www.geekcruises.com, oracle odyssey, may 2002. i personally will be talking about backup/recovery concepts(which will include rman), oracle 9i data guard(failover, etc), both of those 3 hr presentations. check out the .pdf brochure, i'm sure what you are looking for will be covered. joe __ Do You Yahoo!? Send your FREE holiday greetings online! http://greetings.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Paul Baumgartel 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).
Table MONITORING
The DBMS_STATS.GATHER_SCHEMA_STATS procedure (with the GATHER STALE option), which uses the information produced when a table is in MONITORING mode, does not work properly in any current release of Oracle (see bug 1890016 on MetaLink). __ Do You Yahoo!? Send your FREE holiday greetings online! http://greetings.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Paul Baumgartel 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: Training Recommendations
D: all of the above. joe Paul Baumgartel wrote: Joe, when people ask questions during these presentations, do you reply: a. RTFM b. Try it yourself and see c. nope ? ;-) --- Joe Testa [EMAIL PROTECTED] wrote: Pat, thats easy, geekcruises.com. www.geekcruises.com, oracle odyssey, may 2002. i personally will be talking about backup/recovery concepts(which will include rman), oracle 9i data guard(failover, etc), both of those 3 hr presentations. check out the .pdf brochure, i'm sure what you are looking for will be covered. joe __ Do You Yahoo!? Send your FREE holiday greetings online! http://greetings.yahoo.com -- Joe Testa, Oracle DBA Want to have a good time with a bunch of geeks? Check out: http://www.geekcruises.com/standard_interface/future_cruises.html I'm presenting, when registering drop my name :) -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Joe Testa 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).
ORA-00904 on a valid view when using count(*)
we have view named gain_view which was created with this sql SELECT co.name, sec.alias, sec.security_name, sec.security_symbol, sq.open, sq.high, sq.low, sq.close, sq.previous, sq.last_trade_price, sq.total_volume, sq.total_value, (((sq.last_trade_price - sq.previous)/sq.previous) * 100) AS perc_change, (sq.last_trade_price - sq.previous) AS value_gain FROM trade_quotes_vw sq, security sec, company co WHERE sq.security_symbol = sec.security_symbol AND sec.company_id = co.company_id AND NVL(sq.previous,0) 0 AND (NVL(sq.last_trade_price,0) - NVL(sq.previous,0)) 0 ORDER BY perc_change DESC when we do a select * from gain_view we get the correct results but when we use select count(*) from gain_vw we are getting ORA-00904 any ideas? -- Maria Aurora VT de la Vega (OCP) Database Specialist Philippine Stock Exchange, Inc. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Maria Aurora VT de la Vega 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).
