Buf Hit Ratio
I am running the following (Oracle suggested - 8i) query to get the biffer hit ratio. This is our production Apps database. The numbers for logical and physical reads seem VERY high (especially phys_reads, which obviously causes our hit ratio to drop to 0). What could have caused this? SQL> list 1 select A.value + B.value "logical_reads", 2 C.value "phys_reads", 3 D.value "phy_writes", 4 (A.value+B.value)-C.value "log_minus_phys", 5 round(100 * ((A.value+B.value)-C.value) / (A.value+B.value)) 6 "Buffer Hit Ratio" 7 from V$SYSSTAT A, V$SYSSTAT B, V$SYSSTAT C, V$SYSSTAT D 8 where A.statistic# = 38 9 AND B.statistic# = 39 10 AND C.statistic# = 40 11* AND D.statistic# = 44 SQL> / logical_reads phys_reads phy_writes log_minus_phys Buffer Hit Ratio 18,446,744,070,414,253,130 18,446,744,069,433,707,5592,043,488 980,545,5710 Here are some other stats; DTSTAMPPHYSICAL_READSDB_BLOCK_GETS CONSISTENT_GETS PHYSICAL_WRITES --- PHYSICAL_READS_DIRECT PHYSICAL_WRITES_DIRECT SESSION_LOGICAL_READS -- - 08-APR-2002 10:35:16 18,446,744,069,434,437,169 171,781,916 18,446,744,070,280,471,598 2,103,859 18,446,744,047,946,114,866966,679 1,032,014,671 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Glenn Travis INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Pl/SQL code help
Roland, I think you have a quote in the wrong place: If A'||AvdNr||'.ICA_ARTIKEL@'||LookUpServerName|| 'BORTTAGS_FLAGG = 0 '' || Should probably read: If 'A'||AvdNr||'.ICA_ARTIKEL@'||LookUpServerName|| 'BORTTAGS_FLAGG = 0 ' || > Cheers, > > John Thomas -Original Message- Sent: 08 April 2002 13:43 To: Multiple recipients of list ORACLE-L Hallo, I have some trouble with this pl/sql procedure. I would like that this lvsql to be run only if the field Borttags_flagg = 0 but i get an error in the if statement, whatis wrong with this? It get the erromressage: LS-00103: Encountered the symbol "||AvdNr||" when expecting one of the following: . ( * @ % & = - + < / > at in mod not rem then <> or != or ~= >= <= <> and or like betwe.. when I compile the whole procedure. Please help me with this. (If the borttags_flagg = 1 then it will continue the loop and check for next one. etc... If A'||AvdNr||'.ICA_ARTIKEL@'||LookUpServerName|| 'BORTTAGS_FLAGG = 0 '' || then (this lvsql is to be run only if field borttags_flagg = 0) lvSQL := 'SELECT ICA_ARTIKEL.FSGPRIS,ICA_ARTIKEL.BORTTAGS_FLAGG,ICA_ARTIKEL.PANTBELOPP ' || --PBK.LPKORGEANREL.EANREL,PBK.LPKORGEANREL.VARUTYP ' || 'FROM A'||AvdNr||'.ICA_ARTIKEL@'||LookUpServerName|| ' ' || --PBK.LPKORGEANREL ' || 'WHERE ICA_ARTIKEL.EAN=' || EanLPVara || ' ' || 'AND ICA_ARTIKEL.DATUMhttp://www.orafaq.com -- Author: Thomas, John INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 Objects in the System Tablespace.
Jay, I always set up my production databases having SYSTEM, SYS, and DBSNMP with default tblsp of SYSTEM; SYS gets temp tblsp of TEMP. Users like ORACLE, OUTLN, TRACESVR, ORDSYS, etc. get TOOLS tblsp for default (and TEMP for temp tblsp). I have never had any problems doing it this way. Thank you, Paul Sherman DBA voice - 781-501-4143 (office) fax- 781-278-8341 (office) email - [EMAIL PROTECTED] -Original Message- Sent: Monday, April 08, 2002 10:09 AM To: Multiple recipients of list ORACLE-L I am trying to determine what Oracle "officially" considers foreign objects in the SYSTEM tablespace. If you check out Note 122669.1, section 7.1, Oracle recommends a query to find foreign objects in your system tablespace. This query will report such users as: AURORA$JIS$UTILITY$ CTXSYS MDSYS ORDSYS OSE$HTTP$ADMIN OUTLN It is part of our normal procedures to setup a TOOLS tablespace, and set this as the default tablespace for the user SYSTEM. Objects such as SQLPLUS_PRODUCT_PROFILE will be created in this tablespace. I've browsed around MetaLink and posted in one of the forums, but I'm not really getting any concrete answers as to which users should be permitted to have objects in the SYSTEM tablespace. I do know that it is OK to move OUTLN to another tablespace. Comments would be appreciated. Thanks, Jay -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jay Hostetter INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Sherman, Paul R. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 Replication - is it "on" by default?
Paul - The only book I've found specific to Oracle replication is: Oracle Distributed Systems by Charles Dye. It is pretty good. Replication isn't the easiest thing to learn. The fundamental questions to ask are: is your application designed to work with replication? Are you just trying to replicate for reporting purposes? Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, April 08, 2002 8:13 AM To: Multiple recipients of list ORACLE-L Hi folks, we've never identified any requirement here for using any kind of replication. Consequently I know nothing whatsoever about Oracle Replication. Now I've been asked whether or not we use Oracle Replication and, if so, whether it can be disabled. So can anyone tell me whether Replication is a feature automatically included in Oracle 8i Enterprise Edition? And is there an easy way of telling whether or not it's "on"? If it's "on", can it be turned "off" (if that's a meaningful question!), and if so, how? I've tried briefly RTFMing, but although the manuals contain a wealth of info about how to use Replication, I can see nothing about how to tell whether it's active or not, and how to switch it on or off. Any pointers, please, anyone? Paul Vincent -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Paul Vincent INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Another RMAN Problem ---> Urgent !!
Jay and Tom, Thanks a million for your replies. I have now ran the script by creating it and then executing it from the RMAN prompt. It worked but I got the following error : rman target / catalog rman_sid1/rman_sid1@rman817 auxiliary / Recovery Manager: Release 8.1.7.2.0 - Production RMAN-06005: connected to target database: SID1 (DBID=647056675) RMAN-06008: connected to recovery catalog database RMAN-06020: connected to auxiliary database RMAN> run {execute script dup_db;} RMAN-03021: executing script: dup_db RMAN-03022: compiling command: set RMAN-03022: compiling command: allocate RMAN-03023: executing command: allocate RMAN-08030: allocated channel: dupdb_d1 RMAN-08500: channel dupdb_d1: sid=43 devtype=SBT_TAPE RMAN-08526: channel dupdb_d1: VERITAS NetBackup for Oracle8 - Release 3.4GA (030800) RMAN-03022: compiling command: set limit RMAN-03023: executing command: set limit RMAN-03022: compiling command: set RMAN-03022: compiling command: set RMAN-03022: compiling command: set RMAN-03022: compiling command: set RMAN-03022: compiling command: set RMAN-03022: compiling command: Duplicate Db RMAN-03026: error recovery releasing channel resources RMAN-08031: released channel: dupdb_d1 RMAN-00571: === RMAN-00569: === ERROR MESSAGE STACK FOLLOWS === RMAN-00571: === RMAN-03015: error occurred in stored script dup_db RMAN-03002: failure during compilation of command RMAN-03013: command type: Duplicate Db RMAN-05500: the auxiliary database must be not mounted when issuing a DUPLICATE command Can you tell me if there is anything wrong with my Connect String ?? If so, how do I specify the connect string correctly ?? The auxiliary is DEFINITELY not mounted here since I have started it in NOMOUNT mode. The target db is the one I am trying to duplicate and the auxiliary db is the duplicate db I am attempting to create. The target db is obviously open. I am not using a password file since I am working directly on the server. Regards, Samir Samir Sarkar Oracle DBA SchlumbergerSema Email : [EMAIL PROTECTED] [EMAIL PROTECTED] Phone : +44 (0) 115 - 957 6028 EPABX : +44 (0) 115 - 957 6418 Ext. 76028 Fax : +44 (0) 115 - 957 6018 -Original Message- Sent: 08 April 2002 14:06 To: [EMAIL PROTECTED]; SARKAR, Samir Your input to RMAN should be a script or "here" list. Scrap the shell script, put your "run" script into a file, run RMAN from the command line and call the rman "run" script that you just created.. Also, set NLS_DATE_FORMAT and NLS_LANG. Depending on your database version the "to_date" function will not work as you have it in "set until time". Jay >>> [EMAIL PROTECTED] 04/08/02 09:43AM >>> Dear All, I am totally out of my depth here and this is a production db which needs to be restored urgently. I shall explain my scenario first. Our application team had lost some data after last Wednesday night's backup. They have a tool to have a dump of the data from the database from which they can extract the data if needed. This dump was taken on Thursday morning. Due to some ***hole they lost some more data on Thursday during the day. They recovered this data from the database dump which they had taken on Thursday morning. They now want the data which was lost on Wednesday. Since there was no database dump available prior to this, I suggested that we can create a duplicate database upto a point of time (as on Wednesday night) to a different location on the server. They could then take a database dump with their tool and extract the lost records. This is my script which I compiled with help from Metalink and an old post from John Hallas (the backup of our target database is taken on tape with Veritas Netbackup) : #!/bin/ksh rman target / catalog rman_sid1/rman_sid1@rman817 auxiliary / run { set until time "to_date('20-MAR-2002 20:00:00','DD-MON- HH24:MI:SS)"; allocate channel c_dlt1 type 'SBT_TAPE'; allocate auxiliary channel dupdb_d1 type disk; setlimit channel c_dlt1 kbytes 67108864 maxopenfiles 128 readrate 256; setlimit channel dupdb_d1 kbytes 67108864 maxopenfiles 128 readrate 256; set newname for datafile '/disk01/oradata/sid1/data/system01sid1.dbf' to '/disk01/oradata/test/data/system01sid1.dbf'; set newname for datafile '/disk01/oradata/sid1/data/rbs01sid1.dbf' to '/disk01/oradata/test/data/rbs01sid1.dbf'; set newname for datafile '/disk01/oradata/sid1/data/temp01sid1.dbf' to '/disk01/oradata/test/data/temp01sid1.dbf'; set newname for datafile '/disk01/oradata/sid1/data/tools01sid1.dbf' to '/disk01/oradata/test/data/tools01sid1.dbf'; set newname for datafile '/disk01/oradata/sid1/data/users01sid1.dbf' to '/disk01/oradata/test/data/users01sid1.dbf'; duplicate target database to test logfile group 1 '/disk03/oradata/test/logs/log01a01sid1.dbf' size 180M, group 2 '/disk03/oradat
Re: OCP-Network Exam
1.You should study gui's. There are about 10 questions . 2.There are a few question about command line utilities and their parameters. 3.Connection Manager is important. thats all which I can remember - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Monday, April 08, 2002 5:58 PM > Hi, > > Greetings, > > I am on the preparation for my OCP 8i Network Administration exam. I > welcome any tips or questions or model papers. So that i can prepare myself > well for the exam. > > Looking forward to your inputs, > > With best regards, > > Muths > > > _ > Do You Yahoo!? > Get your free @yahoo.com address at http://mail.yahoo.com > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Muthaiah > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (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: Arslan Bahar INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
utl_file_dir question
I have couple question. We need to set up 15 trainer in the training room by tomorrow for a pl/sql class. I need to set up utl_file_dir for them. This is NT environment. question 1: If I create 15 users in oracle, do I need to ask Nt admin to create system users for them as well? 2: It should be 15 different directories for each of them. How to set it in int.ora? 3: Since the training room are remote access to server. The utl_file_dir will write to local or server? Those are sound pretty easy question. I never set this up before and they want it now. I will check the manual as well. Thanks in advance, Joan -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Joan Hsieh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
anyone know how to change tablespace names?
is there a back-end way to effect an "alter tablespace rename to ..." type of thing? yes, I know Oracle doesn't support mucking with the data dictionary, but . . . thx -bill Bill Magaliff Framework, Inc. 914-631-2322 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Another RMAN Problem ---> Urgent !!
I'm hoping that you have a "sev 1" TAR open on this, as well. Keep pestering them if you haven't had a response; keep the TAR status at "IMMEDIATE RESPONSE REQUIRED" (by responding multiple times) whenever you hit the ball back to them. Also, be sure that all relevant facts are recorded online through MetaLink, in case this later degenerates into a finger-pointing game. You cannot play that game with verbal records... --- You are connecting both TARGET and AUXILIARY to the same database instance (i.e. "/"). For a DUPLICATE DATABASE operation, you don't have to connect to the TARGET at all, if I recall correctly; a DUPLICATE DATABASE operation doesn't involve the TARGET. Sounds funny, until you consider that the operation is reading from tape to the new AUXILIARY database instance. For the AUXILIARY, be sure that you have already created another empty database instance (using most of the TARGET databases "init.ora" parms, with obvious exceptions like CONTROL_FILE) which is started up NOMOUNT before starting this DUPLICATE DATABASE operation. If what I suspect is correct, then I think you ought to be *glad* that RMAN just "hung" instead of following its directions! Of course, a relevant error message would a lot *nicer* than just "hanging" that way... - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Monday, April 08, 2002 7:43 AM > Dear All, > > I am totally out of my depth here and this is a production db which needs to > be restored urgently. > I shall explain my scenario first. Our application team had lost some data > after last Wednesday night's > backup. They have a tool to have a dump of the data from the database from > which they can extract the > data if needed. This dump was taken on Thursday morning. Due to some ***hole > they lost some more data > on Thursday during the day. They recovered this data from the database dump > which they had taken on > Thursday morning. > They now want the data which was lost on Wednesday. Since there was no > database dump available prior to > this, I suggested that we can create a duplicate database upto a point of > time (as on Wednesday night) to a > different location on the server. They could then take a database dump with > their tool and extract the lost records. > This is my script which I compiled with help from Metalink and an old > post from John Hallas (the backup of our target database is taken on tape > with Veritas Netbackup) : > > #!/bin/ksh > > rman target / catalog rman_sid1/rman_sid1@rman817 auxiliary / > > run { > > set until time "to_date('20-MAR-2002 20:00:00','DD-MON- HH24:MI:SS)"; > > allocate channel c_dlt1 type 'SBT_TAPE'; > > allocate auxiliary channel dupdb_d1 type disk; > > setlimit channel c_dlt1 kbytes 67108864 maxopenfiles 128 readrate 256; > setlimit channel dupdb_d1 kbytes 67108864 maxopenfiles 128 readrate 256; > > set newname for datafile >'/disk01/oradata/sid1/data/system01sid1.dbf' > to '/disk01/oradata/test/data/system01sid1.dbf'; > > set newname for datafile >'/disk01/oradata/sid1/data/rbs01sid1.dbf' > to '/disk01/oradata/test/data/rbs01sid1.dbf'; > > set newname for datafile >'/disk01/oradata/sid1/data/temp01sid1.dbf' > to '/disk01/oradata/test/data/temp01sid1.dbf'; > > set newname for datafile >'/disk01/oradata/sid1/data/tools01sid1.dbf' > to '/disk01/oradata/test/data/tools01sid1.dbf'; > > set newname for datafile >'/disk01/oradata/sid1/data/users01sid1.dbf' > to '/disk01/oradata/test/data/users01sid1.dbf'; > > duplicate target database to test > > logfile > group 1 '/disk03/oradata/test/logs/log01a01sid1.dbf' size 180M, > group 2 '/disk03/oradata/test/logs/log02a01sid1.dbf' size 180M, > group 3 '/disk03/oradata/test/logs/log03a01sid1.dbf' size 180M; > } > > What happens is that RMAN connects to the target, catalog and auxiliary > database and goes into the > RMAN prompt and just hangs thereafter some time, when I type 'exit' > there out of frustration, I > get the following errors : > > ./create_dupdb.sh[5]: run: not found > /create_dupdb.sh[9]: allocate: not found > /create_dupdb.sh[11]: allocate: not found > /create_dupdb.sh[13]: setlimit: not found > /create_dupdb.sh[14]: setlimit: not found > /create_dupdb.sh[17]: /disk01/oradata/sid1/data/system01sid1.dbf: cannot > execute > /create_dupdb.sh[18]: to: not found > /create_dupdb.sh[21]: /disk01/oradata/sid1/data/rbs01sid1.dbf: cannot > execute > /create_dupdb.sh[22]: to: not found > /create_dupdb.sh[29]: /disk01/oradata/sid1/data/temp01sid1.dbf: cannot > execute > /create_dupdb.sh[30]: to: not found > /create_dupdb.sh[33]: /disk01/oradata/sid1/data/tools01sid1.dbf: cannot > execute > /create_dupdb.sh[34]: to: not found > /create_dupdb.sh[37]: /disk01/oradata/sid1/data/users01sid1.dbf: cannot > execute > /create_dupdb.sh[38]: to: not found > /create_dupdb.sh[77]: duplicate: not found > /create_dupdb.sh[79]: logfile: not found > /create_dupdb.sh[80]: group
OCP-Network Exam
Hi, Greetings, I am on the preparation for my OCP 8i Network Administration exam. I welcome any tips or questions or model papers. So that i can prepare myself well for the exam. Looking forward to your inputs, With best regards, Muths _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Muthaiah INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: data block stockage capacity
It is truly impossible to calculate the exact "storage capacity" for an Oracle database block, for several reasons: * some data structures (ITLs, table chart, row chart, etc) grow and shrink due to the current state of transactions on rows in the block * uncommited DELETEs and UPDATEs which shrink a row "reserve" their space until COMMIT or ROLLBACK, again forming a dependency on the current state of transactions on rows in the block * most importantly, most Oracle datatypes (except DATEs and CHARs) are variable-length, adjusting the length dependent on data values PCTUSED is merely a threshold value, the percentage value under which used-space in the block must fall in order to return to the free list. Personally, I just tend to round the DB_BLOCK_SIZE down to the nearest "000" (i.e. 8192 down to 8000) in my own mind. It's far from scientific and far from exact, but when it's impossible to be exact, who cares? If you are trying to estimate how much space X rows of a specific table are going to consume, the best (and easiest) method is extrapolation. Obtain a relatively small sample of data (i.e. 10,000 rows), insert them into a table previously truncated (with DROP STORAGE) and then ANALYZE COMPUTE the table. The value of BLOCKS is the number of blocks populated. The value of "#-rows/BLOCKS" is your average density of rows per block, so take the number of rows you eventually expect (i.e. "X") and divide that by the average density of rows per block to get the expected number of blocks. Sure, the last populated block in the table might be "short" a few rows, so if you feel like correcting for that, you can do so by querying FILE# and BLOCK# from the ROWID and doing a GROUP BY to COUNT(*) the number of rows per block. Usually you'll find that the highest block is a little short by "Y" rows, so recalculate using something like "(10,000 - Y) / (BLOCKS - 1)"... It's just as accurate as any other method and a helluva lot faster and easier to calculate. Hope this helps... - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Monday, April 08, 2002 6:58 AM > How can I calculate the appropriate stocakge capacity space for a > block (8K). > > It is exact to use data BLOCk_SIZE*(PCTUSED/100). > > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Andrey Bronfin > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing > Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (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: Bernard, Gilbert > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (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: Tim Gorman INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Another RMAN Problem ---> Urgent !!
Hi Samir, Have you got your environment variables set up correctly. These would be the following ones export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1 export NB_ORA_SERV=server name export NB_ORA_CLIENT=client name export NB_ORA_SCHED= export NB_ORA_CLASS= These will all be set up in the rman backup scripts (I expect) John -Original Message- Sent: 08 April 2002 14:44 To: Multiple recipients of list ORACLE-L Dear All, I am totally out of my depth here and this is a production db which needs to be restored urgently. I shall explain my scenario first. Our application team had lost some data after last Wednesday night's backup. They have a tool to have a dump of the data from the database from which they can extract the data if needed. This dump was taken on Thursday morning. Due to some ***hole they lost some more data on Thursday during the day. They recovered this data from the database dump which they had taken on Thursday morning. They now want the data which was lost on Wednesday. Since there was no database dump available prior to this, I suggested that we can create a duplicate database upto a point of time (as on Wednesday night) to a different location on the server. They could then take a database dump with their tool and extract the lost records. This is my script which I compiled with help from Metalink and an old post from John Hallas (the backup of our target database is taken on tape with Veritas Netbackup) : #!/bin/ksh rman target / catalog rman_sid1/rman_sid1@rman817 auxiliary / run { set until time "to_date('20-MAR-2002 20:00:00','DD-MON- HH24:MI:SS)"; allocate channel c_dlt1 type 'SBT_TAPE'; allocate auxiliary channel dupdb_d1 type disk; setlimit channel c_dlt1 kbytes 67108864 maxopenfiles 128 readrate 256; setlimit channel dupdb_d1 kbytes 67108864 maxopenfiles 128 readrate 256; set newname for datafile '/disk01/oradata/sid1/data/system01sid1.dbf' to '/disk01/oradata/test/data/system01sid1.dbf'; set newname for datafile '/disk01/oradata/sid1/data/rbs01sid1.dbf' to '/disk01/oradata/test/data/rbs01sid1.dbf'; set newname for datafile '/disk01/oradata/sid1/data/temp01sid1.dbf' to '/disk01/oradata/test/data/temp01sid1.dbf'; set newname for datafile '/disk01/oradata/sid1/data/tools01sid1.dbf' to '/disk01/oradata/test/data/tools01sid1.dbf'; set newname for datafile '/disk01/oradata/sid1/data/users01sid1.dbf' to '/disk01/oradata/test/data/users01sid1.dbf'; duplicate target database to test logfile group 1 '/disk03/oradata/test/logs/log01a01sid1.dbf' size 180M, group 2 '/disk03/oradata/test/logs/log02a01sid1.dbf' size 180M, group 3 '/disk03/oradata/test/logs/log03a01sid1.dbf' size 180M; } What happens is that RMAN connects to the target, catalog and auxiliary database and goes into the RMAN prompt and just hangs thereafter some time, when I type 'exit' there out of frustration, I get the following errors : ./create_dupdb.sh[5]: run: not found ./create_dupdb.sh[9]: allocate: not found ./create_dupdb.sh[11]: allocate: not found ./create_dupdb.sh[13]: setlimit: not found ./create_dupdb.sh[14]: setlimit: not found ./create_dupdb.sh[17]: /disk01/oradata/sid1/data/system01sid1.dbf: cannot execute ./create_dupdb.sh[18]: to: not found ./create_dupdb.sh[21]: /disk01/oradata/sid1/data/rbs01sid1.dbf: cannot execute ./create_dupdb.sh[22]: to: not found ./create_dupdb.sh[29]: /disk01/oradata/sid1/data/temp01sid1.dbf: cannot execute ./create_dupdb.sh[30]: to: not found ./create_dupdb.sh[33]: /disk01/oradata/sid1/data/tools01sid1.dbf: cannot execute ./create_dupdb.sh[34]: to: not found ./create_dupdb.sh[37]: /disk01/oradata/sid1/data/users01sid1.dbf: cannot execute ./create_dupdb.sh[38]: to: not found ./create_dupdb.sh[77]: duplicate: not found ./create_dupdb.sh[79]: logfile: not found ./create_dupdb.sh[80]: group: cannot execute ./create_dupdb.sh[81]: group: cannot execute ./create_dupdb.sh[82]: group: cannot execute ./create_dupdb.sh[83]: syntax error at line 84 : `}' unexpected Could anybody please help me in identifying what is the problem and why does it say 'run:not found' and the other subsequent lines ?? This is highly urgent for me !! Thanks and Regards, Samir Samir Sarkar Oracle DBA SchlumbergerSema Email : [EMAIL PROTECTED] [EMAIL PROTECTED] Phone : +44 (0) 115 - 957 6028 EPABX : +44 (0) 115 - 957 6418 Ext. 76028 Fax : +44 (0) 115 - 957 6018 ___ This email is confidential and intended solely for the use of the individual to whom it is addressed. Any views or opinions presented are solely those of the author and do not necessarily represent those of SchlumbergerSema. If you are not the intended recipient, be advised that you have received this email in error and that any use, dissemination, forwarding, printing, or copying of this email is strictly prohibited. If you have received this email in error please notify the SchlumbergerSe
Re: Another RMAN Problem ---> Urgent !!
Your input to RMAN should be a script or "here" list. Scrap the shell script, put your "run" script into a file, run RMAN from the command line and call the rman "run" script that you just created.. Also, set NLS_DATE_FORMAT and NLS_LANG. Depending on your database version the "to_date" function will not work as you have it in "set until time". Jay >>> [EMAIL PROTECTED] 04/08/02 09:43AM >>> Dear All, I am totally out of my depth here and this is a production db which needs to be restored urgently. I shall explain my scenario first. Our application team had lost some data after last Wednesday night's backup. They have a tool to have a dump of the data from the database from which they can extract the data if needed. This dump was taken on Thursday morning. Due to some ***hole they lost some more data on Thursday during the day. They recovered this data from the database dump which they had taken on Thursday morning. They now want the data which was lost on Wednesday. Since there was no database dump available prior to this, I suggested that we can create a duplicate database upto a point of time (as on Wednesday night) to a different location on the server. They could then take a database dump with their tool and extract the lost records. This is my script which I compiled with help from Metalink and an old post from John Hallas (the backup of our target database is taken on tape with Veritas Netbackup) : #!/bin/ksh rman target / catalog rman_sid1/rman_sid1@rman817 auxiliary / run { set until time "to_date('20-MAR-2002 20:00:00','DD-MON- HH24:MI:SS)"; allocate channel c_dlt1 type 'SBT_TAPE'; allocate auxiliary channel dupdb_d1 type disk; setlimit channel c_dlt1 kbytes 67108864 maxopenfiles 128 readrate 256; setlimit channel dupdb_d1 kbytes 67108864 maxopenfiles 128 readrate 256; set newname for datafile '/disk01/oradata/sid1/data/system01sid1.dbf' to '/disk01/oradata/test/data/system01sid1.dbf'; set newname for datafile '/disk01/oradata/sid1/data/rbs01sid1.dbf' to '/disk01/oradata/test/data/rbs01sid1.dbf'; set newname for datafile '/disk01/oradata/sid1/data/temp01sid1.dbf' to '/disk01/oradata/test/data/temp01sid1.dbf'; set newname for datafile '/disk01/oradata/sid1/data/tools01sid1.dbf' to '/disk01/oradata/test/data/tools01sid1.dbf'; set newname for datafile '/disk01/oradata/sid1/data/users01sid1.dbf' to '/disk01/oradata/test/data/users01sid1.dbf'; duplicate target database to test logfile group 1 '/disk03/oradata/test/logs/log01a01sid1.dbf' size 180M, group 2 '/disk03/oradata/test/logs/log02a01sid1.dbf' size 180M, group 3 '/disk03/oradata/test/logs/log03a01sid1.dbf' size 180M; } What happens is that RMAN connects to the target, catalog and auxiliary database and goes into the RMAN prompt and just hangs thereafter some time, when I type 'exit' there out of frustration, I get the following errors : ./create_dupdb.sh[5]: run: not found ./create_dupdb.sh[9]: allocate: not found ./create_dupdb.sh[11]: allocate: not found ./create_dupdb.sh[13]: setlimit: not found ./create_dupdb.sh[14]: setlimit: not found ./create_dupdb.sh[17]: /disk01/oradata/sid1/data/system01sid1.dbf: cannot execute ./create_dupdb.sh[18]: to: not found ./create_dupdb.sh[21]: /disk01/oradata/sid1/data/rbs01sid1.dbf: cannot execute ./create_dupdb.sh[22]: to: not found ./create_dupdb.sh[29]: /disk01/oradata/sid1/data/temp01sid1.dbf: cannot execute ./create_dupdb.sh[30]: to: not found ./create_dupdb.sh[33]: /disk01/oradata/sid1/data/tools01sid1.dbf: cannot execute ./create_dupdb.sh[34]: to: not found ./create_dupdb.sh[37]: /disk01/oradata/sid1/data/users01sid1.dbf: cannot execute ./create_dupdb.sh[38]: to: not found ./create_dupdb.sh[77]: duplicate: not found ./create_dupdb.sh[79]: logfile: not found ./create_dupdb.sh[80]: group: cannot execute ./create_dupdb.sh[81]: group: cannot execute ./create_dupdb.sh[82]: group: cannot execute ./create_dupdb.sh[83]: syntax error at line 84 : `}' unexpected Could anybody please help me in identifying what is the problem and why does it say 'run:not found' and the other subsequent lines ?? This is highly urgent for me !! Thanks and Regards, Samir Samir Sarkar Oracle DBA SchlumbergerSema Email : [EMAIL PROTECTED] [EMAIL PROTECTED] Phone : +44 (0) 115 - 957 6028 EPABX : +44 (0) 115 - 957 6418 Ext. 76028 Fax : +44 (0) 115 - 957 6018 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jay Hostetter INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from
Re: RE: Oracle vs. MS SQL
Here are excerpts from a thread posted by Jim Hawkins and Jared Still back in February. Subj: Just Got Back from SqlServer 2000 training On Mon, 18 Feb 2002, Jim Hawkins wrote: > During the class, I kept a list of all the "I can't believe this is > really the case with SQL*Server..." items, and thought you might all > like to see it. These are just notes I took on a Palm Pilot, so > forgive me if they are a litte undetailed. I walked away from the > class thinking, "this is just MS Access with bells and whistles." > I'm not saying it doesn't have its place in the database market, but > I just don't see how it competes with Oracle and DB2. If you even > want to think about scaling, you have to implement Windows > clustering, which is one of the hidden costs I see that Microsoft > doesn't come right out and say. > > *Row size cannot span multiple 8k pages, therefore max row size = 8k > > *Cannot take DB out of "archivelog" mode. Can limit what is posted > to txn log, but cannot stop it. > > *Txn logs not mirrored. Must rely on RAID or other mirroring > software. > > *Separate permissions for RI checking. Requires two permission > grants if foreign key exists - one for child table and one for parent > table. Called REFERENCES permission. > > *Recommended that ALL production objects owned by DBO - not > conducive to multi-schema instances. > > *Activities that are restricted during backups: > 1. Creating or modifying databases. > 2. Performing autogrow operations. > 3. Creating indexes. > 4. Performing nonlogged operations. > 5. Shrinking a database. > > *Backups directly to tape require the tape to be attached locally to SQL Server. > > *When txn log fills up, have to just "truncate" the log in order for > processing to continue. Leaves system vulnerable until you get a > full DB backup. > > *If you have a 100GB DB that is full, your backup will be 100GB. No > compression of backups! Jared, I was going to respond, but you did a great job for me. Your points were my points exactly. I really tried to go to the SQL*Server class with an open mind thinking "I'm adding a skill set", but I found myself constantly comparing to Oracle. I didn't mean to start the Holy War again, but thought it would make an interesting conversation. A bit more: Having databases in noarchivelog mode, especially during batch loads for data warehouses/datamarts is extremely important for a large database shop like ours. In terms of RAID, I was just pointing out that while we mirror our redo logs to at least two different groups with two different members, I was shocked that the transaction log in SQL*Server was in no way mirrored by SQL*Server. It was either do it at the hardware/OS level or risk it. Not a "Mission Critical" mentality. As for transferring 10GB over the network, this would be just backing up our archive logs, not to mention the datafiles themselves. We do it every day around the clock using our tape silo. We use RMAN with hotbackups directly to tape via Veritas NetBackup enterprise wide. 10GB is trivial in the Oracle world, however, judging by the response I got, not so trivial in the SQL*Server world. One last thing: Having been to the Oracle education classes, I was expecting to learn in depth how SQL*Server uses memory to buffer the database, shared SQL, etc. thinking this would be a major tuning strategy for SQL*Server. Based on the nature of your system, you could gear the equivalent of an SGA accordingly. I almost spit up my two cups of coffee when the instructor showed me the GUI slide-bar that controls memory allocation to SQL*Server. "If you need more, just slide the bar to the right..." I still chuckle... Jim Hawkins Oracle Database Administrator [EMAIL PROTECTED] wrote: >Couldn't resist responding to this. > >*Cannot take DB out of "archivelog" mode. Can limit what is posted to txn >log, but cannot stop it. >>> Why would you want to? So you have the remote possibility >>> of ending up with a corrupt, unrecoverable database if the >>> power supply on the system fails? > >JS: Taking a database out of archive mode is certainly valid for large >load operations. Let's see, I want to load 50 gig of raw data into >my data warehouse tonight, that will generate about 800 gig of redo. > >Do I really want to do generate that much redo, deal with the overhead, >and back it up besides? Or would it be easier to put the DW back in >archive mode and back up the new data? > >*Txn logs not mirrored. Must rely on RAID or other mirroring software. >>> Hardware RAID/mirrors are much better than software, so if >>> you are comparing Oracle software based mirrors to the >>> hardware based ones we use then our way is much faster > >JS: No mention of reliability there though is there? If I don't have >control >over the hardware layout, I want Oracle to mirror the logs, period. > > >Backups directly to tape require the tape to be attached lo
Re:data block stockage capacity
Close, but no cookie. Look in the Admin guide, appendix A-1. There is some overhead that you also need to take into consideration. Dick Goulet Reply Separator Author: "Bernard; Gilbert" <[EMAIL PROTECTED]> Date: 4/8/2002 4:58 AM How can I calculate the appropriate stocakge capacity space for a block (8K). It is exact to use data BLOCk_SIZE*(PCTUSED/100). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andrey Bronfin INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Bernard, Gilbert INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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).
Foreign Objects in the System Tablespace.
I am trying to determine what Oracle "officially" considers foreign objects in the SYSTEM tablespace. If you check out Note 122669.1, section 7.1, Oracle recommends a query to find foreign objects in your system tablespace. This query will report such users as: AURORA$JIS$UTILITY$ CTXSYS MDSYS ORDSYS OSE$HTTP$ADMIN OUTLN It is part of our normal procedures to setup a TOOLS tablespace, and set this as the default tablespace for the user SYSTEM. Objects such as SQLPLUS_PRODUCT_PROFILE will be created in this tablespace. I've browsed around MetaLink and posted in one of the forums, but I'm not really getting any concrete answers as to which users should be permitted to have objects in the SYSTEM tablespace. I do know that it is OK to move OUTLN to another tablespace. Comments would be appreciated. Thanks, Jay -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jay Hostetter INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Oracle vs. MS SQL
Gene - The $40K is the Enterprise Edition pricing as I recall. Can you move to Standard Edition? If you are using EE features, then chances are that MS SQL won't do the job. Also you can point out the eWeek benchmark between Oracle and MS SQL. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Friday, April 05, 2002 6:23 PM To: Multiple recipients of list ORACLE-L OK, timing is impeccable. My boss just got the Oracle Bill, new licensing model $40k per processor for web based apps and flipped. I have some MSCE's working here pushing him to switch to SQL*server. Does anyone know where I can find reasons to stay w/ Oracle? Some things already mentioned here, but the MSCE's would say this list is bias, go figure :) Does SQLServer 2000 support blobs, row level locking, etc? Thanks, Gene PS. Do I move on to another Oracle shop or switch to SQLserver? OMG, the thought of working only on windoze makes me puke. I know this answer! >>> [EMAIL PROTECTED] 04/05/02 14:11 PM >>> There are some technical points worth considering. For example, SQL Server does not have true row level locking. It's table level locking, or some really creative SQL to fake it. This has a direct impact on scalability and performance. --- DENNIS WILLIAMS <[EMAIL PROTECTED]> wrote: > Oops, a couple of items I didn't make clear: > - I was never able to compare the cost of Oracle > support with the cost of > Microsoft support. Oracle prices annual maintenance, > which includes the > right to upgrade to a new version of Oracle. MS > prices out per incident or > for all MS software at a location. If you can > estimate the number of > calls/month, then you could compare. > - Oracle DBA salary vs. MS SQL DBA salary. I feel > the difference is > primarily due to less experience, training. I find > it ironic that this > probably causes less reliability for Microsoft > (Microsoft has even > complained that organizations don't assign their > best people to administer > MS products), yet then Microsoft brags about how you > can save money because > their people ar cheaper. > > -Original Message- > Sent: Friday, April 05, 2002 11:14 AM > To: Multiple recipients of list ORACLE-L > > > I recently prepared a total cost comparison between > Oracle and MS SQL. I > appreciate the support several people on this list > provided me. In return, > here are some of the main points I learned. > - For smaller systems, investigate whether Oracle > Standard Edition will > meet your requirements. For example, most people > assume that to use > replication, you need EE. For our purposes the basic > replication that comes > with SE was adequate. > - Microsoft also offers SQL in both EE and SE > versions. Thanks very much > to Gints Plivna for providing me a > feature-by-feature comparison between the > different versions. MS SE is not equivalent to > Oracle SE. In most cases, the > more valid comparison is between MS SQL EE and > Oracle SE. > - For maintenance, there are two parts to > consider: Upgrade privilege and > support. Oracle bundles both of these together. Make > sure Microsoft is > priced with Software Assurance, which gives upgrade > privilege. Microsoft > prices support by the incident or by the location > (all Microsoft software). > I was never able to get a comparison. > - MS SQL EE with Software Assurance is actually > more expensive than Oracle > SE. Priced by the CPU. > - Since pricing is by CPU and RISC systems offer > higher database > performance (according to many people on this list) > and Oracle offers higher > performance in a head-to-head comparison (according > to the recently > published Eweek benchmark), I compared Oracle SE on > a 1-CPU Sun box with MS > SQL on a 2-CPU Intel box. The Intel box was cheaper, > but those two CPUs > really kill you on licensing! In my mind I am > convinced that both setups > could offer equivalent performance. > - I was provided figures that the average DBA > salary (including health, > vacation, etc.) on Oracle is $85,000 and on MS SQL > $68,500. A lading > industry analyst stated that the main reason MS SQL > sites have less > reliability is because there are few processes to > ensure high availability, > high performance. Developing these processes in the > MS SQL world is more > trial-and-error while these are well-documented in > the Oracle world. I would > add that several authors that participate in this > list have created that > documentation. > > Dennis Williams > DBA > Lifetouch, Inc. > [EMAIL PROTECTED] > > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: DENNIS WILLIAMS > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: > (858) 538-5051 > San Diego, California-- Public Internet > access / Mailing Lists > > To REMOVE yourself from this mailing list, send an > E-Mail message > to:
Re:Please help resolving report generation performance probl
Three words: TUNE THE SQL. Database performance is 80% SQL Tuning and 20% database tuning. It would sound like you've got a statement or two that is doing a whole bunch of full table scans, probably somewhere inside a nested loop. Take a look inside $ORACLE_HOME\sqlplus\admin for the plustrce.sql file. If you've no tuning tools it will help. Dick Goulet Reply Separator Author: "Denmark Weatherburne" <[EMAIL PROTECTED]> Date: 4/7/2002 10:18 AM Hi DBA's, I've been trying to isolate the bottleneck with our Oracle database. I work as an Oracle DBA for the Government of a developing country (Belize). Recently, as it is income tax time, the department has to reconcile all witholdings by the employer with their payment receipt records. This involves some data entry and a report generation by employer (witholder) which lists all witholdings by each employee. This report can generate lots of pages depending on the number of employees. In some cases, the report has to be run overnight, as it takes too long (several hours) to generate. I've tried giving more resources to Oracle. I've tried creating a copy of the production database on another machine to use only for generating reports. I've increased the size and number of rollback segments. I've tuned some parameters. However, I have not observed any significant improvement in the report generation performance. I know tuning the SQL might be required, however, I don't have much experience in this area. The SQL statemements were written by consultants who have long left. We do have the source code though. We are running Oracle 8.0.5.2.1 on NT 4.0 The NT server is a Dell 4400 with Dual CPU and 1GB RAM We are using hardware RAID 5. Our database is OLTP with reporting. It is a small database (exported data is about 150 MB). I would appreciate your recommendations and advice. Thanks in advance, Denmark Weatherburne _ Chat with friends online, try MSN Messenger: http://messenger.msn.com The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact [EMAIL PROTECTED] for clarification. Report_SQL.zip Description: Zip archive The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact [EMAIL PROTECTED] for clarification. Hourly_Bstat_Estat_Reports.zip Description: Zip archive
RE: Oracle 8i Study Guide
Helen: You could try http://www.examcram.com. Also, I just found Oracle's list of test objectives. http://www.oracle.com/education/certification/objectives/index.html?content. html You may just want to consider going ahead and buying the study books. Here are the Sybex books. http://www.amazon.com/exec/obidos/ASIN/0782126855/qid=1018271515/sr=2-1/ref= sr_2_1/002-7587220-4526465 Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Friday, April 05, 2002 7:38 PM To: Multiple recipients of list ORACLE-L I just got a job after being out of work in Denver for the past eight months. I need to complete the Oracle certification. I will completing Oracle's Mixed Release Path program. Does anyone know of a free site where I can get study guide material for the Oracle 8i exams? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Helen J Mitchell INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Another RMAN Problem ---> Urgent !!
Samir, It looks like your Rman commands are not being passed to Rman, and are instead being executed by ksh. Try moving all of your Rman command into a separate file, and changing your rman command to: rman target / catalog rman_sid1/rman_sid1@rman817 auxiliary / cmdfile (rman command file name) msglog {output log file name) I know there is ane asier way to do this within ksh, but this would be the fastest way for me. hope this helps. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Monday, April 08, 2002 9:44 AM To: Multiple recipients of list ORACLE-L Dear All, I am totally out of my depth here and this is a production db which needs to be restored urgently. I shall explain my scenario first. Our application team had lost some data after last Wednesday night's backup. They have a tool to have a dump of the data from the database from which they can extract the data if needed. This dump was taken on Thursday morning. Due to some ***hole they lost some more data on Thursday during the day. They recovered this data from the database dump which they had taken on Thursday morning. They now want the data which was lost on Wednesday. Since there was no database dump available prior to this, I suggested that we can create a duplicate database upto a point of time (as on Wednesday night) to a different location on the server. They could then take a database dump with their tool and extract the lost records. This is my script which I compiled with help from Metalink and an old post from John Hallas (the backup of our target database is taken on tape with Veritas Netbackup) : #!/bin/ksh rman target / catalog rman_sid1/rman_sid1@rman817 auxiliary / run { set until time "to_date('20-MAR-2002 20:00:00','DD-MON- HH24:MI:SS)"; allocate channel c_dlt1 type 'SBT_TAPE'; allocate auxiliary channel dupdb_d1 type disk; setlimit channel c_dlt1 kbytes 67108864 maxopenfiles 128 readrate 256; setlimit channel dupdb_d1 kbytes 67108864 maxopenfiles 128 readrate 256; set newname for datafile '/disk01/oradata/sid1/data/system01sid1.dbf' to '/disk01/oradata/test/data/system01sid1.dbf'; set newname for datafile '/disk01/oradata/sid1/data/rbs01sid1.dbf' to '/disk01/oradata/test/data/rbs01sid1.dbf'; set newname for datafile '/disk01/oradata/sid1/data/temp01sid1.dbf' to '/disk01/oradata/test/data/temp01sid1.dbf'; set newname for datafile '/disk01/oradata/sid1/data/tools01sid1.dbf' to '/disk01/oradata/test/data/tools01sid1.dbf'; set newname for datafile '/disk01/oradata/sid1/data/users01sid1.dbf' to '/disk01/oradata/test/data/users01sid1.dbf'; duplicate target database to test logfile group 1 '/disk03/oradata/test/logs/log01a01sid1.dbf' size 180M, group 2 '/disk03/oradata/test/logs/log02a01sid1.dbf' size 180M, group 3 '/disk03/oradata/test/logs/log03a01sid1.dbf' size 180M; } What happens is that RMAN connects to the target, catalog and auxiliary database and goes into the RMAN prompt and just hangs thereafter some time, when I type 'exit' there out of frustration, I get the following errors : ./create_dupdb.sh[5]: run: not found ./create_dupdb.sh[9]: allocate: not found ./create_dupdb.sh[11]: allocate: not found ./create_dupdb.sh[13]: setlimit: not found ./create_dupdb.sh[14]: setlimit: not found ./create_dupdb.sh[17]: /disk01/oradata/sid1/data/system01sid1.dbf: cannot execute ./create_dupdb.sh[18]: to: not found ./create_dupdb.sh[21]: /disk01/oradata/sid1/data/rbs01sid1.dbf: cannot execute ./create_dupdb.sh[22]: to: not found ./create_dupdb.sh[29]: /disk01/oradata/sid1/data/temp01sid1.dbf: cannot execute ./create_dupdb.sh[30]: to: not found ./create_dupdb.sh[33]: /disk01/oradata/sid1/data/tools01sid1.dbf: cannot execute ./create_dupdb.sh[34]: to: not found ./create_dupdb.sh[37]: /disk01/oradata/sid1/data/users01sid1.dbf: cannot execute ./create_dupdb.sh[38]: to: not found ./create_dupdb.sh[77]: duplicate: not found ./create_dupdb.sh[79]: logfile: not found ./create_dupdb.sh[80]: group: cannot execute ./create_dupdb.sh[81]: group: cannot execute ./create_dupdb.sh[82]: group: cannot execute ./create_dupdb.sh[83]: syntax error at line 84 : `}' unexpected Could anybody please help me in identifying what is the problem and why does it say 'run:not found' and the other subsequent lines ?? This is highly urgent for me !! Thanks and Regards, Samir Samir Sarkar Oracle DBA SchlumbergerSema Email : [EMAIL PROTECTED] [EMAIL PROTECTED] Phone : +44 (0) 115 - 957 6028 EPABX : +44 (0) 115 - 957 6418 Ext. 76028 Fax : +44 (0) 115 - 957 6018 ___ This email is confidential and intended solely for the use of the individual to whom it is addressed. Any views or opinions presented are solely those of the author and do not necessarily represent those of SchlumbergerSema. If you are not the intended recipient, be advised that you have rece
Re: Oracle Replication - is it "on" by default?
I believe the scripts to create the various stored procedures come with the Enterprise Edition but Replication is not something that Oracle does on its own. At best, the packages are automatically created when you create the database. There is a LOT of work you have to do on your own to set up replication. I think you can state with confidence that you are not using replication. --- Paul Vincent <[EMAIL PROTECTED]> wrote: > Hi folks, > > we've never identified any requirement here for using any kind of > replication. Consequently I know nothing whatsoever about Oracle > Replication. Now I've been asked whether or not we use Oracle > Replication > and, if so, whether it can be disabled. So can anyone tell me whether > Replication is a feature automatically included in Oracle 8i > Enterprise > Edition? And is there an easy way of telling whether or not it's > "on"? If > it's "on", can it be turned "off" (if that's a meaningful question!), > and if > so, how? > > I've tried briefly RTFMing, but although the manuals contain a wealth > of > info about how to use Replication, I can see nothing about how to > tell > whether it's active or not, and how to switch it on or off. Any > pointers, > please, anyone? > > Paul Vincent > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Paul Vincent > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing > Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Yahoo! Tax Center - online filing with TurboTax http://taxes.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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).
Another RMAN Problem ---> Urgent !!
Dear All, I am totally out of my depth here and this is a production db which needs to be restored urgently. I shall explain my scenario first. Our application team had lost some data after last Wednesday night's backup. They have a tool to have a dump of the data from the database from which they can extract the data if needed. This dump was taken on Thursday morning. Due to some ***hole they lost some more data on Thursday during the day. They recovered this data from the database dump which they had taken on Thursday morning. They now want the data which was lost on Wednesday. Since there was no database dump available prior to this, I suggested that we can create a duplicate database upto a point of time (as on Wednesday night) to a different location on the server. They could then take a database dump with their tool and extract the lost records. This is my script which I compiled with help from Metalink and an old post from John Hallas (the backup of our target database is taken on tape with Veritas Netbackup) : #!/bin/ksh rman target / catalog rman_sid1/rman_sid1@rman817 auxiliary / run { set until time "to_date('20-MAR-2002 20:00:00','DD-MON- HH24:MI:SS)"; allocate channel c_dlt1 type 'SBT_TAPE'; allocate auxiliary channel dupdb_d1 type disk; setlimit channel c_dlt1 kbytes 67108864 maxopenfiles 128 readrate 256; setlimit channel dupdb_d1 kbytes 67108864 maxopenfiles 128 readrate 256; set newname for datafile '/disk01/oradata/sid1/data/system01sid1.dbf' to '/disk01/oradata/test/data/system01sid1.dbf'; set newname for datafile '/disk01/oradata/sid1/data/rbs01sid1.dbf' to '/disk01/oradata/test/data/rbs01sid1.dbf'; set newname for datafile '/disk01/oradata/sid1/data/temp01sid1.dbf' to '/disk01/oradata/test/data/temp01sid1.dbf'; set newname for datafile '/disk01/oradata/sid1/data/tools01sid1.dbf' to '/disk01/oradata/test/data/tools01sid1.dbf'; set newname for datafile '/disk01/oradata/sid1/data/users01sid1.dbf' to '/disk01/oradata/test/data/users01sid1.dbf'; duplicate target database to test logfile group 1 '/disk03/oradata/test/logs/log01a01sid1.dbf' size 180M, group 2 '/disk03/oradata/test/logs/log02a01sid1.dbf' size 180M, group 3 '/disk03/oradata/test/logs/log03a01sid1.dbf' size 180M; } What happens is that RMAN connects to the target, catalog and auxiliary database and goes into the RMAN prompt and just hangs thereafter some time, when I type 'exit' there out of frustration, I get the following errors : ./create_dupdb.sh[5]: run: not found ./create_dupdb.sh[9]: allocate: not found ./create_dupdb.sh[11]: allocate: not found ./create_dupdb.sh[13]: setlimit: not found ./create_dupdb.sh[14]: setlimit: not found ./create_dupdb.sh[17]: /disk01/oradata/sid1/data/system01sid1.dbf: cannot execute ./create_dupdb.sh[18]: to: not found ./create_dupdb.sh[21]: /disk01/oradata/sid1/data/rbs01sid1.dbf: cannot execute ./create_dupdb.sh[22]: to: not found ./create_dupdb.sh[29]: /disk01/oradata/sid1/data/temp01sid1.dbf: cannot execute ./create_dupdb.sh[30]: to: not found ./create_dupdb.sh[33]: /disk01/oradata/sid1/data/tools01sid1.dbf: cannot execute ./create_dupdb.sh[34]: to: not found ./create_dupdb.sh[37]: /disk01/oradata/sid1/data/users01sid1.dbf: cannot execute ./create_dupdb.sh[38]: to: not found ./create_dupdb.sh[77]: duplicate: not found ./create_dupdb.sh[79]: logfile: not found ./create_dupdb.sh[80]: group: cannot execute ./create_dupdb.sh[81]: group: cannot execute ./create_dupdb.sh[82]: group: cannot execute ./create_dupdb.sh[83]: syntax error at line 84 : `}' unexpected Could anybody please help me in identifying what is the problem and why does it say 'run:not found' and the other subsequent lines ?? This is highly urgent for me !! Thanks and Regards, Samir Samir Sarkar Oracle DBA SchlumbergerSema Email : [EMAIL PROTECTED] [EMAIL PROTECTED] Phone : +44 (0) 115 - 957 6028 EPABX : +44 (0) 115 - 957 6418 Ext. 76028 Fax : +44 (0) 115 - 957 6018 ___ This email is confidential and intended solely for the use of the individual to whom it is addressed. Any views or opinions presented are solely those of the author and do not necessarily represent those of SchlumbergerSema. If you are not the intended recipient, be advised that you have received this email in error and that any use, dissemination, forwarding, printing, or copying of this email is strictly prohibited. If you have received this email in error please notify the SchlumbergerSema Helpdesk by telephone on +44 (0) 121 627 5600. ___ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: SARKAR, Samir INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists ---
RE: a theoretical question
It will perform the update regardless of current value of the column. The value checking is only done if 1. You have specified a where clause 2. There is a check constraint that would require such check 3. You have a explicit condition check in pre or post update trigger that would require it. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- Sent: Monday, April 08, 2002 9:23 AM To: Multiple recipients of list ORACLE-L Deart gurus ! I'm just wondering what happens in this situation : i issue the following SQL : update AAA set BBB=0 where ROWID='X' ; Now , if the value of the BBB column is already 0 for the given row (or a set of rows) , what will actually happen behind the curtains ? I mean, will Oracle somehow detect that actually the update does not need to occur , or will it perform the update regardless of the current values of the columns . Thanks a lot, DBAndrey -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andrey Bronfin INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). ***1 This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify ESPN at (860) 766-2000 and delete this e-mail message from your computer, Thank you. ***1
Re: anyone see my DBA_AUDIT_TRAIL ??? (solved) bug or Architecture ?
You don't say which version you are using but some versions of 8 had a problem with desc and synonyms. Try desc sys.dba_audit_trail and see if that works. I know this was a problem in early 8.0 (8.03, 8.0.4) but thought it was fixed by 8i. John [EMAIL PROTECTED] wrote: >Hi, > >Is this characteristic of a bug or is the Oracle Architecture ? > >SQL> desc dba_audit_exists >ERROR: >ORA-24372: invalid object for describe > > >SQL> select count(*) from dba_audit_exists; > > COUNT(*) >-- > 0 > >SQL> desc dba_audit_exists > Name Null?Type > - > OS_USERNAMEVARCHAR2(255) > USERNAME VARCHAR2(30) > USERHOST VARCHAR2(128) > TERMINAL VARCHAR2(255) > TIMESTAMP NOT NULL DATE > OWNER VARCHAR2(30) > OBJ_NAME VARCHAR2(128) > ACTION_NAMEVARCHAR2(27) > NEW_OWNER VARCHAR2(30) > NEW_NAME VARCHAR2(128) > OBJ_PRIVILEGE VARCHAR2(16) > SYS_PRIVILEGE VARCHAR2(40) > GRANTEEVARCHAR2(30) > SESSIONID NOT NULL NUMBER > ENTRYID NOT NULL NUMBER > STATEMENTID NOT NULL NUMBER > RETURNCODENOT NULL NUMBER > > > >Sinardy > > >> -Original Message- >>From: Sinardy Xing >>Sent: 08 April 2002 11:41 >>Subject: anyone see my DBA_AUDIT_TRAIL ??? >> >>Hi guys, >> >>Please help me solved this >> >> >>SQL> show user >>USER is "SYS" >> >> >>SQL> select owner, object_name, object_type from all_objects where object_name = >'DBA_AUDIT_TRAIL'; >> >>OWNER OBJECT_NAMEOBJECT_TYPE >>-- -- -- >>SYSDBA_AUDIT_TRAILVIEW >>PUBLIC DBA_AUDIT_TRAILSYNONYM >> >> >>SQL> desc DBA_AUDIT_TRAIL >>ERROR: >>ORA-24372: invalid object for describe >> >> >>Why I can not describe my DBA_AUDIT_TRAIL ? >> -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ora NT DBA INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
a theoretical question
Deart gurus ! I'm just wondering what happens in this situation : i issue the following SQL : update AAA set BBB=0 where ROWID='X' ; Now , if the value of the BBB column is already 0 for the given row (or a set of rows) , what will actually happen behind the curtains ? I mean, will Oracle somehow detect that actually the update does not need to occur , or will it perform the update regardless of the current values of the columns . Thanks a lot, DBAndrey -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andrey Bronfin INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 Replication - is it "on" by default?
Hi folks, we've never identified any requirement here for using any kind of replication. Consequently I know nothing whatsoever about Oracle Replication. Now I've been asked whether or not we use Oracle Replication and, if so, whether it can be disabled. So can anyone tell me whether Replication is a feature automatically included in Oracle 8i Enterprise Edition? And is there an easy way of telling whether or not it's "on"? If it's "on", can it be turned "off" (if that's a meaningful question!), and if so, how? I've tried briefly RTFMing, but although the manuals contain a wealth of info about how to use Replication, I can see nothing about how to tell whether it's active or not, and how to switch it on or off. Any pointers, please, anyone? Paul Vincent -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Paul Vincent INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Oracle9i init.ora / Re: ORACLE-L Digest -- Volume 2002, Number 086
We had a visit today from oracle support. The guy took a look on our NT plaything for testing oracle 9i and said: How come you have only 512MB memory on this? Your database and OEM console alone use all this ram". UPGRADE Yechiel Adar, Mehish - Original Message - To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Sent: Wednesday, March 27, 2002 9:43 PM > BOFH: > > you might want to check and see if your BIOS supports > >512MB RAM and if you have any open slots, if so, > assuming they aren't locked, you can steal some RAM > from other people's computers in your office (they > probably won't notice any difference, othrewise mumble > something about how the last router upgrade had driver > problems, and the network must be why things seem so > slow). otherwise find out where the LAN guys hide the > keys (promise them Krispy Creme doughnuts?). > > > ORACLE-L Digest -- Volume 2002, Number 086 > > -- > > > > From: "Mark Leith" <[EMAIL PROTECTED]> > > Date: Tue, 26 Mar 2002 16:58:42 - > > Subject: RE: pL/SQL PROCEDUR > > > > LMAO > > > > Obligatory Oracle Question: > > > > Does anybody have a sample init.ora file that they use for a 9.0.1 instance, > > for a sandbox database on Win2K with 512Mb RAM? Basically for my desktop PC > > play database.. > > > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Eric D. Pierce > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (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: Yechiel Adar INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
data block stockage capacity
How can I calculate the appropriate stocakge capacity space for a block (8K). It is exact to use data BLOCk_SIZE*(PCTUSED/100). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andrey Bronfin INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Bernard, Gilbert INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Pl/SQL code help
Hallo, I have some trouble with this pl/sql procedure. I would like that this lvsql to be run only if the field Borttags_flagg = 0 but i get an error in the if statement, whatis wrong with this? It get the erromressage: LS-00103: Encountered the symbol "||AvdNr||" when expecting one of the following: . ( * @ % & = - + < / > at in mod not rem then <> or != or ~= >= <= <> and or like betwe.. when I compile the whole procedure. Please help me with this. (If the borttags_flagg = 1 then it will continue the loop and check for next one. etc... If A'||AvdNr||'.ICA_ARTIKEL@'||LookUpServerName|| 'BORTTAGS_FLAGG = 0 '' || then (this lvsql is to be run only if field borttags_flagg = 0) lvSQL := 'SELECT ICA_ARTIKEL.FSGPRIS,ICA_ARTIKEL.BORTTAGS_FLAGG,ICA_ARTIKEL.PANTBELOPP ' || --PBK.LPKORGEANREL.EANREL,PBK.LPKORGEANREL.VARUTYP ' || 'FROM A'||AvdNr||'.ICA_ARTIKEL@'||LookUpServerName|| ' ' || --PBK.LPKORGEANREL ' || 'WHERE ICA_ARTIKEL.EAN=' || EanLPVara || ' ' || 'AND ICA_ARTIKEL.DATUM testplsql.SQL Description: Binary data
THANKS - a PL/SQL question - how to catch errors without going i
Many thanks to all who replied ! Have a nice day ! DBAndrey * 03-9254520 * 058-548133 * mailto:[EMAIL PROTECTED] -Original Message- Sent: Thu, April 04, 2002 9:37 PM To: Multiple recipients of list ORACLE-L ex Have you considered just adding another exception handler: i := 1; while i < 10 loop begin select the_name from the_table into myvar where the_id = i ; exception when others then null; -> or whatever you want to do; end; end loop; The net effect is the same. HTH - Brian -Original Message- Bronfin Sent: Thursday, April 04, 2002 12:49 PM To: Multiple recipients of list ORACLE-L ex ps , i meant i := 1; while i < 10 loop select the_name from the_table into myvar where the_id = i ; end loop; DBAndre > -Original Message- > From: Andrey Bronfin > Sent: Thu, April 04, 2002 9:50 PM > To: [EMAIL PROTECTED] (E-mail); [EMAIL PROTECTED] (E-mail); > oralist@lists (E-mail) > Subject: a PL/SQL question - how to catch errors without going into > exceptions block > > Dear gurus ! > I'm wondering whtether i can catch an SQL error (from inside a PL/SQL > proc) without jumping to the EXCEPTION block > OR > is there a way to jump back to the body of the proc from the EXCEPTION > block (i know that GOTO can not do it). > > For example , assume i have users with IDs 1,2,5,6 in my table and i want > to do some loop like this > > i := 1; > while i < 10 loop > select the_name from the_table into myvar where the_id = 1; > end loop; > . > > I will be thrown to the EXCEPTION block as soon as i becomes 3. > And i can never go back to the loop from the EXCEPTION block , in order to > continue looping ;-( > So , can i just tell PL/SQL something like "never mind if U fail (i.e. an > exception is thrown) , just go to the next iteration ... > > I'm wondering if there is something similar to PERL's > next if . > > Thanks a lot > Andre > > > > > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andrey Bronfin INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Brian McGraw INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Andrey Bronfin INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
AW: two listeners problem ???
What's about PORT 2481 for GIOP, I think you also have to use different ports for GIOP (2481 für LISTENER817 and 2482 for LISTENER816). regards Volker Schoen E-Mail: mailto:[EMAIL PROTECTED] http://www.inplan.de -Ursprüngliche Nachricht- Von: Janet Linsy [mailto:[EMAIL PROTECTED]] Gesendet: Samstag, 6. April 2002 08:08 An: Multiple recipients of list ORACLE-L Betreff: two listeners problem ??? Hi, I have two databases ORCL817 and ORCL816 in version 817, 816 on the same machine Sun 5.7. I'd like to configure seperate listener for each database. I used different port, listener name (1521 and LISTENER817 for ORCL817 , 1522 and LISTENER816 for ORCL816), but somehow the listener on 1522 cannot be started. Why??? Here is the listener.ora and tnsnames.ora for both database: listener.ora for 816 = LISTENER816 = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = gatech-denver1)(PORT = 1522)) ) ) (DESCRIPTION = (PROTOCOL_STACK = (PRESENTATION = GIOP) (SESSION = RAW) ) (ADDRESS = (PROTOCOL = TCP)(HOST = gatech-denver1)(PORT = 2481)) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = ORCL816) (ORACLE_HOME = /export/apps/oracle/admin/product/8.1.6) (SID_NAME = ORCL816) ) tnsnames.ora for 816 = ORCL816 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = gatech-denver1)(PORT = 1522)) ) (CONNECT_DATA = (SERVICE_NAME = ORCL816) ) ) listener.ora for 817 = LISTENER817 = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = gatech-denver1)(PORT = 1521)) ) ) (DESCRIPTION = (PROTOCOL_STACK = (PRESENTATION = GIOP) (SESSION = RAW) ) (ADDRESS = (PROTOCOL = TCP)(HOST = gatech-denver1)(PORT = 2481)) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = ORCL817) (ORACLE_HOME = /export/apps/oracle/admin/product/8.1.7) (SID_NAME = ORCL817) ) ) tnsnames.ora = ORCL817 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = gatech-denver1)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = ORCL817) ) ) Anyone knows what went wrong? Thank you! Janet __ Do You Yahoo!? Yahoo! Tax Center - online filing with TurboTax http://taxes.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Janet Linsy INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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).
How to Audit a table
Hi, How can I audit a table whether is selected as SELECT col1, col2 FROM tab WHERE col1 = 'condition'; and NOT all the SELECT queries I'm using Oracle 8i Thanks Sinardy -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sinardy Xing INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
OAS 4.0.8.2
Hi I am using OAS 4082 on Sun Solaris, with Oracle 8.1.6. I was wondering if there is any way of Using 2 DAD's (Data Access Descriptors) with one plsql cartridge agent ? Regards Saj -- Sajid Iqbal Database Team Leader Email: [EMAIL PROTECTED] Website: http://www.vianetworks.co.uk -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sajid Iqbal INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).