RE: RMAN Catalog: 1 vs. many - Opinions

[K Gopalakrishnan]

Gaja:

Having dedicated RMAN server is indeed  a good idea,
but RMAN catalog is not designed for that much (!!?)
high concurrency. If you look at the commands (executed
by the RMAN during backup) very closely, you will see
lots of SELECT for UPDATE and this will cause a big
bottleneck in the backup process.

It happened at one of our client place where they backup
200+ databases with  a single RMAN catalog at 2-3 hrs 
interval.  The workaround suggested was 
a) Have more RMAN Catalogs
b) Run the backup in different times.

So again it 'all depends '

Best Regards,
K Gopalakrishnan

 


-Original Message-
Krishna Vaidyanatha
Sent: Friday, April 04, 2003 4:04 PM
To: Multiple recipients of list ORACLE-L


Paul et al.,

I agree with Paul's setup of a dedicated RMAN catalog
server. It is not a bad idea to have the datafiles for
this database on mirrored volumes. I also think a good
practice related to "saving the catalog" against tape
media errors is to do a full export on the RMAN
Catalog DB and stash the export dumpfile away on a
mirrored volume away from the datafiles. 

If this DB has nothing but the RMAN catalog, it should
not be very large (let me know if this is otherwise)
and thus the full export will be a good method to have
a "logical backup". One more thing to save the skin on
your back, at a time of need. When Murphy is around I
think the word "mercy" vanishes from the English
dictionary.


Cheers,

Gaja

--- [EMAIL PROTECTED] wrote:
> I have setup a server for just rman catalog backed
> up to tape with clones
> offsite for DR.  It makes it easier as scripts are
> parameterized to keep
> aware of backup statuses = etc.  Having this on a
> centralized management
> server and can't believe this isn't just common
> practice.  Just make sure no
> single point of failure.
> 
> Oracle OCP DBA
> 
> 
> -Original Message-
> Sent: Friday, April 04, 2003 3:39 PM
> To: Multiple recipients of list ORACLE-L
> 
> 
> Deepak
>You may also wish to consider not using
> catalog(s) -- control file
> backups. This might make it easier to add and remove
> databases in your
> environment. I'm presuming most databases are on
> their own server.
>If you want to be able to run a single query that
> will report any backup
> problems across all your databases, you will use a
> single catalog.
> 
> Dennis Williams
> DBA, 40%OCP, 100% DBA
> Lifetouch, Inc.
> [EMAIL PROTECTED] 
> 
> 
> -Original Message-
> Sent: Friday, April 04, 2003 2:09 PM
> To: Multiple recipients of list ORACLE-L
> 
> 
> 
> We are in the process of moving from traditional hot
> backups to one using RMAN. Total databases are
> around
> 200+. Should we be using 1 or few RMAN catalogs, or
> 1
> catalog per database? 
> 
> Thanks,
> Deepak
> 
> __
> Do you Yahoo!?
> Yahoo! Tax Center - File online, calculators, forms,
> and more
> http://tax.yahoo.com
> -- 
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.net
> -- 
> Author: Deepak 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.net
> -- 
> Author: DENNIS WILLIAMS
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- 858-538-5051
> http://www.fatcity.com
> San Diego, California-- Mailing list and web
> hosting services
>
-
> To REMOVE yourself from this mailing list, send an
> E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of
> 'ListGuru') and in
> the message BODY, include a line containing: UNSUB
> ORACLE-L
> (or the name of mailing list you want to be removed
> from).  You may
> also send the HELP command for other information
> (like subscribing).
> 


=


__
Do you Yahoo!?
Yahoo! Tax Center - File online, calculators, forms, and more
http://tax.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Gaja Krishna Vaidyanatha
  INET: [EMAIL PROTECTED]

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

OT - Project Management

[Abdul Aleem]

Hi,

Browsing on the net I came across a site "Project Management Institute" PMI
for short. www.pmi.org The offer two levels of courses in project management
Associate and Professional.

Anyone know about it? How good it is to acquire?

Thanks in advance

Aleem

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

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: PL/SQL- cursors and commits

[Ganesh Raja]

Title: Message



This is the last thng u will be writing .. a Commit inside a Loop for 
every n records processed. 
 
First Like Ashish Said u will get ORA-01002 and apart from this u will 
hit by a bigger problem.. ORA-01555 on long running quries..
 
HTH
 
 
Best Regards,Ganesh RDID : +65-6215-8413HP  : 
+65-9067-8474 

  
  -Original Message-From: [EMAIL PROTECTED] 
  [mailto:[EMAIL PROTECTED] On Behalf Of AshishSent: Saturday, 
  April 05, 2003 12:19 AMTo: Multiple recipients of list 
  ORACLE-LSubject: RE: PL/SQL- cursors and 
  commits
  As 
  the book says, it fails with following error (9.2.0.1 on 
  Win2k).
   
  declare*ERROR at line 
  1:ORA-01002: fetch out of sequenceORA-06512: at line 12
  
-Original Message-From: [EMAIL PROTECTED] 
[mailto:[EMAIL PROTECTED]On Behalf Of Wiegand, KurtSent: 
Friday, April 04, 2003 9:39 AMTo: Multiple recipients of list 
ORACLE-LSubject: PL/SQL- cursors and commits
I've been 'experimenting' with the following code 
in 8.1.5 and it seems to work fine.  However, my "ORACLE PL/SQL" book from O'REILLY (Steven Feuerstein 
Bill Pribyl 1997) leads me to believe 
that it should not work.  They state "As soon as a cursor with a FOR 
UPDATE is OPENed, all rows...are locked. 
When [a COMMIT]..occurs, the locks...are released. As a result, you 
cannot execute another FETCH against a FOR 
UPDATE cursor after you COMMIT.."  They go further to suggest an ORA-01002 would be returned. 
Any comments? Thanks. 
Kurt Wiegand [EMAIL PROTECTED] 
declare   
local_f1 ctest.f1%TYPE := 0;   
local_f2 ctest.f2%TYPE := 0;   
batch_count number(6) := 0;   cursor 
c_select is   select f1,f2 from 
ctest   for update; 
begin   
open c_select;   loop 
  fetch 
c_select   
into local_f1,  
local_f2;   
exit when c_select%NOTFOUND;   update ctest  
set f2 = f2 + 1  
where current of c_select; 
 batch_count := 
batch_count + 1; 
 if batch_count > 99 
then     batch_count := 0; 
    
commit;  end 
if;     end loop;   close 
c_select;   commit; end; 

RE: Full table scan difference

[DENNIS WILLIAMS]

Cary, Denny - Thanks very much. I was running out of ideas on a Friday
afternoon, then I just ran out. Have a good weekend, will try these
suggestions Monday.

Dennis Williams
DBA, 40%OCP, 100% DBA
Lifetouch, Inc.
[EMAIL PROTECTED] 


-Original Message-
Sent: Friday, April 04, 2003 3:54 PM
To: Multiple recipients of list ORACLE-L


1. Dial 1-0-0-4-6.
2. Look at the latching and pinning statistics promoted at asktom, ixora,
and jlcomp.


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

Upcoming events:
- Hotsos Clinic 101 in Chicago, London, Reykjavik, Ottawa, Denver
- Visit www.hotsos.com for schedule details...
- IOUG-A Live 2003, Orlando, 10am Monday 28 April: "Oracle Operational
Timing Data"

-Original Message-
WILLIAMS
Sent: Friday, April 04, 2003 2:49 PM
To: Multiple recipients of list ORACLE-L

List - As I mentioned earlier, a new manager came from a site that regularly
rebuilt tables. I protested the value of this, but his response was "well,
at least you could test it." So on a test instance I've been doing a CTAS to
create a reorganized copy of the table in the same tablespace (LMT with
uniform extents, autoextend on). As expected, the number of blocks is very
close. 
   Then I've been doing a FTS (select count(*)) on both tables. Usually the
time is very close. However, on one large table, the original is much faster
-- 18-sec. vs. 27-sec. 21,349 blocks vs. 21179. Same plan. Does anyone have
a theory for this discrepancy?

Dennis Williams
DBA, 40%OCP, 100% DBA
Lifetouch, Inc.
[EMAIL PROTECTED] 
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: DENNIS WILLIAMS
  INET: [EMAIL PROTECTED]

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

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: 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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: DENNIS WILLIAMS
  INET: [EMAIL PROTECTED]

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

Re: Autoallocate vs Uniform extent performance

[Rachel Carmichael]

Jack,

I've got no experience with out-of-line CLOB segments, so I don't know
if that changes how the drop would work.

As for the maximum number of extents, that's what I was told. Not that
it was necessarily "don't ever do that", just something to think about.

Rachel
--- [EMAIL PROTECTED] wrote:
> Rachel,
> 
> At a recent past job, under 8.1.6 on Win2k we had tables with
> out-of-line
> CLOB segments of 30,000 extents (1MB each).  Every month we dropped
> one to
> make room for another (6 months of CLOB documents online).  It always
> just
> took a few seconds for the drop.  These were in DMTs.
> 
> Later we switched servers and I changed to LMTs of 100MB Uniform
> Extents
> for the CLOB segments.  Going from 30,000 to 300 extents for those
> hulks
> made no noticeable difference in query or interMedia indexing
> performance,
> nor did it noticeably change the time it took to drop the tables.
> 
> Here at AISD, our student information database (SASI, for those in
> Education who know this 3rd party app) has over 47,000 tables and
> 70,000
> indexes (typical abysmal design for a 3rd party app, eh?), many of
> them
> empty or with very few rows.  A few months ago I rebuilt it under
> 8.1.7.4.6
> (Win2k - it was previously at 8.1.7.0.0) with LMTs of 8KB Uniform
> Extents
> to save space.  Surprisingly, only 40 or so segments have over 1000
> extents.  One, a consolidated Student table, has a little over 10,000
> extents.  We've noticed no problem at all with performance, etc.
> 
> I've not been concerned about extent counts for several years now,
> and I've
> seen nothing convincing that I should be.  Maybe I've just not hit
> the
> situation where it matters.  That is not to say that extents don't
> matter,
> but it's only if they obey the stupid directives of uninformed
> duhvelopers,
> such as those of our 3rd party Financials system, where they used
> PctIncrease of 50.  Like children and dogs, there are no bad extents,
> just
> bad designers.;-)
> 
> Jack C. Applewhite
> Database Administrator
> Austin Independent School District
> Austin, Texas
> 512.414.9715 (wk)
> 512.935.5929 (pager)
> [EMAIL PROTECTED]
> 
> 
> 
>  
>  
>   Rachel Carmichael  
>  
>   <[EMAIL PROTECTED]To:   Multiple
> recipients of list ORACLE-L  
>   o.com>   
> <[EMAIL PROTECTED]> 
>   Sent by: cc:   
>  
>   [EMAIL PROTECTED] Subject:  Re:
> Autoallocate vs Uniform extent performance
>  
>  
>  
>  
>   04/04/2003 07:01   
>  
>   AM 
>  
>   Please respond to  
>  
>   ORACLE-L   
>  
>  
>  
>  
>  
> 
> 
> 
> 
> rumor hath it (as I've never actually had an object hit that high a
> number) that when you exceed 4K extents it's time to resize. This
> came
> from one of the instructors in Oracle University, one who is
> well-known
> to actually have more than a clue. He said this at the Data Internals
> class, before 9i was released.
> 
> I have not seen his test results but I do know that tests done
> with
> DMTs have shown that large numbers of extents (I believe Kevin Loney
> tested with 60K extents, and I vaguely remember a conversation with
> Cary where he said he had also tested large numbers)  are a problem
> during operations that empty a lot of extents (think large deletes)
> because of thrashing on FET$ and UET$. Since an LMT doesn't access
> those tables by design, I would think that that problem goes away.
> --
> Author: Rachel Carmichael
>   INET: [EMAIL PROTECTED]
> 
> 
> 
> 
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> -- 
> 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

Re: Rollback and what table's DML caused an ORA-1555?

[Anjo Kolk]

Sometimes the extents should be larger (fewer larger extents, then  many
small extents).

- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Saturday, April 05, 2003 1:30 AM


> Yes, your assumption is right. Create new rollback
> segments without the OPTIMAL clause, bring them
> online, take the old ones offline, blow the old ones
> away, document the new ones in your init.ora, remove
> the old ones from your init.ora. See whether this
> solves your problem. My guess is that you should be
> set. Keep us posted.
>
> Cheers,
>
> Gaja
>
>
> --- "Jesse, Rich" <[EMAIL PROTECTED]> wrote:
> > Excellent advice, Gaja!  Yes, we do have OPTIMAL
> > set.  From the docs I don't
> > see a way to remove the OPTIMAL setting once used.
> > I assume this means new
> > RBSs?
> >
> >
> > Rich
> >
> > Rich JesseSystem/Database
> > Administrator
> > [EMAIL PROTECTED]   Quad/Tech
> > International, Sussex, WI USA
> >
> >
> > -Original Message-
> > Sent: Friday, April 04, 2003 12:54 PM
> > To: Multiple recipients of list ORACLE-L
> >
> >
> > Rich,
> >
> > The overcommiting is a definite suspect and is worth
> > looking into. ALso, do you by chance have OPTIMAL
> > set
> > on your rollback segments? If so, I'd suggest you
> > remove the OPTIMAL clause and try again. In my
> > experience, I have had my share of hassles with
> > OPTIMAL. Even when it was sized 'reasonably large'
> > (way above INITIAL * MINEXTENTS) for the
> > application.
> >
> > OPTIMAL does increase the probability of ORA-1555,
> > as
> > extents of your rollback segments that have the
> > "before images" of your transactions, can get
> > dropped,
> > while your queries are left "high and dry". Just
> > something to check.
> >
> > The downside of not setting OPTIMAL is more disk
> > usage
> > for your rollback segments. In the bigger scheme of
> > things, it may be much cheaper to eat the disk cost
> > (which is probably a few dollars) than to deal with
> > queries failing with ORA-1555s. If you are so
> > strapped
> > for space, then you can "hand shrink" the rollback
> > segments using the ALTER rollback segment xxx SHRINK
> > command via a job, at a time when no queries are
> > running.
> >
> > If you don't have OPTIMAL set, and if you are using
> > any READ-ONLY tablespaces that were put in READ-ONLY
> > mode recently, then try the following:
> >
> > 1) Set those tablespaces back to READ-WRITE mode
> > 2) Write a sql-generating-sql script that performs a
> > select count(*) on all the objects in the tablespace
> > 3) Set the tablespaces back to READ-ONLY.
> >
> > The above exercise will force a block-cleanout (if
> > required) on all the objects, which may not have
> > occured before the tablespace was originally put in
> > READ-ONLY mode.
> >
> > Hope this helps,
> >
> > Gaja
> >
> > --- "Jesse, Rich" <[EMAIL PROTECTED]> wrote:
> > > Hey all,
> > >
> > > Fighting with a lot of ORA-1555s lately on 8.1.7.4
> > > on HP/UX.  Most of them
> > > are now coming from long-running Business Objects
> > > (B.O.) queries against our
> > > OLTP DB.  I think I need to recreate the RBS
> > > tablespace (currently 1MB
> > > extents in LMT), but until I can get time to do
> > > that, I'd like to approach
> > > this from the application side, where I think the
> > > majority of the problem is
> > > occurring.  I've been tracking TPM based on "user
> > > commits" in V$SYSSTAT and
> > > we spiked at 20K TPM just before the B.O. query in
> > > question ORA-1555'd.
> > > From STATSPACK reports, I think the most likely
> > > cause for this is a COMMIT
> > > for every DML in a batch job.  From what I've
> > read,
> > > including MetaLink
> > > 40689.1, this over committing is one potential
> > cause
> > > of ORA-1555s.
> > >
> > > In order to narrow down the problem, I've turned
> > on
> > > event 1555 in the
> > > instance.  Is it possible to determine what
> > > table(s)' DML is causing the
> > > ORA-1555 based on the trace file?  I have the last
> > > wait state, which happens
> > > to be "db file sequential read", but I don't know
> > if
> > > there's any
> > > correlation.  If there is, I should be able to
> > > determine which table by the
> > > file# and block# given in the trace.  Is this
> > > correct?
> > >
> > > Also, if the over-committing process is not doing
> > > any DML on the tables of
> > > the B.O. query, is it still a possible suspect of
> > > causing the ORA-1555
> > > because of the potential of overwriting another
> > > process' RBS?
> > >
> > > Damn.  I was hoping to be at 9i before I had to
> > deal
> > > with RBSs...  :)
> > >
> > >
> > > Rich
> > >
> > > Rich JesseSystem/Database
> > > Administrator
> > > [EMAIL PROTECTED]   Quad/Tech
> > > International, Sussex, WI USA
> > --
> > Please see the official ORACLE-L FAQ:
> > http://www.orafaq.net
> > --
> > Author: Jesse, Rich
> >   INET: [EMAIL PROTECTED]
> >
> > Fat City Network Services 

Re: Full table scan difference

[Anjo Kolk]

I wrote an LIO monitor, that should help to show what kind of LIO you are
doing. It could help explain the difference in time. Give it a try and let
me know what you see. Download it from oraperf.com

Anjo.

- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Friday, April 04, 2003 10:49 PM


> List - As I mentioned earlier, a new manager came from a site that
regularly
> rebuilt tables. I protested the value of this, but his response was "well,
> at least you could test it." So on a test instance I've been doing a CTAS
to
> create a reorganized copy of the table in the same tablespace (LMT with
> uniform extents, autoextend on). As expected, the number of blocks is very
> close.
>Then I've been doing a FTS (select count(*)) on both tables. Usually
the
> time is very close. However, on one large table, the original is much
faster
> -- 18-sec. vs. 27-sec. 21,349 blocks vs. 21179. Same plan. Does anyone
have
> a theory for this discrepancy?
>
> Dennis Williams
> DBA, 40%OCP, 100% DBA
> Lifetouch, Inc.
> [EMAIL PROTECTED]
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: DENNIS WILLIAMS
>   INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
>

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

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: Importing Schema getting compilation errors on some

[Scott Stefick]

Rick,

I agree with having to celebrate, since I was able to accomplish my task
before I left for the weekend.  That's always a good thing. 
Have a good weekend as well and good luck with the weather and the
fishing.

-Scott


At 03:43 PM 4/4/03 -0800, you wrote:
Scott-
 
Wow - what a way to finish a
week, I actually got something right. I may have to celebrate - looks
like it's time to pull out my fly rod and attack some trout - if it
doesn't snow again tonight. ( The challenges of living in MT
)
 
Here's a picture for the rest
of the world to see - I love it here.
http://www.nps.gov/glac/whatsnew/cam2_mcdonald.htm
 
Have an excellent weekend
folks
 
Rick Weiss 
Helena, MT

-Original Message-
From: Scott Stefick
[mailto:[EMAIL PROTECTED]] 
Sent: Friday, April 04, 2003 16:26
To: Multiple recipients of list ORACLE-L
Subject: RE: Importing Schema getting compilation errors on
some


To answer the previous questions:


1. No (That was the problem)
2. Yes
3. Yes


Thanks so much.  It's working now.  granted execute on
dbms_office to famis and that did it.  


Maybe it's the fact that I'm a newbie, but I assumed the grants would
come over with the import.  I think I understand what happened
now.  The grants on the objects that "famis" owns come
over with the import, not any of the grants for objects outside of that
schema.  I think about it now and I'm like "Damn that makes
total sense!"  I don't know why I assumed that famis would get
the grants from all of the other objects, that "doesn't make sense
to me now".


Rick, if you're ever in the Chicago area, I'll have to buy you a beer
(or whatever you prefer).


Also, thanks to everyone who helped me.
Rick Weiss
Darrell Landrum
Stephane Faroult
Gary Kirsh
Charlie Mengler


Thanks!
-Scott










At 02:53 PM 4/4/03 -0800, you wrote:
Do you have execute privs set for MAILER.DBMS_OFFICE?? 
Does it exist on the second database??
Is the MAILER schema on the database?


(I'm climbing my ladder of thoughts as I'd check them)


Just a thought 


Rick Weiss
Helena, MT USA


-Original Message-
Sent: Friday, April 04, 2003 15:24
To: Multiple recipients of list ORACLE-L






Here is the output of the show errors command (which I didn't know
about... 
Thanks).


==
SQL> show errors trigger famis_pr_status_insert
Errors for TRIGGER FAMIS_PR_STATUS_INSERT:


LINE/COL ERROR

-
106/32   PLS-00201: identifier 'MAILER.DBMS_OFFICE' must be
declared
106/32   PL/SQL: Statement ignored
180/32   PLS-00201: identifier 'MAILER.DBMS_OFFICE' must be
declared
180/32   PL/SQL: Statement ignored
SQL>




SQL> show errors trigger famis_req_insert
Errors for TRIGGER FAMIS_REQ_INSERT:


LINE/COL ERROR

-
187/21   PLS-00201: identifier 'MAILER.DBMS_OFFICE' must be
declared
187/21   PL/SQL: Statement ignored
264/25   PLS-00201: identifier 'MAILER.DBMS_OFFICE' must be
declared
264/25   PL/SQL: Statement ignored
339/25   PLS-00201: identifier 'MAILER.DBMS_OFFICE' must be
declared
339/25   PL/SQL: Statement ignored
416/24   PLS-00201: identifier 'MAILER.DBMS_OFFICE' must be
declared
416/24   PL/SQL: Statement ignored
SQL>
=


I'm guessing it's a problem with DBMS_OFFICE and not with the trigger
after 
seeing this.


-Scott






At 01:43 PM 4/4/03 -0800, you wrote:
>Scott, can you run this command in sqlplus and e-mail the results
out 
>to us...
>
>show errors trigger triggername
>
> >>> [EMAIL PROTECTED] 04/04/03 02:14PM
>>>
>Hello,
>
>Oracle 8.1.7.4
>HPUX 11.11
>App is Famis Maintenance Management System
>
>I have 3 instances TEST, TRAIN, PROD and I am trying to refresh
the 
>whole "famis" schema on TRAIN with the
"famis" schema on TEST.
>Every time I try to do the import, I get compilation errors for
4
>triggers
>(out of 75).  I tried to re-compile them in the TRAIN
instance after
>the
>import and get the same compilation errors.  I try to
re-compile them
>in
>TEST which is where they came from and they compile without a
problem.
>  I
>don't understand why this would happen when I exported the whole
schema
>
>from TEST and imported the whole .dmp file into TRAIN.
>
>Any Thoughts?
>
>-Scott
>
>
>**
>Scott Stefick
>Systems / Oracle Certified DBA
>Wm. Rainey Harper College
>847.925.6130
>**
>--
>Please see the official ORACLE-L FAQ:
http://www.orafaq.net
>--
>Author: Scott Stefick
>   INET: [EMAIL PROTECTED]
>
>Fat City Network Services    -- 858-538-5051
http://www.fatcity.com
>San Diego, California   
-- Mailing list and web hosting services
>-
>To REMOVE yourself from this mailing list, send an E-Mail
message
>to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru'

