Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question.

Asked: September 08, 2003 - 8:17 pm UTC

Last updated: October 09, 2012 - 12:41 pm UTC

Version: 9.0.2.3

Viewed 50K+ times! This question is

You Asked

I have surfed OTN for 10g information, but believe me it’s a lot of documentation :)

If possible can you just name few important features that you loved most in Oracle 10g (as a product designer or beta tester)

It will help me to read proper documentation.


and Tom said...

well, the docs for 10g are not there yet but......

a) ASM automatic storage management has caught my eye
b) ADDM -- the manageability features look awesome (automatic database diagnostic monitor)
c) new sql features are always cool -- the model clause is really cool


Rating

  (374 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

When will the database be available for playing

Tarry Singh, September 09, 2003 - 11:09 am UTC

Any ideas when would the database be available for download and also the documentation?



Tom Kyte
September 09, 2003 - 12:01 pm UTC

sometime this calendar year, when it is done :)

Sign up w/ OTN to be notified when 10g is available......

Mark J. Bobak, September 09, 2003 - 3:25 pm UTC

Tarry,

If you go to </code> http://otn.oracle.com/ <code>you can sign up to be
notified when 10g is available for download. I don't have
the exact URL handy, but I know you can, cause I just did it
today.

-Mark

We forgetting something...?

Robert, September 09, 2003 - 5:11 pm UTC

>> can you just name few important features that you loved >> most in Oracle 10g (as a product designer or beta tester)

How'bout that old gal...Ms Marvel ?


Thnaks

A reader, September 09, 2003 - 5:28 pm UTC

Thanks for your answer.


Pablo Rovedo, September 09, 2003 - 10:15 pm UTC

Tom:

Could you show us some example using the new sql model clause?. i'm waiting for the official docs to read something about it. Have you already upgraded your site to 10g?

Regards
Pablo Rovedo

Tom Kyte
September 10, 2003 - 10:48 am UTC

umm, no, i haven't upgraded to 10g.

you do not run production systems on beta software. 10g was just announced, it is in beta. I will not be using it in production until -- well -- until it is in production. And even then, i'll use 10g here when it is convienent or necessary to upgrade.




Your favourate 10g features - ASM

A reader, September 10, 2003 - 9:14 am UTC

With ASM,DBAs no longer need to manage files and drives individually. Instead, disk groups can be created consistingof disks and their assigned files. Essentialy, the combination of OMF and ASM eliminates the need for a DBA to specify the file name and location for the physical database files when creating a new database, as well as in other database operations - you simply identify the destination disk group, and oracle takes care of the rest. ASM can also be extended to support other administrative procedurees, including backup/recovery and disk management.

presentation

Dave, September 11, 2003 - 6:26 pm UTC

just wondering how your sql model clause presentation was received on Monday and how your book signing went as well

Tom Kyte
September 11, 2003 - 8:20 pm UTC

i thought it went well, but it is hard to tell from my side :)

re:presentation

A reader, September 11, 2003 - 8:49 pm UTC

I thought the presentation was great - Tom's explanation
was very clear . Tom, I felt that you could have perhaps
given us some numbers as to compare different
equivalent queries (one with and one without the
model clause.) It would have perhaps made it a little
more clear as to the performance impact of this
clause.

I also got a chance to talk to Tom and get his
autograph on my copy of his latest book - it was a
privilege indeed. And I think Tom has done a wonderful
job in his latest book (from whatever I have read so far)

Menon:)

Menon:)

I am jealous of you Mr. Menon!!! :)

A reader, September 12, 2003 - 4:21 am UTC


Ramasamy, September 12, 2003 - 9:07 am UTC

I have surfed OTN for 10g information, but believe me it’s a lot of
documentation :)


I couldn't find any 10G documentation pdf in the otn website.It will be really great if you could type the link.

Tom Kyte
September 12, 2003 - 10:47 am UTC

as stated:

well, the docs for 10g are not there yet but......


10g is announced, not shipping.

Mirjana, September 12, 2003 - 10:20 am UTC

Can you put a link to sql model clause documentation (or articles),please?

Tom Kyte
September 12, 2003 - 10:57 am UTC

ADDM and SQL Tuning Advisor

Mark, September 12, 2003 - 10:34 am UTC

Tom, I just got your new book. Very nice.

I was wondering if you could give a short overview of ADDM and SQL Tuning Advisor in 10G. I was looking at

</code> http://technet.oracle.com/products/database/oracle10g/pdf/OW_General_OracleDatabase10G_Revolution_10R1_081903.pdf <code>

How does this work?

Thanks



Tom Kyte
September 12, 2003 - 10:58 am UTC

think of it like stats pack PLUS the authors of stats pack inside the database. they are codifying their knowledge and instead of just reporting back "whats up", they are saying "whats up and here is what to do about it"

Thanks a lot!!!!!

Mirjana, September 12, 2003 - 11:08 am UTC


ow2003 whitepapers

A reader, September 15, 2003 - 4:39 pm UTC

Tom Kyte
September 15, 2003 - 8:00 pm UTC

thanks!!

9i release 3 cancelled?

juancarlosreyesp@yahoo.com, September 15, 2003 - 6:09 pm UTC

Hi Tom, now that oracle presented 10g, Oracle9iR3, is going ot be cancelled, I remember it was going to have an c compilator included, etc.

Thanks :)

Tom Kyte
September 15, 2003 - 8:08 pm UTC

9iR3 was never announced or talked about as far as I know.

the last time we had a c compiler was for the mainframe. rather then port Oracle to a "mainframe" language, we wrote a C compiler. but that was years ago....



A reader, September 15, 2003 - 8:52 pm UTC


about automatic SGA memory management

zhou yuwen, September 15, 2003 - 9:56 pm UTC

I have an oracle10g beta for Linux,but I can't find parameter SGA_TARGET,so I can't use automatic SGA memory management feature. who can I enable this new feature?

thanks Tom

Tom Kyte
September 16, 2003 - 7:47 am UTC

beta 1 didn't have it

beta 2 does

do you have beta2?

about automatic SGA memory management

zhou yuwen, September 15, 2003 - 9:57 pm UTC

I have an oracle10g beta for Linux,but I can't find parameter SGA_TARGET,so I can't use automatic SGA memory management feature. How can I enable this new feature?

thanks Tom

About automatic SGA memory management

zhou yuwen, September 16, 2003 - 10:31 am UTC

thanks Tom.

I have only oracle10g beta1.

:) Thanks

A reader, September 16, 2003 - 10:56 am UTC

I read this somewhere, but I don't remember where was that article.

SQL Model Clause

Suvarna Patki, September 19, 2003 - 4:32 am UTC

Hi Tom,

I was trying to access following link to read more on SQL Model clause. But it leads to nowhere :(

</code> http://otn.oracle.com/products/bi/pdf/10gr1_twp_bi_dw_sqlmodel.pdf <code>

Please help.

Tom Kyte
September 20, 2003 - 5:13 pm UTC

i just clicked on it -- worked for me?

(not sure where you got the link from in the first place, that and I don't manage all of OTN :)

A reader, September 24, 2003 - 4:27 pm UTC

Tom,
pls correct my understanding:
Oracle 10g grid computing deployment implies using RAC as its base component? is'nt it?


Thanks as always..

Tom Kyte
September 25, 2003 - 5:03 am UTC

that is part of the picture, yes - an optional component.

there is no "base" component. it is lots of pieces.


10g beta

Nathan, September 25, 2003 - 9:01 am UTC

Tom,

Why dont you provide your readers with a Beta copy :) .

Regards
Nathan

A reader, September 26, 2003 - 3:14 pm UTC

Tom,
a) Does sql model clause require 10g OLAP option?
b) Is the OLAP option mdb a superset of the standard rdbms?

Thanks as always

Tom Kyte
September 26, 2003 - 8:09 pm UTC

a) it won't be OLAP option no. but the feature/option set is not yet set in stone

b) don't know what you mean

Server on one CD

robert, October 01, 2003 - 1:35 pm UTC


I just heard an interview with Andy Mendelsohn and he said
the 10g server software will be on ONE CD....wow ! now that's progress !

cd or dvd?

subbu, October 03, 2003 - 3:14 pm UTC


Tom Kyte
October 03, 2003 - 3:45 pm UTC

cd

10g New Features

A reader, October 03, 2003 - 4:04 pm UTC

Guys, there is a sample chapter from the 10g New Features book -- with commentary by J. Lewis ! This is an Oracle Press book.

</code> https://asktom.oracle.com/magazine-archive.htm <code>

Lewis' note is very funny -- what do you think, Tom ?

<q>
Flushing the Buffer Cache

Prior to Oracle Database 10 g, the only way to flush the database buffer cache was to
shut down the database and restart it. This was perhaps not the most graceful way of
performing this activity because it required shutting down applications and disconnecting
users, creating all sorts of mayhem (not that flushing the buffer cache in and of itself
can’t cause some short-term mayhem of its own!).
Oracle Database 10 gnow allows you to flush the database buffer cache with the alter
system command using the flush buffer_cache parameter, as shown in this example:
Alter system flush buffer_cache;

NOTE

I am not sure why you would want to flush the buffer cache. If you
can think of a good reason why, let me know!
</q>

;)))))

Tom Kyte
October 04, 2003 - 9:33 am UTC

there are two times when it comes into play

a) you are using raw or unbuffered IO (forcedirectio on the mount option in solaris for example) and you want to time your disk speed. This is very rare.

b) you mistakenly believe that you need to benchmark with an empty buffer cache to get "real world" numbers. This "thought" occurs because your benchmark contains a single query with a single set of inputs and you cannot be bothered to simulate a real world workload. Problem is -- in real world buffer cache is neither 100% empty, not 100% full so problem is with benchmark, not with what is cached or not.


Note: in 9i, you could "alter tablespace offline/online" to "flush" the buffer cache of blocks relating to that tablespace.

This flushing command is mostly (IMO) to make people "feel better".

Unless someone can come up with a good reason for it.... besides "for benchmarking" since benchmarking should simulate reality and reality never starts with an empty buffer cache.

Hey! It was possible at least in 9i without shutting down...

Piotr Jarmuz, October 04, 2003 - 9:43 am UTC

Hello

<q>
Prior to Oracle Database 10 g, the only way to flush the database buffer cache
was to
shut down the database and restart it.
</q>



p_jarmuz@test> create table test tablespace example as select * from all_objects;

p_jarmuz@test> create index test_idx on test(object_name);

p_jarmuz@test> set autotrace traceonly
p_jarmuz@test> set timing on

p_jarmuz@test> select * from test where object_name < 'a';

23838 rows selected.

Elapsed: 00:00:02.56

Execution Plan
----------------------------------------------------------
0
SELECT STATEMENT Optimizer=CHOOSE


1 0
TABLE ACCESS (BY INDEX ROWID) OF 'TEST'


2 1
INDEX (RANGE SCAN) OF 'TEST_IDX' (NON-UNIQUE)






Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
18270 consistent gets
327 physical reads
0 redo size
1575443 bytes sent via SQL*Net to client
17978 bytes received via SQL*Net from client
1591 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
23838 rows processed


So we see 327 physical reads.

One more time to "warm up" the buffer cache



p_jarmuz@test> select * from test where object_name < 'a';

23838 rows selected.

Elapsed: 00:00:02.48

Execution Plan
----------------------------------------------------------
0
SELECT STATEMENT Optimizer=CHOOSE


1 0
TABLE ACCESS (BY INDEX ROWID) OF 'TEST'


2 1
INDEX (RANGE SCAN) OF 'TEST_IDX' (NON-UNIQUE)






Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
18270 consistent gets
0 physical reads
0 redo size
1575443 bytes sent via SQL*Net to client
17978 bytes received via SQL*Net from client
1591 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
23838 rows processed


See this time ZERO physical reads all blocks were fetched from buffer cache...


Now let's set tablespace offline and back online again...

p_jarmuz@test> alter tablespace example offline;

Tablespace altered.

Elapsed: 00:00:00.13

p_jarmuz@test> alter tablespace example online;

Tablespace altered.

Elapsed: 00:00:00.06


And one more time our query:


p_jarmuz@DEVDB.ASCO.DE> select * from test where object_name < 'a';

23838 rows selected.

Elapsed: 00:00:02.60

Execution Plan
----------------------------------------------------------
0
SELECT STATEMENT Optimizer=CHOOSE


1 0
TABLE ACCESS (BY INDEX ROWID) OF 'TEST'


2 1
INDEX (RANGE SCAN) OF 'TEST_IDX' (NON-UNIQUE)






Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
18270 consistent gets
327 physical reads
0 redo size
1575443 bytes sent via SQL*Net to client
17978 bytes received via SQL*Net from client
1591 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
23838 rows processed

This time all blocks were read from disk again or really from OS buffer cache as I did not flushed it and elapsed time also proves it
It is basically no different. I flushed buffer cache elapsed time after the flush would be much, much longer

I learnt "this technique" from Oracle's Magazine and Tom's article.

Regards

Piotr

Oops... Tom was faster as always... :-)

Piotr Jarmuz, October 04, 2003 - 9:44 am UTC

Regards

Piotr

10G features

Mark, October 07, 2003 - 1:26 pm UTC

Tom,

Can talk about Tablespace Groups and why you would want to use them verses a single large temp tablespace?

Thanks


Tom Kyte
October 07, 2003 - 1:34 pm UTC

I'll punt as I haven't tried them out myself yet. this is from the docs so far:


....
Using a tablespace group, rather than a single temporary tablespace, can alleviate problems caused where one tablespace is inadequate to hold the results of a sort, particularly on a table that has many partitions. A tablespace group enables parallel execution servers in a single parallel operation to use multiple temporary tablespaces.
.........

I don't necessarily see the benefit of the first one so much as the second point -- to reduce contention and spread the work load out (note: it would be true of multiple sessions all sorting at the same time as well)

10g features

apl, October 09, 2003 - 5:08 am UTC

i am am oracle8i certifeid professional. i am preparing for upgradation exam in oracle9i.
Yesterday i read about oracle 10g features. in 10g tuning,backup and recovery, cloning,performance monitoring.. all can do by the database. means there is little role for dba.
i heard that this can make organisation profitable and mke the reource utlise for other jobs. So my question is what will be our role. only in the application dba side.means database design and query writing.
now i am thinking seriously, is it necessary for an upgrade. since the role of dba is coming down.

Tom Kyte
October 09, 2003 - 4:37 pm UTC


Have you (as a DBA) been asked recently to

a) managed smaller databases (or do they seem to be getting larger)

b) databases with less users (or do they seem to be getting larger)

c) databases with more uptime requirements (or do they seem to be saying "it's ok for the db to be down whenever, we don't care")

d) databases with more applications (or have they been getting rid of applications, going back to simplier times)


What I mean is -- are you being asked to to MORE or LESS over time? If being asked to do more (that is what I'm supposing people will be saying, it is what I hear) -- are they increasing your staff? Or are you being asked to do more with what you have? Do you want some weekends off occasionally?

Here is my take on it. Some (many) things are easier to manage. Its a relief. We don't have to worry about that "unfixable 3rd party query that cannot be changed" in 10g because of a thing called a sql profile. We don't have to worry about rebalancing our IO when given a new mount point (taking files offline, moving them, bringing them back on). We can concentrate on everything else you need to do -- secure it, manage it, keep it up, patch it, test it, configure it and yes -- tune it. It is more self managing -- it is not going to fire you.

How to answer management

A reader, November 14, 2003 - 2:34 pm UTC

The more management hears about 10g, the more I (DBA) am hearing "with 10g, we don't need DBA's. The database takes care of itself". How can I answer that?


Tom Kyte
November 15, 2003 - 8:46 am UTC

are you being told any of the following:

a) we'd like you to manage smaller databases (and less of them)
b) we'd like you to manage less applications running on these databases
c) we are going to start lowering the number of users you need to support that use
these applications
d) it is OK to be down or suffer from poor performance whenever. thats not a
problem.


OR, are you hearing:

a) your databases are going to grow (and you have more of them)
b) you'll have more apps running on them
c) your user base is going to double
d) if you let these databases go down for a minute, or they start running slow
I want you in my office to explain why
E) OH, YEAH, YOU CANNOT HIRE ANYMORE PEOPLE TO HELP


I hear the second set of requirements, not the first. We are treading water as it is - we need a little help here. Do you really like moving files around to tune IO? finding the high load sql and figuring out what index the developers forgot to tell you about?

Antony, November 19, 2003 - 2:46 pm UTC

Has the number of bugs come down in 10g ?

Almost every new feature of oracle has quite a few bugs associated with that whether it is partitioning or materialized view or cursor_sharing or automatic segment management or parallel execution ....So one hesitates before implementing a new feature, even though some of them are quite beneficial

FLASHBACK

A reader, November 26, 2003 - 3:30 pm UTC

Hi Tom, one question I read there will be a fulldatabase flashback,
the question is
can you go to a full database flashback permanently,
this is like restoring a backup (if you didn't do ddls)?

I think this will be nice, if you didn't execute any ddl command you will can to do a restore backup with a simple command, very useful for our restore needs.

Tom Kyte
November 26, 2003 - 3:36 pm UTC

that is exactly what it is, yes.

Thanks

A reader, November 26, 2003 - 3:42 pm UTC


but still missing ...

Aino, November 27, 2003 - 4:20 am UTC

I am interested in the pl/sql features. Looking forward to regular expressions and improved collection manipulations.
But my most wanted feature of the db, the boolean datatype for a column, is still missing :-((

MMON

Reader, December 22, 2003 - 2:17 pm UTC

Tom, is MMON a manadatory background process in 10g? If it dies, will it crash the instance or will it alert? Thanks.

Tom Kyte
December 22, 2003 - 2:33 pm UTC

they are part of as far as I know -- and when I killed them, they got restarted -- so, if they die, they are restarted for you.

Re: How to answer management

A reader, December 22, 2003 - 2:46 pm UTC

What I'm hearing is "10g automatically takes care of everything that you are doing so we won't need a DBA if we upgrade to 10g. The database is self tuning and self administering."

Tom Kyte
December 22, 2003 - 2:51 pm UTC

umm, yeah, right.

look upwards to "How to answer management"

10g Stole My Job

Mark A. Williams, December 22, 2003 - 3:53 pm UTC

Many interesting and useful features - but I have not yet seen:

prescient_mode = TRUE

I mean what features are seriously going to eliminate the need for a DBA? Sure, some things will be easier to do and require less "busy work", but will that actually eliminate the need for a DBA? I don't mean to sound funny about it, but no one has yet told me about a new feature that has me worried about job security.

insert into bit_bucket values(.02);

- Mark

Re: 10g Stole My Job

A reader, December 23, 2003 - 8:22 am UTC

Well, you know that, and I know that, unfortunately some management doesn't know that. They'll eliminate their DBA's and only find out later that they've gotten themselves into a huge mess. OK by me, because then they can hire me on to dig them out at big $$$. :-)

Tom Kyte
December 23, 2003 - 11:33 am UTC

i cannot imagine anyone would make staffing decisions based on something they've read in a magazine, not seen for real.

Data pump

Reader, December 23, 2003 - 12:21 pm UTC

Tom, in 10g is data pump a replacement for the ever popular exp/imp? How is expdb and impdp utilities differ from exp/imp utility? Thanks.

Tom Kyte
December 23, 2003 - 4:53 pm UTC

data pump is the "next generation" yes. the venerable and ancient exp/imp still exist.

data pump can do things like

o parallelize
o direct path imports
o server to server transfer (look ma, no disk)

for example.

Imagine

Jim, December 23, 2003 - 5:24 pm UTC

Tom,
Dilbert is alive and well. Sometimes I have gotten an eerie feeling that Scott Adams must have been spying on companies I have worked for. There definately are management types that will reduce head count because:

1. People are pluggable.
2. Oracle says they have automated and made their database easier to administer.
3. They lack the wisdom and understanding of the purpose of DBAs.
(eg they think they rebuild tables and indexes to get them into one extent; fortunately Oracle says we don't need to do this anymore so we can get rid of the DBA.)

I thank my lucky stars that at least my immediate boss is more enlightened!

By the way does Oracle make a mauve database? I hear mauve databases are the best; they use the most RAM.

Happy holidays to you and everyone who peruses your site.

Tom Kyte
December 23, 2003 - 5:46 pm UTC

i'm fond of the red myself. it goes much faster then the other colors, just like the red cars.

FLASHBACK

A reader, December 23, 2003 - 5:31 pm UTC

For that kind of FLASHBACK how much undo we need... 1TB? :-)

A reader, December 24, 2003 - 9:25 am UTC

I don't think 1 tera is too much, If think you don't have clear the way flashback works.
here I got an excelente article ;)
</code> http://www.geocities.com/juancarlosreyesp/OracleAskTom.pdf <code>

It should work like now works, but with all tables at once, and it will read only the info it need, so the speed should be very similar than the one you got in 9i when you do a flashback, or better.


Merry Chrismas, and thanks to Tom
and to every one.
My presente

--------
/:)/:) / |
------- | |
| | | |/|
|-----|/|/
| | | /
-------

I don't have too much time to get a better one.

A reader, January 08, 2004 - 4:39 pm UTC

hi tom,

we are on 8.1.7 release right now.

it looks like (announced) that 10g(9.3 ?) is going to be released soon(6 months from now).

should we wait for 10g and migrate from 8i --> 10g or we wait for 10g to get stable, normally we wait 1 year to get
the softwere stable (patch and all.) My other concern is that 10g verion is 9.3.x(correct??), so is 10g full relase or an incremental to 9i. Should I worry?

if 10g is going to be around soon. we don't really want to move to 9i if possible !! because right now 8i is stable .

please give your advise.

Tom Kyte
January 08, 2004 - 8:22 pm UTC

10g is going to probably be 10.1.0.2 (that is what it is now).

9iR2 is very stable as well.

It is hard to tell you what to do. Depends lots on needs and goals.

A reader, January 09, 2004 - 1:08 pm UTC

I know 9ir2 is stable. A large issue with me is that 9i relase schedule is alot like mirosoft's release cycles(To Fast). I would go to 9ir2 today, but I do not want to upgrade to 10g in a year. It is an expesive propersition in man hours to fully test and implement. My goal is to have a release that is supported by Oracle, No major bugs. There are features in 10g that would be on my wishlist, but now entirely needed. My business needs for oracle 9 or 10 is none. The businesses people do not care. This is an entirely technolgy reason.

Tom Kyte
January 09, 2004 - 2:24 pm UTC

guess it will come down to

a) do you want to upgrade in the next 4 months. 9i is your most likely candidate

b) do you want to upgrade 6 or more months from now, 10g will be something to seriously consider

c) in between 4/6 months -- hard to say.

Upgrade - Oracle 9 or 10 ?

Timothy, January 09, 2004 - 3:33 pm UTC

The previous poster mentioned wanting a stable release supported by Oracle.

Well - if so - that pretty much would answer the question - no?

Oracle has listed 8.1.7 as being desupported as of 12-31-03.

So, if you want a database fully supported by Oracle - and 8i is no longer supported and 10 is not available yet - the answer would be to go with 9.

Upgrade - Oracle 9 or 10 ?

Timothy, January 09, 2004 - 3:52 pm UTC

My bad - I just re-read the Oracle desupport notices. 8.1.7 is being desupported on 12-31-03 AND 12-31-04 --- depending on the platform it runs on. So, depending on your platform, you may be fully supported on 8i by Oracle for another year.

Metalink articles: 148054.1 & 250629.1

Could you provide a link to 10g online document? Thanks.

Reader, January 28, 2004 - 3:38 pm UTC


Tom Kyte
January 29, 2004 - 7:38 am UTC

not yet, as soon as it is released, it'll be right there on otn with the rest.

10g Release Date

10g Man, January 30, 2004 - 7:51 pm UTC

Check otn.oracle.com in the next few days. According to Chuck, 10g Linux was due out today and Windows 30 days from today. I would bet it will be online next week ..



Releases

Sanjaya Balasuriya, February 03, 2004 - 5:47 am UTC

Tom,

I saw that Oracle 10g has been released today.
But it has been released only for HP-UX and Sparc.

Why it's not for other OSs ?



Tom Kyte
February 03, 2004 - 7:54 am UTC

they are coming day by day.

Is 10g a replacement for 9i, or something completely new?

Nasir Sarwar, February 03, 2004 - 3:44 pm UTC


Tom Kyte
February 03, 2004 - 6:19 pm UTC

10g is the next release of the database following in the footsteps of

7.0
7.1
7.2
7.3
8.0
8ir1
8ir2
8ir3
9ir1
9ir2
and now.... 10g release 1.

What is the first release number of Oracle Database?

Suresh Govindarajan, February 04, 2004 - 1:13 am UTC

Probably I think version 5 is the first release of oracle Database. There should be some reason your list started with version 7.0. By the way I am a regular reader of your site for the past 3 years and learned a lot. I really admire the way you present the answers. Thanks.

Tom Kyte
February 04, 2004 - 7:26 am UTC

I had to start somewhere, i just did the last 11 years -- I'd have to go back to version 2 in order to be "complete"


see
</code> https://asktom.oracle.com/Misc/oramag/oracle9i-looks-past-initora-and-sys.html <code>

for a 'history'

Oracle 10g download

Johan Snyman, February 04, 2004 - 2:02 am UTC

I see Oracle 10g is now avaialble for download from OTN, but only for Solaris (64bit) and HU-UX. Do you perhaps know when the release for Linux and Tru64 Unix will be available for download - I would like to start testing as soon as possible.

Tom Kyte
February 04, 2004 - 7:23 am UTC

soon. just keep checking back. (see above -- this was just asked...)

10g availability

A reader, February 04, 2004 - 4:12 am UTC

I was told by Oracle last week that Linux would be available this week and windows 'would be some time later'

9i and 10g on the same server

Arun Gupta, February 04, 2004 - 4:35 pm UTC

Tom,
We have development server (Solaris 64 bit) with 9.2.0.4 installed. Can we do a test install of 10g in a different Oracle home on the same server without impacting anything else?
Thanks


Tom Kyte
February 04, 2004 - 6:05 pm UTC

I've got 7.x, 8.x, 9.x, 10.x all on the same server. works dandy (as long as I don't start too many of them at once :)

what about 11x?

D.B.Nemec, February 04, 2004 - 5:31 pm UTC

If you add a release year to the history of main versions of Oracle DB you receive a nice sequence:

7 1992
8 1997 i.e. 5 years later
9 2001 i.e. 4 years later
10 2004 i.e. 3 years later

consequently the version 11x should by released in 2 years in 2006 (i.e. announced in 2005).

J.Nemec


Tom Kyte
February 04, 2004 - 6:17 pm UTC

;)

Good obervation Nemec.

A reader, February 05, 2004 - 1:02 am UTC


10g release

Jorre, February 05, 2004 - 2:34 am UTC

I have this in my crontab (every 5 minutes)
wget </code> http://otn.oracle.com/software/products/database/oracle10g/index.html <code>|grep "for Linux"


Just downloaded Linux version

A reader, February 08, 2004 - 11:16 pm UTC

The wait over...It's here.....

Downloaded Oracle 10g

praveen sehgal, February 09, 2004 - 2:41 pm UTC

Downloaded 10g but it won't install on redhat9 :(
Speaking of platform independency...will 10g ever install on Redhat9 ???

Tom Kyte
February 09, 2004 - 2:51 pm UTC

No -- redhat 9, an end user desktop version of RedHat, a discontinued line actually is neither of

RHAS2.1
RHAS3.0

the "server" platforms where people expect to be able to install, configure and run database "server" software.


This would be like installing a server product on "Windows ME" or something. Just not "appropriate"


Redhat9

Praveen Sehgal, February 09, 2004 - 3:26 pm UTC

Tom is it not possible and even smart to have OS dependency at Kernel level as opposed to Distro level. I would love to create my own optimized distro of Linux that runs Oracle and get support from Oracle since my distro is based on a certain version of Linux Kernel.

Now Oracle always installed on Redhat9 or many other distros for that matter now all of a sudden we remove that compatibility..it just throws us off.

I would really like to know your thoughts on why Oracle would not or can not be distro independent.

Tom Kyte
February 09, 2004 - 4:17 pm UTC

this is called "a database"

it is a bit more to the wire then say, oh, a word processor.

the thought here is "you want the data to be reliably stored and retrieved", there is but one way to do that -- on a standard, expected, server platform.

This is for SERVERS.

This is not for 'end user desktop' platforms. Never has been.

Open Source, Linux, et.al. does not mean the practices of the past are obsolete, to be forgotten, etc. Just as there was never Oracle for windows me, or the other desktop only platforms -- there will not be for (a now obsolete, dead by the way, why bother supporting) new desktop only platforms. It makes no sense to install a server on such a platform.

We support a finite set of platforms and distributions (guess that is what "distro" is). Just like Ximian for example -- the desktop guys -- they won't intstall on AS.


Linux

Dave, February 09, 2004 - 3:44 pm UTC

it's perfectly possible to install 10g on Redhat 9 (or any version of Linux)

You just need to know what you are doing ...

Tom Kyte
February 09, 2004 - 4:26 pm UTC

revise that to

"you just need to know how to hack the scripts, fake out the libraries, blah blah blah"

it is not "intuitively obvious", I gave up on that path long long ago for the cascadiing downstream "side effects" (eg: I could never get 

SQL> shutdown immediate
SQL> startup <<<==== would always fail
SQL> startup <<<==== this second one would work

on rh8.0 with 9ir2.  You can imagine what a pain that would be during say, a patch upgrade? or just normal scripts day to day.....

if you just want to play -- go ahead, give it a go, but don't expect "truly amazing things".  

 

Redhat9

Praveen Sehgal, February 09, 2004 - 3:55 pm UTC

Perhaps I missed something Dave, but in 10g first thing that the Installer does is check the Linux Versions and it quits operation if you are not running Redhat 2.x 3.x or United linux. It does not proceed with installation on Redhat9. I've tried this personally and checked the installer code. Unless there's a workaround......

Maybe you can share how you made it work.

Tom Kyte
February 09, 2004 - 4:22 pm UTC

it can be made to work -- but it is neither supported, nor recommended. You'll have cascading problems down the road....

I'm happily using RHAS3.0 on my laptop's and desktops (actually nicer then rh7 and rh8 were -- since rh9 was the end of the road -- decided to go with the product line that actually has life in it)

A reader, February 09, 2004 - 4:50 pm UTC

To be fair, its been pretty stable for a day :-).

Installed easier than 9i did on RedHat9 as well (and a lot quicker). I only want it for playing and basic testing, I wont run anything major on it.

I also dont have Redhat2 or 3 and dont want to run it on Windows - so I'm kinda stuck. Also dont have a spare Solaris server kicking about.

If it doesnt work - so be it, ill give up. I didnt have to mess about with libraries either (wouldnt of gone near it if i did). I wont post how its done because its not fair on others - but it works for me

Tom Kyte
February 09, 2004 - 8:09 pm UTC

you can post -- i'm sure it'll be out there on the web any second now ;) There is a site dedicated to doing this sort of stuff even (at least one)

install 10g in redhat 9 or whatever

A reader, February 09, 2004 - 5:18 pm UTC

A reader, February 09, 2004 - 7:26 pm UTC


10 bug ?

A reader, February 10, 2004 - 11:35 am UTC

hi tom, I installed 30-day version of 10g for solaris 5.8
(added all required patches described in installation guide)
oracle version : 10.1.0.2.0 

-------------------------------------------------------
my question :
 please look at the query listed below.
in 
query#1 : I select all the columns and 
          I get one row(s) back.
where as in 
query#2 : I count the rows and it returns 0
         expacted answer is 1
Note : The From clause and the Where clause in the query is identical.

--- Please comment. it is me or 10g ? 
--------------------------------------------------
bash-2.03$ sqlplus my10g/my10g

SQL*Plus: Release 10.1.0.2.0 - Production on Tue Feb 10 11:27:07 2004

