Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Jim.

Asked: October 29, 2007 - 12:19 pm UTC

Last updated: July 24, 2008 - 10:08 am UTC

Version: 8.1.7

Viewed 1000+ times

You Asked

This is a "can you settle a dispute" kind of question. You may consider this to be the wrong forum for such a question. If so, no hard feelings.

A colleague and I were discussing java in the database and when the JVM first made it into the Oracle database. My first recollection of it was in 8i. My colleague said he thought it was as early as 7.3.4. Having been around since Oracle would fit on a floppy do you remember?

and Tom said...

The JVM in the database was a new feature in 8.1.5 (Oracle 8i Release 1). It was actually part of the reason for the "i" in 8i - the internet database...

http://docs.oracle.com/cd/A87860_01/doc/server.817/a76962/ch2.htm#117384


Ok, I like these sorts of questions - so, let me ask all of you some :)

I will snail-mail an autographed copy of Expert Oracle Database Architecture to the first person that correctly provides ALL of the versions the following features were added to Oracle.

You will have to use a valid email address when you "review" this question to provide your answers (your email is never displayed, only I see it and I'll email you to get your snail mail address - do not put your address online...)

I will be the "decider" here, meaning, the list is correctly answered when I say it is :) Valid versions are:

ops$tkyte%ORA10GR2> select distinct version from features order by version;

VERSION
--------------------
10.1
10.2
11.1
2
3
4
5
6
7.0
7.1
7.2
7.3
8.0
8.1.5
8.1.6
8.1.7
9.0
9.2

18 rows selected.



So, here are the features:

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select rownum, txt
  2    from (select txt from features order by rnd)
  3  /

    ROWNUM TXT
---------- ----------------------------------------------------------------------
         1 Real Application Testing
         2 Read only Replication
         3 Distributed Query
         4 Drop column
         5 Client-Server (where the client could be elsewhere in the network)
         6 Object Relational Features
         7 Ability to return result sets from stored procedures (ref cursors)
         8 Commit and Rollback (transactions)
         9 Triggers
        10 Function based indexes
        11 Materialized Views
        12 Rman
        13 Audit SYSDBA/SYSOPER activity
        14 Automatic Undo Management
        15 Resumable Operations
        16 Automatic Storage Management (ASM)
        17 Streams
        18 Bitmap Indexes
        19 csscan - Character Set Scanner utility
        20 Flashback Query
        21 Case statement (IN SQL, instead of decode)
        22 Parallel Query
        23 Transparent column level encryption
        24 Tablespace encryption
        25 PL/SQL
        26 Partitioning
        27 Row Level Locking
        28 Read Consistency (my favorite feature!)
        29 2 Phase Commit
        30 Sorted Hash Clusters
        31 Conditional compilation for PL/SQL
        32 Connect By Queries (select ename, level from emp connect by prior....)
        33 Update anywhere Replication

33 rows selected.

Rating

  (38 ratings)

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

Comments

Versions

Josh, October 30, 2007 - 11:07 am UTC

Not sure if it's 100%, or if I'm even close to the first, but it's a start right?

1 Real Application Testing
11g
2 Read only Replication
8i
3 Distributed Query
5.1
4 Drop column
8.1.5
5 Client-Server (where the client could be elsewhere in the network)
5.0
6 Object Relational Features
8.0
7 Ability to return result sets from stored procedures (ref cursors)
9.0.1
8 Commit and Rollback (transactions)
3
9 Triggers
7.0
10 Function based indexes
8i
11 Materialized Views
8.0
12 Rman
9.0.1
13 Audit SYSDBA/SYSOPER activity
10g
14 Automatic Undo Management
9i
15 Resumable Operations
9i
16 Automatic Storage Management (ASM)
10g
17 Streams
9.2
18 Bitmap Indexes
7.3
19 csscan - Character Set Scanner utility
8i
20 Flashback Query
9i
21 Case statement
9.0
22 Parallel Query
7.1
23 Transparent column level encryption
10g
24 Tablespace encryption
11g
25 PL/SQL
6.0
26 Partitioning
8.0
27 Row Level Locking
6.0
28 Read Consistency (my favorite feature!)
4
29 2 Phase Commit
9i
30 Sorted Hash Clusters
10g
31 Conditional compilation for PL/SQL
10g
32 Connect By Queries (select ename, level from emp connect by prior....)
7.0
33 Update anywhere Replication
10g
Tom Kyte
October 30, 2007 - 11:31 am UTC