RE: RMAN Catalog: 1 vs. many - Opinions

[Gaja Krishna Vaidyanatha]

Paul et al.,

I agree with Paul's setup of a dedicated RMAN catalog
server. It is not a bad idea to have the datafiles for
this database on mirrored volumes. I also think a good
practice related to "saving the catalog" against tape
media errors is to do a full export on the RMAN
Catalog DB and stash the export dumpfile away on a
mirrored volume away from the datafiles. 

If this DB has nothing but the RMAN catalog, it should
not be very large (let me know if this is otherwise)
and thus the full export will be a good method to have
a "logical backup". One more thing to save the skin on
your back, at a time of need. When Murphy is around I
think the word "mercy" vanishes from the English
dictionary.


Cheers,

Gaja

--- [EMAIL PROTECTED] wrote:
> I have setup a server for just rman catalog backed
> up to tape with clones
> offsite for DR.  It makes it easier as scripts are
> parameterized to keep
> aware of backup statuses = etc.  Having this on a
> centralized management
> server and can't believe this isn't just common
> practice.  Just make sure no
> single point of failure.
> 
> Oracle OCP DBA
> 
> 
> -Original Message-
> Sent: Friday, April 04, 2003 3:39 PM
> To: Multiple recipients of list ORACLE-L
> 
> 
> Deepak
>You may also wish to consider not using
> catalog(s) -- control file
> backups. This might make it easier to add and remove
> databases in your
> environment. I'm presuming most databases are on
> their own server.
>If you want to be able to run a single query that
> will report any backup
> problems across all your databases, you will use a
> single catalog.
> 
> Dennis Williams
> DBA, 40%OCP, 100% DBA
> Lifetouch, Inc.
> [EMAIL PROTECTED] 
> 
> 
> -Original Message-
> Sent: Friday, April 04, 2003 2:09 PM
> To: Multiple recipients of list ORACLE-L
> 
> 
> 
> We are in the process of moving from traditional hot
> backups to one using RMAN. Total databases are
> around
> 200+. Should we be using 1 or few RMAN catalogs, or
> 1
> catalog per database? 
> 
> Thanks,
> Deepak
> 
> __
> Do you Yahoo!?
> Yahoo! Tax Center - File online, calculators, forms,
> and more
> http://tax.yahoo.com
> -- 
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.net
> -- 
> Author: Deepak 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.net
> -- 
> Author: DENNIS WILLIAMS
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- 858-538-5051
> http://www.fatcity.com
> San Diego, California-- Mailing list and web
> hosting services
>
-
> To REMOVE yourself from this mailing list, send an
> E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of
> 'ListGuru') and in
> the message BODY, include a line containing: UNSUB
> ORACLE-L
> (or the name of mailing list you want to be removed
> from).  You may
> also send the HELP command for other information
> (like subscribing).
> 


=


__
Do you Yahoo!?
Yahoo! Tax Center - File online, calculators, forms, and more
http://tax.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Gaja Krishna Vaidyanatha
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: Rollback and what table's DML caused an ORA-1555?

[Gaja Krishna Vaidyanatha]

Dear Jonathan,

How are you my friend? Hope all is well with you and
your family in the UK. When we spoke to James Morle
last evening, he drew our attention to a B-52 flying
over his home. Interesting times we live in!

Yes, you are right in your observation, that "in
general" one does not have to do it. I remember
helping out a customer on a Saturday afternoon with
this problem a year or so ago, hence wanted to share
the experience. My memory fails me in retrieving the
exact version number where the problem was
encountered. 

At any rate, it was one of those "delayed block clean
out" gotchas. In the specific case, the customer had
nobody on the system, after a shutdown, single user,
single session and was pulling his hair out with a
ORA-1555. Turns out the previous night, the objects in
the original READ-ONLY tablespace, was loaded with new
data, and the status was changed back to READ-ONLY. 

Due to whatever reason (I could not gather whether
there (were/were not) any long-running queries), the
clean out did not occur on some of the objects, and
this caused the ORA-1555, as his current query was
trying to build a read-consistent image from the
rollback segment and could not find the before
image(obviously). We got around the problem by doing
what I had suggested in my posting. You are right in
that the relevant indexes should also be touched and
cleaned by some query.

Cheers,

Gaja

--- Jonathan Lewis <[EMAIL PROTECTED]>
wrote:
> 
> In general, you don't need to do this in 
> recent versions of Oracle.  Oracle knows
> that all the data in the tablespace MUST
> have been committed before the tablespace
> was switched to read-only (you can only 
> switch a tablspace to readonly when there
> are no active transactions that started before
> the call to do the switch), and can use 
> the 'read-only' SCN as the upper bound
> commit for the data in the tablespace.
> 
> The only problem would come if you had a 
> long-running query that started before a 
> tablespace was made readonly - and Oracle 
> needed to know whether some data had 
> changed before or after the query started -
> AND you had managed to overwrite the 
> undo that contained the relevant older
> version of the transaction table.
> 
> If you do need to do this, don't forget
> that you also have to worry about indexes -
> count(*) could be satisfied from an index, 
> and the 1555 problem could be down to 
> an index block anyway - so the "SQL to
> write SQL" has to be a little clever.
> 
> 
> Regards
> 
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk
> 
>   The educated person is not the person 
>   who can answer the questions, but the 
>   person who can question the answers -- T. Schick
> Jr
> 
> 
> One-day tutorials:
> http://www.jlcomp.demon.co.uk/tutorial.html
> 
> UK___April 8th
> UK___April 22nd
> Denmark__May 21-23rd
> USA_(FL)_May 2nd
> 
> Three-day seminar:
> see http://www.jlcomp.demon.co.uk/seminar.html
> UK_(Manchester)_May
> Estonia___June (provisional)
> USA_(CA, TX)_August
> 
> The Co-operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
> 
> 
> - Original Message - 
> To: "Multiple recipients of list ORACLE-L"
> <[EMAIL PROTECTED]>
> Sent: 04 April 2003 19:53
> 
> 
> > 
> > 1) Set those tablespaces back to READ-WRITE mode
> > 2) Write a sql-generating-sql script that performs
> a
> > select count(*) on all the objects in the
> tablespace
> > 3) Set the tablespaces back to READ-ONLY. 
> > 
> 
> 
> -- 
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.net
> -- 
> Author: Jonathan Lewis
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- 858-538-5051
> http://www.fatcity.com
> San Diego, California-- Mailing list and web
> hosting services
>
-
> To REMOVE yourself from this mailing list, send an
> E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of
> 'ListGuru') and in
> the message BODY, include a line containing: UNSUB
> ORACLE-L
> (or the name of mailing list you want to be removed
> from).  You may
> also send the HELP command for other information
> (like subscribing).
> 


=


__
Do you Yahoo!?
Yahoo! Tax Center - File online, calculators, forms, and more
http://tax.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Gaja Krishna Vaidyanatha
  INET: [EMAIL PROTECTED]

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

Re: How to Determine Oracle Session ID given UNIX PID?

[Gaja Krishna Vaidyanatha]

Sam,

Try this out:

undef os_pid

select S.Username "User", P.Spid "OS ProcessID", S.Sid
"Session ID", S.Serial# "Serial#"
   from V$SESSION S, V$PROCESS P
 where S.PADDR = P.ADDR
   and P.SPID = &os_pid
/

Cheers,

Gaja

--- Sam Bootsma <[EMAIL PROTECTED]> wrote:
> Oracle 7.3.4 running on AIX 4.3.3
> 
>  
> 
> The ps command on our AIX box shows a UNIX process
> taking 50% of the
> CPU.  The PID is 89510.  When I try to find the
> corresponding session on
> the Oracle database it returns no rows.  Is there a
> way I can map the
> given PID on UNIX to a session id on Oracle?  See
> below for the query I
> ran.
> 
>  
> 
> SQL> l
> 
>   1  select sid from v$session
> 
>   2* where process like '%89510%'
> 
> SQL> /
> 
>  
> 
> no rows selected
> 
>  
> 
> Thanks for your information!
> 
>  
> 
>  
> 
>  
> 
> Sam
> 
> [EMAIL PROTECTED]
> 
>  
> 
> 


=


__
Do you Yahoo!?
Yahoo! Tax Center - File online, calculators, forms, and more
http://tax.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Gaja Krishna Vaidyanatha
  INET: [EMAIL PROTECTED]

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

Re: How to Determine Oracle Session ID given UNIX PID?

[Ray Stell]

I like this version posted here, not my doing.
Can't vouch for v7 validity, however:

 set lines 600
 set linesize 500
 set verif off
 col username for a15
 col osuser for a15
 col terminal for a8
 col program for a32
 col machine for a15
 col type for a4 trunc
 col logon_time hea 'LOGON-START-TIME' for a20
 col module for a10
 col sid for 999
 col serial# for 99
select a.username, a.osuser,
 --a.terminal,
 a.machine, a.program,
type, to_char(a.logon_time,'DD-MON- HH24:MI:SS') logon_time,
substr(a.module,1,10)
 module, a.sid, a.serial#
   from v$session a, sys.audit_actions b
  where -- a.status = 'ACTIVE' and
 b.action = a.command
 order by a.username
col username form a12
col osuser   form a12
col machine  form a15
col sid  form 
select vs.username,
   vs.sid,
   vs.serial#,
   vs.osuser,
   vs.machine,
   vs.process,
   vp.spid
from v$session vs, v$process vp
where vs.paddr = vp.addr;



On Fri, Apr 04, 2003 at 03:19:24PM -0800, Sam Bootsma wrote:
> Oracle 7.3.4 running on AIX 4.3.3
> 
>  
> 
> The ps command on our AIX box shows a UNIX process taking 50% of the
> CPU.  The PID is 89510.  When I try to find the corresponding session on
> the Oracle database it returns no rows.  Is there a way I can map the
> given PID on UNIX to a session id on Oracle?  See below for the query I
> ran.
> 
>  
> 
> SQL> l
> 
>   1  select sid from v$session
> 
>   2* where process like '%89510%'
> 
> SQL> /
> 
>  
> 
> no rows selected
> 
>  
> 
> Thanks for your information!
> 
>  
> 
>  
> 
>  
> 
> Sam
> 
> [EMAIL PROTECTED]
> 
>  
> 

-- 
===
Ray Stell   [EMAIL PROTECTED] (540) 231-4109 KE4TJC28^D
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
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: How to Determine Oracle Session ID given UNIX PID?

[Jacques Kilchoer]

Title: RE: How to Determine Oracle Session ID given UNIX PID?





http://www.orafaq.com/faqscrpt.htm


entered "unix session oracle" and clicked "Search"


found this:


http://www.orafaq.net/archive/oracle-l/2002/03/06/223435.htm


<<... Anyway from the subject line I
assume (OH, that word again) that you have a Unix process id that you want to
trace back to the session id.  To do so,
    select sid from v$session, v$process
  where paddr = addr
  and spid = 'Unix Process ID';
Dick Goulet>>


-Original Message-
From: Sam Bootsma [mailto:[EMAIL PROTECTED]]


Oracle 7.3.4 running on AIX 4.3.3


The ps command on our AIX box shows a UNIX process taking 50% of the CPU.  The PID is 89510.  When I try to find the corresponding session on the Oracle database it returns no rows.  Is there a way I can map the given PID on UNIX to a session id on Oracle?  See below for the query I ran.

SQL> l
  1  select sid from v$session
  2* where process like '%89510%'
SQL> /


no rows selected

RE: Importing Schema getting compilation errors on some

[Weiss, Rick]

Title: Message



Scott-
 
Wow - 
what a way to finish a week, I actually got something right. I may have to 
celebrate - looks like it's time to pull out my fly rod and attack some trout - 
if it doesn't snow again tonight. ( The challenges of living in MT 
)
 
Here's 
a picture for the rest of the world to see - I love it here.
http://www.nps.gov/glac/whatsnew/cam2_mcdonald.htm
 
Have 
an excellent weekend folks
 
Rick 
Weiss 
Helena, MT

  
  -Original Message-From: Scott Stefick 
  [mailto:[EMAIL PROTECTED] Sent: Friday, April 04, 2003 
  16:26To: Multiple recipients of list ORACLE-LSubject: 
  RE: Importing Schema getting compilation errors on someTo 
  answer the previous questions:1. No (That was the 
  problem)2. Yes3. YesThanks so much.  It's working 
  now.  granted execute on dbms_office to famis and that did it.  
  Maybe it's the fact that I'm a newbie, but I assumed the grants would 
  come over with the import.  I think I understand what happened now.  
  The grants on the objects that "famis" owns come over with the import, not any 
  of the grants for objects outside of that schema.  I think about it now 
  and I'm like "Damn that makes total sense!"  I don't know why I assumed 
  that famis would get the grants from all of the other objects, that "doesn't 
  make sense to me now".Rick, if you're ever in the Chicago area, I'll 
  have to buy you a beer (or whatever you prefer).Also, thanks to 
  everyone who helped me.Rick WeissDarrell LandrumStephane 
  FaroultGary KirshCharlie 
  MenglerThanks!-ScottAt 02:53 PM 4/4/03 
  -0800, you wrote:
  Do you have execute privs set for 
MAILER.DBMS_OFFICE?? Does it exist on the second database??Is the 
MAILER schema on the database?(I'm climbing my ladder of thoughts as 
I'd check them)Just a thought Rick WeissHelena, MT 
USA-Original Message-Sent: Friday, April 04, 2003 
15:24To: Multiple recipients of list ORACLE-LHere is the 
output of the show errors command (which I didn't know about... 
Thanks).==SQL> 
show errors trigger famis_pr_status_insertErrors for TRIGGER 
FAMIS_PR_STATUS_INSERT:LINE/COL ERROR 
-106/32   
PLS-00201: identifier 'MAILER.DBMS_OFFICE' must be 
declared106/32   PL/SQL: Statement 
ignored180/32   PLS-00201: identifier 'MAILER.DBMS_OFFICE' 
must be declared180/32   PL/SQL: Statement 
ignoredSQL>SQL> show errors trigger 
famis_req_insertErrors for TRIGGER FAMIS_REQ_INSERT:LINE/COL 
ERROR 
-187/21   
PLS-00201: identifier 'MAILER.DBMS_OFFICE' must be 
declared187/21   PL/SQL: Statement 
ignored264/25   PLS-00201: identifier 'MAILER.DBMS_OFFICE' 
must be declared264/25   PL/SQL: Statement 
ignored339/25   PLS-00201: identifier 'MAILER.DBMS_OFFICE' 
must be declared339/25   PL/SQL: Statement 
ignored416/24   PLS-00201: identifier 'MAILER.DBMS_OFFICE' 
must be declared416/24   PL/SQL: Statement 
ignoredSQL>=I'm 
guessing it's a problem with DBMS_OFFICE and not with the trigger after 
seeing this.-ScottAt 01:43 PM 4/4/03 -0800, you 
wrote:>Scott, can you run this command in sqlplus and e-mail the 
results out >to us...>>show errors trigger 
triggername>> >>> [EMAIL PROTECTED] 04/04/03 
02:14PM Hello,>>Oracle 8.1.7.4>HPUX 
11.11>App is Famis Maintenance Management System>>I 
have 3 instances TEST, TRAIN, PROD and I am trying to refresh the 
>whole "famis" schema on TRAIN with the "famis" schema on 
TEST.>Every time I try to do the import, I get compilation errors for 
4>triggers>(out of 75).  I tried to re-compile them in 
the TRAIN instance after>the>import and get the same 
compilation errors.  I try to re-compile them>in>TEST 
which is where they came from and they compile without a 
problem.>  I>don't understand why this would happen when 
I exported the whole schema>>from TEST and imported the whole 
.dmp file into TRAIN.>>Any 
Thoughts?>>-Scott>>>**>Scott 
Stefick>Systems / Oracle Certified DBA>Wm. Rainey Harper 
College>847.925.6130>**>-->Please 
see the official ORACLE-L FAQ: http://www.orafaq.net>-->Author: Scott 
Stefick>   INET: 
[EMAIL PROTECTED]>>Fat City Network 
Services    -- 858-538-5051 http://www.fatcity.com>San Diego, 
California    -- Mailing list and web 
hosting 
services>->To 
REMOVE yourself from this mailing list, send an E-Mail message>to: 
[EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the 
>message BODY, include a line containing: UNSUB

RE: Rollback and what table's DML caused an ORA-1555?

[Gaja Krishna Vaidyanatha]

Yes, your assumption is right. Create new rollback
segments without the OPTIMAL clause, bring them
online, take the old ones offline, blow the old ones
away, document the new ones in your init.ora, remove
the old ones from your init.ora. See whether this
solves your problem. My guess is that you should be
set. Keep us posted.

Cheers,

Gaja


--- "Jesse, Rich" <[EMAIL PROTECTED]> wrote:
> Excellent advice, Gaja!  Yes, we do have OPTIMAL
> set.  From the docs I don't
> see a way to remove the OPTIMAL setting once used. 
> I assume this means new
> RBSs?
> 
> 
> Rich
> 
> Rich JesseSystem/Database
> Administrator
> [EMAIL PROTECTED]   Quad/Tech
> International, Sussex, WI USA
> 
> 
> -Original Message-
> Sent: Friday, April 04, 2003 12:54 PM
> To: Multiple recipients of list ORACLE-L
> 
> 
> Rich,
> 
> The overcommiting is a definite suspect and is worth
> looking into. ALso, do you by chance have OPTIMAL
> set
> on your rollback segments? If so, I'd suggest you
> remove the OPTIMAL clause and try again. In my
> experience, I have had my share of hassles with
> OPTIMAL. Even when it was sized 'reasonably large'
> (way above INITIAL * MINEXTENTS) for the
> application. 
> 
> OPTIMAL does increase the probability of ORA-1555,
> as
> extents of your rollback segments that have the
> "before images" of your transactions, can get
> dropped,
> while your queries are left "high and dry". Just
> something to check. 
> 
> The downside of not setting OPTIMAL is more disk
> usage
> for your rollback segments. In the bigger scheme of
> things, it may be much cheaper to eat the disk cost
> (which is probably a few dollars) than to deal with
> queries failing with ORA-1555s. If you are so
> strapped
> for space, then you can "hand shrink" the rollback
> segments using the ALTER rollback segment xxx SHRINK
> command via a job, at a time when no queries are
> running.
> 
> If you don't have OPTIMAL set, and if you are using
> any READ-ONLY tablespaces that were put in READ-ONLY
> mode recently, then try the following:
> 
> 1) Set those tablespaces back to READ-WRITE mode
> 2) Write a sql-generating-sql script that performs a
> select count(*) on all the objects in the tablespace
> 3) Set the tablespaces back to READ-ONLY. 
> 
> The above exercise will force a block-cleanout (if
> required) on all the objects, which may not have
> occured before the tablespace was originally put in
> READ-ONLY mode.
> 
> Hope this helps,
> 
> Gaja
> 
> --- "Jesse, Rich" <[EMAIL PROTECTED]> wrote:
> > Hey all,
> > 
> > Fighting with a lot of ORA-1555s lately on 8.1.7.4
> > on HP/UX.  Most of them
> > are now coming from long-running Business Objects
> > (B.O.) queries against our
> > OLTP DB.  I think I need to recreate the RBS
> > tablespace (currently 1MB
> > extents in LMT), but until I can get time to do
> > that, I'd like to approach
> > this from the application side, where I think the
> > majority of the problem is
> > occurring.  I've been tracking TPM based on "user
> > commits" in V$SYSSTAT and
> > we spiked at 20K TPM just before the B.O. query in
> > question ORA-1555'd.
> > From STATSPACK reports, I think the most likely
> > cause for this is a COMMIT
> > for every DML in a batch job.  From what I've
> read,
> > including MetaLink
> > 40689.1, this over committing is one potential
> cause
> > of ORA-1555s.
> > 
> > In order to narrow down the problem, I've turned
> on
> > event 1555 in the
> > instance.  Is it possible to determine what
> > table(s)' DML is causing the
> > ORA-1555 based on the trace file?  I have the last
> > wait state, which happens
> > to be "db file sequential read", but I don't know
> if
> > there's any
> > correlation.  If there is, I should be able to
> > determine which table by the
> > file# and block# given in the trace.  Is this
> > correct?
> > 
> > Also, if the over-committing process is not doing
> > any DML on the tables of
> > the B.O. query, is it still a possible suspect of
> > causing the ORA-1555
> > because of the potential of overwriting another
> > process' RBS?
> > 
> > Damn.  I was hoping to be at 9i before I had to
> deal
> > with RBSs...  :)
> > 
> > 
> > Rich
> > 
> > Rich JesseSystem/Database
> > Administrator
> > [EMAIL PROTECTED]   Quad/Tech
> > International, Sussex, WI USA
> -- 
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.net
> -- 
> 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 comma

RE: Importing Schema getting compilation errors on some

[Scott Stefick]

To answer the previous questions:

1. No (That was the problem)
2. Yes
3. Yes

Thanks so much.  It's working now.  granted execute on
dbms_office to famis and that did it.  

Maybe it's the fact that I'm a newbie, but I assumed the grants would
come over with the import.  I think I understand what happened
now.  The grants on the objects that "famis" owns come
over with the import, not any of the grants for objects outside of that
schema.  I think about it now and I'm like "Damn that makes
total sense!"  I don't know why I assumed that famis would get
the grants from all of the other objects, that "doesn't make sense
to me now".

Rick, if you're ever in the Chicago area, I'll have to buy you a beer (or
whatever you prefer).

Also, thanks to everyone who helped me.
Rick Weiss
Darrell Landrum
Stephane Faroult
Gary Kirsh
Charlie Mengler

Thanks!
-Scott





At 02:53 PM 4/4/03 -0800, you wrote:
Do you have execute privs set for
MAILER.DBMS_OFFICE?? 
Does it exist on the second database??
Is the MAILER schema on the database?

(I'm climbing my ladder of thoughts as I'd check them)

Just a thought 

Rick Weiss
Helena, MT USA

-Original Message-
Sent: Friday, April 04, 2003 15:24
To: Multiple recipients of list ORACLE-L



Here is the output of the show errors command (which I didn't know
about... 
Thanks).

==
SQL> show errors trigger famis_pr_status_insert
Errors for TRIGGER FAMIS_PR_STATUS_INSERT:

LINE/COL ERROR