Copyright (c) 1982, 2004, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> set lines 120
SQL> set pages 40
SQL> set feedback on
SQL> set echo on
SQL> set termout on
SQL> 
SQL>   
SQL>    SELECT  I.INST_cODE   ,I.INST_IS_LIVE  ,NVL(I.INST_LAST_UPDATED,SYSDATE) , 
  2                       I.INST_PARENT_CODE,I.INST_RGN_CODE,I.INST_RGN_NAME 
  3      FROM institution_v40 i,institution_v40 P ,
  4              (SELECT *  FROM alias_v40 WHERE alias_aliastype_code = 'INAB') a ,
  5                      (SELECT site_inst_code, site_code, site_name, 
  6                                      GREATEST (site_last_updated, sr.site2siter_last_updated) AS s_ts
  7               FROM site_v40,(SELECT * FROM site_to_site_role_v40  
  8                                                      WHERE site2siter_siter_code = 'PRMRY' 
  9                                                      AND site2siter_is_live = 'Y') sr
 10               WHERE site_code = sr.site2siter_site_code
 11                      ) s
 12     WHERE  1 = 1
 13     AND i.inst_parent_code = p.inst_code(+) 
 14     AND UPPER (i.inst_name) LIKE UPPER ('%test%')
 15      AND i.inst_code NOT IN (SELECT i2ir.inst2instr_inst_code
 16                               FROM inst_to_institution_role_v40 i2ir
 17                              WHERE inst2instr_instr_code = 'CDSRE')
 18     AND i.inst_code IN (SELECT i2ir.inst2instr_inst_code
 19                           FROM inst_to_institution_role_v40 i2ir
 20                          WHERE (   inst2instr_instr_code = 'CUSTR'
 21                                 OR inst2instr_instr_code = 'BRKRG'))
 22       AND i.inst_code = a.alias_entity_code(+)      
 23       AND UPPER (a.alias_name) LIKE UPPER ('%test%')
 24          AND i.inst_is_live = 'Y'
 25              and i.inst_code = s.site_inst_code(+);

INST_CODE   I NVL(I.INS INST_PARENT INST INST_RGN_NAME
----------- - --------- ----------- ---- --------------------------------------------------
I0000003915 Y 06-FEB-04             ASIA Asia

1 row selected.

SQL>     
SQL>  SELECT  count(*)  --I.INST_cODE   ,I.INST_IS_LIVE  ,NVL(I.INST_LAST_UPDATED,SYSDATE) , 
  2  --                   I.INST_PARENT_CODE,I.INST_RGN_CODE,I.INST_RGN_NAME 
  3      FROM institution_v40 i,institution_v40 P ,
  4              (SELECT *  FROM alias_v40 WHERE alias_aliastype_code = 'INAB') a ,
  5                      (SELECT site_inst_code, site_code, site_name, 
  6                                      GREATEST (site_last_updated, sr.site2siter_last_updated) AS s_ts
  7               FROM site_v40,(SELECT * FROM site_to_site_role_v40  
  8                                                      WHERE site2siter_siter_code = 'PRMRY' 
  9                                                      AND site2siter_is_live = 'Y') sr
 10               WHERE site_code = sr.site2siter_site_code
 11                      ) s
 12     WHERE  1 = 1
 13     AND i.inst_parent_code = p.inst_code(+) 
 14     AND UPPER (i.inst_name) LIKE UPPER ('%test%')
 15      AND i.inst_code NOT IN (SELECT i2ir.inst2instr_inst_code
 16                               FROM inst_to_institution_role_v40 i2ir
 17                              WHERE inst2instr_instr_code = 'CDSRE')
 18     AND i.inst_code IN (SELECT i2ir.inst2instr_inst_code
 19                           FROM inst_to_institution_role_v40 i2ir
 20                          WHERE (   inst2instr_instr_code = 'CUSTR'
 21                                 OR inst2instr_instr_code = 'BRKRG'))
 22       AND i.inst_code = a.alias_entity_code(+)      
 23       AND UPPER (a.alias_name) LIKE UPPER ('%test%')
 24          AND i.inst_is_live = 'Y'
 25              and i.inst_code = s.site_inst_code(+) ;                 

COUNT(*)--I.INST_CODE,I.INST_IS_LIVE,NVL(I.INST_LAST_UPDATED,SYSDATE),--I.INST_PARENT_CODE,I.INST_RGN_CODE,I.INST_RGN_NA
------------------------------------------------------------------------------------------------------------------------
                                                                                                                       0

1 row selected.

SQL>  

Tom Kyte
February 10, 2004 - 3:46 pm UTC

if you give me

a) the smallest
b) yet 100% complete
c) reproducible (so I can run it myself)

testcase, I'd be glad to take a look and file a bug if needed.

a) is very very important -- cut it down, remove columns from table creates, supply only that which is necessary to reproduce the issue.


results with 9i

A reader, February 10, 2004 - 11:42 am UTC

another test i did is I ran this query with 9i R2
on solaris5.8 64 bit and it produces correct results.


we are testing 9i and 10g as we are on 8.1.7 .
if 10g is stable we directly want to go to 10g and skip 9i.
possible ? your thoughts ?

Tom Kyte
February 10, 2004 - 3:47 pm UTC

definitely possible.

Dave, February 10, 2004 - 4:23 pm UTC

what is worthwhile though is learning all the new features on 9i as well to get a full understanding ...

From 8.1.7 to where?

Olga, March 01, 2004 - 4:20 am UTC

Hi Tom,

my client wants to migrate from 8.1.7 to 9ir2 in a few months. They think it could be a risk to migrate to the newest 10g. It's not a a very big Database (perhaps 10 GB) and they don't use a lot of "complicated" features. So why don't try the newest release?

What's your opinion?

Bye,

Olga



Tom Kyte
March 01, 2004 - 8:08 am UTC

read up a couple, we talked about this briefly.

asm

reader, March 07, 2004 - 4:07 pm UTC

To use ASM in 10g, what should one do as a DBA? Using ASM a default feature in 10g? Has Oracle incorporated the software to manage ASM as part of database software? Thanks.

Tom Kyte
March 07, 2004 - 4:50 pm UTC

one would setup and configure disk groups. using asm is an option -- the default is still "plain old files". yes, asm is part of the database feature set itself. Enterprise manager is your gui, sqlplus your command line to manage.

10g

Dave, March 07, 2004 - 5:25 pm UTC

i am curious - now that you have upgraded to 10g here - what new features are you using or did you just migrate the core db and stay as you are.

Tom Kyte
March 08, 2004 - 7:46 am UTC

we just migrated... (and picked up the plsql feature of being faster)

everything in steps.

we migrated so we can start using new features

10G for Mandrake Linux

A reader, March 07, 2004 - 7:50 pm UTC

Dear Tom,

Any idea when 10g will be available for Mandrake Linux(9.0)

Dave, March 08, 2004 - 4:09 am UTC

Only Linux supported is Redhat AS 2.1 and 3 and United Linux.

Anything else is unsupported

10g management

Arun Gupta, March 08, 2004 - 11:46 am UTC

Tom,
My understanding is that for a 10g database, there are two GUI management methods, one using Database Control and other using the Grid Control. The Database Control is like a self contained OEM with the repository residing within the database to be managed (sysaux tablespace?) whereas the Grid Control is the real Enterprise Manager. The Grid Control is to be installed separately from the server software (as it is available as a separate 3 CD download from OTN). The Database Control is installed by default with the server software and any database can be configured to be managed by Database Control with minimum configuration.

Is this understanding correct?
Thanks.


Tom Kyte
March 08, 2004 - 2:20 pm UTC

correct - dead on. dbconsole comes with the db, EM is a separate "thing"

No more stats gathering - no, wait...

Neil, March 09, 2004 - 5:50 am UTC

Tom,
I just noticed from the 10g performance tuning guide that stats can be automatically gathered. Before I celebrated, further reading revealed that it may not be the optimal way to gather stats if the tables are either volatile or growing by more than 10% (due to a load or frenzied activity during the day). In this case, one is advised to revert manual stats gathering. It struck me that this could be a headache in a hybrid system: if automated stats gathering is enabled, exceptions must be made for any table greatly affected by batch processing (three batch runs per 24 hours following the sun), and still some kind of script is needed to make sure those tables are properly analysed.
What would be the most pragmatic and common sense approach to stats gathering for a system like this moving to 10g?

Tom Kyte
March 09, 2004 - 12:11 pm UTC

why would this be a 'separate script', would not the obvious place be "the batch process itself". The batch (it would seem) would have sufficient information to know that "hey, my last step might be a gather or even a SET (to avoid a gather).

Is 10g available only for 64 bit unix platform? Thanks.

reader, March 20, 2004 - 2:15 pm UTC


Tom Kyte
March 21, 2004 - 9:51 am UTC

I'm running it on 32bit linux and it is there for 32bit windows as well.

it is not on 32bit solaris yet if that is what you mean specifically, hp and aix only do 64bit now.

Performance with 10g

A reader, March 22, 2004 - 12:33 am UTC

Ask Tom seems to be a bit slower now a days. Or is it because of my network. Can you extract your famous performance log report and check :-)


Tom Kyte
March 22, 2004 - 6:52 am UTC

It used to generate pages in 1 unit of time with 9i.

It is now generating pages in 0.7 units of time with 10g. (eg: faster)

can you time a transfer of a presentation or something off of the site and see what sort of Kbs you get? (see home page for link)

Performance with 10g

A reader, March 22, 2004 - 11:15 pm UTC

0.98 MB in 49 secs
Got around 30-35 KB/sec (kept changing). Anyway it might be my network :-)
(As my code is also in production, I have realized it is great to be prepared for such queries - i.e by instrumenting the code and keeping the performance history in a table)

Oracle 10g and forms 6i

Nikunj, March 23, 2004 - 4:48 am UTC

Dear Tom,

I had installed oracle 10g ee on windows 2000, it works fine but when i try to compile my exsting forms 6i form after connecting to 10g server on my some pl/sql block i get below error message ora-00600 internal error code, arguments [17069],[57985992],[],[],[],[],[],[].

Same form after get connect to oracle 9.2 ee it works fine.

Regards,

Tom Kyte
March 23, 2004 - 7:16 am UTC

please contact support.

What is OC4J installation?

reader, April 22, 2004 - 7:30 pm UTC


Tom Kyte
April 23, 2004 - 10:15 am UTC

Oracle Containers for Java = OC4J

a j2ee runtime engine.

datapump

reader, April 23, 2004 - 12:04 pm UTC

I am trying to understand the data pump in 10g. If I want to use data pump, I should have created a directory for dump files at the OS level only then I can use data pump. Is it a fair statement? Does it mean that dump files should exist on the server and they cannot be on the clients?

Tom Kyte
April 23, 2004 - 2:01 pm UTC

the dump file will go where ever the data pump is run. expdp can only write to file systems it can actually "see" -- or it can write to impdp over the network to another database.

asm

Sam, April 23, 2004 - 12:58 pm UTC

Is the allocation unit (doc says 1MB) the same as stripe size for files in ASM? Is there a way to change it? Thanks.

Tom Kyte
April 23, 2004 - 2:25 pm UTC

the A in ASM stands for auto. Auto is meaning "very little configuration (else it wouldn't be "auto" anymore)"

it is 1m and 128k - 128k used for some files like control files, 1m for other stuff.

I'm not aware of a (documented) manner of changing it. If you believe you really need to change it, you can contact support.

asm

Sam, April 23, 2004 - 5:56 pm UTC

(1) Fine that it is an automatic process. However, DBA needs to learn how to configure disks to be part of ASM disk groups. For example, on UNIX platform what is the command one should use to configure disks to be part of ASM disk groups?

(2) On Windows for example on my laptop, can I specify C:\ or D:\ as disk groups?

Thanks. You are the best.

Tom Kyte
April 23, 2004 - 7:43 pm UTC

1) you give the raw devices to asm.  you just give the stuff to it.  you do not create logical volumes.  you do not create file systems.  You just give the devices straight to asm.

2) at OS level you need create logical partitions without formating to be used by ASM. 

in order to discovery the asm disk properly you may 
need set-up the parameter:  osm_diskstring='\\.\*:'

Or if you like after create the ASM instance you can use ASM commands to
create a disk group with mirror using the previous partitions:

SQL> CREATE DISKGROUP dg_pc Normal REDUNDANCY
     FAILGROUP GF2
     DISK '\\.\H:' NAME C2D1  SIZE 2000M ,
          '\\.\I:' NAME C2D2  SIZE 2000M
     FAILGROUP GF1
     DISK '\\.\D:' NAME C1D1  SIZE 2000M ,
          '\\.\G:' NAME C1D2  SIZE 2000M


 

Thanks

sam, April 23, 2004 - 7:48 pm UTC


Flashback Database

David, April 23, 2004 - 8:33 pm UTC

My understanding is that Flashback database needs flashback log file in which oracle saves the block images. If that is the case,if I truncated a table, does oracle keep all of the block images in the flashback logs so that one could flashback the database to retrieve the rows? A table may contain thousands of blocks, does oracle keep all of the block images in the flshback logs? Thanks.

Tom Kyte
April 26, 2004 - 4:45 am UTC

it would not need to at the point of time of the truncate.

when you first turn on the flashback log -- "nothing is there", yet could could -- 5 minutes after enableing it -- flash back your entire instance.

You see, the before images in the log are added over time. After you turn it on -- Oracle will start putting before images in their during a checkpoint (not all -- we just need an "old enough block", not every checkpointed version of a block over time).

So, enable flashback - truncate a table. Nothing really needs to go into the flashback log as yet. As you reallocate that space and someone reuses the block -- we'll copy out the old stuff before checkpointing (if needed -- we might already have an "old enough" block out there in the flashback log).

The truncated data isn't really any different than a "regular" table.

recovery thru resetlogs

Charles, April 24, 2004 - 6:57 pm UTC

10g doc says that one does not have to perform whole database backup after the database is open with resetlogs. I was wondering what is the new concept that would allow us to use the backups taken before resetlogs? Thanks for your time.

Tom Kyte
April 26, 2004 - 5:43 am UTC

you can do it in 9i as well too (rman permits it).

you should backup right after resetlogs regardless. the process is "not as easy". it is there in the event the disk fails AFTER the open, but BEFORE the backup completes.

gathering stats

Chris, April 27, 2004 - 7:00 pm UTC

(1) Is it true that in 10g one does not need to schedule a job to gather stats for schema objects as the database automatically does it for us? If so, is the use of DBMS_STATS package limited in 10g?

(2) I was reading the doc about GATHER_STATS_JOB that automatically collects stats for all objects. How do I find out what this job actually does and what script it uses to do the same.

Thanks.

9i Enterprise features enabled in 10g standard edition

A reader, May 12, 2004 - 2:47 pm UTC

1. Hi Tom, I can't find a document clearly showing previous 9i enterprise features made available to 10 stndard edition.
Like function indexes was only in enterprise in 8i.

I'll need good arguments to justify a migration to 10g.
otherwise we will have to wait to 11c to migrate.

2. Seriously talking, next database release will be availabe I think in 3 years?.

Please if you can :)


Tom Kyte
May 13, 2004 - 9:12 am UTC

1) there is a feature function matrix with each release - however, I'm not aware of any document that says "this was in ee and is now in se" like that.

</code> http://docs.oracle.com/docs/cd/B13789_01/license.101/b13552/editions.htm#sthref24 <code>

3) I've been at Oracle for going on 11 years. In 11 years I've seen
7.0, 7.1, 7.2, 7.3, 8.0, 8ir1, 8ir2, 8ir3, 9ir1, 9ir1, 10gr1

I fully expect that by the time I'm here 12 years, there will be another name in that list.

first 10gr1 patchset due out june 2004, next 10g -- sometime next year.

A reader, May 13, 2004 - 9:53 am UTC

Thanks Tom,
I'm trying to see good reason to go from 9i se to 10g se, and I'm trying to see if there is a very useful improvement for us, to convince to migrate it.

the reason for my question is because
in
</code> http://otn.oracle.com/products/database/oracle10g/pdf/twp_general_10gdb_product_family_0104.pdf <code>

You can't see in detail some additions and sometimes you find some feature was enabled from ee to se, and you didn't know because you are not going to be testing every feature to see if someone was enabled.

My specific question is
I see Basic Replication Y Y Updateable materialized

Does this means fast update on commit?

Thanks Tom on advance

Tom Kyte
May 13, 2004 - 10:52 am UTC

REPLICATION never has, nor will support "on commit" refresh.

that works with materialized views in a single database only. It is not practical or even desirable to have an "on commit refresh" thing happening -- the only thing you achieve is

o horrible performance
o terribly bad availability

there is syncronous replication -- which has the same exact attributes -- that is your equivalent of on commit refreshes -- but I would avoid it like the plague.




A reader, May 13, 2004 - 2:46 pm UTC

Thanks Tom

data pump

Sam, May 14, 2004 - 10:23 pm UTC

I am trying to understand the difference between the usual exp/imp utility and the new datapump. I was reading an article in Oracle Magazine (March/April 2004 issue Page 69) in which the author was saying "exp and imp utilities ran as clients and did the bulk of the work." What does it mean "as clients". Exp/imp still creates dump file only on the server so does the new data pump. Could you please clarify? Thanks.

Tom Kyte
May 15, 2004 - 12:31 pm UTC

it means logic was moved from the client (imp/exp) into the kernel (impdb/expdp rely on internal functions lots more -- not just sql).


you could write exp/imp yourself pretty much (well, maybe not the direct path export, but that is why expdp is only incrementally faster -- not orders of magnitude faster). The are just standard clients of the database.

my favourite 10g new corruption feature :-P

LSC, May 17, 2004 - 10:47 am UTC

try rename second member of any redo log group (this is REDHAT AS 3.0) and btw already corrupted me two databases, is it me doing something wrong?

>!cp /u03/oradata/lnx101/redo01b.log /tmp
>startup mount
ORACLE instance started.

Total System Global Area 96468992 bytes
Fixed Size 777596 bytes
Variable Size 70263428 bytes
Database Buffers 25165824 bytes
Redo Buffers 262144 bytes
Database mounted.
>select * from v$logfile;

GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- ------------------------------------------------------------ ---
1 ONLINE /u03/oradata/lnx101/redo01.log NO
2 ONLINE /u03/oradata/lnx101/redo02.log NO
3 ONLINE /u03/oradata/lnx101/redo03.log NO
1 ONLINE /u03/oradata/lnx101/redo01b.log NO
2 ONLINE /u03/oradata/lnx101/redo02b.log NO
3 ONLINE /u03/oradata/lnx101/redo03b.log NO

6 rows selected.

>alter
2 database rename file
3 '/u03/oradata/lnx101/redo01b.log'
4 to
5 '/tmp/redo01b.log'
6 ;

Database altered.

>select * from v$logfile;

GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- ------------------------------------------------------------ ---
1 ONLINE /tmp/redo01b.log NO
2 ONLINE /u03/oradata/lnx101/redo02.log NO
3 ONLINE /u03/oradata/lnx101/redo03.log NO
1 ONLINE /u03/oradata/lnx101/redo01b.log NO
2 ONLINE /u03/oradata/lnx101/redo02b.log NO
3 ONLINE /u03/oradata/lnx101/redo03b.log NO

>select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 52 10485760 2 NO INACTIVE 222986 17-MAY-04
2 1 53 10485760 2 NO INACTIVE 222988 17-MAY-04
3 1 54 10485760 2 NO CURRENT 222990 17-MAY-04

>alter database backup controlfile to trace;

--------- see what happens in the trace file ---

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "LNX101" NORESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 454
LOGFILE
GROUP 1 (
'/tmp/redo01b.log'
) SIZE 10M,
GROUP 2 (
'/u03/oradata/lnx101/redo02.log',
'/u03/oradata/lnx101/redo02b.log'
) SIZE 10M,
GROUP 3 (
'/u03/oradata/lnx101/redo03.log',
'/u03/oradata/lnx101/redo03b.log'
) SIZE 10M
-- STANDBY LOGFILE
DATAFILE
'/u03/oradata/lnx101/system01.dbf',
'/u03/oradata/lnx101/undotbs01.dbf',
'/u03/oradata/lnx101/sysaux01.dbf',
'/u03/oradata/lnx101/users01.dbf'
CHARACTER SET WE8ISO8859P1
;

Group 1 first member dissapeared?!

>alter database drop logfile group 1;
alter database drop logfile group 1
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [2739], [1], [], [], [], [], [], []

>recover database until cancel;
Media recovery complete.
>alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [2738], [1], [], [], [], [], [], []


>alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


>alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [2738], [1], [], [], [], [], [], []

>alter database drop logfile member '/tmp/redo01b.log';
alter database drop logfile member '/tmp/redo01b.log'
*
ERROR at line 1:
ORA-00362: member is required to form a valid logfile in group 1
ORA-01517: log member: '/tmp/redo01b.log'


>alter database drop logfile member '/u03/oradata/lnx101/redo01b.log';
alter database drop logfile member '/u03/oradata/lnx101/redo01b.log'
*
ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [2160], [], [], [], [], [], [], []



OMG OMG OMG!!!!

You cannot rename logfile in 10g?!

Tom Kyte
May 17, 2004 - 3:36 pm UTC

I reproduced -- however you did not need to toast the database.

You could just take the create control file, "fix it" and run that script. Eg, I renamed a file in group 2 to /tmp/redo02_x.log - reproduced your entire issue. I did the backup controlfile to trace -- edited it ran:

idle> CREATE CONTROLFILE REUSE DATABASE "ORA10G" RESETLOGS NOARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 454
7 LOGFILE
8 GROUP 1 (
9 '/home/ora10g/oradata/ora10g/redo01.log',
10 '/home/ora10g/oradata/ora10g/redo01_x.log'
11 ) SIZE 10M,

just fixed these 4 lines, replaced the /tmp with the real log

12 GROUP 2 (
13 '/home/ora10g/oradata/ora10g/redo02.log',
14 '/home/ora10g/oradata/ora10g/redo02_x.log'
15 ) SIZE 10M,
16 GROUP 3 (
17 '/home/ora10g/oradata/ora10g/redo03.log',
18 '/home/ora10g/oradata/ora10g/redo03_x.log'
19 ) SIZE 10M
20 -- STANDBY LOGFILE
21 DATAFILE
22 '/home/ora10g/oradata/ora10g/system01.dbf',
23 '/home/ora10g/oradata/ora10g/undotbs01.dbf',
24 '/home/ora10g/oradata/ora10g/sysaux01.dbf',
25 '/home/ora10g/oradata/ora10g/users01.dbf',
26 '/home/ora10g/oradata/ora10g/ORA10G/datafile/o1_mf_big_tabl_05xmosfm_.dbf',
27 '/home/ora10g/oradata/ora10g/ORA10G/datafile/o1_mf_test_dro_08ty0mrs_.dbf'
28 CHARACTER SET WE8ISO8859P1
29 ;

Control file created.

idle> ALTER DATABASE OPEN RESETLOGS;

Database altered.

idle> ALTER TABLESPACE TEMP ADD TEMPFILE '/home/ora10g/oradata/ora10g/temp01.dbf'
2 SIZE 616562688 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;

Tablespace altered.


Please contact support with your simple test case -- definitely "not the right behaviour"

recycle bin feature not work for system?

A reader, May 17, 2004 - 10:55 am UTC

lsc@DSIM-SIMDESBD>create table to_be_dropped as select * from dba_users;

Table created.

lsc@DSIM-SIMDESBD>select owner, object_name from dba_recyclebin;

no rows selected

lsc@DSIM-SIMDESBD>drop table to_be_dropped;

Table dropped.

lsc@DSIM-SIMDESBD>select owner, object_name from dba_recyclebin;

OWNER OBJECT_NAME
------------------------------ ------------------------------
LSC BIN$2p/Sf8ANPsngMAB/AQAItw==$0

lsc@DSIM-SIMDESBD>purge table to_be_dropped;

Table purged.

lsc@DSIM-SIMDESBD>conn system
Enter password:
Connected.
system@DSIM-SIMDESBD>create table to_be_dropped as select * from dba_users;

Table created.

system@DSIM-SIMDESBD>drop table to_be_dropped;

Table dropped.

system@DSIM-SIMDESBD>select owner, object_name from dba_recyclebin;

no rows selected

system doesnt use recyclebin?

Tom Kyte
May 17, 2004 - 3:37 pm UTC

it is documented that you must use a non-SYSTEM locally managed tablespace


no way you should create anything in system
no way you should drop anything that was in system (that would be our data dictionary)

A reader, May 17, 2004 - 3:59 pm UTC


Help Requested!!!!!!!!!!!!!!

A reader, May 17, 2004 - 5:13 pm UTC

Hi Tom,
Yet again i seek your help.
I downloaded 10G from Oracle's site and i just couldn't wait to install it on my desktop. But i can't :'(. The setup just wouldn't start. I have 9iR2 installed on the same desktop and i cannot uninstall that. I do want to check out the new features but i guess i have to wait. Can you help me get a work around or is it not possible to install it on the same computer with 9i. The docs on 10G do not say anything about installing it with 9i.
Playing the waiting game............

Tom Kyte
May 18, 2004 - 7:41 am UTC

well, if you are on linux, who knows maybe I could

maybe if we knew what the symptoms were, we might have something to say.

maybe if we had an idea what OS you are using...

and so on. I have 10g, 9ir2, 8ir3 installed in my windoze vm and 9ir1, 9ir2, 10g installed on my linux real machine. didn't really do anything "special"

10g is not suitable at all for production

A reader, May 17, 2004 - 5:29 pm UTC

If the simple rename file feature does not work this must NOT used in production!

Is the way you suggested renaming in controlfile supported or it's a trick? Would OSS accept this kind of workaround? (well if this is workaround)

Tom Kyte
May 18, 2004 - 7:44 am UTC

you are "slightly over-reacting" as they say.

did you know that in 7.3, if you resized a file smaller and tried to rebuild your control files, it would not work. so, following your theory, 7.3 (used by most every Oracle customer) should not have been used.

This "workaround" fixes the problem. You rebuild the control files, you fixed the name/location of the redo log file. it 100% solves the issue.

My typical method of renaming a log file (something that can really only be safely done with the database up in mount mode, but not only since it involves playing with the redo log files location) is to do it online via

o create a new group
o drop old group

It would be rare to move logs "in production"

Please, file a tar with support. I agree, needs to be fixed. I disagree this would stop anyone -- especially since the fix is so "easy" and nothing is ever lost.

your workaround

A reader, May 18, 2004 - 8:14 am UTC

I would like to know the workaround is supported?
Nevertheless this not a very nice surprise because this such a basic feature that noone would expect failure.

Tom Kyte
May 18, 2004 - 3:01 pm UTC

yes, it is supported. it is all standard SQL DDL. Create Controlfile.

Help Requested!!!!!!!!!!!!!!

A reader, May 18, 2004 - 10:21 am UTC

Hi Tom,
sorry for the incomplete information.
as u said you have multiple versions running on the same machine. Well i have Windows XP along with the required service packs. I tried it again today and it failed yet again. I clicked on autorun and it started as usual but when i clicked on Install/Deinstall Products it just goes away and does not proceed to the next screen.
Any suggestions.

TO: A reader XP-Installation

Jens, May 18, 2004 - 10:58 am UTC

I had the same problem on Windows. Look here:

</code> http://forums.oracle.com/forums/thread.jsp?forum=64&thread=234324&message=670303 <code>

That helped me.






Thanks Friend!!!!!!!!!!!!

A reader, May 18, 2004 - 11:21 am UTC

Thanks Jens for your suggestion. I have JAVA version 1.3 as this is a new hard disk. I will intstall the newer one today and if i still have problems i know where to search.
Thanks a lot


ASM question

A reader, May 19, 2004 - 3:17 pm UTC

hi Tom
What is the relationship between ASM instance - the host
on which it runs and the databases (cluster/non cluster)
it manages? e.g.
1. can one ASM manage more than one databases (I presume yes.)?
2. can one ASM instance manage a non cluster db and a RAC.
3. If an ASM instance is managing multiple database instances (cluster and/or non cluster) and if I want to
know which disks in a diskgroup are being used by
which databases (including the ones that are shared)
then is their a way to find it out (through the
v$ or gv$ views?)

Thank you!!!

Tom Kyte
May 19, 2004 - 5:47 pm UTC

1) on asm instance can be used by more than one instance on that machine. asm is just like a file system driver, each machine needs one and everything on that machine uses the same one.

2) each node in the cluster would need an asm instance.

3) a diskgroup is like a logical volume, like a filesystem but only for database files. every instance uses every disk.

thanx Tom!

A reader, May 20, 2004 - 6:07 pm UTC

"3) a diskgroup is like a logical volume, like a filesystem but only for database
files. every instance uses every disk. "

That is usually the case - but is it possile that
an ASM instance is managing both a standalone db
and a RAC db instance on a host. In this case
can there be any sharing of storage between these two
cases?

Another question - is it necessary to assign a disk
to a diskgroup before we use it to store data in the
database - or can we use a standalone disk for storing
db data?

Thanx a bunch!!

Tom Kyte
May 20, 2004 - 8:48 pm UTC

standalone db and rac are not any different to asm, they are both "just oracle instances"

yes, they can "share storage", ever instance can use every disk. rac or standalone -- they are just "instances"


you can use

RAW
Cooked (regular file systems you can "ls" or "dir" on)
ASM

all at the same time.

thank you Tom!

A reader, May 21, 2004 - 11:48 am UTC

In the "corner" cases where we have one ASM instance
maintaining a standalone and a RAC database - if storage
is shared between them - and if I want to find out how
much the storage is (Without double counting) - what
query would give me that answer (assuming I can connect
to all databases.) If this is a complicated
q not to be asked as a followup - please let me know and
just ignore it.

Also, is it necessary for an admin to assign a disk
to a diskgroup before using it for storing db data?

Many thanx!:)


Tom Kyte
May 21, 2004 - 11:57 am UTC

you would just query the data dictionary to see how big each tablespace was. Just like usual.

actually

A reader, May 21, 2004 - 2:03 pm UTC

the requirement was to get the storage of data files as
well as control files and redo log files - I suspect
we have to go through some of the views v$asm_disk/
v$asm_file, v$asm_disk_group etc to get this info.

thanx!

Tom Kyte
May 21, 2004 - 2:11 pm UTC

v$ views contain that data as well - v$log/v$logfile for example.

A reader, May 21, 2004 - 4:33 pm UTC

Tom,

While browsing the book "Oracle 10G new features" by Mike Ault & Madhu Tumma on page 19, i was surprised(glad) to see your name in the book

Hope Don Burlesons starts using your advice and mentioning your name in his book someday, maybe when he stops using hit ratios :-) Which i am not sure is gonna happen.

Thanks.

A reader, May 25, 2004 - 12:06 am UTC

Tom,

I was looking at utl_mail package and as per docs

<doc>
You must both install UTL_MAIL and define the SMTP_OUT_SERVER.

To install UTL_MAIL: 
sqlplus sys/<pwd>
SQL> @$ORACLE_HOME/rdbms/admin/utlmail.sql
SQL> @$ORACLE_HOME/rdbms/admin/prvtmail.sql
</doc>

i was able to find utlmail.sql but the second one is missing. My environment is Windows 2000. Its only me or has somebody faced this

Thanks
Rahul.


 

Tom Kyte
May 25, 2004 - 7:06 am UTC

[tkyte@tkyte-pc admin]$ ls prvtmail*
prvtmail.plb
^^^

A reader, May 25, 2004 - 11:39 am UTC

Tom,

I want to practice ASM on my linux laptop having one hard drive, can i do it?

Thanks.

Tom Kyte
May 25, 2004 - 12:00 pm UTC

we use VMWARE to do that internally. You can slice it up as much as you like "virtually"

otherwise, you'd have to basically repartition the drive to play with it to any degree.

Questions on ASM

A reader, May 25, 2004 - 6:25 pm UTC

