Re: SQL Help Urgent!!!!!!!!!

Hello sultan You can create a view for the cc table and use with the bb table to display the output . create view cc_view as select id, sum(amount) amt from cc group by id select a.id,a.amount ,b.amount from bb a,cc_view b where a.id=b.id; Hope this solution work's for u. I will also

top 5 segments from each tablespace...

i hv written a pl/sql to print out the 5 largest segments (max number of extents) from each of the tbs present in a DB... can this is done using sql too ? just curious... regards -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rahul INET: [EMAIL PROTECTED]

Re: top 5 segments from each tablespace...

Yes, by either using SQL tricks involving internal sorting of various kinds or by using the new syntax introduced in 8i, where you can have top, bottom and much more. It's part of the DW stuff and the socalled SQL99 syntax, I think. If your purpose of this is to find the segments that are

Re: ways to allow developers to see all packge bodies without EXECUTE ANY PROCEDURE?

I think I would just re-define all_source to include that priv. Just remember to re-run the definition after upgrades, perhaps. Mogens Jack Silvey wrote: Good day listers, We have some developers that would like to see all the package bodies in our data warehouse. This is a reasonable

what is meant by dirty buffers

Hi, In a book I read that DBWO writes dirty data buffers from data buffer cache to the data files. I would appreciate if someone could kindly clear my confusion. What is meant by dirty data buffers? If these are dirty (not good/healthy) why to write to these to data files? TIA! Aleem --

Re: what is meant by dirty buffers

Working with FM Oracle8i Master Index: D press on DI Looking for dirty buffers found link dirty buffer (Concepts) and here is an excerpt: The buffers in the cache are organized in two lists: the write list and the least recently used (LRU) list. The write list holds dirty buffers, which

Re: what is meant by dirty buffers

This buffers are changed by Oracle in memory only (in cache), so they are different from the blocks on disk Therefore, they must be written to disk, after which they are not dirty any more. That's how oracle makes changes to speed up the process - everything is done in memory first, then written

RE: what is meant by dirty buffers

i am not too sure but i think dirty means that the data that is written to the data files mite also be obsolete. This means that the data has already been deleted from the database yet it is written into the file. This is similar to what people call a dirty read in informix cursors where the data

RE: what is meant by dirty buffers

Aleem, A dirty buffer is a data block that has been changed by a user. When a user selects/updates/deletes from a table, the data from that table is pulled in to the buffer cache, where it can then be manipulated.. If a user selects for update, and then updates that data it is then known as

Telephone Alerts

We have some UNIX batch processes that send us e-mails when they have finished. Does anyone have any experience of sending a SMS message to a mobile phone? Simon Fox Room 221 Furlong House Queens Drive NOTTINGHAM NG2 1AL Phone 0115 957 6536 Fax 0115 957 6548

Re: Telephone Alerts

This is a standalone package if you don't want to write your own. http://www.bai.de/sendsms/sendsms.shtml.en otherwise use Google and search for SMS and ETSI: This one looked particularly useful: http://www.dreamfabric.com/sms/ Steve Parker E Mail: [EMAIL PROTECTED]

RE: Good HR vs. Bad HR...

give me a break! It took me 25+ years to get to the point where I have a group who will listen to me. And that's only one small group in the larger group I support, I doubt I'll be that lucky across the board |+--- || | ||

Re: Telephone Alerts

That depends on mobile phone service provider, I think We all have e-mail addresses in form [EMAIL PROTECTED], where xxx is our number. By default they are closed and if we would like to get e-mails then we have to send special sms to special service number to enable this feature. And

Re: Telephone Alerts

Somemobile network providers make use of email gateway for sms. In that case you have some email address which is redirected by your provider to your phone. Alexandre - Original Message - From: FOX, Simon To: Multiple recipients of list ORACLE-L Sent: Monday, May

Re: ways to allow developers to see all packge bodies without EXECUTE ANY PROCEDURE?

Better to just create a new view called DEV_SOURCE? Owned by a TOAD account, not SYS? - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, May 13, 2002 4:58 AM EXECUTE ANY PROCEDURE? I think I would just re-define all_source to include that

Re: Telephone Alerts

Simon, Many cellphone providers provide a service whereby an email recieved at a specific address is sent on to a mobile phone in the form of a page. Here is the routine that I use to scrape my output logs and send a success or failure page to my cellphone: #!/bin/ksh # shell script to run sql

RE: WAITS

That's neat. I working on HTML/GUI interface to statspack. I think I've got most of the thing figured out. Right now, I'm able to display phys read/write IO directly from one of the statspack tables every hour displayed in graphincal format on a web page. Working on other reports as well.

