More 'Features', Utilities and Tips Select Star

More 'Features', Utilities and Tips Select Star

By Tony Jambu
Got your attention, didn't I? In the previous January issue of Select, Rich Niemiec, Select's executive editor, wrote that readers wanted more tips and techniques articles. This column is mainly based on comments, tips and utilities provided by people just like you on CompuServe and the Internet. If you have any tips that you would like to share with your fellow readers, please do not be shy in sending them to me no matter how trivial you may think they are. All the information in this issue of Select Star, was provided by Oracle users and staff. The topics covered include:

Shared Pool Bug
The Year 2000
IP Connection via SQL*Net
Personal Oracle's Password
Coalescing Free Space
Limits with Pfile
A SQL*Plus 'feature'
OFA revisited
In the previous issue, I mentioned the lack of OFA compliance with Oracle 7.2's installation and syntax problems with some scripts. This caused concern with some readers, who wanted to know more. Sources have informed me that the OFA standard was not without its problem and is currently being reworked. It should be reintroduced with 7.3. As one observant Oracle DBA found out, there was a single line in the installation scripts which set a flag to turn on OFA style installation. This line was commented out.
With regard to the syntax errors in the installation, it appeared to be port specific. Solaris 2 in particular.
[As a side note, coraenv that is being distributed with Oracle 7.2 has a logic error with regards to the testing of C-shell shell variables. If you do not set the ORAENV_ASK variable and source the coraenv, you will get an error. The programmer assumed that C-shell would test the conditions from right to left with the '||' condition, line 41; just like the way Oracle would test it predicates or conditions. UNIX shell tests the conditions from left to right. Thus the error.]
Shared Pool Bug - ORA-04031
There has been a hive of discussion in the Internet's comp.databases.oracle newsgroup concerning Oracle 7.2 databases experiencing "ORA-04031 Unable to allocate XXX bytes of shared memory." This appears to be a generic problem with Oracle 7.2.2. The initial standard response from Oracle worldwide support had been to increase the Shared Pool or to pin your important packages and procedures.
As reported by Dave DeVore of Portland State University, they followed Oracle's instruction and increased the shared pool area from 9MB to 33MB and helped, but only temporarily. The problem reappeared after continuous use of the database. Following weeks of discussion, it was learned that there is a patch for this problem that should bring your version to 7.2.3. If you are communicating with Oracle support, quote the following bug numbers: 309484, 322904, 318582 and 323222.
Coalescing Adjacent Freespace
Another topic attracting a lot of online discussion covers coalescing fragmented free space in a tablespace. "ORA-01547" is normally the result of fragmented free space. Malcolm Turner provided the following utility which he obtained from an ex-Oracle colleague.
The above technique can be used against Oracle V6 and 7. With Oracle 7, however, SMON has an additional task which runs regularly to coalesce adjacent free space. Beginning with Oracle 7.1, SMON will not coalesce these adjacent free spaces if the tablespace's PCTINCREASE is set to '0'. It has to be greater than '0'. accept TABLESPACE_NAME prompt 'Tablespace: '
set echo off
set verify off
set feedback off
set pause off
set linesize 120
set termout off

drop table TMP_FS
/

create table TMP_FS
 (TABLESPACE_NAME char(30),
 BYTES number,
 COALESCABLE char(1))
/

set termout on

prompt Analysing coalescable free space...

