Re: Some features of Oracle

On Tue, 23 Sep 1997 10:11:20 +1000, Vsevolod Afanasjev <afanasjev_at_cse.unsw.edu.au> wrote:

>Some funny features of Oracle:
>
>1. RESOURCE and DBA roles carry implicitly the system privilege
>UNLIMITED TABLESPACE. This fact is never mentioned in Concepts or
>Adm. Guide. It is mentioned only once in SQL Reference. This privilege
>does not appear in the list of privileges granted to these roles.

You get UNLIMITED TABLESPACE by default profile.

>
>2. User group PUBLIC has nothing to do with PUBLIC rollback segments.

Public rollback segments are accessible by any instance when running parallel server, it has nothing to do with user accounts.

>3. User SYSTEM has nothing to do with SYSTEM tablespace or SYSTEM
>rollback segment.

I agree.

>4. The first command for creating a new database is CONNECT INTERNAL
>even there is nothing to connect to.
>
>5. To reset the High Water Mark, you need to delete all data.

You need to truncate the table in order to do this, which frees all storage. So what's wrong here, with respect to meaning of the high water mark ?

>6. Message "snapshot too old" has nothing to do with snapshots.

You're right, it has nothing to do with the replication features. The term 'snapshot' refers to Oracle's approach to concurrent data access: read consistency. A DML statement or cursor always sees the data as it was when execution began. This is done by saving the before image into the rollback segments. If your rollback segments are too small, or if you commit inside a cursor loop, this 'snapshot' gets overwritten and you will get this message.

>7. CREATE DATABASE command creates one RBS while you need two.

Say, you don't have piles of scripts handy for such tasks ?? :-)

>8. SYSTEM rollback segment must be online. At the same time, it must not
>be listed in INIT.ORA.

The system rollback segment is private to the instance.

>9. CREATE DATABASE command creates, mounts, and opens the database.
>Similarly, CREATE TABLESPACE command brings newly created tablespace
>online. However, CREATE ROLLBACK SEGMENT command creates offline
>rollback segment which should be brought online separately.
>
>10. Do you want to have fun? Try ALTER DATABASE CLOSE.

I prefer shutdown normal. ;-)

>11. You need to apply the last incremental export twice.
>
>12. There is no way to access the current SCN.

What use could you make of it ?

>13. There is no way to check the extendability status of datafiles.
>
>14. If SELECT a INTO l_a with WHERE condition does not find any rows,
>it raises an exception. At the same time, SELECT count(*) does not
>raise this exception.

Aggregate functions won't ever raise no_data_found. The select .. into behaviour fully complies to the SQL standard.

>15. ROWID is both a pseudocolumn and a datatype.

The rowid is the physical storage address of a row. Why did they decide to implement a rowid data type ? I don't know, perhaps just for giving developers the opportunity to develop code that cannot easily be ported to the next Oracle release :-).

>16. Apparently, NUMBER is a variable-length datatype (in the sence
>that a small number, like 5, will occupy less space than
>123456789012345678901234567890). Why isn't it called VARNUMBER?
>
>17. DBMS_OUTPUT.ENABLE does not enable output in SQL*Plus. You need
>to use SQL*PLus command SET SERVEROUTPUT ON.

It is not sufficient to have output enabled on the server. The client program normally has to look if there's something in the output buffer using DBMS_OUTPUT.GET_LINE. If you SET SERVEROUTPUT ON, SQL*Plus kindly performs these calls in the background for you.

>18. With SID/Windows Registry set properly, Personal Oracle 7.2 under
>Win95 does not ask for password for INTERNAL when connecting from
>SQL*DBA but does ask when connecting from SQL*Plus.
>
>19. BEFORE INSERT FOR EACH ROW trigger is actually fired AFTER an
>attempt of insert. If you don't believe it (I know you don't), then
>create table with NOT NULL column and BEFORE INSERT FOR EACH ROW
>trigger which checks whether the column is NULL and attempt to insert
>NULL in the column. What message do you get, from constraint or from
>trigger?

The message comes from the constraint violation, as expected. Changing columns in before triggers is done before constraints are checked, so there's nothing wrong with the trigger code. For details on trigger execution, see Oracle Server Concepts, Ch. 15 Database Triggers: Trigger behaviour just seems allright to me, I can't see your point.

>20. Oracle SQL Trace is not the same as Oracle TRACE.

Yes, and getting into performance trouble it's great to have both of them.

>21. If you have a table with MINEXTENTS >> 1, then EXPORT/IMPORT with
>COMPRESS=Y will increase the space allocated for the table by a factor
>of two. Indeed, this option will lead to "consolidation" of all
>existing extents into one, however, MINEXTENTS will be preserved. For
>example, I create table with
> STORAGE (INITIAL 10K NEXT 10K MINEXTENTS 5 MAXEXTENTS 10
> PCTINCREASE 0),
>fill all five initial extents with data,
>and export it with COMPRESS=Y, then the export file will contain
> STORAGE (INITIAL 50K NEXT 10K MINEXTENTS 5 MAXEXTENTS 10)
>
>22. It is impossible to use overloaded (packaged) functions
>in SQL statements because the pragma RESTRICT_REFERENCES
>applies only to the last function (but they fixed this in
>Oracle8).
>
>What do you think?

Sorry, I couldn't comment on all your points from scratch. Yes, Oracle may have some stunning features, perhaps even some small inconsistencies in concepts and terminology, but that's what working with it makes life an ever interesting adventure :-)) And besides, do you know any other database with which you can do what you can with Oracle ?

Peter Received on Wed Sep 24 1997 - 00:00:00 CDT