I listed the versions to use!!!

5.1 - was not one of them. Neither are 8i, 9i, 10g.....

but no - many incorrect versions..... :)


Sid, October 30, 2007 - 12:04 pm UTC

Tom,

This is real fun. I have been using Oracle only since 8i so my answers below 8i are just guess (Yes, I didn't google to find below answers ;-) )

Thanks

1 Real Application Testing - 11.1
2 Read only Replication - 7.3
3 Distributed Query - 7.1
4 Drop column - 8.1.5
5 Client-Server (where the client could be elsewhere in the network) - 7.0
6 Object Relational Features - 8.1.6
7 Ability to return result sets from stored procedures (ref cursors) - 8.1.7
8 Commit and Rollback (transactions) - 5
9 Triggers - 7.0
10 Function based indexes - 8.0
11 Materialized Views - 8.1.5
12 Rman - 8.1.5
13 Audit SYSDBA/SYSOPER activity - 8.1.7
14 Automatic Undo Management - 9.0
15 Resumable Operations - 9.0
16 Automatic Storage Management (ASM) - 10.1
17 Streams - 9.2
18 Bitmap Indexes - 8.1.5
19 csscan - Character Set Scanner utility - 7.0
20 Flashback Query - 10.1
21 Case statement - 9.0
22 Parallel Query - 8.1.5
23 Transparent column level encryption - 11.0
24 Tablespace encryption - 11.0
25 PL/SQL - 7.0
26 Partitioning - 8.1.5
27 Row Level Locking - 6.0
28 Read Consistency (my favorite feature!) - 6.0
29 2 Phase Commit - 6.0
30 Sorted Hash Clusters - 10.1
31 Conditional compilation for PL/SQL - 7.0
32 Connect By Queries (select ename, level from emp connect by prior....) - 6.0
33 Update anywhere Replication -9.0

Tom Kyte
October 30, 2007 - 12:59 pm UTC

sorry, I only had to read to #2 to find a wrong one...

A quick glance says you got 10 right.

Starting a Chronological Answer

S, October 30, 2007 - 1:14 pm UTC

Connect By Queries    5
PL/SQL                6 - but only SQL*Forms was able to call them, 
                          no execution inside the database
Commit and Rollback   - they were there in version 4. That's as far as my experience goes


I'm not sure about Client - Server but it was there in 6 and may have been introduced in 5.

Starting from version 8 the documentation is more or less available on-line so it's just work to get those answers correctly.
Tom Kyte
October 30, 2007 - 2:08 pm UTC

you got your first one wrong...

and the second one you got the right version, but the wrong explanation :) The execution was in the database and any client could do it, not just forms!

the third one is wrong :) well, not the way you said it, but it doesn't answer the question

My answers to features & versions

Alfonso Rizo-Patron, October 30, 2007 - 2:07 pm UTC

Real Application Testing = 11.1
Read-Only Replication = 7.0
Distributed Query = 5
Drop Column = 8.1.5
Client-Server = 5
Object-Relational features = 8.0
Return result sets from stored procs (ref cursors) = 7.3
Commit & Rollback = 3
Triggers = 7.0
Function-based indexes = 8.1.5
Materialized Views = 8.0
RMAN = 8.0
Audit sysdba/sysoper = 9.2
Automatic UNDO management = 9.0
Resumable operations = 9.0
Automatic Storage Management (ASM) = 10.2
Streams = 9.2
Bitmap indexes = 7.3
csscan = 8.1.6
Flashback query = 9.0
Case stmt = 8.1.5
Parallel Query = 7.1
Transparent column level encryption = 10.2
Tablespace encryption = 11.1
PL/SQL = 6
Partitioning = 8.0
Row-level locking = 6
Read Consistency = 4
2 Phase Commit = 7.0
Sorted Hash Clusters = 10.1
Conditional PL/SQL compilation = 10.2
Connect By queries = 2
Update anywhere replication = 7.1
Tom Kyte
October 30, 2007 - 2:25 pm UTC

close, I count at least 5 that are off :) (and they were not that far off)

So, let's try :)

Konstantin, October 30, 2007 - 2:08 pm UTC