declare
  cursor C_CFS is
  select TABLESPACE_NAME,
   FILE_ID,
   BLOCK_ID,
   BYTES,
   '1' CONTIGUOUS
 from DBA_FREE_SPACE DFS1
  where not exists
   (select null
   from DBA_FREE_SPACE DFS2
   where DFS1.TABLESPACE_NAME = DFS2.TABLESPACE_NAME
   and DFS1.FILE_ID = DFS2.FILE_ID
   and DFS1.BLOCK_ID = DFS2.BLOCK_ID + DFS2.BLOCKS)
  and exists
   (select null
    from DBA_FREE_SPACE DFS2
    where DFS1.TABLESPACE_NAME = DFS2.TABLESPACE_NAME
    and DFS1.FILE_ID = DFS2.FILE_ID
    and DFS1.BLOCK_ID + DFS1.BLOCKS = DFS2.BLOCK_ID)
  and TABLESPACE_NAME like upper('&TABLESPACE_NAME')
  union
  select TABLESPACE_NAME,
   FILE_ID,
   BLOCK_ID,
   BYTES,
   '2'
 from DBA_FREE_SPACE DFS1
 where exists
  (select null
  from DBA_FREE_SPACE DFS2
  where DFS1.TABLESPACE_NAME = DFS2.TABLESPACE_NAME
  and DFS1.FILE_ID = DFS2.FILE_ID
  and DFS1.BLOCK_ID = DFS2.BLOCK_ID + DFS2.BLOCKS)
  and exists
   (select null
    from DBA_FREE_SPACE DFS2
    where DFS1.TABLESPACE_NAME = DFS2.TABLESPACE_NAME
    and DFS1.FILE_ID = DFS2.FILE_ID
    and DFS1.BLOCK_ID + DFS1.BLOCKS = DFS2.BLOCK_ID)
    and TABLESPACE_NAME like upper('&TABLESPACE_NAME')
  union
  select TABLESPACE_NAME,
   FILE_ID,
   BLOCK_ID,
   BYTES,
   '3'
  from DBA_FREE_SPACE DFS1
  where exists
   (select null
from DBA_FREE_SPACE DFS2
   where DFS1.TABLESPACE_NAME = DFS2.TABLESPACE_NAME
   and DFS1.FILE_ID = DFS2.FILE_ID
   and DFS1.BLOCK_ID = DFS2.BLOCK_ID + DFS2.BLOCKS)
  and not exists
   (select null
   from DBA_FREE_SPACE DFS2
   where DFS1.TABLESPACE_NAME = DFS2.TABLESPACE_NAME
   and DFS1.FILE_ID = DFS2.FILE_ID
   and DFS1.BLOCK_ID + DFS1.BLOCKS = DFS2.BLOCK_ID)
  and TABLESPACE_NAME like upper('&TABLESPACE_NAME')
  union
  select TABLESPACE_NAME,
   FILE_ID,
   BLOCK_ID,
   BYTES,
   '4'
  from DBA_FREE_SPACE DFS1
  where not exists
   (select null
   from DBA_FREE_SPACE DFS2
   where DFS1.TABLESPACE_NAME = DFS2.TABLESPACE_NAME
   and DFS1.FILE_ID = DFS2.FILE_ID
   and DFS1.BLOCK_ID = DFS2.BLOCK_ID + DFS2.BLOCKS)
  and not exists
   (select null
   from DBA_FREE_SPACE DFS2
   where DFS1.TABLESPACE_NAME = DFS2.TABLESPACE_NAME
   and DFS1.FILE_ID = DFS2.FILE_ID
   and DFS1.BLOCK_ID + DFS1.BLOCKS = DFS2.BLOCK_ID)
  and TABLESPACE_NAME like upper('&TABLESPACE_NAME')
  order by 2, 3;
  R_CFS C_CFS%rowtype;
  TOT_BYTES number;
begin
      for R_CFS in C_CFS
      loop
             if R_CFS.CONTIGUOUS = '1'
             then TOT_BYTES := R_CFS.BYTES;
             elsif R_CFS.CONTIGUOUS = '2'
             then TOT_BYTES := TOT_BYTES + R_CFS.BYTES;
             elsif R_CFS.CONTIGUOUS = '3'
	 then
                    insert into TMP_FS
                    (TABLESPACE_NAME,
                    BYTES,
                    COALESCABLE)
                    values
                    (R_CFS.TABLESPACE_NAME,
                    TOT_BYTES + R_CFS.BYTES,
                    'Y');
                    commit;
             else
                    insert into TMP_FS
                    (TABLESPACE_NAME,
                    BYTES,
                    COALESCABLE)
                    values
                    (R_CFS.TABLESPACE_NAME,
                    R_CFS.BYTES,
                    'N');
                    commit;
             end if;
      end loop;
