Re: Oracle FM interpretation of locking behavior
If I understand what you are asking, a simple test will tell you. >>> Session A SQL> create table t2 (x number); Table created. SQL> insert into t2 values (1); 1 row created. SQL> insert into t2 values (2); 1 row created. SQL> commit; Commit complete. SQL> update t2 set x=4 where x=1; 1 row updated. SQL> Notice No commit; >>>>>>> Session B SQL> update t2 set x=4 where x=2; 1 row updated. SQL> update t2 set x=4 where x=1; >>> this waits for the commit/rollback in session A Robert Pegram Oracle Certified DBA (8i,9i) --- "Jesse, Rich" <[EMAIL PROTECTED]> wrote: > Hey all, > > After converting three columns on a multi-million > row table from NULLs to > SPACEs, the devs found one other program that pops > NULLs into those columns > (sigh). So, with only 150 rows needing updating, my > knee-jerk reaction was > to do a simple: > > UPDATE mytable > SET mcol1 = ' ' > WHERE mcol1 IS NULL; > > ...and repeat for "mcol2" and "mcol3". Since this > is a heavy table for us > (Time/Attendance), I'm wondering about locking, > since the UPDATEs won't be > using an index because of the "IS NULL". When I > RTFM for 8.1.7, I found > this: > > --- > The locking characteristics of INSERT, UPDATE, > DELETE, and SELECT ... FOR > UPDATE statements are as follows: > > The transaction that contains a DML statement > acquires exclusive row locks > on the rows modified by the statement. Other > transactions cannot update or > delete the locked rows until the locking transaction > either commits or rolls > back. > > The transaction that contains a DML statement does > not need to acquire row > locks on any rows selected by a subquery or an > implicit query, such as a > query in a WHERE clause. A subquery or implicit > query in a DML statement is > guaranteed to be consistent as of the start of the > query and does not see > the effects of the DML statement it is part of. > > A query in a transaction can see the changes made by > previous DML statements > in the same transaction, but cannot see the changes > of other transactions > begun after its own transaction. > > In addition to the necessary exclusive row locks, a > transaction that > contains a DML statement acquires at least a row > exclusive table lock on the > table that contains the affected rows. If the > containing transaction already > holds a share, share row exclusive, or exclusive > table lock for that table, > the row exclusive table lock is not acquired. If the > containing transaction > already holds a row share table lock, Oracle > automatically converts this > lock to a row exclusive table lock. > --- > > To me, this says that the row locks will only be > placed on the affected rows > and not every row in the table, in addition to the > RX lock on the table. Is > this correct? I guess I'm looking for evidence that > I could or could not > update this table during the day. > > Thanks! > > Rich Jesse > > Rich Jesse System/Database > Administrator > [EMAIL PROTECTED] Quad/Tech > International, Sussex, WI USA > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: Jesse, Rich > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 > http://www.fatcity.com > San Diego, California-- Mailing list and web > hosting services > - > To REMOVE yourself from this mailing list, send an > E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of > 'ListGuru') and in > the message BODY, include a line containing: UNSUB > ORACLE-L > (or the name of mailing list you want to be removed > from). You may > also send the HELP command for other information > (like subscribing). > __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Robert Pegram 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: OT: Web hosting
I have been thinking of using ipowerweb. http://www.ipowerweb.com/ Rob Pegram --- [EMAIL PROTECTED] wrote: > Sorry for the off topic, but I don't have any one > locally to ask this of. > > My co-workers that are developers tend to be > somewhat MScentric. > > Can anyone recommend a good, inexpensive web hosting > company > that runs on open source: Linux, Perl, MySQL, PHP, > etc.? > > This is to be for my personal use, and I'm trying to > get good, and cheap. > :) > > Thanks, > > Jared > __ Do you Yahoo!? Free Pop-Up Blocker - Get it now http://companion.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Robert Pegram 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: Solaris/8.1.7/LOBSEGMENT
alter table table_name modify lob (lob_column) (storage (maxextents unlimited)); Look in dba_lobs if you are unsure of the table_name the lobsegment belongs to. Rob Pegram Oracle Certified DBA --- "Vergara, Michael (TEM)" <[EMAIL PROTECTED]> wrote: > Hi all! > > I'm getting a report that a LOBSEGMENT has reached > MAXEXTENTS. > How do I modify this? ALTER LOBSEGMENT doesn't > work. I'm still > R'ing the FMs, but I thought I'd ask the list too. > > Thanks, > Mike > > --- > === > Michael P. Vergara > Oracle DBA > Guidant Corporation > > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.net > -- > Author: Vergara, Michael (TEM) > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 > http://www.fatcity.com > San Diego, California-- Mailing list and web > hosting services > - > To REMOVE yourself from this mailing list, send an > E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of > 'ListGuru') and in > the message BODY, include a line containing: UNSUB > ORACLE-L > (or the name of mailing list you want to be removed > from). You may > also send the HELP command for other information > (like subscribing). > __ Do you Yahoo!? Yahoo! Platinum - Watch CBS' NCAA March Madness, live on your desktop! http://platinum.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Robert Pegram 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: Parallel Rollbacks
Look at the parameter fast_start_parallel_rollback Also check out metalink Note:144332.1 Parallel query servers get 100% cpu Rob Pegram Oracle Certified DBA - 8i, 9i --- Steve Perry <[EMAIL PROTECTED]> wrote: > I hope somebody can provide me with more information > on parallel rollbacks. > I've checked metalink and google, but haven't found > much information. I read > that starting in 8i, SMON can be used to clean up a > process after it's been > killed. > > Questions first, story last. > > What criteria is used to decide if PMON or SMON is > used? > Is SMON used only for parallel rollbacks, otherwise > PMON is used? > Why was there so many archive logs generated to > rollback the transaction? I > wasn't expecting that. > Is the last query adequate to monitor the progress > or is there a better way? > Are there any init.ora parameters that I can set to > change the behaviour or > improve performance? > > Thanks, > Steve > > --- > SAP on Oracle 8.1.7.4.1 on Win NT 4.0 > > The other evening, the SAP Basis admin killed a long > running process that > hadn't generated much redo (600 meg), but after he > killed it, it generated > 2.5 gig of redo in about an about an hour. > > I got called about 20 minutes after he did it > because the system started > running slow. > I logged in and ran this query to see if there were > any open transactions, > but nothing came back. > > SELECT vs.username , vs.osuser > , r.name rollback_segment , vt.used_ublk > , vt.used_urec , vt.start_time > , vs.sid , vs.serial# > , p.spid process > FROM v$transaction vt > , v$rollnamer > , v$session vs > , v$process p > WHERE vt.addr = vs.taddr and vt.xidusn = r.usn > and p.addr = > vs.paddr > order by r.name > / > > I kept seeing the logs switches, so I started > looking in v$session_wait and > saw the following events. > > EVENT > > PX Deq: Txn Recovery Start > PX Deq: Txn Recovery Start > PX Deq: Txn Recovery Start > Wait for stopper event to be increased > > Metalink pointed me to: > V$fast_start_servers > v$fast_start_transactions > > From there, I ran this (not sure if the joins are > correct) that allowed me > to watch the process slowly comple. > > SELECT T.USN > -- , R.NAME > , USERNAME > -- , SERIAL# > , TERMINAL > , PROGRAM > , T.STATE > , ROUND (UNDOBLOCKSDONE / UNDOBLOCKSTOTAL * > 100, 1 ) PCT_DONE > , T.UNDOBLOCKSDONE > , T.UNDOBLOCKSTOTAL > , T.SLT > , T.SEQ > , T.PID > , T.CPUTIME > -- , T.PARENTUSN > -- , T.PARENTSLT > -- , T.PARENTSEQ > from v$fast_start_transactions T > , v$TRANSACTION R > , v$process p > WHERE T.PARENTUSN = R.XIDUSN > and T.PARENTSLT = R.XIDSLOT > and T.PARENTSEQ = R.XIDSQN > and t.pid= p.pid > / > > --output from query > >USN Oracle User TERMINAL > PROGRAM > STATE PCT_DONE UNDOBLOCKSDONE > UNDOBLOCKSTOTALSLT > SEQPIDCPUTIME PARENTUSN PARENTSLT > PARENTSEQ > -- > - - > --- -- -- > --- -- --- > --- -- -- -- -- > -- > 14 SYSTEM SATSAP26 > ORACLE.EXE (P000) > RECOVERING 98.8 633522 > 641130 89 > 207166 17 1 0 0 >0 > > > > > > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.net > -- > Author: Steve Perry > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 > http://www.fatcity.com > San Diego, California-- Mailing list and web > hosting services > - > To REMOVE yourself from this mailing list, send an > E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of > 'ListGuru') and in > the message BODY, include a line containing: UNSUB > ORACLE-L > (or the name of mailing list you want to be removed > from). You may > also send the HELP command for other information > (like subscribing). > __ Do you Yahoo!? Yahoo! Shopping - Send Flowers for Valentine's Day http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.oraf
Re: Unsetting OPTIMAL in rollback segments
> space management may generate > > undo to do a coalesce. If the > > amount of undo we generate > > cannot fit in the current > > extent we will get a 1581 as we > > are now trying to use the > > extent that we are trying to > > add." > > > > Suggestions for resolving the > > problems are to make the > > INITIAL EXTENTS a large number > > ( maybe even set minextents = > > maxextents), and unset OPTIMAL > > > > > > Regards > > Tom Villane > > Oracle Support Metalink Analyst > > > > > > > > > -- > 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!? LAUNCH - Your Yahoo! Music Experience http://launch.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Robert Pegram 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: Reverse Engineering Triggers
Steve, Here is a korn shell script I use. It also looks at dba_trigger_cols. I was trying to do the same thing a while back, but most of the scripts I found didn't look at dba_trigger_cols. This view becomes important if a trigger does something like 'before update of on '. I will also use export sometimes if the trigger is simple. HTH Rob Pegram Oracle Certified DBA #!/bin/ksh # # Purpose: Create a backup of the existing triggers. A file will be created # in ./bu with the source. Use it before # Create or Replace to save a copy of the existing. # If you want all the triggers, change the trig_cursor to # where owner not in ('SYS','SYSTEM); and take out of this # shell script. Create another one without owner name do/done, etc # # usage dobupkg # # prereq's: 1. mkdir bu from the directory you are #going to run this from #2. Create a file allpkg.lst owner name # cat alltrg.lst|while read owner name do echo $owner $name sqlplus -s / < wrote: > Anyone have a handy-dandy script to create a script > which recreates > triggers? (By querying user/dba_triggers.) > > Member of the Wheel Reinvention Prevention Society, > Steve Orr > -- > 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!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Robert Pegram 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: order by in subquery workaround
; also send the HELP command for other information > (like subscribing). > > > > === > De informatie verzonden in dit e-mailbericht is > vertrouwelijk en is > uitsluitend bestemd voor de geadresseerde. > Openbaarmaking, > vermenigvuldiging, verspreiding en/of verstrekking > van deze informatie aan > derden is, behoudens voorafgaande schriftelijke > toestemming van Ernst & > Young, niet toegestaan. Ernst & Young staat niet in > voor de juiste en > volledige overbrenging van de inhoud van een > verzonden e-mailbericht, noch > voor tijdige ontvangst daarvan. Ernst & Young kan > niet garanderen dat een > verzonden e-mailbericht vrij is van virussen, noch > dat e-mailberichten > worden overgebracht zonder inbreuk of tussenkomst > van onbevoegde derden. > > Indien bovenstaand e-mailbericht niet aan u is > gericht, verzoeken wij u > vriendelijk doch dringend het e-mailbericht te > retourneren aan de verzender > en het origineel en eventuele kopieën te verwijderen > en te vernietigen. > > Ernst & Young hanteert bij de uitoefening van haar > werkzaamheden algemene > voorwaarden, waarin een beperking van > aansprakelijkheid is opgenomen. De > algemene voorwaarden worden u op verzoek kosteloos > toegezonden. > = > The information contained in this communication is > confidential and is > intended solely for the use of the individual or > entity to whom it is > addressed. You should not copy, disclose or > distribute this communication > without the authority of Ernst & Young. 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. > Ernst & Young does not > guarantee that the integrity of this communication > has been maintained nor > that the communication is free of viruses, > interceptions or interference. > > If you are not the intended recipient of this > communication please return > the communication to the sender and delete and > destroy all copies. > > In carrying out its engagements, Ernst & Young > applies general terms and > conditions, which contain a clause that limits its > liability. A copy of > these terms and conditions is available on request > free of charge. > === > > > > > > > -- > === message truncated === __ Do You Yahoo!? Sign up for SBC Yahoo! Dial - First Month Free http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Robert Pegram 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: range checking ??? URGENT
If you are on 8i (I know it works on 8.1.6 and 8.1.7), you can use select case: SQL> select * from junk; X -- 10 15 20 25 SQL> select x, (case when x < 11 then 'small' when x < 21 then 'med' when x < 31 then 'large' end) from junk 2 / X (CASE -- - 10 small 15 med 20 med 25 large SQL> HTH Rob Pegram Oracle Certified DBA --- Leslie Lu <[EMAIL PROTECTED]> wrote: > Hi all, > > Can decode work on a range, like if Code is > 100 > and > < 200, then name is A; if code>200 and code<300, > then > name is B; I have about 20 ranges to check. If > decode cannot handle that, what's an easy way to do > that? > > Thank you! > > Leslie > > __ > Do You Yahoo!? > Find the one for you at Yahoo! Personals > http://personals.yahoo.com > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: Leslie Lu > 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!? Find the one for you at Yahoo! Personals http://personals.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Robert Pegram 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: Export via pipe ksh script
Is this what you are looking for? #!/bin/ksh mknod /tmp/exp_pipe p compress < /tmp/exp_pipe > export.dmp.Z & exp file=/tmp/exp_pipe userid... log. Rob Pegram Oracle Certified DBA --- Thomas Jeff <[EMAIL PROTECTED]> wrote: > Could someone please send me a ksh script for doing > imp/exp via a pipe. > We're running AIX 4.3.3. > > Thanks! > > > Jeffery D Thomas > DBA > Thomson Information Services > Thomson multimedia Inc. > > Email: [EMAIL PROTECTED] > DBA Quickplace: http://gkmqp.tce.com/tis_dba > > > > __ 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: Robert Pegram 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: Recovery scenario query
Sean, First of all, datafiles actually belong to a tablespace - not a schema. 1. You could place the tablespace into read-only mode, take a backup of the tablespaces datafile(s), then no additional backups are required for those datafile(s) (and you are assured that no one updates the data). 2. In general, to recover from media failure, you would have to restore the datafile from some backup, and apply all the archived redo logs since the backup. HTH Rob Pegram Oracle DBA --- "O'Neill, Sean" <[EMAIL PROTECTED]> wrote: > We perform disk-to-disk offline backups on a nighlty > basis. Database is > running in archived log mode. Due to space > contraints (don't ask =:-[ ) we > are currently forced to backup only some of the data > files. The data files > excluded belong to specific schema owners and are > not being updated, or so > I'm informed =:-0. I'm trying to get my head around > what the recovery > implications are if some of the data files not > backed up have being updated > and a recovery is required?. I expect it depends on > what the recovery > scenario is. So whats worst case scenario and what > would my options, if any > be?. > > > Oracle 7.3.3, 8.0.5, 8.1.7 > NT4, W2K > - > Seán O' Neill > Organon (Ireland) Ltd. > [subscribed: digest mode] > > > > > This message, including attached files, may contain > confidential > information and is intended only for the use by the > individual > and/or the entity to which it is addressed. Any > unauthorized use, > dissemination of, or copying of the information > contained herein is > not allowed and may lead to irreparable harm and > damage for which > you may be held liable. If you receive this message > in error or if > it is intended for someone else please notify the > sender by > returning this e-mail immediately and delete the > message. > > -- > 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!? Send FREE video emails in Yahoo! Mail! http://promo.yahoo.com/videomail/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Robert Pegram 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 shadow process
If you are on Solaris, you may be interested in NOTE: 153655.1 SOLARIS: Determing Oracle Memory Usage on Solaris. CUT <<<<<<<< Many programs which display memory usage, like "top" or "ps -lf" do not distinguish between shared and private memory and show the SGA usage in each background and shadow process. Subsequently, it may appear as though Oracle is using several times more memory than what is actually installed on the system. To properly determine how much memory Oracle is using, you must use a tool which separates private and shared memory. One such tool is "/usr/proc/bin/pmap". CUT <<<<<< Robert Pegram Oracle DBA --- sonia pajerowski <[EMAIL PROTECTED]> wrote: > Hello All, > How does oracle allocate memory to oracle shadow > process (client). eg. oracleORCL > (DESCRIPTION=(LOCAL=no > )(ADDRESS=(PROTOCOL=BEQ))). > > I have dedicated connections to the databases and > the > processes are using 320M-340M of memory. Is there > any > parameter I can change to reduce the memory > allocation > to about 30M or less. > > Current value for Sort area size value is 65536. > > > Thanks > Sonia P. > > __ > Do You Yahoo!? > Great stuff seeking new owners in Yahoo! Auctions! > http://auctions.yahoo.com > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: sonia pajerowski > 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!? Great stuff seeking new owners in Yahoo! Auctions! http://auctions.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Robert Pegram 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: STORAGE PARAMETER?
What version of Oracle? In 8i, instead of export/import, you use the following: alter table XXX move tablespace YYY storage (...); You will need to rebuild the indexes after executing this statement. I also don't think any dml is allowed during the move, but selects are allowed. Someone will correct me if I am wrong. For indexes, you can use: alter index XXX rebuild tablespace YYY storage (...) online; If I remember correctly, using the rebuild online option, dml is allowed against the table during the rebuild - Check the documentation. Robert Pegram Oracle DBA --- Seema Singh <[EMAIL PROTECTED]> wrote: > Hi > If I change next and pctincrease storage parameter > of tables and Indexes > then it will effect after the chage.My question is > what will be happened > with old data in tables ?Means old data will be in > same storage.Is there any > impact? > Is there any way to remove fragmentation except > export/drop/create/import? > THX > -Seema > > > > _ > Join the worlds largest e-mail service with MSN > Hotmail. > http://www.hotmail.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!? Great stuff seeking new owners in Yahoo! Auctions! http://auctions.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Robert Pegram 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: Sending email from Unix
On Solaris I have used uuencode - check the man page for more details. uuencode $FILE $FILE | mailx -s "subject" [EMAIL PROTECTED] Robert Pegram Oracle DBA --- Sona <[EMAIL PROTECTED]> wrote: > Oracle standby strange behaviorHi > I want to send an email from Unix with an attachment > .does anyone know how to do this? > > TIA > __ Do You Yahoo!? Great stuff seeking new owners in Yahoo! Auctions! http://auctions.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Robert Pegram 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: Instream SQL
#/bin/ksh sqlplus -s / << EOF select 'x' from dual; exit; EOF Robert Pegram Oracle DBA --- "Smith, Ron L." <[EMAIL PROTECTED]> wrote: > I have a batch script running on Unix and I need to > call a short SQL script > (3 or 4 lines). Can someone show me the syntax > to execute an inline SQL session so I can have all > the code in one script? > > Ron Smith > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: Smith, Ron L. > 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!? Send FREE Valentine eCards with Yahoo! Greetings! http://greetings.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Robert Pegram 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).
Moving Oracle Binaries from HP to SUN
Has anyone done the following: Create a db using Oracle Loaded on an HP Server - The datafiles, controlfiles, etc will be on EMC Later, Install the oracle binaries on a SUN Server, basically unplug the HP from the SYMM, and plug in the SUN and bring up the database. I was asked this yesterday because some of the sun hardware is on back order, and they want to put the project in production before the parts will arrive. There is a spare HP server (bought for another project) they can use until the parts arrive. I am awaiting an official response from Oracle, but thought I would get your input. TIA, Robert Pegram - EDS Oracle DBA __ Do You Yahoo!? Send FREE Valentine eCards with Yahoo! Greetings! http://greetings.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Robert Pegram 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: Moving Oracle Binaries from HP to SUN
Thanks for the responses. I thought exp/imp was the only way to go, but I feel much more confident now. That was also Oracle's response. BTW, I think they have changed their thinking on this issue anyway. Thanks, Robert Pegram - EDS Oracle DBA --- bill thater <[EMAIL PROTECTED]> wrote: > [EMAIL PROTECTED] wrote: > > >Has anyone done the following: > > > >Create a db using Oracle Loaded on an HP Server > >- The datafiles, controlfiles, etc will be on > EMC > > > >Later, > >Install the oracle binaries on a SUN Server, > basically > >unplug the HP from the SYMM, and plug in the SUN > and > >bring up the database. > > > been there, done that, blew up big time.;-) > > as far as i know it won't work. you'd have to go > the exp/imp route. > > > -- > -- > Bill "Shrek" Thater ORACLE DBA > [EMAIL PROTECTED] > > You gotta program like you don't need the money, > You gotta compile like you'll never get hurt, > You gotta run like there's nobody watching, > It's gotta come from the heart if you want it to > work. > > Hear and you forget; see and you remember; do and > you understand. - Confucius > > > > > > > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: bill thater > 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!? Send FREE Valentine eCards with Yahoo! Greetings! http://greetings.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Robert Pegram 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 append generating redo
Stephane, I don't understand what you mean when you say you can select on t1 before any commit. I just tried this on an 8.1.7 database, and there was a signicant reduction in redo. There was only 668 bytes of redo generated vs. 2320 (see below). I think some redo will still be generated as you allocate extents (my tablespace is dictionary managed). Rob Pegram Oracle Certified DBA SQL> create table t as select * from dba_users; Table created. SQL> alter table t nologging; Table altered. SQL> delete from t; 15 rows deleted. SQL> commit; Commit complete. SQL> select value from v$mystat, v$statname where v$mystat.statistic# = v$statname.statistic# and v$statname.name = 'redo size' VALUE -- 117720 SQL> insert /*+ append */ into t as select * from dba_users; insert /*+ append */ into t as select * from dba_users * ERROR at line 1: ORA-00926: missing VALUES keyword SQL> insert /*+ append */ into t select * from dba_users; 15 rows created. SQL> commit; Commit complete. SQL> select value from v$mystat, v$statname where v$mystat.statistic# = v$statname.statistic# and v$statname.name = 'redo size' VALUE -- 118388 SQL> delete from t; 15 rows deleted. SQL> SQL> commit; Commit complete. SQL> select value from v$mystat, v$statname where v$mystat.statistic# = v$statname.statistic# and v$statname.name = 'redo size' VALUE -- 123856 SQL> insert into t select * from dba_users; 15 rows created. SQL> commit; Commit complete. SQL> select value from v$mystat, v$statname where v$mystat.statistic# = v$statname.statistic# and v$statname.name = 'redo size' VALUE -- 126176 --- paquette stephane <[EMAIL PROTECTED]> wrote: > Hi, > > I'm trying the following insert /*+ append */ into > t1 > as select * from t2; > > t1 is created with nologging attribute. > > The insert is not using the hint at all. > I can select on t1 (before any commit) which I > should > not be able to do if the append hint was used. > > Any ways to get the hing used ? > (Oracle 817/NT) > > TIA > > = > Stéphane Paquette > DBA Oracle, consultant entrepôt de données > Oracle DBA, datawarehouse consultant > [EMAIL PROTECTED] > > ___ > Do You Yahoo!? -- Une adresse @yahoo.fr gratuite et > en français ! > Yahoo! Mail : http://fr.mail.yahoo.com > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: =?iso-8859-1?q?paquette=20stephane?= > 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! Tax Center - online filing with TurboTax http://taxes.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Robert Pegram 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: Locally managed tablespaces/What a DBA can do ?
The poster implied he was going to change the temporary tablespace to locally managed. > > > > > to some of the other org's, so maybe staying > > > with > > > > > dictionary-managed would > > > > > be > > > > > best, except for the temporary tablespace. > > > > > > > > > > Darren. All of my production databases are still dictionary managed. I played around with locally managed tablespaces a while back on a test server, and when I ran a generic hot backup script, which attempted to dynamically put each tablespace in backup mode, it failed on the temporary tablespace. Of course all other tablespaces were successful (as you pointed out). Hopefully he would be doing this in a test environment first anyway, but I was just pointing out that he may want to test backup/recovery after converting. Rob Pegram Oracle Certified DBA --- "Koivu, Lisa" <[EMAIL PROTECTED]> wrote: > That's not a temp tablespace (TEMP_DATA_A). It's a > place to put temporary > load tables. My temporary tablespace is TEMP, which > is dictionary. From > the research I did on LMT's before I decided to use > them, it looked like > there were several bugs associated with temporary > tablespaces being LMT's so > I left my temporary ts dictionary. > > LK > > > > -Original Message- > > From: Robert Pegram [SMTP:[EMAIL PROTECTED]] > > Sent: Wednesday, April 17, 2002 11:34 AM > > To: Multiple recipients of list ORACLE-L > > Subject:RE: Locally managed tablespaces/What a > DBA can do ? > > > > What about your locally managed "temporary" > > tablespace? > > > > > > --- "Koivu, Lisa" <[EMAIL PROTECTED]> > wrote: > > > What? I was just able to do it. 8.1.7, W2K > > > > > > (INV-SYSTEM)>@ts > > > > > > TS_NAME LMT MB_FREE MB_TOTAL > > > PCT_USED > > > --- -- - - > > > - > > > LOAD_DATA_A LOCAL740 2000 > > > > 63 > > > LRG_INDEX_A LOCAL390 2500 > > > > 84.4 > > > LRG_INDEX_B LOCAL980 1000 > > > > 2 > > > LRG_TABLE_A LOCAL 2440 4000 > > > > 39 > > > LRG_TABLE_B LOCAL360 1000 > > > > 64 > > > MED_INDEX_A LOCAL480 1000 > > > > 52 > > > MED_TABLE_A LOCAL680 1000 > > > > 32 > > > RBS DICTIONARY 299 1000 > > > > 70.1 > > > SMALL_INDEX_A LOCAL183 200 > > > > 8.5 > > > SMALL_TABLE_A LOCAL169 200 > > > > 15.5 > > > SYSTEM DICTIONARY 110 200 > > > > 45 > > > TEMP DICTIONARY 961 1000 > > > > 3.9 > > > TEMP_DATA_A LOCAL900 1000 > > > > 10 > > > TOOLS LOCAL 1920 > > > > 5 > > > USERS LOCAL 3475 > > > > 54.7 > > > > > > 15 rows selected. > > > > > > (INV-SYSTEM)>alter tablespace load_data_a begin > > > backup; > > > > > > Tablespace altered. > > > > > > (INV-SYSTEM)>alter tablespace load_data_a end > > > backup; > > > > > > Tablespace altered. > > > > > > > > > > > > > -Original Message- > > > > From: Robert Pegram [SMTP:[EMAIL PROTECTED]] > > > > Sent: Wednesday, April 17, 2002 9:58 AM > > > > To: Multiple recipients of list ORACLE-L > > > > Subject:RE: Locally managed tablespaces/What > a > > > DBA can do ? > > > > > > > > Darren, > > > > > > > > If using a hot backup strategy (other than > Rman), > > > > make sure to test your backup/recovery if you > > > switch > > > > the temporary tablespace to locally managed. > You > > > > can't put a locally managed temporary > tablespace > > > in > > > > backup mode. > > > > > > > > Rob Pegram > > > > Oracle Certified DBA > > > > > > > > > > > > > > > > SQL> create
RE: Unable to rebuild database from hot backup
e control files. > > > > After doing > > recover database using backup controlfile; > > > > I got > > SVRMGR> alter database open resetlogs; > > alter database open resetlogs > > * > > ORA-01113: file 1 needs media recovery > > ORA-01110: data file 1: > '/data1/nyccp/systnyccp.dbf > > > > Thinking that perhaps I just needed to apply more > logs I went on to > > apply > > another 12 hours worth of archive logs. I still > get the same error. > > > > > > Now I'm really concerned that my hot backup is > invalid for some > > reason. > > Does anyone have any suggestions for what else I > can look at? > > > > TIA, > > Jay Miller > > -- > > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > > -- > > Author: Miller, Jay > > 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: Miller, Jay > > 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 a great connection at Yahoo! Personals. > http://personals.yahoo.com > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: Rachel Carmichael > 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 a great connection at Yahoo! Personals. http://personals.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Robert Pegram 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: Unable to rebuild database from hot backup
Luckily I have never had this situation in production, and what I have read also suggests using the ascii file created from 'BACKUP CONTROLFILE TO TRACE' for recovery. I just had a little time on my hands and decided to give it a try. As I stated, I am by no means an expert in recovery (more like a novice), I was just stating what I observed. Maybe someone else will let us know their opinion. Thanks for your input. Rob Pegram --- "Jesse, Rich" <[EMAIL PROTECTED]> wrote: > Is this recovery method valid? The loss of > controlfiles is supposed to be > recovered using the trace file generated from a > previous BACKUP CONTROLFILE > TO TRACE, isn't it? At least according to the > Oracle8 Backup and Recovery > class it is. > > The part that concerns me is that you are recovering > using an ONLINE redo > log (don't know if it was the active one or not). > Could be the paranoid in > me, but that just doesn't seem right. The admission > that "I don't think > this is 100% true for every situation" would seem to > suggest that you agree > that this method shouldn't be used for > backup/recovery on production > databases. > > My $.02. > > Rich Jesse System/Database > Administrator > [EMAIL PROTECTED] Quad/Tech > International, Sussex, WI USA > > -Original Message- > Sent: Friday, October 26, 2001 08:30 > To: Multiple recipients of list ORACLE-L > > > Tim, > > > You can not simply cancel a "recover > > database using backup > > controlfile" since Oracle is expecting you perform > a > > complete recovery ( > > since your not using the UNTIL CANCEL/TIME/CHANGE > ) > > and canceling a complete > > recovery leaves the stop SCN in the controlfile at > > infinity... Therefore, > > the recovery is never complete and you will always > > receive the "needs media > > recovery message"... > > I don't think this is 100% true for every situation. > > I am no recovery expert, but I just did this the > other > day, and to verify it, I ran the test again this > morning. > > 1. Shutdown abort > 2. Delete all current controlfiles > 3. Replaced the binary controlfiles from last hot > backup (they were copied as part of the backup > script) > 4. Startup mount > 5. Recover database using backup controlfile > 6. Kept applying logs - only trick was that the > last > log was an online redo log, so I had to type in that > log explicitly. There I received the message Media > Recover Complete. > 7. Alter database open resetlogs. > > Rob Pegram > Oracle Certified DBA > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: Jesse, Rich > 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 a great connection at Yahoo! Personals. http://personals.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Robert Pegram 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).