1 Real Application Testing [11.1]
2 Read only Replication [7.0] - If "Read Only Replication" means "snapshots"
3 Distributed Query [5.1]
4 Drop column [8.1.5]
5 Client-Server (where the client could be elsewhere in the network) [5.0]
6 Object Relational Features [8.0]
7 Ability to return result sets from stored procedures (ref cursors) [7.2]
8 Commit and Rollback (transactions) [3]
9 Triggers [7.0]?
10 Function based indexes [8.1.5]
11 Materialized Views [7.0]
12 Rman [8.0]
13 Audit SYSDBA/SYSOPER activity [9.2]
14 Automatic Undo Management [9.0]
15 Resumable Operations [9.0]
16 Automatic Storage Management (ASM) [10.1]
17 Streams [9.2]
18 Bitmap Indexes [7.3]
19 csscan - Character Set Scanner utility [8.1.7]
20 Flashback Query [9.0]
21 Case statement [8.1.6] - searched CASE [9.0] - simple CASE
22 Parallel Query [6.2]
23 Transparent column level encryption [10.2]
24 Tablespace encryption [11.1]
25 PL/SQL [6.0]
26 Partitioning [8.0]
27 Row Level Locking [6.0]
28 Read Consistency (my favorite feature!) [4]
29 2 Phase Commit [10.2]
30 Sorted Hash Clusters [10.1]
31 Conditional compilation for PL/SQL [10.2]
32 Connect By Queries (select ename, level from emp connect by prior....) [8.1.7]
33 Update anywhere Replication [7.1]
Tom Kyte
October 30, 2007 - 2:27 pm UTC

even closer - 4 that I see wrong....


LOL

Michel Cadot, October 30, 2007 - 2:27 pm UTC


Ha ha ha!
Number 21 is a trap (SQL or PL/SQL?).
And only few can remember numbers 8, 27, 28 and 32.
I'm surprised to not see Oracle Parallel Server and analytic functions in the list. :)

Regards
Michel

Tom Kyte
October 30, 2007 - 3:27 pm UTC

I updated 21 - you are correct, it was possibly ambiguous

I didn't want to make the list HUGE, just big :)

Competition rules

Konstantin, October 30, 2007 - 2:41 pm UTC

Should I spend a night to retry in order to obtain the powerful artefact? :)

maybe this list is correct

MAK, October 30, 2007 - 3:04 pm UTC

1 Real Application Testing
11.1
2 Read only Replication
7.3
3 Distributed Query
5
4 Drop column
8.1.5
5 Client-Server (where the client could be elsewhere in the network)
5
6 Object Relational Features
8.0
7 Ability to return result sets from stored procedures (ref cursors)
7.2
8 Commit and Rollback (transactions)
3
9 Triggers
7.0
10 Function based indexes
8.1.5
11 Materialized Views
8.1.5
12 Rman
8.0
13 Audit SYSDBA/SYSOPER activity
9.2
14 Automatic Undo Management
9.0
15 Resumable Operations
9.2
16 Automatic Storage Management (ASM)
10.1
17 Streams
9.2
18 Bitmap Indexes
7.3
19 csscan - Character Set Scanner utility
10.1
20 Flashback Query
9.0
21 Case statement
8.1.7
22 Parallel Query
7.0
23 Transparent column level encryption
10.2
24 Tablespace encryption
11.1
25 PL/SQL
6
26 Partitioning
8.0
27 Row Level Locking
6
28 Read Consistency (my favorite feature!)
4
29 2 Phase Commit
7.1
30 Sorted Hash Clusters
10.1
31 Conditional compilation for PL/SQL
10.2
32 Connect By Queries (select ename, level from emp connect by prior....)
4
33 Update anywhere Replication
7.1

Tom Kyte
October 30, 2007 - 3:35 pm UTC

not correct, at least 6 wrong :)

maybe this list is correct

MAK, October 30, 2007 - 3:12 pm UTC

Tom
csscan is in 8.1.6

Another Challenge

S, October 30, 2007 - 3:41 pm UTC

Here is another challenge. Given the two posts with 4 and 5 wrong answers, how many different combinations (not in a mathematical sense) of all 33 answers are candidates to be correct?

S
Tom Kyte
October 30, 2007 - 3:46 pm UTC

not forgetting that I count quickly and could be off by one or two :)

If I find at least one wrong, I'll try to say quickly how many are probably wrong in addition...

Scoring....

Alfonso Rizo-Patron, October 30, 2007 - 3:58 pm UTC

