DB Triggers vs Stored Procedures
Hi All I would like to know the difference between using the Stored procedures in DB Triggers and writing the code directly in the DB Trigger. Which would be better to use and what r the advantages. Rgds Sathya -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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: Database up longer that host?
Try this out. thisqry is working well for hrs and mins. hop u modify this qry get for hrs also wishes select (((sysdate-startup_time)*60*24-mod((sysdate-startup_time)*60*24,60))/60)HRS , round(mod((sysdate-startup_time)*60*24,60))MIN from v$instance Wishes Sathyanarayanan |+---> || "Stephen Andert" | || | || | || 03/12/2002 09:28 | || Please respond to| || ORACLE-L | || | |+---> >--| | | | To: Multiple recipients of list ORACLE-L | | <[EMAIL PROTECTED]> | | cc: (bcc: Sathyanaryanan K/VGIL) | | Subject: RE: Database up longer that host? | >--| Govind, Actually, what I want it the same format as I have, I just want the numbers to match (or fall within) the numbers reported by the unix uptime command for example "up 4 days, 21:08 hours". In my case, the unix uptime is saying the host was last restarted after the database startup_time reported in v$instance. Stephen >>> [EMAIL PROTECTED] 12/02/02 07:43PM >>> You may use following query will give you the uptime in hours and in minutes. select sysdate, startup_time, round( (sysdate - startup_time) *24*60 ,0 ) uptime_in_minutes, round( (sysdate - startup_time) *24 ,0) uptime_in_hours from v$instance SYSDATESTARTUP_TIME UPTIME_IN_MINUTES UPTIME_IN_HOURS -- -- - --- 20021202203918 20021202044608 953 16 Hope this is what you wanted. -Original Message- Sent: Monday, December 02, 2002 8:34 PM To: Multiple recipients of list ORACLE-L On Mon, 2 Dec 2002, Stephane Faroult wrote: > Stephen Andert wrote: > > > > I use a script named db_uptime.sql (I think I got it from the list here) > > to calculate how long the database has been up. The output compares > > nicely to the unix uptime command. > >I hope that the query doesn't come from the list, because it is > wrong. The error is to apply floor() before multiplying by 24 or 60 - > you have tremendous rounding errors. > My own database has not been up long enough to be 100% sure about it but > I believe the following to be correct : Hi: Neither of the scripts works for me. Try this? select 'Host Name : '||host_name|| chr(10)|| 'Instance Name : '||instance_name|| chr(10)|| 'Uptime : ' ||floor(xx)||'days ' || floor( 24 * (xx - floor(xx)) ) || 'hours ' || round( 60 * (24 * xx - floor(24 * xx))) || 'minutes ' from ( select host_name,instance_name ,(sysdate-STARTUP_TIME) xx from v$instance ) / Meg Crocker -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Meg Crocker 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: <[EMAIL PROTECTED] 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephen Andert 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 info
Re: Effect of Upgrading O/S to the 817 database !!!
If ur upgrading from nt 5 to 2000 then he first opt should work. Just install oracle on w2000 and start up the db coz the services are n nt. but if ur goin in for a fresh install of w2000, it is better to create a fresh db and import. Alternatively u can create a db with the same config of existig db and copy the old db folder with that of ur new one. hope this should work. wishes. Regards, Sathyanarayanan |+---> || "Jackson | || Dumas" | || | || | || 29/11/2002 | || 18:33| || Please | || respond to | || ORACLE-L | || | |+---> >--| | | | To: Multiple recipients of list ORACLE-L | | <[EMAIL PROTECTED]> | | cc: (bcc: Sathyanaryanan K/VGIL) | | Subject: Effect of Upgrading O/S to the 817| | database !!! | >--| Hi We are currently running Oracle 817 database on a Windows NT, version 5, service pack 6. We need to upgrade O/S to Windows 2000. What should we do on the database side, do we need to do a new Oracle 817 software installation after upgrading O/S and try to startup the database or do we need to do everything from scratch, i.e. install software, create database and import ? I tought this should not have an effect on the database, if that the case, do we then need to just try to startup the database after O/S upgrade ? Please help ...your response will be highly appreciated. Desperado ___ http://www.webmail.co.za the South-African free email service NetWiseGurus.Com Portal - Your Own Internet Business Today! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jackson Dumas 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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: SQL tuning help
check out the status in v$sess Regards, Sathyanarayanan |+---> || "Sergei" | ||| || | || 27/11/2002 | || 00:24| || Please | || respond to | || ORACLE-L | || | |+---> >--| | | | To: Multiple recipients of list ORACLE-L | | <[EMAIL PROTECTED]> | | cc: (bcc: Sathyanaryanan K/VGIL) | | Subject: SQL tuning help | >--| Hello everybody, I have the following query that runs every week. UPDATE tmp_brian_metareward1 tmp SET offers_seen = (SELECT count(f.fastcash_id) FROM metareward.fastcash f WHERE f.subsite_id = tmp.subsite_id and attempt >= trunc(sysdate-1) and attempt < trunc(sysdate) group by tmp.subsite_id); This week it began to hang and I can't figure out why. No changes were made to a database. Please advise me on how I can tune it, which hints to add, or anything else I can do. Thank you Sergei -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sergei 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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: help with connecting to sqlplus
Just check for the Oracle services in Control Panel-> Services. ur Instance,TNS should be started. if not start these services and try connecting. Regards, Sathyanarayanan |+---> || john | || | || | || 23/11/2002 | || 22:43| || Please | || respond to | || ORACLE-L | || | |+---> >--| | | | To: Multiple recipients of list ORACLE-L | | <[EMAIL PROTECTED]> | | cc: (bcc: Sathyanaryanan K/VGIL) | | Subject: help with connecting to sqlplus | >--| ver 8i on windows NT4 chose the Typical install in 8i during installation hence i guess a starter db is created by the installation via svrmgrl did startup trying to connect to sqlplus both gui as well as command line (on the same server box itself) tried with just sqlplus and it challenges for Enter user-name: i entered sys / as sysdba asks for password: i entered change_on_install error ORA-01033: Oracle initialization in progress. waited for 20 minutes, same error. did a shutdown and startup again, same error --- other clues/messages shutdown, shutdown normal show this Error ORA-01507: database not mounted connected to sqlplus with nolog and creating a sample table with CREATE TABLE or do a DESCRIBE TABLE for this shows "not connected" __ Do you Yahoo!? Yahoo! Mail Plus ? Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: john 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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: Storing of number datatype in table
Thank u list for ur imm resp. It was the prob with numwidth. Now I have set the num width to 25 and s working fine. But whenever i stat the sql the default is set to 9. how do i change the def numwidth?? Regards, Sathyanarayanan |+---> || "Arup Nanda" | || | || | || 22/11/2002 | || 19:38| || Please | || respond to | || ORACLE-L | || | |+---> >--| | | | To: Multiple recipients of list ORACLE-L | | <[EMAIL PROTECTED]> | | cc: (bcc: Sathyanaryanan K/VGIL) | | Subject: Re: Storing of number datatype in | | table | >--| The number is more than the numwidth specified. Try this SQL> set numwidth 13 SQL> select trn_id from trnid; Your numwidth is perhpas defined as 9; so anything of more precision is displayed as exponetial notation; internally all numbers are stored the same. HTH Arup Nanda www.proligence.com - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Friday, November 22, 2002 6:23 AM > Dear List > > Have a look at the sql !!! > > SQL> create table trnid > 2 ( trn_id number(10)); > Table created. > > SQL> insert into trnid(trn_id) values('11');/* 10 1's inserted*/ > 1 row created. > > SQL> insert into trnid(trn_id) values('1');/* 9 1's inserted*/ > 1 row created. > > SQL> commit; > Commit complete. > > SQL> select trn_id from trnid; > >TRN_ID > - > 1.111E+09 > 1 > > can anyone tell why the number(10) is stored in exp format > > Regards, > > Sathyanarayanan > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: > 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). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Arup Nanda 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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: OEM Config Assistant
some rights has to granted to the user n for setting up the repository u may need to modify ur register entry for the mgmt svr. check out the installation notes to the set the values of key in the registry. Regards, Sathyanarayanan |+> || "Mike Sardina"| || | ||| || 23/11/2002| || 01:29 | || Please respond| || to ORACLE-L | ||| |+> >--| | | | To: Multiple recipients of list ORACLE-L | | <[EMAIL PROTECTED]> | | cc: (bcc: Sathyanaryanan K/VGIL) | | Subject: OEM Config Assistant | >--| Using OEM Configuration Assistant 2.2, I tried to set up a Management Server/repository, etc. and through the gui I created a user with a password, etc. Now when I try to log into the Management Server with that username/password, I get "invalid credentials". Through sqlplus I can log into that username/password though. Any ideas on what the problem could be? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mike Sardin 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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).
Storing of number datatype in table
Dear List Have a look at the sql !!! SQL> create table trnid 2 ( trn_id number(10)); Table created. SQL> insert into trnid(trn_id) values('11');/* 10 1's inserted*/ 1 row created. SQL> insert into trnid(trn_id) values('1');/* 9 1's inserted*/ 1 row created. SQL> commit; Commit complete. SQL> select trn_id from trnid; TRN_ID - 1.111E+09 1 can anyone tell why the number(10) is stored in exp format Regards, Sathyanarayanan -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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).