RE: dbms_stats.gather_schema_stats
I have a nightly running script to execute DBMS_STATS.GATHER_TABLE_STATS on all tables in a schema. The schema has about 250 tables. This program 2-3 hours to finish. Guang > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of > Mitchell > Sent: Thursday, June 12, 2003 4:10 PM > To: Multiple recipients of list ORACLE-L > Subject: Re: dbms_stats.gather_schema_stats > > > Hi All > > May I know what is best interval to exec > dbms_stats.gather_schema_stats on a > instance with 300 tables that updated everyday. > > By the way, Anybody has any detailed command reference for > dbms_stats. I am > not sure for the following such as degree? > > execute dbms_stats.gather_schema_stats > > ownname ='FNSS', > degree => 4, > cascade => true > ); > > Thanks in advance > Mitchell > > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Mitchell > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: gmei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
OT: question about DynaLoader::dl_load_file from perl profiler output
Hi: I am using this command to profile a perl program on our unix box /tools/bin/perl -d:DProf my_program.pl. and then "dprofpp -u" to get the following: Total Elapsed Time = 0.477847 Seconds User Time = 0.319582 Seconds Exclusive Times %Time ExclSec CumulS #Calls sec/call Csec/c Name 49.7 0.159 0.295 6 0.0265 0.0491 main::BEGIN 12.5 0.040 0.040 2 0.0200 0.0200 DynaLoader::dl_load_file 6.26 0.020 0.020 1 0.0200 0.0200 DBD::Oracle::db::_login 6.26 0.020 0.020 8 0.0025 0.0025 Exporter::export 3.13 0.010 0.010 1 0.0100 0.0100 AutoLoader::AUTOLOAD 3.13 0.010 0.010 94 0.0001 0.0001 DBI::_install_method 3.13 0.010 0.017 46 0.0002 0.0004 DBI::db::prepare 3.13 0.010 0.020 3 0.0033 0.0066 File::Spec::Unix::BEGIN 3.13 0.010 0.009 48 0.0002 0.0002 DBI::_new_handle 3.13 0.010 0.050 1 0.0100 0.0498 DBI::install_driver 3.13 0.010 0.099 1 0.0099 0.0989 obsData::BEGIN 0.00 0.000 -0.000 2 0. - DynaLoader::dl_load_flags 0.00 0.000 -0.000 2 0. - DynaLoader::dl_undef_symbols 0.00 0.000 -0.000 2 0. - DynaLoader::dl_find_symbol 0.00 0.000 -0.000 2 0. - DynaLoader::dl_install_xsub What is "DynaLoader::dl_load_file" corresponding to in my perl code? TIA. Guang -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: gmei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Performance Problems Solaris Vs Windows
Have you run "analyze table ...compute statitics;" on these tables on Solaris? Guang -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]On Behalf Of Munish BajajSent: Wednesday, June 11, 2003 12:35 PMTo: Multiple recipients of list ORACLE-LSubject: Performance Problems Solaris Vs Windows Hi Gurus, I'm facing a weird problem. I'm running a same query on windows as well as on Solaris both having Oracle 8.0.5 database using CBO optimizer. The Query runs fine on Windows (takes 20-30 Secs) while the same hangs on Solaris and takes 4-6 hrs to return the results. Both the databases have approx same number of rows and indexes. The Query is : SELECT COUNT(*) FROM mam_assets a, mam_asset_attr_domain_values dmv65549 WHERE a."ID" = dmv65549.asset_id AND a."ID" IN (SELECT dmv3.asset_id FROM mam_asset_attr_domain_values dmv3 WHERE dmv3.domain_value_id = 71 AND dmv3.asset_attribute_xid = 3 AND dmv3.domain_xid = 7) AND a."ID" IN (SELECT dmv3.asset_id FROM mam_asset_attr_domain_values dmv3 WHERE dmv3.domain_value_id = 71 AND dmv3.asset_attribute_xid = 3 AND dmv3.domain_xid = 7) Explain Plan on Solaris 16 SELECT STATEMENT 15 SORT (AGGREGATE) 14 NESTED LOOPS 12 NESTED LOOPS 10 MERGE JOIN (CARTESIAN) 4 VIEW 3 SORT (UNIQUE) 2 TABLE ACCESS (BY INDEX ROWID), MAM_ASSET_ATTR_DOMAIN_VALUES (GMASTER) 1 INDEX (RANGE SCAN), ATRVALDOM_DOMVAL_FK_I (GMASTER) 9 SORT (JOIN) 8 VIEW 7 SORT (UNIQUE) 6 TABLE ACCESS (BY INDEX ROWID), MAM_ASSET_ATTR_DOMAIN_VALUES (GMASTER) 5 INDEX (RANGE SCAN), ATRVALDOM_DOMVAL_FK_I (GMASTER) 11 INDEX (UNIQUE SCAN), AST_PK (GMASTER) 13 INDEX (RANGE SCAN), ATRVALDOM_AST_FK_I (GMASTER) Explain Plan on Windows 15 SELECT STATEMENT 14 SORT (AGGREGATE) 13 NESTED LOOPS 11 HASH JOIN 4 VIEW 3 SORT (UNIQUE) 2 TABLE ACCESS (BY INDEX ROWID), MAM_ASSET_ATTR_DOMAIN_VALUES (QUARKDMS) 1 INDEX (RANGE SCAN), ATRVALDOM_DOMVAL_FK_I (QUARKDMS) 10 NESTED LOOPS 8 VIEW 7 SORT (UNIQUE) 6 TABLE ACCESS (BY INDEX ROWID), MAM_ASSET_ATTR_DOMAIN_VALUES (QUARKDMS) 5 INDEX (RANGE SCAN), ATRVALDOM_DOMVAL_FK_I (QUARKDMS) 9 INDEX (UNIQUE SCAN), AST_PK (QUARKDMS) 12 INDEX (RANGE SCAN), ATRVALDOM_AST_FK_I (QUARKDMS) As u can clearly see that on Solaris the Oracle does a Merge Join (Cartesian) which is very expensive and hence takes a lot of time. Please help me understand this and provide any solution if possible. Thanks to One and all Best Regards Munish Bajaj
sql query optimization
NUMBER GAPNUMBER PVAL VARCHAR2(16) SCORE NUMBER QUERYSTART NUMBER QUERYEND NUMBER SUBJSTART NUMBER SUBJENDNUMBER CCOMMENT VARCHAR2(300) BLASTDATE DATE QFRAME NUMBER SFRAME NUMBER QUERYSPID NUMBER SUBJSPID NUMBER SQL> desc mt.seqtable ; Name Null?Type - IDNOT NULL NUMBER AASEQIDNUMBER DNASEQID NUMBER GENEIDNOT NULL NUMBER USECHAR(1) ALTSPLICE VARCHAR2(128) MUTANT VARCHAR2(128) STRAIN VARCHAR2(128) CDSSTRING VARCHAR2(2000) VALID CHAR(1) GENOMEPROJ CHAR(1) CDNA CHAR(1) PARTIALNTERM CHAR(1) PARTIALCTERM CHAR(1) TRANSIDNUMBER CCOMMENT VARCHAR2(300) ESTCHAR(1) CLASS VARCHAR2(128) SEQDATEDATE CURID NUMBER SQL> desc isi.observationlist; Name Null?Type - IDNOT NULL NUMBER GENEID NUMBER CURATIONTYPE NUMBER PROTEOMEREFID NUMBER SOURCEID NUMBER SOURCETABLEVARCHAR2(25) DESTID NUMBER DESTTABLE VARCHAR2(25) DESTDATE DATE REFERENCETYPE VARCHAR2(1) EVIDENCECODE NUMBER CURATORID NUMBER EDITORID NUMBER UPDATESTAMPDATE CURATIONSTATUS VARCHAR2(1) ORIGINALSTAMP DATE NEXTOBSNUMBER TARGET VARCHAR2(15) REFTARGET VARCHAR2(15) TOOL VARCHAR2(25) OLDGENEID NUMBER SQL> desc isi.termobs; Name Null?Type - --- OBSID NUMBER TERMID NUMBER CONTEXTNUMBER SQL> desc isi.arc Name Null?Type - --- TERMIDNOT NULL NUMBER PARENTTERMID NOT NULL NUMBER OBSID NUMBER ARCTYPE NOT NULL NUMBER ARCID NUMBER -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: gmei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: query run time vs IN list elements
Yes, if I took the "count(descriptortermid)" part out, the query run very fast. Thanks for you help. Guang > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of > [EMAIL PROTECTED] > Sent: Friday, June 06, 2003 4:00 PM > To: Multiple recipients of list ORACLE-L > Subject: Re: query run time vs IN list elements > > > ok i think i figured out what is going on. sorry for all the > emails. long day... > > Look at your query. You are including a count and a group by. > Both are costly operations. As you increase the values in > 'IN' you are increasing the size of the resultset. > > COUNT and group by need to work harder since the resultset > they are working off of is larger. > > Its easier to count to 10 and then group then it is to count > to 10,000 and group. > > > > -- Elapsed: 00:00:16.93 > select geneid ,genegroup, count(descriptortermid) > from gene2disease2H > where geneid in > (165383,165409,165603,166524,149490,166141,166342,166829,16683 > 2,167771,16849 > 7,163450,163817, > 165751,167437,167438,167476,164118,166054,168728,162967,164533 > ,164545,163212 > , 168501) > group by geneid, genegroup; > > > > > > From: "gmei" <[EMAIL PROTECTED]> > > Date: 2003/06/06 Fri PM 02:09:44 EDT > > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > > Subject: query run time vs IN list elements > > > > Hi: > > > > Today I have something I don't fully understand. I have > oracle 8173 on Sun > > Solaris. I have the following query that runs pretty fast > when the number of > > elements in the "IN" list is small. But if I kept adding > more "geneids" in > > the IN list, my query time increased dramatically. Now > there is no index on > > any columns on the table. I got very similar results even > if I created index > > on gene2disease2H.geneid. So this seem to suggest this > situation has not to > > do with index. > > > > So my question is: why did I see the sigificant time > increase when I only > > add one more geneid? > > > > TIA. > > > > Guang > > > > SQL> desc gene2disease2H > > Name Null?Type > > - > -- > > GENEID NUMBER > > GENEGROUP VARCHAR2(25) > > DESCRIPTORTERMID NOT NULL NUMBER > > NUMABSTRACTS NUMBER > > DATESTAMP DATE > > > > > > -- Elapsed: 00:00:08.32 > > select geneid ,genegroup, count(descriptortermid) > > from gene2disease2H > > where geneid in > > > (165383,165409,165603,166524,149490,166141,166342,166829,16683 > 2,167771,16849 > > 7,163450,163817, > > > 165751,167437,167438,167476,164118,166054,168728,162967,164533 > ,164545,163212 > > ) > > group by geneid, genegroup; > > > > > > -- Elapsed: 00:00:16.93 > > select geneid ,genegroup, count(descriptortermid) > > from gene2disease2H > > where geneid in > > > (165383,165409,165603,166524,149490,166141,166342,166829,16683 > 2,167771,16849 > > 7,163450,163817, > > > 165751,167437,167438,167476,164118,166054,168728,162967,164533 > ,164545,163212 > > , 168501) > > group by geneid, genegroup; > > > > > > > > -- Elapsed: 00:00:31.97 > > select geneid ,genegroup, count(descriptortermid) > > from gene2disease2H > > where geneid in > > > (165383,165409,165603,166524,149490,166141,166342,166829,16683 > 2,167771,16849 > > 7,163450,163817, > > > 165751,167437,167438,167476,164118,166054,168728,162967,164533 > ,164545,163212 > > , 168501,151968) > > group by geneid, genegroup; > > > > > > -- Elapsed: 00:01:61.51 > > select geneid ,genegroup, count(descriptortermid) > > from gene2disease2H > > where geneid in > > > (165383,165409,165603,166524,149490,166141,166342,166829,16683 > 2,167771,16849 > > 7,163450,163817, > > > 165751,167437,167438,167476,164118,166054,168728,162967,164533 > ,164545,163212 > > , 168501,151968, > > 166472) > > group by geneid, genegroup; > > > > > > -- Elapsed: 00:02:124.63 > > select geneid ,genegroup, count(descriptortermid) > > from gene2disease2H > > where geneid in > > > (165383,165409,165603,166524,149490,166141,166342,
RE: query run time vs IN list elements
Title: RE: query run time vs IN list elements Hi: 1. I should have mentioned that I analyzed the index after creating the index, also I looked at the explain table in both situations: Execution Plan-- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=81 Card=267 Bytes=29 37) 1 0 SORT (GROUP BY) (Cost=81 Card=267 Bytes=2937) 2 1 TABLE ACCESS (FULL) OF 'GENE2DISEASE2H' (Cost=28 Card=26 7 Bytes=2937) Execution Plan-- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=57 Card=267 Bytes=29 37) 1 0 SORT (GROUP BY) (Cost=57 Card=267 Bytes=2937) 2 1 INLIST ITERATOR 3 2 TABLE ACCESS (BY INDEX ROWID) OF 'GENE2DISEASE2H' (Cos t=4 Card=267 Bytes=2937) 4 3 INDEX (RANGE SCAN) OF 'GENE2DISEASE2H_GENEID' (NON-U NIQUE) (Cost=2 Card=267) 2. I have not tried using "exists". I was wondering why IN had such problem (I have not seen this situation before)? Guang -Original Message-From: Whittle Jerome Contr NCI [mailto:[EMAIL PROTECTED]Sent: Friday, June 06, 2003 2:14 PMTo: [EMAIL PROTECTED]Cc: [EMAIL PROTECTED]Subject: RE: query run time vs IN list elements Guang, 1. Just because you created an index doesn't necessarily mean Oracle is using it especially if using CBO and you haven't analyzed the table and index after the index creation. Try using a hint. 2. If IN isn't meeting your needs, try converting it to an EXISTS statement. Jerry Whittle ASIFICS DBA NCI Information Systems Inc. [EMAIL PROTECTED] 618-622-4145 -Original Message- From: gmei [SMTP:[EMAIL PROTECTED] Hi: Today I have something I don't fully understand. I have oracle 8173 on Sun Solaris. I have the following query that runs pretty fast when the number of elements in the "IN" list is small. But if I kept adding more "geneids" in the IN list, my query time increased dramatically. Now there is no index on any columns on the table. I got very similar results even if I created index on gene2disease2H.geneid. So this seem to suggest this situation has not to do with index. So my question is: why did I see the sigificant time increase when I only add one more geneid? TIA. Guang <<< cut >>>
query run time vs IN list elements
Hi: Today I have something I don't fully understand. I have oracle 8173 on Sun Solaris. I have the following query that runs pretty fast when the number of elements in the "IN" list is small. But if I kept adding more "geneids" in the IN list, my query time increased dramatically. Now there is no index on any columns on the table. I got very similar results even if I created index on gene2disease2H.geneid. So this seem to suggest this situation has not to do with index. So my question is: why did I see the sigificant time increase when I only add one more geneid? TIA. Guang SQL> desc gene2disease2H Name Null?Type - -- GENEID NUMBER GENEGROUP VARCHAR2(25) DESCRIPTORTERMID NOT NULL NUMBER NUMABSTRACTS NUMBER DATESTAMP DATE -- Elapsed: 00:00:08.32 select geneid ,genegroup, count(descriptortermid) from gene2disease2H where geneid in (165383,165409,165603,166524,149490,166141,166342,166829,166832,167771,16849 7,163450,163817, 165751,167437,167438,167476,164118,166054,168728,162967,164533,164545,163212 ) group by geneid, genegroup; -- Elapsed: 00:00:16.93 select geneid ,genegroup, count(descriptortermid) from gene2disease2H where geneid in (165383,165409,165603,166524,149490,166141,166342,166829,166832,167771,16849 7,163450,163817, 165751,167437,167438,167476,164118,166054,168728,162967,164533,164545,163212 , 168501) group by geneid, genegroup; -- Elapsed: 00:00:31.97 select geneid ,genegroup, count(descriptortermid) from gene2disease2H where geneid in (165383,165409,165603,166524,149490,166141,166342,166829,166832,167771,16849 7,163450,163817, 165751,167437,167438,167476,164118,166054,168728,162967,164533,164545,163212 , 168501,151968) group by geneid, genegroup; -- Elapsed: 00:01:61.51 select geneid ,genegroup, count(descriptortermid) from gene2disease2H where geneid in (165383,165409,165603,166524,149490,166141,166342,166829,166832,167771,16849 7,163450,163817, 165751,167437,167438,167476,164118,166054,168728,162967,164533,164545,163212 , 168501,151968, 166472) group by geneid, genegroup; -- Elapsed: 00:02:124.63 select geneid ,genegroup, count(descriptortermid) from gene2disease2H where geneid in (165383,165409,165603,166524,149490,166141,166342,166829,166832,167771,16849 7,163450,163817, 165751,167437,167438,167476,164118,166054,168728,162967,164533,164545,163212 , 168501,151968, 166472,167771) group by geneid, genegroup; -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: gmei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
OT: Is there a simple perl utility or program similar to Oracle's dbms_profiler?
Hi: I have a perl program which calls lots of subroutines, and plus it calls sqls through DBI/DBD. I am wondering if there are some utilities or programs similar to Oracle's dbms_profiler that I could use to identify parts that are heavy-hitters? TIA. Guang -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: gmei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: would imp speed affect by setting indexes to NOLOGGING?
I have a question about index creation. When I run create index xyz ... tablespace indx_ts ... at what stage "sort_area_size" (and temp space if sort_area_size is not large enough) is used during the process? Is my understanding correct that the index will be created in "indx_ts" (not temp tablespace). As long as we have enough space in "indx_ts" we are fine. I thought sort_area_size and temp space is used for sorting only. Guang -- Original Message: In respone to someone's question about improving exp/imp speed I had posted the following quite some time ago. Most of it may help you. - Kirti 9) After the import is completed, set sort_area_size, sort_area_retained_size to a higher value (whatever is adequate and possible) to speed up index build process. Also, consider TEMPORARY type temp tablespace with properly configured initial and next (multiples of sort_area_size) extents. Make sure temp tablespace has ample room, should index build processes perform disk sorts. Also, make sure quota is okay on tablespaces for primary key constraint indexes. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: gmei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: would imp speed affect by setting indexes to NOLOGGING?
Everything in the schema on Server B is dropped before import, so I have an empty schema to begin with. I am not sure I understand what you meant by "dropping the indexes before import"? Did you mean export/import indexes separately? I guess I could do that. Right now I just try to keep the program simple because the time is not an issue. I just want to know if there is an "easy and simple" way to increase the imp speed without changing the program too much. Guang > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of > Ron Thomas > Sent: Tuesday, May 27, 2003 1:50 PM > To: Multiple recipients of list ORACLE-L > Subject: Re: would imp speed affect by setting indexes to NOLOGGING? > > > > Have you considered dropping the indexes before import, then > rebuilding parallel nologging? > > Ron Thomas > Hypercom, Inc > [EMAIL PROTECTED] > Each new user of a new system uncovers a new class of bugs. > -- Kernighan > > > > > > [EMAIL PROTECTED] > > > Sent by: To: > [EMAIL PROTECTED] > > [EMAIL PROTECTED] cc: > > >Subject: > would imp speed affect by setting indexes to NOLOGGING? > > > > > 05/27/2003 11:19 > > > AM > > > Please respond to > > > ORACLE-L > > > > > > > > > > > > > Hi, > > Would the imp speed increase by setting all indexes to NOLOGGING? > > I have a schema with 249 tables and 442 indexes and I have a > weekly schema > refresh program running as this: > > exp schema from Server A (Oracle 8173 on Sun Solaris box) > ftp dump file from Server A to Server B, the dump file size > is 1.9GB after > compression > imp schema on Server B (also Oracle 8173 on Sun Solaris box), the imp > usually take about 7 hours. > > With everything elase the same, last week I changed all indexes in the > schema on Server A to "NOLOGGING" (they were all set to > "logging" before), > hoping to increase the speed of imp on Server B. Because I thought the > "create index ... nologging" would reduce redo log writing, therefore > increase the imp speed quite a bit (there are some big > indexes there!). But > this morning I found the imp still took about the same time as before, > although I was told that the system I/O seems to be lower. > > Does anyone have similar experience? TIA. > > Guang > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: gmei > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > > > > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Ron Thomas > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: gmei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
would imp speed affect by setting indexes to NOLOGGING?
Hi, Would the imp speed increase by setting all indexes to NOLOGGING? I have a schema with 249 tables and 442 indexes and I have a weekly schema refresh program running as this: exp schema from Server A (Oracle 8173 on Sun Solaris box) ftp dump file from Server A to Server B, the dump file size is 1.9GB after compression imp schema on Server B (also Oracle 8173 on Sun Solaris box), the imp usually take about 7 hours. With everything elase the same, last week I changed all indexes in the schema on Server A to "NOLOGGING" (they were all set to "logging" before), hoping to increase the speed of imp on Server B. Because I thought the "create index ... nologging" would reduce redo log writing, therefore increase the imp speed quite a bit (there are some big indexes there!). But this morning I found the imp still took about the same time as before, although I was told that the system I/O seems to be lower. Does anyone have similar experience? TIA. Guang -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: gmei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: create interMedia index problem
6 150 128
PROTEOME 164,872 1,048,576 15.75219 25,600
RBS5,131,360 8,388,608 61.2 6 8002,048
RESCTX43,832 6,803,456 .66628 504
RESDATA 34,470,408 46,137,344 74.7 361 2319
RESINDEX 41,046,376 69,206,016 59.3 300 1063 512,000
SYSTEM74,320 153,600 48.4 401 1151,120
TEMP 10,224,960 10,240,000 99.9 1 20455,000
YPD 12,769,224 16,777,216 76.1 249 # 292,976
TABLESPACE_NAME USED-Kb ALLOC-KbUSED% SEGS >EXT >NEXTEXT
--- --- -- - -
YPDCUST0 1,048,576 .0 0 00
Then I ran
create index DRUGDATAINDEX_TEXT on DRUGDATA (TEXT)
indextype is ctxsys.context
parameters ('LEXER ctxsys.ISILEX WORDLIST ctxsys.ISIWORDLIST
STOPLIST ctxsys.ISISTOP storage isistore memory 50M');
I got
create index DRUGDATAINDEX_TEXT on DRUGDATA (TEXT)
*
ERROR at line 1:
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-2: interMedia Text error:
DRG-50857: oracle error in drixtab.create_index_tables
ORA-01658: unable to create INITIAL extent for segment in tablespace
RESINDEX
ORA-06512: at "CTXSYS.DRUE", line 126
ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 78
ORA-06512: at line 1
and I got from alert_log file:
ORA-1652: unable to extend temp segment by 128000 in tablespace
RESINDEX
I did coalesce on all tablespace and added another 2G datafile on RESINDEX
tablespace, I still got the same error. BTW, I could run the same sql on
two other DEV instances without any problem. The RESINDEX ts on them are
much more filled (like 90% full).
Any idea what might be the problem? TIA.
Guang
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: gmei
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
why FTS is chosen here?
Hi: We have an oracle 8173 running on Solaris 2.8. I am trying to understand why oracle chooses the Full Table scan in it's explain plan in the floowing query: SQL> SET AUTOTRACE TRACEONLY EXPLAIN; SQL> select TermID from Observationlist, TermObs where ID = ObsID; Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=9477 Card=267 By tes=44766729) 10 HASH JOIN (Cost=9477 Card=267 Bytes=44766729) 21 INDEX (FAST FULL SCAN) OF 'PK_OBSERVATION' (UNIQUE) (Cos t=834 Card=2513357 Bytes=15080142) 31 TABLE ACCESS (FULL) OF 'TERMOBS' (Cost=1126 Card=267 Bytes=28966707) SQL> SET AUTOTRACE off; SQL> desc Observationlist; Name Null?Type - IDNOT NULL NUMBER GENEID NUMBER CURATIONTYPE NUMBER PROTEOMEREFID NUMBER SOURCEID NUMBER SOURCETABLEVARCHAR2(25) DESTID NUMBER DESTTABLE VARCHAR2(25) DESTDATE DATE REFERENCETYPE VARCHAR2(1) EVIDENCECODE NUMBER CURATORID NUMBER EDITORID NUMBER UPDATESTAMPDATE CURATIONSTATUS VARCHAR2(1) ORIGINALSTAMP DATE NEXTOBSNUMBER TARGET VARCHAR2(15) REFTARGET VARCHAR2(15) TOOL VARCHAR2(25) OLDGENEID NUMBER SQL> desc TermObs; Name Null?Type - OBSID NUMBER TERMID NUMBER CONTEXTNUMBER SQL> select count(*) from Observationlist; COUNT(*) -- 2513357 SQL> select count(*) from TermObs; COUNT(*) -- 267 TermObs.OBSID has FK constaint pointing to Observationlist.ID. TermObs.OBSID is also indexed. Observationlist.ID is PK in the table. Both tables have been analyzed (using compute statistics). I would expect the query to use index on TermObs.OBSID. I tried to put hints in but they resulted in much higher "cost" than the one without. Any explaination why TermObs.OBSID's index is not used? TIA. Guang -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: gmei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Disable / enable constraints
SYS_ contraints are the ones that when you created them you did not give a name. Oracle will automatically assign a name like 'SYS_1234567'. You can drop tables in the schema before import. Import will automatically create tables if it does not exist. HTH. Guang > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of > Smith, Ron > L. > Sent: Tuesday, March 11, 2003 11:50 AM > To: Multiple recipients of list ORACLE-L > Subject: Disable / enable constraints > > > I need to truncate and import data into several schemas. The > tables have > lots of constraints. I can produce a script to disable and enable the > contriants but I would like to know more about the > constraint_type field in > dba_constraints and what are all the SYS_ contraints? Should > I disable all > constraints for a schema before the import or only certain types? > > Thanks! > Ron > If you are not the intended recipient of this e-mail message, any use, > distribution or copying of the message is prohibited. Please > let me know > immediately by return e-mail if you have received this > message by mistake, > then delete the e-mail message. Thank you. > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Smith, Ron L. > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: gmei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Tablespaces - datafiles
You can do 1. Create a new temporary tablespace (say temp1) 2. Assign all user's TEMPORARY_TABLESPACE to temp1 3. Drop the old temporary tablespace, remove it's OS files. 4. Re-create the temporary tablespace using new OS file names 5. Re-Assign all user's TEMPORARY_TABLESPACE to this new temporary tablespace 6. Drop temp1 and remove it's OS files HTH. Guang > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of LeRoy > Kemnitz > Sent: Tuesday, March 11, 2003 8:39 AM > To: Multiple recipients of list ORACLE-L > Subject: Tablespaces - datafiles > > > All - > > I am having a problem with the datafiles in a temporary tablespace. I > need to move and rename three different datafiles in the > tablespace. I > am able to take them offline - no problem. I cna make the changes at > the OS level. I am running on Unix. But I can't get the changes to > show up in the OEM inorder to bring them back on-line. > > Do I need to remove all users from this tablespace before making these > changes? The tablespace is temporary so does that make a difference? > Any suggestions? > > LeRoy > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: LeRoy Kemnitz > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: gmei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Tablespaces - datafiles
Tom: You are absolutely right. What I do is to have a nightly script to check the available disk space (using "df -k" and awk) and total sum of dba_temp_files to make sure there always enough disk space for the LMT temp tablespace. Guang > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of > Mercadante, > Thomas F > Sent: Tuesday, March 11, 2003 9:19 AM > To: Multiple recipients of list ORACLE-L > Subject: RE: Tablespaces - datafiles > . > Now here is the kicker. Let's say you have a disk that is 9 > gig is size. > You can create 10-1 Gig Temp data files on that disk. Since > Oracle does not > create the files full-sized, there is nothing to stop this > from happening. > Sometime later, as the TEMP tablespace gets used, the files grow until > eventually the disk fills up, and a sql query crashes with an > obscure disk > io error. Oracle is trying to expand the TEMP datafiles to > the size it's > been told they should be. But there is no physical space > left on disk. > > Documentation in 817 does not mention this. But 92 doc's are > up to date. > > nice surprise, eh? > > Tom Mercadante > Oracle Certified Professional > > > -Original Message- > Sent: Tuesday, March 11, 2003 8:39 AM > To: Multiple recipients of list ORACLE-L > > > All - > > I am having a problem with the datafiles in a temporary tablespace. I > need to move and rename three different datafiles in the > tablespace. I > am able to take them offline - no problem. I cna make the changes at > the OS level. I am running on Unix. But I can't get the changes to > show up in the OEM inorder to bring them back on-line. > > Do I need to remove all users from this tablespace before making these > changes? The tablespace is temporary so does that make a difference? > Any suggestions? > > LeRoy > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: LeRoy Kemnitz > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Mercadante, Thomas F > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: gmei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
drop table vs truncate table -- performance
Hi: Oracle 8173 on Sun Solaris 2.8. I am running a program that creates and drops tables repeatedly in a schema. The table dropping part looks like this: drop table ypd.t1 cascade constraints; drop table ypd.t2 cascade constraints; drop table ypd.t3 cascade constraints; drop table ypd.t4 cascade constraints; I thought I might be able to use "truncate table ..." and then "drop table ..." to make this process a bit faster. However the tests I did today showed otherwise: SQL> select bytes from dba_segments where segment_name='SEQUENCE'; BYTES -- 1949810688 SQL> create table ypd_owner.SEQUENCE1 2 tablespace ypd nologging as select * from mt.SEQUENCE; Table created. Elapsed: 00:04:272.50 SQL> drop table ypd_owner.SEQUENCE1; Table dropped. Elapsed: 00:00:05.19 SQL> create table ypd_owner.SEQUENCE1 2 tablespace ypd nologging as select * from mt.SEQUENCE; Table created. Elapsed: 00:04:275.85 SQL> truncate table ypd_owner.SEQUENCE1; Table truncated. Elapsed: 00:00:06.99 SQL> drop table ypd_owner.SEQUENCE1; Table dropped. Elapsed: 00:00:00.70 --- Tablspace ypd is LMT. Similar test on smaller table showed the same result "drop table ..." is faster than "truncate table ...". Any explanation? TIA. Guang -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: gmei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: perl DBI question: fetchrow_array
I tried "fetch" (which is same as fetchrow_arrayref):
$dat->bind_columns(undef,\($row));
while($dat->fetch) {
print DATA "$row\n";
}
And it seems the performance improved a bit (about 5%). Using RowCacheSize
or not does not seem to matter. The size of the table can vary from 1 row to
1 million rows. So I won't use fetchall_arrayref.
Thanks for your help.
Guang
> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of
> [EMAIL PROTECTED]
> Sent: Thursday, March 06, 2003 5:54 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: perl DBI question: fetchrow_array
>
>
> 1) fetchrow_arrayref is faster than fetchrow_array, as Alex has noted.
>
> 2) I see you've already set RowCacheSize. Anecdotal evidence
> ( not just
> mine)
> suggests that the diminished returns obtained by setting this
> >100 aren't
> worth it.
>
> 3) try selectall_arrayref if you're data is not really
> large. 'really
> large' depends
> on your environment.
>
> 4) join DBI users list, found at lists.perl.org.
>
> Jared
>
>
>
>
>
>
> Alex <[EMAIL PROTECTED]>
> Sent by: [EMAIL PROTECTED]
> 03/06/2003 01:34 PM
> Please respond to ORACLE-L
>
>
> To: Multiple recipients of list ORACLE-L
> <[EMAIL PROTECTED]>
> cc:
> Subject:Re: perl DBI question: fetchrow_array
>
>
> try using fetchrow_arrayref and see if its faster or less resource
> intensive.
>
>
>
> On Thu, 6 Mar 2003, gmei wrote:
>
> > I have some perl code which selects table data and write it
> into a file.
> I
> > have something like:
> >
> > ---
> > $dbh->{RowCacheSize} = 1;
> > open(DATA, ">$tn") || die "Can't open file\n";
> > $dat=$dbh->prepare("select
> id||chr(9)||FUNCTIONID||chr(9)||GENEID from
> > FUNCTION2GENE");
> > $dat->execute();
> > while(($row) = $dat->fetchrow_array) {
> > print DATA "$row\n";
> > }
> > close(DATA);
> > -
> >
> > I am trying to see if there is any way to speed up the process.
> >
> > So here is my question:
> >
> > Is "fetchrow_array" the fatest way to get the data?
> >
> > TIA.
> >
> > Guang
> >
> >
> >
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > --
> > Author: gmei
> > INET: [EMAIL PROTECTED]
> >
> > Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> > San Diego, California-- Mailing list and web
> hosting services
> >
> -
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from). You may
> > also send the HELP command for other information (like subscribing).
> >
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Alex
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
>
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author:
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: gmei
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
perl DBI question: fetchrow_array
I have some perl code which selects table data and write it into a file. I
have something like:
---
$dbh->{RowCacheSize} = 1;
open(DATA, ">$tn") || die "Can't open file\n";
$dat=$dbh->prepare("select id||chr(9)||FUNCTIONID||chr(9)||GENEID from
FUNCTION2GENE");
$dat->execute();
while(($row) = $dat->fetchrow_array) {
print DATA "$row\n";
}
close(DATA);
-
I am trying to see if there is any way to speed up the process.
So here is my question:
Is "fetchrow_array" the fatest way to get the data?
TIA.
Guang
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: gmei
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Invalid column in table - how to access it?
Use: Select "name" from messages;
See this example:
SQL> create table t1 ("col1" number);
Table created.
SQL> desc t1;
Name Null?Type
-
col1 NUMBER
SQL> insert into t1 (col1) values (1);
insert into t1 (col1) values (1)
*
ERROR at line 1:
ORA-00904: invalid column name
SQL> insert into t1 ("col1") values (1);
1 row created.
SQL> select col1 from t1;
select col1 from t1
*
ERROR at line 1:
ORA-00904: invalid column name
SQL> select "col1" from t1;
col1
--
1
Guang
> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of
> [EMAIL PROTECTED]
> Sent: Friday, February 28, 2003 1:40 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Invalid column in table - how to access it?
>
>
> I have a table - messages. I couldn't access one column in this table
> when I do
>
> Select name from messages;
>
> I get an error message - ORA-00904: invalid column name
>
> All the other columns I could select from this table. When I checked
> the table structure through OEM, I found this column has double quotes
> around it i.e., "NAME". All other columns do not have double quotes
> around their name.
>
> Question:
>
> How do I select this column from select statement?
>
> How do I correct this problem?
>
> Thanks for the help in advance.
>
> Rao
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: <[EMAIL PROTECTED]
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: gmei
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Purely for your amusement
Title: RE: Purely for your amusement I think the reason that Microsoft does this is to keep the control of OS market in China. MS has a huge market share in China, almost exclusively. I read a report a while ago saying some local goverment (Beijing Minucipal?) is concerned about OS security (who knows if MS's OS does not send something about the machine back to US?), which is understandable. So they are trying to use Linux as an alternative. MS must have sensed this "threat", that's why they signed agreement with Chinese Ministry of Education not long ago to provide hardware and software to set up 30 centers to train university students, and now to privide code to the governemnt for review. It is just a business decision. I expect we will see more and more governments request MS to do so. Guang Mei -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]On Behalf Of Nick WagnerSent: Friday, February 28, 2003 12:49 PMTo: Multiple recipients of list ORACLE-LSubject: RE: Purely for your amusement hmmm... ok, let's give one of the largest known hacking/cracking/virii producing countries in the world, the source code to the most common desktop in the world for 'security' purposes? Bill has got be to smokin' something... Any bets on how long it will take the source code to make it on the net? -Original Message- From: Boivin, Patrice J [mailto:[EMAIL PROTECTED]] Sent: Friday, February 28, 2003 9:19 AM To: Multiple recipients of list ORACLE-L Subject: RE: Purely for your amusement Excerpt from http://www.siliconvalley.com/mld/siliconvalley/business/columnists/gmsv/5286 438.htm Microsoft committed to providing Chinese government with information necessary to buy Microsoft products: The Chinese government will soon be able to view the source code for the Windows operating system, now that it has agreed to participate in Microsoft's Government Security Program (GSP) -- a program designed to assuage concerns about the security of the OS by allowing governments to review its underlying source code. "We are committed to providing the Chinese government with information that will help them deploy and maintain secure computing infrastructures," said Microsoft Chairman Bill Gates who was in Beijing today to announce the pact. "We see this agreement as a significant step forward in Microsoft's relations with the Chinese government." Gates can only hope. After all, the Chinese government has long been quite vocal about its preference for Linux, even going so far as to develop its own version of the operating system, dubbed Red Flag Linux, that it says will eventually replace Windows and Unix on all of its government PCs and servers. Pat. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Boivin, Patrice J INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Re: DBMS_STATS
We run DBMS_STATS.GATHER_TABLE_STATS nightly as cron job using this script
to spool the result to a temp file, then use sqlplus to run that file.
select 'execute DBMS_STATS.GATHER_TABLE_STATS('''||owner||''','''||
segment_name||''',cascade => TRUE);'
from ( select owner,segment_name, sum(bytes) from dba_segments s
where segment_type like 'TABLE%' and
owner in ('XYZ') and
s.partition_name is null
group by owner,segment_name
order by sum(s.bytes) desc);
We use multi-thread (3 threads) to run this script and it cuts the whole
time by half. Now it takes about 1.5 hours (our data set is small). For
another larger schema, we do this once a month.
Guang Mei
> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of chao_ping
> Sent: Friday, February 28, 2003 3:49 AM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: Re: DBMS_STATS
>
>
> Hi, friends:
> How do you use dbms_stats.gather_schema_stats in OLTP
> production system?
> I ever used estimate statistics =20% percent, and some
> time have serious performance impact while two big table join
> in my production changed. Later I changed to compute and till
> now , It is ok.
> And another question about CBO stability:
> Do you always analyze your database weekly(or with less
> data changed, monthly)? Some times because of something
> wrong, the db is performance poor. While development team
> will always say: it must be your CBO that is misfunction,
> repair it! Faint, I think CBO is ok in most case with compute
> statistics, but that kind of question is really headache,
> right? How do you friends answer that kind of questions?
> Sometimes I even want to stop analyze the db weekly.As far as
> I know, some site like Ebay do not analye database.Can it
> because they use rbo?
> And to jeff, for DW application, for indexed columns
> size 1 is good because it gather histrogram data, but for
> OLTP system, do you think it is necessary? Friends please
> share your opinions? Do you use
> dbms_stats.gather_table_stats, or dbms_stats.gather_schema_stats?
>
> My scripts:
> >echo begin to analyze user bidder at `date +%x%T`
> >>/export/home/oracle/log/analyze.log
> >sqlplus /nolog < >connect / as sysdba
> >alter session set sort_area_size=1;
> >alter session set sort_multiblock_read_count =128;
> >execute
> dbms_stats.gather_schema_stats(ownname=>'bidder',estimate_perc
> ent=>99,degree=>8,cascade=>true,method_opt=>'for all columns');
> >quit
> >EOF
> >echo finished analyze user bidder at `date +%x%T`
> >>/export/home/oracle/log/analyze.log
>
> Thanks.
>
>
>
>
> Regards
> zhu chao
> msn:[EMAIL PROTECTED]
> www.cnoug.org(China Oracle User Group)
>
> === 2003-02-27 08:09:00 ,you wrote£º===
>
> >Terrian, Tom (Contractor) (DAASC) wrote:
> >
> >> I have never had good luck with DBMS_STATS. It seems that the old
> >> analyze runs much faster.Runs in 45 seconds:analyze table
> >> log_trans partition (log_trans_20030104) estimate
> statistics sample 5
> >> percent; Takes over 2 hours:execute
> >> dbms_stats.gather_table_stats(ownname => 'LDGADMIN', -
> >> tabname => 'LOG_TRANS', -
> >> partname =>
> >> 'LOG_TRANS_20030102', -
> >> estimate_percent => 5);
> >> Am I missing something? Aren't both commands the same?Thanks,Tom
> >
> > Hello Tom
> >
> >We too experienced terrible performance upon first using dbms_stats.
> >There
> >are two things we did that brought the performance in line with the
> >analyze.
> >1. We set method_opt = 'FOR ALL INDEXED COLUMNS SIZE 1'
> >2. We set estimate_percent = 15
> >
> >Hope this helps.
> >
> >BTW:
> >Sun/Solaris 2.6 & 2.8
> >Running 8i, 9i, 9.2
> >5 terabyte db's
> >
> >Jeff
>
> = = = = = = = = = = = = = = = = = = = =
>
>
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: chao_ping
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAI
RE: select count(case ...) slow in PL/SQL, any better way?
Hi:
Just after I sent my original message, it occured to me that I could use
this to "optimize" the sql (gneid is the PK column of the table):
select count(A.geneid), count(B.geneid) into count1, count2
from isi.nametag A , isi.nametag B where A.geneid=geneid1 and
B.geneid=geneid2;
Guang
> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of gmei
> Sent: Monday, February 24, 2003 4:07 PM
> To: Multiple recipients of list ORACLE-L
> Subject: select count(case ...) slow in PL/SQL, any better way?
>
>
> Hi:
>
> Oracle 8173 on Sun Solaris 2.8. I am trying to "optimize" the
> follwoing code
> (in an PL/SQL package) into one table call (instead of two)
>
> select count(1) into count1 from isi.nametag where geneid=geneid1;
> select count(1) into count2 from isi.nametag where geneid=geneid2;
>
> The following code works in sqlplus, but not in PL/SQL:
>
> select count(case when geneid=geneid1 then 1 else null end )
> into count1,
>count(case when geneid=geneid2 then 1 else null
> end ) into count2
> from isi.nametag ;
>
> I have to use dynamic sql to get around this problem. But
> it's perofrmance
> is horrible.
>
> SQL> set serveroutput on
> SQL> declare
> 2i number:=0;
> 3str varchar2(200);
> 4 count1 number;
> 5count2 number;
> 6 begin
> 7str := 'select count(case when geneid=:x1 then 1 else
> null end ) ,
> 8 count(case when geneid=:x2 then 1 else null end )
> 9from isi.nametag';
> 10for x1 in 1 .. 10 Loop
> 11 for x2 in 20 .. 30 Loop
> 12 i := i +1;
> 13 EXECUTE IMMEDIATE str INTO count1, count2 USING x1, x2;
> 14 end loop;
> 15end loop;
> 16dbms_output.put_line('i =' || i);
> 17 end;
> 18 /
> i =110
>
> PL/SQL procedure successfully completed.
>
> Elapsed: 00:00:10.96
>
> SQL> declare
> 2 i number:=0;
> 3 count1 number;
> 4count2 number;
> 5 begin
> 6for x1 in 1 .. 100 Loop
> 7 for x2 in 200 .. 300 Loop
> 8i := i +1;
> 9 select count(1) into count1 from isi.nametag where
> geneid=x1;
> 10 select count(1) into count2 from isi.nametag where
> geneid=x2;
> 11 end loop;
> 12end loop;
> 13dbms_output.put_line('i =' || i);
> 14 end;
> 15 /
> i =10100
>
> PL/SQL procedure successfully completed.
>
> Elapsed: 00:00:04.06
>
> Is there a better way to optimize the orginal code? TIA.
>
> Guang Mei
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: gmei
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: gmei
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
select count(case ...) slow in PL/SQL, any better way?
Hi:
Oracle 8173 on Sun Solaris 2.8. I am trying to "optimize" the follwoing code
(in an PL/SQL package) into one table call (instead of two)
select count(1) into count1 from isi.nametag where geneid=geneid1;
select count(1) into count2 from isi.nametag where geneid=geneid2;
The following code works in sqlplus, but not in PL/SQL:
select count(case when geneid=geneid1 then 1 else null end ) into count1,
count(case when geneid=geneid2 then 1 else null end ) into count2
from isi.nametag ;
I have to use dynamic sql to get around this problem. But it's perofrmance
is horrible.
SQL> set serveroutput on
SQL> declare
2i number:=0;
3str varchar2(200);
4 count1 number;
5count2 number;
6 begin
7str := 'select count(case when geneid=:x1 then 1 else null end ) ,
8 count(case when geneid=:x2 then 1 else null end )
9from isi.nametag';
10for x1 in 1 .. 10 Loop
11 for x2 in 20 .. 30 Loop
12 i := i +1;
13 EXECUTE IMMEDIATE str INTO count1, count2 USING x1, x2;
14 end loop;
15end loop;
16dbms_output.put_line('i =' || i);
17 end;
18 /
i =110
PL/SQL procedure successfully completed.
Elapsed: 00:00:10.96
SQL> declare
2 i number:=0;
3 count1 number;
4count2 number;
5 begin
6for x1 in 1 .. 100 Loop
7 for x2 in 200 .. 300 Loop
8i := i +1;
9 select count(1) into count1 from isi.nametag where geneid=x1;
10 select count(1) into count2 from isi.nametag where geneid=x2;
11 end loop;
12end loop;
13dbms_output.put_line('i =' || i);
14 end;
15 /
i =10100
PL/SQL procedure successfully completed.
Elapsed: 00:00:04.06
Is there a better way to optimize the orginal code? TIA.
Guang Mei
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: gmei
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Question on "set autotrace on statistics"
Hi: I am trying to understand why sqlplus did not display stats when I run "analyze table ..." and "create index ..."? TIA. Guang -- SQL> set autotrace on statistics SQL> analyze table gene_upr compute statistics; Table analyzed. SQL> desc gene_upr; Name Null?Type - ID NUMBER NAME VARCHAR2(128) NAME_UPR VARCHAR2(128) SPECIESID NUMBER SQL> CREATE index gene_upr_id_index 2 ON gene_upr(id) 3 TABLESPACE INDEXES nologging 4 STORAGE (INITIAL 1500 NEXT 1500 pctincrease 0); Index created. SQL> select count(*) from plan_table; COUNT(*) -- 0 Statistics -- 0 recursive calls 4 db block gets 1 consistent gets 0 physical reads 0 redo size 187 bytes sent via SQL*Net to client 311 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: gmei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: why these tables can not be seen from user_segments?
No matter where I look, there is no segment correspnding to it. Anyway, here is the user_table info (not sure if it helps). SQL> select TABLE_NAME,IOT_TYPE 2 from user_tables where table_name='DR$ABSTRACT_TEXT$K'; TABLE_NAME IOT_TYPE -- DR$ABSTRACT_TEXT$K IOT SQL> select * from dba_segments where segment_name ='DR$ABSTRACT_TEXT$K'; no rows selected SQL> > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Jonathan > Lewis > Sent: Tuesday, February 04, 2003 12:35 PM > To: Multiple recipients of list ORACLE-L > Subject: Re: why these tables can not be seen from user_segments? > > > > I think you will find that these tables > are index-organized-tables. The table > definition exists, but in the absence of > an overflow, there is only an index > segment. > > > Regards > > Jonathan Lewis > http://www.jlcomp.demon.co.uk > > Coming soon a new one-day tutorial: > Cost Based Optimisation > (see http://www.jlcomp.demon.co.uk/tutorial.html ) > > UK___March 19th > USA_(FL)_May 2nd > > > Next Seminar dates: > (see http://www.jlcomp.demon.co.uk/seminar.html ) > > USA_(CA, TX)_August > > > The Co-operative Oracle Users' FAQ > http://www.jlcomp.demon.co.uk/faq/ind_faq.html > > > -Original Message- > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > Date: 04 February 2003 16:17 > > > >Hi: > > > >Oracle 8173 on Sun Box. > > > > > >SQL> select table_name from user_tables > > 2 where table_name like 'DR$%' > > 3 order by table_name; > > > >TABLE_NAME > >-- > >DR$ABSTRACT_TEXT$I > >DR$ABSTRACT_TEXT$K > >DR$ABSTRACT_TEXT$N > >DR$ABSTRACT_TEXT$R > >DR$COREREF_ABSTRACT$I > >DR$COREREF_ABSTRACT$K > >DR$COREREF_ABSTRACT$N > >DR$COREREF_ABSTRACT$R > >DR$COREREF_TITLE$I > >DR$COREREF_TITLE$K > >DR$COREREF_TITLE$N > > > >TABLE_NAME > >-- > >DR$COREREF_TITLE$R > >DR$TEST_CONTEXT$I > >DR$TEST_CONTEXT$K > >DR$TEST_CONTEXT$N > >DR$TEST_CONTEXT$R > > > >16 rows selected. > > > >Why tables like DR$ABSTRACT_TEXT$K, DR$ABSTRACT_TEXT$N do not have > their > >corresponding segments? What are they and their functions? > > > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Jonathan Lewis > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > ----- > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: gmei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: why these tables can not be seen from user_segments?
They don't seem to be views, they are tables SQL> select view_name from user_views where view_name like 'DR$%'; no rows selected SQL> -Original Message- Sent: Tuesday, February 04, 2003 11:53 AM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Hi, I found this out the hard way to: dba_tables, all_tables, and user_tables also include views. Of course, views don't have segments. Jerry Whittle ASIFICS DBA NCI Information Systems Inc. [EMAIL PROTECTED] 618-622-4145 -Original Message- Hi: Oracle 8173 on Sun Box. SQL> select count(*) from user_tables; COUNT(*) -- 326 SQL> select count(*) from user_segments where segment_type='TABLE'; COUNT(*) -- 318 <<< stuff cut >>> <>
why these tables can not be seen from user_segments?
Hi: Oracle 8173 on Sun Box. SQL> select count(*) from user_tables; COUNT(*) -- 326 SQL> select count(*) from user_segments where segment_type='TABLE'; COUNT(*) -- 318 SQL> select segment_name from user_segments 2 where segment_type='TABLE' 3 and segment_name like 'DR$%' 4 order by segment_name; SEGMENT_NAME DR$ABSTRACT_TEXT$I DR$ABSTRACT_TEXT$R DR$COREREF_ABSTRACT$I DR$COREREF_ABSTRACT$R DR$COREREF_TITLE$I DR$COREREF_TITLE$R DR$TEST_CONTEXT$I DR$TEST_CONTEXT$R 8 rows selected. SQL> select table_name from user_tables 2 where table_name like 'DR$%' 3 order by table_name; TABLE_NAME -- DR$ABSTRACT_TEXT$I DR$ABSTRACT_TEXT$K DR$ABSTRACT_TEXT$N DR$ABSTRACT_TEXT$R DR$COREREF_ABSTRACT$I DR$COREREF_ABSTRACT$K DR$COREREF_ABSTRACT$N DR$COREREF_ABSTRACT$R DR$COREREF_TITLE$I DR$COREREF_TITLE$K DR$COREREF_TITLE$N TABLE_NAME -- DR$COREREF_TITLE$R DR$TEST_CONTEXT$I DR$TEST_CONTEXT$K DR$TEST_CONTEXT$N DR$TEST_CONTEXT$R 16 rows selected. Why tables like DR$ABSTRACT_TEXT$K, DR$ABSTRACT_TEXT$N do not have their corresponding segments? What are they and their functions? TIA. Guang Mei -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: gmei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
FW: OT: unix script quetion: to replace $ with \$
SQL> show user;
USER is "ISI"
SQL> select segment_name from user_segments
2 where segment_type='TABLE'
3 and segment_name like 'DR$%'
4 order by segment_name;
SEGMENT_NAME
DR$ABSTRACT_TEXT$I
DR$ABSTRACT_TEXT$R
DR$COREREF_ABSTRACT$I
DR$COREREF_ABSTRACT$R
DR$COREREF_TITLE$I
DR$COREREF_TITLE$R
DR$TEST_CONTEXT$I
DR$TEST_CONTEXT$R
8 rows selected.
-- login to essex (a unix box):
login: oracle
Password:
Last login: Mon Feb 3 21:25:31 from rex.xyz.com
Sun Microsystems Inc. SunOS 5.8 Generic Patch October 2001
essex$ echo "exec analyze table ISI.DR$TEST_CONTEXT$R compute statistics;" |
sed 's/\$/\\\$/g'
exec analyze table ISI.DR compute statistics;
essex$
It will try to analyze table "DR", which does not exist.
Guang
-Original Message-
Sent: Tuesday, February 04, 2003 9:20 AM
To: Multiple recipients of list ORACLE-L
Nope. I will work. Try it.
> -Original Message-
>
> The problem is that if LINE contains "$" (dollar sign), as
> some InterMedia
> table names do, the piped command will fail.
>
> Guang
>
> > -Original Message-
> > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of
> > Stephen Lee
> > Sent: Monday, February 03, 2003 3:54 PM
> > To: Multiple recipients of list ORACLE-L
> > Subject: RE: OT: unix script quetion: to replace $ with \$
> >
> >
> >
> > Would something like this be adaptable to your stuff?
> >
> > #!/usr/bin/ksh
> >
> > {
> > sqlplus -s <<-XXX
> > joe/blow@SID
> > set this and that off
> > set the other thing and trimspool on
> > set whatever else
> > select table_name from dba_tables where owner = 'BUBBA';
> > } | while read LINE; do
> > echo "exec analyze_that_puppy BUBBA.$LINE real good;" | sed
> > 's/\$/\\\$/g'
> > done
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > --
> > Author: Stephen Lee
> > INET: [EMAIL PROTECTED]
> >
> > Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> > San Diego, California-- Mailing list and web
> hosting services
> >
> -
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from). You may
> > also send the HELP command for other information (like subscribing).
> >
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: gmei
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Stephen Lee
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-----
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: gmei
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: OT: unix script quetion: to replace $ with \$
The problem is that if LINE contains "$" (dollar sign), as some InterMedia
table names do, the piped command will fail.
Guang
> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of
> Stephen Lee
> Sent: Monday, February 03, 2003 3:54 PM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: OT: unix script quetion: to replace $ with \$
>
>
>
> Would something like this be adaptable to your stuff?
>
> #!/usr/bin/ksh
>
> {
> sqlplus -s <<-XXX
> joe/blow@SID
> set this and that off
> set the other thing and trimspool on
> set whatever else
> select table_name from dba_tables where owner = 'BUBBA';
> } | while read LINE; do
> echo "exec analyze_that_puppy BUBBA.$LINE real good;" | sed
> 's/\$/\\\$/g'
> done
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Stephen Lee
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: gmei
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