-
106/32   PLS-00201: identifier 'MAILER.DBMS_OFFICE' must be
declared
106/32   PL/SQL: Statement ignored
180/32   PLS-00201: identifier 'MAILER.DBMS_OFFICE' must be
declared
180/32   PL/SQL: Statement ignored
SQL>


SQL> show errors trigger famis_req_insert
Errors for TRIGGER FAMIS_REQ_INSERT:

LINE/COL ERROR

-
187/21   PLS-00201: identifier 'MAILER.DBMS_OFFICE' must be
declared
187/21   PL/SQL: Statement ignored
264/25   PLS-00201: identifier 'MAILER.DBMS_OFFICE' must be
declared
264/25   PL/SQL: Statement ignored
339/25   PLS-00201: identifier 'MAILER.DBMS_OFFICE' must be
declared
339/25   PL/SQL: Statement ignored
416/24   PLS-00201: identifier 'MAILER.DBMS_OFFICE' must be
declared
416/24   PL/SQL: Statement ignored
SQL>
=

I'm guessing it's a problem with DBMS_OFFICE and not with the trigger
after 
seeing this.

-Scott



At 01:43 PM 4/4/03 -0800, you wrote:
>Scott, can you run this command in sqlplus and e-mail the results out

>to us...
>
>show errors trigger triggername
>
> >>> [EMAIL PROTECTED] 04/04/03 02:14PM
>>>
>Hello,
>
>Oracle 8.1.7.4
>HPUX 11.11
>App is Famis Maintenance Management System
>
>I have 3 instances TEST, TRAIN, PROD and I am trying to refresh the

>whole "famis" schema on TRAIN with the "famis"
schema on TEST.
>Every time I try to do the import, I get compilation errors for
4
>triggers
>(out of 75).  I tried to re-compile them in the TRAIN instance
after
>the
>import and get the same compilation errors.  I try to re-compile
them
>in
>TEST which is where they came from and they compile without a
problem.
>  I
>don't understand why this would happen when I exported the whole
schema
>
>from TEST and imported the whole .dmp file into TRAIN.
>
>Any Thoughts?
>
>-Scott
>
>
>**
>Scott Stefick
>Systems / Oracle Certified DBA
>Wm. Rainey Harper College
>847.925.6130
>**
>--
>Please see the official ORACLE-L FAQ:
http://www.orafaq.net
>--
>Author: Scott Stefick
>   INET: [EMAIL PROTECTED]
>
>Fat City Network Services    -- 858-538-5051
http://www.fatcity.com
>San Diego, California    --
Mailing list and web hosting services
>-
>To REMOVE yourself from this mailing list, send an E-Mail
message
>to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the 
>message BODY, include a line containing: UNSUB ORACLE-L (or the name
of 
>mailing list you want to be removed from).  You may also send
the HELP 
>command for other information (like subscribing).
>
>--
>Please see the official ORACLE-L FAQ:
http://www.orafaq.net
>--
>Author: Darrell Landrum
>   INET: [EMAIL PROTECTED]
>
>Fat City Network Services    -- 858-538-5051
http://www.fatcity.com
>San Diego, California    --
Mailing list and web hosting services
>-
>To REMOVE yourself from this mailing list, send an E-Mail
message
>to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the 
>message BODY, include a line containing: UNSUB ORACLE-L (or the name
of 
>mailing list you want to be removed from).  You may also send
the HELP 
>command for other information (like subscribing).


**
Scot

RE: linux intel support matrix

[Chris Berry]

From: "Weaver, Walt" <[EMAIL PROTECTED]>
They make you work in the afternoon
--Walt Weaver
  Bozeman, Montana
You mean sometimes you aren't at work?

Chris Berry
[EMAIL PROTECTED]
Systems Administrator
JM Associates
"Without change, something sleeps inside us, and seldom awakens.  The 
sleeper must awaken." -- Duke Leto Atreides

_
MSN 8 helps eliminate e-mail viruses. Get 2 months FREE*.  
http://join.msn.com/?page=features/virus

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

How to Determine Oracle Session ID given UNIX PID?

[Sam Bootsma]

Oracle 7.3.4 running on AIX 4.3.3

 

The ps command on our AIX box shows a UNIX process taking
50% of the CPU.  The PID is 89510.  When I try to find the
corresponding session on the Oracle database it returns no rows.  Is there
a way I can map the given PID on UNIX to a session id on Oracle?  See
below for the query I ran.

 

SQL> l

  1  select sid from v$session

  2* where process like '%89510%'

SQL> /

 

no rows selected

 

Thanks for your information!

 

 

 

Sam

[EMAIL PROTECTED]

 

RE: [ Oracle EnterPrise Manager]

[Jacques Kilchoer]

Title: RE: [ Oracle EnterPrise Manager]





>-Original Message-
>From: Dory Edson [mailto:[EMAIL PROTECTED]]
>
>My question is about Oracle EnterPrise Manager
>
>I have installed Oracle 8.1.17i, and I can't start the Oracle
> EnterPrise Manager. (it can't find the service our server)
>I realized that the service OracleManagementService Isn't running, 
>and if I try to start this service, it doesn't work.
> Windows 2000 server shows the message: Error 1053.


Where do you see that error message? In a log file?


Have you read the 8.1.7 Oracle Enterprise Manager Configuration Guide?


Have you tried turning on logging for the Management server to get more details on the error? From The Fine Manual:


<
Logging and tracing of the Management Server is specified in the omsconfig.properties file, located in the $ORACLE_HOME\sysman\config directory. 

TRACING.ENABLED = |  Activates/deactivates tracing. Default is false. 


LOGGING.ENABLED = |  Activates/deactivates logging. Default is false. 


LOGGING.DIR =   Default is $ORACLE_HOME\sysman\log. 



Note: 
In order to set LOGGING.DIR to a directory of c:\temp, you must use "\\" and set LOGGING.DIR=c:\\temp 
If you do not, the \t in c:\temp is read as a tab character and the Management Server fails to start.  

 
TRACING.LEVEL =  
Set value to specify the detail of trace information to collect if tracing is enabled. 
Possible values for  are listed below: 
5 - user: displays only messages of a critical or error level. 
3 - admin: displays user trace level messages and warning messages. 
2 - dev: displays all messages from admin trace level as well as informational and debug message 


LOGGING.FILENAME =  
Defaults to oms.log.0, oms.log.1, oms.log.2, and so on. 


LOGGING.MAX_SIZE =  
Controls the total maximum size of the log files. The value you specify for the LOGGING.MAX_SIZE property is in MB. Its default value is 50 and will result in two log files of max size 25MB. Setting the parameter to 0 indicates that a single log file will be used with no limit. 

LOGGING.MAX_FILE_CNT =  
Defines the maximum number of files the log will span at any given time. The cumulative size of all the files would be less than or equal to LOGGING.MAXSIZE. 

Default value is 2. 
If LOGGING.MAX_SIZE=0 (unlimited log size), LOGGING.MAX_FILE_CNT will not make sense and hence ignored. 



You can always experiment with running the console "standalone", without an Oracle Management Service (though there are several operations that depend on the OMS). At least I know you can do it in 9.2, by entering this at the command line:

oemapp console oem.loginmode=standalone

RE: Importing Schema getting compilation errors on some

[Scott Stefick]

No, both instances were built the same way and they both worked before 
this.  I noticed that when I did a "show errors trigger trigger_name"
I saw:
=
106/32 PLS-00201: identifier 'MAILER.DBMS_OFFICE' must be declared
106/32 PL/SQL: Statement ignored
=
I'm checking why this would have happened.  So far I've checked the 
synonyms and the dba_objects view and they look ok.

-Scott



At 01:59 PM 4/4/03 -0800, you wrote:
Scott:

My first thought is to ask if there are any external references (other
schemas, SYS packages, etc.) in the triggers that may not exist in TRAIN
that do exist in TEST
Rick Weiss

-Original Message-
Sent: Friday, April 04, 2003 13:14
To: Multiple recipients of list ORACLE-L
Hello,

Oracle 8.1.7.4
HPUX 11.11
App is Famis Maintenance Management System
I have 3 instances TEST, TRAIN, PROD and I am trying to refresh the whole
"famis" schema on TRAIN with the "famis" schema on TEST.
Every time I try to do the import, I get compilation errors for 4 triggers
(out of 75).  I tried to re-compile them in the TRAIN instance after the
import and get the same compilation errors.  I try to re-compile them in
TEST which is where they came from and they compile without a problem.   I
don't understand why this would happen when I exported the whole schema
from TEST and imported the whole .dmp file into TRAIN.
Any Thoughts?

-Scott

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


**
Scott Stefick
Systems / Oracle Certified DBA
Wm. Rainey Harper College
847.925.6130
**
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Scott Stefick
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: Importing Schema getting compilation errors on some

[Weiss, Rick]

Do you have execute privs set for MAILER.DBMS_OFFICE?? 
Does it exist on the second database??
Is the MAILER schema on the database?

(I'm climbing my ladder of thoughts as I'd check them)

Just a thought 

Rick Weiss
Helena, MT USA

-Original Message-
Sent: Friday, April 04, 2003 15:24
To: Multiple recipients of list ORACLE-L



Here is the output of the show errors command (which I didn't know about... 
Thanks).

==
SQL> show errors trigger famis_pr_status_insert
Errors for TRIGGER FAMIS_PR_STATUS_INSERT:

LINE/COL ERROR
 -
106/32   PLS-00201: identifier 'MAILER.DBMS_OFFICE' must be declared
106/32   PL/SQL: Statement ignored
180/32   PLS-00201: identifier 'MAILER.DBMS_OFFICE' must be declared
180/32   PL/SQL: Statement ignored
SQL>


SQL> show errors trigger famis_req_insert
Errors for TRIGGER FAMIS_REQ_INSERT:

LINE/COL ERROR
 -
187/21   PLS-00201: identifier 'MAILER.DBMS_OFFICE' must be declared
187/21   PL/SQL: Statement ignored
264/25   PLS-00201: identifier 'MAILER.DBMS_OFFICE' must be declared
264/25   PL/SQL: Statement ignored
339/25   PLS-00201: identifier 'MAILER.DBMS_OFFICE' must be declared
339/25   PL/SQL: Statement ignored
416/24   PLS-00201: identifier 'MAILER.DBMS_OFFICE' must be declared
416/24   PL/SQL: Statement ignored
SQL>
=

I'm guessing it's a problem with DBMS_OFFICE and not with the trigger after 
seeing this.

-Scott



At 01:43 PM 4/4/03 -0800, you wrote:
>Scott, can you run this command in sqlplus and e-mail the results out 
>to us...
>
>show errors trigger triggername
>
> >>> [EMAIL PROTECTED] 04/04/03 02:14PM >>>
>Hello,
>
>Oracle 8.1.7.4
>HPUX 11.11
>App is Famis Maintenance Management System
>
>I have 3 instances TEST, TRAIN, PROD and I am trying to refresh the 
>whole "famis" schema on TRAIN with the "famis" schema on TEST.
>Every time I try to do the import, I get compilation errors for 4
>triggers
>(out of 75).  I tried to re-compile them in the TRAIN instance after
>the
>import and get the same compilation errors.  I try to re-compile them
>in
>TEST which is where they came from and they compile without a problem.
>  I
>don't understand why this would happen when I exported the whole schema
>
>from TEST and imported the whole .dmp file into TRAIN.
>
>Any Thoughts?
>
>-Scott
>
>
>**
>Scott Stefick
>Systems / Oracle Certified DBA
>Wm. Rainey Harper College
>847.925.6130
>**
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.net
>--
>Author: Scott Stefick
>   INET: [EMAIL PROTECTED]
>
>Fat City Network Services-- 858-538-5051 http://www.fatcity.com
>San Diego, California-- Mailing list and web hosting services
>-
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the 
>message BODY, include a line containing: UNSUB ORACLE-L (or the name of 
>mailing list you want to be removed from).  You may also send the HELP 
>command for other information (like subscribing).
>
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.net
>--
>Author: Darrell Landrum
>   INET: [EMAIL PROTECTED]
>
>Fat City Network Services-- 858-538-5051 http://www.fatcity.com
>San Diego, California-- Mailing list and web hosting services
>-
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the 
>message BODY, include a line containing: UNSUB ORACLE-L (or the name of 
>mailing list you want to be removed from).  You may also send the HELP 
>command for other information (like subscribing).


**
Scott Stefick
Systems / Oracle Certified DBA
Wm. Rainey Harper College
847.925.6130
**
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Scott Stefick
  INET: [EMAIL PROTECTED]

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

Fat City Network S

Re: Importing Schema getting compilation errors on some

[Darrell Landrum]

There's probably many more causes for this than I am aware, but most of
the occasions when I have seen an "identifier blah.blah must be
declared" was when I was logged in as the wrong user trying to compile
the procedure.


>>> [EMAIL PROTECTED] 04/04/03 04:24PM >>>

Here is the output of the show errors command (which I didn't know
about... 
Thanks).

==
SQL> show errors trigger famis_pr_status_insert
Errors for TRIGGER FAMIS_PR_STATUS_INSERT:

LINE/COL ERROR

-
106/32   PLS-00201: identifier 'MAILER.DBMS_OFFICE' must be declared
106/32   PL/SQL: Statement ignored
180/32   PLS-00201: identifier 'MAILER.DBMS_OFFICE' must be declared
180/32   PL/SQL: Statement ignored
SQL>


SQL> show errors trigger famis_req_insert
Errors for TRIGGER FAMIS_REQ_INSERT:

LINE/COL ERROR

-
187/21   PLS-00201: identifier 'MAILER.DBMS_OFFICE' must be declared
187/21   PL/SQL: Statement ignored
264/25   PLS-00201: identifier 'MAILER.DBMS_OFFICE' must be declared
264/25   PL/SQL: Statement ignored
339/25   PLS-00201: identifier 'MAILER.DBMS_OFFICE' must be declared
339/25   PL/SQL: Statement ignored
416/24   PLS-00201: identifier 'MAILER.DBMS_OFFICE' must be declared
416/24   PL/SQL: Statement ignored
SQL>
=

I'm guessing it's a problem with DBMS_OFFICE and not with the trigger
after 
seeing this.

-Scott



At 01:43 PM 4/4/03 -0800, you wrote:
>Scott, can you run this command in sqlplus and e-mail the results out
to
>us...
>
>show errors trigger triggername
>
> >>> [EMAIL PROTECTED] 04/04/03 02:14PM >>>
>Hello,
>
>Oracle 8.1.7.4
>HPUX 11.11
>App is Famis Maintenance Management System
>
>I have 3 instances TEST, TRAIN, PROD and I am trying to refresh the
>whole
>"famis" schema on TRAIN with the "famis" schema on TEST.
>Every time I try to do the import, I get compilation errors for 4
>triggers
>(out of 75).  I tried to re-compile them in the TRAIN instance after
>the
>import and get the same compilation errors.  I try to re-compile them
>in
>TEST which is where they came from and they compile without a
problem.
>  I
>don't understand why this would happen when I exported the whole
schema
>
>from TEST and imported the whole .dmp file into TRAIN.
>
>Any Thoughts?
>
>-Scott
>
>
>**
>Scott Stefick
>Systems / Oracle Certified DBA
>Wm. Rainey Harper College
>847.925.6130
>**
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.net 
>--
>Author: Scott Stefick
>   INET: [EMAIL PROTECTED] 
>
>Fat City Network Services-- 858-538-5051 http://www.fatcity.com 
>San Diego, California-- Mailing list and web hosting services
>-
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from).  You may
>also send the HELP command for other information (like subscribing).
>
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.net 
>--
>Author: Darrell Landrum
>   INET: [EMAIL PROTECTED] 
>
>Fat City Network Services-- 858-538-5051 http://www.fatcity.com 
>San Diego, California-- Mailing list and web hosting services
>-
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from).  You may
>also send the HELP command for other information (like subscribing).


**
Scott Stefick
Systems / Oracle Certified DBA
Wm. Rainey Harper College
847.925.6130
**
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net 
-- 
Author: Scott Stefick
  INET: [EMAIL PROTECTED] 

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

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

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

RE: RMAN: What blocks are backed up with a full backup?

[Freeman Robert - IL]

 
>> ...getting old...

Noo


RF

-Original Message-
To: Multiple recipients of list ORACLE-L
Sent: 4/4/2003 3:48 PM

[EMAIL PROTECTED] wrote:
> Teach me? Hell, there is more stuff in the world I don't know than I
know
> of! I've been wrong before and I'll be wrong again and  and I hate
this,
> I FORGET STUFF. Easy stuff, simple stuff stuff I kick myself all
over
> creation because I forgot it.

yup, simple stuff you do without thinking about, then you can't remember

shit when someone asks you about it.;-)  i think it's called getting
old.;-)

-- 
--
Bill "Shrek" Thater  ORACLE DBA
 [EMAIL PROTECTED]

Om mani padme hum.


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

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

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

DBA ignorance (was Win2k memory weirdness)

[Paul Baumgartel]

Please disregard my previous post--the problem was that the
sga_max_size was set very low.  

__
Do you Yahoo!?
Yahoo! Tax Center - File online, calculators, forms, and more
http://tax.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Paul Baumgartel
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: Sub-query in order by clause

[Jacques Kilchoer]

Title: RE: Sub-query in order by clause





Yes, I see that. (hangs head in shame)


> -Original Message-
> From: Nicoll, Iain [mailto:[EMAIL PROTECTED]]
>  
> As I believe has been pointed out already it may possibly be 
> more efficient
> if dept is very small and emp is very large (especially if 
> there are filters
> and a join would be done before a filter was applied).  
> Alternatively it may
> be that Oracle believe it is more intuitive to people with no 
> preconceived
> ideas.
>  
> -Original Message-
> Sent: 04 April 2003 20:24
> 
> Why would you do that instead of 
> SELECT emp.deptno, empno, ename 
> FROM emp a, dept b 
> WHERE dept.deptno = emp.deptno 
> order by dept.deptname ; 
> 
> > -Original Message- 
> > From: Nicoll, Iain [ mailto:[EMAIL PROTECTED]
>  ] 
> > 
> > Don't really know but couldn't it be useful if you had 
> >  
> > ORDER BY ( SELECT deptname FROM dept 
> > WHERE dept.deptno = emp.deptno) 

RE: Importing Schema getting compilation errors on some triggers

[Kirsh, Gary]

Maybe the triggers reference objects in a different schema?
Gary

-Original Message-
Sent: Friday, April 04, 2003 3:14 PM
To: Multiple recipients of list ORACLE-L


Hello,

Oracle 8.1.7.4
HPUX 11.11
App is Famis Maintenance Management System

I have 3 instances TEST, TRAIN, PROD and I am trying to refresh the whole 
"famis" schema on TRAIN with the "famis" schema on TEST.
Every time I try to do the import, I get compilation errors for 4 triggers 
(out of 75).  I tried to re-compile them in the TRAIN instance after the 
import and get the same compilation errors.  I try to re-compile them in 
TEST which is where they came from and they compile without a problem.   I 
don't understand why this would happen when I exported the whole schema 
from TEST and imported the whole .dmp file into TRAIN.

Any Thoughts?

-Scott


**
Scott Stefick
Systems / Oracle Certified DBA
Wm. Rainey Harper College
847.925.6130
**
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Scott Stefick
  INET: [EMAIL PROTECTED]

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

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: Importing Schema getting compilation errors on some

[Scott Stefick]

Here is the output of the show errors command (which I didn't know about... 
Thanks).

==
SQL> show errors trigger famis_pr_status_insert
Errors for TRIGGER FAMIS_PR_STATUS_INSERT:
LINE/COL ERROR
 -
106/32   PLS-00201: identifier 'MAILER.DBMS_OFFICE' must be declared
106/32   PL/SQL: Statement ignored
180/32   PLS-00201: identifier 'MAILER.DBMS_OFFICE' must be declared
180/32   PL/SQL: Statement ignored
SQL>
SQL> show errors trigger famis_req_insert
Errors for TRIGGER FAMIS_REQ_INSERT:
LINE/COL ERROR
 -
187/21   PLS-00201: identifier 'MAILER.DBMS_OFFICE' must be declared
187/21   PL/SQL: Statement ignored
264/25   PLS-00201: identifier 'MAILER.DBMS_OFFICE' must be declared
264/25   PL/SQL: Statement ignored
339/25   PLS-00201: identifier 'MAILER.DBMS_OFFICE' must be declared
339/25   PL/SQL: Statement ignored
416/24   PLS-00201: identifier 'MAILER.DBMS_OFFICE' must be declared
416/24   PL/SQL: Statement ignored
SQL>
=
I'm guessing it's a problem with DBMS_OFFICE and not with the trigger after 
seeing this.

-Scott



At 01:43 PM 4/4/03 -0800, you wrote:
Scott, can you run this command in sqlplus and e-mail the results out to
us...
show errors trigger triggername

>>> [EMAIL PROTECTED] 04/04/03 02:14PM >>>
Hello,
Oracle 8.1.7.4
HPUX 11.11
App is Famis Maintenance Management System
I have 3 instances TEST, TRAIN, PROD and I am trying to refresh the
whole
"famis" schema on TRAIN with the "famis" schema on TEST.
Every time I try to do the import, I get compilation errors for 4
triggers
(out of 75).  I tried to re-compile them in the TRAIN instance after
the
import and get the same compilation errors.  I try to re-compile them
in
TEST which is where they came from and they compile without a problem.
 I
don't understand why this would happen when I exported the whole schema
from TEST and imported the whole .dmp file into TRAIN.

Any Thoughts?

-Scott

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


**
Scott Stefick
Systems / Oracle Certified DBA
Wm. Rainey Harper College
847.925.6130
**
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Scott Stefick
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: Do triggers cause a context switch between SQL & PL/SQL

[Jonathan Lewis]

But that's begging the question.
What actually occurs that allows you to
say that there is a "context switch" ?

I know that one of the advertised performance
enhancements in v9 was the 'more efficient
context switching between SQL and PL/SQL'
(or some such thing) - but a name is not a thing.

Does the Oracle process run as a virtual
machine, and build two completely independent
top-level heaps/stacks and maintain a set of
virtual registers - one for an SQL environment
and one for a PL/SQL environment ?  Do you
get multiple "contexts" if SQL calls a pl/sql function
that opens a cursor that calls a pl/sql function ?

Can anyone put a description under the name ?


Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

  The educated person is not the person
  who can answer the questions, but the
  person who can question the answers -- T. Schick Jr


One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html

UK___April 8th
UK___April 22nd
Denmark__May 21-23rd
USA_(FL)_May 2nd

Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
UK_(Manchester)_May
Estonia___June (provisional)
USA_(CA, TX)_August

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: 04 April 2003 21:58