-
      delete from TMP_FS FS1
      where COALESCABLE = 'N'
      and not exists
              (select null
              from TMP_FS FS2
              where FS2.TABLESPACE_NAME = FS1.TABLESPACE_NAME
              and FS2.COALESCABLE = 'Y'
              and FS2.BYTES <= FS1.BYTES);
      commit;
end;
/

set termout off
spool fsc.tmp

prompt spool fsc.log
prompt set termout on
prompt prompt Creating temporary tables...
prompt set termout off
prompt set feedback on
prompt set echo on

select 'create table TMP_FS_'||to_char(rownum)||
' (X char(1)) tablespace '|| TABLESPACE_NAME ||
' storage (initial ' || to_char(BYTES) || ');'
from TMP_FS
order by BYTES desc
/

prompt set echo off
prompt set termout on
prompt prompt Dropping temporary tables...
prompt set termout off
prompt set echo on

select 'drop table TMP_FS_'||to_char(rownum)||';'
from TMP_FS
/

prompt set echo off
prompt set feedback off
prompt spool off
prompt set termout on

spool off

start fsc.tmp

drop table TMP_FS
/
exit
If you have been reading Select Star in the previous issues, you would have learned about the undocumented command, "ALTER SESSION SET EVENTS." Surprise, surprise. There is an event that will force SMON to start coalescing the tablespace specified.
alter session set events 'immediate trace name coalesce level tablespace#';
This knowledge was used by the more efficient script provided by Marty Boos of Fingerhut below.
REM *****************************************************
REM  Author   : John Marvin (Fingerhut Companies)
REM  Filename : coalesce.sql
REM
REM 	Date	Who	Change / reason
REM 	-----	---	---------------------------------
REM	21-dec-94	jhm	Original
REM	1-oct-95 	av	Changed so that just tablespace # input is needed
REM 			The utility will try to coalesce 512 extents
REM *****************************************************

set linesize 80
set pagesize 40
set verify off
REM set newpage 0
REM set termout off
col nb1 format 999999999999 noprint new_value nb2

set feed off
set newpage 1
PROMPT *****************************************************************
PROMPT This utility will try to coalesce 4095 extents in the desired Tblsp
PROMPT *****************************************************************
PROMPT
select ts#,
       name
from sys.ts$
order by ts#;

set head off

REM select 'convert hi 4 bytes as number of extents and low 4 bytes a TS#	',
REM        '(hex to decimal conversion)         	',
REM        '                	',
REM        'ex: 0005 0000 = 327680 (5 ext in sys)'
REM from dual;
REM select ' '
REM from dual;

set head on
set feed on

PROMPT
accept nb prompt 'Enter tablespace number :'
set termout off
select (268369920+&nb) nb1 from dual;
set termout on
alter session set events 'immediate trace name coalesce level &nb2';