Hi Tom
1. how common is the scenario where ASM is serving
a RAC database - do you know of any currently used ones?
do you think they would be commonly used going
forward
2. Configuration
hosts h1, h2 are members of a RAC database
asm instances asm1 and asm2 are running on h1 and
h2 respectively. say we log in to asm1 and
find out an entry in v$asm_disk_group whose name = dg1

We then log into asm2 and find an entry in the
v$asm_disk_group whose name = dg1.

Can we assume that the underlying storage of dg1
are the same - i.e. that these two are actually
the same disk group visible from both ASM instances?

thanx!




Tom Kyte
May 25, 2004 - 8:17 pm UTC

1) if you use 10g RAC with standard edition *you have to use ASM* (and our clustering software). there is no choice.

I know of a few that are doing this, like anything new..... I look forward to implementing just for regular databases (no more OS specific stuff, just Oracle and a machine)

2) as long as the asm instances are managing the same disks, yes. else no.



thanx!

A reader, May 25, 2004 - 9:00 pm UTC


Veritas rules anyways

A reader, May 26, 2004 - 1:36 am UTC

No matter the hype Oracle is put on ASM

Veritas still rules

Tom Kyte
May 26, 2004 - 8:07 am UTC



rules what?


if you want

o just to take a bunch of disk
o implement S.A.M.E.
o without buying extra stuff for money
o for a database

how does it "rule"? veritas is great for building filesystems. unfortunately, databases don't need or want filesystems. a filesystem is nothing more than a database itself. so you have a database on a database on a database.

veritas is great for an SA to create filesystems.
it does not do too much for a DBA.

Veritas rules anyways?

Martin Guillen, May 26, 2004 - 4:50 pm UTC

mmmmh...Veritas VM is a great product but...
Does it rules the same on Linux, OS/390, Windows and so on?
If so, great...
For me ASM is among the best new features of 10g.
Sorry about this OT,
Martin.

bug in ASM?

A reader, May 27, 2004 - 6:59 pm UTC

When I tried to connect to my ASM instance on Solaris.
I noticed the following behavior (we use OS authentication.)

I set my ORACLE_HOME to /a/b/c   (notice there is no "/"
         at the end)
sqlplus /nolog
SQL> connect / as sysdba

The above says "connected to an idle instance" even though
the instance is up.

The problem goes awat if you set your ORACLE_HOME to
/a/b/c/ (notice the ending "/".)

Is this a known issue and can others reproduce it?

Thanx! 

Tom Kyte
May 27, 2004 - 9:02 pm UTC

ORACLE_HOME + ORACLE_SID = key used to attach to shared memory.

if the db was started with an oracle home of /a/b/c -- then trying to connect to it with /a/b/c/ won't work.

you need to make sure the oracle home you use, is the oracle home used to start

( /usr/ucb/ps -auxwwe will show you environment variables of running processes on solaris)

thanx!! that explains it!

A reader, May 28, 2004 - 11:25 am UTC


What do you think about the future of 11 afd

A reader, June 07, 2004 - 12:05 pm UTC

Hi Tom
Now we are waiting new sql server release, to seee if it fixes several restrictions in standard database war.
I think if Oracle wants to win the war sql server vs. Oracle, ( in addition to give a developer free runtime for business who has small number of clients, from 1 to 20 for example)

I think the tendency is to eliminate routine work and enrich his work programming and designing.

I think Oracle should give expert tools for design, this is a list of what I'm thinking about, maybe you have another ideas
:

1. Precreated common funcions, is always a waste of time to have to create the basic library that
finally every one uses. Somethink like plsvision package from quest software.
2. An interface including all database process, specifing its order, including consideration about the
kind of database, and an explantion from every step and problems it can have.
All should work in a wizard way, "there was x problems, what decision you want to take a),b) or other"
3. Scripts and views for most commonts tasks in a dba work.
3. Scripts and views for most commonts tasks for detecting and solving critical problems.
4. database templates, for example a basic erp, a basic crm, including comments in fields and a very
brief introduction, in this way using designer for example you can get a database in production in a few hours.


In some microsoft product I saw you can use a precreated database, so this is not a really new idea, the point is that no body had done a really serious, PROFESSIONAL an attractive product to help in first database stages design and expert process for monitoring, tuning and fixing; and I'm not talking about automatic self tunning.
I'm talking about database tutoring, now to get an expertise in Oracle you haev to read a lot and be in several forums; and I think this is unevitable. But there could be a way to accelerate and simplify the toruting first mistakes all dba uses to suffer, and sometimes this could have really serious consequences.

I think a very nice feature like this in the way Oracle uses to do, will allow him to be one step forward than sql server.

MODEL clause

A reader, June 10, 2004 - 7:21 pm UTC

I read Jonathan Gennick's article on the MODEL clause at

</code> https://asktom.oracle.com/Misc/oramag/on-uniqueness-space-and-numbers.html <code>

but frankly I didnt quite understand it.

Tom, could you please explain this in your inimitable style?

Thanks

Tom Kyte
June 10, 2004 - 8:20 pm UTC

don't worry - model is hard, model is complex, model is somewhat "not fathomed by me 100% yet".

i believe for model to be used by the masses, it'll have to be picked up by something like Discoverer and the code generated for us.

if you search for

model clause


on otn -- you'll get lots more examples/white papers.

to a reader

Dave, June 11, 2004 - 4:51 am UTC

Oracle has all of the features you want - have you actually gone and looked for them?

1. precreated Oracle functions - have you looked and the library of packages of procedures Oracle has?

2. take a look at 10g OEM, everything you will want there (you can also get them in 9i if you want to put in a little effort - which you obviously dont want to do)

3. Take a look in $ORACLE_HOME/rdbms/admin/demo (i think)

4. Fired up dbca recently?

5. Oracle designer - if you dont like it there are loads of design tools available out there

Oracle Database 10g: The Top 20 Features for DBAs

Sikandar Hayat, June 14, 2004 - 11:39 pm UTC

Oralce 9i PL/SQL in Forms/Reports 6/6i

Masood Qaisar, June 29, 2004 - 7:56 am UTC

I am using Oracle Developer 6/6i & oralce 9i Database.
How can i use new feature of PL/SQL & SQL in Developer 6/6i.
Like case statement.

Tom Kyte
June 29, 2004 - 8:12 am UTC

i haven't touched forms since march of 1995 -- best off to try the discussion forums on otn.oracle.com for forms related things.

A reader, June 29, 2004 - 1:30 pm UTC

Hey Masood Qaisar from Pakistan what has your question to do with 10g new features?


A reader, June 29, 2004 - 3:42 pm UTC

The relation is simple
he is using Oracle Developer 6/6i and wants to use new oracle 9i Database new features, like case statement

case statement is his favorite feature, present in 10g too.
So there is the relation. ;)

The answer to his question is:
"You can't, you can't use iot table, etc., and because now you have to buy ias to use 9i, unless you buy ias you can't upgrade to a new developer realease.
The best you can do is create a function in the database and call that function".


Forms6i and 8i/9i/10g SQL

Gary, June 29, 2004 - 8:01 pm UTC

The response saying that you can't use the case statement in Forms 6i is not strictly accurate.

Something Forms does get passed straight to the database.
For example, you can base a block on a from clause such as
"FROM (select case when col_a between 'A' and 'D' then col_b else col_c end col_alias_d from table_e)"
You can also use any database query in record groups.

Some restriction apply to datatypes (Forms 6i can't handle TIMESTAMP for example).

Most restrictions apply to Forms PL/SQL which is close to the PL/SQL allowed in Oracle8 server (which also didn't allow access to all the SQL available in 8/8i). You can use dynamic SQL to access these features (EXEC_SQL in forms, which maps pretty closely to DBMS_SQL on the server) or hide them in server side packages or views.


10g new CBO features :P

A reader, July 01, 2004 - 11:22 am UTC

The CBO seems dumb in 8.1.7, got a bit clever in 9.2.0 then back to dumb in 10.1.0.2 :-P

alter session set optimizer_features_enable = '8.1.7'

SELECT /* 8.1.7 */ libro.c_nivel, libro.c_asignatura, libro.c_titulo, libro.n_alumnos
FROM libro_usado libro, definicion_mercado def
WHERE libro.c_centro = :b7
AND libro.c_nivel = :b6
AND libro.c_asignatura = :b5
AND libro.b_cerrado_periodo = 'N'
AND libro.b_bloqueado = 'N'
AND libro.b_estado = 'N'
AND libro.c_titulo_es_adoptado IN (
SELECT titulo.c_titulo
FROM titulo
WHERE titulo.c_tipo_titulo IN (
SELECT c_tipo_titulo
FROM tipo_titulo
WHERE t_clase = 'T'))
AND def.c_pais = :b4
AND def.c_autonoma = :b3
AND def.c_provincia = :b2
AND def.c_periodo = :b1
AND def.c_nivel = libro.c_nivel
AND def.b_def_mercado = 'S'
AND def.c_edicion = libro.c_edicion_es_adoptado
AND def.c_idioma = libro.c_idioma_es_adoptado
AND def.c_asignatura = libro.c_asignatura
AND def.b_bloqueado = 'N'
ORDER BY libro.c_nivel, libro.c_asignatura

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.01 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 13 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.01 0.00 0 13 0 0


alter session set optimizer_features_enable = '9.2.0'
SELECT /* 9.2.0 */ libro.c_nivel, libro.c_asignatura, libro.c_titulo, libro.n_alumnos
FROM libro_usado libro, definicion_mercado def
WHERE libro.c_centro = :b7
AND libro.c_nivel = :b6
AND libro.c_asignatura = :b5
AND libro.b_cerrado_periodo = 'N'
AND libro.b_bloqueado = 'N'
AND libro.b_estado = 'N'
AND libro.c_titulo_es_adoptado IN (
SELECT titulo.c_titulo
FROM titulo
WHERE titulo.c_tipo_titulo IN (
SELECT c_tipo_titulo
FROM tipo_titulo
WHERE t_clase = 'T'))
AND def.c_pais = :b4
AND def.c_autonoma = :b3
AND def.c_provincia = :b2
AND def.c_periodo = :b1
AND def.c_nivel = libro.c_nivel
AND def.b_def_mercado = 'S'
AND def.c_edicion = libro.c_edicion_es_adoptado
AND def.c_idioma = libro.c_idioma_es_adoptado
AND def.c_asignatura = libro.c_asignatura
AND def.b_bloqueado = 'N'
ORDER BY libro.c_nivel, libro.c_asignatura

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.01 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 5 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.01 0.00 0 5 0 0


alter session set optimizer_features_enable = '10.1.0'
SELECT /* 10.1.0 */ libro.c_nivel, libro.c_asignatura, libro.c_titulo, libro.n_alumnos
FROM libro_usado libro, definicion_mercado def
WHERE libro.c_centro = :b7
AND libro.c_nivel = :b6
AND libro.c_asignatura = :b5
AND libro.b_cerrado_periodo = 'N'
AND libro.b_bloqueado = 'N'
AND libro.b_estado = 'N'
AND libro.c_titulo_es_adoptado IN (
SELECT titulo.c_titulo
FROM titulo
WHERE titulo.c_tipo_titulo IN (
SELECT c_tipo_titulo
FROM tipo_titulo
WHERE t_clase = 'T'))
AND def.c_pais = :b4
AND def.c_autonoma = :b3
AND def.c_provincia = :b2
AND def.c_periodo = :b1
AND def.c_nivel = libro.c_nivel
AND def.b_def_mercado = 'S'
AND def.c_edicion = libro.c_edicion_es_adoptado
AND def.c_idioma = libro.c_idioma_es_adoptado
AND def.c_asignatura = libro.c_asignatura
AND def.b_bloqueado = 'N'
ORDER BY libro.c_nivel, libro.c_asignatura

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.01 0.00 0 58 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.01 0.00 0 58 0 0


We had to set optimizer_features_enable = '9.2.0'
otherwise our batch jobs takes 5 hours instead of 30 minutes!!! Not sure if it´s worth using 10g and 9i CBO :-P

ASM and Network Storage Systems

Mark, July 09, 2004 - 6:48 pm UTC

We are in the process of upgrading from 8.1.7 to 10g. We are very interested in using ASM. Our database storage will all be on a network storage system (EMC).

My understanding of ASM, is that you make disk groups by assigning raw disks. Oracle automatically moves files around to spread the load. Our SA is configuring the storage system, and wants to use the network storage platform to stripe and mirror the disks and only present Oracle with 1 LUN.

It seems to me that this defeats most of ASM's benefits. However, the SA argues that there is no way that ASM will outperform the EMC storage, and using ASM defeats most of EMC's benefits.

I am looking for comments/advise relating to ASM and Network Storage.



Tom Kyte
July 09, 2004 - 7:14 pm UTC

no way :) love it. hasn't seen it, doesn't know it, but knows all about it.

I would suggest benchmarking and prototyping to achieve two goals -- see how it works, what it does and to see how well or not in your environment it does it.

Most SA's are taken aback by this, since it is moving something that historically has been in their domain - into your domain.

Files stink for databases -- file systems make life easier for SA's but not for DBA's. disk groups make life easier for both (as it tends to cut the SA out of the loop much earlier and truly does make life easier for the dba)

But I would suggest a "getting to know you" period. I've seen asm work very well -- especially when it comes to adding or removing storage.



ASM

A reader, July 09, 2004 - 11:51 pm UTC

How does ASM work exactly? Suppose I put 10 disks in a disk group and present that to Oracle. Over the course of the next few weeks, Oracle determines that certain segments are heavily accessed and are "hot spots". Would ASM automatically stripe the various extents of the segment over the multiple disks, automagically and transparently? If the 10 "disks" are themselves LUNs, would it still do this? Or does ASM require that you add physical disks to the disk group and not LUNs?

Thanks

Tom Kyte
July 10, 2004 - 9:17 am UTC

ASM would place extents for segments evenly over the disks. If you add a new disk to the group, Oracle would pick up extents (we are talking small extents -- it is ok to have lots and ASM puts to bed that myth forever) that are on disks already and rebalance the load. if you take out a disk -- the reverse would happen. A massive SAME implementation.

ASM picks up at that LUNs level.

ASM

A reader, July 10, 2004 - 9:25 am UTC

1. So all the extent rebalancing as disks are added and removed happens while the tables are online and accessible to users?

2. Dont understand what you mean by "ASM picks up at that LUNs level". AFAIK Oracle has no way to determine what the details about a LUN are, for all intents and purposes it appears as a physical disk. So, when I add a "disk" to ASM which is really a RAID LUN, would ASM still do its SAME stuff?

Thanks

Tom Kyte
July 10, 2004 - 8:43 pm UTC

1) absolutely

2) you give it the stuff you have after setting up the LUNs (or direct attach storage or whatever). Yes, ASM would still do it SAME stuff (althouge you can certain choose to not us the "M" if the hardware is already doing that)

SAME

A reader, July 11, 2004 - 12:20 am UTC

"Yes, ASM would still do it SAME stuff (althouge you can
certain choose to not us the "M" if the hardware is already doing that)"

Well, I was referring more to the "S" part. Given a already striped LUN, can ASM be configured to not either "S" (stripe) or "M" (mirror) anything? i.e. I just want to use ASM for its "online hot-spot load balancing" feature, period. Can I do this? Thanks

[The reason I ask is that somehow I dont think enterprises everywhere are going to just throw away all their existing investment in EMC, Veritas, and just start presenting raw disks to Oracle!]

Tom Kyte
July 11, 2004 - 9:42 am UTC

ASM doesn't have an online hot spot feature, it strips everything to avoid the online hot spots.

You can still *use* emc veritas whatever -- but you don't have to buy it anymore if you don't want to. things change, things evolve.

Thanks

A reader, July 11, 2004 - 10:00 am UTC


ASM - is it single point of failure?

P, July 15, 2004 - 12:18 pm UTC

tom,
Isn’t ASM a single point of failure or a bottleneck. Also one more database for backup/maintenance.

thank you,
P



Tom Kyte
July 15, 2004 - 5:47 pm UTC

No more than veritas is a single point of failure

or your operating system is a single point of failure


ASM is just a file system -- and is no more a single point of failure than any file system driver is.

ASM works in a clustered environment -- each node in the cluster runs an instance of ASM.

No, it is not a single point of failure.

And you backup the configuration -- just as you would for veritas or whatever you use to manage storage (you don't keep that information in your head do you?)

AWR doesnt show SQL statistcs

A reader, July 19, 2004 - 9:04 am UTC

Hi

I ran a couple of AWR reports in a 10g production db and it shows empty SQL statistics... but the tables do have data how so?

Tom Kyte
July 19, 2004 - 9:27 am UTC

please be more specific. what do you mean by "empty sql statistics"

it shows this

A reader, July 19, 2004 - 10:07 am UTC

^LSQL ordered by Elapsed Time DB/Inst: RDZP/RDZP Snaps: 62-64

No data exists for this section of the report.
-------------------------------------------------------------

^LSQL ordered by CPU Time DB/Inst: RDZP/RDZP Snaps: 62-64

No data exists for this section of the report.
-------------------------------------------------------------

^LSQL ordered by Gets DB/Inst: RDZP/RDZP Snaps: 62-64

No data exists for this section of the report.
-------------------------------------------------------------

^LSQL ordered by Reads DB/Inst: RDZP/RDZP Snaps: 62-64

No data exists for this section of the report.
-------------------------------------------------------------


Tom Kyte
July 19, 2004 - 10:34 am UTC

which script are you running for that report?

that looks like statspack?

hi

A reader, July 19, 2004 - 1:11 pm UTC

I executed awrrpti.sql but chose TEXT output, and it shows that...

Tom Kyte
July 19, 2004 - 1:51 pm UTC

what was your snap level

the snap level was default

A reader, July 19, 2004 - 3:56 pm UTC

Hi

I have not modified the snap level. Not sure if default gather expensive SQL? However in wrh$ssql I do see entries...

Tom Kyte
July 19, 2004 - 4:48 pm UTC

well, if you believe there should be some sql showing there -- it would be cause for a tar with support. I cannot reproduce on my systems at all. what does the gui dbconsole show?

I dont use OEM

A reader, July 20, 2004 - 4:39 pm UTC

Hi

We dont use GUI. I will open a tar about this. I ran more reports today and still shows no data related with SQL statements. I am using Red Hat Enterprise Linux 3. Are you using the same?

Tom Kyte
July 20, 2004 - 8:46 pm UTC

yes.

A reader, July 23, 2004 - 11:57 am UTC

Hi Tom, what can you do with materialized views in 10G standard edition you couldn't do previously in 7.
I see product family but there is not clear what you can do.
Could you please explain it ;) please



Tom Kyte
July 23, 2004 - 4:30 pm UTC

well, MV's didn't exist in version 7, so I guess the answer is "just about everything you can do to a MV in 10g is new as compared to 7"?

but you'd want to read the new features guide -- or just read the data warehousing guide about MV's to learn about them.

In 7.x you had snapshots -- for replication.

In 815 we added MV's as a super set of snapshots -- they included the magic of query rewrite and "refresh on commit" inside of a single instance.

A reader, July 23, 2004 - 5:14 pm UTC

Thanks Tom, I read about this feature, the problem is I don't find where in detail explains which works in standard edition and what not, 
I see in oracle 9.2 the v$optoin
Materialized view rewrite                                        FALSE                                                    Materialized view warehouse refresh                              FALSE                                                    
But I run this script and it worked


SQL> create materialized view hola 
build immediate refresh on commit enable query rewrite as 
select owner,count(*) from cachito.xtest group by owner;
Materialized view created.
SQL> select * from hola;
OWNER                            COUNT(*)
------------------------------ ----------
CACHITO                                48
CACHITO2                               18
OUTLN                                  14
PUBLIC                               2020
SYS                                  4025
SYSTEM                                708
TEMP                                   34
WMSYS                                 262
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        571  bytes sent via SQL*Net to client
        499  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          8  rows processed
SQL> select owner,count(*) from cachito.xtest group by owner;
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         92  consistent gets
          0  physical reads
          0  redo size
        571  bytes sent via SQL*Net to client
        499  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          8  rows processed
SQL> delete from xtest where owner = 'CACHITO';
SQL> commit;
SQL> select * from hola;
OWNER                            COUNT(*)
------------------------------ ----------
CACHITO2                               18
OUTLN                                  14
PUBLIC                               2020
SYS                                  4025
SYSTEM                                708
TEMP                                   34
WMSYS                                 262
7 rows selected.
SQL> delete from xtest where owner = 'CACHITO2';
18 rows deleted.
SQL> select * from hola;
OWNER                            COUNT(*)
------------------------------ ----------
CACHITO2                               18
OUTLN                                  14
PUBLIC                               2020
SYS                                  4025
SYSTEM                                708
TEMP                                   34
WMSYS                                 262
7 rows selected.
SQL> commit;
SQL> select * from hola;
OWNER                            COUNT(*)
------------------------------ ----------
OUTLN                                  14
PUBLIC                               2020
SYS                                  4025
SYSTEM                                708
TEMP                                   34
WMSYS                                 262
6 rows selected.
SQL>


As I understand in standard Edition I shouldn't do this, so my question, and is very important, because this could be an important reason to migrate to 10g is

What can I do in 10g Standard Edition using materialized view, and what I can't, or where is explained in detail this?

Thanks Tom 

Tom Kyte
July 23, 2004 - 5:36 pm UTC

the oracle 9i new features guide details what you have and what you don't

SE is not licensed to use MV rewrite.

not in 9i, not in 10g (in 10g the guide is called the licence guide)

A reader, July 23, 2004 - 5:49 pm UTC

Thanks Tom

How to generate/save DB Create Scripts in 10G ?

Valar, August 06, 2004 - 11:59 am UTC

Hi Tom,

I would like to know if there is a way to generate/save the db create scripts in 10G. I have done a fresh install of 10G for Windows on a new PC and when I use the DBCA, I don't find an option to save the scripts. I am looking at the earlier version of 9i and I do see the option in there. Am I  missing something obvious ?  Please let me know.

SQL> select banner from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Prod
PL/SQL Release 10.1.0.2.0 - Production
CORE    10.1.0.2.0      Production
TNS for 32-bit Windows: Version 10.1.0.2.0 - Production
NLSRTL Version 10.1.0.2.0 - Production

SQL> 

Thanks,
Valar 

Tom Kyte
August 06, 2004 - 1:23 pm UTC

that is, as I understand it, correct - you can save it as a template, but DBCA would be the thing to "run it"

Once you have a database -- you can use the EM grid control to clone a database or software install "silently" (like a script)

Option to generate/save db create scripts

Valar, August 06, 2004 - 5:16 pm UTC

Hi Tom,

Thanks to the folks on metalink forum, I found that the option to save the db create scripts in 10G prompts up only when you select the database to be created is 'a custom database'. It doesn't show up when the other options like General Purpose/DataWare House is chosen.

Thanks,
Valar

Tom Kyte
August 07, 2004 - 9:47 am UTC

cool, thanks for the followup.

datapump

reader, August 12, 2004 - 3:15 pm UTC

What is the equivalent option for show=y in datapump impdp utility in 10g? Thanks.

Tom Kyte
August 12, 2004 - 3:36 pm UTC

SQLFILE=

Reduntancy Levels of ASM

A reader, August 16, 2004 - 10:21 am UTC

Dear Tom,

I have a DB created using ASM. Now I want to see the reduntancy levels of ASM. Is there a simple way to check this?

Tom Kyte
August 16, 2004 - 7:36 pm UTC

v$asm_disk -- the reference guide documents all of the v$ / dba views you have available.

ASM

A reader, August 17, 2004 - 10:26 am UTC

Tom,

We are building a server which will implement a SAME array. I am trying to get a grasp of ASM before we go and ahead and purchase expensive RAID controllers -- we were planning on having the hardware do striping and mirroring, but now I'm seconding guessing that.

As you said in April regarding ASM striping: "it is 1m and 128k - 128k used for some files like control files, 1m for other stuff." It seems to me if we left striping to the hardware we wouldn't get this kind of advantage.

Could you explain the pros and cons of striping and mirroring with hardware as opposed to with ASM? Much appreciated.

Tom Kyte
August 17, 2004 - 10:34 am UTC

two separate discussions.

first, the choice to mirror at the hardware or ASM layer is totally up to you. if you want to do it in the array, no problem.

as for the striping -- it is really "do you want to use files". files are a database themselves (inodes are like the data dictionary, pages are like blocks -- file 5, block 55 is a index range scan, table access by index rowid operation in a file!! it is a keyed read). do you need a database on a database?

Also, files are great for SA's -- not so great for DBA's. So, you get this array and they set up 4 independent logical volumes. each is well striped by itself -- but across them? What happens if you get two of these LV's today -- and the other two are used for something else. Next year, they give you another of the LV's -- what then? An independent file system, upon which you have to move files from the other LV's onto (down time, decision time). ASM is to make this process easier -- just add disk to a diskgroup, it rebalances the database data, take a disk away and the same.

ASM is for DBA's for managing database data
what
Filesystems are for SA's for managing disk


Filesystems are easy for SA's
Filesystems are not the optimal solution for the DBA's


I'd suggest you try out ASM -- give it a test drive, kick the tires and see what you think first.

ASM vs SAN or NAS

reader, August 21, 2004 - 11:13 am UTC

Can ASM be configured to access storage devices on the network across the firewalls? Or the disks need to be attached to the server containing ASM instance? Thanks.

Tom Kyte
August 21, 2004 - 12:20 pm UTC

ASM can use any disk you could "format" and do an ls on (dir whatever).

if you can make a filesystem on it, ASM could use it.

RMAN incremental backups

Robert, August 21, 2004 - 7:29 pm UTC

From,

</code> http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10735/bkup.htm#1032144 <code>

<quote>The size of the change tracking file is proportional to the size of the database and the number of enabled threads of redo. The size is not related to the frequency of updates to the database.<quote>

I am confused about the above statemnet from the doc. Could you please clarify why this is so? It would make more sense if the size of change tracking file depends on how much change we make to the database rather than the size of the database. Thanks.

Tom Kyte
August 21, 2004 - 8:28 pm UTC

the tracking file tracks blocks that are modified (or not)

if a block is modified once or a billion times, it is still just "modified".

so, flag for the blocks, each block has a flag.

(and this applies only to 10g and above, new 10g feature)

10g feature

READER, August 21, 2004 - 9:54 pm UTC

select 'dbms_mview.refresh('''||mview_name||''')'||';' from user_mviews;

I read somewhere that in 10g ''' can be replaced with a simpler syntax .

can you tell ?

Thanks

Tom Kyte
August 22, 2004 - 8:07 am UTC

ops$tkyte@ORA10G> exec dbms_output.put_line( <b>q'|</b>How's this for progress?<b>|'</b> );
How's this for progress?
 
PL/SQL procedure successfully completed.
 

New feature .

READER, August 22, 2004 - 9:05 am UTC

Thanks Tom .
Where can I find some more documentation on this .
What will be the key word .


ASM clarification

Sami, August 22, 2004 - 1:13 pm UTC

Dear Tom,

/dev/sda6,/dev/sda7,/dev/sda8 are 3 extended(logical) partitions in my external firewire harddisk.

su - root
# /etc/init.d/oracleasm createdisk VOL1 /dev/<sda6>
Marking disk "/dev/sda6" as an ASM disk [ OK ]
# /etc/init.d/oracleasm createdisk VOL2 /dev/<sda7>
Marking disk "/dev/sda7" as an ASM disk [ OK ]
# /etc/init.d/oracleasm createdisk VOL3 /dev/<sda8>
Marking disk "/dev/sda8" as an ASM disk [ OK ]
#

# /etc/init.d/oracleasm listdisks
VOL1
VOL2
VOL3
#

I have created 10g DB using ASM (selected VOL1 and VOL2 for "Disk Group1" and redundancy level as "Normal")

Here my understanding is that /dev/sda6 and /dev/sda7 has exact replica images. Even if one of my partition (/dev/sda6 or /dev/sda7) goes wrong, the DB will continue to work without any problem.

Is that correct?

Tom Kyte
August 22, 2004 - 5:00 pm UTC

we don't mirror that way. (exact replica's -- no, logical replica's -- yes)

/dev/sda6 can be rebuilt from sda7 and vice versa -- as long as they are in separate failure groups in that disk group.

ASM's logical replica

A reader, August 22, 2004 - 6:16 pm UTC

Dear Tom,

1) Could you kindly explain what is logical replica?
2) How /dev/sda6 can be rebuilt from /dev/sda7 when /dev/sda6 goes wrong? What is the command to rebuild?

Tom Kyte
August 22, 2004 - 8:13 pm UTC

1) not a physical bit for bit, byte for byte replica.

both devices will contain the primary copy for some extents, mirror copies for other extents. neither device would be an exact mirror of the other at the byte for byte level.

2) it just happens, it is what ASM does. it would be transparent to you.

Small but very useful features

Pratap, August 24, 2004 - 2:44 am UTC

Just read about some small but very useful features -
DBMS_UTILITY.FORMAT_ERROR_BACKTRACE - Get line number where error had occured even when exception block is present

One more thing - If there is an error when inserting data in a table then we don't get the column name where the error had occured. I read in this site that this will be available in 10g. Is the feature implememted?

Again small things but of great help to developers.


Tom Kyte
August 24, 2004 - 7:40 am UTC

insert into t values ( 12345, 'helloworldhelloworld' )
                              *
ERROR at line 1:
ORA-12899: value too large for column "OPS$TKYTE"."T"."Y" (actual: 20,
maximum: 10)
 
 
ops$tkyte@ORA10G>
 

UTL_MAIL

Marcio Portes, August 30, 2004 - 8:53 am UTC

Can you reproduce this?

utlmail.sql ok
prvtmail.plb ok

ops$marcio@ORA10G>
ops$marcio@ORA10G>
ops$marcio@ORA10G> create or replace directory my_files as 'c:\scripts';

Directory created.

ops$marcio@ORA10G>
ops$marcio@ORA10G> declare
2 l_output utl_file.file_type;
3 l_file raw(32767);
4 l_size number;
5 block number;
6 b boolean;
7
8 begin
9
10 UTL_FILE.FGETATTR('MY_FILES', 'MARCIO.JPG', b, l_size, block);
11 l_output := utl_file.fopen( 'MY_FILES', 'MARCIO.JPG', 'r' );
12 utl_file.get_raw(l_output, l_file, l_size);
13 utl_file.fclose( l_output );
14 UTL_MAIL.SEND_ATTACH_RAW (
15 sender => 'Marcio Portes <marcio.portes@uol.com.br>',
16 recipients => 'marcio.portes@uol.com.br',
17 subject => 'Foto Argentina',
18 attachment => l_file,
19 message => 'Atachado, segue sua foto!',
20 att_inline => false,
21 att_filename => 'c:\scripts\marcio.jpg');
22 end;
23 /
ERROR:
ORA-03114: not connected to ORACLE


declare
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel


Version: 10.1.2
marcio.jpg has 31k.

-- I changed path of directory to /tmp
create or replace directory my_files as '/tmp';

declare
l_output utl_file.file_type;
l_file raw(32767);
l_size number;
block number;
b boolean;
begin
UTL_FILE.FGETATTR('MY_FILES', 'MARCIO.JPG', b, l_size, block);
l_output := utl_file.fopen( 'MY_FILES', 'MARCIO.JPG', 'r' );
utl_file.get_raw(l_output, l_file, l_size);
utl_file.fclose( l_output );
UTL_MAIL.SEND_ATTACH_RAW (
sender => 'Marcio Portes <marcio.portes@uol.com.br>',
recipients => 'marcio.portes@uol.com.br',
subject => 'Foto Argentina',
attachment => l_file,
message => 'Atachado, segue sua foto!',
att_inline => false,
att_filename => 'c:\scripts\marcio.jpg');
end;
/





Tom Kyte
August 30, 2004 - 9:11 am UTC

ops$tkyte@ORA10G> create or replace directory my_files as '/tmp';
 
Directory created.
 