> Jonathan
>
> it is switching from SQL context to PL/SQL context and vice versa.
>
> Raj
> 

> 
> Rajendra dot Jamadagni at nospamespn dot com
> All Views expressed in this email are strictly personal.
> QOTD: Any clod can have facts, having an opinion is an art !
>

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

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

RE: Importing Schema getting compilation errors on some triggers

[Weiss, Rick]

Scott:

My first thought is to ask if there are any external references (other
schemas, SYS packages, etc.) in the triggers that may not exist in TRAIN
that do exist in TEST

Rick Weiss

-Original Message-
Sent: Friday, April 04, 2003 13:14
To: Multiple recipients of list ORACLE-L


Hello,

Oracle 8.1.7.4
HPUX 11.11
App is Famis Maintenance Management System

I have 3 instances TEST, TRAIN, PROD and I am trying to refresh the whole 
"famis" schema on TRAIN with the "famis" schema on TEST.
Every time I try to do the import, I get compilation errors for 4 triggers 
(out of 75).  I tried to re-compile them in the TRAIN instance after the 
import and get the same compilation errors.  I try to re-compile them in 
TEST which is where they came from and they compile without a problem.   I 
don't understand why this would happen when I exported the whole schema 
from TEST and imported the whole .dmp file into TRAIN.

Any Thoughts?

-Scott


**
Scott Stefick
Systems / Oracle Certified DBA
Wm. Rainey Harper College
847.925.6130
**
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Scott Stefick
  INET: [EMAIL PROTECTED]

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

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: Importing Schema getting compilation errors on some triggers

[Stephane Faroult]

Scott Stefick wrote:
> 
> Hello,
> 
> Oracle 8.1.7.4
> HPUX 11.11
> App is Famis Maintenance Management System
> 
> I have 3 instances TEST, TRAIN, PROD and I am trying to refresh the whole
> "famis" schema on TRAIN with the "famis" schema on TEST.
> Every time I try to do the import, I get compilation errors for 4 triggers
> (out of 75).  I tried to re-compile them in the TRAIN instance after the
> import and get the same compilation errors.  I try to re-compile them in
> TEST which is where they came from and they compile without a problem.   I
> don't understand why this would happen when I exported the whole schema
> from TEST and imported the whole .dmp file into TRAIN.
> 
> Any Thoughts?
> 
> -Scott
> 

Some ideas :
 - public synonyms (and indirectly privileges)
 - database links
-- 
Regards,

Stephane Faroult
Oriole Software
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephane Faroult
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: Sub-query in order by clause

[Jacques Kilchoer]

Title: RE: Sub-query in order by clause





I see your point. The answer to my question "why would you do that instead of ... " is, of course, "because the other way might be faster."

Read Jonathan Lewis' earlier post, he had (as always) some good insight.


> -Original Message-
> From: Lyndon Tiu [mailto:[EMAIL PROTECTED]]
> 
> Which way is faster? The subselect(nested select) or the join?
> 
> Quoting Jacques Kilchoer <[EMAIL PROTECTED]>:
> 
> > Why would you do that instead of
> > SELECT emp.deptno, empno, ename
> > FROM emp a, dept b
> > WHERE dept.deptno = emp.deptno
> > order by dept.deptname ;
> > 
> > > -Original Message-
> > > From: Nicoll, Iain [mailto:[EMAIL PROTECTED]]
> > > 
> > > Don't really know but couldn't it be useful if you had 
> > >  
> > > ORDER BY ( SELECT deptname FROM dept
> > > WHERE dept.deptno = emp.deptno)

RE: linux intel support matrix

[Weaver, Walt]

They make you work in the afternoon

--Walt Weaver
  Bozeman, Montana

> -Original Message-
> From: bill thater [mailto:[EMAIL PROTECTED]
> Sent: Friday, April 04, 2003 2:29 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: linux intel support matrix
> 
> 
> [EMAIL PROTECTED] wrote:
> > Bill,
> >  Welcome back!!! I haven't read you in a long time. Hope 
> all is well.
> > Ron
> 
> just got a 3 month contract after being out of work since the end of 
> june.  guess that's the end of my afternoon naps, and my 
> afternoon beer, 
> for a while.;-)
> 
> -- 
> --
> Bill "Shrek" Thater  ORACLE DBA
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Weaver, Walt
  INET: [EMAIL PROTECTED]

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

[Arun Annamalai]

Hey
1. you dont need to do anything with redologs either backup or restore.
2. you can use the current control file(in which case you dont have to do
anything) or use old control file.

Incase, If you use old control file with combo of current relo logs then you
need to use "recover..blah..blah..using backup controlfile".
If you have old redologs, old controlfiles and old datafiles then just
"recover..blah..blah..until time..blah" will work.

Email if you need more help.
-Arun
Sr. Oracle dba.
- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Wednesday, March 26, 2003 4:33 PM


> Hi,
>
> I have to recover my database back to 23-MAR-03.There was some changes to
the data yesterday and cannot be reverted.
> We want to recover the data as of 23-MAR-03.Database is running on archive
log mode and I have the hot backup datafiles
> of Feb 3 and all the archive logs since then till today.I know how to
recover but to make sure that I don't miss anything i am
> posting this here.
>
> 1.Restore the data tablespace datafiles from the Feb 3rd backup.
> 2.Startup the database in MOUNT mode.
> 3.recover database until time "2003-03-23-12:00:00"
> 4.All the archive logs will get applied
> 5.Open the database with reset logs.I should go back by 3 days wrt to
data.
> Am I missing anything.
> My questions:
> Should I restore the control files(of FEb 3) before starting the
recovery.?
> Should i restore  redo log files?.I donot want the recent changes to be
applied to my database after restoring till 23-MAR-03.
> Thanks
>
>
>
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Basavaraja, Ravindra
>   INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
>
>
>

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

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: Full table scan difference

[Cary Millsap]

1. Dial 1-0-0-4-6.
2. Look at the latching and pinning statistics promoted at asktom, ixora,
and jlcomp.


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

Upcoming events:
- Hotsos Clinic 101 in Chicago, London, Reykjavik, Ottawa, Denver
- Visit www.hotsos.com for schedule details...
- IOUG-A Live 2003, Orlando, 10am Monday 28 April: "Oracle Operational
Timing Data"

-Original Message-
WILLIAMS
Sent: Friday, April 04, 2003 2:49 PM
To: Multiple recipients of list ORACLE-L

List - As I mentioned earlier, a new manager came from a site that regularly
rebuilt tables. I protested the value of this, but his response was "well,
at least you could test it." So on a test instance I've been doing a CTAS to
create a reorganized copy of the table in the same tablespace (LMT with
uniform extents, autoextend on). As expected, the number of blocks is very
close. 
   Then I've been doing a FTS (select count(*)) on both tables. Usually the
time is very close. However, on one large table, the original is much faster
-- 18-sec. vs. 27-sec. 21,349 blocks vs. 21179. Same plan. Does anyone have
a theory for this discrepancy?

Dennis Williams
DBA, 40%OCP, 100% DBA
Lifetouch, Inc.
[EMAIL PROTECTED] 
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: DENNIS WILLIAMS
  INET: [EMAIL PROTECTED]

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

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: 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).

RE: Sub-query in order by clause

[Lyndon Tiu]

Which way is faster? The subselect(nested select) or the join?

Thanks.

-- 
Lyndon Tiu


Quoting Jacques Kilchoer <[EMAIL PROTECTED]>:

> Why would you do that instead of
> SELECT emp.deptno, empno, ename
> FROM emp a, dept b
> WHERE dept.deptno = emp.deptno
> order by dept.deptname ;
> 
> > -Original Message-
> > From: Nicoll, Iain [mailto:[EMAIL PROTECTED]
> > 
> > Don't really know but couldn't it be useful if you had 
> >  
> > ORDER BY ( SELECT deptname FROM dept
> > WHERE dept.deptno = emp.deptno)
> >  
> >  
> > -Original Message-
> > From: Ashish [mailto:[EMAIL PROTECTED]
> > 
> > In Oracle 9.2.0.1, you can now order by using a single-row
> subquery
> > Here is an example:
> >  
> > SELECT emp.deptno, empno, ename
> > FROM emp
> > ORDER BY ( SELECT deptno FROM dept
> > WHERE dept.deptno = emp.deptno );
> >  
> > The question I have is what is the usefulness of this? Under
> which
> > circumstances
> > this can be used?
> 


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

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

Memory strangeness on Win2k

[Paul Baumgartel]

So I have DB (9.2.0.1) running on Win2K, with db_cache_size of 32M. 
Windows Task Manager shows 600+ MB of free physical memory.  ALTER
SYSTEM SET DB_CACHE_SIZE=50248000 SCOPE=BOTH fails with ORA-00384:
Insufficient memory to grow cache.  

I'm going to try ...SCOPE=SPFILE, then bouncing the instance, but any
other ideas would be appreciated.  

I really prefer running Oracle on Unix.

TIA,

Paul Baumgartel


__
Do you Yahoo!?
Yahoo! Tax Center - File online, calculators, forms, and more
http://tax.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Paul Baumgartel
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: What blocks are backed up with a full backup?

[bill thater]

[EMAIL PROTECTED] wrote:
Teach me? Hell, there is more stuff in the world I don't know than I know
of! I've been wrong before and I'll be wrong again and  and I hate this,
I FORGET STUFF. Easy stuff, simple stuff stuff I kick myself all over
creation because I forgot it.
yup, simple stuff you do without thinking about, then you can't remember 
shit when someone asks you about it.;-)  i think it's called getting old.;-)

--
--
Bill "Shrek" Thater  ORACLE DBA
[EMAIL PROTECTED]

Om mani padme hum.

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: bill thater
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: Sub-query in order by clause

[Nicoll, Iain]

Jacques,
 
As I believe has been pointed out already it may possibly be more efficient
if dept is very small and emp is very large (especially if there are filters
and a join would be done before a filter was applied).  Alternatively it may
be that Oracle believe it is more intuitive to people with no preconceived
ideas.
 
Iain Nicoll
-Original Message-
Sent: 04 April 2003 20:24
To: Multiple recipients of list ORACLE-L



Why would you do that instead of 
SELECT emp.deptno, empno, ename 
FROM emp a, dept b 
WHERE dept.deptno = emp.deptno 
order by dept.deptname ; 

> -Original Message- 
> From: Nicoll, Iain [ mailto:[EMAIL PROTECTED]
 ] 
> 
> Don't really know but couldn't it be useful if you had 
>  
> ORDER BY ( SELECT deptname FROM dept 
> WHERE dept.deptno = emp.deptno) 
>  
>  
> -Original Message- 
> From: Ashish [ mailto:[EMAIL PROTECTED]  ] 
> 
> In Oracle 9.2.0.1, you can now order by using a single-row subquery 
> Here is an example: 
>  
> SELECT emp.deptno, empno, ename 
> FROM emp 
> ORDER BY ( SELECT deptno FROM dept 
> WHERE dept.deptno = emp.deptno ); 
>  
> The question I have is what is the usefulness of this? Under which 
> circumstances 
> this can be used? 

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

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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 link and connection

[Stephane Paquette]

Hi all,

Oracle 8172 with dedicated connections.

I've not played that much with database links.

Users from application A will accessed data from another database B with a
dblink.
Real time access is needed so no snapshot, tables and stored proc are
accessed.

There are 300 users in application A so that means that they can open 300
connections in database B.

Is there a way to not used that many connections on database B ?
If not, I guessed I have 2 choices

1. Use sqlnet.expire_time to deconnect session being idle x minutes
2. Use a job that kills session with the dblink username after x minutes
being idle


Any other choices ?

TIA

Stephane Paquette
Administrateur de bases de données
Database Administrator
Standard Life
www.standardlife.ca
Tel. (514) 925-7187
[EMAIL PROTECTED]



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

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: Do triggers cause a context switch between SQL & PL/SQL

[Jared . Still]

Jonathan,

It's just being used in a different context.

Jared






"Jonathan Lewis" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
 04/04/2003 11:23 AM
 Please respond to ORACLE-L

 
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc: 
Subject:Re: Do triggers cause a context switch between SQL & PL/SQL



I have always been puzzled by the expression
"context switch" when talking about sql and pl/sql.

To me, a context switch is something that happens
at the O/S level when a process is suspended by the
scheduler and a different process is resumed.  If a
trigger fires on an insert/update/delete, this is obviously
not happening - the only process executing is your
Oracle server (dedicated or MTS) - so what activity is
being given the name "context switch" ?  Surely the
Oracle process is 'simply' calling a sub-routine somewhere
else in its code path (and building a stack and acquiring
sub-heaps and ...).


Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

  The educated person is not the person
  who can answer the questions, but the
  person who can question the answers -- T. Schick Jr


One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html

UK___April 8th
UK___April 22nd
Denmark__May 21-23rd
USA_(FL)_May 2nd

Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
UK_(Manchester)_May
Estonia___June (provisional)
USA_(CA, TX)_August

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: 01 April 2003 19:19


> Since you the action switches from SQL to PL/SQL to enforce the
trigger,
> it sounds like a context switch to me.
>
> FYI:  This can be improved somewhat by adding "and rownum < 2 " into
the
> WHERE clauses of these.  There will be noticable improvement when
there
> are many child rows.
>
> Jared
>
>
>


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

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




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

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: Rollback and what table's DML caused an ORA-1555?

[Jesse, Rich]

Excellent advice, Gaja!  Yes, we do have OPTIMAL set.  From the docs I don't
see a way to remove the OPTIMAL setting once used.  I assume this means new
RBSs?


Rich

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


-Original Message-
Sent: Friday, April 04, 2003 12:54 PM
To: Multiple recipients of list ORACLE-L


Rich,

The overcommiting is a definite suspect and is worth
looking into. ALso, do you by chance have OPTIMAL set
on your rollback segments? If so, I'd suggest you
remove the OPTIMAL clause and try again. In my
experience, I have had my share of hassles with
OPTIMAL. Even when it was sized 'reasonably large'
(way above INITIAL * MINEXTENTS) for the application. 

OPTIMAL does increase the probability of ORA-1555, as
extents of your rollback segments that have the
"before images" of your transactions, can get dropped,
while your queries are left "high and dry". Just
something to check. 

The downside of not setting OPTIMAL is more disk usage
for your rollback segments. In the bigger scheme of
things, it may be much cheaper to eat the disk cost
(which is probably a few dollars) than to deal with
queries failing with ORA-1555s. If you are so strapped
for space, then you can "hand shrink" the rollback
segments using the ALTER rollback segment xxx SHRINK
command via a job, at a time when no queries are
running.

If you don't have OPTIMAL set, and if you are using
any READ-ONLY tablespaces that were put in READ-ONLY
mode recently, then try the following:

1) Set those tablespaces back to READ-WRITE mode
2) Write a sql-generating-sql script that performs a
select count(*) on all the objects in the tablespace
3) Set the tablespaces back to READ-ONLY. 

The above exercise will force a block-cleanout (if
required) on all the objects, which may not have
occured before the tablespace was originally put in
READ-ONLY mode.

Hope this helps,

Gaja

--- "Jesse, Rich" <[EMAIL PROTECTED]> wrote:
> Hey all,
> 
> Fighting with a lot of ORA-1555s lately on 8.1.7.4
> on HP/UX.  Most of them
> are now coming from long-running Business Objects
> (B.O.) queries against our
> OLTP DB.  I think I need to recreate the RBS
> tablespace (currently 1MB
> extents in LMT), but until I can get time to do
> that, I'd like to approach
> this from the application side, where I think the
> majority of the problem is
> occurring.  I've been tracking TPM based on "user
> commits" in V$SYSSTAT and
> we spiked at 20K TPM just before the B.O. query in
> question ORA-1555'd.
> From STATSPACK reports, I think the most likely
> cause for this is a COMMIT
> for every DML in a batch job.  From what I've read,
> including MetaLink
> 40689.1, this over committing is one potential cause
> of ORA-1555s.
> 
> In order to narrow down the problem, I've turned on
> event 1555 in the
> instance.  Is it possible to determine what
> table(s)' DML is causing the
> ORA-1555 based on the trace file?  I have the last
> wait state, which happens
> to be "db file sequential read", but I don't know if
> there's any
> correlation.  If there is, I should be able to
> determine which table by the
> file# and block# given in the trace.  Is this
> correct?
> 
> Also, if the over-committing process is not doing
> any DML on the tables of
> the B.O. query, is it still a possible suspect of
> causing the ORA-1555
> because of the potential of overwriting another
> process' RBS?
> 
> Damn.  I was hoping to be at 9i before I had to deal
> with RBSs...  :)
> 
> 
> Rich
> 
> Rich JesseSystem/Database
> Administrator
> [EMAIL PROTECTED]   Quad/Tech
> International, Sussex, WI USA
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
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: Importing Schema getting compilation errors on some

[Darrell Landrum]

Scott, can you run this command in sqlplus and e-mail the results out to
us...

show errors trigger triggername

>>> [EMAIL PROTECTED] 04/04/03 02:14PM >>>
Hello,

Oracle 8.1.7.4
HPUX 11.11
App is Famis Maintenance Management System

I have 3 instances TEST, TRAIN, PROD and I am trying to refresh the
whole 
"famis" schema on TRAIN with the "famis" schema on TEST.
Every time I try to do the import, I get compilation errors for 4
triggers 
(out of 75).  I tried to re-compile them in the TRAIN instance after
the 
import and get the same compilation errors.  I try to re-compile them
in 
TEST which is where they came from and they compile without a problem. 
 I 
don't understand why this would happen when I exported the whole schema

from TEST and imported the whole .dmp file into TRAIN.

Any Thoughts?

-Scott


**
Scott Stefick
Systems / Oracle Certified DBA
Wm. Rainey Harper College
847.925.6130
**
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net 
-- 
Author: Scott Stefick
  INET: [EMAIL PROTECTED] 

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

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

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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 Catalog: 1 vs. many - Opinions

[Paula_Stankus]

Title: RE: RMAN Catalog: 1 vs. many - Opinions





I have setup a server for just rman catalog backed up to tape with clones offsite for DR.  It makes it easier as scripts are parameterized to keep aware of backup statuses = etc.  Having this on a centralized management server and can't believe this isn't just common practice.  Just make sure no single point of failure.

Oracle OCP DBA



-Original Message-
From: DENNIS WILLIAMS [mailto:[EMAIL PROTECTED]]
Sent: Friday, April 04, 2003 3:39 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: RMAN Catalog: 1 vs. many - Opinions



Deepak
   You may also wish to consider not using catalog(s) -- control file
backups. This might make it easier to add and remove databases in your
environment. I'm presuming most databases are on their own server.
   If you want to be able to run a single query that will report any backup
problems across all your databases, you will use a single catalog.


Dennis Williams
DBA, 40%OCP, 100% DBA
Lifetouch, Inc.
[EMAIL PROTECTED] 



-Original Message-
Sent: Friday, April 04, 2003 2:09 PM
To: Multiple recipients of list ORACLE-L




We are in the process of moving from traditional hot
backups to one using RMAN. Total databases are around
200+. Should we be using 1 or few RMAN catalogs, or 1
catalog per database? 


Thanks,
Deepak


__
Do you Yahoo!?
Yahoo! Tax Center - File online, calculators, forms, and more
http://tax.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Deepak 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.net
-- 
Author: DENNIS WILLIAMS
  INET: [EMAIL PROTECTED]


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

Re: Autoallocate vs Uniform extent performance

[bill thater]

[EMAIL PROTECTED] wrote:

These guys are why I think I know nothing!
yes my goddess, what ever you say my goddess.;-)

you are the reason i think i don't know anything.  so guess how far 
behind those guys i am.;-)

--
--
Bill "Shrek" Thater  ORACLE DBA
[EMAIL PROTECTED]

Om mani padme hum.

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: bill thater
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: Rollback and what table's DML caused an ORA-1555?

[Jonathan Lewis]

In general, you don't need to do this in 
recent versions of Oracle.  Oracle knows
that all the data in the tablespace MUST
have been committed before the tablespace
was switched to read-only (you can only 
switch a tablspace to readonly when there
are no active transactions that started before
the call to do the switch), and can use 
the 'read-only' SCN as the upper bound
commit for the data in the tablespace.

The only problem would come if you had a 
long-running query that started before a 
tablespace was made readonly - and Oracle 
needed to know whether some data had 
changed before or after the query started -
AND you had managed to overwrite the 
undo that contained the relevant older
version of the transaction table.

If you do need to do this, don't forget
that you also have to worry about indexes -
count(*) could be satisfied from an index, 
and the 1555 problem could be down to 
an index block anyway - so the "SQL to
write SQL" has to be a little clever.


Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

  The educated person is not the person 
  who can answer the questions, but the 
  person who can question the answers -- T. Schick Jr


One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html

UK___April 8th
UK___April 22nd
Denmark__May 21-23rd
USA_(FL)_May 2nd

Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
UK_(Manchester)_May
Estonia___June (provisional)
USA_(CA, TX)_August

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


- Original Message - 
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: 04 April 2003 19:53


> 
> 1) Set those tablespaces back to READ-WRITE mode
> 2) Write a sql-generating-sql script that performs a
> select count(*) on all the objects in the tablespace
> 3) Set the tablespaces back to READ-ONLY. 
> 


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

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

Re: linux intel support matrix

[bill thater]

[EMAIL PROTECTED] wrote:
Bill,
 Welcome back!!! I haven't read you in a long time. Hope all is well.
Ron
just got a 3 month contract after being out of work since the end of 
june.  guess that's the end of my afternoon naps, and my afternoon beer, 
for a while.;-)

--
--
Bill "Shrek" Thater  ORACLE DBA
[EMAIL PROTECTED]

Om mani padme hum.

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: bill thater
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: Autoallocate vs Uniform extent performance

[JApplewhite]

Rachel,

At a recent past job, under 8.1.6 on Win2k we had tables with out-of-line
CLOB segments of 30,000 extents (1MB each).  Every month we dropped one to
make room for another (6 months of CLOB documents online).  It always just
took a few seconds for the drop.  These were in DMTs.

Later we switched servers and I changed to LMTs of 100MB Uniform Extents
for the CLOB segments.  Going from 30,000 to 300 extents for those hulks
made no noticeable difference in query or interMedia indexing performance,
nor did it noticeably change the time it took to drop the tables.

Here at AISD, our student information database (SASI, for those in
Education who know this 3rd party app) has over 47,000 tables and 70,000
indexes (typical abysmal design for a 3rd party app, eh?), many of them
empty or with very few rows.  A few months ago I rebuilt it under 8.1.7.4.6
(Win2k - it was previously at 8.1.7.0.0) with LMTs of 8KB Uniform Extents
to save space.  Surprisingly, only 40 or so segments have over 1000
extents.  One, a consolidated Student table, has a little over 10,000
extents.  We've noticed no problem at all with performance, etc.

