DIMENSIONS in 9i Standard Edition

2002-09-19 Thread Alexandre Gorbatchev

Hello guys,

Does anybody know if I can use dimensions in Oracle 9.0.1 SE? Namely CREATE
DIMENSION?
I'm getting ORA-00439: feature not enabled: Materialized view rewrite.
Does this feature present only in EE?

Tia,
Alexandre
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Alexandre Gorbatchev
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



RE: Re-creating control files with larger maxdatafile setting

2002-09-19 Thread Vikas Khanna

KG, 

It will not help just by increasing the db_files parameter in the Init.ora
file. Whenever you need to change MAXDATAFILES,MAXLOGFILES,MAXLOGMEMBERS for
your database the change is to recreate the control file and then mount and
open the database.

What needs to be done is : 

Svrmgr> Alter database backup controlfile to TRACE;
This would generate an ASCII text based file at the location defined by
USE_DUMP_DEST in your init.ora. Edit that file in any text editor and chane
the Value of MAXDATAFILES (in your case). Save the file with some name at a
particular destination.eg C:\Controlfile\Control.Sql

SVRMGR> Shutdown immediate;
Instance Shutdown.
SVRMGR>Startup Nomount;
Instance Started. 
SVRMGR>@C:\Controlfile\Control.Sql;
Statement Processed.
SVRMGR> Alter database mount;
Database Mounted.
Svrmgr> Alter database open;
Database opened.

Now you can create the new datafiles to expand your database. Never the
parameter db_files can be more that the MADATAFILES declared in your
controlfile;

Thanks
Vikas Khanna

-Original Message-
Sent: Thursday, September 19, 2002 1:28 PM
To: Multiple recipients of list ORACLE-L


George:

If you want to recreate the controlfile **just to **
increase the max datafiles, you can simply increase
the db_files parameter and bounce the database. This
will automatically chance the max datafiles settings
when you create the additional datafiles.

KG


-Original Message-
Leonard (ZA)
Sent: Thursday, September 19, 2002 12:13 AM
To: Multiple recipients of list ORACLE-L


Hi all

Does someone maybe have a step by step guide to re-creating the control
files. I have found that one the my databases the maxdatafiles has been set
to small, would like to increase it now.

thx

George

George Leonard
Oracle Database Administrator
Dimension Data (Pty) Ltd
(Reg. No. 1987/006597/07)
Tel: (+27 11) 575 0573
Fax: (+27 11) 576 0573
E-mail:[EMAIL PROTECTED]
Web:   http://www.didata.co.za
 
You Have The Obligation to Inform One Honestly of the risk, And As a Person
You Are Committed to Educate Yourself to the Total Risk In Any Activity!
Once Informed & Totally Aware of the Risk, Every Fool Has the Right to Kill
or Injure Themselves as They See Fit!



**
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
the system manager.

This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.

www.mimesweeper.com
**

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: George Leonard (ZA)
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: K Gopalakrishnan
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Vikas Khanna
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



RE: Re-creating control files with larger maxdatafile setting

2002-09-19 Thread Naveen Nahata

KG,

you cannot increase the db_files parameters beyond the MAXDATALIFLES number.
Oracle reserves space for storing the info about individual datafiles in the
control files depending upon the MAXDATAFILES.

To increase the number of datafiles beyond this limit, you have to recreate
the Control file.

TO create a control file, do a ALTER DATABASE BACKUP CONTROLFILE TO TRACE,
which will create a script file to recreate the control file. You can edit
that file and increase the MAXDATAFILES and then recreate the controlfile
using the script file

Regards
Naveen

-Original Message-
Sent: Thursday, September 19, 2002 1:28 PM
To: Multiple recipients of list ORACLE-L


George:

If you want to recreate the controlfile **just to **
increase the max datafiles, you can simply increase
the db_files parameter and bounce the database. This
will automatically chance the max datafiles settings
when you create the additional datafiles.

KG


-Original Message-
Leonard (ZA)
Sent: Thursday, September 19, 2002 12:13 AM
To: Multiple recipients of list ORACLE-L


Hi all

Does someone maybe have a step by step guide to re-creating the control
files. I have found that one the my databases the maxdatafiles has been set
to small, would like to increase it now.

thx

George

George Leonard
Oracle Database Administrator
Dimension Data (Pty) Ltd
(Reg. No. 1987/006597/07)
Tel: (+27 11) 575 0573
Fax: (+27 11) 576 0573
E-mail:[EMAIL PROTECTED]
Web:   http://www.didata.co.za
 
You Have The Obligation to Inform One Honestly of the risk, And As a Person
You Are Committed to Educate Yourself to the Total Risk In Any Activity!
Once Informed & Totally Aware of the Risk, Every Fool Has the Right to Kill
or Injure Themselves as They See Fit!



**
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
the system manager.

This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.

www.mimesweeper.com
**

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: George Leonard (ZA)
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: K Gopalakrishnan
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Naveen Nahata
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



RE: Re-creating control files with larger maxdatafile setting

2002-09-19 Thread K Gopalakrishnan

Not quite true. Starting from Oracle8 you don't need
to recreate the controlfile to increase the MAXDATAFILES
parameter.

You can just change the db_files and bounce the server.

KG


-Original Message-
Khanna
Sent: Thursday, September 19, 2002 1:43 AM
To: Multiple recipients of list ORACLE-L


KG,

It will not help just by increasing the db_files parameter in the Init.ora
file. Whenever you need to change MAXDATAFILES,MAXLOGFILES,MAXLOGMEMBERS for
your database the change is to recreate the control file and then mount and
open the database.

What needs to be done is :

Svrmgr> Alter database backup controlfile to TRACE;
This would generate an ASCII text based file at the location defined by
USE_DUMP_DEST in your init.ora. Edit that file in any text editor and chane
the Value of MAXDATAFILES (in your case). Save the file with some name at a
particular destination.eg C:\Controlfile\Control.Sql

SVRMGR> Shutdown immediate;
Instance Shutdown.
SVRMGR>Startup Nomount;
Instance Started.
SVRMGR>@C:\Controlfile\Control.Sql;
Statement Processed.
SVRMGR> Alter database mount;
Database Mounted.
Svrmgr> Alter database open;
Database opened.

Now you can create the new datafiles to expand your database. Never the
parameter db_files can be more that the MADATAFILES declared in your
controlfile;

Thanks
Vikas Khanna

-Original Message-
Sent: Thursday, September 19, 2002 1:28 PM
To: Multiple recipients of list ORACLE-L


George:

If you want to recreate the controlfile **just to **
increase the max datafiles, you can simply increase
the db_files parameter and bounce the database. This
will automatically chance the max datafiles settings
when you create the additional datafiles.

KG


-Original Message-
Leonard (ZA)
Sent: Thursday, September 19, 2002 12:13 AM
To: Multiple recipients of list ORACLE-L


Hi all

Does someone maybe have a step by step guide to re-creating the control
files. I have found that one the my databases the maxdatafiles has been set
to small, would like to increase it now.

thx

George

George Leonard
Oracle Database Administrator
Dimension Data (Pty) Ltd
(Reg. No. 1987/006597/07)
Tel: (+27 11) 575 0573
Fax: (+27 11) 576 0573
E-mail:[EMAIL PROTECTED]
Web:   http://www.didata.co.za

You Have The Obligation to Inform One Honestly of the risk, And As a Person
You Are Committed to Educate Yourself to the Total Risk In Any Activity!
Once Informed & Totally Aware of the Risk, Every Fool Has the Right to Kill
or Injure Themselves as They See Fit!



**
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
the system manager.

This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.

www.mimesweeper.com
**

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: George Leonard (ZA)
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: K Gopalakrishnan
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Vikas Khanna
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for o

RE: Tracking Temp Segment Usage and Event 10046

2002-09-19 Thread Cary Millsap
Title: Tracking Temp Segment Usage and Event 10046









For more information about 10053, see Wolfgang Breitling’s http://www.hotsos.com/dnloads/1/10053/Breitling2002.pdf.
Wolfgang will
present this and another new 10053 paper at our Symposium in Dallas next February (http://www.hotsos.com/events/symposium).


 



Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com

Upcoming events:
- Hotsos Clinic, Oct
1–3 San Francisco, Oct 15–17 Dallas, Dec 9–11 Honolulu
- 2003 Hotsos Symposium on
Oracle® System Performance, Feb 9–12 Dallas
- Next event: Miracle Database Forum, Sep
20–22 Middlefart Denmark



-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]] On Behalf Of Jamadagni,
Rajendra
Sent: Wednesday, September 18,
2002 10:33 AM
To: Multiple recipients of list
ORACLE-L
Subject: RE: Tracking Temp Segment
Usage and Event 10046

 



I spent last two
days working on that SQR and finally nailed it. Developers were using DISTINCT
as a rule and CBO was choosing incorrect indexes. 





 





I had to do
following to make it work ...





 





1. we put
hints in appropriate places





2. created 3
new indexes to help the queries





3. Created 1 MV





4. Change the
pl/sql code to be more efficient





5. Remove
unnecessary distinct conditions.





 





After all this
said and done, I finally have the SQR taking around 375 seconds compared to
about 1500 seconds. Even if I run all 8 of them in parallel it doesn't blow the
TEMP segments.





 





Now comes the
difficult part, to explain the development the things that we had to do and why
we had to do them.





 





I think I am
really starting to see the power of event 10046 ... I just wish I should learn
more on event 10053 as well, but that will come too eventually.





 





I am still going
to track the temp segment usage though ...





 





Thanks everyone
for all your kind suggestions, I am not done yet, there is more work to be
done.





 





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!



-Original Message-
From: Khedr, Waleed
[mailto:[EMAIL PROTECTED]]
Sent: Wednesday, September 18,
2002 11:03 AM
To: Multiple recipients of list
ORACLE-L
Subject: RE: Tracking Temp Segment
Usage and Event 10046



I'd start looking at the execution plans
first and examine if there is any Cartesian joins and the order the tables get
joined.





 





Waleed












RE: Why does my insert creates so many logs?

2002-09-19 Thread Nicoll, Iain \(Calanais\)

Doesn't have any triggers does it?

-Original Message-
Sent: Wednesday, September 18, 2002 8:39 PM
To: Multiple recipients of list ORACLE-L


Hi.

A developer of mine is running a large insert as
select:

insert /* parallel hint */ into table A 
nologging 
(select * from table b where ...);

There are no indices on table A and a PK disabled.
Still that insert generates a large amount of logs.
What could be the reason for that? Any ideas? Table A
is not partitioned and has NOLOGGING attribute on the
dba_tables set to Yes.

thanks

Gene

__
Do you Yahoo!?
Yahoo! News - Today's headlines
http://news.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Gurelei
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Nicoll, Iain \(Calanais\)
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



RE: Lost ofall redo logs

2002-09-19 Thread Robertson Lee - lerobe

update your CV

-Original Message-
Sent: 18 September 2002 21:30
To: Multiple recipients of list ORACLE-L


Hi people...

What to do if I have LOST ALL REDO LOGS and i don´t have any backup?

Thanks in advance!

:
Gilberto Gampert  Universidade de Passo Fundo
Administrador de Banco de Dados   Passo Fundo - RS - Bra5il
[EMAIL PROTECTED]http://www.upf.br
:


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Gilberto Gampert
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).


*

The information contained in this communication is
confidential, is intended only for the use of the recipient
named above, and may be legally privileged.
If the reader of this message is not the intended
recipient, you are hereby notified that any dissemination, 
distribution, or copying of this communication is strictly
prohibited.
If you have received this communication in error,
please re-send this communication to the sender and
delete the original message or any copy of it from your
computer system. Thank You.

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Robertson Lee - lerobe
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



RE: _spin_count Revealed

2002-09-19 Thread Cary Millsap

EXCELLENT site! Makes me feel right at home :).


Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com

Upcoming events:
- Hotsos Clinic, Oct 1-3 San Francisco, Oct 15-17 Dallas, Dec 9-11
Honolulu
- 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12 Dallas
- Next event: Miracle Database Forum, Sep 20-22 Middelfart Denmark



-Original Message-
Rich
Sent: Wednesday, September 18, 2002 10:18 AM
To: Multiple recipients of list ORACLE-L

I doubt the _counter_corriolis_effect parameter would do much:

http://www.ems.psu.edu/~fraser/Bad/BadCoriolis.html

:)

Rich Jesse   System/Database Administrator
[EMAIL PROTECTED]  Quad/Tech International, Sussex, WI
USA


> -Original Message-
> From: Grant Allen [mailto:[EMAIL PROTECTED]]
> Sent: Tuesday, September 17, 2002 7:18 PM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: _spin_count Revealed
> 
> 
> You're forgetting about the _counter_corriolis_effect 
> parameter, introduced
> in 9i.  If you set this to true, then you need to flip you're 
> setting for
> _spin_count.  I've also found it useful to buy hard drives 
> with synchromesh
> to make gear changes smoother :-)
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jesse, Rich
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Cary Millsap
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



RMAN implementation strategy

2002-09-19 Thread O'Neill, Sean

Hi Folks,

We're planning to implement RMAN as part of our B&R solution and by
extenstion also as part of our DR solution.  I've been trying to locate
information on how best to configure RMAN across our organisation.  

For example it's advised you place catalog on separate server to production
server.  So server A might house catalog for server B and vice versa.  But
in a DR scenario where both servers could be destroyed there are I 'suspect'
potential implementations on overall MTTR depending on configuration.  Is it
then perhaps better to locate all catalogs on a dedicated server which
ideally would be replicated somehow to eliminate it as a singal point of
failure.

Also we have a requirment to be able to potentially recover data as far back
as 7 years.  These are currently comprised of monthly backups taken out of
regular cycle and archived off site.  I'm thinking it might be an idea to
set up a two catalogs, one for regular monthly cycle and another to record
these monthly archives as the maintenace of the catalog might be cumbersome
trying to ensure the montlhy archive data records do not get accidentally
deleted.

I've had a trawl across the Web courtesy of Google but did not find any
papers which appear to deal with these type of issues.  The RMAN User's
Guide and Reference does not appear to address them either.  Your
feedback/comments or references to papers would be much appeciated!.

Oracle 7.3.3, 8.0.5, 8.1.7 
NT4, W2K 
-
Seán O' Neill
Organon (Ireland) Ltd.
[subscribed: digest mode] 

This message, including attached files, may contain confidential
information and is intended only for the use by the individual
and/or the entity to which it is addressed. Any unauthorized use,
dissemination of, or copying of the information contained herein is
not allowed and may lead to irreparable harm and damage for which
you may be held liable. If you receive this message in error or if
it is intended for someone else please notify the sender by
returning this e-mail immediately and delete the message.

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: O'Neill, Sean
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



RE: Re-creating control files with larger maxdatafile setting

2002-09-19 Thread Naveen Nahata

KG,

This is from 9i Manual:

DB_FILES:

Parameter type Integer
Default value  200
Parameter classStatic
Range of values 
Minimum:   the current actual number of datafiles in the
database
Maximum:   the value that was specified in the MAXDATAFILES
   clause the last time CREATE DATABASE or CREATE
CONTROLFILE
   was executed
Oracle9i Real Application Clusters You must set this parameter for every
instance, and multiple
instances must have the same value.

Regards
Naveen

-Original Message-
Sent: Thursday, September 19, 2002 2:39 PM
To: Multiple recipients of list ORACLE-L


Not quite true. Starting from Oracle8 you don't need
to recreate the controlfile to increase the MAXDATAFILES
parameter.

You can just change the db_files and bounce the server.

KG


-Original Message-
Khanna
Sent: Thursday, September 19, 2002 1:43 AM
To: Multiple recipients of list ORACLE-L


KG,

It will not help just by increasing the db_files parameter in the Init.ora
file. Whenever you need to change MAXDATAFILES,MAXLOGFILES,MAXLOGMEMBERS for
your database the change is to recreate the control file and then mount and
open the database.

What needs to be done is :

Svrmgr> Alter database backup controlfile to TRACE;
This would generate an ASCII text based file at the location defined by
USE_DUMP_DEST in your init.ora. Edit that file in any text editor and chane
the Value of MAXDATAFILES (in your case). Save the file with some name at a
particular destination.eg C:\Controlfile\Control.Sql

SVRMGR> Shutdown immediate;
Instance Shutdown.
SVRMGR>Startup Nomount;
Instance Started.
SVRMGR>@C:\Controlfile\Control.Sql;
Statement Processed.
SVRMGR> Alter database mount;
Database Mounted.
Svrmgr> Alter database open;
Database opened.

Now you can create the new datafiles to expand your database. Never the
parameter db_files can be more that the MADATAFILES declared in your
controlfile;

Thanks
Vikas Khanna

-Original Message-
Sent: Thursday, September 19, 2002 1:28 PM
To: Multiple recipients of list ORACLE-L


George:

If you want to recreate the controlfile **just to **
increase the max datafiles, you can simply increase
the db_files parameter and bounce the database. This
will automatically chance the max datafiles settings
when you create the additional datafiles.

KG


-Original Message-
Leonard (ZA)
Sent: Thursday, September 19, 2002 12:13 AM
To: Multiple recipients of list ORACLE-L


Hi all

Does someone maybe have a step by step guide to re-creating the control
files. I have found that one the my databases the maxdatafiles has been set
to small, would like to increase it now.

thx

George

George Leonard
Oracle Database Administrator
Dimension Data (Pty) Ltd
(Reg. No. 1987/006597/07)
Tel: (+27 11) 575 0573
Fax: (+27 11) 576 0573
E-mail:[EMAIL PROTECTED]
Web:   http://www.didata.co.za

You Have The Obligation to Inform One Honestly of the risk, And As a Person
You Are Committed to Educate Yourself to the Total Risk In Any Activity!
Once Informed & Totally Aware of the Risk, Every Fool Has the Right to Kill
or Injure Themselves as They See Fit!



**
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
the system manager.

This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.

www.mimesweeper.com
**

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: George Leonard (ZA)
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: K Gopalakrishnan
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be re

Re: HELP! CAN't STARTUP REMOTE DATABASE USING SQL*PLUS - BUG?-NADA o

2002-09-19 Thread Yechiel Adar
Title: HELP! CAN't STARTUP REMOTE DATABASE USING SQL*PLUS - BUG?-NADA on METASTINK



Hello
 
I noticed that your sqlplus logon connects to an idle 
instance.
That's mean that you are NOT connected to the 
remote database.
You need to set oracle_sid or use connect 
system/password@database.
 
Also why not do shutdown immediate instead of 
abort?
 
Yechiel AdarMehish

  - Original Message - 
  From: 
  [EMAIL PROTECTED] 
  To: Multiple recipients of list ORACLE-L 
  Sent: Wednesday, September 18, 2002 6:18 
  PM
  Subject: HELP! CAN't STARTUP REMOTE 
  DATABASE USING SQL*PLUS - BUG?-NADA o
  
  Guys 
  Trying to automate rman cold backups using sql*plus versus 
  svrmgrl.  RMAN is local and target databases are remote.  
  Executing the following (just from a script seems to 
  work just fine interactively): 
   
  $ORACLE_HOME/bin/sqlplus  /nolog 
  <<-EOD 
   
  connect 
  system/password;  
   
  @tst2.sql;  
   
  shutdown 
  abort  
   
  @tst2.sql;   
  
   startup  
  pfile=$ORACLE_HOME/dbs/init$DBNAME.ora;    
   spool 
  $STATUSFILE   
   
  select status from v\$instance where status = 
  'MOUNTED';    
   spool 
  off   
   
  EOD 
   
   
  
  SQL> 
  Connected. 
  SQL> Connected to an idle 
  instance. 
  SQL> ORACLE instance shut 
  down. 
  SQL> Connected to an idle 
  instance. 
  SQL> ORA-01031: insufficient 
  privileges 
  SQL> not spooling 
  currently 
  SQL>  select status 
  from v$instance where status = 
  'MOUNTED'    
  *   
  ERROR at line 
  1:    
  ORA-01034: ORACLE not 
  available 
      
      
  SQL> not spooling 
  currently 
  SQL> SP2-0042: unknown command "EOD" - rest of line 
  ignored.    
  SQL> SQL> 
  Disconnected  
  irm00swas04:oracle:/usr/oracle/tools/backups/callscripts    
  


RE: Lost ofall redo logs

2002-09-19 Thread Mark J. Bobak

And preferably, forget about this little "incident" when doing so...;-)

On Thu, 2002-09-19 at 05:43, Robertson Lee - lerobe wrote:
> update your CV
> 
> -Original Message-
> Sent: 18 September 2002 21:30
> To: Multiple recipients of list ORACLE-L
> 
> 
> Hi people...
> 
> What to do if I have LOST ALL REDO LOGS and i don´t have any backup?
> 
> Thanks in advance!
> 
> :
> Gilberto Gampert  Universidade de Passo Fundo
> Administrador de Banco de Dados   Passo Fundo - RS - Bra5il
> [EMAIL PROTECTED]http://www.upf.br
> :
> 
> 
-- 
--
Mark J. Bobak
Oracle DBA
[EMAIL PROTECTED]
"It is not enough to have a good mind.  The main thing is to use it
well."
-- Rene Descartes
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mark J. Bobak
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



RE: Re-creating control files with larger maxdatafile setting

2002-09-19 Thread K Gopalakrishnan

Not sure from which manual it is mentioned. In any case
the manual is incorrect for Oracle8 and above  versions.
You **CAN** create the datafiles than MAXDATAFILES
if you have specified db_files parameter greater than
the MAXDATAFILES.

You may want to file a Documentation BUG on that Manual.


KG



-Original Message-
Nahata
Sent: Thursday, September 19, 2002 2:39 AM
To: Multiple recipients of list ORACLE-L


KG,

This is from 9i Manual:

DB_FILES:

Parameter type Integer
Default value  200
Parameter classStatic
Range of values
Minimum:   the current actual number of datafiles in the
database
Maximum:   the value that was specified in the MAXDATAFILES
   clause the last time CREATE DATABASE or CREATE
CONTROLFILE
   was executed
Oracle9i Real Application Clusters You must set this parameter for every
instance, and multiple
instances must have the same value.

Regards
Naveen

-Original Message-
Sent: Thursday, September 19, 2002 2:39 PM
To: Multiple recipients of list ORACLE-L


Not quite true. Starting from Oracle8 you don't need
to recreate the controlfile to increase the MAXDATAFILES
parameter.

You can just change the db_files and bounce the server.

KG


-Original Message-
Khanna
Sent: Thursday, September 19, 2002 1:43 AM
To: Multiple recipients of list ORACLE-L


KG,

It will not help just by increasing the db_files parameter in the Init.ora
file. Whenever you need to change MAXDATAFILES,MAXLOGFILES,MAXLOGMEMBERS for
your database the change is to recreate the control file and then mount and
open the database.

What needs to be done is :

Svrmgr> Alter database backup controlfile to TRACE;
This would generate an ASCII text based file at the location defined by
USE_DUMP_DEST in your init.ora. Edit that file in any text editor and chane
the Value of MAXDATAFILES (in your case). Save the file with some name at a
particular destination.eg C:\Controlfile\Control.Sql

SVRMGR> Shutdown immediate;
Instance Shutdown.
SVRMGR>Startup Nomount;
Instance Started.
SVRMGR>@C:\Controlfile\Control.Sql;
Statement Processed.
SVRMGR> Alter database mount;
Database Mounted.
Svrmgr> Alter database open;
Database opened.

Now you can create the new datafiles to expand your database. Never the
parameter db_files can be more that the MADATAFILES declared in your
controlfile;

Thanks
Vikas Khanna

-Original Message-
Sent: Thursday, September 19, 2002 1:28 PM
To: Multiple recipients of list ORACLE-L


George:

If you want to recreate the controlfile **just to **
increase the max datafiles, you can simply increase
the db_files parameter and bounce the database. This
will automatically chance the max datafiles settings
when you create the additional datafiles.

KG


-Original Message-
Leonard (ZA)
Sent: Thursday, September 19, 2002 12:13 AM
To: Multiple recipients of list ORACLE-L


Hi all

Does someone maybe have a step by step guide to re-creating the control
files. I have found that one the my databases the maxdatafiles has been set
to small, would like to increase it now.

thx

George

George Leonard
Oracle Database Administrator
Dimension Data (Pty) Ltd
(Reg. No. 1987/006597/07)
Tel: (+27 11) 575 0573
Fax: (+27 11) 576 0573
E-mail:[EMAIL PROTECTED]
Web:   http://www.didata.co.za

You Have The Obligation to Inform One Honestly of the risk, And As a Person
You Are Committed to Educate Yourself to the Total Risk In Any Activity!
Once Informed & Totally Aware of the Risk, Every Fool Has the Right to Kill
or Injure Themselves as They See Fit!



**
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
the system manager.

This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.

www.mimesweeper.com
**

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: George Leonard (ZA)
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: K Gopalakrishnan
  INET: [EMAIL PROTECTED]

Fat City Network Service

RE: Re-creating control files with larger maxdatafile setting

2002-09-19 Thread K Gopalakrishnan

THis one is from  pg 876 of Oracle9i SQL Reference
PArt # A90125-01


*BEGIN QUOTE
MAXDATAFILES Clause
Specify the initial sizing of the datafiles section of the control file at
CREATE
DATABASE or CREATE CONTROLFILE time. An attempt to add a file whose number
is greater than MAXDATAFILES, but less than or equal to DB_FILES, causes the
control file to expand automatically so that the datafiles section can
accommodate
more files.
The number of datafiles accessible to your instance is also limited by the
initialization parameter DB_FILES.
END QUOTE


