Database, SQL and PL/SQL

On Speeding, Dating, and Spelling

Our technologist finds the OS, makes things appear faster, dates clients, and spells out numbers.

By Tom Kyte Oracle ACE Employee

July/August 2006

I once read on your site that someone had demonstrated how to determine the OS of the SQL*Plus session; however, I am now unable to find the information. I have a SQL script that I use to create a SQL script. I would like to extend this further, so that if the original SQL script is called from a UNIX-based SQL*Plus session, the original SQL script creates both a SQL script and a shell script.

I think what you are remembering from the Ask Tom site was how to find the OS of the server the database is running on. That would be via DBMS_UTILITY.PORT_STRING:

SQL> begin
  2  dbms_output.put_line(
  3    dbms_utility.port_string );
  4  end;
  5  /

That will tell you not which OS your SQL*Plus client is running on but rather the OS of the database server itself, which might be different. There are two things you can look at, however, that can be very useful: the PROGRAM and PROCESS columns in V$SESSION. The PROGRAM column generally shows the name of the client program that connects to the database (but this can easily be spoofed if files are copied to different names), and the PROCESS column shows the process ID of the client connecting to the database—the process ID from the client machine itself. Both of these columns will give you a very good idea of the client OS from which SQL*Plus is being executed. If you use the following query

SQL> select program, process.
  2    from v$session
  3    where sid =
  4  (select sid
  5     from v$mystat
  6    where rownum = 1
  7  )
  8  /
------------   --------------
sqlplus.exe    704:416

you can see the name of the client process as well as the client process ID—the above example was a Windows SQL*Plus client, as evidenced by the ".exe" in the program name as well as the colon (:) in the process. If I use a UNIX client to connect to the same database, I will observe something similar to the following:

SQL> select program, process
  8  /
PROGRAM                     PROCESS
-------------------------   --------------
sqlplus@host(TNS V1-V3)     10227

The ".exe" is missing from the program name because UNIX does not use that extension, and further, the process ID of the client does not include the colon as it does for a Windows client.

Thanks to all of the online participants for coming up with some good ideas.

Making Something Fast

We have an application that creates users and places private synonyms in users' accounts. On occasion a security administrator drops multiple users who no longer need access to the database. It can take about two minutes to drop a user that contains about a thousand synonyms. I'm getting complaints that it takes too long to remove the obsolete accounts. Apart from transitioning the system to using public synonyms, do you have any suggestions on improving the performance of the DROP USER command?

It is all about perception. Whenever I have a long-running process, I think about how I can "background" it so an unlucky end user never has to wait for it to complete. If the end user doesn't have to wait for it, it will seem instantaneous.

So, move long-running processes into the background, and the end users think, "Wow, this is really fast!" What I recommend is to turn the process of DROP USER USERNAME CASCADE into the following:

1. ALTER USER USERNAME LOCK; (The account is disabled, so the "secure goal" is achieved.)
2. dbms_job.submit( l_job, 'execute immediate ''drop user a cascade'';' );
3. commit;

And give the user a message immediately that says "OK." The locking of the account will achieve the "goal" of dropping the user (by removing access), and the actual dropping of the user schema—which might take a bit of time—will happen shortly after the COMMIT, in the background, without making the end user wait. As far as the end user is concerned, the act of dropping the user is instantaneous and the response time is always the same. Consistency is important to end users.

I do this with lots of apparently slow things—hide the real work in the background and let the end users continue, and they think the application is much faster than it is.


Why does the NLS_DATE_FORMAT in my init.ora sometimes not work? I have it set, but the default date format isn't set correctly for my applications.

If the client environment sets any of the NLS_* parameters, they override the server in all cases. So if the client sets, for example, the NLS_LANG parameter, that will cause all NLS_* settings on the server to be ignored. The server will use the client's specified values and default values for all other NLS settings instead, ignoring anything in init.ora.

Where that typically comes into play is if the client is Windows. The client install on Windows sets the NLS_LANG parameter in the registry by default. The fact that the client sets the NLS_LANG parameter causes the NLS settings you put in the init.ora not to be used by that client. To solve this, you can

  • Set the NLS_DATE_FORMAT in the registry on the client

  • Put an ALTER SESSION SET nls_date_format= your_format statement in your application right after the connect

  • Use an AFTER LOGON trigger similar to the one supplied below

Personally, I prefer the second option. If you have an application that relies on a specific default date format, it should explicitly request that format. The reason: If you try to install two applications in the same database and their date formats conflict, you won't be able to use both applications without setting the date formats explicitly, which will prevent consolidation in the future. It is best never to have any of your applications dependent on certain default init.ora settings—this would prevent your application from behaving nicely with other applications in the same database.