I've not been concerned about extent counts for several years now, and I've
seen nothing convincing that I should be.  Maybe I've just not hit the
situation where it matters.  That is not to say that extents don't matter,
but it's only if they obey the stupid directives of uninformed duhvelopers,
such as those of our 3rd party Financials system, where they used
PctIncrease of 50.  Like children and dogs, there are no bad extents, just
bad designers.;-)

Jack C. Applewhite
Database Administrator
Austin Independent School District
Austin, Texas
512.414.9715 (wk)
512.935.5929 (pager)
[EMAIL PROTECTED]



   

  Rachel Carmichael

  <[EMAIL PROTECTED]To:   Multiple recipients of list 
ORACLE-L  
  o.com><[EMAIL PROTECTED]>
 
  Sent by: cc: 

  [EMAIL PROTECTED] Subject:  Re: Autoallocate vs Uniform 
extent performance
   

   

  04/04/2003 07:01 

  AM   

  Please respond to

  ORACLE-L 

   

   





rumor hath it (as I've never actually had an object hit that high a
number) that when you exceed 4K extents it's time to resize. This came
from one of the instructors in Oracle University, one who is well-known
to actually have more than a clue. He said this at the Data Internals
class, before 9i was released.

I have not seen his test results but I do know that tests done with
DMTs have shown that large numbers of extents (I believe Kevin Loney
tested with 60K extents, and I vaguely remember a conversation with
Cary where he said he had also tested large numbers)  are a problem
during operations that empty a lot of extents (think large deletes)
because of thrashing on FET$ and UET$. Since an LMT doesn't access
those tables by design, I would think that that problem goes away.
--
Author: Rachel Carmichael
  INET: [EMAIL PROTECTED]




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
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: What blocks are backed up with a full backup?

[Freeman Robert - IL]

Teach me? Hell, there is more stuff in the world I don't know than I know
of! I've been wrong before and I'll be wrong again and  and I hate this,
I FORGET STUFF. Easy stuff, simple stuff stuff I kick myself all over
creation because I forgot it.

Me the master, na Thats where Jonathan and Connor and others come
in. Me, I'm a 
humble DBA who just wants to have some fun and answer an occational question
correctly.

RF

-Original Message-
To: Multiple recipients of list ORACLE-L
Sent: 4/4/2003 12:03 PM

I apologize, I shouldn't be trying to teach the master.

-Original Message-
Sent: Friday, April 04, 2003 12:09 PM
To: Multiple recipients of list ORACLE-L


This is accurate and far more correct than my quickly written statement
was.
I actually say this almost word for word in the book. This is why even a
table truncate will not reduce RMAN space usage. Sorry, I'll endevor in
the
future to be more susinct! :-)

RF

-Original Message-
To: Multiple recipients of list ORACLE-L
Sent: 4/4/2003 10:13 AM

What it doesn't write are the blocks that are not allocated to any
extent. RMAN doesn't go into the logical structures, like tables and
indexes, 
it looks into the tablespace header and reads the information from the
bitmap information there. It cannot go into tables/indexes because it
should also work when the database is only mounted and not opened, which
genrally means that data dictionary is not accessible. Empty blocks 
are blocks that don't have any rows in them but are allocated to an
extent. New or unallocated blocks are blocks that have been initialized
when the datafile was allocated to the tablespace but have not been
assigned
to any object (table, index, materialized view, cluster, partition or
alike)

-Original Message-
Sent: Thursday, April 03, 2003 4:24 PM
To: Multiple recipients of list ORACLE-L


Actually this is how RMAN works. It writes all blocks up to the HWM of a
given table,
even empty ones. So, if your HWM is artifically high, you will encounter
backups
that are larger than they need to be.

Oracle9i RMAN Backup and Recovery On bookshelves now!

RF


-Original Message-
To: Multiple recipients of list ORACLE-L
Sent: 4/3/2003 11:19 AM

Keith
   Here is my understanding (don't rely on this one). When Oracle
allocates
tablespace, the disk blocks are cleared. My interpretation is that when
RMAN
encounters a clear block, it doesn't write it to the backup piece. I
don't
think it spends a lot of time trying to figure out above HWM and such.

Dennis Williams
DBA, 40%OCP, 100% DBA
Lifetouch, Inc.
[EMAIL PROTECTED] 


-Original Message-
Sent: Thursday, April 03, 2003 10:44 AM
To: Multiple recipients of list ORACLE-L


Our RMAN backup is backing up much more than the actual data blocks when
doing a full backup. I know that it backs up all blocks that have "ever
been
used", but I'm trying to figure out exactly what that means. My first
thought was that it backs up all blocks below the HWM, but I analyzed
the
tables and that is not the case.

Sometimes it backs up more blocks than exist below the HWM for the
tables
and sometimes it backs up fewer blocks than those below the HWM.

We are doing this to determine what we can do to reduce the size of the
backup.

Anyone have an idea how this works?

Keith


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

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

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

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

Importing Schema getting compilation errors on some triggers

[Scott Stefick]

Hello,

Oracle 8.1.7.4
HPUX 11.11
App is Famis Maintenance Management System
I have 3 instances TEST, TRAIN, PROD and I am trying to refresh the whole 
"famis" schema on TRAIN with the "famis" schema on TEST.
Every time I try to do the import, I get compilation errors for 4 triggers 
(out of 75).  I tried to re-compile them in the TRAIN instance after the 
import and get the same compilation errors.  I try to re-compile them in 
TEST which is where they came from and they compile without a problem.   I 
don't understand why this would happen when I exported the whole schema 
from TEST and imported the whole .dmp file into TRAIN.

Any Thoughts?

-Scott

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

Full table scan difference

[DENNIS WILLIAMS]

List - As I mentioned earlier, a new manager came from a site that regularly
rebuilt tables. I protested the value of this, but his response was "well,
at least you could test it." So on a test instance I've been doing a CTAS to
create a reorganized copy of the table in the same tablespace (LMT with
uniform extents, autoextend on). As expected, the number of blocks is very
close. 
   Then I've been doing a FTS (select count(*)) on both tables. Usually the
time is very close. However, on one large table, the original is much faster
-- 18-sec. vs. 27-sec. 21,349 blocks vs. 21179. Same plan. Does anyone have
a theory for this discrepancy?

Dennis Williams
DBA, 40%OCP, 100% DBA
Lifetouch, Inc.
[EMAIL PROTECTED] 
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: DENNIS WILLIAMS
  INET: [EMAIL PROTECTED]

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

RMAN Catalog: 1 vs. many - Opinions

[Deepak Sharma]

We are in the process of moving from traditional hot
backups to one using RMAN. Total databases are around
200+. Should we be using 1 or few RMAN catalogs, or 1
catalog per database? 

Thanks,
Deepak

__
Do you Yahoo!?
Yahoo! Tax Center - File online, calculators, forms, and more
http://tax.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Deepak 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: Do triggers cause a context switch between SQL & PL/SQL

[Jamadagni, Rajendra]

Title: RE: Do triggers cause a context switch between SQL & PL/SQL





Jonathan


it is switching from SQL context to PL/SQL context and vice versa.


Raj

Rajendra dot Jamadagni at nospamespn dot com
All Views expressed in this email are strictly personal.
QOTD: Any clod can have facts, having an opinion is an art !



*This e-mail 
message is confidential, intended only for the named recipient(s) above and may 
contain information that is privileged, attorney work product or exempt from 
disclosure under applicable law. If you have received this message in error, or are 
not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 
and delete this e-mail message from your computer, Thank 
you.*1

RE: RMAN Catalog: 1 vs. many - Opinions

[DENNIS WILLIAMS]

Deepak
   You may also wish to consider not using catalog(s) -- control file
backups. This might make it easier to add and remove databases in your
environment. I'm presuming most databases are on their own server.
   If you want to be able to run a single query that will report any backup
problems across all your databases, you will use a single catalog.

Dennis Williams
DBA, 40%OCP, 100% DBA
Lifetouch, Inc.
[EMAIL PROTECTED] 


-Original Message-
Sent: Friday, April 04, 2003 2:09 PM
To: Multiple recipients of list ORACLE-L



We are in the process of moving from traditional hot
backups to one using RMAN. Total databases are around
200+. Should we be using 1 or few RMAN catalogs, or 1
catalog per database? 

Thanks,
Deepak

__
Do you Yahoo!?
Yahoo! Tax Center - File online, calculators, forms, and more
http://tax.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Deepak 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.net
-- 
Author: DENNIS WILLIAMS
  INET: [EMAIL PROTECTED]

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

RE: Sub-query in order by clause

[Jacques Kilchoer]

Title: RE: Sub-query in order by clause





Why would you do that instead of
SELECT emp.deptno, empno, ename
FROM emp a, dept b
WHERE dept.deptno = emp.deptno
order by dept.deptname ;


> -Original Message-
> From: Nicoll, Iain [mailto:[EMAIL PROTECTED]]
> 
> Don't really know but couldn't it be useful if you had 
>  
> ORDER BY ( SELECT deptname FROM dept
> WHERE dept.deptno = emp.deptno)
>  
>  
> -Original Message-
> From: Ashish [mailto:[EMAIL PROTECTED]]
> 
> In Oracle 9.2.0.1, you can now order by using a single-row subquery
> Here is an example:
>  
> SELECT emp.deptno, empno, ename
> FROM emp
> ORDER BY ( SELECT deptno FROM dept
> WHERE dept.deptno = emp.deptno );
>  
> The question I have is what is the usefulness of this? Under which
> circumstances
> this can be used?

Re: OLAP Window Functions e.g. rank()

[Jonathan Lewis]

Which version of 8i ?
They didn't appear until 8.1.6.


Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

  The educated person is not the person
  who can answer the questions, but the
  person who can question the answers -- T. Schick Jr


One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html

UK___April 8th
UK___April 22nd
Denmark__May 21-23rd
USA_(FL)_May 2nd

Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
UK_(Manchester)_May
Estonia___June (provisional)
USA_(CA, TX)_August

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: 01 April 2003 13:58


> There is a script, over on DBA-village.com, that uses rank() and
> ration_to_report(), which are features that I apparently didn't
install
> along with the database (8i)

> Do I have to install this from the CD and re-start the database in
order
> to use?
> Are there any possible problems with installing this?
>
> Regards
>
> Craig Healey
>


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

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

[ Oracle EnterPrise Manager]

[Dory Edson]

Hello friends,
 
I'm a new member, my name is Dory Edson, and I live 
in Brasil.
 
My question is about Oracle EnterPrise 
Manager
 
I have installed Oracle 8.1.17i, and I can't start 
the Oracle EnterPrise Manager. (it can't find the service our 
server)
I realized that 
the service OracleManagementService Isn't running, 
and if I try to start this service, it doesn't 
work. Windows 2000 server shows the message: Error 1053.
 
What should I do ? 
 
Thanks

[ Oracle Enterprise Manager]

[Dory Edson]

Hello friends,
 
I'm a new member, my name is Dory Edson, and I live 
in Brasil.
 
My question is about Oracle EnterPrise 
Manager
 
I have installed Oracle 8.1.17i, and I can't start 
the Oracle EnterPrise Manager. (it can't find the service our 
server)
I realized that 
the service OracleManagementService Isn't running, 
and if I try to start this service, it doesn't 
work. Windows 2000 server shows the message: Error 1053.
 
What should I do ? 
 
Thanks

RE: dbshut script - why shutdown so much???

[Jesse, Rich]

One more foot in the grave of us diehard IMMEDIATE fans (nee anti-ABORT).

Good to know that it's an option!  Now if I can just get all of our various
software vendors to support anything above 8.1.7.2.0...


Rich

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

-Original Message-
Sent: Friday, April 04, 2003 10:34 AM
To: Multiple recipients of list ORACLE-L


In 9i Release 2 :
  
 exec dbms_stats.flush_database_monitoring_info 

Nothing of the sort in earlier releases... 

- Kirti 

-Original Message-
Sent: Friday, April 04, 2003 9:09 AM
To: Multiple recipients of list ORACLE-L


Oh joy!  The ABORT argument again!  ;)

Is there a way to flush table monitoring counts before the ABORT, either in
8i or 9i?


Rich
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
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: Do triggers cause a context switch between SQL & PL/SQL

[Jonathan Lewis]

Don't worry about the fact that it is inefficient,
worry about the fact that it simply doesn't work


Person 2 inserts a row into table ''ApplicationFormCriteria''
but doesn't commit.  The 'pseudo-RI' trigger fires and finds
the parent row in 'ApplicationForm' so the row is deemed
okay.

Person 1 deletes the parent row from "ApplicationForm" -
the 'pseudo-RI' trigger fires - and doesn't see the uncommitted
row from the other session (that's the joy of Oracle - massive
lack of interference on uncommitted data - your programmer
may have a Sybase/MSSQL background) so the row deletes
okay.

Person 1 commits.

Person 2 commits.

Whoopsie.


Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

  The educated person is not the person
  who can answer the questions, but the
  person who can question the answers -- T. Schick Jr


One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html

UK___April 8th
UK___April 22nd
Denmark__May 21-23rd
USA_(FL)_May 2nd

Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
UK_(Manchester)_May
Estonia___June (provisional)
USA_(CA, TX)_August

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: 01 April 2003 18:48


> Question:
>
> If some idiot decides to circumvent Oracle's referential integrity
and
> re-implement it by using triggers (insert, update, delete) that
checks the
> foreign (parent/child) key fields in other tables like this,
>
> declare numrows INTEGER;
> begin
> -- ApplicationForm is used if the state and other criteria match
that
> in
> -- ApplicationFormCriteria ON PARENT DELETE RESTRICT
> select count(*) into numrows
>   from ApplicationFormCriteria
>   where
> ApplicationFormCriteria.applicationFormId =
:old.applicationFormId;
> if (numrows > 0)
> then
>   raise_application_error(
> -20343,
> 'Database Integrity Violation - Cannot DELETE row in Table
> ''ApplicationForm'' because referencing row exists in table
> ''ApplicationFormCriteria'' for Primary Key (applicationFormId)=' ||
> :old.applicationFormId
>   );
> end if;
>
>
> end;
>
> would it cause context switching between the SQL & PL/SQL engines?
>
> Yes, some genius did this in one of our databases.  Two hundred
> third/fourth normal form tables enforced by 800 triggers...  I have
him
> registered to be stoned in public.
>


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

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

RE: Autoallocate vs Uniform extent performance

[Rachel Carmichael]

Actually it was Bethesda a few years earlier but yes, Scott Gossett's
classes. He is indeed impressive. 

I took Scott Heisey's class on 8i new features in Chicago in '96 or
'97. Also a great instructor.

I've never taken a class with Cary (I'm hoping to kinda change that at
the Hotsos Symposium next year) but I've seen him present and spent
time talking with him

These guys are why I think I know nothing!

Rachel

--- "Gogala, Mladen" <[EMAIL PROTECTED]> wrote:
> You've also been to Scott Gosset's classes in NYC in the year 2001?
> He had
> a whole series of those classes. It was a very rare event indeed,
> given that
> he
> lives in Peoria, Il. I was very, very impressed with him and his
> knowledge.
> He warmly recommended Steve Adams book, which has really surprised me
> because
> I know from the private communication that Steve has never worked for
> oracle
> and
> has never been granted access to the oracle source code.
> The two classes that I missed were OPS classes given by Scott Heisey
> (a
> friend
> of mine was able to go and he claims that Scott Heisey is a genuine
> cornucopia
> of the OPS information) and the class given by one and only Cary
> Millsap.
> 
> -Original Message-
> Sent: Friday, April 04, 2003 8:01 AM
> To: Multiple recipients of list ORACLE-L
> 
> 
> rumor hath it (as I've never actually had an object hit that high a
> number) that when you exceed 4K extents it's time to resize. This
> came
> from one of the instructors in Oracle University, one who is
> well-known
> to actually have more than a clue. He said this at the Data Internals
> class, before 9i was released.
> 
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> -- 
> Author: Gogala, Mladen
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (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!?
Yahoo! Tax Center - File online, calculators, forms, and more
http://tax.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Rachel Carmichael
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: SunCluster 3. 0 Veritas File system RAC

[Scott]

Vladimir,

You can either use Sun Cluster 3.0 using raw devices
only with Veritas's Cluster Volume manager to manage
the devices.

or

You can use Veritas's DBE/AC 3.5 edition and use the
Veritas Cluster manager and Cluster File Systems
(includes Cluster Volume Manager by Default) or raw
devices.

You can't Veritas's CFS on SC3.0.

Hope this helps,

Scott



--- Vladimir Barac <[EMAIL PROTECTED]> wrote:
> Anyone using this configuration?
> 
> Since I would like to avoid raw partitions, I was
> thinking of recomending Veritas File system...
> 
> Is it certified / working? Anyone working with such
> combo?
> 
> I will fire metalink page soon, but would like to
> have some input from listers!
> 
> Thanks,
> Vladimir Barac


__
Do you Yahoo!?
Yahoo! Tax Center - File online, calculators, forms, and more
http://tax.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Scott
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: Do triggers cause a context switch between SQL & PL/SQL

[Jonathan Lewis]

Most of your cost there comes from the fact that
the triggered approach turns an array process into
a single row process.  If you want a "fairer" comparison
you need to ensure that your raw bulk load is only
processing one row at a time.


Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

  The educated person is not the person
  who can answer the questions, but the
  person who can question the answers -- T. Schick Jr


One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html

UK___April 8th
UK___April 22nd
Denmark__May 21-23rd
USA_(FL)_May 2nd

Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
UK_(Manchester)_May
Estonia___June (provisional)
USA_(CA, TX)_August

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: 01 April 2003 22:24


>
> I did some bulk load testing with triggers on v8.1.7.4 a few months
ago.
> All the trigger(s) had for code was a "NULL;" statement.  The first
trigger
> increased the time 100% in the insert (ie: a load that took 1 minute
> increased to 2 minutes with the trigger).  Each additional trigger
after
> the first cost an additional 25%.
>
> So I would read that as a 75% cost/increase to do the SQL to PLSQL
switch.
>
>


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

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

RE: Things to validate / collect before Killing a session !!!!

[Reddy, Madhusudana]

Title: RE: Things to validate / collect before Killing a session 




  Thanks Kurt 
  !!!
   
  All,
   
  what Actually Oracle 
  will do when we kill the JOB by " kill -9" , instead of "Alter System 
  ".  What about the Rollback  
??
  Any PARALLEL parameters in init.ora has any effect if 
  we kill the process by "kill -9".

  Any other inputs on 
  " Collecting the stats before killing a session " 
  ??
  [Madhu, 
  Reddy] 
   
   -Original 
  Message-From: Wiegand, Kurt 
  [mailto:[EMAIL PROTECTED]Sent: Friday, April 04, 2003 11:24 
  AMTo: Multiple recipients of list ORACLE-LSubject: RE: 
  Things to validate / collect before Killing a session 
  
  I always check to see how much rollback they have in case I 
  want to watch it rollback and I always make sure I get 
  the pid for the associated UNIX process in case I 
  decide I need to kill it.  Of course, I always almost remember to do these two things. 
  Kurt 
  -Original Message- From: 
  Reddy, Madhusudana [mailto:[EMAIL PROTECTED]] 
  Sent: Friday, April 04, 2003 11:34 AM To: Multiple recipients of list ORACLE-L Subject: Things to validate / collect before Killing a session 
   
  Hi List, We all need to kill the user 
  sessions in the situations of locking or sometime on end user / application 
  support person's request . I would like to know best practices followed before 
  killing a session , Any of you collect some information/statistics before kill 
  ?? 
  Please share your ideas and advice me  on this. 
  
  Thanks in advance,Madhu 
  -- Please see the official ORACLE-L 
  FAQ: http://www.orafaq.net 
  -- Author: Reddy, Madhusudana 
    INET: [EMAIL PROTECTED] 
  Fat City Network Services    -- 858-538-5051 http://www.fatcity.com San Diego, California    -- 
  Mailing list and web hosting services - 
  To REMOVE yourself from this mailing list, send an E-Mail 
  message to: [EMAIL PROTECTED] (note EXACT spelling 
  of 'ListGuru') and in the message BODY, include a line 
  containing: UNSUB ORACLE-L (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: Rollback and what table's DML caused an ORA-1555?

[Gaja Krishna Vaidyanatha]

Rich,

The overcommiting is a definite suspect and is worth
looking into. ALso, do you by chance have OPTIMAL set
on your rollback segments? If so, I'd suggest you
remove the OPTIMAL clause and try again. In my
experience, I have had my share of hassles with
OPTIMAL. Even when it was sized 'reasonably large'
(way above INITIAL * MINEXTENTS) for the application. 

OPTIMAL does increase the probability of ORA-1555, as
extents of your rollback segments that have the
"before images" of your transactions, can get dropped,
while your queries are left "high and dry". Just
something to check. 

The downside of not setting OPTIMAL is more disk usage
for your rollback segments. In the bigger scheme of
things, it may be much cheaper to eat the disk cost
(which is probably a few dollars) than to deal with
queries failing with ORA-1555s. If you are so strapped
for space, then you can "hand shrink" the rollback
segments using the ALTER rollback segment xxx SHRINK
command via a job, at a time when no queries are
running.

If you don't have OPTIMAL set, and if you are using
any READ-ONLY tablespaces that were put in READ-ONLY
mode recently, then try the following:

1) Set those tablespaces back to READ-WRITE mode
2) Write a sql-generating-sql script that performs a
select count(*) on all the objects in the tablespace
3) Set the tablespaces back to READ-ONLY. 

The above exercise will force a block-cleanout (if
required) on all the objects, which may not have
occured before the tablespace was originally put in
READ-ONLY mode.

Hope this helps,

Gaja