ops$tkyte@ORA10G> declare
  2     l_output   utl_file.file_type;
  3     l_file          raw(32767);
  4     l_size          number;
  5     block           number;
  6     b               boolean;
  7   begin
  8     UTL_FILE.FGETATTR('MY_FILES', 'MARCIO.JPG', b, l_size, block);
  9     l_output := utl_file.fopen( 'MY_FILES', 'xtmpx.sql', 'r' );
 10     utl_file.get_raw(l_output, l_file, l_size);
 11     utl_file.fclose( l_output );
 12     UTL_MAIL.SEND_ATTACH_RAW (
 13           sender     => 'tom kyte <tkyte@oracle.com>',
 14           recipients => 'tkyte@oracle.com',
 15           subject    => 'Foto Argentina',
 16           attachment => l_file,
 17           message    => 'Atachado, segue sua foto!',
 18           att_inline => false,
 19           att_filename => '/tmp/xtmpx.sql');
 20   end;
 21  /
 
PL/SQL procedure successfully completed.
 


worked for me, I'll have to suggest support for that one -- you most likely have a trace file in your udump to go along with this that they'll want to see. 

Data Pump

A reader, September 02, 2004 - 5:01 pm UTC

I was reading the article in

</code> https://asktom.oracle.com/Misc/oramag/on-fetching-storing-and-indexing.html <code>

Sounds awesome...

1. It mentions

create database link srcdb using 'srcdb';
impdb ... network_link='srcdb'
"Data Pump Import fetches the necessary metadata from the source across the database link and re-creates it in the target"

The db link above would need to be created with the proper credentials on the source db to be able to select from dictionary views and stuff, right?

2. expdp lora/lora123 TABLESPACES=\(ts1,ts2\) DUMPFILE=ts1_ts2.dmp DIRECTORY=dump_dir

This step creates a file /u01/dumps/ ts1_ts2.dmp with the contents of the TS1 and TS2 tablespaces

Would the 'dump_dir' contain only the metadata or the actual data in the tablespaces?

3. The example on the 'pull tablespaces' feature talks about a DBMS_FILE_TRANSFER package! So is this a FTP/RCP replacement that can transfer big files from one database to another regardless of OS? Is this limited to transfer database files only or any files accessible to the database server?

Thanks

Tom Kyte
September 02, 2004 - 9:39 pm UTC

1) anything would have to have the proper authorizations, yes. this is not "bypass_security=true"

2) the contents (the rows) would be there. not too much different from exp ... tablespace=...

3) see
</code> http://docs.oracle.com/docs/cd/B14117_01/appdev.101/b10802/d_ftran.htm#996730 <code>

limitations are listed at the bottom. nothing over 2TB is acceptable for starters...



ASM

Sam, September 04, 2004 - 12:09 pm UTC

In a non-RAC environment,

(1)Can I have an ASM instance on one server that would mount disk groups for database instance located on another server?
(2) If the answer for the above is NO, does it mean that a ASM instance must be created on the server where the database instance is available? Thanks. You are awesome.



Tom Kyte
September 04, 2004 - 1:05 pm UTC

1) no, just like you wouldn't run veritas on server A for server B. Your file system is something "near and dear" to you. ASM is a file system for database files.

2) absolutely -- even in RAC, there would be a single ASM instance on each node in the cluster.

sql profile

Rob, September 04, 2004 - 8:28 pm UTC

From 10g doc,

</code> http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10752/sql_tune.htm#37539 <code>

<quote> The query optimizer can sometimes produce inaccurate estimates about an attribute of a statement due to lack of information, leading to poor execution plans <quote>

What are the attributes they are talking about in the above statement? Thanks.

Tom Kyte
September 05, 2004 - 10:19 am UTC

missing, incomplete, or stale statistics.

eg: should the query --

select * from t where x = 55;

use an index on t(x)? without histograms, it might not know that a FULL SCAN is the only reasonable plan.

SQL Profiles let it go even further as to the information known to the optimizer.

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:22829633914543 <code>



begin backup

reader, September 06, 2004 - 12:39 pm UTC

In 10g, Oracle has 'alter database begin backup' command. Previously, oracle has been recommending to take online backups tablespace by tablespace to minimize the amount of redo. Now, why this new syntax as it would generate tons of redo (assumin users perform a lots of DMLs) until the backup of all database files complete. Or in 10g is there any change in the redo generation when the whole database is in backup mode? Thanks for clarifying.

Tom Kyte
September 06, 2004 - 12:47 pm UTC

actually, we recommand rman which generates NO ADDITIONAL redo whatsoever during backup mode.


Since many people just:

a) altered every tablespace begin backup at 3am when the system was "pretty slow"
b) copied files
c) altered every tablesapce end backup

instead of:

for x in (select tablespaces)
begin backup of that tablespace
copy just those files
end backup


because it was "easier", we made it "safer" -- so you don't "miss" a begin/end backup tablespace command.



But, if you use RMAN, you would find it doesn't use or need or desire that "begin backup mode", which is only necessary when you don't like the database back itself up.

remap_tablespace

Rob, September 11, 2004 - 10:39 am UTC

(1)In 10g datapump, one has to precreate the tablespace before inmport to move objects from one tablespace to another tablespace using remap_tablespace parameter. Is this correct?

(2) what is the use of remap_datafile?

Thanks.

Tom Kyte
September 11, 2004 - 10:53 am UTC

1) pretty much -- as normally you are not issuing "create tablespace" statements in the impdb - you are importing data into a schema or set of tables.

2) documentation says...

The ability, in an import job, to change the name of the source datafile to a different name in all DDL statements where the source datafile is referenced. See REMAP_DATAFILE on page 3-27.

ignore=y

Sam, September 11, 2004 - 11:52 am UTC

What is the equivalent option for ignore=y in datapump? Thanks.

table_exists_action - I found the answer from online doc.

Sam, September 11, 2004 - 11:58 am UTC

So please ignore my previous question. Thanks.

scheduler

Raja, September 12, 2004 - 12:02 pm UTC

In 10g, does scheduler have a functionality to do the following out of the box?

If I have three jobs Job A, Job B and Job C. I want to run Job B only if Job A is successful. If so, could you please help me how to do it or any pointer to doc would be much appreciated.

Thanks.

Tom Kyte
September 12, 2004 - 4:36 pm UTC

9i can do that, 8i can do that -- any release can do that


job c isn't relevant to the problem at hand (A and B don't seem to be related to it)

but either:

A would submit B as its last step

OR

(my preferred approach) A and B *are* the job

begin
a;
b;
end;

that would be the job.


You can read more about the job scheduler (more sophisticated one) in 10g in the new features guide and other online docs available on otn.

tkprof on 10g

A reader, September 13, 2004 - 12:57 pm UTC

Hi Tom in the new features is there some way to get the tkprof output from the client machine in sqlplus, without having to get the file from the server and get the tkprof output.

Thanks

Tom Kyte
September 13, 2004 - 3:24 pm UTC

asm

Rob, September 13, 2004 - 2:39 pm UTC

How does database instance know which asm instance it should access? It is puzzling to me! Is there anythin one should specify in the tnsnames.ora file? Thanks.

Tom Kyte
September 13, 2004 - 3:57 pm UTC

there is "the asm instance" on the server. there is only one instance it could access.



A reader, September 13, 2004 - 3:57 pm UTC

Thanks Tom, I thought there was some thing really new about the way to get them :)

asm instance

reader, September 14, 2004 - 2:24 pm UTC

<quote>there is "the asm instance" on the server. there is only one instance it could access.<quote>

Tom,

(1) does it mean that I cannot have more than one ASM instance on the server?
(2) What is the mechanism that would allow db instance to contact asm instance? I don't see any parameter in db instance to specify asm instance location or sid.

Thanks.


Tom Kyte
September 14, 2004 - 2:32 pm UTC

1) correct, it is sort of like "veritas", you either have a file system or you don't.

2) it is a known service -- just like you don't tell oracle how to talk to UFS or NTFS or VxFS or ..... asm is a filesystem for the Oracle database.

setup 10g Grid

Vaibhav, September 15, 2004 - 7:49 am UTC

Hi,
i require to check out the grid computing feature of 10g from a purely academic point of view. I have browsed the oracle site and it is mentioned that quite some resources are required to setup the grid (RAC using firewire technology, special network cards, clustering software). As I am doing this as a part of enhancing my knowledge base, i am not in a position to financially facilitate this type of environment. Is it possible to setup the 10g grid using normal networked PCs ?

I have 5 networked PCs(P-3 448MHz, 256 MB RAM, 40 GB HDD running on Win2K, 100 MBps LAN connectivity), Please tell me if there is a way in which i can check out the features with these resources in hand.

Tom Kyte
September 15, 2004 - 9:49 am UTC

</code> http://www.oracle.com/technology/pub/articles/hunter_rac.html

is about the cheapest setup I've seen.

We have run 10g RAC on RH30 using NFS storage, but this may or may not be supported in production.  In our case, a third Linux machine provided the NFS storage.  We ran the NFS server on a Fedora box, the newer kernel having much better I/O performance. This was for a demo system, not production and is certainly not best practice as the NFS machine is a single point of failure.

You could configure the third box as n i-scsi provider  - see:

http://linux-iscsi.sourceforge.net/ <code>

for details

dbms_output

A reader, September 16, 2004 - 10:07 pm UTC

One of your 10g new features presentations has this

"sqlplus - dbms_output after select"

what does that mean exactly?

Thanks

Tom Kyte
September 17, 2004 - 8:25 am UTC

ops$tkyte@ORA9IR2> create or replace function f return number
  2  as
  3  begin
  4          dbms_output.put_line( 'Hello World' );
  5          return 42;
  6  end;
  7  /
 
Function created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select f from dual;
 
         F
----------
        42

<b>in 9ir2 and before, sqlplus dumps dbms_output output after modification dml and after plsql runs, so, in order to see our output we have to do this:</b>

ops$tkyte@ORA9IR2> exec null
Hello World
 
PL/SQL procedure successfully completed.
 
<b>in 10g, this happens:</b>

ops$tkyte@ORA10G> select f from dual;
 
         F
----------
        42
 
Hello World
ops$tkyte@ORA10G> 


<b>sqlplus runs dbms_output.get_lines and dumps the buffer after a select now... (means I've got lots of "exec null" to clean up :)</b>  In 5 years, people will be asking me "whats up with this exec null stuff in your scripts -- or they will ascribe some special magic meaning to it... mythology in the making i think
 

exec null

A reader, September 17, 2004 - 11:36 am UTC

Thats funny (the mythology part)

But seriously, of what use is this? Why would you need to see dbms_output after a select? To see debugging statements in the code and such?

And this affects only sqlplus so whats the big deal? Any arbitrary Oracle session is always free to do dbms_output.get_lines and dump the buffer on their own if they want in any version.

Thanks

Tom Kyte
September 17, 2004 - 11:54 am UTC



the use is to see dbms_output stuff after a select -- i use it all of the time, search for

"exec null"

on this site. It was listed as a "new sqlplus feature"

"sqlplus - dbms_output after select"





datapump

reader, September 18, 2004 - 10:36 am UTC

Why datapump uses XML to export metadata rather than DDL as conventional exp does? Any advantage in that? I have obeserved that this XML format metadata makes Datapump export dump file larger than original exp dump file? I was wondering what is the significance of this feature in Datapump? Thanks. You are the best.

Tom Kyte
September 18, 2004 - 11:27 am UTC

I guess because XML is really cool (sarcasm)

Here it actually has a purpose, it is useful. The filters (change tablespace X to Y, datafile Z to A, this to that) are implementable as style sheets -- transformations -- to turn the XML into DDL to be applied. It lets us treat the DDL as something we can query (exclude these, include these, look for that, transform this) easily.

DDL is just text

XML that happens to have data in it that could be transformed into DDL is something we can process more intensely -- many of the new features would be really hard without it (imp and exp would need to parse DDL in order to perform the transformations -- now, they just need to be able to develop simple style sheets to do the same)

ASM and control files

Mark, October 11, 2004 - 11:34 pm UTC

Tom,

I have a question about dealing with control files stored in an ASM disk group. Suppose I have two disk groups with a control file located on each. I decide to add a third diskgroup, and would like add a third control file. What is the best way to add the third control file?

Before ASM, I'd just stop the database, make a copy of the control file, startup nomount, alter system set control_files = 'control01...control03' spfile=spfile, shutdown, startup.



Tom Kyte
October 12, 2004 - 7:57 am UTC

the create controlfile command can be used (outlined in the admin guide).

alternatively, put the controlfile into a high redundancy group (triple mirror).

</code> http://docs.oracle.com/docs/cd/B14117_01/server.101/b10739/storeman.htm#sthref1629 <code>

10g Oracle Interconnect adapter

Sudip Bhowmik, October 13, 2004 - 9:03 am UTC

Tom,currently I'm in soup and I think only you can rescue me.I am installed Oracle AS interconnect(10g).I can able to connect different oracle database residing on different machines.But I want to connect one mssequel server residing on different machine.Because of this I downloaded JDBC .But I'm unable to connect SQL server.would you suggest some configuration solution so that we can solve the problem..
i have installed HUB and Adapters for database and advanced Queue ...next scenario is how to run the applications..
if at all there is a need to configure the adapter.ini file then please give the detail code...what other changes we require to make so that it will work..bye..


Tom Kyte
October 13, 2004 - 9:14 am UTC

can you write a standalone java/jdbc program using those jdbc adapters you downloaded from somewhere and connect to sqlserver successfully (eg: remove big pieces of code and see what you can do first)....




configure Oracle Metalink Credentials

Dawar Naqvi, October 14, 2004 - 12:49 am UTC

Tom,

Do you have experince with configuration of Oracle Metalink Credentials from db console home page?

If yes please read below.

I have installed Oracle Enterprise Manager 10g (Oracle DB version: 10.1.0.2.0)
OS:SuSE Linux Enterprise Server.

Now I'd like to configure Oracle Metalink Credentials from home page of console.
It will help us for downloading latest patches.
Because we have proxy settings I need to configure proxy setting. I do not know how to do that yet?

OR

Our information security folk ask me to provide ip address & port number of http:\\updates.oracle.com.
He can allow this web site browse with out any restriction.
The IP address of this site is 148.87.36.12.

What is the port no for this site?


Regrads,
Dawar


Tom Kyte
October 14, 2004 - 9:37 am UTC

http protocol runs on port 80 unless you have an explicit port number in the URL itself.

so, 80

resource manager in 10g

John, October 16, 2004 - 12:17 pm UTC

I was reading the doc about mapping modules or service names to consumer groups. Does it mean that is it possible now in 10g to map the common username to a resource group in three tier architecture? If so, this is a great enhancement to resource manager capability. To map a common user name such APP_USER to a consumer group, how do I do? Any specific syntax or command that I should use. Thanks much for your time.

Tom Kyte
October 16, 2004 - 12:59 pm UTC

in 10g, things can be done by the TNS service you use to connect. Yes, it is for 3 tier architectures where the user isn't really known to the database in too many cases.

application1 connects as app/app@hr_database
application2 connects as app/app@finapps_database

under the covers -- both are the same database, but different services -- things can be done at the service level instead of user.

tns service

reader, October 19, 2004 - 4:50 pm UTC

When you say TNS service (above comment) is it the connect string that we specify in the tnsnames or SERVICE_NAME? Thanks.

Tom Kyte
October 19, 2004 - 8:44 pm UTC

it is the SERVICE_NAME.

ORA-942

A reader, October 20, 2004 - 9:32 am UTC

9ir2 enhanced the error message for ORA-904 to add the %s i.e tell us which column it is complaining about.

But ORA-942 still doesnt have the table/view name it is complaining about.

Do you know if 10g has fixed this?

Thanks

Tom Kyte
October 20, 2004 - 11:33 am UTC

ops$tkyte@ORA10G> select * from t2;
select * from t2
              *
ERROR at line 1:
ORA-00942: table or view does not exist

same thing.
 

A reader, October 20, 2004 - 12:00 pm UTC

Darn. That would be a good enhancement request! Thanks

DBMS_FILE_COPY

reader, October 22, 2004 - 11:00 am UTC

(1)Can I use this package to copy a file from one os to another?
(2) Does the file be owned by oracle user part of the dba group? or can I copy other binary files as well?
(2) The doc says block size should be a multiple of 512 bytes to use this package to copy files? What does it mean by this?

Thanks.

Tom Kyte
October 22, 2004 - 6:01 pm UTC

assuming dbms_file_transfer...

1) as long as the other os is running oracle.
2) the userid the database is running as needs to be able to READ the source file and write to the destination.
3) this was designed primarly to copy datafile files -- not just any file in general. It wants the filesize to be a multiple of 512. eg: filesize/512 = integer value.

10g new features

reader, October 23, 2004 - 1:30 am UTC

I was reading the doc about segment advisor that would analyze tables for example and come up with recommendation to shrink tables to reclaim space below HWM. I was wondering what is the criteria the segment advisor uses to find segments that need shrinking? For example, If I go and delete a couple of hundred rows or thousands of rows from a table would it be a candidate table for the advisor? Thanks


Tom Kyte
October 23, 2004 - 9:57 am UTC

it is based on %'s -- if X% of the table is whitespace, recommend to shrink it.

it is not based on "rows", but rather the relative percentage of the table that is "empty" I believe (but have not proven :)

it cannot be done based on rows -- it can only be done based on "size of empty space" or "% of empty space"

adding a controlfile in ASM

David, October 24, 2004 - 11:18 am UTC

I was reading the discussion above about adding a controlfile to database in ASM. If I understand correctly from your answer above, if CREATE CONTROLFILE is the only way to do it, that is going to be a problem as I would loose RMAN information after recreating the controlfile. Are you sure there is no ther way to do it rather than recreating controlfile? Thanks.

Tom Kyte
October 24, 2004 - 11:28 am UTC

you don't have a repository for rman?

I'm not aware of another method in ASM, but I haven't done a deep dive into ASM myself as yet.

Oracle Developer

Dawar, October 25, 2004 - 12:00 pm UTC

Hello Tom,

Can we use Job Schedule utility in 10g to pick the particular files from Main Frame (IBM) and to dump into Oracle DB?

cheers,
Dawar



Tom Kyte
October 25, 2004 - 12:04 pm UTC

sure, if you can get the files using plsql, java, whatever -- ftp, whatever, you can schedule a job to run.

just a warning on scheduler...

connor, October 25, 2004 - 12:19 pm UTC

before you go prime time with the scheduler you probably want to patch to 10.1.0.3.

there's some issues with the base release



Oracle Developer

Dawar, October 25, 2004 - 12:47 pm UTC

Connor,

My DB version is 10.1.0.2.0.

Regards,
Dawar

data pump

reader, October 25, 2004 - 1:22 pm UTC

It is using external table mechanism to extract data whenever direct path is not possible. Is it like CTAS or anyhing proprietory tool to extract data from tables? Thanks.

Tom Kyte
October 25, 2004 - 1:29 pm UTC

data dump is the next generation exp/imp tool.

not sure what you mean -- you talk of external tables and direct path? CTAS and "extract"?

Data pump

reader, October 25, 2004 - 3:04 pm UTC

Sorry for the confusion above. I was wondering what mechanism data pump uses to extract data from tables? Regular exp uses SELECT * FROM T. Similarly what is the mechanism datapump uses. The doc says it calls the package DBMS_DATAPUMP. What does the procedure in the package do to extract data? again do they issue SELECTs? Thanks.

Tom Kyte
October 25, 2004 - 3:36 pm UTC

expdb pretty much does direct pathing type of operations.

you can create an external table as SELECT using sql as well. (in the data pump format). that would use a create table command to create a datapump format file in the OS.




code for builtin procedures

David, October 27, 2004 - 2:08 pm UTC

Is it possible to find code for built-in plsql procedures like DBMS_STATS.GATHER_TABLE_STATS or 10g automated job that runs procedure DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC?

Are they wrapped so even DBAs cannot see the code? Thanks.

Tom Kyte
October 27, 2004 - 4:18 pm UTC

nope, not at all. most of them are in C anyway.

you can sql_trace them to see their sql, but not the code.

you can sql_trace them to see their sql

reader, October 29, 2004 - 3:15 pm UTC

You mean the recursive sql issued by oracle for dbms_stats procedure? Thanks.

Tom Kyte
October 29, 2004 - 5:08 pm UTC

yes

ASM

David, October 30, 2004 - 11:23 am UTC

Mirroring makes sense only If I have data spread across disks that are part of different controllers. Does ASM have the intelligence to give me an error if I created failure groups with disks part of the same controller? Thanks.

Tom Kyte
October 30, 2004 - 2:12 pm UTC

no it doesn't?

mirroring is effective with a single controler -- multiple disks.

Sure, if the controller fails, you lose access to the disks, but you haven't lost a disk. You don't need to go into recovery mode, you just need to fix the controller.

But, separating them out over different hardware is recommended.


Mirroring is a physical disk thing.

ASM question

Robert, October 30, 2004 - 9:18 pm UTC

Suppose I have defined 2 failure groups each has 4 disks. In this configuration, assume ASM has mirrored all of data. i.e. One group with 4 disks have original data and the other group with 4 disks has mirrored data. Now, if I lost a disk in the mirrored group, what happens? I don't have space for ASM to mirror? Would database instance hang? What kind of error I would see? Thanks.

Tom Kyte
October 31, 2004 - 3:00 am UTC

We mirror extents -- not entire disks, so we can be quite flexible.

In the event of a disk failure in a failure group it will perform a rebalance, the contents (data extents) of the failed disk are reconstructed using the redundant copies of the extents from the partnered disk that resides in failure group. During the rebalance, if the database instance needs to access an extent whose primary extent was on the failed disk, then the database will read the mirror copy from the appropriate disk in failure group. Once the rebalance is complete, and the disk contents fully reconstructed, the database instance will return to reading primary copies only.


ASM question continued...

Rob, October 31, 2004 - 10:44 am UTC

After the failure of a disk (partner disk) in the failure group, during rebalance if there is not enough space to mirror the extents, I was wondering what would happen? Because I have chosen two way mirroring for a ASM file but ASM could not find space to mirror. At that time ASM should give an error. Is it not? OR ASM recognizes that there is no space to mirror and does it stop mirroring? Thanks.

Tom Kyte
October 31, 2004 - 11:38 am UTC

If there is not enough space to recontruct the data from a failed drive, then the rebalance will stop, and some data will have only one copy.

The database will still be able to read data, but will be unable to create new files in that disk group, and will be unable to tolerate a subsequent disk failure.

You would be adding a new disk really soon (or taking the data offline if that is your wish)

dbcontrol

reader, November 01, 2004 - 8:29 pm UTC

Is there an agent process for dbcontrol? For grid control there should be an agent process on each box. I was wondering is it necessary for dbcontrol? Thanks.

Tom Kyte
November 02, 2004 - 7:16 am UTC

dbconsole is the "agent", it has everything you need to manage a single instance of the database.

scheduler

reader, November 03, 2004 - 1:35 pm UTC

Is it possible to create a schedule to run a job say at 5, 10, 20, 35, 55 (at arbitrary minutes as we can do in cron)? Thanks.

Tom Kyte
November 05, 2004 - 10:46 am UTC

yes, you can either do it from dbms_job in the job itself or you can come up with any function that returns the next date you want.

do you have my book "Expert one on one Oracle"? I show how the job can compute its "next time" or how you can write any function you want.

(case when to_number(to_char(sysdate,'mi')) < 5 then 5
when to_number(to_char(sysdate,'mi')) < 10 then 10
when to_number(to_char(sysdate,'mi')) < 20 then 20
when to_number(to_char(sysdate,'mi')) < 35 then 35
when to_number(to_char(sysdate,'mi')) < 55 then 55
else 65
end)/24/60 + trunc(sysdate,'hh24')

would do 5, 10, 20, 35, 55 for example.

temporary tablespace group

reader, November 04, 2004 - 11:54 am UTC

What is the advantage of creating a group of temp tablespaces vs one large temp tablespace? If a session needs space in temp tablespace, how does oracle assign temp tablespace within the group? If the process uses all of the space in one of the temp tablespaces in the group can that process use another temp tablespace within the same group without throwing error? Thanks.

Tom Kyte
November 05, 2004 - 1:04 pm UTC

it is mostly for large scale DW enviroments with lots of concurrent sorts to disk -- to spread the workload out over many tablespaces (avoiding getting of eachother in space allocation and such)

also, if you have lots of JBOD (just a bunch of disks) it is a way to spread the IO out (striping would achieve same)

RMAN compression

A reader, November 04, 2004 - 9:19 pm UTC

10g RMAN has the ability to make compressed backups. What kind of compression is this? Over and above that done by the tape device? Is it like gzip/compress on the .dbf files? Oracle datafiles dont compress very well?

Thanks

Tom Kyte
November 05, 2004 - 4:16 pm UTC

it is binary compression like gzip.

datafiles compress EXTREMELY well? they compress very nicely actually -- massively.

RMAN compression

A reader, November 05, 2004 - 5:08 pm UTC

You are right, I was thinking about binary executables (that dont compress well).

I tried gzip --best --verbose on my system datafile (700MB) and it compressed down to 100MB!

Thanks

flash recovery area

Rob, November 06, 2004 - 12:43 am UTC

I set db_recovery_file_dest_size to 40G and my hard disk has only 20G but it did not result in any error. It allows me to set any value. I was wondering on what basis oracle could give an alert when flash recovery area becomes 85% full? If it uses the value that I set, the alert may be misleading. Thanks.

SQL> select * from v$recovery_file_dest;

NAME
--------------------------------------------------------------------------------
SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
----------- ---------- ----------------- ---------------
/u01/app/oracle/flash_recovery_area
 4.2950E+10          0                 0               0
 

Tom Kyte
November 06, 2004 - 10:40 am UTC

that is a cap on the max space you want it to use, the real cap will be "so sorry, out of diskspace"

it is like setting your "maxsize" on an autoextend datafile to 2048m but only having 1m of free space left -- it'll stop when it runs out of space

ASM

reader, November 06, 2004 - 10:56 am UTC

We are evaluating ASM. It looks very promising. We are excited about migrating our database(s) to ASM. I have a question, if I may, regarding the best practice of placing spfile and controlfile. Especially if I placed spfile on the diskgroup say, DiskGroupA, when Oracle starts up it will be looking for spfile from the default location $ORACLE_HOME/dbs. I was wondering do I have to have a pfile in ORACLE_HOME/dbs that has an entry like spfile=+DiskGroupA/spfile.ora? We would like to know how does oracle find spfile if it is placed in an ASM diskgroup? Thanks.

Tom Kyte
November 06, 2004 - 11:19 am UTC

yes, would we use a one line pfile to say where the spfile is to be found.

statistics_level

reader, November 09, 2004 - 11:45 am UTC

The MMON process pushes stats from memory to AWR every hour by default. In our environment, there is no database activity from 6 pm to 6 am. Is it advisable to turn off this automated collection of stats to disk by setting statisctics_level = basic during the off hours? Do you recommend it?

Tom Kyte
November 09, 2004 - 12:13 pm UTC

no, if no one is using it -- why bother?

statistics_level

Reader, November 09, 2004 - 12:18 pm UTC

Good point. Another question related to this. In one of our databases we do bulk load for about 4 to 5 hours in the middle of the night? Is it helpful if I set statistics_level = basic during the bulk load time period to avoid some overhead and then turn it back on after the load is complete? Thanks.

Tom Kyte
November 09, 2004 - 1:21 pm UTC

nope, that'd be a time you want instrumentation on, so you can trend how it is going over time -- especially since you won't be there.

sql profile

reader, November 09, 2004 - 6:28 pm UTC

(1) Is sql profile in 10g specific for a query?
(2) Can a sql statement have multiple profiles associated with it?
(3) If a sql profile already exists for a sql query, and then if I run the sql tuning advisor on that sql statement, would oracle update the existing sql profile or creates a new one? Thanks.

Tom Kyte
November 09, 2004 - 7:50 pm UTC

see

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:22829633914543, <code>


they are for sql statements, you can have multiple categories (so different "sessions" can see different ones), depends on whether you used a different category or not.

But that link above has links straight into the documentation that goes into the sql profiles, their related views, and associated API's for managing them.

Kamal Kishore, November 09, 2004 - 10:55 pm UTC

hi Tom,
Has __db_cache_size superceded the db_cache_size initialization parameter?


SQL> show parameter db_cache_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
__db_cache_size                      big integer 100M
db_cache_size                        big integer 0
SQL>
SQL> disconnect
Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL>
 

Tom Kyte
November 10, 2004 - 6:59 am UTC

No, the other one is involved if you use the SGA TARGET. it is not "replacing it", it is a different way to do it (an internal way, the self sizing SGA way)

scheduler jobs

reader, November 10, 2004 - 10:14 am UTC

In 10g new SCHEDULER, can sys or system stop or disable a job created by another user? In dbms_jobs I believe only the job owner can "kill" the job for example. Thanks.

Tom Kyte
November 13, 2004 - 10:30 am UTC

yes, an appropriately priveleged user can remove the job:


ops$tkyte@ORA10G> create or replace procedure the_job
  2  as
  3  begin
  4          null;
  5  end;
  6  /
 
Procedure created.
 
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> grant create job to ops$tkyte;
 
Grant succeeded.
 
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> alter system set resource_limit=true;
 
System altered.
 
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> BEGIN
  2     DBMS_SCHEDULER.CREATE_PROGRAM
  3     ( program_name        =>'THE_TEST_JOB',
  4       program_action      =>'begin the_job; end;',
  5       program_type        =>'PLSQL_BLOCK',
  6       number_of_arguments =>0,
  7       comments            =>'demo job',
  8       enabled             =>TRUE);
  9  END;
 10  /
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> begin
  2     sys.dbms_scheduler.create_schedule
  3     ( repeat_interval => 'FREQ=DAILY;INTERVAL=2;BYHOUR=18;BYMINUTE=0;BYSECOND=0',
  4       start_date      => to_timestamp_tz('2004-11-13 US/Eastern', 'YYYY-MM-DD TZR'),
  5       comments        => 'demo job...',
  6       schedule_name   => 'my_schedule');
  7  end;
  8  /
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> BEGIN
  2     sys.dbms_scheduler.create_job
  3     ( job_name      => 'RUN_THE_JOB',
  4       program_name  => 'THE_TEST_JOB',
  5       schedule_name => 'MY_SCHEDULE',
  6       job_class     => 'DEFAULT_JOB_CLASS',
  7       comments      => 'just a demo still',
  8       auto_drop     => FALSE,
  9       enabled       => TRUE);
 10  END;
 11  /
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA10G>
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> exec print_table( 'select * from user_scheduler_jobs');
.JOB_NAME                     : RUN_THE_JOB
.JOB_CREATOR                  : OPS$TKYTE
.CLIENT_ID                    :
.GLOBAL_UID                   :
.PROGRAM_OWNER                : OPS$TKYTE
.PROGRAM_NAME                 : THE_TEST_JOB
.JOB_TYPE                     :
.JOB_ACTION                   :
.NUMBER_OF_ARGUMENTS          :
.SCHEDULE_OWNER               : OPS$TKYTE
.SCHEDULE_NAME                : MY_SCHEDULE
.START_DATE                   : 13-NOV-04 06.00.00.000000 PM US/EASTERN
.REPEAT_INTERVAL              :
.END_DATE                     :
.JOB_CLASS                    : DEFAULT_JOB_CLASS
.ENABLED                      : TRUE
.AUTO_DROP                    : FALSE
.RESTARTABLE                  : FALSE
.STATE                        : SCHEDULED
.JOB_PRIORITY                 : 3
.RUN_COUNT                    : 0
.MAX_RUNS                     :
.FAILURE_COUNT                : 0
.MAX_FAILURES                 :
.RETRY_COUNT                  : 0
.LAST_START_DATE              :
.LAST_RUN_DURATION            :
.NEXT_RUN_DATE                : 13-NOV-04 06.00.00.000000 PM US/EASTERN
.SCHEDULE_LIMIT               :
.MAX_RUN_DURATION             :
.LOGGING_LEVEL                : RUNS
.STOP_ON_WINDOW_CLOSE         : FALSE
.INSTANCE_STICKINESS          : TRUE
.SYSTEM                       : FALSE
.JOB_WEIGHT                   : 1
.NLS_ENV                      : NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA'
NLS_CURRENCY='$' NLS_ISO_CURRENCY='AMERICA' NLS_NUMERIC_CHARACTERS='.,'
NLS_CALENDAR='GREGORIAN' NLS_DATE_FORMAT='DD-MON-RR'
NLS_DATE_LANGUAGE='AMERICAN
.SOURCE                       : ORA10G
.DESTINATION                  :
.COMMENTS                     : just a demo still
.FLAGS                        : 4228180
-----------------
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> @connect system/oracle
ops$tkyte@ORA10G> set termout off
system@ORA10G> @login
system@ORA10G> set termout off
system@ORA10G> set termout on
system@ORA10G>
system@ORA10G> exec dbms_scheduler.drop_job( 'OPS$TKYTE.RUN_THE_JOB' );
 