So it is clear that you can add datafiles to the database without
recreating the control files as long as the db_files are specified
to higher number.


KG


-Original Message-
Nahata
Sent: Thursday, September 19, 2002 2:39 AM
To: Multiple recipients of list ORACLE-L


KG,

This is from 9i Manual:

DB_FILES:

Parameter type Integer
Default value  200
Parameter classStatic
Range of values
Minimum:   the current actual number of datafiles in the
database
Maximum:   the value that was specified in the MAXDATAFILES
   clause the last time CREATE DATABASE or CREATE
CONTROLFILE
   was executed
Oracle9i Real Application Clusters You must set this parameter for every
instance, and multiple
instances must have the same value.

Regards
Naveen

-Original Message-
Sent: Thursday, September 19, 2002 2:39 PM
To: Multiple recipients of list ORACLE-L


Not quite true. Starting from Oracle8 you don't need
to recreate the controlfile to increase the MAXDATAFILES
parameter.

You can just change the db_files and bounce the server.

KG


-Original Message-
Khanna
Sent: Thursday, September 19, 2002 1:43 AM
To: Multiple recipients of list ORACLE-L


KG,

It will not help just by increasing the db_files parameter in the Init.ora
file. Whenever you need to change MAXDATAFILES,MAXLOGFILES,MAXLOGMEMBERS for
your database the change is to recreate the control file and then mount and
open the database.

What needs to be done is :

Svrmgr> Alter database backup controlfile to TRACE;
This would generate an ASCII text based file at the location defined by
USE_DUMP_DEST in your init.ora. Edit that file in any text editor and chane
the Value of MAXDATAFILES (in your case). Save the file with some name at a
particular destination.eg C:\Controlfile\Control.Sql

SVRMGR> Shutdown immediate;
Instance Shutdown.
SVRMGR>Startup Nomount;
Instance Started.
SVRMGR>@C:\Controlfile\Control.Sql;
Statement Processed.
SVRMGR> Alter database mount;
Database Mounted.
Svrmgr> Alter database open;
Database opened.

Now you can create the new datafiles to expand your database. Never the
parameter db_files can be more that the MADATAFILES declared in your
controlfile;

Thanks
Vikas Khanna

-Original Message-
Sent: Thursday, September 19, 2002 1:28 PM
To: Multiple recipients of list ORACLE-L


George:

If you want to recreate the controlfile **just to **
increase the max datafiles, you can simply increase
the db_files parameter and bounce the database. This
will automatically chance the max datafiles settings
when you create the additional datafiles.

KG


-Original Message-
Leonard (ZA)
Sent: Thursday, September 19, 2002 12:13 AM
To: Multiple recipients of list ORACLE-L


Hi all

Does someone maybe have a step by step guide to re-creating the control
files. I have found that one the my databases the maxdatafiles has been set
to small, would like to increase it now.

thx

George

George Leonard
Oracle Database Administrator
Dimension Data (Pty) Ltd
(Reg. No. 1987/006597/07)
Tel: (+27 11) 575 0573
Fax: (+27 11) 576 0573
E-mail:[EMAIL PROTECTED]
Web:   http://www.didata.co.za

You Have The Obligation to Inform One Honestly of the risk, And As a Person
You Are Committed to Educate Yourself to the Total Risk In Any Activity!
Once Informed & Totally Aware of the Risk, Every Fool Has the Right to Kill
or Injure Themselves as They See Fit!



**
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
the system manager.

This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.

www.mimesweeper.com
**

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: George Leonard (ZA)
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mai

Index question

2002-09-19 Thread Ofer Harel

Good morning,

Suppose there is a long running query (which already parsed) using an index.
Now I dropped the index. What should happened to the running query?

Ofer Harel
DBA team
Barak ITC
[EMAIL PROTECTED]


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Ofer Harel
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



RE: Lost ofall redo logs

2002-09-19 Thread Bill Buchan


If you lost the redo logs *after* you shutdown the database, you can do the 
following to re-create them:


1. As DBA: startup mount;

2. alter database backup controlfile to trace;

3. select * from v$controlfile;

4. shutdown

5. Delete the controlfiles identified by (3) as these need to be re-created.

6. Edit the tracefile created by (2) to look something like the following:


STARTUP NOMOUNT
CREATE CONTROLFILE SET DATABASE "SID" RESETLOGS NOARCHIVELOG
 MAXLOGFILES 6
 MAXLOGMEMBERS 2
 MAXDATAFILES 64
 MAXINSTANCES 1
 MAXLOGHISTORY 226
LOGFILE
   GROUP 1 '/usr4/oradata/SID/redoSID01a.log'  SIZE 50M,
   GROUP 2 '/usr4/oradata/SID/redoSID02a.log'  SIZE 50M,
   GROUP 3 '/usr4/oradata/SID/redoSID03a.log'  SIZE 50M,
   GROUP 4 '/usr4/oradata/SID/redoSID04a.log'  SIZE 50M
DATAFILE
   '/usr1/oradata/SID/system_01.dbf',
   '/usr2/oradata/SID/users_01.dbf',
   '/usr3/oradata/SID/users_indexes_01.dbf',
   '/usr3/oradata/SID/rollback_01.dbf'
CHARACTER SET WE8ISO8859P1
;
ALTER DATABASE OPEN RESETLOGS;
ALTER TABLESPACE TEMP ADD TEMPFILE '/usr1/oradata/SID/temp_01.dbf' REUSE;


ie.

i.  Remove leading comments and lines beginning with #
ii. In the CREATE CONTROLFILE line change REUSE to SET
and NORESETLOGS to RESETLOGS
iii.Get rid of RECOVER DATABASE
iv. Change ALTER DATABASE OPEN to ALTER DATABASE OPEN RESETLOGS
(v. Ignore ALTER TABLESPACE stuff if you are not using LMT
temporary tablespaces.)

7. Now go into SQL*Plus as DBA and run the script, which should build new
controlfiles, re-create the log files and open the database.


Remember to back up everything you *do* have before starting.  This will 
not work if you lost the redo logs before you shutdown the database.

- Bill.


- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Wednesday, September 18, 2002 5:30 PM



 > Hi people...
 >
 > What to do if I have LOST ALL REDO LOGS and i don´t have any backup?
 >
 > Thanks in advance!
 >
 > :
 > Gilberto Gampert  Universidade de Passo Fundo
 > Administrador de Banco de Dados   Passo Fundo - RS - Bra5il
 > [EMAIL PROTECTED]http://www.upf.br
 > :
 >
 >
 > --
 > Please see the official ORACLE-L FAQ: http://www.orafaq.com
 > --
 > Author: Gilberto Gampert
 >   INET: [EMAIL PROTECTED]

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Bill Buchan
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



RE: Index question

2002-09-19 Thread Larry Elkins

Ofer,

Testing against 8.1.7.4, the drop index was successful and an error was
reported by my long running query:

ERROR:
ORA-08103: object no longer exists

Regards,

Larry G. Elkins
[EMAIL PROTECTED]
214.954.1781

> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Ofer Harel
> Sent: Thursday, September 19, 2002 6:09 AM
> To: Multiple recipients of list ORACLE-L
> Subject: Index question
>
>
> Good morning,
>
> Suppose there is a long running query (which already parsed)
> using an index.
> Now I dropped the index. What should happened to the running query?
>
> Ofer Harel
> DBA team
> Barak ITC
> [EMAIL PROTECTED]

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Larry Elkins
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



Apologies for Off-Topic Question - Where has Oracle-l-OT gone ?

2002-09-19 Thread Steve . Parker

Has the Off-Topic group gone ?

Steve Parker
E Mail: [EMAIL PROTECTED]
         --


RE: Re-creating control files with larger maxdatafile setting

2002-09-19 Thread Naveen Nahata

Well, point taken. I'll try and test it too when I get time.

My point came from the DB Reference Guide, 9i Release 2. You can also see the
same point in the SQL Reference in ALTER SYSTEM command. Maybe it is
out-dated.

That caused the confusion.

Regards
Naveen

-Original Message-
Sent: Thursday, September 19, 2002 4:18 PM
To: Multiple recipients of list ORACLE-L


THis one is from  pg 876 of Oracle9i SQL Reference
PArt # A90125-01


*BEGIN QUOTE
MAXDATAFILES Clause
Specify the initial sizing of the datafiles section of the control file at
CREATE
DATABASE or CREATE CONTROLFILE time. An attempt to add a file whose number
is greater than MAXDATAFILES, but less than or equal to DB_FILES, causes the
control file to expand automatically so that the datafiles section can
accommodate
more files.
The number of datafiles accessible to your instance is also limited by the
initialization parameter DB_FILES.
END QUOTE


So it is clear that you can add datafiles to the database without
recreating the control files as long as the db_files are specified
to higher number.


KG


-Original Message-
Nahata
Sent: Thursday, September 19, 2002 2:39 AM
To: Multiple recipients of list ORACLE-L


KG,

This is from 9i Manual:

DB_FILES:

Parameter type Integer
Default value  200
Parameter classStatic
Range of values
Minimum:   the current actual number of datafiles in the
database
Maximum:   the value that was specified in the MAXDATAFILES
   clause the last time CREATE DATABASE or CREATE
CONTROLFILE
   was executed
Oracle9i Real Application Clusters You must set this parameter for every
instance, and multiple
instances must have the same value.

Regards
Naveen

-Original Message-
Sent: Thursday, September 19, 2002 2:39 PM
To: Multiple recipients of list ORACLE-L


Not quite true. Starting from Oracle8 you don't need
to recreate the controlfile to increase the MAXDATAFILES
parameter.

You can just change the db_files and bounce the server.

KG


-Original Message-
Khanna
Sent: Thursday, September 19, 2002 1:43 AM
To: Multiple recipients of list ORACLE-L


KG,

It will not help just by increasing the db_files parameter in the Init.ora
file. Whenever you need to change MAXDATAFILES,MAXLOGFILES,MAXLOGMEMBERS for
your database the change is to recreate the control file and then mount and
open the database.

What needs to be done is :

Svrmgr> Alter database backup controlfile to TRACE;
This would generate an ASCII text based file at the location defined by
USE_DUMP_DEST in your init.ora. Edit that file in any text editor and chane
the Value of MAXDATAFILES (in your case). Save the file with some name at a
particular destination.eg C:\Controlfile\Control.Sql

SVRMGR> Shutdown immediate;
Instance Shutdown.
SVRMGR>Startup Nomount;
Instance Started.
SVRMGR>@C:\Controlfile\Control.Sql;
Statement Processed.
SVRMGR> Alter database mount;
Database Mounted.
Svrmgr> Alter database open;
Database opened.

Now you can create the new datafiles to expand your database. Never the
parameter db_files can be more that the MADATAFILES declared in your
controlfile;

Thanks
Vikas Khanna

-Original Message-
Sent: Thursday, September 19, 2002 1:28 PM
To: Multiple recipients of list ORACLE-L


George:

If you want to recreate the controlfile **just to **
increase the max datafiles, you can simply increase
the db_files parameter and bounce the database. This
will automatically chance the max datafiles settings
when you create the additional datafiles.

KG


-Original Message-
Leonard (ZA)
Sent: Thursday, September 19, 2002 12:13 AM
To: Multiple recipients of list ORACLE-L


Hi all

Does someone maybe have a step by step guide to re-creating the control
files. I have found that one the my databases the maxdatafiles has been set
to small, would like to increase it now.

thx

George

George Leonard
Oracle Database Administrator
Dimension Data (Pty) Ltd
(Reg. No. 1987/006597/07)
Tel: (+27 11) 575 0573
Fax: (+27 11) 576 0573
E-mail:[EMAIL PROTECTED]
Web:   http://www.didata.co.za

You Have The Obligation to Inform One Honestly of the risk, And As a Person
You Are Committed to Educate Yourself to the Total Risk In Any Activity!
Once Informed & Totally Aware of the Risk, Every Fool Has the Right to Kill
or Injure Themselves as They See Fit!



**
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
the system manager.

This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.

www.mimesweeper.com
***

Re: Lost ofall redo logs

2002-09-19 Thread Joe Testa

As I stated earlier, right out of the user managed recover docs(loss of 
all online redo logs):

you'll need to do the restore from backup, using what archivelogs 
available roll forward to point of last archived log and open resetlogs 
<--- the abbreviated version.

joe


  Recovering After the Loss of All Members of an Online Redo Log Group

If a media failure damages all members of an online redo log group, then 
different scenarios can occur depending on the type of online redo log 
group affected by the failure and the archiving mode of the database.

If the damaged log group is inactive, then it is not needed for crash 
recovery; if it is active, then it is needed for crash recovery.

*If the group is . . .* *Then . . .* *And you should . . .*

Inactive

It is not needed for crash recovery

Clear the archived or unarchived group.

Active

It is needed for crash recovery

Attempt to issue a checkpoint and clear the log; if impossible, then you 
must restore a backup and perform incomplete recovery up to the most 
recent available log.

Current

It is the log that Oracle is currently writing to

Attempt to clear the log; if impossible, then you must restore a backup 
and perform incomplete recovery up to the most recent available log.

Your first task is to determine whether the damaged group is active or 
inactive.


*Loss of Multiple Redo Log Groups*

If you have lost multiple groups of the online redo log, then use the 
recovery method for the most difficult log to recover. The order of 
difficulty, from most difficult to least difficult, follows:

   1. The current online redo log
   2. An active online redo log
   3. An unarchived online redo log
   4. An inactive online redo log





Arun Chakrapanirao wrote:

>Well one I do want to make clear I have never tried to give bad advice to
>people.
>Since the user in his mail had said that he does not have a backup and does
>not have any online redo logs can you please let me know how else can he
>open the database, when he said he does not have a back it assumes that he
>does not have a os backup and also the rman backup.
>This might be the undocumented thing but this Is the only way it works
>And this had happened in one our case and the only way we could open the
>database is to force open the database in a corrupt mode take an export and
>import back into the new database.
>
>If you still think I am trying to give a bad advice well sorry that I am
>giving bad advices.
>
>
>-Original Message-
>Sent: Thursday, September 19, 2002 12:33 AM
>To: Multiple recipients of list ORACLE-L
>
>Arun, since WHEN has been using undocumented parameters the normal way 
>of doing things, I think you need to go back and lookup backup/recovery 
>concepts.
>
>Feel free to read both user managed and RMAN backup recovery docs/scenarios.
>
>You are giving bad advice to people on the list who might be new.
>
>joe
>
>
>Arun Chakrapanirao wrote:
>
>  
>
>>The only way u can open your database is to add a parameter file
>>as
>>
>>_allow_resetlogs_corruption=TRUE
>>
>>_corrupted_rollback_segments= TRUE
>>
>>_offline_rollback_segments=(the rollback segment names)
>>
>>activate the database and  then immediately take an export of the whole
>>database.
>>Create a new database and then import all the data to this new database.
>>
>>
>>-Original Message-
>>Sent: Wednesday, September 18, 2002 5:23 PM
>>To: Multiple recipients of list ORACLE-L
>>
>>Nop, all redo logs gone away...
>>
>>- Original Message -
>>To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
>>Sent: Wednesday, September 18, 2002 5:43 PM
>>
>>
>> 
>>
>>
>>
>>>What about mirrored redo group members?
>>>
>>>Igor Neyman, OCP DBA
>>>[EMAIL PROTECTED]
>>>
>>>
>>>
>>>- Original Message -
>>>To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
>>>Sent: Wednesday, September 18, 2002 4:30 PM
>>>
>>>
>>>Hi people...
>>>
>>>What to do if I have LOST ALL REDO LOGS and i don´t have any backup?
>>>
>>>Thanks in advance!
>>>
>>>:
>>>Gilberto Gampert  Universidade de Passo Fundo
>>>Administrador de Banco de Dados   Passo Fundo - RS - Bra5il
>>>[EMAIL PROTECTED]http://www.upf.br
>>>:
>>>
>>>
>>>--
>>>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>>>--
>>>Author: Gilberto Gampert
>>> INET: [EMAIL PROTECTED]
>>>
>>>Fat City Network Services-- 858-538-5051 http://www.fatcity.com
>>>San Diego, California-- Mailing list and web hosting services
>>>-
>>>To REMOVE yourself from this mailing list, send an E-Mail message
>>>to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
>>>the message BODY, include a line containing: UNSUB ORACLE-L
>>>(or the name of mailing list you want to be removed from).  

Re: Index question

2002-09-19 Thread Alexandre Gorbatchev

I think it's not possible. Query will lock an index in some way, so you
cannot drop it until query releases it because DDL will not be able to
accure exclusive lock on the index.
I'm not sure when query releases index lock. I would say at the end of a
query but may be wrong.

Alexandre

- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Thursday, September 19, 2002 1:08 PM


> Good morning,
>
> Suppose there is a long running query (which already parsed) using an
index.
> Now I dropped the index. What should happened to the running query?
>
> Ofer Harel
> DBA team
> Barak ITC
> [EMAIL PROTECTED]
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Ofer Harel
>   INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Alexandre Gorbatchev
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



Re: Lost ofall redo logs

2002-09-19 Thread Joe Testa

KG, doesn't anyone think not having a backup is a bad thing.

You can go down that route if you wish but as I remember undocumented 
parameters are best used with the assistance of OWS and not just used at 
will.

joe


K Gopalakrishnan wrote:

>Arun:
>
>You are right. The original poster *clearly* said
>
>LOST ALL REDO LOGS
>NO BACKUP.
>
>In the above situation, you have to open the
>databases using the some special tricks and
>I don't think you need to go back and refer
>BAckup and Recovery Manuals (You can not
>find anything for this situation, that is
>another story)
>
>Those parameters will bring up the database
>if the database is cleanly shudowned (read:
>Shutdown Normal/Immediate) and you will
>have to do some more tricks to get the
>database up if it is crashed (read: shutdown
>ABORT).
>
>I don't think it is a bad advice..
>
>YMMV
>
>KG
>
>
>-Original Message-
>Chakrapanirao
>Sent: Wednesday, September 18, 2002 11:08 PM
>To: Multiple recipients of list ORACLE-L
>
>
>Well one I do want to make clear I have never tried to give bad advice to
>people.
>Since the user in his mail had said that he does not have a backup and does
>not have any online redo logs can you please let me know how else can he
>open the database, when he said he does not have a back it assumes that he
>does not have a os backup and also the rman backup.
>This might be the undocumented thing but this Is the only way it works
>And this had happened in one our case and the only way we could open the
>database is to force open the database in a corrupt mode take an export and
>import back into the new database.
>
>If you still think I am trying to give a bad advice well sorry that I am
>giving bad advices.
>
>
>-Original Message-
>Sent: Thursday, September 19, 2002 12:33 AM
>To: Multiple recipients of list ORACLE-L
>
>Arun, since WHEN has been using undocumented parameters the normal way
>of doing things, I think you need to go back and lookup backup/recovery
>concepts.
>
>Feel free to read both user managed and RMAN backup recovery docs/scenarios.
>
>You are giving bad advice to people on the list who might be new.
>
>joe
>
>
>Arun Chakrapanirao wrote:
>
>  
>
>>The only way u can open your database is to add a parameter file
>>as
>>
>>_allow_resetlogs_corruption=TRUE
>>
>>_corrupted_rollback_segments= TRUE
>>
>>_offline_rollback_segments=(the rollback segment names)
>>
>>activate the database and  then immediately take an export of the whole
>>database.
>>Create a new database and then import all the data to this new database.
>>
>>
>>-Original Message-
>>Sent: Wednesday, September 18, 2002 5:23 PM
>>To: Multiple recipients of list ORACLE-L
>>
>>Nop, all redo logs gone away...
>>
>>- Original Message -
>>To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
>>Sent: Wednesday, September 18, 2002 5:43 PM
>>
>>
>>
>>
>>
>>
>>>What about mirrored redo group members?
>>>
>>>Igor Neyman, OCP DBA
>>>[EMAIL PROTECTED]
>>>
>>>
>>>
>>>- Original Message -
>>>To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
>>>Sent: Wednesday, September 18, 2002 4:30 PM
>>>
>>>
>>>Hi people...
>>>
>>>What to do if I have LOST ALL REDO LOGS and i don4t have any backup?
>>>
>>>Thanks in advance!
>>>
>>>:
>>>Gilberto Gampert  Universidade de Passo Fundo
>>>Administrador de Banco de Dados   Passo Fundo - RS - Bra5il
>>>[EMAIL PROTECTED]http://www.upf.br
>>>:
>>>
>>>
>>>--
>>>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>>>--
>>>Author: Gilberto Gampert
>>> INET: [EMAIL PROTECTED]
>>>
>>>Fat City Network Services-- 858-538-5051 http://www.fatcity.com
>>>San Diego, California-- Mailing list and web hosting services
>>>-
>>>To REMOVE yourself from this mailing list, send an E-Mail message
>>>to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
>>>the message BODY, include a line containing: UNSUB ORACLE-L
>>>(or the name of mailing list you want to be removed from).  You may
>>>also send the HELP command for other information (like subscribing).
>>>
>>>--
>>>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>>>--
>>>Author: Igor Neyman
>>> INET: [EMAIL PROTECTED]
>>>
>>>Fat City Network Services-- 858-538-5051 http://www.fatcity.com
>>>San Diego, California-- Mailing list and web hosting services
>>>-
>>>To REMOVE yourself from this mailing list, send an E-Mail message
>>>to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
>>>the message BODY, include a line containing: UNSUB ORACLE-L
>>>(or the name of mailing list you want to be removed from).  You may
>>>also send the HELP command for other information (like subscrib

RE: Index question

2002-09-19 Thread Naveen Nahata

The Query will be re-parsed. As soon as u do a DDL, the dependent queries in
Shared SQL area are invalidated.

Regards
Naveen

-Original Message-
Sent: Thursday, September 19, 2002 4:39 PM
To: Multiple recipients of list ORACLE-L


Good morning,

Suppose there is a long running query (which already parsed) using an index.
Now I dropped the index. What should happened to the running query?

Ofer Harel
DBA team
Barak ITC
[EMAIL PROTECTED]


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Ofer Harel
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Naveen Nahata
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



RE: Apologies for Off-Topic Question - Where has Oracle-l-OT gone ?

2002-09-19 Thread Thomas, Kevin

Nope :O)

-Original Message-
Sent: 19 September 2002 13:18
To: Multiple recipients of list ORACLE-L



Has the Off-Topic group gone ? 

Steve Parker
E Mail: [EMAIL PROTECTED]
--


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Thomas, Kevin
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



RE: Lost ofall redo logs

2002-09-19 Thread K Gopalakrishnan

Joe:


I understand what you say.. But the situation is
very bad. He does **NOT** have ANY backups.

KG


-Original Message-
Sent: Thursday, September 19, 2002 4:43 AM
To: Multiple recipients of list ORACLE-L


As I stated earlier, right out of the user managed recover docs(loss of
all online redo logs):

you'll need to do the restore from backup, using what archivelogs
available roll forward to point of last archived log and open resetlogs
<--- the abbreviated version.

joe


  Recovering After the Loss of All Members of an Online Redo Log Group

If a media failure damages all members of an online redo log group, then
different scenarios can occur depending on the type of online redo log
group affected by the failure and the archiving mode of the database.

If the damaged log group is inactive, then it is not needed for crash
recovery; if it is active, then it is needed for crash recovery.

*If the group is . . .* *Then . . .* *And you should . . .*

Inactive

It is not needed for crash recovery

Clear the archived or unarchived group.

Active

It is needed for crash recovery

Attempt to issue a checkpoint and clear the log; if impossible, then you
must restore a backup and perform incomplete recovery up to the most
recent available log.

Current

It is the log that Oracle is currently writing to

Attempt to clear the log; if impossible, then you must restore a backup
and perform incomplete recovery up to the most recent available log.

Your first task is to determine whether the damaged group is active or
inactive.


*Loss of Multiple Redo Log Groups*

If you have lost multiple groups of the online redo log, then use the
recovery method for the most difficult log to recover. The order of
difficulty, from most difficult to least difficult, follows:

   1. The current online redo log
   2. An active online redo log
   3. An unarchived online redo log
   4. An inactive online redo log





Arun Chakrapanirao wrote:

>Well one I do want to make clear I have never tried to give bad advice to
>people.
>Since the user in his mail had said that he does not have a backup and does
>not have any online redo logs can you please let me know how else can he
>open the database, when he said he does not have a back it assumes that he
>does not have a os backup and also the rman backup.
>This might be the undocumented thing but this Is the only way it works
>And this had happened in one our case and the only way we could open the
>database is to force open the database in a corrupt mode take an export and
>import back into the new database.
>
>If you still think I am trying to give a bad advice well sorry that I am
>giving bad advices.
>
>
>-Original Message-
>Sent: Thursday, September 19, 2002 12:33 AM
>To: Multiple recipients of list ORACLE-L
>
>Arun, since WHEN has been using undocumented parameters the normal way
>of doing things, I think you need to go back and lookup backup/recovery
>concepts.
>
>Feel free to read both user managed and RMAN backup recovery
docs/scenarios.
>
>You are giving bad advice to people on the list who might be new.
>
>joe
>
>
>Arun Chakrapanirao wrote:
>
>
>
>>The only way u can open your database is to add a parameter file
>>as
>>
>>_allow_resetlogs_corruption=TRUE
>>
>>_corrupted_rollback_segments= TRUE
>>
>>_offline_rollback_segments=(the rollback segment names)
>>
>>activate the database and  then immediately take an export of the whole
>>database.
>>Create a new database and then import all the data to this new database.
>>
>>
>>-Original Message-
>>Sent: Wednesday, September 18, 2002 5:23 PM
>>To: Multiple recipients of list ORACLE-L
>>
>>Nop, all redo logs gone away...
>>
>>- Original Message -
>>To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
>>Sent: Wednesday, September 18, 2002 5:43 PM
>>
>>
>>
>>
>>
>>
>>>What about mirrored redo group members?
>>>
>>>Igor Neyman, OCP DBA
>>>[EMAIL PROTECTED]
>>>
>>>
>>>
>>>- Original Message -
>>>To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
>>>Sent: Wednesday, September 18, 2002 4:30 PM
>>>
>>>
>>>Hi people...
>>>
>>>What to do if I have LOST ALL REDO LOGS and i don´t have any backup?
>>>
>>>Thanks in advance!
>>>
>>>:
>>>Gilberto Gampert  Universidade de Passo Fundo
>>>Administrador de Banco de Dados   Passo Fundo - RS - Bra5il
>>>[EMAIL PROTECTED]http://www.upf.br
>>>:
>>>
>>>
>>>--
>>>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>>>--
>>>Author: Gilberto Gampert
>>> INET: [EMAIL PROTECTED]
>>>
>>>Fat City Network Services-- 858-538-5051 http://www.fatcity.com
>>>San Diego, California-- Mailing list and web hosting services
>>>-
>>>To REMOVE yourself from this mailing list, send an E-Mail mess

Re: RMAN implementation strategy

2002-09-19 Thread Jay Hostetter

Sean,

  I wasn't able to put our catalog database on another server, so after backups I 
export the catalog database and ftp the .dmp file to another machine.
  As far as the 7 year requirement, can you export the relevant data and save the .dmp 
file on tape for 7 years?  Your biggest issue will be upgrades.  When you are on 9i, 
you can't restore an 8i backup.  You would have to keep 8i around for 7 years.  I 
don't purge any of my backup information out of the catalog, in case I have to restore 
an old backup.  But I haven't gone through any upgrades since I've been using rman.  
Important data that may be needed in the future is exported an copied to tape.

Jay

>>> [EMAIL PROTECTED] 09/19/02 06:08AM >>>
Hi Folks,

We're planning to implement RMAN as part of our B&R solution and by
extenstion also as part of our DR solution.  I've been trying to locate
information on how best to configure RMAN across our organisation.  

For example it's advised you place catalog on separate server to production
server.  So server A might house catalog for server B and vice versa.  But
in a DR scenario where both servers could be destroyed there are I 'suspect'
potential implementations on overall MTTR depending on configuration.  Is it
then perhaps better to locate all catalogs on a dedicated server which
ideally would be replicated somehow to eliminate it as a singal point of
failure.

Also we have a requirment to be able to potentially recover data as far back
as 7 years.  These are currently comprised of monthly backups taken out of
regular cycle and archived off site.  I'm thinking it might be an idea to
set up a two catalogs, one for regular monthly cycle and another to record
these monthly archives as the maintenace of the catalog might be cumbersome
trying to ensure the montlhy archive data records do not get accidentally
deleted.

I've had a trawl across the Web courtesy of Google but did not find any
papers which appear to deal with these type of issues.  The RMAN User's
Guide and Reference does not appear to address them either.  Your
feedback/comments or references to papers would be much appeciated!.

Oracle 7.3.3, 8.0.5, 8.1.7 
NT4, W2K 
-
Seán O' Neill
Organon (Ireland) Ltd.
[subscribed: digest mode] 





**DISCLAIMER
This e-mail message and any files transmitted with it are intended for the use of the 
individual or entity to which they are addressed and may contain information that is 
privileged, proprietary and confidential. If you are not the intended recipient, you 
may not use, copy or disclose to anyone the message or any information contained in 
the message. If you have received this communication in error, please notify the 
sender and delete this e-mail message. The contents do not represent the opinion of 
D&E except to the extent that it relates to their official business.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jay Hostetter
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



RE: Index question

2002-09-19 Thread Naveen Nahata

Sorry for the mis-interpretation. I thought the question was if the same SQL
statement is re-executed after dropping the index.

regards
naveen

-Original Message-
Sent: Thursday, September 19, 2002 5:28 PM
To: Multiple recipients of list ORACLE-L


The Query will be re-parsed. As soon as u do a DDL, the dependent queries in
Shared SQL area are invalidated.

Regards
Naveen

-Original Message-
Sent: Thursday, September 19, 2002 4:39 PM
To: Multiple recipients of list ORACLE-L


Good morning,

Suppose there is a long running query (which already parsed) using an index.
Now I dropped the index. What should happened to the running query?

Ofer Harel
DBA team
Barak ITC
[EMAIL PROTECTED]


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Ofer Harel
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Naveen Nahata
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Naveen Nahata
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



RE: Tracking Temp Segment Usage and Event 10046

2002-09-19 Thread Jamadagni, Rajendra
Title: Tracking Temp Segment Usage and Event 10046



Thanks Cary
 
One thing I noticed is some parameters referenced in that document are 
different in 9iR2 ... I guess Wolfgang is probably working on the 9iR2 version 
...
 
It is a very nice paper ...
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!

  -Original Message-From: Cary Millsap 
  [mailto:[EMAIL PROTECTED]]Sent: Thursday, September 19, 2002 
  5:24 AMTo: Multiple recipients of list ORACLE-LSubject: 
  RE: Tracking Temp Segment Usage and Event 10046
  
  For more information 
  about 10053, see Wolfgang Breitling’s http://www.hotsos.com/dnloads/1/10053/Breitling2002.pdf. 
  Wolfgang will present this 
  and another new 10053 paper at our Symposium in Dallas next February (http://www.hotsos.com/events/symposium). 
  
   
  
  Cary 
  MillsapHotsos 
  Enterprises, Ltd.http://www.hotsos.comUpcoming 
  events:- Hotsos 
  Clinic, Oct 1–3 San Francisco, Oct 15–17 Dallas, Dec 9–11 Honolulu- 2003 Hotsos Symposium on 
  Oracle® System Performance, Feb 9–12 Dallas- Next event: Miracle Database Forum, Sep 20–22 
  Middlefart Denmark
   

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: Lost ofall redo logs

2002-09-19 Thread Mercadante, Thomas F

I agree with you Joe.

I would *never* use an undocumented parameter unless Oracle told me to (or
unless I was testing something that I could recreate).

Using these params on the advice of someone from this list is very
dangerous.

The larger question is, why are the redo logs not being backed up?

Arun, you need to establish a backup policy *immediately* so that you are
not being put into this position again.

Tom Mercadante
Oracle Certified Professional


-Original Message-
Sent: Thursday, September 19, 2002 7:48 AM
To: Multiple recipients of list ORACLE-L


KG, doesn't anyone think not having a backup is a bad thing.

You can go down that route if you wish but as I remember undocumented 
parameters are best used with the assistance of OWS and not just used at 
will.

joe


K Gopalakrishnan wrote:

>Arun:
>
>You are right. The original poster *clearly* said
>
>LOST ALL REDO LOGS
>NO BACKUP.
>
>In the above situation, you have to open the
>databases using the some special tricks and
>I don't think you need to go back and refer
>BAckup and Recovery Manuals (You can not
>find anything for this situation, that is
>another story)
>
>Those parameters will bring up the database
>if the database is cleanly shudowned (read:
>Shutdown Normal/Immediate) and you will
>have to do some more tricks to get the
>database up if it is crashed (read: shutdown
>ABORT).
>
>I don't think it is a bad advice..
>
>YMMV
>
>KG
>
>
>-Original Message-
>Chakrapanirao
>Sent: Wednesday, September 18, 2002 11:08 PM
>To: Multiple recipients of list ORACLE-L
>
>
>Well one I do want to make clear I have never tried to give bad advice to
>people.
>Since the user in his mail had said that he does not have a backup and does
>not have any online redo logs can you please let me know how else can he
>open the database, when he said he does not have a back it assumes that he
>does not have a os backup and also the rman backup.
>This might be the undocumented thing but this Is the only way it works
>And this had happened in one our case and the only way we could open the
>database is to force open the database in a corrupt mode take an export and
>import back into the new database.
>
>If you still think I am trying to give a bad advice well sorry that I am
>giving bad advices.
>
>
>-Original Message-
>Sent: Thursday, September 19, 2002 12:33 AM
>To: Multiple recipients of list ORACLE-L
>
>Arun, since WHEN has been using undocumented parameters the normal way
>of doing things, I think you need to go back and lookup backup/recovery
>concepts.
>
>Feel free to read both user managed and RMAN backup recovery
docs/scenarios.
>
>You are giving bad advice to people on the list who might be new.
>
>joe
>
>
>Arun Chakrapanirao wrote:
>
>  
>
>>The only way u can open your database is to add a parameter file
>>as
>>
>>_allow_resetlogs_corruption=TRUE
>>
>>_corrupted_rollback_segments= TRUE
>>
>>_offline_rollback_segments=(the rollback segment names)
>>
>>activate the database and  then immediately take an export of the whole
>>database.
>>Create a new database and then import all the data to this new database.
>>
>>
>>-Original Message-
>>Sent: Wednesday, September 18, 2002 5:23 PM
>>To: Multiple recipients of list ORACLE-L
>>
>>Nop, all redo logs gone away...
>>
>>- Original Message -
>>To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
>>Sent: Wednesday, September 18, 2002 5:43 PM
>>
>>
>>
>>
>>
>>
>>>What about mirrored redo group members?
>>>
>>>Igor Neyman, OCP DBA
>>>[EMAIL PROTECTED]
>>>
>>>
>>>
>>>- Original Message -
>>>To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
>>>Sent: Wednesday, September 18, 2002 4:30 PM
>>>
>>>
>>>Hi people...
>>>
>>>What to do if I have LOST ALL REDO LOGS and i don4t have any backup?
>>>
>>>Thanks in advance!
>>>
>>>:
>>>Gilberto Gampert  Universidade de Passo Fundo
>>>Administrador de Banco de Dados   Passo Fundo - RS - Bra5il
>>>[EMAIL PROTECTED]http://www.upf.br
>>>:
>>>
>>>
>>>--
>>>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>>>--
>>>Author: Gilberto Gampert
>>> INET: [EMAIL PROTECTED]
>>>
>>>Fat City Network Services-- 858-538-5051 http://www.fatcity.com
>>>San Diego, California-- Mailing list and web hosting services
>>>-
>>>To REMOVE yourself from this mailing list, send an E-Mail message
>>>to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
>>>the message BODY, include a line containing: UNSUB ORACLE-L
>>>(or the name of mailing list you want to be removed from).  You may
>>>also send the HELP command for other information (like subscribing).
>>>
>>>--
>>>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>>>--
>>>Author: Igor Neyman
>>> INET: 

Re: Lost ofall redo logsOTHER...

2002-09-19 Thread Bill Christison

Joe T.,
 When we'd recently posted positions to FATCITY you'd
replyed;..the economy must be getting better (based on the
job(s)posting.
 Yes,I agree.However,I'd never tried posting to Fcity.I've
been placing SAS/Statisticians since 1993 and use SAS-L.
 *FYI., I didn't obtain any response,other than yours.If
you've any thoughts(why)or could suggest another location to
post/to make ORACLE people aware of career alternatives,I'd
appreciate your insights,
Bill Christison
--- Joe Testa <[EMAIL PROTECTED]> wrote:
> KG, doesn't anyone think not having a backup is a bad thing.
> 
> You can go down that route if you wish but as I remember
> undocumented 
> parameters are best used with the assistance of OWS and not
> just used at 
> will.
> 
> joe
> 
> 
> K Gopalakrishnan wrote:
> 
> >Arun:
> >
> >You are right. The original poster *clearly* said
> >
> >LOST ALL REDO LOGS
> >NO BACKUP.
> >
> >In the above situation, you have to open the
> >databases using the some special tricks and
> >I don't think you need to go back and refer
> >BAckup and Recovery Manuals (You can not
> >find anything for this situation, that is
> >another story)
> >
> >Those parameters will bring up the database
> >if the database is cleanly shudowned (read:
> >Shutdown Normal/Immediate) and you will
> >have to do some more tricks to get the
> >database up if it is crashed (read: shutdown
> >ABORT).
> >
> >I don't think it is a bad advice..
> >
> >YMMV
> >
> >KG
> >
> >
> >-Original Message-
> >Chakrapanirao
> >Sent: Wednesday, September 18, 2002 11:08 PM
> >To: Multiple recipients of list ORACLE-L
> >
> >
> >Well one I do want to make clear I have never tried to give
> bad advice to
> >people.
> >Since the user in his mail had said that he does not have a
> backup and does
> >not have any online redo logs can you please let me know how
> else can he
> >open the database, when he said he does not have a back it
> assumes that he
> >does not have a os backup and also the rman backup.
> >This might be the undocumented thing but this Is the only way
> it works
> >And this had happened in one our case and the only way we
> could open the
> >database is to force open the database in a corrupt mode take
> an export and
> >import back into the new database.
> >
> >If you still think I am trying to give a bad advice well
> sorry that I am
> >giving bad advices.
> >
> >
> >-Original Message-
> >Sent: Thursday, September 19, 2002 12:33 AM
> >To: Multiple recipients of list ORACLE-L
> >
> >Arun, since WHEN has been using undocumented parameters the
> normal way
> >of doing things, I think you need to go back and lookup
> backup/recovery
> >concepts.
> >
> >Feel free to read both user managed and RMAN backup recovery
> docs/scenarios.
> >
> >You are giving bad advice to people on the list who might be
> new.
> >
> >joe
> >
> >
> >Arun Chakrapanirao wrote:
> >
> >  
> >
> >>The only way u can open your database is to add a parameter
> file
> >>as
> >>
> >>_allow_resetlogs_corruption=TRUE
> >>
> >>_corrupted_rollback_segments= TRUE
> >>
> >>_offline_rollback_segments=(the rollback segment names)
> >>
> >>activate the database and  then immediately take an export
> of the whole
> >>database.
> >>Create a new database and then import all the data to this
> new database.
> >>
> >>
> >>-Original Message-
> >>Sent: Wednesday, September 18, 2002 5:23 PM
> >>To: Multiple recipients of list ORACLE-L
> >>
> >>Nop, all redo logs gone away...
> >>
> >>- Original Message -
> >>To: "Multiple recipients of list ORACLE-L"
> <[EMAIL PROTECTED]>
> >>Sent: Wednesday, September 18, 2002 5:43 PM
> >>
> >>
> >>
> >>
> >>
> >>
> >>>What about mirrored redo group members?
> >>>
> >>>Igor Neyman, OCP DBA
> >>>[EMAIL PROTECTED]
> >>>
> >>>
> >>>
> >>>- Original Message -
> >>>To: "Multiple recipients of list ORACLE-L"
> <[EMAIL PROTECTED]>
> >>>Sent: Wednesday, September 18, 2002 4:30 PM
> >>>
> >>>
> >>>Hi people...
> >>>
> >>>What to do if I have LOST ALL REDO LOGS and i don4t have
> any backup?
> >>>
> >>>Thanks in advance!
> >>>
>
>>>:
> >>>Gilberto Gampert  Universidade de Passo
> Fundo
> >>>Administrador de Banco de Dados   Passo Fundo - RS -
> Bra5il
> >>>[EMAIL PROTECTED]http://www.upf.br
>
>>>:
> >>>
> >>>
> >>>--
> >>>Please see the official ORACLE-L FAQ: http://www.orafaq.com
> >>>--
> >>>Author: Gilberto Gampert
> >>> INET: [EMAIL PROTECTED]
> >>>
> >>>Fat City Network Services-- 858-538-5051
> http://www.fatcity.com
> >>>San Diego, California-- Mailing list and web
> hosting services
>
>>>-
> >>>To REMOVE yourself from this mailing list, send an E-Mail
> message
> >>>to: [EMAIL PROTECTED] (note EXACT spelling of
> 'ListGuru') and in
> >>>the message BODY, includ

Staspack Grapher/Viewer ?

2002-09-19 Thread Walter K

Does anyone have or know of any utilities, preferably 
freeeware or very cheap, that can produce graphs of 
the data collected by statspack?

Thanks VERY much in advance.
-walt

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Walter K
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



Fw: Lost ofall redo logs

2002-09-19 Thread Tim Gorman



Oh yeah!  How could I forget:  make darn 
sure that you are working with Oracle Support actively, step-by-step, if you are 
in a scenario which might call for the use of these parameters.  It would 
be *disastrous* to start down this road if unnecessary, and there are other 
quirks when using them that only OWS can diagnose and overcome...
 
...need my coffee, NOW!
 
- Original Message - 
From: Tim Gorman 
To: [EMAIL PROTECTED] 
Sent: Thursday, September 19, 2002 7:03 AM
Subject: Re: Lost ofall redo logs

One correction...
 
The undocumented "_corrupted_rollback_segments" 
parameter does not take a TRUE/FALSE value setting.  It is another 
list of rollback segment names (similar to "rollback_segments" and 
"_offline_rollback_segments") which will be considered corrupted.  
Roughly, the meaning of these undocumented 
parameters is (and I'm doing this from memory and pre-morning-coffee so 
those who know please correct as needed):

  rollback_segments

  
List of rollback segment names on which to 
perform ALTER ROLLBACK SEGMENT ... ONLINE immediately after opening the 
database instance.

  _offline_rollback_segments

  
List of rollback segments names to not scan 
during OPEN in order to mark transactions as "DEAD" (uncommitted) or "ROLLED 
BACK" (during instance recovery).  Also, these RBS will not be used for 
any new transactions and cannot be brought online.  Since these RBS do 
not have any DEAD transactions after OPEN, then SMON will not attempt 
rollback any txns behind the scenes.  If, while the instance is 
open, an entry in an ITL in a database block refers to one of these RBS, 
then the server process will attempt to read the transaction table in the 
RBS header and also attempt to read undo blocks in the RBS body in order to 
rollback the transaction or achieve a consistent read.  Please 
note that this is *dangerous* and that the instance can become "hung" 
through infinite loops and other nasty stuff while this is being 
performed.  In this event, the instance may have to be killed and the 
RBS involved may have to be listed in the "_corrupted_rollback_segment" 
parameter if there is any hope of using the database 
  again...

  _corrupted_rollback_segments

  
Another list of rollback segments to not scan 
during OPEN and not allow online after OPEN.  Transaction table is not 
scanned (as with "_offline_") during instance recovery and transactions 
within are not rolled back or marked DEAD, ever.  If, after the 
instance is open, an entry in an ITL in a database block refers to one of 
these RBS, then the server process will *not* attempt to reference the RBS 
in order to rollback the transaction or achieve a consistent read.  
Instead, the changes from the transaction will be marked as "committed" as 
is.
I'm sure that you can all see the danger in 
using these parameters.  Transactions that should be rolled back will not 
be.
 
With "_offline", the database will attempt 
to use info within the RBS but will never allow the RBS to be brought online for 
new transactions.  You use "_offline" if you think you can get away with 
salvaging the data in the RBS.
 
With "_corrupted", there is no hope, which 
is usually the situation if the header block of the RBS is corrupted.  In 
this case, the "after-image" data stored in the data segments (i.e. table, 
cluster, and index) are considered as "committed", since there is no way to tell 
if they really were or weren't.  The fact that the "after-images" may have 
been incomplete only adds to the fun and excitement...
 
As Arun had indicated (but not fully 
enough!), a database opened with either of these parameters really cannot be 
"trusted" for future use.  The firm recommendation is to use the database 
instance thus opened merely to salvage as much data as possible for import (one 
way or another) into a freshly-created new database.  Using these parameters will put the database on life-support 
for a short time only, hopefully long enough to extract its data.  It would 
be foolish to entrust new data to it ever again...
 
Hope this helps...
 
-Tim
 
- Original Message - 
From: "K Gopalakrishnan" <[EMAIL PROTECTED]>
To: "Multiple recipients of list ORACLE-L" 
<[EMAIL PROTECTED]>
Sent: Thursday, September 19, 2002 12:58 
AM
Subject: RE: Lost ofall redo logs
> Arun:> > You are right. The original poster *clearly* 
said> > LOST ALL REDO LOGS> NO BACKUP.> > In 
the above situation, you have to open the> databases using the some 
special tricks and> I don't think you need to go back and refer> 
BAckup and Recovery Manuals (You can not> find anything for this 
situation, that is> another story)> > Those parameters will 
bring up the database> if the database is cleanly shudowned 
(read:> Shutdown Normal/Immediate) and you will> have to do some 
more tricks to get the> database up if it is crashed (read: 
shutdown> ABORT).> > I 

Re: Index question

2002-09-19 Thread Alexandre Gorbatchev

> Testing against 8.1.7.4, the drop index was successful and an error was
> reported by my long running query:
>
> ERROR:
> ORA-08103: object no longer exists
Is this because the index is locked by query only when it's used? From one
side looks reasonable, but from another - no good someone can drop an index
user by running query.

Is there any index locking at all??

Regards,
Alexandre

>
> Regards,
>
> Larry G. Elkins
> [EMAIL PROTECTED]
> 214.954.1781
>
> > -Original Message-
> > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Ofer Harel
> > Sent: Thursday, September 19, 2002 6:09 AM
> > To: Multiple recipients of list ORACLE-L
> > Subject: Index question
> >
> >
> > Good morning,
> >
> > Suppose there is a long running query (which already parsed)
> > using an index.
> > Now I dropped the index. What should happened to the running query?
> >
> > Ofer Harel
> > DBA team
> > Barak ITC
> > [EMAIL PROTECTED]
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Larry Elkins
>   INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Alexandre Gorbatchev
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



RE: HELP! CAN't STARTUP REMOTE DATABASE USING SQL*PLUS - BUG?-NA

2002-09-19 Thread Paula_Stankus
Title: HELP! CAN't STARTUP REMOTE DATABASE USING SQL*PLUS - BUG?-NADA on METASTINK



I am 
actually using shutdown immediate - for testing I used shutdown abort just to 
speed up my testing on a database that could take it.  

  -Original Message-
  
 


RE: Why does my insert creates so many logs?

2002-09-19 Thread Gurelei

None.
--- "Nicoll, Iain (Calanais)"
<[EMAIL PROTECTED]> wrote:
> Doesn't have any triggers does it?
> 
> -Original Message-
> Sent: Wednesday, September 18, 2002 8:39 PM
> To: Multiple recipients of list ORACLE-L
> 
> 
> Hi.
> 
> A developer of mine is running a large insert as
> select:
> 
> insert /* parallel hint */ into table A 
> nologging 
> (select * from table b where ...);
> 
> There are no indices on table A and a PK disabled.
> Still that insert generates a large amount of logs.
> What could be the reason for that? Any ideas? Table
> A
> is not partitioned and has NOLOGGING attribute on
> the
> dba_tables set to Yes.
> 
> thanks
> 
> Gene
> 
> __
> Do you Yahoo!?
> Yahoo! News - Today's headlines
> http://news.yahoo.com
> -- 
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> -- 
> Author: Gurelei
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- 858-538-5051
> http://www.fatcity.com
> San Diego, California-- Mailing list and web
> hosting services
>
-
> To REMOVE yourself from this mailing list, send an
> E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of
> 'ListGuru') and in
> the message BODY, include a line containing: UNSUB
> ORACLE-L
> (or the name of mailing list you want to be removed
> from).  You may
> also send the HELP command for other information
> (like subscribing).
> -- 
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> -- 
> Author: Nicoll, Iain \(Calanais\)
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- 858-538-5051
> http://www.fatcity.com
> San Diego, California-- Mailing list and web
> hosting services
>
-
> To REMOVE yourself from this mailing list, send an
> E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of
> 'ListGuru') and in
> the message BODY, include a line containing: UNSUB
> ORACLE-L
> (or the name of mailing list you want to be removed
> from).  You may
> also send the HELP command for other information
> (like subscribing).


__
Do you Yahoo!?
New DSL Internet Access from SBC & Yahoo!
http://sbc.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Gurelei
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



Re: Lost ofall redo logs

2002-09-19 Thread Ray Stell


There is the other expensive avenue of calling in Oracle and
the DUL team.  It depends on the criticality of the data.
A few years ago a former dba here had the experience and 
it is said to have cost a mere $20K.  Maybe the data is 
worth more than that. 




