How to keep root out?
Just for grins, I'll ask this question... Is there any way to keep the Unix "root" user from logging into the database (i.e. connect internal or / as sysdba)? Currently using 8.1.7.4 on Solaris 8 here. We have a couple people in our Unix admin group that feel the need to "help" by writing their own DB monitoring scripts. Of course, they don't know what they're talking about. They do not have formal logins for the database, but since they are root users they are connecting via "connect internal". This is not only counterproductive but actually a potential security issue--just because someone has root doesn't necessarily entitle them to see the data in the database. What if it is a payroll database? So, I'm curious,is there any way to prevent access via "connect internal" or "/ as sysdba"? Thanks in advance. W
RMAN - Remote vs Local Backups
Hi, Can anyone think of a reason(s) why oneWOULD want to backup a database from a box other than the database box itself? Are there any advantages to this kind of configuration? For example: Box-A (production db server) Box-B (rman db server) A cron job runs on Box-B which backups upthe databasefrom Box-A. Thanks in advance! -w
RE: RMAN - Remote vs Local Backups
Thanks Tim, Dennis and Ron for your feedback. I appreciate it. Let me clarify what I'm seeking. In my example, I am using a centralized catalog which is on its own dedicated database/server and backups are to tape. BCV's are not involved. Normally, in my experience, RMAN backups are initiated from the target server via a cron job. But, I've seen a case where a cron job for an RMAN backup was run from a box that was different from the database server machine. I find this configuration strange and confusing because it implies this was done for a "reason" and makes life difficult to find out where all the backups are running from. In the scenario of backing up the database on box A via an rman/cron job on box B,is this particular configuration more network resource intensive and therefore slower versus the backup being initiated from the same machine as the database? If not, could someone explain why? Does this make sense? Thanks again. -w DENNIS WILLIAMS [EMAIL PROTECTED] wrote: Walter - As RMAN was introduced in Oracle8i, that was the ideal. I thinkOracle viewed RMAN as a high-level feature that would help you manage thebackups for large server farms. They emphasized that the catalog was the wayto go. With the catalog on another box, if the server was toasted, you couldslide another system into that spot and with a couple of RMAN commands youcould have that up and going again. Obviously if you use the catalog methodon the box you are backing up, you must have a second instance, and eventhen you introduce more vulnerabilities than the configuration where thecatalog is on another server.With Oracle9i, Oracle added many of the features that were onlyavailable in the catalog method to the control-file method. According to myOracle Education Instructor John Hibbard who is pretty plugged into thesethings, Oracle is trying ! to emphasize that the catalog method may not suiteveryone's situation and the control file method may best suit your needs.As others on this list have pointed out, not all conference speakers havegotten that message.Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message-Sent: Thursday, May 29, 2003 11:55 AMTo: Multiple recipients of list ORACLE-LHi,Can anyone think of a reason(s) why one WOULD want to backup a database froma box other than the database box itself? Are there any advantages to thiskind of configuration?For example:Box-A (production db server)Box-B (rman db server)A cron job runs on Box-B which backups up the database from Box-A.Thanks in advance!-w-- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: DENNIS WILLIAMSINET: [EMAIL PROTECTED] OMFat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California -- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
RE: RMAN - Remote vs Local Backups
In my scenario, target DB to be backed up on box A and rman database/catalog on Box B, I can "run" the backup from A or from B no problem. But, what I'm trying to get at is it better, worse or indifferent to "run" the backup from B or A. I would think there is extra network traffic to "run" the backup from B.DENNIS WILLIAMS [EMAIL PROTECTED] wrote: Walter - What you describe is the standard RMAN configuration. Box Bcontains the RMAN catalog, therefore it must command the backup. And so thecron job must run on Box B. But the actual backup occurs on the targetmachine (A in your example). If you back up to tape, you must have an MML(Media Management Library). You can also back up to disk (that is what Ido).Since the actual backup occurs on the target machine, not much networktraffic is involved. RMAN sends some commands, the target sends some statusback, and that is about it.Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message-Sent: Thursday, May 29, 2003 4:30 PMTo: Multiple recipients of list ORACLE-LThanks Tim, Dennis and Ron for your feedback. I appreciate it.Let me clarify what I'm seeking! . In my example, I am using a centralizedcatalog which is on its own dedicated database/server and backups are totape. BCV's are not involved.Normally, in my experience, RMAN backups are initiated from the targetserver via a cron job. But, I've seen a case where a cron job for an RMANbackup was run from a box that was different from the database servermachine. I find this configuration strange and confusing because it impliesthis was done for a "reason" and makes life difficult to find out where allthe backups are running from.In the scenario of backing up the database on box A via an rman/cron job onbox B, is this particular configuration more network resource intensive andtherefore slower versus the backup being initiated from the same machine asthe database? If not, could someone explain why?Does this make sense?Thanks again.-wDENNIS WILLIAMS <[EMAIL PROTECTED]>wrote:Walter - As RMAN was introduced in Oracle8i, that was the ideal. I thinkOracle viewed RMAN as a high-level feature that would help you manage thebackups for large server farms. They emphasized that the catalog was the wayto go. With the catalog on another box, if the server was toasted, you couldslide another system into that spot and with a couple of RMAN commands youcould have that up and going again. Obviously if you use the catalog methodon the box you are backing up, you must have a second instance, and eventhen you introduce more vulnerabilities than the configuration where thecatalog is on another server.With Oracle9i, Oracle added many of the features that were onlyavailable in the catalog method to the control-file method. According to myOracle Education Instructor John Hibbard who is pretty plugged into thesethings, Oracle is trying ! to emphasize that the catalog method may not suiteveryone's situation and the c! ontrol file method may best suit your needs.As others on this list have pointed out, not all conference speakers havegotten that message.Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message-Sent: Thursday, May 29, 2003 11:55 AMTo: Multiple recipients of list ORACLE-LHi,Can anyone think of a reason(s) why one WOULD want to backup a database froma box other than the database box itself? Are there any advantages to thiskind of configuration?For example:Box-A (production db server)Box-B (rman db server)A cron job runs on Box-B which backups up the database from Box-A.Thanks in advance!-w-- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: DENNIS WILLIAMSINET: [EMAIL PROTECTED] OMFat City Network Services -- 858-538-5051 http://www.fatcity.comSan! Diego, California -- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).-- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: DENNIS WILLIAMSINET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California -- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BO! DY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
Re: RMAN Reporting?
Ha ha. You tell me how to group the records together so they can be added, electronically or manually, and I will be glad to do so... Ruth Gramolini [EMAIL PROTECTED] wrote: Add them together! Or are we so in need of a technical solution that weforget the pencil and paper?Ruth- Original Message -To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>Sent: Wednesday, December 11, 2002 2:30 PM Yes. The completion_time is available for the backup piece (BP) and the backup set (BS) but not for the entire backup. That would be fine if there was a way of relating the "pieces" or "sets" to a single backup but I see no way of doing this. --- Original Message --- To: Multiple recipients of list ORACLE-L <ORACLE- [EMAIL PROTECTED] Did you try running a query against rman.bp. It has a column called completion_time. Ruth - Original Message - To: "Mul! tiple recipients of list ORACLE-L" <ORACLE- [EMAIL PROTECTED] Sent: Wednesday, December 11, 2002 1:11 PMIs there no way to produce a report from the RMAN views/tables that lists the date and duration of the backups for each database? I have pored through the RC views and can produce such a report at the backup set level but not at the database level. Am I missing something? Any suggestions? Thanks. -w -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Walter K INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Dieg! o, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ruth Gramolini INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services -- --- To REMOVE yourself from this mailing list, send an E- Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Walter K INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from th! is mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Ruth GramoliniINET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California -- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HEL! P command for other information (like subscribing).
RMAN Reporting?
Is there no way to produce a report from the RMAN views/tables that lists the date and duration of the backups for each database? I have pored through the RC views and can produce such a report at the backup set level but not at the database level. Am I missing something? Any suggestions? Thanks. -w -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Walter K INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: RMAN Reporting?
Sure. If I have three databases (AAA, BBB and CCC), I would like to see a report that says database AAA was backed up on 10-DEC-2002 starting at 21:00 and ended at 23:07. I can produce such a report if AAA was ONLY backed up once for a given day but not if it was backed up multiple times in a day. This seems like a pretty basic report a lot of people would want but it doesn't look like the schema was designed with much in the way of reporting/trending in mind. Thanks. -w --- Original Message --- To: Multiple recipients of list ORACLE-L ORACLE- [EMAIL PROTECTED] Walter - Can you explain your goal in a little more detail? RMAN has some standard reports for listing the date of backups. What do you mean by duration? The time interval that RMAN was backing up your data? I doubt that RMAN stores that, probably just the completion time. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, December 11, 2002 12:11 PM To: Multiple recipients of list ORACLE-L Is there no way to produce a report from the RMAN views/tables that lists the date and duration of the backups for each database? I have pored through the RC views and can produce such a report at the backup set level but not at the database level. Am I missing something? Any suggestions? Thanks. -w -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Walter K INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services -- --- To REMOVE yourself from this mailing list, send an E- Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services -- --- To REMOVE yourself from this mailing list, send an E- Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Walter K INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: RMAN Reporting?
Yes. The completion_time is available for the backup piece (BP) and the backup set (BS) but not for the entire backup. That would be fine if there was a way of relating the pieces or sets to a single backup but I see no way of doing this. --- Original Message --- To: Multiple recipients of list ORACLE-L ORACLE- [EMAIL PROTECTED] Did you try running a query against rman.bp. It has a column called completion_time. Ruth - Original Message - To: Multiple recipients of list ORACLE-L ORACLE- [EMAIL PROTECTED] Sent: Wednesday, December 11, 2002 1:11 PM Is there no way to produce a report from the RMAN views/tables that lists the date and duration of the backups for each database? I have pored through the RC views and can produce such a report at the backup set level but not at the database level. Am I missing something? Any suggestions? Thanks. -w -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Walter K INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ruth Gramolini INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services -- --- To REMOVE yourself from this mailing list, send an E- Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Walter K INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: RMAN Reporting?
I agree. I can get what I want by looking at time stamps on log files, grepping, etc. but that can be a lot of work to put together. I have a need to do some trending analysis and was expecting that I could get this info out of the RMAN tables but it's looking like that's just wishful thinking. -w --- Original Message --- To: Multiple recipients of list ORACLE-L ORACLE- [EMAIL PROTECTED] Perhaps looking at the last update time on the log files? And I assume you know what time the backup started ... ? Is there no way to produce a report from the RMAN views/tables that lists the date and duration of the backups for each database? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephen Lee INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services -- --- To REMOVE yourself from this mailing list, send an E- Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Walter K INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Staspack Grapher/Viewer ?
Does anyone have or know of any utilities, preferably freeeware or very cheap, that can produce graphs of the data collected by statspack? Thanks VERY much in advance. -walt -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Walter K INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
v$sort_usage
Hi, Could someone shed some light on how v$sort_usage is populated? It is supposed to show active sorts in the database. If that is the case then I don't understand why I see the following: 1. An entry exists for a user/sort, yet according to v$session the session is NOT active. 2. Multiple entries exist for a user with the SAME session_addr. I don't understand how a session could have multiple sort segments. As always, you're feedback is appreciated. Thanks. -w -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Walter K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Varrays as Lobs Tablespace Changing
Some VARRAY questions... 1. How do I determine a VARRAY data type within the database--just simply an odd name for a datatype (i.e. O_EXT_XYZ...) ? 2. If I have a VARRAY that has been stored as a LOB, is there a way I can change the tablespace on the underlying LOB via an ALTER TABLE...MOVE...? What I've read so far says I can't change the LOB storage parameters in this case. Thanks! -w -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Walter K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Mysterious Deadlock
Thanks for the link. The example shown at the bottom of the article looks like an unindexed foreign key issue rather than an initrans/pctfree issue. What's really strange about my deadlock is that it shows no other session waiting on the lock except itself. Originally, I thought the deadlock we were encountering was a lack of ITL slots based on all the articles I had read at the time but when I attempt to reproduce the ITL shortage scenario I always get what looks like a traditional deadlock (X waiting for X) when I deliberately deprive a block of ITL slots. I'm open to ideas, suggestions... Thank you. -w --- Original Message --- To: Multiple recipients of list ORACLE-L ORACLE- [EMAIL PROTECTED] http://www.dbdomain.com/a120197.htm it's down near the end of the article, but they talk about a deadlock with no rows and say it appears to be a problem with the initrans or pctfree setting on that table --- Walter K [EMAIL PROTECTED] wrote: We have an application that is periodically encountering what appears to be a self-deadlock. Only one session is listed and it holds an exclusive (X) lock and is waiting for a share (S) lock with NO ROWS waited. I have pasted the deadlock graph at the bottom- -hopefully it will be legible. Does anyone know how such a lock could be produced? I'd really like to be able to recreate such a scenario. Thanks! -w Current SQL statement for this session: update ACCOUNT set ACCOUNT_ID=:ACCOUNT_ID,ACCOUNT_NAME=:ACCOUNT_NAME,LAST_ MODIFIED=:LAST_MODIFIED,CUSTOMER_NODE_ID=:CUSTOMER_NODE _ID,ACCOUNT_TYPE_ID=:ACCOUNT_TYPE_ID,ACCOUNT_BALANCE=:A CCOUNT_BALANCE,BALANCE_DATE=:BALANCE_DATE,INVOICE_ID=:I NVOICE_ID,PREVIOUS_INVOICE_ID=:PREVIOUS_INVOICE_ID,CRED IT_LIMIT=:CREDIT_LIMIT,UNBILLED_AMOUNT=:UNBILLED_AMOUNT ,ACCOUNT_ACTION_CODE=:ACCOUNT_ACTION_CODE,ACCOUNT_AC TIO N_DATE=:ACCOUNT_ACTION_DATE where ACCOUNT_ID=:key_ACCOUNT_ID The following deadlock is not an ORACLE error. It is a deadlock due to user error in the design of an application or from issuing incorrect ad-hoc SQL. The following information may aid in determining the deadlock: Deadlock graph: -Blocker(s) --- --Waiter(s)- Resource Name process session holds waits process session holds waits TX-0007004c-26bf34 95 X 34 95 S session 95: DID 0001-0025-0002E096 session 95: DID 0001-0025-0002E096 Rows waited on: Session 95: no row -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Walter K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists -- -- To REMOVE yourself from this mailing list, send an E- Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Walter K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Mysterious Deadlock
Jack, Thanks for the reply. The table is defined as: INI_TRANS = 1 PCT_FREE = 10 I meant to mention in my original posting that the platform is Solaris 8, Oracle 8.1.7.0/32-bit. I don't know what the exact number of concurrent transactions is, probably no more than a few (1-3). The deadlock does not occur all the time. What's even stranger, is that the error can show up in the alert log at different times yet with the SAME pid for the trace file name--for example, today might be prod_ora_12345.trc showing up at different times in the alert log and next week Monday it might show prod_ora_67890.trc multiple times. Did your deadlocks show up as no rows and only one line (S waiting for X) in the deadlock graph? Thanks again. -w --- Original Message --- To: Multiple recipients of list ORACLE-L ORACLE- [EMAIL PROTECTED] Walter, we had a situation where our pctfree was 0, our initrans was 4, and we were trying to update the table using 8 concurrent processes. Since we had no space to grow, our ITL could not expand, and some of the processes deadlocked with a similiar error. What is your pctfree and initrans? how many procs are trying to insert/delete/update it at once? are ALL dying, or just some? jack silvey --- Walter K [EMAIL PROTECTED] wrote: We have an application that is periodically encountering what appears to be a self-deadlock. Only one session is listed and it holds an exclusive (X) lock and is waiting for a share (S) lock with NO ROWS waited. I have pasted the deadlock graph at the bottom- -hopefully it will be legible. Does anyone know how such a lock could be produced? I'd really like to be able to recreate such a scenario. Thanks! -w Current SQL statement for this session: update ACCOUNT set ACCOUNT_ID=:ACCOUNT_ID,ACCOUNT_NAME=:ACCOUNT_NAME,LAST _ MODIFIED=:LAST_MODIFIED,CUSTOMER_NODE_ID=:CUSTOMER_NOD E _ID,ACCOUNT_TYPE_ID=:ACCOUNT_TYPE_ID,ACCOUNT_BALANCE=: A CCOUNT_BALANCE,BALANCE_DATE=:BALANCE_DATE,INVOICE_ID=: I NVOICE_ID,PREVIOUS_INVOICE_ID=:PREVIOUS_INVOICE_ID,CRE D IT_LIMIT=:CREDIT_LIMIT,UNBILLED_AMOUNT=:UNBILLED_AMOUN T ,ACCOUNT_ACTION_CODE=:ACCOUNT_ACTION_CODE,ACCOUNT_ACTI O N_DATE=:ACCOUNT_ACTION_DATE where ACCOUNT_ID=:key_ACCOUNT_ID The following deadlock is not an ORACLE error. It is a deadlock due to user error in the design of an application or from issuing incorrect ad-hoc SQL. The following information may aid in determining the deadlock: Deadlock graph: -Blocker(s) --- --Waiter(s)- Resource Name process session holds waits process session holds waits TX-0007004c-26bf34 95 X 34 95 S session 95: DID 0001-0025-0002E096 session 95: DID 0001-0025-0002E096 Rows waited on: Session 95: no row -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Walter K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists -- -- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists -- -- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists -- -- To REMOVE yourself from this mailing list, send an E- Mail message to: [EMAIL PROTECTED] (note
Re: Mysterious Deadlock
I checked out ixora a few weeks ago when this problem surfaced and saw the article you are probably referring to but that was a library cache lock and doesn't look to be the same as what I'm experiencing. I sure wish I knew how to read the trace files like Steve Adams can as well as I wish I knew how to use the various events and levels like he does... :( -w --- Original Message --- To: Multiple recipients of list ORACLE-L ORACLE- [EMAIL PROTECTED] I remember having this problem a long time ago and finding an explanation on www.ixora.com.au I don't have time to search my notes at the moment but if I can, I'll do it a bit later. Henry - Original Message - To: Multiple recipients of list ORACLE-L ORACLE- [EMAIL PROTECTED] Sent: Thursday, June 06, 2002 2:48 PM http://www.dbdomain.com/a120197.htm it's down near the end of the article, but they talk about a deadlock with no rows and say it appears to be a problem with the initrans or pctfree setting on that table --- Walter K [EMAIL PROTECTED] wrote: We have an application that is periodically encountering what appears to be a self-deadlock. Only one session is listed and it holds an exclusive (X) lock and is waiting for a share (S) lock with NO ROWS waited. I have pasted the deadlock graph at the bottom- -hopefully it will be legible. Does anyone know how such a lock could be produced? I'd really like to be able to recreate such a scenario. Thanks! -w Current SQL statement for this session: update ACCOUNT set ACCOUNT_ID=:ACCOUNT_ID,ACCOUNT_NAME=:ACCOUNT_NAME,LAST_ MODIFIED=:LAST_MODIFIED,CUSTOMER_NODE_ID=:CUSTOMER_NODE _ID,ACCOUNT_TYPE_ID=:ACCOUNT_TYPE_ID,ACCOUNT_BALANCE=:A CCOUNT_BALANCE,BALANCE_DATE=:BALANCE_DATE,INVOICE_ID=:I NVOICE_ID,PREVIOUS_INVOICE_ID=:PREVIOUS_INVOICE_ID,CRED IT_LIMIT=:CREDIT_LIMIT,UNBILLED_AMOUNT=:UNBILLED_AMOUNT ,ACCOUNT_ACTION_CODE=:ACCOUNT_ACTION_CODE,ACCOUNT_ACT IO N_DATE=:ACCOUNT_ACTION_DATE where ACCOUNT_ID=:key_ACCOUNT_ID The following deadlock is not an ORACLE error. It is a deadlock due to user error in the design of an application or from issuing incorrect ad-hoc SQL. The following information may aid in determining the deadlock: Deadlock graph: -Blocker(s) --- --Waiter(s)- Resource Name process session holds waits process session holds waits TX-0007004c-26bf34 95 X 34 95 S session 95: DID 0001-0025-0002E096 session 95: DID 0001-0025-0002E096 Rows waited on: Session 95: no row -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Walter K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists -- -- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Henry Poras INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists -- -- To REMOVE yourself from this mailing list, send an E- Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Walter K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San
Re: Mysterious Deadlock
All indexes on the table involved have INI_TRANS=2 and PCT_FREE=10. --- Original Message --- To: Multiple recipients of list ORACLE-L ORACLE- [EMAIL PROTECTED] What's the initrans for the indexes involved ? Anjo. Walter K wrote: We have an application that is periodically encountering what appears to be a self-deadlock. Only one session is listed and it holds an exclusive (X) lock and is waiting for a share (S) lock with NO ROWS waited. I have pasted the deadlock graph at the bottom- -hopefully it will be legible. Does anyone know how such a lock could be produced? I'd really like to be able to recreate such a scenario. Thanks! -w Current SQL statement for this session: update ACCOUNT set ACCOUNT_ID=:ACCOUNT_ID,ACCOUNT_NAME=:ACCOUNT_NAME,LAST_ MODIFIED=:LAST_MODIFIED,CUSTOMER_NODE_ID=:CUSTOMER_NODE _ID,ACCOUNT_TYPE_ID=:ACCOUNT_TYPE_ID,ACCOUNT_BALANCE=:A CCOUNT_BALANCE,BALANCE_DATE=:BALANCE_DATE,INVOICE_ID=:I NVOICE_ID,PREVIOUS_INVOICE_ID=:PREVIOUS_INVOICE_ID,CRED IT_LIMIT=:CREDIT_LIMIT,UNBILLED_AMOUNT=:UNBILLED_AMOUNT ,ACCOUNT_ACTION_CODE=:ACCOUNT_ACTION_CODE,ACCOUNT_AC TIO N_DATE=:ACCOUNT_ACTION_DATE where ACCOUNT_ID=:key_ACCOUNT_ID The following deadlock is not an ORACLE error. It is a deadlock due to user error in the design of an application or from issuing incorrect ad-hoc SQL. The following information may aid in determining the deadlock: Deadlock graph: -Blocker(s) --- --Waiter(s)- Resource Name process session holds waits process session holds waits TX-0007004c-26bf34 95 X 34 95 S session 95: DID 0001-0025-0002E096 session 95: DID 0001-0025-0002E096 Rows waited on: Session 95: no row -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Walter K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Anjo Kolk INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists -- -- To REMOVE yourself from this mailing list, send an E- Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Walter K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: ITL Deadlock Example?
Thanks for the replies (K,Jonathan,Anjo). I'm getting deadlocks to occur but they're not producing the deadlock graphs I was expecting to see. I'm looking to reproduce the scenario that will generate the following kind of deadlock graph--where it looks like a self-deadlock: -Blocker(s) --- --Waiter(s)- Resource Name process session holds waits process session holds waits TX-00050032-214322 23 X 22 23 S session 23: DID 0001-0016-17E7 session 23: DID 0001-0016-17E7 Rows waited on: Session 23: no row But, the deadlock graph that my testing is generating looks like: -Blocker(s) -Waiter(s)- Resource Name process session holds waits process session holds waits TX-00030052-1fb911 11 X 12 15 X TX-00040058-23ef12 15 X 11 11 X session 11: DID 0001-000B-0002 session 15: DID 0001-000C-0002 session 15: DID 0001-000C-0002 session 11: DID 0001-000B-0002 Rows waited on: Session 15: obj - rowid = 153E - AAABU+AAFLIAAA Session 11: obj - rowid = 153E - AAABU+AAFACAAA It is my understanding that the deadlock graph I am trying to reproduce is caused by ITL shortage deadlocks. I have verified, via dbms_rowid.rowid_block_number(), that I am updating rows in different blocks. Thanks again. -w It gets more complicated under Oracle 9 because initrans has a minimum value of 2 on tables; but under oracle 8, the following should work: create table t1 initrans 1 maxtrans 1 insert a few dozen rows into t1 so that there are rows in at least two blocks, and commit. session 1 update a row in block 1 session 2 update a row in block 2 session 1 update a row in block 2 -- will wait on a TX/4 session 2 update a row in block 1 -- will wait on a TX/4 After ca, three seconds session 1 will report ORA- 00060. In Oracle 9, you may get lucky with just two blocks of data and three concurrent sessions, but for consistency you need to have three blocks of data and rotate through the sessions updating one row in each block from each session in turn. Jonathan Lewis http://www.jlcomp.demon.co.uk |Walter K wrote: | | Can someone post an example of how to trigger a | deadlock (ORA-0060) due to ITL shortage? This is for | informational/fact-finding purposes. | | I've created a test table with MAXTRANS=1 and can | cause the enqueue waits between two sessions | contending for the same block but I can't seem to | cause a deadlock to occur. | | Thanks. | -w | -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists --- - To REMOVE yourself from this mailing list, send an E- Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Walter K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: ITL Deadlock Example?
I thought I was pretty careful in performing the steps for my test case--I had 2 rows in each of 3 blocks and tested with only two of the blocks using two different sessions exactly as described. To answer your question, in the deadlock graph I am trying to reproduce and understand, the session is holding an exclusive (X) and waiting for a share (S). The DML being performed is a simple update. No function or procedural calls are involved. No triggers exist on the table. No parallel DML, autonomous transactions, or distributed transactions are involved. The table is not partitioned. I saw posts on this kind of scenario on the Ixora site and in Google and that is what led me to believe it is an ITL shortage issue. The even stranger thing is that the ORA-0060 error has been raised about 10 times in the past 3 days and the trace file has the SAME name each time. As of today the error has occurred once and the trace file name has finally changed. My environment is SunOS 5.8, 8.1.7.3 (64-bit), OLTP database (~60Gb) with ~100 concurrent users. Thanks again. -w --- Original Message --- To: Multiple recipients of list ORACLE-L ORACLE- [EMAIL PROTECTED] Something has gone wrong with your test, you've produced a normal data deadlock. The ITL deadlock graph you should see from my description would have two lines with the cross-over on an X lock and an S lock on each line. But the deadlock graph you are trying to pin down does look very odd - as you say it does look like a self-deadlock. Because of the usual mess-up from email, I can't tell if your session is holding an exclusive and waiting for a share, or holding a share and waiting for an exclusive, though. There are seven different reasons for TX/4 lock waits (hence deadlocks) that I've found so far - but I don't think I've seen one quite like this. Are you using any of: distributed transaction partitioned tables parallel DML Autonomous transactions Jonathan Lewis http://www.jlcomp.demon.co.uk Author of: Practical Oracle 8i: Building Efficient Databases Next Seminar - Australia - July/August http://www.jlcomp.demon.co.uk/seminar.html Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- To: Multiple recipients of list ORACLE-L ORACLE- [EMAIL PROTECTED] Date: 22 May 2002 16:15 |Thanks for the replies (K,Jonathan,Anjo). I'm getting |deadlocks to occur but they're not producing the |deadlock graphs I was expecting to see. | |I'm looking to reproduce the scenario that will |generate the following kind of deadlock graph--where |it looks like a self-deadlock: | | -Blocker(s) - -- |--Waiter(s)- |Resource Name process session holds waits |process session holds waits |TX-00050032-214322 23 |X 22 23 S |session 23: DID 0001-0016-17E7 session 23: |DID 0001-0016-17E7 |Rows waited on: |Session 23: no row | |But, the deadlock graph that my testing is generating |looks like: | -Blocker(s) -- -- |-Waiter(s)- |Resource Name process session holds waits |process session holds waits |TX-00030052-1fb911 11 |X 12 15 X |TX-00040058-23ef12 15 |X 11 11 X |session 11: DID 0001-000B-0002 session 15: DID |0001-000C-0002 |session 15: DID 0001-000C-0002 session 11: DID |0001-000B-0002 |Rows waited on: |Session 15: obj - rowid = 153E - AAABU+AAFLIAAA |Session 11: obj - rowid = 153E - AAABU+AAFACAAA | | |It is my understanding that the deadlock graph I am |trying to reproduce is caused by ITL shortage |deadlocks. I have verified, via |dbms_rowid.rowid_block_number(), that I am updating |rows in different blocks. | |Thanks again. |-w | -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists -- -- To REMOVE yourself from this mailing list, send an E- Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Walter K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail
ITL Deadlock Example?
Can someone post an example of how to trigger a deadlock (ORA-0060) due to ITL shortage? This is for informational/fact-finding purposes. I've created a test table with MAXTRANS=1 and can cause the enqueue waits between two sessions contending for the same block but I can't seem to cause a deadlock to occur. Thanks. -w -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Walter K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
LogMiner - Null Username
Does anyone know why USERNAME in V$LOGMNR_CONTENTS is not always populated? I have encountered numerous instances where a select like 'Select username, sql_redo from v$logmnr_contents' shows a username for some operations and no username for others. I have also noticed that DELETE operations always have a null Username. Any ideas? Thanks. -w -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Walter K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
LogMiner - Null Username
Does anyone know why USERNAME in V$LOGMNR_CONTENTS is not always populated? I have encountered numerous instances where a select like 'Select username, sql_redo from v$logmnr_contents' shows a username for some operations and no username for others. I have also noticed that DELETE operations always have a null Username. Thanks. -w -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Walter K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
PK - Character vs. Numeric
I'm sure this has been raised in the past, but... Is it better, in terms of performance, to use numeric primary keys versus character/string keys? It is my understanding that this is really a space-savings issue rather than a performance issue. Can someone elaborate more on this? Thanks. -W -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Walter K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
DBMS_STATS.gather_database_stats
If you're not supposed to analyze SYS and SYSTEM then can anyone explain why the DBMS_STATS.GATHER_DATABASE_STATS procedure does? We have run into scenarios where the data dictionary becomes almost unusable until SYS gets analyzed again via this procedure. I.e. can't describe v$ views, selects against v$ views take forever to return results, etc. The obvious solution is to not use this particular procedure but it still begs the question WHY doesn't it exclude SYS and SYSTEM? Are there any reasons why you would want to analyze these schemas? Thanks. -w -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Walter K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: DBMS_STATS.gather_database_stats
I can't answer the question about the performance after deleting the statistics because we have a contract DBA here that insists it's okay to analyze the internal schemas even though he's the one that acknowledges the performance problem if the stats get a little stale! Unfortunately, he always gets his way, right or wrong...sigh... --- Original Message --- To: Multiple recipients of list ORACLE-L ORACLE- [EMAIL PROTECTED] Walter, this is actually a bug. It's supposedly fixed in 9i though I haven't tried it. Anybody? Do you still see a performance hit when you delete the statistics from the SYS objects? Regards, Mike Hately -Original Message- Sent: 25 April 2002 15:33 To: Multiple recipients of list ORACLE-L If you're not supposed to analyze SYS and SYSTEM then can anyone explain why the DBMS_STATS.GATHER_DATABASE_STATS procedure does? We have run into scenarios where the data dictionary becomes almost unusable until SYS gets analyzed again via this procedure. I.e. can't describe v$ views, selects against v$ views take forever to return results, etc. The obvious solution is to not use this particular procedure but it still begs the question WHY doesn't it exclude SYS and SYSTEM? Are there any reasons why you would want to analyze these schemas? Thanks. -w __ __ This email and any attached to it are confidential and intended only for the individual or entity to which it is addressed. If you are not the intended recipient, please let us know by telephoning or emailing the sender. You should also delete the email and any attachment from your systems and should not copy the email or any attachment or disclose their content to any other person or entity. The views expressed here are not necessarily those of Churchill Insurance Group plc or its affiliates or subsidiaries. Thank you. Churchill Insurance Group plc. Company Registration Number - 2280426. England. Registered Office: Churchill Court, Westmoreland Road, Bromley, Kent BR1 1DP. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hately Mike INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists -- -- To REMOVE yourself from this mailing list, send an E- Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Walter K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Sqlplus Auto-trim Whitespace???
Hi, A co-worker of mine is trying to remember how to have trailing whitespace automatically removed from each COLUMN in the output of a query within SQL*Plus. He said he saw the tip in a book a couple years ago and can't remember for the life of him how he went about it. I have poured through the docs myself and other references and can't seem to find a way to do this. The objective is to set the COLSEP to say '|' and then be able to perform something like a SELECT * FROM table such that the output/display is variable length for each column. The solution we're looking for is purely within SQL*Plus, not doing something with PL/SQL. Also, the solution should not involve using the COLUMN command to control the formatting of each column literally. Anyone know how to do this? Does anyone remember the tip and possibly the name of the book? Thanks again! -w __ Do You Yahoo!? Yahoo! Tax Center - online filing with TurboTax http://taxes.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Walter K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Sqlplus Auto-trim Whitespace???
Thanks for the suggestion. Unfortunately, trimspool is only at the record level, not the column level. Same with trimout. I'm looking for variable column widths without having to format each column or go through the hassle of a long select statement with concatenations. -w --- Murray, Margaret [EMAIL PROTECTED] wrote: Walter, set trimspool ON (or set trimsp on or set trims on) you can also use show all at the sqlplus prompt to get an idea about what other options have been set (if you don't remember the command). Margaret -Original Message- From: Walter K [mailto:[EMAIL PROTECTED]] Sent: Friday, April 05, 2002 2:37 PM To: Multiple recipients of list ORACLE-L Subject: Sqlplus Auto-trim Whitespace??? Hi, A co-worker of mine is trying to remember how to have trailing whitespace automatically removed from each COLUMN in the output of a query within SQL*Plus. He said he saw the tip in a book a couple years ago and can't remember for the life of him how he went about it. I have poured through the docs myself and other references and can't seem to find a way to do this. The objective is to set the COLSEP to say '|' and then be able to perform something like a SELECT * FROM table such that the output/display is variable length for each column. The solution we're looking for is purely within SQL*Plus, not doing something with PL/SQL. Also, the solution should not involve using the COLUMN command to control the formatting of each column literally. Anyone know how to do this? Does anyone remember the tip and possibly the name of the book? Thanks again! -w __ Do You Yahoo!? Yahoo! Tax Center - online filing with TurboTax http://taxes.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Walter K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Murray, Margaret INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Yahoo! Tax Center - online filing with TurboTax http://taxes.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Walter K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Where's Chris these days?
Anyone know where Christopher Spence went? I haven't seen him in this forum for a couple months now... Just curious... __ Do You Yahoo!? Yahoo! Greetings - Send FREE e-cards for every occasion! http://greetings.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Walter K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Email -- DB (export/parse)
Hi, Does anyone know of a utility that would allow me to export email, from say Outlook or Outlook Express, directly to a database or to a flat file (delimited) for import into a database? It doesn't need to be fancy, basically just date/time, to/from, subject, body. Thanks. -w __ Do You Yahoo!? Yahoo! Sports - Coverage of the 2002 Olympic Games http://sports.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Walter K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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-01555 Mystery (Help)
Hi, A user in our data warehousing group is running into the old ORA-01555 (snapshot too old) error every time she runs a massive (20 million rows) select against one table via a view. I confirmed that the view only translates to the one table. The user swears that no one would be making any updates/deletes to the table she is selecting from. I suggested she lock the table in exclusive mode, prior to running her massive select to guarantee no one else could change the data in the table and cause the triggering of the 1555 error. Locking the table was a viable option because it's a staging table in the warehouse itself. She locked the table in exclusive mode last night and it locked; fired off her query, and it failed 5 hours later with the 1555 error again. I'm stumped on this. I just don't see how this is possible. Any suggestions? Thanks!!! -w __ Do You Yahoo!? Great stuff seeking new owners in Yahoo! Auctions! http://auctions.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Walter K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: ORA-01555 Mystery (Help)
Jared, would you elaborate more on this? Does this need to be a 'compute' or can it be an 'estimate' on the analyze? I read the info on Steve's site as suggested by Barb and it sounds like block cleanout may be the issue but I'm still trying to digest the concept/issue as it relates to my circumstance. For the others that have contributed to the thread, yes, the table is definitely locked in exclusive mode (via a different session) before the SELECT is performed and the lock is not released until the following day. I too was suspicious that the lock was accidentally being released. -w --- [EMAIL PROTECTED] wrote: Delayed block cleanouts can still cause the ORA-1555, even after locking the table in exlusive mode. That's the purpose of the analyze, to force the block cleanouts. Jared Paul Baumgartel [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/25/02 09:30 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: ORA-01555 Mystery (Help) Sure, but the original post concerns a *query*, not a transaction, and before running the query, the user locked the queried table in exclusive mode, to ensure that no other session could write to the queried table. How do we account for the query's need to read from rollback? --- Baker, Barbara [EMAIL PROTECTED] wrote: I have a batch job that does this consistently. It's the only job in the database; it sets the transaction to a hugh rollback segment. And it eats its own tail. Depending on how the job is written, it may need a read consistent view itself (as opposed to some other query in the database needing that read consistent view.)In that case, it may well go try to read its own rollback segment, only to find that it's been overwritten. (Oddly enough, even when there's plenty of space to extend the rollback, Oracle will decide to overwrite the original rollback segments rather than extend if it thinks it doesn't need those segments any more.) I'd strongly suggest you get the stuff from Steve Adams' ixora site that places an uncommitted transaction in your rollback segments for the length of the run.This will guarantee that the rollback segments don't get overwritten. Good luck! Barb -- From:Walter K[SMTP:[EMAIL PROTECTED]] Reply To:[EMAIL PROTECTED] Sent:Friday, January 25, 2002 9:15 AM To: Multiple recipients of list ORACLE-L Subject: ORA-01555 Mystery (Help) Hi, A user in our data warehousing group is running into the old ORA-01555 (snapshot too old) error every time she runs a massive (20 million rows) select against one table via a view. I confirmed that the view only translates to the one table. The user swears that no one would be making any updates/deletes to the table she is selecting from. I suggested she lock the table in exclusive mode, prior to running her massive select to guarantee no one else could change the data in the table and cause the triggering of the 1555 error. Locking the table was a viable option because it's a staging table in the warehouse itself. She locked the table in exclusive mode last night and it locked; fired off her query, and it failed 5 hours later with the 1555 error again. I'm stumped on this. I just don't see how this is possible. Any suggestions? Thanks!!! -w __ . -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Great stuff seeking new owners in Yahoo! Auctions! http://auctions.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Walter K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line
RE: ORA-01555 Mystery (Help)
Another fact, that should be mentioned, is that the table in question was built (loaded) two days ago. The nightly ETL processes for the warehouse are pretty substantial and the likelyhood of a block not getting cleaned/flushed out for a couple days should be nil. To summarize: 1. Tuesday Night: -truncate/load table 'A' (24 million rows) -Perform massive select from 'A', fails 5 hours later with 1555. NO DML BEING PERFORMED AGAINST 'A' BY ANY OTHER SESSION 2. Wednesday Night: -Perform massive select against 'A', fails 5 hours later with ORA-1555. NO DML BEING PERFORMED AGAINST 'A' BY ANY OTHER SESSION 3. Thursday night: -'lock table A in exclusive mode;' via session 123 -perform massive select against 'A', fails 5 hours later with ORA-1555 via session 124. NO DML BEING PERFORMED AGAINST 'A' BY ANY OTHER SESSION -session 123 still has exclusive lock on table 'A' the following morning 4. Friday morning: -Walter is stumped but still trying to figure out a solution! :) -w --- [EMAIL PROTECTED] wrote: Delayed block cleanouts can still cause the ORA-1555, even after locking the table in exlusive mode. That's the purpose of the analyze, to force the block cleanouts. Jared Paul Baumgartel [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/25/02 09:30 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: ORA-01555 Mystery (Help) Sure, but the original post concerns a *query*, not a transaction, and before running the query, the user locked the queried table in exclusive mode, to ensure that no other session could write to the queried table. How do we account for the query's need to read from rollback? --- Baker, Barbara [EMAIL PROTECTED] wrote: I have a batch job that does this consistently. It's the only job in the database; it sets the transaction to a hugh rollback segment. And it eats its own tail. Depending on how the job is written, it may need a read consistent view itself (as opposed to some other query in the database needing that read consistent view.)In that case, it may well go try to read its own rollback segment, only to find that it's been overwritten. (Oddly enough, even when there's plenty of space to extend the rollback, Oracle will decide to overwrite the original rollback segments rather than extend if it thinks it doesn't need those segments any more.) I'd strongly suggest you get the stuff from Steve Adams' ixora site that places an uncommitted transaction in your rollback segments for the length of the run.This will guarantee that the rollback segments don't get overwritten. Good luck! Barb -- From:Walter K[SMTP:[EMAIL PROTECTED]] Reply To:[EMAIL PROTECTED] Sent:Friday, January 25, 2002 9:15 AM To: Multiple recipients of list ORACLE-L Subject: ORA-01555 Mystery (Help) Hi, A user in our data warehousing group is running into the old ORA-01555 (snapshot too old) error every time she runs a massive (20 million rows) select against one table via a view. I confirmed that the view only translates to the one table. The user swears that no one would be making any updates/deletes to the table she is selecting from. I suggested she lock the table in exclusive mode, prior to running her massive select to guarantee no one else could change the data in the table and cause the triggering of the 1555 error. Locking the table was a viable option because it's a staging table in the warehouse itself. She locked the table in exclusive mode last night and it locked; fired off her query, and it failed 5 hours later with the 1555 error again. I'm stumped on this. I just don't see how this is possible. Any suggestions? Thanks!!! -w __ . -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Great stuff seeking new owners in Yahoo! Auctions! http://auctions.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Walter K INET
RE: ORA-01555 Mystery (Help)
Jared: The table was analyzed (via estimate) Wednesday night. I don't know if it was before or after Wednesday night's attempt at the extract but if the blocks are getting cleaned out via the 'analyze..estimate' then delayed block cleanout should definitely not have been an issue for Thursday night's attempt. Barb: I understand what Mladen is saying but it shouldn't be an issue because the table has been locked in exclusive mode prior to the running of the query and the lock took. So, even if some rogue process out there was attempting to change data in the table after the query started, it shouldn't matter because the rogue process(es) would get stuck waiting on the table lock. I was lucky that the query is being re-run today while I am still at the office so I looked at the DB activity and nothing is happening in the rollbacks, as I expected to see. Discrete transactions aren't an issue as the warehouse developers don't know about them. I also recreated all of the rollback segments for grins. We'll see what happens tonight. Unfortunately, if it works tonight I still won't really know why. :( -w --- Baker, Barbara [EMAIL PROTECTED] wrote: No wonder you're mystified. This doesn't make sense. I can understand how you might have had a problem Tues night, but Wed night you should have sailed. Here's one more thing to add to your 'bag of tricks': try running this query (in batch every 15 minutes or so, if you can) to see what user(s) are accessing which rollbacks at any given time. Probably won't help (unless Mladen is right, and someone is not coming clean with the complete truth). But it can't hurt. select TO_CHAR(SYSDATE,'DD-MON-:HH24:MI:SS'), osuser o, username u, segment_name s, substr(sa.sql_text,1,500) txt from v$session s, v$transaction t, dba_rollback_segs r, v$sqlarea sa where s.taddr=t.addr and t.xidusn=r.segment_id(+) and s.sql_address=sa.address(+) / -- From: Walter K[SMTP:[EMAIL PROTECTED]] Reply To: [EMAIL PROTECTED] Sent: Friday, January 25, 2002 12:30 PM To: Multiple recipients of list ORACLE-L Subject:RE: ORA-01555 Mystery (Help) Another fact, that should be mentioned, is that the table in question was built (loaded) two days ago. The nightly ETL processes for the warehouse are pretty substantial and the likelyhood of a block not getting cleaned/flushed out for a couple days should be nil. To summarize: 1. Tuesday Night: -truncate/load table 'A' (24 million rows) -Perform massive select from 'A', fails 5 hours later with 1555. NO DML BEING PERFORMED AGAINST 'A' BY ANY OTHER SESSION 2. Wednesday Night: -Perform massive select against 'A', fails 5 hours later with ORA-1555. NO DML BEING PERFORMED AGAINST 'A' BY ANY OTHER SESSION 3. Thursday night: -'lock table A in exclusive mode;' via session 123 -perform massive select against 'A', fails 5 hours later with ORA-1555 via session 124. NO DML BEING PERFORMED AGAINST 'A' BY ANY OTHER SESSION -session 123 still has exclusive lock on table 'A' the following morning 4. Friday morning: -Walter is stumped but still trying to figure out a solution! :) -w --- [EMAIL PROTECTED] wrote: Delayed block cleanouts can still cause the ORA-1555, even after locking the table in exlusive mode. That's the purpose of the analyze, to force the block cleanouts. Jared Paul Baumgartel [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/25/02 09:30 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: ORA-01555 Mystery (Help) Sure, but the original post concerns a *query*, not a transaction, and before running the query, the user locked the queried table in exclusive mode, to ensure that no other session could write to the queried table. How do we account for the query's need to read from rollback? --- Baker, Barbara [EMAIL PROTECTED] wrote: I have a batch job that does this consistently. It's the only job in the database; it sets the transaction to a hugh rollback segment. And it eats its own tail. Depending on how the job is written, it may need a read consistent view itself (as opposed to some other query in the database needing that read consistent view.)In that case, it may well go try to read its own rollback segment, only to find that it's been overwritten. (Oddly enough, even when there's plenty of space to extend the rollback, Oracle will decide to overwrite the original rollback segments rather than extend if it thinks it doesn't need those segments any more.) I'd strongly suggest you get the stuff from
RMAN - restoring archive logs
Hi,
I want to analyze some archive log files with
LogMiner. The logs are stored in an RMAN backup on
tape. I am trying to restore the files to a
development box but when I perform the restore I get
the error message RMAN-20003: target database
incarnation not found in recovery catalog. The syntax
I am using is:
run {
allocate channel t1 type 'sbt_tape';
restore archivelog from logseq 200 until logseq 233;
}
Can the archive logs only be restored back to the
original database? The target database I am using is a
restored backup of the same production database but on
a development box.
Any suggestions would be appreciated. Thanks.
-w
__
Do You Yahoo!?
Send FREE video emails in Yahoo! Mail!
http://promo.yahoo.com/videomail/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Walter K
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
LogMiner and ORA-03113
I'm trying to analyze some archive logs via LogMiner and I keep getting the error ORA-03113 (end of file on communications channel) when I query the V$LOGMNR_CONTENTS view. I did a new and start prior to running the query. This process that I am going through works on some log files but not on others. The log files aren't corrupted because I can restore/recover the database and apply the logs successfully for a roll-forward. I'm just running a 'select count(*)' query at this point. Has anyone else encountered this and come up with a solution? I've checked MetaLink, Google, etc and so far have not come up with anything that describes what may be the cause. I'm running 8.1.7.2 (64bit) on Solaris 8. MANY thanks in advance. -w __ Do You Yahoo!? Send FREE video emails in Yahoo! Mail! http://promo.yahoo.com/videomail/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Walter K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: LogMiner and ORA-03113
Block size is 8k. Interesting, how is the block size related? --- Joe Testa [EMAIL PROTECTED] wrote: whats your blocksize, anything other than 2K has been know to cause all kinds of problems. joe Walter K wrote: I'm trying to analyze some archive logs via LogMiner and I keep getting the error ORA-03113 (end of file on communications channel) when I query the V$LOGMNR_CONTENTS view. I did a new and start prior to running the query. This process that I am going through works on some log files but not on others. The log files aren't corrupted because I can restore/recover the database and apply the logs successfully for a roll-forward. I'm just running a 'select count(*)' query at this point. Has anyone else encountered this and come up with a solution? I've checked MetaLink, Google, etc and so far have not come up with anything that describes what may be the cause. I'm running 8.1.7.2 (64bit) on Solaris 8. MANY thanks in advance. -w __ Do You Yahoo!? Send FREE video emails in Yahoo! Mail! http://promo.yahoo.com/videomail/ -- Joe Testa, Oracle DBA Want to have a good time with a bunch of geeks? Check out: http://www.geekcruises.com/standard_interface/future_cruises.html I'm presenting, when registering drop my name :) -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Joe Testa INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Send FREE video emails in Yahoo! Mail! http://promo.yahoo.com/videomail/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Walter K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
dbms_stats.gather... (method_opt)
Hi, Could someone tell me what the HIDDEN columns option is in the DBMS_STATS.GATHER_SCHEMA_STATS and DBMS_STATS.GATHER_TABLE_STATS procedures? I don't seem to find any explanation anywhere about its purpose. Thanks again. -w __ Do You Yahoo!? Send FREE video emails in Yahoo! Mail! http://promo.yahoo.com/videomail/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Walter K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
dbms_stats.gather... (method_opt)
Hi, Could someone tell me what the HIDDEN columns option is in reference to the DBMS_STATS.GATHER_SCHEMA_STATS and DBMS_STATS.GATHER_TABLE_STATS procedures? Thanks again. -w __ Do You Yahoo!? Send FREE video emails in Yahoo! Mail! http://promo.yahoo.com/videomail/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Walter K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
EMC Not Releasing Space
Hi, Just curious if anyone has any experience with an EMC Symmetrix not releasing the space for a deleted datafile. If so, is there a solution or work-around? Yesterday, we took a tablespace offline, dropped it and then deleted the associated datafile. The file was 1G in size and today the space has still not been released as indicated by 'df -k'. According to another DBA here, they have seen this in the past and said bouncing the DB remedied the problem. It sounds like the inodes are still in use by Oracle but I can't imagine why. I've worked with Symmetrix systems before and have never experienced this behavior. We are running 8.1.7.2 (64bit) on Solaris 2.8. Any suggestions? As always, your feedback is appreciated. -w __ Do You Yahoo!? Send FREE video emails in Yahoo! Mail! http://promo.yahoo.com/videomail/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Walter K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
How to Trace PL/SQL
Is there a way to trace the SQL executed by a PL/SQL program (i.e. stored procedure)? Using DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION... does not seem to work when the session is calling a stored procedure. I'm running 8.1.7.2 on Solaris 2.8. Thanks. -w __ Do You Yahoo!? Send FREE video emails in Yahoo! Mail! http://promo.yahoo.com/videomail/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Walter K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Deadlock Graphs
Hi, Can anyone provide some details, or perhaps point me to a web site, that explains the components of a deadlock graph (and/or the contents of the trace file) in detail? I'm trying to understand why some deadlocks are occurring in one of my systems and I'm having trouble trying to decipher the trace file. Thanks. -w __ Do You Yahoo!? Send your FREE holiday greetings online! http://greetings.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Walter K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Blocking illegal connections
Hi, We're trying to prevent users from connecting to our production database via illegal connections (i.e. tools like TOAD, etc.). Does anyone have any suggestions on how to block connections from query tools (i.e. Toad, SQL Nav, etc.) in such a way that an error message is reported back to the tool user that the connection is prohibited? We've tried designing a logon trigger with a PRAGMA definition but the error message is not getting relayed back to the client. As always, your feedback is appreciated! -w __ Do You Yahoo!? Check out Yahoo! Shopping and Yahoo! Auctions for all of your unique holiday gifts! Buy at http://shopping.yahoo.com or bid at http://auctions.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Walter K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Blocking illegal connections
Unfortunately, some of the Apps we have are canned and we don't have the ability to modify them as suggested. Any ideas on the trigger approach? Thanks again! -w - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, December 12, 2001 5:10 PM A close workaround is with roles. By default give everyone has 'create session' (so they at least log on), but any other privs they need come from a password protected role. The apps you write enable the role at login, so if anyone comes in not via an app, all they can do is login and nothing else... C --- Walter K [EMAIL PROTECTED] wrote: Hi, We're trying to prevent users from connecting to our production database via illegal connections (i.e. tools like TOAD, etc.). Does anyone have any suggestions on how to block connections from query tools (i.e. Toad, SQL Nav, etc.) in such a way that an error message is reported back to the tool user that the connection is prohibited? We've tried designing a logon trigger with a PRAGMA definition but the error message is not getting relayed back to the client. As always, your feedback is appreciated! -w __ Do You Yahoo!? Check out Yahoo! Shopping and Yahoo! Auctions for all of your unique holiday gifts! Buy at http://shopping.yahoo.com or bid at http://auctions.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Walter K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = Connor McDonald http://www.oracledba.co.uk (mirrored at http://www.oradba.freeserve.co.uk) Some days you're the pigeon, some days you're the statue __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Connor=20McDonald?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Walter K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Checkpoint Duration
I have LOG_CHECKPOINTS_TO_ALERT set in my init.ora file and according to my alert log they are generally taking about 30 minutes to complete, sometimes only a couple minutes. Does that sound right? The database is about 20G and doesn't have much transaction activity. It's a development database and export/import is run several times during the day. The DB is RAID-5 but the I/O wait is usually very low. Any feedback/suggestions are appreciated. Thanks! -w __ Do You Yahoo!? Find a job, post your resume. http://careers.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Walter K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Oravle vs. Informix
Hi, Sorry to start one of these threads but can anyone shed some light on some of the differences between Oracle (8i) and Informix? Trying to find this info out via the web has been VERY time consuming. Are there any compelling reasons for using Informix over Oracle, or visa versa? Thanks. -w __ Do You Yahoo!? Listen to your Yahoo! Mail messages from any phone. http://phone.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Walter K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Optimal Disk Layout??
Hi, I'm encuntering a relatively high iowait percentage when my hot backups are running. The platform is Sun (E420 2cpu running SunOS 5.7) with an A1000 disk array (8 9Gb drives, hardware Raid-5). The array is one volume and all DB components (redo, archive, data, index, system, etc.) are on the same volume except for the binaries. The database is not large, only ~8Gb in size and the transactional volume is not much either. However, when the backups run, the iowait according to 'top' hovers between 50-70% which causes our application to time-out via Web Logic 6.0. The developers can't explain why the timeouts are occurring (WLS 6.0 is a new upgrade from 5.1). The SysAdmin isn't much help either. I have an opportunity to rebuild the database on another machine and use RAID 1+0 -- the thought being that we are choking ourselves with Raid-5 when the hot backups are performing the cp's (copies) and then the files are compressed. My first question is, how is the data distributed across the drives in my Raid-5 configuration? Is each disk being filled contiguously in series or is the data being spread around in a pseudo-striping manner? My second question/dilema is, the new array (another A1000) will have 6 18Gb drives and with Raid 1+0 that shrinks to 3 drives of usable space for everything except redo and archive. 4 9Gb drives will be added in two mirrored sets, one for redo and the other for archive. I'm afraid that I will see worse I/O performance with the new array because it has so fewer physical drives, thus eliminating the benefit of not having to write the parity info. Do you concur? Knowing the two arrays I have to work with, which would be the better configuration? Any suggestions, recommendations would VERY much be appreciated. Thanks again for the feedback. -w __ Terrorist Attacks on U.S. - How can you help? Donate cash, emergency relief information http://dailynews.yahoo.com/fc/US/Emergency_Information/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Walter K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Optimal Disk Layout??
Isn't it 3 drives for data, not 6, because of the mirroring? I agree, mirroring the extra 4 (9Gb) drives isn't necessary because this can be accomplished via the DB config. Veritas is also being used on the existing array and the sys admin seems to think that we've got extra overhead for that on top of the hardware raid-5. I have also been told that the controller has a 16Mb cache on it, and it only goes to 64Mb. -w --- Christopher Spence [EMAIL PROTECTED] wrote: 1. depends on the stripe width, data may be on one or many drives, parity will always get written to all. 2. I believe your performance will be better; you have 6 drives for data, and 4 for archive/redo. That equates to 12 drives over the previous 8. However, you were using all eight for everything. I don't believe there is any purpose to mirror the four drives, I like to use Raid 0 or single drives for redo, and just do multiple members and archive destinations. Redo/Archive is high write activity, Raid 1 takes a small hit on write, and only benefits read performance. You can get the same redundancy using duplex destination and multiple members. Having redo logs and archive logs separate will prove to be beneficial. All and all, I think you will come up with a better configuration performance-wise. Make sure the A1000 has the 80Mb cache, and has the latest firmware, as it there are a lot of buggy A1000's out there that cause corruption if they are saturated. 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 -Original Message- Sent: Friday, September 21, 2001 10:15 AM To: Multiple recipients of list ORACLE-L Hi, I'm encuntering a relatively high iowait percentage when my hot backups are running. The platform is Sun (E420 2cpu running SunOS 5.7) with an A1000 disk array (8 9Gb drives, hardware Raid-5). The array is one volume and all DB components (redo, archive, data, index, system, etc.) are on the same volume except for the binaries. The database is not large, only ~8Gb in size and the transactional volume is not much either. However, when the backups run, the iowait according to 'top' hovers between 50-70% which causes our application to time-out via Web Logic 6.0. The developers can't explain why the timeouts are occurring (WLS 6.0 is a new upgrade from 5.1). The SysAdmin isn't much help either. I have an opportunity to rebuild the database on another machine and use RAID 1+0 -- the thought being that we are choking ourselves with Raid-5 when the hot backups are performing the cp's (copies) and then the files are compressed. My first question is, how is the data distributed across the drives in my Raid-5 configuration? Is each disk being filled contiguously in series or is the data being spread around in a pseudo-striping manner? My second question/dilema is, the new array (another A1000) will have 6 18Gb drives and with Raid 1+0 that shrinks to 3 drives of usable space for everything except redo and archive. 4 9Gb drives will be added in two mirrored sets, one for redo and the other for archive. I'm afraid that I will see worse I/O performance with the new array because it has so fewer physical drives, thus eliminating the benefit of not having to write the parity info. Do you concur? Knowing the two arrays I have to work with, which would be the better configuration? Any suggestions, recommendations would VERY much be appreciated. Thanks again for the feedback. -w __ Terrorist Attacks on U.S. - How can you help? Donate cash, emergency relief information http://dailynews.yahoo.com/fc/US/Emergency_Information/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Walter K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Christopher Spence INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE
Good/Cheap Backup Agent?
Hi, We're looking for an inexpensive backup agent for our databases. The databases are 8i and are on Sun boxes but we're looking to have the agent/client hosted from an NT box (because it appears to be cheaper that way). What are your recommendations? Do all of the agents work through RMAN these days? Are there any features or caveats I should look for? Thanks! -w __ Terrorist Attacks on U.S. - How can you help? Donate cash, emergency relief information http://dailynews.yahoo.com/fc/US/Emergency_Information/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Walter K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Good/Cheap Backup Agent?
I neglected to mention that we're trying to backup to tape. I'm not sure what is involved with configuring a media manager for Oracle that will allow Rman to work with a tape drive but it would seem to me at that point I'm into a 3rd party solution anyway. Yes? No? What does it take to get a tape drive to work with RMAN? If a 3rd party solution is better, then I'm back to inquiring about an inexpensive, but useful, solution. Thanks. -w --- Ruth Gramolini [EMAIL PROTECTED] wrote: Go for it! Rman is cheap (free with Oracle) and works well. You can either run it from OEM (Backup Manager) or directly from the rman prompt or using shell scripts. Good luck, Ruth - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, September 18, 2001 3:05 PM Hi, We're looking for an inexpensive backup agent for our databases. The databases are 8i and are on Sun boxes but we're looking to have the agent/client hosted from an NT box (because it appears to be cheaper that way). What are your recommendations? Do all of the agents work through RMAN these days? Are there any features or caveats I should look for? Thanks! -w __ Terrorist Attacks on U.S. - How can you help? Donate cash, emergency relief information http://dailynews.yahoo.com/fc/US/Emergency_Information/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Walter K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Ruth Gramolini INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Terrorist Attacks on U.S. - How can you help? Donate cash, emergency relief information http://dailynews.yahoo.com/fc/US/Emergency_Information/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Walter K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Spatial included w/9i ????
!! Please do not post Off Topic to this List !! Can anyone tell me if they know whether Oracle Spatial is included with 9i EE? The on-line documentation for Options is linked to Oracle8 (why I don't know) and is vague. Thanks! -w __ Terrorist Attacks on U.S. - How can you help? Donate cash, emergency relief information http://dailynews.yahoo.com/fc/US/Emergency_Information/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Walter K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Licensing??
Hi, Can someone explain how the named-user licensing works? Also, has concurrent usage licensing gone away? We have a need for an additional database to use for mapping/geo-coding purposes. The primary application will periodically perform a query against this new database to look up some mapping info. Essentially, the application will always maintain a handful of sessions to the mapping database. It may perform the lookup as often as 10 times an hour. The new database will essentially be read-only. The docs say NOT to allow the sharing of usernames for multiple concurrent users. Although the application may be hosting several users, no more than a handful would ever need to get data from the mapping database thus the idea of going cheap by buying say 5-10 named user licenses. It seems that for a few $K that I could accomplish what I want with the database using named-user licensing rather than dropping $14K for a single-CPU license (2yr). We may also want to go with a dual-cpu box which would mean another $14k! Am I treading a thin line here? I hope this makes sense. As always, your feedback is appreciated! -w __ Do You Yahoo!? Get email alerts NEW webcam video instant messaging with Yahoo! Messenger http://im.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Walter K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Licensing
Hi, Can someone explain how the named-user licensing works? Also, has concurrent usage licensing gone away? We have a need for an additional database to use for mapping/geo-coding purposes. The primary application will periodically perform a query against this new database to look up some mapping info. Essentially, the application will always maintain a handful of sessions to the mapping database. It may perform the lookup as often as 10 times an hour. The new database will essentially be read-only. What is the definition of a user as it pertains to the licensing? The docs say NOT to allow the sharing of usernames for multiple concurrent users. Although the application may be hosting several users, no more than a handful would ever need to get data from the mapping database thus the idea of going cheap by buying say 5-10 named user licenses. It seems that for a few $K that I could accomplish what I want with the database using named-user licensing rather than dropping $14K+ for a single-CPU license. We may also want to go with a dual-cpu box which would mean another $14k! Am I treading a thin line here? I hope this makes sense. As always, your feedback is appreciated! -w __ Do You Yahoo!? Get email alerts NEW webcam video instant messaging with Yahoo! Messenger http://im.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Walter K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Tricky Hierarchical Query
Hi, I am stumped on how to do a particular hierarchical query. The query needs to be written entirely in SQL. I have a table (ACCOUNT) with 3 columns of interest in it, ACCOUNT_ID, ROOT_ID, PARENT_ID. Every row represents an account. If an account is the root (top-most), then the ROOT_ID and PARENT_ID columns are null. If an account is a parent, then the ROOT_ID is populated but the PARENT_ID is null. Accounts can be nested multiple levels deep. I need to find all of the accounts that belong to the root of the given account but not include any accounts that are children of the given account. Does this make sense? I.e. 5 6 7 8 9 10 11 1213 14 I'm not sure if my tree diagram will be dispalyed properly after I email this but in essence if 8 is the given account number, I want everything returned except for 8, 11 and 12. Thanks in advance! -w __ Do You Yahoo!? Get email alerts NEW webcam video instant messaging with Yahoo! Messenger http://im.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Walter K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Tricky Hierarchical Query
Sorry. I mis-stated the relationship to the parent in my original email. Parent_id is always populated unless the row is a root row. The data for all rows in my sample tree would be: ACCOUNT_ID ROOT_ID PARENT_ID 5null null 65 5 75 5 85 6 95 6 10 5 7 11 5 8 12 5 8 13 5 9 14 5 10 -w --- Jacques Kilchoer [EMAIL PROTECTED] wrote: What would the data in your table look like, for the tree in the example? e.g. 6 is a child of 5, but a parent of 8 and 9. Would the data look like this? account_id root_idparent_id 5 null null 6 5null 8 5 6 9 5 6 -Original Message- From: Walter K [mailto:[EMAIL PROTECTED]] I am stumped on how to do a particular hierarchical query. The query needs to be written entirely in SQL. I have a table (ACCOUNT) with 3 columns of interest in it, ACCOUNT_ID, ROOT_ID, PARENT_ID. Every row represents an account. If an account is the root (top-most), then the ROOT_ID and PARENT_ID columns are null. If an account is a parent, then the ROOT_ID is populated but the PARENT_ID is null. Accounts can be nested multiple levels deep. I need to find all of the accounts that belong to the root of the given account but not include any accounts that are children of the given account. Does this make sense? I.e. 5 6 7 8 9 10 11 1213 14 I'm not sure if my tree diagram will be dispalyed properly after I email this but in essence if 8 is the given account number, I want everything returned except for 8, 11 and 12. __ Do You Yahoo!? Get email alerts NEW webcam video instant messaging with Yahoo! Messenger http://im.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Walter K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
High DML Table - I/O Suggestions???
Sorry to repost this, but I just lost all of my email from the past two days. Arghh! I have a table that is ~100Mb in size. The table will always contain 500,000 to 1,000,000 records in it at any given time and approximately that number of inserts, updates and deletes will be performed against it on a daily basis. Only one user will be performing the DML operations on the table and that will be via a background process (i.e. daemon). The table has no foreign keys nor a primary key but it may require an index or two because of the updates and deletes. Unfortunately, I can't put the table on a dedicated disk and I am stuck with Raid-5. My question is, what suggestions does anyone have to offer that will give me the best possible I/O in this case? Is trying to put this table in a Keep pool going to help at all? Thanks again for your advise (and patience). -w __ Do You Yahoo!? Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Walter K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
High DML Table - Suggestions??
Hi, I have a table that is going to have a large amount of inserts, updates and deletes performed against it daily. Approximately 1,000,000 transactions per day (some single-record, some multi-record). The table is ~100Mb in size. I'm looking for some suggestions on what I can do to have the most optimal I/O for the table. I've been doing a little reading about buffer pools. Is assigning this table to a KEEP pool a practical approach or is that not going to buy me anything because DML is involved? Does anyone have any other suggestions? Unfortunately, I can't put the table on a dedicated disk and I am stuck with Raid-5 currently. Any suggestions would be appreciated. Thanks in advance! -w __ Do You Yahoo!? Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Walter K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: High DML Table - Suggestions??
Good point about the FKs. Fortunately, the table is stand-alone is is not a parent or a child of any other table. I thought about initrans and freelists but the table will only be touched by a single user process which is running in the background performing all of the DML transactions, much like a daemon. -w --- Christian Trassens [EMAIL PROTECTED] wrote: Don't put it in a buffer keep because of the amount of consistent gets the table could have. It seems to be a high volatile table. Maybe it is too late but you should look on the initrans, freelist of the table. Also if the table has foreign keys, talk with Development to leave the table without them. I should consider to partition the tablemaybe a hash one. Regards. --- Walter K [EMAIL PROTECTED] wrote: Hi, I have a table that is going to have a large amount of inserts, updates and deletes performed against it daily. Approximately 1,000,000 transactions per day (some single-record, some multi-record). The table is ~100Mb in size. I'm looking for some suggestions on what I can do to have the most optimal I/O for the table. I've been doing a little reading about buffer pools. Is assigning this table to a KEEP pool a practical approach or is that not going to buy me anything because DML is involved? Does anyone have any other suggestions? Unfortunately, I can't put the table on a dedicated disk and I am stuck with Raid-5 currently. Any suggestions would be appreciated. Thanks in advance! -w __ Do You Yahoo!? Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Walter K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = Eng. Christian Trassens Senior DBA Systems Engineer [EMAIL PROTECTED] [EMAIL PROTECTED] Phone : 541149816062 __ Do You Yahoo!? Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Christian Trassens INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Walter K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Disk Names - sdXX vs. cXtXdXsX
Is there a method or utility to translate disk names reported by sar from the sdXX format to the cXtXdX format? I'm on a Sun platform. Many thanks in advance. -w __ Do You Yahoo!? Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Walter K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Indexes Used per Query
Will the optimizer (CBO) ever use more than one index in the access path for a table in a query? I have never seen this behavior and have always been curious. __ Do You Yahoo!? Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Walter K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Import Issue/question
Hi, What is the Import utility doing exactly after it says the rows of a table have been imported? I thought the order of events was create table, create indexes, load data, move on to next table and at the end of everything enable the constraints. I am running an import of one schema's tables into another and the import always hangs on one table for a few minutes. The data volume is not big at all, only ~ 100Mb, and the table in question only contains 210,000 rows. What puzzles me is that the hang occurs AFTER Import says it imported all of the rows for the particular table. Does anyone know what is really taking place within the utility between tables being imported? Are the number of rows listed actually loaded at the point it says it has loaded them? Any thoughts/ideas would be appreciated. Thanks! -w __ Do You Yahoo!? Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Walter K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Sqlplus--how to suppress Connected.
Hi, I am calling a SQL script from a Unix Korn shell script and I can't seem to prevent SQL*Plus from responding with the text Connected. when my script logs into the database. I am calling the script using: sqlplus -S /NOLOG @script_name The first line of script_name.sql contains the text CONNECT / AS SYSDBA. Any suggestions? Thanks again! -w __ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Walter K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
SID on sqlplus prompt?
Hi, Is there a way to get the SID or database name displayed in the command prompt of SQL*Plus? Can this be generated dynamically if I perform a CONNECT user/pw ? Thanks! -w __ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail - only $35 a year! http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Walter K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
No Subject
I have the following in a blob column:
18 21:20:48 CDT 2001: Customer notified of: Order
Accepted
Wed Apr 18 22:11:09
CDT 2001: Order cancelled: demo
Wed Apr 18 22:11:09 CDT 2001: Flagged for
Review.
When I run the following via SQL*Plus:
select
dbms_lob.instr(order_text,utl_raw.cast_to_raw('Customer'),1,1)
from order_help where order_id=5944108;
I get the answer: 103
What's up with that? :) Shouldn't I be getting back a
result of 23 (give or take 1)?
The contents of the BLOB column (order_text) were
converted from a LONG RAW using the TO_LOB( )
function, prior to the above query.
Thanks again!
-w
__
Do You Yahoo!?
Get personalized email addresses from Yahoo! Mail - only $35
a year! http://personal.mail.yahoo.com/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Walter K
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: Tablespace Allocation Type Extent Management
How does one convert a tablespace from dictionary to local? I've wanted to do the reverse but I never saw anything in the docs that said this was an option. Are you saying then, that this is probably what happened in my case and now the tablespace is performing extent management based on 8k extents? I'm confused. Thanks again. -w --- Connor McDonald [EMAIL PROTECTED] wrote: When you convert from dictionary to local, then you will see the USER allocation type - because all of the segments that already exist in the tablespace. You also need to be careful when converting that you have a reasonable size for common denominator of all extents. If you had (for example) some 128k and some 136k extents, then the local tspace conversion would need it bitmaps based on 8k - not probably what you had in mind. Cheers Connor --- Walter K [EMAIL PROTECTED] wrote: I seem to be confused about the relationship between tablespace management and allocation methods. What is the USER allocation_type (as reported by DBA_TABLESPACES)? I thought this corresponded only to dictionary-managed tablespaces--I see it associated with locally-managed tablespaces as well. Also, I thought that PCTINCREASE was only applicable to dictionary-managed tablespaces. Again, I am seeing a PCT_INCREASE=50 for some locally-managed tablespaces in one of my databases. How is this possible? Based on everything I have read (docs metalink) about LMTs vs. Dictionary, I am perplexed by the results of the following query I ran: 1 select extent_management, allocation_type, pct_increase, count(*) 2 from dba_tablespaces 3 group by extent_management, allocation_type, pct_increase; EXTENT_MAN ALLOCATIO PCT_INCREASE COUNT(*) -- - -- DICTIONARY USER 0 4 DICTIONARY USER50 3 LOCAL SYSTEM 1 LOCAL UNIFORM 0 1 LOCAL USER50283 --- As always, your feedback is ALWAYS appreciated. -w __ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail - only $35 a year! http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Walter K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = Connor McDonald http://www.oracledba.co.uk (mirrored at http://www.oradba.freeserve.co.uk) Some days you're the pigeon, some days you're the statue Do You Yahoo!? Get your free @yahoo.co.uk address at http://mail.yahoo.co.uk or your free @yahoo.ie address at http://mail.yahoo.ie -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Connor=20McDonald?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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!? Get personalized email addresses from Yahoo! Mail - only $35 a year! http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Walter K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
DBMS_LOB.INSTR( ) ????
I have the following in a blob column:
18 21:20:48 CDT 2001: Customer notified of: Order
Accepted
Wed Apr 18 22:11:09
CDT 2001: Order cancelled: demo
Wed Apr 18 22:11:09 CDT 2001: Flagged for
Review.
When I run the following via SQL*Plus:
select
dbms_lob.instr(order_text,utl_raw.cast_to_raw('Customer'),1,1)
from order_help where order_id=5944108;
I get the answer: 103
What's up with that? :) Shouldn't I be getting back a
result of 23 (give or take 1)?
The contents of the BLOB column (order_text) were
converted from a LONG RAW using the TO_LOB( )
function, prior to the above query.
Thanks again!
-w
__
Do You Yahoo!?
Get personalized email addresses from Yahoo! Mail - only $35
a year! http://personal.mail.yahoo.com/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Walter K
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: Working with a BLOB
Sometimes the obvious isn't obvious...
I had the bulk of the query but was just getting hung
up on the 1st 75 bytes requirement. It never clicked
with me that the instr( ) function in the dbms_log
package already gave me a position and all I needed to
do was simply constrain it with a between clause.
The query below won't work with a blob because the
data is stored as binary so the '.com' needs to be
wrapped in the UTL_RAW.CAST_TO_RAW( ) function first
so the datatypes are compatible.
Thanks again!! :-)
-w
--- Vipul Lakhani [EMAIL PROTECTED] wrote:
just a guess but try
select dbms_lob.instr(blob_col, '.com')
from atable
where dbms_lob.instr(blob_col, '.com') between 0 an
75
-Original Message-
Sent: 30 May 2001 23:30
To: Multiple recipients of list ORACLE-L
I've been through the documentation on LOBs but am
still stuck trying to figure out how to interrogate
the contents of a BLOB.
We have a table with a BLOB column in it. All it
contains in text data (i.e. memo notes). Why it was
created as a BLOB and not a CLOB is unknown to me
and
done before I was hired. All I need to do is
determine
if a particular string ('.com') pattern exists in
the
column, within the first 75 bytes, and return its
starting position.
Would someone help me out? Thanks!
-w
__
Do You Yahoo!?
Get personalized email addresses from Yahoo! Mail -
only $35
a year! http://personal.mail.yahoo.com/
--
Please see the official ORACLE-L FAQ:
http://www.orafaq.com
--
Author: Walter K
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX:
(858) 538-5051
San Diego, California-- Public Internet
access / Mailing Lists
To REMOVE yourself from this mailing list, send an
E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of
'ListGuru') and in
the message BODY, include a line containing: UNSUB
ORACLE-L
(or the name of mailing list you want to be removed
from). You may
also send the HELP command for other information
(like subscribing).
_
This message has been checked for all known viruses
by Star Internet
delivered through the MessageLabs Virus Scanning
Service. For further
information visit http://www.star.net.uk/stats.asp
or alternatively call
01285 884400.
This message is intended only for the use of the
person(s) (the intended recipient (s)) to whom it
is addressed.
It may contain information which is privileged and
confidential.
If you are not the intended recipient, please
contact the sender as soon as possible.
The views expressed in this communication may not
necessarily be the views of InterX plc.
Any copyright in this message shall remain vested in
InterX plc © and the intended recipient may only
copy the same for internal business purposes or as
otherwise stated in this message.
--
Please see the official ORACLE-L FAQ:
http://www.orafaq.com
--
Author: Vipul Lakhani
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX:
(858) 538-5051
San Diego, California-- Public Internet
access / Mailing Lists
To REMOVE yourself from this mailing list, send an
E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of
'ListGuru') and in
the message BODY, include a line containing: UNSUB
ORACLE-L
(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!?
Get personalized email addresses from Yahoo! Mail - only $35
a year! http://personal.mail.yahoo.com/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Walter K
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Tablespace Allocation Type Extent Management
I seem to be confused about the relationship between tablespace management and allocation methods. What is the USER allocation_type (as reported by DBA_TABLESPACES)? I thought this corresponded only to dictionary-managed tablespaces--I see it associated with locally-managed tablespaces as well. Also, I thought that PCTINCREASE was only applicable to dictionary-managed tablespaces. Again, I am seeing a PCT_INCREASE=50 for some locally-managed tablespaces in one of my databases. How is this possible? Based on everything I have read (docs metalink) about LMTs vs. Dictionary, I am perplexed by the results of the following query I ran: 1 select extent_management, allocation_type, pct_increase, count(*) 2 from dba_tablespaces 3 group by extent_management, allocation_type, pct_increase; EXTENT_MAN ALLOCATIO PCT_INCREASE COUNT(*) -- - -- DICTIONARY USER 0 4 DICTIONARY USER50 3 LOCAL SYSTEM 1 LOCAL UNIFORM 0 1 LOCAL USER50283 --- As always, your feedback is ALWAYS appreciated. -w __ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail - only $35 a year! http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Walter K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Working with a BLOB
I've been through the documentation on LOBs but am
still stuck trying to figure out how to interrogate
the contents of a BLOB.
We have a table with a BLOB column in it. All it
contains in text data (i.e. memo notes). Why it was
created as a BLOB and not a CLOB is unknown to me and
done before I was hired. All I need to do is determine
if a particular string ('.com') pattern exists in the
column, within the first 75 bytes, and return its
starting position.
Would someone help me out? Thanks!
-w
__
Do You Yahoo!?
Get personalized email addresses from Yahoo! Mail - only $35
a year! http://personal.mail.yahoo.com/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Walter K
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Standby vs. Adv. Replication (Multi-master)
Hi, I'm looking for feedback on setting up a high-availability architecture for our production database. In a nutshell, we are a 24-hour shop and I need to be able to keep a secondary database (failover) in sync with the primary in case the primary fails. I have supported advanced replication (asynchronous) in the past but it was a single master relationship not multi-master. I'm leaning towards a standby database setup because my experience with advanced replication is less than favorable if/when transactions get out of sync. Also, one of the tables contains a LONG RAW. This column may go away or may be converted to a CLOB in the very near future but still needs to be kept in consideration when selecting a solution. The platform is Sun (SunOS 5.7) with 8.1.6. The secondary machine and database will most likely be located in another state. The database is small right now (~10Gb) and will continue to grow, but not too fast. What are your opinions? Is there an obvious choice between the two alternatives? Is there another alternative that I should be considering? Thanks VERY much in advance. -w __ Do You Yahoo!? Yahoo! Auctions - buy the things you want at great prices http://auctions.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Walter K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 vs. Adv. Replication (Multi-master)
- buy the things you want at great prices http://auctions.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Walter K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Space Estimation Performance (numbers)
Hi, How do I calculate the amount of physical space required for storing a number? (i.e. NUMBER, NUMBER(5), NUMBER(10,5), etc.) I believe Oracle uses 2's compliment for storing numbers. A value of 1 does not seem to take up the same amount of space as 9. TIA! -wk __ Do You Yahoo!? Yahoo! Auctions - buy the things you want at great prices http://auctions.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Walter K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Space Estimation (numbers)
Thanks for the info. I know about the VSIZE function, but how do I estimate the amount of space manually? What about negatives, fractional values, etc.? These all require differing amounts of space. Thanks again. Walt --- Toepke, Kevin M [EMAIL PROTECTED] wrote: Walter: You use the VSIZE() function. And you are right about 1 not taking the same amount of size as 9. 1 uses 2 bytes and 9 uses 4. SQL select vsize(1), vsize(9) from dual; VSIZE(1) VSIZE(9) 24 Kevin -Original Message- Sent: Monday, May 21, 2001 1:56 PM To: Multiple recipients of list ORACLE-L Hi, How do I calculate the amount of physical space required for storing a number? (i.e. NUMBER, NUMBER(5), NUMBER(10,5), etc.) I believe Oracle uses 2's compliment for storing numbers. A value of 1 does not seem to take up the same amount of space as 9. TIA! -wk __ Do You Yahoo!? Yahoo! Auctions - buy the things you want at great prices http://auctions.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Walter K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Toepke, Kevin M INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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! Auctions - buy the things you want at great prices http://auctions.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Walter K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Space Estimation (numbers)
Thanks, but the table doesn't exist yet. Also, I need to estimate the space needs for a column of a numeric datatype, not the row itself. --- John Lewis [EMAIL PROTECTED] wrote: Oracle stores the avg_row_length in all_tables view. Use analyze_schema to get the most recent numbers. -Original Message- Sent: Monday, May 21, 2001 12:12 PM To: Multiple recipients of list ORACLE-L Thanks for the info. I know about the VSIZE function, but how do I estimate the amount of space manually? What about negatives, fractional values, etc.? These all require differing amounts of space. Thanks again. Walt --- Toepke, Kevin M [EMAIL PROTECTED] wrote: Walter: You use the VSIZE() function. And you are right about 1 not taking the same amount of size as 9. 1 uses 2 bytes and 9 uses 4. SQL select vsize(1), vsize(9) from dual; VSIZE(1) VSIZE(9) 24 Kevin -Original Message- Sent: Monday, May 21, 2001 1:56 PM To: Multiple recipients of list ORACLE-L Hi, How do I calculate the amount of physical space required for storing a number? (i.e. NUMBER, NUMBER(5), NUMBER(10,5), etc.) I believe Oracle uses 2's compliment for storing numbers. A value of 1 does not seem to take up the same amount of space as 9. TIA! -wk __ Do You Yahoo!? Yahoo! Auctions - buy the things you want at great prices http://auctions.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Walter K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Toepke, Kevin M INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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! Auctions - buy the things you want at great prices http://auctions.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Walter K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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! Auctions - buy the things you want at great prices http://auctions.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Walter K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send
RE: Space Estimation (numbers)
Actually, I just answered my own question. The following is from MetaLink. I have seen this article before but could not find it this morning for the life of me!! :-) --- Doc ID: Note:1031902.6 Internal Numeric Data Storage - Oracle stores numeric data in variable-length format. Each value is stored in scientific notation, with one byte used to store the exponent and up to 20 bytes to store the mantissa. However, there are only 38 digits of precision. Oracle does not store leading and trailing zeroes. For example, the number 412 is stored in a format similiar to 4.12 X 10^2, with one byte used to store the exponent(2) and two bytes used to store the three digit significant digits of the mantissa (4,1,2) for a total length of 3. In the example below, the data_value column on the extreme left shows the number value which is being dumped in the dump(number_value) column on the opposite side to find the length of the field. SVRMGR select data_value, dump(number_value) from number_data; DATA_VALUE DUMP(NUMBER_VALUE) --- --- 412 Typ=2 Len=3: 194,5,13 1 Typ=2 Len=2: 193,2 10 Typ=2 Len=2: 193,11 1 Typ=2 Len=2: 195,2 10001 Typ=2 Len=4: 195,2,1,2 -1 Typ=2 Len=3: 62,100,102 -10 Typ=2 Len=3: 62,91,102 -1 Typ=2 Len=3: 60,100,102 -10001 Typ=2 Len=5: 60,100,101,100,102 10.25 Typ=2 Len=3: 193,11,26 100.25 Typ=2 Len=4: 194,2,1,26 1025 Typ=2 Len=3: 194,11,26 10225 Typ=2 Len=4: 195,2,1,26 11 Typ=2 Len=2: 193,12 111 Typ=2 Len=3: 194,2,12 Simple Formula to calculate the length of Number(p) where p is the precision of a given value (scale has no effect), can be calculated using: length = floor [(p+1)/2] + 1 add +1 byte (only for negative numbers where the number of significant digits is less than 38). --- Walter K [EMAIL PROTECTED] wrote: Thanks, but the table doesn't exist yet. Also, I need to estimate the space needs for a column of a numeric datatype, not the row itself. --- John Lewis [EMAIL PROTECTED] wrote: Oracle stores the avg_row_length in all_tables view. Use analyze_schema to get the most recent numbers. -Original Message- Sent: Monday, May 21, 2001 12:12 PM To: Multiple recipients of list ORACLE-L Thanks for the info. I know about the VSIZE function, but how do I estimate the amount of space manually? What about negatives, fractional values, etc.? These all require differing amounts of space. Thanks again. Walt --- Toepke, Kevin M [EMAIL PROTECTED] wrote: Walter: You use the VSIZE() function. And you are right about 1 not taking the same amount of size as 9. 1 uses 2 bytes and 9 uses 4. SQL select vsize(1), vsize(9) from dual; VSIZE(1) VSIZE(9) 24 Kevin -Original Message- Sent: Monday, May 21, 2001 1:56 PM To: Multiple recipients of list ORACLE-L Hi, How do I calculate the amount of physical space required for storing a number? (i.e. NUMBER, NUMBER(5), NUMBER(10,5), etc.) I believe Oracle uses 2's compliment for storing numbers. A value of 1 does not seem to take up the same amount of space as 9. TIA! -wk __ Do You Yahoo!? Yahoo! Auctions - buy the things you want at great prices http://auctions.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Walter K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Toepke, Kevin M INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL
RE: Space Estimation (numbers)
Actually, I just answered my own question. The
following is from MetaLink. I have seen this article
before but could not find it this morning for the life
of me!! :-)
---
Doc ID: Note:1031902.6
Internal Numeric Data Storage
-
Oracle stores numeric data in variable-length format.
Each value is stored in scientific notation, with one
byte used to store the exponent and up to 20 bytes to
store the mantissa. However, there are only 38 digits
of precision. Oracle does not store leading and
trailing zeroes.
For example, the number 412 is stored in a format
similiar to 4.12 X 10^2, with one byte used to store
the exponent(2) and two bytes used to store the three
digit significant digits of the mantissa (4,1,2) for a
total length of 3.
In the example below, the data_value column on the
extreme left shows the number value which is being
dumped in the dump(number_value) column on the
opposite side to find the length of the field.
SVRMGR select data_value, dump(number_value) from
number_data;
DATA_VALUE DUMP(NUMBER_VALUE)
--- ---
412 Typ=2 Len=3: 194,5,13
1 Typ=2 Len=2: 193,2
10 Typ=2 Len=2: 193,11
1 Typ=2 Len=2: 195,2
10001 Typ=2 Len=4: 195,2,1,2
-1Typ=2 Len=3: 62,100,102
-10 Typ=2 Len=3: 62,91,102
-1 Typ=2 Len=3: 60,100,102
-10001 Typ=2 Len=5: 60,100,101,100,102
10.25 Typ=2 Len=3: 193,11,26
100.25 Typ=2 Len=4: 194,2,1,26
1025 Typ=2 Len=3: 194,11,26
10225 Typ=2 Len=4: 195,2,1,26
11 Typ=2 Len=2: 193,12
111 Typ=2 Len=3: 194,2,12
Simple Formula to calculate the length of Number(p)
where p is the
precision of a given value (scale has no effect), can
be calculated
using:
length = floor [(p+1)/2] + 1
add +1 byte (only for negative numbers
where the number of significant digits
is less than 38).
--- Walter K [EMAIL PROTECTED] wrote:
Thanks, but the table doesn't exist yet. Also, I
need
to estimate the space needs for a column of a
numeric
datatype, not the row itself.
--- John Lewis [EMAIL PROTECTED] wrote:
Oracle stores the avg_row_length in all_tables
view.
Use analyze_schema to get the most recent numbers.
-Original Message-
Sent: Monday, May 21, 2001 12:12 PM
To: Multiple recipients of list ORACLE-L
Thanks for the info. I know about the VSIZE
function,
but how do I estimate the amount of space
manually?
What about negatives, fractional values, etc.?
These
all require differing amounts of space.
Thanks again.
Walt
--- Toepke, Kevin M [EMAIL PROTECTED]
wrote:
Walter:
You use the VSIZE() function. And you are
right
about 1 not taking the
same amount of size as 9. 1 uses 2 bytes
and
9 uses 4.
SQL select vsize(1), vsize(9) from
dual;
VSIZE(1) VSIZE(9)
24
Kevin
-Original Message-
Sent: Monday, May 21, 2001 1:56 PM
To: Multiple recipients of list ORACLE-L
Hi,
How do I calculate the amount of physical space
required for storing a number? (i.e. NUMBER,
NUMBER(5), NUMBER(10,5), etc.) I believe Oracle
uses
2's compliment for storing numbers. A value of
1
does not seem to take up the same amount of
space
as
9.
TIA!
-wk
__
Do You Yahoo!?
Yahoo! Auctions - buy the things you want at
great
prices
http://auctions.yahoo.com/
--
Please see the official ORACLE-L FAQ:
http://www.orafaq.com
--
Author: Walter K
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051
FAX:
(858) 538-5051
San Diego, California-- Public Internet
access / Mailing Lists
To REMOVE yourself from this mailing list, send
an
E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of
'ListGuru') and in
the message BODY, include a line containing:
UNSUB
ORACLE-L
(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: Toepke, Kevin M
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051
FAX:
(858) 538-5051
San Diego, California-- Public Internet
access / Mailing Lists
To REMOVE yourself from this mailing list, send
an
E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of
'ListGuru') and in
the message BODY, include a line containing:
UNSUB
ORACLE-L
(or the name of mailing
ODBC bad?
I have been told in the past that ODBC connections to the database are slow and very resource intensive, but unfortunately this was not explained. Fortunately, I haven't really had to deal with ODBC connections much. However, in my new job, several users are using MS Access as their query tool and it looks like the number is growing. So, are ODBC connections to the database really slow and a resource problem? If so, why? Also, is there anything special in regards to the database that I should be watching or tuning outside of the norm just because I have a bunch of Access users hitting the database? Thanks again for the assistance! -w __ Do You Yahoo!? Yahoo! Auctions - buy the things you want at great prices http://auctions.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Walter K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
ODBC Connections Bad?
I have been told in the past that ODBC connections to the database are slow and very resource intensive, but unfortunately this was not explained. Fortunately, I haven't really had to deal with ODBC connections much. However, in my new job, several users are using MS Access as their query tool and it looks like the number is growing. So, are ODBC connections to the database really slow and a resource problem? If so, why? Also, is there anything special in regards to the database that I should be watching or tuning outside of the norm just because I have a bunch of Access users hitting the database? Thanks again for the assistance! -w __ Do You Yahoo!? Yahoo! Auctions - buy the things you want at great prices http://auctions.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Walter K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
ANSI SQL Compliance (Oracle vs. SQL Server)
Which RDBMS is more ANSI SQL compliant, Oracle or MS SQL Server? I have some frustrated SQL Server developers that only understand how to write inner and outer joins using SQL Server's syntax. Don't DB2, Informix and Sybase use the same method of defining joins as predicates? Just curious. Maybe I'm crazy. -w __ Do You Yahoo!? Yahoo! Auctions - buy the things you want at great prices http://auctions.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Walter K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Constraints with Synonyms?
Hi, I have a need to create a synonym in a schema (A) for a table that is another schema (B). This is no problem. However, the table in question is a "parent" table and I cannot create the "references" constraint from the "child" to the "parent" when the "parent" is a synonym. When I attempt to do so I get an ORA-1031 error (Insufficient Privileges). Is this a known limitation of synonyms or am I missing something here? I find the 1031 error a strange one to receive if this a limitation of synonyms--schema A has DBA role granted to it so the problem shouldn't be one of permissions. Any suggestions/feedback would be appreciated. Thanks!! -w PS: Recreating the table from schema B in schema A is not an option, unfortunately.
Bstat/Estat File - Table?
Hi, Just curious if anyone has a script(s) that will take the contents of the report.txt file and turn it into loadable files or something along those lines. I know about 'statspack' but many companies are hesitant to permit the creation of tables for storing statistics on their production databases. Thanks. -wk
Sar Loader Script?
By chance, does anyone have a script that will reparse a collection of sar statistics, that came from "sar -d", into a file that can be used by SQL*Loader?My problem is that the timestamp, which is just that--no date, is only on the first record of each sample rather than every record and I don't want the blank lines in between the samples. Perhaps this is a simple awk routine but I don't know awk well enough to do this. Any assistance is appreciated!-wHere is a sample of the data I am trying to work with:SunOS pandora 5.6 Generic_115161-23 sun4u 01/30/01 08:00:00 device %busy avque r+w/s blks/s avwait avserv 08:01:00 nfs1 0 0.0 0 0 0.0 0.0 sd5 4 0.0 7 132 0.0 6.5 sd5,c 0 0.0 0 0 0.0 0.0 sd5,d 0 0.0 0 0 0.0 0.0 sd5,e 4 0.0 6 132 0.0 7.2 sd22 1 0.1 1 21 0.0 81.3 sd22,a 0 0.0 0 0 0.0 0.0 sd22,b 0 0.0 0 0 0.0 0.0 sd22,c 0 0.0 0 0 0.0 0.0sd22,d 0 0.0 0 0 0.0 0.0 sd22,e 0 0.0 0 0 0.0 0.0 sd22,g 1 0.1 1 21 0.0 81.3 sd22,h 0 0.0 0 0 0.0 0.0 sd23 2 0.0 3 101 0.0 7.1 sd23,c 0 0.0 0 0 0.0 0.0 sd23,d 0 0.0 0 0 0.0 0.0 sd23,e 2 0.0 3 101 0.0 7.1 sd34 0 0.0 0 0 0.0 0.0 sd45 1 0.1 1 21 0.0 96.4 sd45,a 0 0.0 0 0 0.0 0.0 sd45,b 0 0.0 0 0 0.0 0.0 sd45,c 0 0.0 0 0 0.0 0.0 sd45,d 0 0.0 0 0 0.0 0.0 sd45,e 1 0.1 1 21 0.0 96.4 sd45,g 0 0.0 0 0 0.0 0.0 sd45,h 0 0.0 0 0 0.0 0.0 sd46 0 0.0 0 0 0.0 0.0 sd46,c 0 0.0 0 0 0.0 0.0 sd46,d 0 0.0 0 0 0.0 0.0 sd46,e 0 0.0 0 0 0.0 0.0 sd66 0 0.0 0 0 0.0 0.0 sd125 0 0.0 0 0 0.0 0.0 sd126 0 0.0 0 0 0.0 0.0 sd127 0 0.0 0 0 0.0 0.0 sd127,a 0 0.0 0 0 0.0 0.0 sd127,b 0 0.0 0 0 0.0 0.0 sd127,c 0 0.0 0 0 0.0 0.0 sd127,d 0 0.0 0 0 0.0 0.0 sd127,e 0 0.0 0 0 0.0 0.0 sd127,g 0 0.0 0 0 0.0 0.0 sd328 2 0.0 4 70 0.0 4.9sd328,c 0 0.0 0 0 0.0 0.0 sd328,d 0 0.0 0 0 0.0 0.0 sd328,e 2 0.0 4 70 0.0 4.9 sd329 0 0.0 0 0 0.0 0.0 sd329,c 0 0.0 0 0 0.0 0.0 sd329,d 0 0.0 0 0 0.0 0.0 sd329,e 0 0.0 0 0 0.0 0.0 sd330 0 0.0 0 0 0.0 0.0 st32 0 0.0 0 0 0.0 0.0 st33 0 0.0 0 0 0.0 0.0 08:02:00 nfs1 0 0.0 0 0 0.0 0.0 sd5 2 0.0 3 82 0.0 7.4 sd5,c 0 0.0 0 0 0.0 0.0 sd5,d 0 0.0 0 0 0.0 0.0 sd5,e 2 0.0 3 82 0.0 9.1 sd22 1 0.0 1 11 0.0 53.6 sd22,a 0 0.0 0 0 0.0 0.0 sd22,b 0 0.0 0 0 0.0 0.0 sd22,c 0 0.0 0 0 0.0 0.0 sd22,d 0 0.0 0 0 0.0 0.0 sd22,e 0 0.0 0 0 0.0 0.0 sd22,g 1 0.0 1 11 0.0 53.6 sd22,h 0 0.0 0 0 0.0 0.0 sd23 1 0.0 1 49 0.0 12.7 sd23,c 0 0.0 0 0 0.0 0.0 sd23,d 0 0.0 0 0 0.0 0.0 sd23,e 1 0.0 1 49 0.0 12.7 sd34 0 0.0 0 0 0.0 0.0sd45 1 0.0 1 11 0.0 63.4 sd45,a 0 0.0 0 0 0.0 0.0 sd45,b 0 0.0 0 0 0.0 0.0 sd45,c 0 0.0 0 0 0.0 0.0 sd45,d 0 0.0 0 0 0.0 0.0 sd45,e 1 0.0 1 11 0.0 63.4 sd45,g 0 0.0 0 0 0.0 0.0 sd45,h 0 0.0 0 0 0.0 0.0 sd46 0 0.0 0 0 0.0 0.0 sd46,c 0 0.0 0 0 0.0 0.0 sd46,d 0 0.0 0 0 0.0 0.0 sd46,e 0 0.0 0 0 0.0 0.0 sd66 0 0.0 0 0 0.0 0.0 sd125 0 0.0 0 0 0.0 0.0 sd126 0 0.0 0 0 0.0 0.0 sd127 0 0.0 0 0 0.0 0.0 sd127,a 0 0.0 0 0 0.0 0.0 sd127,b 0 0.0 0 0 0.0 0.0 sd127,c 0 0.0 0 0 0.0 0.0 sd127,d 0 0.0 0 0 0.0 0.0 sd127,e 0 0.0 0 0 0.0 0.0 sd127,g 0 0.0 0 0 0.0 0.0 sd328 1 0.0 3 37 0.0 3.7 sd328,c 0 0.0 0 0 0.0 0.0 sd328,d 0 0.0 0 0 0.0 0.0 sd328,e 1 0.0 3 37 0.0 3.7 sd329 0 0.0 0 0 0.0 0.0 sd329,c 0 0.0 0 0 0.0 0.0 sd329,d 0 0.0 0 0 0.0 0.0 sd329,e 0 0.0 0 0 0.0 0.0 sd330 0 0.0 0 0 0.0 0.0 st32 0 0.0 0 0 0.0 0.0st33 0 0.0 0 0 0.0 0.0