create or replace trigger 
after logon
    execute immediate
        'alter session 
         set nls_date_format = 
         ''your format here'' ';

Spelling Out a Number

I am trying to spell out a number. That is, I would like to see the number 123 printed as one hundred twenty-three . Are there any functions available for me?

Believe it or not, there almost is. There is a DATE format of 'Jsp' that spells a Julian date:

SQL> select to_char(sysdate,'J'),
  2  to_char(sysdate,'Jsp')
  3  from dual;
Two Million Four Hundred Fifty-Three 
Thousand Eight Hundred Twelve

Now, this works fine for many numbers, but if you go out of the range of Julian dates, you will receive

ERROR at line 1:
ORA-01854: julian date must be 
between 1 and 5373484

With a little creativity, I can expand this to be as large as I need (if 5,373,484 is not large enough). The PL/SQL function in Listing 1 demonstrates how you might do this.

Code Listing 1: PL/SQL function for spelling out numbers

create or replace
function spell_number( p_number in number )
return varchar2
    type myArray is table of varchar2(255);
    l_str      myArray := myArray( '',
                           ' thousand ', ' million ',
                           ' billion ', ' trillion ',
                           ' quadrillion ', ' quintillion ',
                           ' sextillion ', ' septillion ',
                           ' octillion ', ' nonillion ',
                           ' decillion ', ' undecillion ',
                           ' duodecillion ' );
    l_num   varchar2(50) default trunc( p_number );
    l_return varchar2(4000);
    for i in 1 .. l_str.count
        exit when l_num is null;
        if ( to_number(substr(l_num, length(l_num)-2, 3)) <> 0 )
           l_return := to_char(
                            substr(l_num, length(l_num)-2, 3),
                              'J' ),
                       'Jsp' ) || l_str(i) || l_return;
        end if;
        l_num := substr( l_num, 1, length(l_num)-3 );
    end loop;
    return l_return;

Reporting on Database Free Space

I would like a report in SQL*Plus that shows the free space by tablespace. Do you have a working query that provides that?

I do—I've had one for a long time. Basically, what I have to do is generate a query that reports free space by tablespace (aggregating DBA_FREE_SPACE to the tablespace level) and join that to a query that reports the space allocated to each tablespace (including temporary tablespaces).

The problem is that DBA_FREE_SPACE reports space at the extent level within a tablespace and DBA_DATA_FILES/DBA_TEMP_FILES reports allocated space by file within a tablespace. I need to aggregate the data in each of these views to the tablespace level before I combine them. Inline views are very useful for doing this, and I'll make use of them in the query in Listing 2. Additionally, to accommodate tablespaces that are completely full (and hence would have no entries in DBA_FREE_SPACE), I'll use an outer join to put the answer together.

Code Listing 2: Reporting free space by tablespace

set linesize 121
-- free.sql
-- This SQL Plus script lists freespace by tablespace
column          dummy noprint
column          pct_used        format 999.9          heading "%|Used"
column          name            format a19            heading "Tablespace Name"
column          Kbytes          format 999,999,999    heading "Kbytes"
column          used            format 999,999,999    heading "Used"
column          free            format 999,999,999    heading "Free"
column          largest         format 999,999,999    heading "Largest"
column          max_size        format 999,999,999    heading "MaxPoss|Kbytes"
column          pct_max_used    format 999.           heading "%|Max|Used"
break           on report
compute sum of kbytes on report
compute sum of free on report
compute sum of used on report
select (select decode(extent_management,'LOCAL','*',' ') ||
               decode(segment_space_management,'AUTO','a ','m ')
          from dba_tablespaces where tablespace_name = b.tablespace_name) ||
             nvl(a.tablespace_name,'UNKOWN')) name,
       kbytes_alloc kbytes,
       kbytes_alloc-nvl(kbytes_free,0) used,
       nvl(kbytes_free,0) free,
                          kbytes_alloc)*100 pct_used,
       nvl(largest,0) largest,
       nvl(kbytes_max,kbytes_alloc) Max_Size,
       decode( kbytes_max, 0, 0, (kbytes_alloc/kbytes_max)*100) pct_max_used
from ( select sum(bytes)/1024 Kbytes_free,
              max(bytes)/1024 largest,
       from  sys.dba_free_space
       group by tablespace_name ) a,
     ( select sum(bytes)/1024 Kbytes_alloc,
              sum(maxbytes)/1024 Kbytes_max,
       from sys.dba_data_files
       group by tablespace_name
       union all
      select sum(bytes)/1024 Kbytes_alloc,
              sum(maxbytes)/1024 Kbytes_max,
       from sys.dba_temp_files
       group by tablespace_name )b