PL/SQL procedure successfully completed.
 
system@ORA10G>
system@ORA10G> @connect /
system@ORA10G> set termout off
ops$tkyte@ORA10G> @login
ops$tkyte@ORA10G> set termout off
ops$tkyte@ORA10G> set termout on
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> exec print_table( 'select * from user_scheduler_jobs');
 
PL/SQL procedure successfully completed.
 
 

flashback database

Sam, November 11, 2004 - 11:33 am UTC

Can I use RECOVER PARALLEL to do FLASHBACK DATABASE to use parallel processes just like I can specify parallel when I do conventional recovery? Thanks.

Tom Kyte
November 11, 2004 - 1:33 pm UTC

it is done very differently from a conventional recovery. we take some before images of blocks when needed and just roll them forward a bit from the flashback recovery area. there is no "recover" clause with the flashback really.

So ss flasback database done serial by one process? Thanks.

reader, November 11, 2004 - 1:51 pm UTC


Grid Control

Joseph, November 13, 2004 - 11:49 am UTC

(1) Is it true that 10g Grid control uses 9i database for repository? If so, is it like 10g grid control cannot use 10g database for its repository? Thanks.

(2)In our environment, we have a lot of events/jobs created in 9iR2 OEM repository. If we use 10g Grid control how do we migrate all of these to grid control repository? I remember reading the doc that says, before I create a repository for 10g grid control, I should drop SYSMAN schema from my existing repository database. Then, how am I going to migrate? Thanks.

(3) Also, I myself was wondering about the question above by a reader on flashback databse whether it is done using Single process or parallel? Thanks.

Tom Kyte
November 13, 2004 - 1:07 pm UTC

1) 10.1.0.2 used 9i, 10.1.0.3 (currently shipping release) uses 10g for the repository database.

2)
</code> http://docs.oracle.com/docs/cd/B14117_01/em.101/b12013/migration.htm#sthref823 <code>


3) is is serial as far as I know. it is not a conventional recovery process at all.

temp tspace groups

Connor, November 17, 2004 - 6:16 am UTC

Just thinking aloud here...Whilst I didn't really see any great benefit for temp tspace groups, one possible opportunity is the "transient" increase of temp tspace size.

Lets say you had 8G of temp tspace, but you wanted for (say) a massive operation, 20G of space. If you add 12G of tempfiles, then at the end of operation, you can't just drop that 12G - you have to drop the 20G and then re-add the 8G. (Addmittedly, tempfile ops are pretty quick but you still have that little moment where you either have no temp tspace, or you have redirect all the user accounts to another one)

With temp tspace groups, you would have the 8G tspace in a group, then add a 12G one to it, and then drop the 12G tspace at the end of the exercise. Easy!

hth
Connor

Flashback table

reader, November 20, 2004 - 8:47 pm UTC

Does flashback table feature applicable only for Heap or iot and clusters as well?

Q on ASM

Menon, November 24, 2004 - 5:16 pm UTC

How common is it to have an ASM target (in the limited
cases it is being used or expected to be used) where the
same disk group is serving multiple databases? I know
that this is feasibile but how often do (or will) people
use it in this fashion.

Thanx

Tom Kyte
November 24, 2004 - 5:18 pm UTC

time will tell -- but how many people use the same SAN or logical volume for more than one database?

a diskgroup is just a logical volume for databases.

Oracle Consultant

Dawar, November 30, 2004 - 7:01 pm UTC

Tom,

MY Server OS is SuSE Linux Enterprise Server 8.

My Oracle Database version is 10.1.0.2.0.

I need to transfer few files from my server to the window.

What is the secure way to transfer files from server to the window on same network?

Please note: I have installed putty (SSH) on my window.
I can connect to the server through putty but I wanted to transfer files from the server.

When I used FTP I got as follows:

ftp: connect: Connection refused

Regards,
Dawar



Tom Kyte
November 30, 2004 - 8:46 pm UTC

"to the window"

like the window you look out of?

Dave, December 01, 2004 - 4:11 am UTC

I guess you mean windows, use sftp or scp - ftp is obviously blocked.

If you dont have them, download them

SCP or SFTP

Simo, December 01, 2004 - 4:13 am UTC

If its from windows to unix winscp is pretty good to move files, you can find it from </code> http://winscp.sourceforge.net/eng/ <code>

Otherwise from unix commandline you can usually use sftp or scp.

Oracle 11L

Martin, December 03, 2004 - 4:16 am UTC

Tom,

(I think) I heared someone from oracle mention the "Oracle 11L" database. I cannot find anything on it on the net however.

Is the next major release indeed called 11L and if so could you tell what the L is for?



Tom Kyte
December 03, 2004 - 7:57 am UTC

it was probably a "joke"

10g Release 2 is next.

Oracle 9i to 10g Upgrade issues

Ashok K rathi, December 10, 2004 - 8:42 am UTC

Do you know any known commands or syntax which was working on Oracle 9i but it is not going to work on Oracle 10g ?

I know the copy command is deprecated . This command is not going to work on Oracle 10g ?




Tom Kyte
December 10, 2004 - 10:59 am UTC

it works on 10g -- the list of deprecated/not supported stuff is documented.

goto otn.oracle.com
goto documentation -> search
put in deprecated and ask it to create a virtual "book"

re your demo on dropping a scheduler job

reader, December 11, 2004 - 8:35 pm UTC

Why do you say as ops$kyte,
alter system set resource_limit=true;
What does the above command do?

Also, you granted only create job priv to ops$kyte. Does this priv include create program and create schedule? Thanks.


Tom Kyte
December 12, 2004 - 9:57 am UTC

it permits additional tracking of resources used by a job -- the CPU_USED column in the dba_scheduler_running_jobs for example.


This user:

ops$tkyte@ORA10G> create user a identified by a;
 
User created.
 
ops$tkyte@ORA10G> grant create session to a;
 
Grant succeeded.
 
ops$tkyte@ORA10G> grant create procedure to a;
 
Grant succeeded.
 
ops$tkyte@ORA10G> grant create job to a;
 
Grant succeeded.
 
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> connect a/a


could be used to run the example instead of ops$tkyte.  create job is sufficient. 

Oracle Consultant

Dawar, December 14, 2004 - 12:36 pm UTC

Tom,

I am maintaining & using Oracle Database from some time but never have chance to work with grid.

Could you please tell me what is grid?

And how its work with Oracle.
Please give me some details on the featrures of an Oracle Grid.

Second Part of the question:

We have Oracle 10.1.0.2.0 database running on our server.
I'd like to install grid.
Do I need to install Grid on the same server?
OR on the Separate box.


Regards,
Dawar



Tom Kyte
December 15, 2004 - 12:38 pm UTC

</code> http://www.oracle.com/grid <code>

have you ever transported a tablespace? -- grid
have you played with ASM -- grid
streams -- grid
RAC -- grid
10g Enterprise Manager -- aka "grid control"

Grid is simply using lots of small computers as if they were one. Moving stuff around, using the resources when and where you want to.

Fewer invalidations

A reader, December 14, 2004 - 11:33 pm UTC

Reading

</code> http://www.shadeware.com/archives/000020.html <code>

One of the things mentioned is "Fewer invalidations"

Change table without
-recompiling Stored Procedures
-recompiling Views

How is this achieved? I can ALTER TABLE ADD a column and stored procs and views depending on that table are NOT invalidated? Can you please elaborate?

Thanks

Tom Kyte
December 15, 2004 - 1:28 pm UTC

goto "files" in the tab bar and you can get my presentation/scripts.

you can repoint a synonym and the dependent objects (on the synonym) do not have to get invalidated anymore.

Invalidations

A reader, December 15, 2004 - 3:20 pm UTC

1. OK but how is this implemented? Does the 'create or replace synonym' command really go and recompile all dependent objects so that command takes longer but no subsequent upon-acces recompliations are needed?

2. You said "I used to use loopback database links and views, this is infinitely easier and faster"

Can you please elaborate how loopback links and views can achieve the same result? Thanks


Tom Kyte
December 15, 2004 - 6:22 pm UTC

1) if you repoint a synonym - thats it. period. done.


2)
create a loopback link (a link that points to the same database)

create view V as select * from synonym@loopback_link;

I'm using signature based dependency tracking, so as long as the signature didn't change -- V won't go invalid, even if we play with synonym.

but, I've got two sessions now....

hence this is much easier.


asm

reader, December 18, 2004 - 2:20 pm UTC

You were recommending ASM in this thread. In our shop, we already have invested in Veritas Logical Volume manager and currently we have set up everything that is functioning well. How do I convince my management to migrate to ASM? What are the merits/advantages I could get by using ASM that I cannot get from Veritas? If the management asks me "give me three good reasons why we should migrate to ASM'? because currently if the disk fails we can do hot swaps and we can add disks to logical volume manager. no problem. I am struggling to make a point to my management what would be the "killer" reason to switch to ASM? I thought I could ask you. Thanks.

Tom Kyte
December 18, 2004 - 3:59 pm UTC

Ok, it is six months from now. you request 500gig more storage for your already existing database.

What will you get? an empty new mountpoint? now what.

ASM is three things

o manageability from the DBA perspective, the SA shouldn't be involved in disk allocation to the database, the DBA should be. With your current setup now, who owns it. Does the SA come in over the weekend to move your datafiles (offline operation) or do you do that? And are the end users appreciative of that downtime? ASM -- just slide it in, slide it out.

o availability. if your hardware also does it, so be it, but the database can mirror 2/3 way -- and if a 36gig disk fails -- we just need to find 36gig out there -- we remirror the data straight away (it is not mirrored pairs, it is mirrored and striped). So availability is never compromised, you don't need a closest full of hot spairs, we'll just use whatever additional capacity is out there and let you know "you've lost a disk, get one when you can"

o performance, it is just a database file system, nothing more, nothing less. Only what is needed to support a datafile file system -- no bells, no whistles, not generic at all, very specific.


But at the end of the day, if your mgmt want's to stay with what they go -- no problem. It costs more, but it works. With extra work on the dba's part, but it works.

ADDM feature, how fast are yur disks

Pinguman, December 20, 2004 - 7:07 am UTC

Hi

I am reading ADDM Feature and DBIO_EXPECTED come across. By default this is set to 10ms (10000) however in practice how should us determine if this value is adequate for our I/O subsystems? May be a EMC Clariion (using IDE disks) 10ms is fine but if we have a fiber channel EMC Symmetrix may be 10ms is too slow. Do us determine DBIO_EXPECTED from hardware vendors disk specifications? For example if the vendor says their disks seek time is 8ms should we set 8ms for DBIO_EXPECTED?

Thank you

Tom Kyte
December 20, 2004 - 8:41 am UTC

</code> http://docs.oracle.com/docs/cd/B14117_01/server.101/b10752/diagnsis.htm#34569 <code>

you can either measure it with some utility or accept what the vendor says should be true.

ASM best practices

Mike Friedman, December 20, 2004 - 10:07 am UTC

Speaking of ASM, one thing I can't find is any document explaining best practices if you want to set up a new server from the ground up and use ASM.

My impression after reading about it is to forget about RAID, etc. and just get 2 SCSI controllers and a bunch of disks, make each SCSI controller a Failure Group, and just mirror between them.

Is this the way to go? Or something else?

Connecting to ASM instance

Arun Gupta, December 22, 2004 - 11:00 pm UTC

Tom,
After the presentation in Harrisburg, I downloaded the Desktop Data Center for Red Hat Linux and it works fine. It is really amazing how much Oracle configuration has been done in such a concise set up. It is an excellent way to study RAC/OCFS/ASM configuration.

I was checking the ASM configuration. My question is: Is it possible to connect to ASM instance from a client or do I have to be logged on to the server to connect to ASM instance? No matter what I do, the listener for ASM shows BLOCKED status and I cannot connect from a client. I get ora-12528 error.

Thanks


Tom Kyte
December 23, 2004 - 12:37 pm UTC

you'd manage it locally or via enterprise manager nornmally.

You can put an explicit entry in the listener.ora if you like, they just didn't set it up that way.

ADDM

reader, December 24, 2004 - 8:26 pm UTC

<quote>you can either measure it with some utility .....<quote>

Can I use v$filestat to get a value for it? Thanks.

Tom Kyte
December 24, 2004 - 9:54 pm UTC

v$filestat will show you sort of observed times (can be misleading with async io in write cases).

your vendor of the hardware should have better tools -- to measure THEIR throughputs.

asm ....

reader, January 12, 2005 - 2:46 pm UTC

If I have two way mirroring using ASM, does DBWR process write the same data twice on asm disks? Thanks.

Tom Kyte
January 12, 2005 - 3:53 pm UTC

technically, it is ASM that writes both, but the ASM code is called by dbw to do the IO.

asm and dbwr

reader, January 12, 2005 - 4:14 pm UTC

I understand what you say. However, ultimately dbwr process is the one that actually does the write. am i right? Or does ASM use some other process to write on behalf of dbwr? I am thinking in terms of overhead for dbwr process if it has to write mirrored extents twice in two way or thrice in three way mirroring. Thanks.

Tom Kyte
January 12, 2005 - 6:00 pm UTC

its that same process, and it'll typically be async and directio. (so dbwr isn't waiting for the io's to complete to do the next io - it just has to wait for all of the io's to complete ultimately to say "i'm done")

asm - asynch and direct io??

Rob, January 12, 2005 - 10:55 pm UTC

I thought raw devices do not use os cache. ASM disks are raw devices? Right? My understanding is that the reason why raw devices is faster than file system is that DBWR process writes directly on the disk rather than writing to os cache. Maybe I am confused. To clarify, even though I use raw or asm devices, dbwr writes to os cahe and then os processes wtites to disk? Thanks.

Tom Kyte
January 13, 2005 - 8:21 am UTC

correct, directio. asm diskgroups (filesystems) are built on raw things just like a filesystem is. ask diskgroups have "physical devices" under them just like everything else (nothing special or scary there).

ASM is like a filesystem. A filesystem for oracle database stuff. There is no "filesystem cache" here.

asm and direct io

Rob, January 13, 2005 - 1:15 pm UTC

When you say directio on ASM disk, does it use os cache or not? Does asynchio need os cache? Does DBWR process place the blocks in os cache in direct io? could you clarify? Thanks.

Tom Kyte
January 13, 2005 - 2:05 pm UTC

no cache. directio is no cache.

async io means dbwr doesn't wait for writes to complete, it can fire off 100 writes and then get signaled 100 times that they are done. instead of write/wait/write/wait.... it does write/write/write/write..... wait

direct io

reader, January 13, 2005 - 5:16 pm UTC

<quote>no cache. directio is no cache.<quote>

When asynchio is used with directio, in which cache dbwr process places the blocks for them to get written to data files? Thanks.


Tom Kyte
January 13, 2005 - 6:32 pm UTC

it doesn't, it hands it to the OS and says "write this and signal me when you are done", OS takes buffer to be written, writes it in a blocking fashion, signals that it is done.

incrementally updated image copy

James, January 26, 2005 - 7:55 pm UTC

I am fascinated with this new feature in 10g. So, my understanding is that first day level 0 image copy of database is taken, next day incremental backup level 1 is taken, the third day the incremental backup is applied to level 0 backup to "roll forward". My question is if I wanted to restore the "original" level 0 backup can I do that? because as the "original" level 0 backup is nolonger "original" as it is updated with incremental? right? Thanks.

Tom Kyte
January 27, 2005 - 8:02 am UTC

you are catching the backup "up", instead of tons of incrementals sitting next to eachother, they are applied on top of one another.

the concept is:

a) day 0 you take a full backup. this goes onto disk. You also smartly put this onto tape or something else.

b) day 1 you take an incremental and lay it on top of the disk image from day 0. on disk, you have what appears to be a full backup of the database. You also put this to tape (on tape you have day 0, day 1 - two full backups, but you took only one full backup)

c) day N you take an incremental and lay it on top of the disk image from day N-1. on disk, you have what appears to be a full backup of the database. You also put this to tape (on tape you have day 0, day 1, ... N - N full backups, but you took only one full backup)


You have the original backup as long as you keep it -- on disk, you have the latest "full backup", on your tapes/whatever -- where you keep the last N backups (for you HAVE to keep at least 2 -- at a minimum) -- you have the original "level 0" if you need it.


Faster FTS

A reader, February 01, 2005 - 9:25 am UTC

One of the new features of 10g is "faster full table scan".

Do you know how this has been implemented? What exactly has been made faster? FTS is pretty simple, right? Read dictionary to find header block and keep scanning extents until end-of-segment!

Thanks

Tom Kyte
February 01, 2005 - 2:02 pm UTC

do you have a pointer to where you read about this?

Faster FTS

A reader, February 01, 2005 - 2:08 pm UTC

</code> http://www.rittman.net/archives/000807.html <code>

It points to a OTN whitepaper but I didnt quite understand how the faster FTS was actually done

Tom Kyte
February 01, 2005 - 3:41 pm UTC

it seems to say "they wrote the code better, to use less cpu when full scanning, so if cpu was the issue in your data warehouse, this will help you"

incremental improvements are all over the place in the database from release to release.

10g release 2 (10.2 ) new replication feature?

JC, February 06, 2005 - 2:27 pm UTC

Hi Tom,

I heard that in 10g release 2 (10.2) there will be a new replication feature. This is not Streams replication. I guess, not advanced master-master replicaiton, either.

Questions:
- When will 10g release 2 available?
- Got any info about this new replication feature?

Thanks!!


Tom Kyte
February 07, 2005 - 3:59 am UTC

it'll be out later this year and when it does, we'll talk about it then.

Thanks. Looking forward to 10.2 release.

A reader, February 07, 2005 - 2:11 pm UTC


auto tuning of memory

Jim, February 07, 2005 - 3:16 pm UTC

In 10g, what criteria oracle uses to tune buffer cache? Is it still using buffer cache hit ratio as a criteria to expand buffer cache in auto tuning mode? Thanks.

Tom Kyte
February 07, 2005 - 4:26 pm UTC

actually, it shrinks the buffer cache to give OTHER components - not so much the other way around :)

you start with the sga_target, it sets up nominal sized pools (shared, large, etc). as they need more, take away from the buffer cache and give it to them.



Simple Replication

PK, February 09, 2005 - 12:24 am UTC

Tom,

We have just started an application development in 10gR1. As part of requirements, we need to replicate data across databases (locations). What feature do you recommend to use for replication? Streams or MVs? It may take atleast an year to get this application into production.


Tom Kyte
February 09, 2005 - 2:09 pm UTC

I always recommend when asked about how to replicate that the first thing you do is

well

reconsider... it is almost never really necessary (not never necessary, almost always)

bi-directional replication is really complex
uni-directional replication adds lots to the admin/management

single systems = simple.


but, if you have to -- and

o uni-directional is ok, mv's are easiest
o else if streams has the functionality you want, it would be recommended going forward

asm

reader, February 10, 2005 - 10:23 am UTC

How does ASM work with NAS like Netapps for example? Is there a doc on this? Thanks.

Tom Kyte
February 11, 2005 - 3:17 am UTC

otn has lots of material.

A reader, February 11, 2005 - 10:57 am UTC

Tom,

I was surprised to hear that ASM is actually not an instance by an Oracle Consultant. Tom is it for real, because in the docs startup commands are there and we could also see the background processes running?

Thanks.

Tom Kyte
February 12, 2005 - 7:51 am UTC

there is an instance, an ASM instance.

Just the same as if you were running veritas to manage your file system, there would be a veritas "instance" (processes, memory, etc) out there running.

We have processes out there running as well (you do know, a file system is a database in disguise, we've taken the costume away)

There is an instance.

EZCONNECT

John, February 12, 2005 - 11:04 am UTC

(1) can 8i and 9i clients use 10g feature EZCONNECT to connect to 10g database?
example: 9i sqlplus tool>connect system/oracle@<host>:1521/<service_name
where service_name is pointing to 10g instance.

(2) Similary, from 10g sqlplus, can one use EZCONNECT concept to connect to 9i database? Thanks.

Tom Kyte
February 12, 2005 - 12:53 pm UTC

they do not know how to parse that (the earlier clients). they can use

system/oracle@(description=....)

(eg: the don't necessarily need a tnsnames.ora entry if that is what you are trying to get around)


but the 10g client can use that information to connect to an earlier release.

mail package

John, February 20, 2005 - 10:38 am UTC

In 10g there is a new UTL_MAIL package to send emails from database. How is it different from UTL_SMTP package that is already there in prior versions? I was wondering is there a special reason that one should use UTL_MAIL instead of UTL_SMTP? Thanks.

Tom Kyte
February 20, 2005 - 11:13 am UTC

it is a layer on top of smtp, so you don't have to know how to write "smtp" in order to email. You just say "mail this".

it is easier to use

MODEL clause

A reader, February 21, 2005 - 1:55 pm UTC

Hi Tom,
I just installed oracle 10g on my machine.
Can you explain MODEL clause with example?

Thanks

data pump and undo

A reader, February 28, 2005 - 4:56 pm UTC

Hi

When we used to use import utility we could use commit=y to avoid a huge undo tablespace if the table is big. There is no such parameter in Datapump, impdp. What can we do?

Tom Kyte
February 28, 2005 - 5:25 pm UTC

it can use direct path operations which bypasses undo generation (and the UNDO for an insert is teeny tiny right -- commit=y was basically a way to say "slow=yes_please" more than anything -- indexes, no undo, tables - undo is "delete+rowid")

Advice on taking advantage of rac on data load

A reader, March 10, 2005 - 10:05 am UTC

We are migrating to a 2 node 10g rac for a datawarehouse. On our existing single node instance we have a process that runs nightly that rebuilds some materialized views. The procedure basically runs dbms_snapshot.refresh serially for several MV's. When we moved the process to the grid obviously the whole procedure runs on just one node.

Do you have any advice on how we could get the dbms_snapshot.refreshes to run on all the available nodes?

Sample psuedo code for existing proc

refresh_mvs
begin
dbms_snapshot.refresh('MV1');
dbms_snapshot.refresh('MV2');
dbms_snapshot.refresh('MV3');
dbms_snapshot.refresh('MV4');
dbms_snapshot.refresh('MV5');
end;


Tom Kyte
March 10, 2005 - 7:09 pm UTC

you can use node affinity and dbms_job - you can say "dbms_job please run this on node 1 and this on node 2"

automatic stats job...

John, March 14, 2005 - 8:37 pm UTC

If I created a 10g database MANUALLY, would it have the automatic statistics job created? If not, how do I create the auto stats gather job? Thanks.

Tom Kyte
March 15, 2005 - 7:52 am UTC

do you have a job:

sys@ORA10G> select job_name, job_creator, program_name from DBA_SCHEDULER_JOBS where job_name like 'GATH%';

JOB_NAME JOB_CREATOR
------------------------------ ------------------------------
PROGRAM_NAME
-------------------------------------------------------------------------------
GATHER_STATS_JOB SYS
GATHER_STATS_PROG


auto stats job....

John, March 15, 2005 - 11:12 am UTC

Tom, it was a hypothetical question. At work we don't have 10g yet. We are going to have license soon... I am in the process of evaluating pros and cons .... as you have all of the resources at hand I thought I could ask you the above question. Thanks. You are awesome!

Oracle 10g Features

Rohan Rekhi, March 21, 2005 - 7:42 pm UTC

Tom,

Could you please let me know Silent Features of Oracle10g

Tom Kyte
March 22, 2005 - 11:05 am UTC

see the new features guide available on otn.oracle.com-> documentation.

create text or word format file

Dawar, March 25, 2005 - 5:56 pm UTC

Tom,

Version of DB is 10.1.0.3.

I would like to create text or word format file from the data of existing table in our database.


For example:
emp table has following columns.

EMP_NO NOT NULL NUMBER(6)
DEPT_NO NOT NULL NUMBER(3)
PAY_IT NOT NULL NUMBER(4)
ITE_IT NOT NULL VARCHAR2(1)
ST_DAT NOT NULL DATE
ST_REA VARCHAR2(2)
END_DA DATE

we are getting ton of thousnads rows by
select * from emp;

Now we need to put this data in text file.

Regards,
Dawar



Tom Kyte
March 25, 2005 - 6:58 pm UTC

True Silent Install....

James, April 01, 2005 - 9:40 am UTC

From, </code> http://download-west.oracle.com/docs/cd/B14117_01/em.101/b12140/1_oui_intro.htm#sthref12 <code>

<quote>True silent capability.

When running OUI in silent mode on a character mode console, you no longer need to specify an X-server or set the DISPLAY environment variable on UNIX. No GUI classes are instantiated, making the silent mode truly silent. <quote>

What are those "GUI" classes it is talking about? Could you help me understand the meaning of true silent install? Thanks.



Tom Kyte
April 01, 2005 - 11:11 am UTC

GUI (graphical user interface) code on X would require an X server to connect to. If you tried to run installer and it loaded up a GUI class (code), it would fail.

Normally, the installer wants/needs a display.

Silent install....

James, April 02, 2005 - 8:56 pm UTC

How did oracle do in 9i compared to 10g? Did 9i need x-server packages installed even for silent install? I am little confused about the terminology "silent install". Does oracle suppress GUI screens in silent install? How is it done? Could you explain in detail? Thanks. I am a Windoes person? Is there a silent install in windows as well? Thanks.

Tom Kyte
April 03, 2005 - 9:11 am UTC

silent installs just mean the installer read a script file (a recorded session) to replay the keystrokes.

Like the old fashioned windows "macro recorder" used to do.

OUI is platform independent -- it works the same on windows as on unix, but windows works differently than unix, it does not use X so the issue of the display doesn't come up.

</code> http://docs.oracle.com/docs/cd/B10501_01/em.920/a96697/toc.htm <code>

for james

Connor, April 03, 2005 - 8:45 am UTC

check the universal installer guide for response files. Basically its a file that provides the responses for the typical installer questions. In this way, you can roll out Oracle software (typically the client) to PC's and just have someone click "install.bat" and not have a user answer all those cryptic questions.

Data Pump compressed Files in 10g

Mike Jones, April 06, 2005 - 5:37 pm UTC

Tom,

I realize Data Pump wasn't one of the top 10 things you liked about 10g, but perhaps you can shed some light on this anyway. Please.

In 9i and earlier on Unix, we were able to compress export files by sending exp output to a pipe opened by gzip (or compress). In 10g, Data Pump will not write to pipe (or any serial medium). I see that DP will break up output files into smaller chunks, but is there a way to compress the output, other than running a gzip on the resulting file? The primary problem here is disk space. We would need massively more gigs for export files ( and time) if we don't have ability to compress files as part of the export action.
I know we can still use exp/imp in 10g, but would like to use features of DP where possible and didn't see this addressed in documentation or your site.

Thanks

Tom Kyte
April 06, 2005 - 7:09 pm UTC

you cannot in 10r1, I believe it'll be in 10g2 with data pump...

Retention Guarantee..

Mahesh, April 09, 2005 - 1:20 am UTC

My understanding is that, if I have sized my undo tablespace large enough to support undo_retention, oracle would "gurantee" undo without getting overwritten for the period specified in undo_retention parameter. If this is the case, why does one specify "retention gurantee" option while creating undo tablespace? I was wondering what is the use of "retention gurantee" clause in the syntax? Thanks for your time.

Tom Kyte
April 09, 2005 - 7:38 am UTC

in the event you did not size the undo tablespace big enough.

you make the assumption "if i have size my undo...."
we cannot make that assumption and in fact you could be wrong.


</code> http://docs.oracle.com/docs/cd/B14117_01/server.101/b10759/statements_7003.htm#i2209531 <code>
(btw: new feature of 10g for those that read this and wonder what it is)

data pump ignore=y

A reader, April 29, 2005 - 10:22 am UTC

Hi Tom,

In imp utility in Oracle 9i we have the option to ignore create errors with the option ignore=y. Is there anything equivalent to that option with impdp?

Thanks.

Thanks a lot Tom. You are the best!!!

A reader, April 29, 2005 - 11:01 am UTC


A reader, April 29, 2005 - 2:30 pm UTC

Tom,

I understand this question should be assigned to BMC rather than you, but would like to know your feedback as well.

Can SQLBACKTRACK be used for ASM as it only supports RMAN & sqlbacktrack uses the same concept as RMAN.

Thanks.

Tom Kyte
April 29, 2005 - 6:37 pm UTC

probably not, not if they are doing 'alter tablespace begin backup' type stuff

A reader, April 30, 2005 - 1:44 pm UTC

Tom,

Got a response from BMC, it will be supported in the next release.. Nope they dont use begin backup mode...

Thxanks.

status BLOCKED in Listener in 10G

Thiru, May 04, 2005 - 3:48 pm UTC

Hi Tom,

I am able to shutdown my 10g db on windows 2000, but when I try to startup, gives an error:

ORA-12528: TNS:listener: all appropriate instances are blocking new connections

and I find in listener status:

Instance "varpaw", status BLOCKED, has 1 handler(s) for this service...

When I stop the service and start, everything is ok.

Why is that this is happening? Anything in particular to 10G administration.

Thanks for the time

Tom Kyte
May 04, 2005 - 6:01 pm UTC

stop and start "what" service -- the database service or the listener service.

sounds like you are using dynamic registration (database tells listener about itself) and until the database is up, the listener cannot get to it -- therefore you cannot connect via a listener to start the database.

you would need to use static registration in the listener.ora in order to remotely start the instance. so the listener knows how to fire off a dedicated server for it.

Dynamic Registration

Thiru, May 09, 2005 - 8:56 am UTC

It is the Database service from the windows Services(via the contro panel->Admn Tools)

So if I have to start and stop connected as sysdba, do I have to configure the listener.ora file?

If so, what parameters/attribs to be given in that file?

Thanks for the time and excuse me for the delay in responding.

Tom Kyte
May 09, 2005 - 9:19 am UTC

the service must be running in windows regardless. if you stop the service, you must start it before attempting to startup the database.

the service should always be running. If you don't want the database to start with the service, use oradim to set the autostart property.

A reader, May 09, 2005 - 9:14 am UTC

I got it. Added the SID to the Listener. Is this required for any database on 10g? For 9i rel, the Listener was detecting the instances.

Tom Kyte
May 09, 2005 - 9:21 am UTC

If the listener didn't have a static registration in 9i, same would have happened.

10g startup

Dan, May 10, 2005 - 11:51 am UTC

When starting up my 10g test database, the following is presented in the alert log
db_recovery_file_dest_size of 8192 MB is 30.92% used. This is a user-specified limit on the amount of space that will be used by this database for recovery-related files, and does not reflect the amount of space available in the underlying filesystem or ASM diskgroup.
Where does it come up with the 30.92% used? I looked in v$flashback_databse_stat and v$flashback_database_log and they both have different values for estimated_flashback_size.
Just curious, this would be a good query to know.

Tom Kyte
May 10, 2005 - 1:44 pm UTC

how much space is actually used in your recovery destination directory? if you "du" that directory, it is about 2-3gig?

Dan, May 10, 2005 - 5:07 pm UTC

The properties on the folder (its on my windoze laptop)indicate that its about 2.5 gig. I was wondering, is that what oracle does to determine that number? When I started the instance, I looked in v$sqlarea view and did not see a query using that would have answered this.

Tom Kyte
May 10, 2005 - 11:27 pm UTC

it would not be a query to measure the disk used, it would be a simple system call. It is measuring what is actually used.

Dan, May 11, 2005 - 3:33 pm UTC

Thank you.

Dan, May 13, 2005 - 2:13 pm UTC

The information appears to be stored in the v$recovery_file_dest view. Knew it had to be somewhere.

Tom Kyte
May 13, 2005 - 2:38 pm UTC

thanks for the followup (and of course the information in the view comes from the OS in this case)...

Flashback Database

A reader, May 13, 2005 - 4:12 pm UTC

Tom,
I have set everything required to flashback database on my test database. I create a table with rows. I get the current SCN. I truncate the table. Then I shut down the database. Mount it exclusive. And issue:
SQL> FLASHBACK DATABASE TO SCN  38911075420;
I get (ORA-38753)Cannot flashback data file 17; no flashback log data.
I fail to understand what it is nor I find useful information elsewhere. Is it that, there is no before image block available - it is complaining? How can I flashback the database. Idea is to flashback the database. Not get the table back by some other methods such as flashback query or export with flashback parameter.

Thanks
 

Tom Kyte
May 13, 2005 - 4:36 pm UTC

need to show the step by steps for us here.

Here is the set of setup steps and an example I use, connect just connects, dbls just does a select from user_objects, recreateme drops and creates ops$tkyte.

I am recovering the "lost user"

set echo on
clear screen
                                                                                                                          
@connect "/ as sysdba"
                                                                                                                          
shutdown immediate
startup mount
pause
clear screen
                                                                                                                          
alter database archivelog;
alter system set db_flashback_retention_target = 1440 scope=both;
alter database flashback on;
show parameter db_recovery
pause
clear screen
                                                                                                                          
alter database open;
                                                                                                                          
@connect /
pause
                                                                                                                          
@dbls
select count(*) from user_objects;
pause
                                                                                                                          
clear screen
column SCN new_val S
select dbms_flashback.get_system_change_number SCN from dual;
@recreateme
set echo on
show user
select count(*) from user_objects;
pause
clear screen
                                                                                                                          
@connect "/ as sysdba"
shutdown immediate
startup mount
pause
clear screen
                                                                                                                          
flashback database to scn &s;
pause
clear screen
alter database open resetlogs;
pause
clear screen
@connect /
@dbls
select count(*) from user_objects;
pause
 

Thank you..

A reader, May 13, 2005 - 6:41 pm UTC

That worked, Tom. Thank you so much.

Tablespace Flashback ON/OFF

Shivaswamy, May 13, 2005 - 6:52 pm UTC

Tom,
Can you tell me how does the "Alter tablespace XYZ flashback on/off" work ? If I have a tablespace excluded from flashback and do database flashback, how we can enforce data integrity? If foregin key object is in flashback excluded tablespace, what happens? Can you please explain?

Thanks,

Tom Kyte
May 13, 2005 - 8:16 pm UTC

you are basically saying "i'm willing to lose that data in a flashback event, i'll recover or restore it some other way (or just writ it off)"

the data would be "not there", you would not want to do that with data you need.

Thanks a lot.

Shivaswamy, May 13, 2005 - 8:56 pm UTC


Flash Recovery Area (FRA)

Shivaswamy, May 13, 2005 - 9:22 pm UTC

Tom,
Let us assume I have low cost(performance) file system area for FRA on my big datawarehouse which is on flashback database mode.(couple of TB) If multiplexed copies of the current control file and online redo logs were to go there, I will be sacrificing the IO performance. But, since it is a "back up to disk" approach, I need big storage which needs to be low tier storage in order to be economical. How we can address this catch-22 situation?

And second, What prohibts usage of Raw for FRA?

Thanks.

Tom Kyte
May 14, 2005 - 9:33 am UTC

no catch 22, you get what you pay for is all.

but you make the choice of where to put your multi-plexed copies.

The flash recovery area needs to be tuned much like the archived destination would be in the end.

A reader, May 16, 2005 - 4:04 pm UTC

Tom,

Can you throw some date for 10g R2?

Thanks.



Tom Kyte
May 16, 2005 - 5:08 pm UTC

springtime 2005

but remember....
it is springtime somewhere in the world.....
all year long




A reader, May 16, 2005 - 5:23 pm UTC

LOL...

Thanks..

A reader, May 16, 2005 - 11:59 pm UTC

Tom,

Any specific dates for OCFS version 2. I see currently its beta version.

Thanks.

Tom Kyte
May 17, 2005 - 8:20 am UTC

metalink would be the best place to ask for dates/availability.

How to join 10g release 2 beta program?

jc, May 25, 2005 - 2:53 pm UTC

Hi Tom,

We are business partner of Oracle and already have 10g in our product deliverable to our customers. I am looking forward to new features in 10g release and would like to know how to get 10g release 2 (10.2) beta version.

I tried OPN support and our channel account manager, but have not been able to get a hook to the beta program yet. I was given betaprog@oracle.com email address; but the address is not a valid one.

Do you know how I can contact the Oracle beta program?

Thanks!!



Tom Kyte
May 25, 2005 - 7:34 pm UTC

Please allow me to research this, I'll post what I find.

10g beta contact

jc, May 25, 2005 - 5:11 pm UTC

I just got the contact from our account manager -> betaprog_us@oracle.com.

A reader, June 14, 2005 - 2:16 pm UTC

Could Oracle release documentation some time before the product is released so users could atleast read and know what features/changes to expect.

Thanks.



Tom Kyte
June 14, 2005 - 4:21 pm UTC

chicken and egg, until the product is released -- there is no reliable documentation.

they are finished at the same time.


There are statements of directions, OOW 2004 papers describing the new features and such out there.

A reader, June 16, 2005 - 7:49 pm UTC

Can i use DBMS_FILE_TRANSFER package from my 10G database to pull files from 9i database [ expecting a no answer just need confirmation :-) ]



Tom Kyte
June 16, 2005 - 10:20 pm UTC

how about "i don't know and I don't have the necessary infrastructure right now to test" :)


