Another thing
Iru, October 17, 2005 - 2:40 am UTC
Hi Tom and others.
Personally i think there are other problems that are, say more 'structural':
- The growing number of developers who think of the database as a 'collection of tables and indexes' and nothing more, leaving all the logic to the application side.
- The also growing number of young developers who are 'launched' to the market with some tiny course of sql and forms, and 'learn bad habits' in programming as they do not read any documentation. (In many occasions it's not their fault, you have to pay the bills somehow, you know).
- The (fortunately) less number of people who learn Oracle with version 7, or 6 or even 5 and have not bothered to learn more, and still program and have concepts of this 'age'.
I'm sure there are many more. But from reading a lot on Tom's site, these are happening a lot.
Saludos a Todos.
autonomous transaction
Stojan, October 17, 2005 - 2:41 am UTC
Hello Tom,
If using autonomous transaction means you have another bug how would you go about adding debugging code to your app that writes to tables.
October 17, 2005 - 7:40 am UTC
... you probably have yet another bug.
keyword - probably. Most times I see it being used, it is used wholly inappropriately - to the degree that I would rather have the feature removed at this point because of all of the data integrity issues people have managed to cook up with it.
I debug to OS files myself typically. I can access them as a table if needed.
Slow-by-slow processing and database "independence"
Valdimir Andreev, October 17, 2005 - 3:45 am UTC
Just two of my "favorites"
ORA-01785
A reader, October 17, 2005 - 9:38 am UTC
agree with most items
ORA-01785 is one thing I never understood - never
even tried to understand
to A. Reader
Andrew, October 17, 2005 - 9:53 am UTC
> select user_id, username, account_status from dba_users order by 4;
select user_id, username, account_status from dba_users order by 4
*
ERROR at line 1:
ORA-01785: ORDER BY item must be the number of a SELECT-list expression
Looks like trying to sort, by position, on a column not in the result set.
David Aldridge, October 17, 2005 - 10:29 am UTC
Bye-bye buffer cache hit ratio, hello Oracle Wait Interface.
What the OWI is, how to turn it on and off, how to read the results etc.
Another one.-
Mariano, October 17, 2005 - 12:45 pm UTC
Supposed mandatory tablespace for data and another one for indexes. The wrong thing here is that a lot of DBAs and developers thinks this is the only way to go.
October 17, 2005 - 1:09 pm UTC
well, it is not necessarily "bad", the misunderstanding is the "why you might do it"
You don't really do it for "tuning"
You segregate objects basically to make your life easier. The thing that needs to be understood is that if your life is EASIER with an index in the same tablespace with the data - it is perfectly OK.
You might split them out for many other logical reasons though (like backup and recovery)
order by 4
Mikito Harakiri, October 17, 2005 - 2:35 pm UTC
This is certainly ANSI SQL standard fault. Ordering by column positions is just plain nonsence.
If you are not convinced, consider slight variations in the "order by":
order by 4.000001 -- legitimate, but very different from order by 4
order by -1
etc.
In a word "order by expression" should be treated exactly as the expression is treated in the other parts of the SQL block
October 17, 2005 - 8:52 pm UTC
for once Mikito, I agree with you :)
1785
A reader, October 17, 2005 - 3:15 pm UTC
there are cases when you get 1785 not ordering by
a column-position, but by some expression
the workaround then is to transform
select cols
from tabs
where clause
order by ocols
into
select * from (
select cols
from tabs
where clause
)
order by ocols
example ?
search for ora-01785 on asktom
Tom agrees Mikito!
A reader, October 18, 2005 - 3:48 am UTC
Thats very strange for us.
It seems that Tom is getting old enough to.... ;)
October 18, 2005 - 9:09 am UTC
Or it means that Mikito said something I agree with...
A little bit about indexes and optimizer
bipul, October 18, 2005 - 5:15 am UTC
My 2c.
Some knowledge of indexes would be useful for everybody [developers and DBAs alike]. Especially, the difference betwen b-tree and bitmap indexes. When b-tree is useful? When not to use bitmap [ Sometimes, I see loads of bitmap indexes in an OLTP environment ].
And also understanding of optimizer should be useful. Some basic things like how optimizer decides when to use indexes. Few notes about various joins, nested loop, hash join etc. will be helpful to write better query.
-bipul
October 18, 2005 - 9:10 am UTC
Jonathan Lewis has a book out real soon that'll help nail that last bit.
Another feature!!!
A reader, October 18, 2005 - 8:38 am UTC
Hi Tom,
I fail to understand the logic behind this feature:
When a tables[ace is created in Automatic Allocation mode it sets the maxextents to a huge number and it cannot be changed until it is made manual.
As per Oracle itself, they recommmend the setting for mazextents to be a maximum of 1024 for better performance.
Then why has it been designed that way.
Please clarify.
October 18, 2005 - 9:20 am UTC
Where does oracle say 1024 for better performance?
maxextents are unlimited. IF you believe something shouldn't have lots of extents THEN you should be using a better extent size to limit the number of extents. Just "hitting a wall" (eg: 1000 max extents - stop, fail transactions) only incurrs DOWNTIME, it doesn't actually solve any problem.
Some things that are little understood
Robert Simpson, October 18, 2005 - 10:03 am UTC
Using the following table:
create table t (c varchar2(1));
insert at least 10 rows into table t with different characters, such as 'A' through 'J'
1) For users who are familiar with other non-Oracle databases, here's one:
insert into t values('');
select * from t where c = '';
Can you predict how many rows are returned?
Hint: it's not the same as in other DBMSes
2) select * from t where rownum != 5;
Can you predict how many rows are returned?
Hint: it's always the same, regardless of how many rows are in the table
Killing sessions
Gary, October 18, 2005 - 6:49 pm UTC
The one I'd add (and it's a quickie) is that a session that is killed still has to rollback and so won't necessarily go away immediately (and why killing its Oracle process or restarting the database won't make it rollback any faster). Oh, and that it's better to keep the client (SQL*Plus, TOAD, whatever) until it actually gets the 'your session has been killed' message.
Resource Busy Error while rollback
Rajeswari, November 21, 2005 - 7:46 am UTC
I accept with Gary comments.
Our application is in Pro*C program which does bulk insert(6Million) and update(3Million) based on flat file input. Before update we call procedure which does truncate of the table. For abnormal test scenorio they cancel(Ctlrl+C) the application and immediately re-run the application which calls the truncate procedure. Oracle takes sometime to rollback and release the table so resource busy error is thrown. So it will be better if we have some indication whether background process(rollback) is completed or not.
November 21, 2005 - 8:52 am UTC
you do have an indication
ora-54 is that indication?
PLSQL privileges
Chip Dawes, November 21, 2005 - 4:39 pm UTC
I think alot of folks don't understand why they need a direct grant of privileges (instead of through a role) for a PLSQL program with default (definer) rights.