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.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: 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: order by in subquery workaround
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: Unsetting OPTIMAL in rollback segments
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: 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 TEMPDICTIONARY 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 tablespace temp_dict 2 datafile 'c:oracleoradataorcl emp_dict.dbf' size 10M 3 default storage (initial 1M next 1M maxextents unlimited pctincrease 0) 4* temporary SQL / Tablespace created. SQL create temporary tablespace temp_local 2 tempfile 'c:oracleoradataorcl emp_local.dbf' size 10M 3 extent management local 4 uniform size 1M; Tablespace created. SQL alter tablespace temp_dict begin backup; Tablespace altered. SQL alter tablespace temp_local begin backup; alter tablespace temp_local begin backup * ERROR at line 1: ORA-03217: invalid option for alter of TEMPORARY TABLESPACE --- Browett, Darren [EMAIL PROTECTED] wrote: Okay, maybe re-org would be a little too much, as you say, I would need to know the application better, and with 8 distinctly different apps, including oracle financials, I maybe heading for trouble. But what about simply turning the existing dictionary-managed tablespaces into locally managed tablespaces, other then maintenance, would I gain performance ? From what I read on this list, the growth of my tables is very small compared to some of the other org's, so maybe staying with dictionary-managed would be best, except
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).
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: 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).
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: 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: 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: 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: 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: 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 code200 and code300, 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: Unable to rebuild database from hot backup
: 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).