Re: Create user_exit under unix

Start at http://technet.oracle.com and follow the links for Documentation... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Sunday, May 12, 2002 11:28 PM Dear Gurus, I use : - Oracle Server 8.1.6.0 - Oracle Pro*FORTRAN 1.8.52.0.0 - Oracle

RE: Telephone Alerts

Simon, Working with a tools company that provides a monitoring tool for Oracle - we have had a lot of call for sending alerts to SMS. We've done a fair bit of testing of various SMS providers in the UK so that we can try to recommend a valid solution to our customers - that is able to alert a

RE: Telephone Alerts

We used to use telalert by Telamon (looks like they have been taken over now). Seemed to do the job very nicely considering how many times I got woken up in the wee small hours when I was in manufacturing. GRRR http://www.vytek.com/products/urgentmsg/telalert/index.asp HTH Lee

Re: Telephone Alerts

But the problem is: the network interface must be running on server!!! If it isn't all your emails are Buy old mobilphone, connect it through serial cabel to server and use AT commands to send SMS (the AT interface to send/receive SMS is almost standard :-)). JP On Mon 13. May 2002

Re: Double check

Here is a download site for 9i documentation. http://download-west.oracle.com/otndoc/oracle9i/901_doc/nav/docindex.htm Ken Janusz, CPIM - Original Message - To: LazyDBA.com Discussion [EMAIL PROTECTED] Sent: Monday, May 13, 2002 8:45 AM As far as I know it has been a while since

Re: Telephone Alerts

-- FOX, Simon [EMAIL PROTECTED] We have some UNIX batch processes that send us e-mails when they have finished. Does anyone have any experience of sending a SMS message to a mobile phone? See the last issue of The Perl Journal (TPJ) for a good article on using the SMS module for sending

Re: Solaris RC Script Output

Doubtful you'll find output, some is written to the console and in some cases to syslog. Instead, modify your startup script so it writes output to a logfile. Here's mine: #!/sbin/sh # # /etc/init.d/oradb - Start/Stop the Oracle Databases and SQL/NET listeners # PATH=/usr/bin:/usr/sbin ;

Re: what is meant by dirty buffers

Yeah, Oracle always has at least two names for the same thing. Dirty = Modified. Alexandre Gorbatchev wrote: This buffers are changed by Oracle in memory only (in cache), so they aredifferent from the blocks on disk Therefore, they must be written to disk,after which they are not dirty any

Re: what is meant by dirty buffers

completely wrong. that's not a case in oracle. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, May 13, 2002 2:13 PM i am not too sure but i think dirty means that the data that is written to the data files mite also be obsolete. This

Unix Script --- Archive Log Destination Issue

Hello All, I have a script, which cleans up the archive log destination by moving the old archive logs to a retention area based on the thresholds 1. Percent of space utilization 2. greater than 2 days old. The script was working fine , except for the reason , sometimes we see multiple copies

RE: Telephone Alerts

thanks for this. Will investigate. -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]Sent: 13 May 2002 14:18To: Multiple recipients of list ORACLE-LSubject: Re: Telephone AlertsThis is a standalone package if you don't want to write your own.

Accessing tables through synonyms

Good morning, Environment: Oracle 8.1.6 on HP-UX 11.0 We have a warehouse that is being loaded using Informatica and the load times have drastically increased over the last 1 month. The developers would like to run a test by hitting the same production database and monitor the Informatica

RE: WAITS

I'm able to display phys read/write IO directly from... statspack I started with data from StatsPack but instead went directly to the V$ tables to get it real time, or at least every minute. I get physical writes, physical reads, db block and consistent gets, queries executed with soft and hard

RE: Telephone Alerts

