sqlplus substr formating and column width


Doing a little connection pool troubleshooting (OAAM, I am looking at you!)  I found a great sqlplus script on stackoverflow.

The script uses the substr function to format the width of each column. As in:


select
       substr(a.spid,1,9) pid,
       substr(b.sid,1,5) sid,
       substr(b.serial#,1,5) ser#,
..

The problem: The column widths are all too wide. The values specified in the substr function seem to be ignored.

The solution:  If your database is using a multi-byte character encoding (and why wouldn't you?) you need to use the substrb function. Like this:

select
       substrb(a.spid,1,9) pid,
       substrb(b.sid,1,5) sid,
       substrb(b.serial#,1,5) ser#,
..

Comments

Post a Comment

Popular posts from this blog

OAM R2 REST APIs for Policy Management

Image
Oracle Access Manager 11g R2 provides several new REST APIs. This continues a trend to expose key functionality via Web Services. The OAM Mobile and Social service provides APIs for Authentication, Authorization  and User Profile services.  I will cover those APIs in a future article (have a look here for examples) - but today I want to focus on the  policy management APIs. The Policy Administration API  enables to you to interact with OAM to create a variety of Policy objects such as Application Domains, Resources, AuthN Schemes, and AuthN/AuthZ policies. The policy model is shown below: For example, if you want to retrieve all of the resources in an Application Domain you can perform a GET against the /resource URI: curl -u USER:PASSWORD http://<SERVER>:<PORT>/oam/services/rest/11.1.2.0.0/ssa/policyadmin/resource?appdomain="IAM Suite" Note: The port above is where the OAM Admin Server is deployed (often 7001). It ...
Read more

Stupid Oracle vktm tricks to improve VirtualBox performance

In the process of creating a demo VirtualBox image running OEL 6 and the Oracle database 11.2.0.3.0 I noticed the idle CPU consumption was quite high (8% on the guest, 35% on the host). The culprit turned out to be the Oracle database vktm process. This is a time keeping process - and it calls gettimeofday() *very* frequently.  This can have a negative performance impact in virtualized environments. A colleague who is a database whiz suggested the following trick: sqlplus / as sysdba alter system set "_high_priority_processes"='LMS*' scope=spfile;  This removes the vktm process from the list of high priority processes. After this change (you need to bounce the database) the idle CPU consumption comes down to 1-2% or so. A nice improvement! It goes without saying that this is: a) Totally unsupported b) Probably dangerous. This will most certainly break things in the database - such as statistics, auditing, etc. c) For demo/development use...
Read more

Creating an internal CA and signed server certificates for OpenDJ using cfssl, keytool and openssl

Yes, that title is quite a mouthful, and mostly intended to get the Google juice if I need to find this entry again. I spent a couple of hours figuring out the magical incantations, so thought I would document this here. The problem: You want OpenDJ to use something other than the default self-signed certificate for SSL connections.   A "real" certificate signed by a CA (Certificate Authority) is expensive and a pain to procure and install. The next best alternative is to create your own "internal" CA, and  have that CA sign certificates for your services.   In most cases, this is going to work fine for *internal* services that do not need to be trusted by a browser. You might ask why is this better than just using self-signed certificates?  The idea is that you can import your CA certificate once into the truststore for your various clients, and thereafter those clients will trust any certificate presented that is signed by your CA. For example, assu...
Read more