On Wed, Sep 18, 2002 at 10:08:19PM -0800, Arun Chakrapanirao wrote:
> Well one I do want to make clear I have never tried to give bad advice to
> people.
> Since the user in his mail had said that he does not have a backup and does
> not have any online redo logs can you please let me know how else can he
> open the database, when he said he does not have a back it assumes that he
> does not have a os backup and also the rman backup.
> This might be the undocumented thing but this Is the only way it works
> And this had happened in one our case and the only way we could open the
> database is to force open the database in a corrupt mode take an export and
> import back into the new database.
> 
> If you still think I am trying to give a bad advice well sorry that I am
> giving bad advices.
> 
> 
> -Original Message-
> Sent: Thursday, September 19, 2002 12:33 AM
> To: Multiple recipients of list ORACLE-L
> 
> Arun, since WHEN has been using undocumented parameters the normal way 
> of doing things, I think you need to go back and lookup backup/recovery 
> concepts.
> 
> Feel free to read both user managed and RMAN backup recovery docs/scenarios.
> 
> You are giving bad advice to people on the list who might be new.
> 
> joe
> 
> 
> Arun Chakrapanirao wrote:
> 
> >The only way u can open your database is to add a parameter file
> >as
> >
> >_allow_resetlogs_corruption=TRUE
> >
> >_corrupted_rollback_segments= TRUE
> >
> >_offline_rollback_segments=(the rollback segment names)
> >
> >activate the database and  then immediately take an export of the whole
> >database.
> >Create a new database and then import all the data to this new database.
> >
> >
> >-Original Message-
> >Sent: Wednesday, September 18, 2002 5:23 PM
> >To: Multiple recipients of list ORACLE-L
> >
> >Nop, all redo logs gone away...
> >
> >- Original Message -
> >To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
> >Sent: Wednesday, September 18, 2002 5:43 PM
> >
> >
> >  
> >
> >>What about mirrored redo group members?
> >>
> >>Igor Neyman, OCP DBA
> >>[EMAIL PROTECTED]
> >>
> >>
> >>
> >>- Original Message -
> >>To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
> >>Sent: Wednesday, September 18, 2002 4:30 PM
> >>
> >>
> >>Hi people...
> >>
> >>What to do if I have LOST ALL REDO LOGS and i don?t have any backup?
> >>
> >>Thanks in advance!
> >>
> >>:
> >>Gilberto Gampert  Universidade de Passo Fundo
> >>Administrador de Banco de Dados   Passo Fundo - RS - Bra5il
> >>[EMAIL PROTECTED]http://www.upf.br
> >>:
> >>
> >>
> >>--
> >>Please see the official ORACLE-L FAQ: http://www.orafaq.com
> >>--
> >>Author: Gilberto Gampert
> >>  INET: [EMAIL PROTECTED]
> >>
> >>Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> >>San Diego, California-- Mailing list and web hosting services
> >>-
> >>To REMOVE yourself from this mailing list, send an E-Mail message
> >>to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> >>the message BODY, include a line containing: UNSUB ORACLE-L
> >>(or the name of mailing list you want to be removed from).  You may
> >>also send the HELP command for other information (like subscribing).
> >>
> >>--
> >>Please see the official ORACLE-L FAQ: http://www.orafaq.com
> >>--
> >>Author: Igor Neyman
> >>  INET: [EMAIL PROTECTED]
> >>
> >>Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> >>San Diego, California-- Mailing list and web hosting services
> >>-
> >>To REMOVE yourself from this mailing list, send an E-Mail message
> >>to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> >>the message BODY, include a line containing: UNSUB ORACLE-L
> >>(or the name of mailing list you want to be removed from).  You may
> >>also send the HELP command for other information (like subscribing).
> >>
> >>
> >>
> >
> >  
> >
> 
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> -- 
> Author: Joe Testa
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: U

RE: Privileges needed for truncate

2002-09-19 Thread Jamadagni, Rajendra
Title: RE: Privileges needed for truncate





Alter any table


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!



-Original Message-
From: Jack van Zanen [mailto:[EMAIL PROTECTED]]
Sent: Thursday, September 19, 2002 10:24 AM
To: Multiple recipients of list ORACLE-L
Subject: Privileges needed for truncate



Hi



I need to create a user/role that among other stuff must be able to
truncate a table. I can't figure out which privileges are needed (DBA is a
bit OTT :-))
Try them one by one does not sound appealing at all


TIA



Jack




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: BMC tables

2002-09-19 Thread Bob Metelsky

> 
> they're from BMC's SQL Explorer product.
> either you or ??? demo'd the product or own it.
> Drop them if you want. If you were the one that tested SQL 
> Explorer,  you can uninstall the tables using the product.
> 
Thanks!

drop table BMC_ISE_CASES cascade constraints ;
drop table BMC_ISE_CLASSES cascade constraints ;
drop table BMC_ISE_EVENTS cascade constraints ;
drop table BMC_ISE_FINDINGS cascade constraints ;
drop table BMC_ISE_F_TEXT cascade constraints ;
drop table BMC_ISE_GROUPS cascade constraints ;
drop table BMC_ISE_INDCOLS cascade constraints ;
drop table BMC_ISE_INDUSE cascade constraints ;
drop table BMC_ISE_RUNS cascade constraints ;
drop table BMC_ISE_TABUSE cascade constraints ;
drop table BMC_ISE_V$SQL cascade constraints ;
drop table BMC_ISE_V$SQLTEXT cascade constraints ;
drop table BMC_SQLEX_CALIBRATE_S01 cascade constraints ;
drop table BMC_SQLEX_CFG_S05 cascade constraints ;
drop table BMC_SQLEX_PLAN_TABLE_S05 cascade constraints ;

bob
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Bob Metelsky
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



RE: Query performance improvement ?

2002-09-19 Thread DENNIS WILLIAMS

David
   - I'm assuming this query is being run interactively? 
   - I recently posted a request for help on a query, and the winning
solution was to add a FIRST_ROWS hint, so naturally at this point I'm
convinced this will solve all performance issues.
   - Knowing how many rows are being returned will be helpful in planning
alternate strategies.
   - Try putting an index on your podata table.
   - How selective is your ownerid column? That is, how many distinct values
are in that column?
   - Have you considered bitmapped indexes?
   - I get by implication that you found this query in the SQL buffer and
have started there. I would suggest tracking down the application and users
to find out more about how and when they use this query. It also makes you
seem more proactive, which you are being, so you might as well get credit.
   - As to the distinct qualifier, here is a tip I found via Google:

21. Use EXISTS in Place of DISTINCT

Avoid joins that require the DISTINCT qualifier on the SELECT list when you
submit queries used to determine information at the owner end of a
one-to-many relationship (e.g. departments that have many employees).

For example:

Least Efficient :
SELECT DISTINCT DEPT_NO, DEPT_NAME 
FROMDEPT D, EMP E 
WHERE  D.DEPT_NO = E.DEPT_NO

Most Efficient :
SELECT DEPT_NO, DEPT_NAME
FROMDEPT D
WHERE EXISTS (SELECT   'X'
  FROM EMP E
  WHERE   E.DEPT_NO = D.DEPT_NO);
EXISTS is a faster alternative because the RDBMS kernel realizes that when
the sub-query has been satisfied once, the query can be terminated. 
 
Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED]  


-Original Message-
Sent: Wednesday, September 18, 2002 8:08 PM
To: Multiple recipients of list ORACLE-L


Dennis:

1. The distinct seems difficult to avoid.
2. SQL> select count(*) from podata;

  COUNT(*)
--
 18679
SQL> select count(*) from InvData;

  COUNT(*)
--
 83315
3. The query returned no rows at this moment, the reason
I want to tune this query is because it has been recorded
by statspack.

  Buffer GetsExecutions  Gets per Exec  % Total  Hash Value
---  -- --- 
233,835  357  655.021.3   1783599440
select  distinct A.*   from POData A  , InvData B  where  A.ID =
B.PURCHASEORDERID AND A.OWNERID=B.OWNERID  and A.ownerId = 1  a
nd B.Status = 12  order by  A.ID
4. This query is running under a production system
5. The table just been analyzed recently

SQL> select TABLE_NAME, LAST_ANALYZED from user_tables where table_name in 
('PODATA', 'INVDATA');
TABLE_NAME LAST_ANAL
-- -
INVDATA15-SEP-02
PODATA 15-SEP-02


David Jones
ITResource



-Original Message-
WILLIAMS
Sent: Wednesday, September 18, 2002 3:28 PM
To: Multiple recipients of list ORACLE-L


David
   A couple of observations and questions:
 - The distinct statement looks fairly expensive. Is there a way to
avoid it?
 - Are there any columns on POData? How many rows are in this table?
 - How many rows does this query return?
 - Do the tables contain the number of rows they will in production?
 - Have you analyzed the tables recently?

 
Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED] 


-Original Message-
Sent: Wednesday, September 18, 2002 4:23 PM
To: Multiple recipients of list ORACLE-L


Dear Lister:

Is there any way to further improve the following query performance ?

Thanks

David Jones
ITResource

SQL> select  distinct A.*
>from POData A  , InvData B  where  A.OWNERID=B.OWNERID AND A.ID = 
>B.PURCHASEORDERID
>and B.Status = 12 and A.ownerId = 1   order by  A.ID;



Execution Plan
--
   0  SELECT STATEMENT Optimizer=CHOOSE (Cost=398 Card=8971
Bytes=663854)
   10   SORT (UNIQUE) (Cost=284 Card=8971 Bytes=663854)
   21 HASH JOIN (Cost=170 Card=8971 Bytes=663854)
   32   INDEX (FAST FULL SCAN) OF 'INVDATA_2' (NON-UNIQUE) (Cost=99
Card=8971 Bytes=71768)
   42   TABLE ACCESS (FULL) OF 'PODATA' (Cost=36 Card=18152
Bytes=1198032)

Statistics
--
  0  recursive calls
  4  db block gets
656  consistent gets
  0  physical reads
  0  redo size
   1356  bytes sent via SQL*Net to client
314  bytes received via SQL*Net from client
  1  SQL*Net roundtrips to/from client
  3  sorts (memory)
  0  sorts (disk)
  0  rows processed

SQL> desc PODATA
Name  Null?Type
- 

IDNOT NULL NUMBER(38)
HID

RE: Staspack Grapher/Viewer ?

2002-09-19 Thread George Leonard (ZA)

http://www.statsviewer.narod.ru

fantastic product.

George

George Leonard
Oracle Database Administrator
Dimension Data (Pty) Ltd
(Reg. No. 1987/006597/07)
Tel: (+27 11) 575 0573
Fax: (+27 11) 576 0573
E-mail:[EMAIL PROTECTED]
Web:   http://www.didata.co.za
 
You Have The Obligation to Inform One Honestly of the risk, And As a Person
You Are Committed to Educate Yourself to the Total Risk In Any Activity!
Once Informed & Totally Aware of the Risk, Every Fool Has the Right to Kill
or Injure Themselves as They See Fit!

-Original Message-
Sent: 19 September 2002 15:39 PM
To: Multiple recipients of list ORACLE-L

Does anyone have or know of any utilities, preferably 
freeeware or very cheap, that can produce graphs of 
the data collected by statspack?

Thanks VERY much in advance.
-walt

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Walter K
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).


**
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
the system manager.

This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.

www.mimesweeper.com
**

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: George Leonard (ZA)
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



RE: Oracle Installer not launching in NT -- solved

2002-09-19 Thread Kitty Luo

For your information, the workaround I used is to download oracle 9i client
and install the 9i installer, then install oracle 8.1.7 software
successfully.

Thanks to all who response to my problems.

Kitty

-Original Message-
[EMAIL PROTECTED]
Sent: 17 September, 2002 11:03 AM
To: Multiple recipients of list ORACLE-L


Try installing Java VM and retry. Download Java from www.java.sun.com.

I ever got such a problem on TRU64 Unix. I installed Java, also changed the
parameters used to mount CD ROM. This SHOULD NOT BE the case with NT.

-Original Message-
Sent: Tuesday, September 17, 2002 5:13 PM
To: Multiple recipients of list ORACLE-L


The OUI does not launch at all. I also tried what the note 131299.1
suggests, still not working.

After I download the copy of  symcjit.dll described by Mark
http://www.cool-tools.co.uk/products/downloads/symcjit.dll, I receive the
following error message: jvm.dll could not be found.

Any ideas are appreciated!

Kitty


-Original Message-
Dave
Sent: 17 September, 2002 7:38 AM
To: Multiple recipients of list ORACLE-L


Is the problem that the OUI does not launch or that it launches, does some
installing and then suddenly stops??

Dave

-Original Message-
Sent: Monday, September 16, 2002 9:58 PM
To: Multiple recipients of list ORACLE-L


I have s similar problem (Oracle Installer 8.1.6 / 8.1.7 not launching in
NT) but on a Pentium 3 with NT4 SP5, anyone has a workround?

Many thanks.

Kitty

-Original Message-
Heisler
Sent: 25 July, 2002 12:13 PM
To: Multiple recipients of list ORACLE-L


If it's a Pentium 4, it's a known bug.  See MetaLink note 131299.1.  The
patch is 1507768.


Dennis



> [EMAIL PROTECTED] wrote:
>
> Hi List,
>
> I saw a similar post to this in the last 2 weeks, but deleted it
> (oops) as I am in a unix shop.  But I now have a client who is
> trying to install the oracle client on his nt box in order to demo our
> *product*.
>
> However, he can't get the installer to even launch.  He thinks its
> a Java VM conflict.
>
> Any ideas?  I need this rather quick so please also reply direct
> to me and in the meantime I will hit the internet.
>
> Thanks,
>
> Hannah
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Dennis M. Heisler
  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: Kitty Luo
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Farnsworth, Dave
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Kitty Luo
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, Californ

RE: Staspack Grapher/Viewer ?

2002-09-19 Thread Kevin Lange

We use MRTG to produce graphs for data from different sources  network
stats, hardware stats, database stats.

Maybe you could use it for statspack.



-Original Message-
Sent: Thursday, September 19, 2002 8:39 AM
To: Multiple recipients of list ORACLE-L


Does anyone have or know of any utilities, preferably 
freeeware or very cheap, that can produce graphs of 
the data collected by statspack?

Thanks VERY much in advance.
-walt

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Walter K
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Kevin Lange
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



Database Performance Tuning and Optimization: With Examples from Oracle 8I

2002-09-19 Thread Grabowy, Chris

Anyone know anything about this book that just showed up on Amazon's website??

Database Performance Tuning and Optimization: With Examples from Oracle 8I 
by Sitansu S. Mittra 
Publication date: October 2002 
Publisher: Springer Verlag Pub (Computer Bks) 
Binding:Hardcover 
Subjects: Database management; Oracle (Computer file); Relational Databases 


http://www.amazon.com/exec/obidos/ASIN/0387953930/ref%3Ds%5Fe9/002-3914453-4659241
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Grabowy, Chris
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



RE: Lost ofall redo logs

2002-09-19 Thread Viral Desai

This is very rare that one would loose every online log files for the 
instance.

Hey, I don't think the advice here could be bad, it is the discretion of the 
user who is going to use it.

KG and/or Arun,

Anyways, I'm more interested in KG's comment about "you will
have to do some more tricks to get the database up if it is crashed (read: 
shutdown ABORT)."

Could you please elaborate please?

Thanks,
Viral Desai.



>From: "Mercadante, Thomas F" <[EMAIL PROTECTED]>
>Reply-To: [EMAIL PROTECTED]
>To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
>Subject: RE: Lost ofall redo logs
>Date: Thu, 19 Sep 2002 05:13:27 -0800
>
>I agree with you Joe.
>
>I would *never* use an undocumented parameter unless Oracle told me to (or
>unless I was testing something that I could recreate).
>
>Using these params on the advice of someone from this list is very
>dangerous.
>
>The larger question is, why are the redo logs not being backed up?
>
>Arun, you need to establish a backup policy *immediately* so that you are
>not being put into this position again.
>
>Tom Mercadante
>Oracle Certified Professional
>
>
>-Original Message-
>Sent: Thursday, September 19, 2002 7:48 AM
>To: Multiple recipients of list ORACLE-L
>
>
>KG, doesn't anyone think not having a backup is a bad thing.
>
>You can go down that route if you wish but as I remember undocumented
>parameters are best used with the assistance of OWS and not just used at
>will.
>
>joe
>
>
>K Gopalakrishnan wrote:
>
> >Arun:
> >
> >You are right. The original poster *clearly* said
> >
> >LOST ALL REDO LOGS
> >NO BACKUP.
> >
> >In the above situation, you have to open the
> >databases using the some special tricks and
> >I don't think you need to go back and refer
> >BAckup and Recovery Manuals (You can not
> >find anything for this situation, that is
> >another story)
> >
> >Those parameters will bring up the database
> >if the database is cleanly shudowned (read:
> >Shutdown Normal/Immediate) and you will
> >have to do some more tricks to get the
> >database up if it is crashed (read: shutdown
> >ABORT).
> >
> >I don't think it is a bad advice..
> >
> >YMMV
> >
> >KG
> >
> >
> >-Original Message-
> >Chakrapanirao
> >Sent: Wednesday, September 18, 2002 11:08 PM
> >To: Multiple recipients of list ORACLE-L
> >
> >
> >Well one I do want to make clear I have never tried to give bad advice to
> >people.
> >Since the user in his mail had said that he does not have a backup and 
>does
> >not have any online redo logs can you please let me know how else can he
> >open the database, when he said he does not have a back it assumes that 
>he
> >does not have a os backup and also the rman backup.
> >This might be the undocumented thing but this Is the only way it works
> >And this had happened in one our case and the only way we could open the
> >database is to force open the database in a corrupt mode take an export 
>and
> >import back into the new database.
> >
> >If you still think I am trying to give a bad advice well sorry that I am
> >giving bad advices.
> >
> >
> >-Original Message-
> >Sent: Thursday, September 19, 2002 12:33 AM
> >To: Multiple recipients of list ORACLE-L
> >
> >Arun, since WHEN has been using undocumented parameters the normal way
> >of doing things, I think you need to go back and lookup backup/recovery
> >concepts.
> >
> >Feel free to read both user managed and RMAN backup recovery
>docs/scenarios.
> >
> >You are giving bad advice to people on the list who might be new.
> >
> >joe
> >
> >
> >Arun Chakrapanirao wrote:
> >
> >
> >
> >>The only way u can open your database is to add a parameter file
> >>as
> >>
> >>_allow_resetlogs_corruption=TRUE
> >>
> >>_corrupted_rollback_segments= TRUE
> >>
> >>_offline_rollback_segments=(the rollback segment names)
> >>
> >>activate the database and  then immediately take an export of the whole
> >>database.
> >>Create a new database and then import all the data to this new database.
> >>
> >>
> >>-Original Message-
> >>Sent: Wednesday, September 18, 2002 5:23 PM
> >>To: Multiple recipients of list ORACLE-L
> >>
> >>Nop, all redo logs gone away...
> >>
> >>- Original Message -
> >>To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
> >>Sent: Wednesday, September 18, 2002 5:43 PM
> >>
> >>
> >>
> >>
> >>
> >>
> >>>What about mirrored redo group members?
> >>>
> >>>Igor Neyman, OCP DBA
> >>>[EMAIL PROTECTED]
> >>>
> >>>
> >>>
> >>>- Original Message -
> >>>To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
> >>>Sent: Wednesday, September 18, 2002 4:30 PM
> >>>
> >>>
> >>>Hi people...
> >>>
> >>>What to do if I have LOST ALL REDO LOGS and i don4t have any backup?
> >>>
> >>>Thanks in advance!
> >>>
> >>>:
> >>>Gilberto Gampert  Universidade de Passo Fundo
> >>>Administrador de Banco de Dados   Passo Fundo - RS - Bra5il
> >>>[EMAIL PROTECTED]

RE: Staspack Grapher/Viewer ?

2002-09-19 Thread Mark Leith

This one isn't free, it's charged on a per instance basis, looks quite nice
though:

http://www.geocities.com/alexdabr/


===
 Mark Leith | T: +44 (0)1905 330 281
 Sales & Marketing  | F: +44 (0)870 127 5283
 Cool Tools UK Ltd  | E: [EMAIL PROTECTED]
===
   http://www.cool-tools.co.uk
   Maximising throughput & performance


-Original Message-
Sent: 19 September 2002 14:39
To: Multiple recipients of list ORACLE-L


Does anyone have or know of any utilities, preferably
freeeware or very cheap, that can produce graphs of
the data collected by statspack?

Thanks VERY much in advance.
-walt

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Walter K
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Mark Leith
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



Privileges needed for truncate

2002-09-19 Thread Jack van Zanen

Hi


I need to create a user/role that among other stuff must be able to
truncate a table. I can't figure out which privileges are needed (DBA is a
bit OTT :-))
Try them one by one does not sound appealing at all

TIA


Jack

===
De informatie verzonden in dit e-mailbericht is vertrouwelijk en is
uitsluitend bestemd voor de geadresseerde. Openbaarmaking,
vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan
derden is, behoudens voorafgaande schriftelijke toestemming van Ernst &
Young, niet toegestaan. Ernst & Young staat niet in voor de juiste en
volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch
voor tijdige ontvangst daarvan. Ernst & Young kan niet garanderen dat een
verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten
worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden.

Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u
vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender
en het origineel en eventuele kopieën te verwijderen en te vernietigen.

Ernst & Young hanteert bij de uitoefening van haar werkzaamheden algemene
voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De
algemene voorwaarden worden u op verzoek kosteloos toegezonden.
=
The information contained in this communication is confidential and is
intended solely for the use of the individual or entity to whom it is
addressed. You should not copy, disclose or distribute this communication
without the authority of Ernst & Young. Ernst & Young is neither liable for
the proper and complete transmission of the information contained in this
communication nor for any delay in its receipt. Ernst & Young does not
guarantee that the integrity of this communication has been maintained nor
that the communication is free of viruses, interceptions or interference.

If you are not the intended recipient of this communication please return
the communication to the sender and delete and destroy all copies.

In carrying out its engagements, Ernst & Young applies general terms and
conditions, which contain a clause that limits its liability. A copy of
these terms and conditions is available on request free of charge.
===





--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jack van Zanen
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



Re: Lost ofall redo logs

2002-09-19 Thread Tim Gorman



One correction...
 
The undocumented "_corrupted_rollback_segments" 
parameter does not take a TRUE/FALSE value setting.  It is another 
list of rollback segment names (similar to "rollback_segments" and 
"_offline_rollback_segments") which will be considered corrupted.  
Roughly, the meaning of these undocumented 
parameters is (and I'm doing this from memory and pre-morning-coffee so 
those who know please correct as needed):

  rollback_segments

  
List of rollback segment names on which to 
perform ALTER ROLLBACK SEGMENT ... ONLINE immediately after opening the 
database instance.

  _offline_rollback_segments

  
List of rollback segments names to not scan 
during OPEN in order to mark transactions as "DEAD" (uncommitted) or "ROLLED 
BACK" (during instance recovery).  Also, these RBS will not be used for 
any new transactions and cannot be brought online.  Since these RBS do 
not have any DEAD transactions after OPEN, then SMON will not attempt 
rollback any txns behind the scenes.  If, while the instance is 
open, an entry in an ITL in a database block refers to one of these RBS, 
then the server process will attempt to read the transaction table in the 
RBS header and also attempt to read undo blocks in the RBS body in order to 
rollback the transaction or achieve a consistent read.  Please 
note that this is *dangerous* and that the instance can become "hung" 
through infinite loops and other nasty stuff while this is being 
performed.  In this event, the instance may have to be killed and the 
RBS involved may have to be listed in the "_corrupted_rollback_segment" 
parameter if there is any hope of using the database 
  again...

  _corrupted_rollback_segments

  
Another list of rollback segments to not scan 
during OPEN and not allow online after OPEN.  Transaction table is not 
scanned (as with "_offline_") during instance recovery and transactions 
within are not rolled back or marked DEAD, ever.  If, after the 
instance is open, an entry in an ITL in a database block refers to one of 
these RBS, then the server process will *not* attempt to reference the RBS 
in order to rollback the transaction or achieve a consistent read.  
Instead, the changes from the transaction will be marked as "committed" as 
is.
I'm sure that you can all see the danger in 
using these parameters.  Transactions that should be rolled back will not 
be.
 
With "_offline", the database will attempt 
to use info within the RBS but will never allow the RBS to be brought online for 
new transactions.  You use "_offline" if you think you can get away with 
salvaging the data in the RBS.
 
With "_corrupted", there is no hope, which 
is usually the situation if the header block of the RBS is corrupted.  In 
this case, the "after-image" data stored in the data segments (i.e. table, 
cluster, and index) are considered as "committed", since there is no way to tell 
if they really were or weren't.  The fact that the "after-images" may have 
been incomplete only adds to the fun and excitement...
 
As Arun had indicated (but not fully 
enough!), a database opened with either of these parameters really cannot be 
"trusted" for future use.  The firm recommendation is to use the database 
instance thus opened merely to salvage as much data as possible for import (one 
way or another) into a freshly-created new database.  Using these parameters will put the database on life-support 
for a short time only, hopefully long enough to extract its data.  It would 
be foolish to entrust new data to it ever again...
 
Hope this helps...
 
-Tim
 
- Original Message - 
From: "K Gopalakrishnan" <[EMAIL PROTECTED]>
To: "Multiple recipients of list ORACLE-L" 
<[EMAIL PROTECTED]>
Sent: Thursday, September 19, 2002 12:58 
AM
Subject: RE: Lost ofall redo logs
> Arun:> > You are right. The original poster *clearly* 
said> > LOST ALL REDO LOGS> NO BACKUP.> > In 
the above situation, you have to open the> databases using the some 
special tricks and> I don't think you need to go back and refer> 
BAckup and Recovery Manuals (You can not> find anything for this 
situation, that is> another story)> > Those parameters will 
bring up the database> if the database is cleanly shudowned 
(read:> Shutdown Normal/Immediate) and you will> have to do some 
more tricks to get the> database up if it is crashed (read: 
shutdown> ABORT).> > I don't think it is a bad 
advice..> > YMMV> > KG> > > 
-Original Message-> Chakrapanirao> Sent: Wednesday, 
September 18, 2002 11:08 PM> To: Multiple recipients of list 
ORACLE-L> > > Well one I do want to make clear I have never 
tried to give bad advice to> people.> Since the user in his mail 
had said that he does not have a backup and does> not have any online 
redo logs can you please let me know how else can he> open the database, 
when he said he does not have a back it assumes that he> does not have