Telamon Reminds me of a diddy on the classical music station... To go baroque, Telemann. To go broke, tell a woman. :-) -Original Message- Sent: Monday, May 13, 2002 9:33 AM To: Multiple recipients of list ORACLE-L We used to use telalert by Telamon (looks like they have been taken

RE: WAITS

Check out the statspack viewer tool at http://www.geocities.com/alexdabr/ mkb

Re: Redo log files questions. Need help badly

Hi, Thanks for all the replies. I am still unable to archive my redo logs in order to back them up.When startup the database I then enter archive log list; Itshows archiving information for the coonected instance: Database log modeARCHIVELOG automatic archival DISABLED Archive destination

Re: A trigger vs. default value in a table

Hello Andrey KISS - Why write code to do what oracle does for you? Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, May 13, 2002 6:23 PM Dear list ! I need a very simple thing : each time a certain field is null

RE: Telephone Alerts

Thanks for this detailed reply. I shall take this further. -Original Message- Sent: 13 May 2002 14:53 To: Multiple recipients of list ORACLE-L Simon, Many cellphone providers provide a service whereby an email recieved at a specific address is sent on to a mobile phone in the form of

Re: A trigger vs. default value in a table

a default value. |+- || | || | || andreyb@elronte| || lesoft.com | || | || 05/13/2002 | ||

Re: A trigger vs. default value in a table

Hello Andrey KISS - Why write code to do what oracle does for you? Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, May 13, 2002 6:23 PM Dear list ! I need a very simple thing : each time a certain field is null

RE: Unix Script --- Archive Log Destination Issue

My mistake , this new script is only working sometimes. Which is scheduled every hour , but not working at all the hour changes . -Original Message- Sent: Monday, May 13, 2002 11:28 AM To: Multiple recipients of list ORACLE-L Hello All, I have a script, which cleans up the archive log

Re: Accessing tables through synonyms

Let me guess: the duhvelopers have hardcoded their connect strings, right? You don't say why the synonyms didn't work. Hard coded schema name? If the schema name is hard coded into their code, you will need to put these tables in another database. Jared BALA,PRAKASH (Non-HP-USA,ex1)

RE: Oracle Diagnostic and Tuning Packs

Kimberly, The difference was that OEM gave push buttons only. If the change operation failed, there was no access to the underlying scripts. Quest gives you that access, which can be very important in the case of failures. OEM is strictly a black box, or at least it was when we looked at it.

RE: Telephone Alerts

Simon: There are lots of ways to handle this task. Here's what I do. I have a server (machine and database) that is on my main network. In the database on that server is a special table. If I want to send an e-mail from within Oracle, I simple insert a row into this table and commit it.

Anybody get a 1630 error when trying to add an index ?

I have been trying to add an index to a index tablespace and I keep getting a 1630 error. I have 500 extents , but it says the max extents are only 121 and they are filling up. Maybe my dendrites have been temporarily fried and it is something simple. Thanks for your time in advance. Mike

1 Table and its Indexes occupying 30% space of the Database.

Hi All, I have a table in my database ( size 70GB ) with size of 7GB and it has 13 indexes on it. 5 Indexes are of the size 2 GB and rest are around 500-800Mb. Total size of the table and indexes in total is 21GB. The table size is increasing everyday and every 10-12 days I have to add a

RE: Anybody get a 1630 error when trying to add an index ?

Have you checked the amount of max extents in the tablespace? The error you listed is saying that it is running out of extents in dealing with temporary objects created in the tablespace being used. Bryan -Original Message- Sent: Monday, May 13, 2002 3:33 PM To: Multiple recipients of

Re: 1 Table and its Indexes occupying 30% space of the Database.

By the time you have to worry, you will have retired from the job ;-) [EMAIL PROTECTED] wrote: Hi All, I have a table in my database ( size 70GB ) with size of 7GB and it has 13 indexes on it. 5 Indexes are of the size 2 GB and rest are around 500-800Mb. Total size of the table and

RE: Anybody get a 1630 error when trying to add an index ?

Title: RE: Anybody get a 1630 error when trying to add an index ? Mike, Does you index creation script have a storage clause? It should look something like below. CREATE INDEX FLIGHT_LEGS_NDX1 ON FLIGHT_LEGS(D_ACTUAL_TIME, EVENT_TYPE) TABLESPACE ASIFNDX PCTFREE 10

RE: 1 Table and its Indexes occupying 30% space of the Database.

Deepender - Wracking my brain to recall 7.3. Is partitioning a feature? One thing you might consider is to keep each of the indexes in its own tablespace. This will give you more flexibility. I would also keep track of the size each week to help you forecast growth. Size restrictions will depend

LogMiner - Null Username

Does anyone know why USERNAME in V$LOGMNR_CONTENTS is not always populated? I have encountered numerous instances where a select like 'Select username, sql_redo from v$logmnr_contents' shows a username for some operations and no username for others. I have also noticed that DELETE

Migratory birds

My company is having an OPS configuration, 4xHP 9000/N, HP-UX 11, 64 bit, RDBMS 8.1.7.1, 8 CPUs/node. I noticed that some parallel queries are not executed on the node that they were initiated on, but are migrated to the least busy instance at time. Is anybody else aware of this behavior

RE: Anybody get a 1630 error when trying to add an index ?

