RE: Any known problems using NOT IN ?
Naveen, The column does have nulls and this was the problem. Thanks for the insight. Thanks Siddharth Haldankar Zensar Technologies Ltd. Cisco Systems Inc. (Offshore Development Center) # : 091 020 4128394 [EMAIL PROTECTED] [EMAIL PROTECTED] -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Naveen, Nahata (IE10) Sent: Thursday, December 18, 2003 11:59 AM To: Multiple recipients of list ORACLE-L Subject: RE: Any known problems using NOT IN ? Doess the column crs.os_id_fk1 contain nulls? Regards Naveen -Original Message- From: Siddharth Haldankar [mailto:[EMAIL PROTECTED] Sent: Thursday, December 18, 2003 10:54 AM To: Multiple recipients of list ORACLE-L Subject: Any known problems using NOT IN ? Hi Gurus, I have a problem using NOT IN clause in Oracle. However using NOT EXISTS, gives me the right output. Are there any known limitations. This query selects from the master records wherein child records are not active. select * from ct_software_release csr where csr.class = 'NS' AND csr.active_flag = 'Y' AND csr.os_id_pk not IN (SELECT crs.os_id_fk1 FROM CT_ROADMAP_SOFTWARE crs WHERE crs.active_flag = 'Y'); The sub-query in the above case gives 1800 rows. The above query fails to give any rows. select * from ct_software_release csr where csr.class = 'NS' AND csr.active_flag = 'Y' AND NOT EXISTS (SELECT 1 FROM CT_ROADMAP_SOFTWARE crs WHERE crs.os_id_fk1 = csr.os_id_pk AND crs.active_flag = 'Y'); This above query works fine. Thanks Siddharth Haldankar Zensar Technologies Ltd. Cisco Systems Inc. (Offshore Development Center) # : 091 020 4128394 [EMAIL PROTECTED] [EMAIL PROTECTED]
RE: Career Advice
What about books with basic development perspective? Not sure what you mean by that. Do you mean development with Perl? Or just development in general? If development in general, you can't go wrong with 'Code Complete' by Steve McConnell, Microsoft Press. This is the best programming book I've ever read, period. As for Perl, not too much in the way of general programming principals. Best to consider other texts first for that. Not reading 'Code Complete' is a mistake. Jared On Wed, 2003-12-17 at 19:54, Viktor wrote: Dennis, Yes, my thinking is exactly the same. Before I get too much involved with Java (it will be better to take training for Java), I am going to sharpen up my skills with Perl. Should be easier to pick it up, and the learning curve won't be as steep. Every company reorg probably has more minuses that pluses. In our case the new wants the DBA's to participate in the development processes more like developers, which makes us more or less development DBA's. Writing code will be part of our job. Production DBA will do most of the true admin. tasks (unless he is out, then one of the dev. DBA's will pick that up). But other than that, it looks like I am going to be more in the dev. world. Now I'll have to kick my lazy butt to learn new stuff! Jared as always thanks! I've already thought about your book and it's now on order! What about books with basic development perspective? Thanks! Viktor DENNIS WILLIAMS [EMAIL PROTECTED] wrote: Viktor My suggestion is to go for Perl as the quick win and impress your new boss. Perl also tends to be more useful for DBAs. Learning Java can be a more long-term proposition. First, you need to have a strong understanding of object-oriented design. We have trained developers in Java, and it hasn't been a quick learn for them. Based on what I've seen, I would push for Java training. Also, before you tear into Java, you may want to get a basic understanding of how the web stuff like HTML works. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, December 17, 2003 6:24 PM To: Multiple recipients of list ORACLE-L Let's take this topic into a more concrete scenario. New boss, company reorg, cross-training is enforced and now DBA'S's are going to be split into development groups. Need to learn Perl(looking forward to it actually!!!) and Java. Books, web sites, docs - all these material is great. But what if you're expected to learn fast and I can learn quickly, but still, do you guys have some advice on how can one express teach himself. Managing expectation is one thing I need to talk with boss about. Surely I would not't want to be overwhelmed with stuff at the beginning. But at the same time I am kinda excited about picking up on Java and Perl. The questions is what are the tricks and tips for learning on a fast track? Thanks! Viktor Stephane Faroult wrote: Believe somebody who first learned SQL back in 83, it's too late for Java now. Run-of-the-mill skill. Any young grad will know it and will be less expensive. ERP would be a good bet, because people learn them at work, mostly. Now, would a company change be justified just for that? Probably not. As you said, you are hired for what you know, not what you want to learn. Grasp opportunities, learn whatever looks to you useful - and fun. My 0.02 EUR. SF [EMAIL PROTECTED] wrote: learn java and object oriented programming. go to sun.com and start reading the java docs. go to www.bruceeckel.com and read his java book. do a search on any job sites. a ton more work for java than oracle. people who can do both are in demand. From: Mladen Gogala Date: 2003/12/17 Wed PM 01:49:25 EST To: Multiple recipients of list ORACLE-L Subject: Re: Career Advice Have you ever considered a career in country music? Try getting Stand By your man just right and the rest will come. You have to learn both kinds of music, country and western. May Jake and Elwood be with you. On 12/17/2003 12:44:28 PM, Saira Somani-Mendelin wrote: As an applications analyst/junior dba, I feel I need to learn more but I'm not sure of the direction I should take, so I'm asking for advice. Should I become interested in Oracle Apps? Or should I learn another suite like SAP or Siebel or PeopleSoft? The difficulty is that my company does not use any of these. We use a smaller package by Tecsys called Eli! te and they don't have as many customers - or should I say, as many customers with deep pockets. I know I can get my hands on a working copy of SAP, what about the others? I believe you can purchase an evaluation copy of Apps from the Oracle Store. Has anyone actually tried to train themselves on any of these products? Has anyone installed Apps at home for testing? Sorry if this
RE: db block gets /consistent gets
Syed, Oracle accesses blocks in one of two modes, current or consistent. A 'db block get' is a current mode get. That is, it's the most up-to-date copy of the data in that block, as it is right now, or currently. There can only be one current copy of a block in the buffer cache at any time. Db block gets generally are used when DML changes data in the database. In that case, row-level locks are implicitly taken on the updated rows. There is also at least one well-known case where a select statement does a db block get, and does not take a lock. That is, when it does a full table scan or fast full index scan, Oracle will read the segment header in current mode (multiple times, the number varies based on Oracle version). A 'consistent get' is when Oracle gets the data in a block which is consistent with a given point in time, or SCN. The consistent get is at the heart of Oracle's read consistency mechanism. When blocks are fetched in order to satisfy a query result set, they are fetched in consistent mode. If no block in the buffer cache is consistent to the correct point in time, Oracle will (attempt to) reconstruct that block using the information in the rollback segments. If it fails to do so, that's when a query errors out with the much dreaded, much feared, and much misunderstood ORA-1555 snapshot too old. As to latching, and how it relates, well, consider that the block buffers are in the SGA, which is shared memory. To avoid corruption, latches are used to serialize access to many linked lists and data structures that point to the buffers as well as the buffers themselves. It is safe to say that each consistent get introduces serialization to the system, and by tuning SQL to use more efficient access paths, you can get the same answer to the same query but do less consistent gets. This not only consumes less CPU, it also can significantly reduce latching which reduces serialization and makes your system more scalable. Well, that turned out longer than I planned. If you're still reading, I hope it helped! -Mark -Original Message- From: Sultan Syed [mailto:[EMAIL PROTECTED] Sent: Thu 12/18/2003 1:39 AM To: Multiple recipients of list ORACLE-L Cc: Subject:db block gets /consistent gets Hi list, What is db block gets and consistent gets.? How can I reduce consistent gets ? Ask Tom says each consistent gets is latch, how it could be? Thanks in advance Syed -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Bobak, Mark 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).
SQL tuning...
Can anyone tell me whats wrong with the explain plan below this update is running for quite long time. Even without a single full-table access Thank you in advance! UPDATE STATEMENT Optimizer=CHOOSE (Cost=83 Card=4893 Bytes=327831) UPDATE OF CCM_DEBIT_TBL SEQUENCE OF STAFFWARE_CASEID_S FILTER TABLE ACCESS (BY GLOBAL INDEX ROWID) OF CCM_DEBIT_TBL (Cost=83 Card=4893 Bytes=327831) INDEX (RANGE SCAN) OF IDX_DEBIT_DUE_DATE (NON-UNIQUE) (Cost=26 Card=4893) INDEX (RANGE SCAN) OF IDX_PLAN_DEBIT_CISDEBITDEAD (NON-UNIQUE) (Cost=3 Card=1 Bytes=26) INDEX (RANGE SCAN) OF IDX_PLAN_DEBIT_CISDEBITDEAD (NON-UNIQUE) (Cost=3 Card=1 Bytes=28) TABLE ACCESS (BY GLOBAL INDEX ROWID) OF CCM_ACCOUNT_TBL (Cost=1 Card=1 Bytes=9) INDEX (UNIQUE SCAN) OF PK_ACCOUNT (UNIQUE) (Cost=2 Card=1) Confidentiality Notice The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain confidential or privileged information. If you are not the intended recipient, please notify the sender at Wipro or [EMAIL PROTECTED] immediately and destroy all copies of this message and any attachments.
Re: db block gets /consistent gets
Mark I know you from the metalink. Thank you for your detailed explanation. Syed - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, December 18, 2003 11:24 AM Syed, Oracle accesses blocks in one of two modes, current or consistent. A 'db block get' is a current mode get. That is, it's the most up-to-date copy of the data in that block, as it is right now, or currently. There can only be one current copy of a block in the buffer cache at any time. Db block gets generally are used when DML changes data in the database. In that case, row-level locks are implicitly taken on the updated rows. There is also at least one well-known case where a select statement does a db block get, and does not take a lock. That is, when it does a full table scan or fast full index scan, Oracle will read the segment header in current mode (multiple times, the number varies based on Oracle version). A 'consistent get' is when Oracle gets the data in a block which is consistent with a given point in time, or SCN. The consistent get is at the heart of Oracle's read consistency mechanism. When blocks are fetched in order to satisfy a query result set, they are fetched in consistent mode. If no block in the buffer cache is consistent to the correct point in time, Oracle will (attempt to) reconstruct that block using the information in the rollback segments. If it fails to do so, that's when a query errors out with the much dreaded, much feared, and much misunderstood ORA-1555 snapshot too old. As to latching, and how it relates, well, consider that the block buffers are in the SGA, which is shared memory. To avoid corruption, latches are used to serialize access to many linked lists and data structures that point to the buffers as well as the buffers themselves. It is safe to say that each consistent get introduces serialization to the system, and by tuning SQL to use more efficient access paths, you can get the same answer to the same query but do less consistent gets. This not only consumes less CPU, it also can significantly reduce latching which reduces serialization and makes your system more scalable. Well, that turned out longer than I planned. If you're still reading, I hope it helped! -Mark -Original Message- From: Sultan Syed [mailto:[EMAIL PROTECTED] Sent: Thu 12/18/2003 1:39 AM To: Multiple recipients of list ORACLE-L Cc: Subject: db block gets /consistent gets Hi list, What is db block gets and consistent gets.? How can I reduce consistent gets ? Ask Tom says each consistent gets is latch, how it could be? Thanks in advance Syed -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Bobak, Mark 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: Sultan Syed 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).
InstallActions.log
Hi All What is the location for InstallActions.log ? Thanks and Regards B S Pradhan
Re: SQL tuning...
Venu, Do your 10046 tracing and run it through tkprof of the Hotsos Profiler and you'll know exactly why your query is taking so long. Regards, Gudmundur Can anyone tell me whats wrong with the explain plan below#8230; this update is running for quite long time#8230;. Even without a single full-table access#8230; nbsp; Thank you in advance! nbsp; nbsp; UPDATE STATEMENT Optimizer=CHOOSE (Cost=83 Card=4893 Bytes=327831) UPDATE OF CCM_DEBIT_TBL SEQUENCE OF STAFFWARE_CASEID_S FILTER TABLE ACCESS (BY GLOBAL INDEX ROWID) OF CCM_DEBIT_TBL (Cost=83 Card=4893 Bytes=327831) INDEX (RANGE SCAN) OF IDX_DEBIT_DUE_DATE (NON-UNIQUE) (Cost=26 Card=4893) INDEX (RANGE SCAN) OF IDX_PLAN_DEBIT_CISDEBITDEAD (NON-UNIQUE) (Cost=3 Card=1 Bytes=26) INDEX (RANGE SCAN) OF IDX_PLAN_DEBIT_CISDEBITDEAD (NON-UNIQUE) (Cost=3 Card=1 Bytes=28) TABLE ACCESS (BY GLOBAL INDEX ROWID) OF CCM_ACCOUNT_TBL (Cost=1 Card=1 Bytes=9) INDEX (UNIQUE SCAN) OF PK_ACCOUNT (UNIQUE) (Cost=2 Card=1) Confidentiality Notice The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain confidential or privileged information. If you are not the intended recipient, please notify the sender at Wipro or [EMAIL PROTECTED] immediately and destroy all copies of this message and any attachments. Þessi póstur var sendur með vefpósti mi, http://www.mi.is -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gudmundur Josepsson 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 Retention Policy
Ian,
I think retention policy is new in 9i.
I purge my repository of backups that are older than 90 days (because our
tape systems rotates and reuses tapes after that time) using the change
backuppiece 330783 delete; command. I run a sql script againts the rman
repository looking for pieces that satisfy this requirement. The sql looks
like this:
select 'change backuppiece bp.bp_key delete;'
from rc_backup_piece bp,rc_database db
where db.name = upper('ORACLE_SID')
and bp.db_id = db.dbid
and bp.start_time sysdate-90
/
This is in an 8i database.
Hope this is what you were looking for.
Tom Mercadante
Oracle Certified Professional
-Original Message-
Sent: Wednesday, December 17, 2003 5:04 PM
To: Multiple recipients of list ORACLE-L
How is this set on 8.1.7 and 8.1.6 databases
RMAN CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 8 DAYS;
RMAN-00571: ===
RMAN-00569: === ERROR MESSAGE STACK FOLLOWS ===
RMAN-00571: ===
RMAN-00558: error encountered while parsing input commands
RMAN-01005: syntax error: found identifier: expecting one of: compatible
RMAN-01008: the bad identifier was: RETENTION
RMAN-01007: at line 1 column 11 file: standard input
I looked at commands such as
crosscheck backup of database completed before 'SYSDATE-7';
delete expired backup of database completed before 'SYSDATE-7';
But crosscheck only expires backups which are in the catalog, but not
available on the backup media.
Do I have to use the change command and designate each backup piece?
Ian MacGregor
Stanford Linear Accelerator Center
[EMAIL PROTECTED]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: MacGregor, Ian A.
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Mercadante, Thomas F
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
OEM permissions
We have a new manager that wants his group to use OEM for development access, as an alternative to Toad. He has requested a special Oracle userid with the following grants: SELECT_CATALOG_ROLE SELECT ANY DICTIONARY SELECT ANY TABLE Does this seem reasonable for OEM? The manager is responsible for the data in the database, so I don't see a problem with him viewing the data. There are few database links, and I'll be reviewing them. Any ideas on what mischief could occur? Thanks. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS 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: no longer listening
Mladen, The equivalent of nsswitch.conf on Win2K is the hosts file in winnt\system32\drivers\etc. You'd probably say: the /etc directory in M$ Windoze? Somewhat of a resemblance to the UNIX naming convention. You'd be surprise to find a services file just like the one found in /etc directory of the UNIX and UNIX-like OS'sBut, well this is way off the topic we are dealing with. Like Jared said, we need to focus ;) My name resolution is done by a DC, actually there are several of them. I should probably check to make sure everything is consistent across all DCs as far as computer name and address for my Oracle server host. I doubt there will be differences since they replicate the same domain info. I also checked the hosts file and it only has the localhost line in it. I'll try including a line for my server as it appears in my domain controllers, maybe that'll help. Julio Cesar Quijada-Reina Programmer Analyst Computer Services at Alfred State College -Original Message- Mladen Gogala Sent: Wednesday, December 17, 2003 4:45 PM To: Multiple recipients of list ORACLE-L So, how are your host names resolved? Any changes in DNS or WINS name servers? Did anybody reboot or alter PDC? On NT host names are resolved by asking PDC. If it cannot respond because of reboot, listener may shut down as well. You should try modifying yur local equivalent of nsswitch.conf. whatever the name is. On 12/17/2003 04:24:34 PM, QuijadaReina, Julio C wrote: Mladen, I did look at the listener.log file and did not see any changes as far as network configuration. The host where the database is running on does not have a fixed IP address though. It gets a lease from a DHCP. I checked the logs on the DHCP and the IP for my database host has not changed lately. Also, the HOST parameter in listener.ora has assigned to it the host's name and not its IP address. Julio Cesar Quijada-Reina Programmer Analyst Computer Services at Alfred State College -Original Message- Mladen Gogala Sent: Wednesday, December 17, 2003 3:19 PM To: Multiple recipients of list ORACLE-L Somebody might have changed network configuration. Did you look into the listener.log ? On 12/17/2003 02:19:43 PM, QuijadaReina, Julio C wrote: Hi all, For the last couple of weeks, I have experienced listener down times. The listener will work fine for say a week, and then all the sudden it will no longer allow connections from remote machines. Clients use SQL*plus to connect. Interestingly enough, on the actual server I am able to login just fine. The following are the log lines as they appear in my listener.log. - No longer listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC0ipc))) No longer listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=machinename.domain)(PORT=1521) )) No longer listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST= machinename.domain)(PORT=8080))(Presentation=HTTP)(Session=RAW)) No longer listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST= machinename.domain)(PORT=2100))(Presentation=FTP)(Session=RAW)) - HTTP and FTP are not important, since we do not use any Oracle's HTTP or FTP. But the second line on the log is rather important since it is the listener's port (1521). The server is a Win2K with 128MB of RAM and a small test database for about 60 users of which 20 or so might connect simultaneously. Any ideas as to why this might be happening? Regards, Julio Cesar Quijada-Reina Programmer Analyst Computer Services at Alfred State College -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala 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: QuijadaReina, Julio C 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
RE: no longer listening
Jared, I don't yet have a service to monitor the service that moniters the service monitor. Is this a tongue twister of some sort? ;-) I totally understand where you are coming from. Windoze seems to always have difficulty bringing and keeping services up. I presented this as a reason (among many others) why we should not have had our server on a Windoze box, but the faculty member you teaches one of the Database Concepts course said wanted Window. I myself wouldve gone for Sun Solaris, but heyas in most of the universities and junior colleges in the country. Faculty has the last say on this type of matters (sadly) no matter what Tech Support Staff or better yet real world experience says. Regards, Julio Cesar Quijada-Reina Programmer Analyst Computer Services at Alfred State College -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] Sent: Wednesday, December 17, 2003 7:39 PM To: Multiple recipients of list ORACLE-L Subject: Re: no longer listening I've had similar problems, however, it isn't just limited to Oracle listeners. NT and Win2k are both problematic when it comes to reliably starting services at bootup. They both occasionally have a problem with a service dying for no apparent reason. I don't know why this is. To deal with it I wrote a script that runs as another service on a separate server, checks each day at 05:30 if particular services are running on a number of servers, restarts them if possible and pages me. There's another service, on yet another computer, that checks to make sure the service monitor service is running. I don't yet have a service to monitor the service that moniters the service monitor. Not an ideal solution ( that would be linux where things tend to be more reliable), but it works. Jared QuijadaReina, Julio C [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 12/17/2003 11:19 AM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:no longer listening Hi all, For the last couple of weeks, I have experienced listener down times. The listener will work fine for say a week, and then all the sudden it will no longer allow connections from remote machines. Clients use SQL*plus to connect. Interestingly enough, on the actual server I am able to login just fine. The following are the log lines as they appear in my listener.log. - No longer listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC0ipc))) No longer listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=machinename.domain)(PORT=1521))) No longer listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST= machinename.domain)(PORT=8080))(Presentation=HTTP)(Session=RAW)) No longer listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST= machinename.domain)(PORT=2100))(Presentation=FTP)(Session=RAW)) - HTTP and FTP are not important, since we do not use any Oracle's HTTP or FTP. But the second line on the log is rather important since it is the listener's port (1521). The server is a Win2K with 128MB of RAM and a small test database for about 60 users of which 20 or so might connect simultaneously. Any ideas as to why this might be happening? Regards, Julio Cesar Quijada-Reina Programmer Analyst Computer Services at Alfred State College
RE: OEM permissions
Dennis, select any table has to be a big no no ... anyone can select from sys.link$. But I am still trying how OEM can be used for _development_?? what am I missing? As for One of our groups hired a new consultant and he (claimed to have DBA background) immediately shot off an email saying he needed select any table and select catalog role to do his work. We shot off reply Thanks for your email, while we appreciate your requirements for development, the privileges you are requesting are a tad different than we grant other developers. However we request that you submit a justification for these privileges and tell us how your development would be affected without these and we will accommodate your request. This was 3 months ago and we _still_ haven't heard back. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Thursday, December 18, 2003 8:24 AM To: Multiple recipients of list ORACLE-L We have a new manager that wants his group to use OEM for development access, as an alternative to Toad. He has requested a special Oracle userid with the following grants: SELECT_CATALOG_ROLE SELECT ANY DICTIONARY SELECT ANY TABLE Does this seem reasonable for OEM? The manager is responsible for the data in the database, so I don't see a problem with him viewing the data. There are few database links, and I'll be reviewing them. Any ideas on what mischief could occur? Thanks. ** This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. **5 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra 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).
Checking that the instance is up
Folks - Some months ago there were exchanges on the optimum way to check that an instance was alive. I have searched, hunted and generally scavenged for these exchanges, but without luck. Can anyone give me the subject line, and I can turn again to the archive? thanks, peter edinburgh * This e-mail message, and any files transmitted with it, are confidential and intended solely for the use of the addressee. If this message was not addressed to you, you have received it in error and any copying, distribution or other use of any part of it is strictly prohibited. Any views or opinions presented are solely those of the sender and do not necessarily represent those of the British Geological Survey. The security of e-mail communication cannot be guaranteed and the BGS accepts no liability for claims arising as a result of the use of this medium to transmit messages from or to the BGS. .http://www.bgs.ac.uk * -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Robson, Peter 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 Retention Policy
Thomas,
You are right it's new in 9i
Ramon E. Estevez
[EMAIL PROTECTED]
809-535-8994
-Original Message-
Sent: Thursday, December 18, 2003 9:09 AM
To: Multiple recipients of list ORACLE-L
Ian,
I think retention policy is new in 9i.
I purge my repository of backups that are older than 90 days (because our tape systems
rotates and reuses tapes after that time) using the change backuppiece 330783 delete;
command. I run a sql script againts the rman repository looking for pieces that
satisfy this requirement. The sql looks like this:
select 'change backuppiece bp.bp_key delete;'
from rc_backup_piece bp,rc_database db
where db.name = upper('ORACLE_SID')
and bp.db_id = db.dbid
and bp.start_time sysdate-90
/
This is in an 8i database.
Hope this is what you were looking for.
Tom Mercadante
Oracle Certified Professional
-Original Message-
Sent: Wednesday, December 17, 2003 5:04 PM
To: Multiple recipients of list ORACLE-L
How is this set on 8.1.7 and 8.1.6 databases
RMAN CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 8 DAYS;
RMAN-00571: ===
RMAN-00569: === ERROR MESSAGE STACK FOLLOWS ===
RMAN-00571: ===
RMAN-00558: error encountered while parsing input commands
RMAN-01005: syntax error: found identifier: expecting one of: compatible
RMAN-01008: the bad identifier was: RETENTION
RMAN-01007: at line 1 column 11 file: standard input
I looked at commands such as
crosscheck backup of database completed before 'SYSDATE-7'; delete expired backup of
database completed before 'SYSDATE-7';
But crosscheck only expires backups which are in the catalog, but not available on the
backup media.
Do I have to use the change command and designate each backup piece?
Ian MacGregor
Stanford Linear Accelerator Center
[EMAIL PROTECTED]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: MacGregor, Ian A.
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY,
include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be
removed from). You may also send the HELP command for other information (like
subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Mercadante, Thomas F
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY,
include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be
removed from). You may also send the HELP command for other information (like
subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: =?iso-8859-1?Q?Ram=F3n_Estevez?=
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).
DB link to standby
Title: DB link to standby On standby database server, you can connect to / as sysdba and query fixed tables/views. Is it possible to create a database link which connects to sysdba user to query the fixed tables/view? I tried, but getting following error ORA-02068: following severe error from TEST_SB ORA-01033: ORACLE initialization or shutdown in progress Thanks Ashish
Re: Any known problems using NOT IN ?
Actually, because relational database conform to the rules of set theory, I find it preferable to use MINUS wherever possible. Oracle optimizer is trained to spot set operations and they usually generate sort/merge or hash based execution plan, while NOT IN and NOT EXIST can generate NL plan, which is, generally speaking, undesired when you do set operations. On 12/18/2003 12:39:26 AM, Charu Joshi wrote: Siddharth, The NOT IN query fails to return rows, if the inner sub-query returns NULL values. It is always recommended to use the NOT EXISTS clause, unless you are sure that the inner query will not return any NULLs. Regards, Charu. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Siddharth Haldankar Sent: 18 December 2003 10:54 To: Multiple recipients of list ORACLE-L Subject: Any known problems using NOT IN ? Hi Gurus, I have a problem using NOT IN clause in Oracle. However using NOT EXISTS, gives me the right output. Are there any known limitations. This query selects from the master records wherein child records are not active. select * from ct_software_release csr where csr.class = 'NS' ANDcsr.active_flag = 'Y' ANDcsr.os_id_pk not IN (SELECT crs.os_id_fk1 FROM CT_ROADMAP_SOFTWARE crs WHERE crs.active_flag = 'Y'); The sub-query in the above case gives 1800 rows. The above query fails to give any rows. select * from ct_software_release csr where csr.class = 'NS' ANDcsr.active_flag = 'Y' ANDNOT EXISTS (SELECT 1 FROM CT_ROADMAP_SOFTWARE crs WHERE crs.os_id_fk1 = csr.os_id_pk AND crs.active_flag = 'Y'); This above query works fine. Thanks -- Siddharth Haldankar Zensar Technologies Ltd. Cisco Systems Inc. (Offshore Development Center) # : 091 020 4128394 [EMAIL PROTECTED] [EMAIL PROTECTED] * Disclaimer This message (including any attachments) contains confidential information intended for a specific individual and purpose, and is protected by law. If you are not the intended recipient, you should delete this message and are hereby notified that any disclosure, copying, or distribution of this message, or the taking of any action based on it, is strictly prohibited. * Visit us at http://www.mahindrabt.com -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala 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 permissions
Wouldn't this allow viewing DBA_USERS? I haven't tried this myself, but it seems that I could set up another oracle instance, create a user identified by values, then create database link. -Original Message- We have a new manager that wants his group to use OEM for development access, as an alternative to Toad. He has requested a special Oracle userid with the following grants: SELECT_CATALOG_ROLE SELECT ANY DICTIONARY SELECT ANY TABLE Does this seem reasonable for OEM? The manager is responsible for the data in the database, so I don't see a problem with him viewing the data. There are few database links, and I'll be reviewing them. Any ideas on what mischief could occur? Thanks. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS 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: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Career Advice
The question is where do you want to go today? Actually, the question is, Where do you want to go tomorrow? Consult the crystal ball when it comes to career planning. Fixing VCR's may match one's skill set but such service won't be needed much when robots can stamp out new R/W DVD's for $10 a pop. -Original Message- Mladen Gogala Sent: Wednesday, December 17, 2003 2:04 PM To: Multiple recipients of list ORACLE-L On 12/17/2003 03:44:34 PM, Saira Somani-Mendelin wrote: Well, good, now that we have that cleared up. Don't get me wrong, I do like your keen sense of humour and sarcasm - when I know you're joking and at times its hard to tell. Dennis has a point. The question is where do you want to go today? -- Mladen Gogala Oracle DBA -- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- 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).
RE: Checking that the instance is up
Hi Peter, I remember the thread, but couldn't remember the subject line at all.. Having had this discussion countless times in the past, here's what I would opt for: The best way to make sure that an instance is alive and running would be to do a full connection to that instance, and run a simple piece of sql - such as select 'PING' from dual;. This makes sure that both the listener, and instance are both up and readily accepting connections. You can also monitor for the listener status separately, based upon a TNSPING SID command, that will alert simply to the status of the listener. We do both of these with our monitoring tool, and generate ENTITY_DOWN and NETWORK_DOWN alerts respectively. If you get both an ENTITY_DOWN and NETWORK_DOWN error message at the same time, then this helps to point you at the listener quicker than trying to diagnose why the instance is down. If you just get an ENTITY_DOWN message on it's own, then it points to a database based problem. You should also try and catch the error messages on a failure, and flag those in your warning messages. And of course, there is the point of monitoring the monitor. You should have some process in place that is making sure that the monitor is up and running correctly. Whether this be a date/timestamp placed in to a log file with every successful ping, or making sure a cron/AT job is running etc. depends on how you do this.. Of course, you could buy a tool that already does all of this.. ;) HTH Mark -Original Message- Robson, Peter Sent: 18 December 2003 13:59 To: Multiple recipients of list ORACLE-L Folks - Some months ago there were exchanges on the optimum way to check that an instance was alive. I have searched, hunted and generally scavenged for these exchanges, but without luck. Can anyone give me the subject line, and I can turn again to the archive? thanks, peter edinburgh * This e-mail message, and any files transmitted with it, are confidential and intended solely for the use of the addressee. If this message was not addressed to you, you have received it in error and any copying, distribution or other use of any part of it is strictly prohibited. Any views or opinions presented are solely those of the sender and do not necessarily represent those of the British Geological Survey. The security of e-mail communication cannot be guaranteed and the BGS accepts no liability for claims arising as a result of the use of this medium to transmit messages from or to the BGS. .http://www.bgs.ac.uk * -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Robson, Peter 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). --- Incoming mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.547 / Virus Database: 340 - Release Date: 02/12/2003 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.547 / Virus Database: 340 - Release Date: 02/12/2003 -- 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).
RE: Checking that the instance is up
We do a heartbeat at fixed intervals ... a heartbeat consists of 1. logging in 2. inserting sysdate into a table. 3. sleeping for 3 seconds. 4. performing current_sysdate-sysdate_from_table 5. rollback 6. exit with output from step 4. If the output from step 4 is excessive ( 5 or 6 seconds), DBAs, Unix Admins automatically get paged. This gives a check for instance, listener too. Some might think it is a bit over the top, but we like it. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! ** This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. **4 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra 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: Any known problems using NOT IN ?
btw, a straight not in without a hash_aj, tends to get hideous bench marks. From: Mladen Gogala [EMAIL PROTECTED] Date: 2003/12/18 Thu AM 09:14:24 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: Any known problems using NOT IN ? Actually, because relational database conform to the rules of set theory, I find it preferable to use MINUS wherever possible. Oracle optimizer is trained to spot set operations and they usually generate sort/merge or hash based execution plan, while NOT IN and NOT EXIST can generate NL plan, which is, generally speaking, undesired when you do set operations. On 12/18/2003 12:39:26 AM, Charu Joshi wrote: Siddharth, The NOT IN query fails to return rows, if the inner sub-query returns NULL values. It is always recommended to use the NOT EXISTS clause, unless you are sure that the inner query will not return any NULLs. Regards, Charu. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Siddharth Haldankar Sent: 18 December 2003 10:54 To: Multiple recipients of list ORACLE-L Subject: Any known problems using NOT IN ? Hi Gurus, I have a problem using NOT IN clause in Oracle. However using NOT EXISTS, gives me the right output. Are there any known limitations. This query selects from the master records wherein child records are not active. select * from ct_software_release csr where csr.class = 'NS' ANDcsr.active_flag = 'Y' ANDcsr.os_id_pk not IN (SELECT crs.os_id_fk1 FROM CT_ROADMAP_SOFTWARE crs WHERE crs.active_flag = 'Y'); The sub-query in the above case gives 1800 rows. The above query fails to give any rows. select * from ct_software_release csr where csr.class = 'NS' ANDcsr.active_flag = 'Y' ANDNOT EXISTS (SELECT 1 FROM CT_ROADMAP_SOFTWARE crs WHERE crs.os_id_fk1 = csr.os_id_pk AND crs.active_flag = 'Y'); This above query works fine. Thanks -- Siddharth Haldankar Zensar Technologies Ltd. Cisco Systems Inc. (Offshore Development Center) # : 091 020 4128394 [EMAIL PROTECTED] [EMAIL PROTECTED] * Disclaimer This message (including any attachments) contains confidential information intended for a specific individual and purpose, and is protected by law. If you are not the intended recipient, you should delete this message and are hereby notified that any disclosure, copying, or distribution of this message, or the taking of any action based on it, is strictly prohibited. * Visit us at http://www.mahindrabt.com -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala 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: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Re: Any known problems using NOT IN ?
uhh... thats not exactly true. oracle is a bastardization of set theory. there are alot of cases where 'not exists' and 'not in'(if doing a hash anti-join' are much superior to minus. here are some generalizations. 1. not in with a hash_aj is the best if -- your sub-query is significantly less 'costly' then your out query... that is NOT based on the 'cost' of the explain plain. 2. If they are about the same or the out is more costly, go with not exists. 3. If you need to do a large full tablescan or if the outer query is very small relative to the inner query, minus tends to be the best. These are broad generalizations, but work well and are MUCH better than just guessing. From: Mladen Gogala [EMAIL PROTECTED] Date: 2003/12/18 Thu AM 09:14:24 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: Any known problems using NOT IN ? Actually, because relational database conform to the rules of set theory, I find it preferable to use MINUS wherever possible. Oracle optimizer is trained to spot set operations and they usually generate sort/merge or hash based execution plan, while NOT IN and NOT EXIST can generate NL plan, which is, generally speaking, undesired when you do set operations. On 12/18/2003 12:39:26 AM, Charu Joshi wrote: Siddharth, The NOT IN query fails to return rows, if the inner sub-query returns NULL values. It is always recommended to use the NOT EXISTS clause, unless you are sure that the inner query will not return any NULLs. Regards, Charu. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Siddharth Haldankar Sent: 18 December 2003 10:54 To: Multiple recipients of list ORACLE-L Subject: Any known problems using NOT IN ? Hi Gurus, I have a problem using NOT IN clause in Oracle. However using NOT EXISTS, gives me the right output. Are there any known limitations. This query selects from the master records wherein child records are not active. select * from ct_software_release csr where csr.class = 'NS' ANDcsr.active_flag = 'Y' ANDcsr.os_id_pk not IN (SELECT crs.os_id_fk1 FROM CT_ROADMAP_SOFTWARE crs WHERE crs.active_flag = 'Y'); The sub-query in the above case gives 1800 rows. The above query fails to give any rows. select * from ct_software_release csr where csr.class = 'NS' ANDcsr.active_flag = 'Y' ANDNOT EXISTS (SELECT 1 FROM CT_ROADMAP_SOFTWARE crs WHERE crs.os_id_fk1 = csr.os_id_pk AND crs.active_flag = 'Y'); This above query works fine. Thanks -- Siddharth Haldankar Zensar Technologies Ltd. Cisco Systems Inc. (Offshore Development Center) # : 091 020 4128394 [EMAIL PROTECTED] [EMAIL PROTECTED] * Disclaimer This message (including any attachments) contains confidential information intended for a specific individual and purpose, and is protected by law. If you are not the intended recipient, you should delete this message and are hereby notified that any disclosure, copying, or distribution of this message, or the taking of any action based on it, is strictly prohibited. * Visit us at http://www.mahindrabt.com -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala 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: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information
RE: OEM permissions
Raj - Thanks for your reply. Were this a consultant, my reply would mirror yours, and maybe not so diplomatically. But basically I manage these databases on behalf of this manager, so when he asks for read-only access, I can't really refuse. And I think he is pretty competent as a DBA. He says that he prefers to use OEM instead of Toad. What I'm really asking is what could these grants be used for besides just reading data? If there are other actions that could be done, I could at least ask him not to perform those actions, so if something bad happens I have provided an alert ahead of time. For those who use OEM in your environment, does the SELECT_CATALOG_ROLE and SELECT ANY DICTIONARY privileges sound pretty usual for OEM to be able to scout out the info it needs to paint the pretty displays? Yes, I am checking out how this exposes links and what is available on the other systems the links point to. I have also asked his group not to create any database links. Fortunately we have relatively few links. Again, thanks for your advice. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, December 18, 2003 7:54 AM To: Multiple recipients of list ORACLE-L Dennis, select any table has to be a big no no ... anyone can select from sys.link$. But I am still trying how OEM can be used for _development_?? what am I missing? As for One of our groups hired a new consultant and he (claimed to have DBA background) immediately shot off an email saying he needed select any table and select catalog role to do his work. We shot off reply Thanks for your email, while we appreciate your requirements for development, the privileges you are requesting are a tad different than we grant other developers. However we request that you submit a justification for these privileges and tell us how your development would be affected without these and we will accommodate your request. This was 3 months ago and we _still_ haven't heard back. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Thursday, December 18, 2003 8:24 AM To: Multiple recipients of list ORACLE-L We have a new manager that wants his group to use OEM for development access, as an alternative to Toad. He has requested a special Oracle userid with the following grants: SELECT_CATALOG_ROLE SELECT ANY DICTIONARY SELECT ANY TABLE Does this seem reasonable for OEM? The manager is responsible for the data in the database, so I don't see a problem with him viewing the data. There are few database links, and I'll be reviewing them. Any ideas on what mischief could occur? Thanks. ** This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. **5 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra 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: DENNIS WILLIAMS 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: DB link to standby
DB link to standbyFirst, I don't understandby why connect to a not open standby. Second, maybe this also works for you, like: [EMAIL PROTECTED] oracle]$ sqlplus internal/[EMAIL PROTECTED] @test.sql SQL*Plus: Release 8.1.7.0.0 - Production on Thu Dec 18 23:59:20 2003 (c) Copyright 2000 Oracle Corporation. All rights reserved. Connected to: Oracle8i Enterprise Edition Release 8.1.7.2.0 - 64bit Production With the Partitioning option JServer Release 8.1.7.2.0 - 64bit Production COUNT(*) -- 8 Disconnected from Oracle8i Enterprise Edition Release 8.1.7.2.0 - 64bit Production With the Partitioning option JServer Release 8.1.7.2.0 - 64bit Production [EMAIL PROTECTED] oracle]$ cat test.sql select count(*) from v$session; exit - Original Message - To: Multiple recipients of list ORACLE-L Sent: Thursday, December 18, 2003 9:59 PM On standby database server, you can connect to / as sysdba and query fixed tables/views. Is it possible to create a database link which connects to sysdba user to query the fixed tables/view? I tried, but getting following error ORA-02068: following severe error from TEST_SB ORA-01033: ORACLE initialization or shutdown in progress Thanks Ashish -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: zhu chao INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
OT PHP and Oracle
I am trying through php to do select username from v$session I keep getting Parse error: parse error, unexpected $end in c:\program files\apache group\Apache2\htdocs\actusers.php on line 23 Can anybody help so i can escape the $ char please as this is driving me mental Cheers _ Find a cheaper internet access deal - choose one to suit you. http://www.msn.co.uk/internetaccess -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Craig Richards 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 permissions
Dennis, I think you are probably ok with this. But the best way to do this is to create an Oracle account, grant what he asks, and start OEM using that account. Try and change things and see what happens. Then you will know for sure what the impact is. Good Luck! Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, December 18, 2003 10:35 AM To: Multiple recipients of list ORACLE-L Raj - Thanks for your reply. Were this a consultant, my reply would mirror yours, and maybe not so diplomatically. But basically I manage these databases on behalf of this manager, so when he asks for read-only access, I can't really refuse. And I think he is pretty competent as a DBA. He says that he prefers to use OEM instead of Toad. What I'm really asking is what could these grants be used for besides just reading data? If there are other actions that could be done, I could at least ask him not to perform those actions, so if something bad happens I have provided an alert ahead of time. For those who use OEM in your environment, does the SELECT_CATALOG_ROLE and SELECT ANY DICTIONARY privileges sound pretty usual for OEM to be able to scout out the info it needs to paint the pretty displays? Yes, I am checking out how this exposes links and what is available on the other systems the links point to. I have also asked his group not to create any database links. Fortunately we have relatively few links. Again, thanks for your advice. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, December 18, 2003 7:54 AM To: Multiple recipients of list ORACLE-L Dennis, select any table has to be a big no no ... anyone can select from sys.link$. But I am still trying how OEM can be used for _development_?? what am I missing? As for One of our groups hired a new consultant and he (claimed to have DBA background) immediately shot off an email saying he needed select any table and select catalog role to do his work. We shot off reply Thanks for your email, while we appreciate your requirements for development, the privileges you are requesting are a tad different than we grant other developers. However we request that you submit a justification for these privileges and tell us how your development would be affected without these and we will accommodate your request. This was 3 months ago and we _still_ haven't heard back. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Thursday, December 18, 2003 8:24 AM To: Multiple recipients of list ORACLE-L We have a new manager that wants his group to use OEM for development access, as an alternative to Toad. He has requested a special Oracle userid with the following grants: SELECT_CATALOG_ROLE SELECT ANY DICTIONARY SELECT ANY TABLE Does this seem reasonable for OEM? The manager is responsible for the data in the database, so I don't see a problem with him viewing the data. There are few database links, and I'll be reviewing them. Any ideas on what mischief could occur? Thanks. ** This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. **5 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra 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: DENNIS WILLIAMS 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
RE: RMAN Retention Policy
Did you connect to the target database first? I know this is a simple thing but sometimes we forget. Regards, Ruth -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of MacGregor, Ian A. Sent: Wednesday, December 17, 2003 5:04 PM To: Multiple recipients of list ORACLE-L Subject: RMAN Retention Policy How is this set on 8.1.7 and 8.1.6 databases RMAN CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 8 DAYS; RMAN-00571: === RMAN-00569: === ERROR MESSAGE STACK FOLLOWS === RMAN-00571: === RMAN-00558: error encountered while parsing input commands RMAN-01005: syntax error: found identifier: expecting one of: compatible RMAN-01008: the bad identifier was: RETENTION RMAN-01007: at line 1 column 11 file: standard input I looked at commands such as crosscheck backup of database completed before 'SYSDATE-7'; delete expired backup of database completed before 'SYSDATE-7'; But crosscheck only expires backups which are in the catalog, but not available on the backup media. Do I have to use the change command and designate each backup piece? Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: MacGregor, Ian A. 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: Ruth Gramolini 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: DB link to standby
Make a sure your other db you want to link is not going down:( - Original Message - DATE: Thu, 18 Dec 2003 05:59:33 From: Ashish Sahasrabudhe [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Cc: On standby database server, you can connect to "/ as sysdba" and query fixed tables/views. Is it possible to create a database link which connects to sysdba user to query the fixed tables/view? I tried, but getting following error ORA-02068: following severe error from TEST_SB ORA-01033: ORACLE initialization or shutdown in progress Thanks Ashish Free Poetry Contest. Win $10,000. Submit your poem @ Poetry.com!
ORA-1652: temp segment in tablespace SYSTEM ?!?
--_=_NextPart_001_01C3C583.D0824CB6 Content-Type: text/plain hey everyone ... The developers are loading blobs over database links. One of the 24 subsets of data is failing with the following error: ORA-1652: unable to extend temp segment by 512 in tablespace SYSTEM The subset that is failing is reading a large amount of MRP data. The error occurs in the instance the data is being pulled from. Can someone help me figure out why is this occurring in 'SYSTEM'? All of the users involved in this process are set to use 'TEMP' as their temp space and the target tables are not in 'SYSTEM' either. I've queried v$session and v$sort_usage while the process is running, but all the sorts I see are in 'TEMP'. I've also checked dba_tables and dba_indexes, and aren't any stray objects in the 'SYSTEM' tablespace. What else should I look for? We have plenty of space in TEMP if I can get this process to use it. TIA ... Robyn - - - - - - - Appended by Scientific-Atlanta, Inc. - - - - - - - This e-mail and any attachments may contain information which is confidential, proprietary, privileged or otherwise protected by law. The information is solely intended for the named addressee (or a person responsible for delivering it to the addressee). If you are not the intended recipient of this message, you are not authorized to read, print, retain, copy or disseminate this message or any part of it. If you have received this e-mail in error, please notify the sender immediately by return e-mail and delete it from your computer. --_=_NextPart_001_01C3C583.D0824CB6 Content-Type: text/html !DOCTYPE HTML PUBLIC -//W3C//DTD HTML 4.0 Transitional//EN HTMLHEAD META HTTP-EQUIV=Content-Type CONTENT=text/html; charset=US-ASCII TITLEMessage/TITLE META content=MSHTML 6.00.2800.1276 name=GENERATOR/HEAD BODY DIVFONT face=Arial size=2SPAN class=953251116-18122003hey everyone .../SPAN/FONT/DIV DIVFONT face=Arial size=2SPAN class=953251116-18122003/SPAN/FONTnbsp;/DIV DIVFONT face=Arial size=2SPAN class=953251116-18122003The developers are loading blobs over database links.nbsp; One of the 24 subsets of data is failing with the following error:/SPAN/FONT/DIV DIVFONT size=2FONT face=ArialSPAN class=953251116-18122003/SPAN/FONTFONT face=ArialSPAN class=953251116-18122003/SPAN/FONT/FONTnbsp;/DIV DIVFONT face=ArialFONT size=2SPAN class=953251116-18122003nbsp;nbsp; /SPANORA-1652: unable to extend temp segment by 512 in tablespacenbsp;SYSTEM/FONT/FONT/DIV DIVFONT face=Arial size=2/FONTnbsp;/DIV DIVSPAN class=953251116-18122003FONT face=Arial size=2The subset that is failing is reading a large amount of MRP data.nbsp; The error occurs in the instance the data is being pulled from./FONT/SPAN/DIV DIVSPAN class=953251116-18122003FONT face=Arial size=2/FONT/SPANnbsp;/DIV DIVFONT size=2SPAN class=953251116-18122003FONT face=ArialCan someone help me figure out why /FONT/SPANSPAN class=953251116-18122003FONT face=Arialis this occurring in 'SYSTEM'?nbsp; All of the users involved in this process are set to usenbsp;'TEMP' as their tempnbsp;spacenbsp;and the target tables are not in 'SYSTEM' either.nbsp; I've queriednbsp;v$sessionnbsp; and v$sort_usage while the process is running, but all the sorts I see are in 'TEMP'.nbsp; I've also checked dba_tables and dba_indexes, andnbsp;aren't any stray objects in the 'SYSTEM' tablespace.nbsp; What else should I look for?nbsp; We have plenty of space in TEMP if I can get this process to use it./FONT/SPAN/FONT/DIV DIVSPAN class=953251116-18122003FONT face=Arial size=2/FONT/SPANnbsp;/DIV DIVSPAN class=953251116-18122003FONT face=Arial size=2TIA ... Robyn/FONT/SPAN/DIV DIVSPAN class=953251116-18122003FONT face=Arial size=2/FONT/SPANnbsp;/DIV DIVFONT face=Arial size=2/FONTnbsp;/DIVFONT SIZE=3BR BR - - - - - - - Appended by Scientific-Atlanta, Inc. - - - - - - - BR This e-mail and any attachments may contain information which is confidential, proprietary, privileged or otherwise protected by law. The information is solely intended for the named addressee (or a person responsible for delivering it to the addressee). If you are not the intended recipient of this message, you are not authorized to read, print, retain, copy or disseminate this message or any part of it. If you have received this e-mail in error, please notify the sender immediately by return e-mail and delete it from your computer.BR /FONT /BODY/HTML --_=_NextPart_001_01C3C583.D0824CB6-- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Sands, Robyn 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
Re: Career Advice
On Wed, 17 Dec 2003, Viktor wrote: Let's take this topic into a more concrete scenario. New boss, company reorg, cross-training is enforced and now DBA'S's are going to be split into development groups. Need to learn Perl(looking forward to it actually!!!) and Java. Books, web sites, docs - all these material is great. But what if you're expected to learn fast and I can learn quickly, but still, do you guys have some advice on how can one express teach himself. Managing expectation is one thing I need to talk with boss about. Surely I would not't want to be overwhelmed with stuff at the beginning. But at the same time I am kinda excited about picking up on Java and Perl. The questions is what are the tricks and tips for learning on a fast track? For Perl, hang out a lot at perlmonks.org. Ok, that's just one more thing for you to read, but there's some good wisdom to be found there, some very smart Perl folks there, akin to the caliber of Oracle gurus found in this group. As for fast track, aint no learnin' like doin'. If I were in your situation, here's what I would be doing: Devour a good tutorial book, working through the examples. For Perl, O'Reilly's Learning Perl. For Java, O'Reilly's Head First Java (Don't let the pictures fool you...) HFJ is really quite good. When I first saw it I thought, Oh no, O'Reilly has stooped to the for Dummies level, but such was not the case at all. Emphasis here on 'working through the examples'. Also, as I mentioned above, hang out at 'guru' spots like this list, or perlmonks.org. (Not sure where the real guru spots are for Java...) Looking back on my own learning experience with Perl, I've read a lot of stuff, but it wasn't until I really started getting my hands dirty with it on a couple of bigger projects that it started to become more natural to me. If you have the free time and resources, set up a home-lab with Apache/mod_perl and write a Perl application to do something useful for you. Then write the same thing in Java (maybe with Tomcat). HTH, -- Dan Daniel Hanks - Systems/Database Administrator About Inc., Web Services Division -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Daniel Hanks 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 9i reports
This was true even for 6i after about patchset 6, IIRC. Many notes on metalink concerning this. In a nutshell.. 1. install VNC 2. start a vnc server at a specific screen address, such as 11 3. add a export DISPLAY=host:11 to the .profile of the reports server. 4. go have a beer. Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan [EMAIL PROTECTED] o.uk To: [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED]Subject: Oracle 9i reports .com 12/17/2003 02:24 PM Please respond to ORACLE-L Hi All, Can you please clarify me the following. We have been using reports 6i on Solaries, Now We would like to migrate to Reports 9i, We are told that (As Character set was depricated in 9i ) we need to keep one xterm console open and ruuning every time we execute rwrun. is this true? are there any work-around? thanks for your help, Ravi. BT Yahoo! Broadband - Save £80 when you order online today. Hurry! Offer ends 21st December 2003. The way the internet was meant to be. http://uk.rd.yahoo.com/evt=21064/*http://btyahoo.yahoo.co.uk -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?iso-8859-1?q?Nalla=20Ravi?= INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ron Thomas INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: ORA-1652: temp segment in tablespace SYSTEM ?!?
Are you sure they are not loading blobs into SYSTEM tablespace? Guang -Original Message- Sands, Robyn Sent: Thursday, December 18, 2003 11:29 AM To: Multiple recipients of list ORACLE-L This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. --_=_NextPart_001_01C3C583.D0824CB6 Content-Type: text/plain hey everyone ... The developers are loading blobs over database links. One of the 24 subsets of data is failing with the following error: ORA-1652: unable to extend temp segment by 512 in tablespace SYSTEM The subset that is failing is reading a large amount of MRP data. The error occurs in the instance the data is being pulled from. Can someone help me figure out why is this occurring in 'SYSTEM'? All of the users involved in this process are set to use 'TEMP' as their temp space and the target tables are not in 'SYSTEM' either. I've queried v$session and v$sort_usage while the process is running, but all the sorts I see are in 'TEMP'. I've also checked dba_tables and dba_indexes, and aren't any stray objects in the 'SYSTEM' tablespace. What else should I look for? We have plenty of space in TEMP if I can get this process to use it. TIA ... Robyn -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Guang Mei 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 permissions
I would say OEM for dbas only. To difficult to manage for developers. Dangerous. Too much low level stuff for managers and developers to poke around with. OEM is not really a development tool. 2 cents -Original Message- Sent: Thursday, December 18, 2003 8:24 AM To: Multiple recipients of list ORACLE-L Wouldn't this allow viewing DBA_USERS? I haven't tried this myself, but it seems that I could set up another oracle instance, create a user identified by values, then create database link. -Original Message- We have a new manager that wants his group to use OEM for development access, as an alternative to Toad. He has requested a special Oracle userid with the following grants: SELECT_CATALOG_ROLE SELECT ANY DICTIONARY SELECT ANY TABLE Does this seem reasonable for OEM? The manager is responsible for the data in the database, so I don't see a problem with him viewing the data. There are few database links, and I'll be reviewing them. Any ideas on what mischief could occur? Thanks. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS 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: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Odland, Brad 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: db block gets /consistent gets
Mark, That is perhaps the most concise and easy to understand explanation of current mode vs. consistent mode that I have yet seen. Thanks for posting it. Jared Bobak, Mark [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 12/17/2003 11:24 PM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: db block gets /consistent gets Syed, Oracle accesses blocks in one of two modes, current or consistent. A 'db block get' is a current mode get. That is, it's the most up-to-date copy of the data in that block, as it is right now, or currently. There can only be one current copy of a block in the buffer cache at any time. Db block gets generally are used when DML changes data in the database. In that case, row-level locks are implicitly taken on the updated rows. There is also at least one well-known case where a select statement does a db block get, and does not take a lock. That is, when it does a full table scan or fast full index scan, Oracle will read the segment header in current mode (multiple times, the number varies based on Oracle version). A 'consistent get' is when Oracle gets the data in a block which is consistent with a given point in time, or SCN. The consistent get is at the heart of Oracle's read consistency mechanism. When blocks are fetched in order to satisfy a query result set, they are fetched in consistent mode. If no block in the buffer cache is consistent to the correct point in time, Oracle will (attempt to) reconstruct that block using the information in the rollback segments. If it fails to do so, that's when a query errors out with the much dreaded, much feared, and much misunderstood ORA-1555 snapshot too old. As to latching, and how it relates, well, consider that the block buffers are in the SGA, which is shared memory. To avoid corruption, latches are used to serialize access to many linked lists and data structures that point to the buffers as well as the buffers themselves. It is safe to say that each consistent get introduces serialization to the system, and by tuning SQL to use more efficient access paths, you can get the same answer to the same query but do less consistent gets. This not only consumes less CPU, it also can significantly reduce latching which reduces serialization and makes your system more scalable. Well, that turned out longer than I planned. If you're still reading, I hope it helped! -Mark -Original Message- Sent: Thu 12/18/2003 1:39 AM To: Multiple recipients of list ORACLE-L Cc: Hi list, What is db block gets and consistent gets.? How can I reduce consistent gets ? Ask Tom says each consistent gets is latch, how it could be? Thanks in advance Syed -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Bobak, Mark INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: OT PHP and Oracle
The universally accepted escape character is \. Have you tried that? Craig Richards [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 12/18/2003 08:14 AM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:OT PHP and Oracle I am trying through php to do select username from v$session I keep getting Parse error: parse error, unexpected $end in c:\program files\apache group\Apache2\htdocs\actusers.php on line 23 Can anybody help so i can escape the $ char please as this is driving me mental Cheers _ Find a cheaper internet access deal - choose one to suit you. http://www.msn.co.uk/internetaccess -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Craig Richards 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: db block gets /consistent gets
Thanks. I was having a bout of insomnia last night, so I'm just glad it came out sounding coherent! ;-) -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]Sent: Thursday, December 18, 2003 12:54 PMTo: Multiple recipients of list ORACLE-LSubject: RE: db block gets /consistent getsMark, That is perhaps the most concise and easy to understand explanation of current mode vs. consistent mode that I have yet seen. Thanks for posting it. Jared "Bobak, Mark" [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 12/17/2003 11:24 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: db block gets /consistent getsSyed,Oracle accesses blocks in one of two modes, current or consistent.A 'db block get' is a current mode get. That is, it's the most up-to-datecopy of the data in that block, as it is right now, or currently. Therecan only be one current copy of a block in the buffer cache at any time.Db block gets generally are used when DML changes data in the database.In that case, row-level locks are implicitly taken on the updated rows.There is also at least one well-known case where a select statement doesa db block get, and does not take a lock. That is, when it does a fulltable scan or fast full index scan, Oracle will read the segment headerin current mode (multiple times, the number varies based on Oracle version).A 'consistent get' is when Oracle gets the data in a block which is consistentwith a given point in time, or SCN. The consistent get is at the heart ofOracle's read consistency mechanism. When blocks are fetched in order tosatisfy a query result set, they are fetched in consistent mode. If noblock in the buffer cache is consistent to the correct point in time, Oraclewill (attempt to) reconstruct that block using the information in the rollbacksegments. If it fails to do so, that's when a query errors out with the much dreaded, much feared, and much misunderstood ORA-1555 "snapshot too old".As to latching, and how it relates, well, consider that the block buffersare in the SGA, which is shared memory. To avoid corruption, latches are used to serialize access to many linked lists and data structures that pointto the buffers as well as the buffers themselves. It is safe to say that each consistent get introduces serialization to the system, and by tuningSQL to use more efficient access paths, you can get the same answer to thesame query but do less consistent gets. This not only consumes less CPU,it also can significantly reduce latching which reduces serialization andmakes your system more scalable.Well, that turned out longer than I planned. If you're still reading,I hope it helped!-Mark-Original Message-Sent: Thu 12/18/2003 1:39 AMTo: Multiple recipients of list ORACLE-LCc: Hi list,What is db block gets and consistent gets.?How can I reduce consistent gets ?Ask Tom says each consistent gets is latch, how it could be?Thanks in advanceSyed-- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: Bobak, MarkINET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California-- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
RE: Checking that the instance is up
Mark, Raj - Thank you both for coming back on that request of mine so promptly - I feel that I have a surfeit of riches! peter edinburgh -Original Message- From: Mark Leith [mailto:[EMAIL PROTECTED] Sent: Thursday, December 18, 2003 2:54 PM To: Multiple recipients of list ORACLE-L Subject: RE: Checking that the instance is up Hi Peter, I remember the thread, but couldn't remember the subject line at all.. Having had this discussion countless times in the past, here's what I would opt for: The best way to make sure that an instance is alive and running would be to do a full connection to that instance, and run a simple piece of sql - such as select 'PING' from dual;. This makes sure that both the listener, and instance are both up and readily accepting connections. You can also monitor for the listener status separately, based upon a TNSPING SID command, that will alert simply to the status of the listener. We do both of these with our monitoring tool, and generate ENTITY_DOWN and NETWORK_DOWN alerts respectively. If you get both an ENTITY_DOWN and NETWORK_DOWN error message at the same time, then this helps to point you at the listener quicker than trying to diagnose why the instance is down. If you just get an ENTITY_DOWN message on it's own, then it points to a database based problem. You should also try and catch the error messages on a failure, and flag those in your warning messages. And of course, there is the point of monitoring the monitor. You should have some process in place that is making sure that the monitor is up and running correctly. Whether this be a date/timestamp placed in to a log file with every successful ping, or making sure a cron/AT job is running etc. depends on how you do this.. Of course, you could buy a tool that already does all of this.. ;) HTH Mark -Original Message- Robson, Peter Sent: 18 December 2003 13:59 To: Multiple recipients of list ORACLE-L Folks - Some months ago there were exchanges on the optimum way to check that an instance was alive. I have searched, hunted and generally scavenged for these exchanges, but without luck. Can anyone give me the subject line, and I can turn again to the archive? thanks, peter edinburgh * This e-mail message, and any files transmitted with it, are confidential and intended solely for the use of the addressee. If this message was not addressed to you, you have received it in error and any copying, distribution or other use of any part of it is strictly prohibited. Any views or opinions presented are solely those of the sender and do not necessarily represent those of the British Geological Survey. The security of e-mail communication cannot be guaranteed and the BGS accepts no liability for claims arising as a result of the use of this medium to transmit messages from or to the BGS. .http://www.bgs.ac.uk * -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Robson, Peter 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). --- Incoming mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.547 / Virus Database: 340 - Release Date: 02/12/2003 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.547 / Virus Database: 340 - Release Date: 02/12/2003 -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Robson, Peter INET: [EMAIL PROTECTED] Fat City Network
RE: ORA-1652: temp segment in tablespace SYSTEM ?!?
--_=_NextPart_001_01C3C594.461C3ED6 Content-Type: text/plain No, they weren't loading into the SYSTEM tablespace. Turned out that the AQUSER account had SYSTEM as a temp space in one of the two instances. Didn't think to check AQUSER when I checked the settings for the users running the procedure on either side of the link. Still don't know why the temp segment in SYSTEM wasn't listed in the v$session and v$sort query. This is the query I ran: SELECT b.tablespace, b.segfile#, b.segblk#, b.blocks, a.sid, a.serial#,a.username, a.osuser, a.status FROM v$session a,v$sort_usage b WHERE a.saddr = b.session_addr ORDER BY b.tablespace, b.segfile#, b.segblk#, b.blocks; Robyn -Original Message- Sent: Thursday, December 18, 2003 12:34 PM To: Multiple recipients of list ORACLE-L Are you sure they are not loading blobs into SYSTEM tablespace? Guang -Original Message- Sands, Robyn Sent: Thursday, December 18, 2003 11:29 AM To: Multiple recipients of list ORACLE-L This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. --_=_NextPart_001_01C3C583.D0824CB6 Content-Type: text/plain hey everyone ... The developers are loading blobs over database links. One of the 24 subsets of data is failing with the following error: ORA-1652: unable to extend temp segment by 512 in tablespace SYSTEM The subset that is failing is reading a large amount of MRP data. The error occurs in the instance the data is being pulled from. Can someone help me figure out why is this occurring in 'SYSTEM'? All of the users involved in this process are set to use 'TEMP' as their temp space and the target tables are not in 'SYSTEM' either. I've queried v$session and v$sort_usage while the process is running, but all the sorts I see are in 'TEMP'. I've also checked dba_tables and dba_indexes, and aren't any stray objects in the 'SYSTEM' tablespace. What else should I look for? We have plenty of space in TEMP if I can get this process to use it. TIA ... Robyn -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Guang Mei 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). - - - - - - - Appended by Scientific-Atlanta, Inc. - - - - - - - This e-mail and any attachments may contain information which is confidential, proprietary, privileged or otherwise protected by law. The information is solely intended for the named addressee (or a person responsible for delivering it to the addressee). If you are not the intended recipient of this message, you are not authorized to read, print, retain, copy or disseminate this message or any part of it. If you have received this e-mail in error, please notify the sender immediately by return e-mail and delete it from your computer. --_=_NextPart_001_01C3C594.461C3ED6 Content-Type: text/html Content-Transfer-Encoding: quoted-printable !DOCTYPE HTML PUBLIC -//W3C//DTD HTML 3.2//EN HTML HEAD META HTTP-EQUIV=3DContent-Type CONTENT=3Dtext/html; charset=3DUS-ASCII META NAME=3DGenerator CONTENT=3DMS Exchange Server version 5.5.2655.35 TITLERE: ORA-1652: temp segment in tablespace SYSTEM ?!?/TITLE /HEAD BODY PFONT SIZE=3D2No, they weren't loading into the SYSTEM tablespace.nbsp= ; Turned out that the AQUSER account had SYSTEM as a temp space in one of t= he two instances.nbsp; Didn't think to check AQUSER when I checked the set= tings for the users running the procedure on either side of the link.nbsp;= /FONT/P PFONT SIZE=3D2Still don't know why the temp segment in SYSTEM wasn't li= sted in the v$session and v$sort query.nbsp; This is the query I ran:/FON= T/P PFONT SIZE=3D2SELECT b.tablespace, b.segfile#, b.segblk#, b.blocks, /F= ONT BRFONT SIZE=3D2nbsp;nbsp;nbsp;nbsp;nbsp;nbsp; a.sid, a.serial#,a.= username, a.osuser, a.status/FONT BRFONT SIZE=3D2nbsp; FROM v$session a,v$sort_usage b/FONT BRFONT SIZE=3D2nbsp;WHERE a.saddr =3D b.session_addr/FONT BRFONT SIZE=3D2nbsp;ORDER BY b.tablespace, b.segfile#, b.segblk#, b.bl= ocks;/FONT /P PFONT SIZE=3D2Robyn/FONT /P PFONT SIZE=3D2-Original Message-/FONT BRFONT SIZE=3D2From: Guang Mei [A HREF=3Dmailto:[EMAIL PROTECTED]mail= to:[EMAIL PROTECTED]/A] /FONT BRFONT SIZE=3D2Sent: Thursday, December 18, 2003 12:34 PM/FONT BRFONT SIZE=3D2To: Multiple recipients of list ORACLE-L/FONT BRFONT SIZE=3D2Subject: RE: ORA-1652: temp segment in tablespace SYSTEM= ?!?/FONT /P BR BR PFONT
operating system block and db_block
Hi Group, Can any body explain that on Windows/UNIX what is the size of the block that is read at 1 time and how can we alighn or size oracle db_block_size such that it gives optimal performance ? Regards, Akshay
RE: Career Advice
DENNIS WILLIAMS scribbled on the wall in glitter crayon: Saira I think you have to decide what your goal is. Then you need to decide how to best accomplish that goal. One tool that can lead you toward a goal is self-study. I have used that tool many times myself. However, with experience you learn the self-study tool has its limits. To consider self-study, consider the following questions: 1. Is this an area that I can gain significant knowledge with a reasonable amount of effort? For example, are there good books available? Is the area well-defined enough for self-study? 2. Since I'm trying to substitute self-effort for work experience, is this an area where there are few people with real work experience? 3. Are there credentials that can be earned? i'd like to add one more... 4. is this something where getting it right will still give you a charge after doing it for 10 years or more? [and yes DBA and programming still do for me. but i'm finding the chances of being allowed to do it right are becoming few and far between.] -- Bill Shrek Thater ORACLE DBA I'm going to work my ticket if I can... -- Gilwell song [EMAIL PROTECTED] Time is an illusion, lunchtime doubly so. - Douglas Adams -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Thater, William 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: Career Advice
your goals should tie into the job market. you might absolutely love Pascal programming, but I dont recommend studying it. Right now(and I dont know how it will fluctuate), there is far, far, far more demand for Software Engineers who specialize in Java or .Net. Far, far, far, more than people who specialize in the Oracle database. I think there has been a fundamental shift in database development. In the past you would hire mostly Oracle specialized people to do most of your development. They would use forms or powerbuilder to do your GUIs. These days, a growing number of teams hire a large number of java or .Net experts and only a handful of database people. is this the best way to go? I dont know. I do see a trend though. How long will the trend last? I do not know. The biggest problem for IT workers is that we are so tied to one specific skillset and vendor. If Oracle prices themselves out of the market, our skills become far less valued. Employees today want super specialized skillsets. If you have them and they are hot, your set, but they wont be hot forever and its very hard to switch since people want experience in the specific skillset before hiring you. From: Thater, William [EMAIL PROTECTED] Date: 2003/12/18 Thu PM 01:44:37 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: Career Advice DENNIS WILLIAMS scribbled on the wall in glitter crayon: Saira I think you have to decide what your goal is. Then you need to decide how to best accomplish that goal. One tool that can lead you toward a goal is self-study. I have used that tool many times myself. However, with experience you learn the self-study tool has its limits. To consider self-study, consider the following questions: 1. Is this an area that I can gain significant knowledge with a reasonable amount of effort? For example, are there good books available? Is the area well-defined enough for self-study? 2. Since I'm trying to substitute self-effort for work experience, is this an area where there are few people with real work experience? 3. Are there credentials that can be earned? i'd like to add one more... 4. is this something where getting it right will still give you a charge after doing it for 10 years or more? [and yes DBA and programming still do for me. but i'm finding the chances of being allowed to do it right are becoming few and far between.] -- Bill Shrek Thater ORACLE DBA I'm going to work my ticket if I can... -- Gilwell song [EMAIL PROTECTED] Time is an illusion, lunchtime doubly so. - Douglas Adams -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Thater, William 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: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Simple SQL Question
Hello: I'm trying to figure out the new 9i outer joins. I can get a single table outer join working without any issues. But seem to keep getting errors when trying to do a two table outer join. I know it is just something with my syntax. Could anyone provide a quick sample, thanks in advance. A:= Base Table B:= Child Table 1 B:= Child Table 2 select a.emp, b.ValueA c.ValueB From base_table A outer join child_Table1 B on A.emp=B.Emp... I know the old way of select a.emp, b.ValueA c.ValueB From base_table A, child_Table1 B, child_Table2 C where A.emp=B.Emp(+) and A.emp=C.Emp(+) _ Grab our best dial-up Internet access offer: 6 months @$9.95/month. http://join.msn.com/?page=dept/dialup -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jay Wade 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: Simple SQL Question
select a.emp, b.ValueA c.ValueB From base_table A RIGHT OUTER JOIN child_Table1 B ON b.emp = a.emp RIGHT OUTER JOIN child_Table2 C ON c.emp = a.emp -Original Message- Sent: Thursday, December 18, 2003 2:20 PM To: Multiple recipients of list ORACLE-L Hello: I'm trying to figure out the new 9i outer joins. I can get a single table outer join working without any issues. But seem to keep getting errors when trying to do a two table outer join. I know it is just something with my syntax. Could anyone provide a quick sample, thanks in advance. A:= Base Table B:= Child Table 1 B:= Child Table 2 select a.emp, b.ValueA c.ValueB From base_table A outer join child_Table1 B on A.emp=B.Emp... I know the old way of select a.emp, b.ValueA c.ValueB From base_table A, child_Table1 B, child_Table2 C where A.emp=B.Emp(+) and A.emp=C.Emp(+) _ Grab our best dial-up Internet access offer: 6 months @$9.95/month. http://join.msn.com/?page=dept/dialup -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jay Wade 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: Kevin Toepke 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 Retention Policy
Thanks, for the example script. I had already reached the conclusion that change
backuppiece ... was needed, but
hadn't yet figured how to query for the proper pieces.
Ian MacGregor
Stanford linear Accelerator Center
[EMAIL PROTECTED]
-Original Message-
Sent: Thursday, December 18, 2003 5:07 AM
To: '[EMAIL PROTECTED]'
Cc: MacGregor, Ian A.
Ian,
I think retention policy is new in 9i.
I purge my repository of backups that are older than 90 days (because our tape systems
rotates and reuses tapes after that time) using the change backuppiece 330783 delete;
command. I run a sql script againts the rman repository looking for pieces that
satisfy this requirement. The sql looks like this:
select 'change backuppiece bp.bp_key delete;'
from rc_backup_piece bp,rc_database db
where db.name = upper('ORACLE_SID')
and bp.db_id = db.dbid
and bp.start_time sysdate-90
/
This is in an 8i database.
Hope this is what you were looking for.
Tom Mercadante
Oracle Certified Professional
-Original Message-
Sent: Wednesday, December 17, 2003 5:04 PM
To: Multiple recipients of list ORACLE-L
How is this set on 8.1.7 and 8.1.6 databases
RMAN CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 8 DAYS;
RMAN-00571: ===
RMAN-00569: === ERROR MESSAGE STACK FOLLOWS ===
RMAN-00571: ===
RMAN-00558: error encountered while parsing input commands
RMAN-01005: syntax error: found identifier: expecting one of: compatible
RMAN-01008: the bad identifier was: RETENTION
RMAN-01007: at line 1 column 11 file: standard input
I looked at commands such as
crosscheck backup of database completed before 'SYSDATE-7'; delete expired backup of
database completed before 'SYSDATE-7';
But crosscheck only expires backups which are in the catalog, but not available on the
backup media.
Do I have to use the change command and designate each backup piece?
Ian MacGregor
Stanford Linear Accelerator Center
[EMAIL PROTECTED]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: MacGregor, Ian A.
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: MacGregor, Ian A.
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: Career Advice
Ryan - Excellent points. I well know the feeling of being tied to Oracle's future. As to Oracle pricing itself out of the market, I would like to make three points: - Pricing is one of the quickest things a vendor can change once it becomes convinced this is hurting it. On the other hand, I've seen software vendors that stopped investing in new development. They aren't in business anymore because you can't quickly change that decision. - Oracle being perceived as high priced tends to increase our salaries. A company spends a lot of money on Oracle, so they want it used to good advantage. The salary surveys I've seen show MS SQL Server DBA with lower salaries on the average. - Has anyone seen salary survey results for MySQL or PostgreSQL? The database is free, so how much should a company spend on a DBA? Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, December 18, 2003 1:19 PM To: Multiple recipients of list ORACLE-L your goals should tie into the job market. you might absolutely love Pascal programming, but I dont recommend studying it. Right now(and I dont know how it will fluctuate), there is far, far, far more demand for Software Engineers who specialize in Java or .Net. Far, far, far, more than people who specialize in the Oracle database. I think there has been a fundamental shift in database development. In the past you would hire mostly Oracle specialized people to do most of your development. They would use forms or powerbuilder to do your GUIs. These days, a growing number of teams hire a large number of java or .Net experts and only a handful of database people. is this the best way to go? I dont know. I do see a trend though. How long will the trend last? I do not know. The biggest problem for IT workers is that we are so tied to one specific skillset and vendor. If Oracle prices themselves out of the market, our skills become far less valued. Employees today want super specialized skillsets. If you have them and they are hot, your set, but they wont be hot forever and its very hard to switch since people want experience in the specific skillset before hiring you. From: Thater, William [EMAIL PROTECTED] Date: 2003/12/18 Thu PM 01:44:37 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: Career Advice DENNIS WILLIAMS scribbled on the wall in glitter crayon: Saira I think you have to decide what your goal is. Then you need to decide how to best accomplish that goal. One tool that can lead you toward a goal is self-study. I have used that tool many times myself. However, with experience you learn the self-study tool has its limits. To consider self-study, consider the following questions: 1. Is this an area that I can gain significant knowledge with a reasonable amount of effort? For example, are there good books available? Is the area well-defined enough for self-study? 2. Since I'm trying to substitute self-effort for work experience, is this an area where there are few people with real work experience? 3. Are there credentials that can be earned? i'd like to add one more... 4. is this something where getting it right will still give you a charge after doing it for 10 years or more? [and yes DBA and programming still do for me. but i'm finding the chances of being allowed to do it right are becoming few and far between.] -- Bill Shrek Thater ORACLE DBA I'm going to work my ticket if I can... -- Gilwell song [EMAIL PROTECTED] Time is an illusion, lunchtime doubly so. - Douglas Adams -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Thater, William 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: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for
Re: OT PHP and Oracle
Its like: $p_param= SELECT * FROM v\$parameter order by name; -- Joe Frohne Rawson Oaks Consulting, Remote Oracle Admins http://www.rawsonoaks.com [EMAIL PROTECTED] or [EMAIL PROTECTED] Oak Creek, WI, USA I am trying through php to do select username from v$session I keep getting Parse error: parse error, unexpected $end in c:\program files\apache group\Apache2\htdocs\actusers.php on line 23 Can anybody help so i can escape the $ char please as this is driving me mental Cheers _ Find a cheaper internet access deal - choose one to suit you. http://www.msn.co.uk/internetaccess -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Craig Richards 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: Joe Frohne 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: Career Advice
my biggest concern is the model for development has been changed. The model now is do most development with software engineers and have only a small number of database people. this means less pure oracle jobs. From: DENNIS WILLIAMS [EMAIL PROTECTED] Date: 2003/12/18 Thu PM 02:59:26 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: RE: Career Advice Ryan - Excellent points. I well know the feeling of being tied to Oracle's future. As to Oracle pricing itself out of the market, I would like to make three points: - Pricing is one of the quickest things a vendor can change once it becomes convinced this is hurting it. On the other hand, I've seen software vendors that stopped investing in new development. They aren't in business anymore because you can't quickly change that decision. - Oracle being perceived as high priced tends to increase our salaries. A company spends a lot of money on Oracle, so they want it used to good advantage. The salary surveys I've seen show MS SQL Server DBA with lower salaries on the average. - Has anyone seen salary survey results for MySQL or PostgreSQL? The database is free, so how much should a company spend on a DBA? Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, December 18, 2003 1:19 PM To: Multiple recipients of list ORACLE-L your goals should tie into the job market. you might absolutely love Pascal programming, but I dont recommend studying it. Right now(and I dont know how it will fluctuate), there is far, far, far more demand for Software Engineers who specialize in Java or .Net. Far, far, far, more than people who specialize in the Oracle database. I think there has been a fundamental shift in database development. In the past you would hire mostly Oracle specialized people to do most of your development. They would use forms or powerbuilder to do your GUIs. These days, a growing number of teams hire a large number of java or .Net experts and only a handful of database people. is this the best way to go? I dont know. I do see a trend though. How long will the trend last? I do not know. The biggest problem for IT workers is that we are so tied to one specific skillset and vendor. If Oracle prices themselves out of the market, our skills become far less valued. Employees today want super specialized skillsets. If you have them and they are hot, your set, but they wont be hot forever and its very hard to switch since people want experience in the specific skillset before hiring you. From: Thater, William [EMAIL PROTECTED] Date: 2003/12/18 Thu PM 01:44:37 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: Career Advice DENNIS WILLIAMS scribbled on the wall in glitter crayon: Saira I think you have to decide what your goal is. Then you need to decide how to best accomplish that goal. One tool that can lead you toward a goal is self-study. I have used that tool many times myself. However, with experience you learn the self-study tool has its limits. To consider self-study, consider the following questions: 1. Is this an area that I can gain significant knowledge with a reasonable amount of effort? For example, are there good books available? Is the area well-defined enough for self-study? 2. Since I'm trying to substitute self-effort for work experience, is this an area where there are few people with real work experience? 3. Are there credentials that can be earned? i'd like to add one more... 4. is this something where getting it right will still give you a charge after doing it for 10 years or more? [and yes DBA and programming still do for me. but i'm finding the chances of being allowed to do it right are becoming few and far between.] -- Bill Shrek Thater ORACLE DBA I'm going to work my ticket if I can... -- Gilwell song [EMAIL PROTECTED] Time is an illusion, lunchtime doubly so. - Douglas Adams -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Thater, William 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: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat
** advanced replication :: not propagating
Hi, I newly setup master to master replication : I followed the steps. It is 9i so no need to run catrep: 1) Create replication administrator repadmin 2) Grant privileges and register repadminas propagator and receiver 3) Create public database links, private database link in repadmin. 4) Create master groups, add objects and generate support. 5) Add master sites. 6) Schedule push and purge jobs. 7) Resume master activity The arrangement is primary -- secondary. I have made the push and purge jobs at secondary as broken. After that I made updates to the replicated tables and I can see the entryin deftran. The push job executes at primarybut transactions are not being propagated to secondary. I ran the job manually. It does not give any error but does not propagate. I can still see the entries in deftrandest. The links are working fine. Maybe I missed a step but cannot figure out. When I give the command to quisce replication that is SUSPEND_MASTER_ACTIVITY, then it propagates and applies the change to the other site. Which is strange of course. I tried this couple of times just to make sure that is the case. Maybe quisce does some operation and it leads to it and maybe there is a clue there but I cannot find. Can someone help? Thank You. Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard
Humorous error message
Take a look at the results below. Look especially at the error message. I had to laugh when I read that. Hey, Oracle is telling me it got what it expected, so what's the problem, right? grin Bonus points if you can tell me why the query failed. SQL SELECT FROM_TZ(SYSTIMESTAMP,'-5:00') FROM dual; SELECT FROM_TZ(SYSTIMESTAMP,'-5:00') FROM dual * ERROR at line 1: ORA-00932: inconsistent datatypes: expected TIMESTAMP got TIMESTAMP Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, or send email to [EMAIL PROTECTED] and include the word subscribe in either the subject or body. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Gennick 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: Career Advice
Ryan -- I agree with a lot of what you said, but there are points that I really must disagree on. Firstly, you should *always* study what you love, but know that there are varying degrees of applicability to your chosen field, and certain areas will always be more lucrative than others. Furthermore, as you alluded to, the most lucrative technical areas are constantly in a state of flux. Where VMS was more lucrative than Unix in the mid-80s, the two switched positions in the mid-90s, and VMS is, quite unfortunately, largely dead now. The best thing one can do with a technical career, as you and others have said, is to diversify; but, when one adds breadth, one often sacrifices depth. If, for example, you choose to diversify and become a SunOS System Administrator or Powerbuilder programmer in addition to being an Oracle DBA, as time goes on, you will generally wind up giving one or both short shrift or sacrificing your free time in order to stay on top of both. The more additional responsibilities you add, the less depth you tend to keep. While you lower your exposure to vulnerability due to changes in the technical world or the caprices of the corporate world, you may also lower your overall value in both. Of course, you can market it that you have a bigger picture view of things and steer yourself career towards management; but, again, the higher the bird's eye view you have, the further you tend to be from the keyboard, and if the keyboard is what you love, that's what you need to keep doing. Now that I've been in the field for 25+ years, the best career advice I can give is to stay on top of current trends and be willing to gain competencies as various technologies wax and ditch areas - even of strength - as they wane. The other piece of advice I can give is that hot technology trends have a fixed shelf-life before new technologies replace them; try to stay with products that continue to evolve, or, if you are change-averse, stick with technologies that change very slowly but have proven their staying power (IBM, CICS, Unix). Finally, let me share one of the philosophical cornerstones of my life. Your life is delimited by two points in time: your moment of birth and your moment of death; you can do with the time in between largely what you will, and it is your use of time that defines you. Time is only currency with intrinsic value. If you wind up hating what you do for a living, you will wind up having wasted your life. HTH, Bambi. -Original Message- Sent: Thursday, December 18, 2003 1:19 PM To: Multiple recipients of list ORACLE-L your goals should tie into the job market. you might absolutely love Pascal programming, but I dont recommend studying it. Right now(and I dont know how it will fluctuate), there is far, far, far more demand for Software Engineers who specialize in Java or .Net. Far, far, far, more than people who specialize in the Oracle database. I think there has been a fundamental shift in database development. In the past you would hire mostly Oracle specialized people to do most of your development. They would use forms or powerbuilder to do your GUIs. These days, a growing number of teams hire a large number of java or .Net experts and only a handful of database people. is this the best way to go? I dont know. I do see a trend though. How long will the trend last? I do not know. The biggest problem for IT workers is that we are so tied to one specific skillset and vendor. If Oracle prices themselves out of the market, our skills become far less valued. Employees today want super specialized skillsets. If you have them and they are hot, your set, but they wont be hot forever and its very hard to switch since people want experience in the specific skillset before hiring you. From: Thater, William [EMAIL PROTECTED] Date: 2003/12/18 Thu PM 01:44:37 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: Career Advice DENNIS WILLIAMS scribbled on the wall in glitter crayon: Saira I think you have to decide what your goal is. Then you need to decide how to best accomplish that goal. One tool that can lead you toward a goal is self-study. I have used that tool many times myself. However, with experience you learn the self-study tool has its limits. To consider self-study, consider the following questions: 1. Is this an area that I can gain significant knowledge with a reasonable amount of effort? For example, are there good books available? Is the area well-defined enough for self-study? 2. Since I'm trying to substitute self-effort for work experience, is this an area where there are few people with real work experience? 3. Are there credentials that can be earned? i'd like to add one more... 4. is this something where getting it right will still give you a charge after doing it for 10 years or more? [and yes DBA and programming still do for me. but i'm finding the chances of being
Oracle 9i on AIX 5L(JSF2)
Anybody having problems with Oracle DB hanging up on AIX 5L using JSF2? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jerome Roa 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: Humorous error message
Could the problem be a difference of type? TIMESTAMP vs. TIMESTAMP WITH TIME ZONE The SYSTIMESTAMP function has the return type of TIMESTAMP WITH TIME ZONE. The FROM_TZ function says that it converts a TIMESTAMP to a TIMESTAMP WITH TIME ZONE. Given that I don't use timestamps much, this is only a guess. -Original Message- Jonathan Gennick Sent: Thursday, December 18, 2003 3:34 PM To: Multiple recipients of list ORACLE-L Take a look at the results below. Look especially at the error message. I had to laugh when I read that. Hey, Oracle is telling me it got what it expected, so what's the problem, right? grin Bonus points if you can tell me why the query failed. SQL SELECT FROM_TZ(SYSTIMESTAMP,'-5:00') FROM dual; SELECT FROM_TZ(SYSTIMESTAMP,'-5:00') FROM dual * ERROR at line 1: ORA-00932: inconsistent datatypes: expected TIMESTAMP got TIMESTAMP Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, or send email to [EMAIL PROTECTED] and include the word subscribe in either the subject or body. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Gennick 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: Clark, Tommy 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).
RE: OEM permissions - thanks!
Raj, Thomas, Brad, Bill, anyone I forgot - I feel better about the situation now. Thanks for letting me run this issue past you. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, December 18, 2003 9:35 AM To: Multiple recipients of list ORACLE-L Raj - Thanks for your reply. Were this a consultant, my reply would mirror yours, and maybe not so diplomatically. But basically I manage these databases on behalf of this manager, so when he asks for read-only access, I can't really refuse. And I think he is pretty competent as a DBA. He says that he prefers to use OEM instead of Toad. What I'm really asking is what could these grants be used for besides just reading data? If there are other actions that could be done, I could at least ask him not to perform those actions, so if something bad happens I have provided an alert ahead of time. For those who use OEM in your environment, does the SELECT_CATALOG_ROLE and SELECT ANY DICTIONARY privileges sound pretty usual for OEM to be able to scout out the info it needs to paint the pretty displays? Yes, I am checking out how this exposes links and what is available on the other systems the links point to. I have also asked his group not to create any database links. Fortunately we have relatively few links. Again, thanks for your advice. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, December 18, 2003 7:54 AM To: Multiple recipients of list ORACLE-L Dennis, select any table has to be a big no no ... anyone can select from sys.link$. But I am still trying how OEM can be used for _development_?? what am I missing? As for One of our groups hired a new consultant and he (claimed to have DBA background) immediately shot off an email saying he needed select any table and select catalog role to do his work. We shot off reply Thanks for your email, while we appreciate your requirements for development, the privileges you are requesting are a tad different than we grant other developers. However we request that you submit a justification for these privileges and tell us how your development would be affected without these and we will accommodate your request. This was 3 months ago and we _still_ haven't heard back. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Thursday, December 18, 2003 8:24 AM To: Multiple recipients of list ORACLE-L We have a new manager that wants his group to use OEM for development access, as an alternative to Toad. He has requested a special Oracle userid with the following grants: SELECT_CATALOG_ROLE SELECT ANY DICTIONARY SELECT ANY TABLE Does this seem reasonable for OEM? The manager is responsible for the data in the database, so I don't see a problem with him viewing the data. There are few database links, and I'll be reviewing them. Any ideas on what mischief could occur? Thanks. ** This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. **5 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra 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: DENNIS WILLIAMS 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
Re[2]: Humorous error message
Thursday, December 18, 2003, 4:04:32 PM, Clark, Tommy R ([EMAIL PROTECTED]) wrote: CTR Could the problem be a difference of type? TIMESTAMP vs. TIMESTAMP WITH CTR TIME ZONE Bingo! Tommy, you get the prize, only there is no prize, except perhaps for that warm-fuzzy feeling you get when you solve a problem. I wonder about that error message. My guess is the message is probably built up programmatically, and that the spaces inside the data type name (TIMESTAMP WITH TIME ZONE) trigger the glitch. That's only a guess. Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, or send email to [EMAIL PROTECTED] and include the word subscribe in either the subject or body. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Gennick 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: Career Advice
I agree with Ryan. Pure Oracle jobs aren't hot as they used to be. We are going throughthisright now.Theyare planning to bring in a bunch of new developers and splitting a few DBA's into dev. groups, which means we'll become more likesoftware engineers (who can also do DBA stuff). There will be only one Prod. DBA for a zillion systems. They're drivingin the direction ofbringing in more cross-trained people. They want all-aroind people who know Perl, Java, Oracle etc. The motto has been: "If you get hit by a bus, he/she can do it". The more you know, the better. Cross-training all the way.It's like thatall-in-one fax/printer/copier thing. And at the same time, the paycheck isn't as it had been either. Viktor [EMAIL PROTECTED] wrote: my biggest concern is the model for development has been changed. The model now is do most development with software engineers and have only a small number of database people. this means less pure oracle jobs. From: DENNIS WILLIAMS <[EMAIL PROTECTED]> Date: 2003/12/18 Thu PM 02:59:26 EST To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Subject: RE: RE: Career Advice Ryan - Excellent points. I well know the feeling of being tied to Oracle's future. As to Oracle pricing itself out of the market, I would like to make three points: - Pricing is one of the quickest things a vendor can change once it becomes convinced this is hurting it. On the other hand, I've seen software vendors that stopped investing in new development. They aren't in business anymore! because you can't quickly change that decision. - Oracle being perceived as high priced tends to increase our salaries. A company spends a lot of money on Oracle, so they want it used to good advantage. The salary surveys I've seen show MS SQL Server DBA with lower salaries on the average. - Has anyone seen salary survey results for MySQL or PostgreSQL? The database is free, so how much should a company spend on a DBA? Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, December 18, 2003 1:19 PM To: Multiple recipients of list ORACLE-L your goals should tie into the job market. you might absolutely love Pascal programming, but I dont recommend studying it. Right now(and I dont know how it will fluctuate), there is far, far, far more demand for Software Engineers who specialize in Java or .Net. Far, far, far, more than people who specialize in the Oracle database. I think there has been a fundamental shift in database development. In the past you would hire mostly Oracle specialized people to do most of your development. They would use forms or powerbuilder to do your GUIs. These days, a growing number of teams hire a large number of java or .Net experts and only a handful of database people. is this the best way to go? I dont know. I do see a trend though. How long will the trend last? I do not know. The biggest problem for IT workers is that we are so tied to one specific skillset and vendor. If Oracle prices themselves out of the market, our skills become far less valued. Employees today want super specialized skillsets. If you have them and they are hot, your set, but they wont be hot forever and i! ts very hard to switch since people want experience in the specific skillset before hiring you. From: "Thater, William" <[EMAIL PROTECTED]> Date: 2003/12/18 Thu PM 01:44:37 EST To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Subject: RE: Career AdviceDENNIS WILLIAMS scribbled on the wall in glitter crayon: Saira I think you have to decide what your goal is. Then you need to decide how to best accomplish that goal. One tool that can lead you toward a goal is self-study. I have used that tool many times myself. However, with experience you learn the self-study tool has its limits. To consider self-study, consider the following questions: 1. Is this an area that I can gain significant knowledge with a reasonable amount of effort? For example, are there good books available? Is the area well-defined enough for self-study? 2. Since I'm trying to substitute self-effort for work experience, is this an area where there are few people with real work experience? 3. Are there credentials that can be earned?i'd like to add one more... 4. is this something where getting it right will still give you a charge after doing it for 10 years or more?[and yes DBA and programming still do for me. but i'm finding the chances of being allowed to do it right are becoming few and far between.]-- Bill "Shrek" Thater ORACLE DBA "I'm going to work my ticket if I can..." -- Gilwell song [EMAIL PROTECTED] Time is an illusion, lunchtime doubly so. - Douglas Adams -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Thater, William INET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051
RE: RE: Career Advice
I agree with Ryan. Pure Oracle jobs aren't hot as they used to be. We are going throughthisright now.Theyare planning to bring in a bunch of new developers and splitting a few DBA's into dev. groups, which means we'll become more likesoftware engineers (who can also do DBA stuff). There will be only one Prod. DBA for a zillion systems. They're drivingin the direction ofbringing in more cross-trained people. They want all-aroind people who know Perl, Java, Oracle etc. The motto has been: "If you get hit by a bus, he/she can do it". The more you know, the better. Cross-training all the way.It's like thatall-in-one fax/printer/copier thing. And at the same time, the paycheck isn't as it had been either. Viktor [EMAIL PROTECTED] wrote: my biggest concern is the model for development has been changed. The model now is do most development with software engineers and have only a small number of database people. this means less pure oracle jobs. From: DENNIS WILLIAMS <[EMAIL PROTECTED]> Date: 2003/12/18 Thu PM 02:59:26 EST To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Subject: RE: RE: Career Advice Ryan - Excellent points. I well know the feeling of being tied to Oracle's future. As to Oracle pricing itself out of the market, I would like to make three points: - Pricing is one of the quickest things a vendor can change once it becomes convinced this is hurting it. On the other hand, I've seen software vendors that stopped investing in new development. They aren't in business anymore! because you can't quickly change that decision. - Oracle being perceived as high priced tends to increase our salaries. A company spends a lot of money on Oracle, so they want it used to good advantage. The salary surveys I've seen show MS SQL Server DBA with lower salaries on the average. - Has anyone seen salary survey results for MySQL or PostgreSQL? The database is free, so how much should a company spend on a DBA? Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, December 18, 2003 1:19 PM To: Multiple recipients of list ORACLE-L your goals should tie into the job market. you might absolutely love Pascal programming, but I dont recommend studying it. Right now(and I dont know how it will fluctuate), there is far, far, far more demand for Software Engineers who specialize in Java or .Net. Far, far, far, more than people who specialize in the Oracle database. I think there has been a fundamental shift in database development. In the past you would hire mostly Oracle specialized people to do most of your development. They would use forms or powerbuilder to do your GUIs. These days, a growing number of teams hire a large number of java or .Net experts and only a handful of database people. is this the best way to go? I dont know. I do see a trend though. How long will the trend last? I do not know. The biggest problem for IT workers is that we are so tied to one specific skillset and vendor. If Oracle prices themselves out of the market, our skills become far less valued. Employees today want super specialized skillsets. If you have them and they are hot, your set, but they wont be hot forever and i! ts very hard to switch since people want experience in the specific skillset before hiring you. From: "Thater, William" <[EMAIL PROTECTED]> Date: 2003/12/18 Thu PM 01:44:37 EST To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Subject: RE: Career AdviceDENNIS WILLIAMS scribbled on the wall in glitter crayon: Saira I think you have to decide what your goal is. Then you need to decide how to best accomplish that goal. One tool that can lead you toward a goal is self-study. I have used that tool many times myself. However, with experience you learn the self-study tool has its limits. To consider self-study, consider the following questions: 1. Is this an area that I can gain significant knowledge with a reasonable amount of effort? For example, are there good books available? Is the area well-defined enough for self-study? 2. Since I'm trying to substitute self-effort for work experience, is this an area where there are few people with real work experience? 3. Are there credentials that can be earned?i'd like to add one more... 4. is this something where getting it right will still give you a charge after doing it for 10 years or more?[and yes DBA and programming still do for me. but i'm finding the chances of being allowed to do it right are becoming few and far between.]-- Bill "Shrek" Thater ORACLE DBA "I'm going to work my ticket if I can..." -- Gilwell song [EMAIL PROTECTED] Time is an illusion, lunchtime doubly so. - Douglas Adams -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Thater, William INET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051
RE: Re: Any known problems using NOT IN ?
BTW, NOT IN and NOT EXIST are not synonyms like IN and EXISTS. It will give different results is most scenarios. Asktom site has great examples. -Original Message- [EMAIL PROTECTED] Sent: Thursday, December 18, 2003 10:30 AM To: Multiple recipients of list ORACLE-L btw, a straight not in without a hash_aj, tends to get hideous bench marks. From: Mladen Gogala [EMAIL PROTECTED] Date: 2003/12/18 Thu AM 09:14:24 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: Any known problems using NOT IN ? Actually, because relational database conform to the rules of set theory, I find it preferable to use MINUS wherever possible. Oracle optimizer is trained to spot set operations and they usually generate sort/merge or hash based execution plan, while NOT IN and NOT EXIST can generate NL plan, which is, generally speaking, undesired when you do set operations. On 12/18/2003 12:39:26 AM, Charu Joshi wrote: Siddharth, The NOT IN query fails to return rows, if the inner sub-query returns NULL values. It is always recommended to use the NOT EXISTS clause, unless you are sure that the inner query will not return any NULLs. Regards, Charu. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Siddharth Haldankar Sent: 18 December 2003 10:54 To: Multiple recipients of list ORACLE-L Subject: Any known problems using NOT IN ? Hi Gurus, I have a problem using NOT IN clause in Oracle. However using NOT EXISTS, gives me the right output. Are there any known limitations. This query selects from the master records wherein child records are not active. select * from ct_software_release csr where csr.class = 'NS' ANDcsr.active_flag = 'Y' ANDcsr.os_id_pk not IN (SELECT crs.os_id_fk1 FROM CT_ROADMAP_SOFTWARE crs WHERE crs.active_flag = 'Y'); The sub-query in the above case gives 1800 rows. The above query fails to give any rows. select * from ct_software_release csr where csr.class = 'NS' ANDcsr.active_flag = 'Y' ANDNOT EXISTS (SELECT 1 FROM CT_ROADMAP_SOFTWARE crs WHERE crs.os_id_fk1 = csr.os_id_pk AND crs.active_flag = 'Y'); This above query works fine. Thanks -- -- -- Siddharth Haldankar Zensar Technologies Ltd. Cisco Systems Inc. (Offshore Development Center) # : 091 020 4128394 [EMAIL PROTECTED] [EMAIL PROTECTED] * Disclaimer This message (including any attachments) contains confidential information intended for a specific individual and purpose, and is protected by law. If you are not the intended recipient, you should delete this message and are hereby notified that any disclosure, copying, or distribution of this message, or the taking of any action based on it, is strictly prohibited. * Visit us at http://www.mahindrabt.com -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala 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: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Sami 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
RE: ORA-1652: temp segment in tablespace SYSTEM ?!?
do you have temporary tablespace set for that user? -Original Message- Guang Mei Sent: Thursday, December 18, 2003 12:34 PM To: Multiple recipients of list ORACLE-L Are you sure they are not loading blobs into SYSTEM tablespace? Guang -Original Message- Sands, Robyn Sent: Thursday, December 18, 2003 11:29 AM To: Multiple recipients of list ORACLE-L This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. --_=_NextPart_001_01C3C583.D0824CB6 Content-Type: text/plain hey everyone ... The developers are loading blobs over database links. One of the 24 subsets of data is failing with the following error: ORA-1652: unable to extend temp segment by 512 in tablespace SYSTEM The subset that is failing is reading a large amount of MRP data. The error occurs in the instance the data is being pulled from. Can someone help me figure out why is this occurring in 'SYSTEM'? All of the users involved in this process are set to use 'TEMP' as their temp space and the target tables are not in 'SYSTEM' either. I've queried v$session and v$sort_usage while the process is running, but all the sorts I see are in 'TEMP'. I've also checked dba_tables and dba_indexes, and aren't any stray objects in the 'SYSTEM' tablespace. What else should I look for? We have plenty of space in TEMP if I can get this process to use it. TIA ... Robyn -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Guang Mei 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: Sami 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 tuning...
without knowing the requirement,index,statistics it is very hard to tell. -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]On Behalf Of Venu GopalSent: Thursday, December 18, 2003 5:29 AMTo: Multiple recipients of list ORACLE-LSubject: SQL tuning... Can anyone tell me whats wrong with the explain plan below this update is running for quite long time. Even without a single full-table access Thank you in advance! UPDATE STATEMENT Optimizer=CHOOSE (Cost=83 Card=4893 Bytes=327831)UPDATE OF CCM_DEBIT_TBLSEQUENCE OF STAFFWARE_CASEID_SFILTERTABLE ACCESS (BY GLOBAL INDEX ROWID) OF CCM_DEBIT_TBL (Cost=83 Card=4893 Bytes=327831)INDEX (RANGE SCAN) OF IDX_DEBIT_DUE_DATE (NON-UNIQUE) (Cost=26 Card=4893)INDEX (RANGE SCAN) OF IDX_PLAN_DEBIT_CISDEBITDEAD (NON-UNIQUE) (Cost=3 Card=1 Bytes=26)INDEX (RANGE SCAN) OF IDX_PLAN_DEBIT_CISDEBITDEAD (NON-UNIQUE) (Cost=3 Card=1 Bytes=28)TABLE ACCESS (BY GLOBAL INDEX ROWID) OF CCM_ACCOUNT_TBL (Cost=1 Card=1 Bytes=9)INDEX (UNIQUE SCAN) OF PK_ACCOUNT (UNIQUE) (Cost=2 Card=1) Confidentiality Notice The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain confidential or privileged information. If you are not the intended recipient, please notify the sender at Wipro or [EMAIL PROTECTED] immediately and destroy all copies of this message and any attachments.