from the business desk

2002-09-19 Thread Ray Stell


"Meanwhile, Oracle's disclosure Tuesday that it expects its next quarter
to be weak (with no sure signs of a recovery)..."  "Oracle dropped 71 
cents, or 7.9%, to $8.32."
===
Ray Stell   [EMAIL PROTECTED] (540) 231-4109 KE4TJC28^D
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Ray Stell
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



RE: Apologies for Off-Topic Question - Where has Oracle-l-OT gone

2002-09-19 Thread Kevin Lange



Its 
still out there on Yahoo Groups.

  -Original Message-From: [EMAIL PROTECTED] 
  [mailto:[EMAIL PROTECTED]]Sent: Thursday, September 19, 2002 
  7:18 AMTo: Multiple recipients of list ORACLE-LSubject: 
  Apologies for Off-Topic Question - Where has Oracle-l-OT gone 
  ?Has the Off-Topic group 
  gone ? Steve ParkerE Mail: 
  [EMAIL PROTECTED]        
  --


Re: RMAN implementation strategy

2002-09-19 Thread Ruth Gramolini

I use one recovery catalog for all of the databases I backup.  I do a cold
backup of the recovery catalog database once a week.  It is in archivelog
mode and I backup the archived logs with the database.

I keep my recovery catalog database on a separate disk.  I don't have a
separate machine.  I will be moving it the devopement and reporting  box
soon.

HTH,
Ruth
- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Thursday, September 19, 2002 6:08 AM


Hi Folks,

We're planning to implement RMAN as part of our B&R solution and by
extenstion also as part of our DR solution.  I've been trying to locate
information on how best to configure RMAN across our organisation.

For example it's advised you place catalog on separate server to production
server.  So server A might house catalog for server B and vice versa.  But
in a DR scenario where both servers could be destroyed there are I 'suspect'
potential implementations on overall MTTR depending on configuration.  Is it
then perhaps better to locate all catalogs on a dedicated server which
ideally would be replicated somehow to eliminate it as a singal point of
failure.

Also we have a requirment to be able to potentially recover data as far back
as 7 years.  These are currently comprised of monthly backups taken out of
regular cycle and archived off site.  I'm thinking it might be an idea to
set up a two catalogs, one for regular monthly cycle and another to record
these monthly archives as the maintenace of the catalog might be cumbersome
trying to ensure the montlhy archive data records do not get accidentally
deleted.

I've had a trawl across the Web courtesy of Google but did not find any
papers which appear to deal with these type of issues.  The RMAN User's
Guide and Reference does not appear to address them either.  Your
feedback/comments or references to papers would be much appeciated!.

Oracle 7.3.3, 8.0.5, 8.1.7
NT4, W2K
-
Seán O' Neill
Organon (Ireland) Ltd.
[subscribed: digest mode]

This message, including attached files, may contain confidential
information and is intended only for the use by the individual
and/or the entity to which it is addressed. Any unauthorized use,
dissemination of, or copying of the information contained herein is
not allowed and may lead to irreparable harm and damage for which
you may be held liable. If you receive this message in error or if
it is intended for someone else please notify the sender by
returning this e-mail immediately and delete the message.

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: O'Neill, Sean
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Ruth Gramolini
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



RE: RMAN implementation strategy

2002-09-19 Thread DENNIS WILLIAMS

Sean
   Yes, remember that the real reason you keep the RMAN catalog on another
system is not for disaster recovery, but for normal recoveries. The worst
situation is where you keep the catalog in the instance you are backing up.
The second worst situation is where the recovery catalog is in a separate
instance that shares some disks with the instance it is backing up. Lose a
disk and you can't recover. Again, this isn't designed for disaster
recovery, but for normal recoveries.
   I agree with Jay, the best procedure is to take an export of your RMAN
catalog and FTP it to the server being backed up so it gets on the backup
tape.
   RMAN is not the easiest to test a disaster recovery situation. The one
factor that makes things easier is that even though you use a catalog, RMAN
also writes its information to the control file. You can perform a disaster
recovery with the control file alone. Several of us have done it. There is a
parameter that controls how long the RMAN information will stay in the
control file. I would suggest leaving that alone.
   I agree with Jay, that over a 7 year period you should consider exports
as being less vulnerable to change. After 7 years, is this data still in
your production database, or has it been deleted? For example, we have data
that old in some of our databases, but we don't apply extraordinary measures
to ensure it doesn't get deleted. For example, if a user deleted some old
data, it might not be known immediately. You might consider regular audits
to ensure the old data is intact. Anyway, I don't think this has anything to
do with RMAN. I suppose you could maintain backup records that far back, but
it would be cumbersome. One product you may want to look at is Princeton
Softech Active Archiving. If I had an absolute requirement for 7 year
retention, that is what I would use.
   RMAN is a strange product in that it works differently than you would
assume and it takes awhile to get your head around how it works. A book that
really helped me get started is Oracle Backup and Recovery 101. Robert
Freeman who participates on this list has one coming out soon.
   

 
Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED]  


-Original Message-
Sent: Thursday, September 19, 2002 5:08 AM
To: Multiple recipients of list ORACLE-L


Hi Folks,

We're planning to implement RMAN as part of our B&R solution and by
extenstion also as part of our DR solution.  I've been trying to locate
information on how best to configure RMAN across our organisation.  

For example it's advised you place catalog on separate server to production
server.  So server A might house catalog for server B and vice versa.  But
in a DR scenario where both servers could be destroyed there are I 'suspect'
potential implementations on overall MTTR depending on configuration.  Is it
then perhaps better to locate all catalogs on a dedicated server which
ideally would be replicated somehow to eliminate it as a singal point of
failure.

Also we have a requirment to be able to potentially recover data as far back
as 7 years.  These are currently comprised of monthly backups taken out of
regular cycle and archived off site.  I'm thinking it might be an idea to
set up a two catalogs, one for regular monthly cycle and another to record
these monthly archives as the maintenace of the catalog might be cumbersome
trying to ensure the montlhy archive data records do not get accidentally
deleted.

I've had a trawl across the Web courtesy of Google but did not find any
papers which appear to deal with these type of issues.  The RMAN User's
Guide and Reference does not appear to address them either.  Your
feedback/comments or references to papers would be much appeciated!.

Oracle 7.3.3, 8.0.5, 8.1.7 
NT4, W2K 
-
Seán O' Neill
Organon (Ireland) Ltd.
[subscribed: digest mode] 

This message, including attached files, may contain confidential
information and is intended only for the use by the individual
and/or the entity to which it is addressed. Any unauthorized use,
dissemination of, or copying of the information contained herein is
not allowed and may lead to irreparable harm and damage for which
you may be held liable. If you receive this message in error or if
it is intended for someone else please notify the sender by
returning this e-mail immediately and delete the message.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: O'Neill, Sean
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the mess

sqlplus question

2002-09-19 Thread Carle, William T (Bill), ALCAS

Howdy,

I am spooling my sqlplus output to a file with no headings and all the fields 
separated by a delimiter. I have a field that is defined as varchar2(56), but 
typically only 4 or 5 bytes are filled. Oracle recognizes that and if you select 
length(fld1) from the table, you will get 4. But if I spool this to a file, I always 
get the full 56 bytes padded with blanks. In other words, I get 4 bytes of data and 52 
blanks for that field. I only want the four valid bytes so that my delimiter comes 
immediately after that 4th byte. My sqlplus options are as follows:

set newpage 0 space 0 linesize 5000 pagesize 0 echo off recsep off feedback off
heading off trimspool on colsep "|"


Bill Carle
AT&T
Database Administrator
816-995-3922
[EMAIL PROTECTED]

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Carle, William T (Bill), ALCAS
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



RE: Privileges needed for truncate

2002-09-19 Thread Khedr, Waleed

Unfortunately drop any table.

-Original Message-
Sent: Thursday, September 19, 2002 10:24 AM
To: Multiple recipients of list ORACLE-L


Hi


I need to create a user/role that among other stuff must be able to
truncate a table. I can't figure out which privileges are needed (DBA is a
bit OTT :-))
Try them one by one does not sound appealing at all

TIA


Jack

===
De informatie verzonden in dit e-mailbericht is vertrouwelijk en is
uitsluitend bestemd voor de geadresseerde. Openbaarmaking,
vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan
derden is, behoudens voorafgaande schriftelijke toestemming van Ernst &
Young, niet toegestaan. Ernst & Young staat niet in voor de juiste en
volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch
voor tijdige ontvangst daarvan. Ernst & Young kan niet garanderen dat een
verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten
worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden.

Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u
vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender
en het origineel en eventuele kopieën te verwijderen en te vernietigen.

Ernst & Young hanteert bij de uitoefening van haar werkzaamheden algemene
voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De
algemene voorwaarden worden u op verzoek kosteloos toegezonden.
=
The information contained in this communication is confidential and is
intended solely for the use of the individual or entity to whom it is
addressed. You should not copy, disclose or distribute this communication
without the authority of Ernst & Young. Ernst & Young is neither liable for
the proper and complete transmission of the information contained in this
communication nor for any delay in its receipt. Ernst & Young does not
guarantee that the integrity of this communication has been maintained nor
that the communication is free of viruses, interceptions or interference.

If you are not the intended recipient of this communication please return
the communication to the sender and delete and destroy all copies.

In carrying out its engagements, Ernst & Young applies general terms and
conditions, which contain a clause that limits its liability. A copy of
these terms and conditions is available on request free of charge.
===





-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jack van Zanen
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Khedr, Waleed
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



RE: Privileges needed for truncate

2002-09-19 Thread Bernard, Gilbert

DROP ANY TABLE include the privilege TRUNCATE.  But is not so secure.
An ohher way is create a procedure to do the truncate base on user_table
wich who is the owner of the tables.


-Message d'origine-
De: Jack van Zanen [mailto:[EMAIL PROTECTED]]
Date:   jeudi 19 septembre 2002 16:24
À:  Multiple recipients of list ORACLE-L
Objet:  Privileges needed for truncate

Hi


I need to create a user/role that among other stuff must be able to
truncate a table. I can't figure out which privileges are needed
(DBA is a
bit OTT :-))
Try them one by one does not sound appealing at all

TIA


Jack

===
De informatie verzonden in dit e-mailbericht is vertrouwelijk en is
uitsluitend bestemd voor de geadresseerde. Openbaarmaking,
vermenigvuldiging, verspreiding en/of verstrekking van deze
informatie aan
derden is, behoudens voorafgaande schriftelijke toestemming van
Ernst &
Young, niet toegestaan. Ernst & Young staat niet in voor de juiste
en
volledige overbrenging van de inhoud van een verzonden
e-mailbericht, noch
voor tijdige ontvangst daarvan. Ernst & Young kan niet garanderen
dat een
verzonden e-mailbericht vrij is van virussen, noch dat
e-mailberichten
worden overgebracht zonder inbreuk of tussenkomst van onbevoegde
derden.

Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken
wij u
vriendelijk doch dringend het e-mailbericht te retourneren aan de
verzender
en het origineel en eventuele kopieën te verwijderen en te
vernietigen.

Ernst & Young hanteert bij de uitoefening van haar werkzaamheden
algemene
voorwaarden, waarin een beperking van aansprakelijkheid is
opgenomen. De
algemene voorwaarden worden u op verzoek kosteloos toegezonden.

=
The information contained in this communication is confidential and
is
intended solely for the use of the individual or entity to whom it
is
addressed. You should not copy, disclose or distribute this
communication
without the authority of Ernst & Young. Ernst & Young is neither
liable for
the proper and complete transmission of the information contained in
this
communication nor for any delay in its receipt. Ernst & Young does
not
guarantee that the integrity of this communication has been
maintained nor
that the communication is free of viruses, interceptions or
interference.

If you are not the intended recipient of this communication please
return
the communication to the sender and delete and destroy all copies.

In carrying out its engagements, Ernst & Young applies general terms
and
conditions, which contain a clause that limits its liability. A copy
of
these terms and conditions is available on request free of charge.
===





-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jack van Zanen
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting
services

-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Bernard, Gilbert
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



Re: Privileges needed for truncate

2002-09-19 Thread Jay Hostetter

Jack,

  It used to be DELETE ANY TABLE.  I'm not sure if that has changed in recent 
versions.  Long ago, I setup the following procedure to allow a specific table to be 
truncated, without out the DELETE ANY TABLE privilege.  It must be owned by the owner 
of the table.  I haven't dealt with this issue in years, so if things have changed 
since Oracle 7, somebody else can chime in.

Jay

CREATE OR REPLACE PROCEDURE TRUNC_TAB
(TAB_IN IN USER_TABLES.TABLE_NAME%TYPE)
-- Author   :  Tony Ziemba Sheck Cho 4/18/95
-- Modified :
--   10/14/97 JMH
--   08/06/99 JMH Allow only certain tables to be truncated.
--   08/08/99 JMH Added more tables.
-- Description:
-- This procedure was developed to truncate a table using the table name that is
-- passed in as an input parameter. This procedure illustrates the use of the
-- dbms_sql package to execute SQL DDL statements within PL/SQL.  As of v7.1
-- TRUNCATE cannot be executed on a table unless the user owns the table or
-- the user has DELETE ANY TABLE privelege.  This procedure is a workaround to
-- those limitations.  
--
AS
cursor_id integer;   -- holds cursor id
return_value integer;-- holds call return value
str varchar2(150);   -- string to hold DDL statement
e_wrongtable exception;  -- exception when truncate is done on other tables.
BEGIN
  IF tab_in in ('TS_SECURITY_ACCESS','GL_CODE_COMBINATIONS') THEN
 str := 'truncate table '||tab_in;
 cursor_id := dbms_sql.open_cursor;
 dbms_sql.parse ( cursor_id,str,dbms_sql.native);
-- DDL statements are executed immediately.  This may change
-- in future releases, in which case the following statement will
-- be needed.
-- return_value := dbms_sql.execute(cursor_id);
 dbms_sql.close_cursor(cursor_id);
  ELSE
 RAISE e_wrongtable;
  END IF;
EXCEPTION
   WHEN e_wrongtable THEN
  RAISE_APPLICATION_ERROR(-2,'Procedure restricts tables that can be 
truncated.');
   WHEN OTHERS THEN
  dbms_sql.close_cursor(cursor_id);
END;
/

>>> [EMAIL PROTECTED] 09/19/02 10:23AM >>>
Hi


I need to create a user/role that among other stuff must be able to
truncate a table. I can't figure out which privileges are needed (DBA is a
bit OTT :-))
Try them one by one does not sound appealing at all

TIA


Jack






**DISCLAIMER
This e-mail message and any files transmitted with it are intended for the use of the 
individual or entity to which they are addressed and may contain information that is 
privileged, proprietary and confidential. If you are not the intended recipient, you 
may not use, copy or disclose to anyone the message or any information contained in 
the message. If you have received this communication in error, please notify the 
sender and delete this e-mail message. The contents do not represent the opinion of 
D&E except to the extent that it relates to their official business.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jay Hostetter
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



Re: RMAN implementation strategy

2002-09-19 Thread Yechiel Adar

I want to add another point.
After 7 years your tapes can be unreadable.
If you use exports you can periodically restore them and
backup them to a new tape.

Yechiel Adar
Mehish
- Original Message -
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Sent: Thursday, September 19, 2002 4:28 PM


Sean
   Yes, remember that the real reason you keep the RMAN catalog on another
system is not for disaster recovery, but for normal recoveries. The worst
situation is where you keep the catalog in the instance you are backing up.
The second worst situation is where the recovery catalog is in a separate
instance that shares some disks with the instance it is backing up. Lose a
disk and you can't recover. Again, this isn't designed for disaster
recovery, but for normal recoveries.
   I agree with Jay, the best procedure is to take an export of your RMAN
catalog and FTP it to the server being backed up so it gets on the backup
tape.
   RMAN is not the easiest to test a disaster recovery situation. The one
factor that makes things easier is that even though you use a catalog, RMAN
also writes its information to the control file. You can perform a disaster
recovery with the control file alone. Several of us have done it. There is a
parameter that controls how long the RMAN information will stay in the
control file. I would suggest leaving that alone.
   I agree with Jay, that over a 7 year period you should consider exports
as being less vulnerable to change. After 7 years, is this data still in
your production database, or has it been deleted? For example, we have data
that old in some of our databases, but we don't apply extraordinary measures
to ensure it doesn't get deleted. For example, if a user deleted some old
data, it might not be known immediately. You might consider regular audits
to ensure the old data is intact. Anyway, I don't think this has anything to
do with RMAN. I suppose you could maintain backup records that far back, but
it would be cumbersome. One product you may want to look at is Princeton
Softech Active Archiving. If I had an absolute requirement for 7 year
retention, that is what I would use.
   RMAN is a strange product in that it works differently than you would
assume and it takes awhile to get your head around how it works. A book that
really helped me get started is Oracle Backup and Recovery 101. Robert
Freeman who participates on this list has one coming out soon.



Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED] 


-Original Message-
Sent: Thursday, September 19, 2002 5:08 AM
To: Multiple recipients of list ORACLE-L


Hi Folks,

We're planning to implement RMAN as part of our B&R solution and by
extenstion also as part of our DR solution.  I've been trying to locate
information on how best to configure RMAN across our organisation.

For example it's advised you place catalog on separate server to production
server.  So server A might house catalog for server B and vice versa.  But
in a DR scenario where both servers could be destroyed there are I 'suspect'
potential implementations on overall MTTR depending on configuration.  Is it
then perhaps better to locate all catalogs on a dedicated server which
ideally would be replicated somehow to eliminate it as a singal point of
failure.

Also we have a requirment to be able to potentially recover data as far back
as 7 years.  These are currently comprised of monthly backups taken out of
regular cycle and archived off site.  I'm thinking it might be an idea to
set up a two catalogs, one for regular monthly cycle and another to record
these monthly archives as the maintenace of the catalog might be cumbersome
trying to ensure the montlhy archive data records do not get accidentally
deleted.

I've had a trawl across the Web courtesy of Google but did not find any
papers which appear to deal with these type of issues.  The RMAN User's
Guide and Reference does not appear to address them either.  Your
feedback/comments or references to papers would be much appeciated!.

Oracle 7.3.3, 8.0.5, 8.1.7
NT4, W2K
-
Seán O' Neill
Organon (Ireland) Ltd.
[subscribed: digest mode]

This message, including attached files, may contain confidential
information and is intended only for the use by the individual
and/or the entity to which it is addressed. Any unauthorized use,
dissemination of, or copying of the information contained herein is
not allowed and may lead to irreparable harm and damage for which
you may be held liable. If you receive this message in error or if
it is intended for someone else please notify the sender by
returning this e-mail immediately and delete the message.

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: O'Neill, Sean
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 h

RE: sqlplus question

2002-09-19 Thread Fink, Dan

The only method I know of is to concatenate the fields together in one
column. If there is a way to have variable sized SQL*Plus column formatting,
I'd love to see it...seriously...it would be cool. 

Until then...

  1  select substr(ename,1,length(rtrim(ename))), job
  2* from emp
SQL> /

SMITH |CLERK
ALLEN |SALESMAN
WARD  |SALESMAN
JONES |MANAGER
MARTIN|SALESMAN
SQL> edit
Wrote file afiedt.buf

  1  select substr(ename,1,length(rtrim(ename)))||'|'||job
  2* from emp
SQL> /

SMITH|CLERK
ALLEN|SALESMAN
WARD|SALESMAN
JONES|MANAGER
MARTIN|SALESMAN

-Original Message-
Sent: Thursday, September 19, 2002 8:51 AM
To: Fink, Dan; [EMAIL PROTECTED]


I don't want just 4. It's variable length and I want the actual number of
valid bytes.


Bill Carle
AT&T
Database Administrator
816-995-3922
[EMAIL PROTECTED]
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Fink, Dan
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



RE: sqlplus question

2002-09-19 Thread Fink, Dan

If you want only 4 bytes, us the SUBSTR function to take only what you need.

SQL> select ename, job
  2  from emp;

SMITH  CLERK
ALLEN  SALESMAN
WARD   SALESMAN
JONES  MANAGER
SQL> set colsep '|'
SQL> /

SMITH |CLERK
ALLEN |SALESMAN
WARD  |SALESMAN
JONES |MANAGER
SQL> select substr(ename,1,4), job
  2  from emp;

SMIT|CLERK
ALLE|SALESMAN
WARD|SALESMAN
JONE|MANAGER

-Original Message-
Sent: Thursday, September 19, 2002 9:19 AM
To: Multiple recipients of list ORACLE-L


Howdy,

I am spooling my sqlplus output to a file with no headings and all the
fields separated by a delimiter. I have a field that is defined as
varchar2(56), but typically only 4 or 5 bytes are filled. Oracle recognizes
that and if you select length(fld1) from the table, you will get 4. But if I
spool this to a file, I always get the full 56 bytes padded with blanks. In
other words, I get 4 bytes of data and 52 blanks for that field. I only want
the four valid bytes so that my delimiter comes immediately after that 4th
byte. My sqlplus options are as follows:

set newpage 0 space 0 linesize 5000 pagesize 0 echo off recsep off feedback
off
heading off trimspool on colsep "|"


Bill Carle
AT&T
Database Administrator
816-995-3922
[EMAIL PROTECTED]

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Carle, William T (Bill), ALCAS
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Fink, Dan
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



RE: sqlplus question

2002-09-19 Thread Carle, William T (Bill), ALCAS

I don't want just 4. It's variable length and I want the actual number of valid bytes.


Bill Carle
AT&T
Database Administrator
816-995-3922
[EMAIL PROTECTED]

 -Original Message-
Sent:   Thursday, September 19, 2002 9:51 AM
To: '[EMAIL PROTECTED]'; Carle, William T (Bill), ALCAS
Subject:RE: sqlplus question

If you want only 4 bytes, us the SUBSTR function to take only what you need.

SQL> select ename, job
  2  from emp;

SMITH  CLERK
ALLEN  SALESMAN
WARD   SALESMAN
JONES  MANAGER
SQL> set colsep '|'
SQL> /

SMITH |CLERK
ALLEN |SALESMAN
WARD  |SALESMAN
JONES |MANAGER
SQL> select substr(ename,1,4), job
  2  from emp;

SMIT|CLERK
ALLE|SALESMAN
WARD|SALESMAN
JONE|MANAGER

-Original Message-
Sent: Thursday, September 19, 2002 9:19 AM
To: Multiple recipients of list ORACLE-L


Howdy,

I am spooling my sqlplus output to a file with no headings and all the
fields separated by a delimiter. I have a field that is defined as
varchar2(56), but typically only 4 or 5 bytes are filled. Oracle recognizes
that and if you select length(fld1) from the table, you will get 4. But if I
spool this to a file, I always get the full 56 bytes padded with blanks. In
other words, I get 4 bytes of data and 52 blanks for that field. I only want
the four valid bytes so that my delimiter comes immediately after that 4th
byte. My sqlplus options are as follows:

set newpage 0 space 0 linesize 5000 pagesize 0 echo off recsep off feedback
off
heading off trimspool on colsep "|"


Bill Carle
AT&T
Database Administrator
816-995-3922
[EMAIL PROTECTED]

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Carle, William T (Bill), ALCAS
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Carle, William T (Bill), ALCAS
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



RE: Staspack Grapher/Viewer ?

2002-09-19 Thread Gesler, Rich

Here is something I am playing around with.  The idea came from Burleson's Statspack 
book.  It requires Active Perl with  DBD/DBI installed.  Also MS Graph is used.  This 
is still a work in progress.

#!C:\Perl\bin\perl.exe -w
#
# rpt_avg_bbw_dy.pl
# Report Average Buffer Busy Wait by Day
# This perl script will produce a graphical
# "Signature" of information.
# This information is obtained from statspack tables 
# and takes advantage of perl's Win32 OLE interface
# to Microsoft Graph.
#
use DBI;
use Win32::OLE qw( with in );
use Win32::OLE::Const "Microsoft Graph";
#
# Set Oracle User and Password Information
#
$name= "perfstat";
$passwd  = "x";
$ora_sid = "PROD";



# 1 makes creation process visible.  0 is faster.
my $VISIBLE = 1;
my $iIndex = 0;