Perhaps to be fair the final tally should be by version proximity ? Just a Thought.
Al
Tom Kyte
October 30, 2007 - 4:09 pm UTC

I'm not sure what we would mean by 'version proximity', but in my mind - there are 33 features up there and each has a right answer - the version they came with.

So, an answer is either right or wrong, close counts in horse shoes ;)

Scoring...

Alfonso Rizo-Patron, October 30, 2007 - 4:51 pm UTC

OK. Sigh. (I must be getting too old for this).
Just to be sure though, I just chose among the versions you strictly listed among the 18 above (i.e. no "5.1," just "5," etc.). I mention this because I see some others listing intermediate version numbers not on your original list.
Cheerio,
Al.

OK, my last shot at the title

Alfonso Rizo-Patron, October 30, 2007 - 5:04 pm UTC

Real Application Testing = 11.1
Read-Only Replication = 7.0
Distributed Query = 5
Drop Column = 8.1.5
Client-Server = 5
Object-Relational features = 8.0
Return result sets from stored procs (ref cursors) = 7.2
Commit & Rollback = 3
Triggers = 7.0
Function-based indexes = 8.1.5
Materialized Views = 8.1.5
RMAN = 8.0
Audit sysdba/sysoper = 9.2
Automatic UNDO management = 9.0
Resumable operations = 9.0
Automatic Storage Management (ASM) = 10.2
Streams = 9.2
Bitmap indexes = 7.3
csscan = 8.1.6
Flashback query = 9.0
Case stmt = 8.1.6
Parallel Query = 7.1
Transparent column level encryption = 10.2
Tablespace encryption = 11.1
PL/SQL = 6
Partitioning = 8.0
Row-level locking = 6
Read Consistency = 4
2 Phase Commit = 7.0
Sorted Hash Clusters = 10.1
Conditional PL/SQL compilation = 10.2
Connect By queries = 2
Update anywhere replication = 7.1

(now I give up if these are not correct).

A.
Tom Kyte
October 30, 2007 - 8:06 pm UTC

off by ONE :)

very close, very very close....

How about this one?

Toon Koppelaars, October 30, 2007 - 5:17 pm UTC

1 Real Application Testing [11.1]
2 Read only Replication [7.0] - If "Read Only Replication" means "snapshots"
3 Distributed Query [5]
4 Drop column [8.1.5]
5 Client-Server (where the client could be elsewhere in the network) [5.0]
6 Object Relational Features [8.0]
7 Ability to return result sets from stored procedures (ref cursors) [7.2]
8 Commit and Rollback (transactions) [3]
9 Triggers [7.0]?
10 Function based indexes [8.1.5]
11 Materialized Views [7.0]
12 Rman [8.0]
13 Audit SYSDBA/SYSOPER activity [9.2]
14 Automatic Undo Management [9.0]
15 Resumable Operations [9.0]
16 Automatic Storage Management (ASM) [10.1]
17 Streams [9.2]
18 Bitmap Indexes [7.3]
19 csscan - Character Set Scanner utility [8.1.6]
20 Flashback Query [9.0]
21 Case statement [8.1.6] - searched CASE [9.0] - simple CASE
22 Parallel Query [67.1]
23 Transparent column level encryption [10.2]
24 Tablespace encryption [11.1]
25 PL/SQL [6.0]
26 Partitioning [8.0]
27 Row Level Locking [6.0]
28 Read Consistency (my favorite feature!) [4]
29 2 Phase Commit [7.0 (I can still recall Ken demoing this one.]
30 Sorted Hash Clusters [10.1]
31 Conditional compilation for PL/SQL [10.2]
32 Connect By Queries [4]
33 Update anywhere Replication [7.1]

Tom Kyte
October 30, 2007 - 8:09 pm UTC


off by two, after adjusting the 67.1 to be 7.1 :)

Glitch

Toon Koppelaars, October 30, 2007 - 5:23 pm UTC

OK. The 67.1 (parallel query) wat meant to be a 7.1

here is my answer

Winston, October 30, 2007 - 7:50 pm UTC