Mike, 1630 indicates max # extents (%s) reached in temp segment in tablespace %s. It is probable that the INITIAL/NEXT is set low for your TEMP tablespace and the sorts/temp usage during index creation is bombing out on the upper limit? John Kanagaraj Oracle Applications DBA DBSoft Inc (W):

Create synonyms in externally authenticated user's schema

Title: Create synonyms in externally authenticated user's schema Hi all, When I create a new nt OS authenticated user I need to create several synonyms in that users's schema? How can I create synonyms as that user easily? I know that I can get them to give me their nt password and log

Re: Anybody get a 1630 error when trying to add an index ?

Check for both tablespaces maxextents 1) target tablespace and 2) temp tablespace. It appears that block_size of your database is 2K thus default maxextents are 121. You can alter both tablespaces like alter tablespace tablespace_name default storage(maxextents unlimited); Regards Rafiq

RE: 1 Table and its Indexes occupying 30% space of the Database.

What about max # of datafiles? If you're added them every 2 weeks what about making them larger? After all, not many systems are restricted by the 2Gb file size limit any more. Michael Hand Polaroid Corp -Original Message- Sent: Monday, May 13, 2002 3:38 PM To: Multiple recipients

RE: 1 Table and its Indexes occupying 30% space of the Database.

With the size of your table you might want to consider changing it into a fake partitioned table (assuming there is a key that it would make sense to partition on) for better performance. While 7.3 doesn't support real partitioning, you can create seperate tables (each in their own tablespace)

Re: Create synonyms in externally authenticated user's schema

log on using an account with CREATE ANY SYNONYM privs and create the synonym as: create ntusername.synonym for |+-- || | || | || Beth.Seefelt@tet| ||

RE: WAITS

Thanks Rajesh. Looks to be a very interesting tool. mkb --- [EMAIL PROTECTED] wrote: Check out the statspack viewer tool at http://www.geocities.com/alexdabr/

function based indexes

Hi, We are using function based index and optimizer is using indexes properly. parameter query_rewrite_integrity is set as ENFORCED docs says that we need to change the valus to TRUSTED. I just want to know the comments of DBA's using function based indexes whether this parameter should be

RE: Create synonyms in externally authenticated user's schema

Title: Create synonyms in externally authenticated user's schema If you have the proper privilege, create synonym synonym_owner.synonym_name for table_owner.table_name; Be warned, Oracle's OS authentication is extremely easy to spoof. Ian MacGregor Stanford Linear Accelerator Center

RE: Oracle Diagnostic and Tuning Packs

Jared, All the events in OEM 2.2 and 9 use tcl scripts. Also u can use OS script and create ur own events scripts. for eg, i have an oem job which does a logical export for my unix and windows oracle databases and then fire a tcl script to change the export filename to add the timestamp.

Re: A trigger vs. default value in a table

Hello Andrey The correct answer depends on what you want ! if you can live with null values in the column then a default for the column is OK, but if you want to handle a insert with a null then you need a trigger. Se the little example : SQL create table t (a number, b number default 10);

OCI and Oracle 9i - Install Question

Hi, One of my developers is trying to use OCI on our recently installed Oracle 9i instance. The size of the file libocijdbc9.sl is 142 kb in the 9i version and libocijdbc8.sl was 9 mb in the 8i version. How can I check to see that this was installed properly? Bill Carle ATT Database

RE: Rman ... what do YOU need

Well, we are GO for the RMAN book. Contract should be signed soon. So, hopefully, next October (for OOW) you will be seeing an Rman Backup and Recovery Handbook. Now I have a couple of people to contact... If you are one of those, please be patient, I'll be gettn' to you shortly. :-) RF

RE: Oracle Diagnostic and Tuning Packs

Mandar - Hmmm. . . let me see. I pay big bucks for the OEM Tuning Pack, and woo hoo it will create reports like STATSPACK, which is free? Excuse me while I run down the hall and beg my manager to buy this. I'm just kidding, I'm sure there is more to it than that, it's just the way you

RE: Oracle Diagnostic and Tuning Packs

All the events in OEM 2.2 and 9 use tcl scripts. Also u can use OS script and create ur own events scripts. Mandar, That may be so, but that doesn't give you access to the scripts that OEM generates to accomplish the change. As for writing my own TCL ( ugh ), if I have to write it myself

RE: Migratory birds

Look at the doc: Oracle8i Parallel Server Administration, Deployment, and PerformanceChapter 2. Waleed -Original Message-From: Gogala, Mladen [mailto:[EMAIL PROTECTED]]Sent: Monday, May 13, 2002 4:24 PMTo: Multiple recipients of list ORACLE-LSubject: Migratory birds My

