Finally! The RAID F Simulator is here ( and attachedd )
Dear All, Jesper from the Copenhagen Business School got this crazy idea some weeks ago, and with input from Michael Mller of Miracle A/S, it's my proud honor to present the World's first Oracle-based RAID-F Simulator. It's all fun and games, of course, and Jesper got inspired after reading James Morle's book. He simply copied the 20 pages or so about RAID and handed it to his boss and said that this was the knowledge he needed to understand the way Jesper was thinking about RAID! The boss did read it, and did understand it, and then Jesper went ahead with the RFS project just for fun. With the kind permission of Jared, I have attached it. So this is your Christmas gift from Jesper :-). Best regards, Mogens Spool raid.log Set pages 5 Set lines 132 Set trimspool on Set echo on /* RAID simulator == Introduction The purpose of the script is to have fun, while trying to get a better understanding of RAID-4 and RAID-5. James Morle writes in Scaling Oracle8i that one can play with XOR operations on a scientific calculator to see, that the redundancy in RAID-5 is sufficient to rebuild a crashed disk. After a few minutes using the Windows Calculator Jesper decided to automate the calculations by starting to write this script. Michael got hooked on the idea and made several improvements. The script simulates a RAID-4 system using an Oracle database. We are going to - Deal with physical data disks, a parity disk and one large logical disk. - Set up automatic maintenance of redundancy on the parity disk. - Format the disks and put some data on the logical volume. - See consequences of a disk crash followed by a successful disk rebuild. - Do some updates and check that the integrity is maintained. - Make a binary dump of everything to make it clear, what RAID is about. How to get started -- If you just want a quick tour then - Run this script with SQL*Plus against an Oracle9i database. - Open the logfile raid.log with your favourite editor. - Locate the test section (search for TESTING) and start reading. It is very simple plain SQL simulating a disk crash and a disk rebuild. If you want more then - Continue reading until you are working at the bit-level and/or - See how the simulation was set up in the first section. If you just can't get enough - Solve the exercises and write your own code. Difference Between RAID-4 and RAID-5 A RAID-4 system has a dedicated parity disk, which is maintained at block level like this: RAID-4 disk0 disk1 disk2 disk3 parity disk - - - - --- block 0block 1block 2block 3parity 1 block 4block 5block 6block 7parity 2 block 8block 9block 10 block 11 parity 3 block 12 block 13 block 14 block 15 parity 4 Etc. Considering the 4 physical data disks as one large logical volume, the Logical Volume Blocks can be mapped to the Physical Disk blocks like this: Logical Volume Address = 4 * Physical Disk Address + Disk Number From that statement we can derive: Disk Number = Mod(Logical Volume Address, 4) Physical Disk Address = Trunc(Logical Volume Address / 4) When doing a write to the logical volume we have to - Read the old data block - Read the old parity block - Write the new data block - Write the new parity block This cost of four physical I/Os for one logical write is known as the write penalty. If you are doing many random writes, then the parity disk becomes very hot. To avoid the bottleneck with one single very hot parity disk, RAID-5 stripes the parity blocks on all disks. Thus the write penalty is load balanced on more disks like this. RAID-5: disk0 disk1 disk2 disk3 disk4 - - - - - block 0block 1block 2block 3parity 1 block 4block 5block 6parity 2 block 7 block 8block 9parity 3 block 10 block 11 block 12 parity 4 block 13 block 14 block 15 Etc. NOTE: The write penalty still exists. In RAID-5 it is just load balanced. In order to keep things simple, this script simulates RAID-4. This simplifies calculations regarding where to physically find the data and parity blocks. The difference between RAID-4 and RAID-5 is only where the blocks are stored. There are no differences in the contents of the blocks. Thus we can learn how RAID-4 and RAID-5 works, without making it too difficult to deal with. If you really like complexity, then try to establish the 1:1 relationship between logical and physical blocks in RAID-5. Then migrate this script to support RAID-5. (Easier exercises can be found elsewhere in the script).
Re: Hit Ratio
Well, I guess I could have added a :) after my request on how to fix the hit ratio, but it wouldn't be nearly as much fun. On Sun, 2003-12-21 at 03:29, Mogens Nrgaard wrote: Ah yes, you could introduce heuristically (spelling?!) skewed hit ratios. As Dave Ensor explained at UKOUG, the word heuristic in Oracle's optimizer code can be translated directly into constant. So add a number here or there until it fits. Mogens Jonathan Lewis wrote: Easy, A new formula for the hit ratio Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Sunday, December 21, 2003 1:19 AM Actually, it isn't SAP. I was simply creating a set of MV's based on SAP tables in another database. The script I was running is used to keep track of how much IO is going on, just to ensure that everything is still working during the build. Once the physical IO exceeds the logical IO, the HR goes negative. I wonder what I need to tune to fix this? Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?UTF-8?B?TW9nZW5zIE7DuHJnYWFyZA==?= INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still 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: Finding the most actively DML'd table
Hi, I did something similiar before. It cound be meaningful in some case. My database was io-bound and top wait event was log file sync. It can because of too much commit, I asked business guys and according to there data and business logic, there cound't be so much transactions. I reviewed statspack and v$sql according to executions and sql being with update/insert/delete, I found the root cause, that particular SQL generated 80%(maybe) of total commits. We rewrote the application and IO get down. Regards. Zhu Chao. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Saturday, December 20, 2003 4:04 AM Is it possible to find how many DML operations have ocurred on a given table in a given time period? I'd like to be able to rank the tables in a database based on the number of DML actions performed on them in a given time period. It would be interesting to see which are the 'hot spots' so to speak, and either a) tune access to those tables accordingly, or b) put out the fires. Granted, this strays somewhat from the Millsapian/Holtian doctrine of focusing on the user action with the largest business impact, but I still think it would be interesting to see. Thanks for any help, -- Dan Daniel Hanks - Systems/Database Administrator About Inc., Web Services Division -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Daniel Hanks INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: zhu chao 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: Upgrading to Oracle 9.2.0.4 - Any pitfalls?
What's the bug relating to 1,000s of Partitions, was it the one to with monitoring, or something more interesting ? Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, December 22, 2003 4:49 AM I have said it on this list before, and I will say it again. With Oracle, quality ends with in 4. 7.3.4 8.1.7.4 9.2.0.4 Oracle v9.2.0.4 is fairly stable. I have had to apply only 1, one-off patch related to having 1000's of partitions. You may also want to add the following to your init.ora to prevent a few known bug's -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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).
problems encountered installing db 817 in redhatlinux es 2.1
hi, were they any problem installing db 817 in redhat linux es 2.1? wat are they and how to go about it? thanks . Best Regards, Grace Lim Suy Sing Comm'l Corp -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: 10g new features question for beta testers
Thats why I carefully wrote: opportunity for benefits as opposed to benefits :-) --- Mogens_Nørgaard [EMAIL PROTECTED] wrote: It's the Best of Breed versus One Vendor debate, and there are pros and cons galore. The perfect scenario, of course, is when they combine, so one vendor delivers the best of everything. That's what we have with Microsoft, isn't it? ;-) : Office stuff, OS, Database, ERP, CRM, video player, what have you... Then on the Support side of things, it's indeed good to be able to call One Vendor Only... if that vendor is good at Support. If he isn't, you might be better off if you have more than one option for calling. Mogens Pete Sharman wrote: Just a couple of comments on this which hopefully won't go down the Marketing track too far. :) 1. I'm pretty sure Steve Adams agrees with you, since he co-presented on ASM at OracleWorld in San Fran. Not sure if he monitors this group actively or not, but I believe the presentation he did is loaded with all the other OracleWorld 2003 presentations so you can see what he said. 2. One point which makes a lot of sense to me, and it happens in a variety of places in 10g such as ASM and the RAC clusterware. If you have one vendor to raise an issue with (not that you'd need to do that with Oracle of course!), it's a lot easier to get an answer without the finger pointing that can go on between vendors. Take the clusterware example - if you run into a problem running RAC on Sun with the Sun Cluster technology and Veritas owning the disk side, who you gonna call? GhostBusters, maybe! But if you're running RAC on Sun with Oracle's clusterware and ASM, it's a lot easier to determine who to call. Pete Controlling developers is like herding cats. Kevin Loney, Oracle DBA Handbook Oh no, it's not. It's much harder than that! Bruce Pihlamae, long-term Oracle DBA -Original Message- Connor McDonald Sent: Saturday, December 20, 2003 2:34 AM To: Multiple recipients of list ORACLE-L As with anything I suppose, if a single vendor can be in control of more of the stack between application and physical server structure then there is a greater opportunity for benefits. For example, ASM offers the ability to add disks to a stripe without needing to redistribute(reload) the entire stripeset. A (bug-free) ASM product looks very very impressive to me. Time will tell how close Oracle are to achieving it. hth connor --- [EMAIL PROTECTED] wrote: no ASMs are considerably different. Its supposed to manage everything. You dont give it a file, you give it entire disks and oracle does everything. Sets up files, manages, I/O, everything. you only look at the tablespace level. you dont even install any software on it. If your on SAN, you dont install SAN software on it. From: Goulet, Dick [EMAIL PROTECTED] Date: 2003/12/19 Fri AM 09:14:27 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: 10g new features question for beta testers That is not exactly a new feature. Oracle 9i has Oracle Managed Files where you give it a directory and then just build tablespaces. The database picks the filenames for you. Now mind you it does work, but I'll be damned if I use it in anything other than a development environment. For some reason Oracle has never gotten over that DUMB SAME (Stripe And Mirror Everything) idea. The concept is great in theory, but in practice it's absolutely abysmal at best. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Friday, December 19, 2003 8:24 AM To: Multiple recipients of list ORACLE-L I saw a presentation from Oracle on 10g new features last night in Reston,VA. I know atleast one other person from the list was there. Since Oracle is releasing details and its going to be released(in theory) in the next 2 weeks, I was wondering if you guys could talk about it. 1. does ASMs work as well as Oracle claims? I always wonder about first generation features... takes most software vendors a couple of generations to get it right(takes any project Im on just as long). This is a radical departure. for those of you who dont know. Oracle claims that they will manage your disks for you. All you do is give Oracle some Raw Disks and Oracle will set up, and handle all your datafiles. All you do is look at logical tablespaces. It will also handle I/O balancing. How well does this work? Anyone test it with a SAN? 2. RAC Load Balancing. Oracle claims that you only need Oracle software from now on. They also claim that you can load balance multiple applications. Lets say you have One application that runs batch loads over night and a transactional application
Re: Finally! The RAID F Simulator is here ( and attachedd )
LOL! Just to add my 2c worthI think you can get a simpler XOR implementation with: CREATE OR replace FUNCTION bitor( x IN NUMBER, y IN NUMBER ) RETURN NUMBER AS BEGIN RETURN x + y - bitand(x,y); END; / CREATE OR replace FUNCTION bitxor( x IN NUMBER, y IN NUMBER ) RETURN NUMBER AS BEGIN RETURN bitor(x,y) - bitand(x,y); END; / Cheers Connor --- Jared Still [EMAIL PROTECTED] wrote: Dear All, Jesper from the Copenhagen Business School got this crazy idea some weeks ago, and with input from Michael Möller of Miracle A/S, it's my proud honor to present the World's first Oracle-based RAID-F Simulator. It's all fun and games, of course, and Jesper got inspired after reading James Morle's book. He simply copied the 20 pages or so about RAID and handed it to his boss and said that this was the knowledge he needed to understand the way Jesper was thinking about RAID! The boss did read it, and did understand it, and then Jesper went ahead with the RFS project just for fun. With the kind permission of Jared, I have attached it. So this is your Christmas gift from Jesper :-). Best regards, Mogens Spool raid.log Set pages 5 Set lines 132 Set trimspool on Set echo on /* RAID simulator == Introduction The purpose of the script is to have fun, while trying to get a better understanding of RAID-4 and RAID-5. James Morle writes in Scaling Oracle8i that one can play with XOR operations on a scientific calculator to see, that the redundancy in RAID-5 is sufficient to rebuild a crashed disk. After a few minutes using the Windows Calculator Jesper decided to automate the calculations by starting to write this script. Michael got hooked on the idea and made several improvements. The script simulates a RAID-4 system using an Oracle database. We are going to - Deal with physical data disks, a parity disk and one large logical disk. - Set up automatic maintenance of redundancy on the parity disk. - Format the disks and put some data on the logical volume. - See consequences of a disk crash followed by a successful disk rebuild. - Do some updates and check that the integrity is maintained. - Make a binary dump of everything to make it clear, what RAID is about. How to get started -- If you just want a quick tour then - Run this script with SQL*Plus against an Oracle9i database. - Open the logfile raid.log with your favourite editor. - Locate the test section (search for TESTING) and start reading. It is very simple plain SQL simulating a disk crash and a disk rebuild. If you want more then - Continue reading until you are working at the bit-level and/or - See how the simulation was set up in the first section. If you just can't get enough - Solve the exercises and write your own code. Difference Between RAID-4 and RAID-5 A RAID-4 system has a dedicated parity disk, which is maintained at block level like this: RAID-4 disk0 disk1 disk2 disk3 parity disk - - - - --- block 0block 1block 2block 3 parity 1 block 4block 5block 6block 7 parity 2 block 8block 9block 10 block 11 parity 3 block 12 block 13 block 14 block 15 parity 4 Etc. Considering the 4 physical data disks as one large logical volume, the Logical Volume Blocks can be mapped to the Physical Disk blocks like this: Logical Volume Address = 4 * Physical Disk Address + Disk Number From that statement we can derive: Disk Number = Mod(Logical Volume Address, 4) Physical Disk Address = Trunc(Logical Volume Address / 4) When doing a write to the logical volume we have to - Read the old data block - Read the old parity block - Write the new data block - Write the new parity block This cost of four physical I/Os for one logical write is known as the write penalty. If you are doing many random writes, then the parity disk becomes very hot. To avoid the bottleneck with one single very hot parity disk, RAID-5 stripes the parity blocks on all disks. Thus the write penalty is load balanced on more disks like this. RAID-5: disk0 disk1 disk2 disk3 disk4 - - - - - block 0block 1block 2block 3 parity 1 block 4block 5block 6parity 2 block 7 block 8block 9parity 3 block 10 block 11 block 12 parity 4 block 13 block 14 block 15 Etc. NOTE: The write penalty still exists. In RAID-5 it is just load balanced. In order to keep things simple, this script
Re: Finally! The RAID F Simulator is here ( and attachedd )
I don't think you should be playing with this and having fun when you could use the valuable Christmas period for rebuilding all your indexes. NB Joke But since it's Oracle 9.2 that gets mentioned how about trying the new bit functions: select sys_op_rawtonum( sys_op_vecxor( sys_op_numtoraw(6), sys_op_numtoraw(5) ) ) from dual / I won't guarantee that they're faster, and they certainly look messier than the pl/sql function calls, but I think they are SQL built-ins, and therefore may be faster running than pl/sql function calls. sys_op_vecor and sys_op_vecand are also available. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, December 22, 2003 11:34 AM LOL! Just to add my 2c worthI think you can get a simpler XOR implementation with: CREATE OR replace FUNCTION bitor( x IN NUMBER, y IN NUMBER ) RETURN NUMBER AS BEGIN RETURN x + y - bitand(x,y); END; / CREATE OR replace FUNCTION bitxor( x IN NUMBER, y IN NUMBER ) RETURN NUMBER AS BEGIN RETURN bitor(x,y) - bitand(x,y); END; / Cheers Connor --- Jared Still [EMAIL PROTECTED] wrote: Dear All, Jesper from the Copenhagen Business School got this crazy idea some weeks ago, and with input from Michael Möller of Miracle A/S, it's my proud honor to present the World's first Oracle-based RAID-F Simulator. It's all fun and games, of course, and Jesper got inspired after reading James Morle's book. He simply copied the 20 pages or so about RAID and handed it to his boss and said that this was the knowledge he needed to understand the way Jesper was thinking about RAID! The boss did read it, and did understand it, and then Jesper went ahead with the RFS project just for fun. With the kind permission of Jared, I have attached it. So this is your Christmas gift from Jesper :-). Best regards, Mogens -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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).
Unix question
Hallo all of you, Is there anyone whom could help me with the unix command how to find all rows , that doesnt exists try in a file. I mean how to find all rows which doesnt have the characters try in. Maybe this is too simple, but would appreciate anyone whom could give me some quick help. Thanks in advance Roland -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Unix question
grep command will help you or you could venture to the sed command. grep is probably the most used unix command. To learn more, man grep . [EMAIL PROTECTED] 12/22/03 07:54AM Hallo all of you,Is there anyone whom could help me with the unix command how to find all rows , that doesnt exists "try" in a file.I mean how to find all rows which doesnt have the characters try in.Maybe this is too simple, but would appreciate anyone whom could give me some quick help.Thanks in advanceRoland-- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: INET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California -- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
RE: Unix question
Hi, You can use the grep command in the following manner: grep -v try filename The option -v negates the search pattern, meaning rows which do not have a try string in them. The string can be enclosed in double quotes as well, this is useful if you are searching for multiple patterns... Hope this will help. Regards kesh -Original Message-From: Gene Sais [mailto:[EMAIL PROTECTED]Sent: Monday, December 22, 2003 6:39 PMTo: Multiple recipients of list ORACLE-LSubject: Re: Unix question grep command will help you or you could venture to the sed command. grep is probably the most used unix command. To learn more, man grep . [EMAIL PROTECTED] 12/22/03 07:54AM Hallo all of you,Is there anyone whom could help me with the unix command how to find all rows , that doesnt exists "try" in a file.I mean how to find all rows which doesnt have the characters try in.Maybe this is too simple, but would appreciate anyone whom could give me some quick help.Thanks in advanceRoland-- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: INET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California -- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing). This e-mail and any files transmitted with it are for the sole use of the intended recipient(s) and may contain confidential and privileged information. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message. Any unauthorised review, use, disclosure, dissemination, forwarding, printing or copying of this email or any action taken in reliance on this e-mail is strictly prohibited and may be unlawful. Visit us at http://www.cognizant.com
Re: Upgrading to Oracle 9.2.0.4 - Any pitfalls?
Title: Upgrading to Oracle 9.2.0.4 - Any pitfalls? Jones, ORA-04031 may arrise if DB_CACHE_ADVICEparameter isON/REDDY , and SHARED POOL is unable to allocate the memory required for the advisory. This may be resoleved by increasing SHARED_POOL_SIZE or putting the DB_CACHE_ADVICE=OFF. regards navneet - Original Message - From: Jones, Richard O. To: Multiple recipients of list ORACLE-L Sent: Monday, December 22, 2003 9:59 AM Subject: Upgrading to Oracle 9.2.0.4 - Any pitfalls? Hi, Our main production database was upgraded to Oracle 9.2.0.3 (64 bit) at the end of September 2003. Platform Solaris 64bit 5.8. Since then the database has "hanged" and had to be manually shutdown by killing processes and re-started: - (1) The first one involved the production of numerous:ORA-04031: unable to allocate 26168 bytes of shared memory ("shared pool","unknown object","sga heap(1,0)","session param values") ,errors when users were logging and was linked by Oracle Support with bug number 2921201 (2) Secondly, the database raised an ORA-600 to the alert file:ORA-00600: internal error code, arguments: [510], [0x380068B30], [shared pool], [], [], [], [], [] ,followed by numerous messages:PMON failed to acquire latch, see PMON dump (3) Thirdly, an:ORA-04031: unable to allocate 16384 bytes of shared memory ("shared pool","unknown object","sga heap(1,0)","trace buffer")was raised apparently caused by an Oracle background processes dieing unexpectedly. Should I upgrade to 9.2.0.4? None of the above problems seem to be fixed in 9.2.0.4!!! Our database is a hybrid between OLTP and Decision-Support with a relatively light load. Anyone out there with an unstable 9i database (we were more stable under 8.1.7)? Am I alone?? Many Thanks Richard Jones, DBA
specify nls_lang
Hello list, I am using 9.2.0.1.0 enterprise edition on windows. Earlier when I use to specify nls_lang=French_France.US7ASCII in 9i release 1 I would get the following messages in French c: sqlplus Entrez le nom utilisateur : But now when I specify nls_lang=French_France.US7ASCII , sqlplus sticks to english c: sqlplus Enter user-name: Any ideas ? I tried out the following but to no avail : 1. I specified ora_nls33 to point to D:\OracleXP\Ora92\ocommon\nls\ADMIN\DATA where D:\OracleXP\Ora92\ is my %oracle_home% 2. I set nls_lang=French_France.US7ASCII , log in as a user, checked NLS_SESSION_PARAMETERS. It shows that NLS_LANGUAGE is FRENCH and NLS_TERRITORY is FRANCE. When I select from a column containing dates , the months are in French. sysdate also gives the month in french 3. Same behaviour with nls_lang=French_France.WE8MSWIN1252 and nls_lang=nls_lang=French_France.UTF8 (although this is an incorrect specification since there are no utf8 windows clients ) 4. Similarly when I specify nls_lang=ENGLISH_INDIA.WE8MSWIN1252 my queries pick up the correct local currency symbol. and nls_language and nls_territory values in nls_session_parameters are correct. 5. Alert log doesn't show any errors. My database character set is AL32UTF8 , did not specify an nchar characterset while creating the database. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
ASM question
I decided to play around with ASMs and use the statspack tablespace as my trial balloons(lots of inserts and deletes and I dont care about fragmentation). anyway I just ran SPCREATE. Wierd thing is that there is no data in any of my statspack tables, but their segments sizes vary from 1m to 5m??? any idea why? There has never been any data inserted in them. I just created the tablespace and ran spcreate? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: ASM question
hmmm... odd there is no setting for pct_used on tables, but different settings for percent free. Different settings for initial extent to between tables. anyone have more info on how this 'intelligent' algorithm works? I heard kyte speak last week and he assured us that the algorithm is good and there is only 'irrelevant' fragmentation. I dont want to use it in production until I understand it better. From: [EMAIL PROTECTED] Date: 2003/12/22 Mon AM 09:09:26 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: ASM question I decided to play around with ASMs and use the statspack tablespace as my trial balloons(lots of inserts and deletes and I dont care about fragmentation). anyway I just ran SPCREATE. Wierd thing is that there is no data in any of my statspack tables, but their segments sizes vary from 1m to 5m??? any idea why? There has never been any data inserted in them. I just created the tablespace and ran spcreate? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Duplicating with rman
Yeah. I noticed that too and wondered about it. The script that was posted was, in fact, the script that was run. Maybe same kind of line length limitation? -Original Message- From: Yong Huang [mailto:[EMAIL PROTECTED] Sent: Saturday, December 20, 2003 2:24 PM To: Multiple recipients of list ORACLE-L Subject: Re: Duplicating with rman Hi, I'm not sure why your RMAN output says LOGFILE GROUP 1 ( '/z01/oradata/DEVL/redo_01a.dbf', '/z02/oradata/DEVL/redo_01b.dbf', GROUP 2 ( '/z01/oradata/DEVL/redo_02a.dbf', Where's the ) reuse shown in your RMAN script? Are you sure the script you showed here was run? Yong Huang you wrote: RMAN-06162: sql statement: CREATE CONTROLFILE REUSE SET DATABASE DEVL ... LOGFILE GROUP 1 ( '/z01/oradata/DEVL/redo_01a.dbf', '/z02/oradata/DEVL/redo_01b.dbf', GROUP 2 ( '/z01/oradata/DEVL/redo_02a.dbf', '/z02/oradata/DEVL/redo_02b.dbf', ... RMAN-06136: ORACLE error from auxiliary database: ORA-02236: invalid file name RMAN-06097: text of failing SQL statement: CREATE CONTROLFILE REUSE SET ... LOGFILE GROUP 1 ( '/z01/oradata/DEVL/redo_01a.dbf', '/z02/oradata/DEVL/redo_01b.dbf', GROUP 2 ( '/z01/oradata/DEVL/redo_02a.dbf', '/z02/oradata/DEVL/redo_02b.dbf', ... RMAN script is: run { allocate auxiliary channel d1 type disk; set until logseq 5115 thread 1; set newname for datafile 1 to '/u03/oradata/DEVL/system_01.dbf'; ... duplicate target database to DEVL nofilenamecheck logfile group 1 ('/z01/oradata/DEVL/redo_01a.dbf','/z02/oradata/DEVL/redo_01b. dbf') reuse, group 2 ('/z01/oradata/DEVL/redo_02a.dbf','/z02/oradata/DEVL/redo_02b. dbf') reuse, group 3 __ Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing. http://photos.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Yong Huang INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: ASM question
ignore spcreate.sql actually puts pctfree,pctused, and really bad initial and next extent settings on the tables. its an antiquated script that hasnt been updated. my bad. From: [EMAIL PROTECTED] Date: 2003/12/22 Mon AM 09:09:26 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: ASM question I decided to play around with ASMs and use the statspack tablespace as my trial balloons(lots of inserts and deletes and I dont care about fragmentation). anyway I just ran SPCREATE. Wierd thing is that there is no data in any of my statspack tables, but their segments sizes vary from 1m to 5m??? any idea why? There has never been any data inserted in them. I just created the tablespace and ran spcreate? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Exporting a partition with transport tablespace
Title: Exporting a partition with transport tablespace Hi list, is it possible to export a partition with the transportable tablespace feature ? My partition is over 8 Go. Here my statements , thank you in advance ! SQLexec sys.dbms_tts.transport_set_check('HISTO_DOSSIER_P1_MD_TAB',FALSE); SQL select * from sys.transport_set_violations; VIOLATIONS Partitioned table TOPASE.HISTO_DOSSIER is partially contained in the transportab le set: check table partitions by querying sys.dba_tab_partitions Default Partition (Table) Tablespace HISTO_DOSSIER_P2_MD_TAB for HISTO_DOSSIER n ot contained in transportable set Default Partition (Table) Tablespace HISTO_DOSSIER_P3_MD_TAB for HISTO_DOSSIER n ot contained in transportable set Default Partition (Table) Tablespace HISTO_DOSSIER_P4_MD_TAB for HISTO_DOSSIER n ot contained in transportable set
RMAN - the time has come
Title: RMAN - the time has come Okay, its time to bite the bullet ... time to learn RMAN. Outside of TFM (which I just started reading), are there any good books/articles on RMAN? I know there is one by RFreeman, and it is for 9i (Robert, will there be a 10g version?) Yeah, I am also google'ing, taeoma'ing and in general stfw'ing whenever time permits. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! **This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.**4
RE: RMAN - the time has come
Title: RMAN - the time has come Raj, It really is a quick learn. The best way is to get some scripts (great examples in the rdbms/demo directory) and try them on a test database. Once you get something running, the rest is simple. If you need some samples, let me know. It's really basic stuff. Tom Mercadante Oracle Certified Professional -Original Message-From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]Sent: Monday, December 22, 2003 10:35 AMTo: Multiple recipients of list ORACLE-LSubject: RMAN - the time has come Okay, its time to bite the bullet ... time to learn RMAN. Outside of TFM (which I just started reading), are there any good books/articles on RMAN? I know there is one by RFreeman, and it is for 9i (Robert, will there be a 10g version?) Yeah, I am also google'ing, taeoma'ing and in general stfw'ing whenever time permits. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! **This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.**4
Re: Exporting a partition with transport tablespace
transportable tablespaces need to be totally self-contained. everything that is being transported has to be in that tablespace. it doesnt matter if its a different datafile. you probably have your partitions in seperate tablespaces? or am i wrong? From: NGUYEN Philippe (Cetelem) [EMAIL PROTECTED] Date: 2003/12/22 Mon AM 10:34:25 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Exporting a partition with transport tablespace Hi list, is it possible to export a partition with the transportable tablespace feature ? My partition is over 8 Go. Here my statements , thank you in advance ! SQLexec sys.dbms_tts.transport_set_check('HISTO_DOSSIER_P1_MD_TAB',FALSE); SQL select * from sys.transport_set_violations; VIOLATIONS Partitioned table TOPASE.HISTO_DOSSIER is partially contained in the transportab le set: check table partitions by querying sys.dba_tab_partitions Default Partition (Table) Tablespace HISTO_DOSSIER_P2_MD_TAB for HISTO_DOSSIER n ot contained in transportable set Default Partition (Table) Tablespace HISTO_DOSSIER_P3_MD_TAB for HISTO_DOSSIER n ot contained in transportable set Default Partition (Table) Tablespace HISTO_DOSSIER_P4_MD_TAB for HISTO_DOSSIER n ot contained in transportable set Title: Exporting a partition with transport tablespace Hi list, is it possible to export a partition with the transportable tablespace feature ? My partition is over 8 Go. Here my statements , thank you in advance ! SQLexec sys.dbms_tts.transport_set_check('HISTO_DOSSIER_P1_MD_TAB',FALSE); SQL select * from sys.transport_set_violations; VIOLATIONS Partitioned table TOPASE.HISTO_DOSSIER is partially contained in the transportab le set: check table partitions by querying sys.dba_tab_partitions Default Partition (Table) Tablespace HISTO_DOSSIER_P2_MD_TAB for HISTO_DOSSIER n ot contained in transportable set Default Partition (Table) Tablespace HISTO_DOSSIER_P3_MD_TAB for HISTO_DOSSIER n ot contained in transportable set Default Partition (Table) Tablespace HISTO_DOSSIER_P4_MD_TAB for HISTO_DOSSIER n ot contained in transportable set
Re: Hit Ratio
As a friendly reminder, when debunking myths, I suggest we keep sober and never go overboard. The recently popular formula to get an arbitrary hit ratio is not what a database in normal usage naturally gets. Unless a mischievous developer plays a prank, hit ratios are still useful to some extent in checking database health, although other indicators such as wait events should be given a greater weight. Yong Huang __ Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing. http://photos.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Yong Huang 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: Exporting a partition with transport tablespace
wait or are you just trying to transport 1 partition? i think you have to do regular export and import. From: NGUYEN Philippe (Cetelem) [EMAIL PROTECTED] Date: 2003/12/22 Mon AM 10:34:25 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Exporting a partition with transport tablespace Hi list, is it possible to export a partition with the transportable tablespace feature ? My partition is over 8 Go. Here my statements , thank you in advance ! SQLexec sys.dbms_tts.transport_set_check('HISTO_DOSSIER_P1_MD_TAB',FALSE); SQL select * from sys.transport_set_violations; VIOLATIONS Partitioned table TOPASE.HISTO_DOSSIER is partially contained in the transportab le set: check table partitions by querying sys.dba_tab_partitions Default Partition (Table) Tablespace HISTO_DOSSIER_P2_MD_TAB for HISTO_DOSSIER n ot contained in transportable set Default Partition (Table) Tablespace HISTO_DOSSIER_P3_MD_TAB for HISTO_DOSSIER n ot contained in transportable set Default Partition (Table) Tablespace HISTO_DOSSIER_P4_MD_TAB for HISTO_DOSSIER n ot contained in transportable set Title: Exporting a partition with transport tablespace Hi list, is it possible to export a partition with the transportable tablespace feature ? My partition is over 8 Go. Here my statements , thank you in advance ! SQLexec sys.dbms_tts.transport_set_check('HISTO_DOSSIER_P1_MD_TAB',FALSE); SQL select * from sys.transport_set_violations; VIOLATIONS Partitioned table TOPASE.HISTO_DOSSIER is partially contained in the transportab le set: check table partitions by querying sys.dba_tab_partitions Default Partition (Table) Tablespace HISTO_DOSSIER_P2_MD_TAB for HISTO_DOSSIER n ot contained in transportable set Default Partition (Table) Tablespace HISTO_DOSSIER_P3_MD_TAB for HISTO_DOSSIER n ot contained in transportable set Default Partition (Table) Tablespace HISTO_DOSSIER_P4_MD_TAB for HISTO_DOSSIER n ot contained in transportable set
RE: [***SPAM***] - Re: Upgrading to Oracle 9.2.0.4 - Any pitfalls? - Found word(s) to be removed remove list error e-mail in the Text body.
Last month Oracle Support said there would be a 92045 in Janauary 2004 . -Original Message- zhu chao Sent: Sunday, December 21, 2003 11:49 PM To: Multiple recipients of list ORACLE-L - Found word(s) to be removed remove list error e-mail in the Text body. At lease 9.2.0.5 will be released by oracle. I have seen some note talking about 9.2.0.5 patchset. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, December 22, 2003 12:49 PM I have said it on this list before, and I will say it again. With Oracle, quality ends with in 4. 7.3.4 8.1.7.4 9.2.0.4 Oracle v9.2.0.4 is fairly stable. I have had to apply only 1, one-off patch related to having 1000's of partitions. You may also want to add the following to your init.ora to prevent a few known bug's serial_reuse = disable event = 10235 trace name context forever, level 2 Jones, Richard O. To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] richard.jones.1@cc: aramco.com Subject: Upgrading to Oracle 9.2.0.4 - Any pitfalls? Sent by: [EMAIL PROTECTED] .com 12/21/2003 09:29 PM Please respond to ORACLE-L Hi, Our main production database was upgraded to Oracle 9.2.0.3 (64 bit) at the end of September 2003. Platform Solaris 64bit 5.8. Since then the database has hanged and had to be manually shutdown by killing processes and re-started: - (1) The first one involved the production of numerous: ORA-04031: unable to allocate 26168 bytes of shared memory (shared pool,unknown object,sga heap(1,0),session param values) , errors when users were logging and was linked by Oracle Support with bug number 2921201 (2) Secondly, the database raised an ORA-600 to the alert file: ORA-00600: internal error code, arguments: [510], [0x380068B30], [shared pool], [], [], [], [], [] , followed by numerous messages: PMON failed to acquire latch, see PMON dump ? (3) Thirdly, an: ORA-04031: unable to allocate 16384 bytes of shared memory (shared pool,unknown object,sga heap(1,0),trace buffer) was raised apparently caused by an Oracle background processes dieing unexpectedly. Should I upgrade to 9.2.0.4? None of the above problems seem to be fixed in 9.2.0.4!!! Our database is a hybrid between OLTP and Decision-Support with a relatively light load. Anyone out there with an unstable 9i database (we were more stable under 8.1.7)? Am I alone?? Many Thanks Richard Jones, DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed
Risk of knowing password hash value (Was: OEM permissions)
Hi, Gregory, I only have access to Oracle 9.2 on my laptop. Here's my test. I have ORCL and AUX1 databases, the latter created by RMAN DUPLICATE some time ago. I logon AUX1 as SYSTEM. Set SYSTEM password hash value to the same as in ORCL. Create link L to ORCL without password. Selecting from a table in ORCL @L (i.e. select * from [EMAIL PROTECTED]) throws ORA-1017 invalid username/password. Alternatively, I logon as SYS and create a procedure owned by SYSTEM, with one line execute imediate('select count(*) from [EMAIL PROTECTED]'). When I execute system.this procedure as SYS, I get ORA-1005 null password given. (I could use DBMS_SYS_SQL but using the execute immediate trick obviates the need to remember the syntax in that undocumented package). If I use connect to current_user to create the link, I always get ORA-28030 Server encountered problems accessing LDAP directory service. Could you try on your databases and show how you do it? As I said, this may be a security problem. I'm just too ignorant of it and can't reproduce it for now. Yong Huang Norris, Gregory T [ITS] wrote: There's no reason I can see that he couldn't create the dblink first, and then reset the password using the encrypted value. Alternately, the dblink could be created using the DBMS_SYS_SQL package... no knowledge of the current password required. create database link foo connect to current_user using 'bar'; __ Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing. http://photos.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Yong Huang 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: RMAN - the time has come
Title: RMAN - the time has come In addition to the Freeman book, I would also suggest the RMAN Pocket Reference from O'Reilly. It predates the RF book and certainly comes handy for learning - I learned from there. HTH. Arup Nanda - Original Message - From: Jamadagni, Rajendra To: Multiple recipients of list ORACLE-L Sent: Monday, December 22, 2003 10:34 AM Subject: RMAN - the time has come Okay, its time to bite the bullet ... time to learn RMAN. Outside of TFM (which I just started reading), are there any good books/articles on RMAN? I know there is one by RFreeman, and it is for 9i (Robert, will there be a 10g version?) Yeah, I am also google'ing, taeoma'ing and in general stfw'ing whenever time permits. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! **This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.**4
Using dbms_stats.auto_sample_size in dbms_stats.
Title: Message To all 9i DBA's. I am trying to find out how efficient (or not ) is the option of running dbms_stats with dbms_stats.auto_sample_size. Reading metalink I see a lot of issues with the time it takesto run, and also that sample_size column is always equal to num_rows. Would like to hear from anyone who is using this in a Production environment, tia Yuval. This transmission may contain information that is privileged, confidential and exempt from disclosure under applicable law. If you, [EMAIL PROTECTED], are not the intended recipient, you are hereby notified that any disclosure, copying, distribution, or use of the information contained herein (including any reliance thereon) is STRICTLY PROHIBITED. If you received this transmission in error, please immediately contact the sender and destroy the material in its entirety, whether in electronic or hard copy format. [neumann.webloyalty.com]
Re: Exporting a partition with transport tablespace
Title: Exporting a partition with transport tablespace No it's not. However, you could simply exchange the partition with a table and then export the tablespace. For instance, ALTER TABLE HISTO_DOSSIEREXCHANGE PARTITION part1 WITH TABLE HISTO_DOSSIER_part1; Then export the tablespace. Make sure you do the same for any local indexes, too. HTH. Arup Nanda - Original Message - From: NGUYEN Philippe (Cetelem) To: Multiple recipients of list ORACLE-L Sent: Monday, December 22, 2003 10:34 AM Subject: Exporting a partition with transport tablespace Hi list, is it possible to export a partition with the transportable tablespace feature ? My partition is over 8 Go. Here my statements , thank you in advance ! SQLexec sys.dbms_tts.transport_set_check('HISTO_DOSSIER_P1_MD_TAB',FALSE); SQL select * from sys.transport_set_violations; VIOLATIONS Partitioned table TOPASE.HISTO_DOSSIER is partially contained in the transportab le set: check table partitions by querying sys.dba_tab_partitions Default Partition (Table) Tablespace HISTO_DOSSIER_P2_MD_TAB for HISTO_DOSSIER n ot contained in transportable set Default Partition (Table) Tablespace HISTO_DOSSIER_P3_MD_TAB for HISTO_DOSSIER n ot contained in transportable set Default Partition (Table) Tablespace HISTO_DOSSIER_P4_MD_TAB for HISTO_DOSSIER n ot contained in transportable set
Re: SUN question
Your answer was better than the answer I got from SUN support. Thanks and Happy Holidays. At 09:43 PM 11/27/2003 -0700, [EMAIL PROTECTED] wrote: It's hard to explain this better than the man page. It's short sweet and 2 the point. But alas, I'll try anyway. Define RAM - http://www.techweb.com/encyclopedia/defineterm?term=ram A group of memory chips, typically of the dynamic RAM (DRAM) type, which function as the computer's primary workspace. Define virtual memory - http://www.techweb.com/encyclopedia/defineterm?term=virtual++memory Simulating more memory than actually exists, allowing the computer to run larger programs or more programs concurrently. It breaks up the program into small segments, called pages, and brings as many pages into memory that fit into a reserved area for that program. When additional pages are required, it makes room for them by swapping them to disk. It keeps track of pages that have been modified, so that they can be retrieved when needed again. Define total size of the process - http://www.from_my_brain.dmp This is EVERYTHING it takes to run your program. The code, the process stack, variables, device drivers, shared libraries, memory mapped files (see man -a mmap), shared memory segments (see man -a shmget) and all other IPC type of communications. The key word is EVERYTHING. Remember that all Oracle processes are sharing the same shared memory segment so they are all charged, or appear bigger in total size, because of that. So what it all comes down to is that on Unix machines that are running an Oracle database the SIZE column means just about squat. Even the more important ps command output column RSS is toast for database processes. So now you want to know what the RSS column is right. It stands for resident set size and is defined as The amount of physical memory allocated to a process (see RAM). The ps command output is good for most non Oracle processes and non Java Server Processes (don't ask). But now you are still trying to answer the question your boss asked, How much memory is Oracle using on this server. If you are on Sun Solaris then the answer is only a click away (scroll down a bit and look for the script under Calculate actual memory usage) - http://renko.org/info/ora816/solaris.816/a77184/ch1.htm#39824 If you are on anything else, then a SWAG ( see the first definition - http://www.acronymfinder.com/af-query.asp?String=exactAcronym=swagFind=Find) will have to do. Whew!! Jerome Roa [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] u cc: Sent by: Subject: SUN question [EMAIL PROTECTED] .com 11/25/2003 08:34 AM Please respond to ORACLE-L Does anybody know what the SZ is represents(SUN SOLARIS) ps -efly | egrep 'ora|SZ' bach# ps -efly | egrep 'oracle|SZ' S UID PID PPID C PRI NI RSS SZ WCHANSTIME TTY TIME CMD S oracle 27845 1 0 77 20596552 617600? 02:00:04 ?0:26 oracleTRACS (DESCRIPTION=(LOCAL=no) S oracle 27169 1 0 51 20599536 631296? 23:14:36 ?0:53 oracleTRACS (DESCRIPTION=(LOCAL=no) S oracle 521 1 0 53 202968 9528? Oct 04 ? 1:30 /dbopt/app/oracle/product/817/bin/t man pages states(-y converts it to Kb instead of pages): SZ (l) The total size of the process in virtual memory, including all mapped files and devices, in pages. See pagesize(1). What does this mean? Does this mean that peocess 27845 is using 617600Kb of RAM? Thanks. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jerome Roa INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jerome Roa 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
RE: Exporting a partition with transport tablespace
Title: RE: Exporting a partition with transport tablespace Yes, my partitions are in separate tablespaces so do I have to export all the tablespaces concerned ? wich solution do you use ? each partition is over 8 Go so should I use common export command to backup only one tablespace ? -Message d'origine- De : [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]De la part de [EMAIL PROTECTED] Envoyé : lundi 22 décembre 2003 17:09 À : Multiple recipients of list ORACLE-L Objet : Re: Exporting a partition with transport tablespace transportable tablespaces need to be totally self-contained. everything that is being transported has to be in that tablespace. it doesnt matter if its a different datafile. you probably have your partitions in seperate tablespaces? or am i wrong? From: NGUYEN Philippe (Cetelem) [EMAIL PROTECTED] Date: 2003/12/22 Mon AM 10:34:25 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Exporting a partition with transport tablespace Hi list, is it possible to export a partition with the transportable tablespace feature ? My partition is over 8 Go. Here my statements , thank you in advance ! SQLexec sys.dbms_tts.transport_set_check('HISTO_DOSSIER_P1_MD_TAB',FALSE); SQL select * from sys.transport_set_violations; VIOLATIONS Partitioned table TOPASE.HISTO_DOSSIER is partially contained in the transportab le set: check table partitions by querying sys.dba_tab_partitions Default Partition (Table) Tablespace HISTO_DOSSIER_P2_MD_TAB for HISTO_DOSSIER n ot contained in transportable set Default Partition (Table) Tablespace HISTO_DOSSIER_P3_MD_TAB for HISTO_DOSSIER n ot contained in transportable set Default Partition (Table) Tablespace HISTO_DOSSIER_P4_MD_TAB for HISTO_DOSSIER n ot contained in transportable set
RE: Hit Ratio
My BCHR is currently 96.62%. In the past, it was normally over 99%. What should I do? I'll be waiting for Mladen's reply... :) Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA -Original Message- Sent: Monday, December 22, 2003 10:14 AM To: Multiple recipients of list ORACLE-L As a friendly reminder, when debunking myths, I suggest we keep sober and never go overboard. The recently popular formula to get an arbitrary hit ratio is not what a database in normal usage naturally gets. Unless a mischievous developer plays a prank, hit ratios are still useful to some extent in checking database health, although other indicators such as wait events should be given a greater weight. Yong Huang -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- 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).
RE: 10g new features question for beta testers
But I thought this was the perfect opportunity for Miracle to fill any perceived gap in support? :) Pete Controlling developers is like herding cats. Kevin Loney, Oracle DBA Handbook Oh no, it's not. It's much harder than that! Bruce Pihlamae, long-term Oracle DBA -Original Message- Mogens Nørgaard Sent: Sunday, December 21, 2003 10:14 PM To: Multiple recipients of list ORACLE-L It's the Best of Breed versus One Vendor debate, and there are pros and cons galore. The perfect scenario, of course, is when they combine, so one vendor delivers the best of everything. That's what we have with Microsoft, isn't it? ;-) : Office stuff, OS, Database, ERP, CRM, video player, what have you... Then on the Support side of things, it's indeed good to be able to call One Vendor Only... if that vendor is good at Support. If he isn't, you might be better off if you have more than one option for calling. Mogens Pete Sharman wrote: Just a couple of comments on this which hopefully won't go down the Marketing track too far. :) 1. I'm pretty sure Steve Adams agrees with you, since he co-presented on ASM at OracleWorld in San Fran. Not sure if he monitors this group actively or not, but I believe the presentation he did is loaded with all the other OracleWorld 2003 presentations so you can see what he said. 2. One point which makes a lot of sense to me, and it happens in a variety of places in 10g such as ASM and the RAC clusterware. If you have one vendor to raise an issue with (not that you'd need to do that with Oracle of course!), it's a lot easier to get an answer without the finger pointing that can go on between vendors. Take the clusterware example - if you run into a problem running RAC on Sun with the Sun Cluster technology and Veritas owning the disk side, who you gonna call? GhostBusters, maybe! But if you're running RAC on Sun with Oracle's clusterware and ASM, it's a lot easier to determine who to call. Pete Controlling developers is like herding cats. Kevin Loney, Oracle DBA Handbook Oh no, it's not. It's much harder than that! Bruce Pihlamae, long-term Oracle DBA -Original Message- Connor McDonald Sent: Saturday, December 20, 2003 2:34 AM To: Multiple recipients of list ORACLE-L As with anything I suppose, if a single vendor can be in control of more of the stack between application and physical server structure then there is a greater opportunity for benefits. For example, ASM offers the ability to add disks to a stripe without needing to redistribute(reload) the entire stripeset. A (bug-free) ASM product looks very very impressive to me. Time will tell how close Oracle are to achieving it. hth connor --- [EMAIL PROTECTED] wrote: no ASMs are considerably different. Its supposed to manage everything. You dont give it a file, you give it entire disks and oracle does everything. Sets up files, manages, I/O, everything. you only look at the tablespace level. you dont even install any software on it. If your on SAN, you dont install SAN software on it. From: Goulet, Dick [EMAIL PROTECTED] Date: 2003/12/19 Fri AM 09:14:27 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: 10g new features question for beta testers That is not exactly a new feature. Oracle 9i has Oracle Managed Files where you give it a directory and then just build tablespaces. The database picks the filenames for you. Now mind you it does work, but I'll be damned if I use it in anything other than a development environment. For some reason Oracle has never gotten over that DUMB SAME (Stripe And Mirror Everything) idea. The concept is great in theory, but in practice it's absolutely abysmal at best. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Friday, December 19, 2003 8:24 AM To: Multiple recipients of list ORACLE-L I saw a presentation from Oracle on 10g new features last night in Reston,VA. I know atleast one other person from the list was there. Since Oracle is releasing details and its going to be released(in theory) in the next 2 weeks, I was wondering if you guys could talk about it. 1. does ASMs work as well as Oracle claims? I always wonder about first generation features... takes most software vendors a couple of generations to get it right(takes any project Im on just as long). This is a radical departure. for those of you who dont know. Oracle claims that they will manage your disks for you. All you do is give Oracle some Raw Disks and Oracle will set up, and handle all your datafiles. All you do is look at logical tablespaces. It will also handle I/O balancing. How well does this work? Anyone test it with a SAN? 2. RAC Load Balancing. Oracle claims that you only need Oracle software from now on. They also claim that you can load balance multiple applications. Lets say you have One
Re: RE: Hit Ratio
i dont think many people are using bchr anymore. I think its been talked down to death. only place I hear about it is offshore. people still using the old niemic book. his new took all that stuff out. or am i wrong? From: Jesse, Rich [EMAIL PROTECTED] Date: 2003/12/22 Mon PM 02:14:26 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: Hit Ratio My BCHR is currently 96.62%. In the past, it was normally over 99%. What should I do? I'll be waiting for Mladen's reply... :) Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA -Original Message- Sent: Monday, December 22, 2003 10:14 AM To: Multiple recipients of list ORACLE-L As a friendly reminder, when debunking myths, I suggest we keep sober and never go overboard. The recently popular formula to get an arbitrary hit ratio is not what a database in normal usage naturally gets. Unless a mischievous developer plays a prank, hit ratios are still useful to some extent in checking database health, although other indicators such as wait events should be given a greater weight. Yong Huang -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Rollback datafile size
The datafile for our rollback segments gre huge because of an improper setting. Even setting optimal and shrink, etc., won't help because the segments are scattered. Do I have to create a new tablespace? Can I change the datafile a rollback segment writes to and then take the other offline? The datafile is 4.6 GB but the segments are small, so all that space is wasted. Any help would be appreciated. I'm trying to read up more, but have found nothing about what to do when shrink won't work. Thanks, Michael Milligan Oracle DBA Ingenix, Inc. 2525 Lake Park Blvd. Salt Lake City, Utah 84120 wrk 801-982-3081 mbl 801-628-6058 [EMAIL PROTECTED] This e-mail, including attachments, may include confidential and/or proprietary information, and may be used only by the person or entity to which it is addressed. If the reader of this e-mail is not the intended recipient or his or her authorized agent, the reader is hereby notified that any dissemination, distribution or copying of this e-mail is prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and delete this e-mail immediately. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Michael Milligan 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).
(U) Hello All Again ... Christmas, Cary, Mysql, Perl, etc. ....
CLASSIFICATION: UNCLASSIFIED I have been off on a brand new program for a couple months now and haven't really been working with Oracle all that much. That will come later. Doing alot of prototyping in Perl and Mysql (gag) for this big project.I let everyone know that once the terabytes start flowing in MySql will no longer be an option. Just wanted to wish you all a very Merry Christmas. Cary, I started your book, but have only gotten through about 1/4 of it. Will comment later, but its obvious you write very well. Have a safe holiday everyone and we will see you soon. Mike Classification: UNCLASSIFIED -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Johnson, Michael 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).
(U) RE: RE: Hit Ratio
CLASSIFICATION: UNCLASSIFIED Rich, Burn any source that talks about hit ratio's. What exactly is running slow in your system and at what times ? Talk directly to the user that is experiencing the slowdown and ask them to repeat the behavior. Set a 10046 trace and go find the slowdown while the user is executing the application. Use the wait interface to determine what the culprit is. Lots of good books out on this stuff now. Is any batch job running at this time ? -Original Message- Sent: Monday, December 22, 2003 11:49 AM To: Multiple recipients of list ORACLE-L i dont think many people are using bchr anymore. I think its been talked down to death. only place I hear about it is offshore. people still using the old niemic book. his new took all that stuff out. or am i wrong? From: Jesse, Rich [EMAIL PROTECTED] Date: 2003/12/22 Mon PM 02:14:26 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: Hit Ratio My BCHR is currently 96.62%. In the past, it was normally over 99%. What should I do? I'll be waiting for Mladen's reply... :) Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA -Original Message- Sent: Monday, December 22, 2003 10:14 AM To: Multiple recipients of list ORACLE-L As a friendly reminder, when debunking myths, I suggest we keep sober and never go overboard. The recently popular formula to get an arbitrary hit ratio is not what a database in normal usage naturally gets. Unless a mischievous developer plays a prank, hit ratios are still useful to some extent in checking database health, although other indicators such as wait events should be given a greater weight. Yong Huang -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Classification: UNCLASSIFIED -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Johnson, Michael 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).
stress tests for a scale to 30,000 users
My estimate right now is about a 500GB instance(but could grow). There are several complexities. 1. high transaction system, but also will have alot of long running queries 2. We deliver data daily and rebuild large parts of the database nightly with loads. Im not certain I have the window to analyze every index or get histograms on all the tables. There are VERY large data loads and deliveries. Data has to be delivered by a certain time and we get data feeds from other groups. I cannot control when we recieve data to load. 3. We will not be actively managing the production server. Its going to be delivered as an off the shelf product. I do not know what statistics ill be allowed to have for security reasons(this is not govenment stuff so dont worry about what I say). Its up to the client. 4. We are using web server level connection pooling so tracing isnt very useful. Im essentially the lone performance guy on the team. Ive never done a scale up this large, or with this many complexities. We just managed to convince them to use bind variables... but they haven't been implemented yet. Im having trouble getting accurate test cases. This is what I am 'attempting' to do at first. Please let me know if my approach is accurate. 1. Find out which queries will be run the most. Are there things people will do in the mornings, but not in the afternoon(so far its 'dunno'). 2. Hopefully, I can get a hold of either the use cases or 'preferebly' test cases, so we can design our stress tests around actual user processes. All they are doing now is opening up 50+ users and running queries in loops. What other approach should I take to get started. Im rather troubled by this... -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Hit Ratio
At 11:14 22-12-03 -0800, you wrote: My BCHR is currently 96.62%. In the past, it was normally over 99%. What should I do? I'll be waiting for Mladen's reply... :) Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA Go to www.oracledba.co.uk (Connor) or go to O'Reilly (download page of Cary's book), and download one of the fabulous BCHR enhancement scripts. Especially when your bonus depends on it, this is a good time to perform some BCHR tuning. Regards, Carel-Jan === If you think education is expensive, try ignorance. (Derek Bok) === -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Carel-Jan Engel 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: 10g new features question for beta testers
-Original Message- Mogens Nørgaard Sent: Sunday, December 21, 2003 10:14 PM To: Multiple recipients of list ORACLE-L It's the Best of Breed versus One Vendor debate, and there are pros and cons galore. The perfect scenario, of course, is when they combine, so one vendor delivers the best of everything. That's what we have with Microsoft, isn't it? ;-) : Office stuff, OS, Database, ERP, CRM, video player, what have you... Then on the Support side of things, it's indeed good to be able to call One Vendor Only... if that vendor is good at Support. If he isn't, you might be better off if you have more than one option for calling. Mogens Or, when you happen to be a RDD (Robbed Drunk Dane) in Paris, and you're able to call a particular Danish non-Vendor. That might be even better ;-) Regards, Carel-Jan === If you think education is expensive, try ignorance. (Derek Bok) ===
RE: RE: Hit Ratio
Erm...sorry. I said :) when I should've said ;). Joke. Pun. Tongue-in-cheek. Yer built too low. The fast ones keep going over your head. Gotta keep your eye on the ball. Eye. Ball. That's a joke there, son. Again, sorry. We're in no change mode until after the Holidaze and Foghorn Leghorn's gotta hold of me brain. Back to the Battle With The Vendor over not closing cursors... :) Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA -Original Message- Sent: Monday, December 22, 2003 1:49 PM To: Multiple recipients of list ORACLE-L i dont think many people are using bchr anymore. I think its been talked down to death. only place I hear about it is offshore. people still using the old niemic book. his new took all that stuff out. or am i wrong? From: Jesse, Rich [EMAIL PROTECTED] Date: 2003/12/22 Mon PM 02:14:26 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: Hit Ratio My BCHR is currently 96.62%. In the past, it was normally over 99%. What should I do? I'll be waiting for Mladen's reply... :) Rich -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- 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).
RE: RMAN - the time has come
Title: RMAN - the time has come Try Robert Freeman's book. I have only heard good things about it. Ruth -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]On Behalf Of Jamadagni, RajendraSent: Monday, December 22, 2003 10:35 AMTo: Multiple recipients of list ORACLE-LSubject: RMAN - the time has come Okay, its time to bite the bullet ... time to learn RMAN. Outside of TFM (which I just started reading), are there any good books/articles on RMAN? I know there is one by RFreeman, and it is for 9i (Robert, will there be a 10g version?) Yeah, I am also google'ing, taeoma'ing and in general stfw'ing whenever time permits. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! **This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.**4
STATSPACK interpretation
Title: STATSPACK interpretation We recently experienced a crash on our prod datewarehouse running 9.2.0.2 on AIX 4.3.3. The cause of the crash was 4031 errors generated by background processes (Oracle support has confirmed there is a bug involved), however, since that crash occurred, a certain nightly batch job has slowed to a crawl. Trying to recreate what has happened, I came across this in the STATSPACK report. The interval for this report is 30 minutes. Is it telling me that I have 746 versions of this call eating up 400+ mb at the time of the snapshot? Why would that be? The procedure in question uses bind variables. SQL ordered by Sharable Memory for DB: DSSP Instance: DSSP Snaps: 3309 -3310 - End Sharable Memory Threshold: 1048576 Sharable Mem (b) Executions % Total Hash Value --- 483,580,268 57 411.8 539672786 Module: [EMAIL PROTECTED] (TNS V1-V3) BEGIN GENERATE_PRODUCT_KEYS (:1,:2,:3,:4) ; END; - SQL ordered by Version Count for DB: DSSP Instance: DSSP Snaps: 3309 -3310 - End Version Count Threshold: 20 Version Count Executions Hash Value 746 57 539672786 Module: [EMAIL PROTECTED] (TNS V1-V3) BEGIN GENERATE_PRODUCT_KEYS (:1,:2,:3,:4) ; END; Jeffery D Thomas DBA Thomson Information Services Thomson, Inc. Email: [EMAIL PROTECTED] Indy DBA Master Documentation available at: http://gkmqp.tce.com/tis_dba
RE: RE: Hit Ratio
Rich, you mean due to 'no change mode' you can't even change your hit ratio ... too bad. Happy holidays everyone !! Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Monday, December 22, 2003 3:35 PM To: Multiple recipients of list ORACLE-L Erm...sorry. I said :) when I should've said ;). Joke. Pun. Tongue-in-cheek. Yer built too low. The fast ones keep going over your head. Gotta keep your eye on the ball. Eye. Ball. That's a joke there, son. Again, sorry. We're in no change mode until after the Holidaze and Foghorn Leghorn's gotta hold of me brain. Back to the Battle With The Vendor over not closing cursors... ** This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. **4 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra 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: Rollback datafile size
There is no need to drop the datafiles, just recreate the segments in the current tablespace. Pick a slow time to do this as you will significantly increase the likelihood of 1555s. 1) Offline rbs1 2) Drop rbs1 3) create rbs1 4) Repeat steps 1 - 3 for all rbs2..rbsN. 5) When complete, resize datafiles appropriately. Don't go to small or you run the risk of transactions failing due to unable to extend errors. Daniel Fink Michael Milligan wrote: The datafile for our rollback segments gre huge because of an improper setting. Even setting optimal and shrink, etc., won't help because the segments are scattered. Do I have to create a new tablespace? Can I change the datafile a rollback segment writes to and then take the other offline? The datafile is 4.6 GB but the segments are small, so all that space is wasted. Any help would be appreciated. I'm trying to read up more, but have found nothing about what to do when shrink won't work. Thanks, Michael Milligan Oracle DBA Ingenix, Inc. 2525 Lake Park Blvd. Salt Lake City, Utah 84120 wrk 801-982-3081 mbl 801-628-6058 [EMAIL PROTECTED] This e-mail, including attachments, may include confidential and/or proprietary information, and may be used only by the person or entity to which it is addressed. If the reader of this e-mail is not the intended recipient or his or her authorized agent, the reader is hereby notified that any dissemination, distribution or copying of this e-mail is prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and delete this e-mail immediately. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Michael Milligan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Daniel Fink 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: Exporting a partition with transport tablespace
Title: Exporting a partition with transport tablespace You could create new table in transportable tablespace, exchange data with the partition you want to export, move transportable tablespace file to the destination system, exchange data back from the table into desired partition. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of NGUYEN Philippe (Cetelem) Sent: Monday, December 22, 2003 10:34 AM To: Multiple recipients of list ORACLE-L Subject: Exporting a partition with transport tablespace Hi list, is it possible to export a partition with the transportable tablespace feature ? My partition is over 8 Go. Here my statements , thank you in advance ! SQLexec sys.dbms_tts.transport_set_check('HISTO_DOSSIER_P1_MD_TAB',FALSE); SQL select * from sys.transport_set_violations; VIOLATIONS Partitioned table TOPASE.HISTO_DOSSIER is partially contained in the transportab le set: check table partitions by querying sys.dba_tab_partitions Default Partition (Table) Tablespace HISTO_DOSSIER_P2_MD_TAB for HISTO_DOSSIER n ot contained in transportable set Default Partition (Table) Tablespace HISTO_DOSSIER_P3_MD_TAB for HISTO_DOSSIER n ot contained in transportable set Default Partition (Table) Tablespace HISTO_DOSSIER_P4_MD_TAB for HISTO_DOSSIER n ot contained in transportable set
RE: Rollback datafile size
Daniel, Thank-you very much for your clear answer. Very helpful. When a RBS is taken off-line, does it transfer the rollback information to another segment? Thanks, Mike -Original Message- Sent: Monday, December 22, 2003 1:44 PM To: Multiple recipients of list ORACLE-L There is no need to drop the datafiles, just recreate the segments in the current tablespace. Pick a slow time to do this as you will significantly increase the likelihood of 1555s. 1) Offline rbs1 2) Drop rbs1 3) create rbs1 4) Repeat steps 1 - 3 for all rbs2..rbsN. 5) When complete, resize datafiles appropriately. Don't go to small or you run the risk of transactions failing due to unable to extend errors. Daniel Fink Michael Milligan wrote: The datafile for our rollback segments gre huge because of an improper setting. Even setting optimal and shrink, etc., won't help because the segments are scattered. Do I have to create a new tablespace? Can I change the datafile a rollback segment writes to and then take the other offline? The datafile is 4.6 GB but the segments are small, so all that space is wasted. Any help would be appreciated. I'm trying to read up more, but have found nothing about what to do when shrink won't work. Thanks, Michael Milligan Oracle DBA Ingenix, Inc. 2525 Lake Park Blvd. Salt Lake City, Utah 84120 wrk 801-982-3081 mbl 801-628-6058 [EMAIL PROTECTED] This e-mail, including attachments, may include confidential and/or proprietary information, and may be used only by the person or entity to which it is addressed. If the reader of this e-mail is not the intended recipient or his or her authorized agent, the reader is hereby notified that any dissemination, distribution or copying of this e-mail is prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and delete this e-mail immediately. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Michael Milligan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Daniel Fink 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). This e-mail, including attachments, may include confidential and/or proprietary information, and may be used only by the person or entity to which it is addressed. If the reader of this e-mail is not the intended recipient or his or her authorized agent, the reader is hereby notified that any dissemination, distribution or copying of this e-mail is prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and delete this e-mail immediately. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Michael Milligan 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: RMAN - the time has come
We have talked about a 10g version of the book, which I'm sure will happen at some point in time :-) Robert -Original Message- To: Multiple recipients of list ORACLE-L Sent: 12/22/2003 2:39 PM Try Robert Freeman's book. I have only heard good things about it. Ruth -Original Message- Jamadagni, Rajendra Sent: Monday, December 22, 2003 10:35 AM To: Multiple recipients of list ORACLE-L Okay, its time to bite the bullet ... time to learn RMAN. Outside of TFM (which I just started reading), are there any good books/articles on RMAN? I know there is one by RFreeman, and it is for 9i (Robert, will there be a 10g version?) Yeah, I am also google'ing, taeoma'ing and in general stfw'ing whenever time permits. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! ** This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. **4 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Freeman Robert - IL 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: stress tests for a scale to 30,000 users
Inline... [EMAIL PROTECTED] wrote: My estimate right now is about a 500GB instance(but could grow). There are several complexities. 1. high transaction system, but also will have alot of long running queries Hello, 1555s! I think you will be plagued by these, even with a high number/size of rollback/undo segments. Any chance to push the queries to a DSS? 2. We deliver data daily and rebuild large parts of the database nightly with loads. Im not certain I have the window to analyze every index or get histograms on all the tables. There are VERY large data loads and deliveries. Data has to be delivered by a certain time and we get data feeds from other groups. I cannot control when we recieve data to load. Why analyze every night? If the tables are being rebuilt every night, how much will be changing? If the size/nature of the data and objects are basically the same, populate it with a set of statistics that will enable the CBO to make good decisions and leave it alone. Keep an eye on the data and adjust the stats as needed. If there are changes, would it be possible to determine the new stats and then populate the tables accordingly using dbms_stats? 3. We will not be actively managing the production server. Its going to be delivered as an off the shelf product. I do not know what statistics ill be allowed to have for security reasons(this is not govenment stuff so dont worry about what I say). Its up to the client. 4. We are using web server level connection pooling so tracing isnt very useful. Im essentially the lone performance guy on the team. Ive never done a scale up this large, or with this many complexities. We just managed to convince them to use bind variables... but they haven't been implemented yet. Im having trouble getting accurate test cases. This is what I am 'attempting' to do at first. Please let me know if my approach is accurate. 1. Find out which queries will be run the most. Are there things people will do in the mornings, but not in the afternoon(so far its 'dunno'). 2. Hopefully, I can get a hold of either the use cases or 'preferebly' test cases, so we can design our stress tests around actual user processes. All they are doing now is opening up 50+ users and running queries in loops. I think you are on the right track. If you can turn on tracing with a logon trigger, you should be able to get some/most(?) of the sql and the order in which they are performed. Strip out the extraneous info and you have a test script. James Morle of the Oak Table (www.scaleabilities.com) had a presentation at UKOUG 2003 about using 10046 files for benchmarking. It is not on his site yet, but perhaps we could persuade him to post it (it was excellent!). What other approach should I take to get started. Im rather troubled by this... -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Daniel Fink 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: Rollback datafile size
Mike, When an RBS is taken offline, the undo information is not transfered to another segment, hence the need to perform these actions at a quiet time. When an rbs is taken offline, all transactions currently using the rbs are allowed to complete (either commit or rollback). As these transactions are processing, the status of the segment is 'Pending Offline'. Once the tx is complete, the status will change to 'Offline'. If a query is running and needs undo entries from an offline (including pending) rbs, it will fail with a 1555. Daniel Michael Milligan wrote: Daniel, Thank-you very much for your clear answer. Very helpful. When a RBS is taken off-line, does it transfer the rollback information to another segment? Thanks, Mike -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Daniel Fink 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: stress tests for a scale to 30,000 users
At 13:14 22-12-03 -0800, you wrote: I think you are on the right track. If you can turn on tracing with a logon trigger, you should be able to get some/most(?) of the sql and the order in which they are performed. Strip out the extraneous info and you have a test script. James Morle of the Oak Table (www.scaleabilities.com) had a presentation at UKOUG 2003 about using 10046 files for benchmarking. It is not on his site yet, but perhaps we could persuade him to post it (it was excellent!). You can find James wonderfull paper at http://www.oaktable.net/getFile?id=65. Regards, Carel-Jan === If you think education is expensive, try ignorance. (Derek Bok) === -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Carel-Jan Engel 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: Rollback datafile size
Daniel, Thank you again. That clears it up even more. I'll wait for a quiet time and then proceed. Mike -Original Message- Sent: Monday, December 22, 2003 2:19 PM To: Multiple recipients of list ORACLE-L Mike, When an RBS is taken offline, the undo information is not transfered to another segment, hence the need to perform these actions at a quiet time. When an rbs is taken offline, all transactions currently using the rbs are allowed to complete (either commit or rollback). As these transactions are processing, the status of the segment is 'Pending Offline'. Once the tx is complete, the status will change to 'Offline'. If a query is running and needs undo entries from an offline (including pending) rbs, it will fail with a 1555. Daniel Michael Milligan wrote: Daniel, Thank-you very much for your clear answer. Very helpful. When a RBS is taken off-line, does it transfer the rollback information to another segment? Thanks, Mike -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Daniel Fink 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). This e-mail, including attachments, may include confidential and/or proprietary information, and may be used only by the person or entity to which it is addressed. If the reader of this e-mail is not the intended recipient or his or her authorized agent, the reader is hereby notified that any dissemination, distribution or copying of this e-mail is prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and delete this e-mail immediately. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Michael Milligan 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: stress tests for a scale to 30,000 users
- Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, December 22, 2003 4:14 PM Inline... [EMAIL PROTECTED] wrote: My estimate right now is about a 500GB instance(but could grow). There are several complexities. 1. high transaction system, but also will have alot of long running queries Hello, 1555s! I think you will be plagued by these, even with a high number/size of rollback/undo segments. Any chance to push the queries to a DSS? we are on 9.2, Im hoping a large undo tablespace will be ok. Most of the long running queries will be in query only tables though.(i think... getting too many 'dunnos' when i ask questions). however, cant guarantee they will use undo tablespaces since I cant control or even look at the production instance. 2. We deliver data daily and rebuild large parts of the database nightly with loads. Im not certain I have the window to analyze every index or get histograms on all the tables. There are VERY large data loads and deliveries. Data has to be delivered by a certain time and we get data feeds from other groups. I cannot control when we recieve data to load. Why analyze every night? If the tables are being rebuilt every night, how much will be changing? If the size/nature of the data and objects are basically the same, populate it with a set of statistics that will enable the CBO to make good decisions and leave it alone. Keep an eye on the data and adjust the stats as needed. If there are changes, would it be possible to determine the new stats and then populate the tables accordingly using dbms_stats? 3. We will not be actively managing the production server. Its going to be delivered as an off the shelf product. I do not know what statistics ill be allowed to have for security reasons(this is not govenment stuff so dont worry about what I say). Its up to the client. 4. We are using web server level connection pooling so tracing isnt very useful. Im essentially the lone performance guy on the team. Ive never done a scale up this large, or with this many complexities. We just managed to convince them to use bind variables... but they haven't been implemented yet. Im having trouble getting accurate test cases. This is what I am 'attempting' to do at first. Please let me know if my approach is accurate. 1. Find out which queries will be run the most. Are there things people will do in the mornings, but not in the afternoon(so far its 'dunno'). 2. Hopefully, I can get a hold of either the use cases or 'preferebly' test cases, so we can design our stress tests around actual user processes. All they are doing now is opening up 50+ users and running queries in loops. I think you are on the right track. If you can turn on tracing with a logon trigger, you should be able to get some/most(?) of the sql and the order in which they are performed. Strip out the extraneous info and you have a test script. James Morle of the Oak Table (www.scaleabilities.com) had a presentation at UKOUG 2003 about using 10046 files for benchmarking. It is not on his site yet, but perhaps we could persuade him to post it (it was excellent!). What other approach should I take to get started. Im rather troubled by this... -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Daniel Fink INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ryan 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
Re: stress tests for a scale to 30,000 users
see my responses below... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, December 22, 2003 4:14 PM Inline... [EMAIL PROTECTED] wrote: My estimate right now is about a 500GB instance(but could grow). There are several complexities. 1. high transaction system, but also will have alot of long running queries Hello, 1555s! I think you will be plagued by these, even with a high number/size of rollback/undo segments. Any chance to push the queries to a DSS? 2. We deliver data daily and rebuild large parts of the database nightly with loads. Im not certain I have the window to analyze every index or get histograms on all the tables. There are VERY large data loads and deliveries. Data has to be delivered by a certain time and we get data feeds from other groups. I cannot control when we recieve data to load. Why analyze every night? If the tables are being rebuilt every night, how much will be changing? If the size/nature of the data and objects are basically the same, populate it with a set of statistics that will enable the CBO to make good decisions and leave it alone. Keep an eye on the data and adjust the stats as needed. If there are changes, would it be possible to determine the new stats and then populate the tables accordingly using dbms_stats? 1. we have alot of data feeds. we do ingestion from multiple instances. So 'monitoring' is problematic(plus I have alot to do... I write as much code as I do dba stuff). 2. I cannot guarantee that the tables will be the same. One time we had a 29 million row tables with 17 million deletes. We do alot of full refreshes. I cannot guarantee data being the same. I cannot guarantee that 90% of the values in one column will be updated and the 'spread' of data will change. 3. remember, I do not know if Ill even be able to see statistics in production or have any say on parameter settings, etc... 3. We will not be actively managing the production server. Its going to be delivered as an off the shelf product. I do not know what statistics ill be allowed to have for security reasons(this is not govenment stuff so dont worry about what I say). Its up to the client. 4. We are using web server level connection pooling so tracing isnt very useful. Im essentially the lone performance guy on the team. Ive never done a scale up this large, or with this many complexities. We just managed to convince them to use bind variables... but they haven't been implemented yet. Im having trouble getting accurate test cases. This is what I am 'attempting' to do at first. Please let me know if my approach is accurate. 1. Find out which queries will be run the most. Are there things people will do in the mornings, but not in the afternoon(so far its 'dunno'). 2. Hopefully, I can get a hold of either the use cases or 'preferebly' test cases, so we can design our stress tests around actual user processes. All they are doing now is opening up 50+ users and running queries in loops. I think you are on the right track. If you can turn on tracing with a logon trigger, you should be able to get some/most(?) of the sql and the order in which they are performed. Strip out the extraneous info and you have a test script. James Morle of the Oak Table (www.scaleabilities.com) had a presentation at UKOUG 2003 about using 10046 files for benchmarking. It is not on his site yet, but perhaps we could persuade him to post it (it was excellent!). im using statspack. Ill read morle's article. I have his book, but havent been able to read it yet. He seems like a smart guy. This is a 9.2 instance. I dont know if I can get any 10046 settings in production... This REALLY concerns me. What other approach should I take to get started. Im rather troubled by this... -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Daniel Fink 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
ORA-00600:arguments: [kdibc3position
Hi. I'm getting ora 6000 error with the kdibc3position argument in a prod DB. I have read some information on that issue on the metalink, but the only workaround that I seem to have found was to drop and rebuild the bitmap indices on the table. I couldn't see any info as to how to prevent it in Oracle 8174. Does anyone has any experience with that thing besides drop/recreate indices? thanks Gene __ 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: Gene Gurevich 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).
Delete vs. truncate to free up spaces.
Title: Delete vs. truncate to free up spaces. I am using delete command to delete million records in several tables to free up space in tablespace. I understand delete command does not release unused spaces as truncate command but I could not use truncate to delete ALL records in table as I need to keep one month old of records in table. Please advise a better method I can use to free up spaces. Thanks, David
RE: Delete vs. truncate to free up spaces.
This is one of the cases where a partitioned table can be of great use. What version of Oracle? Standard or Enterprise Edition? With a partitioned table you can say alter table ... drop partition ... ; to easily get rid of a large chunk of data and release the space. See Oracle9i Database Concepts Release 2 (9.2) Part Number A96524-01 Chapter 11 Partitioned Tables and Indexes http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96524/c12parti.htm#464767 or http://tinyurl.com/362ba -Original Message- Nguyen, David M I am using delete command to delete million records in several tables to free up space in tablespace. I understand delete command does not release unused spaces as truncate command but I could not use truncate to delete ALL records in table as I need to keep one month old of records in table. Please advise a better method I can use to free up spaces. Thanks, David -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer 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: Delete vs. truncate to free up spaces.
you can also do the following: 1. create table as and copy just the records you want to keep. 2. Drop the old table 3. rename new table to old table 4. re-create the indexes. if there are alot, issue them as jobs and do it at the same time. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, December 22, 2003 5:44 PM This is one of the cases where a partitioned table can be of great use. What version of Oracle? Standard or Enterprise Edition? With a partitioned table you can say alter table ... drop partition ... ; to easily get rid of a large chunk of data and release the space. See Oracle9i Database Concepts Release 2 (9.2) Part Number A96524-01 Chapter 11 Partitioned Tables and Indexes http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96524/c12parti .htm#464767 or http://tinyurl.com/362ba -Original Message- Nguyen, David M I am using delete command to delete million records in several tables to free up space in tablespace. I understand delete command does not release unused spaces as truncate command but I could not use truncate to delete ALL records in table as I need to keep one month old of records in table. Please advise a better method I can use to free up spaces. Thanks, David -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ryan 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: Delete vs. truncate to free up spaces.
Nguyen, David M wrote: I am using delete command to delete million records in several tables to free up space in tablespace. I understand delete command does not release unused spaces as truncate command but I could not use truncate to delete ALL records in table as I need to keep one month old of records in table. Please advise a better method I can use to free up spaces. Thanks, David David, Several solutions. If you have paid for the partitioning option, you can truncate partitions. Otherwise, it may be faster to do a CREATE TABLE AS SELECT with nologging, parallel, blahblah, to select all the lines you want to keep, truncate the table, and INSERT ... SELECT back in the same manner. Better to have your database in restricted mode then. I used this method in a (euro-zone) bank to delete everything but CHF, GBP and the like when we waved farewell to national currencies, worked pretty efficiently. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult 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: Risk of knowing password hash value (Was: OEM permissions)
Environment: DB1: RH 8.0 with Oracle EE 9.2.0.4 DB2: Win2k SP3 with Oracle EE 9.2.0.1 SYSTEM user on each database initially have different passwords. It goes something like this: DB1: select password from dba_users where username = 'SYSTEM'; Let's say the result is 'AC424SDK4398' DB2: Logon to DB2 as SYSTEM. alter user SYSTEM identified by values 'AC424SDK4398'; create database link systemlink using 'DB1'; Logout, and log back on to DB2 as SYSTEM. select count(*) from [EMAIL PROTECTED]; Works for me in this environment. DB2 is compromised. HTH Jared On Mon, 2003-12-22 at 08:29, Yong Huang wrote: Hi, Gregory, I only have access to Oracle 9.2 on my laptop. Here's my test. I have ORCL and AUX1 databases, the latter created by RMAN DUPLICATE some time ago. I logon AUX1 as SYSTEM. Set SYSTEM password hash value to the same as in ORCL. Create link L to ORCL without password. Selecting from a table in ORCL @L (i.e. select * from [EMAIL PROTECTED]) throws ORA-1017 invalid username/password. Alternatively, I logon as SYS and create a procedure owned by SYSTEM, with one line execute imediate('select count(*) from [EMAIL PROTECTED]'). When I execute system.this procedure as SYS, I get ORA-1005 null password given. (I could use DBMS_SYS_SQL but using the execute immediate trick obviates the need to remember the syntax in that undocumented package). If I use connect to current_user to create the link, I always get ORA-28030 Server encountered problems accessing LDAP directory service. Could you try on your databases and show how you do it? As I said, this may be a security problem. I'm just too ignorant of it and can't reproduce it for now. Yong Huang Norris, Gregory T [ITS] wrote: There's no reason I can see that he couldn't create the dblink first, and then reset the password using the encrypted value. Alternately, the dblink could be created using the DBMS_SYS_SQL package... no knowledge of the current password required. create database link foo connect to current_user using 'bar'; __ Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing. http://photos.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Yong Huang INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still 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: STATSPACK interpretation
What is taking place inside GENERATE_PRODUCT_KEYS() ? Could be dynamic SQL of the worst kind in there. That is, not using bind variables. A 10046 trace level 4 or 12 will show you what is going on there. Jared On Mon, 2003-12-22 at 12:39, Thomas Jeff wrote: We recently experienced a crash on our prod datewarehouse running 9.2.0.2 on AIX 4.3.3.The cause of the crash was 4031 errors generated by background processes (Oracle support has confirmed there is a bug involved), however, since that crash occurred, a certain nightly batch job has slowed to a crawl. Trying to recreate what has happened, I came across this in the STATSPACK report. The interval for this report is 30 minutes. Is it telling me that I have 746 versions of this call eating up 400+ mb at the time of the snapshot? Why would that be? The procedure in question uses bind variables. SQL ordered by Sharable Memory for DB: DSSP Instance: DSSP Snaps: 3309 -3310 - End Sharable Memory Threshold: 1048576 Sharable Mem (b) Executions % Total Hash Value --- 483,580,268 57 411.8539672786 Module: [EMAIL PROTECTED] (TNS V1-V3) BEGINGENERATE_PRODUCT_KEYS (:1,:2,:3,:4) ; END; - SQL ordered by Version Count for DB: DSSP Instance: DSSP Snaps: 3309 -3310 - End Version Count Threshold:20 Version Count Executions Hash Value 746 57539672786 Module: [EMAIL PROTECTED] (TNS V1-V3) BEGINGENERATE_PRODUCT_KEYS (:1,:2,:3,:4) ; END; Jeffery D Thomas DBA Thomson Information Services Thomson, Inc. Email: [EMAIL PROTECTED] Indy DBA Master Documentation available at: http://gkmqp.tce.com/tis_dba -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still 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: RMAN - the time has come
I would say it is like chess. Learning how the pieces move is easy. Learning to put it altogether and use and manage it is not quite as simple. There are a lot of nuances to RMAN, and I don't pretend to have a handle on it. Yet. Jared On Mon, 2003-12-22 at 08:04, Mercadante, Thomas F wrote: Raj, It really is a quick learn. The best way is to get some scripts (great examples in the rdbms/demo directory) and try them on a test database. Once you get something running, the rest is simple. If you need some samples, let me know. It's really basic stuff. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Monday, December 22, 2003 10:35 AM To: Multiple recipients of list ORACLE-L Okay, its time to bite the bullet ... time to learn RMAN. Outside of TFM (which I just started reading), are there any good books/articles on RMAN? I know there is one by RFreeman, and it is for 9i (Robert, will there be a 10g version?) Yeah, I am also google'ing, taeoma'ing and in general stfw'ing whenever time permits. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! ** This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. **4 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still 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: stress tests for a scale to 30,000 users
Since you are on 9i, have you considered monitoring the tables? ( alter/create table monitor ) This would reduce the need to collect statistics so often. Jared On Mon, 2003-12-22 at 12:19, [EMAIL PROTECTED] wrote: My estimate right now is about a 500GB instance(but could grow). There are several complexities. 1. high transaction system, but also will have alot of long running queries 2. We deliver data daily and rebuild large parts of the database nightly with loads. Im not certain I have the window to analyze every index or get histograms on all the tables. There are VERY large data loads and deliveries. Data has to be delivered by a certain time and we get data feeds from other groups. I cannot control when we recieve data to load. 3. We will not be actively managing the production server. Its going to be delivered as an off the shelf product. I do not know what statistics ill be allowed to have for security reasons(this is not govenment stuff so dont worry about what I say). Its up to the client. 4. We are using web server level connection pooling so tracing isnt very useful. Im essentially the lone performance guy on the team. Ive never done a scale up this large, or with this many complexities. We just managed to convince them to use bind variables... but they haven't been implemented yet. Im having trouble getting accurate test cases. This is what I am 'attempting' to do at first. Please let me know if my approach is accurate. 1. Find out which queries will be run the most. Are there things people will do in the mornings, but not in the afternoon(so far its 'dunno'). 2. Hopefully, I can get a hold of either the use cases or 'preferebly' test cases, so we can design our stress tests around actual user processes. All they are doing now is opening up 50+ users and running queries in loops. What other approach should I take to get started. Im rather troubled by this... -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still 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: Delete vs. truncate to free up spaces.
... and if your table is not partitioned, consider using 'CREATE TABLE AS' with WHERE clause that eliminates the rows you wish to delete, recreate indexes and constraints on the new table, drop the old table, rename the new to the old. Keep in mind that stored procedures and triggers that reference the table will need to be recompiled. Jared On Mon, 2003-12-22 at 14:44, Jacques Kilchoer wrote: This is one of the cases where a partitioned table can be of great use. What version of Oracle? Standard or Enterprise Edition? With a partitioned table you can say alter table ... drop partition ... ; to easily get rid of a large chunk of data and release the space. See Oracle9i Database Concepts Release 2 (9.2) Part Number A96524-01 Chapter 11 Partitioned Tables and Indexes http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96524/c12parti.htm#464767 or http://tinyurl.com/362ba -Original Message- Nguyen, David M I am using delete command to delete million records in several tables to free up space in tablespace. I understand delete command does not release unused spaces as truncate command but I could not use truncate to delete ALL records in table as I need to keep one month old of records in table. Please advise a better method I can use to free up spaces. Thanks, David -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still 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: Delete vs. truncate to free up spaces.
It's Oracle8i Enterprise Edition. -Original Message- Jacques Kilchoer Sent: Monday, December 22, 2003 4:44 PM To: Multiple recipients of list ORACLE-L This is one of the cases where a partitioned table can be of great use. What version of Oracle? Standard or Enterprise Edition? With a partitioned table you can say alter table ... drop partition ... ; to easily get rid of a large chunk of data and release the space. See Oracle9i Database Concepts Release 2 (9.2) Part Number A96524-01 Chapter 11 Partitioned Tables and Indexes http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96524/c12p arti.htm#464767 or http://tinyurl.com/362ba -Original Message- Nguyen, David M I am using delete command to delete million records in several tables to free up space in tablespace. I understand delete command does not release unused spaces as truncate command but I could not use truncate to delete ALL records in table as I need to keep one month old of records in table. Please advise a better method I can use to free up spaces. Thanks, David -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nguyen, David M 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: Delete vs. truncate to free up spaces.
And synonyms will have to be re-created. (drop and create). Grants will have to be given. Jared Still [EMAIL PROTECTED] wrote: ... and if your table is not partitioned, consider using'CREATE TABLE AS' with WHERE clause that eliminates the rows you wish to delete, recreate indexes and constraintson the new table, drop the old table, rename the new tothe old.Keep in mind that stored procedures and triggers that reference the table will need to be recompiled.JaredOn Mon, 2003-12-22 at 14:44, Jacques Kilchoer wrote: This is one of the cases where a partitioned table can be of great use. What version of Oracle? Standard or Enterprise Edition? With a partitioned table you can say alter table ... drop partition ... ; to easily get rid of a large chunk of data and release the space. See Oracle9i Database Concepts Release 2 (9.2) Part Number A96524-01 Chapter 11 Partitioned Tables and Indexes http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96524/c12parti.htm#464767 or http://tinyurl.com/362ba -Original Message- Nguyen, David M I am using delete command to delete million records in several tables to free up space in tablespace. I understand delete command does not release unused spaces as truncate command but I could not use truncate to delete ALL records in table as I need to keep one month old of records in table. Please advise a better method I can use to free up spaces. Thanks, David -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: Jared StillINET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California -- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing). Do you Yahoo!? Free Pop-Up Blocker - Get it now
RE: Delete vs. truncate to free up spaces.
Then you should have the partitioning option. Partition your table if you can. -Original Message- Nguyen, David M It's Oracle8i Enterprise Edition. -Original Message- Jacques Kilchoer This is one of the cases where a partitioned table can be of great use. What version of Oracle? Standard or Enterprise Edition? With a partitioned table you can say alter table ... drop partition ... ; to easily get rid of a large chunk of data and release the space. See Oracle9i Database Concepts Release 2 (9.2) Part Number A96524-01 Chapter 11 Partitioned Tables and Indexes http://download-west.oracle.com/docs/cd/B10501_01/server.920/a 96524/c12p arti.htm#464767 or http://tinyurl.com/362ba -Original Message- Nguyen, David M I am using delete command to delete million records in several tables to free up space in tablespace. I understand delete command does not release unused spaces as truncate command but I could not use truncate to delete ALL records in table as I need to keep one month old of records in table. Please advise a better method I can use to free up spaces. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer 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: Delete vs. truncate to free up spaces.
Because there is a lot that could be overlooked, I prefer to do it the other way around: create table tmp_tbl nologging as select * from big_table where (rows you want to keep); truncate table big_table; alter trigger trigger_name disable; (for each trigger on big_table) alter constraint constraint_name disable; (for each constraint) alter index index_name unusable; (for each index) alter table big_table nologging; insert /*+ APPEND */ select * from tmp_tbl; commit; alter table big_table logging; alter index index_name rebuild nologging; alter constraint constraint_name enable; (consider novalidate where appropriate) alter trigger trigger_name enable; @?/rdbmsa/admin/utlrp.sql That way, you're a lot less likely to overlook a grant or synonym. -Mark -Original Message- From: anu [mailto:[EMAIL PROTECTED] Sent: Mon 12/22/2003 6:59 PM To: Multiple recipients of list ORACLE-L Cc: Subject:RE: Delete vs. truncate to free up spaces. And synonyms will have to be re-created. (drop and create). Grants will have to be given. Jared Still [EMAIL PROTECTED] wrote: .. and if your table is not partitioned, consider using 'CREATE TABLE AS' with WHERE clause that eliminates the rows you wish to delete, recreate indexes and constraints on the new table, drop the old table, rename the new to the old. Keep in mind that stored procedures and triggers that reference the table will need to be recompiled. Jared On Mon, 2003-12-22 at 14:44, Jacques Kilchoer wrote: This is one of the cases where a partitioned table can be of great use. What version of Oracle? Standard or Enterprise Edition? With a partitioned table you can say alter table ... drop partition ... ; to easily get rid of a large chunk of data and release the space. See Oracle9i Database Concepts Release 2 (9.2) Part Number A96524-01 Chapter 11 Partitioned Tables and Indexes http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96524/c12parti.htm#464767 or http://tinyurl.com/362ba -Original Message- Nguyen, David M I am using delete command to delete million records in several tables to free up space in tablespace. I understand delete command does not release unused spaces as truncate command but I could not use truncate to delete ALL records in table as I need to keep one month old of records in table. Please advise a better method I can use to free up spaces. Thanks, David -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still 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!? Free Pop-Up Blocker - Get it now http://us.rd.yahoo.com/slv/mailtag/*http://companion.yahoo.com/ winmail.dat
Re: 10g new features question for beta testers
Imagine the banner text: Miracle A/S. The Legacy Support of Tomorrow. Filling the Gap (jeans) like nobody else. Thanks to Tim Gorman for inspiration. I don't recall the text completely anymore, but he used to have this one about Building tomorrow's legacy systems - one crisis at a time. Or something to that effect. Mogens Pete Sharman wrote: But I thought this was the perfect opportunity for Miracle to fill any perceived gap in support? :) Pete Controlling developers is like herding cats. Kevin Loney, Oracle DBA Handbook Oh no, it's not. It's much harder than that! Bruce Pihlamae, long-term Oracle DBA -Original Message- Mogens Nørgaard Sent: Sunday, December 21, 2003 10:14 PM To: Multiple recipients of list ORACLE-L It's the Best of Breed versus One Vendor debate, and there are pros and cons galore. The perfect scenario, of course, is when they combine, so one vendor delivers the best of everything. That's what we have with Microsoft, isn't it? ;-) : Office stuff, OS, Database, ERP, CRM, video player, what have you... Then on the Support side of things, it's indeed good to be able to call One Vendor Only... if that vendor is good at Support. If he isn't, you might be better off if you have more than one option for calling. Mogens Pete Sharman wrote: Just a couple of comments on this which hopefully won't go down the Marketing track too far. :) 1. I'm pretty sure Steve Adams agrees with you, since he co-presented on ASM at OracleWorld in San Fran. Not sure if he monitors this group actively or not, but I believe the presentation he did is loaded with all the other OracleWorld 2003 presentations so you can see what he said. 2. One point which makes a lot of sense to me, and it happens in a variety of places in 10g such as ASM and the RAC clusterware. If you have one vendor to raise an issue with (not that you'd need to do that with Oracle of course!), it's a lot easier to get an answer without the finger pointing that can go on between vendors. Take the clusterware example - if you run into a problem running RAC on Sun with the Sun Cluster technology and Veritas owning the disk side, who you gonna call? GhostBusters, maybe! But if you're running RAC on Sun with Oracle's clusterware and ASM, it's a lot easier to determine who to call. Pete Controlling developers is like herding cats. Kevin Loney, Oracle DBA Handbook Oh no, it's not. It's much harder than that! Bruce Pihlamae, long-term Oracle DBA -Original Message- Connor McDonald Sent: Saturday, December 20, 2003 2:34 AM To: Multiple recipients of list ORACLE-L As with anything I suppose, if a single vendor can be in control of more of the stack between application and physical server structure then there is a greater opportunity for benefits. For example, ASM offers the ability to add disks to a stripe without needing to redistribute(reload) the entire stripeset. A (bug-free) ASM product looks very very impressive to me. Time will tell how close Oracle are to achieving it. hth connor --- [EMAIL PROTECTED] wrote: no ASMs are considerably different. Its supposed to manage everything. You dont give it a file, you give it entire disks and oracle does everything. Sets up files, manages, I/O, everything. you only look at the tablespace level. you dont even install any software on it. If your on SAN, you dont install SAN software on it. From: Goulet, Dick [EMAIL PROTECTED] Date: 2003/12/19 Fri AM 09:14:27 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: 10g new features question for beta testers That is not exactly a new feature. Oracle 9i has Oracle Managed Files where you give it a directory and then just build tablespaces. The database picks the filenames for you. Now mind you it does work, but I'll be damned if I use it in anything other than a development environment. For some reason Oracle has never gotten over that DUMB SAME (Stripe And Mirror Everything) idea. The concept is great in theory, but in practice it's absolutely abysmal at best. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Friday, December 19, 2003 8:24 AM To: Multiple recipients of list ORACLE-L I saw a presentation from Oracle on 10g new features last night in Reston,VA. I know atleast one other person from the list was there. Since Oracle is releasing details and its going to be released(in theory) in the next 2 weeks, I was wondering if you guys could talk about it. 1. does ASMs work as well as Oracle claims? I always wonder about first generation features... takes most software vendors a couple of generations to get it right(takes any project Im on just as long). This is a radical departure. for those of you who dont know. Oracle claims that they will manage your disks for you. All you do is
TNSPING VS. REGULAR PING..! WHY SUCH A DIFFERENCE
I have recently noticed in this one situation that there is a great difference between a tnsping vs a regular ping to the same server. for example this tnsping took about 270 ms which is strange and its consistent Used TNSNAMES adapter to resolve the aliasAttempting to contact (DESCRIPTION = (ADDRESS_LIST = (load_balance = on) (failover = on) (ADDRESS = (PROTOCOL = TCP)(HOST= myhost1.com)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = myhost2.com)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = xyzdb) (FAILOVER_MODE = (TYPE= SELECT) (METHOD = BASIC) (RETRIES = 20) (DELAY = 15OK (270 msec) and a ping to the same host Ping statistics for x.x.x.x: Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),Approximate round trip times in milli-seconds: Minimum = 61ms, Maximum = 70ms, Average = 67ms Why could there be such a difference? Do you Yahoo!? Yahoo! Photos - Get your photo on the big screen in Times Square
Re: Using dbms_stats.auto_sample_size in dbms_stats.
There's some notes on my site (www.oracledba.co.uk) about how it goes about its work. Basically, it will do a slightly more work than you might think - throw a 10046 trace on it and you can see how it does its job. Cheers Connor --- Arnon, Yuval [EMAIL PROTECTED] wrote: To all 9i DBA's. I am trying to find out how efficient (or not ) is the option of running dbms_stats with dbms_stats.auto_sample_size. Reading metalink I see a lot of issues with the time it takes to run, and also that sample_size column is always equal to num_rows. Would like to hear from anyone who is using this in a Production environment, tia Yuval. This transmission may contain information that is privileged, confidential and exempt from disclosure under applicable law. If you, [EMAIL PROTECTED], are not the intended recipient, you are hereby notified that any disclosure, copying, distribution, or use of the information contained herein (including any reliance thereon) is STRICTLY PROHIBITED. If you received this transmission in error, please immediately contact the sender and destroy the material in its entirety, whether in electronic or hard copy format. = Connor McDonald web: http://www.oracledba.co.uk web: http://www.oaktable.net email: [EMAIL PROTECTED] GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day Yahoo! Messenger - Communicate instantly...Ping your friends today! Download Messenger Now http://uk.messenger.yahoo.com/download/index.html -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?iso-8859-1?q?Connor=20McDonald?= 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).
sql_trace when using middle tier connection pooling
Our middle tier opens several dedicated connections. SQL statements are issued through these dedicated connections. If Im running a stress test and I want to trace... what is the best method to use? Just use a logon trigger and trace every session? then dig through it to look at the top sql(which would be indicated by statspack). any other suggestions would be helpful. I really have no control over the middle tier or front end.
Re: TNSPING VS. REGULAR PING..! WHY SUCH A DIFFERENCE
Murali Vallath [EMAIL PROTECTED] wrote: I have recently noticed in this one situation that there is a great difference between a tnsping vs a regular ping to the same server. for example this tnsping took about 270 ms which is strange and its consistent Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADD RESS_LIST = (load_balance = on) (failover = on) (ADDRESS = (PROTOCOL = TCP)(HOST = myhost1.com)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = myhost2.com)( PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = xyzdb) (FAILOVER_MODE = (TYPE = SELECT) (METHOD = BASIC) (RETRIES = 20) (DELAY = 15 OK (270 msec) and a ping to the same host Ping statistics for x.x.x.x: Packets: Sent = 4, Received = 4, Lost = 0 (0% loss), Approximate round trip times in milli-seconds: Minimum = 61ms, Maximum = 70ms, Average = 67ms Why could there be such a difference? === more layers in the OSI stack, longer code path. try passing the argument (# of attempts) to tnsping to see if its consistently high, e.g.: D:\Oracle\Ora92\bintnsping mydb 5 TNS Ping Utility for 32-bit Windows: Version 9.2.0.4.0 - Production on 22-DEC-20 03 23:03:38 Copyright (c) 1997 Oracle Corporation. All rights reserved. Used parameter files: D:\Oracle\Ora92\network\admin\sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = badmofo)(PORT = 1551))) (CONNECT_DATA = (SERVICE_NAME = mydb.mydomain.com))) OK (110 msec) OK (30 msec) OK (10 msec) OK (20 msec) OK (10 msec) Pd __ Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing. http://photos.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Paul Drake 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: Risk of knowing password hash value (Was: OEM permissions)
Jared, I see you log out and log back in as SYSTEM to DB2. But how do you know the password for SYSTEM to log back in with after you change it? What if you don't log out? When I tried that (i.e. not logging out), I got ORA-1017. Yong Huang --- Jared Still [EMAIL PROTECTED] wrote: Environment: DB1: RH 8.0 with Oracle EE 9.2.0.4 DB2: Win2k SP3 with Oracle EE 9.2.0.1 SYSTEM user on each database initially have different passwords. It goes something like this: DB1: select password from dba_users where username = 'SYSTEM'; Let's say the result is 'AC424SDK4398' DB2: Logon to DB2 as SYSTEM. alter user SYSTEM identified by values 'AC424SDK4398'; create database link systemlink using 'DB1'; Logout, and log back on to DB2 as SYSTEM. select count(*) from [EMAIL PROTECTED]; Works for me in this environment. DB2 is compromised. HTH Jared On Mon, 2003-12-22 at 08:29, Yong Huang wrote: Hi, Gregory, I only have access to Oracle 9.2 on my laptop. Here's my test. I have ORCL and AUX1 databases, the latter created by RMAN DUPLICATE some time ago. I logon AUX1 as SYSTEM. Set SYSTEM password hash value to the same as in ORCL. Create link L to ORCL without password. Selecting from a table in ORCL @L (i.e. select * from [EMAIL PROTECTED]) throws ORA-1017 invalid username/password. Alternatively, I logon as SYS and create a procedure owned by SYSTEM, with one line execute imediate('select count(*) from [EMAIL PROTECTED]'). When I execute system.this procedure as SYS, I get ORA-1005 null password given. (I could use DBMS_SYS_SQL but using the execute immediate trick obviates the need to remember the syntax in that undocumented package). If I use connect to current_user to create the link, I always get ORA-28030 Server encountered problems accessing LDAP directory service. Could you try on your databases and show how you do it? As I said, this may be a security problem. I'm just too ignorant of it and can't reproduce it for now. Yong Huang Norris, Gregory T [ITS] wrote: There's no reason I can see that he couldn't create the dblink first, and then reset the password using the encrypted value. Alternately, the dblink could be created using the DBMS_SYS_SQL package... no knowledge of the current password required. create database link foo connect to current_user using 'bar'; __ Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing. http://photos.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Yong Huang 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: Delete vs. truncate to free up spaces.
That works also. I guess it really depends on which one is faster, and that is dependent on what % of the data you are trying to remove, and how big the whole thing is. In any case, writing a script to generate grants is just one more thing on the check list, not too hard to do. Jared On Mon, 2003-12-22 at 16:19, Bobak, Mark wrote: Because there is a lot that could be overlooked, I prefer to do it the other way around: create table tmp_tbl nologging as select * from big_table where (rows you want to keep); truncate table big_table; alter trigger trigger_name disable; (for each trigger on big_table) alter constraint constraint_name disable; (for each constraint) alter index index_name unusable; (for each index) alter table big_table nologging; insert /*+ APPEND */ select * from tmp_tbl; commit; alter table big_table logging; alter index index_name rebuild nologging; alter constraint constraint_name enable; (consider novalidate where appropriate) alter trigger trigger_name enable; @?/rdbmsa/admin/utlrp.sql That way, you're a lot less likely to overlook a grant or synonym. -Mark -Original Message- From: anu [mailto:[EMAIL PROTECTED] Sent: Mon 12/22/2003 6:59 PM To: Multiple recipients of list ORACLE-L Cc: Subject: RE: Delete vs. truncate to free up spaces. And synonyms will have to be re-created. (drop and create). Grants will have to be given. Jared Still [EMAIL PROTECTED] wrote: .. and if your table is not partitioned, consider using 'CREATE TABLE AS' with WHERE clause that eliminates the rows you wish to delete, recreate indexes and constraints on the new table, drop the old table, rename the new to the old. Keep in mind that stored procedures and triggers that reference the table will need to be recompiled. Jared On Mon, 2003-12-22 at 14:44, Jacques Kilchoer wrote: This is one of the cases where a partitioned table can be of great use. What version of Oracle? Standard or Enterprise Edition? With a partitioned table you can say alter table ... drop partition ... ; to easily get rid of a large chunk of data and release the space. See Oracle9i Database Concepts Release 2 (9.2) Part Number A96524-01 Chapter 11 Partitioned Tables and Indexes http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96524/c12parti.htm#464767 or http://tinyurl.com/362ba -Original Message- Nguyen, David M I am using delete command to delete million records in several tables to free up space in tablespace. I understand delete command does not release unused spaces as truncate command but I could not use truncate to delete ALL records in table as I need to keep one month old of records in table. Please advise a better method I can use to free up spaces. Thanks, David -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still 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!? Free Pop-Up Blocker - Get it now http://us.rd.yahoo.com/slv/mailtag/*http://companion.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still 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: TNSPING VS. REGULAR PING..! WHY SUCH A DIFFERENCE
Paul mentioned a few reasons for this. Another is that a ping does not get past the NIC. The ping is answered by software running on the card. You may have noticed at times that a ping is not a reliable method for determining if a server is still functioning. The OS can crash, but the NIC still responds to a ping. Tnsping on the other hand must get a response from Oracle Net service or daemon running on the server, a much longer path as Paul pointed out. HTH Jared On Mon, 2003-12-22 at 17:29, Murali Vallath wrote: I have recently noticed in this one situation that there is a great difference between a tnsping vs a regular ping to the same server. for example this tnsping took about 270 ms which is strange and its consistent Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADD RESS_LIST = (load_balance = on) (failover = on) (ADDRESS = (PROTOCOL = TCP)(HOST = myhost1.com)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = myhost2.com)( PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = xyzdb) (FAILOVER_MODE = (TYPE = SELECT) (METHOD = BASIC) (RETRIES = 20) (DELAY = 15 OK (270 msec) and a ping to the same host Ping statistics for x.x.x.x: Packets: Sent = 4, Received = 4, Lost = 0 (0% loss), Approximate round trip times in milli-seconds: Minimum = 61ms, Maximum = 70ms, Average = 67ms Why could there be such a difference? - Do you Yahoo!? Yahoo! Photos - Get your photo on the big screen in Times Square -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still 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: Risk of knowing password hash value (Was: OEM permissions)
Hi, Okay. I'm almost a believer of this as a problem. How about 9.2.0.4 on RH9.3. 1) What does anyone/everyone get for my this query (my results shown): connect system/[EMAIL PROTECTED]; alter user scott identified by tiger; -- select password from dba_users where username = 'SCOTT'; PASSWORD F894844C34402B67 2) If you all get the same, then I'm concerned. Regards, Mike Thomas --- Yong Huang [EMAIL PROTECTED] wrote: Jared, I see you log out and log back in as SYSTEM to DB2. But how do you know the password for SYSTEM to log back in with after you change it? What if you don't log out? When I tried that (i.e. not logging out), I got ORA-1017. Yong Huang --- Jared Still [EMAIL PROTECTED] wrote: Environment: DB1: RH 8.0 with Oracle EE 9.2.0.4 DB2: Win2k SP3 with Oracle EE 9.2.0.1 SYSTEM user on each database initially have different passwords. __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Michael Thomas 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: Risk of knowing password hash value (Was: OEM permissions)
It doesn't matter which account I logged into DB2 with, as long as that account has privileges to read DBA_USERS. SYSTEM was used simply because it was the only account on the database that could be logged into remotely, so my test could be run without switching between machines. If I had granted SELECT_CATALOG_ROLE to scott, I could have logged in as SCOTT and done the same. Jared PS. Forgot this in private post to Yong: The password is cached, I assume in the PGA. This doesn't work without reconnecting. Logging out isn't strictly necessary, but the way my shell is setup, it takes quite a few less keystrokes to logout/logon than the type 'connect system/[EMAIL PROTECTED]'. On Mon, 2003-12-22 at 20:19, Yong Huang wrote: Jared, I see you log out and log back in as SYSTEM to DB2. But how do you know the password for SYSTEM to log back in with after you change it? What if you don't log out? When I tried that (i.e. not logging out), I got ORA-1017. Yong Huang --- Jared Still [EMAIL PROTECTED] wrote: Environment: DB1: RH 8.0 with Oracle EE 9.2.0.4 DB2: Win2k SP3 with Oracle EE 9.2.0.1 SYSTEM user on each database initially have different passwords. It goes something like this: DB1: select password from dba_users where username = 'SYSTEM'; Let's say the result is 'AC424SDK4398' DB2: Logon to DB2 as SYSTEM. alter user SYSTEM identified by values 'AC424SDK4398'; create database link systemlink using 'DB1'; Logout, and log back on to DB2 as SYSTEM. select count(*) from [EMAIL PROTECTED]; Works for me in this environment. DB2 is compromised. HTH Jared On Mon, 2003-12-22 at 08:29, Yong Huang wrote: Hi, Gregory, I only have access to Oracle 9.2 on my laptop. Here's my test. I have ORCL and AUX1 databases, the latter created by RMAN DUPLICATE some time ago. I logon AUX1 as SYSTEM. Set SYSTEM password hash value to the same as in ORCL. Create link L to ORCL without password. Selecting from a table in ORCL @L (i.e. select * from [EMAIL PROTECTED]) throws ORA-1017 invalid username/password. Alternatively, I logon as SYS and create a procedure owned by SYSTEM, with one line execute imediate('select count(*) from [EMAIL PROTECTED]'). When I execute system.this procedure as SYS, I get ORA-1005 null password given. (I could use DBMS_SYS_SQL but using the execute immediate trick obviates the need to remember the syntax in that undocumented package). If I use connect to current_user to create the link, I always get ORA-28030 Server encountered problems accessing LDAP directory service. Could you try on your databases and show how you do it? As I said, this may be a security problem. I'm just too ignorant of it and can't reproduce it for now. Yong Huang Norris, Gregory T [ITS] wrote: There's no reason I can see that he couldn't create the dblink first, and then reset the password using the encrypted value. Alternately, the dblink could be created using the DBMS_SYS_SQL package... no knowledge of the current password required. create database link foo connect to current_user using 'bar'; __ Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing. http://photos.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Yong Huang INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still 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: 170systems
Hi Allan , I couldn't get any reply from you . Can you please mail me the docs. I am waiting for those docs of 170 Systems. Thanks in Advance . Regards Kirtish P Gaonkar -Original Message- Kirtish P Gaonkar Sent: Saturday, December 13, 2003 10:54 AM To: Multiple recipients of list ORACLE-L Hi Allan , Thanks for your quick reply. I tried mailing you but it rebounced saying unknow host . Please reply me at [EMAIL PROTECTED] . Also you can send me the docs on the same email id . Please zip and send so that none of the files get deleted because of firewall. Once again Thanks For the quick reply. Waiting for the docs. Thanks Regards Kirtish P Gaonkar -Original Message- Sent: Saturday, December 13, 2003 11:01 AM To: [EMAIL PROTECTED] Hi Allan , Thanks for your quick reply. Please do send the 170systems docs at this mail id . Also please zip and send the files so that it doesn't get deleted because of firewall. Once again thanks for your quick reply. Waiting for the docs.. Thanks Regards Kirtish P Gaonkar -Original Message- Nelson, Allan Sent: Friday, December 12, 2003 10:00 PM To: Multiple recipients of list ORACLE-L We are running 170 Systems. Unfortunately over dblinks to a database hosted by our parent corporation. It is abysmally slow. I'll try and find some docs for you. Please reply to me off list with an email address capable of accepting mulit-megabyte pdf's at [EMAIL PROTECTED] Allan -Original Message- Kirtish P Gaonkar Sent: Friday, December 12, 2003 1:29 AM To: Multiple recipients of list ORACLE-L hi , Is Any body using 170systems ? Can you please send me the documents or any info you have. Also we are planning to interface 170systems to Oracle Financials . If anybody has already done this please can you send me the docs. Waiting for replies.. Regards Kirtish P. Gaonkar -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtish P Gaonkar 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). __ This email is intended solely for the person or entity to which it is addressed and may contain confidential and/or privileged information. Copying, forwarding or distributing this message by persons or entities other than the addressee is prohibited. If you have received this email in error, please contact the sender immediately and delete the material from any computer. This email may have been monitored for policy compliance. [021216] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nelson, Allan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtish P Gaonkar INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtish P Gaonkar 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
Re: Upgrading to Oracle 9.2.0.4 - Any pitfalls? - Found word(s) to be
Thanks for the feedback from everyone. I'll probably upgrade to 9.2.0.4 unless there is a pressing reason to upgrade to 9.2.0.4.5 and when it becomes available. Apparently bug no 2921201 is showing as fixed in 9.2.0.5 in Metalink but Oracle support gave me a date of March at the earliest for its release. Richard Jones -Original Message- Tony Johnson Sent: Monday, December 22, 2003 7:29 PM To: Multiple recipients of list ORACLE-L pitfalls? - Found word(s) to be removed remove list error e-mail in the Text body. Last month Oracle Support said there would be a 92045 in Janauary 2004 . -Original Message- zhu chao Sent: Sunday, December 21, 2003 11:49 PM To: Multiple recipients of list ORACLE-L - Found word(s) to be removed remove list error e-mail in the Text body. At lease 9.2.0.5 will be released by oracle. I have seen some note talking about 9.2.0.5 patchset. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, December 22, 2003 12:49 PM I have said it on this list before, and I will say it again. With Oracle, quality ends with in 4. 7.3.4 8.1.7.4 9.2.0.4 Oracle v9.2.0.4 is fairly stable. I have had to apply only 1, one-off patch related to having 1000's of partitions. You may also want to add the following to your init.ora to prevent a few known bug's serial_reuse = disable event = 10235 trace name context forever, level 2 Jones, Richard O. To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] richard.jones.1@cc: aramco.com Subject: Upgrading to Oracle 9.2.0.4 - Any pitfalls? Sent by: [EMAIL PROTECTED] .com 12/21/2003 09:29 PM Please respond to ORACLE-L Hi, Our main production database was upgraded to Oracle 9.2.0.3 (64 bit) at the end of September 2003. Platform Solaris 64bit 5.8. Since then the database has hanged and had to be manually shutdown by killing processes and re-started: - (1) The first one involved the production of numerous: ORA-04031: unable to allocate 26168 bytes of shared memory (shared pool,unknown object,sga heap(1,0),session param values) , errors when users were logging and was linked by Oracle Support with bug number 2921201 (2) Secondly, the database raised an ORA-600 to the alert file: ORA-00600: internal error code, arguments: [510], [0x380068B30], [shared pool], [], [], [], [], [] , followed by numerous messages: PMON failed to acquire latch, see PMON dump ? (3) Thirdly, an: ORA-04031: unable to allocate 16384 bytes of shared memory (shared pool,unknown object,sga heap(1,0),trace buffer) was raised apparently caused by an Oracle background processes dieing unexpectedly. Should I upgrade to 9.2.0.4? None of the above problems seem to be fixed in 9.2.0.4!!! Our database is a hybrid between OLTP and Decision-Support with a relatively light load. Anyone out there with an unstable 9i database (we were more stable under 8.1.7)? Am I alone?? Many Thanks Richard Jones, DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: zhu chao INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tony Johnson INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web
Re: Risk of knowing password hash value (Was: OEM permissions)
On RH 8.0 Oracle 9.2.0.4: F894844C34402B67 It is required that a password for a particular users always hashes to the same value, regardless of platform or Oracle version. This has been true for as long as I have used oracle: since 7.0.13. If not, export/import would not be able to recreate users, and database links without a password would not work. Good reason to protect DBA_USERS, no? Jared On Mon, 2003-12-22 at 20:44, Michael Thomas wrote: Hi, Okay. I'm almost a believer of this as a problem. How about 9.2.0.4 on RH9.3. 1) What does anyone/everyone get for my this query (my results shown): connect system/[EMAIL PROTECTED]; alter user scott identified by tiger; -- select password from dba_users where username = 'SCOTT'; PASSWORD F894844C34402B67 2) If you all get the same, then I'm concerned. Regards, Mike Thomas --- Yong Huang [EMAIL PROTECTED] wrote: Jared, I see you log out and log back in as SYSTEM to DB2. But how do you know the password for SYSTEM to log back in with after you change it? What if you don't log out? When I tried that (i.e. not logging out), I got ORA-1017. Yong Huang --- Jared Still [EMAIL PROTECTED] wrote: Environment: DB1: RH 8.0 with Oracle EE 9.2.0.4 DB2: Win2k SP3 with Oracle EE 9.2.0.1 SYSTEM user on each database initially have different passwords. __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Michael Thomas INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still 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: Risk of knowing password hash value (Was: OEM permissions)
Yes, I misunderstood. Once I change the password, I can no longer connect to the account. My hasty little test was missing an important condition: I should have pretended I didn't know the password to the other database, which would prevent me from logging back on exploiting the db link. Wonder if there's a way around it though? I spent a few minutes looking for a way around that problem, and couldn't find one. Oracle may have covered the bases on this, they've had a few years to perfect it. Jared On Mon, 2003-12-22 at 21:19, Yong Huang wrote: Hey, you're working late! OK. I think you misunderstood. I know you take SYSTEM as an example user. Let's say it's SCOTT who has select_catalog_role. If you login to your own database as SCOTT and change his password hash value, you don't know the clear text password any more. How can you log out and log back in as SCOTT? That's why I ask if you can use the link without logging out after changing the password? Yong --- Jared Still [EMAIL PROTECTED] wrote: It doesn't matter which account I logged into DB2 with, as long as that account has privileges to read DBA_USERS. SYSTEM was used simply because it was the only account on the database that could be logged into remotely, so my test could be run without switching between machines. If I had granted SELECT_CATALOG_ROLE to scott, I could have logged in as SCOTT and done the same. Jared On Mon, 2003-12-22 at 20:19, Yong Huang wrote: Jared, I see you log out and log back in as SYSTEM to DB2. But how do you know the password for SYSTEM to log back in with after you change it? What if you don't log out? When I tried that (i.e. not logging out), I got ORA-1017. Yong Huang --- Jared Still [EMAIL PROTECTED] wrote: Environment: DB1: RH 8.0 with Oracle EE 9.2.0.4 DB2: Win2k SP3 with Oracle EE 9.2.0.1 SYSTEM user on each database initially have different passwords. It goes something like this: DB1: select password from dba_users where username = 'SYSTEM'; Let's say the result is 'AC424SDK4398' DB2: Logon to DB2 as SYSTEM. alter user SYSTEM identified by values 'AC424SDK4398'; create database link systemlink using 'DB1'; Logout, and log back on to DB2 as SYSTEM. select count(*) from [EMAIL PROTECTED]; Works for me in this environment. DB2 is compromised. HTH Jared On Mon, 2003-12-22 at 08:29, Yong Huang wrote: Hi, Gregory, I only have access to Oracle 9.2 on my laptop. Here's my test. I have ORCL and AUX1 databases, the latter created by RMAN DUPLICATE some time ago. I logon AUX1 as SYSTEM. Set SYSTEM password hash value to the same as in ORCL. Create link L to ORCL without password. Selecting from a table in ORCL @L (i.e. select * from [EMAIL PROTECTED]) throws ORA-1017 invalid username/password. Alternatively, I logon as SYS and create a procedure owned by SYSTEM, with one line execute imediate('select count(*) from [EMAIL PROTECTED]'). When I execute system.this procedure as SYS, I get ORA-1005 null password given. (I could use DBMS_SYS_SQL but using the execute immediate trick obviates the need to remember the syntax in that undocumented package). If I use connect to current_user to create the link, I always get ORA-28030 Server encountered problems accessing LDAP directory service. Could you try on your databases and show how you do it? As I said, this may be a security problem. I'm just too ignorant of it and can't reproduce it for now. Yong Huang Norris, Gregory T [ITS] wrote: There's no reason I can see that he couldn't create the dblink first, and then reset the password using the encrypted value. Alternately, the dblink could be created using the DBMS_SYS_SQL package... no knowledge of the current password required. create database link foo connect to current_user using 'bar'; __ Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing. http://photos.yahoo.com/ __ Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing. http://photos.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still 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
RE: Delete vs. truncate to free up spaces.
In such a case what will happen to the transactions that hit the table (since the triggers have been disabled)? Regards Naveen -Original Message- From: Bobak, Mark [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 23, 2003 5:49 AM To: Multiple recipients of list ORACLE-L Subject: RE: Delete vs. truncate to free up spaces. Because there is a lot that could be overlooked, I prefer to do it the other way around: create table tmp_tbl nologging as select * from big_table where (rows you want to keep); truncate table big_table; alter trigger trigger_name disable; (for each trigger on big_table) alter constraint constraint_name disable; (for each constraint) alter index index_name unusable; (for each index) alter table big_table nologging; insert /*+ APPEND */ select * from tmp_tbl; commit; alter table big_table logging; alter index index_name rebuild nologging; alter constraint constraint_name enable; (consider novalidate where appropriate) alter trigger trigger_name enable; @?/rdbmsa/admin/utlrp.sql That way, you're a lot less likely to overlook a grant or synonym. -Mark -Original Message- From: anu [mailto:[EMAIL PROTECTED] Sent: Mon 12/22/2003 6:59 PM To: Multiple recipients of list ORACLE-L Cc: Subject: RE: Delete vs. truncate to free up spaces. And synonyms will have to be re-created. (drop and create). Grants will have to be given. Jared Still [EMAIL PROTECTED] wrote: .. and if your table is not partitioned, consider using 'CREATE TABLE AS' with WHERE clause that eliminates the rows you wish to delete, recreate indexes and constraints on the new table, drop the old table, rename the new to the old. Keep in mind that stored procedures and triggers that reference the table will need to be recompiled. Jared On Mon, 2003-12-22 at 14:44, Jacques Kilchoer wrote: This is one of the cases where a partitioned table can be of great use. What version of Oracle? Standard or Enterprise Edition? With a partitioned table you can say alter table ... drop partition ... ; to easily get rid of a large chunk of data and release the space. See Oracle9i Database Concepts Release 2 (9.2) Part Number A96524-01 Chapter 11 Partitioned Tables and Indexes http://download-west.oracle.com/docs/cd/B10501_01/server.920/a 96524/c12parti.htm#464767 or http://tinyurl.com/362ba -Original Message- Nguyen, David M I am using delete command to delete million records in several tables to free up space in tablespace. I understand delete command does not release unused spaces as truncate command but I could not use truncate to delete ALL records in table as I need to keep one month old of records in table. Please advise a better method I can use to free up spaces. Thanks, David -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still 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!? Free Pop-Up Blocker - Get it now http://us.rd.yahoo.com/slv/mailtag/*http://companion.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Naveen, Nahata (IE10) 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: Risk of knowing password hash value (Was: OEM permissions)
8.1.7 on win2000 SQL select password 2 from dba_users 3 where username = 'SCOTT'; PASSWORD -- F894844C34402B67 - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, December 23, 2003 11:44 AM Hi, Okay. I'm almost a believer of this as a problem. How about 9.2.0.4 on RH9.3. 1) What does anyone/everyone get for my this query (my results shown): connect system/[EMAIL PROTECTED]; alter user scott identified by tiger; -- select password from dba_users where username = 'SCOTT'; PASSWORD F894844C34402B67 2) If you all get the same, then I'm concerned. Regards, Mike Thomas --- Yong Huang [EMAIL PROTECTED] wrote: Jared, I see you log out and log back in as SYSTEM to DB2. But how do you know the password for SYSTEM to log back in with after you change it? What if you don't log out? When I tried that (i.e. not logging out), I got ORA-1017. Yong Huang --- Jared Still [EMAIL PROTECTED] wrote: Environment: DB1: RH 8.0 with Oracle EE 9.2.0.4 DB2: Win2k SP3 with Oracle EE 9.2.0.1 SYSTEM user on each database initially have different passwords. __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Michael Thomas INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: rahul sharma 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).
Rollback segments size calculation
Hi all I am getting ORA-01555 in my database and the solution is to create huge rollback segments but my doubt is how to calculate the size of this huge rollback segments . Now I have 24 G of space in rollback segment tablespace .But still i am getting the error.The environment is datawarehouse and loading happens almost every time except at night . and while loading data loading updataion and deletion happens frequently .Please help me in calculating the size of huge rollback segment . Happy Christmas and New year to all Regards, Shibu DISCLAIMER: This message (including attachment if any) is confidential and may be privileged. Before opening attachments please check them for viruses and defects. MindTree Consulting Private Limited (MindTree) will not be responsible for any viruses or defects or any forwarded attachments emanating either from within MindTree or outside. If you have received this message by mistake please notify the sender by return e-mail and delete this message from your system. Any unauthorized use or dissemination of this message in whole or in part is strictly prohibited. Please note that e-mails are susceptible to change and MindTree shall not be liable for any improper, untimely or incomplete transmission. winmail.dat