I haven't tried -- on the road right now, if you don't get a chance to try, ping this again later.

Oracle 10g Data Transfer and Replication over WAN

Rohit, June 17, 2005 - 2:09 am UTC

Dear Tom,

I am having two requirements:

1) Data transfer between two Oracle 10g servers on Unix, located at two different geographical locations. The communication will be on WAN. Is there a utility or tool to impliment this. The size of data to be sent is about 5000 data parameters at a frequency of about 15 seconds. The requirement is that there should be no data loss between transfers and hence a reliable transfer.

2) Real time Data replication between two Oracle 10g servers on unix situated at different locations. This will again be on WAN and involves same number (5000 data parameters), but on real time basis. Can Oracle data replication be used. Please suggest which type of replication will be suitable for this.

Any inputs will be usefull.

Thanks in advance
Rohit, NY

Tom Kyte
June 17, 2005 - 2:42 pm UTC

have you read the replication guides? yes, advanced replication can do this, basic read only replication can do this, in 9ir2 and above - Streams can do this. Suggest you want to glance at this:

</code> http://docs.oracle.com/docs/cd/B14117_01/server.101/b10728/toc.htm http://docs.oracle.com/docs/cd/B14117_01/server.101/b10732/toc.htm <code>

and real time typically means "syncronous". I would advise you to RUN, do not WALK, but RUN away from that. sync replication means neither system is very available since both systems are dependent on eachother.

If you need real time for failover, see Data Guard, that would be the appropriate technology for that.

Feedback

Rohit, June 20, 2005 - 1:36 am UTC

Dear Tom,

The inputs were very usefull. Thanks very much for your help.

Download doesn't work

A reader, July 13, 2005 - 2:23 pm UTC

Tom, do you know if there is a problem with downloading from OTN? I tried the link above, it takes me to login page, I enter my login and nothing happens.

I have tried downloading different software off of OTN with the same result.

I have tried from different machines and different locations (home and office and another office), same result.

I searched on discussion forum "download" last 7 days and see other people have the same issue, but I didn't see a resolution.

Thanks

Tom Kyte
July 13, 2005 - 2:39 pm UTC

sorry, I don't know. I'm not really affiliated with OTN's infrastructure. I will send them an email to see if they have any comment.

And OTN said back to me...

A reader, July 13, 2005 - 2:56 pm UTC

Hi Tom....I tried the referenced links before and after logging in and accessed them successfully. It's possible that this user has a unique problem.

I also am not aware of any systemic download problems on OTN.

-------------------------------------------

so -- sorry, it does seem to be functional, maybe suggest you clear your cache, or try a different browser to see if it was related to that.

FTP/RCP Versus DBMS_FILE_TRANSFER

Ramasamy T, July 25, 2005 - 5:06 pm UTC

I am trying weigh the advantages of new DBMS_FILE_TRANSFER
over conventional ftp/rcp.

Could you please point out the advantages DBMS_FILE_TRANSFER
over ftp/rcp. I couldn't find any document explaing the advantages of DBMS_FILE_TRANSFER.

Thanks,

Tom Kyte
July 25, 2005 - 5:39 pm UTC

did you find one with the disadvantages :)

It is a tool, if it makes your life easier, use it, if not - don't.

If you want to move a file from within the database - ftp/rcp (things that begin with "r" are generally frowned upon unless you work to secure them) would not be easy.

If you want to move a file from database A to database B and have a database link setup, you are done. No external scripts, passwords, etc - just database stuff.

gather stats job in 10g

David, July 26, 2005 - 7:51 pm UTC

The gather_stats_job uses auto sample size. Is there a way to find out what is the sample size oracle used in gathering stats? Thanks.

Tom Kyte
July 26, 2005 - 7:54 pm UTC

it is recorded in the dictionary.

A reader, July 26, 2005 - 10:48 pm UTC

Tom,

Can you please look into this..

<<
I haven't tried -- on the road right now, if you don't get a chance to try, ping this again later
>>

Thanks.

Tom Kyte
July 27, 2005 - 9:17 am UTC

It does not appear to work, it fails "ungracefully" in fact.


DBMS_ADVISOR.quick_tune

Sami, August 06, 2005 - 5:04 pm UTC

SQL> desc do
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------

 OWNER                                              VARCHAR2(30)
 OBJECT_NAME                                        VARCHAR2(128)
 SUBOBJECT_NAME                                     VARCHAR2(30)
 OBJECT_ID                                 NOT NULL NUMBER
 DATA_OBJECT_ID                                     NUMBER
 OBJECT_TYPE                                        VARCHAR2(19)
 CREATED                                            DATE
 LAST_DDL_TIME                                      DATE
 TIMESTAMP                                          VARCHAR2(19)
 STATUS                                             VARCHAR2(7)
 TEMPORARY                                          VARCHAR2(1)
 GENERATED                                          VARCHAR2(1)
 SECONDARY                                          VARCHAR2(1)

SQL> exec DBMS_ADVISOR.delete_task('emp_quick_tune1');

PL/SQL procedure successfully completed.

SQL> BEGIN
  2     DBMS_ADVISOR.quick_tune(
  3     advisor_name => DBMS_ADVISOR.SQLACCESS_ADVISOR,
  4     task_name    => 'emp_quick_tune1',
  5     attr1        => 'SELECT e.* FROM do e where upper(status)=''VALID''');
  6   end;
  7  /

PL/SQL procedure successfully completed.

SQL>
SQL> -- Display the resulting script.
SQL> SET LONG 100000
SQL> SET PAGESIZE 50000
SQL> SELECT DBMS_ADVISOR.get_task_script('emp_quick_tune1') AS script  FROM   du
al;

SCRIPT
--------------------------------------------------------------------------------

Rem  SQL Access Advisor: Version 10.1.0.1 - Production
Rem
Rem  Username:        SAMI
Rem  Task:            emp_quick_tune1
Rem  Execution date:  06/08/2005 16:49
Rem

set feedback 1
set linesize 80
set trimspool on
set tab off
set pagesize 60

whenever sqlerror CONTINUE



SQL>


It doesn't give any suggestion, what does it mean? Is that query perfectly alright?!? that's why no recommendations? 

Tom Kyte
August 06, 2005 - 5:33 pm UTC

well, hopefully most things are "valid" - if that is a copy of all_objects.

but I'm not sure that quick-tune would recommend a function based index, regular ones, yes.  In 10gr2, it did recommend a function based index when it made sense

(big_table is a copy of all_objects for the requested number of rows and adds a primary key of id to it)

ops$tkyte@ORA10GR2> select count(*) from big_table;

  COUNT(*)
----------
    100000

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> alter table big_table drop constraint big_table_pk;

Table altered.

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> exec DBMS_ADVISOR.delete_task('emp_quick_tune1');

PL/SQL procedure successfully completed.

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> BEGIN
  2     DBMS_ADVISOR.quick_tune(
  3     advisor_name => DBMS_ADVISOR.SQLACCESS_ADVISOR,
  4     task_name    => 'emp_quick_tune1',
  5     attr1        => 'select * from big_table where id+0 = 1' );
  6   end;
  7  /

PL/SQL procedure successfully completed.

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> -- Display the resulting script.
ops$tkyte@ORA10GR2> SET LONG 100000
ops$tkyte@ORA10GR2> SET PAGESIZE 50000
ops$tkyte@ORA10GR2> SELECT DBMS_ADVISOR.get_task_script('emp_quick_tune1') AS script  FROM   dual;

SCRIPT
-------------------------------------------------------------------------------
Rem  SQL Access Advisor: Version 10.2.0.1.0 - Production
Rem
Rem  Username:        OPS$TKYTE
Rem  Task:            emp_quick_tune1
Rem  Execution date:  06/08/2005 17:16
Rem

CREATE INDEX "OPS$TKYTE"."BIG_TABLE_IDX$$_00120001"
    ON "OPS$TKYTE"."BIG_TABLE"
    ("ID"+0)
    COMPUTE STATISTICS;


 

table monitoring

reader, August 08, 2005 - 2:02 pm UTC

If I upgraded my 9i database to 10gR1, will oracle enable table monitoring for all the tables that I had in 9i? Thanks.

Tom Kyte
August 08, 2005 - 8:31 pm UTC

depends if you opt to use the auto gathering of statistics job, or not.

10g EM dbcontrol

John, August 09, 2005 - 3:09 pm UTC

Does 10g EM db control give alert when the database is down? I was wondering the db control uses the repository that is also in the same database. how does it give alert when the db goes down? Thanks.

Tom Kyte
August 09, 2005 - 3:31 pm UTC

there is a java webserver sitting outside of the database.

db console is the home page for A database.

Grid control is "the larger EM" and has a repository, separate from the databases you are managing.

Does 10g optimizer have a learning mode?

A reader, August 26, 2005 - 8:49 am UTC

Tom,
A statement was made by Oracle representatives which is causing us quite a concern. They told us something like in 10g, the optimizer keeps changing the query plans as time progresses to generate better plans automatically, something like a learning mode or self tuning mode. Is this correct? Which docs will have more information about this?

Thanks

Tom Kyte
August 26, 2005 - 9:04 am UTC

you have to tell it to do that using the tuning pack.

for example, in the following the database watches the sql and when asked to tune the sql realizes "hey, they never actually get all of the rows, must want first rows" and makes it so if you tell it to


ops$tkyte@ORA10G> create table sqlprof
  2  as
  3  select * from all_objects;

Table created.

ops$tkyte@ORA10G> 
ops$tkyte@ORA10G> alter table sqlprof add constraint sqlprof_pk primary key(object_id);

Table altered.

ops$tkyte@ORA10G> 
ops$tkyte@ORA10G> exec dbms_stats.gather_table_stats( user, 'SQLPROF', cascade=>true );

PL/SQL procedure successfully completed.

ops$tkyte@ORA10G> 
ops$tkyte@ORA10G> create or replace procedure p
  2  as
  3           cursor c1
  4           is
  5           select object_id, object_name
  6             from sqlprof
  7            order by object_id;
  8  
  9           l_object_id   sqlprof.object_id%type;
 10           l_object_name sqlprof.object_name%type;
 11  begin
 12           open c1;
 13           for i in 1 .. 10
 14           loop
 15               fetch c1 into l_object_id, l_object_name;
 16               exit when c1%notfound;
 17               -- ....
 18           end loop;
 19  end;
 20  /

Procedure created.

ops$tkyte@ORA10G> 
ops$tkyte@ORA10G> 
ops$tkyte@ORA10G> exec p

PL/SQL procedure successfully completed.

ops$tkyte@ORA10G> 
ops$tkyte@ORA10G> begin
  2           for i in 1 .. 1000
  3           loop
  4               p;
  5           end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.

ops$tkyte@ORA10G> 
ops$tkyte@ORA10G> declare
  2           l_sql_id v$sql.sql_id%type;
  3  begin
  4           select sql_id  into l_sql_id
  5         from v$sql
  6            where sql_text = 'SELECT OBJECT_ID, OBJECT_NAME FROM SQLPROF ORDER BY OBJECT_ID';
  7           dbms_output.put_line(
  8           sys.dbms_sqltune.create_tuning_task
  9           ( sql_id     => l_sql_id,
 10         task_name => 'sqlprof_query' ) || ' in place...' );
 11  end;
 12  /
sqlprof_query in place...

PL/SQL procedure successfully completed.

ops$tkyte@ORA10G> 
ops$tkyte@ORA10G> exec dbms_sqltune.execute_tuning_task( task_name => 'sqlprof_query' );

PL/SQL procedure successfully completed.

ops$tkyte@ORA10G> 
ops$tkyte@ORA10G> SELECT status FROM DBA_ADVISOR_LOG WHERE task_name = 'sqlprof_query';

STATUS
-----------
COMPLETED

ops$tkyte@ORA10G> 
ops$tkyte@ORA10G> SET LONG 100000
ops$tkyte@ORA10G> SET LINESIZE 100
ops$tkyte@ORA10G> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'sqlprof_query')
  2    FROM DUAL;

DBMS_SQLTUNE.REPORT_TUNING_TASK('SQLPROF_QUERY')
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : sqlprof_query
Scope              : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status  : COMPLETED
Started at         : 11/14/2004 13:45:13
Completed at       : 11/14/2004 13:45:13

-------------------------------------------------------------------------------
SQL ID  : 3zfpa86satsm3
SQL Text: SELECT OBJECT_ID, OBJECT_NAME FROM SQLPROF ORDER BY OBJECT_ID

-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------

1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
  A potentially better execution plan was found for this statement.

  Recommendation (estimated benefit: 99.45%)
  ------------------------------------------
    Consider accepting the recommended SQL profile.
    execute :profile_name := dbms_sqltune.accept_sql_profile(task_name =>
                             'sqlprof_query')

-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------

1- Original With Adjusted Cost
------------------------------
Plan hash value: 1044598349

--------------------------------------------------------------------------------
------
| Id  | Operation          | Name    | Rows  | Bytes |TempSpc| Cost (%CPU)| Time
     |
--------------------------------------------------------------------------------
------
|   0 | SELECT STATEMENT   |         | 47487 |  1391K|       |   546   (3)| 00:0
0:07 |
|   1 |  SORT ORDER BY     |         | 47487 |  1391K|  3736K|   546   (3)| 00:0
0:07 |
|   2 |   TABLE ACCESS FULL| SQLPROF | 47487 |  1391K|       |   151   (2)| 00:0
0:02 |
--------------------------------------------------------------------------------
------

2- Using SQL Profile
--------------------
Plan hash value: 337606071

--------------------------------------------------------------------------------
----------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)|
Time     |
--------------------------------------------------------------------------------
----------
|   0 | SELECT STATEMENT            |            |    10 |   300 |     3   (0)|
00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| SQLPROF    | 47487 |  1391K|     3   (0)|
00:00:01 |
|   2 |   INDEX FULL SCAN           | SQLPROF_PK |    10 |       |     2   (0)|
00:00:01 |
--------------------------------------------------------------------------------
----------

-------------------------------------------------------------------------------


ops$tkyte@ORA10G> 
ops$tkyte@ORA10G> exec dbms_sqltune.drop_tuning_task( 'sqlprof_query' );

PL/SQL procedure successfully completed.

ops$tkyte@ORA10G> spool off 

A reader, August 30, 2005 - 8:49 am UTC

Sorry I didn't knew where to put this.
I had found it very useful to index locally your site (i downloaded), documentation and other papers.
I think Oracle should include in the documentation package you download, to bring the same use than tahiti.oracle.com to search documentation.
google desktop is amazingly useful and fast, give a try.

Tom Kyte
August 30, 2005 - 12:07 pm UTC

I don't want people downloading the site, when I catch them - I make it not possible for them. It is not possible to 'sync' without killing my site - please don't do that in the future, you kill it for everyone else.

The site is searchable.
Google has indexed it.
That is enough.

How does Google index asktom

Gary, August 31, 2005 - 1:44 am UTC

Probably out of your field (maybe AskGoogle.com) but
"It is not possible to 'sync' without killing my site"
and "Google has indexed it."

Since the pages are generated dynamically out of your database, how can Google index it without sync'ing it.

My own guess is that Google has some of asktom indexed, but not all. Do you have any info on how many hits Google crawlers make on your site (or does it use the RSS feeds) ?

Tom Kyte
August 31, 2005 - 1:17 pm UTC

</code> http://tinyurl.com/cyywc <code>

they seem to have most of them... I'll have to research that :)

you just made me think that i should spoon feed to google just what I want them to have (that is how I beat the web-wackers, i return them a page that says "no". for google, I should generate links without a sessionid.....)

one more on the list of things to do.

Google SiteMaps

Alberto Dell'Era, August 31, 2005 - 2:40 pm UTC

> you just made me think that i should spoon feed to google
> just what I want them to have

Maybe google SiteMaps may help here:

</code> http://www.google.com/webmasters/sitemaps/docs/en/about.html <code>

I have never used them, anyway they seem to apply for your "issue", especially since they say "If your site has dynamic content or pages that aren't easily discovered by following links" ...

Tom Kyte
August 31, 2005 - 3:30 pm UTC

thanks, bookmarked!

Can I export AWR?

A reader, September 01, 2005 - 9:16 am UTC

Tom,
In 10gr1, using AWR, is it possible to:
a) Generate report for a single sql like I used to do in 9i using sprepsql?
b) Is it possible to export the AWR repository so I can get all the information like in 9i I could export PERFSTAT and get all the statspack information even a month later?

Thanks...

flushing buffer cache

reader, September 09, 2005 - 10:03 am UTC

How is it different from alter system checkpoint? I thought forcing a checkpoint also clears the buffer cache as dbwr process would have written all of the blocks. Thanks.

Tom Kyte
September 09, 2005 - 11:10 am UTC

but blocks remain (not all blocks are even in need of a checkpoint).


flushing buffer cache

reader, September 09, 2005 - 2:07 pm UTC

re your answer above, when you say blocks remain, do you mean the blocks that were not checkpointed? such as non-dirty blocks? or even the checkpointed blocks remnain in buffer cache? Thanks.

Tom Kyte
September 09, 2005 - 2:26 pm UTC

 
ops$tkyte@ORA10G> create table t ( x int primary key, y int );
 
Table created.
 
ops$tkyte@ORA10G> insert into t values ( 1, 1 );
 
1 row created.
 
ops$tkyte@ORA10G> exec dbms_stats.gather_table_stats( user, 'T', cascade=>true );
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> column fno new_val F
ops$tkyte@ORA10G> column bno new_val B
ops$tkyte@ORA10G> select dbms_rowid.ROWID_RELATIVE_FNO(rowid) fno,
  2         dbms_rowid.rowid_block_number(rowid) bno
  3    from t;
 
       FNO        BNO
---------- ----------
         4      69204
 
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> select * from t where x = 1;
 
         X          Y
---------- ----------
         1          1
 
ops$tkyte@ORA10G> set autotrace traceonly
ops$tkyte@ORA10G> select * from t where x = 1;
 
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=1 Bytes=6)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T' (TABLE) (Cost=1 Card=1 Bytes=6)
   2    1     INDEX (UNIQUE SCAN) OF 'SYS_C0014477' (INDEX (UNIQUE)) (Cost=0 Card=1)
 
 
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        467  bytes sent via SQL*Net to client
        508  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
 
ops$tkyte@ORA10G> set autotrace off
ops$tkyte@ORA10G> update t set y = y+1;
 
1 row updated.
 
ops$tkyte@ORA10G> commit;
 
Commit complete.
 
ops$tkyte@ORA10G> exec print_table( 'select * from v$bh where file# = &F and block# = &B' );
.FILE#                        : 4
.BLOCK#                       : 69204
.CLASS#                       : 1
.STATUS                       : cr
.XNC                          : 0
.FORCED_READS                 : 0
.FORCED_WRITES                : 0
.LOCK_ELEMENT_ADDR            : 00
.LOCK_ELEMENT_NAME            :
.LOCK_ELEMENT_CLASS           :
.DIRTY                        : N
.TEMP                         : N
.PING                         : N
.STALE                        : N
.DIRECT                       : N
.NEW                          : N
.OBJD                         : 79791
.TS#                          : 4
-----------------
.FILE#                        : 4
.BLOCK#                       : 69204
.CLASS#                       : 1
.STATUS                       : xcur
.XNC                          : 0
.FORCED_READS                 : 0
.FORCED_WRITES                : 0
.LOCK_ELEMENT_ADDR            : 00
.LOCK_ELEMENT_NAME            :
.LOCK_ELEMENT_CLASS           :
.DIRTY                        : Y
.TEMP                         : N
.PING                         : N
.STALE                        : N
.DIRECT                       : N
.NEW                          : N
.OBJD                         : 79791
.TS#                          : 4
-----------------
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA10G> alter system checkpoint;
 
System altered.
 
ops$tkyte@ORA10G> exec print_table( 'select * from v$bh where file# = &F and block# = &B' );
.FILE#                        : 4
.BLOCK#                       : 69204
.CLASS#                       : 1
.STATUS                       : cr
.XNC                          : 0
.FORCED_READS                 : 0
.FORCED_WRITES                : 0
.LOCK_ELEMENT_ADDR            : 00
.LOCK_ELEMENT_NAME            :
.LOCK_ELEMENT_CLASS           :
.DIRTY                        : N
.TEMP                         : N
.PING                         : N
.STALE                        : N
.DIRECT                       : N
.NEW                          : N
.OBJD                         : 79791
.TS#                          : 4
-----------------
.FILE#                        : 4
.BLOCK#                       : 69204
.CLASS#                       : 1
.STATUS                       : xcur
.XNC                          : 0
.FORCED_READS                 : 0
.FORCED_WRITES                : 0
.LOCK_ELEMENT_ADDR            : 00
.LOCK_ELEMENT_NAME            :
.LOCK_ELEMENT_CLASS           :
.DIRTY                        : N
.TEMP                         : N
.PING                         : N
.STALE                        : N
.DIRECT                       : N
.NEW                          : N
.OBJD                         : 79791
.TS#                          : 4
-----------------
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA10G> set autotrace traceonly
ops$tkyte@ORA10G> select * from t where x = 1;
 
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=1 Bytes=6)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T' (TABLE) (Cost=1 Card=1 Bytes=6)
   2    1     INDEX (UNIQUE SCAN) OF 'SYS_C0014477' (INDEX (UNIQUE)) (Cost=0 Card=1)
 
 
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        467  bytes sent via SQL*Net to client
        508  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
 
ops$tkyte@ORA10G> set autotrace off


No physical IO, block when from dirty to not dirty (xcur copy) 

flush buffer cache

reader, September 09, 2005 - 3:49 pm UTC

great stuff! Thanks.

flushing buffer cahe

reader, September 09, 2005 - 5:24 pm UTC

looking at your demo, it is like even after checkpoint is done, Oracle keeps the block status in the buffer cache as 'xcur'. Does it mean that it did not *free* the block? The block is there so next time if the same block is accessed, it can use it and it does not have to go to disk. Is my understanding correct from your demo? Thanks.

Tom Kyte
September 09, 2005 - 5:45 pm UTC

yes, block is there, block is usable, block is not dirty (so block could sort of be considered free since it could be claimed by someone else if the need for space arose)

incrementally updated backup in real world

A reader, September 11, 2005 - 3:31 pm UTC

Hi

I have been doing reserach about incrementally updated backups. It is quite interesting but I think it is only useful for small size databases and complete restores because

1. image copies must be used
2. image copies cannot be compressed by RMAN nor manually

So say if I have a 500GB database to use this feature I must have another 500GB disk free space (flash recovery area).
One may argue that restore is faster but only if we are performing complete restores (which is not most of cases)

Dont you agree?

Tom Kyte
September 11, 2005 - 6:28 pm UTC

500gb can be had for a couple of thousand dollars at most (in fact, I could do it for $600 - it would be faster than tape and almost disposable for that cost).

There are two types of disk

a) really expensive
b) not expensive

you don't have to keep your backups on (a), you might even find disk to be cheaper than your tape (not that you can forget about using tape, you need to backup the backups)


If you are using tape and you need the last file from the tape - that's a long time to wait to seek on tape. No time at all on a random IO device.

It doesn't matter how much you have to restore - random IO devices will be slightly more flexible.

A reader, December 10, 2005 - 1:59 pm UTC

Tom,

I was asked this question "Can 10G CRS be used as a HA(like Sun Cluster active-passive) hardware?" which according to me is weird. Can you please provide your feedback on this?

Thanks.

Tom Kyte
December 11, 2005 - 3:00 am UTC

yes it could be - even more so in 10gR2 with failed application notification and such.

If just looking at RAC, you would just set it up so that everyone connects to node1, unless and until node1 fails, then they fail over to node2 which is ready to go straight away.

A reader, December 11, 2005 - 1:13 pm UTC

Tom,

Thanks for the feedback so if i understand you correctly lets say node1 db fails or some other reason filesystem will be unmounted on node1(node2 is running without filesystem or db), mounted on node2 and db will be started on node2 without manual intervention.

Thanks.



Tom Kyte
December 11, 2005 - 5:26 pm UTC

No, I'm saying node1 and node2 in a cluster always have read write access to the entire filesystem constantly, there is no mount/unmount step.

And in RAC, node2 is always "running", it is just there - ready to go.

A reader, December 11, 2005 - 6:00 pm UTC

Tom,

Based on the current configuration that we have is hardware
HA using Sun Clusters(Active-Passive) our filesystems are SAN based(EMC) which have oracle binaries, datafiles and indexes.

Lets say db is running on Node1 with filesystems mounted cluster process check for db availability if the db is not available it tries to start and if it fails then cluster process unmounts filesystems(external) on node1 and mounts on Node2 and starts the db on Node2. Can the same be acheived using Oracle CRS? which i think defeats the whole purpose of RAC..

Tom Kyte
December 11, 2005 - 6:34 pm UTC

we would do that with *rac*, it would be active active, you would just choose to not use node2 until and unless node1 failed.

or you could use node1 and node2 continously (as RAC is designed to do)

A reader, December 11, 2005 - 10:54 pm UTC

Thanks Tom for your valuable feedback.

Your favorite 10g features

A reader, December 18, 2005 - 1:34 am UTC

Tom,
Can you please list "Your favorite 10g features"..

Thanks

Thanks

A reader, December 18, 2005 - 9:58 pm UTC

Thanks tom for providing link for your presentation.


AWR Customization

Fahd Mirza, December 20, 2005 - 1:52 am UTC

Tom, albeit, I am using AskTom and your blog for quite a time and a silent
admirer, this is my very post on asktom. Needless to say, your work is splendid.

My question is that, say we have three tables t1,t2,t3 in the schema of 10gR2
database. I presume that AWR collects statistics on them for every hour, as
statistics_level parameter is set to 'typical'.

I want AWR to collect statistics on table t1 and t3, but not on t2. How could I
do it?
Thanks.