--- "Jesse, Rich" <[EMAIL PROTECTED]> wrote:
> Hey all,
> 
> Fighting with a lot of ORA-1555s lately on 8.1.7.4
> on HP/UX.  Most of them
> are now coming from long-running Business Objects
> (B.O.) queries against our
> OLTP DB.  I think I need to recreate the RBS
> tablespace (currently 1MB
> extents in LMT), but until I can get time to do
> that, I'd like to approach
> this from the application side, where I think the
> majority of the problem is
> occurring.  I've been tracking TPM based on "user
> commits" in V$SYSSTAT and
> we spiked at 20K TPM just before the B.O. query in
> question ORA-1555'd.
> From STATSPACK reports, I think the most likely
> cause for this is a COMMIT
> for every DML in a batch job.  From what I've read,
> including MetaLink
> 40689.1, this over committing is one potential cause
> of ORA-1555s.
> 
> In order to narrow down the problem, I've turned on
> event 1555 in the
> instance.  Is it possible to determine what
> table(s)' DML is causing the
> ORA-1555 based on the trace file?  I have the last
> wait state, which happens
> to be "db file sequential read", but I don't know if
> there's any
> correlation.  If there is, I should be able to
> determine which table by the
> file# and block# given in the trace.  Is this
> correct?
> 
> Also, if the over-committing process is not doing
> any DML on the tables of
> the B.O. query, is it still a possible suspect of
> causing the ORA-1555
> because of the potential of overwriting another
> process' RBS?
> 
> Damn.  I was hoping to be at 9i before I had to deal
> with RBSs...  :)
> 
> 
> Rich
> 
> Rich JesseSystem/Database
> Administrator
> [EMAIL PROTECTED]   Quad/Tech
> International, Sussex, WI USA
> -- 
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.net
> -- 
> 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).
> 


=


__
Do you Yahoo!?
Yahoo! Tax Center - File online, calculators, forms, and more
http://tax.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Gaja Krishna Vaidyanatha
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: Do triggers cause a context switch between SQL & PL/SQL

[Jonathan Lewis]

I have always been puzzled by the expression
"context switch" when talking about sql and pl/sql.

To me, a context switch is something that happens
at the O/S level when a process is suspended by the
scheduler and a different process is resumed.  If a
trigger fires on an insert/update/delete, this is obviously
not happening - the only process executing is your
Oracle server (dedicated or MTS) - so what activity is
being given the name "context switch" ?  Surely the
Oracle process is 'simply' calling a sub-routine somewhere
else in its code path (and building a stack and acquiring
sub-heaps and ...).


Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

  The educated person is not the person
  who can answer the questions, but the
  person who can question the answers -- T. Schick Jr


One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html

UK___April 8th
UK___April 22nd
Denmark__May 21-23rd
USA_(FL)_May 2nd

Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
UK_(Manchester)_May
Estonia___June (provisional)
USA_(CA, TX)_August

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: 01 April 2003 19:19


> Since you the action switches from SQL to PL/SQL to enforce the
trigger,
> it sounds like a context switch to me.
>
> FYI:  This can be improved somewhat by adding "and rownum < 2 " into
the
> WHERE clauses of these.  There will be noticable improvement when
there
> are many child rows.
>
> Jared
>
>
>


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

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

Re: linux intel support matrix

[Ron Rogers]

Bill,
 Welcome back!!! I haven't read you in a long time. Hope all is well.
Ron

>>> [EMAIL PROTECTED] 04/04/03 12:29PM >>>
[EMAIL PROTECTED] wrote:
> I seriously advise against the Intel Itanic machines. If you
> need 64 bits you can either wait for the Clawhammer or switch to
> something else that already supports 64 bits. One little HP
superdome
> would be a nice decoration in my living room. Intel Itanic has hit
an
> iceberg.

now don't hold back, let us know how you really feel.;-)  me i'd settle

for a nice alpha.  i could even deal with VMS.;-)


-- 
--
Bill "Shrek" Thater  ORACLE DBA
 [EMAIL PROTECTED] 

Om mani padme hum.


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

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

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

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

Re: Lies, damn lies and statistics

[Jonathan Lewis]

Stephane,

Three things I don't need to tell you about
your example:

Prior to CPU-costing in 9, the order of
single table predicates can have a dramatic
impact on CPU without changing the amount
of logical I/O.

Logical I/O should always include the statistics
"buffer is pinned count" - it's just another way of
visiting a buffer - the cost of getting there is cheaper,
but still not free.

Any change to the query could modify
the number of times a pl/sql function is called -
which could be far more CPU intensive than
all the logical I/O effects anyway.



Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

  The educated person is not the person
  who can answer the questions, but the
  person who can question the answers -- T. Schick Jr


One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html

UK___April 8th
UK___April 22nd
Denmark__May 21-23rd
USA_(FL)_May 2nd

Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
UK_(Manchester)_May
Estonia___June (provisional)
USA_(CA, TX)_August

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: 01 April 2003 22:14



> Here is the result :
>
> original  all_rows
> Steps in plan176   166
> recursive calls  259  1776
> db block gets 72   324
> consistent gets   474556  6700
> physical reads 12497  1981
> redo size152 21736
> bytes to client 3060  3060
> bytes from client   5811  4500
> SQL*Net roundtrips 2 2
> memory sorts  1013
> disk sorts 0 0
> rows   6 6
> Elapsed time   24.75> 4 mn
>
> Although LIOs have drastically reduced, and so have PIOs, our time
is
> about 10 times worse!


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

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

Re: RE: Autoallocate vs Uniform extent performance

[Jonathan Lewis]

I think you ought to refine your test:

Create two tables at one extent each,
then alternately allocate one extent
to each table until you get to a very
large number of extents.  THEN try
dropping one of them.

Remember to set tablespace quotas
for the user creating the table.


It still won't scare you off, by the way,
so you don't have to do it.  Check with
Connor which version of Oracle introduced
the modification that updates tsq$ just
once one the drop, rather than once
per extent as this does make a difference.


Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

  The educated person is not the person
  who can answer the questions, but the
  person who can question the answers -- T. Schick Jr


One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html

UK___April 8th
UK___April 22nd
Denmark__May 21-23rd
USA_(FL)_May 2nd

Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
UK_(Manchester)_May
Estonia___June (provisional)
USA_(CA, TX)_August

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: 04 April 2003 09:23


> Hi Pete,
>
> Stop using my favourite answer :)
>
> I'm not suggesting this is particularly scientific but here's a
quick
> test on dropping a moderate number of extents (9.2 on XP):
>
> SQL> create tablespace biggish
>  2  datafile 'c:\bowie\biggish01.dbf' size 2000M
>  3  uniform size 16K;
>
> Tablespace created.
>
> SQL> set timing on
> SQL> create table lots_of_extents (x number)
>  2  storage (initial 1990M)
>  3  tablespace biggish;
>
> Table created.
>
> Elapsed: 00:00:49.06
>
> SQL> select count(*) from user_extents where segment_name
> = 'LOTS_OF_EXTENTS';
>
>  COUNT(*)
> --
>127423
>
> Elapsed: 00:00:04.01
>
> SQL> drop table lots_of_extents;
>
> Table dropped.
>
> Elapsed: 00:00:06.08
>
> Based on the above, I could drop a table with 1,000,000 extents in
> under 1 minute.


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

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

RE: Sub-query in order by clause

[Nicoll, Iain]

Don't really know but couldn't it be useful if you had 
 
ORDER BY ( SELECT deptname FROM dept
WHERE dept.deptno = emp.deptno)
 
 
-Original Message-
Sent: 04 April 2003 17:34
To: Multiple recipients of list ORACLE-L


Hello list,
 
In Oracle 9.2.0.1, you can now order by using a single-row subquery
Here is an example:
 
SELECT emp.deptno, empno, ename
FROM emp
ORDER BY ( SELECT deptno FROM dept
WHERE dept.deptno = emp.deptno );
 
The question I have is what is the usefulness of this? Under which
circumstances
this can be used? Any ideas?
 
-Ashish
OCP DBA
 
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Nicoll, Iain
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: Sub-query in order by clause

[Jonathan Lewis]

Wow !
I didn't realise that was possible - I'll have to
see what Oracle can do with the execution plan.

Your example could, of course, be rearranged
to join emp to dept in order to order by some
column of dept - and that might give us a clue
why this could be useful.

Pure hand-waving here - not arguing for or 
against - but what if you have a heavy-duty
multitable query that returned only a few rows.
It could be the case that joining in 'dept' (i.e.
just one more table needed to supply the
ordering) would require that join to be exercised 
a very large number of times, either as a nested loop, 
or as an extra hash join. Whereas if you effectively joined 
in 'dept' after the query was complete as per your
example, the additional cost would be small.

(In some ways, this is like taking advantage of
the 'push_subq' hint to rearrange the moment
at which Oracle executes a non-mergeable 
subquery.)

 In many cases though (if not all) I suspect the 
same effect could be produced by using a no_merge 
inline view - which has the added benefit of being
intuitively a little more readable.  (Although, your
site may have a "no hints" standard that could 
preclude the use of the inline view option).


Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

  The educated person is not the person 
  who can answer the questions, but the 
  person who can question the answers -- T. Schick Jr


One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html

UK___April 8th
UK___April 22nd
Denmark__May 21-23rd
USA_(FL)_May 2nd

Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
UK_(Manchester)_May
Estonia___June (provisional)
USA_(CA, TX)_August

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


- Original Message - 
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: 04 April 2003 17:33


> Hello list,
> 
> In Oracle 9.2.0.1, you can now order by using a single-row subquery
> Here is an example:
> 
> SELECT emp.deptno, empno, ename
> FROM emp
> ORDER BY ( SELECT deptno FROM dept
> WHERE dept.deptno = emp.deptno );
> 
> The question I have is what is the usefulness of this? Under which
> circumstances
> this can be used? Any ideas?
> 
> -Ashish
> OCP DBA
> 

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

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

RE: Autoallocate vs Uniform extent performance

[Gogala, Mladen]

You've also been to Scott Gosset's classes in NYC in the year 2001? He had
a whole series of those classes. It was a very rare event indeed, given that
he
lives in Peoria, Il. I was very, very impressed with him and his knowledge.
He warmly recommended Steve Adams book, which has really surprised me
because
I know from the private communication that Steve has never worked for oracle
and
has never been granted access to the oracle source code.
The two classes that I missed were OPS classes given by Scott Heisey (a
friend
of mine was able to go and he claims that Scott Heisey is a genuine
cornucopia
of the OPS information) and the class given by one and only Cary Millsap.

-Original Message-
Sent: Friday, April 04, 2003 8:01 AM
To: Multiple recipients of list ORACLE-L


rumor hath it (as I've never actually had an object hit that high a
number) that when you exceed 4K extents it's time to resize. This came
from one of the instructors in Oracle University, one who is well-known
to actually have more than a clue. He said this at the Data Internals
class, before 9i was released.

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

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: What blocks are backed up with a full backup?

[Gogala, Mladen]

I apologize, I shouldn't be trying to teach the master.

-Original Message-
Sent: Friday, April 04, 2003 12:09 PM
To: Multiple recipients of list ORACLE-L


This is accurate and far more correct than my quickly written statement was.
I actually say this almost word for word in the book. This is why even a
table truncate will not reduce RMAN space usage. Sorry, I'll endevor in the
future to be more susinct! :-)

RF

-Original Message-
To: Multiple recipients of list ORACLE-L
Sent: 4/4/2003 10:13 AM

What it doesn't write are the blocks that are not allocated to any
extent. RMAN doesn't go into the logical structures, like tables and
indexes, 
it looks into the tablespace header and reads the information from the
bitmap information there. It cannot go into tables/indexes because it
should also work when the database is only mounted and not opened, which
genrally means that data dictionary is not accessible. Empty blocks 
are blocks that don't have any rows in them but are allocated to an
extent. New or unallocated blocks are blocks that have been initialized
when the datafile was allocated to the tablespace but have not been
assigned
to any object (table, index, materialized view, cluster, partition or
alike)

-Original Message-
Sent: Thursday, April 03, 2003 4:24 PM
To: Multiple recipients of list ORACLE-L


Actually this is how RMAN works. It writes all blocks up to the HWM of a
given table,
even empty ones. So, if your HWM is artifically high, you will encounter
backups
that are larger than they need to be.

Oracle9i RMAN Backup and Recovery On bookshelves now!

RF


-Original Message-
To: Multiple recipients of list ORACLE-L
Sent: 4/3/2003 11:19 AM

Keith
   Here is my understanding (don't rely on this one). When Oracle
allocates
tablespace, the disk blocks are cleared. My interpretation is that when
RMAN
encounters a clear block, it doesn't write it to the backup piece. I
don't
think it spends a lot of time trying to figure out above HWM and such.

Dennis Williams
DBA, 40%OCP, 100% DBA
Lifetouch, Inc.
[EMAIL PROTECTED] 


-Original Message-
Sent: Thursday, April 03, 2003 10:44 AM
To: Multiple recipients of list ORACLE-L


Our RMAN backup is backing up much more than the actual data blocks when
doing a full backup. I know that it backs up all blocks that have "ever
been
used", but I'm trying to figure out exactly what that means. My first
thought was that it backs up all blocks below the HWM, but I analyzed
the
tables and that is not the case.

Sometimes it backs up more blocks than exist below the HWM for the
tables
and sometimes it backs up fewer blocks than those below the HWM.

We are doing this to determine what we can do to reduce the size of the
backup.

Anyone have an idea how this works?

Keith


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

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

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

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

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

Re: linux intel support matrix

[bill thater]

[EMAIL PROTECTED] wrote:
I seriously advise against the Intel Itanic machines. If you
need 64 bits you can either wait for the Clawhammer or switch to
something else that already supports 64 bits. One little HP superdome
would be a nice decoration in my living room. Intel Itanic has hit an
iceberg.
now don't hold back, let us know how you really feel.;-)  me i'd settle 
for a nice alpha.  i could even deal with VMS.;-)

--
--
Bill "Shrek" Thater  ORACLE DBA
[EMAIL PROTECTED]

Om mani padme hum.

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: bill thater
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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 ?????????

[Jonathan Lewis]

It's worth noting that even in the execution path
you get a clue that the table isn't actually visited:

Note the FILTER line -
FILTER means too many things, but one of the
meaning is:
"I'm not really going to do the child bit
because I know that the result set will
be empty"

This meaning appeared (I think) when partition
view were introduced to show partition elimination


Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

  The educated person is not the person 
  who can answer the questions, but the 
  person who can question the answers -- T. Schick Jr


One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html

UK___April 8th
UK___April 22nd
Denmark__May 21-23rd
USA_(FL)_May 2nd

Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
UK_(Manchester)_May
Estonia___June (provisional)
USA_(CA, TX)_August

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


- Original Message - 
> 
> select count(*) from crazy_table where 1=2
> 
> Explain Plan
> ---
> ...4 SELECT STATEMENT
> ...3 .SORT (AGGREGATE)
> ...2 ..FILTER
> ...1 ...TABLE ACCESS (FULL) OF 'ARTHUR.CRAZY_TABLE'
> 

> 
> 
> 
> >>> [EMAIL PROTECTED] 04/03/03 05:58PM >>>
> If I execute a query like ,
> select count(*) from  some_table where 1=2 .
> why does oracle bothers to look at tables . Since there is only one
> condition which is false .
> 


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

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

RE: Things to validate / collect before Killing a session !!!!

[Wiegand, Kurt]

Title: RE: Things to validate / collect  before Killing a session 






I always check to see how much rollback they have in case I want to watch

it rollback and I always make sure I get the pid for the associated UNIX

process in case I decide I need to kill it.  Of course, I always almost

remember to do these two things. 


Kurt


-Original Message-

From: Reddy, Madhusudana [mailto:[EMAIL PROTECTED]]

Sent: Friday, April 04, 2003 11:34 AM

To: Multiple recipients of list ORACLE-L

Subject: Things to validate / collect before Killing a session 



Hi List,

We all need to kill the user sessions in the situations of locking or sometime on end user / application support person's request . I would like to know best practices followed before killing a session , Any of you collect some information/statistics before kill ?? 

Please share your ideas and advice me  on this. 


Thanks in advance,Madhu



-- 

Please see the official ORACLE-L FAQ: http://www.orafaq.net

-- 

Author: Reddy, Madhusudana

  INET: [EMAIL PROTECTED]


Fat City Network Services    -- 858-538-5051 http://www.fatcity.com

San Diego, California    -- Mailing list and web hosting services

-

To REMOVE yourself from this mailing list, send an E-Mail message

to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in

the message BODY, include a line containing: UNSUB ORACLE-L

(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 full table scan

[Jared . Still]

A zip file of the run_stats scripts can be downloaded from

http://www.cybcon.com/~jkstill/download/run_stats.zip

Jared






"Igor Neyman" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
 04/04/2003 06:08 AM
 Please respond to ORACLE-L

 
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc: 
Subject:Re: oracle full table scan


Would you please?

Igor Neyman, OCP DBA
[EMAIL PROTECTED]



- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Thursday, April 03, 2003 3:28 PM


> Sorry, the attachments didn't make it, though they were only text.
>
> I can put them some accessible via the web if anyone wants them.
>
> Jared
>
>
>
>
>
> [EMAIL PROTECTED]
> Sent by: [EMAIL PROTECTED]
>  04/03/2003 11:34 AM
>  Please respond to ORACLE-L
>
>
> To: Multiple recipients of list ORACLE-L
<[EMAIL PROTECTED]>
> cc:
> Subject:RE: oracle full table scan
>
>
> If you can change it to an IOT, it may be beneficial.
>
> There's no blanket clause to be used that says 'Always do this'.
>
> I higly encourage folks on this list to setup and use the run_stats
> method of comparing different access methods.  This is something
> Tom Kyte put together.  It is very simple to use.
>
> URL:  http://osi.oracle.com/~tkyte/runstats.html
>
> I've attached my versions of the scripts for your convenience.
>
> You can use these to easily compare unindexed vs indexed
> reads on small tables, indexed vs IOT, etc.
>
> Jared
>
>
>
>
>
>
>
> "Jamadagni, Rajendra" <[EMAIL PROTECTED]>
> Sent by: [EMAIL PROTECTED]
>  04/03/2003 10:05 AM
>  Please respond to ORACLE-L
>
>
> To: Multiple recipients of list ORACLE-L
> <[EMAIL PROTECTED]>
> cc:
> Subject:RE: oracle full table scan
>
>
> Thanks Jared,
> What if my developer is selecting all or most of the records from the
> table and not all the columns in the select list are in the index that
> should have been used?
> I understand your point, in fact to use Jonathan's words .. "should a
> small lookup table BE an index (IOT)?" ... I am testing this approach 
here
>
> and have found some performance benefit out of it.
> Cheers
> Raj
> 
--
--
>
> Rajendra dot Jamadagni at nospamespn dot com
> All Views expressed in this email are strictly personal.
> QOTD: Any clod can have facts, having an opinion is an art !
>
> -Original Message-
> Sent: Thursday, April 03, 2003 1:01 PM
> To: [EMAIL PROTECTED]
> Cc: Jamadagni, Rajendra
> Importance: High
>
> Raj,
> Indexing small tables is a good thing if you are doing single row 
lookups.
>
> An index read and lookup by rowid is much more scalable than
> doing an  FTS, even if the table is only 2 blocks.
> Jared
>
>
>
>
>
>
> The previous attachment was filtered out by the ListGuru mailing
> software at fatcity.com because binary attachments are not appropriate
> for mailing lists.  If you want a copy of the attachment which was
> removed, contact the sender directly and ask for it to be sent to
> you by private E-mail.
>
> This warning is inserted into all messages containing binary
> attachments which have been removed by ListGuru.  If you have questions
> about this message, contact [EMAIL PROTECTED] for clarification.
> The previous attachment was filtered out by the ListGuru mailing
> software at fatcity.com because binary attachments are not appropriate
> for mailing lists.  If you want a copy of the attachment which was
> removed, contact the sender directly and ask for it to be sent to
> you by private E-mail.
>
> This warning is inserted into all messages containing binary
> attachments which have been removed by ListGuru.  If you have questions
> about this message, contact [EMAIL PROTECTED] for clarification.
> The previous attachment was filtered out by the ListGuru mailing
> software at fatcity.com because binary attachments are not appropriate
> for mailing lists.  If you want a copy of the attachment which was
> removed, contact the sender directly and ask for it to be sent to
> you by private E-mail.
>
> This warning is inserted into all messages containing binary
> attachments which have been removed by ListGuru.  If you have questions
> about this message, contact [EMAIL PROTECTED] for clarification.
> The previous attachment was filtered out by the ListGuru mailing
> software at fatcity.com because binary attachments are not appropriate
> for mailing lists.  If you want a copy of the attachment which was
> removed, contact the sender directly and ask for it to be sent to
> you by private E-mail.
>
> This warning is inserted into all messages containing binary
> attachments which have been removed by ListGuru.  If you have questions
> about this message, contact [EMAIL PROTECTED] for clarification.
> The previous attachment was filtered out by the ListGuru mailing
> software at fatcity.com because binary att

RE: Rollback and what table's DML caused an ORA-1555?

[DENNIS WILLIAMS]

Rich
  For what it's worth, here would be my priorities:

 1. Ask the users to run the report at a different time. Maybe plot the
transactions over a 24-hour period to display better time. They won't like
this,  and you may want to soften it with "until we get the application
modified".
 2. Create a new rollback tablespace. You can do this quickly. Ask the
report users to hold off running reports for a few minutes, then offline the
current rollback tablespace. It won't go off until all current transactions
complete.
 3. Modify the application. In my experience, actions #1 and #2 can be
done within hours. Modifying the application may take between a couple of
days and never.

Dennis Williams
DBA, 40%OCP, 100% DBA
Lifetouch, Inc.
[EMAIL PROTECTED] 


-Original Message-
Sent: Friday, April 04, 2003 10:54 AM
To: Multiple recipients of list ORACLE-L


Hey all,

Fighting with a lot of ORA-1555s lately on 8.1.7.4 on HP/UX.  Most of them
are now coming from long-running Business Objects (B.O.) queries against our
OLTP DB.  I think I need to recreate the RBS tablespace (currently 1MB
extents in LMT), but until I can get time to do that, I'd like to approach
this from the application side, where I think the majority of the problem is
occurring.  I've been tracking TPM based on "user commits" in V$SYSSTAT and
we spiked at 20K TPM just before the B.O. query in question ORA-1555'd.
>From STATSPACK reports, I think the most likely cause for this is a COMMIT
for every DML in a batch job.  From what I've read, including MetaLink
40689.1, this over committing is one potential cause of ORA-1555s.

In order to narrow down the problem, I've turned on event 1555 in the
instance.  Is it possible to determine what table(s)' DML is causing the
ORA-1555 based on the trace file?  I have the last wait state, which happens
to be "db file sequential read", but I don't know if there's any
correlation.  If there is, I should be able to determine which table by the
file# and block# given in the trace.  Is this correct?

Also, if the over-committing process is not doing any DML on the tables of
the B.O. query, is it still a possible suspect of causing the ORA-1555
because of the potential of overwriting another process' RBS?

Damn.  I was hoping to be at 9i before I had to deal with RBSs...  :)


Rich

Rich JesseSystem/Database Administrator
[EMAIL PROTECTED]   Quad/Tech International, Sussex, WI USA
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
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.net
-- 
Author: DENNIS WILLIAMS
  INET: [EMAIL PROTECTED]

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

