RE: Intermedia Indexing
Hemant, The 8.1.6 docs (no change to this in the 8.1.7 addendum) say that only PDF 1.0, 1.1, and 1.2 formats are supported by the interMedia Text filters. We index plain text and HTML documents (about 3 million per month) and have had zero errors for the last two years. We're currently on 8.1.7.3.0 under Win2k. Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- [EMAIL PROTECTED] Sent: Friday, April 26, 2002 1:18 AM To: Multiple recipients of list ORACLE-L Hi, We have an Oracle iFS installation and have enabled Intermedia. Some documents fail indexing with Status 1 or Status 2 errors. The documents are PDF (pdf 1.3), Excel, World and HTM documents. I do have a TAR open with Support. Just would like to know from the field how successful is Intermedia in indexing documents ? Are there any gotchas ? Oracle8i 8.1.7.3 on Solaris for iFS9.0.1 Hemant K Chitale Principal DBA Chartered Semiconductor Manufacturing Ltd -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite 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: 9i new features, the saga continues?
Yes! Jack C. ApplewhiteDatabase Administrator/DeveloperOCP Oracle8 DBAiNetProfit, Inc.Austin, Texaswww.iNetProfit.com[EMAIL PROTECTED](512)327-9068 -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of JOE TESTASent: Thursday, April 25, 2002 8:14 AMTo: Multiple recipients of list ORACLE-LSubject: 9i new features, the saga continues? Ok life has slowed down some and i've got some free time to potentially pickup the 9i new features saga i did about 6 months ago. Anyone still interested? The next on the list was 9i data guard. joe
RE: DB_BLOCK_CHECKING and DB_BLOCK_CHECKSUM
Beth, Dennis, The 8.1.7 docs say that DB_Block_Checking costs 1%-10% in overhead - the 10% end for periods of intense insert/update activity - and to turn it on if you can afford it. DB_Block_Checksum costs 1%-2% and Oracle recommends turning it on always. We've had both turned on for months (340GB 8.1.7.3.0 DB under Win2k) and not noticed any significant performance hit. We insert and index (interMedia Text) up to 280,000 documents each night, so our insert/update load is not trivial. Having those two checks going on makes me feel more secure. We've never gotten so much as a warning, so we've not needed it. However, like any kind of insurance (e.g., backups), as long as it doesn't cost too much, aren't you glad to pay to leave it unused? ;-) Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- Beth Sent: Monday, April 22, 2002 3:25 PM To: Multiple recipients of list ORACLE-L Thanks Dennis. Its a paranoid Monday question. Actually I came across an Oracle document which suggested that they always be enabled. I was skeptical so decided to ask the real experts instead :-) -Original Message- Sent: Monday, April 22, 2002 4:03 PM To: Multiple recipients of list ORACLE-L Beth - Are you asking because you are experiencing a corruption problem, or because you're having a paranoid Monday? ;-) I believe the overhead is enough that you wouldn't turn them on just because. But if you are experiencing occasional corruption, you could tolerate quite a bit of overhead. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, April 22, 2002 2:20 PM To: Multiple recipients of list ORACLE-L Hi everybody, I'm soliciting opinions on whether or not its a good practice to enable DB_BLOCK_CHECKING and DB_BLOCK_CHECKSUM. How much overhead is associated? TIA, Beth -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite 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: Help needed
Rakesh, Yes. You can certainly export a single partition at a time for logical backups. If you put each partition in its own tablespace, you can physically back up individual partions as well. Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- banerjee Sent: Wednesday, April 03, 2002 8:18 AM To: Multiple recipients of list ORACLE-L HELP Hi, I am working with Oracle 8i version in our ERP applications. I have one question regarding the backup of Oracle database. In the database I am designing it is required to partition the database as per financial year.Almost all the tables will be partitioned accordingly. I want to know WHETHER IT IS POSSIBLE TO TAKE THE BACKUP OF INDIVIDUAL PARTITIONS (i.e. OF THE YEARWISE DATA). IF YES, THEN HOW IT CAN BE DONE. Waiting for your reply at the earliest, as my design will be dependent on your feedback. With Regards Rakesh Banerjee -- Author: rakesh banerjee INET: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite 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: Dynamic create and execute procedure
Cool! A PL/SQL procedure that responds to your every wish! I want one of those too! Seriously Gilbert, be a bit more specific about your desirements. Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 Bernard, Gilbert [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 30/03/2002 12:54 AM I need to create a generic dynamic procedure to create and execute any procedure I wish. === -- Author: Bernard, Gilbert INET: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite 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: How to calculate time using SQL
David, Select (DateTime1 - DateTime2) * 24 * 60 * 60 From Dual ; Oracle date arithmetic results are in fractional days, so the above gets you the number of seconds between two Date datatype arguments. Of course you can go ahead and multiply 24*60*60 to get Seconds/Day, but I prefer to leave it that way so it's more obvious what my intent is. Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- David M Sent: Monday, April 01, 2002 9:48 AM To: Multiple recipients of list ORACLE-L Is it possible to calculate seconds using SQL? For example, I'd like to subtract these two time to get difference in seconds: 10:20:32 - 10:25:29 Thanks, David -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite 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: LAST REBUILD INDEX
Seema, Probably Last_DDL_Time in DBA_Objects, though that just tells you the last time any DDL was executed on the object. Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- Sent: Monday, April 01, 2002 9:58 AM To: Multiple recipients of list ORACLE-L Hi WHich view show when was the last rebuild of indexes? Thx -Seema -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite 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: Rebuild Oracle Listener Service
Ron, The easiest way to do that is use the Oracle Net8 Configuration Assistant. It's under Programs/Oracle - OraHomeYour8iHome/Network Administration and a snap to use. Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- L. Sent: Monday, April 01, 2002 10:49 AM To: Multiple recipients of list ORACLE-L My oracle listener service is not working on NT. Can someone send me a script to rebuild the listener service? Thanks! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite 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: How to calculate time using SQL
David, SysDate is a function and can be referenced in any SQL statement. So you could do the following if you want seconds between the two, or leave off some or all of the trailing multiplicands to get fractional minutes, hours or days. Select ( SysDate - Next_Date ) * 24 * 60 * 60 From User_Refresh ; You need to do a little reading in the Oracle SQL Reference under Datatypes to find out details of the Date datatype and Date arithmetic. Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- David M Sent: Monday, April 01, 2002 11:42 AM To: Multiple recipients of list ORACLE-L Jack, How about substracting a system date with a database replication date to get difference of time? Can you please give me a specific command? SQL select to_char(sysdate, 'Dy Mon Dd HH24:MI:SS ') from dual; TO_CHAR(SYSDATE,'DYMONDD Mon Apr 01 11:08:00 2002== System Time. SQL select next_date from user_refresh; NEXT_DATE Mon Apr 01 11:11:01 2002 == Replication time. Thanks, David -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite 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: LAST REBUILD INDEX
Seema, What version are you on? For 8.1.7.3.0 under Win2k Last_DDL_Time in DBA_Objects *does* show when an Index last had an Alter Index xxx Rebuild executed against it. I just now tested to verify that. Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- Sent: Monday, April 01, 2002 11:42 AM To: Multiple recipients of list ORACLE-L Jack No! thx -Seema From: Jack C. Applewhite [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: LAST REBUILD INDEX Date: Mon, 01 Apr 2002 08:53:33 -0800 Seema, Probably Last_DDL_Time in DBA_Objects, though that just tells you the last time any DDL was executed on the object. Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- Sent: Monday, April 01, 2002 9:58 AM To: Multiple recipients of list ORACLE-L Hi WHich view show when was the last rebuild of indexes? Thx -Seema -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite 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: Do programmers tune SQL?
Dennis, I've had to keep after the Java developers here to use bind variables instead of literals. Apparently, it's much easier for them to construct literal SQL. They now understand the importance and use bind variable pretty much all the time. A few months ago they started spawning multiple threads of some Java processes and used a thingey called Connection Pooling to mediate connections to Oracle. They fell back to using literal SQL, until they figured out how to use bind variables with Connection Pooling. Apparently, it's even harder to use bind variables with Connection Pooling. If you make the Java developers aware of these issues up front, maybe they'll go ahead and learn how to code it right initially. Sorry I can't give details, 'cause all I know about Java is how to spell it! ;-) Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- WILLIAMS Sent: Monday, April 01, 2002 11:20 AM To: Multiple recipients of list ORACLE-L Oh, I guess I'm a little slow on Monday. I've enjoyed the discussion so far. Very close to my situation. Given my situation - i.e., we haven't had many SQL statement problems, but expecting to receive more with Java, I'm wondering how I can get ahead of the game. I have worked on a set of SQL statement recommendations, simple stuff like make sure screen queries use and index. I am considering creating a checklist form for SQL statements for the developers to use. Like include the listing from EXPLAIN PLAN. Does anyone have any thoughts on this approach? I'm not sure if the developers/management would go for it, but I thought it would be worth asking as a starting point. Everybody is new to Java and a little nervous, so they are probably more open to suggestions. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite 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: Long-running SQL
Cherie, At least the leading column in your index doesn't have an index-killing function on it in the Where clause - the NVL function on the other columns makes Oracle not use them for hitting the index. Since you're only interested in returning a single value, perhaps a stored function that returns that value and takes :b1 - :b9 as input arguments could be tuned to be faster. It could be used in the SQL Select. Check out the docs for the use of stored functions in SQL statements. Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- [EMAIL PROTECTED] Sent: Wednesday, March 27, 2002 8:33 AM To: Multiple recipients of list ORACLE-L We have a statement that I feel takes too long to run in a nightly data load. The table it runs against has 386,000 records. It runs for about 10 seconds on average. We're only loading about 50,000 records a night but this statement is running during the majority of the 9-hour load time. This is causing the load to run longer than our allowable window and causing me untold headaches. If anyone has any suggestions to make this run faster, I'd be greatly appreciative. The columns in the where statement are all part of an index. However, the functions on the columns add additional execution time and complexity. This is an 8.0.4 database so I can not make this a function-based index. I put this in a couple of SQL tuning tools and came up with no valid alternatives. I can't help thinking that the statement could be rewritten into a couple of statements so that it would be more efficient. However, I'm not skilled enough with SQL to do it. Perhaps someone else is. Here's the code. SELECT /*+ INDEX(EXP_COST_CENTER_DIM EXP_COST_CENTER_DIM_IDX1) + */ EXP_COST_CENTER_KEY FROM EXP_COST_CENTER_DIM WHERE ACCOUNT_NUMBER = :b1 AND NVL(ORG_LEVEL_1_VALUE,'NONE') = NVL(:b2,'NONE') AND NVL(ORG_LEVEL_2_VALUE,'NONE') = NVL(:b3,'NONE') AND NVL(ORG_LEVEL_3_VALUE,'NONE') = NVL(:b4,'NONE') AND NVL(ORG_LEVEL_4_VALUE,'NONE') = NVL(:b5,'NONE') AND NVL(ORG_LEVEL_5_VALUE,'NONE') = NVL(:b6,'NONE') AND NVL(ORG_LEVEL_6_VALUE,'NONE') = NVL(:b7,'NONE') AND NVL(ORG_LEVEL_7_VALUE,'NONE') = NVL(:b8,'NONE') AND NVL(ORG_LEVEL_8_VALUE,'NONE') = NVL(:b9,'NONE') AND ROWNUM = 1 SQL desc exp_cost_center_dim NameNull?Type --- EXP_COST_CENTER_KEY NOT NULL NUMBER(7) ACCOUNT_NUMBER NOT NULL NUMBER(9) BATCH_WINDOW_DATE_KEY NOT NULL NUMBER(5) ORG_LEVEL_1_VALUEVARCHAR2(20) ORG_LEVEL_2_VALUEVARCHAR2(20) ORG_LEVEL_3_VALUEVARCHAR2(20) ORG_LEVEL_4_VALUEVARCHAR2(20) ORG_LEVEL_5_VALUEVARCHAR2(20) ORG_LEVEL_6_VALUEVARCHAR2(20) ORG_LEVEL_7_VALUEVARCHAR2(20) ORG_LEVEL_8_VALUEVARCHAR2(20) DATA_SOURCE_MOD_DATETIMENOT NULL DATE DATA_WAREHOUSE_MOD_DATETIME NOT NULL DATE DATA_MART_MOD_DATETIME NOT NULL DATE SQL select column_name from dba_ind_columns where index_name ='EXP_COST_CENTER_D IM_IDX1'; COLUMN_NAME ACCOUNT_NUMBER ORG_LEVEL_1_VALUE ORG_LEVEL_2_VALUE ORG_LEVEL_3_VALUE ORG_LEVEL_4_VALUE ORG_LEVEL_5_VALUE ORG_LEVEL_6_VALUE ORG_LEVEL_7_VALUE ORG_LEVEL_8_VALUE SQL select column_name from dba_ind_columns where index_name ='EXP_COST_CENTER_D IM_PK'; COLUMN_NAME EXP_COST_CENTER_KEY Thanks, Cherie Machler Oracle DBA Gelco Information Network -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite 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: Check Constraint
Jay, Two possibilities: 1. Before you add the constraint, issue a Select query with the Where clause being the NOT version of your Check Constraint. This is the least hassle. 2. Use the Exceptions Into clause when you create the Check Constraint. This will populate an Exceptions table with the RowIDs of the rows that violate the Check. See the Constraint_Clause section of the SQL Reference for details. Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- Sent: Tuesday, March 26, 2002 3:15 PM To: Multiple recipients of list ORACLE-L When a check constraint is added to a table, is there an option in Oracle that will display or list the rows in the table that violate the constraint? TIA. Jay -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite 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: Automatic shutdown on NT does a shutdown abort
Peter, You don't even get a line in your Alert log like the following, which is what I always get immediately after I issue a Shutdown Immediate? ALTER DATABASE CLOSE NORMAL Perhaps 30sec. isn't enough time for your DB to do a Shutdown Immediate before the Oracle Service is stopped, which is equivalent to a Shutdown Abort? Try increasing ORA_sid_SHUTDOWN_TIMEOUT and see what happens. Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- Peter Sent: Monday, March 25, 2002 10:23 AM To: Multiple recipients of list ORACLE-L Environment: Oracle 8.1.7 NT 4.0 sp 6 Registry parameters: ORA_sid_SHUTDOWN = TRUE ORA_sid_SHUTDOWN_TYPE=i ORA_sid_SHUTDOWN_TIMEOUT=30 When I stop the OracleServicesid, the database appears to do a shutdown abort. No entries are made in the alert log to indicate that a clean shutdown occurred. When I restart the service, the alert log contains messages like Beginning crash recovery of 1 threads. Any ideas what I have missed? Thanks, Peter Schauss Northrop Grumman Corporation 516-346-3148 [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite 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: Automatic shutdown on NT does a shutdown abort
Jared, Thanks for the links. However, we should be wary since the document at the end of the first link states (near the bottom) 1000 microseconds make one second! Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- [EMAIL PROTECTED] Sent: Monday, March 25, 2002 2:14 PM To: Multiple recipients of list ORACLE-L Found the links for setting up Oracle to autostart/shutdown on NT. Jared http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_datab ase_id=NOTp_id=136214.1 http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_datab ase_id=FORp_id=82122.996 http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_datab ase_id=FORp_id=70988.996 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite 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: Windows NT and Virtual Memory
Raj, Oracle can only use 2GB of RAM for all its processes under NT or Win2k unless you're using NT Enterprise Edition or Win2k Advanced Server and you set an NT / Win2k parameter to allow it to use up to 3GB or (yet another OS setup) 4GB and more. Check out the Windows-specific docs for info. and references to papers on the MS site. I went through that investigation a while back, but the exact details have left my brain cell. ;-) Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- [EMAIL PROTECTED] Sent: Friday, March 22, 2002 9:59 AM To: Multiple recipients of list ORACLE-L Operating System: Windows NT 4.0 with SP 6a Oracle Version : 8.1.7 We recently migrated an Oracle database from one Windoze NT box to another. The NT administrators claim that the NT boxes are indentical in all respects, except that the old one had Service Pack 4. We have recreated the database on the new box with exactly indentical initialization paremeters, file sizes, extent sizes, et all. The database gleefully accepts all connections upto about 220 users (V$license, v$session). When the 221st user tries to logon, the TNS-12500: Unable to start a dedicated server process is thrown. After all the troubleshooting (Connect timeouts, Disable otracing, tracing client), we discovered that as soon as the Virtual memory comes approaches 1.7Gb, this problem starts happening. I read articles on Metalink which suggested that this is a limitation with Windows NT, and suggests some recommendation to delay this problem. So, we recommended the same to business. But now they wish to know why these errors never occured in the old NT server. And to further worsen matters, the old server has been cleaned up. So, I have no way of going back and Checking. Has anyone else on this list encountered the same problem? Possible workarounds, if any? Thanks Raj -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite 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[2]: Fav. Urban Legend...Mem vs Disk
A most enjoyable book Venus on the Half Shell, written by Philip Jose Farmer under the pseudonym Kilgore Trout, who was a character in many novels - particularly Breakfast of Champions - by Kurt Vonnegut, who was a good friend of Mr. Farmer - also the author of the Riverworld trilogy (quadrilogy) and others. Oh, the connections! But the REAL purpose of this post (to keep it On Topic) is to report that my problem with excessive SNPx memory use turned out to be an artifact of upgrading from 8.1.6.0.0 - in which the interMedia Text indexing functions were handled by ExtProc - to 8.1.7.2.5 - in which the iM Text functions are incorporated into the RDBMS kernel. In 8.1.6 the 200MB used during index resyncs was released by ExtProc when the process finished. In 8.1.7 the SNP job runs the resync process and the 200MB used is not released. Periodically stopping and restarting the SNPx processes releases that memory and is an OK workaround. Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- Eskridge Sent: Wednesday, March 20, 2002 9:33 PM To: Multiple recipients of list ORACLE-L ... Hmmph. More kowtowing to Douglas Adam's cheap rip off on Kilgore Trout's epic, Venus on the Half Shell. Check the name of the FTL drive in the latter and compare it to The Question. Curious though, how the answer is just one more than the maximum ITL slots with 2k blocks... (he says in a desperate attempt to get back on topic) -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite 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: Standby Database Problem
Title: Standby Database Problem Hussain, As long as none of your datafiles were corrupted by what I assume was the crash of your Standby Instance, you can reinstall 8i and bring up the Standby just as you describe. The datafiles don't care at all where Oracle Home is. Irecently had to reconfigure our Production and Standby database servers to add a global hot spare drive to each. Because of that I had to reinstall 8i and moved Oracle Homeon both of them. However, 90% of the datafiles stayed just like they were - a few had to move to a new drive. Both DBs came up just fine and I'm now back in Managed Standby mode. Of course your Listener.ora will have to reference the new Oracle Home, but since you're reinstalling 8i, I'm guessing you'll create a Listener from scratch anyway. Jack Jack C. ApplewhiteDatabase Administrator/DeveloperOCP Oracle8 DBAiNetProfit, Inc.Austin, Texaswww.iNetProfit.com[EMAIL PROTECTED](512)327-9068 -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Hussain Ahmed QadriSent: Tuesday, March 19, 2002 12:13 AMTo: Multiple recipients of list ORACLE-LSubject: Standby Database Problem Hi While trying to install developer6 on our standby database test server (OS NT4, Oracle 8.1.7), I have some how corrupted the 8i, and hence the standby database is not working. Its actually the Oracle Suite tools which are not working. I am going to configure it again, that is install the Oracle8i again. But what I want to know is that do I have to copy the datafiles from the main server again and recreate the control file. Or just reinstall the oracle database, copy the archive redologs, which are missing, apply them and then mount the standby database in recover managed mode with the existing Datafile copies? The reason I am asking this is that the configuration of database is not changed nor the database has been brought from standby to active mode, just registry entries have been changed/deleted becuase of selecting a different home for Developer6 (Thats another problem that after installing Oracle 8i, when u want to install Dev6,it doesn't allow installation in the same Oracle home and neither in a separate home, but if you try it again in a separate home, like I did, it removes the entries of first Oracle home, so no Oracle 8i! ) So in such case WHAT happens to the STAND BY Database??? Looking forward to your replies Regards, Hussain Ahmed Qadri Database Administrator Shaukat Khanum Memorial Cancer Hospital Research Centre [EMAIL PROTECTED] www.shaukatkhanum.org.pk
RE: How to register an package in oracle Database!!
Ayyappan, You can use the procedures in DBMS_APPLICATION_INFO to publish and interrogate the states and actions of modules. Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- [EMAIL PROTECTED] Sent: Tuesday, March 19, 2002 3:43 AM To: Multiple recipients of list ORACLE-L Hi I am having one package which I have created. If I an executing that package on one session and in another session I want to know the Process of the package is running with the package name. How to Know that?. Regards Ayyappan.S -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite 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: what pl/sql construct can return multiple rows?
Bill, A PL/SQL Table of MyTable%RowType can be returned from a function or as an Out Argument from a procedure. A Ref Cursor (Cursor Variable) can also be returned as an Out Argument from a procedure. See the PL/SQL Users Guide and Reference for info. on these. Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- Bill Sent: Tuesday, March 19, 2002 7:58 AM To: Multiple recipients of list ORACLE-L Hi, Is there a way to write a procedure to return multiple rows? I have some nasty SQL that I'd like to convert to run server-side, but how do you spit out multiple rows from PL/SQL? thx Bill Magaliff Framework, Inc. 914-631-2322 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Locally Managed Tablespaces
Antonio, We use LMTs for all tablespaces - except for System, of course. No issues, problems, etc. We're happy. 8.1.7.2.5 on Win2k; ~340GB in 29 tablespaces. Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- [EMAIL PROTECTED] Sent: Monday, March 18, 2002 7:28 AM To: Multiple recipients of list ORACLE-L Hi, Anyone using LMT for rollback segments ? Any issues , suggestions , ... ? TIA, Antonio Belloni -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite 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: calling program
Big Planet, Whoa, now there's a can of worms! In a word - no. Not unless you pass that info. in as an argument. Think about what you're asking. Since PL/SQL procedures can be called from, not only other PL/SQL procedures and functions, but the SQL*Plus command line, ODBC calls, Java programs, etc. etc., what would be the identification mechanism? Also, since PL/SQL functions can be embedded in SQL statements, there's yet another problem - how does a SQL statementknow/report what program is using it? The overhead to give you what you're asking would, IMHO, be toohighif provided by the PL/SQL engine. Jack Jack C. ApplewhiteDatabase Administrator/DeveloperOCP Oracle8 DBAiNetProfit, Inc.Austin, Texaswww.iNetProfit.com[EMAIL PROTECTED](512)327-9068 -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Big PlanetSent: Friday, March 15, 2002 2:18 PMTo: Multiple recipients of list ORACLE-LSubject: calling program Hi LIst , Is there a way a pl/sql procedureor function can know the calling procedure or calling program . -ak
RE: calling program
Ouch! Looks like I need to RTFM, not only before asking a question, but before answering one - shoot from the hip = shot in the foot. 8-( Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- Rajendra Sent: Friday, March 15, 2002 3:39 PM To: Multiple recipients of list ORACLE-L Actually you can, dbms_utility.format_call_stack, but you'll have to parse the information. See http://osi.oracle.com/~tkyte/who_called_me/index.html 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! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite 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: Identification of tables NOT being used in the System.
Dharminder, Instead of the overhead of auditing, how about periodic queries of V$SQL_Text for SQL statements that reference those tables. It should be pretty easy to come up with occurrences and counts for each table, though statements using bind variables could cause low counts for the tables referenced by them. Heck, you might flush the Shared Pool occasionally and requery V$SQL_Text just to see which tables are the hottest - most frequently referenced. That may not be a good idea on a Production DB, though - depends on your application load. If you bounce your database regularly (for cold backups, for instance), do it right before shutdown. It's just a quick thought...which I probably absorbed from someone else's posting on this very valuable list. ;-) If so, then my thanks to the originator. Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- Dharminder Sent: Friday, March 15, 2002 3:08 PM To: Multiple recipients of list ORACLE-L In our production database environment, I have a list of about 1000 tables ,for which we want to find if these tables are being used by anyone. How it can be done. One of the ideas is that we start database auditing on these tables for a considerable period of time say one month. Then for those tables for which there is nothing in database audit, we assume that tables are not being used. For this option I would like to know if we put auditing on these 1000 tables, how much extra burden it is add onto the system (CPU, Memory etc). We are using Oracle 8.1.6 on HP-UX 11.00. If there are some other alternatives, please let me know. Thanks. Dharminder Kumar -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Locally managed ts
Ayyapps, Use the DBMS_SPACE_ADMIN supplied PL/SQL package. Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- [EMAIL PROTECTED] Sent: Friday, March 08, 2002 4:03 AM To: Multiple recipients of list ORACLE-L Hi all Can we able to change the dictionary managed tablespace to locally managed tablespace. if so how? Ayyapps -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Oracle Indexing
Sinardy, 5 DB blocks is the default for INITIAL and NEXT extents, if you don't specify them, not necessarily the recommended size. The extent size of any segment depends more on the size of the segment, but should always be an integer multiple of db_file_multiblock_read_count. The best recommendation is to have one or a few standard extent sizes in locally-managed tablespaces. Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- Sent: Friday, March 08, 2002 1:48 AM To: Multiple recipients of list ORACLE-L Hi all, Oracle said: The best extent size of an index to minimize fragmetation is 5 times of db block size. My question is why 5 times is the recommended size, why not 4 times or 6 times or perhaps 0.5 of your db block size. Thanks Sinardy -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite 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: Primary keys
Harvinder, Every table needs a PK. Otherwise, you have no way of identifying a row uniquely. You also need an index on each FK, to prevent locking problems when updating/deleting the parent table. You can kill two birds with one stone by making your history tables' PKs (enforced by unique indexes) be a concatenation of the FK column(s) - make it(them) the leading column(s) - with at least one more column that produces a unique ID for each row. Then you've got both a PK and an indexed FK and everyone is happy! ...except maybe your duhvelopers, who are obviously not all that knowledgeable, so who cares if they're happy?!?! ;-) Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- Singh Sent: Thursday, March 07, 2002 11:13 AM To: Multiple recipients of list ORACLE-L HI, We have some history tables that are pointing to parent tables. Parent tables has primary key. Our developers are saying that we have foreign key from history table to parent table and we don't need primary key on history table. Does there are any benefits if we have primary key(concatenated) on history table. OR do we should leave history tables without primary key Thanks --Harvinder -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Harvinder Singh INET: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite 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: Restrictng tables during export/import
Harvinder, IMHO, you are using the wrong approach. FGAC is meant for row-level security. In fact, DBMS_RLS (RLS = Row-Level Security) is the package that supports FGAC. It's a lot easier to use the TABLES parameter in your export and import parameter files to control which tables are exported/imported. Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- Singh Sent: Thursday, March 07, 2002 10:18 AM To: Multiple recipients of list ORACLE-L We are testing Fine-Grained Access Control to export the complete schema excluding some tables. This works well for export but during import it try to import all tables. Is there any way we can restrict import of some tables using Fine-Grained Access Control .We are using function as specified in metalink as follows: CREATE or REPLACE FUNCTION exclude_table (obj_schema VARCHAR2, obj_name VARCHAR2) RETURN VARCHAR2 IS d_predicate VARCHAR2(2000); BEGIN if sys_context ('USERENV', 'SESSION_USER') = 'EXP_DB' THEN d_predicate := '1=2'; else d_predicate := ''; end if; RETURN d_predicate; END exclude_table; Thanks --Harvinder -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Harvinder Singh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite 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: Hundreds of schemas in one instance?
Ben, Have you investigated Oracle8i's Virtual Private Database feature - A.K.A. Fine-Grained Access Control (FGAC) or Row-Level Security (DBMS_RLS). It's available in 8i EE. It keeps users logically separate, even if you only use one schema. I believe it would be much easier to maintain than hundreds of schemas - adding/deleting rows is much easier than adding/dropping schemas and their objects. I implemented FGAC in an internationally-accessed Web-based application in which each customer's data was kept separate from every others, but in a single schema. Works very well. Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- Sent: Wednesday, March 06, 2002 1:29 PM To: Multiple recipients of list ORACLE-L Hi Our university wants to set up a server that will provide groups on campus with a standard set of services for web hosting, data collection or whatever they want to do. As much as possible each user should have their own isolated chunk of the server. An Oracle database will sit in the background to provide whatever database services they need. My thought is to go with one instance with a unique schema, including separate tablespaces/datafiles, for each user. Some of the pros for this are: - easy set up for new users - easy software upgrades - simplified tuning, backups, monitoring, auditing - user isolation, especially disk space usage - multiple instances would use far more memory Some cons are: - everyone must use the same release of the software - database down time affects everyone - might run into system maximums, for example max. number of datafiles - an enormous SYSTEM tablespace Has anyone had to this kind of thing? Any comments or suggestions? TIA, Ben == Ben Poels - Senior Technical Analyst - Queen's University at Kingston Phone: 613.533.2449 Fax: 613.533.2168 Email: [EMAIL PROTECTED] == -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite 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: EXTENTS?
Yes, this one will get a lot of traffic, I'll bet. I just looked in the 8.1.7 docs (SQL Reference - Storage Clause) and it says that the minimum INITIAL is 2 DB blocks for non-bitmapped segments, 3 for bitmapped segments. The minimum for NEXT is 1 DB block. Unlesss it's a new 9i feature, a DB block can belong to one and only one Extent. Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- [EMAIL PROTECTED] Sent: Monday, March 04, 2002 3:13 PM To: Multiple recipients of list ORACLE-L I am confused. Are you talking of multiple extents in a block? Throws my fundamentals topsyturvy. Raj -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite 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: Which FM???
Rodd, Oracle8i Reference Jack Jack C. ApplewhiteDatabase Administrator/DeveloperOCP Oracle8 DBAiNetProfit, Inc.Austin, Texaswww.iNetProfit.com[EMAIL PROTECTED](512)327-9068 -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Rodd HolmanSent: Friday, March 01, 2002 12:53 PMTo: Multiple recipients of list ORACLE-LSubject: Which FM???I'm looking for descriptions of the more cryptic columns in some of the V$'s. Anyone know which FM those are in? TIA Rodd
RE: SNP0...SNPx Memory Hogs
Ruth, I'm sure they're Oracle Job Queue processes because the lines below show up in my Alert.Log when I stop and start them by issuing the commands in the first two lines. That's when up to 500MB of memory is freed by the Oracle Process. BTW, SNMP stands for Simple Network Management Protocol (I'm pretty sure, at least). It's a TCP-related thingey - a technical term ;-). That's totally different (again, I'm pretty sure) from the Oracle SNP Job Queue processes. From Alert.Log: -- ... ALTER SYSTEM SET job_queue_processes=0; ALTER SYSTEM SET job_queue_processes=4; Thu Feb 28 07:01:19 2002 Restarting dead background process SNP0 SNP0 started with pid=8 Thu Feb 28 07:01:19 2002 Restarting dead background process SNP1 SNP1 started with pid=9 Thu Feb 28 07:01:19 2002 Restarting dead background process SNP2 SNP2 started with pid=10 Thu Feb 28 07:01:20 2002 Restarting dead background process SNP3 SNP3 started with pid=11 ... --- Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- Gramolini Sent: Thursday, February 28, 2002 8:53 AM To: Multiple recipients of list ORACLE-L At least in 8.0.x the oracle intelligent agent process are dbsnmp processes. Are you sure that these are oracle processes and not OS related. Ruth - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, February 27, 2002 4:20 PM 8.1.7.2.5 under Win2k Server I just discovered that my four SNP processes were sitting around, doing no work, taking up about 500MB of RAM. Is this normal? ... -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite 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: poor query performance
Bill, Try this: Delete From T1 Where F1 In ( Select F1 From T1 Minus Select PK From T2 ); This will delete T1 rows for which there's no matching PK in T2 and will most likely be quicker than the Not Exists query, though you never can say for sure! ;-) Oh, and the Cost that Explain Plan reports is purely relative and can't be used for comparison across different queries. Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- Bill Sent: Thursday, February 28, 2002 3:24 PM To: Multiple recipients of list ORACLE-L I have a query that deletes rows from a table with 57K rows, as follows delete from T1 where not exists (select T2.PK from T2 where T2.PK = T1.F1); T2.PK is the Primary Key on Table T2 T1.F1 is an indexed field on table T1 Explain plan shows a low cost (80) but the delete takes about 10+ minutes. T1 has about 57K rows T2 has about 29500 rows other queries with costs in the 700 range are pretty quick - this one's a dog any ideas? thanks Bill Magaliff Framework, Inc. 914-631-2322 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite 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).
SNP0...SNPx Memory Hogs
8.1.7.2.5 under Win2k Server I just discovered that my four SNP processes were sitting around, doing no work, taking up about 500MB of RAM. Is this normal? In Task Manager, the Oracle process was showing to be using about 1.4GB. After I issued Alter System Set Job_Queue_Processes=0 it shrank to about 900MB. I then issued Alter System Set Job_Queue_Processes=4, but the Mem Usage stayed the same. None of those SNP processes had done any work (i.e., run any jobs) for about 10 hours. I would have expected the SNP processes to release memory when their jobs finish. I've been trying to figure out what processes were chewing up RAM, ramping up over the course of 3 or 4 days. Some Java processes that we run were found to be hogs, but after I got the developers to disconnect/reconnect occasionally, those sessions were OK. I finally found the culprits when I was checking sessions' session pga memory. A couple of sessions were using about 200MB each. I checked to see what SQL they'd been running and found it to be dbms_ijob calls, which lead me to suspect the SNP processes - alas, Win2k doesn't let you look at individual Oracle processes like UNIX does. Anyway, I can't find any mention anywhere that dormant SNP processes can be memory hogs. Am I missing something? For now I'll set Job_Queue_Processes to 0 and back to 4 right after the heavy work each night. Any init parameters I'm missing? ...other suggestions? Thanks. Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite 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: Global variable in Pl/SQL
Andrey, You can't make a PL/SQL variable value visible to all sessions. You'll have to put the value in a row in a table and commit it. Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- Sent: Tuesday, February 26, 2002 2:44 PM To: Multiple recipients of list ORACLE-L Dear list ! How can i make a package variable visible to all sessions , please ? I.e. i have a package PPP that has a package variable VVV. There is a stored proc in the package , PPP.SP1 In this procedure i set the package variable VVV to some value. Now , i want all the sessions connected to the instance to be able to see this value of PPP.VVV . Thanks a lot in advance ! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite 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: datafile compression
Keith, I've been using PKZip (version 4.00) under Win2k to zip and unzip Oracle export dump files for months without a hitch. In fact, I just now zipped and unzipped a 400MB 8i datafile with no errors. Are you sure there wasn't some other factor that affected the test you did? Do you have the latest version of PKZip? Command line PKZip would be my first choice. Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- Worley Sent: Thursday, February 07, 2002 5:23 PM To: Multiple recipients of list ORACLE-L Hello everyone. Let me say first to all who are on the list group THANK YOU. From reading the emails I receive I have learned a LOT the past few weeks. My question here is. I am trying to find a way to compress the datafiles when I copy them to a backup folder on NT. Does anyone have a suggestion as to what product they use to compress datafiles? Has anyone ever used the COMPRESS option on a folder in NT to compress datafiles? If so, was there ever a problem. I did try pkzip and when the files unzipped it gave errors abour the crc checks. I also researched this and didn't find much about compressing datafiles BUT i'm not that good with the oracle doc's yet either. Thanks in advance for any responses to this email and everyone have a good day. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite 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: Where does a DBA go from here?
Rich, The best Oracle training I've ever had by far is playing around with a small test DB on a laptop or PC. Stress it, hurt it, recover it, tune it, etc., etc. When done in conjunction with one of the fine books mentioned by others on this list - even better. I've only had one official Oracle training course - in the spring of 1989. It was 4-1/2 days of RDBMS v5, SQL*Forms and RPT/RPF. Since then I've attended some of the mini courses at IOUG, ODTUG, Open World, etc. - nice little hits of the latest technologies. I studied for my Oracle8 OCP exams by working through Jason Couchman's book and beating up an 8.0.5 DB on my PC - learned a hell of a lot. My on-going study plan for Oracle skills boils down to Beating, Browsing and Books. - Beat up a test database - Browse fine Web sites like Steve Adams' IXORA (and beat up the DB) - Buy and read good books (and beat up the DB) I think I've learned about as much as if I'd sat in a bunch of classes - and saved a pile of money. ;-) Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- Sent: 07 February 2002 21:07 To: Multiple recipients of list ORACLE-L So, there I am. I've taken the main Oracle courses -- Intro to SQL, DBA (Oracle 7!), Backup Recovery, Network Admin, and Perf Tuning. Now where do I go for more Oracle training? This is sparked by a recent perceived lag in one of our new databases. We've tracked it down to a possible hot block or two, but I never used X$BH or V$LATCH_CHILDREN in any of my Oracle classes. And I *know* I'm far from being ready for an Internals class. So how do I get from here to there? education.oracle.com doesn't seem to have a whole lot other than Internals. Or is that where I'm at now? Confused and no beer. Rich JesseSystem/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite 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: capacity planning??
Shibu, Go to http://www.orapub.com Craig Shallahamer is THE Guru on the subject. I took his course a couple years ago - excellent! Jack Jack C. ApplewhiteDatabase Administrator/DeveloperOCP Oracle8 DBAiNetProfit, Inc.Austin, Texaswww.iNetProfit.com[EMAIL PROTECTED](512)327-9068 -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of ShibuSent: Friday, February 08, 2002 12:13 AMTo: Multiple recipients of list ORACLE-LSubject: capacity planning?? Hi all Can anyone send me a doc or white paper on capcity planning?? regards, shibu
RE: Removing data form a table
Lance, Truncate Table YourTableHere ; This deletes all the rows in the table without the cost of rollback. Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- Sent: Friday, February 08, 2002 3:25 PM To: Multiple recipients of list ORACLE-L I want to create a procedure the drops all the data from a table. I was trying to drop table then create this proved to be extremely complicated in Oracle. I think the above route is much better. Does anyone know how to do this? Lance -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Oracle and Document Management: any real stories?
To add to what Marin suggests... Oracle's interMedia Text option supports full text indexing and a powerful set of full text query operators. We use Oracle8i's iM Text to index and query about 15 million plain text and HTML documents. However, to get the docs in and out and create a user-friendly interface for the query operators, you need N programmers (and DBAs) writing M lines of code - in our case Cold Fusion, Java, and PL/SQL. Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- Dimitrov Sent: Thursday, February 07, 2002 12:07 PM To: Multiple recipients of list ORACLE-L - Original Message - TOPIC: Oracle and Document Management: any real stories or information? Does anyone have any experience with Oracle and Document Management that they're willing to share? [I'm cross-posting to Oracle Apps and Oracle Server.] check Oracle Internet File System - it provides check-in/check-out and versioning functionality, access control, several interfaces to access the content http://technet.oracle.com/products/ifs/content.html hth, Marin -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite 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: 8.1.7.2.5 interMedia Text Slow Sync - SOLVED
For what seems to be the few of you who use interMedia Text, I found the problem - it was ME. :-( I had set (in CTX_Parameters) Max_Index_Memory to 200MB (from the default of 12.5MB) and used 200MB when I created indexes, but had overlooked Default_Index_Memory (default of 12.5MB), which is used by CTX_DDL.Sync_Index. After I set Default_Index_Memory to 200MB, re-syncs jumped from about 750 CLOBs per minute to over 2,500 per minute. Oracle was able to sort an entire batch of 157,000 CLOBs in memory, instead of only about 8,000 at a time - surprise, surprise with 16 times the memory available. BTW, it didn't hurt that I spread the datafiles of the tablespaces that hold the DR$$X segments across 2 drives, instead of just 1. That boosted re-syncs from 600 CLOBs per minute to 750. I/O distribution never hurts. Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- Applewhite Sent: Thursday, January 24, 2002 9:35 AM To: Multiple recipients of list ORACLE-L We are experiencing very slow interMedia Text resyncs ( CTX_DDL.Sync_Index ) under 8.1.7.2.5 on Win2k Server. We upgraded from 8.1.6.0.0 to 8.1.7.2.5 earlier this month (both under Win2k Server) on essentially the same hardware. We used to see about 700-2000 CLOB documents per second indexed under 8.1.6, but now see only about 400-800. The low-high numbers relate to the size of the index being sync'd - low just before we roll out a monthly partition (total 2 million CLOBs), high just after (total 1 million CLOBs). Query performance is better under 8.1.7 - partly because I spread the DR$$I table across 3 drives. By the looks of the I/O pattern, I should spread DR$$X (the index on DR$$I table) across 2 or 3 drives as well. Anybody have any words of wisdom as to what I can do to speed things up? BTW, I'm curious at how 8.1.7 balances I/O. While it's writing to the DR$$X segment, it doesn't write to the online redo logs - it waits until it's reading from DR$$X segment before it writes to the redo logs. I wonder why it doesn't do the writes to redo in parallel with the writes to DR$$X. Thanks. Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite 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: Where to store BLOB's
Yechiel Adar, We use option #2 (LOB segments in another tablespace). It allows faster queries of the non-LOB data in the main table and gives us flexibility as to storage parameters for the LOB segment(s). We also use interMedia Text to index the LOB (CLOB, in our case) column and those index segments are in yet another tablespace. BTW, we have about 15 million CLOB documents, so we're not talking a tiny amount of data here. Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- Sent: Tuesday, January 29, 2002 10:56 AM To: Multiple recipients of list ORACLE-L Hello all We are designing a new application. This application stores files that were FTP to the clients and keep then for resubmitting (if the user ask for them). There are two formats: 1) Standard records whose internal format is known. These files lets the user ask for a subset of the records, i.e. all the records for branch that were submitted in the last week, etc. . 2) Strange files that are moved as a file. Here the application is not aware of the internal format, i.e. excel files, and these files are going to be stored as Blob's and the user can ask only for the whole file again. I know that you can store Blob's in three ways: (already rtfm a little) 1) In the record (up to 4k), more goes to another tablespace. 2) In Oracle but in another tablespace (always). 3) As external files. I would like to learn from your experience what is the best, easiest to implement, easiest to admin, less demanding on Oracle and all the good stuff. TIA Yechiel Adar, Mehish Computer Services [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite 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).
8.1.7.2.5 interMedia Text Slow Sync
We are experiencing very slow interMedia Text resyncs ( CTX_DDL.Sync_Index ) under 8.1.7.2.5 on Win2k Server. We upgraded from 8.1.6.0.0 to 8.1.7.2.5 earlier this month (both under Win2k Server) on essentially the same hardware. We used to see about 700-2000 CLOB documents per second indexed under 8.1.6, but now see only about 400-800. The low-high numbers relate to the size of the index being sync'd - low just before we roll out a monthly partition (total 2 million CLOBs), high just after (total 1 million CLOBs). Query performance is better under 8.1.7 - partly because I spread the DR$$I table across 3 drives. By the looks of the I/O pattern, I should spread DR$$X (the index on DR$$I table) across 2 or 3 drives as well. Anybody have any words of wisdom as to what I can do to speed things up? BTW, I'm curious at how 8.1.7 balances I/O. While it's writing to the DR$$X segment, it doesn't write to the online redo logs - it waits until it's reading from DR$$X segment before it writes to the redo logs. I wonder why it doesn't do the writes to redo in parallel with the writes to DR$$X. Thanks. Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite 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: Searching Inside CLOBs
Sundeep, You couldn't have made a better case for using interMedia Text index(es) on your CLOB column(s) if you'd had help from Oracle Marketing! ;-) interMedia Text is very powerful and efficient. We use it to index and search almost 15 million CLOB documents, many in HTML format. interMedia even supports section searching, so you can search only between selected tags within a CLOB. Check out the interMedia Text docs. It's even available in Standard Edition. You (and your users) will love it. Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- maini Sent: Monday, January 21, 2002 3:06 PM To: Multiple recipients of list ORACLE-L Our developers store XML docuements inside CLOBS and want to search the CLOB contents based on a search string. I have lot of concern about such queries but could benefit from someone on the list who has already solved the performnce issues with such searches. Typical existing queries I have come across are: SELECT columns FROM list of tables WHERE join conditions AND UPPER(tab1.topic) LIKE '%:in_string%' OR UPPER(tab2.subtopic) LIKE '%:in_string%' OR . OR dbms_lob.instr(bodytext,:in_string,1,1) 0; Note: in_string is always upper case but the column contents are not. Concerns: 1. Poor index selection due to UPPER 2. CLOB searches are wrong as the CLOB contents are case sensitive. One way to remedy this would be to build a user defined function which will read the CLOB col. in chunks and look for string matches inside individual chunks till a success or end of CLOB. Either way that is a pretty intense way to search for a string among table data when a user is waiting for a quick response. Keyword searches are supported differently. This mechanism is only for random string searches. Any cues or suggestions? Especially for case sensitive searches inside CLOBS? TIA Sundeep = Sundeep Maini Consultant Currently on Assignement at Marshfield Clinic WI [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite 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: multiple extents are OK, dagnabbit!
Title: Message Jerry, If they want to pay you to reduce their extents, then let 'em! ;-) "A fool and his money are soon parted." If they employ youand want you to work weekends on this, then it's worth the effort to educate them. I'm surprised an official Oracle white paper didn't convince them. You may just be out of luck - adamant, entrenched misinformation is sometimes difficult to dislodge. If my anecdotal situation could be of any help, here it is. We just moved our production 8.1.6.0.0 database to 8.1.7.2.5 on a new, but almost identical server. Old server's OS was Windows 2000 Server with Service Pack 2 - new server, the same. Old server had dual 550MHz Xeon CPUs - new server, the same. Old server had 2GB RAM - new server has 4GB RAM (of which Oracle can only use 2GB anyway). Old server had eighteen 36GB drives - new server has twenty 36GB drives. In both cases configured as JBOD (Just a Bunch Of Drives - no RAID, no mirroring, no striping of any kind). Our 6 documents tableseach had (andhas) its own drive andeachhad (and has) about 2 million rows. The out-of-line CLOB documents take up about 20-30GB for each table. Each of those segments had between 20,000 and 30,000 1MB extents. For the year we operated that way, we never had a problem with performance, even with a full interMedia Text index on the CLOB column. When we moved theDBto 8.1.7.2.5, I pre-created those tables with 100MB extents for the CLOB segments before I imported the documents. So, now we're down to a few hundred extents per segment, instead of tens of thousands. It hasn't made any noticeable difference on performance. If numbers of extents really mattered, a 100 to 1 reduction would have made an impact - it didn't. Whatdid make a difference was spreading the main token table (DR$...$I) of the interMedia Text index across 3 drives, instead of one. Distributing I/O has significant impact. Number of extents per segment has close to zero impact. The Oracle white paper is dead-on accurate. Hope my experience helps convince your boneheaded clients. ;-) Jack Jack C. ApplewhiteDatabase Administrator/DeveloperOCP Oracle8 DBAiNetProfit, Inc.Austin, Texaswww.iNetProfit.com[EMAIL PROTECTED](512)327-9068 -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Cunningham, GeraldSent: Thursday, January 17, 2002 3:46 PMTo: Multiple recipients of list ORACLE-LSubject: multiple extents are OK, dagnabbit! Hi there - I'm trying to convince a client that multiple extents for a table will not hurt their performance. It's a PeopleSoft app, and PeopleSoft is telling them that they need to reorg any object with greater than 10 extents (even indexes). This Oracle 8.1.6. I've referenced the "How to Stop Defragmenting and Start Living: The Definitive Word on Fragmentation" white paper by Bhaskar Himatsingka and Juan Loaiza of Oracle. That didn't convince them. I tried to explain that Oracle reads BUFFERS and not extents, etc., but that didn't work. I'm about to open a vein. Does anybody have any references that they can point me to? (Something from PeopleSoft would be ideal, though I would be suprised if it existed.) I read a rant on somebody's web site a while back that was really good, but alas I cannot remember his name or URL. (I blame my kids for my failing memory). Thanks! - Jerry
RE: Partitions
The latter. Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- Sent: Wednesday, January 16, 2002 3:07 PM To: Multiple recipients of list ORACLE-L List, If we use partitioning for a big tables after finish development, do we have to change the code for accessing diffrent partition or this will handle by oracle itself. Thanks Hamid Alavi Office 818 737-0526 Cell818 402-1987 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite 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: Partitions
You asked which of two outcomes will occur if you partiton tables after development: 1. ...do we have to change the code for accessing diffrent partition... or 2. ...this will handle by oracle itself. Of your two proposed outcomes, 1. is the former and 2. is the latter. Oracle will handle access to table partitions automatically - your code does not have to be modified. I was attempting to answer your question in a succinct manner - sorry for not being clear enough. Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- Sent: Wednesday, January 16, 2002 4:25 PM To: Multiple recipients of list ORACLE-L what do you mean the latter Jack -Original Message- Sent: Wednesday, January 16, 2002 2:00 PM To: Multiple recipients of list ORACLE-L The latter. Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- Sent: Wednesday, January 16, 2002 3:07 PM To: Multiple recipients of list ORACLE-L List, If we use partitioning for a big tables after finish development, do we have to change the code for accessing diffrent partition or this will handle by oracle itself. Thanks Hamid Alavi Office 818 737-0526 Cell818 402-1987 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite 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: Recover Primary DB from Standby DB
Jeremiah, Thanks for the info. It's nice not to be an idiot! ;-) ...and so much easier to copy up-to-date datafiles from the Standby than to restore from tape backup in the event of a disk failure on the Primary. We'll use our Standby only in the event of a failure more catastrophic than a disk or two. BTW, I finally found mention in the docs that Oracle sanctions using datafiles from the Standby to recover the Primary database. It's the very last bullet point in the section Additions to Compatibility and Operational Requirements in chapter 12 (Standby Database) of Oracle8i Documentation Addendum Release 3 (8.1.7). It's not mentioned in the Compatibility and Operational Requirements in chapter 1 (Standby Database Concepts) in the 8.1.6 Standby docs. --- You can use standby database datafiles to recover a primary database only on Oracle release 8.0.4 or higher. --- Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- Wilton Sent: Friday, January 04, 2002 2:10 PM To: Multiple recipients of list ORACLE-L There is no problem with using the standby as a source of good files in the event a file on the primary becomes media corrupt. Once stopped, you can use a standby just like a good backup of the database. The primary will not reject the file. This is particularly useful for single tablespace complete recoveries. It is not advisable to use the standby as the *only* backup of a database. If you discover that for whatever reason you need to recover to a point in time prior to the standby, you will have to restore from a prior physical backup and roll forward. On another topic: On Fri, 4 Jan 2002, Grabowy, Chris wrote: Errr...what's the point in having a standby database then? If you have a media failure then you fail over to your standby database, and your up. Once the standby database comes up then you will have to rebuild the old primary database from the old standby anyway, since you now have all these new transactions hitting the old standby server. If you perform graceful failover, A.K.A. role reversal, you can have the former primary pick up as the standby immediately after failover without recopying. I cover this topic in my Openworld paper on my site. -- Jeremiah Wilton http://www.speakeasy.net/~jwilton -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Oracle 8.1.7 on MS 2000
Yes, under Windows 2000 Server. We installed 8.1.7.0.0, then applied the 8.1.7.2.1 patch, then applied the 8.1.7.2.2 patch, and finally applied the 8.1.7.2.5 patch. Then I built the database and imported our old 8.1.6.0.0 data last weekend. Looks OK so far...less memory leakage...DBMS_Stats works nicely - it was a dog (in my limited trials) on 8.1.6. Our DB is spread across about 340GB of datafiles on 20 disks, so there's a significant amount of data. Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- Sent: Monday, January 07, 2002 2:11 PM To: Multiple recipients of list ORACLE-L Yes. Install 8.1.7, upgrade to 8.1.7.2.1, only then create databases. One of the interim patches between the two levels knocked out SNP processing in existing databases - no job would run. Unfortunately, testing jobs was not a part of the overall QA process so I don't know exactly which patch was bad. Oracle could not fix the problem and after battling it for a week I ended up rebuilding databases. There were other issues, specifically, Netassist wouldn't work with Names Server in 8.1.7.0, it would just crash, as well as some other setback I can't recall. Having said that, the patched up 8.1.7.2.1 on Win2k seems stable and solid. After running it for 6 months we are planning on moving into production shortly. If you're going Windows, this is the way to do it. Gary Weber Senior DBA Charles Jones, LLC||Superior Information Services, LLC 609-530-1144, ext 5529 -Original Message- L. Sent: Monday, January 07, 2002 2:51 PM To: Multiple recipients of list ORACLE-L Any experience with Oracle 8.1.7 on MS2000? Ron Smith -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite 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).
Recover Primary DB from Standby DB
Am I an Idiot? I've maintained a Managed Standby database (8.1.6 under Win2k) for a year now. We've now moved to new servers (8.1.7.2.5 under Win2k) and will again have both Primary (Production) and Managed Standby databases. In planning a new backup stragegy, it occurs to me (why not ever before?) that I should be able to use the datafiles of my Standby DB (along with the Primary's archived and online redo logs) to recover my Primary DB in the event of media failure. In the event of a drive failure on the Primary server, shouldn't I be able to shutdown the Standby DB and copy it's relevant datafiles to the Primary, then use the Primary's archived and online redo logs to recover those datafiles to the point of drive failure? It makes sense to me, but nowhere in the docs have I found mention that this is an appropriate action. Am I missing something? Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite 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: Error in Package
Ramon, You've declared the Procedure SELECCIONAR differently in the package spec and body. Oracle must think you're creating an overloaded procedure with different arguments for each version. The spec and body declarations must match. Jack Jack C. ApplewhiteDatabase Administrator/DeveloperOCP Oracle8 DBAiNetProfit, Inc.Austin, Texaswww.iNetProfit.com[EMAIL PROTECTED](512)327-9068 -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Ramon EstevezSent: Monday, December 17, 2001 9:21 AMTo: Multiple recipients of list ORACLE-LSubject: Error in Package Hi List, What is wrong with this package, I compared it to one example in a book and looks equal, but still get an error. CREATE OR REPLACE PACKAGE MANTENIMIENTO_COMPROBANTES AS TYPE COMPROBANTES_RECORD IS RECORD ( GRUPO COMPROBANTES.GRUPO%TYPE, COMPANIA COMPROBANTES.AGENCIA%TYPE, TIPO_COMPROBANTE COMPROBANTES.TIPO_COMPROBANTE%TYPE, COMPROBANTE COMPROBANTES.COMPROBANTE%TYPE, FECHA COMPROBANTES.FECHA%TYPE, AG_CUENTA COMPROBANTES.AG_CUENTA%TYPE, CUENTA COMPROBANTES.CUENTA%TYPE, DETALLE COMPROBANTES.DETALLE%TYPE, CONCEPTO COMPROBANTES.CONCEPTO%TYPE, ESTATUS COMPROBANTES.ESTATUS%TYPE, ESTATUS_IMPRESION COMPROBANTES.ESTATUS_IMPRESION%TYPE, DESC_AGENCIA AGENCIAS.DESCRIPCION%TYPE, CUENTA_CONTABLE DATOS_BALANCES.CUENTA_CONTABLE%TYPE, DESC_CUENTA CUENTAS.DESCRIPCION%TYPE, DESC_CLIENTE CLIENTES.NOMBRE_COMERCIAL%TYPE ); Para Seleccionar Registros de la Tabla-- TYPE COMPROBANTES_REFCURSOR IS REF CURSOR RETURN COMPROBANTES_RECORD; Para Instrucciones DML-- TYPE TABLA_COMPROBANTES IS TABLE OF COMPROBANTES_RECORD INDEX BY BINARY_INTEGER; Procedimiento para Seleccionar registros de la tabla-- PROCEDURE SELECCIONAR (PCOMPROBANTESQRY IN OUT COMPROBANTES_REFCURSOR, PGRUPO IN COMPROBANTES.GRUPO%TYPE, PCOMPANIA IN COMPROBANTES.COMPANIA%TYPE, PTIPO_COMPROBANTE IN COMPROBANTES.TIPO_COMPROBANTE%TYPE, PCOMPROBANTE IN COMPROBANTES.COMPROBANTE%TYPE ); END MANTENIMIENTO_COMPROBANTES; The package header creates fine. This is the package body 1 -- 2 -- Desarrollo del Paquete para Mantenimiento Tabla de Comprobantes 3 -- 4 CREATE OR REPLACE PACKAGE BODY MANTENIMIENTO_COMPROBANTES AS 5 -- 6 -- Procedimiento para Seleccionar registros de la tabla 7 -- 8 PROCEDURE SELECCIONAR (PCOMPROBANTESQRY IN OUT COMPROBANTES_REFCURSOR, 9 PGRUPO IN NUMBER,10 PCOMPANIA IN NUMBER,11 PTIPO_COMPROBANTE IN NUMBER,12 PCOMPROBANTE IN NUMBER) IS13 BEGIN14 OPEN PCOMPROBANTESQRY15 FOR16 SELECT C.GRUPO, C.COMPANIA, C.TIPO_COMPROBANTE,17 C.COMPROBANTE, C.FECHA, C.AG_CUENTA,18 C.CUENTA, C.DETALLE, C.CONCEPTO,19 C.ESTATUS, C.ESTATUS_IMPRESION, A.DESCRIPCION20 DESC_AGENCIA, D.CUENTA_CONTABLE, CT.DESCRIPCION21 DESC_CUENTA, CTE.NOMBRE_COMERCIAL DESC_CLIENTE22 FROM23 COMPROBANTES C, AGENCIAS A, DATOS_BALANCES D,24 CUENTAS CT, CLIENTES CTE25 WHERE26 C.GRUPO = PGRUPO AND27 C.COMPANIA = PCOMPANIA AND28 C.TIPO_COMPROBANTE = PTIPO_COMPROBANTE AND29 C.COMPROBANTE = PCOMPROBANTE AND30 A.AGENCIA = C.AGENCIA AND31 D.GRUPO(+) = C.GRUPO AND32 D.COMPANIA(+) = C.COMPANIA AND33 D.AGENCIA(+) = C.AG_CUENTA AND34 D.CUENTA(+) = C.CUENTA AND35 CT.GRUPO(+) = D.GRUPO AND36 CT.COMPANIA(+) = D.COMPANIA AND37 CT.CUENTA(+) = D.CUENTA_CONTABLE AND38 CTE.CLIENTE = D.CLIENTE;39 END SELECCIONAR;40* END MANTENIMIENTO_COMPROBANTES;41 / Warning: Package Body created with compilation errors. SQL SHOW ERRORS PACKAGE BODY MANTENIMIENTO_COMPROBANTES;Errors for PACKAGE BODY MANTENIMIENTO_COMPROBANTES: LINE/COL ERROR -35/16 PLS-00323: subprogram or cursor 'SELECCIONAR' is declared in a package specification and must be defined in the package body Any help, suggestions would be appreciated !!! Ramon E. Estevez [EMAIL PROTECTED] Dominican Republic 809-565-3121
RE: DB SIZE ?
Seema, Several possible solutions - you pick. - Delete Data - Drop Tables - Drop Indexes - Drop Tablespaces That ought to get you started. ;-) Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- Sent: Wednesday, December 12, 2001 12:20 PM To: Multiple recipients of list ORACLE-L hi Just a thought.How do we reduce Database size? Thanks -Seema -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite 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: DBA Weakest Link
Paul, It's very thoughful of you to pre-plan an activity during your party, but, with all due respect, YOU'VE GOT TO BE KIDDING! Sound like fun? NOO!! DBA trivia at a Chrismas party?!?!? Come on, think of something EVERYONE (significant others included) can enjoy! DBAs have a bad enough reputation for not having a life - you don't need to reinforce it. IMHO, shop talk should be strictly banned at parties. Think of something a bit more fun - movie trivia, music lyrics, the best jokes I've ever heard. There should be plenty of internet sites at which you could find lots of info. for topics such as these. Or - my favorite - have anyone who knows how to dance teach the others a step or two of Swing, Salsa, etc. Get those normally sedentary DBAs off their duffs and on their feet! Have a dance contest! Jokes could have the party-goers rolling on the floor - DBA trivia absolutely WON'T. Respectfully, Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- Sent: Friday, December 07, 2001 8:20 AM To: Multiple recipients of list ORACLE-L Hello DBAs, I'm having a company holiday party today, and as you all may know my company employs mostly DBAs. (Pythian is an Oracle DBA Outsourcing shop.) I'm thinking of having a DBA Weakest Link game for fun at some point of the evening. Yes, I know, the partners/husbands/wives will think it's a bit of a bore, but imagine the fun for us DBAs! :-) So what I'm trying to do is get many, many weakest link-style questions (with answers) ready. I want them mostly to be easy, but with the odd hard one snuck in for unfairness. Please help out by submitting your questions, and I'll summarize and post the complete list back to the list when I'm done! Sound like fun? I'll get us started with a format. ... Paul -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite 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: RAID system max throughput
Dennis, I'm no RAID guru, but I can sure imagine disk heads thrashing around, trying to satisfy a mix of sequential and random reads and writes, causing the DB to wait, but not getting anywhere near the rated throughput for the RAID controller or channel. Could that possibly be the case? Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- WILLIAMS Sent: Friday, December 07, 2001 10:40 AM To: Multiple recipients of list ORACLE-L Whenever I discuss disk waits with my system administrator, I always get the reply that the RAID system isn't anywhere near its rated throughput. Maybe I'm wrong, but I don't see any of the tuning books mentioning that as a relevant performance characteristic. However, I've never been able to move the discussion beyond this point. Can anyone straighten me out on this point or point me to a resource that might be applicable. Our system is Oracle 8.1.6, Compaq Tru64. We use hardware RAID-5 with a battery-backed RAM cache, and have about 3 RAID sets (plus some extra disks for redo logs, etc.), and performance is fine, but I'm always looking as to how we can improve Oracle performance. The application is our corporate ERP system. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite 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 to find duplicate rows...
Jim, Oops! Right after I sent this, I saw that you asked to find, not delete, duplicates. Sorry, I've spent the last two days de-duping a bunch of tables, so I've got deletion on the brain. How about: Select SomeColumn From MyTable Where MyColumn In ( Select MyColumn From My Table Group By MyColumn Having Count(*) 1 ) ; Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- Sent: Thursday, December 06, 2001 3:34 PM To: [EMAIL PROTECTED] Jim, An option that handles multiple duplicates is: Delete From MyTable Where (MyColumn,RowID) In ( Select MyColumn,RowID From MyTable Minus Select MyColumn,Min(RowID) From MyTable Group By MyColumn ); You could use Max(RowID) as well, depending on your needs. Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- JAMES W [IT/1000] Sent: Thursday, December 06, 2001 2:26 PM To: Multiple recipients of list ORACLE-L Hello all, I know this has come across many times now, and I thought I had it saved somewhere - you know how that goes... I need to query a table to find all duplicate rows based on a given column. Don't feel like re-inventing the wheel... Thanks in advance, Jim -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite 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 to find duplicate rows...
Jim, An option that handles multiple duplicates is: Delete From MyTable Where (MyColumn,RowID) In ( Select MyColumn,RowID From MyTable Minus Select MyColumn,Min(RowID) From MyTable Group By MyColumn ); You could use Max(RowID) as well, depending on your needs. Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- JAMES W [IT/1000] Sent: Thursday, December 06, 2001 2:26 PM To: Multiple recipients of list ORACLE-L Hello all, I know this has come across many times now, and I thought I had it saved somewhere - you know how that goes... I need to query a table to find all duplicate rows based on a given column. Don't feel like re-inventing the wheel... Thanks in advance, Jim -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite 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: Duplexing Archived Log Files
Dennis, We use log_archive_dest_1 log_archive_dest_2 sending a copy of each archived redo log to our Standby database (log_archive_dest_2). log_archive_dest_2 is used with the OPTIONAL parameter, since we don't want to stop archiving if the Standby is down or there's some problem with the LAN. We also have log_archive_min_succeed_dest = 1 to make sure that the archiving succeeds at least to the local target drive (log_archive_dest_1). It's worked great for us (8.1.6.0.0 EE under Win2k). BTW, we use RMan for hot backups and like it very much. We can also issue the Alter Tablespace Begin Backup command and get instantaneous results - which I had to do recently to restore some datafiles to the Standby after one of its disks failed. I wonder why you get such slow response? Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- WILLIAMS Sent: Thursday, December 06, 2001 12:29 PM To: Multiple recipients of list ORACLE-L Is anyone using the LOG_ARCHIVE_DUPLEX_DEST parameter? Any problems with it? We are on Oracle 8.1.6 on Compaq Tru64. On our ERP 150-gig. database, we are currently doing a cold backup weekly. This means that we are very dependent on archive logs. - Recovery time isn't a big concern. In the event of a failure, recovery time isn't that critical. Loss of a week of work would be. - We have tried hot backups successfully on smaller databases, but we found that once we issue ALTER TABLESPACE BEGIN BACKUP there was often a delay of hours for that command to complete. So we gave up on hot backups. - We are starting to work with RMAN and feel in the long run this will be an ideal solution. Thanks to everyone for answering my newby RMAN questions. - Recently Oracle has added a feature to duplex the archive log files. This sounds like it would lessen our archive log vulnerability. However, it causes my sys admin more work, so I would appreciate some feedback before I add this burden to his work. Thanks for any thoughts you care to share. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite 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: V7 online backups
Charlie, You must first put your database in ArchiveLog mode, of course. The basic init.ora parameters to set and the command Alter Database ArchiveLog (while mounted, but not open, after a clean shutdown) haven't really changed from 7 to 8 to 8i, except now you can specify archive log duplexing. A simple SQL script might get you started. For each Tablespace: Alter Tablespace TS1 Begin Backup ; Host cp source_directory/ts1_datafile_1.dbf target Host cp source_directory/ts1_datafile_2.dbf target ... Alter Tablespace TS1 End Backup ; ... Save the datafile copies and your archived redo logs, as well as backup control files, and you've got it covered. Oh, and don't back up your online redo logs - they'll be worthless for a recovery and can cause you grief if you restore them over your current, good, ones. Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- Mengler Sent: Thursday, December 06, 2001 12:29 PM To: Multiple recipients of list ORACLE-L I'm not sure if I'm bragging or complaining, but I've never setup or supported online backups for Oracle. I'm being directed to implement online backups for a V7.3.4.5 instance. A search of OTN reveals that Oracle no longer seems to have any V7 documents available anymore. :-( Since this DB is stuck at V7.3.4.5 for at least 6 more months, I need to get online backups operational without using RMAN. If somebody who has *nix shell script(s) or Perl code that implements online backups, I'd appreciate if you would send a copy my way. I'm just looking for something to get me started. TIA HAND! -- Charlie Mengler Maintenance Warehouse [EMAIL PROTECTED] 10641 Scripps Summit Ct. 858-831-2229 San Diego, CA 92131 to_date((2440588+((date_time-mod(date_time,86400))/86400)),'J')-8/24+mod(dat e_time,86400)/86400 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Charlie Mengler 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: Jack C. Applewhite 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 Loader Commit Point?
Ken, Check the Loader log file. The records are being rejected for some reason and Loader only tells you in the log. Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- Sent: Monday, December 03, 2001 3:33 PM To: Multiple recipients of list ORACLE-L I am working on a control script that I have restricted to only loading 5 records as a test. Basically I am taking data from the load file and putting it into a table SQL Loader runs and gives me this. - SQL*Loader: Release 8.1.7.0.0 - Production on Mon Dec 3 15:22:26 2001 (c) Copyright 2000 Oracle Corporation. All rights reserved. Commit point reached - logical record count 5 -- When I do a select on this table I get no rows selected. I don't get an error message from SQL Loader. So any ideas as to what the problem is? Thanks, Ken Janusz, CPIM Database Conversion Lead Sufficient System, Inc. Minneapolis, MN -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite 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: deleting duplicate records
Sunil, If there are multiple duplicates foreven a few SKU_Num values, you're doing multiple scans (full table or full index) to get all the dups out. You might reconstruct the SQL to not use a looping construct if there are lots of duplicate rows for each SKU_Num Delete From FMS_Test Where ( SKU_Num, RowID ) In ( Select SKU_Num, RowID From FMS_Test Minus Select SKU_Num, Max ( RowID ) From FMS_Test Group By SKU_Num ) ; Or keep the loop and add a Commit right after the Delete statement - that will cut down on Rollback segment usage. Is there an index on the column sku_num? It would probably help as well. Jack Jack C. ApplewhiteDatabase Administrator/DeveloperOCP Oracle8 DBAiNetProfit, Inc.Austin, Texaswww.iNetProfit.com[EMAIL PROTECTED](512)327-9068 -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of [EMAIL PROTECTED]Sent: Monday, December 03, 2001 3:10 PMTo: Multiple recipients of list ORACLE-LSubject: deleting duplicate records Hello all, could someone please tell me why the procedure below(Author:Nick Butcher) takes less than a minute ona table with 50,000 rows and about 21 mins on a table with 235,000 rows?? i have created a bigger rollback segment to take care of this, but no improvement.where should i be looking for bottlenecks?? CREATE PROCUDURE DUPES_DEL ASBEGIN LOOPDELETE from fms_testwhere row_id in(select min(rowid)from fms_testgroup by sku_numhaving count (*) 1);EXIT WHEN SQL%NOTFOUNDEND LOOP;COMMIT;END; appreciate it.Sunil NookalaDellCorp.Austin, TX
RE: rman disk channel destination
Title: rman disk channel destination Lisa, You specify the destination directory with the FORMAT keyword, by which you both format the backup piece files and specify their destination. Jack Jack C. ApplewhiteDatabase Administrator/DeveloperOCP Oracle8 DBAiNetProfit, Inc.Austin, Texaswww.iNetProfit.com[EMAIL PROTECTED](512)327-9068 -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Koivu, LisaSent: Thursday, November 29, 2001 3:30 PMTo: Multiple recipients of list ORACLE-LSubject: rman disk channel destination This info isn't forthcoming on metalink or in the doco as far as I can tell. Can anyone tell me how to change the directory associated with the rman command 'allocate channel disk'? Or at least figure out where it is, rather than hunt and peck? Is it fixed? Boy that would really bite. Thanks Lisa Koivu Oracle Database Monkey Mama Fairfield Resorts, Inc. 954-935-4117
RE: svrmgrl and W2k
Title: svrmgrl and W2k Lisa, I'm running 8.1.6 and 8.1.7 EE on Win2k machines and I connect to them all the time via Terminal Server. I get the same ORA-12560, but I just invoke svrmgrl using internal@test or internal@dev. Works just fine.Am I missing part of the problem? Jack Jack C. ApplewhiteDatabase Administrator/DeveloperOCP Oracle8 DBAiNetProfit, Inc.Austin, Texaswww.iNetProfit.com[EMAIL PROTECTED](512)327-9068 -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Koivu, LisaSent: Tuesday, November 27, 2001 9:25 AMTo: Multiple recipients of list ORACLE-LSubject: svrmgrl and W2k OH MY GOSH this sucks. Is anyone running 8.1.7 EE on W2k? I am trying to start the database (and the listener, for that matter) via Terminal Server and it keeps erring out with ORA-12560 protocol adapter error. I look on Metalink and it just plain says, Go to the console. WHAT? Are you KIDDING! Here's the note: fact: Oracle Server - Enterprise Edition fact: RDBMS fact: MS Windows 2000 fact: MS Windows NT symptom: Cannot connect to SVRMGRL symptom: ORA-12560: TNS:protocol adapter error symptom: Cannot connect to SVRMGR30 symptom: ORA-12203: TNS:unable to connect to destination cause: Bequeath protocol is not supported with Microsoft Terminal Server Client. ORA-12203 is seen on Oracle 8.0.6. ORA-12560 is seen on Oracle8i. fix: Connecting to SVRMGRL is done by using the BEQUEATH protocol which is not available using an MS Windows 2000 Terminal Server Client. To startup the instance, use the the Windows 2000 console to connect to the SVRMGRL. I can do this with W2k, Terminal Server and Standard Edition. Has anyone else seen this ? I am so irked I could scream. (But it could be the hormones). Lisa Koivu Oracle Database Monkey. Fairfield Resorts, Inc. 954-935-4117
RE: Revoke Delete
Actually, you can. Use FGAC (Fine-Grained Access Control) and you can put a Policy in place on a table that even the table owner can't bypass - even System can't bypass. Only Sys can bypass FGAC policies - and the owner of the security schema in which you place the Policy functions. I've used FGAC and Application Context successfully to enforce complex security, but the more I think about it, you could really do some fiendish tricks with it - if you were the fiendish kind. ;-) Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- Baumgartel Sent: Tuesday, November 27, 2001 3:55 PM To: Multiple recipients of list ORACLE-L You can't revoke the ability to delete from the schema owner. You could revoke CREATE SESSION from the schema owner, but that doesn't solve the problem of DBA-privileged accounts being able to delete. I'm guessing that this is a perfect opportunity to use an INSTEAD OF trigger. --- Aldi Barco [EMAIL PROTECTED] wrote: Hi Listers, How can we revoke 'delete privilege' from the schema owner of the table and also from DBA ? If it is not possible, can we set through trigger ? Thanks. Aldi _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Aldi Barco INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Yahoo! GeoCities - quick and easy web site hosting, just $8.95/month. http://geocities.yahoo.com/ps/info1 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Paul Baumgartel 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: Jack C. Applewhite 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: IOT and ora-600 [ktbgcur_2] on NT 81714
Bruce, This may not be related at all, but I sometimes get ORA-00600 [smbalo_1] on an IOT when the Analyze Table The_Table Estimate Statistics Sample x Percent statement is executed against it. The table gets truncated and repopulated nightly and has about 544,000 rows. What's weird is that a few weeks ago ... Sample 5 Percent would produce the error, but 4 and 6 percent wouldn't. Just last night 6 percent started producing the error and now only 3 percent or lower will work. I haven't pursued it with Oracle Support, since we're soon to move to a new Win2k server with 8.1.7.2.5 (we're currently on 8.1.6.0.0 under Win2k) and I'm hoping the problem will just go away. Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- Bruce (CALBBAY) Sent: Thursday, November 22, 2001 8:15 PM To: Multiple recipients of list ORACLE-L Hi, We are testing the use of IOTs getting ready to implement 1 in production. During some of the testing we have encountered an ora-600 [ktbgcur_2] in the alert log. This has been logged with Oracle support to get an explanation of what this means but in the meantime I was wondering if anyone else has come across this? The all important version information: Oracle 8.1.7.1.4 - Windows NT4, Quad processor server with parallel degree of default on all tables and indexes. From the alert log: Sun Nov 18 14:36:54 2001 Errors in file d:\oracle\admin\abcd\udump\ORA00657.TRC: ORA-00600: internal error code, arguments: [ktbgcur_2], [], [], [], [], [], [], [] From the user dump file (ora00657.trc mentioned above) *** 2001-11-18 14:36:54.843 ksedmp: internal or fatal error ORA-00600: internal error code, arguments: [ktbgcur_2], [], [], [], [], [], [], [] Current SQL statement for this session: SELECT ANLY_ANALYSIS,ELMN_ELEMENT,ELEMENT_PERCENTAGE FROM ANALYSIS_ELEMENTS WHERE ROWID=:1 FOR UPDATE OF ANLY_ANALYSIS NOWAIT - Call Stack Trace - Any feedback would be appreciated. Thanks, Bruce Reardon mailto:[EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite 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: CLOB datatype
Rick, CLOBs been bery bery good to me. ;-)(8.1.6 on Win2k) We collect 50,000 - 200,000 documents per day and store them in out-of-line CLOBs in a partitioned table. The CLOB column has an interMedia Text index on it, which allows us to query up to about 12 million documents with great performance. CLOBs can be stored in separate segments from the regular data, allowing better storage and access control. CLOBs can be easily manipulated with the DBMS_LOB package. They suffer from none of the many problems caused by LONG columns. They're GRREAT! Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- [EMAIL PROTECTED] Sent: Wednesday, November 14, 2001 4:37 PM To: Multiple recipients of list ORACLE-L Does anyone have any opinions on using CLOB/NCLOB datatypes in Oracle? I am running 8.1.7.2 on Solaris. Oracle Docs state that CLOBs can handle up to 4 Gig. Are there any gotchas by using CLOBs? Thanks for any information, Rick Stephenson -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite 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: how to enable FGAC ?
Seema, FGAC, which is supported by the supplied PL/SQL package DBMS_RLS (Row Level Security), is only available in Oracle8i Enterprise Edition. Even if you just have Standard Edition, you can get a lot of similar benefit by using Application Context, which you would need to use anyway to implement FGAC properly. Both worked very nicely for me - at least in 8.1.6. Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- Sent: Wednesday, October 31, 2001 10:00 AM To: Multiple recipients of list ORACLE-L Hi I want to implement Fine-grained access control feature in my database. i run select * from v_$option and see in my database Fine-grained access control is having value false.Let me know how to enable this feature at Database end? Thanks Seema -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite 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: Stored Procedures Question
Ken, To see exactly how the stored procedure files will be used, you'll need to examine the DDL script to find out how they're called. That might tell you which directory in which to put them. If you have to run them manually, do it after the DDL script finishes. Execute (from SQL*Plus) all the *.PKS (Package Spec) files first, then the *.FNC (Function) and *.PRC (Procedure) files, followed by *.PKB (Package Body), then *.TRG (Triggers). If there are any functions that call procedures you may have to run the *.FNC files again, after the *.PRC. Since nothing can call a trigger, the *.TRG files are last, right after the *.PKB, since the code in the package bodies could call procedures or functions. If any views reference publice packaged variables, you may have to recreate those views. Check the Status column in User_Objects to see if anything ends up INVALID. You'll have to recompile... Alter Function x Compile ; Alter Package x Compile ; Alter Package x Compile Body ; ... Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- Sent: Wednesday, October 31, 2001 10:15 AM To: Multiple recipients of list ORACLE-L I received the DDL SQL script (approx. 11,000 lines of code) to generate a database from our other office in Malaysia. Along with this script I got another file with approx. 720 files called stored procedures with the suffix .PRC, .PKB, .PKS, .FNC. AND .TRG. When I go to generate the DB using the DDL SQL script how will these stored procedure files be used? Will the SQL script call them, or will they be called and used after the DB is built? Where should these stored procedure files be stored in the directory structure? The box is W2000 and Oracle is 8.1.7. Thanks much, Kenneth C. Janusz, CPIM Database Conversion Lead Sufficient System, Inc. Minneapolis, MN -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite 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: Problems with Intermedia
Samir, interMedia Text, Audio, Image, Video? What version of Oracle? What platform? We need more info. We use interMedia Text heavily (Oracle 8.1.6 on Win2k) and I've never seen the ctxhx process. Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- Samir Sent: Monday, October 22, 2001 6:10 AM To: Multiple recipients of list ORACLE-L Hi there !! Hi there, An application running the Intermedia process ctxhx is seen to b consuming an unusually high CPU time thus preventing other applications from running. Being very new to Intermedia I do not why this is happening. Can somebody help me on how to diagnose the problem of high CPU usage by ctxhx and correct it ?? Any help will be greatly appreciated. Thanks. Samir Sarkar Oracle DBA - Lennon Team SchlumbergerSema Email : [EMAIL PROTECTED] [EMAIL PROTECTED] Phone : +44 (0) 115 - 95 76217 EPABX : +44 (0) 115 - 957 6418 Ext. 76217 Fax : +44 (0) 115 - 957 6018 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite 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
Dave, By relevant section I assume you mean under GRANT in Oracle8i SQL Reference. Table 11-1 System Privileges is self-explanatory while tables 11-3 and 11-4 cover object privileges. For a full explanation of what any particular privilege implies, you'll have to dig into the docs and find the sections dealing with the thing on which the privilege is based. For example, you'll have to read about Application Context to understand what CREATE ANY CONTEXT (Create any context namespace) implies - see Oracle8i Application Developer's Guide - Fundamentals for details on Application Context and Fine-Grained Access Control. I'm not sure that a single list could explain enough without becoming a lengthy tome, though Oracle certainly could have done better (at least some links to relvant sections) in the GRANT tables of privileges. Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- Sent: Friday, October 19, 2001 7:05 AM To: Multiple recipients of list ORACLE-L Hi, Can anyone point me in the direction of a list of privileges (version 8.1.7) and their meanings. Obviously most are self explanatory but there are a few I'm not too sure about. I tried using the Java Search facility in the Oracle documentation but this only points me to the relevant section and does not highlight where in the section the search words are. I have also tried Metalink and could not find a definitive list. Any help would be appreciated. Dave Leach -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite 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: providing 24*7 database ---
Narender, Transportable Tablespaces might allow you to load data in an offline instance then quickly plug in the new data to the production instance. Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- Akula Sent: Thursday, October 18, 2001 4:30 PM To: Multiple recipients of list ORACLE-L hi gurus, Our shop ( GIS oracle spatials ) attempting to provide a production database (7x 24 hours) , currently we have to offline database for users while loading of data. we donot what users to access data while loading. We are thinking of provide 24* 7 services to customers with out going offline. What are the best possible solutions ? I had few but I donot know its right direction . Possible Solutions Replication - * not possible until Oracle 9i spatial (because of the restriction on replicating objects). * Even then expense of additional licensing/machinery may be too great Duplicate instances - have 2 instances and users switch from one to other after production load.-- how to implement ? Duplicate schema within current database. Have 3 schemas inside a single TIPSPROD instance. The schema with the current data remains in production until the second schema is loaded with the new data. This is can only work if we introduce a third schema that holds the views. At certain time, we redefine all the views in the DATAVIEWS schema. --- its kind of duplication --- certainly not my option Can some body direct me where to look or any ideas ? TIA narender.akula -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Narender Akula INET: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite 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: Intermedia Performance Benchmarks anyone ?
Martin, We use interMedia Text to index and query up to about 10-15 million CLOB documents (up to 5KB each). We're on 8.1.6.0.0 under Win2k - 2 550MHz CPUs, 2GB RAM, 18 36GB drives. Because a domain index cannot be partitioned, we have the documents spread across 5 tables (on 6 drives). One is a 2 partition table (each partition on its own drive) containing the current two months of docs, the other 4 hold the 4 prior months' docs. We can query the entire 6 months of docs via a Union View on them - even Contains() queries work fine on this view. When we add a new month's partition, the prior month's partition gets turned into a table (segment exchange). The interMedia Text indexes on the partitioned table and the new prior month are rebuilt. Lately we've been getting about 3.5 million docs/month and the index rebuild takes about 7 hours - that's 7 hrs. for the index on the prior month and 7 more hours for the index on the partitioned table, which only contains one month of docs at that point. Since we're adding docs every day, we sync the interMedia index every morning. Last night we added about 200,000 docs and it took about 3 hours for the index to resync. We don't use ctxsrv, but use CTX_DDL.Sync_Index. When we get over about 4.5 million docs in a table, the resync really slows down. The in-memory part still happens at about 150 docs/sec, but when interMedia writes to disk it slows down a bunch. What took 3 hours today will take 10 hours in a couple of weeks. That's why I plan on spreading the DR$$I segment across multiple drives by spreading the datafiles of its tablespace across those drives. BTW, that brings up some performance points - be sure you cache the DR$$R segment (use CACHE not CACHE READS, due to bugs in Oracle): Alter Table DR$YourIndexName$R Modify LOB (Data) (Cache) ; Also ensure that your LOBs are out-of-line and stored in their own segment(s) on drive(s) separate from the regular data. Make sure that your I_TABLE_CLAUSE, R_TABLE_CLAUSE, and I_INDEX_CLAUSE all specify tablespaces on their own drives to spread the I/O out even further. We're getting 2GB more RAM on a new server, so I plan on caching the 900MB DR$$X segment, which is the index on the DR$$I token table. I've learned a lot about how interMedia Text processes different kinds of queries by watching disk I/O on Win2k's Performance Monitor while I issue various flavors. Our folks use lots of complex query terms with heavy use of the Stemmer. I've gotten them to switch from using tons of ORs to using the Equivalence operator and we're getting much better results using NEAR than simple ANDs. Performance is very good, with CONTAINS queries returning results in less than a second for terms that are rare in the docs, up to a minute for terms that are common in lots (e.g. hundreds of thousands) of docs. If you're going to do synonym searches, you'd better start looking for a good thesaurus - the one Oracle ships is pretty limited. We've not found a good one for the technical lingo our docs contain, so we don't do ABOUT queries at this time. Get familiar with CTX_Query.Explain, it will help you understand things like what the Stemmer *really* does and how complex queries are parsed. Hope this helps. Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- Kendall Sent: Thursday, October 04, 2001 10:00 AM To: Multiple recipients of list ORACLE-L Hello all, Although I have installed Intermedia as part of my general DBA duties before I have not experienced any particular requirements on throughput rate or indexing. I need some information on being able to deal with large volumes of product data (e.g. 1 million products in a retail application) and be able to perform 'intelligent' searches against the metadata (things like typographical error matching, synonyms etc.) as well as the more usual parametric search (i.e. advanced search page with lots of metadata specific fields). Indexing time and max throughput are also of interest. Any data based on experience would be appreciated. Thanks Martin -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite 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: Intermedia Performance Benchmarks anyone ?
Ross, I disagree that interMedia Text is way too slow to scale. Our experience has convinced us that I/O bottlenecks are the main performance killers with large interMedia Text indexes. The problem is that it takes some experience to find out how this special kind of index is structured (6 or 8 separate table and index segments per index) and how it behaves. As usual, the Oracle docs are pitifully inadequate - you've gotta search through TechNet and MetaLink for details and bug workarounds (like CACHE instead of CACHE READS for DR$$R). Caching the DR$$R segment helped immensely and I can see that when pieces of the DR$$X index are cached, queries with terms in those pieces are lightening fast. I am betting that when I spread the DR$$I table across multiple drives, instead of the single drive ours is currently on, we'll see much better performance of NEAR queries (which depend on the word position info. there), as well as faster index resyncs. In 9i Domain Indexes become partitionable, so I'm looking forward (in about a year - experiences with 6.0, 7.0, 8.0 and 8.1.5 have made me wary) to putting our 6 (or more) months of docs into one partitoned table. There may be other I/O distributing kinds of enhancements by then, as well. For sure I'll have explored every trick I can think of! ;-) With more drives and a bit more RAM, I think we can handle 10 million docs per month (60 million total online), even on our lil' ol' Win2k box. That's just x3 to x4 of what we do now. Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- Sent: Thursday, October 04, 2001 5:36 PM To: Multiple recipients of list ORACLE-L *excellent* post. thanks. Anyone out there put the indexes and tables on solid state disk? They have ssd up to about 10G and higher, I hearjust curious, not trying to invoke a global listserv discussion on how it can't work or wouldn't be worth it, especially on microsoft platforms, etc. It would be neat to hear about an InterMedia indexing miracle. This really neat tool just sounds WAAY to slow to scale at this point, which answers a pet question of mine. (Something like Why do services like 'Ask Jeeves' suck so hard?) In Love and Peas, etc. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite 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: Extents size.
Multiple extents a good thing? YES! I'm *depending* on many multiple extents of an interMedia index segment (the DR$$I segment) to distribute I/O for full text indexing and queries. I plan to distribute the datafiles of the tablespace holding the DR$$I segment across multiple drives and set the uniform extent size to 1MB. Since Oracle8i distributes new extents for a table or index in a round-robin fashion, I'll get even distribution of that big token table across several spindles. (...and with a couple gig more RAM on a new Win2k box we're getting, I'll be able to cache all 900MB of the DR$$X index - YAY!)8^) BTW, the largest of our out-of-line CLOB segments have nearly 30,000 extents (1MB per extent) with no performance problems at all. However, I am going to implement 100MB extents for those CLOB segments on the new box, to keep the LMT bitmaps within an 8KB block. Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- [EMAIL PROTECTED] Sent: Tuesday, October 02, 2001 12:15 PM To: Multiple recipients of list ORACLE-L Back in the V6 days it was a desired characteristic to have every thing in the first extent of an object for performance reasons. Thankfully those days are gone and it really does not matter how many extents there are. Rachel has a presentation on Oracle Myths where she actually portrays having multiple extents as a good thing from an IO perspective (Rachel, correct me if I got this wrong). Although I can't give you exact examples, take a look and v$filestat. I've found that tablespaces where there are more than one extent in the objects have a lower average io wait time that those where everything is in the first extent. The only real good reason I have found for re-organizing a tablespace is to get all of the used extents at one end and all of the free extents (you know those little bitty ones that individually aren't worth the trouble, but together!!) at the other end. Dick Goulet -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite 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: For i (Select..)
If you only use a cursor in this manner, the only place that the cursor can be used is in the particular For Loop in which it is defined - it can't be shared by other procedures or functions. If you design and create packaged public cursors, they can be reused from other stored procedures and/or client side programs. Code reuse is just one advantage. When table structures change - as they invariably do - you have only a few cursors to change, instead of a bazillion Cursor For Loops. Also, you can more easily tune a few public cursors. I'd also recommend passing in Table%RowType arguments to your public cursors and specifying that they return Table%RowType records. That further isolates your code from table changes. Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- Zsolt Sent: Monday, October 01, 2001 6:15 PM To: Multiple recipients of list ORACLE-L Hi, If this code is a cursor then why doesn't everybody use it this way? For me it seems a lot simplier and easier than declaring a cursor in a normal way. At 14:50 2001.10.01. -0800, you wrote: the code IS a cursor --- Csillag Zsolt [EMAIL PROTECTED] wrote: Hi, I've read in Tom Kyte's book the following technique: For i in (Select statement ) Loop Process records here End Loop ; I have tried it and it works great. The question is that what is the difference betwen a cursor and the code above? Which is more efficient? Zsolt Csillag Hungary -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite 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: Physical access to servers for maintenance
Title: RE: Physical access to servers for maintenance Me too. We've got our 4 Production Win2k servers (2 Oracle8i servers, 2 Webservers) at a colocation facility and have used either PCAnywhere or Terminal Services to access them over the Internet for the last 9 months. The developers have never needed to have access to the servers except to put in the Cold Fusion CD to install CF Server. I've only needed access once, to load some big files that I later imported into a test db on one of the servers (actually, that's when I put in the CF CD, to save the developers a trip). Even then, I really didn't need to be there, I could have transferredmy files via PCAnywhere. If I were the SysAdmin I would have needed access a couple of times to replace failed drives, but the colocation guys took care of that. I recovered our Production db remotely after they replaced the drives. It's very nice to be able to PCAnywhere in to those boxes from home. Jack Jack C. ApplewhiteDatabase Administrator/DeveloperOCP Oracle8 DBAiNetProfit, Inc.Austin, Texaswww.iNetProfit.com[EMAIL PROTECTED](512)327-9068 -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Kimberly SmithSent: Wednesday, September 26, 2001 11:15 AMTo: Multiple recipients of list ORACLE-LSubject: RE: Physical access to servers for maintenance You can use a tool such as ControlIT to connect to NT machines. Its how I do it, both from the office, and from home. How do all these people who claim you need access to the server room to do the day to day stuff provide remote support? I will be damned if I come in during the middle of the night for a database issue.
RE: ENABLING FOREIGN KEY CONSTRAINTS
Harvinder, Spool this to a file, then run it after re-enabling your PK. BTW, you could do the reverse of this to disable the FKs, too. Select 'Alter Table ' || Table_Name || ' Enable Constraint ' || Constraint_Name || ' ;' From User_Constraints Where R_Constraint_Name = Re-Enabled_PK_Name ; Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- Singh Sent: Wednesday, September 26, 2001 11:40 AM To: Multiple recipients of list ORACLE-L Hi, When we do alter table table_name disable primary key cascade;..it also disable all the foreign key constraints but when we after do alter table table_name enable primary key..it does not enable foreign keys is there any syntax that we can enable all th foreign keys referencing particular table.. Thanks -Harvinder -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite 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: Design Issue - Quick response appreciated
Rao, I'd add three columns. Transaction_ID Not Null UK Parent_Trans_IDNot Null FK to Transaction_ID Original_Trans_ID Not Null FK to Transaction_ID Transaction_ID would be the sequence-generated Surrogate Key. For the original record Parent_Trans_ID and Original_Trans_ID would be equal to Transaction_ID. Each new version of the original record would have the original record's Transaction_ID as its Original_Trans_ID, but would receive the Transaction_ID of the record it came from as its Parent_Trans_ID. All information about a new record's heritage would be contained in the record from which it came - no pre-insert lookups required. That way you could easily get all versions of a record (Original_Trans_ID = xxx), while also giving yourself the flexibility of tracing the hierarchical version history via Parent_Trans_ID. Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- Maheswara Sent: Monday, September 24, 2001 2:46 PM To: Multiple recipients of list ORACLE-L Chris, Thanks for the suggestion. In our case, once a record is inserted, we cannot update the record. If any column need to be changed, then, we insert another record which would contain all the data of the columns of the previous record + the data of the changed column (or columns). I am toying with the following idea. Please point out if there are any probs with this. 1. I would create a surrogate key whenever a record is inserted and then insert this record in the transaction table with generation number 1 (please see item # 3 below). 2. I would maintain a separate table - say - KEY TABLE with the surrogate key + all the keys that uniquely identify the record. 3. I would also maintain a table - say - GENERATION TABLE. The columns in this would be - surrogate key + generation number. 4. Whenever, a record is being inserted, I would check key table. If no record with the keys are present, then, I would insert a record in the key table + insert one record in GENERATION table; in this table, generation number would be 1 for this record. 5. If a user tries insert a record which is already existing in the KEY TABLE, then, I would update the generation number column in GENERATION table. In the above way, whenever, I want to get all the previous records, I would go to GENERATION Table and then get the generation number for that surrogate key. (I would be getting the maximum generation number because I always updating this column with the last generation number). Once, I know the last generation number, then, it is a question pulling all the records with the surrogation key + (all the generation numbers). Thanks, Rao ... -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite 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: Best Uses for Oracle Designer 2000?
David, I've used Designer (and Oracle*CASE before it) for about 10 years now. I've always used it for logical data modelling and physical database implementation. In my Developer past, I've done a ton of Oracle Forms and Reports generation from Designer, as well as Web Server Generator apps. All my PL/SQL stored packages have always (and still do) live in Designer. Now that I'm focussed on Development DBA work, I user Designer for physical database design and implementation and PL/SQL package development. It is wonderful for specifying tables, along with their constraints, indexes, triggers, etc., etc. then generating theDDL scripts to create the objects. Designer even lets you deploy the same object, such as a table, with different storage parameters to different databases - say, development, test, and production. The reverse engineering functionality (called "Design Capture" in the tool) of Designer is extremely useful. You don't have to create all the objects of a database by hand, just point Designer at a schema and let it suck up all the details into the Designer Repository - a real time saver. There is definitely a *steep* learning curve associated with Designer and the benefits of the tool are more along the lines of quality, consistency, impact analysis, etc. rather than more rapid development and/or deployment. Get some (or lots) of training andhire a Designer "Guru" to spend some time with you showing you some good techniques for doing what you want to do. Don't get an expert in Oracle Forms generation to help you with Designer's DBA functionaliy, however. Get the "Guru" that has the specific expertise you need. Check out the Oracle Development Tools User Group (ODTUG) at www.odtug.comfor info on how to subscribe to the Designer (and other) lists, which are hosted along with this list right here at FatCity. Designer is HUGE, but you can successfully and profitably use just the pieces you want. Jack Jack C. ApplewhiteDatabase Administrator/DeveloperOCP Oracle8 DBAiNetProfit, Inc.Austin, Texaswww.iNetProfit.com[EMAIL PROTECTED](512)327-9068 -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of David WagonerSent: Thursday, September 20, 2001 4:45 PMTo: Multiple recipients of list ORACLE-LSubject: Best Uses for Oracle Designer 2000? Is anyone out there deriving much benefit from Oracle Designer 2000? My company has the product and I set it up but it looks like the initial investment in learning the product and creating everything in the repository will be extremely time-consuming. So, please tell me your favorite uses for Designer. Thanks, David David B. Wagoner Database Administrator Arsenal Digital Solutions Worldwide Inc. 4815 Emperor Blvd., Suite 110 Durham, NC 27703 Tel. (919) 941-4645 Fax (919) 474-0695 Email mailto:[EMAIL PROTECTED] Web http://www.arsenaldigital.com/ *** NOTICE *** This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, 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 the sender at (919) 941-4645 and delete this e-mail message from your computer. Thank you.
RE: Need assistance with a 'VIEW'.
William, Views can't prompt the User for values. Maybe a stored function or procedure would be better? An application interface could store a User-provided Date in a public packaged variable, which could be referenced in the Where clause of the view. Do you want to Group By just Code1,Code2 or Group By Code1,Code2,Date? In other words, do you want just one resultant row representing the aggregate of all rows with Date InputDate, or a resultant row for every Date? We need more info. Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- Rogge Sent: Tuesday, September 18, 2001 10:47 AM To: Multiple recipients of list ORACLE-L I have been given the nice task of creating a view of some data in our database. This doesn't seem like a major task, but after 2 days of work, I am stumped. Given table 'table1' code1, code2, date, value1, value2 I need to produce a view showing code1, code2, sum(value1), sum(value2) where date 'user input date' I have not been able to get the view to prompt for the date to limit the sum by. Specifics: Oracle RDMBS 7.3.4 Server: Unix What else can I say, but I am totally stumped. HELP! -- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite 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: veritas backupexec horribly slow on one NT server
Jeffrey, What version(s) of Oracle? Are you using RMan? We use Veritas NetBackup Data Center 3.4GA because our colocation facility supplies it. We run Oracle8i 8.1.6 under Win2k (dual 500MHz CPUs, 2GB RAM) and see in the neighborhood of 300MB/min ( about 200GB of backup in about 10 hrs), using RMan to feed the hot backup files to the Veritas tape server. That's with nightly batch processes running against that database as well. This rate is pretty much "out of the box", since I've not tried to tune or optimize it in any way. Jack Jack C. ApplewhiteDatabase Administrator/DeveloperOCP Oracle8 DBAiNetProfit, Inc.Austin, Texaswww.iNetProfit.com[EMAIL PROTECTED](512)327-9068 -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Jeffrey BeckstromSent: Monday, September 17, 2001 10:35 AMTo: Multiple recipients of list ORACLE-LSubject: veritas backupexec horribly slow on one NT server Running backupexec 7.3 on 5 database servers. 4 of 5 run at 40-50 M/minute. One is running at only 20 M / minute. weird thing is that the slow one is running on our fastest box (2 - 1000 MHz processors). Any ideas. Veritas just says that the database is slow. While this is a development instance with all users currently locked out. Jeffrey BeckstromDatabase AdministratorGreater Cleveland Regional Transit Authority1240 W. 6th StreetCleveland, Ohio 44113(216) 781-4204
RE: ORA-1410 on interMedia Text Index - SOLVED
Hi, Just a follow-up for you interMedia Text fans out there. The statement below worked for us to eliminate the ORA-1410 and ORA-600 [12700] errors. Alter Table DR$Our_CLOB_Index$R Modify LOB (Data) (Cache); The interMedia Text Performance FAQ on TechNet recommends (Cache Reads) to speed up queries. That worked for us until we started deleting selected Documents. Now (Cache) gives us the large query performance boost we wanted, albeit with a significant, though acceptable (30-40%), increase in time taken for index re-syncs. Hope this helps someone. Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- Applewhite Sent: Wednesday, September 12, 2001 7:40 PM To: Multiple recipients of list ORACLE-L Hi, 8.1.6.0.0 on Win2k with interMedia Text index on table of 4 million CLOB documents. It's been in stable operation for over 8 months, with approx. 70,000 documents being added daily, approx. 10,000 deleted daily, and a sync of the index each day. We started getting ORA-1410 invalid ROWID for Contains queries and ORA-600 [12700] errors in the Alert log. This happened a few days after we altered the DR$index$R table to Cache Reads for its Data LOB column. Metalink Bug 1668041 indicates that changing Cache Reads to just Cache for the DR$...$R table solved the problem for an 8.1.7. WinNT DB. Has anyone else experienced this? I'm rebuilding the index now (for the 2nd time this week! - takes 7.25 hours) and want to know if this fix will work. Thanks for any advice or suggestions. Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite 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: how to zip email?
Andrea, Search for the file outlook.pst. That's the single file in which Outlook stores all emails, notes, addresses, everything. It's a pain, but what do you expect from Micros**t. Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- Oracle Sent: Monday, September 17, 2001 12:15 PM To: Multiple recipients of list ORACLE-L Hi all, This is not an Oracle question, but thought peoele on this list might know: I saved many emails about Oracle, Unix in the Microsoft Outlook, and I like to send these information esle where. I'd like to know where are these emails stored in the local or network directory, so I can make a zip. (NT 4.0, Microsoft Outlook 97). Thank you! Andrea -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite 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).
ORA-1410 on interMedia Text Index
Hi, 8.1.6.0.0 on Win2k with interMedia Text index on table of 4 million CLOB documents. It's been in stable operation for over 8 months, with approx. 70,000 documents being added daily, approx. 10,000 deleted daily, and a sync of the index each day. We started getting ORA-1410 invalid ROWID for Contains queries and ORA-600 [12700] errors in the Alert log. This happened a few days after we altered the DR$index$R table to Cache Reads for its Data LOB column. Metalink Bug 1668041 indicates that changing Cache Reads to just Cache for the DR$...$R table solved the problem for an 8.1.7. WinNT DB. Has anyone else experienced this? I'm rebuilding the index now (for the 2nd time this week! - takes 7.25 hours) and want to know if this fix will work. Thanks for any advice or suggestions. Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite 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: nvl not using index
BigP, You could have an If Then ElsIf...Else statement that tested the input arguments of interest (looks like p_loginid and p_firstname), each part having an appropriate Select that did not use NVL if that input argument was Not Null. In essence, you'd be tailoring your Ref Cursor to the input argument thatcaused the most useful index to be used. Right now it looks like just three Selectsmight do it. One if p_loginid was not null, one if p_firstname was not null, and one if both were null. Of course, dynamic SQL would work, too, but that can get pretty messy to write and maintain. Jack Jack C. ApplewhiteDatabase Administrator/DeveloperOCP Oracle8 DBAiNetProfit, Inc.Austin, Texaswww.iNetProfit.com[EMAIL PROTECTED](512)327-9068 -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Big PlanetSent: Monday, September 10, 2001 5:20 PMTo: Multiple recipients of list ORACLE-LSubject: nvl not using index Hi All , I am writing this proc does a searchin databasebased on these in parameters and returns a ref cursor . Now one more of these in parameters can be null and my query should return data neglecting null parameters . So I use nvl in the query as shown below . Now my problem is , the query doesnt use index available on table since i m using a function . Is there any way I can rewrite this query so that it meets my requirements and use the index . I have other option is to create a dynamic sql based on in parameters . TIA for any help . -BigP PROCEDURE get_alertlog ( p_loginidvarchar2 , p_startdate date, p_firstname varchar2 , p_enddate date , p_status out number, p_msg out varchar2, p_refcursor out c_refcursor ) Begin open p_refcursor for select logpin , logtype , logaction , logdate , memberpin FROM mem WHEREloginid =nvl(p_loginid , loginid ) AND logdate between nvl(p_startdate,to_date('1-jan-1900','dd-mon-') ) And firstname = nvl( p_firstname , firstname ) and nvl(p_enddate , sysdate ) ; End ;
RE: OOW presentations
Rachel, Feelings of cynicism with regards to anything Oracle are usually accurate. ;-) I presented at OOW '98 and found the same situation. The Oracle presentations I got into were pretty much marketing. The non-Oracle presentations were usually (not always) in the smallest rooms and were frequently packed by the time I got there. I ended up attending the Designer 2.x hands-on workshop 4 times, ignoring the multimedia presentation and playing around with Designer. That was kind of enlightening. Dancing to Big Bad Voodoo Daddy at the big bash was fun, except hardly any of the Geek Girls there knew how to Jitterbug. However, all in all, it wasn't worth the trip. That was my last OOW. Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- Carmichael Sent: Friday, September 07, 2001 9:07 AM To: Multiple recipients of list ORACLE-L Oracle's got the list of presenters and presentations up on the site of 287 presentations, 85 are by non-Oracle employees. Of the 202 presentations by Oracle, I wonder how many are just marketing hype? Ya know, if the company wasn't paying, and if one of my dearest friends didn't live in San Jose, I don't think I'd bother going. Rachel (feeling very cynical this morning) -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite 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: [1] oerr
Our Win2k 8.1.6 EE installation only has three *.msg files - one for DRG (interMedia) and two for hs (heterogeneous services). The DRG message file is truly ascii, but the hs *.msg files are really *.msb. All the RDBMS message files are *.msb - not a single *.msg. Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- Sent: Friday, September 07, 2001 2:21 PM To: Multiple recipients of list ORACLE-L I don't have an NT database in front of me now, but I thought it didn't have both the oraus.msg and oraus.msb files. Is that correct? Henry -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite 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: Kind of cool feature! DBMS_RANDOM
Anjan, Look in Oracle_Home/RDBMS/admin for the dbmsrand.sql file. The package is better documented there, at least for the 8.1.6 release. I used it last year to generate a Session ID for a Web-accessible application to maintain state across a User's multiple page hits. Jack Jack C. ApplewhiteDatabase Administrator/DeveloperOCP Oracle8 DBAiNetProfit, Inc.Austin, Texaswww.iNetProfit.com[EMAIL PROTECTED](512)327-9068 -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Anjan ThakuriaSent: Wednesday, September 05, 2001 5:26 PMTo: Multiple recipients of list ORACLE-LSubject: Re: Kind of cool feature! DBMS_RANDOMHi Chris, Pl excuse me for writing directly. I looked up this package and there is no mention of the STRING option at all. Metalink search returned no hits. Could you please send me the location where you got the information from. Thanks in advance Anjan Christopher Spence wrote: In 8.1.6 Oracle added a new feature which I don't believe is very well documented, it is great. DBMS_RANDOM.STRING([OPT], [LEN]); This will create a random string with a length of up to 60 characters. Great for force populating tables. The opt is for things like L, U, M (Lower, Upper, Mixed case), there are a few different options. One thing I did to force populate a table is: DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(5, 10)); Which creates random string with random length between 5 and 10 characters. Run 10,000,000 times, I got a table with 30 columns and 10,000,000 random rows in no time. "Do not criticize someone until you walked a mile in their shoes, that way when you criticize them, you are a mile a way and have their shoes." Christopher R. Spence Oracle DBA Phone: (978) 322-5744 Fax: (707) 885-2275 Fuelspot 73 Princeton Street North, Chelmsford 01863
RE: Stored Procedure Performance Problem --- Please Help
Title: Stored Procedure Performance Problem --- Please Help Viral, Are you saying your entire database is on one 13GB drive? If so, it's no wonder this operation takes a very long time - you're I/O bound in a big way! The inserts and updates of tables and indexes, not to mention disk sorts in the Temp segment, as well as rollback, redo, etc. are thrashing the heck out of that drive! If you have multiple 13GB drives, then you need to tell us more about table, index and tablespace distribution. Jack Jack C. ApplewhiteDatabase Administrator/DeveloperOCP Oracle8 DBAiNetProfit, Inc.Austin, Texaswww.iNetProfit.com[EMAIL PROTECTED](512)327-9068 -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Viral AminSent: Friday, August 31, 2001 9:00 AMTo: Multiple recipients of list ORACLE-LSubject: Stored Procedure Performance Problem --- Please Help Hi All, Hardware : Dell server - 256MB RAM, 13GB Hard Disk, Single CPU Software : Oracle 8.1.7 (Non-parallel server option) OS : Windows NT 4.0 SP6 BackGround: Following is the table structure and record count ... Regards Viral Amin
RE: 8.1.7 on W2K
Uma, Check your registry to ensure that the key ORA_sid_AUTOSTART is set to TRUE. That will start the instance after the Service starts. It's documented for 8.1.6 in Appendix B Oracle8i Configuration Parameters and the Registry in Oracle8i Client Administrator's Guide Release 2 (8.1.6) for Windows. There you'll also find out other goodies, like how to set up for the instance to Shutdown Immediate when the server is shutdown. Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- Sent: Thursday, August 30, 2001 12:04 PM To: Multiple recipients of list ORACLE-L Guys, I have an just finished upgrading to 8.1.7 on all my boxes some of which are Windows2000. I have had no problems with Sun Solaris, HP and Windows NT4.0. But on Windows2000, every time the box is rebooted the instance does not come up automatically. However the service shows as running. I checked the registry to make sure I had ORACLE_SID set right. I have to manually start the instance every time and its a pain in the 'you know what'. If anyone has had this problem and solved it please let me know the solution. Thanks, Uma Mohoni Consultant, CDI Corporation @ iKimbo Inc. 500-A Huntmar Park Drive Herndon, VA 20170 (703) 904-4150 Ext:237 [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite 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: Auto log apply to standby db using Perl
Rob (Jo?), This is from the Oracle8i docs (Chapter 5 of "Oracle8i Standby Database Concepts and Administration"), but it's so basic that I believe it would apply to 8.0 ... 9.Write a script that you can periodically run to check the log files in the managed recovery directory and move the log files that have a specified timestamp to the manual recovery directory. If new redo logs are being moved, start the manual recovery mode and apply the newly moved redo logs. The following PERL script performs what is outlined in this step: #!/usr/local/bin/perl #How many hours the standby database should lag behind the primary database $LAG_HOUR = 4; #The manual recovery directory $DEST_DIR = '/fs2/oracle/stdby/'; #The flag for whether there are new logs to be applied. $needApply = 0; #Check the managed recovery directory while ( /fs2/oracle/stby_log/*.arc ) { # Get the timestamp of the file $file_time = (stat($_))[9]; # See if the file is "old enough" if ( time-$file_time $LAG_HOUR*60*60 ) { print "mv $_ $DEST_DIR\n"; system "mv $_ $DEST_DIR"; $needApply = 1; } } #If redo logs were moved in this round, apply them if ( $needApply == 1 ) { system "/usr/Lagged_Standby/ApplyLog"; } The SHELL script (/usr/Lagged_Standby/ApplyLog) used to apply the redo logs consists of the following: sqlplus internal EOF recover automatic standby database; cancel exit EOF 10.Refer to your platform-specific documentation for information on how to create a job that is triggered at specific times throughout the day. For example, in UNIX, you can write a CRON job file. Issue the man crontab command at the UNIX command shell to get help on how to write a CRON job file. ... Jack Jack C. ApplewhiteDatabase Administrator/DeveloperOCP Oracle8 DBAiNetProfit, Inc.Austin, Texaswww.iNetProfit.com[EMAIL PROTECTED](512)327-9068 -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Jo KingSent: Wednesday, August 29, 2001 1:23 PMTo: Multiple recipients of list ORACLE-LSubject: Auto log apply to standby db using Perl Environment is NT4 with Oracle 8.0.5. I wish to automate the procedure for applying the archive logs to a standby database. The logs are automatically transferred to standby destination 'area'. I am planning to use Perl (currently learning) to performthis. After gaining connectivity to database via svrmgr30 and issuing command : - recover automatic standby database; I'm NOT surehow to go aboutcoding ( 'stacking') responses to.. "Archive file not found message..". when there are no longer any more archive files to apply. I wish to respond cancel to the standard message : - Enter RET...CANCEL and then exit out of svrmgr30. If anybody has a Perl script for handling such a situation (or similar) or could give me an exampleon how to achieve, I'd be very grateful. /Rob
RE: Flat File vs. Database
Dennis, I'm stunned... How about: - Data Integrity (Referential Integrity and all the other constraints on data) - Concurrency (Readers don't block Writers and vice versa) - Scalability - Accessibility (no 3rd party tools could ever access your flat files for analysis or reporting) - Security You're dang right it's an no-brainer! ;-) Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- [EMAIL PROTECTED] Sent: Wednesday, August 29, 2001 4:46 PM To: Multiple recipients of list ORACLE-L Hi all, We are proposing a project to convert applications that use flat files to Oracle databases. An essential piece of this proposal is to present to the management the advantages of databases over flat files. Are there any websites, whitepapers that talk about this? I know this probably is a no-brainer for most of you but we just want to have all of our ducks in a row so that we can make a strong case. TIA Dennis Meng Database Administrator Focal Communications 847-954-8328 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite 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:
The number of ORACLE.EXE processes in Task Manager indicate the number of Oracle Services started. Those Oracle instances might not yet be started, however. You'd have to check that via connecting with SQL*Plus. You can start up and shut down and Oracle instances and the ORACLE.EXE processes will remain in Task Manager - though Mem Usage may vary drastically, depending on the SGA settings - until you stop the Services. I'm not familiar with Oracle Parallel Server, so I can't for sure say that multiple Oracle instances on the same NT server could mount the same database. If that can't happen, then each Oracle process would indeed correspond to an Oracle database. Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- Weatherburne Sent: Thursday, August 23, 2001 12:41 PM To: Multiple recipients of list ORACLE-L Subject: Hi Again DBA's We are running Oracle 8.0.5 on NT 4. I read that Oracle is implemented as one multi-threaded process on NT. I observe two ORACLE80.EXE proceses running in the Task Manager. There are also two STRTDB80.EXE processes running as well. Does the number of ORACLE.EXE processes represent the number of databases that are open? Thanks in advance for your input! Denmark Weatherburne -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite 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: Stored procedures that return multiple rows
Paul, For acouple of projects that I've worked on, several Java programmers have liked having Ref Cursors returned from PL/SQL stored procedures. They could work with those much more easily than PL/SQL tables. Can't remember if we even tried VArrays. I don't think I'd like them for returning table data - too much work to populate them. Jack Jack C. ApplewhiteDatabase Administrator/DeveloperOCP Oracle8 DBAiNetProfit, Inc.Austin, Texaswww.iNetProfit.com[EMAIL PROTECTED](512)327-9068 -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Paul BaumgartelSent: Thursday, August 23, 2001 3:11 PMTo: Multiple recipients of list ORACLE-LSubject: Stored procedures that return multiple rows We're considering a mandate that all database access be via stored procedures (probably in packages). These would becalled eithervia OCCI (the C++ call interface) or JDBC. Myquestion is whether anyone's had experience in returninga result set from a PL/SQL procedure under thesecircumstances, and how it was implemented:did you return a ref cursor, an index-by table, a set of arrays? Any advice will be appreciated. Thanks! Paul Baumgartel MortgageSight Holdings, LLC [EMAIL PROTECTED]
RE: CLOBs storage vs Varchar storage
Rick, If the CLOB is 4KB and stored inline with the table, then it takes up only as much space as it needs - much like a VarChar2. If the CLOB is stored out-of-line in a LOB segment, it takes at least one, possibly many more, Oracle DB Blocks, depending on your specification of Chunk in the Create Table command. Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- T (Richard) Sent: Monday, August 13, 2001 12:50 PM To: Multiple recipients of list ORACLE-L Hi, I am resending this question as I cannot find the answer CLOBs vs VARCHAR. If a varchar datatype is not completely used it will not allocate storage for what is was defined. Does a CLOB data type use entire storage if not completely used? Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite 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: For those who got Code Red in the face
Our webserver got hit a couple of weeks ago. It got cleaned up and the security patch(es) applied. I thought nothing more about it. However, I think it or a variant got three of our other Win2k servers that don't run IIS at all. Yesterday I found a strange process, VMGR32.exe, chewing up 50% CPU on our production db server. The file, in C:\WinNT\System32, was dated 07/30/2001 08:40pm. Another file, acer4.exe, of exactly the same size, 272KB, had exactly the same datetime. Neither file shows the usual Version tab in the Properties window (after right click on the file). I searched the Microsoft site and did a Google search on both, with zero hits. Suspicious... I checked out http://www.net-security.org/text/articles/coverage/code-red/ but couldn't see any similarities until it suggested running netstat -an to see if your server was connecting to dozens of random IP addresses at port :80. I did and ours was! I changed the service Remote Administration Service (which loads VMGR32.exe) to Manual and rebooted the servers. The connections to random IP addresses at port :80 have stopped and VMGR32.exe is no longer running as a process. I also installed Win2k Service Pack 2. I hope I've squashed this worm! Have I? Are the port :80 connections and VMGR32.exe related or have I been chasing the wrong culprit? The NT sysadmin at our colocation facility isn't a lot of help (one reason we're looking to switch pretty soon!), so I'm kind of at a loss. Any suggestions? Thanks. Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- [EMAIL PROTECTED] Sent: Monday, August 06, 2001 2:24 PM To: Multiple recipients of list ORACLE-L New worm targets same systems as Code Red Security analysts warned that a new and potentially dangerous worm began circulating over the weekend, targeting the same Windows-based servers as the high-profile Code Red worm. http://computerworld.com/nlt/1%2C3590%2CNAV47_STO62834_NLTAM%2C00.html -- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite 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: View Help
Venkata, Yes. Create a public variable in a PL/SQL package stored in the database. Reference that public variable in the where clause of your view and populate that variable before you select from the view. Since each session gets its own instantiation of the packaged variable, each session uses a customized version of the view. It's a handy technique! BTW, create the variable with a default value (Null or some real value) such that the view will work as desired even if the variable is not explicitly assigned a value. Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- Subramanian Sent: Wednesday, August 01, 2001 8:22 AM To: Multiple recipients of list ORACLE-L Dear All, Is it possible to create a view where I will pass the condition to the where clause dynamically. eg) Create view v1 as select ename,empno,sal from emp where deptno=:a The value of 'a' I will pass the value dynamically when I do the select. eg) select * from v1. Now I'll pass the value say 20. Is this possible Any suggestions or workaround for this. TIA Regards Venkata Get 250 color business cards for FREE! http://businesscards.lycos.com/vp/fastpath/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: C.S.Venkata Subramanian INET: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite 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: retrieving BLOB column..
Saurabh, Check out the DBMS_LOB package. There are lots of procedures and functions for manipulating BLOBs and CLOBs. Jack Jack C. ApplewhiteDatabase Administrator/DeveloperOCP Oracle8 DBAiNetProfit, Inc.Austin, Texaswww.iNetProfit.com[EMAIL PROTECTED](512)327-9068 -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Saurabh SharmaSent: Tuesday, July 24, 2001 8:10 AMTo: Multiple recipients of list ORACLE-LSubject: retrieving BLOB column.. hi all, do any one tell me how can i retrieve from a blob column. these will be displayed in browser through asp page. r there any functions used to retrieved records(jpg files, gif) from a blob. Saurabh Sharma [EMAIL PROTECTED]http://www.geocities.com/saurabh00pc/stride.html
RE: waits on sequential scans - how did i solve it
Rahul, If I'd known that there were only 8 columns in the table, I'd have included the recommendation to investigate an Index-Organized Table. That is where the table IS the index and the index IS the table. This saves disc space and cuts I/O in half for DML on the table, since a separate index is not maintained. Check out the docs on IOTs. Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- Sent: Tuesday, July 24, 2001 8:53 AM To: Multiple recipients of list ORACLE-L list, based on the recommendation of posters (Jack) , i re-created the index with all the columns of a table ! (all 8 of them) analyzed the table/index and now all the queries are satisfied off an indexed range scan.. i also put the indexes on raw devices. regards -- From: Jack C. Applewhite[SMTP:[EMAIL PROTECTED]] Reply To: [EMAIL PROTECTED] Sent: Tuesday, July 17, 2001 8:25 PM To: Multiple recipients of list ORACLE-L Subject: RE: how to improve sequential scans ? Rahul, Could this table be partitioned and the partitions spread across multiple disks? Could the index be partitioned as well? The concept here is, of course, divide and conquer. Could a column or two (or three) be added to the index to satisfy the query without having to hit the table? How frequently are these literal queries being issued? Are they shredding your shared pool and chewing up CPU by making Oracle do extra work in shared pool memory management? Are there aggregation (vs aggravation g) functions or order bys in the queries that might be causing sorts to disk? If aggregation, could you use materialized views to satisfy the queries? ...just a few ideas. Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- Sent: Tuesday, July 17, 2001 7:36 AM To: Multiple recipients of list ORACLE-L list (AIX, 7.3.2) 5 clients are shooting the same sql to read data from an 18 million rows table. each time the sql uses a different literal value in the where clause... no bind variables. I CANNOT TOUCH THE APPLICATION, and have been given the task to re-configure the DB to increase performance. i have moved the table and it;s associated index to separate disks. and iostat show that only that only those two disks are being read. the session wait show that all the times the sessions are waiting on db file sequential read the db file being sequentially read in the above sessiion is the TABLE from which all the sid's are reading the table is analyzed and the sql's issued use the index. how can i further tune this config. ? TIA Rahul PS: my next step is to put the files on raw disks. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite 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: RMAN
Joe, The Oracle-supplied "Oracle8i Recovery Manager User's Guide and Reference" has served me well. I'd advise experimenting with the various backup and recovery options and RMan commands on a little test DB to get the hang of it. RMan works very nicely. It's been much more reliable than the Veritas Netbackup tape backend. Jack Jack C. ApplewhiteDatabase Administrator/DeveloperOCP Oracle8 DBAiNetProfit, Inc.Austin, Texaswww.iNetProfit.com[EMAIL PROTECTED](512)327-9068 -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of JOE TESTASent: Monday, July 23, 2001 10:29 AMTo: Multiple recipients of list ORACLE-LSubject: RMAN Well looks like i've held off learning RMAN long enough, any pointers, good RTFM, etc. thanks, joe
RE: Re[2]: security problem with 8i
Oh yeah! I've got one even better! When I joined a previous company, their *Web-accessible* application's administration username/password was admin/admin! Their production Oracle DB - accessed via the admin/admin protected app - had system/manager and mps/mps (mps stands for Main Production Schema), plus all the usual default schemas like ctxsys/ctxsys... Needless to say, I closed those holes pretty quickly! Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- Carmichael Sent: Wednesday, July 18, 2001 11:13 AM To: Multiple recipients of list ORACLE-L I would doubt he's joking. I've had simular experiences transferred to another department within the same company. Get a call from my old boss our dba is out sick, we HAVE to have this done today, this is a highly secured system you have to help and make the changes from this pc I go there, cannot log into the database with the username and password he gives me. We call the dba (who was really sick), apologize and ask for the username and password -- same as what I had. Still does not work. I stop, think and say let me try something and log in as system/manager I do what they ask me to, then take my old boss aside and explain (gently) that he has a security hole in his highly secured system that I could drive a truck through. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite 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).