RE: How to Identify System Tables?
if nothing else works pick up all the tables based on the CREATED value of dba_objects > -- > From: [EMAIL PROTECTED][SMTP:[EMAIL PROTECTED]] > Sent: Friday, November 02, 2001 2:13 PM > To: LazyDBA.com Discussion > Subject: How to Identify System Tables? > > Hi Guys, > By mistake, I ran my production script in SYSTEM schema. So, I need > to > identify SYSTEM objects and remove all objects created by the script. Can > anyone help me out to find an easy way to identify system tables and > non-system tables in SYSTEM schema? > > Thanks in advance, > Anthony > > > > > Oracle documentation is here: > http://tahiti.oracle.com/pls/tahiti/tahiti.homepage > To unsubscribe: send a blank email to [EMAIL PROTECTED] > To subscribe: send a blank email to [EMAIL PROTECTED] > Visit the list archive: http://www.LAZYDBA.com/odbareadmail.pl > Tell yer mates about http://www.farAwayJobs.com > By using this list you agree to these > terms:http://www.lazydba.com/legal.html > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rahul INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Constraint Enable/Disable
with import you can give
CONSTRAINTS=N
Make a FREE long distance call from your PC!
http://www.eboom.com/free/
- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Thursday, November 01, 2001 08:50 PM
> I am trying to import data into a database, but it keeps skipping rows
> because of constraints. So I want to disable the constraints, import the
> data and then enable the constraints again.
>
> Can I do this? or is there another way to go about it?
>
> Thanks
>
> -Original Message-
> Sent: Friday, 2 November 2001 3:30 PM
> To: Multiple recipients of list ORACLE-L
>
>
> Namaskar !!
>
> alter table table_name disable constraint cons_name;
> you could generate sql's to generate these ..
>
> select 'alter table '||table_name||' disable constraint
> '||constraint_name||';'
> from all_constraints
> where constraint_type in ('C','R')
> /
>
> later you could replace the DISABLE with ENABLE and ..
>
> regards
>
>
>
> > --
> > From: Sujatha Madan[SMTP:[EMAIL PROTECTED] ]
> > Reply To: [EMAIL PROTECTED]
> > Sent: Friday, November 02, 2001 11:15 AM
> > To: Multiple recipients of list ORACLE-L
> > Subject: Constraint Enable/Disable
> >
> > Hi,
> > Is there a way to disable all constraints, and then enable them all
again?
> > Thanx
> > Sujatha
> >
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Rahul
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing Lists
>
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Sujatha Madan
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing Lists
>
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Nikunj Gupta
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Oracle on NT startup question
Title: RE: Oracle on NT startup question In UNIX there is not service process, but Win NT/2K requires it, something I discovered when I started to working on WIN env. Confusing if you do come from a UNIX env. Look at it this way the Service + the Database( Datafiles, redologs, controlfiles etc) are the instance. The Service is a process which is started "in preparation" for the mounting of the instance, in otherwords setting up the memory and so on. If you start up the service and not the instance(Database) you will get an error from the listener(also a service) stating that Oracle is not available. You will then be required to start the instance(Database) from svrmgrl/Sqlplus. But as you have already discovered, the service can be setup to automatically start the instance(Database) once the service has started, making it almost UNIX like in that sense. Come to think of it, if you open the services facility in the ControlPanel, right click on the oracle service, properties, you can change the startup mode from automatic to manual. Which will then not start the service or the instance at startup of the server. It will require you to start it manually very time. HTH Denham -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Thursday, November 01, 2001 4:20 PM To: Multiple recipients of list ORACLE-L Subject: RE: Oracle on NT startup question I'm sorry. I thought that you mounted a database and that the instance was the area in memory (corresponding to the SGA and PGA) that Oracle used. So what is the instance? Denham Eva @TFMC.co.za> <[EMAIL PROTECTED]> Sent by: root cc: Subject: RE: Oracle on NT startup question 11/01/2001 12:25 AM Please respond to ORACLE-L I am not sure I understand you correctly, but on NT Oracle runs as a service and an instance(the mounted database). The service must be started for the instance to be able to mount and connected to. However the service can be started without having to mount the instance. HTH Denham -Original Message- Sent: Wednesday, October 31, 2001 3:50 PM To: Multiple recipients of list ORACLE-L My understanding is that the instance is the service. Can you start the instance without a mounted database? Andrey Bronfin To: Multiple recipients of list ORACLE-L @elrontelesof cc: t.com> Subject: Oracle on NT startup question Sent by: root 10/31/2001 04:35 AM Please respond to ORACLE-L Dear list ! If my NT server (which runs an Oracle instance) gets rebooted accidentally, how can i bring up the Oracle services without starting up the instance ? I want to startup the instance with a script later . I'm interested in an answer for both Oracle 8.0.5 and 8.1.7 on NT4. Thanks in advance. DBAndrey * 03-9254520 * 053-464562 * mailto:[EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andrey Bronfin INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Pub
recreate controlfile
Hi lists, Can anybody tell explain me this: We would re-create a controlfile to make a new copy of the database with another name. I tried to create a new db from existing database coldbackup . But I tried to recrete with only one datafile i.e. system datafile. I skipped all the remaining datafiles. I tried it twice. It is saying recover the datafile using backup controlfile. when I tried to recover it, it returning that the file is not restored from a sufficient backup. (But the datafile backup is taken from cold backup) is there a way to just re-create a controlfile (using a single datafile system. if not, at least how many minimum of datafiles i need to recreate a controlfile from the backup set.) Regards, Srinivas -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tatireddy, Shrinivas (MED, Keane) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: create trigger after insert
THANKS,
I
resolved the issue.
If
I use a script like the one below to test the trigger "for each row"
and "statement insert" it is
taken as multiple statements.
It
works fine now
Regards
Suhen
List,
I am creating a trigger that should
fire when records are inserted into a table TEST and should send an email via
utl_smtp. The mailing is working fine.
I am trying to get the trigger to
fire after a group insert statement rather than each time a record is inserted.
I have not included the "for
each row" syntax but it still fires after each row is inserted.
Will the entry below be regarded as
one insert statement if I put into a sql script and execute via sqlplus.
insert into test values ('Suhen');
insert into test values ('Suhen');
insert into test values ('Suhen');
insert into test values ('Suhen');
My create trigger syntax used:
create or replace trigger
test_trigger after insert on test
begin
send_mail ('Suhen','[EMAIL PROTECTED]','Insert','Records
inserted into test');
end;
/
I am busy looking through the docs
for more info.
Any help would be greatly
appreciated.
Regards
Suhen
Re: Constraint Enable/Disable
Hi,
Hope these script will help you..
Disable
Constraints..
select 'alter table &tab disable constraint
'||constraint_name||' cascade;' from dba_constraints where
owner = upper('&owner') and table_name = upper('&tab')
/
Enable
Constraints
select 'alter table &tab enable constraint
'||constraint_name||';' from dba_constraints where owner =
upper('&owner') and table_name = upper('&tab') /
Try these in your test environment.. and have
fun
Nikunj
Make a FREE long distance call from
your PC!http://www.eboom.com/free/
- Original Message -
From:
Sujatha Madan
To: Multiple recipients of list ORACLE-L
Sent: Thursday, November 01, 2001 08:15
PM
Subject: Constraint Enable/Disable
Hi,
Is there a way to disable all constraints, and then enable them all
again?
Thanx
Sujatha
RE: Constraint Enable/Disable
I am trying to import data into a database, but it keeps skipping rows
because of constraints. So I want to disable the constraints, import the
data and then enable the constraints again.
Can I do this? or is there another way to go about it?
Thanks
-Original Message-
Sent: Friday, 2 November 2001 3:30 PM
To: Multiple recipients of list ORACLE-L
Namaskar !!
alter table table_name disable constraint cons_name;
you could generate sql's to generate these ..
select 'alter table '||table_name||' disable constraint
'||constraint_name||';'
from all_constraints
where constraint_type in ('C','R')
/
later you could replace the DISABLE with ENABLE and ..
regards
> --
> From: Sujatha Madan[SMTP:[EMAIL PROTECTED] ]
> Reply To: [EMAIL PROTECTED]
> Sent: Friday, November 02, 2001 11:15 AM
> To: Multiple recipients of list ORACLE-L
> Subject: Constraint Enable/Disable
>
> Hi,
> Is there a way to disable all constraints, and then enable them all again?
> Thanx
> Sujatha
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Rahul
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Sujatha Madan
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Constraint Enable/Disable
Thanks!
-Original Message-
Sent: Friday, 2 November 2001 3:22 PM
To: '[EMAIL PROTECTED]'
Cc: '[EMAIL PROTECTED]'
Namaskar !!
alter table table_name disable constraint cons_name;
you could generate sql's to generate these ..
select 'alter table '||table_name||' disable constraint
'||constraint_name||';'
from all_constraints
where constraint_type in ('C','R')
/
later you could replace the DISABLE with ENABLE and ..
regards
> --
> From: Sujatha Madan[SMTP:[EMAIL PROTECTED] ]
> Reply To: [EMAIL PROTECTED]
> Sent: Friday, November 02, 2001 11:15 AM
> To: Multiple recipients of list ORACLE-L
> Subject: Constraint Enable/Disable
>
> Hi,
> Is there a way to disable all constraints, and then enable them all again?
> Thanx
> Sujatha
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Sujatha Madan
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Constraint Enable/Disable
Namaskar !!
alter table table_name disable constraint cons_name;
you could generate sql's to generate these ..
select 'alter table '||table_name||' disable constraint
'||constraint_name||';'
from all_constraints
where constraint_type in ('C','R')
/
later you could replace the DISABLE with ENABLE and ..
regards
> --
> From: Sujatha Madan[SMTP:[EMAIL PROTECTED] ]
> Reply To: [EMAIL PROTECTED]
> Sent: Friday, November 02, 2001 11:15 AM
> To: Multiple recipients of list ORACLE-L
> Subject: Constraint Enable/Disable
>
> Hi,
> Is there a way to disable all constraints, and then enable them all again?
> Thanx
> Sujatha
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Rahul
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Re: ORA-04031
Are you using MTS ? If yes, try and increase LARGE_POOL_SIZE Make a FREE long distance call from your PC! http://www.eboom.com/free/ - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Thursday, November 01, 2001 03:25 PM > Hi, > > we are running batch job executing following statement in a loop about 1 > million iterations: > select sysdate from dual... > we are getting error ora-04031 unable to allocate 2400 bytes in shared > pool.. > and when we check the sys tables it is showing same number of pare call as > executions. > why oracle is trying to parse this statement every time.. > this is the only job running and user connected at this time... > > Thanks > -Harvinder > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Harvinder Singh > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nikunj Gupta INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Constraint Enable/Disable
Hi, Is there a way to disable all constraints, and then enable them all again? Thanx Sujatha
create trigger after insert
List,
I am creating a trigger that should fire when records are
inserted into a table TEST and should send an email via
utl_smtp.
The mailing is working fine.
I am trying to get the trigger to fire after a group insert statement rather than each time a record is
inserted.
I have not included the "for each row" syntax
but it still fires after each row is inserted.
Will the entry below be regarded as one insert statement if
I put into a sql script and execute via sqlplus.
insert into test values ('Suhen');
insert into test values ('Suhen');
insert into test values ('Suhen');
insert into test values ('Suhen');
My create trigger syntax used:
create or replace trigger test_trigger
after insert on test
begin
send_mail
('Suhen','[EMAIL PROTECTED]','Insert','Records inserted into
test');
end;
/
I am busy looking through the docs for more info.
Any help would be greatly appreciated.
Regards
Suhen
RE: Arch configuration -- I/O stuck
Hi Pablo, I've seen an 'ls' hang for more than a minute under 10.20 when there were a lot of delayed writes pending on an unrelated file-system. A colleague of mine (Chris Bunting) did some testing to reproduce the problem and concluded that all filesystems of the same type (JFS or HFS) were affected. HP made some kernel changes for 11.0 that have reduced the severity of the problem, but it can still occur. If your case the archive writes are not delayed writes because Oracle explicitly opens the files in synchronous mode, so you should not see a delay any longer than that corresponding to the time that it would take your Symmetrix box to destage the cache allocations for the target LUNs, unless there happens to be simultaneous heavy delayed write activity elsewhere on the system. The failure of the multiblock_read_test.sql script probably indicates that the "large" table that you scanned already had a large number of block in the cache. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ - For DBAs @ http://www.christianity.net.au/ - For all -Original Message- Sent: Friday, 2 November 2001 6:39 To: Steve Adams; Multiple recipients of list ORACLE-L Steve, thanks for the help and for the url and the advice of stripping. I don't understand what I'm pasting here , I'm executing a 'ls' in a FS that's in a different disk in differents LUNs (on the same Symmetrix), why is it still stucking. Shouldn't it be placed in a different queue?? "The 'ls' is probably getting stuck because the I/O is very slow and file system metadata writes are stuck in the I/O queue while locks are held on the file system metadata pending the completion of those writes." One more question, besides what you just advised me, I've been trying to reduce ARCH bandwidth (as I read in a TIP at your site), to spread ARCH work along more time and reduce the impact in foreground processes. So I've set log_archive_buffers from 4 to 2 and today I've tried to set log_archive_buffer_size to the MAX_IO_SIZE of the OS. But I found a problem with this. I tried to check what was the MAX_IO_SIZE, so I used 10046 event and check at scattered reads in a big FTS (as you do in your scripts) and I always got p3=5. I checked this into 2 differents databases running on the same box. Both reported p3=5 (5 blocks I think), but the surprise is that one of them has got db_block_size=4K and the other db_block_size=8K. How can it be possible? according to this test MAX_IO_SIZE could be 20K or 40K. what's wrong here? And something worst, MAX_IO_SIZE can't be so small, right? I thought it was 1MB or 512K in HP-UX 11.0 thannks for your time. TIA --- Steve Adams <[EMAIL PROTECTED]> escribió: > Hi Pablo, > > The 'ls' is probably getting stuck because the I/O > is very slow and file system metadata writes are > stuck in the I/O > queue while locks are held on the file system > metadata pending the completion of those writes. > > The problem could be that you are saturating the > cache allocations for the EMC LUNs containing your > archive destination > file system. See the answer at > http://www.ixora.com.au/q+a/0010/20102738.htm for a > bit about the EMC cache allocation > policy. To solve the problem you can use LVM to > stripe a large number of small LUNs together so as > to increase the total > amount of cache available for the archival writes. > You would also do well to avoid RAID-S of course! > > @ Regards, > @ Steve Adams > @ http://www.ixora.com.au/ - For > DBAs > @ http://www.christianity.net.au/ - For all > > > > -Original Message- > From: Pablo ksksksk [mailto:[EMAIL PROTECTED]] > Sent: Thursday, 1 November 2001 5:45 > To: Multiple recipients of list ORACLE-L > Subject: Arch configuration -- I/O stuck > > > Hi list, > > Oracle 7.3.4 > HP-UX > log_archive_buffer_size=32 (redo log blocks = 1K) > log_archive_buffers=4 > Filesystem based (no direct I/O) > > I've been detecting that my box gets stucked > eventually for some time. > When this happens I can't do even a "ls" (it > actually executes it but it takes a long time). > If I check my cpu with TOP, I see 47% idle time > and > there's no process monopolizing the CPU. > But when I check disk activity with sar -d I see > that one disk is 100% busy and it's avwait+avserv > > 1000 ms. The other disks are fine. > I then check disk activity with Glance and I can > identify the process that's writting/reading on this > disk is: ARCH (ARCH is writting a 1.9 GB redo log.) > > So here are my doubts: > 1)If only one disk is saturated (I've got > about > 30 disks in this box (a SYMMETRIX array) with some > controllers), why does the whole box get stucked? > why > are even other applications connected to other > instances running on this box affected? (may be > because the HP-UX LVM system gets saturated???) > > 2) What can I do to avoid this problem?, > (reduce > log_archive_buffers par
RE: Startup error
What's your SEMMSL, SEMMNI value set to? >>> [EMAIL PROTECTED] 11/01/01 07:25PM >>> I think you probably need to revisit the kernel paramters such as SEM*. -Original Message- Sent: Thursday, November 01, 2001 2:35 PM To: Multiple recipients of list ORACLE-L Hi, I created a database (8.0.6 - HP-UX 11) and then I changed the processes parameter to 100 (initial value was 50). I then tried to start the database and I get the following error: SVRMGR> startup ORA-07279: spcre: semget error, unable to get first semaphore set. HP-UX Error: 28: No space left on device Additional information: 1 SVRMGR> Could someones please tell me what this means and how I should fix it. Thanks Sujatha -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sujatha Madan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Wong, Bing INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Richard Ji INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: gnarly delete statement?
t2.timestamp > t1.timestamp? he wants to delete the LATEST insert timestamp, right Paul? --- Regina Harter <[EMAIL PROTECTED]> wrote: > Try this: > > Delete from my_table t1 > where a = 2 and exists > (select 'X' from my_table t2 > where t2.a = t1.a > andt2.b = t1.b > andt2.c = t1.b > andt2.d = t1.d > andt2.timestamp < t1.timestamp) > > At 12:55 PM 11/1/01 -0800, you wrote: > >Given a business rule that says a combination of three columns must > be > >unique if and only if a fourth column equals a certain value, and if > >the table in question contains rows that violate this requirement, > I'm > >trying to write a single SQL statement that will remove the > duplicates. > > Of each set of duplicate rows, I'll delete the one with the latest > >insert timestamp. > > > >Let's call the columns > > > >a b c d timestamp > > > >The combination of b, c, and d must be unique if a = 2. > > > >I can get the duplicate row values along with the latest timestamp > via > > > >select max(tstamp), a, b, c from > >my_table group by a, b, c having count(*) > 1 > > > >But I can't figure out how to use that in a delete statement. > > > >Suggestions greatly appreciated! > > > > > >Paul Baumgartel > > > > > >__ > >Do You Yahoo!? > >Make a great connection at Yahoo! Personals. > >http://personals.yahoo.com > >-- > >Please see the official ORACLE-L FAQ: http://www.orafaq.com > >-- > >Author: Paul Baumgartel > > INET: [EMAIL PROTECTED] > > > >Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > >San Diego, California-- Public Internet access / Mailing > Lists > > > >To REMOVE yourself from this mailing list, send an E-Mail message > >to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > >the message BODY, include a line containing: UNSUB ORACLE-L > >(or the name of mailing list you want to be removed from). You may > >also send the HELP command for other information (like subscribing). > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Regina Harter > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing > Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Find a job, post your resume. http://careers.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Startup error
for solaris the soln is to bum up semmns parameter. --- Sujatha Madan <[EMAIL PROTECTED]> wrote: > Hi, > > I created a database (8.0.6 - HP-UX 11) and then I > changed the processes > parameter to 100 (initial value was 50). I then > tried to start the database > and I get the following error: > > SVRMGR> startup > ORA-07279: spcre: semget error, unable to get first > semaphore set. > HP-UX Error: 28: No space left on device > Additional information: 1 > SVRMGR> > > Could someones please tell me what this means and > how I should fix it. > > Thanks > > Sujatha > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: Sujatha Madan > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: > (858) 538-5051 > San Diego, California-- Public Internet > access / Mailing Lists > > To REMOVE yourself from this mailing list, send an > E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of > 'ListGuru') and in > the message BODY, include a line containing: UNSUB > ORACLE-L > (or the name of mailing list you want to be removed > from). You may > also send the HELP command for other information > (like subscribing). __ Do You Yahoo!? Find a job, post your resume. http://careers.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deepak Thapliyal INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: TTI Layer Function codes
Hi Raj, I would imagine that the details of the implementation of the two-task common protocol and its interface functions are not the sort of documentation that Oracle is going to make available to customers! Other than the obvious step of contacting Support, check the operating system hardware diagnostic logs for evidence of either memory or network errors, and make sure that you don't have someone trying to use some old client software that is not compatible with the RDBMS version. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ - For DBAs @ http://www.christianity.net.au/ - For all -Original Message- Sent: Friday, 2 November 2001 6:46 To: Multiple recipients of list ORACLE-L Hi all, I am investigating some ORA-600 [12333] errors. According to note 35928.1 the second argument represents TTI Layer Function Code, followed by FUNCTION Code and SEQUENCE. I tried to look up TTI Layer Function codes but couldn't find any reference, does anyone know where I can find them? Thanks Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art ! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Adams INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Startup error
I think you probably need to revisit the kernel paramters such as SEM*. -Original Message- Sent: Thursday, November 01, 2001 2:35 PM To: Multiple recipients of list ORACLE-L Hi, I created a database (8.0.6 - HP-UX 11) and then I changed the processes parameter to 100 (initial value was 50). I then tried to start the database and I get the following error: SVRMGR> startup ORA-07279: spcre: semget error, unable to get first semaphore set. HP-UX Error: 28: No space left on device Additional information: 1 SVRMGR> Could someones please tell me what this means and how I should fix it. Thanks Sujatha -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sujatha Madan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Wong, Bing INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Startup error
Title: RE: Startup error Looks like you need to adjust the kernel parameters for semaphores... Refer to the Oracle install guide for the recommended minimums for each parameter... You can find them at... http://docs.oracle.com/database_mp_8.html And specifically... http://docs.oracle.com/a81689/SHIPHOME/DOC/product_0/a73325.pdf HTH Tim -Original Message- From: Sujatha Madan [mailto:[EMAIL PROTECTED]] Sent: Thursday, November 01, 2001 5:35 PM To: Multiple recipients of list ORACLE-L Subject: Startup error Hi, I created a database (8.0.6 - HP-UX 11) and then I changed the processes parameter to 100 (initial value was 50). I then tried to start the database and I get the following error: SVRMGR> startup ORA-07279: spcre: semget error, unable to get first semaphore set. HP-UX Error: 28: No space left on device Additional information: 1 SVRMGR> Could someones please tell me what this means and how I should fix it. Thanks Sujatha -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sujatha Madan INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Startup error
Yes ... thanks I solved it and it works fine now -Original Message- Sent: Friday, 2 November 2001 11:25 AM To: Multiple recipients of list ORACLE-L I think you probably need to revisit the kernel paramters such as SEM*. -Original Message- Sent: Thursday, November 01, 2001 2:35 PM To: Multiple recipients of list ORACLE-L Hi, I created a database (8.0.6 - HP-UX 11) and then I changed the processes parameter to 100 (initial value was 50). I then tried to start the database and I get the following error: SVRMGR> startup ORA-07279: spcre: semget error, unable to get first semaphore set. HP-UX Error: 28: No space left on device Additional information: 1 SVRMGR> Could someones please tell me what this means and how I should fix it. Thanks Sujatha -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sujatha Madan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Wong, Bing INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sujatha Madan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Any DBA or Oracle Jobs in MA - Boston
I have friends looking. Thanks, Waleed -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Toad vs SQL Navigator
Title: RE: Toad vs SQL Navigator Ah... You are correct that Golden and PLEDIT are not tools that compare to TOAD or SQLNAV but that does not mean that there are not excellent tools... For years I was a die hard SQL*Plus fan... A SQL*Plus bigot you might say... I avoiding tools like TOAD and stuck to the command line... A couple years ago, I used PLEDIT for some light PL/SQL work and it did a great job... Sure, TOAD or SQLNAV are much better ( debuggers etc ) but they are overkill for a hacker like myself... But, on my latest contract I did quite a bit more PL/SQL than usual... And, I decided that I REALLY liked PLEDIT... So, I gave Golden a try... And, after using Golden for a few weeks, I can not think of life without it... I am still a command line guy and Golden is like a improved SQL*Plus... I *love* the workspace feature... The run sheet options ( whole sheet, here to end of sheet, current line ) that operate with function keys... The favorites pulldown... Support for DBMS_OUTPUT... And the support for SQL*Plus commands ( like spool )... Golden is a query tool... Not a "all in one" tool like TOAD... But, IMHO, it is a very good query tool for those SQL*Plus bigots like myself... :-) Tim PS - Plus, since I had to buy it myself, I loved the price... :-) -Original Message- From: Jesse, Rich [mailto:[EMAIL PROTECTED]] Sent: Thursday, November 01, 2001 5:35 PM To: Multiple recipients of list ORACLE-L Subject: RE: Toad vs SQL Navigator I just downloaded and testdrove Golden. Egad. Not even close to any of the Quest tools. There's so much it's lacking that I don't have the time to make a list. If you're gonna use that, just download the freeware version of TOAD from http://www.toadsoft.com So much better with so many more features and usability, IMHO. Of course, I'm using a fully registered version (as well as a beta). :) My $.02 Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- Sent: Thursday, November 01, 2001 15:35 To: Multiple recipients of list ORACLE-L My answer is always the same... Neither! :-) Instead, try Golden from Benthic Software (www.benthicsoftware.com). It's easy to try it without buying it, just use the annoyware. Also try PLEDIT as a PL/SQL development environment. It serves our purposes very well, and at 35$ it completely demolishes it's competition for cost/benefit. Paul -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: log growing
Dennis Listener.ora file is ok.I have not enable TRACE_LEVEL.I am getting the following message in sqlnet.log file I have application server 4 but this error is coming on only one server. What could be problem. Thanks -Seema >From: DENNIS WILLIAMS <[EMAIL PROTECTED]> >Reply-To: [EMAIL PROTECTED] >To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> >Subject: RE: log growing >Date: Thu, 01 Nov 2001 11:40:36 -0800 > >Seema - Check your listener.ora file and look at the TRACE_LEVEL parameter. >To effect a change, start/stop the listener. >Dennis Williams >DBA >Lifetouch, Inc. >[EMAIL PROTECTED] > > >-Original Message- >Sent: Thursday, November 01, 2001 1:10 PM >To: Multiple recipients of list ORACLE-L > > >Hi >I checked sqlnet.log file is growing.I want to set parameter to not grow >sqlnet.ora at application end.Let me know what parameter I need to set in >which file. >Thanks >-Seema > >_ >Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp > >-- >Please see the official ORACLE-L FAQ: http://www.orafaq.com >-- >Author: Seema Singh > INET: [EMAIL PROTECTED] > >Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 >San Diego, California-- Public Internet access / Mailing Lists > >To REMOVE yourself from this mailing list, send an E-Mail message >to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in >the message BODY, include a line containing: UNSUB ORACLE-L >(or the name of mailing list you want to be removed from). You may >also send the HELP command for other information (like subscribing). >-- >Please see the official ORACLE-L FAQ: http://www.orafaq.com >-- >Author: DENNIS WILLIAMS > INET: [EMAIL PROTECTED] > >Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 >San Diego, California-- Public Internet access / Mailing Lists > >To REMOVE yourself from this mailing list, send an E-Mail message >to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in >the message BODY, include a line containing: UNSUB ORACLE-L >(or the name of mailing list you want to be removed from). You may >also send the HELP command for other information (like subscribing). _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seema Singh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
ORA-04031
Hi, we are running batch job executing following statement in a loop about 1 million iterations: select sysdate from dual... we are getting error ora-04031 unable to allocate 2400 bytes in shared pool.. and when we check the sys tables it is showing same number of pare call as executions. why oracle is trying to parse this statement every time.. this is the only job running and user connected at this time... Thanks -Harvinder -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Harvinder Singh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Startup error
Hi, I created a database (8.0.6 - HP-UX 11) and then I changed the processes parameter to 100 (initial value was 50). I then tried to start the database and I get the following error: SVRMGR> startup ORA-07279: spcre: semget error, unable to get first semaphore set. HP-UX Error: 28: No space left on device Additional information: 1 SVRMGR> Could someones please tell me what this means and how I should fix it. Thanks Sujatha -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sujatha Madan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
MVs on 8.0.5
I have a project that the lead developer wants to use materialized views with. The database is 8.0.5 on some flavor on Unix (client site). I have no experience with MVs and what I have heard with 8.0.5 isn't good. The data in question will be 2+ million records. I am leaning to building tables and writing PL/SQL for the ETL. What are the pros/cons of using MVs with 8.0.5? Any IMHOs about this? Thanks, Todd Carlson Oracle Database Administrator Tripos, Inc. (314) 647-8837 ext. 3246 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Todd Carlson INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Toad vs SQL Navigator
I just downloaded and testdrove Golden. Egad. Not even close to any of the Quest tools. There's so much it's lacking that I don't have the time to make a list. If you're gonna use that, just download the freeware version of TOAD from http://www.toadsoft.com So much better with so many more features and usability, IMHO. Of course, I'm using a fully registered version (as well as a beta). :) My $.02 Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- Sent: Thursday, November 01, 2001 15:35 To: Multiple recipients of list ORACLE-L My answer is always the same... Neither! :-) Instead, try Golden from Benthic Software (www.benthicsoftware.com). It's easy to try it without buying it, just use the annoyware. Also try PLEDIT as a PL/SQL development environment. It serves our purposes very well, and at 35$ it completely demolishes it's competition for cost/benefit. Paul -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Synonyms can be VERY bad for performance
Cherie,
My comment on the consistent gets comparison came from using autotrace.
More specifically, from the tkprof output, the troublesome statement was:
select OBJ.OBJECT_TYPE ,OBJ.OBJECT_NAME ,OBJ.OWNER into :b0,:b1,:b2
from
ALL_SYNONYMS SYN ,ALL_OBJECTS OBJ where SYN.SYNONYM_NAME=:b1 and
SYN.OWNER=:b2) and SYN.TABLE_NAME=OBJ.OBJECT_NAME) and SYN.TABLE_OWNER=
OBJ.OWNER) and OBJ.OBJECT_TYPE in ('TABLE','VIEW','SYNONYM'))
I created a select only version of this with no bind variables and put the
same statement into a .sql file 11 times.
(I put it in 11 times because the tkprof output showed up 11
executes / 11 fetches).
I then logged onto our database as sys and turned autotrace on.
When run in first_rows, it used 40183 consistent gets.
I then did an alter session set optimizer_mode=rule;
When run in rule mode, the script used 44 consistent gets.
Hence my comment on 1000 times more in first_rows.
The tkprof comparison of when the database was in first rows vs the database
in choose mode showed an even bigger difference.
In first_rows, the fetch call query value was 427438.
In choose, the fetch call query value was 220.
This is a difference of 2000 times.
The synonym translations were particularly bad for us due to the very high
number of synonyms in database (we have 11143 synonyms, around 9600 of these
came from installing Java).
So as Connor suggested, in first_rows mode you will encounter some very bad
queries against the data dictionary.
An example of 1 which has been fixed by Oracle is catblock.sql -
there is an updated version available on Metalink -
see note 122567.1 titled "Poor Performance in Query on
DBA_WAITERS"
Whilst searching for the notes suggested by Anita, I came across a
good forum discussion
(see
http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_datab
ase_id=FOR&p_id=279251.999 )
This describes how the ODBC driver 8.1.7.4 has been fixed /
improved to use rule hints when accessing the data dictionary.
Before this if you used the Oracle ODBC driver and were in
first_rows mode we had to wait 5 - 10 minutes just to link a table in
Access.
I haven't yet had a chance to download the new ODBC driver
to test this out.
It would be good if the all_objects / all_synonyms views were hinted
to give good execution plans regardless of the optimizer_mode you were in.
Cherie - to see if this problem is affecting you and how much I would do a
couple of things:
count(*) from dba_synonyms
In first_rows, trace a query using synonyms and run tkprof on it -
see how much time is allocated to synonym translation
Alter to (say) choose, trace the same query using synonyms and run
tkprof on it - see how much time is allocated to synonym translation
If you run the tkprof explain plan as sys you will be able to see the
(potentially) different execution plans used during synonym translation.
I hope this helps & I'll be interested to see your results.
Regards,
Bruce Reardon
mailto:[EMAIL PROTECTED]
-Original Message-
Sent: Friday, 2 November 2001 4:04
Basically virtually all of the ALL_ and DBA_ etc
objects in the data dictionary are views - some very
complex. When you use optimizer_mode = first_rows,
you are now using the CBO on the data dictionary -
since the dict is "optimized" for RBO, you can get
some occasional anomalies when accessing dictionary
objects as part of an app.
hth
connor
--- [EMAIL PROTECTED] wrote: >
> Bruce,
>
> Can you expand further on the following statement?
> We use a lot of
> synonyms (not in forms but in SQL).
>
> This led to the above query using around 1000 times
> more consistent gets
> than it needed to (due to "bad" execution plan).
>
>
> Thanks,
>
> Cherie Machler
> Oracle DBA
> Gelco Information System
>
-
>
> "Reardon, Bruce (CALBBAY)"
>Subject: Synonyms can be VERY bad for performance
> 10/30/01 10:35 PM
>
> For your information and comment.
>
> We have just had a situation where the use of
> synonyms in our Forms
> application was very bad for performance.
>
> In particular, opening a form was taking around 11
> seconds, and 9.3 seconds
> of that was spent in translating the synonyms.
> A section of the tkprof output is shown below.
>
> select OBJ.OBJECT_TYPE ,OBJ.OBJECT_NAME ,OBJ.OWNER
> into :b0,:b1,:b2
> from
> ALL_SYNONYMS SYN ,ALL_OBJECTS OBJ where
> SYN.SYNONYM_NAME=:b1 and
> SYN.OWNER=:b2) and SYN.TABLE_NAME=OBJ.OBJECT_NAME)
> and SYN.TABLE_OWNER=
> OBJ.OWNER) and OBJ.OBJECT_TYPE in
> ('TABLE','VIEW','SYNONYM'))
>
>
> call count cpuelapsed disk
> querycurrent
> rows
> --- -- -- --
> -- --
> --
> Parse0 0.00 0.00
Re: Arch configuration -- I/O stuck
Allright! Back to the OT list, Hannibal! We'll discuss where you stuck your log over there. --Scott "Mohan, Ross" wrote: > > LoL! > > I always advocate "stripping" when there is a stuck log. > > -Original Message- > Sent: Thursday, November 01, 2001 3:40 PM > To: Multiple recipients of list ORACLE-L > > Steve, thanks for the help and for the url and the > advice of stripping. > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Mohan, Ross > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Scott Shafer INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: commit rate
Edward - Take a look at STATSPACK. The summary report has a statistic labeled "Transactions", which I believe is what you want. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, November 01, 2001 3:02 PM To: Multiple recipients of list ORACLE-L Hi Edward, please list, correct me if I'm wrong I think may be the amount of "log_file_sync" could help you too. But I think you should substract DBWR waits on this event. I also think that 'user commits' statistics is independent of group commits, this statistic is updated whenever a user issues a commit and group commits is just a way LGWR can write entries from log buffer to redo logs. HTH greetings DC - Original Message - To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Sent: Thursday, November 01, 2001 5:35 AM > Hi List, > > I try to estimate commit rate on our system. Currently I just periodically collect values of 'user > commits' statistics in v$sysstat in order to estimate frequency of LGWR's writes. But I assume this > statistics doesn't reflect group commits. Is there more sophisticated method to find frequency of > LGWR's writes? > > Thanks, > Ed > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Edward Shevtsov > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Diego Cutrone INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Toad vs SQL Navigator
My answer is always the same... Neither! :-) Instead, try Golden from Benthic Software (www.benthicsoftware.com). It's easy to try it without buying it, just use the annoyware. Also try PLEDIT as a PL/SQL development environment. It serves our purposes very well, and at 35$ it completely demolishes it's competition for cost/benefit. Paul - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Thursday, November 01, 2001 10:55 AM Hi All, We are looking at purchasing TOAD or SQL Navigator from Quest. I think they have purchase EZSQL also which I liked(good and cheap). I guess there goal is to eliminate the competition. I have some experience with free version of TOAD but not with SQL Navigator. Can someone share there pros/cons,why purchase one over the other, etc. if they have used both of these products? Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Paul Vallee INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: gnarly delete statement?
Try this: Delete from my_table t1 where a = 2 and exists (select 'X' from my_table t2 where t2.a = t1.a andt2.b = t1.b andt2.c = t1.b andt2.d = t1.d andt2.timestamp < t1.timestamp) At 12:55 PM 11/1/01 -0800, you wrote: >Given a business rule that says a combination of three columns must be >unique if and only if a fourth column equals a certain value, and if >the table in question contains rows that violate this requirement, I'm >trying to write a single SQL statement that will remove the duplicates. > Of each set of duplicate rows, I'll delete the one with the latest >insert timestamp. > >Let's call the columns > >a b c d timestamp > >The combination of b, c, and d must be unique if a = 2. > >I can get the duplicate row values along with the latest timestamp via > >select max(tstamp), a, b, c from >my_table group by a, b, c having count(*) > 1 > >But I can't figure out how to use that in a delete statement. > >Suggestions greatly appreciated! > > >Paul Baumgartel > > >__ >Do You Yahoo!? >Make a great connection at Yahoo! Personals. >http://personals.yahoo.com >-- >Please see the official ORACLE-L FAQ: http://www.orafaq.com >-- >Author: Paul Baumgartel > INET: [EMAIL PROTECTED] > >Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 >San Diego, California-- Public Internet access / Mailing Lists > >To REMOVE yourself from this mailing list, send an E-Mail message >to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in >the message BODY, include a line containing: UNSUB ORACLE-L >(or the name of mailing list you want to be removed from). You may >also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Regina Harter INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: gnarly delete statement?
don't suppose you can properly normalize the tables through materialized views and update the "pk" through that, eh? -Original Message- Sent: Thursday, November 01, 2001 3:56 PM To: Multiple recipients of list ORACLE-L Given a business rule that says a combination of three columns must be unique if and only if a fourth column equals a certain value, and if the table in question contains rows that violate this requirement, I'm trying to write a single SQL statement that will remove the duplicates. Of each set of duplicate rows, I'll delete the one with the latest insert timestamp. Let's call the columns a b c d timestamp The combination of b, c, and d must be unique if a = 2. I can get the duplicate row values along with the latest timestamp via select max(tstamp), a, b, c from my_table group by a, b, c having count(*) > 1 But I can't figure out how to use that in a delete statement. Suggestions greatly appreciated! Paul Baumgartel __ Do You Yahoo!? Make a great connection at Yahoo! Personals. http://personals.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Paul Baumgartel INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohan, Ross INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: TTI Layer Function codes
Thanks ... I was looking for something in docs before I have to turn on the trace ... Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art ! *2 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. *2
RE: gnarly delete statement?
Paul - Try this (may have a typo or two since I can't run it) delete from mytable x where a.timestamp < (select max(timestamp) from mytable y where x.b = y.b and x.c = y.c and x.d = y.d and x.a = 2) This is a mod of the SQL statement in Corrigan and Gurry. -Original Message- Sent: Thursday, November 01, 2001 2:56 PM To: Multiple recipients of list ORACLE-L Given a business rule that says a combination of three columns must be unique if and only if a fourth column equals a certain value, and if the table in question contains rows that violate this requirement, I'm trying to write a single SQL statement that will remove the duplicates. Of each set of duplicate rows, I'll delete the one with the latest insert timestamp. Let's call the columns a b c d timestamp The combination of b, c, and d must be unique if a = 2. I can get the duplicate row values along with the latest timestamp via select max(tstamp), a, b, c from my_table group by a, b, c having count(*) > 1 But I can't figure out how to use that in a delete statement. Suggestions greatly appreciated! Paul Baumgartel __ Do You Yahoo!? Make a great connection at Yahoo! Personals. http://personals.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Paul Baumgartel INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: TTI Layer Function codes
you're deep in the SQLNET TNS stack, near a small series of twisty passages. (turn high tracing on.. grep the logs for "tti" and brace yourself ) -Original Message- Sent: Thursday, November 01, 2001 3:46 PM To: Multiple recipients of list ORACLE-L Hi all, I am investigating some ORA-600 [12333] errors. According to note 35928.1 the second argument represents TTI Layer Function Code, followed by FUNCTION Code and SEQUENCE. I tried to look up TTI Layer Function codes but couldn't find any reference, does anyone know where I can find them? Thanks Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art ! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohan, Ross INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: commit rate
Hi Edward, please list, correct me if I'm wrong I think may be the amount of "log_file_sync" could help you too. But I think you should substract DBWR waits on this event. I also think that 'user commits' statistics is independent of group commits, this statistic is updated whenever a user issues a commit and group commits is just a way LGWR can write entries from log buffer to redo logs. HTH greetings DC - Original Message - To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Sent: Thursday, November 01, 2001 5:35 AM > Hi List, > > I try to estimate commit rate on our system. Currently I just periodically collect values of 'user > commits' statistics in v$sysstat in order to estimate frequency of LGWR's writes. But I assume this > statistics doesn't reflect group commits. Is there more sophisticated method to find frequency of > LGWR's writes? > > Thanks, > Ed > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Edward Shevtsov > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Diego Cutrone INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Arch configuration -- I/O stuck
LoL! I always advocate "stripping" when there is a stuck log. -Original Message- Sent: Thursday, November 01, 2001 3:40 PM To: Multiple recipients of list ORACLE-L Steve, thanks for the help and for the url and the advice of stripping. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohan, Ross INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: 25 words or less - ETL's - opinions (best of) and nightmares (my
-- Brian MacLean <[EMAIL PROTECTED]> on 11/01/01 12:25:22 -0800 > Come on everyone. In 25 words or less. > > "What I like or dislike about my ETL tool". Ab Initio > "The ETL feature I like most is it's fast, scales nicely and is fairly straightforward to maintain. Their GDE makes it fairly strightforward to generate the ETL code and test it. > "The ETL feature I wish I had most is __". the GDE requires windows. > "If I had it to do all over again I would ". Still buy Ab Initio and VMware. > "Buying ___ was the best decision I ever made because ". It works. You can't say that about most things. > "IMAO __". The product is well thought out and effective for scalable computing needs. If all you want to do is tickle a few thousand records/minute use DBI. If you need to move millions then Ab Inito is the best bet. DBI is the obvious other choice. Partly because it also works, well, and partly because it's now supported by Oracle. Perl also provides the best general toolkit for mangling data available. -- Steven Lembark 2930 W. Palmer Workhorse Computing Chicago, IL 60647 +1 800 762 1582 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
gnarly delete statement?
Given a business rule that says a combination of three columns must be unique if and only if a fourth column equals a certain value, and if the table in question contains rows that violate this requirement, I'm trying to write a single SQL statement that will remove the duplicates. Of each set of duplicate rows, I'll delete the one with the latest insert timestamp. Let's call the columns a b c d timestamp The combination of b, c, and d must be unique if a = 2. I can get the duplicate row values along with the latest timestamp via select max(tstamp), a, b, c from my_table group by a, b, c having count(*) > 1 But I can't figure out how to use that in a delete statement. Suggestions greatly appreciated! Paul Baumgartel __ Do You Yahoo!? Make a great connection at Yahoo! Personals. http://personals.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Paul Baumgartel INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: RE: recover on different hardware
hm. might be the fall 2002 meeting. --- Henry Poras <[EMAIL PROTECTED]> wrote: > Next time you're invited I'll be happy to show you the Necco factory. > Henry > > -Original Message- > Sent: Thursday, November 01, 2001 10:51 AM > To: Multiple recipients of list ORACLE-L > > > Dick, > > thank you -- how come I don't get invited to speak at NOUG anymore? > :( > > Actually, I've decided that there should be a corollary to my Rule 0 > -- > while that backup is being made, go and have a cup of coffee and take > a > pad and paper with you and start to plan what to do. > > This presumes that you haven't already sat down and at least thought > out what would happen in any given circumstance and planned for it :) > > I've found that whenever I just "rush in where angels fear to tread" > I > screw it up. Because I realize (as Barb did) that I should have done > (or should not have done) something one second AFTER I hit the enter > key. > > I like checklists. Hell, I LOVE checklists. > > I've submitted a presentation to IOUG - I hope they take it, as it > may > well be the last time I present, I'm thinking of hanging up my > presenting shoes :) > > Rachel > > --- [EMAIL PROTECTED] wrote: > > Barbara, > > > > Like Rachel I've been in the same place you are too, a couple > of > > times. In > > every instance where I've been faced with recovering a database > > and/or datafile > > the worst part of the process is getting started. Namely figuring > > out 1) what's > > broke, 2) what do I have to fix it with, 3) where do I want to be > in > > the end. > > > > In a case similar to yours (the disk farm blew), we recovered > the > > previous > > cold backup to a replacement system of the same type. The problem > I > > had was > > that the cold backup was from the previous weekend and it was > > Thursday with > > somewhere around 100 archived redo logs to play with and the online > > redo was > > lost with the disk farm. The answer was simply to restore > > everything, then > > before starting the DB I copied the one remaining control file from > > the old > > system onto the new system, replacing all of the existing files > with > > this one. > > Now the control files were all in sync with each other, but not > with > > the DB > > files. When the DB was started it of course complained that it > > needed recovery. > > Recovered the DB with the 'until ' option so that I > > consumed all of > > the archived redo, but stopped short of the online redo, and reset > > the logs. In > > the end the DB opened normally with no problems, although it's > uptime > > was VERY > > short, as I shut it down almost immediately and put a new backup of > > what I had > > in the safe. > > > > Once your over the initial sinking feeling in your stomach and > > adrenaline > > rush, it's a piece of cake. OH BTW, something we all should > practice > > every once > > in a while. I know I do. > > > > Dick Goulet > > > > PS: if you get a chance, attend one of Rachel's presentations. > It's > > more than > > well worth it. Actually change that to "make a chance". > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.com > > -- > > Author: > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > > San Diego, California-- Public Internet access / Mailing > > Lists > > > > > To REMOVE yourself from this mailing list, send an E-Mail message > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > > the message BODY, include a line containing: UNSUB ORACLE-L > > (or the name of mailing list you want to be removed from). You may > > also send the HELP command for other information (like > subscribing). > > > __ > Do You Yahoo!? > Make a great connection at Yahoo! Personals. > http://personals.yahoo.com > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Rachel Carmichael > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing > Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Henry Poras > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing > Lists > -
TTI Layer Function codes
Hi all, I am investigating some ORA-600 [12333] errors. According to note 35928.1 the second argument represents TTI Layer Function Code, followed by FUNCTION Code and SEQUENCE. I tried to look up TTI Layer Function codes but couldn't find any reference, does anyone know where I can find them? Thanks Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art ! *2 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. *2
RE: Arch configuration -- I/O stuck
Steve, thanks for the help and for the url and the advice of stripping. I don't understand what I'm pasting here , I'm executing a 'ls' in a FS that's in a different disk in differents LUNs (on the same Symmetrix), why is it still stucking. Shouldn't it be placed in a different queue?? "The 'ls' is probably getting stuck because the I/O is very slow and file system metadata writes are stuck in the I/O queue while locks are held on the file system metadata pending the completion of those writes." One more question, besides what you just advised me, I've been trying to reduce ARCH bandwidth (as I read in a TIP at your site), to spread ARCH work along more time and reduce the impact in foreground processes. So I've set log_archive_buffers from 4 to 2 and today I've tried to set log_archive_buffer_size to the MAX_IO_SIZE of the OS. But I found a problem with this. I tried to check what was the MAX_IO_SIZE, so I used 10046 event and check at scattered reads in a big FTS (as you do in your scripts) and I always got p3=5. I checked this into 2 differents databases running on the same box. Both reported p3=5 (5 blocks I think), but the surprise is that one of them has got db_block_size=4K and the other db_block_size=8K. How can it be possible? according to this test MAX_IO_SIZE could be 20K or 40K. what's wrong here? And something worst, MAX_IO_SIZE can't be so small, right? I thought it was 1MB or 512K in HP-UX 11.0 thannks for your time. TIA --- Steve Adams <[EMAIL PROTECTED]> escribió: > Hi Pablo, > > The 'ls' is probably getting stuck because the I/O > is very slow and file system metadata writes are > stuck in the I/O > queue while locks are held on the file system > metadata pending the completion of those writes. > > The problem could be that you are saturating the > cache allocations for the EMC LUNs containing your > archive destination > file system. See the answer at > http://www.ixora.com.au/q+a/0010/20102738.htm for a > bit about the EMC cache allocation > policy. To solve the problem you can use LVM to > stripe a large number of small LUNs together so as > to increase the total > amount of cache available for the archival writes. > You would also do well to avoid RAID-S of course! > > @ Regards, > @ Steve Adams > @ http://www.ixora.com.au/ - For > DBAs > @ http://www.christianity.net.au/ - For all > > > > -Original Message- > From: Pablo ksksksk [mailto:[EMAIL PROTECTED]] > Sent: Thursday, 1 November 2001 5:45 > To: Multiple recipients of list ORACLE-L > Subject: Arch configuration -- I/O stuck > > > Hi list, > > Oracle 7.3.4 > HP-UX > log_archive_buffer_size=32 (redo log blocks = 1K) > log_archive_buffers=4 > Filesystem based (no direct I/O) > > I've been detecting that my box gets stucked > eventually for some time. > When this happens I can't do even a "ls" (it > actually executes it but it takes a long time). > If I check my cpu with TOP, I see 47% idle time > and > there's no process monopolizing the CPU. > But when I check disk activity with sar -d I see > that one disk is 100% busy and it's avwait+avserv > > 1000 ms. The other disks are fine. > I then check disk activity with Glance and I can > identify the process that's writting/reading on this > disk is: ARCH (ARCH is writting a 1.9 GB redo log.) > > So here are my doubts: > 1)If only one disk is saturated (I've got > about > 30 disks in this box (a SYMMETRIX array) with some > controllers), why does the whole box get stucked? > why > are even other applications connected to other > instances running on this box affected? (may be > because the HP-UX LVM system gets saturated???) > > 2) What can I do to avoid this problem?, > (reduce > log_archive_buffers parameter may be, or increase > log_archive_buffer_size) > > help me on this > Thanks > ___ Do You Yahoo!? Yahoo! Messenger Comunicación instantánea gratis con tu gente. http://messenger.yahoo.es -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Pablo=20ksksksk?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
25 words or less - ETL's - opinions (best of) and nightmares (my
Come on everyone. In 25 words or less. "What I like or dislike about my ETL tool". "The ETL feature I like most is __". "The ETL feature I wish I had most is __". "If I had it to do all over again I would ". "The SOB that bought ___ should be nailed to a ___ and left to _". "Buying ___ was the best decision I ever made because ". "IMAO __". (More than 25 words if ya want to, I just didn't want to pressure anyone. Ranters, please feel free to go on for days) TIA Brian Petronius on Reorganization --- We trained hard -- but it seemed that every time we were beginning to form up into teams, we would be reorganized. I was to learn that later in life we tend to meet any new situation by reorganizing, and the wonderful method it can be for creating the illusion of progress while producing confusion, inefficiency and demoralization. -- Petronius Arbiter, 66 CE -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Brian MacLean INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Oracle Data Guard
Jeff - With 8.1.7, I believe that you are limited to read-only or standby, but you can't have both simultaneously. It is either in recovery mode accepting redo logs from production or open and allowing read-only. I believe that with 9i the options are much more flexible. Read up on the Oracle9i Real Application Clusters. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, November 01, 2001 1:20 PM To: Multiple recipients of list ORACLE-L Hi. I'm looking into implementing a read-only stand-by database. I'd like to have it read-only for periodic reporting during the day. Is this possible under 8.1.7, or 9i? Are there any drawbacks to having the stand-by database opened for read-only? I would have two goals in mind: 1. A stand-by, read-only reporting db. 2. Easy switch-over to the stand-by. Are these two goals compatible? I can't find any documention on Data Guard for 8.1.7, as the links on Technet are broken. Thanks Jeff -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jeff Wiegard INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: log growing
Seema - Check your listener.ora file and look at the TRACE_LEVEL parameter. To effect a change, start/stop the listener. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, November 01, 2001 1:10 PM To: Multiple recipients of list ORACLE-L Hi I checked sqlnet.log file is growing.I want to set parameter to not grow sqlnet.ora at application end.Let me know what parameter I need to set in which file. Thanks -Seema _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seema Singh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: RE: recover on different hardware
Next time you're invited I'll be happy to show you the Necco factory. Henry -Original Message- Sent: Thursday, November 01, 2001 10:51 AM To: Multiple recipients of list ORACLE-L Dick, thank you -- how come I don't get invited to speak at NOUG anymore? :( Actually, I've decided that there should be a corollary to my Rule 0 -- while that backup is being made, go and have a cup of coffee and take a pad and paper with you and start to plan what to do. This presumes that you haven't already sat down and at least thought out what would happen in any given circumstance and planned for it :) I've found that whenever I just "rush in where angels fear to tread" I screw it up. Because I realize (as Barb did) that I should have done (or should not have done) something one second AFTER I hit the enter key. I like checklists. Hell, I LOVE checklists. I've submitted a presentation to IOUG - I hope they take it, as it may well be the last time I present, I'm thinking of hanging up my presenting shoes :) Rachel --- [EMAIL PROTECTED] wrote: > Barbara, > > Like Rachel I've been in the same place you are too, a couple of > times. In > every instance where I've been faced with recovering a database > and/or datafile > the worst part of the process is getting started. Namely figuring > out 1) what's > broke, 2) what do I have to fix it with, 3) where do I want to be in > the end. > > In a case similar to yours (the disk farm blew), we recovered the > previous > cold backup to a replacement system of the same type. The problem I > had was > that the cold backup was from the previous weekend and it was > Thursday with > somewhere around 100 archived redo logs to play with and the online > redo was > lost with the disk farm. The answer was simply to restore > everything, then > before starting the DB I copied the one remaining control file from > the old > system onto the new system, replacing all of the existing files with > this one. > Now the control files were all in sync with each other, but not with > the DB > files. When the DB was started it of course complained that it > needed recovery. > Recovered the DB with the 'until ' option so that I > consumed all of > the archived redo, but stopped short of the online redo, and reset > the logs. In > the end the DB opened normally with no problems, although it's uptime > was VERY > short, as I shut it down almost immediately and put a new backup of > what I had > in the safe. > > Once your over the initial sinking feeling in your stomach and > adrenaline > rush, it's a piece of cake. OH BTW, something we all should practice > every once > in a while. I know I do. > > Dick Goulet > > PS: if you get a chance, attend one of Rachel's presentations. It's > more than > well worth it. Actually change that to "make a chance". > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing > Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Make a great connection at Yahoo! Personals. http://personals.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Henry Poras INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Oracle Data Guard
It is possible in 8.1.7 >From: "Jeff Wiegard" <[EMAIL PROTECTED]> >Reply-To: [EMAIL PROTECTED] >To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> >Subject: Oracle Data Guard >Date: Thu, 01 Nov 2001 11:20:22 -0800 > >Hi. I'm looking into implementing a read-only stand-by database. I'd >like to have it read-only for periodic reporting during the day. Is >this possible under 8.1.7, or 9i? > >Are there any drawbacks to having the stand-by database opened for >read-only? I would have two goals in mind: 1. A stand-by, read-only >reporting db. 2. Easy switch-over to the stand-by. Are these two >goals compatible? I can't find any documention on Data Guard for >8.1.7, as the links on Technet are broken. > >Thanks > >Jeff >-- >Please see the official ORACLE-L FAQ: http://www.orafaq.com >-- >Author: Jeff Wiegard > INET: [EMAIL PROTECTED] > >Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 >San Diego, California-- Public Internet access / Mailing Lists > >To REMOVE yourself from this mailing list, send an E-Mail message >to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in >the message BODY, include a line containing: UNSUB ORACLE-L >(or the name of mailing list you want to be removed from). You may >also send the HELP command for other information (like subscribing). _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: ARUN K C INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: log growing
I think u have set trace on in sqlnet.ora check it out >From: "Seema Singh" <[EMAIL PROTECTED]> >Reply-To: [EMAIL PROTECTED] >To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> >Subject: log growing >Date: Thu, 01 Nov 2001 11:10:19 -0800 > >Hi >I checked sqlnet.log file is growing.I want to set parameter to not grow >sqlnet.ora at application end.Let me know what parameter I need to set in >which file. >Thanks >-Seema > >_ >Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp > >-- >Please see the official ORACLE-L FAQ: http://www.orafaq.com >-- >Author: Seema Singh > INET: [EMAIL PROTECTED] > >Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 >San Diego, California-- Public Internet access / Mailing Lists > >To REMOVE yourself from this mailing list, send an E-Mail message >to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in >the message BODY, include a line containing: UNSUB ORACLE-L >(or the name of mailing list you want to be removed from). You may >also send the HELP command for other information (like subscribing). _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: ARUN K C INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: OCP News for Military Veterans
uhthe Market for Oracle aggrandizement? I think that's flourishing, eh? -Original Message- Sent: Thursday, November 01, 2001 1:55 PM To: Multiple recipients of list ORACLE-L Pretty poor timing considering the current market. Jared Ken Janusz fsys.com>cc: Sent by: Subject: OCP News for Military Veterans [EMAIL PROTECTED] om 11/01/01 08:16 AM Please respond to ORACLE-L Oracle Certification Gains GI And Veterans Approval REDWOOD SHORES, CA--(INTERNET WIRE)--Nov 01, 2001-- Oracle Corp.announced today that their education arm, Oracle(R) University, received notification from the United States Veterans Association that Oracle Professional Certifications are now part of the GI Bill and Dependents Educational Assistance reimbursement programs. All eligible veterans, their spouses and children can receive reimbursement for the costs of taking the Oracle Certification exams, seen as a requirement for many job opportunities. Oracle University is one of the largest and most innovative information technology (IT) professional education providers in the world, with more than 90,000 certified professionals. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ken Janusz INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohan, Ross INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Oracle Data Guard
Hi. I'm looking into implementing a read-only stand-by database. I'd like to have it read-only for periodic reporting during the day. Is this possible under 8.1.7, or 9i? Are there any drawbacks to having the stand-by database opened for read-only? I would have two goals in mind: 1. A stand-by, read-only reporting db. 2. Easy switch-over to the stand-by. Are these two goals compatible? I can't find any documention on Data Guard for 8.1.7, as the links on Technet are broken. Thanks Jeff -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jeff Wiegard INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: CHANGING CHARACTER SET
NLS_LANG should be set on client, either UNIX environment variable or NT registry entry. I don't think there's a way to force client to use a particular character set. This is the whole point of NLS, I think. Server should not care what language/character set client uses, and vice versa. Gerardo -Original Message- Sent: Thursday, November 01, 2001 10:27 AM To: 'Molina, Gerardo' Gerardo, Thanks for your help. I need little more clarification: When we change the database character set from US7ASCII TO UTF8 Do this will only affects for the server database and client can still export in different character set as per setting of NLS_LANG on their machine.. If i want to force clients to use NLS_LANG of server database how can i set some parameters on server . NLS_LANG is set as environment varable but if i want to add entry in init.ora we have parameter NLS_LANGUAGE and NLS_TERRITORY...but how can we specify the chacter set in init.ora file I know i am asking some silly questions but i am totally confused with oracle docs. Thanks -Harvinder -Original Message- Sent: Thursday, November 01, 2001 11:40 AM To: Multiple recipients of list ORACLE-L Harvinder, try setting NLS_LANG=AMERICAN_AMERICA.UTF8, then doing the export. If NLS_LANG is not set, the default is US7ASCII as you experienced. Refer to MetaLink Note 48644.1 and Note 15095.1 Gerardo -Original Message- Sent: Thursday, November 01, 2001 7:30 AM To: Multiple recipients of list ORACLE-L Gerardo, The trick works for me also but when i try to export it shows as: Connected to: Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production With the Partitioning option JServer Release 8.1.7.0.0 - Production Export done in US7ASCII character set and US7ASCII NCHAR character set server uses UTF8 character set (possible charset conversion) Do we are able to change character set or our existing data is still using US7ASCII. Thanks -Harvinder -Original Message- Sent: Thursday, November 01, 2001 2:00 AM To: Multiple recipients of list ORACLE-L Try this procedure: The extra db bounce did the trick for me. Don't ask me why. I think I found it on MetaLink or opened a TAR. HTH Gerardo SVRMGR> SHUTDOWN IMMEDIATE; -- or NORMAL SVRMGR> STARTUP MOUNT; SVRMGR> ALTER SYSTEM ENABLE RESTRICTED SESSION; SVRMGR> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0; SVRMGR> ALTER SYSTEM SET AQ_TM_PROCESSES=0; SVRMGR> ALTER DATABASE OPEN; SVRMGR> ALTER DATABASE CHARACTER SET ; SVRMGR> SHUTDOWN IMMEDIATE; SVRMGR> STARTUP RESTRICT; SVRMGR> SHUTDOWN IMMEDIATE; SVRMGR> STARTUP; -Original Message- Sent: Wednesday, October 31, 2001 9:02 AM To: Multiple recipients of list ORACLE-L Hi, We are planning to change out database charater set from US7ASCII to UTF8 and we followed folowing steps as specified in metalink... but still when i see the trace of controlfile it shows character set as US7ASCII.. Do i need to do export/import to change takes place. we have oracle 8.1.7.1.5 on WIN2K. SVRMGR> SHUTDOWN IMMEDIATE; -- or NORMAL SVRMGR> STARTUP MOUNT; SVRMGR> ALTER SYSTEM ENABLE RESTRICTED SESSION; SVRMGR> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0; SVRMGR> ALTER SYSTEM SET AQ_TM_PROCESSES=0; SVRMGR> ALTER DATABASE OPEN; SVRMGR> ALTER DATABASE CHARACTER SET ; SVRMGR> SHUTDOWN IMMEDIATE; -- OR NORMAL SVRMGR> STARTUP; Thanks -Harvinder -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Harvinder Singh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Molina, Gerardo INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Harvinder Singh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists --
log growing
Hi I checked sqlnet.log file is growing.I want to set parameter to not grow sqlnet.ora at application end.Let me know what parameter I need to set in which file. Thanks -Seema _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seema Singh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: OCP News for Military Veterans
Pretty poor timing considering the current market. Jared Ken Janusz fsys.com>cc: Sent by: Subject: OCP News for Military Veterans [EMAIL PROTECTED] om 11/01/01 08:16 AM Please respond to ORACLE-L Oracle Certification Gains GI And Veterans Approval REDWOOD SHORES, CA--(INTERNET WIRE)--Nov 01, 2001-- Oracle Corp.announced today that their education arm, Oracle(R) University, received notification from the United States Veterans Association that Oracle Professional Certifications are now part of the GI Bill and Dependents Educational Assistance reimbursement programs. All eligible veterans, their spouses and children can receive reimbursement for the costs of taking the Oracle Certification exams, seen as a requirement for many job opportunities. Oracle University is one of the largest and most innovative information technology (IT) professional education providers in the world, with more than 90,000 certified professionals. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ken Janusz INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
system command from within stored procedure
Group, Is there anyway to execute a system level command from within a stored procedure and also get a return code back ie. A) did some prep on a partitioned table B) used utl_file to unload some new data from remote location C) NOW I want to run SQLLDR - direct path, NORECOVERY D) continue with my stored procedure TIA Al Rusnak 804-734-8453 [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rusnak, George A. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Re[2]:RE: recover on different hardware
yeah got the invite.. I was teasing you :) I'll have to talk to Marlene before committing to anything... won't even commit to commit to anything :) --- [EMAIL PROTECTED] wrote: > Rachel, > > My first rule on recoveries is to go get the cup of coffee after > a walk > around the building to cool off. It helps to drag along your SA so > that you > have an idea of what is stored out there on tape. BTW, I have the > same problem > with the key too. I also have the same LOVE for checklists. > Violated > that rule once, my co-workers refuse to allow facilities to fix the > dent I put > in the wall, one second after pushing that key! > > OH, your invite is probably in the mail too. > > Dick Goulet > > Reply Separator > Author: Rachel Carmichael <[EMAIL PROTECTED]> > Date: 11/1/2001 7:51 AM > > Dick, > > thank you -- how come I don't get invited to speak at NOUG anymore? > :( > > Actually, I've decided that there should be a corollary to my Rule 0 > -- > while that backup is being made, go and have a cup of coffee and take > a > pad and paper with you and start to plan what to do. > > This presumes that you haven't already sat down and at least thought > out what would happen in any given circumstance and planned for it :) > > I've found that whenever I just "rush in where angels fear to tread" > I > screw it up. Because I realize (as Barb did) that I should have done > (or should not have done) something one second AFTER I hit the enter > key. > > I like checklists. Hell, I LOVE checklists. > > I've submitted a presentation to IOUG - I hope they take it, as it > may > well be the last time I present, I'm thinking of hanging up my > presenting shoes :) > > Rachel > > --- [EMAIL PROTECTED] wrote: > > Barbara, > > > > Like Rachel I've been in the same place you are too, a couple > of > > times. In > > every instance where I've been faced with recovering a database > > and/or datafile > > the worst part of the process is getting started. Namely figuring > > out 1) what's > > broke, 2) what do I have to fix it with, 3) where do I want to be > in > > the end. > > > > In a case similar to yours (the disk farm blew), we recovered > the > > previous > > cold backup to a replacement system of the same type. The problem > I > > had was > > that the cold backup was from the previous weekend and it was > > Thursday with > > somewhere around 100 archived redo logs to play with and the online > > redo was > > lost with the disk farm. The answer was simply to restore > > everything, then > > before starting the DB I copied the one remaining control file from > > the old > > system onto the new system, replacing all of the existing files > with > > this one. > > Now the control files were all in sync with each other, but not > with > > the DB > > files. When the DB was started it of course complained that it > > needed recovery. > > Recovered the DB with the 'until ' option so that I > > consumed all of > > the archived redo, but stopped short of the online redo, and reset > > the logs. In > > the end the DB opened normally with no problems, although it's > uptime > > was VERY > > short, as I shut it down almost immediately and put a new backup of > > what I had > > in the safe. > > > > Once your over the initial sinking feeling in your stomach and > > adrenaline > > rush, it's a piece of cake. OH BTW, something we all should > practice > > every once > > in a while. I know I do. > > > > Dick Goulet > > > > PS: if you get a chance, attend one of Rachel's presentations. > It's > > more than > > well worth it. Actually change that to "make a chance". > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.com > > -- > > Author: > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > > San Diego, California-- Public Internet access / Mailing > > Lists > > > > > To REMOVE yourself from this mailing list, send an E-Mail message > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > > the message BODY, include a line containing: UNSUB ORACLE-L > > (or the name of mailing list you want to be removed from). You may > > also send the HELP command for other information (like > subscribing). > > > __ > Do You Yahoo!? > Make a great connection at Yahoo! Personals. > http://personals.yahoo.com > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Rachel Carmichael > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing > Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXA
Re[2]:RE: recover on different hardware
Rachel, My first rule on recoveries is to go get the cup of coffee after a walk around the building to cool off. It helps to drag along your SA so that you have an idea of what is stored out there on tape. BTW, I have the same problem with the key too. I also have the same LOVE for checklists. Violated that rule once, my co-workers refuse to allow facilities to fix the dent I put in the wall, one second after pushing that key! OH, your invite is probably in the mail too. Dick Goulet Reply Separator Author: Rachel Carmichael <[EMAIL PROTECTED]> Date: 11/1/2001 7:51 AM Dick, thank you -- how come I don't get invited to speak at NOUG anymore? :( Actually, I've decided that there should be a corollary to my Rule 0 -- while that backup is being made, go and have a cup of coffee and take a pad and paper with you and start to plan what to do. This presumes that you haven't already sat down and at least thought out what would happen in any given circumstance and planned for it :) I've found that whenever I just "rush in where angels fear to tread" I screw it up. Because I realize (as Barb did) that I should have done (or should not have done) something one second AFTER I hit the enter key. I like checklists. Hell, I LOVE checklists. I've submitted a presentation to IOUG - I hope they take it, as it may well be the last time I present, I'm thinking of hanging up my presenting shoes :) Rachel --- [EMAIL PROTECTED] wrote: > Barbara, > > Like Rachel I've been in the same place you are too, a couple of > times. In > every instance where I've been faced with recovering a database > and/or datafile > the worst part of the process is getting started. Namely figuring > out 1) what's > broke, 2) what do I have to fix it with, 3) where do I want to be in > the end. > > In a case similar to yours (the disk farm blew), we recovered the > previous > cold backup to a replacement system of the same type. The problem I > had was > that the cold backup was from the previous weekend and it was > Thursday with > somewhere around 100 archived redo logs to play with and the online > redo was > lost with the disk farm. The answer was simply to restore > everything, then > before starting the DB I copied the one remaining control file from > the old > system onto the new system, replacing all of the existing files with > this one. > Now the control files were all in sync with each other, but not with > the DB > files. When the DB was started it of course complained that it > needed recovery. > Recovered the DB with the 'until ' option so that I > consumed all of > the archived redo, but stopped short of the online redo, and reset > the logs. In > the end the DB opened normally with no problems, although it's uptime > was VERY > short, as I shut it down almost immediately and put a new backup of > what I had > in the safe. > > Once your over the initial sinking feeling in your stomach and > adrenaline > rush, it's a piece of cake. OH BTW, something we all should practice > every once > in a while. I know I do. > > Dick Goulet > > PS: if you get a chance, attend one of Rachel's presentations. It's > more than > well worth it. Actually change that to "make a chance". > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing > Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Make a great connection at Yahoo! Personals. http://personals.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists
Re:OCP News for Military Veterans
Problem, the test has to be taken on or after 1 March 2001. Dick Goulet Reply Separator Author: Ken Janusz <[EMAIL PROTECTED]> Date: 11/1/2001 8:16 AM Oracle Certification Gains GI And Veterans Approval REDWOOD SHORES, CA--(INTERNET WIRE)--Nov 01, 2001-- Oracle Corp.announced today that their education arm, Oracle(R) University, received notification from the United States Veterans Association that Oracle Professional Certifications are now part of the GI Bill and Dependents Educational Assistance reimbursement programs. All eligible veterans, their spouses and children can receive reimbursement for the costs of taking the Oracle Certification exams, seen as a requirement for many job opportunities. Oracle University is one of the largest and most innovative information technology (IT) professional education providers in the world, with more than 90,000 certified professionals. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ken Janusz INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: OT - RAM disk
Must disagree here. Just upgraded my ram from 128 meg to 1 Gig ($120.00 US) and my "Undying" is running like a champ, loads, saves, visuals, everything. 900 mz Athlon, Voodoo 5000 w/ 128m mem. Can't believe the difference. Just put them in the slots, nothing else. One caveat, Norton Antivirus decided it doesn't have enough memory to run now. -Original Message- Sent: Thursday, November 01, 2001 10:16 AM To: Multiple recipients of list ORACLE-L and b Games are usually CPU bound...or system bus bound (vid card to mobo, natch ). -Original Message- Sent: Wednesday, October 31, 2001 12:02 PM To: Multiple recipients of list ORACLE-L Hey Walt, How about using the RAMDISK on your gaming pc. I'll bet it would scream. ROR mª¿ªm -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ron Rogers INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohan, Ross INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: ODell, Charles INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Data Deletion in Tables with Foreign Keys
Dick- Do you have any experience with using "on delete cascade"? Do you know
if it will be faster, or just easier? I was just curious how it worked
behind the scenes and if it would still just be doing table scans.
Thanks again.
Erik
> -Original Message-
> From: [EMAIL PROTECTED] [SMTP:[EMAIL PROTECTED]]
> Sent: Thursday, November 01, 2001 10:10 AM
> To: Multiple recipients of list ORACLE-L
> Subject: Re:Data Deletion in Tables with Foreign Keys
>
> Erik,
>
> First off, do the foreign keys have the 'on delete cascade' option
> turned
> on? If not then do so as it makes keeping things in sync much easier.
> Actually
> in this scenario you don't have to worry about the child tables.
>
> Second, what do you mean by "The design of the application prohibits
> me from
> adding indexes to these tables. "? I've not seen any application that
> 'prohibits' adding indexes.
>
> Dick Goulet
>
> Reply Separator
> Author: Erik Williams <[EMAIL PROTECTED]>
> Date: 11/1/2001 5:45 AM
>
> I need to prune data from a set of tables every day. I need to retain the
> last 90 days of information. Two of the tables, A and B, have foreign keys
> to a third, C. I cannot disable the constraints prior to deleting the
> data,
> because the system is 24/7. I have created a script that will delete the
> data from each of the tables with foreign keys first, then from the parent
> table. The problem I am having is the time it is taking to perform the
> deletions. The A and B tables are without indexes on the foreign key,
> because they very high volume insertion tables and very infrequent lookup.
> These tables are very large. The design of the application prohibits me
> from
> adding indexes to these tables.
>
> Here is the code:
>
> set serveroutput on
> set timing on
>
> DECLARE
> id number(15);
> dtm date;
> cnt number;
> cursor purge_c is
> select id
> from C
> where dtm < sysdate-90;
> BEGIN
> open purge_c;
> fetch purge_c into id;
> cnt := 0;
> while (purge_c%FOUND) loop
> cnt := cnt + 1;
> delete from A where id = id;
> delete from B where id = id;
> delete from C where id = id;
> commit;
> fetch purge_c into id;
> end loop;
> close purge_c;
> DBMS_OUTPUT.PUT_LINE('Number of sessions deleted: ' || cnt);
> END;
> /
>
> set timing off
> set serveroutput off
>
>
> I was thinking about creating another loop so that commits will only be
> done
> every 1000 deletions, but I think that the commits are a very a small
> percentage of the time compared to the table scans. I also considered
> partitioned tables, but I really don't want to go to that length. I was
> hoping to hear how other people handle this issue.
>
> Thanks.
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Erik Williams
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing Lists
>
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author:
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing Lists
>
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Erik Williams
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: 11.5.4 Easy Question (?) - Resolved
Folks -- I got it. Seems that what happened was that one of the scripts that needed to be run by the applications user shuts the listener down and attempts (but fails) to restart it. Now, the problem was that the lsnr process was out there, but that there were no associated services with it, so if you're not looking closely enough, you wouldn't find the problem. #$%^&* Thanks to all who offered help... Bambi. -Original Message- Sent: Thursday, November 01, 2001 10:36 AM To: 'Bellows, Bambi' What do you see when you type that in ? The last part of that url should take you to the Apps login screen. -Original Message- Sent: Thursday, November 01, 2001 10:02 AM To: Jerry Hess No, that works. I'm trying it with http://servername:9008/ -Original Message- Sent: Thursday, November 01, 2001 9:42 AM To: '[EMAIL PROTECTED]' What type of error message is the Web Server giving you ? I assume you are trying to access it with something like "http://servername:8000/dev60cgi/f60cgi";. Do you see the initial Oracle splash screen ? -Original Message- Sent: Thursday, November 01, 2001 9:10 AM To: Multiple recipients of list ORACLE-L Well, yes and no. 1) Database up 2) Listener up 3) Log in as Apps person (hence source in appsora.env) 4) Other processes listed -Original Message- Sent: Thursday, November 01, 2001 8:10 AM To: Multiple recipients of list ORACLE-L Are you starting everything up in the proper order ? - Database first (8.1.?) - 8.1.? listener - running your applications environment file (APPSORA.env) - the other processes you listed Just a guess. -Original Message- Sent: Wednesday, October 31, 2001 3:55 PM To: Multiple recipients of list ORACLE-L Friends -- Our pesky users are having problems logging on from the Web front end of Oracle Financials. This was working just fine 10/25 when the last pesky user successfully logged in. Since then, the box (Tru64 v5.1) has been bounced, and we have started the following scripts successfully... 1) addbctl.sh (database startup) 2) addlnctl.sh (listener startup... you'd think Oracle would use its own conventions, wouldn't you?) 3) adalnctl.sh (applications listener) 4) adcmctl.sh (concurrent manager startup) 5) adrepctl.sh (reports server) 6) adfrmctl.sh (forms server) 7) adtcfctl.start (tcf sockets) 8) adapcctl.sh (Apache web process) 9) adfmsctl.sh (forms metric server) 10) adfmcctl.sh (forms metric client) I figure if the database is up, the listeners are up and concurrent manager is up, everything should be fine. What am I missing? TIA! Bambi. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bellows, Bambi INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jerry Hess INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bellows, Bambi INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bellows, Bambi INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spellin
Re: recover on different hardware
If your redo logs are out of sequence, i.e you have 1, 2, 3,5,6,8 then you will not be able to do a complete recovery. Get the time of the last in sequence redo log and recovery until time. The svrmgrl command is like this; recover database until time '2001/11/01 hh:mi:ss' using backup control file The it will ask you for redo logs. Reply auto and it should come back ok. The open the database with resetlogs. HTH, Ruth - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Thursday, November 01, 2001 2:15 AM > Been here 18 hours. No database. Looks grim. > Hardware blew chunks. Major ugly. > Plan is to reconstruct entire system on new hardware. > If I get my dbf files from last night's cold backup (as well as all my > config files), I should have a database that looks just like last night at > 11:00 pm. > DB should come up clean, should think it's a happy camper. > > I do have most of my archived redo logs from today. What recovery option do > I use to start applying the redo logs I've been able to salvage? (Guess I'm > confused cuz the database will not think it needs recovery.) > > I'm about to RTFM, but I'm a bit bleary-eyed. > Any assistance greatly appreciated. > > Thanks! > Barb > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Baker, Barbara > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ruth Gramolini INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: recover on different hardware
The trick is to call someone else over to hit the key. This way you have deniability. (Yes, I spent too many years in intel) --S Rachel Carmichael wrote: > > Dick, > > thank you -- how come I don't get invited to speak at NOUG anymore? :( > > Actually, I've decided that there should be a corollary to my Rule 0 -- > while that backup is being made, go and have a cup of coffee and take a > pad and paper with you and start to plan what to do. > > This presumes that you haven't already sat down and at least thought > out what would happen in any given circumstance and planned for it :) > > I've found that whenever I just "rush in where angels fear to tread" I > screw it up. Because I realize (as Barb did) that I should have done > (or should not have done) something one second AFTER I hit the enter > key. > > I like checklists. Hell, I LOVE checklists. > > I've submitted a presentation to IOUG - I hope they take it, as it may > well be the last time I present, I'm thinking of hanging up my > presenting shoes :) > > Rachel _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Scott Shafer INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Synonyms can be VERY bad for performance
Basically virtually all of the ALL_ and DBA_ etc
objects in the data dictionary are views - some very
complex. When you use optimizer_mode = first_rows,
you are now using the CBO on the data dictionary -
since the dict is "optimized" for RBO, you can get
some occasional anomalies when accessing dictionary
objects as part of an app.
hth
connor
--- [EMAIL PROTECTED] wrote: >
> Bruce,
>
> Can you expand further on the following statement?
> We use a lot of
> synonyms (not in forms but in SQL).
>
> This led to the above query using around 1000 times
> more consistent gets
> than it needed to (due to "bad" execution plan).
>
>
> Thanks,
>
> Cherie Machler
> Oracle DBA
> Gelco Information System
>
>
>
>
>
>
> "Reardon, Bruce (CALBBAY)"
>
>
> <[EMAIL PROTECTED]
>To: Multiple recipients of list ORACLE-L
> <[EMAIL PROTECTED]>
> to.com.au>
>cc:
>
> Sent by: [EMAIL PROTECTED]
>Subject: Synonyms can be VERY bad for
> performance
>
>
>
>
>
>
> 10/30/01 10:35 PM
>
>
> Please respond to ORACLE-L
>
>
>
>
>
>
>
>
>
>
>
>
> For your information and comment.
>
> We have just had a situation where the use of
> synonyms in our Forms
> application was very bad for performance.
>
> In particular, opening a form was taking around 11
> seconds, and 9.3 seconds
> of that was spent in translating the synonyms.
> A section of the tkprof output is shown below.
>
> select OBJ.OBJECT_TYPE ,OBJ.OBJECT_NAME ,OBJ.OWNER
> into :b0,:b1,:b2
> from
> ALL_SYNONYMS SYN ,ALL_OBJECTS OBJ where
> SYN.SYNONYM_NAME=:b1 and
> SYN.OWNER=:b2) and SYN.TABLE_NAME=OBJ.OBJECT_NAME)
> and SYN.TABLE_OWNER=
> OBJ.OWNER) and OBJ.OBJECT_TYPE in
> ('TABLE','VIEW','SYNONYM'))
>
>
> call count cpuelapsed disk
> querycurrent
> rows
> --- -- -- --
> -- --
> --
> Parse0 0.00 0.00 0
>0 0
> 0
> Execute 11 0.03 0.03 0
>0 0
> 0
> Fetch 11 9.26 9.27 0
> 427438 55
> 11
> --- -- -- --
> -- --
> --
> total 22 9.29 9.30 0
> 427438 55
> 11
>
>
> System Details: Oracle 8.1.7.1.4, NT 4 Sp6a, quad
> processor server,
> optimizer_mode = first_rows, JVM installed
>The JVM install created 10300 objects
> with an object_type like
> '%JAVA%' and around 9600 synonyms.
>
> It was the optimizer_mode = first_rows (combined
> with all the synonyms from
> the JVM install) that was the real problem.
>
> Because we were in first_rows, queries against the
> data dictionary were
> optimized in first_rows mode rather than rule.
>This was despite us not having any
> statistics on system or sys
> objects.
>
> This led to the above query using around 1000 times
> more consistent gets
> than it needed to (due to "bad" execution plan).
>
>
> We found 2 ways to get around this:
>Get rid of the synonyms and use "alter
> session set
> current_schema"
> in a logon trigger, OR
>Change the optimizer_mode to choose.
>
> Our central development team decided to initially go
> with altering the
> optimizer_mode to choose.
>This improved the form opening time to
> approx 4 secs but 10% of
> this
> (0.44) seconds is still spent on translating
> synonyms.
>
> To me, this just goes to show that synonyms can be
> bad for performance as
> well as being bad for scalability.
>
> Regards,
> Bruce Reardon
> mailto:[EMAIL PROTECTED]
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: Reardon, Bruce (CALBBAY)
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- (858) 538-5051 FAX:
> (858) 538-5051
> San Diego, California
RE: Data Deletion in Tables with Foreign Keys
Hi, We are continuously getting following error when we are running long tests. test makes connection to database multiple times. 10/31/2001 9:18:01 PM ERROR: cannot open connection 'dsn=QUARK_ORACLE;uid=nmdbo;pwd=nmdbo;' - error [-2147467259] [Microsoft][ODBC driver for Oracle][Oracle]ORA-00604: error occurred at recursive SQL level Do someone have an idea what can be the possible cause? Thanks -Harvinder -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Harvinder Singh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: CHANGING CHARACTER SET
Harvinder, try setting NLS_LANG=AMERICAN_AMERICA.UTF8, then doing the export. If NLS_LANG is not set, the default is US7ASCII as you experienced. Refer to MetaLink Note 48644.1 and Note 15095.1 Gerardo -Original Message- Sent: Thursday, November 01, 2001 7:30 AM To: Multiple recipients of list ORACLE-L Gerardo, The trick works for me also but when i try to export it shows as: Connected to: Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production With the Partitioning option JServer Release 8.1.7.0.0 - Production Export done in US7ASCII character set and US7ASCII NCHAR character set server uses UTF8 character set (possible charset conversion) Do we are able to change character set or our existing data is still using US7ASCII. Thanks -Harvinder -Original Message- Sent: Thursday, November 01, 2001 2:00 AM To: Multiple recipients of list ORACLE-L Try this procedure: The extra db bounce did the trick for me. Don't ask me why. I think I found it on MetaLink or opened a TAR. HTH Gerardo SVRMGR> SHUTDOWN IMMEDIATE; -- or NORMAL SVRMGR> STARTUP MOUNT; SVRMGR> ALTER SYSTEM ENABLE RESTRICTED SESSION; SVRMGR> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0; SVRMGR> ALTER SYSTEM SET AQ_TM_PROCESSES=0; SVRMGR> ALTER DATABASE OPEN; SVRMGR> ALTER DATABASE CHARACTER SET ; SVRMGR> SHUTDOWN IMMEDIATE; SVRMGR> STARTUP RESTRICT; SVRMGR> SHUTDOWN IMMEDIATE; SVRMGR> STARTUP; -Original Message- Sent: Wednesday, October 31, 2001 9:02 AM To: Multiple recipients of list ORACLE-L Hi, We are planning to change out database charater set from US7ASCII to UTF8 and we followed folowing steps as specified in metalink... but still when i see the trace of controlfile it shows character set as US7ASCII.. Do i need to do export/import to change takes place. we have oracle 8.1.7.1.5 on WIN2K. SVRMGR> SHUTDOWN IMMEDIATE; -- or NORMAL SVRMGR> STARTUP MOUNT; SVRMGR> ALTER SYSTEM ENABLE RESTRICTED SESSION; SVRMGR> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0; SVRMGR> ALTER SYSTEM SET AQ_TM_PROCESSES=0; SVRMGR> ALTER DATABASE OPEN; SVRMGR> ALTER DATABASE CHARACTER SET ; SVRMGR> SHUTDOWN IMMEDIATE; -- OR NORMAL SVRMGR> STARTUP; Thanks -Harvinder -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Harvinder Singh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Molina, Gerardo INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Harvinder Singh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Molina, Gerardo INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Toad vs SQL Navigator
Title: RE: Toad vs SQL Navigator We have sqlnav here at my site, it's cool and all but pretty expensive. I really liked ezsql and am sad to see it go away (damn shame). I have a question, since it's no longer available for sale how illegal would it be for me to obtain a key from someone to use it? I mean I'd love to pay the 100 bucks for it, but there seems no way to get it and I don't want to pay for toad. Lets say some independent record company/band makes a great album and then 2 years later the band breaks up and the company dissolves. Since there are no means for me to purchase the album anymore, is it illegal to get a copy from a friend to enjoy it? Instead of sqlnav I'd recommend getting pl/sql developer from Allround Automations www.allroundautomations.nl. I first heard about it from Christopher Spence and when I checked it out myself it does everything that sqlnav will do. The great thing is that it's only 150 bucks! I'm sure there might be a couple of features in SQLNav that pl/sql developer lacks but IMHO it definitely wouldn't justify the price difference. Later, Ivan -Original Message- From: Cale, Rick T (Richard) [mailto:[EMAIL PROTECTED]] Sent: Thursday, November 01, 2001 10:56 AM To: Multiple recipients of list ORACLE-L Subject: Toad vs SQL Navigator Hi All, We are looking at purchasing TOAD or SQL Navigator from Quest. I think they have purchase EZSQL also which I liked(good and cheap). I guess there goal is to eliminate the competition. I have some experience with free version of TOAD but not with SQL Navigator. Can someone share there pros/cons,why purchase one over the other, etc. if they have used both of these products? Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Toad vs SQL Navigator
Take a look at PL/SQL Developer from All Around Automations, much better than both of them, and a lot cheaper. But Toad is kind of a better product for the money (750 vrs 5000). Navigator is severely overpriced. "Do not criticize someone until you walked a mile in their shoes, that way when you criticize them, you are a mile a way and have their shoes." Christopher R. Spence Oracle DBA Phone: (978) 322-5744 Fax:(707) 885-2275 Fuelspot 73 Princeton Street North, Chelmsford 01863 -Original Message- Sent: Thursday, November 01, 2001 10:56 AM To: Multiple recipients of list ORACLE-L Hi All, We are looking at purchasing TOAD or SQL Navigator from Quest. I think they have purchase EZSQL also which I liked(good and cheap). I guess there goal is to eliminate the competition. I have some experience with free version of TOAD but not with SQL Navigator. Can someone share there pros/cons,why purchase one over the other, etc. if they have used both of these products? Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Christopher Spence INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Is this script correct ?
Try set serveroutput on size 100 declare -- V_SP NUMBER(32) := NULL; V_FB NUMBER(32) := NULL; V_PF NUMBER(32,2) := NULL; -- BEGIN select to_number(value) into V_SP from v$parameter where name = 'shared_pool_size'; select sum(bytes) into V_FB from v$sgastat where name = 'free memory'; V_PF := (V_FB/V_SP)*100; dbms_output.put_line(V_SP||' Shared Pool Size '); dbms_output.put_line(V_FB||' Free Bytes '); dbms_output.put_line(V_PF||'% Free '); END; Raymond Lee Meng HongTo: Multiple recipients of list ORACLE-L @infopro.com.cc: my> Subject: Re: Is this script correct ? Sent by: root 11/01/2001 03:10 AM Please respond to ORACLE-L Hei , is this script correct to determint my share spool size ??? But why it give me 3 row instead of 1 ??? My DB is 8.1.7 running in win2kpro 128 RAM.. col value for 999,999,999,999 heading Shared Pool Size col bytes for 999,999,999,999 heading Free Bytes select to_number(v$parameter.value) value, v$sgastat.bytes, (v$sgastat.bytes/v$parameter.value)*100 PercentFree from v$sgastat, v$parameter where v$sgastat.name = 'free memory' and v$parameter .name = 'shared_pool_size' / 700 3921045.60148571428571 700 6144008.77714285714286 700 32768 0.468114285714286 Raymond Lee Infopro Sdn Bhd "Do not criticize someone until you walked a mile in their shoes, that way when you criticize them, you are a mile a way and have their shoes." -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Raymond Lee Meng Hong INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: OT - close your eyes and delete - RE: RAM Disks, redo logs, a
Mark, I don't get it...isnt' the ELSA an AGP ver2 card? This game should run in video memory for the most part, plus computations...and an occasional load of new textures/templates etc. How odd. (maybe move this to Oracle OT?) - Ross -Original Message- Sent: Thursday, November 01, 2001 10:51 AM To: Multiple recipients of list ORACLE-L logs, and b I'd love to get a RAMDISK in to my gaming machine - P4 1.4 256Mb RDRAM 40 gig UDMA100 64Mb Geforce 3 (ELSA GLADIAC 921) Thinking of upgrading the hard drive at the moment to 60/80 gig.. Hmmm.. Anyone got any good articles etc for RAMDISKS? I'd like to read up on them a little more.. BTW: The above mentioned machine is ONLY bound by the speed of its hard drive.. Though I am still thinking of adding an extra 256Mb of RDRAM :) Mark -Original Message- Sent: Thursday, November 01, 2001 15:16 To: Multiple recipients of list ORACLE-L b Games are usually CPU bound...or system bus bound (vid card to mobo, natch ). -Original Message- Sent: Wednesday, October 31, 2001 12:02 PM To: Multiple recipients of list ORACLE-L Hey Walt, How about using the RAMDISK on your gaming pc. I'll bet it would scream. ROR mª¿ªm -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ron Rogers INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohan, Ross INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mark Leith INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohan, Ross INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Oracle Financials DBA Team Leader Needed in Chicago..
We are searching for an Oracle Financials DBA Team Leader for a client located in downtown Chicago. The individual will perform DBA and supervisory duties for Oracle Financials Systems. Only candidates who already live in the Greater Chicago area can be considered. This is a full time staff position so no sub-contractors or third parties please. Please do not call or send a resume if you are not in the U.S. and/or need sponsorship. * Requirements: We would like someone with 3 or more years of experience as an Oracle DBA and at least 1 year of recent experience as an Oracle Financials DBA. This is a senior level position, requiring 6-8 years of overall IT experience. Person should have experience as a team lead or supervisor. Should have significant experience working in a Unix environment. Must be current US Citizen or permanent resident. This positions offer: * Stability * The opportunity to become a key member of the team. * Base salary-up to 95K + excellent benefits PLEASE do not send your resume if you are not in the United States. For immediate consideration, please send your resume as a Word attachment to: OraStaff, Inc. Email: [EMAIL PROTECTED] ph: 1-800 -549-8502 Please use job code: One/Chicago/Fin DBA Leader/Alan All Submissions are handled in confidence. *We pay referral fees. So please contact me if you know of anyone who would be qualified/interested in the posiition described above- if it is not a match for your skills. Thanks, Bill Law -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: OraStaff INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Data Deletion in Tables with Foreign Keys
Erik - Another suggestion that has been mentioned on this forum is to
consider partitioning. You could partition by insertion date, with each
partition holding a month of data. Then you could clean up by truncating the
oldest partition. Without indexes, any use of these tables will probably be
table scans, so you may receive a performance bonus for queries that only
need data within a certain date range.
Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED]
-Original Message-
Sent: Thursday, November 01, 2001 10:00 AM
To: Multiple recipients of list ORACLE-L
That is an excellent suggestion! I had not thought of that.
Those tables are populated with data as a result of web site interactions.
Inserts need to be as fast as possible. There were no indexes added due to
the overhead of maintaining an associated index.
Thanks again for the suggestion. I am going to look into that this
afternoon.
Erik
> -Original Message-
> From: [EMAIL PROTECTED] [SMTP:[EMAIL PROTECTED]]
> Sent: Thursday, November 01, 2001 10:10 AM
> To: Multiple recipients of list ORACLE-L
> Subject: Re:Data Deletion in Tables with Foreign Keys
>
> Erik,
>
> First off, do the foreign keys have the 'on delete cascade' option
> turned
> on? If not then do so as it makes keeping things in sync much easier.
> Actually
> in this scenario you don't have to worry about the child tables.
>
> Second, what do you mean by "The design of the application prohibits
> me from
> adding indexes to these tables. "? I've not seen any application that
> 'prohibits' adding indexes.
>
> Dick Goulet
>
> Reply Separator
> Author: Erik Williams <[EMAIL PROTECTED]>
> Date: 11/1/2001 5:45 AM
>
> I need to prune data from a set of tables every day. I need to retain the
> last 90 days of information. Two of the tables, A and B, have foreign keys
> to a third, C. I cannot disable the constraints prior to deleting the
> data,
> because the system is 24/7. I have created a script that will delete the
> data from each of the tables with foreign keys first, then from the parent
> table. The problem I am having is the time it is taking to perform the
> deletions. The A and B tables are without indexes on the foreign key,
> because they very high volume insertion tables and very infrequent lookup.
> These tables are very large. The design of the application prohibits me
> from
> adding indexes to these tables.
>
> Here is the code:
>
> set serveroutput on
> set timing on
>
> DECLARE
> id number(15);
> dtm date;
> cnt number;
> cursor purge_c is
> select id
> from C
> where dtm < sysdate-90;
> BEGIN
> open purge_c;
> fetch purge_c into id;
> cnt := 0;
> while (purge_c%FOUND) loop
> cnt := cnt + 1;
> delete from A where id = id;
> delete from B where id = id;
> delete from C where id = id;
> commit;
> fetch purge_c into id;
> end loop;
> close purge_c;
> DBMS_OUTPUT.PUT_LINE('Number of sessions deleted: ' || cnt);
> END;
> /
>
> set timing off
> set serveroutput off
>
>
> I was thinking about creating another loop so that commits will only be
> done
> every 1000 deletions, but I think that the commits are a very a small
> percentage of the time compared to the table scans. I also considered
> partitioned tables, but I really don't want to go to that length. I was
> hoping to hear how other people handle this issue.
>
> Thanks.
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Erik Williams
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing Lists
>
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author:
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing Lists
>
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
-
RE: Toad vs SQL Navigator
Our developers use both products. Light Oracle developers, including some power end-users, get by just fine with TOAD. High level folks, those into heavy PL/SQL, swear by SQL Nav. Gary Weber Senior DBA Charles Jones, LLC||Superior Information Services, LLC 609-530-1144, ext 5529 -Original Message- T (Richard) Sent: Thursday, November 01, 2001 10:56 AM To: Multiple recipients of list ORACLE-L Hi All, We are looking at purchasing TOAD or SQL Navigator from Quest. I think they have purchase EZSQL also which I liked(good and cheap). I guess there goal is to eliminate the competition. I have some experience with free version of TOAD but not with SQL Navigator. Can someone share there pros/cons,why purchase one over the other, etc. if they have used both of these products? Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gary Weber INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
OCP News for Military Veterans
Oracle Certification Gains GI And Veterans Approval REDWOOD SHORES, CA--(INTERNET WIRE)--Nov 01, 2001-- Oracle Corp.announced today that their education arm, Oracle(R) University, received notification from the United States Veterans Association that Oracle Professional Certifications are now part of the GI Bill and Dependents Educational Assistance reimbursement programs. All eligible veterans, their spouses and children can receive reimbursement for the costs of taking the Oracle Certification exams, seen as a requirement for many job opportunities. Oracle University is one of the largest and most innovative information technology (IT) professional education providers in the world, with more than 90,000 certified professionals. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ken Janusz INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Toad vs SQL Navigator
Why purchase TOAD over SQL Navigator? They both do basically the same thing, right??? You have direct access to Toadman Jim himself. You have input as to what you want to see in the product. Bugs get addressed *immediately*. New beta versions at least weekly. And Jim himself is one helluva human being. See for yourself: http://www.egroups.com/list/toad And the EZSQL author (my apologies to him, I forget his name) is on the TOAD team, if that means anything to ya. >From some people I've talked to at Quest, the users of each are fanatically loyal. I'm no different, I guess. Good luck! Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- Sent: Thursday, November 01, 2001 09:56 To: Multiple recipients of list ORACLE-L Hi All, We are looking at purchasing TOAD or SQL Navigator from Quest. I think they have purchase EZSQL also which I liked(good and cheap). I guess there goal is to eliminate the competition. I have some experience with free version of TOAD but not with SQL Navigator. Can someone share there pros/cons,why purchase one over the other, etc. if they have used both of these products? Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Backing up a database on WindowsNT
Not really but I guess I should consider it. Sounds a little better then waiting for CA to catch up. -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Wednesday, October 31, 2001 10:00 PM To: Multiple recipients of list ORACLE-L Kimberly, As an alternate suggestion, have you considered using RMAN with a media link into Arcserve to allow it to back up to tape directly? Regards, Bruce Reardon -Original Message- Sent: Thursday, 1 November 2001 8:55 To: Multiple recipients of list ORACLE-L I have two databases on Windows NT that are Oracle 8.1.7. ArcService is the method of backup for our NT Servers. We installed the Oracle Agent for backing up the database as I do not have space to copy it to disk first. One of the features we are using in the database is tempfile's with temporary tablespaces. I actually expect the temporary tablespace to be utilized quite a bit in one database so I don't want to resort to the older method. The installed copy CA Oracle Agent is unable to deal with this temp file and as a result it will not backup the control file. The fact that its not even supposed to back that tablespace up seems to be irrelevant. Has anyone been able to get this configuration to work? -- Oracle Database Administrator Fujitsu (503)669-6050 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Reardon, Bruce (CALBBAY) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kimberly Smith INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: OT - close your eyes and delete - RE: RAM Disks, redo logs, and b
I'd love to get a RAMDISK in to my gaming machine - P4 1.4 256Mb RDRAM 40 gig UDMA100 64Mb Geforce 3 (ELSA GLADIAC 921) Thinking of upgrading the hard drive at the moment to 60/80 gig.. Hmmm.. Anyone got any good articles etc for RAMDISKS? I'd like to read up on them a little more.. BTW: The above mentioned machine is ONLY bound by the speed of its hard drive.. Though I am still thinking of adding an extra 256Mb of RDRAM :) Mark -Original Message- Sent: Thursday, November 01, 2001 15:16 To: Multiple recipients of list ORACLE-L b Games are usually CPU bound...or system bus bound (vid card to mobo, natch ). -Original Message- Sent: Wednesday, October 31, 2001 12:02 PM To: Multiple recipients of list ORACLE-L Hey Walt, How about using the RAMDISK on your gaming pc. I'll bet it would scream. ROR mª¿ªm -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ron Rogers INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohan, Ross INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mark Leith INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re:RE: recover on different hardware
Dick, thank you -- how come I don't get invited to speak at NOUG anymore? :( Actually, I've decided that there should be a corollary to my Rule 0 -- while that backup is being made, go and have a cup of coffee and take a pad and paper with you and start to plan what to do. This presumes that you haven't already sat down and at least thought out what would happen in any given circumstance and planned for it :) I've found that whenever I just "rush in where angels fear to tread" I screw it up. Because I realize (as Barb did) that I should have done (or should not have done) something one second AFTER I hit the enter key. I like checklists. Hell, I LOVE checklists. I've submitted a presentation to IOUG - I hope they take it, as it may well be the last time I present, I'm thinking of hanging up my presenting shoes :) Rachel --- [EMAIL PROTECTED] wrote: > Barbara, > > Like Rachel I've been in the same place you are too, a couple of > times. In > every instance where I've been faced with recovering a database > and/or datafile > the worst part of the process is getting started. Namely figuring > out 1) what's > broke, 2) what do I have to fix it with, 3) where do I want to be in > the end. > > In a case similar to yours (the disk farm blew), we recovered the > previous > cold backup to a replacement system of the same type. The problem I > had was > that the cold backup was from the previous weekend and it was > Thursday with > somewhere around 100 archived redo logs to play with and the online > redo was > lost with the disk farm. The answer was simply to restore > everything, then > before starting the DB I copied the one remaining control file from > the old > system onto the new system, replacing all of the existing files with > this one. > Now the control files were all in sync with each other, but not with > the DB > files. When the DB was started it of course complained that it > needed recovery. > Recovered the DB with the 'until ' option so that I > consumed all of > the archived redo, but stopped short of the online redo, and reset > the logs. In > the end the DB opened normally with no problems, although it's uptime > was VERY > short, as I shut it down almost immediately and put a new backup of > what I had > in the safe. > > Once your over the initial sinking feeling in your stomach and > adrenaline > rush, it's a piece of cake. OH BTW, something we all should practice > every once > in a while. I know I do. > > Dick Goulet > > PS: if you get a chance, attend one of Rachel's presentations. It's > more than > well worth it. Actually change that to "make a chance". > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing > Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Make a great connection at Yahoo! Personals. http://personals.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Data Deletion in Tables with Foreign Keys
That is an excellent suggestion! I had not thought of that.
Those tables are populated with data as a result of web site interactions.
Inserts need to be as fast as possible. There were no indexes added due to
the overhead of maintaining an associated index.
Thanks again for the suggestion. I am going to look into that this
afternoon.
Erik
> -Original Message-
> From: [EMAIL PROTECTED] [SMTP:[EMAIL PROTECTED]]
> Sent: Thursday, November 01, 2001 10:10 AM
> To: Multiple recipients of list ORACLE-L
> Subject: Re:Data Deletion in Tables with Foreign Keys
>
> Erik,
>
> First off, do the foreign keys have the 'on delete cascade' option
> turned
> on? If not then do so as it makes keeping things in sync much easier.
> Actually
> in this scenario you don't have to worry about the child tables.
>
> Second, what do you mean by "The design of the application prohibits
> me from
> adding indexes to these tables. "? I've not seen any application that
> 'prohibits' adding indexes.
>
> Dick Goulet
>
> Reply Separator
> Author: Erik Williams <[EMAIL PROTECTED]>
> Date: 11/1/2001 5:45 AM
>
> I need to prune data from a set of tables every day. I need to retain the
> last 90 days of information. Two of the tables, A and B, have foreign keys
> to a third, C. I cannot disable the constraints prior to deleting the
> data,
> because the system is 24/7. I have created a script that will delete the
> data from each of the tables with foreign keys first, then from the parent
> table. The problem I am having is the time it is taking to perform the
> deletions. The A and B tables are without indexes on the foreign key,
> because they very high volume insertion tables and very infrequent lookup.
> These tables are very large. The design of the application prohibits me
> from
> adding indexes to these tables.
>
> Here is the code:
>
> set serveroutput on
> set timing on
>
> DECLARE
> id number(15);
> dtm date;
> cnt number;
> cursor purge_c is
> select id
> from C
> where dtm < sysdate-90;
> BEGIN
> open purge_c;
> fetch purge_c into id;
> cnt := 0;
> while (purge_c%FOUND) loop
> cnt := cnt + 1;
> delete from A where id = id;
> delete from B where id = id;
> delete from C where id = id;
> commit;
> fetch purge_c into id;
> end loop;
> close purge_c;
> DBMS_OUTPUT.PUT_LINE('Number of sessions deleted: ' || cnt);
> END;
> /
>
> set timing off
> set serveroutput off
>
>
> I was thinking about creating another loop so that commits will only be
> done
> every 1000 deletions, but I think that the commits are a very a small
> percentage of the time compared to the table scans. I also considered
> partitioned tables, but I really don't want to go to that length. I was
> hoping to hear how other people handle this issue.
>
> Thanks.
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Erik Williams
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing Lists
>
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author:
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing Lists
>
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Erik Williams
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). Yo
Toad vs SQL Navigator
Hi All, We are looking at purchasing TOAD or SQL Navigator from Quest. I think they have purchase EZSQL also which I liked(good and cheap). I guess there goal is to eliminate the competition. I have some experience with free version of TOAD but not with SQL Navigator. Can someone share there pros/cons,why purchase one over the other, etc. if they have used both of these products? Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: CHANGING CHARACTER SET
Gerardo, The trick works for me also but when i try to export it shows as: Connected to: Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production With the Partitioning option JServer Release 8.1.7.0.0 - Production Export done in US7ASCII character set and US7ASCII NCHAR character set server uses UTF8 character set (possible charset conversion) Do we are able to change character set or our existing data is still using US7ASCII. Thanks -Harvinder -Original Message- Sent: Thursday, November 01, 2001 2:00 AM To: Multiple recipients of list ORACLE-L Try this procedure: The extra db bounce did the trick for me. Don't ask me why. I think I found it on MetaLink or opened a TAR. HTH Gerardo SVRMGR> SHUTDOWN IMMEDIATE; -- or NORMAL SVRMGR> STARTUP MOUNT; SVRMGR> ALTER SYSTEM ENABLE RESTRICTED SESSION; SVRMGR> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0; SVRMGR> ALTER SYSTEM SET AQ_TM_PROCESSES=0; SVRMGR> ALTER DATABASE OPEN; SVRMGR> ALTER DATABASE CHARACTER SET ; SVRMGR> SHUTDOWN IMMEDIATE; SVRMGR> STARTUP RESTRICT; SVRMGR> SHUTDOWN IMMEDIATE; SVRMGR> STARTUP; -Original Message- Sent: Wednesday, October 31, 2001 9:02 AM To: Multiple recipients of list ORACLE-L Hi, We are planning to change out database charater set from US7ASCII to UTF8 and we followed folowing steps as specified in metalink... but still when i see the trace of controlfile it shows character set as US7ASCII.. Do i need to do export/import to change takes place. we have oracle 8.1.7.1.5 on WIN2K. SVRMGR> SHUTDOWN IMMEDIATE; -- or NORMAL SVRMGR> STARTUP MOUNT; SVRMGR> ALTER SYSTEM ENABLE RESTRICTED SESSION; SVRMGR> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0; SVRMGR> ALTER SYSTEM SET AQ_TM_PROCESSES=0; SVRMGR> ALTER DATABASE OPEN; SVRMGR> ALTER DATABASE CHARACTER SET ; SVRMGR> SHUTDOWN IMMEDIATE; -- OR NORMAL SVRMGR> STARTUP; Thanks -Harvinder -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Harvinder Singh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Molina, Gerardo INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Harvinder Singh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: 11.5.4 Easy Question (?)
Well, yes and no. 1) Database up 2) Listener up 3) Log in as Apps person (hence source in appsora.env) 4) Other processes listed -Original Message- Sent: Thursday, November 01, 2001 8:10 AM To: Multiple recipients of list ORACLE-L Are you starting everything up in the proper order ? - Database first (8.1.?) - 8.1.? listener - running your applications environment file (APPSORA.env) - the other processes you listed Just a guess. -Original Message- Sent: Wednesday, October 31, 2001 3:55 PM To: Multiple recipients of list ORACLE-L Friends -- Our pesky users are having problems logging on from the Web front end of Oracle Financials. This was working just fine 10/25 when the last pesky user successfully logged in. Since then, the box (Tru64 v5.1) has been bounced, and we have started the following scripts successfully... 1) addbctl.sh (database startup) 2) addlnctl.sh (listener startup... you'd think Oracle would use its own conventions, wouldn't you?) 3) adalnctl.sh (applications listener) 4) adcmctl.sh (concurrent manager startup) 5) adrepctl.sh (reports server) 6) adfrmctl.sh (forms server) 7) adtcfctl.start (tcf sockets) 8) adapcctl.sh (Apache web process) 9) adfmsctl.sh (forms metric server) 10) adfmcctl.sh (forms metric client) I figure if the database is up, the listeners are up and concurrent manager is up, everything should be fine. What am I missing? TIA! Bambi. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bellows, Bambi INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jerry Hess INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bellows, Bambi INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: RAM Disks, redo logs, and beer
Cheers... the SDS stuff looks to be (at least as of a year ago) the best in the market. I knew a really nice, bright guy who worked with them in that timeframe, Mark Hayakashi, i think The utilities for file layout/io are good, the idea is great, but set expectations low. If you get a 10x improvement in some key area, consider it a job well done. If 100x, put a little bit extra in the collection plate. in *any* case, having the SDS dog and pony routine will tell you more about your file/io/sql and that ain't bad. Keep those cards and letters coming! - Ross -Original Message- Sent: Wednesday, October 31, 2001 11:31 AM To: Multiple recipients of list ORACLE-L Thanks for the info, Ross. We have a tablespace set up on the RAM disk, we know what the hot files are, and we've moved some of'em to the RAM disk (it's not big enough to move all of'em there). Now we're just gonna sit back and watch all the great performance stats roll in. :>) FWIW, the SQL is doggy. It's developed on MySQL and ported to Oracle with very little change. Kind of ugly. But, the RAM disk was available so we figured we'd take advantage of it. Thanks, --Walt -Original Message- Sent: Wednesday, October 31, 2001 7:35 AM To: Multiple recipients of list ORACLE-L Walt, The SDS guys have a few neat utilities that'll help you decide what your hot files are...with some DBA magic, you can figure out what your hot tables in those files are. Make a new, small "hot" tablespace, and put it on the ramdisk. And yes, with all the failsafes built into the ramdisk by SDS, i'd skip the duplex. Benchmark it all, pre and post, because your throughput/bottleneck and app performance will change alot and you'll want to know "how". Oh, and...if your SQL is doggy (multitable joins...sorting...etc...) it may well be that you'll become compute bound, and no amount of disk will save you. Please keep us posted! good luck - Ross Mohan -Original Message- Sent: Tuesday, October 30, 2001 4:51 PM To: Multiple recipients of list ORACLE-L Anybody out there ever use, or are using, RAM disks? We recently purchased a 2gb RAM disk from Solid Data Systems, originally to put some of our larger MySQL databases on it in hopes of increasing the performance. Since speed wasn't really the problem we're having with MySQL it didn't do any good. SO, we attached it to one of our Oracle database servers this weekend. I've moved the customer database redo logs to the RAM disk and am hoping for wonderful things to happen once we put a load on the machine. Since I don't know a damn thing about RAM disks I was hoping someone could give me some pointers on correct RAM disk etiquette. I'm duplexing the redo logs and both plexes are on the RAM disk. During periods of high usage will this cause the RAM disk to overheat? Will it get confused? Will it roll over and die? Should I ignore everything my mother ever taught me about redo logs and de-duplex the suckers? Also, the redo logs take up about 250mb. The RAM disk holds 2gb, and since the little guy put us back about US$29,000, we would sure like to use the rest of the space. We're thinking about putting some of the more active tables on the RAM disk. Anybody ever do something like this? Any suggestions on what not to do? I'll also be talking to the Solid Data Systems engineers to get their input, but I'd like to hear from the people who actually use the things. Thanks, --Walt Weaver Bozeman, Montana P.S. The beer part of this message was rerouted to ORACLE-L-OT. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Weaver, Walt INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohan, Ross INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Weaver, Walt INET: [EMAIL PROTECTED] Fat City Network
Re:Data Deletion in Tables with Foreign Keys
Erik,
First off, do the foreign keys have the 'on delete cascade' option turned
on? If not then do so as it makes keeping things in sync much easier. Actually
in this scenario you don't have to worry about the child tables.
Second, what do you mean by "The design of the application prohibits me from
adding indexes to these tables. "? I've not seen any application that
'prohibits' adding indexes.
Dick Goulet
Reply Separator
Author: Erik Williams <[EMAIL PROTECTED]>
Date: 11/1/2001 5:45 AM
I need to prune data from a set of tables every day. I need to retain the
last 90 days of information. Two of the tables, A and B, have foreign keys
to a third, C. I cannot disable the constraints prior to deleting the data,
because the system is 24/7. I have created a script that will delete the
data from each of the tables with foreign keys first, then from the parent
table. The problem I am having is the time it is taking to perform the
deletions. The A and B tables are without indexes on the foreign key,
because they very high volume insertion tables and very infrequent lookup.
These tables are very large. The design of the application prohibits me from
adding indexes to these tables.
Here is the code:
set serveroutput on
set timing on
DECLARE
id number(15);
dtm date;
cnt number;
cursor purge_c is
select id
from C
where dtm < sysdate-90;
BEGIN
open purge_c;
fetch purge_c into id;
cnt := 0;
while (purge_c%FOUND) loop
cnt := cnt + 1;
delete from A where id = id;
delete from B where id = id;
delete from C where id = id;
commit;
fetch purge_c into id;
end loop;
close purge_c;
DBMS_OUTPUT.PUT_LINE('Number of sessions deleted: ' || cnt);
END;
/
set timing off
set serveroutput off
I was thinking about creating another loop so that commits will only be done
every 1000 deletions, but I think that the commits are a very a small
percentage of the time compared to the table scans. I also considered
partitioned tables, but I really don't want to go to that length. I was
hoping to hear how other people handle this issue.
Thanks.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Erik Williams
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
OT - close your eyes and delete - RE: RAM Disks, redo logs, and b
Games are usually CPU bound...or system bus bound (vid card to mobo, natch ). -Original Message- Sent: Wednesday, October 31, 2001 12:02 PM To: Multiple recipients of list ORACLE-L Hey Walt, How about using the RAMDISK on your gaming pc. I'll bet it would scream. ROR mª¿ªm -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ron Rogers INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohan, Ross INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re:RE: recover on different hardware
Barbara, Like Rachel I've been in the same place you are too, a couple of times. In every instance where I've been faced with recovering a database and/or datafile the worst part of the process is getting started. Namely figuring out 1) what's broke, 2) what do I have to fix it with, 3) where do I want to be in the end. In a case similar to yours (the disk farm blew), we recovered the previous cold backup to a replacement system of the same type. The problem I had was that the cold backup was from the previous weekend and it was Thursday with somewhere around 100 archived redo logs to play with and the online redo was lost with the disk farm. The answer was simply to restore everything, then before starting the DB I copied the one remaining control file from the old system onto the new system, replacing all of the existing files with this one. Now the control files were all in sync with each other, but not with the DB files. When the DB was started it of course complained that it needed recovery. Recovered the DB with the 'until ' option so that I consumed all of the archived redo, but stopped short of the online redo, and reset the logs. In the end the DB opened normally with no problems, although it's uptime was VERY short, as I shut it down almost immediately and put a new backup of what I had in the safe. Once your over the initial sinking feeling in your stomach and adrenaline rush, it's a piece of cake. OH BTW, something we all should practice every once in a while. I know I do. Dick Goulet PS: if you get a chance, attend one of Rachel's presentations. It's more than well worth it. Actually change that to "make a chance". -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: recover on different hardware
De nada chica... glad you had a backup of the files to work with --- "Baker, Barbara" <[EMAIL PROTECTED]> wrote: > Rachel, Paul: > Ya, that was it. > I re-copied all the files and this time did NOT open normally. > Recovery completed. > I'm outta here. > > Thanks for your help! > Barb > > > > -- > > From: Rachel Carmichael[SMTP:[EMAIL PROTECTED]] > > Reply To: [EMAIL PROTECTED] > > Sent: Thursday, November 01, 2001 2:50 AM > > To: Multiple recipients of list ORACLE-L > > Subject:Re: recover on different hardware > > > > Barb, > > > > What Paul said -- Do NOT open the database normally. If you use the > > backup controlfile, Oracle presumes that the SCN's in the headers > may > > or may not be in sync and will allow you to recover past the SCN in > the > > controlfile. Presumes it's "fuzzy". > > > > You have my sympathies, I've been there, done that and it ain't > pretty. > > > > let us know how it went > > > > Rachel > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Baker, Barbara > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing > Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Make a great connection at Yahoo! Personals. http://personals.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: 10.7 on sequent to 11i on sun
Garreth, dynix/ptx is being EOL'ed as of current release, i think. i mean, it's rock solid and will be around for years working well, but... thought you'd like to know. hth Ross -Original Message- Sent: Thursday, November 01, 2001 7:10 AM To: Multiple recipients of list ORACLE-L > We have a cust on 10.7 on Sequent box. We want to go upgrade to 11i on Sun > box. > > Options we have i think - > > a) upgrade to 11i on Sequent (upgrade database to 8.1.7) then export > import onto sun box (empty 11i system) > > b) install 10.7 on sun, export /import system from sequent into 10.7 > system on sun and then upgrade to 11i. > > If option B is done do we need to install all the y2k patches etc on the > 10.7 sun to have a working system or can we ignore them and use it only as > a temp stepping stone in the upgrade process. > > Please advise, or suggest if there are any other ways. > > Many thanks. > > Garreth Reed > Database Administrator, ICL > > E-mail:[EMAIL PROTECTED] > > This e-mail is intended for the addressee named above. As this e-mail may > contain confidential or privileged information if you are not, or suspect > you are not, the named addressee or the person responsible for delivering > the message to the named addressee, please telephone me immediately. > Please note that we cannot guarantee that this message or any attachment > is virus free or has not been intercepted and amended. The views of the > author may not necessarily reflect those of the Company. > > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Reed Garreth INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohan, Ross INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Oracle on NT startup question
I'm sorry. I thought that you mounted a database and that the instance was the area in memory (corresponding to the SGA and PGA) that Oracle used. So what is the instance? Denham Eva <[EMAIL PROTECTED]> Sent by: rootcc: Subject: RE: Oracle on NT startup question 11/01/2001 12:25 AM Please respond to ORACLE-L I am not sure I understand you correctly, but on NT Oracle runs as a service and an instance(the mounted database). The service must be started for the instance to be able to mount and connected to. However the service can be started without having to mount the instance. HTH Denham -Original Message- Sent: Wednesday, October 31, 2001 3:50 PM To: Multiple recipients of list ORACLE-L My understanding is that the instance is the service. Can you start the instance without a mounted database? Andrey Bronfin To: Multiple recipients of list ORACLE-L @elrontelesof cc: t.com> Subject: Oracle on NT startup question Sent by: root 10/31/2001 04:35 AM Please respond to ORACLE-L Dear list ! If my NT server (which runs an Oracle instance) gets rebooted accidentally, how can i bring up the Oracle services without starting up the instance ? I want to startup the instance with a script later . I'm interested in an answer for both Oracle 8.0.5 and 8.1.7 on NT4. Thanks in advance. DBAndrey * 03-9254520 * 053-464562 * mailto:[EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andrey Bronfin INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: recover on different hardware
Barbara, I'm going to take a bit of a guess here - but this should work: set aside the control files used when the database was opened normally. grab only the control files from the cold backup set (ideally a backup control file) or earlier and re-attempt recovery. I'm assuming that the size of the database is large. If not, restore the entire set and attempt recovery again. This is where snapshots on a higher-end storage volume are pretty sweet. hth, Paul "Baker, Barbara" wrote: > > Doh! > Your message came minutes after I'd already opened the database. > When I attempt the recovery I get the message > SVRMGR> recover database until cancel using backup controlfile; > ORA-00279: change 278038264 generated at 11/01/01 01:28:13 needed > for thread 1 > ORA-00289: suggestion : > /u14/oradata/ent/arch/arch.logentarch16551.dbf > ORA-00280: change 278038264 for thread 1 is in sequence #16551 > ORA-00283: recovery session canceled due to errors > ORA-00356: inconsistent lengths in change description > ORA-00353: log corruption near block 225 change 29240139592704 time > 10/31/01 01: > > Do you think this is because I did indeed already open the database > normally? Or do you think it's cuz I really do have bad archive files?? > > Thanks for your response. > Much appreciated. > Barb > > > -- > > From: Paul Drake[SMTP:[EMAIL PROTECTED]] > > Reply To: [EMAIL PROTECTED] > > Sent: Thursday, November 01, 2001 1:05 AM > > To: Multiple recipients of list ORACLE-L > > Subject: Re: recover on different hardware > > > > otn - http://tahiti.oracle.com/pls/tahiti/tahiti.homepage > > this site really sucks bad - half the links are broken. wtf? > > its 8.1.6 that's off of correction, not 8.1.7. > > my copy of 8i Backup recovery handbook is at the office, and the docs > > are not available online. > > > > > > most important: > > don't open the database normally after the restore > > > > startup mount exclusive > > > > > > set autorecovery on > > recover database until cancel using backup controlfile; > > cancel > > alter database open resetlogs > > shutdown > > cold backup > > > > note: apparently, any read-only tablespaces should be offline while > > recovering using a backup controlfile. > > I had never heard of this, but stumbled across it while perusing the > > docs. > > > > > > "Baker, Barbara" wrote: > > > > > > Been here 18 hours. No database. Looks grim. > > > Hardware blew chunks. Major ugly. > > > Plan is to reconstruct entire system on new hardware. > > > If I get my dbf files from last night's cold backup (as well as all my > > > config files), I should have a database that looks just like last night > > at > > > 11:00 pm. > > > DB should come up clean, should think it's a happy camper. > > > > > > I do have most of my archived redo logs from today. What recovery > > option do > > > I use to start applying the redo logs I've been able to salvage? (Guess > > I'm > > > confused cuz the database will not think it needs recovery.) > > > > > > I'm about to RTFM, but I'm a bit bleary-eyed. > > > Any assistance greatly appreciated. > > > > > > Thanks! > > > Barb > > > > > > -- > > > Please see the official ORACLE-L FAQ: http://www.orafaq.com > > > -- > > > Author: Baker, Barbara > > > INET: [EMAIL PROTECTED] > > > > > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > > > San Diego, California-- Public Internet access / Mailing Lists > > > > > > To REMOVE yourself from this mailing list, send an E-Mail message > > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > > > the message BODY, include a line containing: UNSUB ORACLE-L > > > (or the name of mailing list you want to be removed from). You may > > > also send the HELP command for other information (like subscribing). > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.com > > -- > > Author: Paul Drake > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > > San Diego, California-- Public Internet access / Mailing Lists > > > > To REMOVE yourself from this mailing list, send an E-Mail message > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > > the message BODY, include a line containing: UNSUB ORACLE-L > > (or the name of mailing list you want to be removed from). You may > > also send the HELP command for other information (like subscribing). > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Baker, Barbara > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists >
Data Deletion in Tables with Foreign Keys
I need to prune data from a set of tables every day. I need to retain the
last 90 days of information. Two of the tables, A and B, have foreign keys
to a third, C. I cannot disable the constraints prior to deleting the data,
because the system is 24/7. I have created a script that will delete the
data from each of the tables with foreign keys first, then from the parent
table. The problem I am having is the time it is taking to perform the
deletions. The A and B tables are without indexes on the foreign key,
because they very high volume insertion tables and very infrequent lookup.
These tables are very large. The design of the application prohibits me from
adding indexes to these tables.
Here is the code:
set serveroutput on
set timing on
DECLARE
id number(15);
dtm date;
cnt number;
cursor purge_c is
select id
from C
where dtm < sysdate-90;
BEGIN
open purge_c;
fetch purge_c into id;
cnt := 0;
while (purge_c%FOUND) loop
cnt := cnt + 1;
delete from A where id = id;
delete from B where id = id;
delete from C where id = id;
commit;
fetch purge_c into id;
end loop;
close purge_c;
DBMS_OUTPUT.PUT_LINE('Number of sessions deleted: ' || cnt);
END;
/
set timing off
set serveroutput off
I was thinking about creating another loop so that commits will only be done
every 1000 deletions, but I think that the commits are a very a small
percentage of the time compared to the table scans. I also considered
partitioned tables, but I really don't want to go to that length. I was
hoping to hear how other people handle this issue.
Thanks.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Erik Williams
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: 11.5.4 Easy Question (?)
Are you starting everything up in the proper order ? - Database first (8.1.?) - 8.1.? listener - running your applications environment file (APPSORA.env) - the other processes you listed Just a guess. -Original Message- Sent: Wednesday, October 31, 2001 3:55 PM To: Multiple recipients of list ORACLE-L Friends -- Our pesky users are having problems logging on from the Web front end of Oracle Financials. This was working just fine 10/25 when the last pesky user successfully logged in. Since then, the box (Tru64 v5.1) has been bounced, and we have started the following scripts successfully... 1) addbctl.sh (database startup) 2) addlnctl.sh (listener startup... you'd think Oracle would use its own conventions, wouldn't you?) 3) adalnctl.sh (applications listener) 4) adcmctl.sh (concurrent manager startup) 5) adrepctl.sh (reports server) 6) adfrmctl.sh (forms server) 7) adtcfctl.start (tcf sockets) 8) adapcctl.sh (Apache web process) 9) adfmsctl.sh (forms metric server) 10) adfmcctl.sh (forms metric client) I figure if the database is up, the listeners are up and concurrent manager is up, everything should be fine. What am I missing? TIA! Bambi. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bellows, Bambi INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jerry Hess INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Oracle 7 documentation - Good One
Title: RE: Oracle 7 documentation - Good One We currently have a grand total 138 databases, (and more on the way on what seems like a weekly basis) with a active DBA support team of about 12, but some are part time and many are dedicated to special projects. Matt Adams - GE Appliances - [EMAIL PROTECTED] It will make sense when you stop thinking logically, and start thinking Oracle-ly - Jim Droppa > -Original Message- > From: Boivin, Patrice J [mailto:[EMAIL PROTECTED]] > Sent: Thursday, November 01, 2001 8:10 AM > To: Multiple recipients of list ORACLE-L > Subject: RE: Oracle 7 documentation - Good One > > > We are running some 7.3 servers, are in the process of upgrading. > > Matthew, out of curiosity, how many DBAs and operators do you have to > support 44 instances? > > Regards, > Patrice Boivin > Systems Analyst (Oracle Certified DBA) > > -Original Message- > From: Adams, Matthew (GEA, 088130) > [SMTP:[EMAIL PROTECTED]] > Sent: Thursday, November 01, 2001 8:50 AM > To: Multiple recipients of list ORACLE-L > Subject: RE: Oracle 7 documentation - Good One > > We have 44 instances still running one of > 8 versions of Oracle7, ranging from 7.2.2.3 > to 7.3.4.4. > > They don't give me as many headaches as some of > my 8.0 and 8.1 instances > > > Matt Adams - GE Appliances - [EMAIL PROTECTED] > It will make sense when you stop thinking logically, > and start thinking Oracle-ly - Jim Droppa > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Boivin, Patrice J > INET: [EMAIL PROTECTED] > > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California -- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). >
RE: Oracle 7 documentation - Good One
Deepak, We still have two databases (out of 65) that are 7.3.4. These applications cannot be upgraded so the databases are going to be at 7.3.4 until the applications are "sunsetted" next year. Cherie Machler Oracle DBA Gelco Information Network Deepak Thapliyal yahoo.com> cc: Sent by:Subject: RE: Oracle 7 documentation - Good One [EMAIL PROTECTED] 10/31/01 01:55 PM Please respond to ORACLE-L no offense.. just a sincere question. are people still using o7 out there? talking to oracle support regarding ECS must a be real pain in the butt as i guess they might have stopped supplying patches for o7. in our shop people are jittery continuing on 816 as its de-supported from TODAY (officially with no ECS support .. just workarounds) and we are thinking of hopping on 817 until 9.2 or 9.3 is released and proves stable just some thoughts... as i was pleasently surprised looking the the hits the o7 doc thread has recieved Deepak PS: ok now no holy war here please; --- "Eric D. Pierce" <[EMAIL PROTECTED]> wrote: > Are you sure? > > I wasted time trying to find some minor > documentation > (platform specific release notes?) there a while > ago, and > after complaining that I couldn't find it, others > said > everything *isn't* there. > > Frustrating considering that they have a huge number > of > "CD packs" etc that appear to be small incrementals. > > It does appear that all the major documentation is > there. > > brgrds, > ep > > ORACLE-L Digest -- Volume 2001, Number 304 > > -- > > > > From: [EMAIL PROTECTED] > > Date: Tue, 30 Oct 2001 09:21:35 -0800 > > Subject: RE: Oracle 7 documentation - Good One > > > > Thanks for sharing this! > > > > Finally, complete documentation for Oracle 7 on. > ... > > ---original--- > > |> Dear All, > |> I hit upon this site. I thought its worth sharing > |> http://docs.oracle.com/ > > > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: Eric D. Pierce > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: > (858) 538-5051 > San Diego, California-- Public Internet > access / Mailing Lists > > To REMOVE yourself from this mailing list, send an > E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of > 'ListGuru') and in > the message BODY, include a line containing: UNSUB > ORACLE-L > (or the name of mailing list you want to be removed > from). You may > also send the HELP command for other information > (like subscribing). __ Do You Yahoo!? Make a great connection at Yahoo! Personals. http://personals.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deepak Thapliyal INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yours
RE: Oracle 7 documentation - Good One
Hi All, It's nice to learn that there are so many Oracle 7 database's still out there . It does not give me a Complex any longer . Oracle 7.3.4.4.1 DBA and Oracle 8i's tinkrer . Probably I am already outdated with Oracle 9i out in the market . Shreepad -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: bizarre AQ problem
7.3.2? baby now 5, THERE was a real geek's database --- Guy Hammond <[EMAIL PROTECTED]> wrote: > I submitted a TAR and Oracle seem to think it's because the shared > pool > is too small. Hmmm. Upping it did seem to resolve the problem, but... > this is a scenario in which a bit of Oracle functionality just stops > working, without throwing an exception from the trigger code, nor > even > making an entry in the alert log. I wonder what else is lurking under > the hood. > > 8.1.6 is more or less desupported now, which is annoying. I miss > 7.3.2, > now *there* was a real man's database :0) > > g > > > -Original Message- > Sent: 30 October 2001 18:05 > To: Multiple recipients of list ORACLE-L > > > Cool! Sounds like a buffer thing. > > what is AVG_ROW_LEN on table? > what is event, p1,p2,p3 for that sid in v$session_wait? > what if table is EMPTY ( well, ok...put in faux PKs, at least) > and then, there are all the AQ settings to consider > > > -Original Message- > Sent: Tuesday, October 30, 2001 4:35 AM > To: Multiple recipients of list ORACLE-L > > > Hello, > > I'm having a very strange AQ problem, and I can't for the life of me > work out what's going wrong. The platform is Solaris 2.6 and 2.8, > Oracle > 8.1.7.2, C++ OCI application compiled with SunPro. There is a table > with > about a dozen columns, one of which is a primary key. The application > does a SELECT on the primary key column, then reads these into an > array. > Then, for each element in this array it loops, doing SELECT * FROM > table > WHERE primarykey = element. We have isolated the problem to this > specific bit of code. > > If there are <700 rows in this table, or if the first query adds ' > WHERE > rownum < 700', everything works exactly as you would expect. If it > tries > to retrieve more than 700... the AQs for that schema just stop > working, > until the database is restarted. Has anyone come across something > like > this before? I can't understand how SELECT could cause this (there > are > triggers on this table for INSERT, UPDATE and DELETE, each of which > does > submit a message to an AQ). > > Thanks, > > g > > > > -- > Guy Hammond > AVT Technologies > 1 Martha's Buildings > 180 Old Street > London EC1V 9BP > > Telephone: 020 7454 4174 > Mobile: 07966 164687 > Web: http://www.avt.co.uk/ > > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Guy Hammond > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing > Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Mohan, Ross > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing > Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Guy Hammond > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing > Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Make a great connection at Yahoo! Personals. http://personals.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru
RE: Tahiti
Jan, I can get to the first page on the site but moving around to other pages is taking forever. Dave -Original Message- Sent: Thursday, November 01, 2001 6:10 AM To: Multiple recipients of list ORACLE-L I cannot get on any link from Tahiti. Does anybody know what's up? JP -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jan Pruner INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Farnsworth, Dave INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: recover on different hardware
the corruption looks to be bad archived logs -- you'd get a message to the effect that the database doesn't need recovery if the logs were okay after you opened normal Rule 0 in Rachel's Recovery cookbook: ALWAYS back up what you have right now before you attempt anything, so if what you try doesn't work, you can go back to the same place and try something different. yes, this adds additional time to the recovery process. And most times, it's unnecessary, the recovery is fairly simplistic and you are fine and don't need that backup. And then there's times like this one. Which is why, when I present on backup and recovery, I talk about being the sort of person who wants to wear a belt, have elastic in my waistband AND wear suspenders (braces for you non-American types, I learned the hard way not to say 'suspenders' to Europeans :) ) Rachel --- "Baker, Barbara" <[EMAIL PROTECTED]> wrote: > Doh! > Your message came minutes after I'd already opened the database. > When I attempt the recovery I get the message > SVRMGR> recover database until cancel using backup controlfile; > ORA-00279: change 278038264 generated at 11/01/01 01:28:13 needed > for thread 1 > ORA-00289: suggestion : > /u14/oradata/ent/arch/arch.logentarch16551.dbf > ORA-00280: change 278038264 for thread 1 is in sequence #16551 > ORA-00283: recovery session canceled due to errors > ORA-00356: inconsistent lengths in change description > ORA-00353: log corruption near block 225 change 29240139592704 time > 10/31/01 01: > > Do you think this is because I did indeed already open the database > normally? Or do you think it's cuz I really do have bad archive > files?? > > Thanks for your response. > Much appreciated. > Barb > > > -- > > From: Paul Drake[SMTP:[EMAIL PROTECTED]] > > Reply To: [EMAIL PROTECTED] > > Sent: Thursday, November 01, 2001 1:05 AM > > To: Multiple recipients of list ORACLE-L > > Subject:Re: recover on different hardware > > > > otn - http://tahiti.oracle.com/pls/tahiti/tahiti.homepage > > this site really sucks bad - half the links are broken. wtf? > > its 8.1.6 that's off of correction, not 8.1.7. > > my copy of 8i Backup recovery handbook is at the office, and the > docs > > are not available online. > > > > > > most important: > > don't open the database normally after the restore > > > > startup mount exclusive > > > > > > set autorecovery on > > recover database until cancel using backup controlfile; > > cancel > > alter database open resetlogs > > shutdown > > cold backup > > > > note: apparently, any read-only tablespaces should be offline while > > recovering using a backup controlfile. > > I had never heard of this, but stumbled across it while perusing > the > > docs. > > > > > > "Baker, Barbara" wrote: > > > > > > Been here 18 hours. No database. Looks grim. > > > Hardware blew chunks. Major ugly. > > > Plan is to reconstruct entire system on new hardware. > > > If I get my dbf files from last night's cold backup (as well as > all my > > > config files), I should have a database that looks just like last > night > > at > > > 11:00 pm. > > > DB should come up clean, should think it's a happy camper. > > > > > > I do have most of my archived redo logs from today. What > recovery > > option do > > > I use to start applying the redo logs I've been able to salvage? > (Guess > > I'm > > > confused cuz the database will not think it needs recovery.) > > > > > > I'm about to RTFM, but I'm a bit bleary-eyed. > > > Any assistance greatly appreciated. > > > > > > Thanks! > > > Barb > > > > > > -- > > > Please see the official ORACLE-L FAQ: http://www.orafaq.com > > > -- > > > Author: Baker, Barbara > > > INET: [EMAIL PROTECTED] > > > > > > Fat City Network Services-- (858) 538-5051 FAX: (858) > 538-5051 > > > San Diego, California-- Public Internet access / Mailing > Lists > > > > > > > To REMOVE yourself from this mailing list, send an E-Mail message > > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and > in > > > the message BODY, include a line containing: UNSUB ORACLE-L > > > (or the name of mailing list you want to be removed from). You > may > > > also send the HELP command for other information (like > subscribing). > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.com > > -- > > Author: Paul Drake > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > > San Diego, California-- Public Internet access / Mailing > Lists > > > > > To REMOVE yourself from this mailing list, send an E-Mail message > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > > the message BODY, include a line containing: UNSUB ORACLE-L > > (or the name of mailing list you want to be
RE: recover on different hardware
Rachel, Paul: Ya, that was it. I re-copied all the files and this time did NOT open normally. Recovery completed. I'm outta here. Thanks for your help! Barb > -- > From: Rachel Carmichael[SMTP:[EMAIL PROTECTED]] > Reply To: [EMAIL PROTECTED] > Sent: Thursday, November 01, 2001 2:50 AM > To: Multiple recipients of list ORACLE-L > Subject: Re: recover on different hardware > > Barb, > > What Paul said -- Do NOT open the database normally. If you use the > backup controlfile, Oracle presumes that the SCN's in the headers may > or may not be in sync and will allow you to recover past the SCN in the > controlfile. Presumes it's "fuzzy". > > You have my sympathies, I've been there, done that and it ain't pretty. > > let us know how it went > > Rachel > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Baker, Barbara INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Synonyms can be VERY bad for performance
Bruce,
Can you expand further on the following statement?We use a lot of
synonyms (not in forms but in SQL).
This led to the above query using around 1000 times more consistent gets
than it needed to (due to "bad" execution plan).
Thanks,
Cherie Machler
Oracle DBA
Gelco Information System
"Reardon, Bruce (CALBBAY)"
<[EMAIL PROTECTED] To: Multiple recipients of
list ORACLE-L <[EMAIL PROTECTED]>
to.com.au> cc:
Sent by: [EMAIL PROTECTED] Subject: Synonyms can be
VERY bad for performance
10/30/01 10:35 PM
Please respond to ORACLE-L
For your information and comment.
We have just had a situation where the use of synonyms in our Forms
application was very bad for performance.
In particular, opening a form was taking around 11 seconds, and 9.3 seconds
of that was spent in translating the synonyms.
A section of the tkprof output is shown below.
select OBJ.OBJECT_TYPE ,OBJ.OBJECT_NAME ,OBJ.OWNER into :b0,:b1,:b2
from
ALL_SYNONYMS SYN ,ALL_OBJECTS OBJ where SYN.SYNONYM_NAME=:b1 and
SYN.OWNER=:b2) and SYN.TABLE_NAME=OBJ.OBJECT_NAME) and SYN.TABLE_OWNER=
OBJ.OWNER) and OBJ.OBJECT_TYPE in ('TABLE','VIEW','SYNONYM'))
call count cpuelapsed disk querycurrent
rows
--- -- -- -- -- --
--
Parse0 0.00 0.00 0 0 0
0
Execute 11 0.03 0.03 0 0 0
0
Fetch 11 9.26 9.27 0 427438 55
11
--- -- -- -- -- --
--
total 22 9.29 9.30 0 427438 55
11
System Details: Oracle 8.1.7.1.4, NT 4 Sp6a, quad processor server,
optimizer_mode = first_rows, JVM installed
The JVM install created 10300 objects with an object_type like
'%JAVA%' and around 9600 synonyms.
It was the optimizer_mode = first_rows (combined with all the synonyms from
the JVM install) that was the real problem.
Because we were in first_rows, queries against the data dictionary were
optimized in first_rows mode rather than rule.
This was despite us not having any statistics on system or sys
objects.
This led to the above query using around 1000 times more consistent gets
than it needed to (due to "bad" execution plan).
We found 2 ways to get around this:
Get rid of the synonyms and use "alter session set
current_schema"
in a logon trigger, OR
Change the optimizer_mode to choose.
Our central development team decided to initially go with altering the
optimizer_mode to choose.
This improved the form opening time to approx 4 secs but 10% of
this
(0.44) seconds is still spent on translating synonyms.
To me, this just goes to show that synonyms can be bad for performance as
well as being bad for scalability.
Regards,
Bruce Reardon
mailto:[EMAIL PROTECTED]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Reardon, Bruce (CALBBAY)
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lis
