Re: table partitions
Your high value for each partition can just be the beginning of every month. For example: CREATE TABLE ACCOUNTS ( STATEMENT_DATE DATE NOT NULL, ACCOUNT_NUMBER VARCHAR2(8)NOT NULL, BILLING_CYCLE VARCHAR2(2)NOT NULL,
Re: table partitions
Resending, since my message was truncated...
Your high value for each partition can just be the beginning of every month. For
example:
CREATE TABLE ACCOUNTS
(
STATEMENT_DATE DATE NOT NULL,
ACCOUNT_NUMBER VARCHAR2(8)NOT NULL,
BILLING_CYCLE VARCHAR2(2)NOT NULL,
...
PARTITION BY RANGE (STATEMENT_DATE)
(
PARTITION ACCOUNTS_JAN02 VALUES LESS THAN (TO_DATE(' 2002-02-01', '-MM-DD))
TABLESPACE BILH_DATA_01,
PARTITION ACCOUNTS_FEB02 VALUES LESS THAN (TO_DATE(' 2002-03-01', '-MM-DD))
TABLESPACE BILH_DATA_02,
PARTITION ACCOUNTS_MAR02 VALUES LESS THAN (TO_DATE(' 2002-04-01', '-MM-DD))
TABLESPACE BILH_DATA_03
);
Jay
[EMAIL PROTECTED] 12/15/03 07:34PM
create table aadedupekeys
(
file_id number,
rundate date,
pk number(10),
dk varchar2(128),
constraint aadedupekeys_pk primary key (file_id,rundate)
)
;
Is there anyway to partition a table (above) in months e.g.
('January','Februray'...). I want to use the rundate and list partition it
using the months of the year. How can I do that without actually having to
create another column in the table that only contains the month it belongs
to? I cannot seem to use a function on the rundate field as part of the
partition syntax to generate a month to partition the data.
Thanks for your help,
Rick Stephenson
**DISCLAIMER
This e-mail message and any files transmitted with it are intended for the use of the
individual or entity to which they are addressed and may contain information that is
privileged, proprietary and confidential. If you are not the intended recipient, you
may not use, copy or disclose to anyone the message or any information contained in
the message. If you have received this communication in error, please notify the
sender and delete this e-mail message. The contents do not represent the opinion of
DE except to the extent that it relates to their official business.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jay Hostetter
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).
SAN Comparison Question
One of our hardware guys is seeking an opinion on SANs. He is comparing the Hitachi Thunder 9500 to the HP EVA 5000. Does anybody have any pros or cons to offer for either one? Good or bad experiences? Thank you, Jay **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jay Hostetter 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).
3rd Party Oracle Licenses
We are purchasing a software package from a vendor. The vendor states that the package includes sufficient Oracle licenses. Since I'm supposed to keep on top of our licensing costs, I'm trying to make sure that there are no surprises down the road - such as additional Oracle support fees or Oracle claiming that we don't have this new box licensed, etc. How can the vendor prove that they are providing a license? When I asked them for some type of proof, they forward the OLSA to me, which is basically generic - it doesn't tell me if the license is SE, EE, SE One, perpertual, term, CPU, Named User, etc. Any thoughts or do I just take their word for it? Thanks, Jay **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jay Hostetter 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: Great story! Metalink down caused inhouse crash
No wonder MetaLink is slow - it's too busy serving up graphics to customer installations. [EMAIL PROTECTED] 11/07/03 11:44AM At my last project, we were putting in OraFin and we had a team of Oracle consultants doing up the front-end setup stuff (populating screens, etc). Well, one day, I get this panicked call that the system was down. Well, of course, that was silly. The system was up just fine, thank-you-very-much, and the database was fine, and even my version of the app was just fine-and-dandy. This reminded me of the old lightbulb joke (I got one over here just like it and it works fine for me), so I tried to log in as the user from my app, and it worked fine. I went upstairs, and sure-nuf, she couldn't log in and couldn't log in. She *swore* she had never logged onto Unix and had never changed *anything* except data, and it was working fine til 9:15 then boom! Well, after awhile, I ran out of ideas and tried to log a TAR, and, as you can tell from the subject line, Metalink was down. It was down all morning, and started coming up slowly around 1pm. When I was finally able to enter the text of the TAR, I was just about to log the TAR and my phone rang with a Gee, you fixed it, thank you!. Well, *that* was a bit too much of a coincidence for me. Well, the upshot of the whole thing was that, for whatever reason, in their setups, they wanted the actual Oracle splash page to come up instead of the one that ships with OraFin and one of their in-house experts who had since disappeared had hardcoded in the server that hosted Metalink into the app. And the really great thing was when I took out the reference to that machine leaving the default splash screen as was, they didn't even notice. What a day that was! Bambi. **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jay Hostetter 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: Uncle Larry, wake up!!!
That darn NULL process was always *hogging* the CPU. [EMAIL PROTECTED] 11/07/03 02:09PM Nope, we would be using IMS and/or CICS DL/I. On the beginning of my career, when I was a junior programmer using completely outdated, badly overused language (COBOL, for short) an ancient IBM 3084 with only 32M RAM was able to service 800+ users, as long as there weren't too many TSO users. I'd hate to explain what CICS, MVS and TSO are (or were), but those things were way more optimal and way faster then any other database I've ever seen, and that was happenening in the year 1986. At the end of 1986, I was reassigned to check out that wonderful new box with OS that has held so much promiseVAX 3900 with VMS 4.6. Does anybody still remember the NULL.COM and NULL process? On 11/07/2003 01:44:35 PM, [EMAIL PROTECTED] wrote: yes, but as you all know by now ( or should know ), having the best technology does not make you a market leader. MySQL will walk all over PostgreSQL, regardless of the superiority of the latter. Otherwise, we would all be using RDB, wouldn't we? Jared Jesse, Rich [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/07/2003 06:45 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Uncle Larry, wake up!!! Let's not forget PostgreSQL, arguably more enterprise-ready than MySQL, and now with clustering on Linux: http://www.open-mag.com/0182533982.shtml Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA -Original Message- Sent: Thursday, November 06, 2003 8:04 PM To: Multiple recipients of list ORACLE-L Can I say I told you so! now? ;) **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jay Hostetter 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: Archivelog - Disk space issue.
Your backup script should be deleting archive logs after they are backed up. With
RMAN, you could do the following, just to backup and delete your archivelogs:
run
{
allocate channel ch1 type disk format '/bkup4/oracle/%d/arc_s%s_p%p_%t';
set limit channel ch1 kbytes=100; # Limit sets to 1 Gb.
backup archivelog all delete input;
}
If you're not using RMAN, delete the logs that have been backed up by your script.
If you're not backing them up, you're going to have recovery issues.
Jay
[EMAIL PROTECTED] 10/15/03 09:44AM
Hello Gurus,
Have some disk space issues, so looking for ways to cleanup.
Please confirm that my thinking is correct.
I run the command and example output below:-
SQL archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destinationC:\Oracle\oracle.
Oldest online log sequence 2334
Next log sequence to archive 2338
Current log sequence 2338
I can write a script (Perl) to remove all archive files 2334?
Much appreciate any input or confirmation.
Regards
Denham Eva
Oracle DBA
Linux like TeePee... No Windows, No Gates and Apache inside!
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Denham Eva
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)
**DISCLAIMER
This e-mail message and any files transmitted with it are intended for the use of the
individual or entity to which they are addressed and may contain information that is
privileged, proprietary and confidential. If you are not the intended recipient, you
may not use, copy or disclose to anyone the message or any information contained in
the message. If you have received this communication in error, please notify the
sender and delete this e-mail message. The contents do not represent the opinion of
DE except to the extent that it relates to their official business.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jay Hostetter
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: Financials and APPS password
April, We lost this battle with our developers - they have the password, along with strict instructions to behave. Nobody else should have the password to any of the schemas (APPS, GL, INV, etc.). We create logins for users that need them and grant the necessary rights to objects. As you know, APPS can do just about anything in the database, so you're asking for trouble if you let the whole company in there. Chances are you already have some objects in that schema like MICROSOFTDTPROPERTIES. Jay [EMAIL PROTECTED] 10/15/03 08:39AM Okay, anyone using Financials... E-Business suite... Oracle 11i... whatever you want to call it... I am trying to apply SOME kind of security to my databases. It appears that it is critical for everyone to be able to access production using the APPS id Finance and accounting people, developers, everyone. What does everyone else do in their setups? The newest reason is the need to run the new Mass Additions Trace which apparently requires that you use the apps id. We have found a way to set up any user with a read only version of what APPS has (since they have to be able to compile reports in production and access production data live rather than a month old clone), but Oracle says that you need to run Mass Additions Trace as apps. Does anyone let the entire company have the production apps user's password? April Wells Oracle DBA/Oracle Apps DBA Corporate Systems Amarillo Texas /\ / \ / \ \ / \/ \ \ \ \ Few people really enjoy the simple pleasure of flying a kite Adam Wells age 11 The information contained in this communication, including attachments, is strictly confidential and for the intended use of the addressee only; it may also contain proprietary, price sensitive, or legally privileged information. Notice is hereby given that any disclosure, distribution, dissemination, use, or copying of the information by anyone other than the intended recipient is strictly prohibited and may be illegal. If you have received this communication in error, please notify the sender immediately by reply e-mail, delete this communication, and destroy all copies. Corporate Systems, Inc. has taken reasonable precautions to ensure that any attachment to this e-mail has been swept for viruses. We specifically disclaim all liability and will accept no responsibility for any damage sustained as a result of software viruses and advise you to carry out your own virus checks before opening any attachment. **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jay Hostetter 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: Unintentional Humor
You mean the ls on the $ORACLE_HOME/bin directory (imported directly into the documentation without editing)? I always wanted to know how to get those secret ckpcch.ora and ncomp.log utilities to work. Jay [EMAIL PROTECTED] 10/06/03 02:39PM OK, well I thought it was funny. Doing a google search on some Oracle stuff, I found the following page. http://www.dba-oracle.com/bp/bp_book6_utils.htm Scroll down a bit? Do you see the funny part? Jared **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jay Hostetter 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: COBOL TO ORACLE
What OS are you using? I assume the COBOL app resides on the same box as the database? When performance is poor, can you see what the top processes are? As for the merits of COBOL, our COBOL apps hum along very nicely, processing millions of records a month. Also, I like having conversations with developers that tell me I connect to the database and run this SQL statement... vs getting into some convoluted discussion about objects, object servers, ODBC formatted SQL, etc. Perhaps I'm blissfully ignorant about some aspects of programming, but I can tell you that our COBOL programs that follow the KISS method give us the least amount of headaches. Jay [EMAIL PROTECTED] 10/01/03 03:09PM Then perhaps you can help me . . . We are suffering through a Pro*Cobol / Oracle on the mainframe implementation for a newly developed in house application. We have a mainframe with 1.7GB - 2GB REAL memory. 4 CPU machine - we have two logical CPUs in our LPAR We have 8 instances running (each with at least 300MB SGA) We are having major performance problems when people start doing a lot of work on these instances. The Mainframe SYSADMIN insists that there is NO swapping, NO paging occuring. There is no problem because each instance can have 2GB VIRTUAL memory and this is fine and dandy because this is how the mainframe works. Did you ever use Oracle on a mainframe? Did you come up with guidelines for minimum requirements? (like in the UNIX install guides / NT install guides, you need X RAM, etc) Thanks Babette -Original Message- Sent: 2003-09-30 5:15 PM To: Multiple recipients of list ORACLE-L On Tue, 2003-09-30 at 16:19, Stephane Paquette wrote: Like Thomas Day said, Oracle is an rdbms and COBOL a programming language. COBOL *** WAS *** a programming language. Horse *** WAS *** basis of transport. You should have used past tense, Stephane. I'm not really that partial when it comes to horses, but having suffered COBOL, I would really leave it in the ancient past, together with Spanish Inquisition and crucifiction as a viable capital punishment. **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jay Hostetter 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: OEM
In my experience, OEM doesn't work well with any version. [EMAIL PROTECTED] 08/20/03 12:59PM Does OEM 9.2.0.1 works well with 8.1.7.4 database ? or in general with all versions of databases (7.3,8.0,8i,9i ) ? Does it recognise underline db version and sends commands appropriately . Any Idea ? thanks, -ak **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jay Hostetter 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: Partitioning
If it has a date column, partition by that column into whatever makes sense (weeks,
months, etc.)
CREATE TABLE YOURTABLE
(
YOURDATE DATE NOT NULL,
YOURCOLUMN NUMBER
)
PARTITION BY RANGE (YOURDATE)
(
PARTITION YOURTABLE_JUN03 VALUES LESS THAN (TO_DATE(' 2003-07-01', '-MM-DD')
TABLESPACE DATA_06,
PARTITION YOURTABLE_JUL03 VALUES LESS THAN (TO_DATE(' 2003-08-01', '-MM-DD)
TABLESPACE DATA_07,
PARTITION DETAILS_AUG03 VALUES LESS THAN (TO_DATE(' 2003-09-01', '-MM-DD')
TABLESPACE DATA_08
);
Jay
[EMAIL PROTECTED] 08/12/03 12:04PM
I have worked with partitioning before but have yet encountered the following
challenge -
The table we are trying to partition is a large table with hundreds of millions of
rows, which is ok. But it does not have a month column, although it has dates. I would
like to partition by month because this table contains years of data and partitioning
by days will result in thousands of partitions. Of course we can add a month column
but I think that will require extensive downtime which we can't afford and I suspect
it will cause row-chaining as well. So anybody care to share with me any other
options/suggestions?
TIA
Dennis
**DISCLAIMER
This e-mail message and any files transmitted with it are intended for the use of the
individual or entity to which they are addressed and may contain information that is
privileged, proprietary and confidential. If you are not the intended recipient, you
may not use, copy or disclose to anyone the message or any information contained in
the message. If you have received this communication in error, please notify the
sender and delete this e-mail message. The contents do not represent the opinion of
DE except to the extent that it relates to their official business.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jay Hostetter
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: How to take sql*loader trace
Issue this command from SQL*Plus prior to starting your SQL*Loader session: ALTER SYSTEM SET max_dump_file_size = unlimited; ALTER SYSTEM SET timed_statistics = true; ALTER SYSTEM SET EVENTS '10046 trace name context forever, level 12'; Then, issue this command after the session has started: ALTER SYSTEM SET EVENTS '10046 trace name context off'; This will trace all NEW sessions that start between the two SET EVENTS commands. Jay [EMAIL PROTECTED] 08/11/03 10:39AM Hi All, I'm loading a set of data into one of my schema. And wanted to take the trace files also. I tried the follwoing. 1. Identified the sid,serila# for the sql*loader session. 2. used the follwoing, exec sys.dbms_system.set_sql_trace_in_session(19,11250,TRUE); but this is not generation any trace files. But if I trun my system to sql_trace=TRUE, I'm able to collect the trace details. I don't want to do this. I want to take the trace only for the sql*loader session. How do i do that. TIA, Senthil. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Senthil Kumar 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) **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jay Hostetter 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).
Linked Server and remote TNSNAMES
We have a SQL Server database that needs to link to an Oracle database. I have been able to successfully setup this linked server entry in SQL Server, but only when I am using a local TNSNAMES.ORA. We typically have all of our Win2000 machines point to a central TNSNAMES.ORA file that is on a file server using ifile (in the local tnsnames). For example: ifile=//cluster01\apps\oracle\sqlnet\tnsmaster.ora this works for SQL*Plus only Using the above ifile entry in the local TNSNAMES file, I am able to connect to databases from this box using SQL*Plus. However, my SQL Server linked server reports a ORA-12154: TNS:could not resolve service name. If I change my ifile entry to use a local tnsnames file, I no longer get this error from SQL Server. For example: ifile=c:\oracle\ora920\network\admin\tnsmaster.ora this works for SQL*Plus and SQL Server SQL Plus works for both scenarios. SQL Server works only for the second scenario. What has me stumped is that SQL*Plus works, but SQL Server does not. I think it might be some type of network rights issue, but I have not confirmed it. Does anybody use SQL Server to link to an Oracle database, using a TNSNAMES file that is remote? Thanks, Jay **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jay Hostetter 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: ** table triggers
It sounds like this column belongs in a table that is the parent of tables A and B. [EMAIL PROTECTED] 07/30/03 02:09PM You can't. An update statement is what fires the trigger. Trigger cannot see where does the statement come from. May be you should rethink the overall design of the application? On 2003.07.30 13:59, A Joshi wrote: Hi, I have a two tables A and B. Both have a field expected_delay_now and when it gets updated by a user in either table I want update it in corresponding row in the other table. However when this update is as a result of a trigger (and not user updated) how do I skip the update thru the trigger??? Thanks - Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software -- Mladen Gogala Oracle DBA **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jay Hostetter 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: Maximum Open Cursors on Insert Trigger
Thank you for the replies. It turn out to be a code issue. The developer added olecmd.dispose(), which fixed the problem. [EMAIL PROTECTED] 07/28/03 11:54AM There are some relevant notes on MetaLink that may be of help. Search on 'visual basic ora-1000' Jared On Monday 28 July 2003 07:54, Jay Hostetter wrote: We have a developer that is inserting a large number of records using a VB program. An insert trigger exists on the table. This trigger checks a parent table for records. I know this trigger really is not needed, since a Foreign Key exists to enforce referential integrity, so I plan to disable it (furthermore, it does a SELECT FOR UPDATE, which doesn't make sense). However, I'm trying to understand why the developer keeps getting ORA-01000: maximum open cursors exceeded during the inserts. I've bumped OPEN_CURSORS up to 1000. Is there something unique to VB that could be causing this problem? I've done mass inserts before on tables that have triggers without running into this type of problem. I can't see any problem in the trigger logic, since the cursor is always closed. This is a 9.2.0.3 database on Tru64. Thanks, Jay The source for the trigger is: .. **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jay Hostetter 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).
Maximum Open Cursors on Insert Trigger
We have a developer that is inserting a large number of records using a VB program. An insert trigger exists on the table. This trigger checks a parent table for records. I know this trigger really is not needed, since a Foreign Key exists to enforce referential integrity, so I plan to disable it (furthermore, it does a SELECT FOR UPDATE, which doesn't make sense). However, I'm trying to understand why the developer keeps getting ORA-01000: maximum open cursors exceeded during the inserts. I've bumped OPEN_CURSORS up to 1000. Is there something unique to VB that could be causing this problem? I've done mass inserts before on tables that have triggers without running into this type of problem. I can't see any problem in the trigger logic, since the cursor is always closed. This is a 9.2.0.3 database on Tru64. Thanks, Jay The source for the trigger is: CREATE OR REPLACE TRIGGER TOPAS.TI_CABLE_PAIRS BEFORE INSERT ON CABLE_PAIRS FOR EACH ROW DECLARE INTEGRITY_ERROR EXCEPTION; ERRNOINTEGER; ERRMSG CHAR(200); DUMMYINTEGER; FOUNDBOOLEAN; -- DECLARATION OF INSERTCHILDPARENTEXIST CONSTRAINT FOR THE PARENT EXCHANGES CURSOR CPK1_CABLE_PAIRS(VAR_EXCHANGE VARCHAR) IS SELECT 1 FROM EXCHANGES WHERE EXCHANGE = VAR_EXCHANGE AND VAR_EXCHANGE IS NOT NULL FOR UPDATE OF EXCHANGE; BEGIN -- PARENT EXCHANGES MUST EXIST WHEN INSERTING A CHILD IN CABLE_PAIRS IF :NEW.EXCHANGE IS NOT NULL THEN OPEN CPK1_CABLE_PAIRS(:NEW.EXCHANGE); FETCH CPK1_CABLE_PAIRS INTO DUMMY; FOUND := CPK1_CABLE_PAIRS%FOUND; CLOSE CPK1_CABLE_PAIRS; IF NOT FOUND THEN ERRNO := -20002; ERRMSG := 'Parent does not exist in EXCHANGES. Cannot create child in CABLE_PAIRS.'; RAISE INTEGRITY_ERROR; END IF; END IF; -- ERRORS HANDLING EXCEPTION WHEN INTEGRITY_ERROR THEN RAISE_APPLICATION_ERROR(ERRNO, ERRMSG); END; / **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jay Hostetter 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: how to schedule a job every alternate sundays in unix cron
I'm not a Unix expert, but one thing you could do is schedule the script to run every Sunday, then within the script add some logic so that the script completes every other Sunday. For example, in ksh: # Check to see if this script ran last Sunday. if [[ -a ran_last_week.dat ]]; then rm ran_last_week.dat exit else # Create a file to signal that the script ran. touch ran_last_week.dat rest of script Jay [EMAIL PROTECTED] 07/16/03 09:09AM Hello All, OS: Solaris 2.8 I have to run a backup script in every alternate Sunday. I cant find a way to submit it in cron. Can somebody help me how to setup a job to run alternate Sundays in unix cron . Thanks and Regards, Srinivas __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: kommareddy sreenivasa 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). **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jay Hostetter 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: ROWNUM -- HOW ARE ROWS SELECTED?
Mary Ann, Assume that the rows are selected in random order. Primary Keys and Order By exist for ordering and qualifying your data. If you just select * from EMP2, most likely the rows willl come back in the order that they were inserted, but this is not necessarily true, especially if data has been deleted from the table. Add a column that is a sequence number or a date/time stamp, then order by that column. select emp_sequence_no,gender from emp where emp_sequence_no = 20 order by emp_sequence_no; Jay Hostetter Oracle DBA D. E. Communications Ephrata, PA USA [EMAIL PROTECTED] 07/09/03 12:44PM Its obvious I hadnt fully understood ROWNUM yet, as you see we are learning bits and pieces as we go along. Help me out here, will you? Talk to me like I'm a 10-year old, its ok. MaryAnn, the best way to understand rownum is to do the following: SQL SELECT ROWNUM, GENDER 2 FROM (SELECT ROWNUM, GENDER 3 FROM EMP2 4 WHERE ROWNUM = 20) You will quickly see that, no matter how you order the result set, the first record returned is rownum #1, second is rownum #2 etc. The rownum value is assigned as rows are RETURNED or DISPLAYED, not as they are selected. Fine, the rows are numbered as returned or displayed(not selected). BUT THEN HOW ARE THEY SELECTED? My emp2 table has 1+ rows in it, how are only 20 selected? Based on what criteria? How do I get 20 back? I want to understand this first. Then, once I get these 20 back, then fine, they are numbered starting from 1, that part I kind of figured it out, or so I think. The part I dont get, is HOW ARE THEY SELECTED? thx maa **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jay Hostetter 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: Help Needed regarding partitioning
Read about exchanging partitions in chapter 17 and especially Converting a Partition View into a Partitioned Table : http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96521/toc.htm Even though you may not be using a partitioned view, you can use this technique to create the partitioned table. Jay Hostetter Oracle DBA D. E. Communications Ephrata, PA USA [EMAIL PROTECTED] 06/25/03 05:54AM Hi Listers, Please let me know whether I can convert an existing heavy table into a partitioned table and how? I need this to improve my query performance. Thanks and Best Regards Munish Bajaj **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jay Hostetter 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).
9i - Which Patchset
Any opinions on which patchset of 9.2 is most stable: 9.2.0.1, 9.2.0.2, 9.2.0.3? We're running HP Tru64. Thanks! Jay Hostetter Oracle DBA D. E. Communications Ephrata, PA USA **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jay Hostetter 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: 9i - Which Patchset
I know that one is still wet behind the ears. I was debating whether or not to stay on 9.2.0.1 until 9.2.0.3 has been out there a few weeks. I'd hate to see them withdraw a patchset after I've applied it (like 8.1.6.3 or 8.1.7.2.0). Thanks for the replies. Jay [EMAIL PROTECTED] 03/18/03 11:44AM Jay, We are running 9.2.0.3 On Tru64. Downloaded and applied just three days ago (it came out in March 14th). Took care of two bugs I opened up with respect to subpartition splitting and moving the index subpartition to a separate tablespace. Although it's too early to pass a verdict; it seems stabler than 9.2.0.2. HTH. Arup Nanda - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, March 18, 2003 9:58 AM Any opinions on which patchset of 9.2 is most stable: 9.2.0.1, 9.2.0.2, 9.2.0.3? We're running HP Tru64. Thanks! Jay Hostetter Oracle DBA D. E. Communications Ephrata, PA USA **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jay Hostetter 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: Arup Nanda 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). **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jay Hostetter 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: FW: Oracle Enterprise vs Standard
This document (which isn't always easy to find) compares the two: http://otn.oracle.com/products/oracle9i/pdf/o9i_family_features.pdf Jay Hostetter Oracle DBA D. E. Communications Ephrata, PA USA [EMAIL PROTECTED] 03/13/03 03:43AM I need to buy a new Oracle license for 50 users. I have got two different quotes for Oracle Enterprise Server and Oracle Standard; it seems the former is quite expensive. What problems will I get if I buy the cheaper Standard version. This Oracle will be used for BAAN ERP implementation. The environment will be IBM RS6000, AIX 5L and a mixture of LAN and WAN clients Thanks in advance for you advice Regards Tapiwa **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jay Hostetter 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).
Apache and mod_plsql
We are running 9iAS. If I understand this correctly, 9iAS is simply Apache with a PL/SQL module from Oracle. Our management would like to save the Oracle Support dollars for 9iAS, so I am looking into alternatives. We use the PL/SQL web packages, so my options are limited unless we choose to rewrite code. My questions are: 1) Is it possible to add the (or a) PL/SQL module to Apache without going through Oracle - or does the combination of the two simply give you 9iAS, which is an Oracle product? 2) Any comments or experience with DBPrism (http://www.plenix.com/dbprism/), which is the only possible alternative that I have found so far? I've been trying to read about modowa (http://www.sharemation.com/~dmcmahon/modowa.htm) but the server is down at the moment. Thank you, Jay **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jay Hostetter 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: Question related to security
Why don't you use the trick for restoring the password? select 'alter user APPUSER identified by values '''||password||''';' from sys.dba_users where username = 'APPUSER'; save the resulting alter user statement Now, change the password for your third part vendor app, login as the user, grant the select on the tables (preferably to a role), log out, and change the password back to what it was. Jay [EMAIL PROTECTED] 03/05/03 07:34AM That would let the account see ANY table in the database, wouldn't it? Pat. -Original Message- Sent: Tuesday, March 04, 2003 6:05 PM To: Multiple recipients of list ORACLE-L grant select any table to your developer / Shouldn't this work? Raj - Rajendra dot Jamadagni at espn dot com Any views expressed here are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !! -Original Message- Sent: Tuesday, March 04, 2003 4:05 PM To: Multiple recipients of list ORACLE-L Hi all - I have an interesting problem at hand - we have a request from a developer that ask to access some tables owned by a user generated by third party app. Since I don't have the password for this user and system/sys do not have admin option on these tables, I can't grant select to the developer. Changing password for this app can be tricky and will be used as last resort. My question is is there anything else I can do to meet this request? Thanks Dennis **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jay Hostetter 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: Apache and mod_plsql
The problem with that idea is that the webserver portion will be installed in a DMZ, away from the database. Which got me thinking...what if I just installed the http portion of the database install on the webserver box? I would be using the http listener that comes with the database, but I wouldn't be using the database on the same machine. I wonder what licensing issues that raises? Jay [EMAIL PROTECTED] 03/05/03 02:19PM If you don't plan to have many users, I think you could make do with the Oracle HTTP Server (Powered by Apache) that comes bundled with the database from 8.1.6 and onwards. No need to rewrite any code, as it comes with mod_plsql and the PL/SQL Web Toolkit. You could also consider ChangeGroup PL/SQL Server Pages (see http://www.changegroup.biz/da/cgpsp.psp). That would require a rewrite, but it shouldn't be that hard... Regards, Michael Garfield Sørensen, CeDeT - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: 5. marts 2003 16:50 The O'Reilly book Oracle and Open Source says DBPrism is a continuation of / based on OWSKiller, and is one of the most astonishing success stories of Java, Oracle, and open source cooperation. (p.299). They explain how to install it, how to use it, adapters you can get for it, including Cocoon (a Java publishing framework). Pat. -Original Message- Sent: Wednesday, March 05, 2003 10:06 AM To: Multiple recipients of list ORACLE-L We are running 9iAS. If I understand this correctly, 9iAS is simply Apache with a PL/SQL module from Oracle. Our management would like to save the Oracle Support dollars for 9iAS, so I am looking into alternatives. We use the PL/SQL web packages, so my options are limited unless we choose to rewrite code. My questions are: 1) Is it possible to add the (or a) PL/SQL module to Apache without going through Oracle - or does the combination of the two simply give you 9iAS, which is an Oracle product? 2) Any comments or experience with DBPrism (http://www.plenix.com/dbprism/), which is the only possible alternative that I have found so far? I've been trying to read about modowa (http://www.sharemation.com/~dmcmahon/modowa.htm) but the server is down at the moment. Thank you, Jay **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jay Hostetter 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: DBMS_STATS
When is the best time to gather Global Stats? Using the old ANALYZE command, I would analyze each partition as it accumulated data. Now that we have global stats, should I be gather global stats each time I analyze a partition? That would naturally increase the length of time to gather stats. Oracle recommends deleting global stats on partitioned tables in 11i (running on an 8i database). See bde_last_analyzed.sql on MetaLink. In fact, if you read the comments in this script, it implies that you should never gather global stats on a partitioned table. Any thoughts on this? Has anyone had problems with global stats on 9i? Also, does anybody recommend SKEWONLY or AUTO_SAMPLE_SIZE with DBMS_STATS? My testing shows that this causes the analyze to take longer (which is to be expected). I haven't yet determined if its worth the extra time. Thanks, Jay Hostetter Oracle DBA D. E. Communications Ephrata, PA USA [EMAIL PROTECTED] 02/25/03 07:49PM To expand on this, the action level is controlled by the granularity parameter... Granularity of statistics to collect (only pertinent if the table is partitioned). DEFAULT: Gather global- and partition-level statistics. SUBPARTITION: Gather subpartition-level statistics. PARTITION: Gather partition-level statistics. GLOBAL: Gather global statistics. So, in you case, run the following... execute dbms_stats.gather_table_stats(ownname = 'LDGADMIN', - tabname = 'LOG_TRANS', - partname = 'LOG_TRANS_20030102', estimate_percent = 5, granularity = 'PARTITION'); See the supplied package reference for more details... Tim -Original Message- Sent: Tuesday, February 25, 2003 4:50 PM To: Multiple recipients of list ORACLE-L The default action of dbms_stats against a single partition of a partitioned table is much more aggressive than a simple analyze of the partition. At the least, it does a similar analyze of the whole table in order to maintain the global table statistics - you need to set the granularity of the analyze to stop this happening. On a quick test, with sql_trace turned on and using 9.2.0.2, your choice of parameters gave me: Two 5% analyzes of the specified partition, with a small difference relating to one longish varchar() column. One 5% analyze of the whole table. One 50% analyze of the whole table - restricted to a much smaller process of a subset of the columns that omitted the one longish varchar() column. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon one-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___March 19th USA_(FL)_May 2nd Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: 25 February 2003 18:12 I have never had good luck with DBMS_STATS. It seems that the old analyze runs much faster. Runs in 45 seconds: analyze table log_trans partition (log_trans_20030104) estimate statistics sample 5 percent; Takes over 2 hours: execute dbms_stats.gather_table_stats(ownname = 'LDGADMIN', - tabname = 'LOG_TRANS', - partname = 'LOG_TRANS_20030102', - estimate_percent = 5); Am I missing something? Aren't both commands the same? Thanks, Tom -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Johnston, Tim 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
Re: Using fully qualified table_name.database_object in
We use fully qualified table names to avoid confusion. Ever poke around in Oracle Apps (11i) databases? OK...it references an object owned by APPS, but waitthat's a synonym that points to a table in INV... Synonyms can make your applications portable to another schema. However, in the 8 years that we've been growing our own applications, we've never ported to another schema. The one advantage that I can think of is that you can have multiple application schemas in the same database for testing purposes. Your developers could then reference whichever schema they want to use for testing via synonyms. However, I prefer to spend less time tracking down synonyms by not using them in the first place. Jay [EMAIL PROTECTED] 02/24/03 11:29AM I would like to know if it is advocated to use fully qualified table_name.database objects in application code. Example would be schema.table_name in a PL/SQL code. I would like to know the Pros/Cons if there are any? Thanks in advance. - Do you Yahoo!? Yahoo! Tax Center - forms, calculators, tips, and more **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jay Hostetter 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: transferring data from OLTP DB to Reporting DB
I joined this thread late - another possibility is using the sqlplus COPY command in a script. Again, depends on volume of data, etc. etc. REM number of record groups before commit occurs set copycommit 1; REM number of records in each group set arraysize 1000; copy from uname/pwd@yourdb insert WRKORD.MTL_ITEM_SUB_INVENTORIES using - select INVENTORY_ITEM_ID, ORGANIZATION_ID, SECONDARY_INVENTORY - from INV.MTL_ITEM_SUB_INVENTORIES; Jay [EMAIL PROTECTED] 02/20/03 09:34AM Vijaya There are several methods you can use to accomplish this. I think you will have to explore the various methods in your environment and decide which one seems to work best for you: 1. Export with a query (Naveen's suggestion) 2. Database link. 3. Transportable tablespace. 4. RMAN DUPLICATE command. 5. Standby database. 6. Replication. 7. Third-party tools like Quest Shareplex. It depends on the volume of rows that are being transferred, the capacity of the respective systems, time windows, effect on the production system, whether additional tables need to be moved also either now or in the future, etc. -Original Message- Sent: Thursday, February 20, 2003 1:24 AM To: Multiple recipients of list ORACLE-L Hi, I have a requirement like this: I have a transaction database(OLTP) and after closure of every day's transactions, i need to move data into another Reporting DB. Only the records with Status Flag as 'Y' on transaction database tables needs to be moved over to the Reporting DB. Can you please suggest any approaches/strategies/tools for doing above requirement. regards, Vijaya Chander V.S -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vijaya Chander V.S INET: [EMAIL PROTECTED] **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jay Hostetter 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: DBMS_JOB : Can submit but does not execute
Make sure job_queue_processes 0. Also check job_queue_interval.
Jay Hostetter
Oracle DBA
D. E. Communications
Ephrata, PA USA
[EMAIL PROTECTED] 02/18/03 08:28PM
Hi Gurus,
I use the following command to execute immediately.
Connected.
SQL BEGIN
2 DBMS_JOB.RUN(1);
3 END;
4 /
PL/SQL procedure successfully completed.
--
-- I change the time for next execution to check whether it can submit on 18 Feb 2003
at 8am
--
1 begin
2 DBMS_JOB.CHANGE(1,
3 ' PROCEDURE_NAME;',to_date('1802200308:00:00','ddmmhh24:mi:ss'),'SYSDATE+1');
4* END;
SQL /
It did not execute. Any advice ? TIA
Regds,
Catherine
-Original Message-
From: CHAN Chor Ling Catherine (CSC)
Sent: Wednesday, February 19, 2003 9:15 AM
To: '[EMAIL PROTECTED]'
Subject:DBMS_JOB : Can submit but does not execute
Hi Gurus,
I have submitted a job but it does not execute. Why? Does the owner
require any privileges ? However, I can use DBMS_JOB.CHANGE command to execute
immediately. I would like the job to execute on a daily basis. Any advice ? TIA
--
-- Submit a job
--
1 DECLARE
2job BINARY_INTEGER;
3 BEGIN
4DBMS_JOB.SUBMIT(job,'PROCEDURE_NAME;',SYSDATE,'SYSDATE+1');
5DBMS_OUTPUT.PUT_LINE(TO_CHAR(JOB));
6* END;
SQL /
1
PL/SQL procedure successfully completed.
SQL COMMIT;
Commit complete.
--
-- Execute the job
--
1 begin
2 DBMS_JOB.CHANGE(1,
3 '
PROCEDURE_NAME;',to_date('1802200308:00:00','ddmmhh24:mi:ss'),'SYSDATE+1');
4* END;
SQL /
PL/SQL procedure successfully completed.
SQL COMMIT;
Commit complete.
--
-- Check whether the job is submitted
--
SQL SELECT * FROM DBA_JOBS
JOB LOG_USER PRIV_USER
- -- --
SCHEMA_USERLAST_DATE LAST_SEC THIS_DATE THIS_SEC
NEXT_DATE NEXT_SEC TOTAL_TIME B
-- - -
- -- -
INTERVAL
FAILURES
-
WHAT
NLS_ENV
MISC_ENV
INSTANCE
-
1 USER1 USER1
USER1 17-FEB-03 20:01:20
18-FEB-03 08:00:00 16 N
SYSDATE+1
0
PROCEDURE_NAME;
NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$'
NLS_ISO_CURRENCY='AMERICA' NLS_NUME
RIC_CHARACTERS='.,' NLS_DATE_FORMAT='DD-MON-YY'
NLS_DATE_LANGUAGE='AMERICAN' NLS_SORT='BINARY'
01020002
0
Regds,
Catherine
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: CHAN Chor Ling Catherine (CSC)
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).
**DISCLAIMER
This e-mail message and any files transmitted with it are intended for the use of the
individual or entity to which they are addressed and may contain information that is
privileged, proprietary and confidential. If you are not the intended recipient, you
may not use, copy or disclose to anyone the message or any information contained in
the message. If you have received
Automatic Segment Space Management
I'm continuing to introduce myself to 9i. I've been reading about Automatic Segment Space Management, and I just wondered if anybody had any positive/negative experiences with it. I got some good info at: http://www.dbazine.com/burleson11.html Thank you, Jay Hostetter Oracle DBA D. E. Communications Ephrata, PA USA **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jay Hostetter 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: DBMS_JOB
The PL/SQL packages that we have running as jobs resubmit themselves when the job is complete. So we can just add logic before calling DBMS_JOB.SUBMIT. Can your PL/SQL be modified in this way? Jay [EMAIL PROTECTED] 02/18/03 09:14AM Hi All, Does anybody know a way of to get DBMS_JOB to run a job every 15 minutes, unless the time of day is between X and Y (for example 00:00am 03:00am)? All help appreciated! Pointers to RTFM more than welcome! ;) Mark === Mark Leith | T: +44 (0)1905 330 281 Sales Marketing | F: +44 (0)870 127 5283 Cool Tools UK Ltd | E: [EMAIL PROTECTED] === http://www.cool-tools.co.uk Maximising throughput performance -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mark Leith 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). **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jay Hostetter 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: Plain Old Database
Thanks for all the replies. The reason I asked is because dbca calls some of these items Standard Database Features. Steve - I think that the mod plsql is the module that let's you generate html using PL/SQL (htp and htf pl/sql packages). Jay [EMAIL PROTECTED] 02/13/03 06:19PM Thanks Allan, as we all know Oracle is unbreakable, so security isn't an issue. :-) I know mod perl and mod ssl but what's mod plsql? How is it used and why? Anyone using it? Steve -Original Message- Sent: Thursday, February 13, 2003 3:46 PM To: Multiple recipients of list ORACLE-L The Apache shipped with the Oracle database has mod perl, mod ssl, and mod plsql installed into it, but is AFAIK a stock Apache distribution otherwise. It includes stuff for JSP's as well. You can't put an i in your name if you don't have web stuff and as we all know Oracle is unbreakable, so security isn't an issue. Allan -Original Message- Sent: Thursday, February 13, 2003 3:34 PM To: Multiple recipients of list ORACLE-L I'm with you. Oracle behaves too much like Microsoft trying to get you install stuff whether you need it or not. I've come across a lot databases where the installer person installed stuff probably thinking, I'm not sure if we'll need it or not so I'll just install it anyway. I don't use the dbca stuff but I did the same as you at a shop that used InterMedia. I then modified the generated scripts and only installed what was really needed. I wonder how many databases are vulnerable to hacking because Apache was installed from the Oracle CD's? Is there anything different about the Apache shipped by Oracle vs. what's publically available? Why does Oracle put Apache stuff on the CD's? Curious too, Steve Orr **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jay Hostetter 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: Ora-00600 fun
OS and version? Possibilities from Metalink: Increase the SHMMAX kernel parameter to 2139095040 (2Gb-8Mb). See note 70240.1 Jay [EMAIL PROTECTED] 02/14/03 06:33AM After my nightly cold backup and during the database startup I got this error; ksedmp: internal or fatal error ORA-00600: internal error code, arguments: [3051], [82], [73], [], [], [], [], [] ORA-04030: out of process memory when trying to allocate 8389132 bytes (pga heap,THREAD RECOVERY) The database mounts but does not startup. I already filed a TAR with Oracle but was wondering if any of you might have a clue on this one. This is suppose to happen on Monday, not Friday. Thanks, Dave -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Farnsworth, Dave 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). **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jay Hostetter 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: Database naming conventions
Are we talking SID or connect string? I seem to remember that there was (or is on certain OSs) a limit to the length of the SID - so I tend to keep the SIDs short and sweet. If it is the connect string - then who is it that needs to know it is an Oracle database? The user? Why? The DBA? Er...shouldn't he/she already know that? I agree with you - I would eliminate the 'ora'. You can illustrate the absurdity by insisting that it also include the version number and OS ora9201tru64p24x7. Jay Hostetter Oracle DBA D. E. Communications Ephrata, PA USA [EMAIL PROTECTED] 02/14/03 11:23AM List, I'm use to using a standard D=development T=test P=production. So for a database newly created on development it would be called something like D24X7. Then when it was created on Production it would be called P24X7. Or along similar lines. I'm working with an other DBA who wants everything to start with ora. Therefore it would be called orad24x7 and orap24x7. I've argued the ora is rather redundant since everyone will know it's an Oracle database they are connecting to. He is adamant it should have the ora identification so it is easily identified. I feel it will cause more confusion having ora at the beging of every dbname. Any thoughts for against either position? TIA M.Godlewski **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jay Hostetter 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: Lock table and disallow select
I agree with Raj - why does your user want to do this? Nobody will see the changes until a commit occurs anyway. Jay Hostetter Oracle DBA D. E. Communications Ephrata, PA USA [EMAIL PROTECTED] 02/14/03 10:04AM Query from user. can the following be done in PL/SQL? I need to be able to lock a table in such a way that not only can no other session alter any records in the table, but they cannot read the table at all (i.e. do SELECTs on it) until I'm done with it. Any SELECT statements would pause until I release the lock. Is such a thing possible? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Dunn 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). **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jay Hostetter 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).
Plain Old Database
I am preparing to migrate from 8.1.7 to 9.0.2. I was just wondering if I am the only DBA that doesn't like all of this other stuff in my database - Ultrasearch, Intermedia, Oracle Text, and XML. I used dbca to generate my database creation scripts, so I could understand what the tool is doing to create the database. I've gone through and commented out these things that we won't be using. Am I in left field on this, or are others installing these options/products in case they need them later on (assuming you don't have an immediate need for them)? Same question for the http listener - are most dbas installing it and using it? Just curious. Thanks, Jay **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jay Hostetter INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Rman and database shutdowns
Ron, Are you saying that you use VMS backup commands to backup the database data files while the database is open? This will result in unreliable backups unless you put the tablespaces in hot backup mode. If you are not using RMAN, you should have a script that backs up each datafile without using the /incremental qualifier on the VMS backup command. If you are doing a cold backup with RMAN, the database should be a MOUNT state. If it is mounted and open, then RMAN will do a hot backup. RMAN won't care how many times you did a shutdown/startup before you execute your RMAN backup. Jay [EMAIL PROTECTED] 02/12/03 10:18AM List, OS OpenVms 7.3-1 Oracle 8.1.7 rel 3 Archivelog mode. We are trying to work through backup issues here and I have a question. The system nightly backup procedures backup up the disks used by Oracle (software and data). A full backup works but an incremental does not backup any files. Inorder to get the changed files to be backed up as an incremental the sysadmin shutdown the database to flag the files as changed. Questions: How will the shutdown effect the RMAN backup if it does not know that it was shutdown for a cold backup? Are there any possible problems doing a restore from the cold backup and then using the RMAN backup/restore? I am still trying to figure out the OpenVms OS. Perserverience required...! Ron -- 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). **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jay Hostetter INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Rman and database shutdowns UPDATE
Ron, Back when we had Oracle on VMS (Oracle 7.1 - pre RMAN), we used this backup command for each datafile: backup/log/ignore=(interlock,nobackup)/new This will backup open files. But if your tablespace isn't in hot backup mode, it doesn't help you for database recovery. You might get lucky if there is little-to-no activity at the time of your backup, but this is a risk few DBAs would take. Jay [EMAIL PROTECTED] 02/12/03 11:23AM Just got out of a meeting with the systems group. The Incremental backup was not working because the command was written wrong. There has been a decision to do cold backups each night. The system people swear that the OpenVms can successfully backup an open file (datafile) and have it recovered properly. They can backup it up with an Ignore flag that backups up open files? I will go with a cold backup methodology. Ron [EMAIL PROTECTED] 02/12/03 10:18AM List, OS OpenVms 7.3-1 Oracle 8.1.7 rel 3 Archivelog mode. We are trying to work through backup issues here and I have a question. The system nightly backup procedures backup up the disks used by Oracle (software and data). A full backup works but an incremental does not backup any files. Inorder to get the changed files to be backed up as an incremental the sysadmin shutdown the database to flag the files as changed. Questions: How will the shutdown effect the RMAN backup if it does not know that it was shutdown for a cold backup? Are there any possible problems doing a restore from the cold backup and then using the RMAN backup/restore? I am still trying to figure out the OpenVms OS. Perserverience required...! Ron -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ron Rogers **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jay Hostetter 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: multiple oracle homes
Ray - the one thing we do is always make sure we don't corrupt the Oracle Inventory files. Whenever we install a new version of Oracle, we copy then delete the current oraInst.loc file (e.g. cp oraInst.loc oraInst_u03.loc), which on our system is located in /var/opt/oracle. We have our Oracle Homes on different file systems. If you want them on the same file system, be sure your Oracle Inventory is located under $ORACLE_HOME, so that each version can have it's own inventory location. Always be sure you have the right oraInst.loc file in place when you are doing your patching. See note 182853.1 Jay Hostetter Oracle DBA D. E. Communications Ephrata, PA USA [EMAIL PROTECTED] 02/06/03 04:18PM Ray - Since I don't see where anyone replied, I think the problem is your stress on well documented. Might have scared people off. The basic procedure is to use separate ORACLE_HOME for each version. Make sure each version doesn't share anything you don't want them to. Most Unix systems require certain kernel parameters to be increased. I am not seen where anyone has clearly documented all aspects of this situation. Make sure you try this first on a test server. Once you've done it a time or two, your confidence will increase. Then you can write that clear documentation and post it on a web site for everyone's benefit. ;-) Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, February 06, 2003 7:09 AM To: Multiple recipients of list ORACLE-L Where is it well documented how to install multiple server versions, 8i and 9i, on the same unix server? === Ray Stell [EMAIL PROTECTED] (540) 231-4109 KE4TJC28^D **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jay Hostetter 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: multiple oracle homes
I agree with Dennis. We tried this several years ago and decided it was best to stick with one username. I forget what the exact issues were - it might have been file privileges when you are upgrading a database. Jay [EMAIL PROTECTED] 02/07/03 10:54AM Ray - My 2 cents worth. Don't ever use another username besides Oracle. Had a bad experience :-) Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Friday, February 07, 2003 8:24 AM To: Multiple recipients of list ORACLE-L On Thu, Feb 06, 2003 at 05:08:55AM -0800, Ray Stell wrote: Where is it well documented how to install multiple server versions, 8i and 9i, on the same unix server? -- Thanks for you replies. I've never tried this before and it seems like there are two different approaches on the surface: 1. use two different userids, ora817 and ora920, to do the install. This seems stupid, since it replicates the product directory structure and oraInventory stuff under different ownership. This might be safer since it is like running one version in that everything is seperate, but maybe there are operational issues to not using the oracle userid. Seems like there may be a gotcha waiting in the wings. Like maybe you can't run the same listener for both, or worse. 2. use the same oracle userid for both installs and change the environment vars as needed. Seems like you could damage the first install if you made a mistake. Also, it seems like in a stressful failure situation you don't want to have to think about who's on first? I don't know, third base. Are both paths valid? **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jay Hostetter INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: RMAN - problems restoring to clone server
Gary, Why are you trying to create the RMAN schema? You could be connecting to the RMAN database that was used for making the backup. To clone a database, your target should be your production db, your catalog should be the RMAN catalog that was used for the backup, your clone should be auxiliary. e.g.: rman uname/password@prod target uname/password@rmandb auxiliary / (assuming that your environment is set for the auxiliary database - SID, ORACLE_HOME, etc.). I only use RMAN for backing up to disk, so I can' t help with the MML portion (media management layer). Jay [EMAIL PROTECTED] 01/31/03 12:20PM Can you please clarify what you mean by linking the MML to Oracle? I am unable to create the RMAN schema,table,and views on the 'to-be-clone' database yet since it is just a non-mounted instance at this point in time. I assumed I would connect to rman as a rcvcat on the server with the dbcat database and the legato tape device. Then connect as the target to the to-be-clone non-mounted instance. Is this correct? Then in the restore rman script I use: NSR_SERVER as the tape device/dbcat server NSR_CLIENT as the to-be-clone target server Is this correct? Thanks in advance! -Gary **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jay Hostetter INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: RMAN - problems restoring to clone server
Gary,
This is the procedure that I use to setup a duplicate/clone/auxiliary database, as
outlined on pg. 7-18 of the 8.1.6 Rel.2 RMAN User's Guide and Reference. This creates
a duplicate database with a unique dbid. Rman knows that it should recover to the
clone because I issue a duplicate command in my script. The command parameters are
confusing ('target' would more aptly be named 'source' and 'auxiliary' is the real
'target' when cloning).
Looking back over your email, I see that you are on 8.0.6, so there may be some
differences. I better let the advice to those that are more experienced with that
version.
Here is a sample script that I use:
run {
set until scn 1016905055;
allocate auxiliary channel ch1 type disk;
allocate auxiliary channel ch2 type disk;
set newname for datafile 1 to '/t202/oradata/INTT/system01.dbf';
set newname for datafile 2 to '/t202/oradata/INTT/tools01.dbf';
...yadda yadda...
set newname for datafile 11 to '/t204/oradata/INTT/tape_data01.dbf';
duplicate target database to INTT
logfile
group 1 ('/t202/oradata/INTT/redo1a.log','/t203/oradata/INTT/redo1b.log') size 1m,
...yadda yadda...
group 4 ('/t202/oradata/INTT/redo4a.log','/t203/oradata/INTT/redo4b.log') size 1m;
}
Jay
Gary Jackson [EMAIL PROTECTED] 01/31/03 02:54PM
Jay,
I'm confused. Based on what you said I would want to connect my target to
my production db and my catalog to my dbcat/tapeserver. If I understand your
suggestion correctly, how would RMAN know to recover to the new clone
database?
-gary
**DISCLAIMER
This e-mail message and any files transmitted with it are intended for the use of the
individual or entity to which they are addressed and may contain information that is
privileged, proprietary and confidential. If you are not the intended recipient, you
may not use, copy or disclose to anyone the message or any information contained in
the message. If you have received this communication in error, please notify the
sender and delete this e-mail message. The contents do not represent the opinion of
DE except to the extent that it relates to their official business.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jay Hostetter
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 backup software for daily backups...
We use RMAN to backup to disk. From disk, our sysadmins backup the files to tape. I believe they are using Legato Networker. They frequently have problems with their backups - I don't know the specifics. I just know that there are many times that I have been thankful that we never bothered to interface RMAN and Networker. My databases are independent of their backup problems (as long as my backups eventually get to tape!). And if I need to create a test database from a backup, it is right there on disk. I don't have to wait for a tape drive to become available. We didn't have the time to get the interface working. And if I remember correctly, there were additional licensing costs for an Oracle/Networker module. I think we're better off in the long run. Jay [EMAIL PROTECTED] 01/30/03 08:09AM I work for the State of Vermont and money is always an issue. Rman is included with Oracle so there is no additional cost. I have no experience with other backup software for Oracle. It has worked for me and I have been using since I started here. Ruth - Original Message - From: Peter R To: Multiple recipients of list ORACLE-L Sent: Thursday, January 30, 2003 7:39 AM Subject: Re: Best backup software for daily backups... Thankyou Ruth, What about Veritas Netbackup and Tivoli software for bakcups, Which one do you think is best and effective in long term and easy to admin. Thanks again for your only response. From: Ruth Gramolini [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: Best backup software for daily backups... Date: Wed, 29 Jan 2003 05:39:46 -0800 We are doing the same upgrade, and we will continue to use rman for all backups. Ruth - Original Message - From: Peter R To: Multiple recipients of list ORACLE-L Sent: Wednesday, January 29, 2003 7:39 AM Subject: Best backup software for daily backups... Hi Friends, We are going to upgrade our database(Oracle9i) with Unix server(AIX5.2), I would like to know best backup software for new production server with 250-350Gb database size. Ours is 24X7 shop, so we are going to take daily hotbackups. Any ideas and experiance from gurus!!! Thanks peter. _ Add photos to your e-mail with MSN 8. Get 2 months FREE*. http://join.msn.com/?page=features/featuredemail -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Peter R 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). _ STOP MORE SPAM with the new MSN 8 and get 2 months FREE* http://join.msn.com/?page=features/junkmail -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Peter R 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). **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jay Hostetter INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services
Re: Global Stats
Lisa, On our home grown partitioned databases, I am still using the ANALYZE command for partitions. There were some bugs associated with DBMS_STATS and partitions, although I don't remember the specifics at the moment (it may have been with 8.1.6). I haven't had a chance to go back and see if we want to switch to DBMS_STATS. On our 11i database (8.1.7), Oracle provided scripts specifically check to make sure that global stats to not exist on partitioned tables (search for bde_last_analyzed.sql on MetaLink). We analyze these tables with FND_STATS and a granularity of PARTITION. Others may have more specific info on where the issue stands beyond 8.1.7. Jay Hostetter Oracle DBA D. E. Communications Ephrata, PA USA [EMAIL PROTECTED] 01/28/03 02:10PM Hi everyone, Back to the lovely world of Oracle :) I've been reading up on statistics. Out of the 8.1.7 doco: /* Partitioned schema objects may contain multiple sets of statistics. They can have statistics which refer to the entire schema object as a whole (global statistics), they can have statistics which refer to an individual partition, and they can have statistics which refer to an individual subpartition of a composite partitioned object. Unless the query predicate narrows the query to a single partition, the optimizer uses the global statistics. Because most queries are not likely to be this restrictive, it is most important to have accurate global statistics. Intuitively, it may seem that generating global statistics from partition-level statistics should be straightforward; however, this is only true for some of the statistics. For example, it is very difficult to figure out the number of distinct values for a column from the number of distinct values found in each partition because of the possible overlap in values. Therefore, actually gathering global statistics with the DBMS_STATS package is highly recommended, rather than calculating them with the ANALYZE statement */ The table I need to generate stats for is currently 32GB and grows by ~2GB per week. Even the smallest estimate with calculating global stats will take a long long time and I may not be able to spring for all the required temp space. How does the list feel about global stats? Does anyone agree with the documentation that they most important? I'm thinking my partitioned statistics are the most important. Any input is appreciated. Thanks Lisa Koivu Oracle Database Administrator Fairfield Resorts, Inc. 5259 Coconut Creek Parkway Ft. Lauderdale, FL, USA 33063 **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jay Hostetter 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: New Schema or New Database?
Another concern is user rights. When you copy a production database to test, everything in that database is an exact copy of production. If you just import a production schema into a test schema, your users and their roles are not updated. You will have to discuss your concerns with the developers. The benefits may outweigh the costs. Jay [EMAIL PROTECTED] 01/21/03 09:53AM Our DBA group has recently been getting numerous requests for new databases (training, inventory, customer contacts, etc..) from different departments within the company. Our normal procedure is to create a new instance for the database, create the schema, users, etc..., set up backups and turn it over. However, with the volume of requests we are now getting, we are pondering the idea of creating just one instance and giving each database request its own tablespace and schema. (similar to informix and sybase architecture). My questions for discussion are these; 1) What are the benefits/risks associated with this scenario? Please note that these databases/schemas are unrelated. 2) What questions (for a user questionaire) should we ask regarding their database requirements, which will help us make an informed decision? My concerns are; 1) the inability to tune the instance for one schema/applications performance needs. 2) uptime/availability requirements may differ among the databases. 3) backup/restore scenarios specific to the schema/database (restore just one schema to a point-in-time). We want to be able to save on memory(sga) and processes by combining the databases into one instance as schemas, but don't want to limit the different applications to 'one-size-fits-all' for performance/recovery scenarios. Any advice would be greatly welcomed. **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jay Hostetter 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: Oracle Silent Installs and Response files...
Greg, I have response files for our client installs (Win2k and WinNT) for 8.1.6. It took several hours of experimenting before I got it to work. Haven't tried it on Unix or with database installs. I basically took the example response file and just tweaked it a bit. Let me know if you want a copy or if you need more info. Jay Hostetter Oracle DBA D. E. Communications Ephrata, PA USA [EMAIL PROTECTED] 01/17/03 09:24AM Hey guys- Have any of you all messed around with the silent install/response files? I'm looking for tips, advice, do's, don'ts,etc,etc TIA Greg Loughmiller Sr Manager - Enterprise Data Architecture gloughmiller (IPS) 678.893.3217 (office) **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jay Hostetter 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: Important - Oracle Pricing on Standby/DR/Failover
I just renewed our Cognos support. It took 2 emails and a 10 minute phone call (of which 8 minutes were spent talking football - go Eagles!). Contrast this with our Oracle support negotiations which have been going on since SEPTEMBER! I'll spare you the details, but let's just say that I am extremely frustrated with this licensing subject. I've watched the Software Investment Guide change several times during the last few months. It seems like the rules that you are trying to play by are constantly changing - now I see this reference to Price Hold for named users - where does that come from? I think management would switch to another DB vendor in a heartbeat just so they could understand what they're paying for. Is licensing for those *other* databases just as complicated (not that I advocate an attempted migration, mind you)? The one thing I've learned in this process is to always run your numbers. Our contract said a 10% discount, but the numbers didn't reflect it. Does anybody go through a 3rd party for buying their Oracle support? I know that vendors can resell licenses - can they resell support too? I'm thinking that it might be less of a headache to deal with a vendor than with Oracle. Jay [EMAIL PROTECTED] 01/16/03 12:13AM Hi Jared I have a reply from someone who does not want to be identified. This is his case. His company tried reasoning and discussing it with Oracle and even tried a compromised (which I would not be happy with) He company put forward to Oracle to pay for the full licence on the production server AND the minimum for the standby. In the case the standby was a single CPU and so the minimum licence is a 5 User Licence. This was to account for any DBA connection to check the integrity of the standby database. Even this was not acceptable to Oracle. How greedy can you be? Can you say Gordon Gekko? BTW The following information applies to all You need to know the difference between NAMED USER and NAMED USER PLUS. (extract from SELECT*Star) Gone also is the Named User license. In its place is Named User Plus. Companies wanting to purchase additional user licenses for the same machine will need to convert their Named User licenses to Named User Plus licenses if they do not have a Price Hold on the license. The minimum number of licenses must be the greater of either the actual number of users or the Minimum Named User Plus (25 per CPU) for the server. Minimum Named User (Enterprise Edition) per CPU used to be 10 but now the minimum Named User Plus per CPU is 25. In some instances, customers are forced to buy more licenses than is required when looking for additional licenses. The key difference between Named User and Named User Plus is that Named User does not allow for batch processing whereas Named User Plus does. ta tony At 08:57 PM 15/01/2003 -0800, Jared Still wrote: Thanks Tony. Looks like Larry E is trying to boost revenues in a down economy by any means necessary. You're right, this doesn't seem right. Jared **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jay Hostetter INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: RE : RMAN Repository
But I don't anticipate having to recover from a prior incarnation when I have a perfectly good backup from the last successful backup. I get requests from developers to refresh test databases from production backups that are 30 days old. This is for billing system software, where they need to test bill runs. Back when our cluster software was still buggy, this would occassionally require restoring from a previous incarnation, because the production database had been recovered with RESETLOGS. If I can do this in 8.1.7 without an RMAN respository, I'd love to hear about it. Arup - I love the leather interior tank analogy! Jay Hostetter Oracle DBA D. E. Communications Ephrata, PA USA [EMAIL PROTECTED] 01/09/03 06:19PM Hey Brian, I only talk about the way it SHOULD be... not what I actually do. :-) I confess to presently using the suppository, er a repository but anticipate just using control files after we upgrade to 9i with its enhanced RMAN features. I have a shell script with parameters and if it's a non-catalog backup I also backup the controlfiles. I don't anticipate problems with exceeding max files. I create a daily ASCII file with a listing of the all the database files. Not sure what else you're looking for. Do you accept losing the backup history and cross fingers Huh??? I have CONTROL_FILE_RECORD_KEEP_TIME set to 7 or 14 days, I forget which. But I don't anticipate having to recover from a prior incarnation when I have a perfectly good backup from the last successful backup. I once had to do a PITR to recover dropped tables that weren't noticed until 5 days later. To do this I created another database on another server, did the PITR then restored the specific tables while all the other tables remained current. I was able to do this without a repository. I have some scripted recovery scenarios which I occasionally practice on a test machine. Hmmm... it's been a while and it's a new year so it's probably a good time to review and test backup/recovery scenarios. Recover scenarios should include something like the following: loss of a non-system, non-rollback segment datafile; loss of a rollback segment datafile; loss of a system datafile; recovering a temporary tablespace; loss of 1 or all controlfiles; restoring archivelogs; a complete database restore; loss of inactive online redo log; loss of current online redo log; database server meltdown and recovery to a replacement server, PITR and tablespace PITR... Who said backup and recovery was boring? :-) Steve Orr **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jay Hostetter INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE : RMAN Repository
Is it an old fashioned backup (non-rman)? I export our rman repository and ftp the .dmp file to another server. Jay [EMAIL PROTECTED] 01/09/03 03:30PM I do a cold backup of my repository daily. Replication of it is not a bad idea, as Arup mentioned, though I haven't tried it myself. Speaking of backing up the RMAN repository, does anyone back them up hot? Seems to me that would not be a good idea. Jared Ruth Gramolini [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/09/2003 11:04 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: RE : RMAN Repository The earlly versions of rman suggested that you put a 2nd recovery catalog in one of the databases you are using the real recovery catalog for. Then you use this to record the backups of the recovery catalog database. I never headed this advice, altho I do use a recovery catalog for all production, developement, and test databases that I back up. Ruth **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jay Hostetter 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: Buffer Pool Testing
The reason that I consider it a problem is that I have increased the physical IO on my database. I see Full Table Scans on these two tables, which are large tables relative to the others. I moved them to they RECYCLE pool so that they would have less of an impact on the DEFAULT pool. By doing so, it looks like I am forcing the database to do more physical reads than it may have been doing with these tables in the DEFAULT pool. I thought the FTS blocks would fight with each other (the blocks queried the most often would stay in the pool) over the RECYCLE pool, but the algorithm keeps the first blocks in place. Knowing this, I think I need to re-evaluate whether or not these tables were causing a problem in the DEFAULT pool to begin with. I tried _db_percent_hot_recycle=10, but didn't really notice much of a difference. Since all of my blocks are read into this pool by FTS, I was hoping that it would consider some of them to be hot, thereby causing the rest to be flushed out faster. I just wanted the hottest FTS blocks to stay in the RECYCLE pool, but that wasn't the case. Jay [EMAIL PROTECTED] 01/02/03 06:44PM The 'problem' is, as you say, related to the tablescan. Craig is correct that NORMALLY a full tablescan will only permit a limited number of blocks to get into the cache at the LRU end of the chain - the number is typically the size of db_file_multblock_read_count. There are a couple of special cases though, the first being when there are free blocks in the buffer, Oracle will just keep packing in the multiblock reads into the cache until there are no free blocks left. (And specifically free means state=0). The other special case I can think of at the moment is when you have multiple tablescans going on concurrently, and depending on precise timing you can end up with multiples of db_file_multiblock_read_count blocks from different tables near the LRU end of the cache. There was a period, I believe, when the RECYCLE pool did behave a little differently (can anyone confirm this ?) but in 9.2.0.2, it handles tablescans just the same way as the default pool. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon a new one-day tutorial: Cost Based Optimisation (see http://www.jlcomp.demon.co.uk/tutorial.html ) Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) England__January 21/23 The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 02 January 2003 19:03 So it seems like my problem is the full table scan. Craig Shallahammer mentions this in his All About Oracle's Touch-Count Data Block Buffer Algoithm paper - The modified LRU algorithm places full-table scanned blocks read into the buffer cache at the LRU end of the LRU chain and only permits a limited number of these blocks to exist in the cache at once. Using my second example (query ALRA_TRANSACTION_HISTORY then WORK_ORDER_STEP), I can get more blocks of WORK_ORDER_STEP into the cache if I run queries that don't do full table scans. I still expected multiple queries against a table (full-scan or otherwise) to replace the cache blocks that I was no longer using - especially in the RECYCLE pool. But it appears as though the algorithm doesn't work that way. Thanks, Jay also send the HELP command for other information (like subscribing). **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jay Hostetter 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: Buffer Pool Testing
This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jay Hostetter 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).
Buffer Pool Testing
I'm spending some time today experimenting with buffer pools in 8.1.7. I have two tables that I have assigned to the RECYCLE pool. I have been running various queries that perform full table scans, then checking the buffers to see what gets aged out. During my testing, it seems like the first blocks to get into the RECYCLE buffer pool will stay there. The following two tables are assigned to the RECYCLE pool. No other segments are assigned to it: WORK_ORDER_STEP - 428 blocks of data ALRA_TRANSACTION_HISTORY - 14152 blocks of data The RECYCLE pool has 1000 blocks. I startup the database, query the WORK_ORDER_STEP table (1 time), then run multiple queries against the ALRA_TRANSACTION_HISTORY table (6 times), I see the following in the buffers (the source for this query is at the end of my email): BP_NAME OBJ_OWNER NAME BLOCKS MAX_TOUCH MIN_TOUCH AVG_TOUCH --- -- -- - - - RECYCLE BIS ALRA_TRANSACTION_HISTORY 569 14 0 .02 RECYCLE WRKORD WORK_ORDER_STEP 431 1 0 .00 If I startup the database, query the ALRA_TRANSACTION_HISTORY table (1 time), then run multiple queries against the WORK_ORDER_STEP table (6 queries), I get these results: First query - 1000 blocks are used as expected BP_NAME OBJ_OWNER NAME BLOCKS MAX_TOUCH MIN_TOUCH AVG_TOUCH --- -- -- - - - RECYCLE BIS ALRA_TRANSACTION_HISTORY 1000 2 0 .00 After querying the second table multiple times, I expected more than just 9 blocks to be given up. I expected more like 431 blocks. BP_NAME OBJ_OWNER NAME BLOCKS MAX_TOUCH MIN_TOUCH AVG_TOUCH --- -- -- - - - RECYCLE BIS ALRA_TRANSACTION_HISTORY 991 2 0 .00 RECYCLE WRKORD WORK_ORDER_STEP 9 4 0 .44 I expected the blocks (from the table that was queried first) to be aged out as I queried the second table (over and over). This does not occur. Am I hitting a bug or just misunderstanding the buffer management algorithms? ALRA_TRANSACTION_HISTORY blocks should be LRU as I hit the WORK_ORDER_STEP table over and over. Thanks, Jay Here is the query that I use to check what is in the buffer pools: select bpd.bp_name, u.name obj_owner, o.name, count(*) BLOCKS, max(tch) max_touch, min(tch) min_touch, avg(tch) avg_touch from x$kcbwds wds, /* working data sets */ x$kcbwbpd bpd, /* buffer pools */ x$bh bh, /* buffer headers */ obj$ o,/* objects */ user$ u where wds.set_id = bpd.bp_lo_sid and wds.set_id = bpd.bp_hi_sid and bpd.bp_size != 0 and bh.indx between wds.start_buf# and wds.end_buf# and o.dataobj# = bh.obj and bh.state !=0 and o.owner# !=0 /* exclude sys */ and o.owner# = u.user# group by bpd.bp_name, u.name, o.name order by bpd.bp_name, u.name, o.name ; **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jay Hostetter 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: what's the exact meaning of those messages?
Check your backup script to see if it forces log switches (alter system archive log). If it does, you may be hitting bug 1377090. See note 119547.1. It is a message that you can ignore if it occurs during the alter system.. command. Jay Hostetter Oracle DBA D. E. Communications Ephrata, PA USA [EMAIL PROTECTED] 12/30/02 02:33AM Hi, I found the following message from the alert.log: Sun Dec 29 22:35:42 2002 ARC0: Beginning to archive log# 3 seq# 126 ARC0: Failed to archive log# 3 seq# 126 Sun Dec 29 22:35:42 2002 ARCH: Completed archiving log# 3 seq# 126 It seemed the ARC didn't work in the begining of archive, but worked finally after a while, I got lots of those messages from alert.log file. what's the exact meaning of those messages? My Oracle is 8.1.7.3.0. and OS is aix 5.1 Thanks in advance. Jim [EMAIL PROTECTED] 2002-12-30 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jim 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). **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jay Hostetter 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: Enqueue Waits in Oracle Financials
John, I know this is an old topic, but Oracle Support proposed a patch and we finally got it tested. It looks like this enqueue wait goes away with the application of concurrent processing rollup patchset C 2385942. Thanks, Jay Hostetter Oracle DBA D. E. Communications Ephrata, PA USA [EMAIL PROTECTED] 12/05/02 07:39PM Jay, Does this come from the alert manager? Do you have any of those new-fangled 11i modules (or should I call the 'mangled'!!). You could use the script below (adapted from Govind who posted this a few days back) set pages 100 column sid_serial format a10 heading Sid/Ser# column username format a15 heading DB/OSUser column start_time format a18 heading StartTime column mins_pending format 999 heading Mins column used_ublk format heading Blks column name format a10 heading Rbs Name column status format a12 heading Status select sid || '/' || serial# sid_serial, username || '/' || osuser username, substr(t.start_time,1,18) start_time, round( ( sysdate - TO_DATE( start_time, 'MM/DD/YY HH24:MI:SS') ) *24*60 ,0 ) mins_pending, r.name, t.used_ublk , decode(t.space, 'YES', 'SPACE TX', decode(t.recursive, 'YES', 'RECURSIVE TX', decode(t.noundo, 'YES', 'NO UNDO TX', t.status))) status from v$transaction t, v$rollname r, v$session s where t.xidusn = r.usn and t.ses_addr = s.saddr order by t.start_time / If the OS user turns out to be 'applmgr' for any waiting TXN then pursue this from the CM side. Otherwise, you can look at the Forms users. In any case, are you using OAM (Oracle Applications Manager)? John Kanagaraj Oracle Applications DBA DB Soft Inc Work : (408) 970 7002 Listen to great, commercial-free christian music 24x7x365 at http://www.klove.com ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -Original Message- From: Jay Hostetter [mailto:[EMAIL PROTECTED]] Sent: Thursday, December 05, 2002 8:25 AM To: Multiple recipients of list ORACLE-L Subject: Enqueue Waits in Oracle Financials I noticed a lot of enqueue wait events in our 11i database. I ran some queries and was able to determine the process that is incurring these waits. I dutifully did a set event 10046 and examined the trace file. I've also queried v$lock. I've figured out that this is a UL (user defined) wait. Now I'm stuck. I haven't figured out exactly what we are waiting for. Although by monitoring the current SQL statement for the offending process, I see that it does a SELECT FOR UPDATE in the FND_CONCURRENT_REQUESTS and FND_CONCURRENT_PROGRAMS tables. This creates a TM lock, which I see, but I don't think it explains the UL lock. I've seen examples on how to interpret p1 for an enqueue lock, but not p2. I would appreciate a little guidance. I believe that the offending process is the Internal manager, but I would like to understand a little more about what is occurring. Is this a typical problem in 11i? I guess the ICM may issue user defined locks, then just waits for a certa! in! amount of time. I would guess that all 11i databases have a high number of enqueue waits if this is the case. I am running 11.5.6 against 8.1.7 on Tru64. Thank you, Jay Sample output from the trace: WAIT #114: nam='enqueue' ela= 102 p1=1431044098 p2=1073807913 p3=0 WAIT #114: nam='enqueue' ela= 103 p1=1431044098 p2=1073807914 p3=0 WAIT #114: nam='enqueue' ela= 102 p1=1431044098 p2=1073807915 p3=0 So if I check out p1 I see a UL lock mode 2: SQL run 1 SELECT chr(bitand(1431044098,-16777216)/16777215)|| 2 chr(bitand(1431044098, 16711680)/65535) Lock, 3 to_char( bitand(1431044098, 65535) )Mode 4* from dual Lo M -- - UL 2 cut **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jay Hostetter 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
Re: RMAN and cron
Ron,
Try setting your environment variables in your test.sh script. I'm not a unix guru,
so I can't tell you why your profile isn't being used when the script is run from
cron, but my guess is that is what is happening.
Jay
[EMAIL PROTECTED] 12/23/02 09:23AM
list,
Cron server os RedHat 7.2
Database server os OpenVMS 7.3-1
Database Oracle 8.1.7 rel 3
Rman server os OpenVMS 7.3-1
Rman Oracle 8.1.7 rel 3
On the Cron server I have created a script that will backup the
database server and catalog the action on the rman server. When I try to
have cron perform the backup sctipt I get
RMAN-571 and LEM-00031, and LEM-00033 error message. According to
Metalink it is an environmental error that the oracle variables are not
set. I have set up my environment as follows:
file: /alphaprd/profile
ORACLE_HOME = cron server ORACLE_HOME
ORACLE_BASE =cron server ORACLE_BASE
LD_LIBRARY_PATH = cron server LD_LIBRARY_PATH
TNS_ADMIN = cron server TNS_ADMIN
export ORACLE_HOME ORACLE_BASE LD_LIBRARY_PATH TNS_ADMIN
Script to backup the database controlfile: test.sh
./alphaprd/profile
echo start backup: /alphaprd/rman_test.log
/home/oracle/OraHome1/bin/rman EOF /alphaprd/rman_test.log
connect target sys/password@database server
connect catalog rman/rman@rman server
run {
allocate channel di type disk;
backup format
'orabck:[backups]control_%U.bus'
(current controlfile);
}
EOF
Crontab entry as ROOT:
10 01 * * 1 su oracle -c /alphaprd/test.sh -u
OR
Crontab entry as ORACLE
10 01 * * 1 /alphaprd/test.sh
The script functions properly if I run it from the command line as
oracle but fails with
RMAN-571 and LEM-00031 and LEM-00033 errors when run from cron.
I don't see how it could be Oracle environmentals when I am running it
as an Oracle cron and the target and catalog are on a different server
that the cron server.
Can you point me in the direction where I can get this resovled?
Thanks,
Ron
--
**DISCLAIMER
This e-mail message and any files transmitted with it are intended for the use of the
individual or entity to which they are addressed and may contain information that is
privileged, proprietary and confidential. If you are not the intended recipient, you
may not use, copy or disclose to anyone the message or any information contained in
the message. If you have received this communication in error, please notify the
sender and delete this e-mail message. The contents do not represent the opinion of
DE except to the extent that it relates to their official business.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jay Hostetter
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: Help Urgent Where is Tablespace Manager??
We stopped using it for the same reasons, plus the fact that we had multiple Oracle Homes on our cluster - which confused OEM. Where is tablespace manager - just type sqlplus Jay [EMAIL PROTECTED] 12/10/02 09:04AM Paula, I am using DBA Studio as a stand-alone product - not using the OMS service part of the product. I gave up on that because it could not discover all of the databases at my current site. We tried to figure out why it didn't work but gave up when I started looking at the network/firewall issues we have here. We also use either Cron or Maestro to schedule all of the backup jobs. Soo, what did that leave for OEM to do? It ended up being just another software product to support that was just not worth the effort to get to work properly. So, we don't use it. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Tuesday, December 10, 2002 7:54 AM To: Multiple recipients of list ORACLE-L Well I installed Version 2.2 of OEM and 9i OEM until I can figure this out in separate homes. I understand that some of the functionality (from Metalink threads) seems to have been gone and that there is issues about which version of OEM and support which version of database and how well the newest version of OEM works in some basic functionality (like discovering services). So has everyone stopped using OEM? -Original Message- Sent: Monday, December 09, 2002 10:11 AM To: '[EMAIL PROTECTED]' This is driving me crazy. Can't seem to find the tablespace manager which I used to pull up from DBA Studio - now I have a different version which doesn't have DBA Studio - where has it been moved or is there a replacement? Help! **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jay Hostetter 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: Enqueue Waits in Oracle Financials
John, Yes, the PMON method is lock. The ICM runs 1 process with a sleep time of 30 seconds and a null value for cache size.All of our managers sleep at least 30 seconds, with the exception of a Service Manager, which is null. I'll probably end up logging a TAR. Thanks, Jay [EMAIL PROTECTED] 12/06/02 07:13PM Jay, Is the PMON method set to LOCK? See the output of the following SQL select profile_option_value from applsys.Fnd_Profile_Option_Values where Level_ID = 10001 And Level_Value = 0 And Application_ID = 0 And Profile_Option_ID = ( Select Profile_Option_Id From apps.Fnd_Profile_Options Where Profile_Option_Name = 'CONC_PMON_METHOD') I believe the ICM (Internal Concurrent Manager) places its own locks for scheduling reasons - maybe that is why you are seeing UL locks. You might also want to check with the Apps SYSADMIN account holder if anything has been changed wrt scheduling (could be the Cache size or Sleep seconds for any of the managers). You can verify if something has been changed by looking at the LAST_UPDATE_DATE on most FND tables. Hth, John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 What would you see if you were allowed to look back at your life at the end of your journey in this earth? ** The opinions and statements above are entirely my own and not those of my employer or clients ** **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jay Hostetter 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: Enqueue Waits in Oracle Financials
Here is the output from the query: Sid/Ser# DB/OSUser StartTime Mins Rbs NameBlks Status -- --- -- -- - 14/106 APPS/applmgr12/06/02 01:29:32 518 RBS2 1 ACTIVE 33/537 APPS/applmgr12/06/02 07:59:42 128 RBS11 1 ACTIVE 160/285APPS/applmgr12/06/02 09:29:0139 RBS10 1 ACTIVE 165/234APPS/applmgr12/06/02 09:58:54 9 RBS14 1 ACTIVE 71/240 APPS/applmgr12/06/02 10:07:24 0 RBS24 1 ACTIVE 45/2 APPS/applmgr12/06/02 10:07:37 0 RBS8 1 ACTIVE 83/280 APPS/applmgr12/06/02 10:07:42 0 RBS27 1 ACTIVE 7 rows selected. The offending SID today is 16, which I don't see in the output from the above query. SQL run 1 select sid, 2 event, 3 total_waits tws, 4 total_timeouts tt, 5 time_waited tw, 6 average_wait avgw 7 from v$session_event 8 where event = 'enqueue' 9* order by time_waited desc,event Sess Total Total Time (ms) Avg (ms) ID Wait Event Waits TimoutsWaited Wait - - --- - 16 enqueue 55945589572543 102 10 enqueue 1 013 13 45 enqueue 1 012 12 I map this SID (16) back to the Internal Manager. By the way, SID 14 (with the highest Mins in your query) is the Service Manager. We scaled back our Alert manager to 1 process because we replaced some of our Alerts with triggers. The Alerts where just too much of a performance problem on our system (they were over 1/2 of our concurrent requests). Do we have any new fangled modules? Yes. Service and Contracts. Are we using OAM? It is installed, but we're not using it. I stumbled into it already and brought up some pretty graphs. Thanks, Jay [EMAIL PROTECTED] 12/05/02 07:39PM Jay, Does this come from the alert manager? Do you have any of those new-fangled 11i modules (or should I call the 'mangled'!!). You could use the script below (adapted from Govind who posted this a few days back) set pages 100 column sid_serial format a10 heading Sid/Ser# column username format a15 heading DB/OSUser column start_time format a18 heading StartTime column mins_pending format 999 heading Mins column used_ublk format heading Blks column name format a10 heading Rbs Name column status format a12 heading Status select sid || '/' || serial# sid_serial, username || '/' || osuser username, substr(t.start_time,1,18) start_time, round( ( sysdate - TO_DATE( start_time, 'MM/DD/YY HH24:MI:SS') ) *24*60 ,0 ) mins_pending, r.name, t.used_ublk , decode(t.space, 'YES', 'SPACE TX', decode(t.recursive, 'YES', 'RECURSIVE TX', decode(t.noundo, 'YES', 'NO UNDO TX', t.status))) status from v$transaction t, v$rollname r, v$session s where t.xidusn = r.usn and t.ses_addr = s.saddr order by t.start_time / If the OS user turns out to be 'applmgr' for any waiting TXN then pursue this from the CM side. Otherwise, you can look at the Forms users. In any case, are you using OAM (Oracle Applications Manager)? John Kanagaraj Oracle Applications DBA DB Soft Inc Work : (408) 970 7002 Listen to great, commercial-free christian music 24x7x365 at http://www.klove.com **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jay Hostetter 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: Are Oracle courses required for Oracle Certification now?
Hang on to those sheets! Mine were stamped Do Not Lose This Report. After I finished all of my tests, I waited and waited for my certification in the mail. I finally called Oracle Education. They didn't have 2 of my tests on record. I had to fax my copies of the reports to them! Jay Hostetter Oracle DBA D. E. Communications Ephrata, PA USA 2+ of snow at 8:00 am and still falling! Wuhoo! [EMAIL PROTECTED] 12/04/02 06:59PM Are you getting a sheet after each exam showing how many you got correct for each section and your overall score? I received that sheet at the testing center after each exam. Other than that, you won't get anything from Oracle until you finish your final exam. It took me about a month to get my packet. At 01:13 PM 12/4/02 -0800, you wrote: Lyndon - I have completed 2 of the 5 Oracle8i exams. Hey - when I took each exam, the testing organization says that I should receive something from Oracle within a couple of weeks. Got nothing. Should I be worried? Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, December 04, 2002 1:10 PM To: Multiple recipients of list ORACLE-L Hey William, Why are you 40% OCP - does this mean you are an OCA? When did you take your very first 9i certification exam? If you took it before Sept. 1, 2002, the you're a 50% OCP. -- Lyndon Tiu Quoting DENNIS WILLIAMS [EMAIL PROTECTED]: My instructor in a recent Oracle Education class said that there were a couple of smart alecs that caused quite a stir within Oracle. After receiving their OCP, they couldn't log into a database, and claimed it was because they had never actually used Oracle. The instructor indicated that the new requirement (9i I believe) would require you to take at least one class. I asked do you mean everyone that takes a class from you will be logging on. He just grinned. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jay Hostetter 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).
Enqueue Waits in Oracle Financials
that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jay Hostetter 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:The future DBAs?
The hard part is explaining to people that don't quite understand the concept. That is an understatement! In fact, most people in our IT department get along - that is until we have meetings to discuss data models. Jay [EMAIL PROTECTED] 11/26/02 05:19PM Personally, I like Data Architecture. And data modeling. I never could get enough of that. The hard part is explaining to people that don't quite understand the concept. Dave Hay rules! http://www.amazon.com/exec/obidos/tg/detail/-/0932633293 Being the sole DBA for the company, I don't get nearly enough opportunities for this anymore, and don't have the time for much of it anyway. Jared [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/26/2002 10:04 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re:The future DBAs? Well, I give MicroSlop pretty poor grades for predicting the future and Monster.com is absolutely useless (naw make that less than) at job stuff in general. I will agree with the person who wrote the article on one point. The job of being a DBA is changing and we all need to remain flexible to remain useful in the marketplace. That in some cases means spreading our wings from the historical role of DBA. We may need to become part time (or full time) data architects, reporting tool experts, etc... But in the end, I don't see us degrading to the level of an order entry clerk nor order entry clerks upgrading to DBA's. As usual the MicroSlop propaganda machine is at work again. Dick Goulet Reply Separator Author: Arup Nanda [EMAIL PROTECTED] Date: 11/25/2002 5:48 PM Fellow DBAs and other DBA wannabes, Ever wondered the best path into a DBA career? Microsoft offers a brilliant way. MSN Careers at http://editorial.careers.msn.com/articles/nofuture/ suggests some jobs are effectively dead, like farmers and sewing machine operators and how the experts in that field can progress to the next logical career move. Guess which profession's logical career move is database administrator? See the excerpt from the webpage here in the attachment as a picture. I just couldn't resist posting it here. May be they are referring to SQL Server DBAs? Arup Nanda **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jay Hostetter 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: redo log file setup with mirrored drives
going to go with what I have actually seen tempered by any tangible, objective, hard evidence I come across. Now for those who are into this worst scenario thing let me ask you: What if I put your storage array between a 30HP air conditioning blower moter and a spot welder, and run a couple of paint shakers on top of the array to boot. What will your vaunted Oracle multiplexing do for you then? Huh? Well, smarty pants, I'm waiting! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephen Lee **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jay Hostetter 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: redo log file setup with mirrored drives
Addressing the corruption issue, Kirti's statement is not speculation. Because my OS/hardware IS reliable a corrupted log file that is mirrored outside of Oracle will be corrupt - the original is corrupt, so is the mirror. If I mirror my log files using Oracle, logfile A may be corrupt, but log file B may NOT be corrupt, depending on what caused the corruption (if it is some Oracle bug, then you're out of luck either way). We had a case where all files that were open on a particular file system became corrupt. The cause was related to a bug in the cluster software during a system crash. This file system was RAID 0+1 - which meant that my file was safe, corruption and all. Fortunately, I had Oracle mirroring the redo log on another file system which was unaffected by the crash. Jay [EMAIL PROTECTED] 11/26/02 10:23AM If I may offer another view -Original Message- Having multiple redo log members has its advantages. The archiver process 'knows' these multiple members and it will optimize the archiving process, Is there any supporting documentation about this optimizing? Are you saying that the makers of hardware-based and software-based RAID have not optimized their RAIDing? If I were a betting man, I would bet that a hardware device can do mirrored writes faster than Oracle. but it does not know about the mirrored copies of these logs. Know? What does it need to know? Mirroring is mirroring. A mirrored copy either exists, or it doesn't. Knowing about it has no effect on the existence of the copy. Computer operations aren't based on faith (although there are many times we are tempted to question that). The other important thing to know is that Oracle issues a separate write for these log members And this improves performance? and in an unlikely event a corrupted write will be restricted to just the affected member. Such corruption will affect all the mirrored copies. Two things: 1. This is pure speculation. 2. If your OS can't do reliable disk writes, then it's time to get a new OS. A database consists of more than just redo logs. It also has pesky little things like data files. Should we have Oracle mirror those too rather than rely on RAIDing for fault tolerance? Why would we expect the OS to reliably write data files and detect hardware errors when it can't reliably maintain redo logs? Pending further evidence to the contrary, I'll take mirroring external to Oracle as the better choice. **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jay Hostetter 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: Oracle is a time machine!!
Did you try it with different NLS_LANGUAGE and NLS_TERRITORY settings?
[EMAIL PROTECTED] 11/22/02 03:49PM
Yes. This is where it gets interesting. England and the colonies didn't
adopt the Gregorian Calendar Reform until September, 1752.
October, 1582 had its normal 31 days in the British Empire and September,
1752 had its normal 30 days outside the British Empire.
So what does all this mean? Technically, Oracle's date routines are correct
in some parts of the world but not others.
Happy Friday,
Mark Stahlke
Oracle DuhBA
Denver Newspaper Agency
-Original Message-
Sent: Friday, November 22, 2002 11:40 AM
To: Multiple recipients of list ORACLE-L
Subject:Re: Oracle is a time machine!!
Does it have any relation to year 1752 adjustment for leap year?
Try following on unix ..
cal 9 1752
September 1752
S M Tu W Th F S
1 2 14 15 16
17 18 19 20 21 22 23
24 25 26 27 28 29 30
-Rachna
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Friday, November 22, 2002 12:55 PM
Don't believe me?? Try this:
create table test(the_date date);
insert into test values (to_date('10-05-1582','mm-dd-') );
select to_char(the_date, 'mm/dd/') from test;
What do you get? :-))
**DISCLAIMER
This e-mail message and any files transmitted with it are intended for the use of the
individual or entity to which they are addressed and may contain information that is
privileged, proprietary and confidential. If you are not the intended recipient, you
may not use, copy or disclose to anyone the message or any information contained in
the message. If you have received this communication in error, please notify the
sender and delete this e-mail message. The contents do not represent the opinion of
DE except to the extent that it relates to their official business.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jay Hostetter
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: Dynamic views
David, You're talking about Partitioned Views, which was the predecessor of partitioned tables. This was the drawback of partitioned views - you had to insert into the individual tables. You can make a sql*loader script figure out which table to insert into (if you're using sql*loader). Otherwise you might what to try writing a procedure. If you have any problems with partitioned views, expect Oracle Support to tell that you should be using partitioned tables. Jay Hostetter Oracle DBA D. E. Communications Ephrata, PA USA [EMAIL PROTECTED] 11/20/02 01:48PM Hi Guys I'm trying to see if I can get around paying oracle $50,000 for partitioning I have a huge table and I want to partition it on date so I created 12 tables _JAN _FEB And so on But I want to create a view that will be used for inserting so it always Points to the current month without having to recreate the view every month. Is there anyway anybody can think of doing this? Thanks David Hill DBA Le Chateau **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jay Hostetter 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: Cognos Reporting Tool
They make several products. We use Impromptu (reporting tool) to feed PowerPlay (OLAP tool). When we did the analysis several years ago, we chose PowerPlay. Building cubes was much easier with PowerPlay than with Oracle Express. Although Express probably scales better. We use PowerPlay to summarize 250 million call records. The trick is to sort and group them in the database before feeding them to PowerPlay. You need individuals with these characteristics to use the tool: 1) Analytical skills 2) Understanding of the data We have a few key users that really use the tool to the fullest extent of its potential. Many folks who were trained just don't grasp the cube concept. This is not the fault of the tool - you will have this regardless of the tool that you use. You will want to have a server dedicated to building and storing the cubes. If they try to push the web version of PowerPlay, be sure to compare the functionality to the client tool. We have the web version - it is good for bringing up some canned PowerPlay reports, but you really want the client version for writing reports and exploring the cubes. I don't know if this has changed in the last 2 years or not. Jay Hostetter Oracle DBA D. E. Communications Ephrata, PA USA [EMAIL PROTECTED] 11/18/02 02:46PM Good afternoon listers. I just found out that I will be meeting with the sales dog and pony folks from Cognos on Tuesday. Have any of you worked with this product? What should I be aware of? What plusses/minuses should I look for? Any suggestions would be welcome. Thanks Rodd Holman **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jay Hostetter 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: SQL Brain Teaser Challenge
Does an in-line view do the trick? select * from (select id, parentid, nodeorder, description from treenode start with parentid=0 connect by prior id = parentid) order by nodeorder; Jay [EMAIL PROTECTED] 11/05/02 12:24PM Challenge: present SQL results hierarchically and sort the nodes. Use sort column without changing data. Here's the DDL/DML to start: create table treenode ( id number not null constraint pk_treenode primary key, parentidnumber not null, nodeorder number not null, description varchar2(20)null); insert into treenode values(1,0,0,'top folder'); insert into treenode values(9,1,0,'1st subfolder'); insert into treenode values(7,1,2,'3rd subfolder'); insert into treenode values(2,1,1,'2nd subfolder'); insert into treenode values(8,7,1,'folder 3 item 2'); insert into treenode values(6,2,3,'folder 2 item 3'); insert into treenode values(5,7,0,'folder 3 item 1'); insert into treenode values(3,2,2,'folder 2 item 2'); insert into treenode values(4,2,1,'folder 2 item 1'); - Here's the data presented hierachically without the desired sort: select * from treenode start with parentid=0 connect by prior id = parentid; ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 7 1 2 3rd subfolder 8 7 1 folder 3 item 2 5 7 0 folder 3 item 1 2 1 1 2nd subfolder 6 2 3 folder 2 item 3 3 2 2 folder 2 item 2 4 2 1 folder 2 item 1 - Desired SQL statement results: ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 6 2 3 folder 2 item 3 7 1 2 3rd subfolder 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 - Kudos to anyone who can figure out how to do this via SQL. Steve Orr Bozeman, Montana -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve 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). **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jay Hostetter 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: Theory v Practice
When code is developed to be database generic, developers will steer away from code for a specific database. However, foreign key and check constraints hardly fall into this category. I don't use Sqlserver or DB2, but I would guess that they implement FKs and constraints. Your developers are wasting a lot of time coding something that the database will do for them. The more logic you can put into the database, the better off you are. They'll have to code the constraints all over again when they want to develop a web front-end. You'll probably see some bad data into your tables due to coding bugs. Oracle isn't bug free, but they've got the constraints nailed down pretty well. Jay [EMAIL PROTECTED] 10/23/02 01:45PM The developers working on our new VB app are also responsible for setting up the Oracle DB behind it. The app is for an order entry/despatch/warehouse system with 5 million customers and 1000 orders per day. We have nearly 400 tables. They are not planning on using primary keys/secondary keys, as they say they will handle all the constraints via VB. I only have a theoretical knowledge of database design, which says this is very wrong. Is the Oracle system being used as anything more than an expensive file system? In real world scenarios, is this a common practice? Regards Craig Healey **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jay Hostetter 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: OEM can't seem to discover 1 instance
Scrap OEM. Seriously, we ran into way too many of these little annoying problems. We decided that we had better things to do with our time. Have you had to re-register all of your jobs, yet? That one was a real pain. Jay [EMAIL PROTECTED] 10/18/02 09:38AM Hmm, thanks to all who are replying. It looks like the instance is not making it into either snmp_rw.ora, snmp_ro.ora or the services.ora. I have manually edited them, but when I restart the agent, it wipes out the manual entries in the snmp_ro.ora and the services.ora. The oratab and listener look fine. Any more good advice? TIA, John P Weatherman Database Administrator Replacements Ltd. **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jay Hostetter 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: Archive files and their Management
I'm not quite sure what you mean by modify the database startup procedure. You must be in MOUNT mode to change between archive and noarchive. You don't need to change the mode each time you startup you database. Once you put a database in archivelog mode, you are in that mode until you change it - no matter how many startup/shutdowns you do. It doesn't matter if you have a cold vs. hot backup. As long as you have database backup and all of your archivelogs, you can do a point-in-time recovery from that backup until your last archivelog. Jay [EMAIL PROTECTED] 10/18/02 12:09PM I'm up for my backup and recovery class here soon, but this conversation has brought up questions that I think some of you wouldn't mind discussing anyway. I see where implementing archive logs requires that you modify the database startup procedure to turn on archiving before opening the database. At this point the DBA should do an immediate baseline backup (orafaq =)) which implies a hot/online backup. I am curious how this goes with point in time cold backups. ie. DBA does a cold backup opens the database in archive log mode. Can you apply accumulated archive logs to a restore from cold backup ? perhaps there are issues with the control file information. -Original Message- Sent: Friday, October 18, 2002 7:53 AM To: Multiple recipients of list ORACLE-L theoretically only the archive files after the backup started are needed. but there might be issues of a corrupted backup, and to guard against that we have a policy of keeping archive files for one week. you should also keep checking your backups by restoring them to a separate location periodically regards Naveen **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jay Hostetter 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: Oracle pricing...
Amar, All human users and non-human operated devices that are accessing the program Check out the software investment guide : http://www.oracle.com/corporate/pricing/index.html?sig.html In particular, I like the 400-employees-on-30-forklifts example (pg. 15 of the guide). I would love to see an Oracle sales rep. explain that one to our CFO! Jay [EMAIL PROTECTED] 10/09/02 06:08AM Hi, found the following information on Oracle site. What I am confused about is Named users license charges. How is this calculated? Are these charged for Oracle users also- SYS/SYSTEM etc. Product Named users Licence Processor Licence Oracle db(enterprise) 800 4 Oracle db(standard) 300 15000 Oracle db(personal) 400 -- rgds amar http://amzone.netfirms.com **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jay Hostetter 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).
Process question
Do I have something wrong with this query? I thought that each oracle OS process
corresponded to one database connection (unless you are using MTS, which we aren't).
This query surprised me. It shows that two different oracle database processes each
correspond to 3 database connections. Can someone explain this?
Thanks,
Jay
SQL run
1 select vs.username,last_call_et, vp.pid,
2 vs.sid,
3 vs.serial#,
4 vs.osuser,
5 vs.machine,
6 vs.process,
7 vp.spid
8 from v$session vs, v$process vp
9 where vs.paddr = vp.addr
10* and process in ('2949917','2952943')
USERNAME LAST_CALL_ETPID SIDSERIAL# OSUSER MACHINE
PROCESS SPID
-- - --
- - -
APPS13180 1511456 applmgr curly.pcsone.com
2949917 2950637
APPS13227 15 132951 applmgr curly.pcsone.com
2949917 2950637
APPS13746 15 157198 applmgr curly.pcsone.com
2949917 2950637
APPS12761136 125961 applmgr curly.pcsone.com
2952943 2953312
APPS12840136 171 9 applmgr curly.pcsone.com
2952943 2953312
APPS12808136 174 13 applmgr curly.pcsone.com
2952943 2953312
6 rows selected.
**DISCLAIMER
This e-mail message and any files transmitted with it are intended for the use of the
individual or entity to which they are addressed and may contain information that is
privileged, proprietary and confidential. If you are not the intended recipient, you
may not use, copy or disclose to anyone the message or any information contained in
the message. If you have received this communication in error, please notify the
sender and delete this e-mail message. The contents do not represent the opinion of
DE except to the extent that it relates to their official business.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jay Hostetter
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: Backup Strategy - Informal Survey
I do nightly exports of my large databases with ROWS=N. This way I can restore users, grants, indexes, table definitions, etc. Jay Hostetter Oracle DBA D. E. Communications Ephrata, PA USA [EMAIL PROTECTED] 09/20/02 10:13AM I'd like to pose a question to you all and get your response. If you are running a database that is larger than 250GB, what place in your backup strategy does a logical export have? Do you do logical exports at all, and if so with what frequency? Do you feel that logical exports are an important part of your backup/recovery strategy? Robert G. Freeman - Oracle OCP Oracle Database Architect CSX Midtier Database Administration Author of several Oracle books you can find on Amazon.com! The avalanche has begun, It is too late for the pebbles to vote. **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jay Hostetter INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: RMAN implementation strategy
Sean, I wasn't able to put our catalog database on another server, so after backups I export the catalog database and ftp the .dmp file to another machine. As far as the 7 year requirement, can you export the relevant data and save the .dmp file on tape for 7 years? Your biggest issue will be upgrades. When you are on 9i, you can't restore an 8i backup. You would have to keep 8i around for 7 years. I don't purge any of my backup information out of the catalog, in case I have to restore an old backup. But I haven't gone through any upgrades since I've been using rman. Important data that may be needed in the future is exported an copied to tape. Jay [EMAIL PROTECTED] 09/19/02 06:08AM Hi Folks, We're planning to implement RMAN as part of our BR solution and by extenstion also as part of our DR solution. I've been trying to locate information on how best to configure RMAN across our organisation. For example it's advised you place catalog on separate server to production server. So server A might house catalog for server B and vice versa. But in a DR scenario where both servers could be destroyed there are I 'suspect' potential implementations on overall MTTR depending on configuration. Is it then perhaps better to locate all catalogs on a dedicated server which ideally would be replicated somehow to eliminate it as a singal point of failure. Also we have a requirment to be able to potentially recover data as far back as 7 years. These are currently comprised of monthly backups taken out of regular cycle and archived off site. I'm thinking it might be an idea to set up a two catalogs, one for regular monthly cycle and another to record these monthly archives as the maintenace of the catalog might be cumbersome trying to ensure the montlhy archive data records do not get accidentally deleted. I've had a trawl across the Web courtesy of Google but did not find any papers which appear to deal with these type of issues. The RMAN User's Guide and Reference does not appear to address them either. Your feedback/comments or references to papers would be much appeciated!. Oracle 7.3.3, 8.0.5, 8.1.7 NT4, W2K - Seán O' Neill Organon (Ireland) Ltd. [subscribed: digest mode] **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jay Hostetter 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: Privileges needed for truncate
Jack,
It used to be DELETE ANY TABLE. I'm not sure if that has changed in recent
versions. Long ago, I setup the following procedure to allow a specific table to be
truncated, without out the DELETE ANY TABLE privilege. It must be owned by the owner
of the table. I haven't dealt with this issue in years, so if things have changed
since Oracle 7, somebody else can chime in.
Jay
CREATE OR REPLACE PROCEDURE TRUNC_TAB
(TAB_IN IN USER_TABLES.TABLE_NAME%TYPE)
-- Author : Tony Ziemba Sheck Cho 4/18/95
-- Modified :
-- 10/14/97 JMH
-- 08/06/99 JMH Allow only certain tables to be truncated.
-- 08/08/99 JMH Added more tables.
-- Description:
-- This procedure was developed to truncate a table using the table name that is
-- passed in as an input parameter. This procedure illustrates the use of the
-- dbms_sql package to execute SQL DDL statements within PL/SQL. As of v7.1
-- TRUNCATE cannot be executed on a table unless the user owns the table or
-- the user has DELETE ANY TABLE privelege. This procedure is a workaround to
-- those limitations.
--
AS
cursor_id integer; -- holds cursor id
return_value integer;-- holds call return value
str varchar2(150); -- string to hold DDL statement
e_wrongtable exception; -- exception when truncate is done on other tables.
BEGIN
IF tab_in in ('TS_SECURITY_ACCESS','GL_CODE_COMBINATIONS') THEN
str := 'truncate table '||tab_in;
cursor_id := dbms_sql.open_cursor;
dbms_sql.parse ( cursor_id,str,dbms_sql.native);
-- DDL statements are executed immediately. This may change
-- in future releases, in which case the following statement will
-- be needed.
-- return_value := dbms_sql.execute(cursor_id);
dbms_sql.close_cursor(cursor_id);
ELSE
RAISE e_wrongtable;
END IF;
EXCEPTION
WHEN e_wrongtable THEN
RAISE_APPLICATION_ERROR(-2,'Procedure restricts tables that can be
truncated.');
WHEN OTHERS THEN
dbms_sql.close_cursor(cursor_id);
END;
/
[EMAIL PROTECTED] 09/19/02 10:23AM
Hi
I need to create a user/role that among other stuff must be able to
truncate a table. I can't figure out which privileges are needed (DBA is a
bit OTT :-))
Try them one by one does not sound appealing at all
TIA
Jack
**DISCLAIMER
This e-mail message and any files transmitted with it are intended for the use of the
individual or entity to which they are addressed and may contain information that is
privileged, proprietary and confidential. If you are not the intended recipient, you
may not use, copy or disclose to anyone the message or any information contained in
the message. If you have received this communication in error, please notify the
sender and delete this e-mail message. The contents do not represent the opinion of
DE except to the extent that it relates to their official business.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jay Hostetter
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: sqlplus question
Instead of using colsep, try this: select ename||'|'||job from emp; I was going to suggest - col emp form a4 but if it can be more than 4 positions, it will wrap. Jay [EMAIL PROTECTED] 09/19/02 11:53AM I don't want just 4. It's variable length and I want the actual number of valid bytes. Bill Carle ATT Database Administrator 816-995-3922 [EMAIL PROTECTED] -Original Message- Sent: Thursday, September 19, 2002 9:51 AM To: '[EMAIL PROTECTED]'; Carle, William T (Bill), ALCAS Subject:RE: sqlplus question If you want only 4 bytes, us the SUBSTR function to take only what you need. SQL select ename, job 2 from emp; SMITH CLERK ALLEN SALESMAN WARD SALESMAN JONES MANAGER SQL set colsep '|' SQL / SMITH |CLERK ALLEN |SALESMAN WARD |SALESMAN JONES |MANAGER SQL select substr(ename,1,4), job 2 from emp; SMIT|CLERK ALLE|SALESMAN WARD|SALESMAN JONE|MANAGER -Original Message- Sent: Thursday, September 19, 2002 9:19 AM To: Multiple recipients of list ORACLE-L Howdy, I am spooling my sqlplus output to a file with no headings and all the fields separated by a delimiter. I have a field that is defined as varchar2(56), but typically only 4 or 5 bytes are filled. Oracle recognizes that and if you select length(fld1) from the table, you will get 4. But if I spool this to a file, I always get the full 56 bytes padded with blanks. In other words, I get 4 bytes of data and 52 blanks for that field. I only want the four valid bytes so that my delimiter comes immediately after that 4th byte. My sqlplus options are as follows: set newpage 0 space 0 linesize 5000 pagesize 0 echo off recsep off feedback off heading off trimspool on colsep | Bill Carle ATT Database Administrator 816-995-3922 [EMAIL PROTECTED] **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jay Hostetter 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).
Process question
Do I have something wrong with this query? I thought that each oracle process
corresponded to one database connection (unless you are using MTS, which we aren't).
This query surprised me. It shows that two different oracle database processes each
correspond to 3 database connections. What am I missing?
Thanks,
Jay
SQL run
1 select vs.username,last_call_et, vp.pid,
2 vs.sid,
3 vs.serial#,
4 vs.osuser,
5 vs.machine,
6 vs.process,
7 vp.spid
8 from v$session vs, v$process vp
9 where vs.paddr = vp.addr
10* and process in ('2949917','2952943')
USERNAME LAST_CALL_ETPID SIDSERIAL# OSUSER MACHINE
PROCESS SPID
-- - --
- - -
APPS13180 1511456 applmgr curly.pcsone.com
2949917 2950637
APPS13227 15 132951 applmgr curly.pcsone.com
2949917 2950637
APPS13746 15 157198 applmgr curly.pcsone.com
2949917 2950637
APPS12761136 125961 applmgr curly.pcsone.com
2952943 2953312
APPS12840136 171 9 applmgr curly.pcsone.com
2952943 2953312
APPS12808136 174 13 applmgr curly.pcsone.com
2952943 2953312
6 rows selected.
**DISCLAIMER
This e-mail message and any files transmitted with it are intended for the use of the
individual or entity to which they are addressed and may contain information that is
privileged, proprietary and confidential. If you are not the intended recipient, you
may not use, copy or disclose to anyone the message or any information contained in
the message. If you have received this communication in error, please notify the
sender and delete this e-mail message. The contents do not represent the opinion of
DE except to the extent that it relates to their official business.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jay Hostetter
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: log_file_sync io wait question
Have you played around with the size and number of your redo logs? Larger redo logs would mean fewer checkpoints. Dropping redo log member mirrors is tempting, but RAID alone isn't enough protection. I experienced a corrupted file system one time, and I was glad that my redo logs were multiplexed on another file system. [EMAIL PROTECTED] 09/18/02 03:39PM well if you have mirrored members that exist on the same disk thats an issue, if this same disk houses the currently used datafiles that would add to the situation. I placed my redo on a seperate raid-1 and dropped the member mirrors and see better stats. I have a buffer size 1mb but I am using 11i applications. Also consider your log file sizes, the larger the more it has to flush at a given time, but the smaller the more often it has to flush all theses consideration above need to be configured according to what you have available hardware-wise. HTH -Original Message- Sent: Wednesday, September 18, 2002 1:09 PM To: Multiple recipients of list ORACLE-L We have a canned package that we use to insert approx 100 records/second into one table (oltp environment). Each record is just under 1K (datatypes = number and varchar). There are several indexes that are build on the table as the records are inserted. Each and every record is committed. Therefore we are flushing the 'redo log buffer' to disk (online redo logfiles) 100 times per second (once per commit). Not surprisingly we have noticed IO waits which we believe are associated with our 'Redo Log buffer'. Namely ; log_file_sync = 180 waits/sec log_file_parallel_write = 180 waits/sec We tried resizing the 'redo log buffer' from 16K to 256K - but we did not notice any improvements. Neither Log switching or archiving seem to be excessive. 100 records per second seems to be our maximum speed without the application queuing up and Oracle showing very high waits on log_file_sync and log_file_parallel_writes. Does anyone know how we might be able to minimize the IO waits? Thanks in advance. ENVIRONMENT oracle : Oracle 8.1.7.4 os : Sun Sparc Solaris 8 box: 8x8 E10K IO : Hitachi SANS unit through fiber and Brocade switch _ Patrick J. Howe -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Pat Howe 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). **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jay Hostetter 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: Restoring RMAN backups to different host ......
Babu,
You need to copy your backup files to HOST_B. If your file systems are different
thant HOST_A, create symbolic links for the directories where rman expects to find the
backups.
Jay Hostetter
Oracle DBA
D. E. Communications
Ephrata, PA USA
[EMAIL PROTECTED] 09/05/02 08:13PM
Dear List,
Iam following the procedure to restore database from RMAN backup from HOST_A
to HOST_B with a recovery catalog.
1. I copied the init.ora file to HOST_B
2. on HOST_B, I made an entry in oratab for 'rtest' database and switch to
'rtest' database using . oraenv
3. issued: rman target / catalog rman/rman@Connect String
4. startup nomount;
5. run {
allocate channel ch1 type 'sbt_tape';
restore controlfile;
}
It generates the following error. list backup of controlfile is showing up
the backup entries.
RMAN-00571: ===
RMAN-00569: === ERROR MESSAGE STACK FOLLOWS ===
RMAN-00571: ===
RMAN-03002: failure during compilation of command
RMAN-03013: command type: restore
RMAN-03007: retryable error occurred during execution of command: IRESTORE
RMAN-07004: unhandled exception during command execution on channel ch1
RMAN-10035: exception raised in RPC: ORA-19507: failed to retrieve
sequential file, handle=nre1u1kk_1_1, parms=
ORA-27029: skgfrtrv: sbtrestore returned error
ORA-19511: sbtrestore: Backup file not found.
RMAN-10031: ORA-19624 occurred during call to
DBMS_BACKUP_RESTORE.RESTOREBACKUPPIECE
Could someone help me in resolving the issue.
Thanks,
-- Babu
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Janardhana Babu Donga
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
**DISCLAIMER
This e-mail message and any files transmitted with it are intended for the use of the
individual or entity to which they are addressed and may contain information that is
privileged, proprietary and confidential. If you are not the intended recipient, you
may not use, copy or disclose to anyone the message or any information contained in
the message. If you have received this communication in error, please notify the
sender and delete this e-mail message. The contents do not represent the opinion of
DE except to the extent that it relates to their official business.
--
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).
Pinning and loads
This one has me confused - If I have a package pinned, why has it been loaded into
the SGA more than once? The database has been up for about 15 hours.
SQL run
1 select owner || '.' || name OBJECT
2 , type
3 , to_char(sharable_mem/1024,'9,999.9') SPACE(K)
4 , loads
5 , executions execs
6 , kept
7 from v$db_object_cache
8 where type in ('PACKAGE','PACKAGE BODY','FUNCTION','PROCEDURE')
9 and (loads 1 or (executions 5000 and sharable_mem 102400) or executions
2)
10* order by executions
All Packages Not Owned by SYS
OBJECT TYPE SPACE(K)LOADSEXECS KEPT
-- -
SYS.DBMS_OUTPUTPACKAGE 20.3 16 187 YES
SYS.DBMS_APPLICATION_INFO PACKAGE 20.2 181,297 YES
Thanks,
Jay
**DISCLAIMER
This e-mail message and any files transmitted with it are intended for the use of the
individual or entity to which they are addressed and may contain information that is
privileged, proprietary and confidential. If you are not the intended recipient, you
may not use, copy or disclose to anyone the message or any information contained in
the message. If you have received this communication in error, please notify the
sender and delete this e-mail message. The contents do not represent the opinion of
DE except to the extent that it relates to their official business.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jay Hostetter
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Oracle Arm Twisting?
Catherine, We are not running HR. We have Financial Apps (GL, AP, PO, etc.). We have test procedures that the users run through. They test basic functionality (bring up various forms, query data, etc). The procedures amount to around 16-20 man-hours of testing. If a patch only affects a form or two, then that is what we test. Lately we have been applying large groups of patches because we are bringing up another module. This requires us to run through our testing procedures. Jay [EMAIL PROTECTED] 08/15/02 09:38PM Hi Jay, Our Oracle HR consultant said that we are to test only the patch readme.txt Enhanced features as Oracle HR is way too big and it's not possible to test every form and report. Are you on 11I Oracle HR ? If yes, do you mean that you test every single form and report ? If yes, how long does it take ? Regds, Catherine -Original Message- From: Jay Hostetter [mailto:[EMAIL PROTECTED]] Sent: Thursday, August 15, 2002 8:38 PM To: Multiple recipients of list ORACLE-L Subject:RE: Oracle Arm Twisting? Catherine, We all feel your pain. My only suggestion is to test heavily. For every hour of patching that we have, we have several hours of pre-req research and testing on another server. You simply can't run this software without a whole separate environment for testing patches. Is there a support group for 11i dbas? Jay [EMAIL PROTECTED] 08/14/02 11:08PM I guess you are not using Oracle Human Resources Applications. For most of the patches that we've applied, there are new bugs. It's a never-ending applying patches-after-patches ... We've lost count of the number of TARs opened. The worst part is after applying a patch to solve a bug, the new problem which was not documented in the readme file always emerge itself after sometime. By then, it's too late to revert back to the old version. Are we the only company having the problem ? sigh.. Regds, Catherine -Original Message- From: Conboy, Jim [mailto:[EMAIL PROTECTED]] Sent: Thursday, August 15, 2002 5:54 AM To: Multiple recipients of list ORACLE-L Subject:RE: Oracle Arm Twisting? I've called Oracle for a TAR once in the past 18 months. Ah, my friend, but you're not using Portal 9.0.2, are you? I've opened more TARs in the last few months than the rest of my Oracle career. Bleeding edge, I guess, so maybe its our own fault. But third-party support for this would have stopped the project before it got started. I agree that its not accurate to generalize all tech support as nimrods. There are some very good ones out there, and even the rest at least have acccess to all the internal notes we can't get to (Gr...why the hell do they show in metalink searches if we can't see them?). And the developers, if you can ever get to them, know their stuff and are a pretty decent bunch. **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- 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
RE: Oracle Arm Twisting?
Catherine, We all feel your pain. My only suggestion is to test heavily. For every hour of patching that we have, we have several hours of pre-req research and testing on another server. You simply can't run this software without a whole separate environment for testing patches. Is there a support group for 11i dbas? Jay [EMAIL PROTECTED] 08/14/02 11:08PM I guess you are not using Oracle Human Resources Applications. For most of the patches that we've applied, there are new bugs. It's a never-ending applying patches-after-patches ... We've lost count of the number of TARs opened. The worst part is after applying a patch to solve a bug, the new problem which was not documented in the readme file always emerge itself after sometime. By then, it's too late to revert back to the old version. Are we the only company having the problem ? sigh.. Regds, Catherine -Original Message- From: Conboy, Jim [mailto:[EMAIL PROTECTED]] Sent: Thursday, August 15, 2002 5:54 AM To: Multiple recipients of list ORACLE-L Subject:RE: Oracle Arm Twisting? I've called Oracle for a TAR once in the past 18 months. Ah, my friend, but you're not using Portal 9.0.2, are you? I've opened more TARs in the last few months than the rest of my Oracle career. Bleeding edge, I guess, so maybe its our own fault. But third-party support for this would have stopped the project before it got started. I agree that its not accurate to generalize all tech support as nimrods. There are some very good ones out there, and even the rest at least have acccess to all the internal notes we can't get to (Gr...why the hell do they show in metalink searches if we can't see them?). And the developers, if you can ever get to them, know their stuff and are a pretty decent bunch. **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- 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).
11i Conc. Manager Question
For those of you running Oracle Applications 11i, have you modified the cache or sleep time for the standard managers? Ours remains at the default, which is cache 1 and sleep null (which I think defaults to 60 seconds). Users are complaining about the system being slow. When I checked it out, they are just getting impatient waiting for concurrent requests to get picked up by the managers - sometimes it takes as long as 2 minutes. Thanks, Jay Hostetter Oracle DBA D. E. Communications Ephrata, PA USA **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jay Hostetter INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: 11i Conc. Manager Question
Tim, We already have other managers set up. I was just curious if other folks tweak the standard manager. Thanks, Jay [EMAIL PROTECTED] 08/13/02 10:28AM Jay, It's a little more complicated than that. It sounds like you are using the single default ConcMgr queue (named standard, if I recall correctly?) and you really should designate different jobs to go to different queues, each of which is configured (in terms of managers and sleep times) according to business requirements... Barb Matthews wrote an excellent paper entitled Herding Cats back in 1996 or so. Definitely pre-R11i, but certainly as relevant today as for R9.4 or whatever she was running at the time. The paper can be found online at her website at http://www.OnCallDBA.com and it should provide some good guidance on setting up queues for your own particular environment and requirements, even if the ConcMgr terminology has maybe changed for R11i... Hope this helps... -Tim - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, August 13, 2002 6:53 AM For those of you running Oracle Applications 11i, have you modified the cache or sleep time for the standard managers? Ours remains at the default, which is cache 1 and sleep null (which I think defaults to 60 seconds). Users are complaining about the system being slow. When I checked it out, they are just getting impatient waiting for concurrent requests to get picked up by the managers - sometimes it takes as long as 2 minutes. Thanks, Jay Hostetter Oracle DBA D. E. Communications Ephrata, PA USA **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jay Hostetter INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: 20 Instances 1 Machine
Good point Dennis - version issues. We create a separate Oracle home for each 3rd party application that we install at our company for this very reason. Jay [EMAIL PROTECTED] 08/02/02 01:05PM Ethan - Now that you have clarified the reason why you want 20 instances, I'll change my advice and say that in your situation, 20 instances may be the better choice. Two other factors you need to consider: 1. An ASP is a lot like the old time-sharing systems. You must have an absolutely bulletproof billing system. This may be easier to accomplish if you have each customer in a separate O.S. username. You might be able to use the O.S. usage reports for billing with separate userids. And you must have good security to keep them from becoming root and giving themselves a discount. 2. Eventually you will need to upgrade Oracle versions. Not all customers will be able to upgrade at the same time due to other constraints on their part. Separate instances will allow you to have different customers on different Oracle versions. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jay Hostetter INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Guys: Is this worth $1,749 for 8 year Oracle veteran DBA
If I may add one more... * upgrades (database, OS, monitoring tool) are virtually painless. Except for a few path changes and/or a few query changes for v$ or DBA_ tables. Good luck upgrading OEM in place on the same machine AND maintinaining all your scheduled jobs without interruption. Which reminds me of another one: * cost Jay [EMAIL PROTECTED] 08/02/02 12:59PM On Thursday 01 August 2002 09:45, Fink, Dan wrote: Case in point, many databases with few dbas. Logging in each morning to each database and checking status and metrics is very inefficient. By the time all the databases are checked, the day is over. This leaves no time to diagnose and repair problems. Scheduled jobs (cron/AT/dbms_job) that query the database/logs and send email is more effecient. At this point you have a tool. A monitoring tool, properly architected and configured, can assist greatly by allowing the dbas to focus on preventing problems and not wasting time determining that there is nothing to worry about. Dan, Advantages of rolling your own: * they do what you want, and only what you want * if your needs change, you can modify them * you don't have to deal with tech support to fix your tools * you have to learn how stuff works to monitor it Well, that last bullet point seems to be temporary with me, I seem to forget stuff after not being close to it for awhile. That also serves to point out what a disconnect GUI tools are. shameless_self_promotion You want some tools? Reserve your copy of Perl for Oracle DBA's. :) /shameless_self_promotion Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jay Hostetter INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Cognos
We had a similiar experience here. I would say that only 10% of the users are really using PowerPlay to its full potential. It requires: 1) a person with analytical skills 2) a person with a very good understanding of the data 3) a person that has time to play with the data. Most of the other users bring up canned PowerPlay reports, and tweak a dimension or two. Very few of them build their own reports and drill deep down into the data. I still think it is a great tool. I setup an cube based on the concurrent requests in our Oracle Financial system. I can find bottlenecks in the concurrent managers, see who submits the most intensive reports, etc. Jay Hostetter Oracle DBA D. E. Communications Ephrata, PA USA [EMAIL PROTECTED] 07/18/02 05:11PM We have deployed cognos, first a local install on all P.C.'s and we are now migrating to the Web based solution. The original selling point was that everybody would be able to quickly and easily create their own reports, with the users accessing our production databases. How wrong that was. What we have now is a central database (we call it a datawarehouse) that houses snapshots and custom reporting tables. All reports are basically canned, and we have a staff member who function is to maintain/build reports and the warehouse. At some point I am sure that users will be able to create their own reports, but from our datawarehouse, not production. -Original Message- Sent: July 18, 2002 9:08 AM To: Multiple recipients of list ORACLE-L Anybody have any experience with Cognos? We've got a bhb that thinks its the solution for giving every end user access to the raw data (groan...loudly!)... I've argued every which-a-way against the concept, now I have to fight the specifics HELP! John P Weatherman Database Administrator Replacements Ltd. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Weatherman INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Browett, Darren INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jay Hostetter INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Cognos
Their OLAP tool is PowerPlay. Their report writer is Impromptu. Which product are they pushing on you? I think PowerPlay is a great tool and easy to use. When we first bought it (3 or 4 years ago), I had a cube built within a day. It was much cheaper and easier to use the Oracle Express. Building a cube can hit your system pretty hard (all of the data needs to be read to summarize it into a cube). You build your cubes off hours and you don't let users build them on demand. Jay Hostetter Oracle DBA D. E. Communications Ephrata, PA USA [EMAIL PROTECTED] 07/18/02 12:08PM Anybody have any experience with Cognos? We've got a bhb that thinks its the solution for giving every end user access to the raw data (groan...loudly!)... I've argued every which-a-way against the concept, now I have to fight the specifics HELP! John P Weatherman Database Administrator Replacements Ltd. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Weatherman INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jay Hostetter INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Oracle Licensing to be Transparent?
Kimberly Floss, database administrator team leader at Quaker Oats Co., in Chicago, said she hopes the guide helps resolve Oracle's issues. Floss, who manages Oracle databases, has not had licensing problems herself ... She obviously has never been involved in any Oracle licensing discussions. Ever try to get more than 1 Oracle employee to give you the same answer on this topic? Especially when it comes to clusters and web servers. Sometimes, even the simple definition of a user elicits hours of debate. These web pages will certainly provide fodder for this list! [EMAIL PROTECTED] 07/12/02 10:43AM Evidently Oracle is going to build some web pages to spell out licensing issues and definitions. Wonder how often that web site will need to be updated. And of course everything will be crystal clear when it's done. ;-) Check it out... http://www.eweek.com/article2/0,3959,361471,00.asp Not sure if you need to subscribe to eWeek to see the above. Steve Orr -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jay Hostetter INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: trial version of Oracle Financial ??
Leslie, Even if you do get a trial version, there is a slim chance that you could get it working without the help of consultants and Oracle support. I often ask folks at our company if anybody ever considered Quickbooks. These tier 1 financial systems are applications from hell! Jay [EMAIL PROTECTED] 06/03/02 05:53PM Ron, That's fine for everything BUT E-Business Applications (and isn't that what most people want?) from the web page - * We do not offer trial licenses for our E-Business Suite Applications. Leslie, Be prepared to spend at least $40 and LOTS of learning time to install to try. I don't think trial versions come with support - and I'm not sure it can be done without patches and many (sometimes hundreds, depending on how many and which modules) iTars. The Vision database is the seeded database with somewhat useful data. Margaret -Original Message- From: Ron Rogers [mailto:[EMAIL PROTECTED]] Sent: Monday, June 03, 2002 5:29 PM To: Multiple recipients of list ORACLE-L Subject: Re: trial version of Oracle Financial ?? Leslie, Go to the Oracle web site and click on the store icon and check out the CDPACKS for $40 each. You can get anything for your own play evaluation purposes. Ron ROR mô¿ôm [EMAIL PROTECTED] 06/03/02 04:58PM Hi, Does anyone know where to get a trial version of Oracle Financial, or other components of 11i? Thank you! Leslie -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Murray, Margaret INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jay Hostetter INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: RMAN - Disk vs Tape backups
Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jay Hostetter INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: _tru64_directio_disabled param Value on Digital Tru64 Unix
Yes, I changed it from the default. Jay Hostetter Oracle DBA D. E. Communications Ephrata, PA USA Hemant K Chitale [EMAIL PROTECTED] 05/30/02 09:32AM So you got a performance improvement by *disabling* DirectIO (ie, by setting _tru64_directio_disabled=TRUE) ? Hemant K Chitale - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, 30 May, 2002 3:48 AM I joined in on this thread a little bit late. I just did a little experimentation with this parameter, and all that I can say is WOW! This is the equivalent of the mythical _make_sql_run_faster! My quick tests on 8.1.7.2 on both Tru64 5.1 pk3 and 5.1a pk1: Query1 : Avg. of 1.13 sec improved to .11 sec. Query2 : Avg. of 11.78 sec improved to 2.12 sec. Query3 : Avg. of .75 sec improved to .08 sec. This included multiple runs of each query, with a database bounce in between of course. So, what is the catch? [EMAIL PROTECTED] 05/29/02 02:26PM Quote from a person who has had researched this thoroughly at our site - Oracle 8.1.6 and later releases check to see if they are running on Tru64 5.0a or later operating system revision.If so, the RDBMS automatically uses the directio mode to open the database files. Directio bypasses the operating system (ADVFS file system) caching and is more efficient; however, ADVFS does not cache any data or pre-fetch read data.For single block random reads directio is a performance improvement--there is less O/S overhead and Oracle does a good job of managing the buffer cache. However, Oracle does not hold multiple block reads in its cache, so if your workload involves a large number of multi-block reads directio is a performance detriment.The blocks are not cached, so re-reads require physical I/O for each read, and Oracle does not pre-fetch data as ADVFS does, so the application incurs more I/O wait. Also, any subsequent access after a file is opened in directio mode inherits the directio mode. This may impact other applications reading the files outside of the database activity--for example backup. In our experience using the Oracle Applications (ERP) suite, overall performance was better with directio disabled. By default directio is enabled if running 8.1.6 or later and Tru64 5.0a or later.The flag to disable was introduced in 8.1.7.2, I believe. We were told not to run 8.1.7.2 on Tru64 (buggy), so we have implemented 8.1.7.3. The default operating mode is: _tru64_directio_disabled = FALSE This enables directio. If you set it TRUE, then the RDBMS I/O will function as it did before--using normal I/O. There is not a lot of risk in changing this option, and directio may prove to be advantageous for a heavy OLTP environment. I would recommend testing outside of Production if at all possible. Bill HTH Srini Chavali Oracle DBA Cummins Inc -- 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). **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- 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). **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you
RE: _tru64_directio_disabled param Value on Digital Tru64 Unix
I joined in on this thread a little bit late. I just did a little experimentation with this parameter, and all that I can say is WOW! This is the equivalent of the mythical _make_sql_run_faster! My quick tests on 8.1.7.2 on both Tru64 5.1 pk3 and 5.1a pk1: Query1 : Avg. of 1.13 sec improved to .11 sec. Query2 : Avg. of 11.78 sec improved to 2.12 sec. Query3 : Avg. of .75 sec improved to .08 sec. This included multiple runs of each query, with a database bounce in between of course. So, what is the catch? [EMAIL PROTECTED] 05/29/02 02:26PM Quote from a person who has had researched this thoroughly at our site - Oracle 8.1.6 and later releases check to see if they are running on Tru64 5.0a or later operating system revision.If so, the RDBMS automatically uses the directio mode to open the database files. Directio bypasses the operating system (ADVFS file system) caching and is more efficient; however, ADVFS does not cache any data or pre-fetch read data.For single block random reads directio is a performance improvement--there is less O/S overhead and Oracle does a good job of managing the buffer cache. However, Oracle does not hold multiple block reads in its cache, so if your workload involves a large number of multi-block reads directio is a performance detriment.The blocks are not cached, so re-reads require physical I/O for each read, and Oracle does not pre-fetch data as ADVFS does, so the application incurs more I/O wait. Also, any subsequent access after a file is opened in directio mode inherits the directio mode. This may impact other applications reading the files outside of the database activity--for example backup. In our experience using the Oracle Applications (ERP) suite, overall performance was better with directio disabled. By default directio is enabled if running 8.1.6 or later and Tru64 5.0a or later.The flag to disable was introduced in 8.1.7.2, I believe. We were told not to run 8.1.7.2 on Tru64 (buggy), so we have implemented 8.1.7.3. The default operating mode is: _tru64_directio_disabled = FALSE This enables directio. If you set it TRUE, then the RDBMS I/O will function as it did before--using normal I/O. There is not a lot of risk in changing this option, and directio may prove to be advantageous for a heavy OLTP environment. I would recommend testing outside of Production if at all possible. Bill HTH Srini Chavali Oracle DBA Cummins Inc -- 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). **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jay Hostetter INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: so when did you switch from NT to unix for oracle
Can you afford non-scheduled reboots? If no, don't even think of NT/2000. **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- 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).
Duplicate from Previous Incarnation
Has anybody duplicated a database from a previous incarnation? Oracle tells me that I should just be able to issue a RESET DATABASE TO inc#. I am a little worried about doing this when connected to my production database and catalog (as required for duplicating). If would like to hear stories from anybody who has done this. Thanks, Jay **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- 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).
Help with Locking Issue
I have been spending most of my morning trying to resolve a locking issue. I think I could me missing the forest for the trees. This is what happens: a user kicks off two identical jobs from two different PCs. Each of these jobs is doing the same thing, but against different rows of data (they are processing work orders in our system, but each job is processing a different work order). One session will wait until the other session completes. I am trying to figure out what they are waiting on. At first I assumed a locked record, but I don't think that is the case. I did quite a bit of research on MetaLink. I even rebuilt the table in case INITRANS and PCTFREE might be too small, but that didn't seem to help either. Here is the output from the query in note 1020047.6. Sess Op Sys OBJ NAME or ID USERNAME User IDTERMINAL TRANS_ID TY Lock Mode Req Mode -- - -- --- --- 12 KEN468 ken468 KEN468-1 FIXED_ASSET_ACTIV TM Row Excl 12 KEN468 ken468 KEN468-1 Trans-196694 TX Exclusive 14 KEN468 Batch BATCHFIXED_ASSET_ACTIV TM Row Excl 14 KEN468 Batch BATCHTrans-196694 TX --Waiting-- Share 14 KEN468 Batch BATCHTrans-65597 TX Exclusive So session 14 is waiting for a share lock. Session 12 has an exclusive lock that is blocking session 14. How do I find out what session 12 has locked that is needed by session 14? Thanks, Jay -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jay Hostetter INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Help with Locking Issue
I had already checked that, but since you asked, I double checked. The primary key on the FIXED_ASSET_ACTIV table has two columns - both are foreign keys from other tables. I created a separate index for the 2nd column in the PK. This fixed my problem! So, the foreign key was indexed, it just wasn't indexed correctly. Thanks, Jay [EMAIL PROTECTED] 04/18/02 01:17PM Jay, do you have any unindexed foreign keys on those tables? If so, Oracle will take out a lock on any transaction involving the parent or child, IIRC. HTH, Scott Shafer San Antonio, TX 210-581-6217 -Original Message- From: Jay Hostetter [SMTP:[EMAIL PROTECTED]] Sent: Thursday, April 18, 2002 11:58 AM To: Multiple recipients of list ORACLE-L Subject: Help with Locking Issue I have been spending most of my morning trying to resolve a locking issue. I think I could me missing the forest for the trees. This is what happens: a user kicks off two identical jobs from two different PCs. Each of these jobs is doing the same thing, but against different rows of data (they are processing work orders in our system, but each job is processing a different work order). One session will wait until the other session completes. I am trying to figure out what they are waiting on. At first I assumed a locked record, but I don't think that is the case. I did quite a bit of research on MetaLink. I even rebuilt the table in case INITRANS and PCTFREE might be too small, but that didn't seem to help either. Here is the output from the query in note 1020047.6. Sess Op Sys OBJ NAME or ID USERNAME User IDTERMINAL TRANS_ID TY Lock Mode Req Mode -- - -- --- --- 12 KEN468 ken468 KEN468-1 FIXED_ASSET_ACTIV TM Row Excl 12 KEN468 ken468 KEN468-1 Trans-196694 TX Exclusive 14 KEN468 Batch BATCHFIXED_ASSET_ACTIV TM Row Excl 14 KEN468 Batch BATCHTrans-196694 TX --Waiting-- Share 14 KEN468 Batch BATCHTrans-65597 TX Exclusive So session 14 is waiting for a share lock. Session 12 has an exclusive lock that is blocking session 14. How do I find out what session 12 has locked that is needed by session 14? Thanks, Jay -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jay Hostetter INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jay Hostetter INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Archival Freeze - Painful lessons learned
). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jay Hostetter INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Recompiling Invalid Objects after Table Rename
$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).
