Infrastructure at your Service

Oracle Archives - Page 32 of 40 - Blog dbi services

Oracle Team

Index on SUBSTR(string,1,n) – do you still need old index?

By | Database management, Oracle | No Comments

By Franck Pachot . In a previous post I’ve shown that from 12.1.0.2 when you have an index on trunc(date) you don’t need additional index. If you need the column with full precision, then you can add it to the index on trunc(). A comment from Rainer Stenzel asked if that optimization is available for other functions. And Mohamed Houri has linked to his post where he shows that it’s the same with a trunc()…

Read More
Nicolas Jardot

AOUG – Real World Performance Tour

By | Database management | No Comments

This week, Tom Kyte, Graham Wood and Andrew Holdsworth were present in Europe for several dates. One of the events was organised by the Austrian Oracle User Group (AOUG) in collaboration with the German and Swiss User Group (DOAG and SOUG) and I had the chance to be there to attend to one session of the Real Worl Performance tour session in Vienna. I think there is no real need to present the Real World…

Read More
Oracle Team

Standard Edition on Oracle Database Appliance

By | Database management, Oracle | 2 Comments

By Franck Pachot . The Oracle Database Appliance is really interresting for small enterprises. It’s very good hardware for very good price. It’s capacity on demand licensing for Enteprise Edition. But small companies usually go to Standard Edition for cost reasons. Then does it make sense to propose only Enterprise Edition to the small companies that are interrested by ODA? Is ODA for small companies? My answer is definitely yes. Small companies can’t buy specific…

Read More
Nicolas Jardot

Use a wallet to encrypt Oracle client passwords

By | Database management | No Comments

In our more and more connected world, security is a big concern. Storing password is a very important topic because if a machine is compromised, we can assume that any password stored in clear text are also compromised. I had the question from a customer how can we encrypt the password in our application and monitoring scripts? In this blog we will discuss one solution: using the Oracle client wallet We find passwords for Oracle…

Read More
David Hueber

ODA 12.1.X.X.X – add a multiplexed control file under ACFS

By | Database management | 5 Comments

Since version 12, ODA stores databases on ACFS volumes instead of ASM directly. This slightly changed the way the files are managed and administer. This articles presents how to multiplex your control files on ACFS. At first database creation, ODA set up 3 ACFS volumes to store all non-CDB databases (CDB are managed on dedicated volumes): /dev/asm/datastore-386 62G 5.2G 57G 9% /u01/app/oracle/oradata/datastore /dev/asm/datastore-286 1.7T 871G 836G 52% /u02/app/oracle/oradata/datastore /dev/asm/datastore-360 2.2T 5.6G 2.2T 1% /u01/app/oracle/fast_recovery_area/datastore These…

Read More
Oracle Team

Generic query for multicriteria search – part II: BIND_AWARE (Adaptive Cursor Sharing)

By | Database management, Oracle | 3 Comments

By Franck Pachot . In the previous post I explained the performance issue encountered when using a generic query to deal with optional search criteria on multiple columns. The statement was shared by all executions, was marked as bind sensitive, but never became bind aware. Let’s use the BIND_AWARE hint. All binds null I assign null for all of them – meaning that I don’t want to filter anything: SQL> exec :job_id:=null; :department_id:=null; :manager_id:=null; :employee_id:=null;…

Read More
Oracle Team

Query the Enterprise Manager collected metrics

By | Database management, Oracle | 5 Comments

By Franck Pachot . Enterprise Manager (Cloud Control for example) gathers a lot of metrics. You can display them from the GUI, but you can also query the SYSMAN views directly. Today, I wanted to get the history of free space in an ASM disk group for the previous week. Here is how I got it. Enterprise Manager metrics are aggregated in MGMT_METRICS_1HOUR (granularity 1 hour, retention 1 month) and MGMT_METRICS_1DAY (granularity 1 day, retention…

Read More
Oracle Team

How to set NLS for SQL Developer

By | Database management, Oracle | No Comments

By Franck Pachot . I’m using Oracle SQL Developer 4.1 Early Adopter for a while and I like it. That version comes with a command line (in beta) which goal is to be fully compatible with sqlplus but running in java, and having a lot more features. Becuse it’s connecting with thin java driver by default, it doesn’t use NLS_LANG. It’s java. It’s unicode. So here is how to set the language and characterset with…

Read More
Oracle Team

OracleText: deletes and garbage

By | Database management, Oracle | No Comments

By Franck Pachot . In the previous post we have seen how the OracleText index tables are maintained when new document arrives: At sync the new documents are read up to the available memory and words are inserted in the $I table with their mapping information. Now we will see how removed documents are processed. We will not cover updates as their are just delete + insert. Previous state Here is the state from the…

Read More
Oracle Team

OracleText: inserts and fragmentation

By | Database management, Oracle | No Comments

By Franck Pachot . I plan to write several posts about OracleText indexes, which is a feature that is not used enough in my opinion. It’s available in all editions and can index small text or large documents to search by words. When you create an OracleText index, a few tables are created to store the words and the association between those words and the table row that contains the document. I’ll start to show…

Read More