RE: Calling report
Hi
syed...
srw.run_report ('d:\test.rdf destype=file
desname=try.out desformat=dflt batch=yes');
The
above will work fine. You should not mention 'report' keyword in the command
line. This is the
mistake in reports help.
Nirmal.
Prior
Deveoper, Current OCP DBA.
-Original Message-From: sultan
[mailto:[EMAIL PROTECTED]]Sent: Wednesday, September 04, 2002 6:03
PMTo: Multiple recipients of list ORACLE-LSubject:
Calling report
Hi
friends
I am using following command to call report
from report
srw.run_report ('report=d:\test.rdf destype=file desname=try.out
desformat=dflt batch=yes');
I have tested this using After Report /Before Report/Action
Trigger.
But this is not calling the
report.
Any solution will be appreciated.
Syed
Re: NLS_DATE_FORMAT
Eric Richmond, hi, try to set it in regedit, or click my computer-property-advanced-enviroment variable-add nls_date_format. Regards zhu chao Eachnet DBA 86-21-32174588-667 [EMAIL PROTECTED] www.happyit.net === 2002-09-09 11:18:00 ,you wrote£º=== On NT/2000, how can you set the NLS date format at the session level? Isn't there a file that you can set it in? What do you write in the file? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Eric Richmon INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = = = = = = = = = = = = = = = = = = = = -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: zhu chao INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Aout plan stabilty matching.
Chuan, If I remember right, you're supposed to turn the outlines on and then run your sikvel or procedure to catch the offensive statement(s). Regards, Ed Hi, All, From Oracle Doc: if the SQL text of the incoming statement exactly matches the SQL text in an outline in that category, then Oracle considers both texts identical, and Oracle uses the outline. Oracle considers any differences a mismatch. How could I ensure the incoming SQL text exactly match the SQL text in an outline? If I fish out an offensive SQL from library cache by some scripts in SQL*Plus, is this offensive SQL text identical to the incoming SQL text? Supposed this SQL text is extracted from stored procedure. Appreciated your experience. Chuan -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Edward Shevtsov INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
OEM: Is there a test to find broken dblinks?
Title: OEM: Is there a test to find broken dblinks? Hi All, I'm looking for an event test in OEM to find broken dblinks. Anyone know such? Thanks.
Tao of backup
For those who are not enlightened yet http://www.taobackup.com
RE: Is there a test to find broken dblinks?
Title: OEM: Is there a test to find broken dblinks? That would be useful, as import drops package bodies when there are invalid db links in packages. Short of testing them manually one by one, I don't know of one. Import must be doing it though, when it tries to compile packages. Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) Systems Admin Operations | Admin. et Exploit. des systèmes Technology Services | Services technologiques Informatics Branch | Direction de l'informatique Maritimes Region, DFO | Région des Maritimes, MPO E-Mail: [EMAIL PROTECTED] -Original Message-From: Sivan Rabinovitz [mailto:[EMAIL PROTECTED]]Sent: Tuesday, September 10, 2002 6:33 AMTo: Multiple recipients of list ORACLE-LSubject: OEM: Is there a test to find broken dblinks? Hi All, I'm looking for an event test in OEM to find broken dblinks. Anyone know such? Thanks.
RE: Here we go again!!
MS will make XML part of the OS... they are the first to bundle XML into an OS... They mention IIS Did anyone review the J2EE and .NET comparison paper on Oracle's site? Do Oracle's conclusions in that paper seem plausible? Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) Systems Admin Operations | Admin. et Exploit. des systèmes Technology Services| Services technologiques Informatics Branch | Direction de l'informatique Maritimes Region, DFO | Région des Maritimes, MPO E-Mail: [EMAIL PROTECTED] -Original Message- Sent: Monday, September 09, 2002 4:58 PM To: Multiple recipients of list ORACLE-L Subject:OT: Here we go again!! Will Bill Gates never learn! Can you say Anti-trust lawsuit #2? Dick Goulet -- MICROSOFT CLAIMS .NET REMOVES NEED FOR APPLICATION SERVER | cw360 Microsoft says. NET Server is all you need. Defying the industry trend for vendors to develop a separate application server for deploying Internet applications and Web services, the company says its Windows .NET Server 2003 will have all the functionality users need. For the full details, click: http://www.cw360.com/articlerd=i=ard=115584fv=1 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Boivin, Patrice J INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Here we go again!!
Can you post a link? I've just been reading an article in Oracle mag about Oracle implementing XML directly in to the database (XML DB), and another article talking of Oracle integrating more tightly with the .NET platform in 9iR2 (as well as support for VLM via Windows' AWE's).. Just wondering if adds any further to what I've just read.. (which doesn't have a great amount of technical content..) Mark -Original Message- Patrice J Sent: 10 September 2002 12:58 To: Multiple recipients of list ORACLE-L MS will make XML part of the OS... they are the first to bundle XML into an OS... They mention IIS Did anyone review the J2EE and .NET comparison paper on Oracle's site? Do Oracle's conclusions in that paper seem plausible? Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) Systems Admin Operations | Admin. et Exploit. des systèmes Technology Services| Services technologiques Informatics Branch | Direction de l'informatique Maritimes Region, DFO | Région des Maritimes, MPO E-Mail: [EMAIL PROTECTED] -Original Message- Sent: Monday, September 09, 2002 4:58 PM To: Multiple recipients of list ORACLE-L Subject:OT: Here we go again!! Will Bill Gates never learn! Can you say Anti-trust lawsuit #2? Dick Goulet -- MICROSOFT CLAIMS .NET REMOVES NEED FOR APPLICATION SERVER | cw360 Microsoft says. NET Server is all you need. Defying the industry trend for vendors to develop a separate application server for deploying Internet applications and Web services, the company says its Windows .NET Server 2003 will have all the functionality users need. For the full details, click: http://www.cw360.com/articlerd=i=ard=115584fv=1 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Boivin, Patrice J INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mark Leith INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Here we go again!!
On Tuesday 10 September 2002 13:58, you wrote: MS will make XML part of the OS... they are the first to bundle XML into an OS... They mention IIS Really??? What about Mac OS X? Did anyone review the J2EE and .NET comparison paper on Oracle's site? Do Oracle's conclusions in that paper seem plausible? Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) Systems Admin Operations | Admin. et Exploit. des systèmes Technology Services| Services technologiques Informatics Branch | Direction de l'informatique Maritimes Region, DFO | Région des Maritimes, MPO E-Mail: [EMAIL PROTECTED] -Original Message- Sent: Monday, September 09, 2002 4:58 PM To: Multiple recipients of list ORACLE-L Subject: OT: Here we go again!! Will Bill Gates never learn! Can you say Anti-trust lawsuit #2? Dick Goulet -- MICROSOFT CLAIMS .NET REMOVES NEED FOR APPLICATION SERVER | cw360 Microsoft says. NET Server is all you need. Defying the industry trend for vendors to develop a separate application server for deploying Internet applications and Web services, the company says its Windows .NET Server 2003 will have all the functionality users need. For the full details, click: http://www.cw360.com/articlerd=i=ard=115584fv=1 -- Pruner Jan [EMAIL PROTECTED] http://jan.pruner.cz/ - Only Robinson Crusoe had all his work done by Friday -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jan Pruner INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Oracle's Online DBA services for Oracle Apps 11i
This may be opening a can of worms, but has anyone on the list used Oracle Support's Online DBA for Applications or Patch Management services for Oracle Apps 11i? If so, what particular services did or are they performing for you? Were or are you satisfied with their work? Thanks, Bob Stauffer DE Communications Ephrata, PA USA [EMAIL PROTECTED] **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Robert Stauffer INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: using obfuscation
I do have an index on the unencrypted SENSITIVE_DATA. Does it look something like select * from original_table where decrypt(sensitive_data) = 'CLEAR TEXT' Yes but the call to decrypt(sensitive_data) is embedded in the view. An index on the function means the unencrypted sensitive data is still in the index. -Original Message- Sent: Monday, September 09, 2002 6:04 PM To: Multiple recipients of list ORACLE-L Subject:Re: using obfuscation Randy, Do you have an index on SENSITIVE_DATA? Also, you didn't include the long running query. Does it look something like select * from original_table where decrypt(sensitive_data) = 'CLEAR TEXT' ? That requires a full table scan, unless you build a functional index with the clear text of the sensitive_data. But if you do that, your data won't really be secure, as the clear text will now be in an index. Jared Steiner, Randy [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 09/09/2002 02:43 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:using obfuscation Hi all, I have downloaded the Metalink Notes on implementing dbms_obfuscation. I am using multiple front ends on the database, so the way I plan to implement the de-encryption is with a de-encrypt function in a view. Create View my_data AS Select de_encrypt(sensitive_data) AS sensitive_data ,other_data FROM original_table ; If I select from the view with a where clause on other_data, the response time is fine. If I select from the view with a where clause on sensitive_data, I do a full table scan and which takes about 15 minutes. The de-encrypt function is copied from a Metalink note, nothing fancy. Since I have various front ends, I can not de-encrypt the data in the front end. The only way I can think of is with the function in a view, but the response time is unacceptable. Does anyone have any thoughts on this? Thanks Randy -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steiner, Randy INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steiner, Randy INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Here we go again!!
OK... I did a search at www.oracle.com: Overall web page comparing the two: Oracle9i and J2EE vs. Microsoft .NET Comparing Oracle9i to Microsoft .NET can be difficult because Microsoft .NET is a combination of marketing hype, product branding and technology re-architecture. In contrast, Oracle9i is already proven to be the most scalable, reliable, secure and available software infrastructure in the industry. Need a summary of the the key differences between the Oracle9i J2EE infrastructure and Microsoft .NET? Start with Table 1 to learn how standards, productivity and openness are the foundation of Oracle9i. http://www.oracle.com/features/9i/index.html?0514_db_orclvsmsft.html Microsoft Presents: The Case of the Bogus Benchmarks Why Micosoft's .NET benchmark isn't an apples-to-apples comparison http://www.oracle.com/features/oow/index.html?oow01_msbench.html A general executive paper on web services, what they are: http://otn.oracle.com/products/ias/web_services/pdf/webservicesstrategy_twp. pdf Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Boivin, Patrice J INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Here we go again!!
I wasn't making a statement, just reporting what was in the article. : ) Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) -Original Message- Sent: Tuesday, September 10, 2002 10:03 AM To: Multiple recipients of list ORACLE-L Subject:Re: Here we go again!! On Tuesday 10 September 2002 13:58, you wrote: MS will make XML part of the OS... they are the first to bundle XML into an OS... They mention IIS Really??? What about Mac OS X? Did anyone review the J2EE and .NET comparison paper on Oracle's site? Do Oracle's conclusions in that paper seem plausible? Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) Systems Admin Operations | Admin. et Exploit. des systèmes Technology Services| Services technologiques Informatics Branch | Direction de l'informatique Maritimes Region, DFO | Région des Maritimes, MPO E-Mail: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Boivin, Patrice J INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re:RE: Here we go again!!
What I found interesting in MicroSoft's marketing hype was their inference that the OS would provide all of the functionality of an Application server. Now they just were in court for bundling the OS and browser. Are we in for a new round of court battles as they bundle the application server and OS? I'm sure BEA and IBM will not be happy. Dick Goulet Reply Separator Author: Boivin; Patrice J [EMAIL PROTECTED] Date: 9/10/2002 5:58 AM I wasn't making a statement, just reporting what was in the article. : ) Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) -Original Message- Sent: Tuesday, September 10, 2002 10:03 AM To: Multiple recipients of list ORACLE-L On Tuesday 10 September 2002 13:58, you wrote: MS will make XML part of the OS... they are the first to bundle XML into an OS... They mention IIS Really??? What about Mac OS X? Did anyone review the J2EE and .NET comparison paper on Oracle's site? Do Oracle's conclusions in that paper seem plausible? Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) Systems Admin Operations | Admin. et Exploit. des systèmes Technology Services| Services technologiques Informatics Branch | Direction de l'informatique Maritimes Region, DFO | Région des Maritimes, MPO E-Mail: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Boivin, Patrice J INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Privileges for other's table
zhu chao, Thanks for checking this out. I tried it again this morning and it worked like your example. At first I thought there might be a difference when creating a view on all rows (*) versus creating the view on a subset (x, y, z) but under both conditions a view was created successfully. Of course, attempting to grant select on the view failed with a ORA-1720. May be this is an hidden feature of 8.1.7.3 on Tru64. Unfortunately, I still have the screen dumps so I can't tell my boss that I'm going crazy and need a extended vacation ;) Mike -Original Message- Hand, Michael T, hi, what you tested is not the normal behavior of oracle, neither the document said like that nor in my database behavior like that.Maybe something is wrong with your database:) Look: SQL conn internal Connected. SQL create user t identified by t; User created. SQL grant connect to t; Grant succeeded. SQL conn t/t Connected. SQL select count(*) from sys.obj$; select count(*) from sys.obj$ * ERROR at line 1: ORA-00942: table or view does not exist SQL conn internal Connected. SQL grant select on obj$ to t; Grant succeeded. SQL conn t/t Connected. SQL select count(*) from sys.obj$; COUNT(*) -- 26011 SQL create or replace view sysobj as select * from sys.obj$; View created. SQL select * from v$version; BANNER Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production PL/SQL Release 8.1.7.0.0 - Production CORE8.1.7.0.0 Production TNS for Linux: Version 8.1.7.0.0 - Development NLSRTL Version 3.4.1.0.0 - Production Regards zhu chao Eachnet DBA 86-21-32174588-667 [EMAIL PROTECTED] www.happyit.net === 2002-09-09 16:23:00 ,you wrote£º=== Ok, I haven't had to deal with privileges much lately but this one had be stumped for a while, V8.1.7 Tru64. This is from an account with minimal privileges: SQL select count(*) from sys.obj$; select count(*) from sys.obj$ * ERROR at line 1: ORA-00942: table or view does not exist SQL connect sys Enter password: Connected. SQL grant select on obj$ to utility; Grant succeeded. SQL connect utility/ Connected. SQL select count(*) from sys.obj$; COUNT(*) -- 37742 SQL create view o_by_vlo as select name, obj# from sys.obj$; create view o_by_vlo as select name, obj# from sys.obj$ * ERROR at line 1: ORA-00942: table or view does not exist SQL connect sys Enter password: Connected. SQL grant select on obj$ to utility with grant option; Grant succeeded. SQL connect utility/ Connected. SQL create view o_by_vlo as select name, obj# from sys.obj$; View created. . . . So it would seem that with grant option is needed to create a view on another schema's tables. It took a little thinking on my part to realize that this makes sense as the view owner would be able to grant privileges on the underlying tables. Incidentally, I also found out that you can't use the grant option within roles. I wonder why? Michael Hand Polaroid Corp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hand, Michael T INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = = = = = = = = = = = = = = = = = = = = -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: zhu chao INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hand, Michael T INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the
RE: Disabling local partitioned indexes for load
Beth, This seems to be it. If I disable to PK, the ignore for everything else is happy...so, the question becomes... I have a table that represents quarterly data. The data in it is basically the same for every quarter...adding the period is what makes things unique. What is important from a processing standpoint is that no row in a single period be a duplicate, duplicates sans period are fine across the table. Due to indexing, loading each successive period is taking way too long, approx. 24 hours. Without indexes this is a matter of a couple hours tops. In this situation, would it make since to (yes this is heresy) NOT have a PK constraint, but have unique indexes on each partition. The UIs concatenated with the periods effectively ARE the PK, but because all the indexes are made local, I can now disable them on the new period during loads. Other than religious conviction (and for the record, I routinely tell developers to NEVER request a table without a PK), am I missing a good reason not to do this? TIA, John P Weatherman Database Administrator Replacements Ltd. -Original Message- Sent: Monday, September 09, 2002 5:25 PM To: Multiple recipients of list ORACLE-L Hi, I believe there is the caveat that it doesn't apply to pk indexes, because they are required to enforce the integrity constaint, although I can't find that in the documentation at the moment. Beth -Original Message- Sent: Monday, September 09, 2002 5:03 PM To: Multiple recipients of list ORACLE-L Sorry...sent with the wrong heading.. -Original Message- Sent: Monday, September 09, 2002 4:18 PM To: Multiple recipients of list ORACLE-L Hi all, I have a large data load to run into a partitioned table. With indexes the load takes over 24 hours, without on the order of 3 hours, obviously, I don't want to mess with the indexes until after the job completes. So I try to drop to local indexes and get ORA-14076. Fair enough. I go to metastink and see note 107976.1...exactly what I want. So I try to use it...and get ORA-01502. Has anybody done something like this and found there's an extra caveat from the note? Every other note oracle has seems to point back to the original one I am using. I'm on Solaris 8/Oracle 9.0.1.3. Any help would be appreciated. Here's the output I'm getting... SQL select index_name, status from dba_ind_partitions where partition_name = 'TYPE13'; INDEX_NAME STATUS -- I_BUY_PR_PCE_TYPE_HIST_3 USABLE I_BUY_PR_PCE_TYPE_HIST_2 USABLE I_BUY_PR_PCE_TYPE_HIST_1 USABLE PK_BUY_PRICE_PCE_TYPE_HISTORY USABLE SQL ALTER TABLE buy_price_piece_type_history MODIFY PARTITION type13 2 UNUSABLE LOCAL INDEXES; Table altered. SQL select index_name, status from dba_ind_partitions where partition_name = 'TYPE13'; INDEX_NAME STATUS -- I_BUY_PR_PCE_TYPE_HIST_3 UNUSABLE I_BUY_PR_PCE_TYPE_HIST_2 UNUSABLE I_BUY_PR_PCE_TYPE_HIST_1 UNUSABLE PK_BUY_PRICE_PCE_TYPE_HISTORY UNUSABLE SQL ALTER SESSION SET skip_unusable_indexes = true; Session altered. SQL insert into buy_price_piece_type_history partition (type13) select * from hold_type13; insert into buy_price_piece_type_history partition (type13) select * from hold_type13 * ERROR at line 1: ORA-01502: index 'RLADMIN.PK_BUY_PRICE_PCE_TYPE_HISTORY' or partition of such index is in unusable state SQL TIA, John P Weatherman Database Administrator Replacements Ltd. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Weatherman INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seefelt, Beth INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Weatherman INET: [EMAIL PROTECTED] Fat
Ioug meeting or HOTSOS Seminar
Hello List I got a message about Hotsos Tuning seminar in Dallas. Scheduled in February 2003. The lecturers list include among others: Tom, Anjo, Gaja. http://www.hotsos.com/events/symposium/ I also know that there is an IOUG Live meeting on May 2003, and many of you recommended going. http://bneo15.sba.com/ew/ioug/index3.cfm?clientsess_id=07332332nextpage=cal lpapersconference_id=71 MAYBE (not shouting but a big maybe) I will convince management to spring for one of the two. Which one you recommend? Yechiel Adar Mehish -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Yechiel Adar INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: using obfuscation
What about...
create view my_data as select de_encrypt(sensitive_data) as
clear_sensitive_data where
sensitive_data = encrypt('CLEAR TEXT') ?
This lets you create an index on the sensitive data without decrypting it, and
the function need only be called once on the clear text.
Caveat: no idea if this should work :)
Steiner, Randy wrote:
Hi all,
I have downloaded the Metalink Notes on implementing dbms_obfuscation. I am
using multiple front ends on the database, so the way I plan to implement
the de-encryption is with a de-encrypt function in a view.
Create View my_data
AS
Select de_encrypt(sensitive_data) AS sensitive_data
,other_data
FROM original_table
;
If I select from the view with a where clause on other_data, the response
time is fine. If I select from the view with a where clause on
sensitive_data, I do a full table scan and which takes about 15 minutes.
The de-encrypt function is copied from a Metalink note, nothing fancy.
Since I have various front ends, I can not de-encrypt the data in the front
end. The only way I can think of is with the function in a view, but the
response time is unacceptable. Does anyone have any thoughts on this?
Thanks
Randy
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Steiner, Randy
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
begin:vcard
n:Jerman;Don
tel;work:919.508.1886
x-mozilla-html:TRUE
org:Database Management Service,Information Technology
version:2.1
email;internet:[EMAIL PROTECTED]
title:Database Administrator
adr;quoted-printable:;;Database Management Service,Information Technology=0D=0A104 Fayetteville Street Mall;Raleigh;NC;27699-1521;USA
x-mozilla-cpt:;-9536
fn:Don Jerman
end:vcard
RE: Here we go again!!
MS will make XML part of the OS... But will it really be XML? They have a history of bending the standards to the point that anyone else's products break with M$. I'd wait to see how well they conform to buy-the-book XML before depending on it for anything. For a good example of this look at the HTML they generate today. -- Steven Lembark 2930 W. Palmer Workhorse Computing Chicago, IL 60647 +1 800 762 1582 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steven Lembark INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: NLS_DATE_FORMAT
To set it at the session level, You need, Alter session set nls_date_format='DD-MON- HH24:MI'; You can give any valid format string. If you want this to happen automatically in your sqlplus session you can add this to your glogin.sql script. Hope this helps. Viral. From: Farnsworth, Dave [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: NLS_DATE_FORMAT Date: Mon, 09 Sep 2002 11:48:24 -0800 Set it in the SIDinit.ora. Dave -Original Message- Sent: Monday, September 09, 2002 2:18 PM To: Multiple recipients of list ORACLE-L On NT/2000, how can you set the NLS date format at the session level? Isn't there a file that you can set it in? What do you write in the file? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Eric Richmon INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Farnsworth, Dave INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). _ Send and receive Hotmail on your mobile device: http://mobile.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Viral Desai INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
gathering history v$ to predict system change
hi, list friends: We know statspack can gather history data and let you compare statistics from different time snapshot.But it is something difficult, for you must make multiple report and open them one by one and compare data between different reports. Now i want to gather some information about the system ,put them in log file and draw pictures(using mrtg from log) about the statistics change,so that i can see which statistics is changing recent time and find problem before it became performance bottleneck or before it stuck the system. In fact, the most useful data i want to get is like the content in the statspack, but i want to get them into one file from different time snapshot.The data i want to gather is like: wait event change trend,instance load profile change trend, the most cost sql change.Is it possible to get these data directly from some join of v$views? Or i can gather such valuable information from some v$view? Please share your experience about performance monitoring and capacity planning:) Regards zhu chao Eachnet DBA 86-21-32174588-667 [EMAIL PROTECTED] www.happyit.net -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: zhu chao INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: using obfuscation
Don,
It seems like a real good idea, but what am I putting inside my call to the
encrypt function in my Create View statement?
Randy
-Original Message-
Sent: Tuesday, September 10, 2002 10:13 AM
To: Multiple recipients of list ORACLE-L
Subject:Re: using obfuscation
File: Card for Don Jerman What about...
create view my_data as select de_encrypt(sensitive_data) as
clear_sensitive_data where
sensitive_data = encrypt('CLEAR TEXT') ?
This lets you create an index on the sensitive data without decrypting it,
and
the function need only be called once on the clear text.
Caveat: no idea if this should work :)
Steiner, Randy wrote:
Hi all,
I have downloaded the Metalink Notes on implementing dbms_obfuscation. I
am
using multiple front ends on the database, so the way I plan to implement
the de-encryption is with a de-encrypt function in a view.
Create View my_data
AS
Select de_encrypt(sensitive_data) AS sensitive_data
,other_data
FROM original_table
;
If I select from the view with a where clause on other_data, the response
time is fine. If I select from the view with a where clause on
sensitive_data, I do a full table scan and which takes about 15 minutes.
The de-encrypt function is copied from a Metalink note, nothing fancy.
Since I have various front ends, I can not de-encrypt the data in the
front
end. The only way I can think of is with the function in a view, but the
response time is unacceptable. Does anyone have any thoughts on this?
Thanks
Randy
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Steiner, Randy
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Steiner, Randy
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
methodology to keep only certain programs to connect to
I've been tasked to ensure only certain app programs access the database. I'm thinking on-logon trigger, check the program field from v$session. unfortunately v$session is for all sessions, i can't seem to find the view that tells me only MY info during login. I only want the sid, serial#, username and program for my just now connection to the database. Does this exist or am I going about this the wrong way? We're thinking of checking those fields to make sure sql*plus, toad, etc can't connect as a particular user(even though the password is known out in the community). any ideas would be greatly appreciated. joe
Reorganizing a database
Hi, We are creating a 45GB database from a restore of a current system. My boss has requested that I reorganize the entire new database. I have my doubts that this will provide as much benefit as he seems to expect, but they are adamant that this is what they want. One of the desired effects is to resize all the datafiles to uniform sizes and distribute some I/O. I'm thinking of using a full export as the base of this activity, splitting the export. This will be done on an HP L class with 2 500MHz processors and 3 GB of memory. What is the best way of accomplishing this as quickly and painlessly as possible? I've looked through Metalink and various books without finding a good general procedure for changing the location and sizes of the datafiles during the import. About how long should this take (ballpark)? Thanks, Russ
RE: methodology to keep only certain programs to connect to
Joe, I use the following with decent success on a logon database trigger: -- Set a unique string for the session and update the session info. client_info_str := 'WTWLOGIN_' || LTRIM(dbms_random.value,'.'); DBMS_APPLICATION_INFO.SET_CLIENT_INFO(client_info_str); -- look into the v$session view for the session just connected. SELECT program, username, osuser, terminal, machine INTO loc_program, loc_username, loc_osuser,loc_terminal,loc_machine FROM V$SESSION WHERE client_info=client_info_str; From here, you can test the loc_program variable against the loc_username to see if the combination is correct. Stuff like: IF loc_username='TESTLOGIN'then RAISE kill_Login; END IF; EXCEPTION WHEN kill_Login THEN RAISE_APPLICATION_ERROR(-20999,'Login''s using this account and this tool are Invalid'); Hope this helps! Tom Mercadante Oracle Certified Professional -Original Message-From: JOE TESTA [mailto:[EMAIL PROTECTED]]Sent: Tuesday, September 10, 2002 11:58 AMTo: Multiple recipients of list ORACLE-LSubject: methodology to keep only certain programs to connect to I've been tasked to ensure only certain app programs access the database. I'm thinking on-logon trigger, check the program field from v$session. unfortunately v$session is for all sessions, i can't seem to find the view that tells me only MY info during login. I only want the sid, serial#, username and program for my just now connection to the database. Does this exist or am I going about this the wrong way? We're thinking of checking those fields to make sure sql*plus, toad, etc can't connect as a particular user(even though the password is known out in the community). any ideas would be greatly appreciated. joe
RE: gathering history v$ to predict system change
Zhu - You can use STATSPACK to gather the statistics into its tables and then query those tables directly. In the book ORACLE High-Performance Tuning with STATSPACK, Don Burleson describes how to do this in detail and provides many example queries. He advocates gathering STATSPACK statistics over a day or week, then reviewing them to identify the times your system is performing the most processing. Based on that, you can gather statistics for the peak times with shorter time intervals. Oracle 8i http://search.barnesandnoble.com/booksearch/isbnInquiry.asp?userid=0E2CIPD0W 9isbn=0072133783 Oracle9i http://search.barnesandnoble.com/booksearch/isbnInquiry.asp?userid=0E2CIPD0W 9isbn=007222360X If you need more explanation, reply back. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, September 10, 2002 10:44 AM To: Multiple recipients of list ORACLE-L hi, list friends: We know statspack can gather history data and let you compare statistics from different time snapshot.But it is something difficult, for you must make multiple report and open them one by one and compare data between different reports. Now i want to gather some information about the system ,put them in log file and draw pictures(using mrtg from log) about the statistics change,so that i can see which statistics is changing recent time and find problem before it became performance bottleneck or before it stuck the system. In fact, the most useful data i want to get is like the content in the statspack, but i want to get them into one file from different time snapshot.The data i want to gather is like: wait event change trend,instance load profile change trend, the most cost sql change.Is it possible to get these data directly from some join of v$views? Or i can gather such valuable information from some v$view? Please share your experience about performance monitoring and capacity planning:) Regards zhu chao Eachnet DBA 86-21-32174588-667 [EMAIL PROTECTED] www.happyit.net -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: zhu chao INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: using obfuscation
Title: RE: using obfuscation
Can you create a Function based index on that column? That could be of use ...
Raj
__
Rajendra Jamadagni MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.
QOTD: Any clod can have facts, but having an opinion is an art!
-Original Message-
From: Steiner, Randy [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, September 10, 2002 11:58 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: using obfuscation
Don,
It seems like a real good idea, but what am I putting inside my call to the
encrypt function in my Create View statement?
Randy
-Original Message-
Sent: Tuesday, September 10, 2002 10:13 AM
To: Multiple recipients of list ORACLE-L
Subject: Re: using obfuscation
File: Card for Don Jerman What about...
create view my_data as select de_encrypt(sensitive_data) as
clear_sensitive_data where
sensitive_data = encrypt('CLEAR TEXT') ?
This lets you create an index on the sensitive data without decrypting it,
and
the function need only be called once on the clear text.
Caveat: no idea if this should work :)
Steiner, Randy wrote:
Hi all,
I have downloaded the Metalink Notes on implementing dbms_obfuscation. I
am
using multiple front ends on the database, so the way I plan to implement
the de-encryption is with a de-encrypt function in a view.
Create View my_data
AS
Select de_encrypt(sensitive_data) AS sensitive_data
,other_data
FROM original_table
;
If I select from the view with a where clause on other_data, the response
time is fine. If I select from the view with a where clause on
sensitive_data, I do a full table scan and which takes about 15 minutes.
The de-encrypt function is copied from a Metalink note, nothing fancy.
Since I have various front ends, I can not de-encrypt the data in the
front
end. The only way I can think of is with the function in a view, but the
response time is unacceptable. Does anyone have any thoughts on this?
Thanks
Randy
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Steiner, Randy
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Steiner, Randy
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
*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.*1
Re: Ioug meeting or HOTSOS Seminar
Depends on what you are looking for. The Hotsos Seminar will be strictly tuning, will be very intense on that, will not have vendors The IOUG conference will have a vendor hall, will have a much wider variety of presentations on all topics It's sort of like deciding between a meal at a buffet, where you get only a taste of a lot of things, or having a regular dinner, where you get a larger portion of something you want. --- Yechiel Adar [EMAIL PROTECTED] wrote: Hello List I got a message about Hotsos Tuning seminar in Dallas. Scheduled in February 2003. The lecturers list include among others: Tom, Anjo, Gaja. http://www.hotsos.com/events/symposium/ I also know that there is an IOUG Live meeting on May 2003, and many of you recommended going. http://bneo15.sba.com/ew/ioug/index3.cfm?clientsess_id=07332332nextpage=cal lpapersconference_id=71 MAYBE (not shouting but a big maybe) I will convince management to spring for one of the two. Which one you recommend? Yechiel Adar Mehish -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Yechiel Adar INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Yahoo! - We Remember 9-11: A tribute to the more than 3,000 lives lost http://dir.remember.yahoo.com/tribute -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Ioug meeting or HOTSOS Seminar
Title: RE: Ioug meeting or HOTSOS Seminar Since performance tuning is just one aspect (although extremely important) of my DBA role I would choose IOUG thinking that I would get performance info. as well as other aspects of my role covered and could pick/choose. Although a conference with Cary Millsap would always be great - the variety would be nice too. -Original Message- From: Yechiel Adar [mailto:[EMAIL PROTECTED]] Sent: Tuesday, September 10, 2002 10:23 AM To: Multiple recipients of list ORACLE-L Subject: Ioug meeting or HOTSOS Seminar Hello List I got a message about Hotsos Tuning seminar in Dallas. Scheduled in February 2003. The lecturers list include among others: Tom, Anjo, Gaja. http://www.hotsos.com/events/symposium/ I also know that there is an IOUG Live meeting on May 2003, and many of you recommended going. http://bneo15.sba.com/ew/ioug/index3.cfm?clientsess_id=07332332=cal lpapersconference_id=71 MAYBE (not shouting but a big maybe) I will convince management to spring for one of the two. Which one you recommend? Yechiel Adar Mehish -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Yechiel Adar INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
9ir2 Install on Linux
I am attempting to install 9ir2 on a Red Hat 7.2 Linux box. Part way through the first attempt I decided to changed the Oracle home value. I stopped the install, deleted the previous Oracle home directory tree, built the new directory and restarted the install. Oracle has stored the value of the old Oracle home somewhere. The install is now crashing because the old Oracle home does not exist. Any idea where it is store and who owns the file? I have already deleted the install files in /tmp. The .bash_profile has the correct Oracle home. = Pete Barnett Lead Database Administrator The Regence Group [EMAIL PROTECTED] __ Yahoo! - We Remember 9-11: A tribute to the more than 3,000 lives lost http://dir.remember.yahoo.com/tribute -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Peter Barnett INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Table Locks
Title: RE: Table Locks Call me crazy if you wish. But I would take a process or system state dump and navigate the locking session's object hierarchy. Yes, I know, ugly as Sin and potentially life-shortening. HTH Tony Aponte -Original Message- From: Alan Davey [mailto:[EMAIL PROTECTED]] Sent: Thursday, August 29, 2002 4:44 PM To: Multiple recipients of list ORACLE-L Subject: Table Locks Hi All, I've noticed some locks on various tables and I'm trying to figure out which DML statements are causing the locks. In this example, the lock isn't being released because the developer forgot to include a commit/rollback. If I look at v$session which is causing the lock and query v$sqlarea with the values in sql_address and prev_sql_addr, I only see select statements that were issued after the DML (in this case a delete). I can query v$sqlarea with the locked table name and find the delete statement, but how do I link this back to the sid that issued it? Also, what if there had been multiple DML statements by this user, how would I know which was the first/last one executed? I'm RTFMing, but so far no luck. Any help would be greatly appreciated. Regards, -- Alan Davey [EMAIL PROTECTED] 212-604-0200 x106 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Alan Davey INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Ioug meeting or HOTSOS Seminar
Given that you will probably see Anjo, Cary, Tom and potentially Gaja, at the event, I would recommend IOUG Live! Reason 1: Significantly more presentations Reason 2: More opportunities to network with other people in your position Reason 3: Greater opportunity to share your stories Thank You Stephen P. Karniotis Product Architect Compuware Corporation Direct: (248) 865-4350 Mobile: (248) 408-2918 Email: [EMAIL PROTECTED] Web:www.compuware.com -Original Message- Sent: Tuesday, September 10, 2002 10:23 AM To: Multiple recipients of list ORACLE-L Hello List I got a message about Hotsos Tuning seminar in Dallas. Scheduled in February 2003. The lecturers list include among others: Tom, Anjo, Gaja. http://www.hotsos.com/events/symposium/ I also know that there is an IOUG Live meeting on May 2003, and many of you recommended going. http://bneo15.sba.com/ew/ioug/index3.cfm?clientsess_id=07332332nextpage=cal lpapersconference_id=71 MAYBE (not shouting but a big maybe) I will convince management to spring for one of the two. Which one you recommend? Yechiel Adar Mehish -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Yechiel Adar INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). The contents of this e-mail are intended for the named addressee only. It contains information that may be confidential. Unless you are the named addressee or an authorized designee, you may not copy or use it, or disclose it to anyone else. If you received it in error please notify us immediately and then destroy it. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Karniotis, Stephen INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: methodology to keep only certain programs to connect to
use v_$mystat - it has the sid - then do your join with v$session -Original Message-From: JOE TESTA [mailto:[EMAIL PROTECTED]]Sent: Tuesday, September 10, 2002 10:58 AMTo: Multiple recipients of list ORACLE-LSubject: methodology to keep only certain programs to connect to I've been tasked to ensure only certain app programs access the database. I'm thinking on-logon trigger, check the program field from v$session. unfortunately v$session is for all sessions, i can't seem to find the view that tells me only MY info during login. I only want the sid, serial#, username and program for my just now connection to the database. Does this exist or am I going about this the wrong way? We're thinking of checking those fields to make sure sql*plus, toad, etc can't connect as a particular user(even though the password is known out in the community). any ideas would be greatly appreciated. joe
RE: methodology to keep only certain programs to connect to
Yes. This works great. You posted your logon trigger before and I've used it with considerable success (and modification). We (will) use the logon trigger to ensure that a particular Oracle userid is logged on only from one machine (no sharing of userids). We also allow certain exemptions, either by userid or machine. I'll post our trigger but it's based on Mr. Mercandante's ideas. --create_LOGON_MULTIPLE_CHECK.sql CREATE OR REPLACE TRIGGER LOGON_MULTIPLE_CHECK AFTER logon ON DATABASE DECLARE client_info_str V$SESSION.CLIENT_INFO%TYPE; var_usernameV$SESSION.USERNAME%TYPE := null; kill_Login EXCEPTION; PRAGMA EXCEPTION_INIT( kill_Login, -20997 ); begin -- Set information string to uniquely identify this session client_info_str := 'Logon_Trigger_' || LTRIM(dbms_random.value,'.'); -- Push information string into v$session DBMS_APPLICATION_INFO.SET_CLIENT_INFO(client_info_str); -- query v$session and see if this user is logged on twice on machines that are not exempt begin SELECT unique(b.username) INTO var_username -- look for more than one logon from v$session a,v$session b where a.username=b.username -- is the user exempt? -- trim off the null character that occasionally gets added to the name AND rtrim(A.USERNAME,CHR(0)) NOT IN (SELECT LME_exemptee FROM LOGON_MULTIPLE_EXEMPTIONS WHERE LME_exemption_type = 'U') -- look for two different machines and a.machine != b.machine -- are any of the machines exempt? -- trim off the null character that occasionally gets added to the machine name AND rtrim(A.MACHINE,CHR(0)) NOT IN (SELECT LME_exemptee FROM LOGON_MULTIPLE_EXEMPTIONS WHERE LME_exemption_type = 'M') AND rtrim(B.MACHINE,CHR(0)) NOT IN (SELECT LME_exemptee FROM LOGON_MULTIPLE_EXEMPTIONS WHERE LME_exemption_type = 'M') -- make sure that we are looking at this logon session and a.client_info=client_info_str; EXCEPTION WHEN OTHERS THEN NULL; end; -- if the user has a logon from more than 1 non-exempt machine then kill this logon! IF var_username is not null THEN RAISE kill_Login; END IF; EXCEPTION WHEN kill_Login THEN RAISE_APPLICATION_ERROR(-20997,'This account is logged on via another machine!'); WHEN OTHERS THEN null; END; / Hope this helps and thanks Tom. Mercadante, Thomas FTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] NDATFM cc: @labor.state.Subject: RE: methodology to keep only certain programs to connect to ny.us Sent by: root 09/10/2002 12:23 PM Please respond to ORACLE-L Joe, I use the following with decent success on a logon database trigger: -- Set a unique string for the session and update the session info. client_info_str := 'WTWLOGIN_' || LTRIM(dbms_random.value,'.'); DBMS_APPLICATION_INFO.SET_CLIENT_INFO(client_info_str); -- look into the v$session view for the session just connected. SELECT program, username, osuser, terminal, machine INTO loc_program, loc_username, loc_osuser,loc_terminal,loc_machine FROM V$SESSION WHERE client_info=client_info_str; From here, you can test the loc_program variable against the loc_username to see if the combination is correct. Stuff like: IF
RE: Reorganizing a database
Russ, This is OT relative to your question slightly, but Be sure to take some good response time measurements (10046 level 8) before the rebuild so that you can make objective before/after comparisons. If youre right about your prediction that all this work wont provide much of a performance impact (and I suspect that you are exactly right), this is the only way youre going to be able to prove it. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic, Oct 13 San Francisco, Oct 1517 Dallas, Dec 911 Honolulu - 2003 Hotsos Symposium on Oracle System Performance, Feb 912 Dallas - Next event: Miracle Database Forum, Sep 2022 Middlefart Denmark -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of Brooks, Russ Sent: Tuesday, September 10, 2002 10:44 AM To: Multiple recipients of list ORACLE-L Subject: Reorganizing a database Hi, We are creating a 45GB database from a restore of a current system. My boss has requested that I reorganize the entire new database. I have my doubts that this will provide as much benefit as he seems to expect, but they are adamant that this is what they want. One of the desired effects is to resize all the datafiles to uniform sizes and distribute some I/O. I'm thinking of using a full export as the base of this activity, splitting the export. This will be done on an HP L class with 2 500MHz processors and 3 GB of memory. What is the best way of accomplishing this as quickly and painlessly as possible? I've looked through Metalink and various books without finding a good general procedure for changing the location and sizes of the datafiles during the import. About how long should this take (ballpark)? Thanks, Russ
RE: methodology to keep only certain programs to connect to
Joe
You can use the sys_context function to get
the auditing session id -
select * from v$session where audsid =
sys_context('USERENV','SESSIONID');
David Lord
-Original Message-From: JOE TESTA
[mailto:[EMAIL PROTECTED]]Sent: 10 September 2002
16:58To: Multiple recipients of list ORACLE-LSubject:
methodology to keep only certain programs to connect to
I've been tasked to ensure only certain app programs access the
database.
I'm thinking on-logon trigger, check the program field from
v$session. unfortunately v$session is for all sessions, i can't seem to
find the view that tells me only MY info during login. I only want the
sid, serial#, username and program for my just now connection to the
database.
Does this exist or am I going about this the wrong way?
We're thinking of checking those fields to make sure sql*plus, toad, etc
can't connect as a particular user(even though the password is known out in
the community).
any ideas would be greatly appreciated.
joe
**
This message (including any attachments) is confidential and may be
legally privileged. If you are not the intended recipient, you should
not disclose, copy or use any part of it - please delete all copies
immediately and notify the Hays Group Email Helpdesk at
[EMAIL PROTECTED]
Any information, statements or opinions contained in this message
(including any attachments) are given by the author. They are not
given on behalf of Hays unless subsequently confirmed by an individual
other than the author who is duly authorised to represent Hays.
A member of the Hays plc group of companies.
Hays plc is registered in England and Wales number 2150950.
Registered Office Hays House Millmead Guildford Surrey GU2 4HJ.
**
Idle Connections
Hi All, I am facing problems with a database with some very irresponsible users who just connect to the database from their applications and simply dont logout. Apart from the usual chidings I have been giving them, could any of you please tell me whether any parameter exists which can be put either in the sqlnet.ora file or database parameter file which will timeout and close the idle connections after a particular time interval ?? Thanks, Samir Samir Sarkar Oracle DBA SchlumbergerSema Email : [EMAIL PROTECTED] Phone : +44 (0) 115 - 957 6028 EPABX : +44 (0) 115 - 957 6418 Ext. 76028 Fax : +44 (0) 115 - 957 6018 _ This email is confidential and intended solely for the use of the individual to whom it is addressed. Any views or opinions presented are solely those of the author and do not necessarily represent those of SchlumbergerSema. If you are not the intended recipient, be advised that you have received this email in error and that any use, dissemination, forwarding, printing, or copying of this email is strictly prohibited. If you have received this email in error please notify the SchlumbergerSema Helpdesk by telephone on +44 (0) 121 627 5600. _ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: SARKAR, Samir INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: methodology to keep only certain programs to connect to
Can you use the USERENV or SYS_CONTEXT function? -Original Message-From: JOE TESTA [mailto:[EMAIL PROTECTED]]Sent: Tuesday, September 10, 2002 9:58 AMTo: Multiple recipients of list ORACLE-LSubject: methodology to keep only certain programs to connect to I've been tasked to ensure only certain app programs access the database. I'm thinking on-logon trigger, check the program field from v$session. unfortunately v$session is for all sessions, i can't seem to find the view that tells me only MY info during login. I only want the sid, serial#, username and program for my just now connection to the database. Does this exist or am I going about this the wrong way? We're thinking of checking those fields to make sure sql*plus, toad, etc can't connect as a particular user(even though the password is known out in the community). any ideas would be greatly appreciated. joe
Re: gathering history v$ to predict system change
zhu chao wrote: hi, list friends: We know statspack can gather history data and let you compare statistics from different time snapshot.But it is something difficult, for you must make multiple report and open them one by one and compare data between different reports. Now i want to gather some information about the system ,put them in log file and draw pictures(using mrtg from log) about the statistics change,so that i can see which statistics is changing recent time and find problem before it became performance bottleneck or before it stuck the system. In fact, the most useful data i want to get is like the content in the statspack, but i want to get them into one file from different time snapshot.The data i want to gather is like: wait event change trend,instance load profile change trend, the most cost sql change.Is it possible to get these data directly from some join of v$views? Or i can gather such valuable information from some v$view? Please share your experience about performance monitoring and capacity planning:) Regards zhu chao Eachnet DBA 86-21-32174588-667 [EMAIL PROTECTED] www.happyit.net V$ data is not, log history excepted, historical - just a snapshot. You just need the list of V$ views you want to keep track off, create your own set of tables with just an additional TIMESTAMP date column, and insert ... select sysdate, from V$... at regular intervals. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Ioug meeting or HOTSOS Seminar
Are we taking votes? From what Cary/Gaja/Anjo/Tom etc have contributed so far on list, I'd say Hotsos. Hey Cary, when are you planning to present in the Washington DC area? mkb --- [EMAIL PROTECTED] wrote: Since performance tuning is just one aspect (although extremely important) of my DBA role I would choose IOUG thinking that I would get performance info. as well as other aspects of my role covered and could pick/choose. Although a conference with Cary Millsap would always be great - the variety would be nice too. -Original Message- Sent: Tuesday, September 10, 2002 10:23 AM To: Multiple recipients of list ORACLE-L Hello List I got a message about Hotsos Tuning seminar in Dallas. Scheduled in February 2003. The lecturers list include among others: Tom, Anjo, Gaja. http://www.hotsos.com/events/symposium/ I also know that there is an IOUG Live meeting on May 2003, and many of you recommended going. http://bneo15.sba.com/ew/ioug/index3.cfm?clientsess_id=07332332nextpage=cal lpapersconference_id=71 MAYBE (not shouting but a big maybe) I will convince management to spring for one of the two. Which one you recommend? Yechiel Adar Mehish -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Yechiel Adar INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Yahoo! - We Remember 9-11: A tribute to the more than 3,000 lives lost http://dir.remember.yahoo.com/tribute -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: mkb INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: gathering history v$ to predict system change
Why not issue queries against the statspack tables to get your info? (if you are using statspack that is) -Joe --- zhu chao [EMAIL PROTECTED] wrote: hi, list friends: We know statspack can gather history data and let you compare statistics from different time snapshot.But it is something difficult, for you must make multiple report and open them one by one and compare data between different reports. Now i want to gather some information about the system ,put them in log file and draw pictures(using mrtg from log) about the statistics change,so that i can see which statistics is changing recent time and find problem before it became performance bottleneck or before it stuck the system. In fact, the most useful data i want to get is like the content in the statspack, but i want to get them into one file from different time snapshot.The data i want to gather is like: wait event change trend,instance load profile change trend, the most cost sql change.Is it possible to get these data directly from some join of v$views? Or i can gather such valuable information from some v$view? Please share your experience about performance monitoring and capacity planning:) Regards zhu chao Eachnet DBA 86-21-32174588-667 [EMAIL PROTECTED] www.happyit.net -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: zhu chao INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Yahoo! - We Remember 9-11: A tribute to the more than 3,000 lives lost http://dir.remember.yahoo.com/tribute -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Joe Raube INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Ioug meeting or HOTSOS Seminar
I don't think you can really compare them. I haven't been to a HOTSOS seminar, but it is in depth tuning of Oracle. IOUG is a smorgasboard of Oracle topics and an excellent networking opportunity. Jared Yechiel Adar [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 09/10/2002 07:23 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Ioug meeting or HOTSOS Seminar Hello List I got a message about Hotsos Tuning seminar in Dallas. Scheduled in February 2003. The lecturers list include among others: Tom, Anjo, Gaja. http://www.hotsos.com/events/symposium/ I also know that there is an IOUG Live meeting on May 2003, and many of you recommended going. http://bneo15.sba.com/ew/ioug/index3.cfm?clientsess_id=07332332nextpage=cal lpapersconference_id=71 MAYBE (not shouting but a big maybe) I will convince management to spring for one of the two. Which one you recommend? Yechiel Adar Mehish -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Yechiel Adar INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Ioug meeting or HOTSOS Seminar
Yechiel, Just to clarify... There are actually two different event types here that I think Jared might have blended together in his response. 1. The Hotsos Clinic is a 3-day course, taught by Jeff Holt and me, dedicated to the single purpose of teaching Oracle performance problem diagnosis and repair. This is the thing scheduled for SF and Dallas in October, and Honolulu in December. 2. The first annual Hotsos Symposium will be held Feb in Dallas. This event is a conference dedicated to Oracle performance, with several internationally acclaimed Oracle performance experts (Tom Kyte, Jonathan Lewis, Anjo, Gaja, James Morle, etc.) on the agenda. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic, Oct 1-3 San Francisco, Oct 15-17 Dallas, Dec 9-11 Honolulu - 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12 Dallas - Next event: Miracle Database Forum, Sep 20-22 Middelfart Denmark -Original Message- [EMAIL PROTECTED] Sent: Tuesday, September 10, 2002 11:24 AM To: Multiple recipients of list ORACLE-L I don't think you can really compare them. I haven't been to a HOTSOS seminar, but it is in depth tuning of Oracle. IOUG is a smorgasboard of Oracle topics and an excellent networking opportunity. Jared Yechiel Adar [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 09/10/2002 07:23 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Ioug meeting or HOTSOS Seminar Hello List I got a message about Hotsos Tuning seminar in Dallas. Scheduled in February 2003. The lecturers list include among others: Tom, Anjo, Gaja. http://www.hotsos.com/events/symposium/ I also know that there is an IOUG Live meeting on May 2003, and many of you recommended going. http://bneo15.sba.com/ew/ioug/index3.cfm?clientsess_id=07332332nextpage =cal lpapersconference_id=71 MAYBE (not shouting but a big maybe) I will convince management to spring for one of the two. Which one you recommend? Yechiel Adar Mehish -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Yechiel Adar INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cary Millsap INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Fatal Error: cannot map libwtc8.so
I'm trying to use SQLLDR Oracle 8.1.6 on a UNIX box but I'm getting: Fatal Error: cannot map libwtc8.so My environmental variables are: ORACLE_BASE=/m1/oradec1/app/oracle ORACLE_HOME=$ORACLE_BASE/product/8.1.6 LIBPATH=$ORACLE_HOME/lib CLASSPATH=$ORACLE_HOME/jlib NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P9 NLS_DATE_FORMAT=DD-MON-YY ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data ORA_NLS=$ORACLE_HOME/ocommon/nls/admin/data PATH=$ORACLE_HOME/bin:$PATH The ORACLE_SID and ORACLE_TERM are also set. Am I missing something? Yes, I know about ORAENV but I'm not the SA and they have it pointing to 7.3.4 (our current production environment). I'm trying to override those values in my script. Thanks in advance. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Thomas Day INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: methodology to keep only certain programs to connect to
Select sid from v$msystat where rownum = 1 Gary Kirsh Next Extent Consulting -Original Message-From: JOE TESTA [mailto:[EMAIL PROTECTED]]Sent: Tuesday, September 10, 2002 11:58 AMTo: Multiple recipients of list ORACLE-LSubject: methodology to keep only certain programs to connect to I've been tasked to ensure only certain app programs access the database. I'm thinking on-logon trigger, check the program field from v$session. unfortunately v$session is for all sessions, i can't seem to find the view that tells me only MY info during login. I only want the sid, serial#, username and program for my just now connection to the database. Does this exist or am I going about this the wrong way? We're thinking of checking those fields to make sure sql*plus, toad, etc can't connect as a particular user(even though the password is known out in the community). any ideas would be greatly appreciated. joe
RE: methodology to keep only certain programs to connect to
With a setup like this, how do you stop a user from simply renaming the program they are using to match what you expect to see and, therefore, getting past your security ?? -Original Message-From: Shaw John-P55297 [mailto:[EMAIL PROTECTED]]Sent: Tuesday, September 10, 2002 11:59 AMTo: Multiple recipients of list ORACLE-LSubject: RE: methodology to keep only certain programs to connect to use v_$mystat - it has the sid - then do your join with v$session -Original Message-From: JOE TESTA [mailto:[EMAIL PROTECTED]]Sent: Tuesday, September 10, 2002 10:58 AMTo: Multiple recipients of list ORACLE-LSubject: methodology to keep only certain programs to connect to I've been tasked to ensure only certain app programs access the database. I'm thinking on-logon trigger, check the program field from v$session. unfortunately v$session is for all sessions, i can't seem to find the view that tells me only MY info during login. I only want the sid, serial#, username and program for my just now connection to the database. Does this exist or am I going about this the wrong way? We're thinking of checking those fields to make sure sql*plus, toad, etc can't connect as a particular user(even though the password is known out in the community). any ideas would be greatly appreciated. joe
RE: Ioug meeting or HOTSOS Seminar
Goto Both -Original Message- Sent: Tuesday, September 10, 2002 7:23 AM To: Multiple recipients of list ORACLE-L Hello List I got a message about Hotsos Tuning seminar in Dallas. Scheduled in February 2003. The lecturers list include among others: Tom, Anjo, Gaja. http://www.hotsos.com/events/symposium/ I also know that there is an IOUG Live meeting on May 2003, and many of you recommended going. http://bneo15.sba.com/ew/ioug/index3.cfm?clientsess_id=07332332nextpage=cal lpapersconference_id=71 MAYBE (not shouting but a big maybe) I will convince management to spring for one of the two. Which one you recommend? Yechiel Adar Mehish -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Yechiel Adar INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Johnson, Michael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
LOBs Questions. Need help.
Hi All,
I want to load PDF files into the database and view the content of the lobs column. Below is what I did step by step. Anyone please point out what did I do wrong in my procedure. I am still unable to view the content of the Lobs column. I need you all for your help.
Please note that I already stored the PDF file (6117cdsapx.pdf) on my Oracle server site at /Disk05/test
CREATE TABLE MY_BOOK_TEXT (
FILE_DESC VARCHAR2 (200),
BOOK_FILE BFILE);
CREATE DIRECTORY TEST AS /Disk05/test;
DECLARE
pdf BFILE;
BEGIN
pdf := BFILENAME (TEST, 6117cdsapx.pdf);
END;
/
INSERT INTO MY_BOOK_TEXT (FILE_DESC, BOOK_FILE )
VALUES (IBM Microprocessor, BFILENAME(TEST, 6117cdsapx.pdf);
This procedure has been executed on the server site. When executing the procedure, it got a warning message: Procedure created with compilation errors.
CREATE OR REPLACE PROCEDURE displayLOB_proc IS Lob_locBLOB; BufferRAW(1024); AmountBINARY_INTEGER := 1024; Position INTEGER := 1; BEGIN /* Select the LOB: */ SELECT m.Map_obj.Drawing INTO Lob_loc FROM Multimedia_tab m WHERE m.Clip_ID = 1; /* Opening the LOB is optional: */ DBMS_LOB.OPEN (Lob_loc, DBMS_LOB.LOB_READONLY); LOOP DBMS_LOB.READ (Lob_loc, Amount, Position, Buffer); /* Display the buffer contents: */ DBMS_OUTPUT.PUT_LINE(utl_raw.cast_to_varchar2(Buffer)); Position := Position + Amount; END LO!
OP; /* Closing the LOB is mandatory if you have opened it: */ DBMS_LOB.CLOSE (Lob_loc); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('End of data'); End;/
Thank you all for your help in advance
LenkaYahoo! - We Remember
9-11: A tribute to the more than 3,000 lives lost
RE: methodology to keep only certain programs to connect to
Joe, you may try: select sid, serial#, username, program from v$session where audsid = userenv( 'sessionid') Regards, Catalin Nastase -Message d'origine-De: JOE TESTA [mailto:[EMAIL PROTECTED]]Date: mardi 10 septembre 2002 17:58À: Multiple recipients of list ORACLE-LObjet: methodology to keep only certain programs to connect to I've been tasked to ensure only certain app programs access the database. I'm thinking on-logon trigger, check the program field from v$session. unfortunately v$session is for all sessions, i can't seem to find the view that tells me only MY info during login. I only want the sid, serial#, username and program for my just now connection to the database. Does this exist or am I going about this the wrong way? We're thinking of checking those fields to make sure sql*plus, toad, etc can't connect as a particular user(even though the password is known out in the community). any ideas would be greatly appreciated. joe
9ir2 install on linux(removal)
try removing the /etc/oraInst.loc and the oraInventory file(the location of it is in the .loc file). Joe
RE: Fatal Error: cannot map libwtc8.so
Check your LD_LIBRARY_PATH setting. -Original Message- Sent: Tuesday, September 10, 2002 1:13 PM To: Multiple recipients of list ORACLE-L I'm trying to use SQLLDR Oracle 8.1.6 on a UNIX box but I'm getting: Fatal Error: cannot map libwtc8.so My environmental variables are: ORACLE_BASE=/m1/oradec1/app/oracle ORACLE_HOME=$ORACLE_BASE/product/8.1.6 LIBPATH=$ORACLE_HOME/lib CLASSPATH=$ORACLE_HOME/jlib NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P9 NLS_DATE_FORMAT=DD-MON-YY ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data ORA_NLS=$ORACLE_HOME/ocommon/nls/admin/data PATH=$ORACLE_HOME/bin:$PATH The ORACLE_SID and ORACLE_TERM are also set. Am I missing something? Yes, I know about ORAENV but I'm not the SA and they have it pointing to 7.3.4 (our current production environment). I'm trying to override those values in my script. Thanks in advance. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Thomas Day INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jerry Hess INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: 9ir2 Install on Linux
How about the oraInventory? -Original Message- Sent: Tuesday, September 10, 2002 12:18 PM To: Multiple recipients of list ORACLE-L I am attempting to install 9ir2 on a Red Hat 7.2 Linux box. Part way through the first attempt I decided to changed the Oracle home value. I stopped the install, deleted the previous Oracle home directory tree, built the new directory and restarted the install. Oracle has stored the value of the old Oracle home somewhere. The install is now crashing because the old Oracle home does not exist. Any idea where it is store and who owns the file? I have already deleted the install files in /tmp. The .bash_profile has the correct Oracle home. = Pete Barnett Lead Database Administrator The Regence Group [EMAIL PROTECTED] __ Yahoo! - We Remember 9-11: A tribute to the more than 3,000 lives lost http://dir.remember.yahoo.com/tribute -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Peter Barnett INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ji, Richard INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Reorganizing a database
Russ - Why don't you consider this: - Create a new tablespaces LMT and uniform sizes (please read the documents to understand this concept clearly). - Move tables into the new tablespaces using ALTER TABLE MOVE. This is MUCH faster than export/import. - If you have space issues, just create the tablespaces you have room for, and then drop old tablespaces as you empty them. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] Message- Sent: Tuesday, September 10, 2002 10:44 AM To: Multiple recipients of list ORACLE-L Hi, We are creating a 45GB database from a restore of a current system. My boss has requested that I reorganize the entire new database. I have my doubts that this will provide as much benefit as he seems to expect, but they are adamant that this is what they want. One of the desired effects is to resize all the datafiles to uniform sizes and distribute some I/O. I'm thinking of using a full export as the base of this activity, splitting the export. This will be done on an HP L class with 2 500MHz processors and 3 GB of memory. What is the best way of accomplishing this as quickly and painlessly as possible? I've looked through Metalink and various books without finding a good general procedure for changing the location and sizes of the datafiles during the import. About how long should this take (ballpark)? Thanks, Russ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
BMC Backtrack until_cancel
Hi all: I have been trying to use the until_cancel option in BMC Backtrack 3.3.00 to do until cancel recovery. But in the maual the description of this option is applies archive logs until there are no more. This is not exactly until cancel IMO. Is there any way to do a real until cancel via Backtrack? thank you Gene __ Yahoo! - We Remember 9-11: A tribute to the more than 3,000 lives lost http://dir.remember.yahoo.com/tribute -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: methodology to keep only certain programs to connect to
Kevin, That has been my point in the past. It is really not feasible to establish connection policy this way. For example: anybody can change the name of the sqlplus.exe executable on their desktop, run it, and connect to the database. v$session.program now reports the new executable name - not sqlplus. The same goes for any tool on the desktop - including odbc connections. Security policy has to start at the account/password level. Tom Mercadante Oracle Certified Professional -Original Message-From: Kevin Lange [mailto:[EMAIL PROTECTED]]Sent: Tuesday, September 10, 2002 1:54 PMTo: Multiple recipients of list ORACLE-LSubject: RE: methodology to keep only certain programs to connect to With a setup like this, how do you stop a user from simply renaming the program they are using to match what you expect to see and, therefore, getting past your security ?? -Original Message-From: Shaw John-P55297 [mailto:[EMAIL PROTECTED]]Sent: Tuesday, September 10, 2002 11:59 AMTo: Multiple recipients of list ORACLE-LSubject: RE: methodology to keep only certain programs to connect to use v_$mystat - it has the sid - then do your join with v$session -Original Message-From: JOE TESTA [mailto:[EMAIL PROTECTED]]Sent: Tuesday, September 10, 2002 10:58 AMTo: Multiple recipients of list ORACLE-LSubject: methodology to keep only certain programs to connect to I've been tasked to ensure only certain app programs access the database. I'm thinking on-logon trigger, check the program field from v$session. unfortunately v$session is for all sessions, i can't seem to find the view that tells me only MY info during login. I only want the sid, serial#, username and program for my just now connection to the database. Does this exist or am I going about this the wrong way? We're thinking of checking those fields to make sure sql*plus, toad, etc can't connect as a particular user(even though the password is known out in the community). any ideas would be greatly appreciated. joe
Re: methodology to keep only certain programs to connect to
Joe,
Try this:
select
s.username,
s.sid,
s.serial#
from v$session s
where userenv('SESSIONID') = s.audsid;
Jared
JOE TESTA [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
09/10/2002 08:58 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject:methodology to keep only certain programs to connect to
I've been tasked to ensure only certain app programs access the database.
I'm thinking on-logon trigger, check the program field from v$session.
unfortunately v$session is for all sessions, i can't seem to find the view
that tells me only MY info during login. I only want the sid, serial#,
username and program for my just now connection to the database.
Does this exist or am I going about this the wrong way?
We're thinking of checking those fields to make sure sql*plus, toad, etc
can't connect as a particular user(even though the password is known out
in the community).
any ideas would be greatly appreciated.
joe
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Cursor : please help
Hi All, I ve a JSP application. Each time I query database ( oracle 9i) from the page, I see that the no.of cursors increments by one. I m accessing database using JDBC, I close resultset and statement objects after query. But I m not closing Connection object since I use a Connection Pool. Can anyone tell why the no,of cursors increasing each time ? Thanks in advance Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: JOSHY MON M C INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: using obfuscation
I've seen this type of thing done using domain indexes, i.e. defining your
own indextypes. Looked like a bit of work, though.
HTH,
Gary
Gary Kirsh
Next Extent Consulting
-Original Message-
Sent: Tuesday, September 10, 2002 11:58 AM
To: Multiple recipients of list ORACLE-L
Don,
It seems like a real good idea, but what am I putting inside my call to the
encrypt function in my Create View statement?
Randy
-Original Message-
Sent: Tuesday, September 10, 2002 10:13 AM
To: Multiple recipients of list ORACLE-L
Subject:Re: using obfuscation
File: Card for Don Jerman What about...
create view my_data as select de_encrypt(sensitive_data) as
clear_sensitive_data where
sensitive_data = encrypt('CLEAR TEXT') ?
This lets you create an index on the sensitive data without decrypting it,
and
the function need only be called once on the clear text.
Caveat: no idea if this should work :)
Steiner, Randy wrote:
Hi all,
I have downloaded the Metalink Notes on implementing dbms_obfuscation. I
am
using multiple front ends on the database, so the way I plan to implement
the de-encryption is with a de-encrypt function in a view.
Create View my_data
AS
Select de_encrypt(sensitive_data) AS sensitive_data
,other_data
FROM original_table
;
If I select from the view with a where clause on other_data, the response
time is fine. If I select from the view with a where clause on
sensitive_data, I do a full table scan and which takes about 15 minutes.
The de-encrypt function is copied from a Metalink note, nothing fancy.
Since I have various front ends, I can not de-encrypt the data in the
front
end. The only way I can think of is with the function in a view, but the
response time is unacceptable. Does anyone have any thoughts on this?
Thanks
Randy
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Steiner, Randy
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Steiner, Randy
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Kirsh, Gary
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Re: Ioug meeting or HOTSOS Seminar
I stand (sit?) corrected... the Hotsos Seminar will have vendors. Possibly a more selective list of vendors, but there will be vendors there. --- Rachel Carmichael [EMAIL PROTECTED] wrote: Depends on what you are looking for. The Hotsos Seminar will be strictly tuning, will be very intense on that, will not have vendors The IOUG conference will have a vendor hall, will have a much wider variety of presentations on all topics It's sort of like deciding between a meal at a buffet, where you get only a taste of a lot of things, or having a regular dinner, where you get a larger portion of something you want. --- Yechiel Adar [EMAIL PROTECTED] wrote: Hello List I got a message about Hotsos Tuning seminar in Dallas. Scheduled in February 2003. The lecturers list include among others: Tom, Anjo, Gaja. http://www.hotsos.com/events/symposium/ I also know that there is an IOUG Live meeting on May 2003, and many of you recommended going. http://bneo15.sba.com/ew/ioug/index3.cfm?clientsess_id=07332332nextpage=cal lpapersconference_id=71 MAYBE (not shouting but a big maybe) I will convince management to spring for one of the two. Which one you recommend? Yechiel Adar Mehish -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Yechiel Adar INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Yahoo! - We Remember 9-11: A tribute to the more than 3,000 lives lost http://dir.remember.yahoo.com/tribute -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Yahoo! - We Remember 9-11: A tribute to the more than 3,000 lives lost http://dir.remember.yahoo.com/tribute -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Fatal Error: cannot map libwtc8.so
Thomas: Try setting the LD_LIBRARY_PATH environment variable. Typically for Solaris it is set to the following: export LD_LIBRARY_PATH=/lib:/usr/lib:/usr/ccs/lib:/usr/dt/lib:/usr/ucblib:$ORACLE_HOME/lib:$ORACLE_HOME/network/lib export LIBPATH=$LD_LIBRARY_PATH If you still get that error, try relinking the sqlldr executable , using the make command: make -f $ORACLE_HOME/rdbms/lib/ins_rdbms.mk isqlldr I hope this helps. RWB Thomas Day [EMAIL PROTECTED]@fatcity.com on 09/10/2002 01:13:27 PM Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: I'm trying to use SQLLDR Oracle 8.1.6 on a UNIX box but I'm getting: Fatal Error: cannot map libwtc8.so My environmental variables are: ORACLE_BASE=/m1/oradec1/app/oracle ORACLE_HOME=$ORACLE_BASE/product/8.1.6 LIBPATH=$ORACLE_HOME/lib CLASSPATH=$ORACLE_HOME/jlib NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P9 NLS_DATE_FORMAT=DD-MON-YY ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data ORA_NLS=$ORACLE_HOME/ocommon/nls/admin/data PATH=$ORACLE_HOME/bin:$PATH The ORACLE_SID and ORACLE_TERM are also set. Am I missing something? Yes, I know about ORAENV but I'm not the SA and they have it pointing to 7.3.4 (our current production environment). I'm trying to override those values in my script. Thanks in advance. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Thomas Day INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: methodology to keep only certain programs to connect to
Jared(and others)
thanks, a bunch you all had what i was looking for perfectly.
joe
[EMAIL PROTECTED] 09/10/02 12:55PM
Joe,Try this:select
s.username, s.sid, s.serial#from v$session
swhere userenv('SESSIONID') =
s.audsid;Jared"JOE TESTA"
[EMAIL PROTECTED]Sent by: [EMAIL PROTECTED]09/10/2002 08:58
AMPlease respond to
ORACLE-L
To: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED] cc:
Subject: methodology to keep only
certain programs to connect toI've been tasked to ensure only
certain app programs access the database.I'm thinking on-logon trigger,
check the program field from v$session. unfortunately v$session is for all
sessions, i can't seem to find the view that tells me only MY info during
login. I only want the sid, serial#, username and program for my just
now connection to the database.Does this exist or am I going about this
the wrong way?We're thinking of checking those fields to make sure
sql*plus, toad, etc can't connect as a particular user(even though the
password is known out in the community).any ideas would be greatly
appreciated.joe
RE: Fatal Error: cannot map libwtc8.so
Thomas export LD_LIBRARY_PATH=$ORACLE_HOME/lib Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- Sent: Tuesday, September 10, 2002 1:13 PM To: Multiple recipients of list ORACLE-L I'm trying to use SQLLDR Oracle 8.1.6 on a UNIX box but I'm getting: Fatal Error: cannot map libwtc8.so My environmental variables are: ORACLE_BASE=/m1/oradec1/app/oracle ORACLE_HOME=$ORACLE_BASE/product/8.1.6 LIBPATH=$ORACLE_HOME/lib CLASSPATH=$ORACLE_HOME/jlib NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P9 NLS_DATE_FORMAT=DD-MON-YY ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data ORA_NLS=$ORACLE_HOME/ocommon/nls/admin/data PATH=$ORACLE_HOME/bin:$PATH The ORACLE_SID and ORACLE_TERM are also set. Am I missing something? Yes, I know about ORAENV but I'm not the SA and they have it pointing to 7.3.4 (our current production environment). I'm trying to override those values in my script. Thanks in advance. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Thomas Day INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: ALTER TABLE MOVE command causes table to grow
DEGREE=1 -Original Message- Sent: Monday, September 09, 2002 8:38 PM To: Multiple recipients of list ORACLE-L What is the DEGREE setting on the table? -Original Message- Sent: Monday, September 09, 2002 4:48 PM To: Multiple recipients of list ORACLE-L alter table email_request_queue move tablespace ts_email_request storage (initial 25m next 25m pctincrease 0); -Original Message- Sent: Monday, September 09, 2002 1:54 PM To: Multiple recipients of list ORACLE-L How did you perform the move? Was the operation done in parallel? -Original Message- Sent: Friday, September 06, 2002 4:13 PM To: Multiple recipients of list ORACLE-L No LOBS. Here's the definition: REQUEST_IDNOT NULL NUMBER PROFILE_IDNOT NULL NUMBER ACCOUNT_IDNOT NULL NUMBER TEMPLATE_ID NOT NULL NUMBER GENERIC_DIFFERENTIATORNOT NULL NUMBER REQUEST_TYPE NOT NULL CHAR(1) ACCOUNT_NONOT NULL CHAR(8) EFFECTIVE_DATENOT NULL DATE EMAIL_ADDRESS NOT NULL VARCHAR2(100) EMAIL_SUBJECT NOT NULL VARCHAR2(100) EMAIL_BODYNOT NULL VARCHAR2(4000) STATUSNOT NULL CHAR(1) STATUS_CHANGE_DATENOT NULL DATE TWEED_SERVER_IDNUMBER TWEED_PACKAGE_PRIORITY NUMBER TWEED_SENDER_ACCOUNT NOT NULL VARCHAR2(50) TWEED_SCHEDULED_DELIVERY_DATE NOT NULL DATE TWEED_CUSTOMER_URLNOT NULL VARCHAR2(255) SENDER_SERVER_ID NUMBER SENDER_INSTANCE_ID NUMBER CREATE_DATE NOT NULL DATE CREATE_USER NOT NULL VARCHAR2(35) UPDATE_DATEDATE UPDATE_USERVARCHAR2(35) -Original Message- Sent: Thursday, September 05, 2002 5:23 PM To: Multiple recipients of list ORACLE-L What the table definition? Are there any LOB's on it? -Original Message- Sent: Thursday, September 05, 2002 2:43 PM To: Multiple recipients of list ORACLE-L pct increase is 0 (uniform sizing) -Original Message- Sent: Thursday, September 05, 2002 2:00 PM To: Multiple recipients of list ORACLE-L you didn't mention the PCT_INCREASE of this segment. -Original Message- mailto:[EMAIL PROTECTED] ] Sent: Thursday, September 05, 2002 12:09 PM To: Multiple recipients of list ORACLE-L Had an annoying surprise last week. A table had grown unexpectedly large and I scheduled a time over the weekend to move it to its own tablespace from my medium tablespace. The table ended up growing 50%. I had anticipated it might grow somewhat given the PCTFREE of 10% but freeing up that space in the blocks should, at most, have caused it to grow by 10% (assuming that 10% was completely full). Does anyone have ideas as to why it would have grown by so much? Indexes are in a different tablespace and the only other change was from an extent size of 4 meg to one of 25 meg. Both are dictionary managed tablespaces. Oracle 8.1.7.2 Solaris 2.6 Thanks, Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.com http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Johnston, Tim INET: [EMAIL PROTECTED] Fat City Network Services-- (858)
Re: 9ir2 Install on Linux
Did you delete the OraInventory directory? It's location is specified in /var/opt/oracle/oraInst.loc file. Ron Thomas Hypercom, Inc [EMAIL PROTECTED] If A = B and B = C, then A = C, except where void or prohibited by law. -- Roy Santoro [EMAIL PROTECTED] To: [EMAIL PROTECTED] 09/10/02 09:18 AMcc: Please respond toSubject: 9ir2 Install on Linux ORACLE-L I am attempting to install 9ir2 on a Red Hat 7.2 Linux box. Part way through the first attempt I decided to changed the Oracle home value. I stopped the install, deleted the previous Oracle home directory tree, built the new directory and restarted the install. Oracle has stored the value of the old Oracle home somewhere. The install is now crashing because the old Oracle home does not exist. Any idea where it is store and who owns the file? I have already deleted the install files in /tmp. The .bash_profile has the correct Oracle home. = Pete Barnett Lead Database Administrator The Regence Group [EMAIL PROTECTED] __ Yahoo! - We Remember 9-11: A tribute to the more than 3,000 lives lost http://dir.remember.yahoo.com/tribute -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Peter Barnett INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ron Thomas INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re:Idle Connections
Samir, Yes there is, 1st set resource_limit = TRUE in the init.ora file. 2nd bounce the database. 3rd create a profile for those who are the inconsiderate bunch and set idle_timeout = x where x is in minutes. Their session will show up in V$session as sniped. You can then easily put together a perl script or something similar, I use Pro*C, that kills off the Unix process and really terminates their session. Works like a charm. Dick Goulet Reply Separator Author: SARKAR; Samir [EMAIL PROTECTED] Date: 9/10/2002 8:18 AM Hi All, I am facing problems with a database with some very irresponsible users who just connect to the database from their applications and simply dont logout. Apart from the usual chidings I have been giving them, could any of you please tell me whether any parameter exists which can be put either in the sqlnet.ora file or database parameter file which will timeout and close the idle connections after a particular time interval ?? Thanks, Samir Samir Sarkar Oracle DBA SchlumbergerSema Email : [EMAIL PROTECTED] Phone : +44 (0) 115 - 957 6028 EPABX : +44 (0) 115 - 957 6418 Ext. 76028 Fax : +44 (0) 115 - 957 6018 _ This email is confidential and intended solely for the use of the individual to whom it is addressed. Any views or opinions presented are solely those of the author and do not necessarily represent those of SchlumbergerSema. If you are not the intended recipient, be advised that you have received this email in error and that any use, dissemination, forwarding, printing, or copying of this email is strictly prohibited. If you have received this email in error please notify the SchlumbergerSema Helpdesk by telephone on +44 (0) 121 627 5600. _ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: SARKAR, Samir INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: 9ir2 Install on Linux
On a RH7.1 test box that I'd hosed, I needed to manually delete and reinstall Oracle. To do this, I needed to remove the $ORACLE_BASE/oraInventory directory (along with all the old $ORACLE_HOME dirs) as this contains all the fun stuff that the installer uses. But one shouldn't nuke directories without knowing the ramifications... :) Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- From: Peter Barnett [mailto:[EMAIL PROTECTED]] Sent: Tuesday, September 10, 2002 11:18 AM To: Multiple recipients of list ORACLE-L Subject: 9ir2 Install on Linux I am attempting to install 9ir2 on a Red Hat 7.2 Linux box. Part way through the first attempt I decided to changed the Oracle home value. I stopped the install, deleted the previous Oracle home directory tree, built the new directory and restarted the install. Oracle has stored the value of the old Oracle home somewhere. The install is now crashing because the old Oracle home does not exist. Any idea where it is store and who owns the file? I have already deleted the install files in /tmp. The .bash_profile has the correct Oracle home. = Pete Barnett Lead Database Administrator The Regence Group [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: methodology to keep only certain programs to connect to
I'm working with an application that uses a combination of encrypted seed numbers and password protected roles to limit access to the application tables to the specific application and version. In this database, any external application (sqlplus, etc) cannot provide access to the application tables since that requires activation of the password protected role. The only default role for users is a connect role that has only connect privs. And, you can't just grab a copy of the application from anywhere and use it against the database since the encrypted seed number compiled into the application is checked against the value in the database before a connection is permitted. Glenn Stauffer On Tue, 2002-09-10 at 11:58, JOE TESTA wrote: I've been tasked to ensure only certain app programs access the database. I'm thinking on-logon trigger, check the program field from v$session. unfortunately v$session is for all sessions, i can't seem to find the view that tells me only MY info during login. I only want the sid, serial#, username and program for my just now connection to the database. Does this exist or am I going about this the wrong way? We're thinking of checking those fields to make sure sql*plus, toad, etc can't connect as a particular user(even though the password is known out in the community). any ideas would be greatly appreciated. joe -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Glenn Stauffer INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
SQL Query tuning help
I am trying to tune a SQL query on Oracle 8.1.6. I have tried several optimizations, but so far have made no improvements. I would appreciate any suggestions. SELECT am.lid, am.name FROM am, so, sa WHERE so.lid = am.lid AND so.key_ = sa.so_key AND am.active = 1 AND so.code = 11 AND sa.ret = 'SB' ORDER BY am.name Tables: am - 250,000 rows, 220,000 rows have active = 1, the others are 0. so - 1.3 million rows, lid has 250,000 distinct values, key_ is unique, code has 12 values, evenly distributed. sa - 1.3 million rows, ret has 281 values, fairly evenly distributed. so_key is pretty unique. Now, you'll probably say there is essentially a 1-1 relationship between so and sa. You are right, but the developer insists this flexibility is essential. The query executes in 16 seconds and returns 185 rows. This is felt to be too slow for an online lookup screen. explain plan results: SELECT STATEMENT Cost = 2955 SORT ORDER BY HASH JOIN HASH JOIN TABLE ACCESS FULL SA TABLE ACCESS FULL SO TABLE ACCESS FULL AM Here is what I've tried so far: Using hints to force Oracle to use indexes. Query Plan SELECT STATEMENT Cost = 62031 SORT AGGREGATE NESTED LOOPS HASH JOIN TABLE ACCESS BY INDEX ROWID SA INDEX FULL SCAN SO_KEY3 TABLE ACCESS BY INDEX ROWID SO INDEX RANGE SCAN PRG_CODE3 TABLE ACCESS BY INDEX ROWID AM INDEX UNIQUE SCAN LID6 Timing result 25 minutes Next I tried creating new indexes that combine both the accessing column as well as the retrieved column, thinking that Oracle could get the result from the index block and not need to retrieve the data block. create index test1 on am (lid, active); create index test2 on sa (so_key, code); SELECT STATEMENT Cost = 2951 SORT AGGREGATE HASH JOIN HASH JOIN INDEX FULL SCAN TEST2 TABLE ACCESS FULL SO TABLE ACCESS BY INDEX ROWID AM INDEX RANGE SCAN TEST1 Hinting so Oracle will use the new indexes, for one table Oracle uses the index only and for the other table, Oracle hits both the index and table itself. Response time is slightly longer than the original query. At this point I'm fresh out of ideas, so any ideas would be appreciated. Thanks. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: methodology to keep only certain programs to connect to
Joe,
Create a view over V$SESSION with the condition
where audsid = SYS_CONTEXT('USERENV', 'SESSIONID')
call it USER_SESSION and grant SELECT TO PUBLIC on it.
--
Regards,
Stephane Faroult
Oriole Software
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Stephane Faroult
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: methodology to keep only certain programs to connect to
what are the drawbacks with such a trigger, what if the code went invalid and would not compile is it possible that you could lock yourself out, or would the base login functionality still work regardless or the status of this trigger? -Original Message-From: Mercadante, Thomas F [mailto:[EMAIL PROTECTED]]Sent: Tuesday, September 10, 2002 12:24 PMTo: Multiple recipients of list ORACLE-LSubject: RE: methodology to keep only certain programs to connect to Joe, I use the following with decent success on a logon database trigger: -- Set a unique string for the session and update the session info. client_info_str := 'WTWLOGIN_' || LTRIM(dbms_random.value,'.'); DBMS_APPLICATION_INFO.SET_CLIENT_INFO(client_info_str); -- look into the v$session view for the session just connected. SELECT program, username, osuser, terminal, machine INTO loc_program, loc_username, loc_osuser,loc_terminal,loc_machine FROM V$SESSION WHERE client_info=client_info_str; From here, you can test the loc_program variable against the loc_username to see if the combination is correct. Stuff like: IF loc_username='TESTLOGIN'then RAISE kill_Login; END IF; EXCEPTION WHEN kill_Login THEN RAISE_APPLICATION_ERROR(-20999,'Login''s using this account and this tool are Invalid'); Hope this helps! Tom Mercadante Oracle Certified Professional -Original Message-From: JOE TESTA [mailto:[EMAIL PROTECTED]]Sent: Tuesday, September 10, 2002 11:58 AMTo: Multiple recipients of list ORACLE-LSubject: methodology to keep only certain programs to connect to I've been tasked to ensure only certain app programs access the database. I'm thinking on-logon trigger, check the program field from v$session. unfortunately v$session is for all sessions, i can't seem to find the view that tells me only MY info during login. I only want the sid, serial#, username and program for my just now connection to the database. Does this exist or am I going about this the wrong way? We're thinking of checking those fields to make sure sql*plus, toad, etc can't connect as a particular user(even though the password is known out in the community). any ideas would be greatly appreciated. joe
Re: methodology to keep only certain programs to connect to
More thoughts : SQL*Plus fills MODULE in, don't know about TOAD (I think it does), but typically a number of PC clients may appear as the name of a DLL. I think that you shoud rather allow in than exclude out, and (ab)use DBMS_APPLICATION_INFO in the programs which are allowed. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Reorganizing a database
You can drop/re-create users and tablespaces, and import users. Down timealso depend on i/o bus and disk system. inka -Original Message-From: Brooks, Russ [mailto:[EMAIL PROTECTED]]Sent: Tuesday, September 10, 2002 11:44 AMTo: Multiple recipients of list ORACLE-LSubject: Reorganizing a database Hi, We are creating a 45GB database from a restore of a current system. My boss has requested that I reorganize the entire new database. I have my doubts that this will provide as much benefit as he seems to expect, but they are adamant that this is what they want. One of the desired effects is to resize all the datafiles to uniform sizes and distribute some I/O. I'm thinking of using a full export as the base of this activity, splitting the export. This will be done on an HP L class with 2 500MHz processors and 3 GB of memory. What is the best way of accomplishing this as quickly and painlessly as possible? I've looked through Metalink and various books without finding a good general procedure for changing the location and sizes of the datafiles during the import. About how long should this take (ballpark)? Thanks, Russ
Re: Reorganizing a database
I've done something similar in the past (move a database to a new machine and go to uniform extents). I didn't find any quick and easy methodology. Basically you have to pre-create the tablespaces where you want them and with the storage parameters that you want. Then import the database to an INDEX_FILE. The table creation statements will be there but they will be remarked out. Edit each table to remove it's storage parameters (so that it will inherit its tablespace's defaults) and run those statements to pre-create the tables where and how you want them. Then do the import with ignore=y but indexes=n. Edit the indexes from the INDEX_FILE to remove the storage parameters and create the indexes. HTH Brooks, RussTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Russ.Brooks cc: @dayzim.com Subject: Reorganizing a database Sent by: root 09/10/2002 11:43 AM Please respond to ORACLE-L Hi, We are creating a 45GB database from a restore of a current system. My boss has requested that I reorganize the entire new database. I have my doubts that this will provide as much benefit as he seems to expect, but they are adamant that this is what they want. One of the desired effects is to resize all the datafiles to uniform sizes and distribute some I/O. I'm thinking of using a full export as the base of this activity, splitting the export. This will be done on an HP L class with 2 500MHz processors and 3 GB of memory. What is the best way of accomplishing this as quickly and painlessly as possible? I've looked through Metalink and various books without finding a good general procedure for changing the location and sizes of the datafiles during the import. About how long should this take (ballpark)? Thanks, Russ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Thomas Day INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
OT: Misinformation Ranting
RANT I've just spent 30 minutes with our SAP administrator trying to convince her that we really don't need to reorganize the tables in our production SAP database. Due to some misinformation in an Oracle Press book, 'Oracle Unleashed' I think, she is equating number of extents with fragmentation. The text she referred me to is in fact discussing 'migrated rows' though that term is never used. She has become convinced that if the extents allocated for tables are not all in contigous space, some very nasty fragmentation will occur. I tried taking it down to disk and explaining that an OLTP system with hundreds of users won't really see much benefit from this, but she wasn't really ready for that. :) Her concern is that there are 29000 extents in an index tablespace. This might have something to do with there being 3400 indexes in said tablespace. Total 'wasted' ( honeycomb ) space in this 250 gig DB is 20 meg. Not much to gain there. The text of the book states that you should expect a '10 to 20 percent performance increase' by reorganizing the tables/indexes. No data to back it up of course. This is on a database that performs very well most of the time, outside of a couple of custom reports that run too long. No complaints from users about slowness. Arrghhh! I just had to vent to the list, cuz there's no one here that understands. \RANT Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: ALTER TABLE MOVE command causes table to grow
I hope this isn't overly simplistic but, is it because of the larger extent size? For instance, if it was in 4m extents before and used 26M, it would have fit in 7 extents with 2M of free space. But when moved to 25M extents, it would use 2 extents with 24M free space. How many segments is the table using, and what's the real size ( ie. sum(bytes))? Beth -Original Message- Sent: Tuesday, September 10, 2002 2:43 PM To: Multiple recipients of list ORACLE-L DEGREE=1 -Original Message- Sent: Monday, September 09, 2002 8:38 PM To: Multiple recipients of list ORACLE-L What is the DEGREE setting on the table? -Original Message- Sent: Monday, September 09, 2002 4:48 PM To: Multiple recipients of list ORACLE-L alter table email_request_queue move tablespace ts_email_request storage (initial 25m next 25m pctincrease 0); -Original Message- Sent: Monday, September 09, 2002 1:54 PM To: Multiple recipients of list ORACLE-L How did you perform the move? Was the operation done in parallel? -Original Message- Sent: Friday, September 06, 2002 4:13 PM To: Multiple recipients of list ORACLE-L No LOBS. Here's the definition: REQUEST_IDNOT NULL NUMBER PROFILE_IDNOT NULL NUMBER ACCOUNT_IDNOT NULL NUMBER TEMPLATE_ID NOT NULL NUMBER GENERIC_DIFFERENTIATORNOT NULL NUMBER REQUEST_TYPE NOT NULL CHAR(1) ACCOUNT_NONOT NULL CHAR(8) EFFECTIVE_DATENOT NULL DATE EMAIL_ADDRESS NOT NULL VARCHAR2(100) EMAIL_SUBJECT NOT NULL VARCHAR2(100) EMAIL_BODYNOT NULL VARCHAR2(4000) STATUSNOT NULL CHAR(1) STATUS_CHANGE_DATENOT NULL DATE TWEED_SERVER_IDNUMBER TWEED_PACKAGE_PRIORITY NUMBER TWEED_SENDER_ACCOUNT NOT NULL VARCHAR2(50) TWEED_SCHEDULED_DELIVERY_DATE NOT NULL DATE TWEED_CUSTOMER_URLNOT NULL VARCHAR2(255) SENDER_SERVER_ID NUMBER SENDER_INSTANCE_ID NUMBER CREATE_DATE NOT NULL DATE CREATE_USER NOT NULL VARCHAR2(35) UPDATE_DATEDATE UPDATE_USERVARCHAR2(35) -Original Message- Sent: Thursday, September 05, 2002 5:23 PM To: Multiple recipients of list ORACLE-L What the table definition? Are there any LOB's on it? -Original Message- Sent: Thursday, September 05, 2002 2:43 PM To: Multiple recipients of list ORACLE-L pct increase is 0 (uniform sizing) -Original Message- Sent: Thursday, September 05, 2002 2:00 PM To: Multiple recipients of list ORACLE-L you didn't mention the PCT_INCREASE of this segment. -Original Message- mailto:[EMAIL PROTECTED] ] Sent: Thursday, September 05, 2002 12:09 PM To: Multiple recipients of list ORACLE-L Had an annoying surprise last week. A table had grown unexpectedly large and I scheduled a time over the weekend to move it to its own tablespace from my medium tablespace. The table ended up growing 50%. I had anticipated it might grow somewhat given the PCTFREE of 10% but freeing up that space in the blocks should, at most, have caused it to grow by 10% (assuming that 10% was completely full). Does anyone have ideas as to why it would have grown by so much? Indexes are in a different tablespace and the only other change was from an extent size of 4 meg to one of 25 meg. Both are dictionary managed tablespaces. Oracle 8.1.7.2 Solaris 2.6 Thanks, Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.com http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE
OEM 9iR2 : Console won't launch from OEM Web Site
Sorry about the previous incomplete mail ! We've been trying unsuccessfully to get the OEM console to launch from OEM's website hosted on a Windows 2000 server running IIS (not oracle HTTP). The EM 9.2.0.1.0 readme clearly states that MS IIS 5.0 is can be used to serve the EM 9.2.0.1.0 website on Windows 2000, so this is appropriate (the website should install with the OMS). But we haven't been able to get this to work. Please let me know if anyone has seen this problem or can offer any solutions. thanks vikas -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kawatra V (Vikas) at Aera INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: SQL Query tuning help
Dennis, If you use the ordered hint and have sa then so then am and also hint to use the index on sa(ret) then I think that would be about the best as you'd be starting with the best filter ie 1.3m/281 giving less than 5000 on average (assuming ret is indexed). I don't know if you'd have to through in an use_nl also. Iain Nicoll -Original Message- Sent: Tuesday, September 10, 2002 8:19 PM To: Multiple recipients of list ORACLE-L I am trying to tune a SQL query on Oracle 8.1.6. I have tried several optimizations, but so far have made no improvements. I would appreciate any suggestions. SELECT am.lid, am.name FROM am, so, sa WHERE so.lid = am.lid AND so.key_ = sa.so_key AND am.active = 1 AND so.code = 11 AND sa.ret = 'SB' ORDER BY am.name Tables: am - 250,000 rows, 220,000 rows have active = 1, the others are 0. so - 1.3 million rows, lid has 250,000 distinct values, key_ is unique, code has 12 values, evenly distributed. sa - 1.3 million rows, ret has 281 values, fairly evenly distributed. so_key is pretty unique. Now, you'll probably say there is essentially a 1-1 relationship between so and sa. You are right, but the developer insists this flexibility is essential. The query executes in 16 seconds and returns 185 rows. This is felt to be too slow for an online lookup screen. explain plan results: SELECT STATEMENT Cost = 2955 SORT ORDER BY HASH JOIN HASH JOIN TABLE ACCESS FULL SA TABLE ACCESS FULL SO TABLE ACCESS FULL AM Here is what I've tried so far: Using hints to force Oracle to use indexes. Query Plan SELECT STATEMENT Cost = 62031 SORT AGGREGATE NESTED LOOPS HASH JOIN TABLE ACCESS BY INDEX ROWID SA INDEX FULL SCAN SO_KEY3 TABLE ACCESS BY INDEX ROWID SO INDEX RANGE SCAN PRG_CODE3 TABLE ACCESS BY INDEX ROWID AM INDEX UNIQUE SCAN LID6 Timing result 25 minutes Next I tried creating new indexes that combine both the accessing column as well as the retrieved column, thinking that Oracle could get the result from the index block and not need to retrieve the data block. create index test1 on am (lid, active); create index test2 on sa (so_key, code); SELECT STATEMENT Cost = 2951 SORT AGGREGATE HASH JOIN HASH JOIN INDEX FULL SCAN TEST2 TABLE ACCESS FULL SO TABLE ACCESS BY INDEX ROWID AM INDEX RANGE SCAN TEST1 Hinting so Oracle will use the new indexes, for one table Oracle uses the index only and for the other table, Oracle hits both the index and table itself. Response time is slightly longer than the original query. At this point I'm fresh out of ideas, so any ideas would be appreciated. Thanks. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nicoll, Iain \(Calanais\) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Fatal Error: cannot map libwtc8.so
I don't see LD_LIBRARY_PATH in there. It should be set to $ORACLE_HOME/lib Jared Thomas Day [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 09/10/2002 11:13 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Fatal Error: cannot map libwtc8.so I'm trying to use SQLLDR Oracle 8.1.6 on a UNIX box but I'm getting: Fatal Error: cannot map libwtc8.so My environmental variables are: ORACLE_BASE=/m1/oradec1/app/oracle ORACLE_HOME=$ORACLE_BASE/product/8.1.6 LIBPATH=$ORACLE_HOME/lib CLASSPATH=$ORACLE_HOME/jlib NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P9 NLS_DATE_FORMAT=DD-MON-YY ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data ORA_NLS=$ORACLE_HOME/ocommon/nls/admin/data PATH=$ORACLE_HOME/bin:$PATH The ORACLE_SID and ORACLE_TERM are also set. Am I missing something? Yes, I know about ORAENV but I'm not the SA and they have it pointing to 7.3.4 (our current production environment). I'm trying to override those values in my script. Thanks in advance. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Thomas Day INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: methodology to keep only certain programs to connect to
I have always thought this was the best way to implement a security package. Nice to see you implemented the seed number for changing encryption. -Original Message- Sent: Tuesday, September 10, 2002 1:49 PM To: Multiple recipients of list ORACLE-L I'm working with an application that uses a combination of encrypted seed numbers and password protected roles to limit access to the application tables to the specific application and version. In this database, any external application (sqlplus, etc) cannot provide access to the application tables since that requires activation of the password protected role. The only default role for users is a connect role that has only connect privs. And, you can't just grab a copy of the application from anywhere and use it against the database since the encrypted seed number compiled into the application is checked against the value in the database before a connection is permitted. Glenn Stauffer On Tue, 2002-09-10 at 11:58, JOE TESTA wrote: I've been tasked to ensure only certain app programs access the database. I'm thinking on-logon trigger, check the program field from v$session. unfortunately v$session is for all sessions, i can't seem to find the view that tells me only MY info during login. I only want the sid, serial#, username and program for my just now connection to the database. Does this exist or am I going about this the wrong way? We're thinking of checking those fields to make sure sql*plus, toad, etc can't connect as a particular user(even though the password is known out in the community). any ideas would be greatly appreciated. joe -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Glenn Stauffer INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kevin Lange INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: using obfuscation
Title: RE: using obfuscation
Wouldnt
that store the sensitive data in the index?
-Original
Message-
From: Jamadagni, Rajendra
[mailto:[EMAIL PROTECTED]]
Sent: Tuesday, September 10, 2002
12:13 PM
To: Multiple recipients of list
ORACLE-L
Subject: RE: using obfuscation
Can you create a Function based index on
that column? That could be of use ...
Raj
__
Rajendra Jamadagni
MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of
ESPN Inc.
QOTD: Any clod can have facts, but having an opinion is an art!
-Original Message-
From: Steiner, Randy [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, September 10, 2002 11:58 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: using obfuscation
Don,
It seems like a real good idea, but what
am I putting inside my call to the
encrypt function in my Create View statement?
Randy
-Original Message-
Sent: Tuesday, September 10, 2002 10:13 AM
To: Multiple recipients of list ORACLE-L
Subject: Re: using
obfuscation
File: Card for Don Jerman
What about...
create view my_data as select de_encrypt(sensitive_data)
as
clear_sensitive_data where
sensitive_data = encrypt('CLEAR TEXT') ?
This lets you create an index on the
sensitive data without decrypting it,
and
the function need only be called once on the clear text.
Caveat: no idea if this should work :)
Steiner, Randy wrote:
Hi all,
I have downloaded the Metalink Notes on implementing
dbms_obfuscation. I
am
using multiple front ends on the database, so the way I plan
to implement
the de-encryption is with a de-encrypt function in a view.
Create View my_data
AS
Select de_encrypt(sensitive_data) AS sensitive_data
,other_data
FROM original_table
;
If I select from the view with a where clause on other_data,
the response
time is fine. If I select from the view with a where clause
on
sensitive_data, I do a full table scan and which takes about
15 minutes.
The de-encrypt function is copied from a Metalink note,
nothing fancy.
Since I have various front ends, I can not de-encrypt the
data in the
front
end. The only way I can think of is with the function
in a view, but the
response time is unacceptable. Does anyone have any
thoughts on this?
Thanks
Randy
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Steiner, Randy
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858)
538-5051 FAX: (858) 538-5051
San Diego,
California -- Public Internet access
/ Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail
message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru')
and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed
from). You may
also send the HELP command for other information (like
subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Steiner, Randy
INET: [EMAIL PROTECTED]
Fat City Network
Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California --
Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and
in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).
You may
also send the HELP command for other information (like
subscribing).
RE: methodology to keep only certain programs to connect to
My experience is that an invalid trigger doesn't fire --- no effect. Also, userids with the DBA role don't fire the trigger. So you can't lock yourself out of the database. Just go in with a DBA role userid and drop the logon trigger. Markham, Richard To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] RMarkhamcc: @hafeleamericSubject: RE: methodology to keep only certain programs to connect to as.com Sent by: root 09/10/2002 01:18 PM Please respond to ORACLE-L what are the drawbacks with such a trigger, what if the code went invalid and would not compile is it possible that you could lock yourself out, or would the base login functionality still work regardless or the status of this trigger? -Original Message- Sent: Tuesday, September 10, 2002 12:24 PM To: Multiple recipients of list ORACLE-L Joe, I use the following with decent success on a logon database trigger: -- Set a unique string for the session and update the session info. client_info_str := 'WTWLOGIN_' || LTRIM(dbms_random.value,'.'); DBMS_APPLICATION_INFO.SET_CLIENT_INFO(client_info_str); -- look into the v$session view for the session just connected. SELECT program, username, osuser, terminal, machine INTO loc_program, loc_username, loc_osuser,loc_terminal,loc_machine FROM V$SESSION WHERE client_info=client_info_str; From here, you can test the loc_program variable against the loc_username to see if the combination is correct. Stuff like: IF loc_username='TESTLOGIN' then RAISE kill_Login; END IF; EXCEPTION WHEN kill_Login THEN RAISE_APPLICATION_ERROR(-20999,'Login''s using this account and this tool are Invalid'); Hope this helps! Tom Mercadante Oracle Certified Professional -Original Message- Sent: Tuesday, September 10, 2002 11:58 AM To: Multiple recipients of list ORACLE-L I've been tasked to ensure only certain app programs access the database. I'm thinking on-logon trigger, check the program field from v$session. unfortunately v$session is for all sessions, i can't seem to find the view that tells me only MY info during login. I only want the sid, serial#, username and program for my just now connection to the database. Does this exist or am I going about this the wrong way? We're thinking of checking those fields to make sure sql*plus, toad, etc can't connect as a particular user(even though the password is known out in the community). any ideas would be greatly appreciated. joe -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Thomas Day INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: methodology to keep only certain programs to connect to
You can't. This is one of the reasons I haven't tried to use this. Jared Kevin Lange [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 09/10/2002 10:53 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: methodology to keep only certain programs to connect to With a setup like this, how do you stop a user from simply renaming the program they are using to match what you expect to see and, therefore, getting past your security ?? -Original Message- Sent: Tuesday, September 10, 2002 11:59 AM To: Multiple recipients of list ORACLE-L use v_$mystat - it has the sid - then do your join with v$session -Original Message- Sent: Tuesday, September 10, 2002 10:58 AM To: Multiple recipients of list ORACLE-L I've been tasked to ensure only certain app programs access the database. I'm thinking on-logon trigger, check the program field from v$session. unfortunately v$session is for all sessions, i can't seem to find the view that tells me only MY info during login. I only want the sid, serial#, username and program for my just now connection to the database. Does this exist or am I going about this the wrong way? We're thinking of checking those fields to make sure sql*plus, toad, etc can't connect as a particular user(even though the password is known out in the community). any ideas would be greatly appreciated. joe -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: dbwr high count of threads
Hi Richard, A couple of questions. What version of Oracle? What version of Solaris? Are you using asynch I/O? Are you on filesystems? If so, which one (ufs, vxfs, vxfs with quickio)? Have you specified ioslaves? If so, how many? How did you determine how many threads you were using? Is the entire database on NFS? Is the server crash a Solaris crash or and Oracle crash? Dave Miller X-Unix-From: [EMAIL PROTECTED] Mon Sep 9 16:26:57 2002 Date: Mon, 09 Sep 2002 14:18:24 -0800 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] X-Comment: Oracle RDBMS Community Forum X-Sender: Ji, Richard [EMAIL PROTECTED] From: Ji, Richard [EMAIL PROTECTED] Subject: dbwr high count of threads X-ListServer: v1.0g, build 72; ListGuru (c) 1996-2001 Bruce A. Bergman Mime-Version: 1.0 Content-Transfer-Encoding: 7bit Hi all, I noticed the dbwr process on Solaris has a very high number of threads (258). To me this is not a problem since I am seeing this on my small development box too. But we recently had some server crash and the consultant is saying Oracle is consuming a lots of resource, citing the high number of Oracle thread count from the core dump analysis. I don't believe this lead to the crash because the core stack trace points to NFS calls in both times. However, I don't know how to explain the high number thread count mostly from the dbwr process. Is this normal? I mean, it looks like it's normal since I see this on all of my instances. How do I convince him that this is ok? Thanks for your help. Richard -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ji, Richard INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: David Miller INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
OEM 9iR2 : Console won't launch from OEM Web Site
Is there a -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kawatra V (Vikas) at Aera INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: SQL Query tuning help
DENNIS WILLIAMS wrote: I am trying to tune a SQL query on Oracle 8.1.6. I have tried several optimizations, but so far have made no improvements. I would appreciate any suggestions. SELECT am.lid, am.name FROM am, so, sa WHERE so.lid = am.lid AND so.key_ = sa.so_key AND am.active = 1 AND so.code = 11 AND sa.ret = 'SB' ORDER BY am.name Tables: am - 250,000 rows, 220,000 rows have active = 1, the others are 0. so - 1.3 million rows, lid has 250,000 distinct values, key_ is unique, code has 12 values, evenly distributed. sa - 1.3 million rows, ret has 281 values, fairly evenly distributed. so_key is pretty unique. Now, you'll probably say there is essentially a 1-1 relationship between so and sa. You are right, but the developer insists this flexibility is essential. The query executes in 16 seconds and returns 185 rows. This is felt to be too slow for an online lookup screen. explain plan results: SELECT STATEMENT Cost = 2955 SORT ORDER BY HASH JOIN HASH JOIN TABLE ACCESS FULL SA TABLE ACCESS FULL SO TABLE ACCESS FULL AM Here is what I've tried so far: Using hints to force Oracle to use indexes. Query Plan SELECT STATEMENT Cost = 62031 SORT AGGREGATE NESTED LOOPS HASH JOIN TABLE ACCESS BY INDEX ROWID SA INDEX FULL SCAN SO_KEY3 TABLE ACCESS BY INDEX ROWID SO INDEX RANGE SCAN PRG_CODE3 TABLE ACCESS BY INDEX ROWID AM INDEX UNIQUE SCAN LID6 Timing result 25 minutes Next I tried creating new indexes that combine both the accessing column as well as the retrieved column, thinking that Oracle could get the result from the index block and not need to retrieve the data block. create index test1 on am (lid, active); create index test2 on sa (so_key, code); SELECT STATEMENT Cost = 2951 SORT AGGREGATE HASH JOIN HASH JOIN INDEX FULL SCAN TEST2 TABLE ACCESS FULL SO TABLE ACCESS BY INDEX ROWID AM INDEX RANGE SCAN TEST1 Hinting so Oracle will use the new indexes, for one table Oracle uses the index only and for the other table, Oracle hits both the index and table itself. Response time is slightly longer than the original query. At this point I'm fresh out of ideas, so any ideas would be appreciated. Thanks. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] Dennis, I note that your select list is only made of columns from am. Your entry points are so.code and sa.ret, the second one being the most selective. I don't think that on such a volume a nested loop would be any better than a hash join between the two, so this part of the Oracle plan needs no change. However, a nested loop is probably what you need with am. I would try things such as SELECT am.lid, am.name FROM am WHERE am.lid in (SELECT so.lid from so, sa WHERE so.key_ = sa.so_key AND so.code = 11 AND sa.ret = 'SB') AND am.active = 1 ORDER BY am.name which may give the same plan as your first example; if this is the case, perhaps that SELECT am.lid, am.name FROM (SELECT so.lid from so, sa WHERE so.key_ = sa.so_key AND so.code = 11 AND sa.ret = 'SB') x, am WHERE am.lid = x.lid AND am.active = 1 ORDER BY am.name will give a better result. If it still doesn't, try the ORDERED hint after the first SELECT. If it still doesn't, add USE_NL(am) after ORDERED but I'd rather avoid it. Thinking while I'm typing, perhaps that all you need is a FIRST_ROWS hint. -- HTH, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: SQL Query tuning help
Dennis, What is the distribution of sa.ret? I didn't see it included in an index. Jared DENNIS WILLIAMS [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 09/10/2002 12:18 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:SQL Query tuning help I am trying to tune a SQL query on Oracle 8.1.6. I have tried several optimizations, but so far have made no improvements. I would appreciate any suggestions. SELECT am.lid, am.name FROM am, so, sa WHERE so.lid = am.lid AND so.key_ = sa.so_key AND am.active = 1 AND so.code = 11 AND sa.ret = 'SB' ORDER BY am.name Tables: am - 250,000 rows, 220,000 rows have active = 1, the others are 0. so - 1.3 million rows, lid has 250,000 distinct values, key_ is unique, code has 12 values, evenly distributed. sa - 1.3 million rows, ret has 281 values, fairly evenly distributed. so_key is pretty unique. Now, you'll probably say there is essentially a 1-1 relationship between so and sa. You are right, but the developer insists this flexibility is essential. The query executes in 16 seconds and returns 185 rows. This is felt to be too slow for an online lookup screen. explain plan results: SELECT STATEMENT Cost = 2955 SORT ORDER BY HASH JOIN HASH JOIN TABLE ACCESS FULL SA TABLE ACCESS FULL SO TABLE ACCESS FULL AM Here is what I've tried so far: Using hints to force Oracle to use indexes. Query Plan SELECT STATEMENT Cost = 62031 SORT AGGREGATE NESTED LOOPS HASH JOIN TABLE ACCESS BY INDEX ROWID SA INDEX FULL SCAN SO_KEY3 TABLE ACCESS BY INDEX ROWID SO INDEX RANGE SCAN PRG_CODE3 TABLE ACCESS BY INDEX ROWID AM INDEX UNIQUE SCAN LID6 Timing result 25 minutes Next I tried creating new indexes that combine both the accessing column as well as the retrieved column, thinking that Oracle could get the result from the index block and not need to retrieve the data block. create index test1 on am (lid, active); create index test2 on sa (so_key, code); SELECT STATEMENT Cost = 2951 SORT AGGREGATE HASH JOIN HASH JOIN INDEX FULL SCAN TEST2 TABLE ACCESS FULL SO TABLE ACCESS BY INDEX ROWID AM INDEX RANGE SCAN TEST1 Hinting so Oracle will use the new indexes, for one table Oracle uses the index only and for the other table, Oracle hits both the index and table itself. Response time is slightly longer than the original query. At this point I'm fresh out of ideas, so any ideas would be appreciated. Thanks. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: SQL Query tuning help
Dennis, You're better off not having an index on the AM table. With 220,000 out of 250,000 rows having the same value, an index will do you more harm than good. You're not much better off on the SO table with only 12 different values out of 1.3 million. The final table SA has 281 different out of 1.3 million. I see why the optimizer chose a table scan. It has to look through most of the table anyway. I would try it with an index of each of your join fields plus a separate index on the ret field of the SA table. I wouldn't even try to index any other fields on the AM or SO tables. Actually, 16 second response time didn't sound too bad to me considering the tables you described. Bill Carle ATT Database Administrator 816-995-3922 [EMAIL PROTECTED] -Original Message- Sent: Tuesday, September 10, 2002 2:19 PM To: Multiple recipients of list ORACLE-L Subject:SQL Query tuning help I am trying to tune a SQL query on Oracle 8.1.6. I have tried several optimizations, but so far have made no improvements. I would appreciate any suggestions. SELECT am.lid, am.name FROM am, so, sa WHERE so.lid = am.lid AND so.key_ = sa.so_key AND am.active = 1 AND so.code = 11 AND sa.ret = 'SB' ORDER BY am.name Tables: am - 250,000 rows, 220,000 rows have active = 1, the others are 0. so - 1.3 million rows, lid has 250,000 distinct values, key_ is unique, code has 12 values, evenly distributed. sa - 1.3 million rows, ret has 281 values, fairly evenly distributed. so_key is pretty unique. Now, you'll probably say there is essentially a 1-1 relationship between so and sa. You are right, but the developer insists this flexibility is essential. The query executes in 16 seconds and returns 185 rows. This is felt to be too slow for an online lookup screen. explain plan results: SELECT STATEMENT Cost = 2955 SORT ORDER BY HASH JOIN HASH JOIN TABLE ACCESS FULL SA TABLE ACCESS FULL SO TABLE ACCESS FULL AM Here is what I've tried so far: Using hints to force Oracle to use indexes. Query Plan SELECT STATEMENT Cost = 62031 SORT AGGREGATE NESTED LOOPS HASH JOIN TABLE ACCESS BY INDEX ROWID SA INDEX FULL SCAN SO_KEY3 TABLE ACCESS BY INDEX ROWID SO INDEX RANGE SCAN PRG_CODE3 TABLE ACCESS BY INDEX ROWID AM INDEX UNIQUE SCAN LID6 Timing result 25 minutes Next I tried creating new indexes that combine both the accessing column as well as the retrieved column, thinking that Oracle could get the result from the index block and not need to retrieve the data block. create index test1 on am (lid, active); create index test2 on sa (so_key, code); SELECT STATEMENT Cost = 2951 SORT AGGREGATE HASH JOIN HASH JOIN INDEX FULL SCAN TEST2 TABLE ACCESS FULL SO TABLE ACCESS BY INDEX ROWID AM INDEX RANGE SCAN TEST1 Hinting so Oracle will use the new indexes, for one table Oracle uses the index only and for the other table, Oracle hits both the index and table itself. Response time is slightly longer than the original query. At this point I'm fresh out of ideas, so any ideas would be appreciated. Thanks. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Carle, William T (Bill), ALCAS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Rerunning CSSCAN character set scan utility on same database
With the end-goal of doing a character set conversion from US7ASCII to UTF8, I have run csminst.sql to create work tables and then run the CSSCAN utility from rdbms/admin on my test database. We have written a C program to identify and alter exceptional characters identified by the CSSCAN utility to valid US7ASCII characters. I then reran csminst.sql and reran CSSCAN utility. Exactly the same number of exceptions were identified by the rescan as were identified in the original character set scan. Either I am missing a step or the C program is not actually replacing the bad characters. Anyone know of some step I need to take to clear out my work files before I rerun the character set scan? I don't see anything in the Notes on Metalink for rerunning csscan on the same database? Cherie -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: methodology to keep only certain programs to connect to
Title: RE: methodology to keep only certain programs to connect to Revoke all roles from all apps. You will have to change some code in authorized apps to enable roles after they log in to allow them to access the database. All stray applications won't do this, so even if they log in they won't be able to access anything. BTW SQLPLUS and TOAD use dbms_application_info to set the module column in v$session. This you can capture in db-logon trigger and kill them. At that stage, it is way too early to change the module information. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Tuesday, September 10, 2002 4:03 PM To: Multiple recipients of list ORACLE-L Subject: RE: methodology to keep only certain programs to connect to You can't. This is one of the reasons I haven't tried to use this. Jared 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.*2
Re: OT: Misinformation Ranting
So, did you bend to her wishes? ltiu [EMAIL PROTECTED] wrote: RANT I've just spent 30 minutes with our SAP administrator trying to convince her that we really don't need to reorganize the tables in our production SAP database. Due to some misinformation in an Oracle Press book, 'Oracle Unleashed' I think, she is equating number of extents with fragmentation. The text she referred me to is in fact discussing 'migrated rows' though that term is never used. She has become convinced that if the extents allocated for tables are not all in contigous space, some very nasty fragmentation will occur. I tried taking it down to disk and explaining that an OLTP system with hundreds of users won't really see much benefit from this, but she wasn't really ready for that. :) Her concern is that there are 29000 extents in an index tablespace. This might have something to do with there being 3400 indexes in said tablespace. Total 'wasted' ( honeycomb ) space in this 250 gig DB is 20 meg. Not much to gain there. The text of the book states that you should expect a '10 to 20 percent performance increase' by reorganizing the tables/indexes. No data to back it up of course. This is on a database that performs very well most of the time, outside of a couple of custom reports that run too long. No complaints from users about slowness. Arrghhh! I just had to vent to the list, cuz there's no one here that understands. \RANT Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: ltiu INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: using obfuscation
Title: RE: using obfuscation I wouldn't worry about that ... unless you dump the index blocks, you won't see that data anyway. OTOH, why don't you set your view to select based on a PK and then apply the function on selected rows?? Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- From: Steiner, Randy [mailto:[EMAIL PROTECTED]] Sent: Tuesday, September 10, 2002 4:13 PM To: Multiple recipients of list ORACLE-L Subject: RE: using obfuscation Wouldn't that store the sensitive data in the index? -Original Message- From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]] Sent: Tuesday, September 10, 2002 12:13 PM To: Multiple recipients of list ORACLE-L Subject: RE: using obfuscation Can you create a Function based index on that column? That could be of use ... Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but 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.*2
Problem upgrading Oracle 8.1.7 on MS2000
I am trying to upgrade Oracle 8.1.7 on MS2000 to patchset 8.1.7.4.1. I keep getting a message that says a component that the installer needs to update is busy. It would be too easy to tell me what it is. I have stopped everything related to Oracle but I still get the message. Can anyone tell me what else I should stop? Is there a log file that tells me what it is trying to do? Thanks! R. Smith -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Smith, Ron L. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re:OT: Misinformation Ranting
Jared, What version of the database does this book refer to? I do remember back in Version 6 and early 7 that having all of your data for a table and/or index all in the first extent was a performance benefit. Regrettably that idea had been proved false more than once after 7.2 hit the street. I notice a performance improvement if tables/indexes are in more than one extent. But if she's talking about migrated and/or chained rows that's another matter one that I'm chasing as I type. Looks like you've got a classic case of someone with enough information to be dangerous. Dick Goulet Reply Separator Author: [EMAIL PROTECTED] Date: 9/10/2002 12:28 PM RANT I've just spent 30 minutes with our SAP administrator trying to convince her that we really don't need to reorganize the tables in our production SAP database. Due to some misinformation in an Oracle Press book, 'Oracle Unleashed' I think, she is equating number of extents with fragmentation. The text she referred me to is in fact discussing 'migrated rows' though that term is never used. She has become convinced that if the extents allocated for tables are not all in contigous space, some very nasty fragmentation will occur. I tried taking it down to disk and explaining that an OLTP system with hundreds of users won't really see much benefit from this, but she wasn't really ready for that. :) Her concern is that there are 29000 extents in an index tablespace. This might have something to do with there being 3400 indexes in said tablespace. Total 'wasted' ( honeycomb ) space in this 250 gig DB is 20 meg. Not much to gain there. The text of the book states that you should expect a '10 to 20 percent performance increase' by reorganizing the tables/indexes. No data to back it up of course. This is on a database that performs very well most of the time, outside of a couple of custom reports that run too long. No complaints from users about slowness. Arrghhh! I just had to vent to the list, cuz there's no one here that understands. \RANT Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Misinformation Ranting
You have my profound sympathies, having been there and done that. Russ -Original Message- Sent: Tuesday, September 10, 2002 4:29 PM To: Multiple recipients of list ORACLE-L RANT I've just spent 30 minutes with our SAP administrator trying to convince her that we really don't need to reorganize the tables in our production SAP database. Due to some misinformation in an Oracle Press book, 'Oracle Unleashed' I think, she is equating number of extents with fragmentation. The text she referred me to is in fact discussing 'migrated rows' though that term is never used. She has become convinced that if the extents allocated for tables are not all in contigous space, some very nasty fragmentation will occur. I tried taking it down to disk and explaining that an OLTP system with hundreds of users won't really see much benefit from this, but she wasn't really ready for that. :) Her concern is that there are 29000 extents in an index tablespace. This might have something to do with there being 3400 indexes in said tablespace. Total 'wasted' ( honeycomb ) space in this 250 gig DB is 20 meg. Not much to gain there. The text of the book states that you should expect a '10 to 20 percent performance increase' by reorganizing the tables/indexes. No data to back it up of course. This is on a database that performs very well most of the time, outside of a couple of custom reports that run too long. No complaints from users about slowness. Arrghhh! I just had to vent to the list, cuz there's no one here that understands. \RANT Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Brooks, Russ INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: SQL Query tuning help
Thanks everyone for your wonderful suggestions. And thanks for leaving the hey stupid off your reply header :-) Rachel - Thanks for the bitmapped idea. These tables don't change often, so that may be a good alternative. Iain - Thanks so much for the detailed suggestions. Rick - Good sanity check, yes, I analyzed the tables. Jared - RET has 281 values, pretty evenly distributed Cary - Query returns 185 rows. Bill - Thanks for the suggestions and insights. Stephane - Good notice that only am values are used. Guess that is why Oracle accessed the data blocks anyway with my new indexes. Duh. Good ideas. Jeff - Thanks for the Mickey Mouse tag. I may need that in the future. Previously this data was on an old mainframe and the business itself was restricted by the inflexibility. My gut reaction was that they overcompensated. Thanks everyone for the wonderful ideas. I was just given a hot project, so it may be a day or two before I get a chance to explore all of them, but I'll let you know. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- Sent: Tuesday, September 10, 2002 2:19 PM To: Multiple recipients of list ORACLE-L I am trying to tune a SQL query on Oracle 8.1.6. I have tried several optimizations, but so far have made no improvements. I would appreciate any suggestions. SELECT am.lid, am.name FROM am, so, sa WHERE so.lid = am.lid AND so.key_ = sa.so_key AND am.active = 1 AND so.code = 11 AND sa.ret = 'SB' ORDER BY am.name Tables: am - 250,000 rows, 220,000 rows have active = 1, the others are 0. so - 1.3 million rows, lid has 250,000 distinct values, key_ is unique, code has 12 values, evenly distributed. sa - 1.3 million rows, ret has 281 values, fairly evenly distributed. so_key is pretty unique. Now, you'll probably say there is essentially a 1-1 relationship between so and sa. You are right, but the developer insists this flexibility is essential. The query executes in 16 seconds and returns 185 rows. This is felt to be too slow for an online lookup screen. explain plan results: SELECT STATEMENT Cost = 2955 SORT ORDER BY HASH JOIN HASH JOIN TABLE ACCESS FULL SA TABLE ACCESS FULL SO TABLE ACCESS FULL AM Here is what I've tried so far: Using hints to force Oracle to use indexes. Query Plan SELECT STATEMENT Cost = 62031 SORT AGGREGATE NESTED LOOPS HASH JOIN TABLE ACCESS BY INDEX ROWID SA INDEX FULL SCAN SO_KEY3 TABLE ACCESS BY INDEX ROWID SO INDEX RANGE SCAN PRG_CODE3 TABLE ACCESS BY INDEX ROWID AM INDEX UNIQUE SCAN LID6 Timing result 25 minutes Next I tried creating new indexes that combine both the accessing column as well as the retrieved column, thinking that Oracle could get the result from the index block and not need to retrieve the data block. create index test1 on am (lid, active); create index test2 on sa (so_key, code); SELECT STATEMENT Cost = 2951 SORT AGGREGATE HASH JOIN HASH JOIN INDEX FULL SCAN TEST2 TABLE ACCESS FULL SO TABLE ACCESS BY INDEX ROWID AM INDEX RANGE SCAN TEST1 Hinting so Oracle will use the new indexes, for one table Oracle uses the index only and for the other table, Oracle hits both the index and table itself. Response time is slightly longer than the original query. At this point I'm fresh out of ideas, so any ideas would be appreciated. Thanks. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB
RE: Misinformation Ranting
Doing Phil Hartman's impression of Bill Clinton 'We feel your pain' end impression -Original Message- Sent: Tuesday, September 10, 2002 2:29 PM To: Multiple recipients of list ORACLE-L RANT I've just spent 30 minutes with our SAP administrator trying to convince her that we really don't need to reorganize the tables in our production SAP database. Due to some misinformation in an Oracle Press book, 'Oracle Unleashed' I think, she is equating number of extents with fragmentation. The text she referred me to is in fact discussing 'migrated rows' though that term is never used. She has become convinced that if the extents allocated for tables are not all in contigous space, some very nasty fragmentation will occur. I tried taking it down to disk and explaining that an OLTP system with hundreds of users won't really see much benefit from this, but she wasn't really ready for that. :) Her concern is that there are 29000 extents in an index tablespace. This might have something to do with there being 3400 indexes in said tablespace. Total 'wasted' ( honeycomb ) space in this 250 gig DB is 20 meg. Not much to gain there. The text of the book states that you should expect a '10 to 20 percent performance increase' by reorganizing the tables/indexes. No data to back it up of course. This is on a database that performs very well most of the time, outside of a couple of custom reports that run too long. No complaints from users about slowness. Arrghhh! I just had to vent to the list, cuz there's no one here that understands. \RANT Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Fink, Dan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: OEM 9iR2 : Console won't launch from OEM Web Site
Kawatra V (Vikas) at Aera wrote: Is there a No. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Misinformation Ranting
I feel your pain. You got the publisher right, but it's not Oracle Unleashed. It's from p109 of the best-selling but utterly reprehensible Oracle Performance Tuning Tips Techniques book. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic, Oct 1-3 San Francisco, Oct 15-17 Dallas, Dec 9-11 Honolulu - 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12 Dallas - Next event: Miracle Database Forum, Sep 20-22 Middelfart Denmark -Original Message- [EMAIL PROTECTED] Sent: Tuesday, September 10, 2002 3:29 PM To: Multiple recipients of list ORACLE-L RANT I've just spent 30 minutes with our SAP administrator trying to convince her that we really don't need to reorganize the tables in our production SAP database. Due to some misinformation in an Oracle Press book, 'Oracle Unleashed' I think, she is equating number of extents with fragmentation. The text she referred me to is in fact discussing 'migrated rows' though that term is never used. She has become convinced that if the extents allocated for tables are not all in contigous space, some very nasty fragmentation will occur. I tried taking it down to disk and explaining that an OLTP system with hundreds of users won't really see much benefit from this, but she wasn't really ready for that. :) Her concern is that there are 29000 extents in an index tablespace. This might have something to do with there being 3400 indexes in said tablespace. Total 'wasted' ( honeycomb ) space in this 250 gig DB is 20 meg. Not much to gain there. The text of the book states that you should expect a '10 to 20 percent performance increase' by reorganizing the tables/indexes. No data to back it up of course. This is on a database that performs very well most of the time, outside of a couple of custom reports that run too long. No complaints from users about slowness. Arrghhh! I just had to vent to the list, cuz there's no one here that understands. \RANT Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cary Millsap INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Misinformation Ranting
Oops, sorry for the mis-attribution, and my unintended slight on the author of 'Oracle Unleashed' Jared Cary Millsap [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 09/10/2002 02:23 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Misinformation Ranting I feel your pain. You got the publisher right, but it's not Oracle Unleashed. It's from p109 of the best-selling but utterly reprehensible Oracle Performance Tuning Tips Techniques book. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic, Oct 1-3 San Francisco, Oct 15-17 Dallas, Dec 9-11 Honolulu - 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12 Dallas - Next event: Miracle Database Forum, Sep 20-22 Middelfart Denmark -Original Message- [EMAIL PROTECTED] Sent: Tuesday, September 10, 2002 3:29 PM To: Multiple recipients of list ORACLE-L RANT I've just spent 30 minutes with our SAP administrator trying to convince her that we really don't need to reorganize the tables in our production SAP database. Due to some misinformation in an Oracle Press book, 'Oracle Unleashed' I think, she is equating number of extents with fragmentation. The text she referred me to is in fact discussing 'migrated rows' though that term is never used. She has become convinced that if the extents allocated for tables are not all in contigous space, some very nasty fragmentation will occur. I tried taking it down to disk and explaining that an OLTP system with hundreds of users won't really see much benefit from this, but she wasn't really ready for that. :) Her concern is that there are 29000 extents in an index tablespace. This might have something to do with there being 3400 indexes in said tablespace. Total 'wasted' ( honeycomb ) space in this 250 gig DB is 20 meg. Not much to gain there. The text of the book states that you should expect a '10 to 20 percent performance increase' by reorganizing the tables/indexes. No data to back it up of course. This is on a database that performs very well most of the time, outside of a couple of custom reports that run too long. No complaints from users about slowness. Arrghhh! I just had to vent to the list, cuz there's no one here that understands. \RANT Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cary Millsap INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Misinformation Ranting
Have you brought to that meeting some basic storage statistics? If she is a decision maker, the only thing to do is to provide solid and dry technical information. Expected outage time of SAP including. inka -Original Message- Sent: Tuesday, September 10, 2002 4:29 PM To: Multiple recipients of list ORACLE-L RANT I've just spent 30 minutes with our SAP administrator trying to convince her that we really don't need to reorganize the tables in our production SAP database. Due to some misinformation in an Oracle Press book, 'Oracle Unleashed' I think, she is equating number of extents with fragmentation. The text she referred me to is in fact discussing 'migrated rows' though that term is never used. She has become convinced that if the extents allocated for tables are not all in contigous space, some very nasty fragmentation will occur. I tried taking it down to disk and explaining that an OLTP system with hundreds of users won't really see much benefit from this, but she wasn't really ready for that. :) Her concern is that there are 29000 extents in an index tablespace. This might have something to do with there being 3400 indexes in said tablespace. Total 'wasted' ( honeycomb ) space in this 250 gig DB is 20 meg. Not much to gain there. The text of the book states that you should expect a '10 to 20 percent performance increase' by reorganizing the tables/indexes. No data to back it up of course. This is on a database that performs very well most of the time, outside of a couple of custom reports that run too long. No complaints from users about slowness. Arrghhh! I just had to vent to the list, cuz there's no one here that understands. \RANT Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Inka Bezdziecka INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