1 Real Application Testing [11.1]
2 Read only Replication [7.0]
3 Distributed Query [5]
4 Drop column [8.1.6]
5 Client-Server (where the client could be elsewhere in the network) [5]
6 Object Relational Features [8.0]
7 Ability to return result sets from stored procedures (ref cursors) [7.2]
8 Commit and Rollback (transactions) [3]
9 Triggers [7.0 ]
10 Function based indexes [8.1.5]
11 Materialized Views [8.1.6]
12 Rman [8.0]
13 Audit SYSDBA/SYSOPER activity
14 Automatic Undo Management [9.0]
15 Resumable Operations [9.0]
16 Automatic Storage Management (ASM)[10.1]
17 Streams [9.2]
18 Bitmap Indexes [7.3]
19 csscan - Character Set Scanner utility [8.1.7]
20 Flashback Query [9.0]
21 Case statement (IN SQL, instead of decode) [8.1.6]
22 Parallel Query [7.1]
23 Transparent column level encryption [10.2]
24 Tablespace encryption [11.1]
25 PL/SQL [6]
26 Partitioning [8.0]
27 Row Level Locking [6.0]
28 Read Consistency (my favorite feature!) [4]
29 2 Phase Commit [ 8.1.6. ]
30 Sorted Hash Clusters [10.1]
31 Conditional compilation for PL/SQL [10.2]
32 Connect By Queries (select ename, level from emp connect by prior....) [2]
33 Update anywhere Replication [7.1]

Tom Kyte
October 30, 2007 - 8:11 pm UTC

off by three (after adding #13...)

missed this one

Winston, October 30, 2007 - 7:58 pm UTC

Audit SYSDBA/SYSOPER activity [9.2]

Ok, one more try...

Alfonso Rizo-Patron, October 30, 2007 - 9:09 pm UTC

(changed the "connect by" queries to v4)

Real Application Testing = 11.1
Read-Only Replication = 7.0
Distributed Query = 5
Drop Column = 8.1.5
Client-Server = 5
Object-Relational features = 8.0
Return result sets from stored procs (ref cursors) = 7.2
Commit & Rollback = 3
Triggers = 7.0
Function-based indexes = 8.1.5
Materialized Views = 8.1.5
RMAN = 8.0
Audit sysdba/sysoper = 9.2
Automatic UNDO management = 9.0
Resumable operations = 9.0
Automatic Storage Management (ASM) = 10.2
Streams = 9.2
Bitmap indexes = 7.3
csscan = 8.1.6
Flashback query = 9.0
Case stmt = 8.1.6
Parallel Query = 7.1
Transparent column level encryption = 10.2
Tablespace encryption = 11.1
PL/SQL = 6
Partitioning = 8.0
Row-level locking = 6
Read Consistency = 4
2 Phase Commit = 7.0
Sorted Hash Clusters = 10.1
Conditional PL/SQL compilation = 10.2
Connect By queries = 4
Update anywhere replication = 7.1
Tom Kyte
October 30, 2007 - 9:29 pm UTC

nope ;)

What about this one...

Robert, October 30, 2007 - 9:19 pm UTC

Real Application Testing = 11.1
Read-Only Replication = 7.0
Distributed Query = 5
Drop Column = 8.1.5
Client-Server = 5
Object-Relational features = 8.0
Return result sets from stored procs (ref cursors) = 7.2
Commit & Rollback = 3
Triggers = 7.0
Function-based indexes = 8.1.5
Materialized Views = 8.1.5
RMAN = 8.0
Audit sysdba/sysoper = 9.2
Automatic UNDO management = 9.0
Resumable operations = 9.0
Automatic Storage Management (ASM) = 10.1
Streams = 9.2
Bitmap indexes = 7.3
csscan = 8.1.6
Flashback query = 9.0
Case stmt = 8.1.6
Parallel Query = 7.1
Transparent column level encryption = 10.2
Tablespace encryption = 11.1
PL/SQL = 6
Partitioning = 8.0
Row-level locking = 6
Read Consistency = 4
2 Phase Commit = 7.0
Sorted Hash Clusters = 10.1
Conditional PL/SQL compilation = 10.2
Connect By queries = 2
Update anywhere replication = 7.1
Tom Kyte
October 30, 2007 - 9:33 pm UTC

bingo!!!

ops$tkyte%ORA10GR2> select rownum, version, txt
  2    from (select version, txt from features order by rnd)
  3  /

    ROWNUM VERSION              TXT