#
# Make connection to Database
#
$dbh = DBI->connect("dbi:Oracle:$ora_sid", $name, $passwd)
or die "Cannot connect : $DBI::errstr";
#
# Prepare Statement to query database
#
$sth = $dbh->prepare("select to_char(snap_time,'day') day, 
avg(new.buffer_busy_wait-old.buffer_busy_wait) bbw from
   perfstat.stats\$buffer_pool_statistics old,
   perfstat.stats\$buffer_pool_statistics new,
   perfstat.stats\$snapshot   sn
where
   new.snap_id = sn.snap_id
and
   old.snap_id = sn.snap_id-1
group by
   to_char(snap_time,'day') ") 
||die "Can't prepare statement: $DBI::errstr";
$sth->execute () 
||die "Can't execute statement: $DBI::errstr";
while (($day, $bbw) = $sth->fetchrow_array) 
 { # loop thru, retrieving data
   $Data[$iIndex] = [$day, $bbw];
   $iIndex = $iIndex + 1;
 }



my %ChartOptions = (
width  =>  640,
height  =>  400,
haslegend  =>  0,
type  =>  xl3DLine,
perspective  =>  30,
rotation  =>  20,
autoscaling  =>  1,
rightangleaxes  =>  1,
title  =>  "Buffer Busy Wait Signature by Day",
);
my( @CELLS ) = ( 'a'..'zz' );
my $File = "C:\\temp\\bbw_day.gif";

# BEGIN CALLOUT A
# new() method creates an instance of MS Graph's Application object.
# To have a remote machine create the chart (DCOM) then change "MSGraph.Application"
# parameter to an anonymous array ["appserver.mydomain.com","MSGraph.Application"]
my $ChartApp = new Win32::OLE( "MSGraph.Application", "Quit" ) ||
die "Cannot create object\n";
# END CALLOUT A

$ChartApp->{Visible} = $VISIBLE;

# BEGIN CALLOUT B
my $DataSheet = $ChartApp->DataSheet();
my $Chart = $ChartApp->Chart();
# END CALLOUT B

foreach my $Option ( keys( %ChartOptions ) )
{
$Chart->{$Option} = $ChartOptions{$Option};
}
# BEGIN CALLOUT C

my $iTotal = $#Data;
foreach my $iIndex ( 0 .. $iTotal)
{
my $iday = $Data[$iIndex][0];
my $ibbw =  $Data[$iIndex][1];
$DataSheet->Range( "$CELLS[$iIndex]0" )->{Value} = $iday;
$DataSheet->Range( "$CELLS[$iIndex]1" )->{Value} = $ibbw;
}
# END CALLOUT C
print "\n";
# Configure the X axis.
if( my $Axis = $Chart->Axes( xlCategory ) )
{
$Axis->{HasMajorGridlines} = 0;
$Axis->{TickLabels}->{orientation} = xlUpward;
with( $Axis->{TickLabels}->{Font},
Name  =>  "Tahoma",
Bold  =>  0,
Italic  =>  0
);
}
# Configure the Y axis.
if( my $Axis = $Chart->Axes( xlValue ) )
{
$Axis->{HasMajorGridlines} = 1;
$Axis->{MajorGridlines}->{Border}->{Weight} = 1;
$Axis->{MajorGridlines}->{Border}->{ColorIndex} = 48;
$Axis->{MajorGridlines}->{Border}->{LineStyle} = xlContinuous;
with( $Chart->Axes( xlValue )->{TickLabels}->{Font},
Name  =>  "Tahoma",
Bold  =>  0,
Italic  =>  0
);
}
# BEGIN CALLOUT D
# Configure data-point labels.
$Chart->SeriesCollection( 1 )->{HasDataLabels} = 1;
if( my $Labels = $Chart->SeriesCollection(1)->DataLabels() )
{
with( $Labels,
NumberFormat  =>  "#.0",
Type  =>  xlDataLabelsShowValue
);
with( $Labels->{Font},
Name  =>  "Tahoma",
Bold  =>  0,
Italic  =>  0,
);
}

if( defined $ChartOptions{title} )
{
$Chart->{HasTitle} = 1;
$Chart->{ChartTitle}->{Text} = $ChartOptions{title};
$Chart->{ChartTitle}->{Font}->{Name} = "Tahoma";
$Chart->{ChartTitle}->{Font}->{Size} = 18;
}

# Remove consecutive redundant data-point labels.
$iTotal = $Chart->SeriesCollection( 1 )->Points()->{Count};
$iIndex = 0;
my $PrevText  = "";
foreach my $Point (in( $Chart->SeriesCollection( 1 )->Points()))
{
my $Percent = int( ++$iIndex * 100 / $iTotal );
my $Text = $Point->{DataLabel}->{Text};
$Point->{MarkerStyle} = xlMarkerStyleDot;
$Point->{DataLabel}->{Font}->{Background} = xlBackgroundOpaque;
$Point->{DataLabel}->{Top} -= 12;
$Point->{HasDataLabel} = 0 if( $Text eq $PrevText );
$PrevText = $Text;
print "\rFormatting: $Percent%";
}
# END CALLOUT D
print "\n";
print "Exporting to GIF file: $File\n";

# BEGIN CALLOUT 

RE: RMAN implementation strategy

2002-09-19 Thread DENNIS WILLIAMS

Yechiel - Excellent point. Even better, burn the exports on a CD, making
several copies. We have started doing that for special year-end reports that
need to be retained for long periods of time. Much cheaper than tapes as
well.

 
Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED]  


-Original Message-
Sent: Thursday, September 19, 2002 10:54 AM
To: Multiple recipients of list ORACLE-L


I want to add another point.
After 7 years your tapes can be unreadable.
If you use exports you can periodically restore them and
backup them to a new tape.

Yechiel Adar
Mehish
- Original Message -
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Sent: Thursday, September 19, 2002 4:28 PM


Sean
   Yes, remember that the real reason you keep the RMAN catalog on another
system is not for disaster recovery, but for normal recoveries. The worst
situation is where you keep the catalog in the instance you are backing up.
The second worst situation is where the recovery catalog is in a separate
instance that shares some disks with the instance it is backing up. Lose a
disk and you can't recover. Again, this isn't designed for disaster
recovery, but for normal recoveries.
   I agree with Jay, the best procedure is to take an export of your RMAN
catalog and FTP it to the server being backed up so it gets on the backup
tape.
   RMAN is not the easiest to test a disaster recovery situation. The one
factor that makes things easier is that even though you use a catalog, RMAN
also writes its information to the control file. You can perform a disaster
recovery with the control file alone. Several of us have done it. There is a
parameter that controls how long the RMAN information will stay in the
control file. I would suggest leaving that alone.
   I agree with Jay, that over a 7 year period you should consider exports
as being less vulnerable to change. After 7 years, is this data still in
your production database, or has it been deleted? For example, we have data
that old in some of our databases, but we don't apply extraordinary measures
to ensure it doesn't get deleted. For example, if a user deleted some old
data, it might not be known immediately. You might consider regular audits
to ensure the old data is intact. Anyway, I don't think this has anything to
do with RMAN. I suppose you could maintain backup records that far back, but
it would be cumbersome. One product you may want to look at is Princeton
Softech Active Archiving. If I had an absolute requirement for 7 year
retention, that is what I would use.
   RMAN is a strange product in that it works differently than you would
assume and it takes awhile to get your head around how it works. A book that
really helped me get started is Oracle Backup and Recovery 101. Robert
Freeman who participates on this list has one coming out soon.



Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED] 


-Original Message-
Sent: Thursday, September 19, 2002 5:08 AM
To: Multiple recipients of list ORACLE-L


Hi Folks,

We're planning to implement RMAN as part of our B&R solution and by
extenstion also as part of our DR solution.  I've been trying to locate
information on how best to configure RMAN across our organisation.

For example it's advised you place catalog on separate server to production
server.  So server A might house catalog for server B and vice versa.  But
in a DR scenario where both servers could be destroyed there are I 'suspect'
potential implementations on overall MTTR depending on configuration.  Is it
then perhaps better to locate all catalogs on a dedicated server which
ideally would be replicated somehow to eliminate it as a singal point of
failure.

Also we have a requirment to be able to potentially recover data as far back
as 7 years.  These are currently comprised of monthly backups taken out of
regular cycle and archived off site.  I'm thinking it might be an idea to
set up a two catalogs, one for regular monthly cycle and another to record
these monthly archives as the maintenace of the catalog might be cumbersome
trying to ensure the montlhy archive data records do not get accidentally
deleted.

I've had a trawl across the Web courtesy of Google but did not find any
papers which appear to deal with these type of issues.  The RMAN User's
Guide and Reference does not appear to address them either.  Your
feedback/comments or references to papers would be much appeciated!.

Oracle 7.3.3, 8.0.5, 8.1.7
NT4, W2K
-
Seán O' Neill
Organon (Ireland) Ltd.
[subscribed: digest mode]

This message, including attached files, may contain confidential
information and is intended only for the use by the individual
and/or the entity to which it is addressed. Any unauthorized use,
dissemination of, or copying of the information contained herein is
not allowed and may lead to irreparable harm and da

RE: sqlplus question

2002-09-19 Thread Nicoll, Iain \(Calanais\)

Bill couldn't you just concatenate the fields together with your delimiter
between.

e.g.

select fld1||'|'||fld2||'|'||fld3

I think the trimspool is just trailing blanks so unless you made that the
last field output you'll get this behaviour.

Iain Nicoll

-Original Message-
Sent: Thursday, September 19, 2002 4:19 PM
To: Multiple recipients of list ORACLE-L


Howdy,

I am spooling my sqlplus output to a file with no headings and all the
fields separated by a delimiter. I have a field that is defined as
varchar2(56), but typically only 4 or 5 bytes are filled. Oracle recognizes
that and if you select length(fld1) from the table, you will get 4. But if I
spool this to a file, I always get the full 56 bytes padded with blanks. In
other words, I get 4 bytes of data and 52 blanks for that field. I only want
the four valid bytes so that my delimiter comes immediately after that 4th
byte. My sqlplus options are as follows:

set newpage 0 space 0 linesize 5000 pagesize 0 echo off recsep off feedback
off
heading off trimspool on colsep "|"


Bill Carle
AT&T
Database Administrator
816-995-3922
[EMAIL PROTECTED]

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Carle, William T (Bill), ALCAS
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Nicoll, Iain \(Calanais\)
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



RE: sqlplus question

2002-09-19 Thread Bob Metelsky

> 
> I am spooling my sqlplus output to a file with no 
> headings and all the fields separated by a delimiter. I have 
> a field that is defined as varchar2(56), but typically only 4 
> or 5 bytes are filled. Oracle recognizes that and if you 
> select length(fld1) from the table, you will get 4. But if I 
> spool this to a file, I always get the full 56 bytes padded 
> with blanks. In other words, I get 4 bytes of data and 52 
> blanks for that field. I only want the four valid bytes so 
> that my delimiter comes immediately after that 4th byte. My 
> sqlplus options are as follows:
> 
> set newpage 0 space 0 linesize 5000 pagesize 0 echo off 
> recsep off feedback off heading off trimspool on colsep "|"
> 
I see using the statement on one line sets all on or off

Show trims

Sounds like your column is getting padded with empty bytes ???
Dispite lentght reporting the actual data

You might have to trim it yourself
??
bob
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Bob Metelsky
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



PL/SQL DDL Permissions

2002-09-19 Thread John Weatherman



I am 
trying to write a pl/sql package to allow end users to rebuild partitioned 
indexes.
I have 
everything working when the package.procedure is run as sys, however when 
I
run it 
as the object owner I get ORA-01031: insufficient privileges errors.  Other 
parts
the 
the package (adding new partitions, truncating the partition) work fine when 
run
by the 
table owner.  It's just the index rebuild that seems to 
choke.
 
Has 
anyone seen anything like this?  I am on Solaris 8, Oracle 9.0.1.3.  
Both sys and
the 
table owner have alter privs on the table and indexes in question.  I've 
got to have 
forgotten something obvious...
 
TIA,
John P Weatherman Database Administrator Replacements Ltd. 


RE: sqlplus question

2002-09-19 Thread Bob Metelsky


> 
> I am spooling my sqlplus output to a file with no 
> headings and all the fields separated by a delimiter. I have 
> a field that is defined as varchar2(56), but typically only 4 
> or 5 bytes are filled. Oracle recognizes that and if you 
> select length(fld1) from the table, you will get 4. But if I 
> spool this to a file, I always get the full 56 bytes padded 
> with blanks. In other words, I get 4 bytes of data and 52 
> blanks for that field. I only want the four valid bytes so 
> that my delimiter comes immediately after that 4th byte. My 
> sqlplus options are as follows:
> 
> set newpage 0 space 0 linesize 5000 pagesize 0 echo off 
> recsep off feedback off heading off trimspool on colsep "|"

Sounds to me like the trimspool should do it...

Are you sure its set on

ME@DB1 -> trimspool on
SP2-0734: unknown command beginning "trimspool ..." - rest of line
ignored.

Try  set trims on 

Or 

Set trimspool on


bob
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Bob Metelsky
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



RE: Query performance improvement ?

2002-09-19 Thread David Jones

Dennis:

Thanks for those helpful suggestions. I'll give it a try.

David Jones
ITResource



-Original Message-
WILLIAMS
Sent: Thursday, September 19, 2002 8:04 AM
To: Multiple recipients of list ORACLE-L


David
   - I'm assuming this query is being run interactively?
   - I recently posted a request for help on a query, and the winning
solution was to add a FIRST_ROWS hint, so naturally at this point I'm
convinced this will solve all performance issues.
   - Knowing how many rows are being returned will be helpful in planning
alternate strategies.
   - Try putting an index on your podata table.
   - How selective is your ownerid column? That is, how many distinct values
are in that column?
   - Have you considered bitmapped indexes?
   - I get by implication that you found this query in the SQL buffer and
have started there. I would suggest tracking down the application and users
to find out more about how and when they use this query. It also makes you
seem more proactive, which you are being, so you might as well get credit.
   - As to the distinct qualifier, here is a tip I found via Google:

21. Use EXISTS in Place of DISTINCT

Avoid joins that require the DISTINCT qualifier on the SELECT list when you
submit queries used to determine information at the owner end of a
one-to-many relationship (e.g. departments that have many employees).

For example:

Least Efficient :
SELECT DISTINCT DEPT_NO, DEPT_NAME
FROMDEPT D, EMP E
WHERE  D.DEPT_NO = E.DEPT_NO

Most Efficient :
SELECT DEPT_NO, DEPT_NAME
FROMDEPT D
WHERE EXISTS (SELECT   'X'
  FROM EMP E
  WHERE   E.DEPT_NO = D.DEPT_NO);
EXISTS is a faster alternative because the RDBMS kernel realizes that when
the sub-query has been satisfied once, the query can be terminated.
 
Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED] 


-Original Message-
Sent: Wednesday, September 18, 2002 8:08 PM
To: Multiple recipients of list ORACLE-L


Dennis:

1. The distinct seems difficult to avoid.
2. SQL> select count(*) from podata;

  COUNT(*)
--
 18679
SQL> select count(*) from InvData;

  COUNT(*)
--
 83315
3. The query returned no rows at this moment, the reason
I want to tune this query is because it has been recorded
by statspack.

  Buffer GetsExecutions  Gets per Exec  % Total  Hash Value
---  -- --- 
233,835  357  655.021.3   1783599440
select  distinct A.*   from POData A  , InvData B  where  A.ID =
B.PURCHASEORDERID AND A.OWNERID=B.OWNERID  and A.ownerId = 1  a
nd B.Status = 12  order by  A.ID
4. This query is running under a production system
5. The table just been analyzed recently

SQL> select TABLE_NAME, LAST_ANALYZED from user_tables where table_name in
('PODATA', 'INVDATA');
TABLE_NAME LAST_ANAL
-- -
INVDATA15-SEP-02
PODATA 15-SEP-02


David Jones
ITResource



-Original Message-
WILLIAMS
Sent: Wednesday, September 18, 2002 3:28 PM
To: Multiple recipients of list ORACLE-L


David
   A couple of observations and questions:
 - The distinct statement looks fairly expensive. Is there a way to
avoid it?
 - Are there any columns on POData? How many rows are in this table?
 - How many rows does this query return?
 - Do the tables contain the number of rows they will in production?
 - Have you analyzed the tables recently?

 
Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED] 


-Original Message-
Sent: Wednesday, September 18, 2002 4:23 PM
To: Multiple recipients of list ORACLE-L


Dear Lister:

Is there any way to further improve the following query performance ?

Thanks

David Jones
ITResource

SQL> select  distinct A.*
>from POData A  , InvData B  where  A.OWNERID=B.OWNERID AND A.ID = 
>B.PURCHASEORDERID
>and B.Status = 12 and A.ownerId = 1   order by  A.ID;



Execution Plan
--
   0  SELECT STATEMENT Optimizer=CHOOSE (Cost=398 Card=8971
Bytes=663854)
   10   SORT (UNIQUE) (Cost=284 Card=8971 Bytes=663854)
   21 HASH JOIN (Cost=170 Card=8971 Bytes=663854)
   32   INDEX (FAST FULL SCAN) OF 'INVDATA_2' (NON-UNIQUE) (Cost=99
Card=8971 Bytes=71768)
   42   TABLE ACCESS (FULL) OF 'PODATA' (Cost=36 Card=18152
Bytes=1198032)

Statistics
--
  0  recursive calls
  4  db block gets
656  consistent gets
  0  physical reads
  0  redo size
   1356  bytes sent via SQL*Net to client
314  bytes received via SQL*Net from client
  1  SQL*Net roundtrips to/from client
  3  sorts (memory)
  0  sorts (disk)
  0  rows processed

SQL> desc PODATA
Name   

RE: sqlplus question

2002-09-19 Thread Viral Desai

select substr(ename,1,length(ename)), job from emp;

>From: "Bob Metelsky" <[EMAIL PROTECTED]>
>Reply-To: [EMAIL PROTECTED]
>To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
>Subject: RE: sqlplus question
>Date: Thu, 19 Sep 2002 08:18:51 -0800
>
>
> >
> > I am spooling my sqlplus output to a file with no
> > headings and all the fields separated by a delimiter. I have
> > a field that is defined as varchar2(56), but typically only 4
> > or 5 bytes are filled. Oracle recognizes that and if you
> > select length(fld1) from the table, you will get 4. But if I
> > spool this to a file, I always get the full 56 bytes padded
> > with blanks. In other words, I get 4 bytes of data and 52
> > blanks for that field. I only want the four valid bytes so
> > that my delimiter comes immediately after that 4th byte. My
> > sqlplus options are as follows:
> >
> > set newpage 0 space 0 linesize 5000 pagesize 0 echo off
> > recsep off feedback off heading off trimspool on colsep "|"
>
>Sounds to me like the trimspool should do it...
>
>Are you sure its set on
>
>ME@DB1 -> trimspool on
>SP2-0734: unknown command beginning "trimspool ..." - rest of line
>ignored.
>
>Try  set trims on
>
>Or
>
>Set trimspool on
>
>
>bob
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author: Bob Metelsky
>   INET: [EMAIL PROTECTED]
>
>Fat City Network Services-- 858-538-5051 http://www.fatcity.com
>San Diego, California-- Mailing list and web hosting services
>-
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from).  You may
>also send the HELP command for other information (like subscribing).




_
MSN Photos is the easiest way to share and print your photos: 
http://photos.msn.com/support/worldwide.aspx

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Viral Desai
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



RE: Privileges needed for truncate

2002-09-19 Thread Bernard, Gilbert

create or replace PROCEDURE 
truncate_table(tbl_name in varchar2)
AUTHID DEFINER
is
total   INTEGER ;
trouve  EXCEPTION   ;
cursor_name INTEGER ;
ret INTEGER ;
trunVARCHAR2(30):= 'truncate table '||
tbl_name;
BEGIN
select  1 
intototal
from  user_tables
where table_name= upper(tbl_name)
;
Raise TROUVE
;
EXCEPTION
When TROUVE
then
cursor_name := DBMS_SQL.OPEN_CURSOR
;
DBMS_SQL.PARSE(cursor_name, trun, DBMS_SQL.native)
;
ret := DBMS_SQL.EXECUTE(cursor_name)
;
DBMS_SQL.CLOSE_CURSOR(cursor_name)
;
When others
then
raise_application_error(-20011,'*** Table '||tbl_name||' Not
authorized ! ***');
END
;
/

-Message d'origine-
De: Khedr, Waleed [mailto:[EMAIL PROTECTED]]
Date:   jeudi 19 septembre 2002 17:24
À:  Multiple recipients of list ORACLE-L
Objet:  RE: Privileges needed for truncate

Unfortunately drop any table.

-Original Message-
Sent: Thursday, September 19, 2002 10:24 AM
To: Multiple recipients of list ORACLE-L


Hi


I need to create a user/role that among other stuff must be able to
truncate a table. I can't figure out which privileges are needed
(DBA is a
bit OTT :-))
Try them one by one does not sound appealing at all

TIA


Jack

===
De informatie verzonden in dit e-mailbericht is vertrouwelijk en is
uitsluitend bestemd voor de geadresseerde. Openbaarmaking,
vermenigvuldiging, verspreiding en/of verstrekking van deze
informatie aan
derden is, behoudens voorafgaande schriftelijke toestemming van
Ernst &
Young, niet toegestaan. Ernst & Young staat niet in voor de juiste
en
volledige overbrenging van de inhoud van een verzonden
e-mailbericht, noch
voor tijdige ontvangst daarvan. Ernst & Young kan niet garanderen
dat een
verzonden e-mailbericht vrij is van virussen, noch dat
e-mailberichten
worden overgebracht zonder inbreuk of tussenkomst van onbevoegde
derden.

Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken
wij u
vriendelijk doch dringend het e-mailbericht te retourneren aan de
verzender
en het origineel en eventuele kopieën te verwijderen en te
vernietigen.

Ernst & Young hanteert bij de uitoefening van haar werkzaamheden
algemene
voorwaarden, waarin een beperking van aansprakelijkheid is
opgenomen. De
algemene voorwaarden worden u op verzoek kosteloos toegezonden.

=
The information contained in this communication is confidential and
is
intended solely for the use of the individual or entity to whom it
is
addressed. You should not copy, disclose or distribute this
communication
without the authority of Ernst & Young. Ernst & Young is neither
liable for
the proper and complete transmission of the information contained in
this
communication nor for any delay in its receipt. Ernst & Young does
not
guarantee that the integrity of this communication has been
maintained nor
that the communication is free of viruses, interceptions or
interference.

If you are not the intended recipient of this communication please
return
the communication to the sender and delete and destroy all copies.

In carrying out its engagements, Ernst & Young applies general terms
and
conditions, which contain a clause that limits its liability. A copy
of
these terms and conditions is available on request free of charge.
===





-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jack van Zanen
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting
services

-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information 

oraperf report - 2 queries

2002-09-19 Thread John . Hallas

I have just tried using www.oraperf.com again after about a year since the
last time.
The output both in terms of formatting, readability and comment are much
improved on what was already a valuable resource
Well done Anjo

2 questions that the list (or even Anjo) may be able to answer for me

With regard to processes the report states
"Another option is to decrease the init.ora parameter "processes". The LGWR
needs to scan all processes to find each process that is waiting for the
commit to be written. The current value for processes is 1300. Try setting
it close to the number of process that you really need."
What I do not understand is that processes is a maximum number so LGWR may
have to read all processes that exist but that number is well below the 1300
level but I cannot see an overhead in having the processes set to high in
that respect. I do agree that other values are calculated based upon the
value of the processes setting and so that is a good reason to reduce the
value to a more realistic one of about 500.
As a side note Steve Adaams states that there is no negative impact of
having too high a processes value (In 8 not necessarily 8i)
(http://www.ixora.com.au/q+a/params.htm

The second point is with regard to  recommending the use of a smaller log
buffer and a smaller log_io_size (recommended to be 3 times avg redo size
per commit).
I was wondering if anyone knows where the current value is recorded. It is
not available from V$parameter and I assume it is in one of the X$ views.
My log buffer is 4M and the redo log block size is 512 but I am stumped as
to the value of _log_io_size ( I think it should be 8192 ie 4M/512)

Thanks


John





-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



Re: RMAN implementation strategy

2002-09-19 Thread Ruth Gramolini

Why can't you just restore the data and put it on a new tape?  Why do you
have to an export to do this?
Ruth
- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Thursday, September 19, 2002 11:53 AM


I want to add another point.
After 7 years your tapes can be unreadable.
If you use exports you can periodically restore them and
backup them to a new tape.

Yechiel Adar
Mehish
- Original Message -
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Sent: Thursday, September 19, 2002 4:28 PM


Sean
   Yes, remember that the real reason you keep the RMAN catalog on another
system is not for disaster recovery, but for normal recoveries. The worst
situation is where you keep the catalog in the instance you are backing up.
The second worst situation is where the recovery catalog is in a separate
instance that shares some disks with the instance it is backing up. Lose a
disk and you can't recover. Again, this isn't designed for disaster
recovery, but for normal recoveries.
   I agree with Jay, the best procedure is to take an export of your RMAN
catalog and FTP it to the server being backed up so it gets on the backup
tape.
   RMAN is not the easiest to test a disaster recovery situation. The one
factor that makes things easier is that even though you use a catalog, RMAN
also writes its information to the control file. You can perform a disaster
recovery with the control file alone. Several of us have done it. There is a
parameter that controls how long the RMAN information will stay in the
control file. I would suggest leaving that alone.
   I agree with Jay, that over a 7 year period you should consider exports
as being less vulnerable to change. After 7 years, is this data still in
your production database, or has it been deleted? For example, we have data
that old in some of our databases, but we don't apply extraordinary measures
to ensure it doesn't get deleted. For example, if a user deleted some old
data, it might not be known immediately. You might consider regular audits
to ensure the old data is intact. Anyway, I don't think this has anything to
do with RMAN. I suppose you could maintain backup records that far back, but
it would be cumbersome. One product you may want to look at is Princeton
Softech Active Archiving. If I had an absolute requirement for 7 year
retention, that is what I would use.
   RMAN is a strange product in that it works differently than you would
assume and it takes awhile to get your head around how it works. A book that
really helped me get started is Oracle Backup and Recovery 101. Robert
Freeman who participates on this list has one coming out soon.



Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED] 


