11,832 reputation
11832
bio website ddbcinc.com
location Rochester Hills, MI
age 37
visits member for 3 years, 6 months
seen 38 mins ago
Oracle ACE

May
7
comment Is commit necessary after DML operation in Function/Procedure?
@Phil - True, but that's a very different animal. An autonomous transaction cannot see the uncommitted changes made by the caller and cannot be rolled back by the caller so it is extremely unlikely that anything other than a logging procedure ought to be declared to use an autonomous transaction.
May
7
answered Oracle extents - How can I have so many with this small size?
May
7
awarded  Nice Answer
May
7
comment Is commit necessary after DML operation in Function/Procedure?
@NickChammas - Oracle doesn't have the concept of a nested transaction, no. If the procedure commits, everything the caller had done up to that point would commit. The caller always starts a transaction implicitly with its first statement (whether that is a procedure call or something else) so it should always be up to the caller to end the transaction.
May
7
answered Is commit necessary after DML operation in Function/Procedure?
May
6
comment oracle sql dependency extractor
Is there any chance you could turn the SQL scripts into stored procedures so that you (or a tool) can use Oracle's dependency tracking data dictionary views? Do you really need to create intermediate tables-- in Oracle, it's almost certainly better to create the tables once when you deploy your application and then to use them in your process-- if the table data is temporary, perhaps they should be global temporary tables.
May
3
comment Oracle extents - How can I have so many with this small size?
Do you really have a dictionary managed tablespace in an 11.2 database? That's the only way to get those initial and next extent sizes. Can you post the query or queries you're using to get these numbers?
May
3
revised Upgrading 10g database to 11g database
improved the grammar of the post
May
3
answered Upgrading 10g database to 11g database
May
2
answered I want to know about the IOPS (I/O Per Second) and How it influences the DB CRUD operation
Apr
20
comment restore database from dmp file
What sort of dump file do you have? Do you know the command line that was used to generate it? Do you know whether it was generated using the classic export utility or the newer DataPump version? Do you know if it is a schema-level export, a full export, a tablespace-level export, or something else?
Apr
20
comment oracle crash with ORA-00942: table or view does not exist
@snow8261 - I updated my answer with a trigger that will log the SQL statement.
Apr
20
revised oracle crash with ORA-00942: table or view does not exist
added 1070 characters in body
Apr
20
answered oracle crash with ORA-00942: table or view does not exist
Apr
16
answered What are the design alternatives when having multiple “type” tables
Apr
14
answered Oracle: DB *partial* constraint?
Apr
13
comment How can I change three hundred procedures at once?
@RainierMorilla - Obviously, you'll want to test it thoroughly but that looks reasonable. Of course, code has a general tendency to resist simple approaches because inevitably someone decided to put things on multiple lines or to do something else that would break a simple TRANSLATE
Apr
13
comment Join on clustered tables shows greater cost than non-clustered tables?
@zainshah120 - The actual amount logical I/O that the query does (consistent gets and block gets), the amount of CPU that it consumes, the amount of memory that it consumes, etc. If you're looking at a query plan rather than running a query, you should be much more concerned that the cardinality estimates for each step are accurate rather than focusing on the COST.
Apr
13
answered How can I change three hundred procedures at once?
Apr
12
answered Join on clustered tables shows greater cost than non-clustered tables?