Tom Kytes Challenge

I Came across a wonderful Challenge Tom(Thomas Kyte) gave to people out there some time back, while going through his asktom site.

The challenge is simple(not as it seems) : Have to correctly provide ALL of the versions the following features were added to Oracle.

Thought interesting so blogged it here.

Happy reading, fun along with knowledge.....just go on

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

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.

=======================================
Check your answers:


select rownum, version, txt
2 from (select version, txt from features order by rnd)
3 /

or

col txt for a40
select * from features order by
to_number(substr(version,1,decode(instr(version,'.'),0,1,instr(version,'.'))))
/



ROWNUM VERSION TXT
---------- -------------------- ----------------------------------------
1 8.1.5 Materialized Views
2 10.1 Sorted Hash Clusters
3 8.0 Rman
4 8.1.6 Case statement
5 7.0 2 Phase Commit
6 11.1 Real Application Testing
7 2 Connect By Queries (select ename, level
from emp connect by prior....)

8 9.0 Automatic Undo Management
9 3 Commit and Rollback (transactions)
10 7.2 Ability to return result sets from store
d procedures (ref cursors)

11 8.1.5 Function based indexes
12 11.1 Tablespace encryption
13 8.1.6 csscan - Character Set Scanner utility
14 4 Read Consistency (my favorite feature!)
15 7.0 Triggers
16 10.1 Automatic Storage Management (ASM)
17 7.1 Update anywhere Replication
18 5 Distributed Query
19 7.1 Parallel Query
20 5 Client-Server (where the client could be
elsewhere in the network)

21 10.2 Transparent column level encryption
22 10.2 Conditional compilation for PL/SQL
23 8.1.5 Drop column
24 9.2 Streams
25 8.0 Object Relational Features
26 9.0 Flashback Query
27 9.2 Audit SYSDBA/SYSOPER activity
28 7.3 Bitmap Indexes
29 6 PL/SQL
30 7.0 Read only Replication
31 6 Row Level Locking
32 8.0 Partitioning
33 9.0 Resumable Operations
================================================================================

Want to know how Tom exactly, created this table :

here is the script,

/*

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;
*/

===================================================================
Tom Kytes web sites...

http://tkyte.blogspot.com/
http://asktom.oracle.com/