Tom Kyte
December 20, 2005 - 8:47 am UTC

AWR statistics are metrics on the system performance - AWR does not collect statistics on tables.

There is a job scheduled by default that will collect statistics on objects that have never had statistics, and then gathers statistics on "stale" objects. This job may or may not be running (ask your DBA).

If this job is not running, you control statistics gathering entirely (and Oracle will dynamically sample unanalyzed tables during a hard parse, increasing the time it takes to hard parse the query)

10g R2 Features

Zahir M, December 21, 2005 - 9:45 am UTC

Tom,

I was wondering if you could shed some light on “Rules Manager”.
How will the use of “Rule Based Development” be efficient compared to today’s programming paradigm ( methodology , if you will )?


Tom Kyte
December 21, 2005 - 7:31 pm UTC

pointer to what you mean - do you mean the "expression stuff" in the database or something in the app server?

10g R2 New Features "Rules Manager and Expression Filter"

Zahir M, December 22, 2005 - 9:41 am UTC

I meant the “Rules Manager and Expression Filter” in the database .

Tom Kyte
December 22, 2005 - 11:02 am UTC

The expression filter was new in 10gr1.

I have not used it yet myself.

Beyond this:

</code> http://www.oracle.com/pls/db102/to_toc?pathname=appdev.102%2Fb14288%2Ftoc.htm&remark=portal+%28Books%29 <code>

I don't really have much more to say about it (yet)

Tablespace Thresholds

A reader, January 27, 2006 - 6:26 am UTC

Tom,
Can you tell me how I can list the the upper and lower threshold levels that are set in a database from the sql prompt?

Thank you.


Tablespace Threshold

A reader, February 01, 2006 - 8:50 pm UTC

Tom,

Do we have a dictionary view/table to get the above information - threshold levels of the tablespaces?

Thanks.

Tom Kyte
February 02, 2006 - 11:49 am UTC

define "threshold" here?

A reader, February 03, 2006 - 12:27 pm UTC

I am sorry. I meant the space thresholds - which are 85 & 97% by default. To see if it is at the default level or there is an altered level for a given tablespace.
Thanks.

Tom Kyte
February 03, 2006 - 5:19 pm UTC

what are space thresholds?

space thresholds

reader, February 03, 2006 - 7:36 pm UTC

To above reader: query dba_thresholds

A reader, February 03, 2006 - 9:52 pm UTC

Thanks.

RMAN 10g

Yogesh, February 04, 2006 - 11:13 am UTC

What should be the role of RMAN in 10g? Especially after having features like Flashback and storage option of SAN/NAS.



Tom Kyte
February 06, 2006 - 12:17 am UTC

umm,
well backup and recovery pops into mind... certainly
block level recovery
online transports of tablespaces
cross platform transports
full database transports



A reader, February 05, 2006 - 8:32 am UTC

for taking backups???

ASM Configuration

Marcio Portes, February 08, 2006 - 11:37 pm UTC

Tom, I don't know if here is the correct place to post this. But I count with your readers to solve my problem as well. Any advice would be appreciate.
The problem is:
I went through ASM configuration example from metalink doc Note:249992.1 so I did those steps.

<quote from metalink 249992.1>
Include devices in diskgroups by editing /etc/sysconfig/rawdevices :
eg /dev/raw/raw1 /dev/sdf
/dev/raw/raw2 /dev/sdg

Set owner, group and permission on device file for each raw device:

$ chown oracle:dba /dev/raw/rawn, chmod 660 /dev/raw/rawn

Bind disk devices to raw devices:

$ service rawdevices restart

So from DBCA you can see the device "raw1" and "raw2".
</quote from metalink 249992.1>

However when I set permission and owner.group and restart the rawdevices service those raw devices files come back to root.disks as owner and group.

BTW - I am using VWare Workstation v. 5.5.1
[root@luke ~]# uname -a
Linux luke.mportes-pc.com 2.6.9-5.EL #1 Wed Jan 5 19:22:18 EST 2005 i686 i686 i386 GNU/Linux
[root@luke ~]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda2 14G 7.9G 4.7G 63% /
/dev/sda1 99M 8.5M 86M 10% /boot
none 584M 0 584M 0% /dev/shm
/dev/sdb4 9.9G 5.4G 4.0G 58% /dsk1

[root@luke ~]# lvmdiskscan
/dev/sda1 [ 101.94 MB]
/dev/root [ 13.40 GB]
/dev/sda3 [ 1.50 GB]
/dev/sdb4 [ 10.00 GB]
/dev/sdc [ 2.00 GB]
/dev/sdd [ 2.00 GB]
/dev/sde [ 2.00 GB]
/dev/sdf [ 2.00 GB]
5 disks
3 partitions
0 LVM physical volume whole disks
0 LVM physical volumes

And I tried this.

[root@luke ~]# ll -d /dev/raw
drwxr-xr-x 2 root root 120 Feb 9 02:34 /dev/raw
[root@luke ~]# ll /dev/raw
total 0
crw-rw---- 1 root disk 162, 1 Feb 9 02:34 raw1
crw-rw---- 1 root disk 162, 2 Feb 9 02:34 raw2
crw-rw---- 1 root disk 162, 3 Feb 9 02:34 raw3
crw-rw---- 1 root disk 162, 4 Feb 9 02:34 raw4
[root@luke ~]# cat /etc/sysconfig/rawdevices
# This file and interface are deprecated.
# Applications needing raw device access should open regular
# block devices with O_DIRECT.
# raw device bindings
# format: <rawdev> <major> <minor>
# <rawdev> <blockdev>
# example: /dev/raw/raw1 /dev/sda1
# /dev/raw/raw2 8 5
/dev/raw/raw1 /dev/sdc
/dev/raw/raw2 /dev/sdd
/dev/raw/raw3 /dev/sde
/dev/raw/raw4 /dev/sdf
[root@luke ~]# chown oracle:dba /dev/raw/raw*
[root@luke ~]# chmod 660 /dev/raw/raw*
[root@luke ~]# ll /dev/raw
total 0
crw-rw---- 1 oracle dba 162, 1 Feb 9 02:34 raw1
crw-rw---- 1 oracle dba 162, 2 Feb 9 02:34 raw2
crw-rw---- 1 oracle dba 162, 3 Feb 9 02:34 raw3
crw-rw---- 1 oracle dba 162, 4 Feb 9 02:34 raw4
[root@luke ~]# service rawdevices restart
Assigning devices:
/dev/raw/raw1 --> /dev/sdc
/dev/raw/raw1: bound to major 8, minor 32
/dev/raw/raw2 --> /dev/sdd
/dev/raw/raw2: bound to major 8, minor 48
/dev/raw/raw3 --> /dev/sde
/dev/raw/raw3: bound to major 8, minor 64
/dev/raw/raw4 --> /dev/sdf
/dev/raw/raw4: bound to major 8, minor 80
done
[root@luke ~]# ll /dev/raw
total 0
crw-rw---- 1 root disk 162, 1 Feb 9 02:39 raw1
crw-rw---- 1 root disk 162, 2 Feb 9 02:39 raw2
crw-rw---- 1 root disk 162, 3 Feb 9 02:39 raw3
crw-rw---- 1 root disk 162, 4 Feb 9 02:39 raw4
[root@luke ~]#

Any idea?
Regards,

Vikas, February 15, 2006 - 11:28 am UTC

Hi Tom,

I have been using the dbms_job to collect the stats using statspack.snap for a period of 15 mins every hr.

However the snapshots get generated and we do want the perfstat schema objects to grow beyond certain limit, that's why we have this purge_snaps schema which runs every day to purge the stats which becomes quite old.

However when we create the ADDM task depending on the lowest snap_id and high snap_id it returns the error 

SQL> Select min(snap_id), max(snap_id) from stats$snapshot;

MIN(SNAP_ID) MAX(SNAP_ID)
------------ ------------
        1264         1322

SQL> BEGIN
  2    -- Create an ADDM task.
  3    DBMS_ADVISOR.create_task (
  4      advisor_name      => 'ADDM',
  5      task_name         => '1264_1322_AWR_SNAPSHOT',
  6      task_desc         => 'Advisor for snapshots 1264 to 1322.');
  7
  8    -- Set the start and end snapshots.
  9    DBMS_ADVISOR.set_task_parameter (
 10      task_name => '1264_1322_AWR_SNAPSHOT',
 11      parameter => 'START_SNAPSHOT',
 12      value     => 1264);
 13
 14    DBMS_ADVISOR.set_task_parameter (
 15      task_name => '1264_1322_AWR_SNAPSHOT',
 16      parameter => 'END_SNAPSHOT',
 17      value     => 1322);
 18
 19    -- Execute the task.
 20    DBMS_ADVISOR.execute_task(task_name => '1264_1322_AWR_SNAPSHOT');
 21  END;
 22  /
BEGIN
*
ERROR at line 1:
ORA-13703: The snapshot pair [1264, 1322] for database_id 405833428 and
instance_id 1 are not found in the current repository.
ORA-06512: at "SYS.PRVT_ADVISOR", line 1283
ORA-06512: at "SYS.DBMS_ADVISOR", line 190
ORA-06512: at line 20

However when i select from dba_hist_snapsot it returns

SQL> Select min(snap_id), max(snap_id) from dba_hist_snapshot;

MIN(SNAP_ID) MAX(SNAP_ID)
------------ ------------
        1598         1773

SQL> BEGIN
  2    -- Create an ADDM task.
  3    DBMS_ADVISOR.create_task (
  4      advisor_name      => 'ADDM',
  5      task_name         => '1598_1773_AWR_SNAPSHOT',
  6      task_desc         => 'Advisor for snapshots 1598 to 1773.');
  7
  8    -- Set the start and end snapshots.
  9    DBMS_ADVISOR.set_task_parameter (
 10      task_name => '1598_1773_AWR_SNAPSHOT',
 11      parameter => 'START_SNAPSHOT',
 12      value     => 1598);
 13
 14    DBMS_ADVISOR.set_task_parameter (
 15      task_name => '1598_1773_AWR_SNAPSHOT',
 16      parameter => 'END_SNAPSHOT',
 17      value     => 1773);
 18
 19    -- Execute the task.
 20    DBMS_ADVISOR.execute_task(task_name => '1598_1773_AWR_SNAPSHOT');
 21  END;
 22  /
 it executes successfully.

Two questions:

1. How come these two tables represents different low and max snap ids.
2. Why the stats$snapshot is returing the error, while creating an ADDM task.

There is only one database and instance, so that's not a conflict.

Appreciate your thoughts on this!
 

Tom Kyte
February 15, 2006 - 12:00 pm UTC

you are using statspack, not AWR

AWR is not statspack
statspack is not AWR


you would use sppurge in $ORACLE_HOME/rdbms/admin to purge information form the statspack schema.

Still needing info on SQL migration to 10g

Rich Miller, February 15, 2006 - 2:57 pm UTC

Hi,

We're using 10g AS and have been querying a 9i database. We're about to upgrade the db to 10gR2 and I'm trying to find out what SQL issues there are -- but I've only heard of one "left join" problem....

What do you know about SQL problems going from 9i to 10g?

Thanks,
Rich

Tom Kyte
February 15, 2006 - 9:45 pm UTC

I've not heard of the "left join" problem myself.

"no" would be my general answer.

10G frontiers

A reader, March 16, 2006 - 9:51 am UTC

Hypothetical question:

Someone says: I have $ XXX available as a grant to research the application of 10G to complex data
problems. Which application areas would you choose ?


Some examples: large spatial databases, data warehousing, life sciences ?
analytics with financial ?

Where are the frontiers of 10G application ?

Great Feature - Need to lear more about it though

Orlando Reyes, March 23, 2006 - 10:55 am UTC

Hi Tom,

I am trying to use this new package, DBMS_SQLTUNE, both from the Grid console and from sql*plus.

I have a long query which in normal circumstances it generates this explain plan and takes over 40 minutes to run:

SELECT STATEMENT [CHOOSE] Cost=28
2.SORT AGGREGATE
3.1 VIEW Cost=28 Card=1 Bytes=
4.1 SORT GROUP BY Cost=28 Card=1 Bytes=142
5.1 FILTER
6.1 NESTED LOOPS OUTER Cost=27 Card=1 Bytes=142
7.1 NESTED LOOPS Cost=26 Card=1 Bytes=113
8.1 NESTED LOOPS OUTER Cost=4 Card=1 Bytes=33
9.1 PARTITION RANGE SINGLE Cost=3 Card=1 Bytes=19
10.1 TABLE ACCESS BY LOCAL INDEX ROWID CL_DIALER_AGNT [ANALYZED] Cost=3 Card=1 Bytes=19
11.1 INDEX RANGE SCAN CL_DIALER_AGNT_IND1 [ANALYZED] Cost=2 Card=1 Bytes=
9.2 TABLE ACCESS BY INDEX ROWID CL_DIM_EMPL [ANALYZED] Cost=1 Card=1 Bytes=14
10.1 INDEX UNIQUE SCAN CL_DIM_EMPL_PK [ANALYZED] Cost=0 Card=1 Bytes=
8.2 PARTITION RANGE ITERATOR Cost=22 Card=1 Bytes=80
9.1 TABLE ACCESS BY LOCAL INDEX ROWID CL_DIALER [ANALYZED] Cost=22 Card=1 Bytes=80
10.1 INDEX RANGE SCAN CL_DIALER_PK [ANALYZED] Cost=2 Card=20 Bytes=
7.2 TABLE ACCESS BY GLOBAL INDEX ROWID CL_DIM_QUE [ANALYZED] Cost=1 Card=1 Bytes=29
8.1 INDEX UNIQUE SCAN PK_CL_DIM_QUE [ANALYZED] Cost=0 Card=1 Bytes=

18 rows selected.

Then, when I use the Grid, and run the SQL Tuning advisor, I get an answer saying that a better execution
plan was found and the benefits could be over 94%, then I accept the recommendation, and then voila! the next time
I run the query, it runs in 10 seconds, with the following explain plan:

SELECT STATEMENT [CHOOSE] Cost=2684
2.SORT AGGREGATE
3.1 VIEW Cost=2684 Card=132188 Bytes=
4.1 SORT GROUP BY Cost=2684 Card=132188 Bytes=18770696
5.1 FILTER
6.1 HASH JOIN RIGHT OUTER Cost=63 Card=132188 Bytes=18770696
7.1 PARTITION RANGE ALL Cost=17 Card=6392 Bytes=185368
8.1 TABLE ACCESS FULL CL_DIM_QUE [ANALYZED] Cost=17 Card=6392 Bytes=185368
7.2 HASH JOIN Cost=44 Card=132188 Bytes=14937244
8.1 HASH JOIN RIGHT OUTER Cost=20 Card=6276 Bytes=207108
9.1 TABLE ACCESS FULL CL_DIM_EMPL [ANALYZED] Cost=16 Card=3075 Bytes=43050
9.2 PARTITION RANGE SINGLE Cost=3 Card=6276 Bytes=119244
10.1 TABLE ACCESS BY LOCAL INDEX ROWID CL_DIALER_AGNT [ANALYZED] Cost=3 Card=6276 Bytes=11924
11.1 INDEX RANGE SCAN CL_DIALER_AGNT_IND1 [ANALYZED] Cost=2 Card=1 Bytes=
8.2 PARTITION RANGE ITERATOR Cost=23 Card=132188 Bytes=10575040
9.1 TABLE ACCESS BY LOCAL INDEX ROWID CL_DIALER [ANALYZED] Cost=23 Card=132188 Bytes=10575040
10.1 INDEX RANGE SCAN CL_DIALER_PK [ANALYZED] Cost=3 Card=20 Bytes=

17 rows selected.

Now, this is great, but the minute I change the query, the "enchanting" is gone, I assume it is because the
query gets hard parsed again and the new execution plan gets back to what it was before.

So my questions are:
1) Does Oracle actually restructure the original query or it just generates a better execution plan different
from the explain plan we see?

2) If the query is restructured, is there any way to see what oracle did so that we can actually incorporate
those changes in the query and be sure it will run as fast always, even if it gets parsed again for a minor
change? I tried to use some hints but not luck to get the goo plan.

3) If the query does not actually gets restructured, and since it does not use bind variables, is there any way to
force it to use the "good" execution plan every time?

4) I know Oracle creates a SQL profile (not totally sure how it works), so is there any way to direct the query
to use or at least consider that profile and run faster?

I know this might be kind of long question, but I am experimenting with this new feature and it looks very cool,
but so far we have not being able to really control it an make it work consistently, so any extra info/ideas will
be very appreciated.

Thanks a lot as usual and have a great day.

Yours truly,

Orlando Reyes


query profiles

A reader, March 28, 2006 - 10:37 am UTC

Tom

I read the following on the OTN site for Oracle 10g/(9i?):

"We also get query profiles, a major enhancement that stores statistical information in the database for the purpose of helping the optimizer make better decisions. This information is particularly useful when you have to deal with untouchable third-party SQL..."

I tried to find more info to "query profiles" at your site, but found nothing. Could you please explain what query profiles are, how they work and why they are useful for untouchable third-party SQL.

Thanks in advance.


Tom Kyte
March 28, 2006 - 4:12 pm UTC

Invoking database Java classes outside the database

Jasbir Kular, April 05, 2006 - 2:47 am UTC

Hi Tom,

I read somewhere (can't seem to find the article anymore) that in 10g I can directly call a database Java class outside of the database using JDBC. Therefore my external Java program doesn't have to call a Java stored procedure or PL/SQL code that in turn calls the database Java class. Can this be done in 10g or was I dreaming that I read about that feature? If this is true can you please also include an example.

Thanks for your help.


Tom Kyte
April 05, 2006 - 5:47 pm UTC

You can do that in 8i - you write a java stored procedure that opens a jdbc connection to some other jdbc source.

It doesn't make sense to say "all a database Java class outside of the database using JDBC." really - jdbc is the Java Database Connectivity API - it is not about java calling java?

Using the Native Java Interface

Jasbir Kular, April 05, 2006 - 5:03 pm UTC

Hi Tom,

I found the information that I was looking for. It is in section "3.5.1 Using the Native Java Interface" of the Java Developers Guide 10.2.

Thanks.

grid -> database server "scales out"?

Intentionally anonymous, April 23, 2006 - 10:56 pm UTC

I had an interview last week. One of the software guys interviewing me asked this:

"Suppose your data server has 30% utilization running existing applications. For your new application, you have to choose one of two approaches:
"1. Use stored procedures on the Oracle 9i data server and push utilization to 70%
"2. Use application servers that can easily and cheaply "scale out" to handle the processing.
"Which would you choose? (And remember, it'll be very expensive to revise if you don't choose the right way first?)"

Of course, I laughed and told him that he was not too subtle in leading me to his preferred answer!

In retrospect, should I have told him that they needed to RAC their 9i installed or move to 10g so that the database would run on scalable, commodity servers -- and scale out just as easily and cheaper as his application servers, while performing better to boot?

Tom Kyte
April 24, 2006 - 12:07 am UTC

or option 3

3) make sure all applications utilize stored procedures so that the utilization could decrease from 30% for the existing applications to some much lower number as the server is now able to perform the existing transactions with less work overall (fewer "do this, do that, do the other thing"). Not only that but it would decrease the likely hood of a single bad apple spoiling the barrel since things would in general be properly bound (we'd only have to worry about the initial call to the procedure). And since the code is in the database, we are well positioned to change the application programming language - which lets face it - changes a lot more often than our database does.

And of course since there is no one answer for all problems, we would be doing some logic in the application tier - we just would put the proper logic in the proper place. A fast fourier transformation - application server. Take database from consistent state A to consistent state B - all about the data and the database.

-----------------------------------------------------------------

I just read this:

</code> http://www.codinghorror.com/blog/archives/000557.html <code>

it seems to apply...




SQL Tuning Advisor

Kamal, May 03, 2006 - 8:05 am UTC

Hi Tom,

What is the difference between SQL Tuning Advisor and SQL Access Advisor...how they differ functionally

Thanks
kamal

conditional Compilation

Giridhar, May 04, 2006 - 6:16 am UTC

Hi Tom,
i am reading this article about conditional compilation.

</code> http://www.oracle.com/technology/pub/articles/10gdba/nanda_10gr2dba_part1.html#compile <code>

If you refer to the "debug_pkg", debug_flag is modified to demonstrate usage of conditional compilation.

However in this example, we can acheive the same by just using normal "IF" statement in this case.

I assume that conditional compilation can be useful if we change some settings using "ALTER SYSTEM" and get the benefits without modifying any code.

Will the conditional condition be useful in the cases mentioned in this example "debug_pkg"?

Thanks Tom..


Tom Kyte
May 04, 2006 - 7:36 am UTC

no, you could not have achieved the same - the $IF statement is evaluated ONCE at compile time (the procedure implicitly recompiled itself when the package was modified!)

a regular IF statement would be evaluated each time at runtime.




RMAN backup and recovery help

A reader, May 16, 2006 - 10:07 am UTC

Hi,

We are trying to do RMAN backups for the first time and I want to test some backups with our test database and restore it. Please point me to some useful RMAN backup and restore guides which will help me with this process.

Thanks.

Tom Kyte
May 16, 2006 - 10:14 am UTC

</code> http://www.oracle.com/pls/db102/portal.portal_db?selected=1 <code>

all documentation is available online on OTN. There are rman guides there as well.

How many people use ASM ?

A reader, May 18, 2006 - 10:42 am UTC

Over 75%-80% industry uses Oracle.

How many of them are using ASM in the mission critical application. Can you get me an idea from your sources ?

or point me to some web site that has this stats ?

Tom Kyte
May 19, 2006 - 9:48 am UTC

we don't actually have numbers like that - that would be like asking "what percentage use an index organized table" - people don't call us and tell us "we are using them"



How many people use ASM ?

A reader, May 18, 2006 - 10:44 am UTC

OR industry is still in "geting to know " Mode and no real production installs ?

Tom Kyte
May 19, 2006 - 9:48 am UTC

there are production installs, at various (many) customer installations.

what's new in release 2,

A reader, June 14, 2006 - 4:49 pm UTC

What are the interesting things you found in 10g Release 2?

Are there any companies still thinking about to go or not to go for Release 2?

What are the advantages of going for release 2 over release 1?

Thanks,


Tom Kyte
June 15, 2006 - 8:26 am UTC

</code> http://www.oracle.com/pls/db102/portal.portal_db?selected=3 <code>

there is a new features guide, as well - many of the documents have a "whats new in" - for example, the admin guide, chapter one is "what is new in Oracle database administration"

check those out, pretty comprehensive.


It would not be wise to migrate to 10gr1 today, given that 10gr2 is production and is the supported release of Oracle in the 10g family.

SQL model clause

Dan Serban, July 21, 2006 - 10:01 am UTC

Hi Tom,

Just a purely academic curiosity.
How would you rewrite your procedure dump_table to take advantage of the SQL model clause?
Do you think it makes sense to do that?
I do obviously, because it looks like the model clause is good at the un-pivoting stuff that dump_table is all about.
It would make for a very nice showcase re: the model feature.

-Dan

Tom Kyte
July 23, 2006 - 7:33 am UTC

you would not make use of model in dump_table - it would not make sense. You just want to dump data - a simple select * is all that it does, all it needs to do.

there is no pivoting or un-pivoting (??) needed.

AWR License

V, August 04, 2006 - 2:18 pm UTC

I have just come accross AWR and notice in my 10g instance that stats are being gathered. I also ran the AWRRPT.sql report to view statistics. After reading the docs, I see that AWR is part of the Dianostics pack. Does this mean I can't run the AWRRPT report if I don't have a licence for the Diagnostics pack? If not, how can I turn off AWR statistics gathering so I can just start statspack?

Tom Kyte
August 04, 2006 - 2:54 pm UTC

You probably do not want to turn off AWR, it can be used by services and support freely.

Yes, you have to be licensed to use it.

You control it with statistics_level

</code> http://docs.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams210.htm#sthref856 <code>

AWR

V, August 04, 2006 - 3:01 pm UTC

So I can't even look at the views that AWRRPT uses to view statistics gathered?

I have to install STATSPACK & basically gather the same statistics that AWR is gathering????

Tom Kyte
August 04, 2006 - 3:40 pm UTC

Correct, unless you licensed it.

New Features...

A reader, August 07, 2006 - 4:55 am UTC

Hi Tom,

We are in the process of upgrading oue databases to 10g.
Whilst on that matter I would like to ask you something about that.

I don't think Oracle has yet implemented it on 10g or maybe saving it for 11g. But still I am compelled to ask it out of sheer curiosity.

In Oracle databases maybe pre 8i, we can create tables and indexes in parallel. Very useful and it has been proved time and time again.
But I would like/prefer having the same feature implemented for Tablespaces/Datafiles also. Will be very useful in case of Big files the kind introduced in 10g.
Creating them in parallel will be a great performance booster and efficient also.

Would like to know your opinion on this topic.


Tom Kyte
August 07, 2006 - 8:09 am UTC

just create the tablespace with tiny datafiles - really small (so they create really fast)

then you can alter them to make them larger in parallel (run many sessions) or just let them autoextend over time as they need.



10g R3

A reader, September 02, 2006 - 6:19 pm UTC

When oracle is planning to release 10G R3 release? or are they planning for 11G as the next release?

Tom Kyte
September 02, 2006 - 6:23 pm UTC

the next major release of Oracle will be announced soon... Whether is it called 10gR3 or 11g isn't really relevant (as the feature set is already decided :)

In other words, I cannot really refer to it as anything other than "the next major release of the database" right now.

A reader, September 03, 2006 - 12:29 pm UTC

Tom,

Did Oracle ever release R3 for any of its version? I mean its always been R2 and patches follow on top of that right??

Thanks.



Tom Kyte
September 04, 2006 - 8:38 am UTC

there was

8ir1 - 8.1.5
8ir2 - 8.1.6
8ir3 - 8.1.7

Thanks

A reader, September 04, 2006 - 1:38 am UTC

Thanks for the update. Yeah, the version name doesn't matter, all we care about the lot of new features with the new major release. I guess that it going to happen in 2007. Am I correct?

Tom Kyte
September 04, 2006 - 8:53 am UTC

that is the best guess, yes.

A reader, September 04, 2006 - 1:30 pm UTC

>>I mean its always been R2 and patches follow on top of that >>right??
Maybe i didnt put it right thanks for correcting me.

What i meant was 8.1.x ( patches follow )
9.1.x
9.2.x

there wasnt any 8.3.x or 9.3.x.


Thanks.

Tom Kyte
September 04, 2006 - 2:36 pm UTC

there were r1, r2, r3 of 8i
there were r1, r2 of 9i
there were r1, r2 or 10g

they changed the number scheme between 8i and 9i - and even 9i to 10g!

9iR1 = 9.0 (not 9.1 there was no 9.1)
9iR2 = 9.2 (no 9.1)

A reader, September 05, 2006 - 12:01 am UTC

Thanks.

10gR3 or 11g

Peter Svacho, September 05, 2006 - 8:22 am UTC

In this document (section Oracle SQL Developer 1.2) is mentioned database 11g:

</code> http://www.oracle.com/technology/products/database/sql_developer/files/sqldeveloperstatementofdirection.htm <code>

Have you heard anything new about new features of planned next release of database?

Tom Kyte
September 05, 2006 - 5:09 pm UTC

I've heard there will be a large conference in San Francisco soon - about two months from now. Lots of things will be said.

Thanks

A reader, September 05, 2006 - 12:43 pm UTC


Materialized View Refresh

jc, September 19, 2006 - 3:39 pm UTC

I recently converted some jobs over to our 10g instance. While refreshing a MView for the first time it failed (unable to extend undo space). I thought it was a strange error (for a Mview) so I queried the MView and to my surprise it still contained data. I executed the refresh again (long running refresh) and while it was refreshing i could still query and get data back.

It appears to perform a seamless; delete, insert, & commit when rebuilding in 10g. However in 9i it appeared to truncate, insert, commit. So durring the refresh in 9i the old data was not availble. I've searched through the new features and cant find anything on this. Is my undo filling up due to a delete over what use to be a truncate (on 9i)?

Thanks,
jc

Tom Kyte
September 20, 2006 - 2:34 pm UTC

that is a new "feature" - that complete refreshes are now done transactionally by default - the Materialize view data won't "disappear" - it used a delete plus insert.

A reader, October 25, 2006 - 12:25 pm UTC

Hi Tom
For the previous question on DBIO_EXPECTED
For windows to measure random access speed I found this nice utility
</code> http://files3.majorgeeks.com/files/ed21b54c417d6d8638aef8efc652fe37/benchmark/qb-setup.exe <code>

Size not refresh in view V$SYSAUX_OCCUPANTS

Hecor Gabriel Ulloa Ligarius, November 03, 2006 - 11:23 am UTC

Hi Team Tom

I´m working with database Oracle10g XE , with Windows Xp 

1.-Verify the size of datafiles

21:14:54 SQL> Select t.tablespace_name  "Tablespace",
21:15:17   2   t.status "Status",
21:15:17   3   ROUND((MAX(d.bytes)/1024/1024) - (SUM(decode(f.bytes, NULL,0, f.bytes))/1024/1024),2) "Used MB",
21:15:17   4   ROUND(SUM(decode(f.bytes, NULL,0, f.bytes))/1024/1024,2) "Free MB"
21:15:17   5   , SUBSTR(d.file_name,1,80) "Datafile name"
21:15:17   6  FROM DBA_FREE_SPACE f , DBA_DATA_FILES d , DBA_TABLESPACES t
21:15:17   7  WHERE t.tablespace_name = d.tablespace_name
21:15:17   8   AND f.tablespace_name(+) = d.tablespace_name
21:15:17   9   AND f.file_id(+) = d.file_id
21:15:17  10  GROUP BY t.tablespace_name ,
21:15:17  11   d.file_name ,
21:15:17  12   t.initial_extent ,
21:15:17  13   t.next_extent ,
21:15:17  14   t.min_extents ,
21:15:17  15   t.max_extents ,
21:15:17  16   t.pct_increase , t.status
21:15:17  17   ORDER BY 1,3 DESC
21:15:17  18  /

Tablespace Status       Used MB    Free MB Datafile name                                            
---------- --------- ---------- ---------- ----------------------------------------                 
EJEMPLO    ONLINE           .06      29.94 C:\ORACLEXE\ORADATA\XE\EJEMPLO01.DBF                     
SYSAUX     ONLINE        426.13       3.88 C:\ORACLEXE\ORADATA\XE\SYSAUX.DBF                        
SYSTEM     ONLINE        344.31       5.69 C:\ORACLEXE\ORADATA\XE\SYSTEM.DBF                        
UNDO       ONLINE         89.63        .38 C:\ORACLEXE\ORADATA\XE\UNDO.DBF                          
USERS      ONLINE          1.63      98.38 C:\ORACLEXE\ORADATA\XE\USERS.DBF                         

Elapsed: 00:00:00.04

2.- Verify the size schema LOGMNR .

Elapsed: 00:00:00.86
21:15:42 SQL> select occupant_name , schema_name , space_usage_kbytes from v$sysaux_occupants where occupant_name like 'LOGMNR' order by 1;

OCCUPANT_NAME                                                                                       
----------------------------------------------------------------                                    
SCHEMA_NAME                                                      SPACE_USAGE_KBYTES                 
---------------------------------------------------------------- ------------------                 
LOGMNR                                                                                              
SYSTEM                                                                         6080 --> Space Usage                
                                                                                                    
3.- Change the tablespace of user LOGMNR
Elapsed: 00:00:00.21
21:16:16 SQL> begin
21:16:36   2   SYS.DBMS_LOGMNR_D.SET_TABLESPACE('EJEMPLO');
21:16:36   3  end;
21:16:37   4  /

PL/SQL procedure successfully completed.
Elapsed: 00:00:04.32

4.- And verify the size of datafiles in the tablespace

21:16:43 SQL> Select t.tablespace_name  "Tablespace",
21:16:57   2   t.status "Status",
21:16:57   3   ROUND((MAX(d.bytes)/1024/1024) - (SUM(decode(f.bytes, NULL,0, f.bytes))/1024/1024),2) "Used MB",
21:16:57   4   ROUND(SUM(decode(f.bytes, NULL,0, f.bytes))/1024/1024,2) "Free MB"
21:16:57   5   , SUBSTR(d.file_name,1,80) "Datafile name"
21:16:57   6  FROM DBA_FREE_SPACE f , DBA_DATA_FILES d , DBA_TABLESPACES t
21:16:57   7  WHERE t.tablespace_name = d.tablespace_name
21:16:57   8   AND f.tablespace_name(+) = d.tablespace_name
21:16:57   9   AND f.file_id(+) = d.file_id
21:16:57  10  GROUP BY t.tablespace_name ,
21:16:57  11   d.file_name ,
21:16:57  12   t.initial_extent ,
21:16:57  13   t.next_extent ,
21:16:57  14   t.min_extents ,
21:16:57  15   t.max_extents ,
21:16:57  16   t.pct_increase , t.status
21:16:57  17   ORDER BY 1,3 DESC
21:16:57  18  /

Tablespace Status       Used MB    Free MB Datafile name                                            
---------- --------- ---------- ---------- ----------------------------------------                 
EJEMPLO    ONLINE             6       24 C:\ORACLEXE\ORADATA\XE\EJEMPLO01.DBF                     
SYSAUX     ONLINE        420.19       9.81 C:\ORACLEXE\ORADATA\XE\SYSAUX.DBF                        
SYSTEM     ONLINE        344.31       5.69 C:\ORACLEXE\ORADATA\XE\SYSTEM.DBF                        
UNDO       ONLINE         89.63        .38 C:\ORACLEXE\ORADATA\XE\UNDO.DBF                          
USERS      ONLINE          1.63      98.38 C:\ORACLEXE\ORADATA\XE\USERS.DBF                         

Elapsed: 00:00:00.18

The tablespace EJEMPLO have used 6MB 

5.- But , the change is not refresh in the view V$SYSAUX_OCCUPANTS

21:16:59 SQL> select occupant_name , schema_name , space_usage_kbytes from v$sysaux_occupants where occupant_name like 'LOGMNR' order by 1;

OCCUPANT_NAME                                                                                       
----------------------------------------------------------------                                    
SCHEMA_NAME                                                      SPACE_USAGE_KBYTES                 
---------------------------------------------------------------- ------------------                 
LOGMNR                                                                                              
SYSTEM                                                                            0 --> no refresh the change??
                                                                                                    

Elapsed: 00:00:00.50
21:17:14 SQL> spool off

Regards
Hector Gabriel Ulloa Ligarius
Living in Santiago of Chile, working in Puerto Rico :D
 

Tom Kyte
November 03, 2006 - 12:14 pm UTC

the goal of the view is to show the system/sysaux usage.

it absolutely refreshed the change. It is 0. Not sure that anything is wrong here.

Sup, February 17, 2007 - 3:53 am UTC

Hi Tom,

I have installed 10g express edition and 10g client in the same machine.

Created one application and it's running fine. But how can I make this application run by a client?

I want to keep this appl in my network so that some users (whom I am going to created using 10g Exp Edition.) can access the application and use it.

Thanks,
Sup
Tom Kyte
February 18, 2007 - 9:39 am UTC

umm

what did you create the application with? what is the client here?

Sup, February 18, 2007 - 10:52 pm UTC

The application is built under 10g express edition application builder. This is running now in the same system where it is built.

I want to install 10g client in another system and run the same application. How can I do that? Please advice.
Tom Kyte
February 19, 2007 - 9:25 am UTC

why not just run it there - but anyway, if you are using APEX, just export the application and import it into another install. The APEX environment has the buttons to do that.

Sup, February 19, 2007 - 10:58 pm UTC

Yes, I did that. But I believe 10g does provide a 3 tier basis for applications to be run from a client machine which were developed in the some machine and placed in the server.

Which means, I have to install 10g in a server and then access the application using clients.

But how do I connect ? Is there any tnsnames.ora to be set up in the client or just the application link will do the job?

Thanks
Tom Kyte
February 20, 2007 - 9:29 am UTC

but if you built the application using APEX - I'm confused, the client lives IN the database when the application is APEX.

I'm not at all sure what you are asking for here.

Sup, February 21, 2007 - 11:43 pm UTC

Hi Tom,

My question is simple - to run the application (built using 10g apex) from the server.

1. I installed 10g apex (free download version) in my local machine
2. Built one application "x" and running it over there under user "abc".
3. Created some users with connect, resource so that they can access "abc" user data through the application "x".
4. But when I installed the 10g client in the same machine, I do not know how can I connect to this 10g server data existing in the same system - rather I would say, if I install 10g apex in some networked computer, I won't know how each user (from different machines) will access the application from the shared server..

Please advice...

Thanks,
Sup
Tom Kyte
February 22, 2007 - 8:40 am UTC

you do not need a client - apex is 100% browser based.

therein lies my confusion, I have no idea why you keep bringing up this 10g client, all you need is a BROWSER. the only way to run an APEX application is via a browser.

A reader, February 22, 2007 - 11:15 pm UTC

Okay - I got it.

I was confused because I had downloaded the Apex client too (I do not know then why they provide that).

I am able to connect to the applications from clients without installing those with just siting the address.

Thanks Tom. I appreciate it.

10g when compared to 9i

A reader, March 01, 2007 - 12:43 pm UTC

Tom,

Do you think 10g is far better when compared to 9i when ones compares goint to 9i from 8i

9i had lots issues IMHO whereas 10g is so much superior


Tom Kyte
March 02, 2007 - 12:50 pm UTC

9i was awesome compared to 8i
8i was awesome compared to 8.0
8.0 was awesome ......


I didn't see the software having "issues"

10gR2 - the current production release - is the only release I would consider for new development, yes

and if I were upgrading an 8i/9i database it would be the only release I would consider.

10g wrt 9i

A reader, March 02, 2007 - 3:46 pm UTC

From a performance point of view, 10g optimizer is so good esp. for datawarehouse. Did not see any noticeable perf improvement when going to 9i from 8i

Anyway - that is my personal experience

thanks

does 10gR2 support statspack?

Sean, March 17, 2007 - 8:32 am UTC

I understood there is the AWR on 10g db. but I also find statspack in $ORACLE_HOME/rdbms/admin.
i created the statspack on my 10gR2 db without error.
but as I ran:

SQL> @spreport


DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
1673202556 YYYY 1 YYYY


Completed Snapshots

Snap Snap
Instance DB Name Id Snap Started Level Comment
------------ ------------ ----- ----------------- ----- ----------------------
YYYY YYYY 1 17 Mar 2007 12:23 5
2 17 Mar 2007 12:24 5


Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 1
Begin Snapshot Id specified: 1

Enter value for end_snap: 2
End Snapshot Id specified: 2


Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is sp_1_2. To use this name,
press <return> to continue, otherwise enter an alternative.
Enter value for report_name:


Using the report name sp_1_2

begin STATSPACK.STAT_CHANGES
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'STAT_CHANGES'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

Tom Kyte
March 17, 2007 - 5:24 pm UTC

I just installed, took two snaps, ran spreport - no issues

are you SURE you used the 10gr2 spcreate to install and are running the 10gr2 spreport?

I haven't heard of any issues either.

Sean, March 22, 2007 - 8:49 pm UTC

Tried it again and work now. not sure what was last time. it could be the env was not right.
Thanks.

key feature by version

Saurabh, April 04, 2007 - 5:43 am UTC

I have been following this site since 3 years and really its been very very helpful, "helpful" is rather too modest it has been much more than that. Thank you for sharing your knowledge with us.

What I am looking at now - is to find out list of "key" features(partitioning, LMT, TTS, CBO, Analytics, VPD, streams, etc) and from which release they were available. Just the features that you think were special. I don't really want information before 8i.

I am not sure if I can request you to list them.
Tom Kyte
April 04, 2007 - 10:22 am UTC

I always laugh at this sort of request.


If partitioning isn't useful to you given your application - then it isn't a key feature for you. But I might call it a key feature.

If clustering isn't useful to you given your application - then it isn't a key feature for you. But I might call it a key feature.


Best bet:

check out the new features guides - the table of contents alone lists the key improvements by release:

8i
http://docs.oracle.com/docs/cd/A87860_01/doc/server.817/a76962/toc.htm

9i R1 and R2
http://docs.oracle.com/docs/cd/B10501_01/server.920/a96531/toc.htm

10g R1 and R2
http://docs.oracle.com/docs/cd/B19306_01/server.102/b14214/toc.htm

Re: key feature by version

David Weigel, April 12, 2007 - 1:13 pm UTC

Is there a single document around that says when various features became available? The code I write may go to customers running different versions of Oracle, and I often have to do things like
   if oracle_version >= '9.0' then
      merge [...]
   else
      insert [...]
      update [...]
   end if

It'd be nice to have a reference that says when new features came to be. (In fact, I'm not sure if 9.0 is right for "merge"...)
Tom Kyte
April 13, 2007 - 12:17 pm UTC

