Fwd: UTL_FILE_DIR on 9iR2
Ah! - got it. To simplify my question I hadn't mentioned that I had more than one allowed directory, and this was where the problem lay: I did: ALTER SYSTEM SET UTL_FILE_DIR = 'directory1, directory2' SCOPE=spfile; Whereas what is required is: ALTER SYSTEM SET UTL_FILE_DIR = 'directory1','directory2' SCOPE=spfile; The first form is accepted by Oracle and looks ok in v$parameter but clearly doesn't do the same thing underneath! Good: this means I can delay implementing DIRECTORYs for a bit longer... (not that I don't want to use them, just that other things have priority) - Bill Date: Fri, 16 Jan 2004 11:15:35 + To: [EMAIL PROTECTED] From: Bill Buchan [EMAIL PROTECTED] Subject: UTL_FILE_DIR on 9iR2 Hi all, I'm looking for some clarification on UTL_FILE_DIR on 9.2. I understand that this parameter is obsolete (Metalink Note 196939.1) and that CREATE DIRECTORY should be used instead. However we wanted to persist with the old method since we already have lots of existing PL/SQL that uses a look up table to decide where to read/write files; yes we'd move to CREATE DIRECTORY eventually but not right now. I thought that although UTL_FILE_DIR was obsolete it was really just deprecated and we could continue using it as before. If UTL_FILE_DIR = '*' then all is indeed well. However if I try to restrict the directories, i.e. UTL_FILE_DIR = '/only/allowed/here' then it does not work (yes, checked filesystem privs). ERROR at line 1: ORA-29280: invalid directory path ORA-06512: at SYS.UTL_FILE, line 18 ORA-06512: at SYS.UTL_FILE, line 424 ORA-06512: at line 4 I can sort of fudge it to work: SQL create directory /only/allowed/here as '/only/allowed/here'; Directory created. SQL grant read on directory /only/allowed/here to public; Grant succeeded. It's annoying to have to create directories with quoted names the same as the paths. Have I missed something or am I just going to have to use CREATE DIRECTORY like this until we have an opportunity to change the PL/SQL? Thanks - Bill. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Bill Buchan 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).
JVM for DBA
Hi all, Does anyone have any good resources on the Oracle JVM from a DBA perspective? (ie. Tuning java parameters, managing storage/validation of java objects, monitoring java pool performance, managing security... etc.) I can find lots of stuff for developers but I don't write too much Java code! Thanks for any info. - Bill. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Bill Buchan 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).
Stored Outline Problem
Hi all, I'm trying to set up stored outlines and am running into the following problem (OUTLN tables are initially empty and the shared pool has just been flushed): ALTER SESSION SET CREATE_STORED_OUTLINES = TRUE; SELECT 'THISISMYTEST' FROM DUAL; ALTER SESSION SET CREATE_STORED_OUTLINES = FALSE; SQL SELECT HASH_VALUE,SQL_TEXT FROM OUTLN.OL$; HASH_VALUE SQL_TEXT -- 1024768383 SELECT 'THISISMYTEST' FROM DUAL SQL SELECT HASH_VALUE,SQL_TEXT FROM V$SQL WHERE SQL_TEXT LIKE '%THISISMYTEST%'; HASH_VALUE SQL_TEXT -- 2081312514 SELECT 'THISISMYTEST' FROM DUAL 2452395670 SELECT HASH_VALUE,SQL_TEXT FROM V$SQL WHERE SQL_TEXT LIKE '% THISISMYTEST%' Now (as expected) there is now one row in OUTLN.OL$ but the HASH_VALUE != the HASH_VALUE for the given SELECT statement in v$sql. In fact the HASH_VALUE doesn't match anything in v$sql. Obviously (and demonstratively) the outline will not be used if the query hashes to a different value that the outline. Am I missing something? (CREATE ANY OUTLINE is granted) Thanks for any help, - Bill. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Bill Buchan 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).
Fwd: Stored Outline Problem
Additional information: The problem described below occurs on 9.2.0.4 (Linux). I have just tried re-producing on 8.1.7.4 (Solaris) and it actually worked fine there (hash values in ol$ and v$sql matched). Once again, any input welcome. Thanks, - Bill. Hi all, I'm trying to set up stored outlines and am running into the following problem (OUTLN tables are initially empty and the shared pool has just been flushed): ALTER SESSION SET CREATE_STORED_OUTLINES = TRUE; SELECT 'THISISMYTEST' FROM DUAL; ALTER SESSION SET CREATE_STORED_OUTLINES = FALSE; SQL SELECT HASH_VALUE,SQL_TEXT FROM OUTLN.OL$; HASH_VALUE SQL_TEXT -- 1024768383 SELECT 'THISISMYTEST' FROM DUAL SQL SELECT HASH_VALUE,SQL_TEXT FROM V$SQL WHERE SQL_TEXT LIKE '%THISISMYTEST%'; HASH_VALUE SQL_TEXT -- 2081312514 SELECT 'THISISMYTEST' FROM DUAL 2452395670 SELECT HASH_VALUE,SQL_TEXT FROM V$SQL WHERE SQL_TEXT LIKE '% THISISMYTEST%' Now (as expected) there is now one row in OUTLN.OL$ but the HASH_VALUE != the HASH_VALUE for the given SELECT statement in v$sql. In fact the HASH_VALUE doesn't match anything in v$sql. Obviously (and demonstratively) the outline will not be used if the query hashes to a different value that the outline. Am I missing something? (CREATE ANY OUTLINE is granted) Thanks for any help, - Bill. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Bill Buchan 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: orbitz fiasco
At 05:40 20/11/2003 -0800, you wrote: 3. However, they chose to restore the control file from tape. This invalidated their database. Maybe I'm missing the obvious, but why does this invalidate their database? Don't you just do RECOVER DATABASE USING BACKUP CONTROLFILE ? (Agreed that it would be quicker to swap in a non-corrupt multiplexed copy but can't see why it should have been a disaster.). Thanks - Bill. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Bill Buchan 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: wakeup time manager
Hi Arup, Yes, AQ is enabled for this database - thanks for the information. - Bill. At 07:59 13/11/2003 -0800, Arup Nanda wrote: Have you enabled Advanced Queue Manager process in the init file? aq_tm_processes? If so, this wait event occurs when the process just waits to see some messages in the queue. Yes, it is an idle event and can be ignored. Fortunately this is listed as idle event in STATSPACK, which does not report these waits. HTH. Arup - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, November 13, 2003 10:24 AM Hi, I cannot find reference to the wakeup time manager wait event in the FM. Can anyone explain it to me? Getting lots of waits for this in a 9.2.0.4 database - but given that both processors and disks are pretty idle at the moment I guess it falls under the category of idle events. Therefore I'm not going to worry about it too much - but would be nice to know what it is. Thanks for any info - Bill. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Bill Buchan 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: wakeup time manager
Hi Arup, Yes, AQ is enabled for this database - thanks for the information. - Bill. At 07:59 13/11/2003 -0800, Arup Nanda wrote: Have you enabled Advanced Queue Manager process in the init file? aq_tm_processes? If so, this wait event occurs when the process just waits to see some messages in the queue. Yes, it is an idle event and can be ignored. Fortunately this is listed as idle event in STATSPACK, which does not report these waits. HTH. Arup - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, November 13, 2003 10:24 AM Hi, I cannot find reference to the wakeup time manager wait event in the FM. Can anyone explain it to me? Getting lots of waits for this in a 9.2.0.4 database - but given that both processors and disks are pretty idle at the moment I guess it falls under the category of idle events. Therefore I'm not going to worry about it too much - but would be nice to know what it is. Thanks for any info - Bill. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Bill Buchan 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).
Archive Destinations: quota_size
Hi all, Is anyone using the QUOTA_SIZE setting in the LOG_ARCHIVE_DEST_n parameters? I am trying to use this for a local archive destination in a non-Dataguard configuration. However if the destination fills up, although the error is correctly identified and reported (alert log and V$ARCHIVE_DEST) and archiving stops, the error never goes away when I clear out the destination. REOPEN is specified (default = 300 seconds). I can manually clear the error force the archiver to restart by doing an ALTER SYSTEM SET LOG_ARCHIVE_DEST_n = 'exactly what it was before' but I was assuming this error would automatically clear after REOPEN seconds when I empty the destination. Is this broken or am I missing something? (9.2.0.4 on Linux) Many thanks - Bill. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Bill Buchan 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).
wakeup time manager
Hi, I cannot find reference to the wakeup time manager wait event in the FM. Can anyone explain it to me? Getting lots of waits for this in a 9.2.0.4 database - but given that both processors and disks are pretty idle at the moment I guess it falls under the category of idle events. Therefore I'm not going to worry about it too much - but would be nice to know what it is. Thanks for any info - Bill. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Bill Buchan 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: Granting object privileges as SYS
At 02:49 12/09/2003 -0800, you wrote: Hi, I'm trying to grant SELECT privileges on some tables in schema X to userid Y. I'm currently connected as SYS. But when I try typing GRANT SELECT ON X.MYTABLE TO Y; ...I get an ORA-01031 error - Insufficient Privileges. Now I thought SYS had all the necessary privileges to do this - i.e. granting access on user X's tables to user Y. But it appears not! Am I missing something here, or is this another one of those loveable Oracle quirks which enrich our lives so much? Oh, by the way, it's Oracle 8.1.7. That's the problem: you can only do this in 9i. In 8i SYS cannot grant privileges on other user's tables. The trivial work-around for 8i is, of course, just to log in as X to grant privileges. The slightly less trivial work-around (if you don't want to log in as X) is for SYS to create a procedure in schema X which grants the required table privileges (execute immediate) to Y and then run it (and drop it). Not my idea - can't remember who first suggested it. HTH - Bill. Regards, Paul Vincent DBA University of Central England -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Bill Buchan 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: Granting object privileges as SYS
At 03:34 12/09/2003 -0800, you wrote: Thanks Bill - not good news, then! Trouble is, I'm setting up a bunch of read-only userids which can see (but not touch) tables in several other schemas. So instead of doing all the grants with a single connect (as SYS), evidently I'll have to connect as each schema-owner in turn, and issue the grants for that schema's objects. Or you could use the procedure trick I mentioned - it's simple to put together what you want to do in a script; the following will run as SYS: SQL create procedure scott.grant_privs 2 as 3 begin 4 execute immediate 'grant select on emp to bill'; 5 end; 6 / Procedure created. SQL begin 2 scott.grant_privs; 3 end; 4 / PL/SQL procedure successfully completed. SQL drop procedure scott.grant_privs; Procedure dropped. Do you think Oracle keep rationing out these long-standing wish-list items with each successive release, in order to encourage us to take up those releases? Still waiting for DEFAULT_INDEX_TABLESPACE :-) - Bill. Oh well, thanks again for the info! Regards, Paul -Original Message- From: Bill Buchan [mailto:[EMAIL PROTECTED]] Sent: 12 September 2003 12:15 To: Multiple recipients of list ORACLE-L Subject: Re: Granting object privileges as SYS At 02:49 12/09/2003 -0800, you wrote: Hi, I'm trying to grant SELECT privileges on some tables in schema X to userid Y. I'm currently connected as SYS. But when I try typing GRANT SELECT ON X.MYTABLE TO Y; ...I get an ORA-01031 error - Insufficient Privileges. Now I thought SYS had all the necessary privileges to do this - i.e. granting access on user X's tables to user Y. But it appears not! Am I missing something here, or is this another one of those loveable Oracle quirks which enrich our lives so much? Oh, by the way, it's Oracle 8.1.7. That's the problem: you can only do this in 9i. In 8i SYS cannot grant privileges on other user's tables. The trivial work-around for 8i is, of course, just to log in as X to grant privileges. The slightly less trivial work-around (if you don't want to log in as X) is for SYS to create a procedure in schema X which grants the required table privileges (execute immediate) to Y and then run it (and drop it). Not my idea - can't remember who first suggested it. HTH - Bill. Regards, Paul Vincent DBA University of Central England -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Bill Buchan 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).
Hidden Columns
Hi all, I know this is RTFM but I can't find the right part in the FM to R. So any help would be appreciated: In the context of: DBMS_STATS.GATHER_DATABASE_STATS(method_opt='FOR ALL HIDDEN COLUMNS'); what is a hidden column. I tried gathering stats FOR ALL HIDDEN COLUMNS and it didn't seem to gather any stats for any columns. What is it meant to do? Thanks - Bill. PS. The complete syntax for the column is: FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Bill Buchan 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).
Fwd: Hidden Columns
Sorry for wasting bandwidth - found the answer 5 minutes later (found it by looking up histograms in the manual, not DBMS_STATS): For those who want to know - a hidden column is an expression for a function based index. Therefore you can use this option to gather stats when using function based indexes. - Bill. Date: Wed, 28 May 2003 14:42:31 +0100 To: [EMAIL PROTECTED] From: Bill Buchan [EMAIL PROTECTED] Subject: Hidden Columns Hi all, I know this is RTFM but I can't find the right part in the FM to R. So any help would be appreciated: In the context of: DBMS_STATS.GATHER_DATABASE_STATS(method_opt='FOR ALL HIDDEN COLUMNS'); what is a hidden column. I tried gathering stats FOR ALL HIDDEN COLUMNS and it didn't seem to gather any stats for any columns. What is it meant to do? Thanks - Bill. PS. The complete syntax for the column is: FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause] -- Intasys Billing Technologies Ltd. www.intasysbilling.com 74 Commercial Street, Commercial Quay, Leith, Edinburgh EH6 6LX tel (0)131 625 8200 fax (0)131 625 8201 email [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Bill Buchan 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).
OEM User Defined Job Environment
Hi all, I'm a bit confused by some OEM/Linux concepts. I'm trying to run a user defined job which is a simple script: #!/bin/ksh env /tmp/envfile However, my preferred credentials on the node are for myself, WBUCHAN. The /tmp/envfile is created, owned by WBUCHAN, but contains lines like: USER=oracle and LOGNAME=oracle I'm confused. Is this job running as wbuchan or as oracle? (This is 9.2 on Linux) This is probably just my ignorance of Linux environment settings. I'd be very grateful is somebody to clear up my confusion. The reason I need to know is to set things up like SSH, so that the jobs can copy off some application-specific information to another machine. The job seems to try to use oracle's keys but accesses them as wbuchan (and hence runs into file permission problems). Thanks - Bill. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Bill Buchan 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: password
Yes, this is the intended behaviour (although I can't find where it actually documented). Passwords are stored using a one-way encryption and this encrypted form applies to all Oracle platforms. It is used, for example, during export/import of full dumps where the users are created on the new (import) database with the same password they had on the old (export) database simply by copying the encrypted form. This must be cross-platform/version, and exp/imp is cross-platform/version. You can do the same thing as exp/imp does by: CREATE USER user IDENTIFIED BY VALUES encrypted form eg CREATE USER TEST IDENTIFIED BY VALUES '7A0F2B316C212D67'; Note, though that the encrypted form of the password is dependent on the username for which it applies, so you cannot use this to set the same password for a differently named user. - Bill. At 06:14 20/12/2002 -0800, you wrote: Same on linux 7.2 Oracle 8.1.7 rel 3 Ron [EMAIL PROTECTED] 12/20/02 04:07AM BOB, its the same on my machine... 8.1.6 on NT -Venu -Original Message- From: Bob Metelsky [mailto:[EMAIL PROTECTED]] Sent: Wednesday, December 18, 2002 3:56 AM To: Multiple recipients of list ORACLE-L Subject: RE: password created a user test identified by test on 2 separate systems in db's with different names The password value was the same Can someone verify if it is the same on their system Create user test identified by test; select password from dba_users where username = 'TEST'; PASSWORD -- 7A0F2B316C212D67 -Original Message- on my db LTRACK1 SQL select password from dba_users where username = 'TEST'; PASSWORD -- 7A0F2B316C212D67 bob -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ron Rogers 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). -- Intasys Billing Technologies Ltd. www.intasysbilling.com 74 Commercial Street, Commercial Quay, Leith, Edinburgh EH6 6LX tel (0)131 625 8200 fax (0)131 625 8201 email [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Bill Buchan 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: best case scenarios for export/import
Hi, That doesn't sound particularly healthy to me. Depends what you're running on I guess. We have a nightly batch job which does an import on 8.1.7, rather weedy 933MHz Intel, 50Mb SGA, 4 x SCSI disk Linux box. The dump file is 4.0Gb which turns out to be about 6Gb data, 1Gb indexes. It takes 3 hours, including all the index creation, and I've not even bothered trying to tune it *at all* (ie. default import parameters) because the window available for it is about 14 hours. One thing to take care over with indexes=n is that this is does not disable the creation of constraint indexes (ie. Primary Key, Unique). You may need to adjust the sort_area_size for these (however, I've not bothered as I still have 11 hours of spare window). When I do need to do this, I bounce the instance and set the sort_area_size instance wide - maybe somebody can suggest a better way of doing it just for the import? I guess your next step is to monitor the import itself and look at the wait events to see if something is obviously amiss. - Bill. At 06:04 20/12/2002 -0800, you wrote: Good day, all: I'm looking for real-life best-case scenarios for running import/export . . . I've been playing with this for quite some time and would like to know how fast I can really expect this to go, particularly for the import. I'd be interested to hear others' experiences - how fast have you been able to import data? what parameters have you used? etc. . . . it's both for informational purposes and as a sanity check. For example: I'm now trying to import a dump file of appx 6.5 Gb - breaks down into 12G data and 4G indexes. using the following params on the first import, to just get the data (I then rerun with the indexfile param to get the indexes): recordlength=65535 buffer=1500 (15M) commit=y indexes=n constraints=n grants=n This will import in appx 36 hours using a single 3 Gb rollback segment What kind of experiences have you had? Thanks bill -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Magaliff, Bill 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: Bill Buchan 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).
Standards: Semicolon or Backslash
Hi all, We're having a discussion over SQL scripting standards. We're trying to decide on best practice for ending a SQL statement. OPTION (A) - BACKSLASH -- BEGIN EXECUTE PL_SQL_BLOCK; END; / UPDATE TABLE SET COLUMN = VALUE / OPTION (B) - SEMICOLON -- BEGIN EXECUTE PL_SQL_BLOCK; END; / UPDATE TABLE SET COLUMN = VALUE; The argument for option (A) is that since we need '/' at the end of PL/SQL blocks, it is consistent to have them for SQL too. The argument for option (B) is that some developers have a tendency to use ';' and '/' and you end up with the update being run twice. ie. UPDATE TABLE SET COLUMN = VALUE; / I'd personally favour (B) as I think the merit of discouraging inadvertent duplicate runs of the same SQL is a better gain than the consistency of having everything end in a '/'. Any comments, suggestions etc. on this? Many thanks - Bill. -- Intasys Billing Technologies Ltd. www.intasysbilling.com 74 Commercial Street, Commercial Quay, Leith, Edinburgh EH6 6LX tel (0)131 625 8200 fax (0)131 625 8201 email [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Buchan 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).
Read Only Login with Source Visibility
Hi all, I am trying to create a read-only login, RO_USER which can do the following: 1. See all tables, views, constraints etc. in one *specific* other schema, APP. 2. See all the source for PL/SQL objects in APP. The first bit is easy: GRANT SELECT on tables, views to RO_USER. Not sure about the second bit. I have tried granting CREATE ANY PROCEDURE and CREATE ANY TRIGGER to RO_USER and using a DDL trigger to prevent these privileges actually being used to create procedures/triggers. This works but does not restrict the source visibility to APP. I have other schemas where I do not want RO_USER to see the source. My other concern is granting SELECT on sequences as this means that they can select nextval from them and hence increment the numbers (not quite read-only!) Any suggestions for fixing these problems would be much appreciated! Thanks - Bill. PS. This is on 8i. -- Intasys Billing Technologies Ltd. www.intasysbilling.com 74 Commercial Street, Commercial Quay, Leith, Edinburgh EH6 6LX tel (0)131 625 8200 fax (0)131 625 8201 email [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Buchan 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: Procedural Consistency
What is the error and how is the error occuring? My experience has been: 1. Long running SQL calling PL/SQL function:will die due to invalidated state of the function. 2. Long running PL/SQL calling PL/SQL procedure (statically): cannot re-compile the procedure during the run as it is pinned. 3. Long running PL/SQL calling PL/SQL procedure (dynamically): will just run whatever procedure exists at the time of the call. - Bill. At 12:43 24/09/2002 -0800, you wrote: Okay, I know I'm being a little lazy on this one, but I'm very interested to hear the ideas/conjecture/proof. So away we go We recently encountered a bug in Oracle where a long running process attempted to execute a procedure that was within a package that had been recompiled since the process had first executed the procedure. This brings up the question as to whether the kernel requires that each time the process executes the procedure that the procedure is exactly the same as when the process first executed it. If so, how does the process keep track of the version of the procedure that it has previously executed? Any input/thoughts are greatly appreciated... Dan -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Buchan 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: Lost ofall redo logs
If you lost the redo logs *after* you shutdown the database, you can do the following to re-create them: 1. As DBA: startup mount; 2. alter database backup controlfile to trace; 3. select * from v$controlfile; 4. shutdown 5. Delete the controlfiles identified by (3) as these need to be re-created. 6. Edit the tracefile created by (2) to look something like the following: STARTUP NOMOUNT CREATE CONTROLFILE SET DATABASE SID RESETLOGS NOARCHIVELOG MAXLOGFILES 6 MAXLOGMEMBERS 2 MAXDATAFILES 64 MAXINSTANCES 1 MAXLOGHISTORY 226 LOGFILE GROUP 1 '/usr4/oradata/SID/redoSID01a.log' SIZE 50M, GROUP 2 '/usr4/oradata/SID/redoSID02a.log' SIZE 50M, GROUP 3 '/usr4/oradata/SID/redoSID03a.log' SIZE 50M, GROUP 4 '/usr4/oradata/SID/redoSID04a.log' SIZE 50M DATAFILE '/usr1/oradata/SID/system_01.dbf', '/usr2/oradata/SID/users_01.dbf', '/usr3/oradata/SID/users_indexes_01.dbf', '/usr3/oradata/SID/rollback_01.dbf' CHARACTER SET WE8ISO8859P1 ; ALTER DATABASE OPEN RESETLOGS; ALTER TABLESPACE TEMP ADD TEMPFILE '/usr1/oradata/SID/temp_01.dbf' REUSE; ie. i. Remove leading comments and lines beginning with # ii. In the CREATE CONTROLFILE line change REUSE to SET and NORESETLOGS to RESETLOGS iii.Get rid of RECOVER DATABASE iv. Change ALTER DATABASE OPEN to ALTER DATABASE OPEN RESETLOGS (v. Ignore ALTER TABLESPACE stuff if you are not using LMT temporary tablespaces.) 7. Now go into SQL*Plus as DBA and run the script, which should build new controlfiles, re-create the log files and open the database. Remember to back up everything you *do* have before starting. This will not work if you lost the redo logs before you shutdown the database. - Bill. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, September 18, 2002 5:30 PM Hi people... What to do if I have LOST ALL REDO LOGS and i don´t have any backup? Thanks in advance! : Gilberto Gampert Universidade de Passo Fundo Administrador de Banco de Dados Passo Fundo - RS - Bra5il [EMAIL PROTECTED]http://www.upf.br : -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gilberto Gampert INET: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Buchan 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).
UFS logging
Hi all, Can anyone share any experience of the logging option on Solaris 8 filesystems with regard to Oracle and performance. I've just inhereted a second hand Sun box to use as a test machine and the filesystems are already set up with this option for both redo log disks and datafile disks. The disks are also set up for buffered i/o which I'm tempted to change to forcedirectio, but I'd prefer to just change one thing at a time and see what happens! Any info much appreciated. - Bill. -- Intasys Billing Technologies Ltd. www.intasysbilling.com 74 Commercial Street, Commercial Quay, Leith, Edinburgh EH6 6LX tel (0)131 625 8200 fax (0)131 625 8201 email [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Buchan 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: Slightly OT: Rounding Negative Numbers
I know the rule round up, but does up mean the highest value (-0.87) or the highest absolute magnitude (-0.88)? :) - Bill. At 09:53 28/08/2002 -0800, you wrote: I would think -0.875 would round up therefore -0.87 is my guess. Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Buchan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Slightly OT: Rounding Negative Numbers
I'm trying to consolidate some numbers generated by a PL/SQL report vs. a
Java based report and these seem to be out due to rounding errors on
negative numbers.
In PL/SQL if I for example, round to 2 decimal places:
SQL select round(-0.875,2) from dual;
ROUND(-0.875,2)
---
-.88
In Java:
import java.*;
public class Round {
public static void main(String[] args) {
double a = -0.875;
int i = (int)java.lang.Math.round(a*100);
double z = i;
z = z / 100;
System.out.println(z);
}
}
java Round
-0.87
which is 0.01 different (1 pence in this case). If I use positive numbers
(+0.875) when the answer is 0.88 for both PL/SQL and Java. PL/SQL's answer
looks morecorrect to me for the negative value - but what is the
mathematically correct rounding of -0.875?!
Thanks!
- Bill.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Bill Buchan
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Lock against Inserts
I have a transaction which issues LOCK TABLE TABLE IN EXCLUSIVE MODE. This is to prevent INSERTs during processing. However this is way too restrictive - we only need to restrict certain types of insert, eg. where col1 = 123. I can do something like SELECT * FROM TABLE WHERE col1 = 123 FOR UPDATE to prevent updates of existing rows of this type, but this will not block further inserts. I could set up an insert trigger to prevent these inserts, but I don't want to error: just wait until the transaction ends. This would appear to make the trigger code too complex - surely there is a way to do this just with simple locks? Any ideas much appreciated! Thanks - Bill. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Buchan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Fwd: Lock against Inserts
Aha! Just seen the DBMS_LOCK example in Thomas Kyte's book where he does pretty much what I need (i.e. request a lock on hash(123) before the transaction starts). Still requires setting up a trigger, which I'd hoped to avoid, but if you need to do it this way, it's gotta go in! Thanks - Bill. I have a transaction which issues LOCK TABLE TABLE IN EXCLUSIVE MODE. This is to prevent INSERTs during processing. However this is way too restrictive - we only need to restrict certain types of insert, eg. where col1 = 123. I can do something like SELECT * FROM TABLE WHERE col1 = 123 FOR UPDATE to prevent updates of existing rows of this type, but this will not block further inserts. I could set up an insert trigger to prevent these inserts, but I don't want to error: just wait until the transaction ends. This would appear to make the trigger code too complex - surely there is a way to do this just with simple locks? Any ideas much appreciated! Thanks - Bill. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Buchan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Adding Control files
At 10:18 04/07/02 -0800, you wrote: Hello All I got a request to add at least 2 more control files to the database. Currently we are having 2 control files. I read the docs (not too much, it is 20:00 now) and did not see it. 1) How do you add a control file? Alter system backup control file and then add it to Init parms? You do not need to do anything at the SQL prompt to add a control file. Shutdown the database, physically copy (at the OS) one of the existing control files to the two new locations (depending on naming conventions at your site you may also need to rename it) and then add the file details to the control_files parameter in the Init file. Then restart the database. 2) I was told that having 4 control files solves some contention. Anybody encountered this? I can't see how this would be the case. You are merely adding redundancy to prevent against loss of the file through multiple disk failures. The control files are all identical and must therefore *all* be updated at a checkpoint. So this potentially *increases* contention, but unless you have very large numbers of data files (or you are also in the middle of doing something with RMAN without a recovery catalog) then the additional overhead should not be too severe. Can you find out some more details from the person who told you this, as I can't see what they're getting at! Oracle 8.1.6.3.4 on NT4 SP6 server. Yechiel Adar Mehish - Bill. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Buchan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: What block size are you using for your new 9i data
Sorry, I'm a bit non-clued up on this read ahead algorithm. Could I be a pain and ask for more details? Does the OS return one OS block if exactly one is requested, but if 2 are requested it thinks aha! sequential scan and goes and gets 4 or 8 or something? The follow on is, does this mean you should use a (minimal) 2k block size on UFS, 512 bytes blocks, or is this read-ahead overhead a smaller performance hit than that of using a database block size which is too small for the application? Thanks - Bill. At 08:48 26/04/02 -0800, you wrote: All, You always want to ensure that your DB_BLOCK_SIZE = File System Block Size. This is to avoid wasted I/O and also the case where the read ahead algorithm is triggered accidentally, when 1 Database Block results in multiple file system blocks being read from disk. If your application performs range scans, there is a high possibility that multiple single database block read requests to a set of contiguous blocks, may result in the read ahead algorithm performing 128K or 256K pre-fetches, even though your application may have not required all 128K or 256K. This problem is rampant on ufs file systems where the default block size is 512 bytes, and with a 8K DB_BLOCK_SIZE, it takes 16 file system blocks to store 1 DB block on disk. However, even if you have advanced file systems and have a 1-is-to-2 ratio of DB block is-to FS blocks, you are still in danger of overloading your I/O sub-system, under the right conditions. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Buchan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Number of CPUs vs. Speed of CPUs
Hi Lisa, I would expect that 2 x 1GHz processors will be faster than 1 x 2GHz processor as there will be less context switching as each processor is handling a smaller individual load. That said, you're probably paying your Oracle licence per processor so it may make financial sense to go for fewer and faster and take the hit. (This is opinion, not based on any real data, so take with pinch of salt!) - Bill. -Original Message- From: YTTRI Lisa [SMTP:[EMAIL PROTECTED]] Sent: Thursday, April 18, 2002 10:58 AM To: Multiple recipients of list ORACLE-L Subject: Number of CPUs vs. Speed of CPUs We are in the process of sizing a new server for multiple Oracle instances. What factors are useful as input in determining how many CPUs and the relative speed of them? For example, do we want fewer, faster CPUs or do we want more, slower CPUs? Are there any good guidelines to determine what the number of CPUs should be? Thanks in advance - Lisa -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Buchan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Recompiling Invalid Objects after Table Rename
Hi all, We have an application which deletes a large number of rows from a table. It would be faster to simply insert the rows that we want to keep into a second table, drop the original table and then rename the second table to that of the one we have just dropped. The only downside that I can see is that all the source objects which reference the original table become invalid. We could: 1. Simply allow the source objects to be recompiled naturally overtime as they are reused (but with the possibility of a large number of invalid objects at any one time in the database and little control over when compilation is done). 2. Force recompilation following the drop table. However this would require logging all objects which would need recompilation. This is an additional step for any new development and would therefore the list of object would be prone to become inaccurate over time. (Could maybe do this automatically using USER_REFERENCES prior to the drop table? - still seems a bit clumsy) Does anyone have any comments on doing this? Many thanks - Bill. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Buchan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Recompiling Invalid Objects after Table Rename
Unfortunately we do not have the partitioning option available. I should mention that when I say I want to delete a large number of rows, the number of rows I want to keep is even larger. (Delete, say, 3 million rows, keep 7 million rows) so the option that some other have suggested of truncating the original table and copying the rows back would likely be much slower than recompiling the invalid objects. Thanks to all those who have suggested a way of doing so. - Bill. At 04:58 15/04/02 -0800, you wrote: $ORACLE_HOME/rdbms/admin/utlrp will recompile all invalid objects. Could you partition the table so that you only need to drop a partition instead of deleting rows? Jay [EMAIL PROTECTED] 04/15/02 08:23AM Hi all, We have an application which deletes a large number of rows from a table. It would be faster to simply insert the rows that we want to keep into a second table, drop the original table and then rename the second table to that of the one we have just dropped. The only downside that I can see is that all the source objects which reference the original table become invalid. We could: 1. Simply allow the source objects to be recompiled naturally overtime as they are reused (but with the possibility of a large number of invalid objects at any one time in the database and little control over when compilation is done). 2. Force recompilation following the drop table. However this would require logging all objects which would need recompilation. This is an additional step for any new development and would therefore the list of object would be prone to become inaccurate over time. (Could maybe do this automatically using USER_REFERENCES prior to the drop table? - still seems a bit clumsy) Does anyone have any comments on doing this? Many thanks - Bill. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Buchan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: 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: Bill Buchan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: OCP-Network Exam
Also, be sure of the correct terminology. Know what network service names, connection strings, etc. are. I went into the exam last week knowing exactly what these things LOOKED like in the config files but not what you're meant to call them (although it was possible to work them out from clearly wrong other choices...; being the only guy setting up these files here I've never had to call them anything in real life!) Have a look at the Oracle 8 Cram Session sheet although it doesn't go into enough detail for the exam - the Oracle manual seems to cover things pretty closely to the format of the exam (much more than for the other exams I reckon). - Bill. Oracle 8i OCP (at last :) - or at least waiting for the bit of paper!) At 07:18 08/04/02 -0800, you wrote: 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Buchan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
DEFERRED FK Constraint
Hi all, When testing behaviour of INITIALLY DEFERRED foreign key constraints I'm confused by the following behaviour and I'd appreciate any interpretation! I create the FK constraint on a column in a 1 million row table. This works OK - if I update individual entries to nonsense values, there is no error until I try to commit. So far so good. However, if I do an UPDATE which affects all rows, and then commit, the commit returns immediately (provided the UPDATE is to correct values, not nonsense). Why is there no delay whilst validating the million rows? (Certainly it takes several seconds to create the constraint in the first place). Any insight welcome! Thanks - Bill. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Buchan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Old Chestnut: Tablespace Fragmentation
I know this one has been done to death: use uniform extents to avoid fragmentation; multiple extents don't hurt (within limits). But what if: Data Warehouse, one big table on a single disk, full table (batch) scan, no concurrent transactions on the database (so no contention for the disk), no fragmentation at the file system level, initially empty buffer cache (startup), read-only operation so DBWR isn't doing anything on this disk. Basically I want to read one data file from end to end. Surely it would make sense to have the disk read moving smoothly from one end of the disk to the other rather than bouncing about all over the place as it may do with multiple extents randomly allocated. Any thoughts? Thanks - Bill. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Buchan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Old Chestnut: Tablespace Fragmentation
Thanks to everyone for their comments on this. You've convinced me. I'll go away and worry about something else instead now! - Bill. At 04:43 27/02/02 -0800, you wrote: I know this one has been done to death: use uniform extents to avoid fragmentation; multiple extents don't hurt (within limits). But what if: Data Warehouse, one big table on a single disk, full table (batch) scan, no concurrent transactions on the database (so no contention for the disk), no fragmentation at the file system level, initially empty buffer cache (startup), read-only operation so DBWR isn't doing anything on this disk. Basically I want to read one data file from end to end. Surely it would make sense to have the disk read moving smoothly from one end of the disk to the other rather than bouncing about all over the place as it may do with multiple extents randomly allocated. Any thoughts? Thanks - Bill. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Buchan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
inode density for Oracle filesystems
Is there any real benefit in using sparse inodes on a filesystem for Oracle datafiles? We originally had a system with few inodes because there would only be a few, large Oracle datafiles. However this has also rendered the free space left useless as a handy place to put a load of very static file archives.I'm tempted to rebuild the filesystem with more inodes. Any reason why not? (This is UFS on Solaris) Thanks for any info - Bill -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Buchan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: How Can I get A sequence Current Value in PROC
Yes, sequenceName.CURRVAL Note that this provides the current value for the session: i.e. the value that was last returned by NEXTVAL within that session. Therefore: 1. NEXTVAL must have been called at least once before in the session to define CURRVAL. 2. Even if NEXTVAL is called for the sequence in another session, CURRVAL in the original session will relate to the previous call to NEXTVAL in the original session - it does not increment. - Bill. At 04:50 26/11/01 -0800, you wrote: Hi when writing a program PROC sequenceName.NEXTVAL points to the next value of a sequence, is there anything like sequenceName.SOMETHING that returns current value of sequence. Thanks E. __ Do You Yahoo!? Yahoo! GeoCities - quick and easy web site hosting, just $8.95/month. http://geocities.yahoo.com/ps/info1 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: ehsan sinavalda INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Bill Buchan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Fwd: RE: Raw partition Vs File System
Hi Steve, Did you manage to contact Bert? Would you be willing to let us know what you think was wrong with the test anyway? Many thanks - Bill. Hi All, Does anyone have an email address for Bert? I looked for his email address when I first read that article a week ago, but did not find one. I think I know what was wrong with his test, but it is hard to be sure because he left out a lot of the details. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ - For DBAs @ http://www.christianity.net.au/ - For all -Original Message- Sent: Friday, 9 November 2001 21:20 To: Multiple recipients of list ORACLE-L Did anyone read Bert Scalzo's article in Pipeline Newsletter this month (http://www.revealnet.com/newsletter-v2/linux2.html)? He was benchmarking various filesystems under Linux (EXT2, EXT3, Reiser, IBM JFS and RAW). I was suprised (and so was he) to see worst performance on RAW devices. I presume this is a Linux-specific issue: can anyone suggest why RAW is so poor on Linux (or want to contest the results?!) Thanks - Bill. At 01:29 09/11/01 -0800, you wrote: If you're using a volume manager (veritias, or disk suite), then raw is pretty much just as easy as file systems. You could always do this incrementally - for example, high io stuff (typically redo, temp, possibly rollback) on raw, and all the rest of file systems etc. hth connor --- Vasu Ramasamy [EMAIL PROTECTED] wrote: Hello Gurus, I am trying to install Oracle Server in the environment as given below. I am in the processes of laying out Physical Database layout. I would like to know the pros and cons of going with Raw partition. The environment : Solaris 2.6 Oracle 8.1.7 Size of database - 60GB No. of tables - 3000 (approx.) Max size of few tables - 3 GB to 5 GB. Thanks for your help. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Vasu Ramasamy INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = Connor McDonald http://www.oracledba.co.uk (mirrored at http://www.oradba.freeserve.co.uk) Some days you're the pigeon, some days you're the statue __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Connor=20McDonald?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Intasys Billing Technologies Ltd. www.intasysbilling.com 74 Commercial Street, Commercial Quay, Leith, Edinburgh EH6 6LX tel (0)131 625 8200 fax (0)131 625 8201 email [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Buchan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Steve Adams INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note
RE: Need help rewriting SQL with TRUNC function
Wouldn't this introduce a time-of-day dependency on the result? Instead, how about: (trunc(sysdate) - erh.arrival_dt)) 6 AND (ie. strictly greater than six days ago) - Bill. At 08:30 15/11/01 -0800, you wrote: Cherie, Change the clause as follows orig == (trunc(sysdate) - trunc(erh.arrival_dt)) = 7 AND new == erh.arrival_dt = sysdate - 7 AND Hope this helps! Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, November 15, 2001 10:00 AM To: Multiple recipients of list ORACLE-L I think that the following statement is keeping this application from using an index in my cost-based execution plan. This is an 8.0.4 database so I don't have the option of creating a function-based index. I'm not that great with SQL. Can anyone help me rewrite this statement so that I don't need to use the TRUNC function on this column in blue? (trunc(sysdate) - trunc(erh.arrival_dt)) = 7 AND Thanks, Cherie Machler Oracle DBA Gelco Information Network -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Buchan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: ORA-01720 while trying to grant select on a view
You need to: grant select on sys.v_$instance to GCSS with grant option; This will allow GCSS to grant select on views based on v_$instance to other users. - Bill. At 01:35 09/11/01 -0800, you wrote: Hi DBA Gurus, I am getting the below error while trying to grant select privileges on a view owned by a user by name GCSS to another user by name etldev: ORA-01720: grant option does not exist for 'SYS.V_$INSTANCE' I even granted select on sys.v_$instance to etldev. Still the problem persists. What could be the problem? Anybody can throw some light on this? Thanks and Regards, KR -- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Buchan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Import questions
If you want the objects to end up in the same tablespaces, then the names must be exactly the same (otherwise the default tablespace is used). If the filesystem and layout on box x is the same as box y then the full import will create the tablespaces using the datafile specs. in the dump file. If the filesystem is different then you should pre-create the tablespaces you need before doing the import. It is probably best to do this anyway (even if the filesystem matches) as you can be sure that the datafiles are where you want them to be and there will be no surprises like running out of disk space. When you do this the import will not try to create the tablespaces, since ones with the same name already exist. - Bill. At 11:40 08/11/01 -0800, you wrote: Hi Is it necessary to create exactly same tablespace name when I want to FULL import of database structure? Let i have one instance A on x box. I have created one instance A on Y box I want to full import structore of database on box Y. Thanks Seema _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seema Singh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Buchan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
SEGTYPE=DATA in v$sort_usage
Hi List, I noticed a couple of entries in v$sort_usage with SEGTYPE = DATA when a developer was doing an insert - confirmed using SESSION_ADDR. I'm familiar with SORT and HASH appearing here but what does DATA mean? Any why is the temporary tablespace being used at all. The insert was INSERT INTO table SELECT * FROM (same)table WHERE ROWNUM 1000 (i.e. No sorting/joining etc.). However, we've been unable to recreate the behaviour by running the insert again (something else was happening at the time?). Not critical - just curious! - Bill. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Buchan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 Auditing
Does anyone have any recommendations on auditing data changes on several tables? I want to keep before and after data along with username/timestamp. I'm thinking of a DML trigger on each table and each with its own audit table? Or what about a single audit table (using TO_CHAR to convert all non-character data into the BEFORE and AFTER columns; only CHAR, VARCHAR2, DATE and NUMBER datatypes are used). That should be slightly more generic. Either way it seems a bit messy. Anybody got a better idea? (This audit trail has to be readily visible to the users - ie. a table - Log Miner isn't really practical). Many thanks for any suggestions. - Bill. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Buchan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
WHERE CURRENT OF Question
Why does the following work? I open a cursor with FOR UPDATE OF COLUMN_A and then do an update of COLUMNB, WHERE CURRENT OF the cursor. Surely I shouldn't be allowed to do that? (Ora 8.1.7.2) Any insight appreciated! Thanks - Bill. SQLWKS create table test_table 2 ( 3 column_anumber, 4 column_bnumber 5 ) 6 Statement processed. SQLWKS insert into test_table values (1,2) 2 1 row processed. SQLWKS insert into test_table values (2,3) 2 1 row processed. SQLWKS insert into test_table values (3,4) 2 1 row processed. SQLWKS declare 2 cursor c1 is select * from test_table for update of column_a; 3 begin 4 for i in c1 loop 5 update test_table set column_b = column_b * 2 6 where current of c1; 7 end loop; 8 end; 9 Statement processed. SQLWKS select * 2 from test_table 3 COLUMN_A COLUMN_B -- -- 1 4 2 6 3 8 3 rows selected. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Buchan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Any experiences with JDBC?
We're thinking of moving our Java application away from proprietary connectivity to Oracle to using JDBC. The application will eventually be deployed to hundreds of client workstations. Anyone using JDBC care to comment on their experiences or what we need to know at the planning stage? We're interested (of course!) in stability, reliability and performance. Many thanks for any insights! Thanks - Bill. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Buchan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Platform
I presume you mean utl_file, not dbms_file. Doesn't this need the utl_file parameter set up with the available directories - could you just check to see if this parameter includes c:\log or /log - Bill. At 05:10 26/09/01 -0800, you wrote: Hello how is it possible in PLSQL to decide on which operating system (NT/LINUX)is server running? I would like to automatically specify directory where output from dbms_file will go. On nt I need something like c:\log and on unix I would like to have something like /log . Is there any view where I can find this information about OS? Thank you for responce Ivo -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Libal, Ivo INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Intasys Billing Technologies Ltd. www.intasysbilling.com 74 Commercial Street, Commercial Quay, Leith, Edinburgh EH6 6LX tel (0)131 625 8200 fax (0)131 625 8201 email [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Buchan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Upgrade to 8.1.7.2 on Linux -- an interesting happening
Yes - same thing - just applied the 8172 patch yesterday to two 817 Standard Edition servers on SuSE 7.2 and they both now indicate Enterprise Edition with all the options enabled. - Bill. At 14:20 20/09/01 -0800, you wrote: Today we upgraded one of our 8.1.7.0.1 _STANDARD EDITION_ distributions on a test server to 8.1.7.2. An interesting thing happened. After we brought up the test database we had running on the machine, it came up indicating that we were running the _ENTERPRISE EDITION_ of Oracle. A query of v$option showed that we were, indeed, running the Enterprise Edition of Oracle. Just for grins, we restored the old 8.1.7.0.1 distribution, brought up the old database, and it was running under the Standard Edition. Has anyone else done an upgrade to 8.1.7.2? Anyone notice the same thing? We think it's great of Oracle to provide us a patch allowing us to seamlessly switch to the Enterprise Edition. Somehow, though, I don't think that's what they're trying to do here... --Walt Weaver Bozeman, Montana -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Weaver, Walt INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Bill Buchan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Lots and lots of redo logs
Out of curiosity and as a test I scrubbed my 16Mb RAID1 array and set it up again with 5 x 50Mb redo groups. I ran the following script to generate lots of redo: begin for i in 1..1500 loop insert into test_table values (mod(i,10)); delete from test_table where anumber = (mod(i+1,10)); commit; end loop; end; ARCHIVELOG mode was on and the archive destination (on another disk) was initially empty. There were 239 log switches and the test completed in 2 hours 56 minutes. I then added a further 250 50Mb redo groups, cleared the archive destination and ran the test again. Again it completed in exactly 2 hours 56 minutes, this time 240 log switches (difference of 1, I guess due to whereabouts in the first log it was when the test started). v$session_wait shows permanent log file parallel write for LGWR while the test runs. Any suggestions why I failed to see any slow down when I (almost) filled the disk with redo groups? (This server wasn't doing anything else and the time of the test). The RAID1 array had a Linux ext2 filesystem on it and I had disabled the write cache. Thanks again, - Bill. At 08:40 14/09/01 -0800, you wrote: !! Please do not post Off Topic to this List !! Generally IDE shows this problem much more than scsi, but on IDE you can see as much as 50% performance degrading, I haven't really tested the difference. If you put the logs on, then fill the rests of the disks with a empty file of that size, you can make sure that the end of the disk is filled with junk and the logs will never pull blocks from that area. Do not criticize someone until you walked a mile in their shoes, that way when you criticize them, you are a mile a way and have their shoes. Christopher R. Spence Oracle DBA Phone: (978) 322-5744 Fax:(707) 885-2275 Fuelspot 73 Princeton Street North, Chelmsford 01863 -Original Message- Sent: Friday, September 14, 2001 12:15 PM To: Multiple recipients of list ORACLE-L !! Please do not post Off Topic to this List !! How big a performance issue is the location of the log file on disk? Even if I create the minimum of 2 archive log groups, how can I be sure the controller hasn't put these on the outer platters anyway? Since these are log files then the writes will be sequential anyway (does that make a difference?) - showing my hardware ignorance here! As you said: But in my opinion here is my priorities. 1. Recoverability 2. Performance I would have thought that having lots of archive log groups (normally) not overwritten for a few days in addition to properly archived logs would boost my recoverability with negligible impact on performance. (Incidentally the MAXLOGFILES maximum value is 255, on Linux anyway). Thanks - Bill. At 07:10 14/09/01 -0800, you wrote: !! Please do not post Off Topic to this List !! It is very common for people to see all that free space and want to fill it. Avoid the desire, fill it with a blank file. Disks are cheap and if you store things on the outer platters performance will suffer. Redo logs and other things in oracle waste disk space as drives get bigger, it is unavoidable. But using the space for something totally defeats the purpose of good configurations. Do not criticize someone until you walked a mile in their shoes, that way when you criticize them, you are a mile a way and have their shoes. Christopher R. Spence Oracle DBA Phone: (978) 322-5744 Fax:(707) 885-2275 Fuelspot 73 Princeton Street North, Chelmsford 01863 -Original Message- Sent: Friday, September 14, 2001 10:35 AM To: Multiple recipients of list ORACLE-L !! Please do not post Off Topic to this List !! Hi Thanks. That's a very good point. I agree that ARCHIVELOG mode will be needed. However, I still have this big disk just for redo logs, so I'm tempted to fill it anyway. This will be a sort of supplementary backup in case the archive disk (+ database disks) crash before the backup. Of course it is only sort of because, as you say, a runaway process will cycle the logs if it generates lots of redo. However, this seems better use of the disk space than just having a few log groups and leaving the rest of the array empty and unused. Unless there are any other implications? Thanks - Bill. Hi Yes you can untill the day that you have this runaway process that creates 20Gb of redo and than crashes your database 5 minutes before the daily offline backup should kick in. But you are only mirroring, why not put your database in archivelogmode. You do not have so much redo per day that your disks/archiver can't handle it. Now if your archive directory is full oracle won't crash, but just stop untill you free up some space (I believe this is the behaviour anyway). Jack -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Buchan
Lots and lots of redo logs
!! Please do not post Off Topic to this List !! I am planning setting up a new database with the redo logs on RAID 1 array (mirror). The amount of space available on the array is 16Gb and only the redo logs will be on there. The application will generate 2Gb of redo per day and will be backed up (cold) each night to tape. If I set up enough groups (MAXLOGFILES) such that the whole array is full of logs (each probably 50Mb in size) can I safely run this in NOARCHIVELOG mode and still expect ARCHIVELOG mode type complete recovery? The application will not overwrite a log till several days (and several backups) after it was last used, and the logs are protected by RAID. Recovery requirement is only to be able to get back to the current state (say that last 24 hours max.) before failure, not recover way back in time. Are there any other issues (eg. performance) that I should consider? Any comments much appreciated. Thanks - Bill. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Buchan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Lots and lots of redo logs
!! Please do not post Off Topic to this List !! Hi Thanks. That's a very good point. I agree that ARCHIVELOG mode will be needed. However, I still have this big disk just for redo logs, so I'm tempted to fill it anyway. This will be a sort of supplementary backup in case the archive disk (+ database disks) crash before the backup. Of course it is only sort of because, as you say, a runaway process will cycle the logs if it generates lots of redo. However, this seems better use of the disk space than just having a few log groups and leaving the rest of the array empty and unused. Unless there are any other implications? Thanks - Bill. Hi Yes you can untill the day that you have this runaway process that creates 20Gb of redo and than crashes your database 5 minutes before the daily offline backup should kick in. But you are only mirroring, why not put your database in archivelogmode. You do not have so much redo per day that your disks/archiver can't handle it. Now if your archive directory is full oracle won't crash, but just stop untill you free up some space (I believe this is the behaviour anyway). Jack -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Buchan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Lots and lots of redo logs
!! Please do not post Off Topic to this List !! How big a performance issue is the location of the log file on disk? Even if I create the minimum of 2 archive log groups, how can I be sure the controller hasn't put these on the outer platters anyway? Since these are log files then the writes will be sequential anyway (does that make a difference?) - showing my hardware ignorance here! As you said: But in my opinion here is my priorities. 1. Recoverability 2. Performance I would have thought that having lots of archive log groups (normally) not overwritten for a few days in addition to properly archived logs would boost my recoverability with negligible impact on performance. (Incidentally the MAXLOGFILES maximum value is 255, on Linux anyway). Thanks - Bill. At 07:10 14/09/01 -0800, you wrote: !! Please do not post Off Topic to this List !! It is very common for people to see all that free space and want to fill it. Avoid the desire, fill it with a blank file. Disks are cheap and if you store things on the outer platters performance will suffer. Redo logs and other things in oracle waste disk space as drives get bigger, it is unavoidable. But using the space for something totally defeats the purpose of good configurations. Do not criticize someone until you walked a mile in their shoes, that way when you criticize them, you are a mile a way and have their shoes. Christopher R. Spence Oracle DBA Phone: (978) 322-5744 Fax:(707) 885-2275 Fuelspot 73 Princeton Street North, Chelmsford 01863 -Original Message- Sent: Friday, September 14, 2001 10:35 AM To: Multiple recipients of list ORACLE-L !! Please do not post Off Topic to this List !! Hi Thanks. That's a very good point. I agree that ARCHIVELOG mode will be needed. However, I still have this big disk just for redo logs, so I'm tempted to fill it anyway. This will be a sort of supplementary backup in case the archive disk (+ database disks) crash before the backup. Of course it is only sort of because, as you say, a runaway process will cycle the logs if it generates lots of redo. However, this seems better use of the disk space than just having a few log groups and leaving the rest of the array empty and unused. Unless there are any other implications? Thanks - Bill. Hi Yes you can untill the day that you have this runaway process that creates 20Gb of redo and than crashes your database 5 minutes before the daily offline backup should kick in. But you are only mirroring, why not put your database in archivelogmode. You do not have so much redo per day that your disks/archiver can't handle it. Now if your archive directory is full oracle won't crash, but just stop untill you free up some space (I believe this is the behaviour anyway). Jack -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Buchan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Lots and lots of redo logs
!! Please do not post Off Topic to this List !! Excellent. Thanks for the information; maybe I won't fill my whole disk with redo now! - Bill. At 08:40 14/09/01 -0800, you wrote: !! Please do not post Off Topic to this List !! Generally IDE shows this problem much more than scsi, but on IDE you can see as much as 50% performance degrading, I haven't really tested the difference. If you put the logs on, then fill the rests of the disks with a empty file of that size, you can make sure that the end of the disk is filled with junk and the logs will never pull blocks from that area. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Buchan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 changes (8.1.6 - 8.1.7)
Help! Can anyone throw any light on the following: I run the following: declare cursor c1 is select to_char(nvl(a.foo,0),'999') from (select '0' foo from dual) a; begin null; end; On Oracle 8.1.6 (Solaris and Linux platforms): Statement Processed On Oracle 8.1.7 (Solaris and Linux platforms): ORA-06550: line 3, column 9: PLS-00307: too many declarations of 'TO_CHAR' match this call ORA-06550: line 3, column 2: PL/SQL: SQL Statement ignored Additionally, if I remove the NVL(), then it also fails, as above, on 8.1.6. I'd be grateful for any pointers. Thanks - Bill. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Buchan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Dumb MTS Concepts Question
I've not used MTS before and am just reading up on it. Anyone help me out with the following? In a dedicated server environment each PGA has a UGA where things like cursor state and session data is held. In a MTS environment, this UGA is in the large pool in the SGA instead. This is just moving memory resources around, not reducing them? How does MTS manage to support more concurrent users - I can't see how the actual UGA gets any smaller, cursor state etc. will still need to be preserved whether or not the session is active. Thanks - Bill -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Buchan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
LinuxFS for Oracle
I am about to attempt an Oracle installation on SuSE Linux 7.2. I need to pick a file-system: ext2 or reiserfs. Does anyone have any comments to share? All I can see from the docs is: Oracle8i Server must be able to verify that file writes have been made to disk. File systems that do not support this verification are not supported for use with Oracle databases, although Oracle software can be installed on them. My knowledge of how the 2 filesystems is 0. I'd be very grateful for any advice and/or interpretation. Thanks - Bill. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Buchan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