Re: why ?????????

[Wolfgang Breitling]

Title: Re: why ?





Oh, Oracle KNOWS that "where 1=2" is always false, so it knows that the 
resultset will be empty. But it has to come up with a plan, so I assume it 
just takes the simplest plan it knows - a full table scan - knowing full 
well that it does not actually have to do the scan.


At 05:58 PM 4/3/2003 -0800, you wrote:
>I'm going to take a guess, since I know that there is at least one member 
>of the list that can do better, but here goes...
>
>Predicates (where clauses) are applied as result sets are processed. 
>Oracle has no knowledge that the predicate will always evaluate to false. 
>This results in a row being read, checking the predicate and rejecting the row.
>
>Okay, gurus, please correct me if I am wrong (by the way, I'm being 
>totally serious...)


Wolfgang Breitling
Centrex Consulting Corporation
http://www.centrexcc.com



 


This email communication is intended as a private communication for the sole use of the primary addressee and those individuals listed for copies in the original message. The information contained in this email is private and confidential and if you are not an intended recipient you are hereby notified that copying, forwarding or other dissemination or distribution of this communication by any means is prohibited.  If you are not specifically authorized to receive this email and if you believe that you received it in error please notify the original sender immediately.  We honour similar requests relating to the privacy of email communications.

Cette communication par courrier électronique est une communication privée à l'usage exclusif du destinataire principal ainsi que des personnes dont les noms figurent en copie.  Les renseignements contenus dans ce courriel sont confidentiels et si vous n'êtes pas le destinataire prévu, vous êtes avisé, par les présentes que toute reproduction, tout transfert ou toute autre forme de diffusion de cette communication par quelque moyen que ce soit est interdit.  Si vous n'êtes pas spécifiquement autorisé à recevoir ce courriel ou si vous croyez l'avoir reçu par erreur, veuillez en aviser l'expéditeur original immédiatement.  Nous respectons les demandes similaires qui touchent la confidentialité des communications par courrier électronique.

Re: Rollback and what table's DML caused an ORA-1555?

[Stephane Faroult]

"Jesse, Rich" wrote:
> 
> Hey all,
> 
> Fighting with a lot of ORA-1555s lately on 8.1.7.4 on HP/UX.  Most of them
> are now coming from long-running Business Objects (B.O.) queries against our
> OLTP DB.  I think I need to recreate the RBS tablespace (currently 1MB
> extents in LMT), but until I can get time to do that, I'd like to approach
> this from the application side, where I think the majority of the problem is
> occurring.  I've been tracking TPM based on "user commits" in V$SYSSTAT and
> we spiked at 20K TPM just before the B.O. query in question ORA-1555'd.
> >From STATSPACK reports, I think the most likely cause for this is a COMMIT
> for every DML in a batch job.  From what I've read, including MetaLink
> 40689.1, this over committing is one potential cause of ORA-1555s.
> 
> In order to narrow down the problem, I've turned on event 1555 in the
> instance.  Is it possible to determine what table(s)' DML is causing the
> ORA-1555 based on the trace file?  I have the last wait state, which happens
> to be "db file sequential read", but I don't know if there's any
> correlation.  If there is, I should be able to determine which table by the
> file# and block# given in the trace.  Is this correct?
> 
> Also, if the over-committing process is not doing any DML on the tables of
> the B.O. query, is it still a possible suspect of causing the ORA-1555
> because of the potential of overwriting another process' RBS?
> 
> Damn.  I was hoping to be at 9i before I had to deal with RBSs...  :)
> 
> Rich
> 
> Rich JesseSystem/Database Administrator
> [EMAIL PROTECTED]   Quad/Tech International, Sussex, WI USA

Rich,

  In ORA-01555 you have two things :
  o DML
  o and a long-running query.

 IMHO the long-running query might be easier to catch. I know that with
BO you don't have much control on the SQL, but who knows, perhaps that
an index here or whatever ...
 Otherwise you can probably merrily get rid of that commit at every DML.
For one thing, it will run much faster. 

-- 
Regards,

Stephane Faroult
Oriole Software
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephane Faroult
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: What blocks are backed up with a full backup?

[Keith Moore]

This matches the data I have collected, with the following addition:

RMAN will backup any blocks that have ever had data in them, even if the
extent has been deallocated or the object dropped.

Keith

- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Friday, April 04, 2003 10:13 AM


> What it doesn't write are the blocks that are not allocated to any
> extent. RMAN doesn't go into the logical structures, like tables and
> indexes,
> it looks into the tablespace header and reads the information from the
> bitmap information there. It cannot go into tables/indexes because it
> should also work when the database is only mounted and not opened, which
> genrally means that data dictionary is not accessible. Empty blocks
> are blocks that don't have any rows in them but are allocated to an
> extent. New or unallocated blocks are blocks that have been initialized
> when the datafile was allocated to the tablespace but have not been
assigned
> to any object (table, index, materialized view, cluster, partition or
alike)
>
> -Original Message-
> Sent: Thursday, April 03, 2003 4:24 PM
> To: Multiple recipients of list ORACLE-L
>
>
> Actually this is how RMAN works. It writes all blocks up to the HWM of a
> given table,
> even empty ones. So, if your HWM is artifically high, you will encounter
> backups
> that are larger than they need to be.
>
> Oracle9i RMAN Backup and Recovery On bookshelves now!
>
> RF
>
>
> -Original Message-
> To: Multiple recipients of list ORACLE-L
> Sent: 4/3/2003 11:19 AM
>
> Keith
>Here is my understanding (don't rely on this one). When Oracle
> allocates
> tablespace, the disk blocks are cleared. My interpretation is that when
> RMAN
> encounters a clear block, it doesn't write it to the backup piece. I
> don't
> think it spends a lot of time trying to figure out above HWM and such.
>
> Dennis Williams
> DBA, 40%OCP, 100% DBA
> Lifetouch, Inc.
> [EMAIL PROTECTED]
>
>
> -Original Message-
> Sent: Thursday, April 03, 2003 10:44 AM
> To: Multiple recipients of list ORACLE-L
>
>
> Our RMAN backup is backing up much more than the actual data blocks when
> doing a full backup. I know that it backs up all blocks that have "ever
> been
> used", but I'm trying to figure out exactly what that means. My first
> thought was that it backs up all blocks below the HWM, but I analyzed
> the
> tables and that is not the case.
>
> Sometimes it backs up more blocks than exist below the HWM for the
> tables
> and sometimes it backs up fewer blocks than those below the HWM.
>
> We are doing this to determine what we can do to reduce the size of the
> backup.
>
> Anyone have an idea how this works?
>
> Keith
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: DENNIS WILLIAMS
>   INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Freeman Robert - IL
>   INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Gogala, Mladen
>   INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (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 transmitted is intended only for the person or entity to
which it is addressed and may contain confidential and/or privileged
material.  If the reader of this message is not the intended recipient,

RE: RMAN: What blocks are backed up with a full backup?

[Freeman Robert - IL]

This is accurate and far more correct than my quickly written statement was.
I actually say this almost word for word in the book. This is why even a
table truncate will not reduce RMAN space usage. Sorry, I'll endevor in the
future to be more susinct! :-)

RF

-Original Message-
To: Multiple recipients of list ORACLE-L
Sent: 4/4/2003 10:13 AM

What it doesn't write are the blocks that are not allocated to any
extent. RMAN doesn't go into the logical structures, like tables and
indexes, 
it looks into the tablespace header and reads the information from the
bitmap information there. It cannot go into tables/indexes because it
should also work when the database is only mounted and not opened, which
genrally means that data dictionary is not accessible. Empty blocks 
are blocks that don't have any rows in them but are allocated to an
extent. New or unallocated blocks are blocks that have been initialized
when the datafile was allocated to the tablespace but have not been
assigned
to any object (table, index, materialized view, cluster, partition or
alike)

-Original Message-
Sent: Thursday, April 03, 2003 4:24 PM
To: Multiple recipients of list ORACLE-L


Actually this is how RMAN works. It writes all blocks up to the HWM of a
given table,
even empty ones. So, if your HWM is artifically high, you will encounter
backups
that are larger than they need to be.

Oracle9i RMAN Backup and Recovery On bookshelves now!

RF


-Original Message-
To: Multiple recipients of list ORACLE-L
Sent: 4/3/2003 11:19 AM

Keith
   Here is my understanding (don't rely on this one). When Oracle
allocates
tablespace, the disk blocks are cleared. My interpretation is that when
RMAN
encounters a clear block, it doesn't write it to the backup piece. I
don't
think it spends a lot of time trying to figure out above HWM and such.

Dennis Williams
DBA, 40%OCP, 100% DBA
Lifetouch, Inc.
[EMAIL PROTECTED] 


-Original Message-
Sent: Thursday, April 03, 2003 10:44 AM
To: Multiple recipients of list ORACLE-L


Our RMAN backup is backing up much more than the actual data blocks when
doing a full backup. I know that it backs up all blocks that have "ever
been
used", but I'm trying to figure out exactly what that means. My first
thought was that it backs up all blocks below the HWM, but I analyzed
the
tables and that is not the case.

Sometimes it backs up more blocks than exist below the HWM for the
tables
and sometimes it backs up fewer blocks than those below the HWM.

We are doing this to determine what we can do to reduce the size of the
backup.

Anyone have an idea how this works?

Keith


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

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

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

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

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

Rollback and what table's DML caused an ORA-1555?

[Jesse, Rich]

Hey all,

Fighting with a lot of ORA-1555s lately on 8.1.7.4 on HP/UX.  Most of them
are now coming from long-running Business Objects (B.O.) queries against our
OLTP DB.  I think I need to recreate the RBS tablespace (currently 1MB
extents in LMT), but until I can get time to do that, I'd like to approach
this from the application side, where I think the majority of the problem is
occurring.  I've been tracking TPM based on "user commits" in V$SYSSTAT and
we spiked at 20K TPM just before the B.O. query in question ORA-1555'd.
>From STATSPACK reports, I think the most likely cause for this is a COMMIT
for every DML in a batch job.  From what I've read, including MetaLink
40689.1, this over committing is one potential cause of ORA-1555s.

In order to narrow down the problem, I've turned on event 1555 in the
instance.  Is it possible to determine what table(s)' DML is causing the
ORA-1555 based on the trace file?  I have the last wait state, which happens
to be "db file sequential read", but I don't know if there's any
correlation.  If there is, I should be able to determine which table by the
file# and block# given in the trace.  Is this correct?

Also, if the over-committing process is not doing any DML on the tables of
the B.O. query, is it still a possible suspect of causing the ORA-1555
because of the potential of overwriting another process' RBS?

Damn.  I was hoping to be at 9i before I had to deal with RBSs...  :)


Rich

Rich JesseSystem/Database Administrator
[EMAIL PROTECTED]   Quad/Tech International, Sussex, WI USA
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
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).

Sub-query in order by clause

[Ashish]

Hello 
list,
 
In Oracle 9.2.0.1, 
you can now order by using a single-row subquery
Here is an 
example:
 
SELECT emp.deptno, empno, enameFROM empORDER 
BY ( SELECT deptno FROM 
deptWHERE dept.deptno = emp.deptno 
);
 
The question I 
have is what is the usefulness of this? Under which 
circumstances
this can be used? 
Any ideas?
 
-Ashish
OCP 
DBA
 

Things to validate / collect before Killing a session !!!!

[Reddy, Madhusudana]

Hi List,
We all need to kill the user sessions in the situations of locking or sometime on end 
user / application support person's request . I would like to know best practices 
followed before killing a session , Any of you collect some information/statistics 
before kill ?? 
Please share your ideas and advice me  on this. 

Thanks in advance,Madhu


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

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: PL/SQL- cursors and commits

[Ashish]

Title: PL/SQL- cursors and commits



As the 
book says, it fails with following error (9.2.0.1 on 
Win2k).
 
declare*ERROR at line 
1:ORA-01002: fetch out of sequenceORA-06512: at line 12

  -Original Message-From: [EMAIL PROTECTED] 
  [mailto:[EMAIL PROTECTED]On Behalf Of Wiegand, KurtSent: 
  Friday, April 04, 2003 9:39 AMTo: Multiple recipients of list 
  ORACLE-LSubject: PL/SQL- cursors and commits
  I've been 'experimenting' with the following code 
  in 8.1.5 and it seems to work fine.  However, my "ORACLE PL/SQL" book from O'REILLY (Steven Feuerstein Bill Pribyl 
  1997) leads me to believe that it should 
  not work.  They state "As soon as a cursor with a FOR UPDATE is 
  OPENed, all rows...are locked. When [a 
  COMMIT]..occurs, the locks...are released. As a result, you cannot execute another FETCH against a FOR UPDATE cursor 
  after you COMMIT.."  They go 
  further to suggest an ORA-01002 would be returned. 
  Any comments? Thanks. 
  Kurt Wiegand [EMAIL PROTECTED] 
  declare   
  local_f1 ctest.f1%TYPE := 0;   
  local_f2 ctest.f2%TYPE := 0;   
  batch_count number(6) := 0;   cursor 
  c_select is   select f1,f2 from 
  ctest   for update; 
  begin   
  open c_select;   loop   fetch c_select 
    
  into local_f1,  
  local_f2;   
  exit when c_select%NOTFOUND;   update ctest  
  set f2 = f2 + 1  
  where current of c_select; 
   batch_count := batch_count 
  + 1; 
   if batch_count > 99 
  then     batch_count := 0; 
      
  commit;  end 
  if;     end loop;   close 
  c_select;   commit; end; 

RE: linux intel support matrix

[Gogala, Mladen]

I seriously advise against the Intel Itanic machines. If you
need 64 bits you can either wait for the Clawhammer or switch to
something else that already supports 64 bits. One little HP superdome
would be a nice decoration in my living room. Intel Itanic has hit an
iceberg.

-Original Message-
Sent: Friday, April 04, 2003 8:29 AM
To: Multiple recipients of list ORACLE-L


On Thu, Apr 03, 2003 at 06:33:53PM -0800, Ray Stell wrote:
> 
> I went to look at the matrix today and found it dramatically
> changed.  I found only 9.2.0 and Redhat AS.  All the other 
> "free" linux versions are gone as well as all 8i versions.
> Do you find the same, maybe the webmeister is messing we me?
> 
> Stupid, fat Hobbitses!


My bad.  I chose Linux Intel Itanium thinking they had changed it to
mean Intel + Itanium.  They changed it from Linux Intel to Linux
IA32.  The free stuff is still there. 

Nice Hobbitses!
===
Ray Stell   [EMAIL PROTECTED] (540) 231-4109 KE4TJC28^D
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Gogala, Mladen
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: dbshut script - why shutdown so much???

[Deshpande, Kirti]

In 9i Release 2 :
  
 exec dbms_stats.flush_database_monitoring_info 

Nothing of the sort in earlier releases... 

- Kirti 

-Original Message-
Sent: Friday, April 04, 2003 9:09 AM
To: Multiple recipients of list ORACLE-L


Oh joy!  The ABORT argument again!  ;)

Is there a way to flush table monitoring counts before the ABORT, either in
8i or 9i?


Rich

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


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

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: submitting statspack.snap through dbms_job

[Wolfgang Breitling]

Title: Re: submitting statspack.snap through dbms_job





var jnr number


exec  dbms_job.submit(job=>:jnr, what=>'statspack.snap(5);', 
next_date=>trunc(sysdate)+10/24, interval=>'trunc(sysdate)+10/24');
exec  dbms_job.submit(job=>:jnr, what=>'statspack.snap(5);', 
next_date=>trunc(sysdate)+14/24, interval=>'trunc(sysdate)+14/24');


commit;


At 06:23 AM 4/4/2003 -0800, you wrote:


>Quick question, I want to submit statspack.snap at 10:00am and 14:00pm 
>through dbms_job, it's friday afternoon and my head is hurting me from a 
>heavy night and the manuals are as clear as mud.
>
>Thanks
>
>
>
>Yahoo! 
>Plus - For a better Internet experience


Wolfgang Breitling
Centrex Consulting Corporation
http://www.centrexcc.com



 


This email communication is intended as a private communication for the sole use of the primary addressee and those individuals listed for copies in the original message. The information contained in this email is private and confidential and if you are not an intended recipient you are hereby notified that copying, forwarding or other dissemination or distribution of this communication by any means is prohibited.  If you are not specifically authorized to receive this email and if you believe that you received it in error please notify the original sender immediately.  We honour similar requests relating to the privacy of email communications.

Cette communication par courrier électronique est une communication privée à l'usage exclusif du destinataire principal ainsi que des personnes dont les noms figurent en copie.  Les renseignements contenus dans ce courriel sont confidentiels et si vous n'êtes pas le destinataire prévu, vous êtes avisé, par les présentes que toute reproduction, tout transfert ou toute autre forme de diffusion de cette communication par quelque moyen que ce soit est interdit.  Si vous n'êtes pas spécifiquement autorisé à recevoir ce courriel ou si vous croyez l'avoir reçu par erreur, veuillez en aviser l'expéditeur original immédiatement.  Nous respectons les demandes similaires qui touchent la confidentialité des communications par courrier électronique.

Re: oracle job question

[AK]

thanks  for the reply .
 
-ak

  - Original Message - 
  From: 
  CP 
  To: Multiple recipients of list ORACLE-L 
  
  Sent: Thursday, April 03, 2003 5:28 
  PM
  Subject: Re: oracle job question
  Yes, the value for JOB_QUEUE_PROCESSES should be >0 for any 
  scheduled jobs to execute.HTHCP[EMAIL PROTECTED] wrote:
  



I am trying to set a job in db , but db is not 
executing the job although it passed the next_date (dba_jobs). 
when I looked at  parameters i found 
JOB_QUEUE_PROCESSES =0  ( i guess its 
default).
sholdn't oracle 
automatically kicks a process ?
Is this the resson 
job is not being executed .
 
-ak
  -- Your favorite stores, helpful shopping tools 
  and great gift ideas. Experience the convenience of buying online with 
  [EMAIL PROTECTED] http://shopnow.netscape.com/ 

Re: why ?????????

[AK]

Thanks ,
That's what I guessed . But was feeling lazy to do 10046 tracing .
N E way thanks for the info .
-ak

- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Thursday, April 03, 2003 5:40 PM


> Ak,
>
> I think what you're seeing is the result of the parse not necessarily
> what ends up happening.
> I ran a similar circumstance with a 10046 trace on and it reports no
> physical reads, no logical reads, and returns 1 row.  The "explain plan"
> still shows a full table scan of my table but the trace doesn't report
> any rows or blocks read from that table.
> It's probably also worth noting that 100% of my waits were sqlnet
> message to / from client.
> Also, I ran this test because I didn't know the answer and thought it a
> very good question.
>
> The table is one I created called crazy_table as select * from
> user_objects.
> The query is
> select count(*) from crazy_table where 1=2;
>
> Here, I paste relevant sections of my trace output, but to see the
> whole picture, you might want to run some tests of your own:
>
>
> SUMMARY OF CALLS BY USER (INTERNAL LAST) AND NON-RECURSIVE/RECURSIVE
> 
>
> OVERALL TOTALS PER CALL FOR ALL NON-RECURSIVE STATEMENTS FOR USER 44
> (ARTHUR)
>
> callcount   cpu   elapsed diskquery
> current rowsmisses
> --- - - -  
>   -
> Parse   2  0.00  0.0100
>00 1
> Execute 3  0.00  0.0000
>01 0
> Fetch   2  0.00  0.0000
>01 0
> --- - - -  
>   -
> total   7  0.00  0.0100
>02 1
>
>
>

***
>
> SUMMARY OF CALLS BY COMMAND TYPE, USER (INTERNAL LAST) AND
> NON-RECURSIVE/RECURSIVE
>

==
>
> OVERALL TOTALS PER COMMAND TYPE FOR ALL NON-RECURSIVE STATEMENTS FOR
> USER 44 (ARTHUR)
>
> command typecount   cpu   elapsed disk
>   query  current rowsmisses
> --- - - - 
>    -
> select. 4  0.00  0.010
>   001 1
> alter session.. 1  0.00  0.000
>   000 0
> pl/sql execute. 2  0.00  0.000
>   001 0
> --- - - - 
>    -
> total.. 7  0.00  0.010
>   002 1
>
>
> SUMMARY OF PHYSICAL READS, LOGICAL READS, ROWS AND MISSES PER CURSOR
>
> cursor user
> id id   command typediskquery
> current rowsmisses
> --  ---  
>   -
> 1. 44.. alter session..00
>  00 0
> 2. 44.. select.00
>  01 1
> 3. 44.. pl/sql execute.00
>  01 0
> --  ---  
>   -
> total.  ...00
>  02 1
>
>
> CURSOR_ID:2  LENGTH:43  ADDRESS:abb65150  HASH_VALUE:427590100
> OPTIMIZER_GOAL:CHOOSE  USER_ID:44 (ARTHUR)
>
> select count(*) from crazy_table where 1=2
>
> callcount   cpu   elapsed diskquery
> current rowsmisses
> --- - - -  
>   -
> Parse   1  0.00  0.0100
>00 1
> Execute 1  0.00  0.0000
>00 0
> Fetch   2  0.00  0.0000
>01 0
> --- - - -  
>   -
> total   4  0.00  0.0100
>01 1
>
> | Rows Row Source Operation
> |  ---
> |1 SORT AGGREGATE
> |0 .FILTER
> |

RE: RMAN: What blocks are backed up with a full backup?

[Gogala, Mladen]

What it doesn't write are the blocks that are not allocated to any
extent. RMAN doesn't go into the logical structures, like tables and
indexes, 
it looks into the tablespace header and reads the information from the
bitmap information there. It cannot go into tables/indexes because it
should also work when the database is only mounted and not opened, which
genrally means that data dictionary is not accessible. Empty blocks 
are blocks that don't have any rows in them but are allocated to an
extent. New or unallocated blocks are blocks that have been initialized
when the datafile was allocated to the tablespace but have not been assigned
to any object (table, index, materialized view, cluster, partition or alike)

-Original Message-
Sent: Thursday, April 03, 2003 4:24 PM
To: Multiple recipients of list ORACLE-L


Actually this is how RMAN works. It writes all blocks up to the HWM of a
given table,
even empty ones. So, if your HWM is artifically high, you will encounter
backups
that are larger than they need to be.

Oracle9i RMAN Backup and Recovery On bookshelves now!

RF


-Original Message-
To: Multiple recipients of list ORACLE-L
Sent: 4/3/2003 11:19 AM

