|
On Speeding, Dating, and Spelling 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> begin 2 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$session 3 where sid = 4 (select sid 5 from v$mystat 6 where rownum = 1 7 ) 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. See asktom.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.) 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
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 data_logon_trigger after logon ON DATABASE begin execute immediate ‘alter session set 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‘) ------------------------------------------ 2453812 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. I encourage you to see asktom.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 replace function spell_number( p_number in number ) return varchar2 as 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); begin for i in 1 .. l_str.count loop exit when l_num is null; if ( to_number(substr(l_num, length(l_num)-2, 3)) <> 0 ) then l_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 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(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_used from ( select sum(bytes)/1024 Kbytes_free, max(bytes)/1024 largest, tablespace_name from sys.dba_free_space group by tablespace_name ) a, ( select sum(bytes)/1024 Kbytes_alloc, sum(maxbytes)/1024 Kbytes_max, tablespace_name from sys.dba_data_files group by tablespace_name union all select sum(bytes)/1024 Kbytes_alloc, sum(maxbytes)/1024 Kbytes_max, tablespace_name 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:
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 / 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 varchar2 is l_str varchar2(255) default NULL; l_num number default p_dec; l_hex varchar2(50) default ‘0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ‘; begin if ( trunc(p_dec) <> p_dec OR p_dec < 0 ) then raise PROGRAM_ERROR; end if; loop 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 is l_num number default 0; l_hex varchar2(50) default ‘0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ‘; begin if (p_from_base = 16) then l_num := to_number( p_str, rpad(‘x‘,63,‘x‘) ); else 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 is begin 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 is begin return to_base( p_dec, 2 ); end to_bin; / create or replace function to_oct( p_dec in number ) return varchar2 is begin return 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. |
|
|
