RE: cache settings NT
thanks for your explanation, i'll go for the 0% read - 100% write cache. -Oorspronkelijk bericht- Van: Igor Neyman [SMTP:[EMAIL PROTECTED]] Verzonden:vrijdag 6 september 2002 22:43 Aan: Multiple recipients of list ORACLE-L Onderwerp:Re: cache settings NT That's why you use write cache only if it's battery supported. Then it should be safe. Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, September 06, 2002 2:28 PM I'll take a shot at this. Don't have any write caching. Oracle may think that it has safely committed the transactions and they are written to disk when they are in fact only in cache. If the machine were to shut down ungracefully, i.e., someone kicks out the plug, then your database would not be in a consistent state. Granted, the odds are small, but is it worth the risk? If the database is archived and you don't mind the increased chance of having to do a recovery then I would say 0% read - 100% write. Oracle is doing it's own read caching in the SGA and it's unlikely that the disk cache will correctly anticipate Oracle's read requirements. Therefore, caching on the read side is only likely to increase IO. Write caching does not lead to any increased IO but it does have some small risk of producing an inconsistent database. GKor @rdw.nl To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent by: rootcc: Subject: cache settings NT 09/06/2002 04:18 AM Please respond to ORACLE-L Hi all I have the following question for you : what are the preferred values for the several cache settings on the fysical disk units (NT W2K) e.g. 100 % read cache - 0 % write cache 50% read cache - 50% write cache is there a difference between settings for an OLTP or DSS solution. etc anyone with an explanation vr. gr. g.g. kor rdw ict groningen -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Thomas Day INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Igor Neyman INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: bind variables
Hi Nicoll, The importance of bind variables, to use in OLTP application is such that if we use, we survive else one or the other day we would feel uncomfortable over the performance issues and would repent over the scalability of the application. Yes, if we use bind variables, the parser does not know how best to execute the statement but basis on rough estimates (50% values are such that the table is holding on basis the WHERE Clause),it generates many execution plans and holds the one which has lowest cost in the V$library cache because at that time the optimizer is not sure what values to bind. But in the case of DSS applications yes I do agree that these bind variables are problematic as the data is in abundance and we have to use the data skewness and its other credentials for the optimizer best to use as we are playing with abundance of data. Thanks Vikas Khanna -Original Message- Sent: 06 September 2002 19:59 PM To: Multiple recipients of list ORACLE-L John, You would have to ask while I've got the book at home. But it's an Orielly book on PL/SQL Programming. Sorry off the top of my head I can't remember the author or title. Dick Goulet Reply Separator Author: John Dunn [EMAIL PROTECTED] Date: 9/6/2002 7:38 AM Despite the importance of using bind variables, the Oracle documentation seems to make very little reference to how to use them(for example the PL/SQL manual) Can anyone point me at any decent documentation on the subject of using bind variables in PL/SQL? John -Original Message- From: Nicoll, Iain (Calanais) [SMTP:[EMAIL PROTECTED]] Sent: 06 September 2002 15:23 To: Multiple recipients of list ORACLE-L Subject: RE: Must Read for Every Developer and DBA I thought that bind variables were faster but you always have to ensure that if you're accessing by data which may be heavily skewed and histograms would usually help you may not want to use bind variables as they will disable the use of histograms. In saying that it doesn't look as though that would be the case here. Iain Nicoll -Original Message- Sent: Friday, September 06, 2002 2:33 PM To: Multiple recipients of list ORACLE-L Hello Vikas, As You said We should always make use of bind variables as it executes faster as compare to the statements where we do not make use of bind variables. Q1) Can you please take a more specific example as how a statement can be altered to make use of bind variable. Q2) I made use of SELECT SQL_TEXT FROM V$SQLAREA WHERE ROWNUM 5 to get few samples for you These are as follows UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID ='A101675' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 67557405 AND AWB_SUFFIX = ' ' AND PROCESS = 1 ANDUSER_ID = 'A101675' UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID = 'A101675' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 68221156 AND AWB_SUFFIX = ' ' AND PROCESS = 1 AND USER_ID = 'A101675' UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID ='A105722' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 67557405 AND AWB_SUFFIX = 'A ' AND PROCESS = 1 AND USER_ID = 'A105722' UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID = 'A105722' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 67557416 AND AWB_SUFFIX = ' ' AND PROCESS = 1 AND USER_ID = 'A105722 How can I Introduce bind variables in these statements ? I may be sending a wrong SAMPLE as I feel I should apply your remove constant function and then send few SQL statements Warm Regards, Om In your case -- you are NOT using bind variables. Taking your update statement here: UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID = 'A101675' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 67557405 AND AWB_SUFFIX = ' ' AND PROCESS = 1 AND USER_ID = 'A101675' that SHOULD BE recoded in the application to become : update cnst_queue set process = :b1, user_id = :b2, date_queued = sysdate, where awb_prefix = :b3 and awb_number = :b4 and awb_suffix = :b5 and awb_process = :b6 and user_id = :b7; and bind in those values before you execute this statement. There are ways in which it could be done and vary from language to language and environment to environment but they ALL support it. You MUST do this. In this case,the first time you execute this statement you need to parse this statement (HARD PARSING) and once the execution plan gets into the SHARED POOL (V$libraryCache) the other users can use this to great effect. They would not reparse this statement again and again and but does do the soft parsing of it. So One Parse may lead to MANY executions instead of
Re: how to find no. of transactions/sec ?
Check the 'user commits' statistic in v$sysstat hth connor --- oraora oraora [EMAIL PROTECTED] wrote: Guys, my DB is on 8.1.6/Win2K. how do i find the number of transactions per second in my DB ? TIA. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: oraora oraora INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 http://www.oaktable.net Remember amateurs built the ark - Professionals built the Titanic __ 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).
RE: bind variables
Yes. -Original Message- Sent: Friday, September 06, 2002 4:28 PM To: Multiple recipients of list ORACLE-L Kevin, Are you saying then, that by default, any static statement that is executed within PL/SQL will not have be re-parsed eg sp_proc(var in varchar2) as begin select last_name from emp where last_name = var; end; If that's the case, I wont have to change much code. mkb --- Toepke, Kevin M [EMAIL PROTECTED] wrote: Actually its easy. Any variable declared in PL/SQL and referenced in a non-dynamic SQL statement is a bind variable. In the following example (#1), some_var is an output bind-variable and other_var is a input bind variable. PL/SQL does manipulation on the statement and will send something like the following (#2) to the database #1 DECLARE some_var NUMBER(1); other_var NUMBER(1) BEGIN SELECT 1 INTO some_var FROM my_table WHERE my_column = other_var; END; #2 SELECT 1 FROM MY_TABLE WHERE MY_COLUMN = :1 Kevin -Original Message- Sent: Friday, September 06, 2002 1:59 PM To: Multiple recipients of list ORACLE-L John, You would have to ask while I've got the book at home. But it's an Orielly book on PL/SQL Programming. Sorry off the top of my head I can't remember the author or title. Dick Goulet Reply Separator Author: John Dunn [EMAIL PROTECTED] Date: 9/6/2002 7:38 AM Despite the importance of using bind variables, the Oracle documentation seems to make very little reference to how to use them(for example the PL/SQL manual) Can anyone point me at any decent documentation on the subject of using bind variables in PL/SQL? John -Original Message- From: Nicoll, Iain (Calanais) [SMTP:[EMAIL PROTECTED]] Sent: 06 September 2002 15:23 To: Multiple recipients of list ORACLE-L Subject: RE: Must Read for Every Developer and DBA I thought that bind variables were faster but you always have to ensure that if you're accessing by data which may be heavily skewed and histograms would usually help you may not want to use bind variables as they will disable the use of histograms. In saying that it doesn't look as though that would be the case here. Iain Nicoll -Original Message- Sent: Friday, September 06, 2002 2:33 PM To: Multiple recipients of list ORACLE-L Hello Vikas, As You said We should always make use of bind variables as it executes faster as compare to the statements where we do not make use of bind variables. Q1) Can you please take a more specific example as how a statement can be altered to make use of bind variable. Q2) I made use of SELECT SQL_TEXT FROM V$SQLAREA WHERE ROWNUM 5 to get few samples for you These are as follows UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID ='A101675' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 67557405 AND AWB_SUFFIX = ' ' AND PROCESS = 1 ANDUSER_ID = 'A101675' UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID = 'A101675' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 68221156 AND AWB_SUFFIX = ' ' AND PROCESS = 1 AND USER_ID = 'A101675' UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID ='A105722' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 67557405 AND AWB_SUFFIX = 'A ' AND PROCESS = 1 AND USER_ID = 'A105722' UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID = 'A105722' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 67557416 AND AWB_SUFFIX = ' ' AND PROCESS = 1 AND USER_ID = 'A105722 How can I Introduce bind variables in these statements ? I may be sending a wrong SAMPLE as I feel I should apply your remove constant function and then send few SQL statements Warm Regards, Om In your case -- you are NOT using bind variables. Taking your update statement here: UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID = 'A101675' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 67557405 AND AWB_SUFFIX = ' ' AND PROCESS = 1 AND USER_ID = 'A101675' that SHOULD BE recoded in the application to become : update cnst_queue set process = :b1, user_id = :b2, date_queued = sysdate, where awb_prefix = :b3 and awb_number = :b4 and awb_suffix = :b5 and awb_process = :b6 and user_id = :b7; and bind in those values before you execute this statement. There are ways in which it could be done and vary from language to language and environment to environment but they ALL support it. You MUST do this. In this case,the first time you execute this statement you need to parse
test - please ignore
Just testing, I'm not getting any messages lately. Igor Neyman, OCP DBA[EMAIL PROTECTED]
RE: connect to the databases without using tnsnames.ora?
Correct. And since I use ONames, a TNSNAMES.ORA file is not supposed to be required on the client. QCO, however, still requires it, but there are workarounds. Tbink about it. What's the point of centralizing your instance names in ONames if you still require that same info to be somehow magically synched on each client? Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- From: Hemant K Chitale [mailto:[EMAIL PROTECTED]] Sent: Friday, September 06, 2002 11:59 AM To: Multiple recipients of list ORACLE-L Subject: RE: connect to the databases without using tnsnames.ora? What do you mean by mistakenly require a TNSNAMES.ORA file ? The TNSNAMES.ORA file is required unless you are using either of a) Oracle Names b) Thin Java client Hemant At 06:38 AM 06-09-02 -0800, you wrote: As an FYI, some 3rd-party products still mistakenly require a TNSNAMES.ORA file. Quest's QCO (at least 2.0, haven't D/L'd 2.1 yet and Quest Support tells me it will be fixed in 3.0 next Q1) is one of them. Also, there is at least one circumstance that requires a TNSNAMES.ORA on a server. I can't remember for the life of me what it is, but something with the Intelligent Agent sticks in my head. Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: test - please ignore
Can you hear me now Good ;o) -Original Message-From: Igor Neyman [mailto:[EMAIL PROTECTED]]Sent: Monday, September 09, 2002 9:38 AMTo: Multiple recipients of list ORACLE-LSubject: test - please ignore Just testing, I'm not getting any messages lately. Igor Neyman, OCP DBA[EMAIL PROTECTED]
RE: Correlated subquery performance in 8i 9i
Hello Larry, The better performance of correlated subqueries is accomplished in 9i without manipulating any parameters. It's automatic! As far as I remember, Oracle uses a 'sort-merge' approach and uses the temp file for this. You can reach Gaja @ [EMAIL PROTECTED] Prakash -Original Message- Sent: Saturday, September 07, 2002 11:23 AM To: Multiple recipients of list ORACLE-L How about a little more info? I'm kind of reading between the lines here but the comments about correlated queries and things changing in 9i make me wonder if Gaja was getting at the ability to exploit hash/merge joins for correlated queries. For example: 1 select * 2 from code_master cm 3 where exists (select null 4from code_detail cd 5* where cm.code = cd.code) SQL / Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=65 Card=5000 Bytes=65000) 10 FILTER 21 TABLE ACCESS (FULL) OF 'CODE_MASTER' (Cost=65 Card=5000 Bytes=65000) 31 INDEX (RANGE SCAN) OF 'CODE_DETAIL_PK' (UNIQUE) (Cost=3 Card=3 Bytes=12) For each row returned from code_master, a correlated query would be issued against code_detail (using an index). Now, if I put this in an in-line view, I can get a hash-join approach, which is desired in this case since I am getting every row from each table -- I would *not* want a correlated approach in this case, I would prefer a hash approach: SQL select * 2 from code_master cm, 3 (select code 4 from code_detail) cd 5 where cm.code = cd.code 6 / Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1106 Card=299600 Bytes=5093200) 10 HASH JOIN (Cost=1106 Card=299600 Bytes=5093200) 21 TABLE ACCESS (FULL) OF 'CODE_MASTER' (Cost=340 Card=10 Bytes=130) 31 INDEX (FAST FULL SCAN) OF 'CODE_DETAIL_PK' (UNIQUE) (Cost=131 Card=299600 Bytes=1198400) So, by going to the in-line view, I get better performance, but this doesn't necessarily mean it is best for all queries, just this query. Now, about 9i and how things change. I wonder if Gaja was referring to always_semi_join becoming an undocumented parameter _always_semi_join and the CBO deciding whether to un-correlate a correlated query and use a hash approach. Note that this is also available in 8i if you set always_semi_join = hash, or use the semi join hints (hash_sj / merge_sj), and the conditions for a semi join are met (and if not, the in-line view approach is a good workaround): SQL alter session set always_semi_join = hash; Session altered. SQL select * 2 from code_master cm 3 where exists (select null 4from code_detail cd 5where cm.code = cd.code) 6 / Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=831 Card=99900 Bytes=1698300) 10 HASH JOIN (SEMI) (Cost=831 Card=99900 Bytes=1698300) 21 TABLE ACCESS (FULL) OF 'CODE_MASTER' (Cost=65 Card=10 Bytes=130) 31 INDEX (FAST FULL SCAN) OF 'CODE_DETAIL_PK' (UNIQUE) (Cost=131 Card=299600 Bytes=1198400) Note that a correlated approach for the exists is no longer used. A hash approach, similar to that when using an in-line technique, is available. Now in 9i, the CBO will choose to stay correlated, or, to un-correlate and use a hash/merge approach, if you do not set the _always_semi_join parameter. SQL connect scott/tiger@or91 Connected. SQL set autotrace trace explain SQL select * 2 from code_master cm 3 where exists (select null 4from code_detail cd 5where cm.code = cd.code) 6 / Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=770 Card=99900 Bytes=1498500) 10 HASH JOIN (SEMI) (Cost=770 Card=99900 Bytes=1498500) 21 TABLE ACCESS (FULL) OF 'CODE_MASTER' (Cost=77 Card=10 Bytes=110) 31 INDEX (FAST FULL SCAN) OF 'CD_CODE_IDX' (NON-UNIQUE) (Cost=208 Card=299600 Bytes=1198400) Note that the CBO chose a hash approach for the above example. Now, if I do something highly selective on code_master (foo_date = trunc(sysdate) - 200), where I *would* want a correlated approach, note that the CBO chooses the correlated approach as opposed to the hash approach above: 1 select * 2 from code_master cm 3 where exists (select null 4from code_detail cd 5where cm.code = cd.code) 6* and foo_date = trunc(sysdate) - 200 SQL / Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=6 Card=1 Bytes=15) 10 NESTED LOOPS (SEMI) (Cost=6 Card=1 Bytes=15) 21 TABLE ACCESS (BY INDEX ROWID) OF 'CODE_MASTER' (Cost=4 Card=1 Bytes=11) 3
RE: Recreate database script
If you export with rows=n, you get a text file with all the ddl to recreate the exportable objects. However, packages/procedures are formatted and not at all easy to use to recreate and the storage clauses are all in bytes. Not a pretty method, but certainly one that can be used. -Original Message- Sent: Friday, September 06, 2002 3:59 PM To: Multiple recipients of list ORACLE-L Export can do this. Right? But the output is not a script but a binary file only Import can understand. ltiu Connie Milliken wrote: Does anyone have a script that will write another script to recreate a particular database quickly with all the info specific to that particular database? Seems to me that I have seen this somewhere before, but I am not sure where. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: ltiu INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Fink, Dan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Recreate database script
/start of plug if you are not using locally managed tablespaces, you can find scripts to do this in the SQL and PL/SQL Annotated Archives /end plug --- Fink, Dan [EMAIL PROTECTED] wrote: If you export with rows=n, you get a text file with all the ddl to recreate the exportable objects. However, packages/procedures are formatted and not at all easy to use to recreate and the storage clauses are all in bytes. Not a pretty method, but certainly one that can be used. -Original Message- Sent: Friday, September 06, 2002 3:59 PM To: Multiple recipients of list ORACLE-L Export can do this. Right? But the output is not a script but a binary file only Import can understand. ltiu Connie Milliken wrote: Does anyone have a script that will write another script to recreate a particular database quickly with all the info specific to that particular database? Seems to me that I have seen this somewhere before, but I am not sure where. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: ltiu INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Fink, Dan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Yahoo! Finance - Get real-time stock quotes http://finance.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).
RE: Recreate database script
DBATool will fix this problem for you, as well as a whole host of added features for database creation scripts, such as rule based storage attribute changes for example - e.g. change ALL indexes in a schema to be stored in an INDEXES tablespace. You can also use it to create HTML based DDL documentation for your schemas instead of spooling creation scripts.. You can find out more, or download a copy from: http://www.cool-tools.co.uk/products/dbatool.html It's free as well.. ;) HTH Mark === Mark Leith | T: +44 (0)1905 330 281 Sales Marketing | F: +44 (0)870 127 5283 Cool Tools UK Ltd | E: [EMAIL PROTECTED] === http://www.cool-tools.co.uk Maximising throughput performance -Original Message- Sent: 09 September 2002 16:33 To: Multiple recipients of list ORACLE-L If you export with rows=n, you get a text file with all the ddl to recreate the exportable objects. However, packages/procedures are formatted and not at all easy to use to recreate and the storage clauses are all in bytes. Not a pretty method, but certainly one that can be used. -Original Message- Sent: Friday, September 06, 2002 3:59 PM To: Multiple recipients of list ORACLE-L Export can do this. Right? But the output is not a script but a binary file only Import can understand. ltiu Connie Milliken wrote: Does anyone have a script that will write another script to recreate a particular database quickly with all the info specific to that particular database? Seems to me that I have seen this somewhere before, but I am not sure where. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: ltiu INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Fink, Dan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mark Leith INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Recreate database script
Thanks. I wonder if you can treat an export file as a *.sql script and run it off sqlplus with the @@script.sql command? I shall give this a try. ltiu Fink, Dan wrote: If you export with rows=n, you get a text file with all the ddl to recreate the exportable objects. However, packages/procedures are formatted and not at all easy to use to recreate and the storage clauses are all in bytes. Not a pretty method, but certainly one that can be used. -Original Message- Sent: Friday, September 06, 2002 3:59 PM To: Multiple recipients of list ORACLE-L Export can do this. Right? But the output is not a script but a binary file only Import can understand. ltiu Connie Milliken wrote: Does anyone have a script that will write another script to recreate a particular database quickly with all the info specific to that particular database? Seems to me that I have seen this somewhere before, but I am not sure where. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: ltiu INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
multiple 8.1.7 db Migration Question
Dear List, i have to migrate around 25 databases to oracle 8.1.7 from 7.3.4. is there an automated procdure where i can run on each server. like a shell script where i can copy the 8.1.7 directory and run the migration process without using the OUI. thanks Sarath __ Do You Yahoo!? Yahoo! Finance - Get real-time stock quotes http://finance.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: sarath kumar INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Recreate database script
Forgot to mention that this will only build the db create script.
Nothing more than that.
- Kirti
-Original Message-
Sent: Monday, September 09, 2002 10:07 AM
To: '[EMAIL PROTECTED]'
Here is one that I used to use some time ago (for Oracle 6 and 7).
This does not support all the *new* stuff available since Oracle8, but it
should not be difficult to change it.. if you do, I would appreciate a copy
:)
- Kirti
#!/bin/ksh
#---
# crdb.ksh - creates sql script that contains statements necessary
# to recreate a database with the current physical
# configuration.
#
# parameters- SID (oracle database identifier)
#
# output- crdb_SID.sql in $ORACLE_HOME/admin/$ORACLE_SID/scripts
directory
#
# Author- Quin Bligh
# Kirti Deshpande
#
#---
get_perm_db_params ()
{
echo Getting permanent database parameters from controlfile trace...
touch $TFILE
sleep 2
sqlplus -s END1 /dev/null
sys/$SYSPW
alter database backup controlfile to trace;
END1
(sqlplus -s END1A
sys/$SYSPW
set heading off
set feedback off
set pages 0
select replace(value,'?','$ORACLE_HOME')
from v\$parameter
where name = 'user_dump_dest';
END1A
) |
read USER_DUMP_DEST
TRACE_FILE=`find $USER_DUMP_DEST -newer $TFILE -name ora_*.trc -print
2/dev/null`
MAXLOGFILES=`grep -i maxlogfiles ${TRACE_FILE}`
MAXDATAFILES=`grep -i maxdatafiles ${TRACE_FILE}`
MAXINSTANCES=`grep -i maxinstances ${TRACE_FILE}`
MAXLOGMEMBERS=`grep -i maxlogmembers ${TRACE_FILE}`
MAXLOGHISTORY=`grep -i maxloghistory ${TRACE_FILE}`
rm -f $TFILE
(sqlplus -s END1C
sys/$SYSPW
set heading off
set feedback off
set pages 0
select value
from nls_database_parameters
where parameter = 'NLS_CHARACTERSET';
END1C
) | read NLS_CHARSET
echo Char set is $NLS_CHARSET
}
#---
get_dbs_files ()
{
echo Getting system tablespace definition...
$SYSFILE
sqlplus -s END2 $TFILE
sys/$SYSPW
set heading off
set feedback off
set pages 0
select || file_name || || ' size ' || bytes/1024 || 'k'
from dba_data_files
where tablespace_name = 'SYSTEM';
END2
i=0
num=`wc -l $TFILE`
cat $TFILE | while read lines
do
i=$(($i + 1))
if [ $i -lt $num ] ; then
echo \t$lines, $SYSFILE
else
echo \t$lines $SYSFILE
fi
done
}
#---
get_log_files ()
{
echo Getting log file definitions...
$LOGFILE
HOLDGROUP=
(sqlplus -s END3
sys/$SYSPW
set heading off
set feedback off
set pages 0
select a.group# || ' ' || a.member || ' ' || b.bytes/1024
from v\$logfile a, v\$log b
where a.group# = b.group#
order by a.group#;
END3
) |
while read GROUP MEMBER SIZE
do
if [[ $GROUP != $HOLDGROUP ]]
then
[ -z $HOLDGROUP ] || echo \n\t\t ) size ${HOLDSIZE}k, $LOGFILE
echo \tGROUP $GROUP ( $LOGFILE
HOLDGROUP=$GROUP
HOLDSIZE=$SIZE
else
echo , $LOGFILE
fi
echo \t\t '$MEMBER'\c $LOGFILE
done
echo \n\t\t ) size ${HOLDSIZE}k $LOGFILE
}
#---
get_dbf_files ()
{
concat_file ()
{
i=0
num=`wc -l /tmp/$TSNAME.$$`
cat /tmp/$TSNAME.$$ | while read lines
do
i=$(($i + 1))
echo $lines | grep ^create /dev/null
if [ $? -eq 0 ] ; then
echo \n$lines $DBFILES
else
echo $lines | grep -v size /dev/null
if [ $? -eq 0 ] ; then
echo \t$lines $DBFILES
elif [ $i -lt $num ] ; then
echo \t$lines, $DBFILES
else
echo \t$lines; $DBFILES
fi
fi
done
}
echo Getting all other tablespace definitions...
$TFILE
$DBFILES
(sqlplus -s END4
sys/$SYSPW
set heading off
set feedback off
set pages 0
select tablespace_name ||' '|| initial_extent ||' '||
next_extent ||' '|| min_extents ||' '|| max_extents ||' '||
pct_increase
from dba_tablespaces
where tablespace_name != 'SYSTEM'
and status != 'INVALID'
order by tablespace_name;
END4
) |
while read TSNAME INITEXT NEXTEXT MINEXT MAXEXT PCTINC
do
echo create tablespace $TSNAME /tmp/$TSNAME.$$
echo \t default storage ( /tmp/$TSNAME.$$
echo \t\t initial $INITEXT /tmp/$TSNAME.$$
echo \t\t next $NEXTEXT /tmp/$TSNAME.$$
echo \t\t minextents$MINEXT /tmp/$TSNAME.$$
echo \t\t maxextents$MAXEXT /tmp/$TSNAME.$$
echo \t\t pctincrease $PCTINC /tmp/$TSNAME.$$
echo \t ) /tmp/$TSNAME.$$
echo \t datafile /tmp/$TSNAME.$$
sqlplus -s END5 /tmp/$TSNAME.$$
sys/$SYSPW
set heading off
set feedback off
set pages 0
select || file_name || || ' size ' || bytes/1024 || 'k'
from dba_data_files
where tablespace_name = '$TSNAME'
order by file_name;
END5
concat_file
rm -f /tmp/$TSNAME.$$
done
}
RE: Recreate database script
Here is one that I used to use some time ago (for Oracle 6 and 7).
This does not support all the *new* stuff available since Oracle8, but it
should not be difficult to change it.. if you do, I would appreciate a copy
:)
- Kirti
#!/bin/ksh
#---
# crdb.ksh - creates sql script that contains statements necessary
# to recreate a database with the current physical
# configuration.
#
# parameters- SID (oracle database identifier)
#
# output- crdb_SID.sql in $ORACLE_HOME/admin/$ORACLE_SID/scripts
directory
#
# Author- Quin Bligh
# Kirti Deshpande
#
#---
get_perm_db_params ()
{
echo Getting permanent database parameters from controlfile trace...
touch $TFILE
sleep 2
sqlplus -s END1 /dev/null
sys/$SYSPW
alter database backup controlfile to trace;
END1
(sqlplus -s END1A
sys/$SYSPW
set heading off
set feedback off
set pages 0
select replace(value,'?','$ORACLE_HOME')
from v\$parameter
where name = 'user_dump_dest';
END1A
) |
read USER_DUMP_DEST
TRACE_FILE=`find $USER_DUMP_DEST -newer $TFILE -name ora_*.trc -print
2/dev/null`
MAXLOGFILES=`grep -i maxlogfiles ${TRACE_FILE}`
MAXDATAFILES=`grep -i maxdatafiles ${TRACE_FILE}`
MAXINSTANCES=`grep -i maxinstances ${TRACE_FILE}`
MAXLOGMEMBERS=`grep -i maxlogmembers ${TRACE_FILE}`
MAXLOGHISTORY=`grep -i maxloghistory ${TRACE_FILE}`
rm -f $TFILE
(sqlplus -s END1C
sys/$SYSPW
set heading off
set feedback off
set pages 0
select value
from nls_database_parameters
where parameter = 'NLS_CHARACTERSET';
END1C
) | read NLS_CHARSET
echo Char set is $NLS_CHARSET
}
#---
get_dbs_files ()
{
echo Getting system tablespace definition...
$SYSFILE
sqlplus -s END2 $TFILE
sys/$SYSPW
set heading off
set feedback off
set pages 0
select || file_name || || ' size ' || bytes/1024 || 'k'
from dba_data_files
where tablespace_name = 'SYSTEM';
END2
i=0
num=`wc -l $TFILE`
cat $TFILE | while read lines
do
i=$(($i + 1))
if [ $i -lt $num ] ; then
echo \t$lines, $SYSFILE
else
echo \t$lines $SYSFILE
fi
done
}
#---
get_log_files ()
{
echo Getting log file definitions...
$LOGFILE
HOLDGROUP=
(sqlplus -s END3
sys/$SYSPW
set heading off
set feedback off
set pages 0
select a.group# || ' ' || a.member || ' ' || b.bytes/1024
from v\$logfile a, v\$log b
where a.group# = b.group#
order by a.group#;
END3
) |
while read GROUP MEMBER SIZE
do
if [[ $GROUP != $HOLDGROUP ]]
then
[ -z $HOLDGROUP ] || echo \n\t\t ) size ${HOLDSIZE}k, $LOGFILE
echo \tGROUP $GROUP ( $LOGFILE
HOLDGROUP=$GROUP
HOLDSIZE=$SIZE
else
echo , $LOGFILE
fi
echo \t\t '$MEMBER'\c $LOGFILE
done
echo \n\t\t ) size ${HOLDSIZE}k $LOGFILE
}
#---
get_dbf_files ()
{
concat_file ()
{
i=0
num=`wc -l /tmp/$TSNAME.$$`
cat /tmp/$TSNAME.$$ | while read lines
do
i=$(($i + 1))
echo $lines | grep ^create /dev/null
if [ $? -eq 0 ] ; then
echo \n$lines $DBFILES
else
echo $lines | grep -v size /dev/null
if [ $? -eq 0 ] ; then
echo \t$lines $DBFILES
elif [ $i -lt $num ] ; then
echo \t$lines, $DBFILES
else
echo \t$lines; $DBFILES
fi
fi
done
}
echo Getting all other tablespace definitions...
$TFILE
$DBFILES
(sqlplus -s END4
sys/$SYSPW
set heading off
set feedback off
set pages 0
select tablespace_name ||' '|| initial_extent ||' '||
next_extent ||' '|| min_extents ||' '|| max_extents ||' '||
pct_increase
from dba_tablespaces
where tablespace_name != 'SYSTEM'
and status != 'INVALID'
order by tablespace_name;
END4
) |
while read TSNAME INITEXT NEXTEXT MINEXT MAXEXT PCTINC
do
echo create tablespace $TSNAME /tmp/$TSNAME.$$
echo \t default storage ( /tmp/$TSNAME.$$
echo \t\t initial $INITEXT /tmp/$TSNAME.$$
echo \t\t next $NEXTEXT /tmp/$TSNAME.$$
echo \t\t minextents$MINEXT /tmp/$TSNAME.$$
echo \t\t maxextents$MAXEXT /tmp/$TSNAME.$$
echo \t\t pctincrease $PCTINC /tmp/$TSNAME.$$
echo \t ) /tmp/$TSNAME.$$
echo \t datafile /tmp/$TSNAME.$$
sqlplus -s END5 /tmp/$TSNAME.$$
sys/$SYSPW
set heading off
set feedback off
set pages 0
select || file_name || || ' size ' || bytes/1024 || 'k'
from dba_data_files
where tablespace_name = '$TSNAME'
order by file_name;
END5
concat_file
rm -f /tmp/$TSNAME.$$
done
}
#---
get_rbs ()
{
echo Getting rollback segment definitions...
$RBS
(sqlplus -s END6
sys/$SYSPW
set heading off
set feedback
Re: Recreate database script
Hello, Could anyone here suggest a software package that can create an ER diagram by simply connecting to an Oracle database, reverse engineer it to see the schema in an ER diagram - instead of in a file with ddl/dml statements. Thanks. ltiu -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: ltiu INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Recreate database script
Could anyone here suggest a software package that can create an ER diagram by simply connecting to an Oracle database, reverse engineer it to see the schema in an ER diagram - instead of in a file with ddl/dml statements. I haven't tested it, but Microsoft Visio 2000 claims to be able to reverse engineer a database. Gary Chambers //- // Lucent Technologies GIO/Unix // 4 Robbins Road, Westford, MA 01886 // 978-399-0481 / 888-480-6924 (Pager) // Nothing fancy and nothing Microsoft //- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gary Chambers INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Recreate database script
Itiu - ERWin Examiner (Computer Associates) is supposed to have that capability. I have never used that capability. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, September 09, 2002 11:23 AM To: Multiple recipients of list ORACLE-L Hello, Could anyone here suggest a software package that can create an ER diagram by simply connecting to an Oracle database, reverse engineer it to see the schema in an ER diagram - instead of in a file with ddl/dml statements. Thanks. ltiu -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: ltiu INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Recreate database script
ErWin is an excellent tool.. Gabriel --- ltiu [EMAIL PROTECTED] wrote: Hello, Could anyone here suggest a software package that can create an ER diagram by simply connecting to an Oracle database, reverse engineer it to see the schema in an ER diagram - instead of in a file with ddl/dml statements. Thanks. ltiu -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: ltiu INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = Any dream worth having is a dream worth fighting for(Cualquier sueño que valga la pena tener, es un sueño por el que vale la pena luchar)Charles Xavier __ Do You Yahoo!? Yahoo! Finance - Get real-time stock quotes http://finance.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gabriel Aragon INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
* Oracle DBA Needed For Long Term Contract in Upstate New
Position: Oracle DBA- 18 month 1099 contract position Location: Albany, New York Rate: Submit your 1099 rate with your resume PLEASE DO NOT send your resume for this position Unless you are qualified for this position based on the requirements outlined below. No H-1B candidates please. *Requirements: -Certified Oracle DBA with a minimum of 5 years experience in a Unix environment. -3 years experience in planning, installing, configuring and tuning an Oracle instance. Current experience should include these activities relative to the Oracle 9i database. -3 years experience using Unix scripting languages and cron -3 years experience in SQL performance tuning. -5 years experience using and administering the Oracle application development tool suite -3 years experience deploying applications using Oracle Application Server and current experience with Oracle 9iAS -2 years experience developing and administering security rules and policies with Oracle Fine Grained Access (a.k.a. Virtual Private Database) -2 years experience with Java application development and deployment -2 years experience in a lead role of managing an Oracle database in a UNIX (preferably AIX) environment. DESIRABLE Experience Listed in Order of Importance: -3 years experience with establishing and administering Recovery plans in a web based high-availability environment. -Experience using RMAN is very desirable. -Significant experience using Enterprise Manager for monitoring and managing multiple Oracle instances. -3 years experience configuring and tuning of Oracle database multi-threaded servers. -Good written communications skills. -Some working knowledge of Windows 98, 2000 and NT operating systems. -Working knowledge of Oracle connectivity software including ODBC, Net*8 and -SQL*Net. -Experience in storage and capacity planning -MUST be- a U.S. citizen or permanent resident. For immediate consideration, please send your resume as a Word attachment along with 1099 rate to: OraStaff, Inc. Email: [EMAIL PROTECTED] Please use job code: One/Albany/DBA contract/RM ph: 1-800 -549-8502 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: OraStaff INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Recreate database script
Most CASE tools have reverse engineering feature. We are using PowerDesigner (from SYBASE). Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, September 09, 2002 12:23 PM Hello, Could anyone here suggest a software package that can create an ER diagram by simply connecting to an Oracle database, reverse engineer it to see the schema in an ER diagram - instead of in a file with ddl/dml statements. Thanks. ltiu -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: ltiu INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Igor Neyman INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
snapshot of a remote snapshot without pk
short question, log demo: Since I can't make a snapshot of a remote partition (this is really what I wanted in the first place), I make a snapshot on the remote machine first and then snapshot the remote snapshot. This works if there is a PK on the remote original table, but not if I try to use rowid. Below is the demo, can anyone suggest a workaround? demo that it works with pk: === on remote machine: -- SQL CREATE TABLE EMP2 (EMPNO NUMBER(4) NOT NULL, ENAME VARCHAR2(10) ) PARTITION by range (empno) (partition e6 values less than (6), partition e12 values less than (12), partition e18 values less than (maxvalue)) / Table created. SQL alter table emp2 add constraint emp_pk primary key(empno); Table altered. (insert some data) create snapshot emp2_snap as select * from emp2 partition (e6); SQL create snapshot emp2_snap as select * from emp2 partition (e6); Materialized view created. SQL select * from emp2_snap; EMPNO ENAME -- -- 1 stellr on local machine: - SQL create snapshot emp2_local_snap as select * from [EMAIL PROTECTED]; Materialized view created. SQL select * from emp2_local_snap; EMPNO ENAME -- -- 1 stellr demo of failure without pk: === on remote machine: -- CREATE TABLE EMP3 (EMPNO NUMBER(4) NOT NULL, ENAME VARCHAR2(10) ) PARTITION by range (empno) (partition e6 values less than (6), partition e12 values less than (12), partition e18 values less than (maxvalue)) / (insert some data) SQL create snapshot log on emp3 with rowid; (can't create the snap without this log) Materialized view log created. create snapshot emp3_e6_snap refresh fast WITH ROWID as select * from emp3 partition (e6); SQL create snapshot emp3_e6_snap refresh fast WITH ROWID as select * from emp3 partition (e6); Materialized view created. SQL select * from emp3_e6_snap; EMPNO ENAME -- -- 1 stellr on local machine: - SQL select * from [EMAIL PROTECTED]; EMPNO ENAME -- -- 1 stellr SQL create snapshot emp3_e6_local_snap refresh fast WITH ROWID as select * from [EMAIL PROTECTED]; * ERROR at line 2: ORA-23413: table STELLR.EMP3_E6_SNAP does not have a snapshot log === Ray Stell [EMAIL PROTECTED] (540) 231-4109 KE4TJC28^D -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ray Stell INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Calling report
sorry dear but this is a dba forum . Do you think
it's a dba question ?
bp
- Original Message -
From:
sultan
To: Multiple recipients of list ORACLE-L
Sent: Wednesday, September 04, 2002 8:03
AM
Subject: Calling report
Hi
friends
I am using following command to call report
from report
srw.run_report ('report=d:\test.rdf destype=file desname=try.out
desformat=dflt batch=yes');
I have tested this using After Report /Before Report/Action
Trigger.
But this is not calling the
report.
Any solution will be appreciated.
Syed
Re: Recreate database script
ERwin, Visia Architect, Oracle Designer all can do it. There may be other packages that I do not know about. I have all three of them and they work. Shakir --- ltiu [EMAIL PROTECTED] wrote: Hello, Could anyone here suggest a software package that can create an ER diagram by simply connecting to an Oracle database, reverse engineer it to see the schema in an ER diagram - instead of in a file with ddl/dml statements. Thanks. ltiu -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: ltiu INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = Mohammed Shakir CompuSoft, Inc. 11 Heather Way East Brunswick, NJ 08816-2825 (732) 672-0464 (Cell) (732) 257-6001 (Home) __ Do You Yahoo!? Yahoo! Autos - Get free new car price quotes http://autos.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohammed Shakir INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Recreate database script
I have used Visio to do that . It links the tables together if they have foreign/primary keys. If you have no relations like that then it will at least list all the tables you pick. -Original Message- Sent: Monday, September 09, 2002 12:09 PM To: Multiple recipients of list ORACLE-L Could anyone here suggest a software package that can create an ER diagram by simply connecting to an Oracle database, reverse engineer it to see the schema in an ER diagram - instead of in a file with ddl/dml statements. I haven't tested it, but Microsoft Visio 2000 claims to be able to reverse engineer a database. Gary Chambers //- // Lucent Technologies GIO/Unix // 4 Robbins Road, Westford, MA 01886 // 978-399-0481 / 888-480-6924 (Pager) // Nothing fancy and nothing Microsoft //- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gary Chambers INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kevin Lange INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Can't create simple MV with FAST refresh
Maybe the IN clause is throwing it?
Try it with -
where trans_type = 'A' or trans_type = 'B'
Just a WAG...
Beth
-Original Message-
Sent: Monday, September 09, 2002 2:38 PM
To: Multiple recipients of list ORACLE-L
Hi all,
Using 8.1.7.4.0 on HP/UX with COMPATIBILE set to 8.1.7, I'm not able
to
create the following simple MV:
CREATE MATERIALIZED VIEW qt_gl_trans_ab_mv
TABLESPACE qt_mat_view
BUILD IMMEDIATE
USING INDEX TABLESPACE qt_mat_view PCTFREE 15 INITRANS 2
REFRESH FAST WITH ROWID
USING LOCAL ROLLBACK SEGMENT rbsbig
START WITH SYSDATE
NEXT (TRUNC(SYSDATE+1) + 4/24)
AS
select fiscal_year, fiscal_period, account_no,
trans_type, trans_amount
from my_general_ledge
where trans_type in ('A','B')
It errors with ORA-12015 cannot create a fast refresh snapshot from a
complex query. According to MetaLink doc 179466.1, a complex query in
a MV
on 8.1.7 is one that has at least one of:
- a distinct or unique keyword
- an aggregate function (e.g. avg, count, max, min, sum, )
- a connect by clause
- a group by or order by clause
- a set operator (UNION, UNION ALL, INTERSECT and MINUS)
- joins other than those in a subquery
But my query has none of those? Thinking somethings hosed or I'm
completely
misunderstanding the IN operator of the WHERE clause, I tried where
trans_type = 'A', but with the same results.
Also, I've created the MV log for the fast refresh:
CREATE MATERIALIZED VIEW LOG ON my_general_ledger
WITH ROWID
I'm just testing out MVs here, but I'm missing something real simple
and/or
stupid here, but for the life of me I can't see what (even after a lunch
break).
Anyone?
Rich Jesse System/Database Administrator
[EMAIL PROTECTED] Quad/Tech International, Sussex, WI
USA
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jesse, Rich
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Seefelt, Beth
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: No space left on device - but I have lots left.
Did I shoot from the hip too quickly? I was in a bad mood caused by some other issues. I've received a few emails telling me how grumpy I appeared to be, some of them through an anonymous remailer. There were also some interesting assumptions about my place on the tree of evolution as well as the species of my ancestry. From subtle hints like that, I conclude that I might have gone too far. If that's the case, I apologize. -Original Message- From: Jared Still [mailto:[EMAIL PROTECTED]] Sent: Sunday, September 08, 2002 3:48 PM To: Multiple recipients of list ORACLE-L Subject: Re: No space left on device - but I have lots left. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gogala, Mladen INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Can't create simple MV with FAST refresh
Hi all,
Using 8.1.7.4.0 on HP/UX with COMPATIBILE set to 8.1.7, I'm not able to
create the following simple MV:
CREATE MATERIALIZED VIEW qt_gl_trans_ab_mv
TABLESPACE qt_mat_view
BUILD IMMEDIATE
USING INDEX TABLESPACE qt_mat_view PCTFREE 15 INITRANS 2
REFRESH FAST WITH ROWID
USING LOCAL ROLLBACK SEGMENT rbsbig
START WITH SYSDATE
NEXT (TRUNC(SYSDATE+1) + 4/24)
AS
select fiscal_year, fiscal_period, account_no,
trans_type, trans_amount
from my_general_ledge
where trans_type in ('A','B')
It errors with ORA-12015 cannot create a fast refresh snapshot from a
complex query. According to MetaLink doc 179466.1, a complex query in a MV
on 8.1.7 is one that has at least one of:
- a distinct or unique keyword
- an aggregate function (e.g. avg, count, max, min, sum, )
- a connect by clause
- a group by or order by clause
- a set operator (UNION, UNION ALL, INTERSECT and MINUS)
- joins other than those in a subquery
But my query has none of those? Thinking somethings hosed or I'm completely
misunderstanding the IN operator of the WHERE clause, I tried where
trans_type = 'A', but with the same results.
Also, I've created the MV log for the fast refresh:
CREATE MATERIALIZED VIEW LOG ON my_general_ledger
WITH ROWID
I'm just testing out MVs here, but I'm missing something real simple and/or
stupid here, but for the life of me I can't see what (even after a lunch
break).
Anyone?
Rich Jesse System/Database Administrator
[EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jesse, Rich
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: connect to the databases without using tnsnames.ora?
Thanks all for the answers. We do use Oracle Names here. Cc Harvest. --- [EMAIL PROTECTED] wrote: Neither sqlnet.ora or tnsnames.ora are required. You can connect to a database by specifying the full connect string. This will work from sqlplus: connect system/manager@(description=(address=(protocol=tcp)(host=remedydev)(port=1521))(connect_data=(sid=orcl))) It works, but is not terribly convenient. I don't imagine your dba was doing this though. Could it be that Oracle Names servers are being used? They don't require tnsnames.ora files. Jared CC Harvest [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 09/05/2002 06:23 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:connect to the databases without using tnsnames.ora? Does anyone here knows how to setup the connections to the database server without using tnsnames.ora? Our DBA didn't use it. But I don't know how and why? Could anyone tell me the other options? Cc Harvest __ Do You Yahoo!? Yahoo! Finance - Get real-time stock quotes http://finance.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: CC Harvest INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Yahoo! Finance - Get real-time stock quotes http://finance.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: CC Harvest INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
NLS_DATE_FORMAT
On NT/2000, how can you set the NLS date format at the session level? Isn't there a file that you can set it in? What do you write in the file? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Eric Richmon INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: ALTER TABLE MOVE command causes table to grow
How did you perform the move? Was the operation done in parallel? -Original Message- Sent: Friday, September 06, 2002 4:13 PM To: Multiple recipients of list ORACLE-L No LOBS. Here's the definition: REQUEST_IDNOT NULL NUMBER PROFILE_IDNOT NULL NUMBER ACCOUNT_IDNOT NULL NUMBER TEMPLATE_ID NOT NULL NUMBER GENERIC_DIFFERENTIATORNOT NULL NUMBER REQUEST_TYPE NOT NULL CHAR(1) ACCOUNT_NONOT NULL CHAR(8) EFFECTIVE_DATENOT NULL DATE EMAIL_ADDRESS NOT NULL VARCHAR2(100) EMAIL_SUBJECT NOT NULL VARCHAR2(100) EMAIL_BODYNOT NULL VARCHAR2(4000) STATUSNOT NULL CHAR(1) STATUS_CHANGE_DATENOT NULL DATE TWEED_SERVER_IDNUMBER TWEED_PACKAGE_PRIORITY NUMBER TWEED_SENDER_ACCOUNT NOT NULL VARCHAR2(50) TWEED_SCHEDULED_DELIVERY_DATE NOT NULL DATE TWEED_CUSTOMER_URLNOT NULL VARCHAR2(255) SENDER_SERVER_ID NUMBER SENDER_INSTANCE_ID NUMBER CREATE_DATE NOT NULL DATE CREATE_USER NOT NULL VARCHAR2(35) UPDATE_DATEDATE UPDATE_USERVARCHAR2(35) -Original Message- Sent: Thursday, September 05, 2002 5:23 PM To: Multiple recipients of list ORACLE-L What the table definition? Are there any LOB's on it? -Original Message- Sent: Thursday, September 05, 2002 2:43 PM To: Multiple recipients of list ORACLE-L pct increase is 0 (uniform sizing) -Original Message- Sent: Thursday, September 05, 2002 2:00 PM To: Multiple recipients of list ORACLE-L you didn't mention the PCT_INCREASE of this segment. -Original Message- mailto:[EMAIL PROTECTED] ] Sent: Thursday, September 05, 2002 12:09 PM To: Multiple recipients of list ORACLE-L Had an annoying surprise last week. A table had grown unexpectedly large and I scheduled a time over the weekend to move it to its own tablespace from my medium tablespace. The table ended up growing 50%. I had anticipated it might grow somewhat given the PCTFREE of 10% but freeing up that space in the blocks should, at most, have caused it to grow by 10% (assuming that 10% was completely full). Does anyone have ideas as to why it would have grown by so much? Indexes are in a different tablespace and the only other change was from an extent size of 4 meg to one of 25 meg. Both are dictionary managed tablespaces. Oracle 8.1.7.2 Solaris 2.6 Thanks, Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.com http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Johnston, Tim INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Recreate database script
Oracle Designer Viso2000 I use Oracle Designer. -Original Message- Sent: Monday, September 09, 2002 12:24 PM To: Multiple recipients of list ORACLE-L Hello, Could anyone here suggest a software package that can create an ER diagram by simply connecting to an Oracle database, reverse engineer it to see the schema in an ER diagram - instead of in a file with ddl/dml statements. Thanks. ltiu -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: ltiu INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Recreate database script
Yeah, you can use Visio. I used it when I need to quickly generate an ER diagram from a schema. -Original Message- Sent: Monday, September 09, 2002 1:09 PM To: Multiple recipients of list ORACLE-L Could anyone here suggest a software package that can create an ER diagram by simply connecting to an Oracle database, reverse engineer it to see the schema in an ER diagram - instead of in a file with ddl/dml statements. I haven't tested it, but Microsoft Visio 2000 claims to be able to reverse engineer a database. Gary Chambers //- // Lucent Technologies GIO/Unix // 4 Robbins Road, Westford, MA 01886 // 978-399-0481 / 888-480-6924 (Pager) // Nothing fancy and nothing Microsoft //- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gary Chambers INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ji, Richard INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Recreate database script
Now called AllFusion Erwin Data Modeler. It will create ER diagram from existing database. Rick DENNIS WILLIAMS DWILLIAMS@life To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] touch.comcc: Sent by: Subject: RE: Recreate database script [EMAIL PROTECTED] m 09/09/2002 12:58 PM Please respond to ORACLE-L Itiu - ERWin Examiner (Computer Associates) is supposed to have that capability. I have never used that capability. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, September 09, 2002 11:23 AM To: Multiple recipients of list ORACLE-L Hello, Could anyone here suggest a software package that can create an ER diagram by simply connecting to an Oracle database, reverse engineer it to see the schema in an ER diagram - instead of in a file with ddl/dml statements. Thanks. ltiu -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: ltiu INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Oracle and Cache Hit Ratio based training
FWIW ... One of our DBAs just got back from a Oracle 9i course and it was stated by the instuctor that Oracle is in the process of revamping all their performance classes to be based on the wait interface. Looks like Cache Hit Ratios may finally go away !!! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Johnson, Michael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
OT: Here we go again!!
Will Bill Gates never learn! Can you say Anti-trust lawsuit #2? Dick Goulet -- MICROSOFT CLAIMS .NET REMOVES NEED FOR APPLICATION SERVER | cw360 Microsoft says. NET Server is all you need. Defying the industry trend for vendors to develop a separate application server for deploying Internet applications and Web services, the company says its Windows .NET Server 2003 will have all the functionality users need. For the full details, click: http://www.cw360.com/articlerd=i=ard=115584fv=1 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: NLS_DATE_FORMAT
Set it in the SIDinit.ora. Dave -Original Message- Sent: Monday, September 09, 2002 2:18 PM To: Multiple recipients of list ORACLE-L On NT/2000, how can you set the NLS date format at the session level? Isn't there a file that you can set it in? What do you write in the file? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Eric Richmon INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Farnsworth, Dave INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re:RE: No space left on device - but I have lots left.
Mladen, Welcome to the club for those who hit send without fully engaging brain. Dick Goulet, president founder Reply Separator Author: Gogala; Mladen [EMAIL PROTECTED] Date: 9/9/2002 10:48 AM Did I shoot from the hip too quickly? I was in a bad mood caused by some other issues. I've received a few emails telling me how grumpy I appeared to be, some of them through an anonymous remailer. There were also some interesting assumptions about my place on the tree of evolution as well as the species of my ancestry. From subtle hints like that, I conclude that I might have gone too far. If that's the case, I apologize. -Original Message- From: Jared Still [mailto:[EMAIL PROTECTED]] Sent: Sunday, September 08, 2002 3:48 PM To: Multiple recipients of list ORACLE-L Subject: Re: No space left on device - but I have lots left. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gogala, Mladen INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Oracle and Cache Hit Ratio based training
I can see it now... DBA 102:Buffer Cache Wait Hit Ratio Interface Tuning... -Original Message- Sent: Monday, September 09, 2002 12:13 PM To: Multiple recipients of list ORACLE-L FWIW ... One of our DBAs just got back from a Oracle 9i course and it was stated by the instuctor that Oracle is in the process of revamping all their performance classes to be based on the wait interface. Looks like Cache Hit Ratios may finally go away !!! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Johnson, Michael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Fink, Dan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Can't create simple MV with FAST refresh
Thanks, but as I had mentioned, I've already tried that.
One part I didn't include is that the table does not have a primary key and
cannot be made to have one, so I'm using ROWID.
Rich Jesse System/Database Administrator
[EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA
-Original Message-
From: Seefelt, Beth [mailto:[EMAIL PROTECTED]]
Sent: Monday, September 09, 2002 1:53 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: Can't create simple MV with FAST refresh
Maybe the IN clause is throwing it?
Try it with -
where trans_type = 'A' or trans_type = 'B'
Just a WAG...
Beth
-Original Message-
Sent: Monday, September 09, 2002 2:38 PM
To: Multiple recipients of list ORACLE-L
Hi all,
Using 8.1.7.4.0 on HP/UX with COMPATIBILE set to 8.1.7, I'm not able
to
create the following simple MV:
CREATE MATERIALIZED VIEW qt_gl_trans_ab_mv
TABLESPACE qt_mat_view
BUILD IMMEDIATE
USING INDEX TABLESPACE qt_mat_view PCTFREE 15 INITRANS 2
REFRESH FAST WITH ROWID
USING LOCAL ROLLBACK SEGMENT rbsbig
START WITH SYSDATE
NEXT (TRUNC(SYSDATE+1) + 4/24)
AS
select fiscal_year, fiscal_period, account_no,
trans_type, trans_amount
from my_general_ledge
where trans_type in ('A','B')
It errors with ORA-12015 cannot create a fast refresh snapshot from a
complex query. According to MetaLink doc 179466.1, a
complex query in
a MV
on 8.1.7 is one that has at least one of:
- a distinct or unique keyword
- an aggregate function (e.g. avg, count, max, min, sum, )
- a connect by clause
- a group by or order by clause
- a set operator (UNION, UNION ALL, INTERSECT and MINUS)
- joins other than those in a subquery
But my query has none of those? Thinking somethings hosed or I'm
completely
misunderstanding the IN operator of the WHERE clause, I tried where
trans_type = 'A', but with the same results.
Also, I've created the MV log for the fast refresh:
CREATE MATERIALIZED VIEW LOG ON my_general_ledger
WITH ROWID
I'm just testing out MVs here, but I'm missing something real simple
and/or
stupid here, but for the life of me I can't see what (even
after a lunch
break).
Anyone?
Rich Jesse System/Database Administrator
[EMAIL PROTECTED] Quad/Tech International,
Sussex, WI
USA
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jesse, Rich
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Oracle and Cache Hit Ratio based training
Hi all, I have a large data load to run into a partitioned table. With indexes the load takes over 24 hours, without on the order of 3 hours, obviously, I don't want to mess with the indexes until after the job completes. So I try to drop to local indexes and get ORA-14076. Fair enough. I go to metastink and see note 107976.1...exactly what I want. So I try to use it...and get ORA-01502. Has anybody done something like this and found there's an extra caveat from the note? Every other note oracle has seems to point back to the original one I am using. I'm on Solaris 8/Oracle 9.0.1.3. Any help would be appreciated. Here's the output I'm getting... SQL select index_name, status from dba_ind_partitions where partition_name = 'TYPE13'; INDEX_NAME STATUS -- I_BUY_PR_PCE_TYPE_HIST_3 USABLE I_BUY_PR_PCE_TYPE_HIST_2 USABLE I_BUY_PR_PCE_TYPE_HIST_1 USABLE PK_BUY_PRICE_PCE_TYPE_HISTORY USABLE SQL ALTER TABLE buy_price_piece_type_history MODIFY PARTITION type13 2 UNUSABLE LOCAL INDEXES; Table altered. SQL select index_name, status from dba_ind_partitions where partition_name = 'TYPE13'; INDEX_NAME STATUS -- I_BUY_PR_PCE_TYPE_HIST_3 UNUSABLE I_BUY_PR_PCE_TYPE_HIST_2 UNUSABLE I_BUY_PR_PCE_TYPE_HIST_1 UNUSABLE PK_BUY_PRICE_PCE_TYPE_HISTORY UNUSABLE SQL ALTER SESSION SET skip_unusable_indexes = true; Session altered. SQL insert into buy_price_piece_type_history partition (type13) select * from hold_type13; insert into buy_price_piece_type_history partition (type13) select * from hold_type13 * ERROR at line 1: ORA-01502: index 'RLADMIN.PK_BUY_PRICE_PCE_TYPE_HISTORY' or partition of such index is in unusable state SQL TIA, John P Weatherman Database Administrator Replacements Ltd. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Weatherman INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: OT: Here we go again!!
-- [EMAIL PROTECTED] on 09/09/02 11:58:25 -0800 http://www.cw360.com/articlerd=i=ard=115584fv=1 As a long time *NIX user I can only hope they never do. -- Steven Lembark 2930 W. Palmer Workhorse Computing Chicago, IL 60647 +1 800 762 1582 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Calling report
No, this is an Oracle forum. The question
do not need to be DBA related. It just happens
that many of the questions are DBA questions.
Jared
BigP [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
09/09/2002 11:23 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject:Re: Calling report
sorry dear but this is a dba forum . Do you think it's a dba question ?
bp
- Original Message -
To: Multiple recipients of list ORACLE-L
Sent: Wednesday, September 04, 2002 8:03 AM
Hi friends
I am using following command to call report from report
srw.run_report ('report=d:\test.rdf destype=file desname=try.out
desformat=dflt batch=yes');
I have tested this using After Report /Before Report/Action Trigger.
But this is not calling the report.
Any solution will be appreciated.
Syed
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Can't create simple MV with FAST refresh
OK, so the MetaLink article seems to be really incomplete, as I found this hidden in the docs: The WHERE clause can contain only joins and they must be equi-joins (inner or outer) and all join predicates must be connected with ANDs. No selection predicates on individual tables are allowed. OK, so I can't have selection predicates with a FAST refresh -- at least in 8i. So Im not sure an MV is going to give me a significant advantage here over a trigger. I just dont like the impact of several insert/update triggers (or one big one) on our G/L. grumble grumble Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- From: Seefelt, Beth [mailto:[EMAIL PROTECTED]] Sent: Monday, September 09, 2002 1:53 PM To: Multiple recipients of list ORACLE-L Subject: RE: Can't create simple MV with FAST refresh Maybe the IN clause is throwing it? Try it with - where trans_type = 'A' or trans_type = 'B' Just a WAG... Beth -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: ALTER TABLE MOVE command causes table to grow
alter table email_request_queue move tablespace ts_email_request storage (initial 25m next 25m pctincrease 0); -Original Message- Sent: Monday, September 09, 2002 1:54 PM To: Multiple recipients of list ORACLE-L How did you perform the move? Was the operation done in parallel? -Original Message- Sent: Friday, September 06, 2002 4:13 PM To: Multiple recipients of list ORACLE-L No LOBS. Here's the definition: REQUEST_IDNOT NULL NUMBER PROFILE_IDNOT NULL NUMBER ACCOUNT_IDNOT NULL NUMBER TEMPLATE_ID NOT NULL NUMBER GENERIC_DIFFERENTIATORNOT NULL NUMBER REQUEST_TYPE NOT NULL CHAR(1) ACCOUNT_NONOT NULL CHAR(8) EFFECTIVE_DATENOT NULL DATE EMAIL_ADDRESS NOT NULL VARCHAR2(100) EMAIL_SUBJECT NOT NULL VARCHAR2(100) EMAIL_BODYNOT NULL VARCHAR2(4000) STATUSNOT NULL CHAR(1) STATUS_CHANGE_DATENOT NULL DATE TWEED_SERVER_IDNUMBER TWEED_PACKAGE_PRIORITY NUMBER TWEED_SENDER_ACCOUNT NOT NULL VARCHAR2(50) TWEED_SCHEDULED_DELIVERY_DATE NOT NULL DATE TWEED_CUSTOMER_URLNOT NULL VARCHAR2(255) SENDER_SERVER_ID NUMBER SENDER_INSTANCE_ID NUMBER CREATE_DATE NOT NULL DATE CREATE_USER NOT NULL VARCHAR2(35) UPDATE_DATEDATE UPDATE_USERVARCHAR2(35) -Original Message- Sent: Thursday, September 05, 2002 5:23 PM To: Multiple recipients of list ORACLE-L What the table definition? Are there any LOB's on it? -Original Message- Sent: Thursday, September 05, 2002 2:43 PM To: Multiple recipients of list ORACLE-L pct increase is 0 (uniform sizing) -Original Message- Sent: Thursday, September 05, 2002 2:00 PM To: Multiple recipients of list ORACLE-L you didn't mention the PCT_INCREASE of this segment. -Original Message- mailto:[EMAIL PROTECTED] ] Sent: Thursday, September 05, 2002 12:09 PM To: Multiple recipients of list ORACLE-L Had an annoying surprise last week. A table had grown unexpectedly large and I scheduled a time over the weekend to move it to its own tablespace from my medium tablespace. The table ended up growing 50%. I had anticipated it might grow somewhat given the PCTFREE of 10% but freeing up that space in the blocks should, at most, have caused it to grow by 10% (assuming that 10% was completely full). Does anyone have ideas as to why it would have grown by so much? Indexes are in a different tablespace and the only other change was from an extent size of 4 meg to one of 25 meg. Both are dictionary managed tablespaces. Oracle 8.1.7.2 Solaris 2.6 Thanks, Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.com http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Johnston, Tim INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling
Solaris 2.9 -- Portal
We're getting prepared to begin implementation of the Oracle Web Portal. Does anyone know if Oracle 9ias will run successfully on Solaris 2.9? It looks like it is officially in pre-certified status, but I wondered if anyone has any real-world experiences with it (+ or -). thanks! -Bob [EMAIL PROTECTED] Bob Brown - KB9LFR Harper Community College ## ## ##Systems Administrator Palatine IL USA Saved by grace -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bob Brown INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Can't create simple MV with FAST refresh
Jesse,
don't have much experience with mv, but shouldn't rowid be included in
select list?
Igor Neyman, OCP DBA
[EMAIL PROTECTED]
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Monday, September 09, 2002 4:18 PM
Thanks, but as I had mentioned, I've already tried that.
One part I didn't include is that the table does not have a primary key
and
cannot be made to have one, so I'm using ROWID.
Rich Jesse System/Database Administrator
[EMAIL PROTECTED] Quad/Tech International, Sussex, WI
USA
-Original Message-
From: Seefelt, Beth [mailto:[EMAIL PROTECTED]]
Sent: Monday, September 09, 2002 1:53 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: Can't create simple MV with FAST refresh
Maybe the IN clause is throwing it?
Try it with -
where trans_type = 'A' or trans_type = 'B'
Just a WAG...
Beth
-Original Message-
Sent: Monday, September 09, 2002 2:38 PM
To: Multiple recipients of list ORACLE-L
Hi all,
Using 8.1.7.4.0 on HP/UX with COMPATIBILE set to 8.1.7, I'm not able
to
create the following simple MV:
CREATE MATERIALIZED VIEW qt_gl_trans_ab_mv
TABLESPACE qt_mat_view
BUILD IMMEDIATE
USING INDEX TABLESPACE qt_mat_view PCTFREE 15 INITRANS 2
REFRESH FAST WITH ROWID
USING LOCAL ROLLBACK SEGMENT rbsbig
START WITH SYSDATE
NEXT (TRUNC(SYSDATE+1) + 4/24)
AS
select fiscal_year, fiscal_period, account_no,
trans_type, trans_amount
from my_general_ledge
where trans_type in ('A','B')
It errors with ORA-12015 cannot create a fast refresh snapshot from a
complex query. According to MetaLink doc 179466.1, a
complex query in
a MV
on 8.1.7 is one that has at least one of:
- a distinct or unique keyword
- an aggregate function (e.g. avg, count, max, min, sum, )
- a connect by clause
- a group by or order by clause
- a set operator (UNION, UNION ALL, INTERSECT and MINUS)
- joins other than those in a subquery
But my query has none of those? Thinking somethings hosed or I'm
completely
misunderstanding the IN operator of the WHERE clause, I tried where
trans_type = 'A', but with the same results.
Also, I've created the MV log for the fast refresh:
CREATE MATERIALIZED VIEW LOG ON my_general_ledger
WITH ROWID
I'm just testing out MVs here, but I'm missing something real simple
and/or
stupid here, but for the life of me I can't see what (even
after a lunch
break).
Anyone?
Rich Jesse System/Database Administrator
[EMAIL PROTECTED] Quad/Tech International,
Sussex, WI
USA
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jesse, Rich
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: Igor Neyman
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: No space left on device - but I have lots left.
Did I, I might have, if that's the case; you waltz divinely, almost Clintonesque ;) On Mon, Sep 09, 2002 at 10:48:21AM -0800, Gogala, Mladen wrote: Did I shoot from the hip too quickly? I was in a bad mood caused by some other issues. I've received a few emails telling me how grumpy I appeared to be, some of them through an anonymous remailer. There were also some interesting assumptions about my place on the tree of evolution as well as the species of my ancestry. From subtle hints like that, I conclude that I might have gone too far. If that's the case, I apologize. -Original Message- From: Jared Still [mailto:[EMAIL PROTECTED]] Sent: Sunday, September 08, 2002 3:48 PM To: Multiple recipients of list ORACLE-L Subject: Re: No space left on device - but I have lots left. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gogala, Mladen INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- === Ray Stell [EMAIL PROTECTED] (540) 231-4109 KE4TJC28^D -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ray Stell INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
using obfuscation
Hi all, I have downloaded the Metalink Notes on implementing dbms_obfuscation. I am using multiple front ends on the database, so the way I plan to implement the de-encryption is with a de-encrypt function in a view. Create View my_data AS Select de_encrypt(sensitive_data) AS sensitive_data ,other_data FROM original_table ; If I select from the view with a where clause on other_data, the response time is fine. If I select from the view with a where clause on sensitive_data, I do a full table scan and which takes about 15 minutes. The de-encrypt function is copied from a Metalink note, nothing fancy. Since I have various front ends, I can not de-encrypt the data in the front end. The only way I can think of is with the function in a view, but the response time is unacceptable. Does anyone have any thoughts on this? Thanks Randy -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steiner, Randy INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: foreign key indexes and parent-table locking
Jared: I've now run through a basic test (on 8.1.7.2.1) and lo and behold I get the exact same results - index or no index! For each dml I show the results of this query for the session performing the DML: select type, id1, id2, lmode, request, block from v$lock here's the process: create table tparent (parentid number primary key); create table tchild (childid number primary key, parentid number, constraint parentid_fk foreign key (parentid) references tparent(parentid)); 1) insert into tparent values (1); LOCK INFO - shared lock on tparent (ID 26902), exclusive row lock on the row TYID1ID2 LMODEREQUEST BLOCK -- -- -- -- -- -- TX 196617 6339 6 0 0 TM 26902 0 3 0 0 2) insert into tchild values (1,1); LOCK INFO - additional shared lock on tchild (ID 26904) TYID1ID2 LMODEREQUEST BLOCK -- -- -- -- -- -- TX 196617 6339 6 0 0 TM 26904 0 3 0 0 TM 26902 0 3 0 0 3) insert into tchild values (2,1); LOCK INFO - no change TYID1ID2 LMODEREQUEST BLOCK -- -- -- -- -- -- TX 196617 6339 6 0 0 TM 26904 0 3 0 0 TM 26902 0 3 0 0 4) commit; 5) insert into tparent values (2); LOCK INFO - again, shared lock on tparent, exclusive row lock TYID1ID2 LMODEREQUEST BLOCK -- -- -- -- -- -- TX 131098 6319 6 0 0 TM 26902 0 3 0 0 6) commit; NO INDEX ON FOREIGN KEY 7) update tchild set parentid = 2 where childid = 2; LOCK INFO - shared lock on tchild (ID 26904), no lock on tparent TYID1ID2 LMODEREQUEST BLOCK -- -- -- -- -- -- TX 262179 6370 6 0 0 TM 26904 0 3 0 0 8) rollback; NOW ADD INDEX ON FOREIGN KEY AND REEXECUTE PREVIOUS STEP 9) create index tchild_i1 on tchild (parentid); 10) update tchild set parentid = 2 where childid = 2; LOCK INFO - shared lock on tchild, no lock on tparent TYID1ID2 LMODEREQUEST BLOCK -- -- -- -- -- -- TX 262177 6370 6 0 0 TM 26904 0 3 0 0 So here's the question - without the index created on the foreign key in step 9, the update in step 7, according to Oracle, should have produced a share lock on the parent table, tparent. But here, both with and without the index, an update made to the child table produces no locks on the parent table. Am I missing something? thanks bill -Original Message- Sent: Friday, September 06, 2002 2:23 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] The theory will make much more sense after you see it in action. Jared Magaliff, Bill [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 09/06/2002 07:23 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: foreign key indexes and parent-table locking I agree that that's the best way to see what actually happens, and I will do that but I like to understand the theory, too . . . -bill -Original Message- Sent: Thursday, September 05, 2002 5:40 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Importance: High Bill, Rather than try to understand that explanation, you may find it more educational to create a pair of tables with a parent/child relationship via foreign key. Put some data in the tables, then do updates and deletes both with and without FK indexes. Examine dba_locks while doing so and observe the lock modes. This will be much easier to understand than the 'documentation' Jared Magaliff, Bill [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 09/05/2002 02:23 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:foreign key indexes and parent-table locking Hi, I'm trying to understand the whole issue of foreign key indexes and locking. Found a note on metalink (11828.1) that seems to explain it, but either it's not clear or I'm missing something. Why then, does an index on the foreign key mean that the shared lock on the parent table is not required? When a row in the child table is inserted, deleted or has its foreign key updated, the
Recreating database from hot backup but only 1 tablespace - Urgen
We need to recover a table that had been truncated. Unfortunately at this time we don't have a server large enough to copy our complete backup (I know, I've been yelling about this for over a year). Can we copy just the tablespaces holding the SYS objects and the table we need, modify our create controlfile script (backed up to trace) and open it that way? I've never tried this before and it's rather urgent so I'm e-mailing while I'm still searching the Velpuri book. Any other suggestions are welcome. Thanks, Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: using obfuscation
Randy, Do you have an index on SENSITIVE_DATA? Also, you didn't include the long running query. Does it look something like select * from original_table where decrypt(sensitive_data) = 'CLEAR TEXT' ? That requires a full table scan, unless you build a functional index with the clear text of the sensitive_data. But if you do that, your data won't really be secure, as the clear text will now be in an index. Jared Steiner, Randy [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 09/09/2002 02:43 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:using obfuscation Hi all, I have downloaded the Metalink Notes on implementing dbms_obfuscation. I am using multiple front ends on the database, so the way I plan to implement the de-encryption is with a de-encrypt function in a view. Create View my_data AS Select de_encrypt(sensitive_data) AS sensitive_data ,other_data FROM original_table ; If I select from the view with a where clause on other_data, the response time is fine. If I select from the view with a where clause on sensitive_data, I do a full table scan and which takes about 15 minutes. The de-encrypt function is copied from a Metalink note, nothing fancy. Since I have various front ends, I can not de-encrypt the data in the front end. The only way I can think of is with the function in a view, but the response time is unacceptable. Does anyone have any thoughts on this? Thanks Randy -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steiner, Randy INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
dbwr high count of threads
Hi all, I noticed the dbwr process on Solaris has a very high number of threads (258). To me this is not a problem since I am seeing this on my small development box too. But we recently had some server crash and the consultant is saying Oracle is consuming a lots of resource, citing the high number of Oracle thread count from the core dump analysis. I don't believe this lead to the crash because the core stack trace points to NFS calls in both times. However, I don't know how to explain the high number thread count mostly from the dbwr process. Is this normal? I mean, it looks like it's normal since I see this on all of my instances. How do I convince him that this is ok? Thanks for your help. Richard -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ji, Richard INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Recreating database from hot backup but only 1 tablespace - U
Jay You should also bring the tablespace holding your ROLLBACK segments. Archive and redo logs, of course. Other than that, I think you are off to a promising start. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, September 09, 2002 5:04 PM To: Multiple recipients of list ORACLE-L Urgen We need to recover a table that had been truncated. Unfortunately at this time we don't have a server large enough to copy our complete backup (I know, I've been yelling about this for over a year). Can we copy just the tablespaces holding the SYS objects and the table we need, modify our create controlfile script (backed up to trace) and open it that way? I've never tried this before and it's rather urgent so I'm e-mailing while I'm still searching the Velpuri book. Any other suggestions are welcome. Thanks, Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Recreating database from hot backup but only 1 tablespace - U
Thanks Dennis, The rollback segments occurred to me already, I'm composing the email to our SAs to restore the files (I'm going to have to wipe out another test box but it's one that can easily be recreated). Thanks! Jay Miller -Original Message- Sent: Monday, September 09, 2002 5:14 PM To: '[EMAIL PROTECTED]' Cc: '[EMAIL PROTECTED]' U rgen Jay You should also bring the tablespace holding your ROLLBACK segments. Archive and redo logs, of course. Other than that, I think you are off to a promising start. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, September 09, 2002 5:04 PM To: Multiple recipients of list ORACLE-L Urgen We need to recover a table that had been truncated. Unfortunately at this time we don't have a server large enough to copy our complete backup (I know, I've been yelling about this for over a year). Can we copy just the tablespaces holding the SYS objects and the table we need, modify our create controlfile script (backed up to trace) and open it that way? I've never tried this before and it's rather urgent so I'm e-mailing while I'm still searching the Velpuri book. Any other suggestions are welcome. Thanks, Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: No space left on device - but I have lots left.
Wait until you see me debating the finer points of the word is. -Original Message- From: Ray Stell [mailto:[EMAIL PROTECTED]] Sent: Monday, September 09, 2002 5:25 PM To: Multiple recipients of list ORACLE-L Subject: Re: No space left on device - but I have lots left. Did I, I might have, if that's the case; you waltz divinely, almost Clintonesque ;) On Mon, Sep 09, 2002 at 10:48:21AM -0800, Gogala, Mladen wrote: Did I shoot from the hip too quickly? I was in a bad mood caused by some other issues. I've received a few emails telling me how grumpy I appeared to be, some of them through an anonymous remailer. There were also some interesting assumptions about my place on the tree of evolution as well as the species of my ancestry. From subtle hints like that, I conclude that I might have gone too far. If that's the case, I apologize. -Original Message- From: Jared Still [mailto:[EMAIL PROTECTED]] Sent: Sunday, September 08, 2002 3:48 PM To: Multiple recipients of list ORACLE-L Subject: Re: No space left on device - but I have lots left. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gogala, Mladen INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- === Ray Stell [EMAIL PROTECTED] (540) 231-4109 KE4TJC28^D -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ray Stell INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Gogala, Mladen INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Recreating database from hot backup but only 1 tablespace - U
Jay - Here is a link to the Oracle documentation for what you are attempting. You'll have to patch the link back together. I don't know what your Oracle version is, but the main change with the newer version is that you can use transportable tablespaces to move the resulting tablespace back to production, while on the older Oracle versions you must use something like export/import. Well, I am glad my study for the OCP benefited you, rather than me. I'm heading home for the evening, but there are other list participants in different time zones if you hit a glitch. Best of luck. http://download-west.oracle.com/otndoc/oracle9i/901_doc/server.901/a90134/os tspitr.htm Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, September 09, 2002 5:38 PM To: Multiple recipients of list ORACLE-L U Thanks Dennis, The rollback segments occurred to me already, I'm composing the email to our SAs to restore the files (I'm going to have to wipe out another test box but it's one that can easily be recreated). Thanks! Jay Miller -Original Message- Sent: Monday, September 09, 2002 5:14 PM To: '[EMAIL PROTECTED]' Cc: '[EMAIL PROTECTED]' U rgen Jay You should also bring the tablespace holding your ROLLBACK segments. Archive and redo logs, of course. Other than that, I think you are off to a promising start. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, September 09, 2002 5:04 PM To: Multiple recipients of list ORACLE-L Urgen We need to recover a table that had been truncated. Unfortunately at this time we don't have a server large enough to copy our complete backup (I know, I've been yelling about this for over a year). Can we copy just the tablespaces holding the SYS objects and the table we need, modify our create controlfile script (backed up to trace) and open it that way? I've never tried this before and it's rather urgent so I'm e-mailing while I'm still searching the Velpuri book. Any other suggestions are welcome. Thanks, Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Changing sysdate
Title: RE: Constraints problem I remember a Y2K testing parameter for setting the database date. It still might work. Search for FIXED_DATE to see what NLS date format to use. HTH Tony Aponte -Original Message-From: Karthikeyan S [mailto:[EMAIL PROTECTED]]Sent: Wednesday, September 04, 2002 2:48 AMTo: Multiple recipients of list ORACLE-LSubject: Changing sysdate Hi, Is it possible to change the sysdate? (Solaris 5.8, Oracle 8.1.7) TIA. K.
Sort Area Size?
Hello, For an SGA of about 512MB and a 2GB database (total of the *.dbf) files. Is 1MB sort area size enough? Thank you for any tips. ltiu -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: ltiu INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Privileges for other's table
Ok, I haven't had to deal with privileges much lately but this one had be stumped for a while, V8.1.7 Tru64. This is from an account with minimal privileges: SQL select count(*) from sys.obj$; select count(*) from sys.obj$ * ERROR at line 1: ORA-00942: table or view does not exist SQL connect sys Enter password: Connected. SQL grant select on obj$ to utility; Grant succeeded. SQL connect utility/ Connected. SQL select count(*) from sys.obj$; COUNT(*) -- 37742 SQL create view o_by_vlo as select name, obj# from sys.obj$; create view o_by_vlo as select name, obj# from sys.obj$ * ERROR at line 1: ORA-00942: table or view does not exist SQL connect sys Enter password: Connected. SQL grant select on obj$ to utility with grant option; Grant succeeded. SQL connect utility/ Connected. SQL create view o_by_vlo as select name, obj# from sys.obj$; View created. . . . So it would seem that with grant option is needed to create a view on another schema's tables. It took a little thinking on my part to realize that this makes sense as the view owner would be able to grant privileges on the underlying tables. Incidentally, I also found out that you can't use the grant option within roles. I wonder why? Michael Hand Polaroid Corp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hand, Michael T INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: ALTER TABLE MOVE command causes table to grow
What is the DEGREE setting on the table? -Original Message- Sent: Monday, September 09, 2002 4:48 PM To: Multiple recipients of list ORACLE-L alter table email_request_queue move tablespace ts_email_request storage (initial 25m next 25m pctincrease 0); -Original Message- Sent: Monday, September 09, 2002 1:54 PM To: Multiple recipients of list ORACLE-L How did you perform the move? Was the operation done in parallel? -Original Message- Sent: Friday, September 06, 2002 4:13 PM To: Multiple recipients of list ORACLE-L No LOBS. Here's the definition: REQUEST_IDNOT NULL NUMBER PROFILE_IDNOT NULL NUMBER ACCOUNT_IDNOT NULL NUMBER TEMPLATE_ID NOT NULL NUMBER GENERIC_DIFFERENTIATORNOT NULL NUMBER REQUEST_TYPE NOT NULL CHAR(1) ACCOUNT_NONOT NULL CHAR(8) EFFECTIVE_DATENOT NULL DATE EMAIL_ADDRESS NOT NULL VARCHAR2(100) EMAIL_SUBJECT NOT NULL VARCHAR2(100) EMAIL_BODYNOT NULL VARCHAR2(4000) STATUSNOT NULL CHAR(1) STATUS_CHANGE_DATENOT NULL DATE TWEED_SERVER_IDNUMBER TWEED_PACKAGE_PRIORITY NUMBER TWEED_SENDER_ACCOUNT NOT NULL VARCHAR2(50) TWEED_SCHEDULED_DELIVERY_DATE NOT NULL DATE TWEED_CUSTOMER_URLNOT NULL VARCHAR2(255) SENDER_SERVER_ID NUMBER SENDER_INSTANCE_ID NUMBER CREATE_DATE NOT NULL DATE CREATE_USER NOT NULL VARCHAR2(35) UPDATE_DATEDATE UPDATE_USERVARCHAR2(35) -Original Message- Sent: Thursday, September 05, 2002 5:23 PM To: Multiple recipients of list ORACLE-L What the table definition? Are there any LOB's on it? -Original Message- Sent: Thursday, September 05, 2002 2:43 PM To: Multiple recipients of list ORACLE-L pct increase is 0 (uniform sizing) -Original Message- Sent: Thursday, September 05, 2002 2:00 PM To: Multiple recipients of list ORACLE-L you didn't mention the PCT_INCREASE of this segment. -Original Message- mailto:[EMAIL PROTECTED] ] Sent: Thursday, September 05, 2002 12:09 PM To: Multiple recipients of list ORACLE-L Had an annoying surprise last week. A table had grown unexpectedly large and I scheduled a time over the weekend to move it to its own tablespace from my medium tablespace. The table ended up growing 50%. I had anticipated it might grow somewhat given the PCTFREE of 10% but freeing up that space in the blocks should, at most, have caused it to grow by 10% (assuming that 10% was completely full). Does anyone have ideas as to why it would have grown by so much? Indexes are in a different tablespace and the only other change was from an extent size of 4 meg to one of 25 meg. Both are dictionary managed tablespaces. Oracle 8.1.7.2 Solaris 2.6 Thanks, Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.com http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Johnston, Tim INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists
RE: Sort Area Size?
I suggest you check the amount of disk sort activity (v$sysstat, v$sesstat). If there are numerous disk sorts taking place, you may want to consider raising Sort_Area_Size and Sort_Area_Retained_Size. Also, if the sorting (to disk) is taking place mainly in the batch processes, consider changing these parameters only for those sessions. Changing these parameters at instance level may waste system memory. HTH, - Kirti -Original Message- Sent: Monday, September 09, 2002 6:18 PM To: Multiple recipients of list ORACLE-L Hello, For an SGA of about 512MB and a 2GB database (total of the *.dbf) files. Is 1MB sort area size enough? Thank you for any tips. ltiu -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: ltiu INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Oracle and Cache Hit Ratio based training
Performance Tuning 102 dear, Performance Tuning 102 --- Fink, Dan [EMAIL PROTECTED] wrote: I can see it now... DBA 102:Buffer Cache Wait Hit Ratio Interface Tuning... -Original Message- Sent: Monday, September 09, 2002 12:13 PM To: Multiple recipients of list ORACLE-L FWIW ... One of our DBAs just got back from a Oracle 9i course and it was stated by the instuctor that Oracle is in the process of revamping all their performance classes to be based on the wait interface. Looks like Cache Hit Ratios may finally go away !!! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Johnson, Michael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Fink, Dan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Yahoo! Finance - Get real-time stock quotes http://finance.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).
Nasty bug in 9.2.0.1 (was: Oracle and Cache Hit Ratio based train
Title: Nasty bug in 9.2.0.1 (was: Oracle and Cache Hit Ratio based training) FWIW There is a document (id 283852) on Metalink updated on 03-SEP-2002 created by CENTER OF EXPERTISE named THE COE PERFORMANCE METHOD A PERFORMANCE METHODOLOGY FOR ENTERPRISE-WIDE INFORMATION SYSTEMS which shows that Oracle Corporation turned from ratio to wait interface approach. The bad news is that in version 9.2.0.1 view V$SESSION_EVENT broken (bug 2429929), also described in Notes 208066.1. Bug fixed in 10i. This bug affects OEM, BSTAT/ESTAT, STATSPACK, etc. Did anybody adjust scripts which join V$SESSION with V$SESSION_EVENT? Should we use something like s.sid=e.sid+1 or s.sid-1=e.sid? Alex. -Original Message- From: Johnson, Michael [mailto:[EMAIL PROTECTED]] Looks like Cache Hit Ratios may finally go away !!!
Re: Privileges for other's table
Hand, Michael T, hi, what you tested is not the normal behavior of oracle, neither the document said like that nor in my database behavior like that.Maybe something is wrong with your database:) Look: SQL conn internal Connected. SQL create user t identified by t; User created. SQL grant connect to t; Grant succeeded. SQL conn t/t Connected. SQL select count(*) from sys.obj$; select count(*) from sys.obj$ * ERROR at line 1: ORA-00942: table or view does not exist SQL conn internal Connected. SQL grant select on obj$ to t; Grant succeeded. SQL conn t/t Connected. SQL select count(*) from sys.obj$; COUNT(*) -- 26011 SQL create or replace view sysobj as select * from sys.obj$; View created. SQL select * from v$version; BANNER Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production PL/SQL Release 8.1.7.0.0 - Production CORE8.1.7.0.0 Production TNS for Linux: Version 8.1.7.0.0 - Development NLSRTL Version 3.4.1.0.0 - Production Regards zhu chao Eachnet DBA 86-21-32174588-667 [EMAIL PROTECTED] www.happyit.net === 2002-09-09 16:23:00 ,you wrote£º=== Ok, I haven't had to deal with privileges much lately but this one had be stumped for a while, V8.1.7 Tru64. This is from an account with minimal privileges: SQL select count(*) from sys.obj$; select count(*) from sys.obj$ * ERROR at line 1: ORA-00942: table or view does not exist SQL connect sys Enter password: Connected. SQL grant select on obj$ to utility; Grant succeeded. SQL connect utility/ Connected. SQL select count(*) from sys.obj$; COUNT(*) -- 37742 SQL create view o_by_vlo as select name, obj# from sys.obj$; create view o_by_vlo as select name, obj# from sys.obj$ * ERROR at line 1: ORA-00942: table or view does not exist SQL connect sys Enter password: Connected. SQL grant select on obj$ to utility with grant option; Grant succeeded. SQL connect utility/ Connected. SQL create view o_by_vlo as select name, obj# from sys.obj$; View created. . . . So it would seem that with grant option is needed to create a view on another schema's tables. It took a little thinking on my part to realize that this makes sense as the view owner would be able to grant privileges on the underlying tables. Incidentally, I also found out that you can't use the grant option within roles. I wonder why? Michael Hand Polaroid Corp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hand, Michael T INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = = = = = = = = = = = = = = = = = = = = -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: zhu chao INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: multiple 8.1.7 db Migration Question
At 09:58 AM 9/9/2002, sarath kumar wrote: Dear List, i have to migrate around 25 databases to oracle 8.1.7 from 7.3.4. is there an automated procdure where i can run on each server. like a shell script where i can copy the 8.1.7 directory and run the migration process without using the OUI. I assume that the reason you don't want to use OUI is that you don't want to have a human sitting there hitting buttons over and over, not because OUI is incapable of doing what you want. If this is the case, search tahiti.oracle.com for response files. It will walk you through scripting the OUI, so that it can do it's work automagically. thanks Sarath __ Do You Yahoo!? Yahoo! Finance - Get real-time stock quotes http://finance.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: sarath kumar INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Justin Cave -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Justin Cave INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Aout plan stabilty matching.
Hi, All, From Oracle Doc: if the SQL text of the incoming statement exactly matches the SQL text in an outline in that category, then Oracle considers both texts identical, and Oracle uses the outline. Oracle considers any differences a mismatch. How could I ensure the incoming SQL text exactly match the SQL text in an outline? If I fish out an offensive SQL from library cache by some scripts in SQL*Plus, is this offensive SQL text identical to the incoming SQL text? Supposed this SQL text is extracted from stored procedure. Appreciated your experience. Chuan Unless otherwise stated, this e-mail does not represent the views of TransACT Communications Pty Limited. This text and any attachments of this e-mail are confidential and may be legally privileged. This email is for the use of the intended recipient only. If you are not the intended recipient do not take any action in relation to this email, other than to notify TransACT Communications by replying to this e-mail and destroying the original communication. Except as required by law, TransACT Communications does not represent that this transmission is free of errors, viruses or interference.