clear breaks
clear computes
clear columns
While all these were being discussed last year, Scott Heisey of Oracle informed us that it would be possible to issue the following command in Oracle 7.3 to coalese a tabelspace.
IALTER TABLESPACE  COALESCE.
P addresses and SQL*Net
Sometimes it is necessary to identify an Oracle session by it's IP address. One such reason is the case where a long running job on a PC via SQL*Net V1 is terminated by rebooting the PC. While the client program may have terminated, its Oracle shadow process may still be executing on the server. If you are unable to identify the users from V$session, there is another method if you know the IP addresses of the PCs.
On UNIX boxes, all SQL*Net TCP connections are via sockets. The example below relates to SQL*Net V1 whose default socket number is 1525.
netstat -A |grep 1525
Get all processes associated with the Oracle socket, 1525.
PCB  	Proto Recv-Q  Send-Q    Local Address    Foreign Address  (state)
fface20c 	tcp        0       0    redbaron.1714    biggles.1525     ESTABLISHED
ffa9d60c 	tcp        0       0    redbaron.1525    biggles.4847     ESTABLISHED
ffa7ae8c 	tcp        0       0    redbaron.1052    biggles.1525     ESTABLISHED
Sample output
Next you will need to read your UNIX memory to identify the IP address. There are a couple of public domain utility to do this, 'ofiles' and 'lsof'. My preference would be to use 'lsof' but the example below uses "ofiles."
ofiles -n PCB_number
This will provide you with the UNIX process id (PID).
If all of this sounds French, take heed. There is an easier solution as mentioned by the ever helpful Thomas Kyte of Oracle Government. Thomas informs us that Oracle has started to capture additional network and user information starting with Oracle 7.2.3 and he has provided an unsupported but invaluable utility.
You will need to set the AUDIT_TRIAL parameter to TRUE in the INIT.ORA file and execute the 'AUDIT CONNECT' command. This causes Oracle to capture enough information in the audit trail to extract the IP address.
- IP package
-  	AUDIT_TRAIL = TRUE must be set in the init.ora file
-  	AUDIT CONNECT; must have been executed by DBA for this to work
-
- the ip package has two external interfaces:
-  	address; 'pure' pl/sql function returning ip address
-  	into_v$session; moves ip address into v$session
-                      so you can "select client_info from v$session"
-
- USAGE:
-
-  	Most commonly you would use the address function directly.
-  	For performance reasons I will suggest it is always used in views
-  	in the following manner:
-
-  	create view my_view
-  	as
-  	select *
-  	  from my_table
-  	 where exists ( select NULL
-      	                  from allowed_ip_addresses
-      	                 where ip_address = ( SELECT IP.ADDRESS FROM DUAL )
-     	              )
-
-  	create view my_view
-  	as
-  	select * from my_other_table
-  	 where ip_address = ( SELECT IP.ADDRESS FROM DUAL )
-