Keith
   Here is my understanding (don't rely on this one). When Oracle
allocates
tablespace, the disk blocks are cleared. My interpretation is that when
RMAN
encounters a clear block, it doesn't write it to the backup piece. I
don't
think it spends a lot of time trying to figure out above HWM and such.

Dennis Williams
DBA, 40%OCP, 100% DBA
Lifetouch, Inc.
[EMAIL PROTECTED] 


-Original Message-
Sent: Thursday, April 03, 2003 10:44 AM
To: Multiple recipients of list ORACLE-L


Our RMAN backup is backing up much more than the actual data blocks when
doing a full backup. I know that it backs up all blocks that have "ever
been
used", but I'm trying to figure out exactly what that means. My first
thought was that it backs up all blocks below the HWM, but I analyzed
the
tables and that is not the case.

Sometimes it backs up more blocks than exist below the HWM for the
tables
and sometimes it backs up fewer blocks than those below the HWM.

We are doing this to determine what we can do to reduce the size of the
backup.

Anyone have an idea how this works?

Keith


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

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

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

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: PL/SQL- cursors and commits

[Reginald . W . Bailey]

Kurt:

It appears that you are committing inside the loop in the same inner block
that the update is in.  The commit is freeing up the rows. I find it a good
practice sometimes to do commits inside the inner block like that, on long
transactions.




Reginald W. Bailey
Your Friendly Neighborhood DBA



   

[EMAIL PROTECTED]  
   
WUSA.COM To: [EMAIL PROTECTED] 
 
Sent by: cc:   

[EMAIL PROTECTED]   Subject: PL/SQL- cursors and commits   
   
om 

   

   

04/04/2003 

08:39 AM   

Please respond 

to ORACLE-L

   

   







I've been 'experimenting' with the following code in 8.1.5 and it seems to
work fine.  However,
my "ORACLE PL/SQL" book from O'REILLY (Steven Feuerstein Bill Pribyl 1997)
leads me to
believe that it should not work.  They state "As soon as a cursor with a
FOR UPDATE is OPENed,
all rows...are locked. When [a COMMIT]..occurs, the locks...are released.
As a result, you
cannot execute another FETCH against a FOR UPDATE cursor after you
COMMIT.."  They
go further to suggest an ORA-01002 would be returned.

Any comments? Thanks.

Kurt Wiegand
[EMAIL PROTECTED]

declare
  local_f1 ctest.f1%TYPE := 0;
  local_f2 ctest.f2%TYPE := 0;
  batch_count number(6) := 0;
  cursor c_select is
  select f1,f2 from ctest
  for update;

begin
  open c_select;
  loop
  fetch c_select
  into local_f1,
 local_f2;
  exit when c_select%NOTFOUND;
  update ctest
 set f2 = f2 + 1
 where current of c_select;

 batch_count := batch_count + 1;

 if batch_count > 99 then
    batch_count := 0;
    commit;
 end if;

  end loop;
  close c_select;
  commit;
end;




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
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: Cannot allocate new log - checkpoint not complete

[DENNIS WILLIAMS]

Fermin
   I would definitely go for your second configuration. I don't think there
is a problem with several redo logs on the same device, since Oracle is only
writing to one log at a time. Actually, if I could devote two devices to
redo logs, I would mirror the redo logs. You can easily recover from the
loss of a data file, but not easily from the loss of an online redo log. And
while we're on the subject, mirror your control files as well.
   The problem I've experienced with placing redo logs on the same device is
data files is that when your database experiences a really large number of
transactions, Oracle is trying desperately to write changed data blocks to
disk at the same time it is trying to write redo data. At some point, Oracle
can't service new transactions until something completes. From the outside
you experience this as a "freeze". In this situation I find it very handy to
submit a STATSPACK snapshot, or run a query that will reveal the system wait
events. 
   You've received a lot of excellent advice on this subject from some
really excellent people (and so-so advice from me). I would highly recommend
that you print all the replies and study them to understand what these
people are really saying.

Dennis Williams
DBA, 40%OCP, 100% DBA
Lifetouch, Inc.
[EMAIL PROTECTED] 


-Original Message-
Sent: Friday, April 04, 2003 4:14 AM
To: Multiple recipients of list ORACLE-L



So do you think the following distribution will contribute to a
better performance:

data datafiles - device a
index datafiles - device b
redolog1 - device c
redolog2 - device d
redolog3 - device c

instead of:

data datafiles - device a
index datafiles - device b
redolog1 - device c
redolog2 - device d
redolog3 - device a

Because I only have 5 devices available.



-Mensaje original-
De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] nombre de Connor
McDonald
Enviado el: viernes, 04 de abril de 2003 11:49
Para: Multiple recipients of list ORACLE-L
Asunto: RE: Cannot allocate new log - checkpoint not complete


Its not really a particular redo log that is the
issue.  You've used up redo's (say) 1, 2, 3 and you
want to cycle around to 1 but the checkpoint that
would free up  redo 1 is not yet finished.

Thus its not a single redo log that is the problem -
the IO rate of the checkpoint is not sufficient quick
to avoid the redo cycling around...If one of your
redo's is on common datafile disk, this could
contribute to this

hth
connor

 --- Fermin Bernaus Berraondo <[EMAIL PROTECTED]>
wrote: > 
>   Dennis,
> 
>   This is our actual distribution:
> 
>   Datafiles belonging to data in a separate disk,
> name it /baandata
>   Datafiles belonging to index in a separate disk,
> name it /baanindex
> 
>   And 3 redolog files, two of them in another two
> separate disks, and the third one located in the
> same device as the data files (/baandata).
> 
>   All of them are mirrored disks.
> 
>   Your comment makes sense, but if keeping datafiles
> and one of the redolog files in the same device
> should affect performance, then I wonder why the
> "cannot allocate new log, checkpoint not complete"
> message is affecting to the 3 redolog files and not
> only to the one located in that datafile device.
> 
>   I did not think on this. Anyway I have no more
> disks in which I can split the redologs...
> 
>   I can not wait for your comments!
> 
>   Regards,
> 
> Fermin.
> 
> -Mensaje original-
> De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
> nombre de DENNIS
> WILLIAMS
> Enviado el: jueves, 03 de abril de 2003 17:04
> Para: Multiple recipients of list ORACLE-L
> Asunto: RE: Cannot allocate new log - checkpoint not
> complete
> 
> 
> Fermin - Connor's reply sparked an idea. By any
> chance do you have your redo
> logs on the same device as your data files? 
> 
> Dennis Williams
> DBA, 40%OCP, 100% DBA
> Lifetouch, Inc.
> [EMAIL PROTECTED] 
> 
> 
> -Original Message-
> Sent: Thursday, April 03, 2003 5:04 AM
> To: Multiple recipients of list ORACLE-L
> 
> 
> Basically as the message suggests the redo cannot be
> recycled until the checkpoint has completed flushing
> out the cache.  
> 
> A *workaround* is to add redo log (size or number)
> but
> its really a heads-up about your I/O subsystem not
> being up to keep up under stress.
> 
> hth
> connor
> 
>  --- Fermin Bernaus Berraondo <[EMAIL PROTECTED]>
> wrote: > 
> > I think I am having problems with my redologs.
> > Under normal circumstances no errors arise, but if
> I
> > do a massive import of data as I was doing last
> > night, this is what alertSID.log shows from time
> to
> > time:
> > 
> > Wed Apr  2 23:29:52 2003
> > Thread 1 advanced to log sequence 557295
> >   Current log# 3 seq# 557295 mem# 0:
> > /baandata/oradata/baan/redobaan03.log
> > Wed Apr  2 23:31:11 2003
> > Thread 1 cannot allocate new log, sequ

Re: Autoallocate vs Uniform extent performance

[Rachel Carmichael]

as has been pointed out to me privately (and it's really okay to
correct me publicly!), it is not delete that would release blocks but
truncate or drop. 

Resolution: do not post before at least two cups of coffee.

My apologies to anyone I might have confused.


--- Rachel Carmichael <[EMAIL PROTECTED]> wrote:
> rumor hath it (as I've never actually had an object hit that high a
> number) that when you exceed 4K extents it's time to resize. This
> came
> from one of the instructors in Oracle University, one who is
> well-known
> to actually have more than a clue. He said this at the Data Internals
> class, before 9i was released.
> 
> I have not seen his test results but I do know that tests done
> with
> DMTs have shown that large numbers of extents (I believe Kevin Loney
> tested with 60K extents, and I vaguely remember a conversation with
> Cary where he said he had also tested large numbers)  are a problem
> during operations that empty a lot of extents (think large deletes)
> because of thrashing on FET$ and UET$. Since an LMT doesn't access
> those tables by design, I would think that that problem goes away.
> 
> 
> --- Richard Foote <[EMAIL PROTECTED]> wrote:
> > Just a general question to everyone (and one I've asked a few times
> 
> > before in different forums).
> > 
> > If we're talking LMT, how many extents are too many ? 
> > 
> > Assuming no quotas (which does introduce some known issues) at what
> 
> > point do you say that your standard uniform size of 64K has
> generated
> > 
> > too many extents and that performance is noticeably suffering to
> the 
> > level where the inconvenience of a table reorg is warranted ? 
> > 
> > When has anyone reached the point with an object in a LMT whereby 
> > performance has been an issue and by *only* reducing the number of 
> > extents, you've said "phew, that's better" ?
> > 
> > If seen many suggestions on standard uniform sizes that are
> somewhat 
> > similar to those used by autoallocate, most of which have a scale
> of 
> > magnitude around the 100 mark. These always made sense with DMT so
> > are 
> > we trying to implement outdated recommendations to LMTs ? Does
> > hitting 
> > the 100 extent mark warrant such concern and need to change our
> > extent 
> > size ?
> > 
> > My little brain usually works best with smaller numbers and I can
> > gauge 
> > the level of growth somewhat easier with smaller number of extents
> > but 
> > is that a justification for being so picky with what extent size an
> 
> > object should have ?
> > 
> > Some dba_ views will take longer to get me details I'm after but is
> 
> > that sufficient justification for being so picky with extent sizes
> ?
> > 
> > Curious in anyone's thoughts as I would hate to think we have a
> myth
> > a 
> > happening ...
> > 
> > Richard
> > 
> > 
> > - Original Message -
> > Date: Friday, April 4, 2003 9:18 am
> > 
> > > I totally agree Gaja.
> > > 
> > > I support a SAP BW system and they create tables with a 100 of 
> > > partitionsand only load 24 of them. With autoallocate, most of 
> > > them are small (64k)
> > > and space is not wasted. If they do decide to load them up, I'm 
> > > still safe
> > > because the extent size increase as the object grows.
> > > 
> > > I'm don't advocate of autoallocate for everything because I can't
> 
> > > determinethe next extent, but this is one place where it's better
> 
> > > than uniform.
> > > 
> > > I also have some uniform LMTs for larger tables that I migrate to
> > when
> > > tables get too big.
> > > 
> > > Steve
> > > 
> > > - Original Message -
> > > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
> > > Sent: Thursday, April 03, 2003 11:33 AM
> > > 
> > > 
> > > > Totally agree with Connor. Just to add a comment to
> > > > his note.
> > > >
> > > > A usage model recommended for UNIFORM vs. AUTOALLOCATE
> > > > follows:
> > > >
> > > > If you know the data volume and growth of your
> > > > segments and they are predictable, then use UNIFORM.
> > > >
> > > > If you are completely in the dark with:
> > > >
> > > > 1) How much data is going to be persisted in the
> > > > segments?
> > > > 2) What growth patterns the segments are going to
> > > > exhibit?
> > > >
> > > > Then use AUTOALLOCATE.
> > > >
> > > > Of course, if you do change your mind, after the fact,
> > > > you can use the MOVE command to the tablespace of
> > > > choice with the extent allocation of your choice.
> > > >
> > > > Cheers,
> > > >
> > > > Gaja
> > > >
> > > > --- Connor McDonald <[EMAIL PROTECTED]> wrote:
> > > > > I don't believe that was the case.  auto and uniform
> > > > > in all of the (admittedly rudimentary and
> > > > > subjective)
> > > > > tests I've done appear the same in terms of
> > > > > performance.
> > > > >
> > > > > I prefer uniform purely for the reasons of:
> > > > >
> > > > > - more thorough elimination of fragmentation
> > > > > - predictability of next extent sizes
> > > > >
> > > > > hth
> > > > > con

Re: why ?????????

[AK]

which means query data dictionary for table defination , but why go to db
blocks ?

-ak

- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Thursday, April 03, 2003 8:13 PM


>
> There are perfectly valid reasons for querying
> a table when no rows are expected to be returned.
>
> create table x
> as
> select *
> from y
> where 1=2;
>
> ... comes to mind.
>
> Jared
>
> On Thursday 03 April 2003 15:58, AK wrote:
> > If I execute a query like ,
> > select count(*) from  some_table where 1=2 .
> > why does oracle bothers to look at tables . Since there is only one
> > condition which is false .
> >
> > If  I write a sql like
> >
> >select count(*)
> >   from  some_table
> >  where column_a= :a
> >  and a is not null;
> >
> > it should not even go to look at table , but explain plan suggests that
it
> > does a index /table scan  .
> >
> > -ak
>
> 
> Content-Type: text/html; charset="iso-8859-1"; name="Attachment: 1"
> Content-Transfer-Encoding: quoted-printable
> Content-Description:
> 
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Jared Still
>   INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
>
>
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: AK
  INET: [EMAIL PROTECTED]

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

RE: SQL Question

[Chelur, Jayadas {PBSG}]

Hi Madhavan,

You are always welcome. I had to write a similiar one
some time back and I remember it was quite a task then.
Thankfully I had a head start this time !. Hope you get
the stuff sorted out. Glad to be of help ...

Regards,
Jayadas

-Original Message-
Sent: Thursday, April 03, 2003 4:34 PM
To: Multiple recipients of list ORACLE-L


Hi Jaydas,
Thanks for the reply.
It gives me a good starting point to go with. The query handles cases
where there are multiple rows.
For eg: U=2006 has G=1010 and S=1013. If there were another U=2010 with
same G=1010, then a rum through
the query would generate a S=1012 for this combination also as the min
checks for group_id in () and that will
evaluate any U belonging to a single group that is part of the multiple
groups that a U belongs to.

But I will take this query as a starting point and will work on getting
that resolved.
Thanks for your time and appreciate your help
Regards,
Madhavan
http://www.dpapps.com

On Thu, 03 Apr 2003 12:28:42 -0800, "Chelur, Jayadas {PBSG}"
<[EMAIL PROTECTED]> said:
> Madhavan,
> 
> I have created a similiar table and inserted the data
> as follows :-
> 
> =
> 
> CREATE TABLE UT
> (
> U   NUMBER(4),
> S   NUMBER(4),
> G   NUMBER(4)
> );
> 
> INSERT INTO UT VALUES(2005,1012,1010);
> INSERT INTO UT VALUES(2005,1012,1011);
> INSERT INTO UT VALUES(2006,1013,1010);
> INSERT INTO UT VALUES(2007,1017,1016);
> INSERT INTO UT VALUES(2008,1018,1010);
> INSERT INTO UT VALUES(2008,1018,1011);
> 
> INSERT INTO UT VALUES(2009,1019,1016);
> INSERT INTO UT VALUES(2001,1020,1010);
> INSERT INTO UT VALUES(2001,1020,1011);
> 
> COMMIT;
> 
> ===
> 
> this query will identify all the security groups and the
> minimum security group id of the "identical" one ...
> 
> 
> SELECT  DISTINCT
> S2.SORIGINAL_SG,/* original security group  */
> S3.SEQUIV_SG/* equivalent security group*/
> FROM(
> SELECT  S, COUNT(*) RECS
> FROMUT
> GROUP   BY S
> ) S1,   /* security groups and their group counts - table1 */
> (
> SELECT  S, COUNT(*) RECS
> FROMUT
> GROUP   BY S
> ) S2,   /* security groups and their group counts - table2 */
> (
> SELECT  DISTINCT S
> FROMUT
> ) S3/* just the unique security groups  */
> WHERE   S1.RECS = S2.RECS   /* match the sec. groups with the same record
> counts */
> AND S1.S<> S2.S /* make sure they are NOT the same security
> group*/
> AND NOT EXISTS  /* make sure they include identical group ids
> */
> (
> SELECT  G FROM UT WHERE S = S1.S 
> MINUS
> SELECT  G FROM UT WHERE S = S2.S
> )
> AND S3.S = ( /* see note */
>SELECT MIN(S)
>FROM   UT
>WHERE  G IN
>   (
>   SELECT  G
>   FROMUT
>   WHERE   S = S1.S
>   )
>)
> 
> /* note :
> this is to find the minimum value of the security id which has the same
> group
> id records as that any of the matching security groups. this minimum
> value
> can
> be used to update the security group ids of all other identical security
> groups
> at a later point of time
> */
> 
>

> 
> 
> you can either change the query to update all the eligible security id to
> their corresponding minimum values or generate equivalent update
> statements
> using this query and run them as a batch ...
> 
> HTH ...
> 
-- 
Madhavan Amruthur
DecisionPoint Applications

-- 
http://www.fastmail.fm - Choose from over 50 domains or use your own
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Madhavan Amruthur
  INET: [EMAIL PROTECTED]

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

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

Re: alter table add

[AK]

yes I added that column in to cursor . varified everything . There was a
synonym . I dropped and recreated synonym , but it doesn't work . Finally I
removed the default clause in newly added column and recreated sysnonym ,
which worked .
This was weired .

-ak

- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Friday, April 04, 2003 3:28 AM


>
> Another question
> Is the procedure selecting from a view on the table ?  Or a synonym to a
> view on the table ?
> -- the view not having been recreated to include the new column.
>
> Hemant
> At 12:23 AM 04-04-03 -0800, you wrote:
> >Have you updated your cursor to add the new column?
> >
> >John
> >
> >-Original Message-
> >Sent: 03 April 2003 21:54
> >To: Multiple recipients of list ORACLE-L
> >
> >
> >I added a column using alter table add ...
> >i can see it from sql . can select it from sql . But if I try to use it
in
> >a pl/sql block or procedure .It can't find that column .
> >is it a bug ?
> >
> >8.1.6. hp-ux
> >
> >-ak
> >
> >
> >--
> >Please see the official ORACLE-L FAQ: http://www.orafaq.net
> >--
> >Author: Hallas, John, Tech Dev
> >   INET: [EMAIL PROTECTED]
> >
> >Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> >San Diego, California-- Mailing list and web hosting services
> >-
> >To REMOVE yourself from this mailing list, send an E-Mail message
> >to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> >the message BODY, include a line containing: UNSUB ORACLE-L
> >(or the name of mailing list you want to be removed from).  You may
> >also send the HELP command for other information (like subscribing).
>
> Hemant K Chitale
> My personal web site is :  http://hkchital.tripod.com
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Hemant K Chitale
>   INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
>
>
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: AK
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: dbshut script - why shutdown so much???

[Jeremiah Wilton]

On Fri, 4 Apr 2003, Darrell Landrum wrote:

> I once read an article in an IT publication (several years ago) in
> which the author stated that if you are still rebooting your Unix
> servers routinely, your Unix admins don't know what they're doing.
> Yes, the language was that harsh.
> I thought this was ludicrous for 2 reasons:
> 1) The author assumes no applications have memory leaks.  (Uh, yeah!)

Host boot should not be necessary to reclaim memory from an
application that leaks memory.  The process/processes that leak memory
just need to exit or be killed for that.

> 2) Users, developers, (even DBAs) make changes.  Imagine a change made
> which doesn't take affect until a shutdown.  Now, imagine the next
> shutdown 4 months later.  Now, imagine that change broke something. 
> Finding the cause could be a perilous exercise.

But that's not "routine," that's "as needed."  If you have to boot to
do something that you need to do, then booting is reasonable.  I think
the author must have been referring to routine reboots when nothing is
actually wrong, or when the solution to the problem can be achieven by
other means than shutting everything down.

--
Jeremiah Wilton
http://www.speakeasy.net/~jwilton

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

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: 8i to 9i clone

[Gogala, Mladen]

If you're talking about the RMAN cloning process, I've never tried it
but it should be possible, at least in theory. RMAN cloning needs
a) Valid backup
b) The "AUX" instance.
c) RMAN catalog.

Now, I know that 8.1.7.1 RMAN executable can use the catalog created with
9.2.0.3 
(tested personally), and of course, 9.2.0.3 database can use catalog created
by 9.2.0.3.
What RMAN executables do is to copy the database files over to the AUX
location, rename
the database by manipulating the control file and open the database. The
answer to your
question is the same as to whether 9.2 software can open 8.1.7 database.
Dunno, haven't
tried, but it should be possible because formats of the block and the rowid
are pretty
much the same.

-Original Message-
Sent: Thursday, April 03, 2003 5:19 PM
To: Multiple recipients of list ORACLE-L


List,

Has anybody cloned a 8i database and moved it over to 9i?  I want to clone a
database that is on 8.1.7 AIX 4.3 32-bit now,  move it to a different server
that is running 9iRel2 AIX 5 64-bit.  Is this possible and has it been done?

thanks,


David Ehresmann   

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

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

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: dbshut script - why shutdown so much???

[Jesse, Rich]

Oh joy!  The ABORT argument again!  ;)

Is there a way to flush table monitoring counts before the ABORT, either in
8i or 9i?


Rich

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


-Original Message-
Sent: Thursday, April 03, 2003 8:59 PM
To: Multiple recipients of list ORACLE-L


All this discussion on shutdown does motivate me to
ask why is everyone shutting down so frequently?

Almost without exception my ideal for any oracle
database is

a) never shut it down
b) if you really do need to shut it down, then use
abort
c) if you are not allowed to use abort, then use
abort-startup-shutdown

Justification for (a):
Ever looked inside  the buffer cache, library cache
etc.  Its the most complicated gobbledeegook you'll
ever see...Very smart people with very little social
skills probably wrote the code for that stuff...And
who pays for those smart people? We do, in license
fees.  Shutting your db is like saying "Hey, I love
paying that monster license, but I'm not planning on
getting any of the benefits".  Its like the classic
"We shut down during quiet time"...In just about every
db I've seen, the definition of "quiet" time (eg
Sunday night) is the time just before the really busy
time (eg Monday morning).  So by shutting down, you
get the least benefit from the various cache's when
you need it most.

Justification for (b):
Worrying about abort is tantamount to having no
confidence in your database to cope with power
outages.  If you are that low in your confidence of
Oracle, then you would have never chosen the product
in the first place.

Justification for (c):
There *are* some times when abort is not appropriate. 
Anything the might change the fundamental
structure/usage of your redo logs is the key thing
here.  With 9i, there appears to be more and more ways
of doing this (supplemental logging, log_parallelism),
so a little care is required.

Cheers
Connor
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
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).