where a.tablespace_name  = b.tablespace_name
order by 1

The query in Listing 2 supplies the following output:

  • Tablespace Name: The name of the tablespace. A leading asterisk (*) in the tablespace name indicates that the tablespace is a locally managed one, whereas a leading blank means that it is an old-fashioned dictionary-managed tablespace. If the second character is a , that tells you that the tablespace is using Automatic Segment Space Management (ASSM) managed storage, whereas if the second character is M , that tells you that the tablespace is manually managed (pctused, freelists, and so on are used to control space utilization).

  • Kbytes: Allocated space of the tablespace; the sum of kilobytes consumed by all datafiles associated with the tablespace.

  • Used: Space in the tablespace that is used by some segment.

  • Free: Space in the tablespace not allocated to any segment.

  • % Used: Ratio of free to allocated space.

  • Largest: The size of the largest contiguous set of blocks available (useful mostly with dictionary-managed tablespaces). If this number in a dictionary-managed tablespace is smaller than the next extent for some object, that object could fail with an "out of space" message, even if the FREE column says there is lots of free space.

  • MaxPoss Kbytes: The autoextend maximum size. (Note: This can be smaller than the allocated size! You can set the maximum size to be less than the current size of a file.)

  • % Max Used: How much of the maximum autoextend size has been used so far.

Base Conversions

How can I convert a number to some other base (say base 2 or base 16) and back again?

There are two parts to this answer. Starting in Oracle8i, the TO_CHAR and TO_NUMBER functions can handle conversions from base 10 (decimal) to base 16 (hexadecimal) and back again:

SQL> select to_char(123,'XX') to_hex,
  2    to_number('7B','XX') from_hex
  3    from dual
  4  /
------  ----------------- 
7B     123

If you need to cover other bases, such as octal (base 8) or binary (base 2), you can accomplish that in PL/SQL pretty easily; in fact, I'll do the base 16 conversions as well. First, I'll code a routine that converts a positive decimal number into any other base up to base 36 (extending the algorithm used to represent numbers up to base 16). See Listing 3.

Code Listing 3: Converting decimals up to base 36

create or replace function to_base( p_dec in number, p_base in number )
return varchar2
    l_str   varchar2(255) default NULL;
    l_num   number  default p_dec;
    l_hex   varchar2(50) default '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ';
    if ( trunc(p_dec) <> p_dec OR p_dec < 0 ) then
        raise PROGRAM_ERROR;
    end if;
        l_str := substr( l_hex, mod(l_num,p_base)+1, 1 ) || l_str;
        l_num := trunc( l_num/p_base );
        exit when ( l_num = 0 );
    end loop;
    return l_str;
end to_base;

Then I need a corresponding routine to convert from any given base back into decimal. This is in Listing 4.

Code Listing 4: Converting other bases to decimal

create or replace function to_dec
( p_str in varchar2,
  p_from_base in number default 16 ) return number
    l_num   number default 0;
    l_hex   varchar2(50) default '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ';
    if (p_from_base = 16)
        l_num := to_number( p_str, rpad('x',63,'x') );
        for i in 1 .. length(p_str) loop
            l_num := l_num * p_from_base + instr(l_hex,upper(substr(p_str,i,1)))-1;
        end loop;
    end if;
    return l_num;
end to_dec;

For convenience, I use the small routines in Listing 5 to perform the most-common conversions.

Code Listing 5: Common base conversion routines

create or replace function to_hex( p_dec in number ) return varchar2
    return to_char( p_dec, 'fm'||rpad('x',63,'x') );
end to_hex;
create or replace function to_bin( p_dec in number ) return varchar2
    return to_base( p_dec, 2 );
end to_bin;
create or replace function to_oct( p_dec in number ) return varchar2
    return to_base( p_dec, 8 );
end to_oct;


I need to know how to prevent Microsoft Windows Server 2003 users with the oradba role from logging into my database without a password. When I'm logged in as administrator—a member of oradba—I can log in to the database AS SYSDBA with any password, even an empty one.

Well, technically, you did use a password. You logged in to the OS, and you provided a password then.

AS SYSDBA is extremely powerful. It uses OS authentication; it does not require database authentication. In fact, it is used to log in before there is an instance. What you need to do is lock down the accounts that are in this group. Remove the users that should not have this excessively strong capability from the oradba group. Users in this group are always permitted access to the database instance AS SYSDBA.

Next Steps

Oracle Vice President Tom Kyte answers your most difficult technology questions. Highlights from that forum appear in this column.

READ more Tom
 Expert Oracle Database Architecture: 9i and 10g Programming Techniques and Solutions

 DOWNLOAD Oracle Database 10g Express Edition


Photography by Aaron Burson, Unsplash