-  	and so on. Basically I am suggesting the IP.ADDRESS be selected
-  	from DUAL. This will cause the PL/SQL routine IP.ADDRESS to be
-  	called ONCE per query, instead of once PER ROW per query. The
-  	overhead associated with calling pl/sql for every row will be
-  	too large to use this successfully if you don't employ the above
-  	'trick'.
-
-  	In short, instead of saying
-   	    x = ip.address
-  	always say
-   	    x = ( select ip.address from dual ).
-  	Instead of saying
-   	    ip.address in ( select x from T .... )
-  	always say
-   	    where exists ( select NULL from T ....
 
-  	always say
-   	    where exists ( select NULL from T ....
-  	                   AND x = ( select ip.address from DUAL )
-
 
create or replace package ip
as
   pragma restrict_references( ip, wnds, wnps, rnps );
 
   function address return varchar2;
pragma restrict_references( address, wnds, wnps );
 
   procedure into_v$session;
end ip;
/
show errors
 
create or replace package body ip
as
 
    theAddress varchar2(2000);
 
    function address return varchar2
 
    function address return varchar2
    is
    begin
        return theAddress;
    end;
 
    procedure into_v$session
    is
    begin
        dbms_application_info.set_client_info( theAddress );
    end;
begin
    select comment_text
      into theAddress
      from user_audit_trail
     where sessionid = userenv('SESSIONID');
 
    declare
        n number;
    begin
        n := instr( theAddress, '(HOST=' );
        theAddress := substr( theAddress, n+6 );
 
        n := instr( theAddress, '(HOST=' );
        theAddress := substr( theAddress, n+6 );
        n := instr( theAddress, ')' );
        theAddress := substr( theAddress, 1, n-1 );
    end;
end ip;
/
show errors
 
grant execute on ip to public
/
create public synonym ip for ip
/
The above utility is also useful on non UNIX platform or TCP/IP connection as the entire SQL*Net 2.0 string the client used to connect to the database is stored in the audit trail if you audit connects.
A SQL*Plus 'feature'
Edith Sandy had a problem with one of her SQL statement where it would work if a comment was embedded somewhere in the SQL statement but not on the last line.
SELECT field1 FROM my_table /* this works OK */
WHERE field1 = 'XXX';
If the comment was placed on the last line, the SQL statement would be left hung and waiting for input.
SELECT field1 FROM my_table
WHERE field1 = 'XXX';  /* but this statement is skipped! */
If a "/" was then typed, an error message complaining about the semi-colon as an invalid character would be displayed. Thus, her request for help.
An answer was provided by Thomas Kyte of Oracle. It appears to be a bug with SQL*Plus rather than with the Oracle RDBMS kernel. The following is an extract of his posting.
"In SQL*Plus there are two ways to terminate a command typically. You can use the "SET CMDSEP c" command to set the command separator to the character c. This defaults to ';'. The other way is to use the '/' SQL*Plus command to run the contents of the buffer.
When using comments /* .. */ or -, SQL*Plus does not seem to recognize the CMDSEP character on that line.
Apparently, the cmdsep must be the last non-white space character on the line.
The error message observed by Edith Sandy was due to the fact that the ';' character isn't valid sql and since the cmdsep character wasn't the last character on the line, SQL*Plus placed it in the buffer to be executed. Entering the '/' cause the buffer to be sent to the database, "select * from dual;" is not a valid SQL, "select * from dual" is. This behavior (the above) is expected given that the semi colon got into the buffer."
So the solution to the problem is to place the comment on a line on its self or not on the last SQL line.
Limits With Pfile or INIT.ORA file
Like any good DBA, when adding or changing any parameters in the pfile or INIT.ORA file, one should always document details of who, when and why the change is required. There was a requirement to increase the maximum number of sessions for the Oracle instance and its value was increased. An additional comment line was inserted explaining the reason for this change. Lo and behold, the production database failed to startup with the following error
DBA-00325: pfile too large
Well apparently there is an 8K byte limit to the size of your pfile. So be warned!
[subhead]Personal Oracle Password
If I made a dollar every time someone asked the question about what the default Personal Oracle password is, I would be a very rich man by now. Is it 'oracle', 'tiger', 'manager' or 'change_on_install'?
The default password for Personal Oracle is 'oracle' but if you have forgotten what you passwords are, Peter ([email protected]) has a solution. Just delete the \ORAWIN\RDBMS71\ADMIN\PASSWORD.ORA and execute the Password Manager utility. This will recreate the password file.
To bypass the password altogether, place the following line in the ORACLE section of the ORACLE.INI file.
DBA_AUTHORIZATION=BYPASS
Year 2000
The year 2000 is approaching and is not too far away. Less than 1,500 days. I know where I'll be on the 31st of December 1999. In the Australian Outback, bush camping with no radio, TV or communication with the outside world as I do not want to be around civilization when all hell breaks loose. But will it? Did you ever wonder about some of the banking systems that are running on 20 year old machines that are now obsolete, where the programs were written in assembly code and there is no longer any source code?
This topic is a serious issue and the IT industry is trying to address it before it becomes a monster of a problem. There are Web sites and mailing lists setup as well as conferences planned to address this problem.
For more information, visit the following site, http://www.year2000.com.

About the Author
Tony Jambu, an Independent Oracle Database Consultant specializing in the UNIX environment works for Australia's largest telecommunication company, Telstra. He is an active member of the International and Australian State (Victoria) Oracle User Groups and has written numerous articles for Oracle journals and newsletters. Tony is also the Asia-Pacific technical editor for Select.
If you have any comments, feedback, information or news and would like to share this information with others please contact the column editor on:

Internet: [email protected]

Compuserve: 100250,2003

Address: 6 Georgiana Close
Chelsea Heights
Melbourne Victoria
Australia 3196

Telephone: +61 (41) 985 2628
+61 (3) 9776 1599

FAX: +61 (3) 9416 3559

This is a copy of an article published @ http://www.ioug.org/