I above pointed you to the only documents that do this. That is the only source I am aware of. In 10g, they talk of 10g new features - but don't go back to 9i, 8i, 8.0, 7.3, .....

problem in setting up repeat_interval

deba, May 14, 2007 - 10:33 am UTC

Hi Tom,

I am trying to setup repeat_interval with expression but unable to do that.

SQL> BEGIN
2 DBMS_SCHEDULER.create_schedule (
3 schedule_name => 'deba_schedule1',
4 start_date => SYSDATE,
5 repeat_interval => 'sysdate+ 5*(1/(24*60))',
6 end_date => NULL,
7 comments => 'Repeats in every 15 mins, for ever.');
8 END;
9 /
BEGIN
*
ERROR at line 1:
ORA-27418: syntax error in repeat interval or calendar
ORA-06512: at "SYS.DBMS_ISCHED", line 679
ORA-06512: at "SYS.DBMS_SCHEDULER", line 1130
ORA-06512: at line 2


SQL> select sysdate+ 5*(1/(24*60)) from dual;

SYSDATE+5*(1/(24*60
-------------------
14/05/2007 15:30:43

SQL> BEGIN
2 DBMS_SCHEDULER.create_schedule (
3 schedule_name => 'deba_schedule1',
4 start_date => sysdate,
5 REPEAT_INTERVAL=> 'SYSDATE+1',
6 end_date => NULL,
7 comments => 'Repeats in every 15 mins, for ever.');
8 END;
9 /
BEGIN
*
ERROR at line 1:
ORA-27418: syntax error in repeat interval or calendar
ORA-06512: at "SYS.DBMS_ISCHED", line 679
ORA-06512: at "SYS.DBMS_SCHEDULER", line 1130
ORA-06512: at line 2

Could you please tell me what the silly mistake I am doing ?

Thanks
Deba
Tom Kyte
December 18, 2007 - 1:33 pm UTC


you use "calendaring syntax"

http://docs.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_sched.htm

for that - not a plsql/sql like expression.

here are examples:
http://docs.oracle.com/docs/cd/B19306_01/server.102/b14231/scheduse.htm#ADMIN10040


FREQ=MINUTELY; INTERVAL=5;

every 5 minutes...

10g and Data Storage

A reader, May 15, 2007 - 4:50 pm UTC

Are you aware of any large, really large, implementations that use Oracle 10g? I mean something like www.flickr.com that stores huge amount of image files. Would a site like flickr use database to store pictures? They allow users to upload pictures in their original size. Suppose an average size of a picture is 1 MB and suppose there are 100,000 pictures uploaded daily (probably a gross underestimate), that's 100 GB of data per day! How do you design a database to handle this kind of application?


Tom Kyte
May 16, 2007 - 10:03 am UTC

well, we have a couple of tens of terabytes with millions of files used by 50,000 employees as a single file server for the entire company - for example.

awr and addm reports

Suraj Shrma, August 29, 2007 - 3:02 am UTC

Tom,

For AWR or ADDM report we refer snap_id from dba_hist_snapshot. Suppose I have a CPU spike on 29th August 2007 between 9AM to 11AM, which of these query will tell me the exact snap_id, which I can use to take my ADDM or AWR reports:

Select MIN(snap_id),MAX(snap_id) from dba_hist_snapshot where to_char(BEGIN_INTERVAL_TIME, 'ddmmyyyyhh24mi') between '290820070900' and `290820071100';

OR

Select MIN(snap_id),MAX(snap_id) from dba_hist_snapshot where to_char(END_INTERVAL_TIME, 'ddmmyyyyhh24mi') between '290820070900' and '290820071100';

My question is which time should I use BEGIN_INTERVAL_TIME, or END_INTERVAL_TIME to find snap_id for the specified time period.

I am confused because of a reason that when I am using BEGIN_INTERVAL_TIME for the same I am getting SQLs with high gets per execution, SQLs contributing CPU and other useful things. On the other hand when I use END_INTERVAL_TIME I did not get much, but some of my seniors use END_INTERVAL_TIME and I can not argue or say anything before having anything authenticated with me.

Also please explain the difference between these two.

Thanks,
Suraj

AWR

A reader, September 07, 2007 - 9:43 am UTC

Advanced Workload Repository

Just gone live and at shutdown I am getting the following messages:-

'ORA-00444: background process "MMNL" failed while starting
ORA-01089: immediate shutdown in progress - no operations are permitted
ORA-00444: background process "MMON" failed while starting
ORA-01089: immediate shutdown in progress - no operations are permitted'

setting the interval at 0 as follows:-
BEGIN
DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS (
retention => 43200,
interval => 0);
END;
/

is supposed to stop the stats being gathered but hasn't. I can't find any related bugs for this, and I know that when ( and if ) we switch to hot backups this will go away. It's only a minor annoyance but if you have any ideas how to stop it I'd be grateful.

Thanks

Compile forms in 10g

chavan, October 10, 2007 - 6:18 am UTC

Hi Tom,

Sorry to have diverted the topic of discussion. i did not find any thread matching with forms compilation issues - hence attached my question here.

I am not able to compile forms in 10g HP-UX. when i do f90gen to compile the form, it's giving error "FRM-91500: Unable to start/complete the build."

- could you please help!
Thanks

Rules

Andrew Markiewicz, December 07, 2007 - 11:53 am UTC

Tom,

The expression filter was new in 10gr1.
I have not used it yet myself.

Beyond this:
http://www.oracle.com/pls/db102/to_toc?pathname=appdev.102%2Fb14288%2Ftoc.htm&remark=portal+%28Books%29
I don't really have much more to say about it (yet)



Any opinion yet on the Rules Manager?
Looks like 11g expands on it. Or at least added an edit screen and call it Complex Event Processing (CEP). Looks similar to Designer...

Andrew

HOW TO DISPLAY THE QUERY TRAINGLE

SHASHI KIRAN, December 18, 2007 - 1:28 am UTC

HI, I WANT TO DISPLAY THE QUERY LIKE

1
1 2 1
1 3 3 3 1
1 4 6 6 4 1
I WANT TO DISPLAY THE QUERY ;LIKE THIS........
CAN U HELP ME PLEASE
Tom Kyte
December 18, 2007 - 1:32 pm UTC

sorry, the triangle feature is slated for version 13.

No idea what you mean by a query triangle. Definitely NOT a 10g feature however.

query triangle

rc, December 19, 2007 - 4:00 am UTC

@SHASHI KIRAN

I have made a query that returns such a triangle. I will post this query this evening. It is the triangle of Pascal also know as the Galton board or quincunx.

You can use connect by to generate trees. See alo http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:73830657104020#563369000346181609 (near to the end).

I cant help laughter :-)

Dusan, December 19, 2007 - 8:50 am UTC

select '
1
1 2 1
1 3 3 3 1
1 4 6 6 4 1 ' triangel_for_SHASHI_KIRAN
from dual
/

Pascal's triangle

rc, December 19, 2007 - 12:59 pm UTC

Pascal's triangle: Every number is the sum of the 2 numbers directly above the number.

1
1 1
1 2 1
1 3 3 1
1 4 6 4 1
1 5 10 10 5 1

You can generate the last row:

create or replace function calculate(p_expression in varchar2)
return number
is
l_result number;
begin
execute immediate ' select '||p_expression||' from dual ' into l_result;
return l_result;
end;
/

variable l_level number
variable l_branches number

exec :l_level := 6;
exec :l_branches := 2;

with data as
(
select path from
(
select num, sys_connect_by_path(num-1,'+') path
from
( select level num from dual connect by level < :l_branches+1)
connect by level < :l_level+1
)
where path like '+1+%'
and length(path) = length(replace(path,'+',null)) + :l_level
)
select ind,count(*)
from
(
select calculate(substr(path,2)) ind
from data
)
group by ind
order by ind;

The output will be:

IND COUNT(*)
---------- ----------
1 1
2 5
3 10
4 10
5 5
6 1

If you change :l_level into 7 you will get the next row of the triangle.

IND COUNT(*)
---------- ----------
1 1
2 6
3 15
4 20
5 15
6 6
7 1

Pascal's triangle II

rc, December 21, 2007 - 2:47 pm UTC

@SHASHI KIRAN

create or replace function calculate(p_expressie in varchar2)
return number
is
l_result number;
begin
execute immediate ' select '||p_expressie||' from dual ' into l_result;
return l_result;
end;
/

variable l_level number
variable l_branches number

exec :l_level := 10;
exec :l_branches := 2;

set lines 200

select triangle_row, totalrow
from
(
select * from
(
with data as
(
select path from
(
select num, sys_connect_by_path(num-1,'+') path
from
( select level num from dual connect by level < :l_branches+1)
connect by level < :l_level+1
)
where path like '+1%'
)
select triangle_row,ind,count(*) num
from
(
select calculate(substr(path,2)) ind, length(replace(path,'+',null)) triangle_row
from data
)
group by ind,triangle_row
)
model
partition by (triangle_row)
dimension by (ind)
measures ( num
, cast(null as varchar2(150)) totalrow
, count(*) over (partition by triangle_row) counter)
rules
(
totalrow[any] order by ind = totalrow[cv(ind)-1]||' '||num[cv(ind)]
)
)
where ind = counter
order by triangle_row,totalrow
;

The output is:

TRIANGLE_ROW TOTALROW
------------ ------------------------------------------
1 1
2 1 1
3 1 2 1
4 1 3 3 1
5 1 4 6 4 1
6 1 5 10 10 5 1
7 1 6 15 20 15 6 1
8 1 7 21 35 35 21 7 1
9 1 8 28 56 70 56 28 8 1
10 1 9 36 84 126 126 84 36 9 1

10 rows selected.

The model clause is used for pivoting. This works in Oracle 10 and 11.


Pascal's triangle III

rc, January 25, 2008 - 2:25 pm UTC

It can be done much easier and faster with using the model clause twice and no more PL/SQL, only SQL. Variable l_numrows is the number of rows of Pascal's triangle.

variable l_numrows number
exec :l_numrows := 5;

select ver,hor,decode(num,0,to_number(null),num) numn
from (select level hor from dual connect by level < :l_numrows+2)
, (select level ver from dual connect by level < :l_numrows+1)
model
dimension by (hor,ver)
measures (cast (null as number(10)) num)
rules
(
num[1,any] = 1
, num[2,1] = 1
, num[hor>1,ver>1] = nvl(num[cv(hor)-1,cv(ver)-1],0) + nvl(num[cv(hor),cv(ver)-1],0)
)
order by ver,hor
;

This will output:

VER HOR NUMN
---------- ---------- ----------
1 1 1
1 2 1
1 3
1 4
1 5
1 6
2 1 1
2 2 2
2 3 1
2 4
2 5
2 6
3 1 1
3 2 3
3 3 3
3 4 1
3 5
3 6
4 1 1
4 2 4
4 3 6
4 4 4
4 5 1
4 6
5 1 1
5 2 5
5 3 10
5 4 10
5 5 5
5 6 1

30 rows selected.

Now we just have to pivot on column ver:

with data as
(
select ver,hor,decode(num,0,to_number(null),num) numn
from (select level hor from dual connect by level < :l_numrows+2)
, (select level ver from dual connect by level < :l_numrows+1)
model
dimension by (hor,ver)
measures (cast (null as number(10)) num)
rules
(
num[1,any] = 1
, num[2,1] = 1
, num[hor>1,ver>1] = nvl(num[cv(hor)-1,cv(ver)-1],0) + nvl(num[cv(hor),cv(ver)-1],0)
)
)
select ver,totalrow,sumrow
from
(
select *
from data
model
partition by (ver)
dimension by (hor)
measures (numn
,cast(null as varchar2(25)) totalrow
,count(*) over (partition by ver) counter
,cast(0 as number(5)) sumrow)
rules
(
totalrow[any] order by hor = totalrow[cv(hor)-1] ||' '||numn[cv(hor)]
, sumrow[any] order by hor = nvl(sumrow[cv(hor)-1],0) + nvl(numn[cv(hor)],0)
)
)
where hor=counter
order by ver,totalrow;

VER TOTALROW SUMROW
---------- ------------------------- ----------
1 1 1 2
2 1 2 1 4
3 1 3 3 1 8
4 1 4 6 4 1 16
5 1 5 10 10 5 1 32

SUMROW=sum of the numbers of totalrow, 1+5+10+10+5+1=32 (there is a relation between the triangle of Pascal and the binomial theorem, see wikipedia).

There are other ways to pivot but you can pivot with the model clause.

I hope there is someone on this planet who likes this.


I think it's just too cool for words.

Chuck Jolley, March 01, 2008 - 4:29 pm UTC

I do.
I think it's just too cool for words.

impdp and scheduler jobs

Harry Zhang, July 03, 2008 - 10:47 am UTC

Hi Tom,

Thanks for your time. I have requirements to impdp a schema with 10g scheduler jobs in the schema. After the import I need to keep those scheduler jobs disabled. I tried to put system in quiesce restricted, but after impdp when I disable jobs, it will hang even using sysdba. If I unquiesce then there are some unwanted mails sent out. In 9i we can just set job queue to 0 then after imp all jobs will not run, but what is the best way in 10g?

Regards
Harry

disable scheduler jobs

Harry Zhang, August 12, 2008 - 10:21 am UTC

I found this can works.

exec dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED','TRUE')
alter system set job_queue_processes=0;
Tom Kyte
August 13, 2008 - 4:36 am UTC

very nice, thanks!

undocumented ?

Sokrates, August 13, 2008 - 5:59 am UTC

Could you please explain a bit further what do you mean by
"very nice" ?

exec dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED','TRUE')

seems to be undocumented , for example
http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/d_sched.htm#i1004630


Tom Kyte
August 18, 2008 - 9:04 am UTC

very nice means - they did what they needed to do in a test database?

Pascal's triangle III revisited

Jmv, August 14, 2008 - 1:23 pm UTC

Something caught my eye today as I was lurking that was posted earlier by rc regarding the Pascal triangle wherein the first row seems to be missing from the ouput. Specifically the output above starts:
VER TOTALROW SUMROW
---------- ------------------------- ----------
1 1 1 2
2 1 2 1 4
...

Shouldn't it start with row 1 as follows:
1 1 1
2 1 1 2
3 1 2 1 4
..

Which can be done with (some rewrite):
accept l_numrows default 10 prompt "Number of rows in Triangle [10]: "

col p_total_row format a75

with data as
(
select ver,
hor,
decode(num, 0, to_number(null),
num) numn
from (select level hor
from dual
connect by level < &&l_numrows+2),
(select level ver
from dual
connect by level < &&l_numrows+1)
model
dimension by (hor,ver)
measures (cast (null as number) num)
rules (
num[1,any] = 1,
num[2,1] = 1,
num[hor>1,ver>1] = nvl(num[cv(hor)-1,cv(ver)-1],0) + nvl(num[cv(hor),cv(ver)-1],0)
)
)
select hor,
lpad (' ', (75 - length(totalrow) )/2) || totalrow p_total_row,
sumrow
from (select *
from data
where numn is not null
model
partition by (ver)
dimension by (hor)
measures (numn,
cast(null as varchar2(75)) totalrow,
count(*) over (partition by ver) counter,
cast(0 as number) sumrow)
rules (totalrow[any] order by hor = totalrow[cv(hor)-1] || ' ' || numn[cv(hor) ],
sumrow[any] order by hor = nvl(sumrow[cv(hor) - 1], 0) + nvl(numn[cv(hor)],0) )
order by 1, 2
)
where (ver = 1 and
hor = 1) or
hor = counter and
hor <= &&l_numrows
/

-- output
Number of rows in Triangle [10]:
old 9: connect by level < &&l_numrows+2),
new 9: connect by level < 10+2),
old 12: connect by level < &&l_numrows+1)
new 12: connect by level < 10+1)
old 42: hor <= &&l_numrows
new 42: hor <= 10

HOR P_TOTAL_ROW SUMROW
---------- --------------------------------------------------------------------------- ----------
1 1 1
2 1 1 2
3 1 2 1 4
4 1 3 3 1 8
5 1 4 6 4 1 16
6 1 5 10 10 5 1 32
7 1 6 15 20 15 6 1 64
8 1 7 21 35 35 21 7 1 128
9 1 8 28 56 70 56 28 8 1 256
10 1 9 36 84 126 126 84 36 9 1 512

10 rows selected.

Well done

rc, August 15, 2008 - 3:11 pm UTC

@Jmv

Yes, that is an improvement!

A Reader, August 15, 2008 - 3:18 pm UTC

SQL> accept l_numrows
17
SQL> /
alt   9:           connect by level < &&l_numrows+2),
neu   9:           connect by level < 17+2),
alt  12:          connect by level < &&l_numrows+1)
neu  12:          connect by level < 17+1)
alt  42:         hor <= &&l_numrows
neu  42:         hor <= 17
                  cast(null as varchar2(75)) totalrow,
                  *
FEHLER in Zeile 32:
ORA-25137: Data value out of range

Error when casting...

Jmv, August 18, 2008 - 11:47 am UTC

As "A Reader" discovered above, re:
cast(null as varchar2(75)) totalrow,
*
FEHLER in Zeile 32:
ORA-25137: Data value out of range

The length of totalrow can easily exceed the 75 used in the cast with subsequently higher number of rows, 17 in this case, for the Pascal triangle.

I increased the original from 25 to 75, both in the cast and in the lpad statement, as a way to play with the formatting of the triangles. The more rows in a triangle the "sloppier" the output gets within SQL*Plus due to wrapping.

Could have used the max varchar2 column length, however, there would still be some maximun number of rows for which the error would reoccur.




download error in Oracle Database 10g for Microsoft Windows Vista

Jery, September 19, 2008 - 9:43 pm UTC

Sorry, I tried to download 10203_vista_w2k8_x86_production_db.zip from this link:

http://www.oracle.com/technology/software/products/database/oracle10g/htdocs/10203vista.html

but this link is down.

Do you know other link to download Oracle Database 10g Release 2 (10.2.0.3) for Microsoft Windows Vista?

Thanks in advance.

Jery

Table Monitoring

A reader, January 07, 2009 - 11:44 pm UTC

Hi Tom,

Regarding followup above:

Followup August 8, 2005 - 8pm US/Eastern:

depends if you opt to use the auto gathering of statistics job, or not.

We have DW environment, and don't want auotomatic gathering of statistics. But Oracle says that
Formerly, you enabled DBMS_STATS to automatically gather statistics for a table by specifying the MONITORING keyword in the CREATE (or ALTER) TABLE statement. Starting with Oracle Database 10g, the MONITORING and NOMONITORING keywords have been deprecated and statistics are collected automatically. If you do specify these keywords, they are ignored.
To disable monitoring of a table, set the STATISTICS_LEVEL initialization parameter to BASIC. Its default is TYPICAL, which enables automatic statistics collection. Automatic statistics collection and the DBMS_STATS package enable the optimizer to generate accurate execution plans.

and
ADDM is enabled by default and is controlled by the STATISTICS_LEVEL initialization parameter. The STATISTICS_LEVEL parameter should be set to TYPICAL or ALL to enable the automatic database diagnostic feature of ADDM. The default setting is TYPICAL. Setting the STATISTICS_LEVEL parameter to BASIC disables many Oracle Database features, including ADDM, and is not recommended.

So, how can I opt to disable the auto gathering of statistics job without disabling ADDM? and the scary bit is - this not recommended by Oracle???
Regards,

Tom Kyte
January 08, 2009 - 9:08 am UTC

you just need to disable the job - leave table monitoring alone (leave it on, it is harmless) and disable the automatic gather database stats job.


http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:27658118048105#59791536013265

Grid on Heterogeneous Servers?

Duke Ganote, September 07, 2009 - 11:23 am UTC

Can a database be gridded across multiple OS? For example, AIX and Win64?
Tom Kyte
September 07, 2009 - 11:42 am UTC

no, Real application clusters requires homogeneous hardware, it doesn't have to be the same size, but the hardware type and OS must be the same.

Throttling the Recovery Manager

Duke Ganote, September 30, 2009 - 10:04 am UTC

#bYou meant this link?
http://download.oracle.com/docs/cd/B19306_01/backup.102/b14191/rcmconc1.htm # BRADV108
After removing the blanks in the link.

It says (among other things):

I/O Read Rate of Backups

By default, RMAN uses all available I/O bandwidth to read/write to disk. You can limit the I/O resources consumed by a backup job with the RATE option of the ALLOCATE CHANNEL or CONFIGURE CHANNEL commands. The RATE option specifies the maximum number of bytes for each second that RMAN reads on the channel.

For example, you can configure automatic channels to limit each channel to read 1 MB a second:

CONFIGURE DEVICE TYPE sbt PARALLELISM 2;
CONFIGURE DEFAULT DEVICE TYPE TO sbt;
CONFIGURE CHANNEL DEVICE TYPE sbt RATE 1M;

In effect, the RATE option throttles RMAN so that a backup job does not consume excessive I/O bandwidth on the computer.

oops

Duke Ganote, September 30, 2009 - 10:07 am UTC

A reader, January 19, 2012 - 8:50 am UTC

Hi Tom,

After run sqltune below was shown in recommendation.Is it modifed query has been done and store in profile.After execute how i can check the query ??Please help

2- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
A potentially better execution plan was found for this statement.

Recommendation (estimated benefit: 26.6%)
-----------------------------------------
- Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name => 'sqlprof_query',
replace => TRUE);
Tom Kyte
January 19, 2012 - 1:38 pm UTC

define 'check the query'

you would verify that it is using a new better plan.


you can also test the profile before accepting it.

A reader, January 21, 2012 - 6:25 am UTC

Hi Tom,

"Check the query" i mean how we can see new modified query done by sqltune...I am very new to this.
Tom Kyte
January 21, 2012 - 10:37 am UTC

did you read the link I gave to you about testing the plan?

you can then use dbms_xplan.display_cursor to see what the query plan used was (run the query in sqlplus, then just select * from table(dbms_xplan.display_cursor)) - like this:

ops$tkyte%ORA11GR2> set serveroutput off
ops$tkyte%ORA11GR2> select * from t;

no rows selected

ops$tkyte%ORA11GR2> select * from table( dbms_xplan.display_cursor );

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
SQL_ID  89km4qj1thh13, child number 1
-------------------------------------
select * from t

Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS FULL| T    |     1 |    26 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)


17 rows selected.

Scheduled Job is taking more time than expected

sachin, October 08, 2012 - 9:08 am UTC

Tom,

I searched for this bit, but could not find any pointer.
2 environments STG and PROD, in PROD env, Job A is taking more than expected than in STG env. You would point to Statspack but there is little infor that we are able to gather from there. RAM in PROD is 4x STG,same goes for the processor speed.

Looking for you now..
Tom Kyte
October 09, 2012 - 12:41 pm UTC

let's see, you have 4x the memory and 4x the cpu power in PROD than STG

it is taking longer in STG than PROD....


really? what are your expectations - why do you think it is taking longer than it should. did you have someone that understands statspack output look at the reports? did you consider running sql trace and tkprof?

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.