RE: Rman ... what do YOU need

Goodluck!! Let me know if you need someone to draw some pretty pictures...or perhaps number the pages... -Original Message- Sent: Monday, May 13, 2002 5:29 PM To: Multiple recipients of list ORACLE-L Well, we are GO for the RMAN book. Contract should be signed soon. So, hopefully,

RE: Oracle Diagnostic and Tuning Packs

Jared, I can start OEM Change Manager 9.0 and edit a table rename script, add my own custom code and let oem run that as a job, or copy the sql and run it from sql*plus. Mandar -Original Message- Sent: Monday, May 13, 2002 1:44 PM To: [EMAIL PROTECTED] Cc: Mandar A. Ghosalkar All

RE: Oracle Diagnostic and Tuning Packs

The best from the oem is the user defined events where in u can write ur own scripts like shell scripts or even perl scripts and pass the value to ur oem ,This one thing has helped me a lot on writing shell scripts and executing it monitor. -Original Message- Sent: Monday, May 13, 2002

RE: function based indexes

Harvinder - I had to make these settings in order to use function-based indexes maybe a year ago in Oracle 8.1.6, and thus far have experienced no ill effects. For specifics of what they do, I would read the Oracle documentation and do a search on http://www.google.com. Try it on your test

Re: Redo log files questions. Solved

Hi All, Finally, I have successfully had the automatic archiving turned on. My problem was I modified the wrong parameter file. Thus that caused the automatic archivalDISABLED. Regards, Trang Meomeo Nguyen [EMAIL PROTECTED] wrote: Hi, Thanks for all the replies. I am still unable to archive

RE: Anybody get a 1630 error when trying to add an index ?

Everybody ... thanks for your help on this. What happened is that I had increased the extents from 121 to 500, but because there was an existing session that had the object I had to wait for that session to end before the 500 extents were noticed. So, I did everything right, I just had to

RE: Redo log files questions. Solved

Been there, done that. Hope it doesn't happen again. -Original Message- Sent: Monday, May 13, 2002 5:43 PM To: Multiple recipients of list ORACLE-L Hi All, Finally, I have successfully had the automatic archiving turned on. My problem was I modified the wrong parameter file.

RE: Telephone Alerts

Optus here will provide you with software for free and a $150 connection charge that gives you an SMS gateway of your own you needed a modem and line for it if I remember right. It then just costs for each message delivered. You can then send email to it or I think some of the monitoring

RE: Oracle Diagnostic and Tuning Packs

You do have access to the scripts. Its not in a nice format and they do warn you of that. The version I evaluated was 9i. -Original Message- Sent: Monday, May 13, 2002 10:26 AM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Kimberly, The difference was that OEM gave push buttons only.

RE: Oracle Diagnostic and Tuning Packs

Mandar, The change management I had in mind could be rather complex. Quest Change manager generates both the scripts to make changes that will put a schema in sync with another, and the scripts to back those changes out. Unless OEM is doing something drastically different than it did 2 years

RE: Rman ... what do YOU need

You are just a glutton for punishment, aren't you. ;) Jared Freeman, Robert [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 05/13/2002 02:28 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Rman

RE: Oracle version usage

Maybe the question was not correctly formulated. Anybody has some knowledge or maybe gess about percentages of 7.3, 8.0, 8.1 and 9 in production and prognosys for a year. Maybe some studies were published? Alex Hillman -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL

RE: Oracle Diagnostic and Tuning Packs

No, as I said just a little while ago, that you would have not read yet:-), you do have access to the scripts they will use to apply the change. And its SQL, just horribly formatted. -Original Message- [EMAIL PROTECTED] Sent: Monday, May 13, 2002 2:54 PM To: Multiple recipients of list

RE: Oracle Diagnostic and Tuning Packs

OEM Change Manager does that too and you do have access to the scripts. We have to stop this thread cause I really hate the thought that I am defending OEM:-) -Original Message- [EMAIL PROTECTED] Sent: Monday, May 13, 2002 5:53 PM To: Multiple recipients of list ORACLE-L Mandar, The

RE: Oracle Diagnostic and Tuning Packs

Jared, I have OEM 2.2 (as comes with Oracle 817) and Change Management pack - I hadn't used it before but this thread got me trying it. OEM 2.2 lets you edit and save the synch script. The script is an OraTCL script but you can clearly see the SQL commands - it also shows you a summary of just