-Original Message-
Sent: Thursday, September 19, 2002 5:08 AM
To: Multiple recipients of list ORACLE-L


Hi Folks,

We're planning to implement RMAN as part of our B&R solution and by
extenstion also as part of our DR solution.  I've been trying to locate
information on how best to configure RMAN across our organisation.

For example it's advised you place catalog on separate server to production
server.  So server A might house catalog for server B and vice versa.  But
in a DR scenario where both servers could be destroyed there are I 'suspect'
potential implementations on overall MTTR depending on configuration.  Is it
then perhaps better to locate all catalogs on a dedicated server which
ideally would be replicated somehow to eliminate it as a singal point of
failure.

Also we have a requirment to be able to potentially recover data as far back
as 7 years.  These are currently comprised of monthly backups taken out of
regular cycle and archived off site.  I'm thinking it might be an idea to
set up a two catalogs, one for regular monthly cycle and another to record
these monthly archives as the maintenace of the catalog might be cumbersome
trying to ensure the montlhy archive data records do not get accidentally
deleted.

I've had a trawl across the Web courtesy of Google but did not find any
papers which appear to deal with these type of issues.  The RMAN User's
Guide and Reference does not appear to address them either.  Your
feedback/comments or references to papers would be much appeciated!.

Oracle 7.3.3, 8.0.5, 8.1.7
NT4, W2K
-
Seán O' Neill
Organon (Ireland) Ltd.
[subscribed: digest mode]

This message, including attached files, may contain confidential
information and is intended only for the use by the individual
and/or the entity to which it is addressed. Any unauthorized use,
dissemination of, or copying of the information contained herein is
not allowed and may lead to irreparable harm and damage for which
you may be held liable. If you receive this message in error or if
it is intended for someone else please notify the sender by
returning this e-mail immediately and del

RE: Why does my insert creates so many logs?

2002-09-19 Thread Viral Desai


A couple of things to try ---

1. Drop the indexes and primary key instead of disabling them. Insert the 
data and recreate pk.

2. This could be due to changes in data dictionary, when you insert large 
number of rows in the table, new extents may be allocated or high water mark 
of the table would be modified. This information need to be logged. I think 
that the redo generated due to this should not be very large though.

Hope this helps.
Viral Desai


>From: Gurelei <[EMAIL PROTECTED]>
>Reply-To: [EMAIL PROTECTED]
>To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
>Subject: RE: Why does my insert creates so many logs?
>Date: Thu, 19 Sep 2002 06:03:29 -0800
>
>None.
>--- "Nicoll, Iain (Calanais)"
><[EMAIL PROTECTED]> wrote:
> > Doesn't have any triggers does it?
> >
> > -Original Message-
> > Sent: Wednesday, September 18, 2002 8:39 PM
> > To: Multiple recipients of list ORACLE-L
> >
> >
> > Hi.
> >
> > A developer of mine is running a large insert as
> > select:
> >
> > insert /* parallel hint */ into table A
> > nologging
> > (select * from table b where ...);
> >
> > There are no indices on table A and a PK disabled.
> > Still that insert generates a large amount of logs.
> > What could be the reason for that? Any ideas? Table
> > A
> > is not partitioned and has NOLOGGING attribute on
> > the
> > dba_tables set to Yes.
> >
> > thanks
> >
> > Gene
> >
> > __
> > Do you Yahoo!?
> > Yahoo! News - Today's headlines
> > http://news.yahoo.com
> > --
> > Please see the official ORACLE-L FAQ:
> > http://www.orafaq.com
> > --
> > Author: Gurelei
> >   INET: [EMAIL PROTECTED]
> >
> > Fat City Network Services-- 858-538-5051
> > http://www.fatcity.com
> > San Diego, California-- Mailing list and web
> > hosting services
> >
>-
> > To REMOVE yourself from this mailing list, send an
> > E-Mail message
> > to: [EMAIL PROTECTED] (note EXACT spelling of
> > 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB
> > ORACLE-L
> > (or the name of mailing list you want to be removed
> > from).  You may
> > also send the HELP command for other information
> > (like subscribing).
> > --
> > Please see the official ORACLE-L FAQ:
> > http://www.orafaq.com
> > --
> > Author: Nicoll, Iain \(Calanais\)
> >   INET: [EMAIL PROTECTED]
> >
> > Fat City Network Services-- 858-538-5051
> > http://www.fatcity.com
> > San Diego, California-- Mailing list and web
> > hosting services
> >
>-
> > To REMOVE yourself from this mailing list, send an
> > E-Mail message
> > to: [EMAIL PROTECTED] (note EXACT spelling of
> > 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB
> > ORACLE-L
> > (or the name of mailing list you want to be removed
> > from).  You may
> > also send the HELP command for other information
> > (like subscribing).
>
>
>__
>Do you Yahoo!?
>New DSL Internet Access from SBC & Yahoo!
>http://sbc.yahoo.com
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author: Gurelei
>   INET: [EMAIL PROTECTED]
>
>Fat City Network Services-- 858-538-5051 http://www.fatcity.com
>San Diego, California-- Mailing list and web hosting services
>-
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from).  You may
>also send the HELP command for other information (like subscribing).




_
MSN Photos is the easiest way to share and print your photos: 
http://photos.msn.com/support/worldwide.aspx

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Viral Desai
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



RE: sqlplus question

2002-09-19 Thread Jay Hostetter

Instead of using colsep, try this:

select ename||'|'||job
from emp;

I was going to suggest -> col emp form a4
but if it can be more than 4 positions, it will wrap.

Jay



>>> [EMAIL PROTECTED] 09/19/02 11:53AM >>>
I don't want just 4. It's variable length and I want the actual number of valid bytes.


Bill Carle
AT&T
Database Administrator
816-995-3922
[EMAIL PROTECTED] 

 -Original Message-
Sent:   Thursday, September 19, 2002 9:51 AM
To: '[EMAIL PROTECTED]'; Carle, William T (Bill), ALCAS
Subject:RE: sqlplus question

If you want only 4 bytes, us the SUBSTR function to take only what you need.

SQL> select ename, job
  2  from emp;

SMITH  CLERK
ALLEN  SALESMAN
WARD   SALESMAN
JONES  MANAGER
SQL> set colsep '|'
SQL> /

SMITH |CLERK
ALLEN |SALESMAN
WARD  |SALESMAN
JONES |MANAGER
SQL> select substr(ename,1,4), job
  2  from emp;

SMIT|CLERK
ALLE|SALESMAN
WARD|SALESMAN
JONE|MANAGER

-Original Message-
Sent: Thursday, September 19, 2002 9:19 AM
To: Multiple recipients of list ORACLE-L


Howdy,

I am spooling my sqlplus output to a file with no headings and all the
fields separated by a delimiter. I have a field that is defined as
varchar2(56), but typically only 4 or 5 bytes are filled. Oracle recognizes
that and if you select length(fld1) from the table, you will get 4. But if I
spool this to a file, I always get the full 56 bytes padded with blanks. In
other words, I get 4 bytes of data and 52 blanks for that field. I only want
the four valid bytes so that my delimiter comes immediately after that 4th
byte. My sqlplus options are as follows:

set newpage 0 space 0 linesize 5000 pagesize 0 echo off recsep off feedback
off
heading off trimspool on colsep "|"


Bill Carle
AT&T
Database Administrator
816-995-3922
[EMAIL PROTECTED] 






**DISCLAIMER
This e-mail message and any files transmitted with it are intended for the use of the 
individual or entity to which they are addressed and may contain information that is 
privileged, proprietary and confidential. If you are not the intended recipient, you 
may not use, copy or disclose to anyone the message or any information contained in 
the message. If you have received this communication in error, please notify the 
sender and delete this e-mail message. The contents do not represent the opinion of 
D&E except to the extent that it relates to their official business.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jay Hostetter
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



RE: sqlplus question

2002-09-19 Thread Naveen Nahata

"set Trimspool on" will only trim the empty spaces at the end of each line.

The only way i know to trim in between the lines is to concatenate all the
columns

SELECT column_1 || '|' || column_2. FROM .

Naveen

-Original Message-
Sent: Thursday, September 19, 2002 9:49 PM
To: Multiple recipients of list ORACLE-L



> 
> I am spooling my sqlplus output to a file with no 
> headings and all the fields separated by a delimiter. I have 
> a field that is defined as varchar2(56), but typically only 4 
> or 5 bytes are filled. Oracle recognizes that and if you 
> select length(fld1) from the table, you will get 4. But if I 
> spool this to a file, I always get the full 56 bytes padded 
> with blanks. In other words, I get 4 bytes of data and 52 
> blanks for that field. I only want the four valid bytes so 
> that my delimiter comes immediately after that 4th byte. My 
> sqlplus options are as follows:
> 
> set newpage 0 space 0 linesize 5000 pagesize 0 echo off 
> recsep off feedback off heading off trimspool on colsep "|"

Sounds to me like the trimspool should do it...

Are you sure its set on

ME@DB1 -> trimspool on
SP2-0734: unknown command beginning "trimspool ..." - rest of line
ignored.

Try  set trims on 

Or 

Set trimspool on


bob
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Bob Metelsky
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Naveen Nahata
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



OT: oracle and appliance storage

2002-09-19 Thread Rick_Cale

Hi All,

Server A,  Win 2000, Oracle 8.1.7
Server B, Win 2000 with Auspex storage appliance

We want to test all datafiles to be on Server B.  How does Oracle on server
A know about datafiles on server B. It is my understanding
that Oracle does not recognize map drives. There must be another way that
Oracle recognizes drives on Server B.  Can anyone shed
any light?

Thanks
Rick


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



RE: Staspack Grapher/Viewer ?

2002-09-19 Thread John Kanagaraj

This product seems to use the now infamous CHR which has been banned from
this list :)

Has anyone figured out how to use MRTG (or Cricket) to get this done? [Why
buy when you can use Open Source!]

John

> -Original Message-
> From: George Leonard (ZA) [mailto:[EMAIL PROTECTED]]
> Sent: Thursday, September 19, 2002 7:48 AM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: Staspack Grapher/Viewer ?
> 
> 
> http://www.statsviewer.narod.ru
> 
> fantastic product.
> 
> George
> 
> George Leonard
> Oracle Database Administrator
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: John Kanagaraj
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



High values

2002-09-19 Thread John Dunn

Is there a Oracle equivalent to the Cobol HIGH-VALUES value?

John
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: John Dunn
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



RE: Staspack Grapher/Viewer ?

2002-09-19 Thread Orr, Steve

MRTG is good stuff but doesn't it use RRDTool. We have used RRDTool with
great success.


-Original Message-
Sent: Thursday, September 19, 2002 8:58 AM
To: Multiple recipients of list ORACLE-L


We use MRTG to produce graphs for data from different sources  network
stats, hardware stats, database stats.

Maybe you could use it for statspack.



-Original Message-
Sent: Thursday, September 19, 2002 8:39 AM
To: Multiple recipients of list ORACLE-L


Does anyone have or know of any utilities, preferably 
freeeware or very cheap, that can produce graphs of 
the data collected by statspack?

Thanks VERY much in advance.
-walt

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Walter K
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Kevin Lange
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Orr, Steve
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



RE: Staspack Grapher/Viewer ?

2002-09-19 Thread mkb

And if you don't want to use MS Graph, you can use
Perl's built-in graphing facility (see Programming
Perl) or gnuplot.

I had developed something similar at a previous gig
but I used gnuplot. 

mkb

--- "Gesler, Rich" <[EMAIL PROTECTED]> wrote:
> Here is something I am playing around with.  The
> idea came from Burleson's Statspack book.  It
> requires Active Perl with  DBD/DBI installed.  Also
> MS Graph is used.  This is still a work in progress.
> 
> #!C:\Perl\bin\perl.exe -w
> #
> # rpt_avg_bbw_dy.pl
> # Report Average Buffer Busy Wait by Day
> # This perl script will produce a graphical
> # "Signature" of information.
> # This information is obtained from statspack tables
> 
> # and takes advantage of perl's Win32 OLE interface
> # to Microsoft Graph.
> #
> use DBI;
> use Win32::OLE qw( with in );
> use Win32::OLE::Const "Microsoft Graph";
> #
> # Set Oracle User and Password Information
> #
> $name= "perfstat";
> $passwd  = "x";
> $ora_sid = "PROD";
> 
> 
> 
> # 1 makes creation process visible.  0 is faster.
> my $VISIBLE = 1;
> my $iIndex = 0;
> 
> 
> #
> # Make connection to Database
> #
> $dbh = DBI->connect("dbi:Oracle:$ora_sid", $name,
> $passwd)
> or die "Cannot connect : $DBI::errstr";
> #
> # Prepare Statement to query database
> #
> $sth = $dbh->prepare("select
> to_char(snap_time,'day') day,
> avg(new.buffer_busy_wait-old.buffer_busy_wait) bbw
> from
>perfstat.stats\$buffer_pool_statistics old,
>perfstat.stats\$buffer_pool_statistics new,
>perfstat.stats\$snapshot   sn
> where
>new.snap_id = sn.snap_id
> and
>old.snap_id = sn.snap_id-1
> group by
>to_char(snap_time,'day') ") 
> ||die "Can't prepare statement: $DBI::errstr";
> $sth->execute () 
> ||die "Can't execute statement: $DBI::errstr";
> while (($day, $bbw) = $sth->fetchrow_array) 
>  { # loop thru, retrieving data
>$Data[$iIndex] = [$day, $bbw];
>$iIndex = $iIndex + 1;
>  }
> 
> 
> 
> my %ChartOptions = (
>   width  =>  640,
>   height  =>  400,
>   haslegend  =>  0,
>   type  =>  xl3DLine,
>   perspective  =>  30,
>   rotation  =>  20,
>   autoscaling  =>  1,
>   rightangleaxes  =>  1,
>   title  =>  "Buffer Busy Wait Signature by Day",
> );
> my( @CELLS ) = ( 'a'..'zz' );
> my $File = "C:\\temp\\bbw_day.gif";
> 
> # BEGIN CALLOUT A
> # new() method creates an instance of MS Graph's
> Application object.
> # To have a remote machine create the chart (DCOM)
> then change "MSGraph.Application"
> # parameter to an anonymous array
> ["appserver.mydomain.com","MSGraph.Application"]
> my $ChartApp = new Win32::OLE(
> "MSGraph.Application", "Quit" ) ||
>   die "Cannot create object\n";
> # END CALLOUT A
> 
> $ChartApp->{Visible} = $VISIBLE;
> 
> # BEGIN CALLOUT B
> my $DataSheet = $ChartApp->DataSheet();
> my $Chart = $ChartApp->Chart();
> # END CALLOUT B
> 
> foreach my $Option ( keys( %ChartOptions ) )
> {
>   $Chart->{$Option} = $ChartOptions{$Option};
> }
> # BEGIN CALLOUT C
> 
> my $iTotal = $#Data;
> foreach my $iIndex ( 0 .. $iTotal)
> {
>   my $iday = $Data[$iIndex][0];
>   my $ibbw =  $Data[$iIndex][1];
>   $DataSheet->Range( "$CELLS[$iIndex]0" )->{Value} =
> $iday;
>   $DataSheet->Range( "$CELLS[$iIndex]1" )->{Value} =
> $ibbw;
> }
> # END CALLOUT C
> print "\n";
> # Configure the X axis.
> if( my $Axis = $Chart->Axes( xlCategory ) )
> {
>   $Axis->{HasMajorGridlines} = 0;
>   $Axis->{TickLabels}->{orientation} = xlUpward;
>   with( $Axis->{TickLabels}->{Font},
>   Name  =>  "Tahoma",
>   Bold  =>  0,
>   Italic  =>  0
>   );
> }
> # Configure the Y axis.
> if( my $Axis = $Chart->Axes( xlValue ) )
> {
>   $Axis->{HasMajorGridlines} = 1;
>   $Axis->{MajorGridlines}->{Border}->{Weight} = 1;
>   $Axis->{MajorGridlines}->{Border}->{ColorIndex} =
> 48;
>   $Axis->{MajorGridlines}->{Border}->{LineStyle} =
> xlContinuous;
>   with( $Chart->Axes( xlValue
> )->{TickLabels}->{Font},
>   Name  =>  "Tahoma",
>   Bold  =>  0,
>   Italic  =>  0
>   );
> }
> # BEGIN CALLOUT D
> # Configure data-point labels.
> $Chart->SeriesCollection( 1 )->{HasDataLabels} = 1;
> if( my $Labels =
> $Chart->SeriesCollection(1)->DataLabels() )
> {
>   with( $Labels,
>   NumberFormat  =>  "#.0",
>   Type  =>  xlDataLabelsShowValue
>   );
>   with( $Labels->{Font},
>   Name  =>  "Tahoma",
>   Bold  =>  0,
>   Italic  =>  0,
>   );
> }
> 
> if( defined $ChartOptions{title} )
> {
>   $Chart->{HasTitle} = 1;
>   $Chart->{ChartTitle}->{Text} =
> $ChartOptions{title};
>   $Chart->{ChartTitle}->{Font}->{Name} = "Tahoma";
>   $Chart->{ChartTitle}->{Font}->{Size} = 18;
> }
> 
> # Remove consecutive redundant data-point labels.
> $iTotal = $Chart->SeriesCollection( 1
> )->Points()->{Count};
> $iIndex = 0;
> my $PrevText  = "";
> foreach my $Point (in( $Chart->SeriesCollect

RE: Consulting Position Available-Oracle DBA/Developer/NYC

2002-09-19 Thread Miller, Jay

But 4th normal form?  Does anyone really use this?

-Original Message-
Sent: Tuesday, September 17, 2002 3:35 PM
To: Multiple recipients of list ORACLE-L


well the market must be picking up some, been definitely more reqs 
posted here a/o recent.

joe


Bill Christison wrote:

>Interested candidates reply to: [EMAIL PROTECTED]
>*
>Consulting Position Title:
>Oracle DBA/Developer
>
>Duties and Responsibilities:
>
>This position is a conversion to an Oracle Data warehouse.
>Excellent verbal skills are needed to obtain technical
>specification from the architect and users then to transform
>into written specifications.
>
>Experience Required:
>*Must have experience in  PL SQL and T SQL
>*Must have 3rd and 4th form data normalization 
>*Must have done business systems analysis
>
>Consulting Assignment Duration: 6 months to 12 months
>
>Assignment Location: New York City
>
>=
>Bill Christison
>Knowlton Group, LLC
>845-258-5129
>www.knowltongroup.com
>
>__
>Do you Yahoo!?
>Yahoo! News - Today's headlines
>http://news.yahoo.com
>


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Joe Testa
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Miller, Jay
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



Process question

2002-09-19 Thread Jay Hostetter

Do I have something wrong with this query?  I thought that each oracle process 
corresponded to one database connection (unless you are using MTS, which we aren't).  
This query surprised me.  It shows that two different oracle database processes each 
correspond to 3 database connections.  What am I missing?

Thanks,
Jay

SQL> run
  1  select vs.username,last_call_et, vp.pid,
  2 vs.sid,
  3 vs.serial#,
  4 vs.osuser,
  5 vs.machine,
  6 vs.process,
  7 vp.spid
  8  from v$session vs, v$process vp
  9  where vs.paddr = vp.addr
 10* and process in ('2949917','2952943')

USERNAME LAST_CALL_ETPID   SIDSERIAL# OSUSER   MACHINE 
  PROCESS   SPID
  -- - --  
- - -
APPS13180 1511456 applmgr  curly.pcsone.com
  2949917   2950637
APPS13227 15   132951 applmgr  curly.pcsone.com
  2949917   2950637
APPS13746 15   157198 applmgr  curly.pcsone.com
  2949917   2950637
APPS12761136   125961 applmgr  curly.pcsone.com
  2952943   2953312
APPS12840136   171  9 applmgr  curly.pcsone.com
  2952943   2953312
APPS12808136   174 13 applmgr  curly.pcsone.com
  2952943   2953312

6 rows selected.



**DISCLAIMER
This e-mail message and any files transmitted with it are intended for the use of the 
individual or entity to which they are addressed and may contain information that is 
privileged, proprietary and confidential. If you are not the intended recipient, you 
may not use, copy or disclose to anyone the message or any information contained in 
the message. If you have received this communication in error, please notify the 
sender and delete this e-mail message. The contents do not represent the opinion of 
D&E except to the extent that it relates to their official business.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jay Hostetter
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



Re: Lost ofall redo logs

2002-09-19 Thread Gilberto Gampert

Hi People

I don't think that i need to update my CV, since I don't administer the
database in subject and that am trying to solve the an unhappy customer's
problem.

I had already researched and tried almost everything that you suggested me
and what really solved went use to suggestion of the not documented
parameter (_allow_resetlogs_corruption), then do a full export, then
recreate the database and do a full import.

I think that this list is wonderful, whenever I had a problem and I didn't
get to solve I asked help here and I went very well assisted, much better
than the oracle support.

Thank you!

:
Gilberto Gampert  Universidade de Passo Fundo
Administrador de Banco de Dados   Passo Fundo - RS - Bra5il
[EMAIL PROTECTED]http://www.upf.br
:




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Gilberto Gampert
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



Re: Privileges needed for truncate

2002-09-19 Thread Ruth Gramolini

To truncate you need delete privileges.  Ruth
- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Thursday, September 19, 2002 10:23 AM


Hi


I need to create a user/role that among other stuff must be able to
truncate a table. I can't figure out which privileges are needed (DBA is a
bit OTT :-))
Try them one by one does not sound appealing at all

TIA


Jack

===
De informatie verzonden in dit e-mailbericht is vertrouwelijk en is
uitsluitend bestemd voor de geadresseerde. Openbaarmaking,
vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan
derden is, behoudens voorafgaande schriftelijke toestemming van Ernst &
Young, niet toegestaan. Ernst & Young staat niet in voor de juiste en
volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch
voor tijdige ontvangst daarvan. Ernst & Young kan niet garanderen dat een
verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten
worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden.

Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u
vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender
en het origineel en eventuele kopieën te verwijderen en te vernietigen.

Ernst & Young hanteert bij de uitoefening van haar werkzaamheden algemene
voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De
algemene voorwaarden worden u op verzoek kosteloos toegezonden.
=
The information contained in this communication is confidential and is
intended solely for the use of the individual or entity to whom it is
addressed. You should not copy, disclose or distribute this communication
without the authority of Ernst & Young. Ernst & Young is neither liable for
the proper and complete transmission of the information contained in this
communication nor for any delay in its receipt. Ernst & Young does not
guarantee that the integrity of this communication has been maintained nor
that the communication is free of viruses, interceptions or interference.

If you are not the intended recipient of this communication please return
the communication to the sender and delete and destroy all copies.

In carrying out its engagements, Ernst & Young applies general terms and
conditions, which contain a clause that limits its liability. A copy of
these terms and conditions is available on request free of charge.
===





--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jack van Zanen
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Ruth Gramolini
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



RE: Staspack Grapher/Viewer ?

2002-09-19 Thread Post, Ethan

I have a 1.5 MB zip file containing my old web site for called gnumetrics
which uses a PL/SQL package and MRTG to chart performance data.  If someone
has a webserver I can load this to I will.  It is old but it works.  At the
very least you will get some ideas on how to do this.  I store all my data
internally now using the same aggregation policy as MRTG to get up to one
year of data in very small size.  I chart it using ODBC an Access with
Microsoft Graph.  Works very well and I get very useful reports.

Ethan Post
perotdba (AIM), epost1 (Yahoo)



-Original Message-
Sent: Thursday, September 19, 2002 12:10 PM
To: Multiple recipients of list ORACLE-L


This product seems to use the now infamous CHR which has been banned from
this list :)

Has anyone figured out how to use MRTG (or Cricket) to get this done? [Why
buy when you can use Open Source!]

John

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Post, Ethan
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



PL/SQL help

2002-09-19 Thread Ron Thomas


I am building a dynamic sql statement which will contain varying number of bind 
variables depending
on user selection criteria.  As an example, the sql statement may be:

c_sql := 'select col1 from  atable where col2 = :1' ;

or it may be

c_sql := 'select col1 from  atable where col2 = :1 and col2 between :2 and :3' ;

or it may be ... etc.  I am trying to avoid ugly code such as:

IF case1 THEN
  OPEN csr FOR c_sql USING var1 ;
ELSIF case2 THEN
  OPEN csr FOR c_sql USING var1, var2, var3 ;
ELSIF .
END IF ;

Once the sql statement is created, it will be opened/closed multiple times, so I want 
to use bind
variables to avoid parsing.

So I thought, hum, sounds like a job for EXECUTE IMMEDIATE, but in the various 
incarnations I tried,
could not get it to work.  The FM have not been much help (still looking tho).

What am I missing?

Thanks,
Ron Thomas
Hypercom, Inc
[EMAIL PROTECTED]
Each new user of a new system uncovers a new class of bugs. -- Kernighan

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Ron Thomas
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



Best method to move Filesystems to RAW Devices.

2002-09-19 Thread Nat

We are planning to move to raw devices for all our existing file systems.
Our database size is around 400 Gig. What is the recommended method that you
guys feel is best as far as time
required to convert and ease of conversion.

We feel we cannot use export - import as this may take more time for
conversion..
I checked many documents to find out  the best method, there are few
suggestions to use RMAN to convert to raw.
seems it is fastest. At this point we have not configured RMAN on our
databases so this suggestion seems to be of no use for us.

Please let me know, if any of you went through this exercise and any
suggestions and tips will be more beneficial,

Thanks in advance,
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Nat
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



RE: Why does my insert creates so many logs?

2002-09-19 Thread Johnston, Tim

What version?  What is the definition of table A?  Have you enabled parallel
DML?  What about the append hint?  According to the docs it is supposed to
be automatic with the parallel hint but...

Tim

-Original Message-
Sent: Wednesday, September 18, 2002 3:39 PM
To: Multiple recipients of list ORACLE-L


Hi.

A developer of mine is running a large insert as
select:

insert /* parallel hint */ into table A 
nologging 
(select * from table b where ...);

There are no indices on table A and a PK disabled.
Still that insert generates a large amount of logs.
What could be the reason for that? Any ideas? Table A
is not partitioned and has NOLOGGING attribute on the
dba_tables set to Yes.

thanks

Gene

__
Do you Yahoo!?
Yahoo! News - Today's headlines
http://news.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Gurelei
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Johnston, Tim
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



Re: oracle and appliance storage

2002-09-19 Thread Yechiel Adar



Use map network drive and then use the new drive letter for files.
Of course, do not forget sharing on the receiving drive.

 Yechiel Adar
 Mehish
 - Original Message -
> From: <[EMAIL PROTECTED]>
> To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> Sent: Thursday, September 19, 2002 7:09 PM
> Subject: OT: oracle and appliance storage
>
>
> > Hi All,
> >
> > Server A,  Win 2000, Oracle 8.1.7
> > Server B, Win 2000 with Auspex storage appliance
> >
> > We want to test all datafiles to be on Server B.  How does Oracle on
> server
> > A know about datafiles on server B. It is my understanding
> > that Oracle does not recognize map drives. There must be another way
that
> > Oracle recognizes drives on Server B.  Can anyone shed
> > any light?
> >
> > Thanks
> > Rick
> >
> >
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > --
> > Author:
> >   INET: [EMAIL PROTECTED]
> >
> > Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> > San Diego, California-- Mailing list and web hosting services
> > -
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from).  You may
> > also send the HELP command for other information (like subscribing).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Yechiel Adar
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