---------- -------------------- ----------------------------------------------------------------------------------------------------
         1 11.1                 Real Application Testing
         2 7.0                  Read only Replication
         3 5                    Distributed Query
         4 8.1.5                Drop column
         5 5                    Client-Server (where the client could be elsewhere in the network)
         6 8.0                  Object Relational Features
         7 7.2                  Ability to return result sets from stored procedures (ref cursors)
         8 3                    Commit and Rollback (transactions)
         9 7.0                  Triggers
        10 8.1.5                Function based indexes
        11 8.1.5                Materialized Views
        12 8.0                  Rman
        13 9.2                  Audit SYSDBA/SYSOPER activity
        14 9.0                  Automatic Undo Management
        15 9.0                  Resumable Operations
        16 10.1                 Automatic Storage Management (ASM)
        17 9.2                  Streams
        18 7.3                  Bitmap Indexes
        19 8.1.6                csscan - Character Set Scanner utility
        20 9.0                  Flashback Query
        21 8.1.6                Case statement
        22 7.1                  Parallel Query
        23 10.2                 Transparent column level encryption
        24 11.1                 Tablespace encryption
        25 6                    PL/SQL
        26 8.0                  Partitioning
        27 6                    Row Level Locking
        28 4                    Read Consistency (my favorite feature!)
        29 7.0                  2 Phase Commit
        30 10.1                 Sorted Hash Clusters
        31 10.2                 Conditional compilation for PL/SQL
        32 2                    Connect By Queries (select ename, level from emp connect by prior....)
        33 7.1                  Update anywhere Replication

33 rows selected.


very nice, you win a cupie doll...

Getting senile here - corrections:

Alfonso Rizo-Patron, October 30, 2007 - 9:22 pm UTC

Back to 2 on Connect by and 10.1 on ASM (not 10.2)
...
Real Application Testing = 11.1
Read-Only Replication = 7.0
Distributed Query = 5
Drop Column = 8.1.5
Client-Server = 5
Object-Relational features = 8.0
Return result sets from stored procs (ref cursors) = 7.2
Commit & Rollback = 3
Triggers = 7.0
Function-based indexes = 8.1.5
Materialized Views = 8.1.5
RMAN = 8.0
Audit sysdba/sysoper = 9.2
Automatic UNDO management = 9.0
Resumable operations = 9.0
Automatic Storage Management (ASM) = 10.1
Streams = 9.2
Bitmap indexes = 7.3
csscan = 8.1.6
Flashback query = 9.0
Case stmt = 8.1.6
Parallel Query = 7.1
Transparent column level encryption = 10.2
Tablespace encryption = 11.1
PL/SQL = 6
Partitioning = 8.0
Row-level locking = 6
Read Consistency = 4
2 Phase Commit = 7.0
Sorted Hash Clusters = 10.1
Conditional PL/SQL compilation = 10.2
Connect By queries = 2
Update anywhere replication = 7.1

Al.
(I'm done here)
Tom Kyte
October 30, 2007 - 9:36 pm UTC

3 minutes after.... Robert from Canada...

Unexpected!!!

Robert, October 30, 2007 - 10:28 pm UTC

Funny thing is that right before I saw the challenge on your blog, I was reading Dave Ensor's excellent chapter from "Oracle Insights - Tales of the Oak Table", where he talks a bit about Oracle's evolution, including some features and the versions they were introduced.

So, since I was in the "features/versions" mood, I decided to give this challenge a try (even though I wasn't so sure about a couple of features).

"very nice, you win a cupie doll..." -lol
Cool, but you said it would be autographed! ;-)

features

A reader, October 30, 2007 - 11:17 pm UTC

Tom can you share with us how and why you have created this features table?

Thanks.

Tom Kyte
October 31, 2007 - 8:24 am UTC

why - because of the original question, it is sometimes fun to do something like this.

how - just going through old presentations, seminars, training material and picking things out here and there.

Call that a challenge?

Tim all, October 31, 2007 - 5:05 am UTC

A real challenge would include a description of the licensing necessary to use the specific feature in the version it was introduced. :)

Whoops!

Tim Hall, October 31, 2007 - 5:06 am UTC

I can't even spell my own name! :)

versions

Laurent Schneider, October 31, 2007 - 5:10 am UTC

What about the Oracle 8 Release 8.0.4, Oracle 8 Release 8.0.5 and Oracle 8 Release 8.0.6? They are listed as "Releases" on http://www.oracle.com/technology/documentation/index.html

About partitioning, we have partitioned views in 7.3.4 (and maybe before, I do not know)

http://download.oracle.com/docs/cd/A57673_01/DOC/dcommon/oin/index.htm
Partition Views
A partition view is a view that for performance reasons brings together several tables to behave as one.


