Ask Tom: On Speeding, Dating, and Spelling

On Speeding, Dating, and Spelling
By Tom Kyte
Our technologist finds the OS, makes things appear faster, dates clients, and spells out numbers.
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> begin2 dbms_output.put_line(3 dbms_utility.port_string );4 end;5 /Linuxi386/Linux-2.0.34-8.1.0
 
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$session3 where sid =4 (select sid5 from v$mystat6 where rownum = 17 )8 /PROGRAM PROCESS------------ --------------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.
Seeasktom.oracle.com/~tkyte/DetermineOS.html for the original online discussion and the evolution of this answer. 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.
NLS_DATE_FORMAT Isn‘t Working
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 triggerdata_logon_triggerafter logonON DATABASEbeginexecute immediate‘alter sessionset nls_date_format =‘‘your format here‘‘ ‘;end;/
 
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;TO_CHAR----------------TO_CHAR(SYSDATE,‘JSP‘)------------------------------------------2453812Two Million Four Hundred Fifty-ThreeThousand 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 bebetween 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. I encourage you to seeasktom.oracle.com/~tkyte/SpellNumber.html online as well to see the evolution of this question over time, including user contributions regarding spelling out numbers in other languages.
Code Listing 1: PL/SQL function for spelling out numbers
create or replacefunction spell_number( p_number in number )return varchar2astype 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);beginfor i in 1 .. l_str.countloopexit when l_num is null;if ( to_number(substr(l_num, length(l_num)-2, 3)) <> 0 )thenl_return := to_char(to_date(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;end;/
 
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 noprintcolumn 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 reportcompute sum of kbytes on reportcompute sum of free on reportcompute sum of used on reportselect (select decode(extent_management,‘LOCAL‘,‘*‘,‘ ‘) ||decode(segment_space_management,‘AUTO‘,‘a ‘,‘m ‘)from dba_tablespaces where tablespace_name = b.tablespace_name) ||nvl(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-nvl(kbytes_free,0))/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_usedfrom ( select sum(bytes)/1024 Kbytes_free,max(bytes)/1024 largest,tablespace_namefrom sys.dba_free_spacegroup by tablespace_name ) a,( select sum(bytes)/1024 Kbytes_alloc,sum(maxbytes)/1024 Kbytes_max,tablespace_namefrom sys.dba_data_filesgroup by tablespace_nameunion allselect sum(bytes)/1024 Kbytes_alloc,sum(maxbytes)/1024 Kbytes_max,tablespace_namefrom sys.dba_temp_filesgroup by tablespace_name )bwhere a.tablespace_name (+) = b.tablespace_nameorder by 1/
 
The query in Listing 2 supplies the following output:
 
ASK Tom
Oracle Vice President Tom Kyte answers your most difficult technology questions. Highlights from that forum appear in this column.
asktom.oracle.com
READ more about
getting the OS
spelling out a number
READ more Tom
Expert Oracle Database Architecture: 9i and 10g Programming Techniques and Solutions
DOWNLOADOracle Database 10g Express Edition
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_hex3 from dual4 /TO_ FROM_HEX------ -----------------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 varchar2isl_str varchar2(255) default NULL;l_num number default p_dec;l_hex varchar2(50) default ‘0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ‘;beginif ( trunc(p_dec) <> p_dec OR p_dec < 0 ) thenraise PROGRAM_ERROR;end if;loopl_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 numberisl_num number default 0;l_hex varchar2(50) default ‘0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ‘;beginif (p_from_base = 16)thenl_num := to_number( p_str, rpad(‘x‘,63,‘x‘) );elsefor i in 1 .. length(p_str) loopl_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 varchar2isbeginreturn to_char( p_dec, ‘fm‘||rpad(‘x‘,63,‘x‘) );end to_hex;/create or replace function to_bin( p_dec in number ) return varchar2isbeginreturn to_base( p_dec, 2 );end to_bin;/create or replace function to_oct( p_dec in number ) return varchar2isbeginreturn to_base( p_dec, 8 );end to_oct;/
 
SYSDBA
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.
Tom Kyte has worked for Oracle since 1993. He is a vice president in the Oracle Public Sector group and the author of Expert Oracle Database Architecture: 9i and 10g Programming Techniques and Solutions (Apress, 2005) and Effective Oracle by Design (Oracle Press, 2003), among others.