Infrastructure at your Service

Category

Oracle

Oracle Team

The future of SQL*Plus and some oddities

By | Database management, Oracle | One Comment

By Franck Pachot . Rumors are coming after OOW14. Oracle is working on a new SQL*Plus version. Not only a new version. It’s a completely new development. Here is what we know currently about the future of sqlplus an sqldeveloper. But I start with some very old stuff. SQL*Plus sqlplus is an old tool. With many features that look as oddities today. You want to see some oddities? Good idea because it can be error-prone….

Read More
Oracle Team

How to disable all database links

By | Database management, Oracle | 4 Comments

By Franck Pachot . A frequent scenario: you refresh test from production with a RMAN duplicate. Once the duplicate is done, you probably change dblinks so that they address the test environment instead of the production one. But are you sure that nobody will connect in between and risk to access production from the test environement? You want to disable all db links until you have finished your post-duplicate tasks. I know two solutions for…

Read More
Oracle Team

Oracle cloud control / SQL Details / Statistics

By | Database management, Oracle | No Comments

By Franck Pachot . A question that I had several times: in Enterprise Manager, in the screen about one SQL statement, the ‘statistics’ tab shows the number of executions, elapsed time, etc. Question is: which time window does it cover? There is a one hour chart above, and two timestamps displayed as ‘First Load Time’ and ‘Last load Time’, and we don’t know which one is related with the execution statistics numbers. I’ll explain it…

Read More
Oracle Team

Oracle: an unexpected lock behavior with rollback

By | Database Administration & Monitoring, Oracle | No Comments

By Franck Pachot . Here is an odd Oracle behavior I observed when a transaction that acquired a lock is rolled back. Note that this is related to a specific combination of locks that should not be encountered in production. So it’s not a bug. Just something unexpected.   First session In my first session I lock the DEPT table in share mode (RS) 20:56:56 SQL1> lock table dept in row share mode; Table(s) Locked….

Read More
Oracle Team

From 8.0 extended rowid to 12c pluggable db: Why Oracle Database is still a great software

By | Database management, Oracle | 4 Comments

By Franck Pachot . Do you know why Oracle Database is a leader in the database market since 30 years? Do you know any other software that is still the leading solution after decades? I think that it is because Oracle has been a good software from the get-go. Many early decisions in the software architecture have revealed themselves later to be the right decision. Several decisions, such as the C language that made it…

Read More
Oracle Team

What about ALTER INDEX … SHRINK SPACE ?

By | Database management, Oracle | No Comments

By Franck Pachot . I have recently published a script to check index fragmentation. But then, do you COALESCE or REBUILD? Well, there also is another option: ALTER INDEX SHRINK SPACE. Let’s compare all those index defragmentation operations. Fragmented index I have an index created when the table had 1 million rows. Then, I deleted 90% of the rows. Here is the index state from: dbms_space.space_usage index_stats after an analyze index validate structure my index…

Read More
Oracle Team

Oracle IOT: when to use Index Organized Tables

By | Database management, Oracle | No Comments

By Franck Pachot . When can we use IOT? That’s a question I had when giving recently the Oracle DBA essentials workshop. the DBA Essential is very dense and there is only half a day about performance. We have the Oracle Performance Tuning workshop to go into those details. But IOTs are under used in my opinion, so I’ll post a use case for them where they are a good idea. Basically: Index access is…

Read More
Oracle Team

Oracle system statistics: Display AUX_STATS$ with calculated values and formulas

By | Oracle | 9 Comments

By Franck Pachot . System statistics can be gathered in NOWORKLOAD or WORKLOAD mode. Different values will be set depending on that and the others will be calculated – derived from them. We can see defined values from SYS.AUX_STATS$ but here is a script that shows the calculated ones as well. With no system statistics or NOWORKLOAD the values of IOSEEKTIM (latency in ms) and IOTFRSPEED (transfer in bytes/ms) are set and the SREADTIM (time…

Read More
Oracle Team

How to measure Oracle index fragmentation

By | Database management, Oracle | 9 Comments

By Franck Pachot . At Oracle Open World 2014, or rather the Oaktable World, Chris Antognini has presented ‘Indexes: Structure, Splits and Free Space Management Internals’. It’s not something new, but it’s still something that is not always well understood: how index space is managed, block splits, fragmentation, coalesce and rebuilds. Kyle Hailey has made a video of it available here. For me, it is the occasion to share the script I use to see…

Read More
Oracle Team

OOW14 Day 5 – not only Oracle OpenWorld

By | Database management, Oracle | No Comments

By Franck Pachot . Oracle’s OpenWorld has ended. It was the fist time I attended this great event and it really is a “great” event: 60000 attendees from 145 countries 500 partners or customers in the exhibit hall 400 demos in the DEMOgrounds 2500 sessions This is Howard Street, a four lane avenue transformed as the Oracle Plazza for the event – in the middle of the 100000m2 of exibition halls and conference rooms: Oracle…

Read More