Congrats to Rob for finding all the correct answers, I did not know Connect by was an Oracle 2 new feature!

Tom Kyte
October 31, 2007 - 8:27 am UTC

Because conventionally speaking, there was 8.0 - there was not a 8.0 release 1, 8.0 release 2 and so on.

Partitioned views != partitioning, partitioned views still exist.

Sort of like snapshots != materialized views. Snapshots existed in 7.0, and were superseded by materialized views in 8i release 1.


and yes, many people are surprised to find "connect by" was a version 2.0 feature!

Bitmap indexes = 7.3

Laurent Schneider, October 31, 2007 - 5:16 am UTC

Thank you

Laurent Schneider, October 31, 2007 - 9:18 am UTC

They should update the documentation then to allow the BITMAP keyword in the CREATE INDEX syntax ;-)

fun stuff

Mark, October 31, 2007 - 9:37 am UTC

I had no clue on most of these, since I've only been using Oracle since around when 9iR2 came out. :) Interesting that CONNECT BY is the oldest feature here...So at some point, you weren't able to drop a column or have read consistency, but you could run a hierarchical query (which to me seems somewhat advanced). That amuses me. :)

I agree

Rahul, October 31, 2007 - 1:00 pm UTC

I agree with Mark. You should have seen me screaming when I saw that 'connect by' was version 2. :)

Rahul

Mark and Rahul, re: connect by

Cosmic Charlie, October 31, 2007 - 1:56 pm UTC

When version 2 of Oracle came out, relational databases were a brand new thing. The prominent DB type was hierarchical. Oracle had to offer a way to handle these relationships or there never would have been Oracle 3.

how about 11g

A reader, November 01, 2007 - 4:19 am UTC

Hi Tom
tried to do it in 11g
ERROR at line 1:
ORA-00942: table or view does not exist

using Oracle for ... storing work-related info

A writer, November 01, 2007 - 9:57 am UTC

ORA-00942: table or view does not exist

That would be Tom's own table/data and not any Oracle dictionary object.
Tom Kyte
November 01, 2007 - 11:35 am UTC

exactly, I created it as follows:

/*

drop table features;
create table features( rnd number, version varchar2(20), txt varchar2(100) );

insert into features values ( dbms_random.random, '2', 'Connect By Queries (select ename, level from emp connect by prior....)');
insert into features values ( dbms_random.random, '3', 'Commit and Rollback (transactions)');
insert into features values ( dbms_random.random, '4', 'Read Consistency (my favorite feature!)');
insert into features values ( dbms_random.random, '5', 'Client-Server (where the client could be elsewhere in the network)');
insert into features values ( dbms_random.random, '5', 'Distributed Query');
insert into features values ( dbms_random.random, '6', 'Row Level Locking');
insert into features values ( dbms_random.random, '6', 'PL/SQL');
insert into features values ( dbms_random.random, '7.0', '2 Phase Commit');
insert into features values ( dbms_random.random, '7.0', 'Triggers');
insert into features values ( dbms_random.random, '7.0', 'Read only Replication');
insert into features values ( dbms_random.random, '7.1', 'Update anywhere Replication');
insert into features values ( dbms_random.random, '7.1', 'Parallel Query');
insert into features values ( dbms_random.random, '7.2', 'Ability to return result sets from stored procedures (ref cursors)');
insert into features values ( dbms_random.random, '7.3', 'Bitmap Indexes');
insert into features values ( dbms_random.random, '8.0', 'Object Relational Features');
insert into features values ( dbms_random.random, '8.0', 'Partitioning');
insert into features values ( dbms_random.random, '8.0', 'Rman');
insert into features values ( dbms_random.random, '8.1.5', 'Materialized Views');
insert into features values ( dbms_random.random, '8.1.5', 'Function based indexes');
insert into features values ( dbms_random.random, '8.1.5', 'Drop column');
insert into features values ( dbms_random.random, '8.1.6', 'Case statement');
insert into features values ( dbms_random.random, '8.1.6', 'csscan - Character Set Scanner utility');
insert into features values ( dbms_random.random, '9.0', 'Automatic Undo Management');
insert into features values ( dbms_random.random, '9.0', 'Resumable Operations');
insert into features values ( dbms_random.random, '9.0', 'Flashback Query');
insert into features values ( dbms_random.random, '9.2', 'Streams');
insert into features values ( dbms_random.random, '9.2', 'Audit SYSDBA/SYSOPER activity');
insert into features values ( dbms_random.random, '10.1', 'Automatic Storage Management (ASM)');
insert into features values ( dbms_random.random, '10.1', 'Sorted Hash Clusters');
insert into features values ( dbms_random.random, '10.2', 'Conditional compilation for PL/SQL');
insert into features values ( dbms_random.random, '10.2', 'Transparent column level encryption');
insert into features values ( dbms_random.random, '11.1', 'Tablespace encryption');
insert into features values ( dbms_random.random, '11.1', 'Real Application Testing');

select distinct version from features order by version;
*/