copying all schemas except sys

2002-09-19 Thread Ben

Hi

Starting with Oracle8i there is a problem with using full export
and full import to re-create a database. The import coughs up a
lot of errors due to the SYS schema (replication and help objects).
How do people go about moving all the schemas from one instance to
another without manually creating all the schema owners in the
new instance and then exporting/importing every schema by name.
What I want is an "ignore sys schema" parameter in the export
utility.

Thanks,

Ben
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Ben
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



Used/Free Temporary Space

2002-09-19 Thread VIVEK_SHARMA


On Issue of Following Query :-

select ollh_acid,decode(oldalh.b2k_type,'ACFFD',oldalh.modify_bod_date , 
oldalh.another_date)
from oldalh,solgam
where oldalh.acid=solgam.acid
and solgam.sol_id = CONSTANT

NOTE - NO Index Exists 
acid has a Unique Value in Every  Record of solgam Table
sol_id is a Value for a Set of  solgam.acid Values in solgam Table

Error :-

ORA-01652 "unable to extend temp segment by %s in tablespace %s"

NOTE - TEMPORARY Tablespace of Size 11 GB

Size of OLDALH Table = 6 GB
Size of solgam = 500 MB

Qs Should the Above Query Used Temporary Space ? if so , Why ?

Qs From which View (How) can we find the Total Temporary Space Used OR Free in 
Temporary Tablespace 

Qs Will Creating an Index on oldalh.acid Reduce Usage of Temporary Space ?

Qs  What index Creation is Advisable on solgam Table  ?

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: VIVEK_SHARMA
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



re: copying all schemas except sys

2002-09-19 Thread Ben

Maybe I answered my own question. If I do the full export
as another dba user (not sys,system) then hopefully I will
not get the sys schema in the export.

Ben

-Original Message-
Sent: September 19, 2002 2:14 PM
To: Oracle List New


Hi

Starting with Oracle8i there is a problem with using full export
and full import to re-create a database. The import coughs up a
lot of errors due to the SYS schema (replication and help objects).
How do people go about moving all the schemas from one instance to
another without manually creating all the schema owners in the
new instance and then exporting/importing every schema by name.
What I want is an "ignore sys schema" parameter in the export
utility.

Thanks,

Ben
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Ben
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



RE: Why does my insert creates so many logs?

2002-09-19 Thread Gurelei

All the indices are dropped and the PK is disabled.
I agree that there are some changes to be done
in the data disctinary tables, but the amount of
archived logs - about 90M per minute looked too high
to me

--- Viral Desai <[EMAIL PROTECTED]> wrote:
> 
> A couple of things to try ---
> 
> 1. Drop the indexes and primary key instead of
> disabling them. Insert the 
> data and recreate pk.
> 
> 2. This could be due to changes in data dictionary,
> when you insert large 
> number of rows in the table, new extents may be
> allocated or high water mark 
> of the table would be modified. This information
> need to be logged. I think 
> that the redo generated due to this should not be
> very large though.
> 
> Hope this helps.
> Viral Desai
> 
> 
> >From: Gurelei <[EMAIL PROTECTED]>
> >Reply-To: [EMAIL PROTECTED]
> >To: Multiple recipients of list ORACLE-L
> <[EMAIL PROTECTED]>
> >Subject: RE: Why does my insert creates so many
> logs?
> >Date: Thu, 19 Sep 2002 06:03:29 -0800
> >
> >None.
> >--- "Nicoll, Iain (Calanais)"
> ><[EMAIL PROTECTED]> wrote:
> > > Doesn't have any triggers does it?
> > >
> > > -Original Message-
> > > Sent: Wednesday, September 18, 2002 8:39 PM
> > > To: Multiple recipients of list ORACLE-L
> > >
> > >
> > > Hi.
> > >
> > > A developer of mine is running a large insert as
> > > select:
> > >
> > > insert /* parallel hint */ into table A
> > > nologging
> > > (select * from table b where ...);
> > >
> > > There are no indices on table A and a PK
> disabled.
> > > Still that insert generates a large amount of
> logs.
> > > What could be the reason for that? Any ideas?
> Table
> > > A
> > > is not partitioned and has NOLOGGING attribute
> on
> > > the
> > > dba_tables set to Yes.
> > >
> > > thanks
> > >
> > > Gene
> > >
> > >
> __
> > > Do you Yahoo!?
> > > Yahoo! News - Today's headlines
> > > http://news.yahoo.com
> > > --
> > > Please see the official ORACLE-L FAQ:
> > > http://www.orafaq.com
> > > --
> > > Author: Gurelei
> > >   INET: [EMAIL PROTECTED]
> > >
> > > Fat City Network Services-- 858-538-5051
> > > http://www.fatcity.com
> > > San Diego, California-- Mailing list and
> web
> > > hosting services
> > >
>
>-
> > > To REMOVE yourself from this mailing list, send
> an
> > > E-Mail message
> > > to: [EMAIL PROTECTED] (note EXACT spelling of
> > > 'ListGuru') and in
> > > the message BODY, include a line containing:
> UNSUB
> > > ORACLE-L
> > > (or the name of mailing list you want to be
> removed
> > > from).  You may
> > > also send the HELP command for other information
> > > (like subscribing).
> > > --
> > > Please see the official ORACLE-L FAQ:
> > > http://www.orafaq.com
> > > --
> > > Author: Nicoll, Iain \(Calanais\)
> > >   INET: [EMAIL PROTECTED]
> > >
> > > Fat City Network Services-- 858-538-5051
> > > http://www.fatcity.com
> > > San Diego, California-- Mailing list and
> web
> > > hosting services
> > >
>
>-
> > > To REMOVE yourself from this mailing list, send
> an
> > > E-Mail message
> > > to: [EMAIL PROTECTED] (note EXACT spelling of
> > > 'ListGuru') and in
> > > the message BODY, include a line containing:
> UNSUB
> > > ORACLE-L
> > > (or the name of mailing list you want to be
> removed
> > > from).  You may
> > > also send the HELP command for other information
> > > (like subscribing).
> >
> >
> >__
> >Do you Yahoo!?
> >New DSL Internet Access from SBC & Yahoo!
> >http://sbc.yahoo.com
> >--
> >Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> >--
> >Author: Gurelei
> >   INET: [EMAIL PROTECTED]
> >
> >Fat City Network Services-- 858-538-5051
> http://www.fatcity.com
> >San Diego, California-- Mailing list and
> web hosting services
>
>-
> >To REMOVE yourself from this mailing list, send an
> E-Mail message
> >to: [EMAIL PROTECTED] (note EXACT spelling of
> 'ListGuru') and in
> >the message BODY, include a line containing: UNSUB
> ORACLE-L
> >(or the name of mailing list you want to be removed
> from).  You may
> >also send the HELP command for other information
> (like subscribing).
> 
> 
> 
> 
>
_
> MSN Photos is the easiest way to share and print
> your photos: 
> http://photos.msn.com/support/worldwide.aspx
> 
> -- 
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> -- 
> Author: Viral Desai
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- 858-538-5051
> http://www.fatcity.com
> San Diego, California-- Mailing list and web
> hosting services
>
-
> To REMOVE yourself from this mailing list, send an
> E-Mail message
> to: [EMAIL PRO

count(*)

2002-09-19 Thread Rishi . Jain

Hi Gurus,

In one of our insert intensive application we are inserting around 3-4
million rows / hour. Also this app needs to do a count(*) of the tables
every 10 minutes for verifying some application based logic. This is really
killing us and it takes a lot of time. 

Can you please guide me to a direction ( built in functions or something
similar). 

Actually this app is being ported from Informix. Informix can somehow keep a
trak of the count(*) of a  table in its header somewhere.

And yes I have tries count(1) , count(indexed_column) etc.


Thanks In Advance.

R.h
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



RE: High values

2002-09-19 Thread DENNIS WILLIAMS

John - Since nobody has replied to your question, I discussed it with a
fellow COBOL programmer. I think that in COBOL you normally would use this
in an iterative loop, as a comparison. In SQL itself, you rarely iterate, so
you probably don't have that much need for a HIGH-VALUE. In PL/SQL you might
be more likely to need it. Myself, I have used such a thing in the C
language before, and there was usually a precompiler value that you could
include. For Oracle, the maximum integer that can be represented is 38 9's
times 10 to the 125th. power. Here is a web address that lists a lot of the
Oracle limits as of 8.0.5.

 http://storacle.princeton.edu:9001/oracle8-doc/server.805/a58242/ch5.htm

Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED]  


-Original Message-
Sent: Thursday, September 19, 2002 12:21 PM
To: Multiple recipients of list ORACLE-L


Is there a Oracle equivalent to the Cobol HIGH-VALUES value?

John
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: John Dunn
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: DENNIS WILLIAMS
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



RE: Staspack Grapher/Viewer ?

2002-09-19 Thread Jesse, Rich

This has me thinking.  What exactly do you want to graph?  I guess I'm just
trying to see some value in graphing data from a single SP report.  Graph
waits as a pie chart?  Or would it be for several reports combined?

Also, after looking again at the layout of an SP report, a home-rolled query
would do much better at feeding MRTG or GNUPLOT.

Just wondering if I can get myself into yet another project...  :)

Rich Jesse   System/Database Administrator
[EMAIL PROTECTED]  Quad/Tech International, Sussex, WI USA

> -Original Message-
> From: John Kanagaraj [mailto:[EMAIL PROTECTED]]
> Sent: Thursday, September 19, 2002 12:10 PM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: Staspack Grapher/Viewer ?
> 
> 
> This product seems to use the now infamous CHR which has been 
> banned from
> this list :)
> 
> Has anyone figured out how to use MRTG (or Cricket) to get 
> this done? [Why
> buy when you can use Open Source!]
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jesse, Rich
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



RE: copying all schemas except sys

2002-09-19 Thread K Gopalakrishnan

Export program **never** exports the contents 
of SYS. WHy do you need a parameter IGNORE_SYS
when it is ignored already?

KG


-Original Message-
Sent: Thursday, September 19, 2002 12:18 PM
To: Multiple recipients of list ORACLE-L


Hi

Starting with Oracle8i there is a problem with using full export
and full import to re-create a database. The import coughs up a
lot of errors due to the SYS schema (replication and help objects).
How do people go about moving all the schemas from one instance to
another without manually creating all the schema owners in the
new instance and then exporting/importing every schema by name.
What I want is an "ignore sys schema" parameter in the export
utility.

Thanks,

Ben
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Ben
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: K Gopalakrishnan
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



RE: Rule Vs Cost

2002-09-19 Thread Surendra . Tirumala

John,

Thanks again for providing those links to Tim's paper and yours. 
They are really helping in understanding/talking about CBO in a better way.
BTW, Today I have experienced the Over-kill of PQ you mentioned. One of our
reports which used to run very fast kept hanging in there and I had to
disable the PQ at table level and use hints whenever needed.

Surendra

-Original Message-
Sent: Friday, September 13, 2002 10:13 PM
To: Multiple recipients of list ORACLE-L


Surendra,
 
Saw no replies, so just pitching in. Moving over to the CBO does give you
flexibility and the option for a query to 'self-tune' itself depending on
the object statistics. (Dare I say 'self-tune' with all the notes flying
around :)  Anyway - what I meant was that _most_ queries would adjust their
execution path depending on these stats. The two inputs to this process is
the objects stats themselves as well as the algorithm that operates on these
stats. This 'algorithm' can further be influenced (for want of a better
term) using a number of init.ora parameters (documented and undocumented).
The art is to determine the right values, but there are a few values such as
OPTIMIZER_INDEX_CACHING and OPTIMIZER_INDEX_COST_ADJ and that you will not
go wrong with - Tim Gorman deals with these two in his excellent paper on
the CBO. Browse at Tim's site at 'http://www.evdbt.com' .
 
At the same time, moving to the CBO without understanding this complex beast
is like getting into a souped-up Porshe when you have just got your Trainee
driver permit.
There are a number of issues with this that even seasoned DBAs miss... I
tried to cover these issues in my paper - just follow the second link on
'http://www.geocites.com/john_sharmila/links.htm'.
 
One way of performing a gradual move to the CBO is by gathering stats and
then using an ON-LOGON trigger to switch the optimizer_goal to CHOOSE for
selected programs/sessions. A final hint: Use PQ with caution - overuse and
ill-planned implementation can be potentially deadly and overwhelme your
CPUs. IMHO PQ is to be used ony in certain cases and that too only from the
SQL using Hints, which will limit PQ only to that SQL.
 
John Kanagaraj 
Oracle Applications DBA 
DB Soft Inc 
Work : (408) 970 7002 

Listen to great, commercial-free christian music 24x7x365 at
http://www.klove.com   

** The opinions and facts contained in this message are entirely mine and do
not reflect those of my employer or customers **

-Original Message-
[mailto:[EMAIL PROTECTED]]
Sent: Friday, September 13, 2002 9:18 AM
To: Multiple recipients of list ORACLE-L


Hello All,
 
I am observing that some of our production instances(8i) are running on Rule
Based Optimizer. I am dealing with the task of tuning a bunch of reports.
While I continue my adventures, can you guys please let me know if there is
any reason to continue with age old Rule based optimizer when we have so
many advantages with CBO? I am exploring the option of using PQ with couple
of reports but the RBO is forcing me to stop thinking about it. 
 
All your suggestion are very much appreciated.
 
Thanks in advance.
 
Surendra Tirumala
Oracle DBA
Cabinet for Workforce Development
Commonwealth of Kentucky

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: John Kanagaraj
  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 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



Re: sqlplus question

2002-09-19 Thread Yechiel Adar

Tried this.  The field length is still 56.

Yechiel Adar
Mehish
- Original Message - 
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Sent: Thursday, September 19, 2002 6:43 PM


> select substr(ename,1,length(ename)), job from emp;
> 
> >From: "Bob Metelsky" <[EMAIL PROTECTED]>
> >Reply-To: [EMAIL PROTECTED]
> >To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> >Subject: RE: sqlplus question
> >Date: Thu, 19 Sep 2002 08:18:51 -0800
> >
> >
> > >
> > > I am spooling my sqlplus output to a file with no
> > > headings and all the fields separated by a delimiter. I have
> > > a field that is defined as varchar2(56), but typically only 4
> > > or 5 bytes are filled. Oracle recognizes that and if you
> > > select length(fld1) from the table, you will get 4. But if I
> > > spool this to a file, I always get the full 56 bytes padded
> > > with blanks. In other words, I get 4 bytes of data and 52
> > > blanks for that field. I only want the four valid bytes so
> > > that my delimiter comes immediately after that 4th byte. My
> > > sqlplus options are as follows:
> > >
> > > set newpage 0 space 0 linesize 5000 pagesize 0 echo off
> > > recsep off feedback off heading off trimspool on colsep "|"
> >
> >Sounds to me like the trimspool should do it...
> >
> >Are you sure its set on
> >
> >ME@DB1 -> trimspool on
> >SP2-0734: unknown command beginning "trimspool ..." - rest of line
> >ignored.
> >
> >Try  set trims on
> >
> >Or
> >
> >Set trimspool on
> >
> >
> >bob
> >--
> >Please see the official ORACLE-L FAQ: http://www.orafaq.com
> >--
> >Author: Bob Metelsky
> >   INET: [EMAIL PROTECTED]
> >
> >Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> >San Diego, California-- Mailing list and web hosting services
> >-
> >To REMOVE yourself from this mailing list, send an E-Mail message
> >to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> >the message BODY, include a line containing: UNSUB ORACLE-L
> >(or the name of mailing list you want to be removed from).  You may
> >also send the HELP command for other information (like subscribing).
> 
> 
> 
> 
> _
> MSN Photos is the easiest way to share and print your photos: 
> http://photos.msn.com/support/worldwide.aspx
> 
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> -- 
> Author: Viral Desai
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Yechiel Adar
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



RE: Why does my insert creates so many logs?

2002-09-19 Thread Fink, Dan

This might sound like an exercise in pointlessness but here goes...

Are you sure you are generating the redo?

Use the following to get the sid of the sessions generating redo and then
track back to the session/process/statement info. I realize that the query
does not exactly query redo, but I have always found that the correlation
between block_changes + consistent_changes and redo entries matches up. If
there are others who can point out the fallacy of my thinking, I await
enlightenment. (No jokes about changing _spin_count please...)

select sid, (block_changes + consistent_changes)
from v$sess_io
where (block_changes + consistent_changes) > 5000   /* insert a reasonable
value here */
order by 2 desc;


   SID (BLOCK_CHANGES+CONSISTENT_CHANGES)
-- --
 8 175079
 5308

Dan Fink
-Original Message-
Sent: Thursday, September 19, 2002 12:24 PM
To: Multiple recipients of list ORACLE-L


All the indices are dropped and the PK is disabled.
I agree that there are some changes to be done
in the data disctinary tables, but the amount of
archived logs - about 90M per minute looked too high
to me

--- Viral Desai <[EMAIL PROTECTED]> wrote:
> 
> A couple of things to try ---
> 
> 1. Drop the indexes and primary key instead of
> disabling them. Insert the 
> data and recreate pk.
> 
> 2. This could be due to changes in data dictionary,
> when you insert large 
> number of rows in the table, new extents may be
> allocated or high water mark 
> of the table would be modified. This information
> need to be logged. I think 
> that the redo generated due to this should not be
> very large though.
> 
> Hope this helps.
> Viral Desai
> 
> 
> >From: Gurelei <[EMAIL PROTECTED]>
> >Reply-To: [EMAIL PROTECTED]
> >To: Multiple recipients of list ORACLE-L
> <[EMAIL PROTECTED]>
> >Subject: RE: Why does my insert creates so many
> logs?
> >Date: Thu, 19 Sep 2002 06:03:29 -0800
> >
> >None.
> >--- "Nicoll, Iain (Calanais)"
> ><[EMAIL PROTECTED]> wrote:
> > > Doesn't have any triggers does it?
> > >
> > > -Original Message-
> > > Sent: Wednesday, September 18, 2002 8:39 PM
> > > To: Multiple recipients of list ORACLE-L
> > >
> > >
> > > Hi.
> > >
> > > A developer of mine is running a large insert as
> > > select:
> > >
> > > insert /* parallel hint */ into table A
> > > nologging
> > > (select * from table b where ...);
> > >
> > > There are no indices on table A and a PK
> disabled.
> > > Still that insert generates a large amount of
> logs.
> > > What could be the reason for that? Any ideas?
> Table
> > > A
> > > is not partitioned and has NOLOGGING attribute
> on
> > > the
> > > dba_tables set to Yes.
> > >
> > > thanks
> > >
> > > Gene
> > >
> > >
> __
> > > Do you Yahoo!?
> > > Yahoo! News - Today's headlines
> > > http://news.yahoo.com
> > > --
> > > Please see the official ORACLE-L FAQ:
> > > http://www.orafaq.com
> > > --
> > > Author: Gurelei
> > >   INET: [EMAIL PROTECTED]
> > >
> > > Fat City Network Services-- 858-538-5051
> > > http://www.fatcity.com
> > > San Diego, California-- Mailing list and
> web
> > > hosting services
> > >
>
>-
> > > To REMOVE yourself from this mailing list, send
> an
> > > E-Mail message
> > > to: [EMAIL PROTECTED] (note EXACT spelling of
> > > 'ListGuru') and in
> > > the message BODY, include a line containing:
> UNSUB
> > > ORACLE-L
> > > (or the name of mailing list you want to be
> removed
> > > from).  You may
> > > also send the HELP command for other information
> > > (like subscribing).
> > > --
> > > Please see the official ORACLE-L FAQ:
> > > http://www.orafaq.com
> > > --
> > > Author: Nicoll, Iain \(Calanais\)
> > >   INET: [EMAIL PROTECTED]
> > >
> > > Fat City Network Services-- 858-538-5051
> > > http://www.fatcity.com
> > > San Diego, California-- Mailing list and
> web
> > > hosting services
> > >
>
>-
> > > To REMOVE yourself from this mailing list, send
> an
> > > E-Mail message
> > > to: [EMAIL PROTECTED] (note EXACT spelling of
> > > 'ListGuru') and in
> > > the message BODY, include a line containing:
> UNSUB
> > > ORACLE-L
> > > (or the name of mailing list you want to be
> removed
> > > from).  You may
> > > also send the HELP command for other information
> > > (like subscribing).
> >
> >
> >__
> >Do you Yahoo!?
> >New DSL Internet Access from SBC & Yahoo!
> >http://sbc.yahoo.com
> >--
> >Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> >--
> >Author: Gurelei
> >   INET: [EMAIL PROTECTED]
> >
> >Fat City Network Services-- 858-538-5051
> http://www.fatcity.com
> >San Diego, California-- Mailing list and
> web hosting services
>
>

RE: count(*)

2002-09-19 Thread Suri, Deepak

I have in the past used parallel hints to speed up a count(*) kind of full
table scan query.

thanx
deepak

-Original Message-
[mailto:[EMAIL PROTECTED]]
Sent: Thursday, September 19, 2002 2:28 PM
To: Multiple recipients of list ORACLE-L


Hi Gurus,

In one of our insert intensive application we are inserting around 3-4
million rows / hour. Also this app needs to do a count(*) of the tables
every 10 minutes for verifying some application based logic. This is really
killing us and it takes a lot of time. 

Can you please guide me to a direction ( built in functions or something
similar). 

Actually this app is being ported from Informix. Informix can somehow keep a
trak of the count(*) of a  table in its header somewhere.

And yes I have tries count(1) , count(indexed_column) etc.


Thanks In Advance.

R.h
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Suri, Deepak
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



RE: Used/Free Temporary Space

2002-09-19 Thread DENNIS WILLIAMS

Vivek - For the username you are executing this query under, what is the
designated temporary tablespace? How many rows (approximately) are you
expecting this query to retrieve? 

Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED]  


-Original Message-
Sent: Thursday, September 19, 2002 1:18 PM
To: Multiple recipients of list ORACLE-L



On Issue of Following Query :-

select ollh_acid,decode(oldalh.b2k_type,'ACFFD',oldalh.modify_bod_date ,
oldalh.another_date)
from oldalh,solgam
where oldalh.acid=solgam.acid
and solgam.sol_id = CONSTANT

NOTE - NO Index Exists 
acid has a Unique Value in Every  Record of solgam Table
sol_id is a Value for a Set of  solgam.acid Values in solgam Table

Error :-

ORA-01652 "unable to extend temp segment by %s in tablespace %s"

NOTE - TEMPORARY Tablespace of Size 11 GB

Size of OLDALH Table = 6 GB
Size of solgam = 500 MB

Qs Should the Above Query Used Temporary Space ? if so , Why ?

Qs From which View (How) can we find the Total Temporary Space Used OR Free
in Temporary Tablespace 

Qs Will Creating an Index on oldalh.acid Reduce Usage of Temporary Space ?

Qs  What index Creation is Advisable on solgam Table  ?

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: VIVEK_SHARMA
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: DENNIS WILLIAMS
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



  1   2   >