Note: Technical Talk being moved to:

Friday, 28 November 2008

PeopleSoft - Find Menu Path

PDF document containing a handy mod-pl/sql based stored procedure to find the PeopleSoft Menu Path, using a Page, Process or Component as the search criteria.

Friday, 21 November 2008

PeopleTools - Running Multiple Installs

Running multiple versions of PeopleTools from the same PC can sometimes be problematic. This is caused by the fact that each version's Configuration Manager writes to the exact same Registry Entry on the computer, namely "ConnectId" and "ConnectPswd".

For installs where the "people" credentials are the same across different PeopleTools installs, this of course is not a problem. The only thing is that the last Configuration Manager invoked has it's settings written to the Registry, which can be a little annoying.

To get around this, it's important to set the desired ConnectId and ConnectPswd prior to invoking PeopleTools. Obviously this would be tedious for users to do manually, as they would have to start up Configuration Manager and type in the correct credentials for the version of PeopleTools they wish to run. It would also, of course be a significant security risk if they knew the people password.

Therefore, I have created a batch file which I have placed in each PeopleTools version's bin directory. When invoked it calls the Configuration Manager, imports the configuration file containing the correct (encrypted) people credentials for that version, i.e. sets the Registry entries, and then invokes the Application Designer executable.

So instead of users double-clicking the Application Designer executable, I get them to run my batch file instead.

rem invoke Configuration Manager and Import Configuration Settings File
pscfg -import:c:\path\file.cfg -quiet

rem invoke Application Designer

Thursday, 20 November 2008

Oracle OEM Mining

It can be quite useful to mine the tables stored in the Oracle Enterprise Manager repository.

To check the size of your databases in gigabytes, simply run:

select target_name
     , to_char (sum (file_size) / 1024 / 1024 / 1024, '999,999.99') sze
  from mgmt$db_datafiles_all
group by target_name
order by 2 desc;

Friday, 14 November 2008

Oracle - Toad vs OEM: Statistics

Something I've learnt (the hard way) is that statistics generation using Toad is inferior to Oracle Enterprise Manager. Main reason is that Toad tends to lean towards using the analyze command rather than the dbms_gather.statistics package. For my money, the following method seems to be the panacea of all table statistics issues:
ownname=> 'OWNERNAME', 
tabname=> 'TABLENAME' , 
estimate_percent=> DBMS_STATS.AUTO_SAMPLE_SIZE, 
cascade=> TRUE);

Thursday, 6 November 2008

Oracle Table and Index Sizes

A useful query to check your large tables and indexes.

In this case, I'm checking those with a combined size of 1gb.

select y.table_name
     , x.tbl_size
     , y.idx_size
     , (tbl_size + idx_size) ttl_mb
  from (select segment_name
             , sum (bytes / 1024) / 1024 tbl_size
             , segment_type
          from dba_segments
         where segment_type in ('TABLE') 
           and owner = '{OWNER}'
        group by segment_name, segment_type) x
     , (select table_name
             , sum (bytes / 1024) / 1024 idx_size
             , segment_type
          from dba_segments a
             , dba_indexes b
         where a.segment_type in ('INDEX')
           and a.owner = '{OWNER}'
           and a.tablespace_name = b.tablespace_name
           and a.segment_name = b.index_name
          group by segment_type, table_name) y
 where x.segment_name = y.table_name 
   and (tbl_size + idx_size) > {1000}
order by tbl_size + idx_size desc;

Another method, applicable to auto segment space managed segments is to use the dbms_space package in lieu of going through dba_segments. In this case, however, I do use dba_segments as a join in order to get a count of the extents.

Unfortunately this method provided some inaccurate results in a locally managed environment.

set serveroutput on


v_kount NUMBER;
v_stmt VARCHAR2(1000);

cursor tbl_space is
select a.table_name
, a.tablespace_name
, a.owner
, sum(b.extents) num_extents
from dba_tables a
, dba_segments b
where a.table_name = b.segment_name
and a.owner = b.owner
and a.owner in ('{OWNER}')
group by a.table_name, a.tablespace_name, a.owner;

cursor idx_space is
select a.index_name
, a.tablespace_name
, a.owner
, sum(b.extents) num_extents
from dba_indexes a
, dba_segments b
where a.index_name = b.segment_name
and a.owner = b.owner
and a.owner in ('{OWNER}')
group by a.index_name, a.tablespace_name, a.owner;



dbms_output.put_line('DT_COLLECTED' || ',' || 'OWNER' || ',' || 'OBJECT_NAME' || ',' || 'OBJECT_TYPE' || ',' || 'OBJECT_TBSPC' || ',' || 'ALLOCATED_MB' || ',' || 'USED_MB' || ',' || 'PERCENT_USED' || ',' || 'NUM_EXTENTS' || ',' || 'ROW_COUNT');

for i in tbl_space loop

v_stmt := 'select count(*) from ' || i.owner || '.' || i.table_name;

execute immediate v_stmt into v_kount;

dbms_space.object_space_usage(i.owner, i.table_name, 'TABLE', NULL, su, sa, cp);

dbms_output.put_line(sysdate || ',' || i.owner || ',' || i.table_name|| ',' || 'TABLE'|| ',' || i.tablespace_name || ',' || TO_CHAR(sa/1024/1024,'999999.99')|| ',' || TO_CHAR(su/1024/1024,'999999.99')|| ',' || to_char((su / sa)*100,'99.99') || '%'|| ',' || i.num_extents|| ',' || v_kount);

end loop;

for i in idx_space loop

dbms_space.object_space_usage(i.owner, i.index_name, 'INDEX', NULL, su, sa, cp);

dbms_output.put_line(sysdate || ',' || i.owner || ',' || i.index_name || ',' || 'INDEX' || ',' || i.tablespace_name || ',' || TO_CHAR(sa/1024/1024,'999999.99') || ',' || TO_CHAR(su/1024/1024,'999999.99') || ',' || to_char((su / sa)*100,'99.99') || '%' || ',' || i.num_extents || ',' || 0);

end loop;


Saturday, 1 November 2008

Random Image Generator

On my website I have been using a third party PHP random image generator which scrolls through my /images folder and displays a random image.

This has been working fine except that my images are stored in multiple folders and as a result I've been missing out on displaying quite a few of these. Also, I don't have any control in terms of turning the random image returned into a hyperlink to the page containing it etc.

To solve this problem, I decided I would use the power of SQL to return a random image, rather than use PHP.

Basically, I have a number of tables in my database, all of which contain a couple of common fields:

  • IMAGE - Stores the directory and name of the Image.
  • URL - Stores the URL of the page on which the image appears.

    I achieved what I was after in a couple of very simple steps:

    Created a View to bring all the IMAGE and URL information into one place:

    Implemented the following SQL and PHP code:
    $resultx = mysql_query($queryx);
    while($row = mysql_fetch_row($resultx))
        $IMAGE = $row[0];
        $URL = $row[1];
        echo "<a href=$URL><img src=$IMAGE></a>";