just used the random number to scramble them up - else they probably would come back in the order of insertion (PROBABLY, from an empty table...)

Updateable views

Lee Elms, November 06, 2007 - 5:45 pm UTC

In which release were updateable views introduced ?
Tom Kyte
November 07, 2007 - 5:05 pm UTC

way back when - views have been updateable for a long long long time.

karthick, December 04, 2007 - 8:49 am UTC

Order by version is not good as version is varchar

this could help

select * from hx_oracle_features order by to_number(substr(version,1,decode(instr(version,'.'),0,1,instr(version,'.'))));

Is JServer required within 10R2?

A reader, July 21, 2008 - 1:07 pm UTC

Hi Tom,

I've a short question. I can't find any documentation about what would happen if I don't install JServer within 10.2? Which features would I loose? What would'nt be working anymore (as LogMiner,...)?

We are discussing internally if we should/have to install JServer even if no programmer is using java stored procedures? What do you mean?


Tom Kyte
July 22, 2008 - 11:04 am UTC

you would have an XE database at that point :)

you would lose most of the XML capabilities.

I would suggest installing it.

Is JServer required within 10R2?

A reader, July 22, 2008 - 1:03 pm UTC

Hi Tom,

Thanks for your answer. I'm afraid "loosing XML functionality" might not be enough to persuade my colleagues.

Is there a documentation somewhere showing in detail which functionality we lose?


Tom Kyte
July 22, 2008 - 6:31 pm UTC

what are your colleagues saying the benefit would be in not having it? Something they can prove, know the numbers of?

I can say

o it would make you unique, different
o it would make you harder to support
o it would make you probably install it during a maintenance window at some point


The memory argument won't fly, to just have it and configure it takes almost nothing. The "it is extra code" won't fly, since you won't be executing it (and if you do, you needed it)

So, what is their reasoning, logic for wanting to remove a pretty common component of the database - they should be striving for "we do a base install that looks like everyone else - that is what everyone else is running - it is by far the most used in real life configuration, meaning we won't run into strange things because we are different"

Is JServer required within 10R2?

A reader, July 23, 2008 - 5:01 am UTC

Hi Tom,

Some of my colleagues are coming from the "good old times" of oracle 6 and 7 as there wasn't any java, xml or other such complicated stuff. ;-(

They argue the system gets that much more complicated the more they install. It gets harder to maintain, manage, upgrade. They will also hit more bugs leading to more patching. They are not willing to do this as they don't need "any" JServer functionality.

Could you please explain which administration functionality we lose if we don't install JServer? Maybe I can argue on that.

Afaik, it is the whole "Log-Mining-Tree". That is Log Miner, CDC, Streams, some Flashback options,... DBMS_FILE_TRANSFER is also based on Streams/JServer, is'nt it? Is there some administration functionality else we lose?

Thanks for your help!

Tom Kyte
July 24, 2008 - 10:08 am UTC

Turn the argument around

They are 100% backwards here. It gets harder to maintain, manage, upgrade - the more they make it UNIQUE FROM EVERY OTHER INSTALL OUT THERE.

Hey, but why do you care - they will be the ones paying the price to maintain, install and upgrade, they will be the ones installing this feature at some point after the fact...


I cannot argue with backwards logic. If they do not understand that being really unique, with a set of features that were not necessarily regressed against internally - will make their lives harder, not easier in any way shape or form - so be it.

The Good Old Days

Paul., July 24, 2008 - 10:46 am UTC

Ah yes,

cpio -icvBdum < /dev/rmt0n

just to load the software while you got a coffee, smoke, movie at the local theater...

Then there was all that relinking to do....

Mmmm, the good old days.

There is nothing easier than installing 10.x (unless it is installing 11.x).

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.