Re: avoid duplicate sql
Can you change from a sql statement to an anonymous pl/sql block? You can trap for dup val on index or check for the records existence prior to trying to insert the new record. james --- Tatireddy, Shrinivas (MED, Keane) [EMAIL PROTECTED] wrote: Hi lists i need help in writing a sql query: i am trying to capture the session information from v$session and inserting the same into someother table xyz with columns username, logon_time. (scott logged in at 10:30 hrs) i put this script in cron. it runs every 30 minutes. but i need the values shoudld not be duplicated. Every time the shell script runs, it inserts values into XYZ. table. Here i need your help. The values should not be duplicated that are being insreted into xyz. since username,logon_time are composite primary key, system will not accept duplicates. Because, in the v$session, if the user stays more than 30 minutes, his values will be sent to xyz table and as already xyz has that row (inserted before 30 minutes by shell), system will deny to insert the duplicate the value into xyz. any scripts? scripts? thanx in advance srinivas -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tatireddy, Shrinivas (MED, Keane) 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!? Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: james ellis 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: temp problem when rebuilding Index
You can also increase your SORT_AREA_SIZE parameter in the initDBNAME.ora. This will allow more of your sorting to be done in memory rather on disk. This will also help speed up the process. --- Raghu Kota [EMAIL PROTECTED] wrote: Hi Friends Iam rebuilding my 6Gb index, But Iam getting problem with TEMP tablespace. What is solution for this?? iam using alter index index_name rebuild tablespace_name; ORA-1652: unable to extend temp segment by 128 in tablespaceTEMP Any Ideas?? Thanks Raghu. _ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Raghu Kota 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 personalized email addresses from Yahoo! Mail http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: james ellis 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: Drop schema and all related objects
connect as sys or system. drop user username cascade; The user and all objects will be removed. james --- Mujeeb Chowdhry [EMAIL PROTECTED] wrote: Hi DBA's, How can I drop schema (user and all it's objects). I have one main user in the application and wants to drop user and all related objects. Can anyone help me in this regard. Thanks Mujeeb Chowdhry Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mujeeb Chowdhry 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! Auctions - buy the things you want at great prices http://auctions.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: james ellis 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: NOLOGGING creates txns in redo/archive logs
This is normal. When a transaction is set to nologging, minimal redo is written because of Data Dictionary changes that are occurring. James --- [EMAIL PROTECTED] wrote: I have been testing logminer and noticed that when I alter a table to NOLOGGING, txns are still recorded in the redo/archive logs. I thought NOLOGGING was NOT supposed to do this. Anyone know if this is expected behavior? At a recent 8i new features class, I explicitly asked the instructor this question and he stated that nologging will not cause any records to be written to the redo/archive logs. TIA. John Fedock iXL, Inc. mailto:[EMAIL PROTECTED] [EMAIL PROTECTED] http://www.ixl.com http://www.ixl.com/ -- 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). __ Do You Yahoo!? Yahoo! Auctions - buy the things you want at great prices http://auctions.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: james ellis 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: Insert from a table to another
You can do a simple insert/select statement within pl/sql declare begin insert into table_a (select * from table_b); end; / if all the columns are in the same order in both tables. If not list the columns out. James --- [EMAIL PROTECTED] wrote: Any one whom can give me a good advice, on a pl/sql procedure, that delete data from table a, then runs a query from table b and and that insert this query into table a. Pleas help me with a good example. Thanks Roland Sköldblom -- 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). __ Do You Yahoo!? Yahoo! Auctions - buy the things you want at great prices http://auctions.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: james ellis 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: index query
You could use the alter index rebuild option. This keeps the old index available for queries until the new index has been created. You just need to make sure you have double the space of the object you are rebuilding when using this technique. James --- Seema Singh [EMAIL PROTECTED] wrote: Hi Dbas I have following query regarding index. 1)how to defragment the index except export/drop/create/import option 2)I have seen my index tablespace it is growing fast as compare to data tablespace even. Please advice what I do. Thanks -seema _ Get your FREE download of MSN Explorer at http://explorer.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seema Singh 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! Auctions - buy the things you want at great prices http://auctions.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: james ellis 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: FOREIGN KEY to a remote table. Is it possible?
This is not possible. It is possible to enforce referential integrity via a trigger over a database link. James --- Andor Gyula [EMAIL PROTECTED] wrote: HI, Is is possible to create a FOREIGN KEY constraint that references to a remote table through DATABASE LINK? Thanks in advance Gyula -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andor Gyula 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 email at your own domain with Yahoo! Mail. http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: james ellis 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: Insert
Yes inserting into a table with an index does cause degradation in the process, but it is very unrealistic to drop and rebuild the index every time you do an insert, unless it is a batch process that runs when no other processing is being done. Also, deleting from the index causes the index to be very fragmented and eventually you will need to rebuild it to reclaim unused space. james --- Kevin Kostyszyn [EMAIL PROTECTED] wrote: Hi DBA's, One of my developers just asked me a question, and honestly I don't know what he's talking about. But, it made me think of my own that I do know the answer to or at least I used to:( Anyway, when doing an insert into a table, doesn't it increase performance to delete any indexes, do the insert then rebuild the index. Or do you just have to disable the index? Also, does this work the same for delete? Also...is there anyway to guestemate how long an insert is going to take? Sincerely, Kevin Kostyszyn DBA Dulcian, Inc www.dulcian.com [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kevin Kostyszyn 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 email at your own domain with Yahoo! Mail. http://personal.mail.yahoo.com/?.refer=text -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: james ellis 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: Script to know the size of each table in a schema ?
Query DBA_SEGMENTS to get this information on table sizes. You can query by owner and segment_type to get schema and table information. --- Bambang Setiawan [EMAIL PROTECTED] wrote: Dear Listers, I just made script to know the size of datafile in a tablepace , but I still have no idea to make a script to know the size of each table in a schema ? is there anyone who has the script ? furthermore , I need some information about relationship diagram which describe the relation among tables/view to calculate storage of objects ( i.e tablespace , datafile , segment , extent ) . thanks in advance : ) =bambang= Bambang Setiawan -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bambang Setiawan 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! Auctions - Buy the things you want at great prices. http://auctions.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: james ellis 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 not open DB :(
Make sure the file specified in the error exists. There may be a problem reading the disk the file is on. --- andrey [EMAIL PROTECTED] wrote: Dear list ! please help : SVRMGR alter database open ; alter database open * ORA-01110: data file 2: 'D:\ORACLE\ORADATA\VTP2\RBS01.DBF' ORA-01115: IO error reading block from file 2 (block # 1) ORA-27070: skgfdisp: async read/write failed OSD-04016: Error queuing an asynchronous I/O request. O/S-Error: (OS 23) Data error (cyclic redundancy check). SVRMGR What do i do ? __ Do You Yahoo!? Yahoo! Auctions - Buy the things you want at great prices. http://auctions.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: james ellis 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: On-Call - terms, conidtions and perks!
Hi Sean, I have worked in two environments that required 24X7 support. My first job as a DBA we had a 4 man team and rotated the pager every week. So each DBA was on-call once a month. All we carried was a pager. We all had personal computers at home, so we really didn't need a laptop. If the on-call DBA needed to go out of town or something he made arrangements before hand to get his rotation covered. There was no monetary supplement for being on-call. My manager kind of lumped it in our salaries and supplemented comp time in. (3Yrs) My second experience was with a larger organization and they equiped on-call personnel with a laptop, pager and cell phone. There was a rotation every week depending on how many people were in your group. They actually paid on-call personnel $125/wk whether you were called or not. So having the laptop and the supplemental income really made that experience a little better. (1yr) I hope that helps --- "O'Neill, Sean" [EMAIL PROTECTED] wrote: Hi Folks, Our company has inidicated it's heading for 24 x 7 operation. So informal talks are taking place about the new "challenges" this brings regarding having staff on call to support IT systems. I would very much appreciate your feedback if you are in an on-call environment as to what the terms, conditions, and perks (if any) you get for same. Some specific information I'm looking for is: - How frequently are you on-call and for how long and to cover what hours. e.g. one week a month from 22:00 - 06:00. - What tools are you given to support this process. e.g. pager, laptop for remote dial-in, cell phone. - Whats the procedure if you "get the call". Do you dial in initially to try to resolve, that failing go on site, in other words the escalation process - What renumeration or other benefits do you get for being on call. - Are there additional 'benefits' if you are called whilst being on call. I'll treat any replies where requested with strict confidentiality. Sean :) Rookie Data Base Administrator [0%] OCP Oracle8i DBA [0%] OCP Oracle9i DBA Organon (Ireland) Ltd. http://www.organon.ie E-mail: [EMAIL PROTECTED] [subscribed: Digest Mode] Visit: http:\\groups.yahoo.com/group/Oracle-OCP-DBA "They tell me nothing, but expect me to know everything!" -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: O'Neill, Sean 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 personalized email addresses from Yahoo! Mail - only $35 a year! http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: james ellis 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).
